domingo, 30 de septiembre de 2012

Oracle RMAN DUPLICATE database

This practical case, shows how to easily duplicate a Oracle database using RMAN.

Starting with our database, instance named TEST, in open mode, we'll create all we need to start in nomount mode a second instance named TEST2, this one will be the cloned database, finally launching copy from RMAN utility to clone the database.

1.- The first step, creating password file for the new instance (TEST2).

[oracle@oracletest ~]$ cd $ORACLE_HOME/dbs
[oracle@oracletest dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwTEST2


Enter password for SYS: 

2.- New listener for TEST2 database.

Configure the listener.ora and tnsnames.ora files, to permit database TEST2 accepting connections.

[oracle@oracletest admin]$ cd $ORACLE_HOME/network/admin
[oracle@oracletest admin]$ vi listener.ora 

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = TEST)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db)
      (SID_NAME = TEST)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = TEST2)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db)
      (SID_NAME = TEST2)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracletest)(PORT = 1521))
  )

ADR_BASE_LISTENER = /u01/app/oracle

TRACE_LEVEL_LISTENER = USER

[oracle@oracletest admin]$ vi tnsnames.ora 

TEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracletest)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TEST)
    )
  )

TEST2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracletest)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TEST2)
3.-  Create pfile.

[oracle@oracletest admin]$ cd $ORACLE_HOME/dbs
[oracle@oracletest dbs]$ vi initTEST2.ora

DB_NAME=TEST2
DB_BLOCK_SIZE=8192
CONTROL_FILES=(+DATA2/test2/controlfile/control01.ctl)
DB_FILE_NAME_CONVERT=(+DATA,+DATA2)
LOG_FILE_NAME_CONVERT=(+DATA,+DATA2)

Only these above parameters are in the new database pfile, DB_NAME specifies the new name for cloned instance, db_block_size is the block size for database, the next parameter indicates the control files location, and the last two parameters are for the naming conversion for datafiles and redo log files, the new ASM disk location for this files will be +DATA2 instead the original +DATA1.

Export ORACLE_SID, and run into nomount mode the new instance TEST2.

