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

December 13, 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. :D

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:\oracle\product\10.2.0\db_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:\oracle\product\10.2.0>cd admin
C:\oracle\product\10.2.0\admin>mkdir cloneDB
C:\oracle\product\10.2.0\admin>cd cloneDB
C:\oracle\product\10.2.0\admin\cloneDB>mkdir adump bdump cdump dpdump hdump pfile scripts udump
C:\oracle\product\10.2.0\admin\cloneDB>dir
 Volume in drive C has no label.
 Volume Serial Number is BC39-7914 

Directory of C:\oracle\product\10.2.0\admin\cloneDB 

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:\oracle\product\10.2.0\db_1\database> 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:\oracle\product\10.2.0\admin\cloneDB\adump'
*.background_dump_dest='C:\oracle\product\10.2.0\admin\cloneDB\bdump'
*.compatible='10.2.0.3.0'
*.control_files='+DG_DATA/cloneDB/controlfile/controlfile1.ctl','+DG_FRA/cloneDB/controlfile/controlfile2.ctl'
*.core_dump_dest='C:\oracle\product\10.2.0\admin\cloneDB\cdump'
*.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:\oracle\product\10.2.0\admin\cloneDB\udump'
*.db_file_name_convert=('dejan','cloneDB')
*.instance_name='cloneDB'

Kreiranje “password” datoteke


C:\> orapwd file=C:\oracle\product\10.2.0\db_1\database\PWDcloneDB.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:\oracle\product\10.2.0\db_1\database\initcloneDB.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:\oracle\product\10.2.0\db_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:\oracle\product\10.2.0\db_1\database\initcloneDB.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:\oracle\product\10.2.0\db_1\database\initcloneDB.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! :D

Post a Comment

Blog "Baze podataka" koristi WordPress | Tekstovi (RSS) | Komentari (RSS)