Kloniranje/dupliciranje Oracle “ASM based” baze (How to duplicate Oracle “ASM based” database)

Thursday, 13.12.2007 – Dejan

Pojavila se potreba da postoje─çu bazu klonirate, da biste mogli npr. izvr┼íiti odre─Ĺene testove i provjeriti funkcionalnost novih programa. U ovom slu─Źaju taj problem, odnosno zadatak, jo┼í kompleksnijim ─Źini kori┼ítenje ASM (Automatic Storage Management), tako da postupak kloniranja baze nije jednostavan kao kod kloniranja baze sa datotekama na OS File-Systemu.

Sad se pitate:”Kako najlak┼íe kopirati Oracle bazu, odnosno kako najefikasnije napraviti klon-bazu?“.

Klon-bazu mo┼żete kreirati na 3 na─Źina:
1. koriste─çi RMAN i naredbu DUPLICATE
2. DBMS_FILE_TRANSFER paket
3. koriste─çi Enterprise Manager i opciju “Clone Database
4. pomo─çu “Cold backup” metode (rijetko se koristi, pogotovo u 24/7 okru┼żenju)

Koju ćete metodu za kloniranje baze koristiti, uopšteno zavisi od nekoliko faktora:
– da li baza koristi ASM ili file system storage
– da li ─çe baze koristiti zajedni─Źku ASM instancu
– da li se klon-baza mora nalaziti na drugom serveru
– da li koristite Grid Control ili samo Database Control
– koju verziju Oracle baze i softwarea koristite (npr. nije svejedno da li koristite Oracle 9i ili Oracle 10g)
– i td.

Mi u firmi imamo 2 potrebe za kloniranjem:
– kloniranje baze na istom serveru i uz kori┼ítenje zajedni─Źke ASM instance
– kloniranje baze na drugi server sa posebnom ASM instancom

Za prvu potrebu koristimo RMAN i naredbu DUPLICATE DATABASE, a za drugu potrebu koristimo DBMS_FILE_TRANSFER paket.

Za sada ─çu objasniti samo prvu metodu, a drugu ─çu nekom drugom prilikom kada budem imao volje i motivacije. ­čśÇ

Kloniranje baze koriste─çi RMAN i naredbu DUPLICATE DATABASE

Metodu kloniranja baze koriste─çi RMAN prikaza─çu jednim prakti─Źnim primjerom, u kojem ─çu korak po korak objasniti ┼íta se i kako mora obaviti, da bi baza bila uspje┼íno klonirana.

U ovom primjeru ─çu koristiti slijede─çe pojmove:
dejanDB: postoje─ça ciljna (target) baza, koju trebamo klonirati
cloneDB: klon-baza
+ASM: zajedni─Źka ASM instanca
ORACLE_HOME: mjesto gdje je instaliran Oracle software; u ovom slu─Źaju je to C:oracleproduct10.2.0db_1

Dakle, u ORACLE_HOME direktorijumu smo instalirali Oracle software i kreirali jednu bazu pod nazivom dejanDB. Tu bazu ozna─Źavamo kao ciljnu, odnosno “target” bazu, koju ─çemo klonirati. Klon-bazu ─çemo nazvati cloneDB i nalazi─çe se na istom serveru na kojem se nalazi i dejanDB, a koristi─çe zajedni─Źku ASM instancu u kojoj ─çe se nalaziti fizi─Źke datoteke (database files).

Kreiranje ASM direktorija

Spojimo se na ASM instancu i kreirajmo potrebne direktorije u kojima ─çe se nalaziti fizi─Źke datoteke:

C:> set ORACLE_SID=+ASM
C:> asmcmd
ASMCMD> ls
DG_DATA
DG_FRA
ASMCMD> cd DG_DATA
ASMCMD> cd dejanDB
ASMCMD> pwd
+DG_DATA/dejanDB 
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
ASMCMD> cd ..
ASMCMD> mkdir cloneDB
ASMCMD> cd cloneDB
ASMCMD> mkdir CONTROLFILE DATAFILE ONLINELOG PARAMETERFILE TEMPFILE
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
ASMCMD> pwd
+DG_FRA
ASMCMD> mkdir cloneDB
ASMCMD> cd cloneDB
ASMCMD> mkdir ARCHIVELOG CONTROLFILE DATAFILE ONLINELOG PARAMETERFILE
ARCHIVELOG/
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
ASMCMD> exit