[oracle@oracletest dbs]$ export ORACLE_SID=TEST2
[oracle@oracletest dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Sep 29 22:41:19 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile='?/dbs/initTEST2.ora';
ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size    2211928 bytes
Variable Size  159387560 bytes
Database Buffers   50331648 bytes
Redo Buffers    5226496 bytes

SQL> create spfile from pfile;

File created.

4.- Start origin database (TEST) in mount or open mode.

[oracle@oracletest ~]$ echo $ORACLE_SID
TEST
[oracle@oracletest ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Sep 29 22:45:17 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  313159680 bytes
Fixed Size    2212936 bytes
Variable Size  163580856 bytes
Database Buffers  142606336 bytes
Redo Buffers    4759552 bytes
Base de datos montada.

5.- Connect to RMAN and executing DUPLICATE command to start with the clone process.

Connect to RMAN on the origin database (TEST) , from here connecting to the new database using the following command: CONNECT AUXILIARY.

Once DUPLICATE command is running, RMAN is responsible for doing all the job.

[oracle@oracletest ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Sep 29 22:46:32 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target sys@TEST  

target database Password: 
connected to target database: TEST (DBID=2093094325, not open)

RMAN> connect auxiliary sys@TEST2

auxiliary database Password: 
connected to auxiliary database: TEST2 (not mounted)

RMAN> duplicate target database to TEST2 from active database;


Starting Duplicate Db at 29-SEP-2012 22:57:20
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=1 device type=DISK

contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     217157632 bytes

Fixed Size                     2211928 bytes
Variable Size                159387560 bytes
Database Buffers              50331648 bytes
Redo Buffers                   5226496 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name = 
 ''TEST'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name = 
 ''TEST2'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   backup as copy current controlfile auxiliary format  '+DATA2/test2/controlfile/control01.ctl';
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''TEST'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''TEST2'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     217157632 bytes

Fixed Size                     2211928 bytes
Variable Size                159387560 bytes
Database Buffers              50331648 bytes
Redo Buffers                   5226496 bytes

Starting backup at 29-SEP-2012 22:59:41
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/u01/app/oracle/product/11.2.0/db/dbs/snapcf_TEST.f tag=TAG20120929T225952 RECID=15 STAMP=795308405
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
Finished backup at 29-SEP-2012 23:00:28

database mounted
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

contents of Memory Script:
{
   set newname for datafile  1 to 
 "+data2";
   set newname for datafile  2 to 
 "+data2";
   set newname for datafile  3 to 
 "+data2";
   set newname for datafile  4 to 
 "+data2";
   backup as copy reuse
   datafile  1 auxiliary format 
 "+data2"   datafile 
 2 auxiliary format 
 "+data2"   datafile 
 3 auxiliary format 
 "+data2"   datafile 
 4 auxiliary format 
 "+data2"   ;
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 29-SEP-2012 23:01:06
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/test/datafile/system.276.794947707
output file name=+DATA2/test2/datafile/system.257.795308479 tag=TAG20120929T230107
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/test/datafile/sysaux.277.794947709
output file name=+DATA2/test2/datafile/sysaux.256.795308565 tag=TAG20120929T230107
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/test/datafile/undotbs1.278.794947711
output file name=+DATA2/test2/datafile/undotbs1.259.795308631 tag=TAG20120929T230107
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/test/datafile/users.259.795088363
output file name=+DATA2/test2/datafile/users.260.795308657 tag=TAG20120929T230107
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 29-SEP-2012 23:04:21

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=15 STAMP=795308662 file name=+DATA2/test2/datafile/system.257.795308479
datafile 2 switched to datafile copy
input datafile copy RECID=16 STAMP=795308662 file name=+DATA2/test2/datafile/sysaux.256.795308565
datafile 3 switched to datafile copy
input datafile copy RECID=17 STAMP=795308662 file name=+DATA2/test2/datafile/undotbs1.259.795308631
datafile 4 switched to datafile copy
input datafile copy RECID=18 STAMP=795308663 file name=+DATA2/test2/datafile/users.260.795308657

contents of Memory Script:
{
   set until scn  1092216;
   recover
   clone database
   noredo
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 29-SEP-2012 23:04:24
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=23 device type=DISK

Finished recover at 29-SEP-2012 23:04:39

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
   sql clone "alter system set  db_name = 
 ''TEST2'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     217157632 bytes

Fixed Size                     2211928 bytes
Variable Size                159387560 bytes
Database Buffers              50331648 bytes
Redo Buffers                   5226496 bytes

sql statement: alter system set  db_name =  ''TEST2'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     217157632 bytes

Fixed Size                     2211928 bytes
Variable Size                159387560 bytes
Database Buffers              50331648 bytes
Redo Buffers                   5226496 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TEST2" RESETLOGS ARCHIVELOG 
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1 ( '+data2' ) SIZE 50 M  REUSE,
  GROUP  2 ( '+data2' ) SIZE 50 M  REUSE,
  GROUP  3 ( '+data2' ) SIZE 50 M  REUSE
 DATAFILE
  '+DATA2/test2/datafile/system.257.795308479'
 CHARACTER SET AL32UTF8


contents of Memory Script:
{
   set newname for tempfile  1 to 
 "+data2";
   switch clone tempfile all;
   catalog clone datafilecopy  "+DATA2/test2/datafile/sysaux.256.795308565", 
 "+DATA2/test2/datafile/undotbs1.259.795308631", 
 "+DATA2/test2/datafile/users.260.795308657";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +data2 in control file

cataloged datafile copy
datafile copy file name=+DATA2/test2/datafile/sysaux.256.795308565 RECID=1 STAMP=795308819
cataloged datafile copy
datafile copy file name=+DATA2/test2/datafile/undotbs1.259.795308631 RECID=2 STAMP=795308819
cataloged datafile copy
datafile copy file name=+DATA2/test2/datafile/users.260.795308657 RECID=3 STAMP=795308820

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=795308819 file name=+DATA2/test2/datafile/sysaux.256.795308565
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=795308819 file name=+DATA2/test2/datafile/undotbs1.259.795308631
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=795308820 file name=+DATA2/test2/datafile/users.260.795308657

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 29-SEP-2012 23:07:24

No hay comentarios:

Publicar un comentario