Kreiranje OS direktorija

Moramo kreirati i direktorije na nivou operativnog sistema (OS je u na┼íem slu─Źaju Windows), u kojima ─çe se nalaziti “alert log”, “trace” i ostale “dump” datoteke:

C:oracleproduct10.2.0>cd admin
C:oracleproduct10.2.0admin>mkdir cloneDB
C:oracleproduct10.2.0admin>cd cloneDB
C:oracleproduct10.2.0admincloneDB>mkdir adump bdump cdump dpdump hdump pfile scripts udump
C:oracleproduct10.2.0admincloneDB>dir
 Volume in drive C has no label.
 Volume Serial Number is BC39-7914 

Directory of C:oracleproduct10.2.0admincloneDB 

12/07/2007  09:58 AM    <DIR>          .
12/07/2007  09:58 AM    <DIR>          ..
12/07/2007  09:58 AM    <DIR>          adump
12/07/2007  09:58 AM    <DIR>          bdump
12/07/2007  09:58 AM    <DIR>          cdump
12/07/2007  09:58 AM    <DIR>          dpdump
12/07/2007  09:58 AM    <DIR>          hdump
12/07/2007  09:58 AM    <DIR>          pfile
12/07/2007  09:58 AM    <DIR>          scripts
12/07/2007  09:58 AM    <DIR>          udump
               0 File(s)              0 bytes
              10 Dir(s)  283,583,328,256 bytes free

Podešavanje init-parametara

Napravimo kopiju init-datoteke od baze dejanDB i imenujmo ju “initcloneDB.ora”:

C:oracleproduct10.2.0db_1database> copy initdejanDB.ora initcloneDB.ora

Otvorimo tu datoteku i podesimo odgovaraju─çe parametre:

*.__db_cache_size=687865856
*.__java_pool_size=4194304
*.__large_pool_size=12582912
*.__shared_pool_size=239075328
*.__streams_pool_size=0
*.audit_file_dest='C:oracleproduct10.2.0admincloneDBadump'
*.background_dump_dest='C:oracleproduct10.2.0admincloneDBbdump'
*.compatible='10.2.0.3.0'
*.control_files='+DG_DATA/cloneDB/controlfile/controlfile1.ctl','+DG_FRA/cloneDB/controlfile/controlfile2.ctl'
*.core_dump_dest='C:oracleproduct10.2.0admincloneDBcdump'
*.cpu_count=2
*.cursor_sharing='EXACT'
*.db_block_size=16384
*.db_create_file_dest='+DG_DATA'
*.db_domain='clone'
*.db_file_multiblock_read_count=16
*.db_files=300
*.db_keep_cache_size=102400000
*.db_name='cloneDB'
*.db_recovery_file_dest='+DG_FRA'
*.db_recovery_file_dest_size=168820736000
*.db_writer_processes=2
*.dispatchers='(PROTOCOL=TCP) (SERVICE=cloneDBXDB)'
*.job_queue_processes=10
*.large_pool_size=10240000
*.log_archive_dest_1='LOCATION=+DG_FRA/cloneDB/'
*.nls_length_semantics='CHAR'
*.open_cursors=300
*.optimizer_mode='FIRST_ROWS'
*.pga_aggregate_target=351272960
*.processes=350
*.remote_login_passwordfile='exclusive'
*.session_cached_cursors=200
*.sessions=390
*.sga_target=1054867456
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='C:oracleproduct10.2.0admincloneDBudump'
*.db_file_name_convert=('dejan','cloneDB')
*.instance_name='cloneDB'

Kreiranje “password” datoteke


C:> orapwd file=C:oracleproduct10.2.0db_1databasePWDcloneDB.ora password=pass123 entries=5

Kreiranje instance za klon-bazu

Instancu za klon-bazu kreiramo pomo─çu oradim alata:

C:>oradim -NEW -SID cloneDB -syspwd "pass123" -startmode manual
Instance created.

Provjerimo da li je nova instanca aktivna i pokrenimo ju u NOMOUNT modusu:

C:>set ORACLE_SID=cloneDB
C:>sqlplus /nolog
SQL*Plus: Release 10.2.0.3.0 - Production on Fri Dec 7 10:24:29 2007
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup nomount pfile=C:oracleproduct10.2.0db_1databaseinitcloneDB.ora
ORACLE instance started.

Total System Global Area 1056964608 bytes
Fixed Size                  2060264 bytes
Variable Size             260046872 bytes
Database Buffers          788529152 bytes
Redo Buffers                6328320 bytes
SQL> exit

listener.ora i tnsnames.ora

Da bismo se mogli spojiti na klon-bazu, moramo dodati unose za klon-bazu u konfiguracijske datoteke listener.ora i tnsnames.ora:

listener.ora: 

    (SID_DESC =
      (GLOBAL_DBNAME = cloneDB)
      (ORACLE_HOME = C:oracleproduct10.2.0db_1)
      (SID_NAME = cloneDB)
    )

tnsnames.ora: 

CLONEDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = cloneDB)
    )
  )

Nakon ┼íto smo sa─Źuvali izmjene, potrebno je listener restartovati, kako bi konfiguracija za klon-bazu imala efekt:

C:> lsnrctl reload

Backup “target” baze

Uslov za kloniranje baze pomo─çu RMAN-a i naredbe DUPLICATE DATABASE je da “target” baza ima ispravan backup. Ukoliko va┼ía baza ima backup, onda mo┼żete presko─Źiti ovaj dio.

C:> set ORACLE_SID=dejanDB 
C:> rman target / 
RMAN> configure device type disk parallelism 2 backup type to copy;
new RMAN configuration parameters: 
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COPY; 
new RMAN configuration parameters are successfully stored 

RMAN> backup database;
RMAN> sql "alter system archive log current";
sql statement: alter system archive log current
RMAN> backup archivelog all;

Kloniranje baze

RMAN> connect auxiliary sys/pass123@cloneDB
connected to auxiliary database: CLONEDB (not mounted) 

RMAN> duplicate target database to cloneDB 
pfile=C:oracleproduct10.2.0db_1databaseinitcloneDB.ora 
logfile '+DG_DATA/cloneDB/onlinelog/redo1.dbf' size 100m, 
'+DG_DATA/cloneDB/onlinelog/redo2.dbf' size 100m; 

Starting Duplicate Db at 07-DEC-07 
... 
contents of Memory Script: 
{ 
   Alter clone database open resetlogs; 
} 
executing Memory Script 

database opened 
Finished Duplicate Db at 07-DEC-07 
RMAN>

SPFILE umjesto PFILE

Logujte se kao SYS, kreirajte SPFILE datoteku i restartujte bazu:

SQL> conn sys/pass123@cloneDB as sysdba
Connected.

SQL> create spfile from pfile='C:oracleproduct10.2.0db_1databaseinitcloneDB.ora';
File created.
SQL> shutdown immediate
SQL> startup

Finalna provjera

Na kraju moramo provjeriti, da li je baza uspje┼íno klonirana i da li funkcioni┼íe kako treba. Logujte se na bazu i pokrenite neki upit. Ne zaboravite provjeriti da li su aktivni neki definisani poslovi (“jobs”) i da ne dolaze u konflikt sa zadanim poslovima na “target” bazi.
Tako─Ĺe provjerite da li su TEMP tablespaces ispravno kreirani:

select property_name, property_value from database_properties;

select username, temporary_tablespace, account_status from dba_users;

Ukoliko neki TEMP tablespace ili tempfile nedostaje, mo┼żete ga kreirati ovom komandom:


CREATE TEMPORARY TABLESPACE "TEMP01" TEMPFILE '+DG_DATA/cloneDB/TEMPFILE/temp01.dbf' SIZE 5000M AUTOEXTEND ON NEXT 104857600 MAXSIZE 10000M;

odnosno dodati tempfile:


alter tablespace TEMP01 add tempfile '+DG_DATA/cloneDB/TEMPFILE/temp01.dbf' SIZE 5000M AUTOEXTEND ON NEXT 104857600 MAXSIZE 10000M;

To bi bilo to.

Sre─çno kloniranje! ­čśÇ

Post a Comment