Tuesday, June 30, 2015

RMAN Duplicate Database using Live/Active Database

RMAN duplicate command is a very useful automated way to create a copy/clone of a database rather than manually creating a clone of a database. Duplicate database can be created on the same server were source database is running or on a different server. Duplicate database can be created using RMAN backup of source database,
or directly from the live database.
Here I will discuss step by step method of creating a duplicate database on the same server where source database is running. Steps of creating a clone on a different server are also same. Moreover, steps are also same whether we are on UNIX based platforms or on Windows.

I performed this duplicate on Oracle 11.2.0.3 running on windows 7. Source database is DEVDB and duplicate database name is DUPDB.

1)
If using a different server for duplicate database, Install same Oracle software (including patches). Also make sure listener is running on the server.

2)
Create password file
If windows, use oradim.exe to create password file and OS level service

C:\Users\salmqure>set oracle_sid=DUPDB
C:\Users\salmqure>oradim -new -sid DUPDB -intpwd oracle
Instance created.

If Linux, use orapwd to create the password file
$export ORACLE_SID=DUPDB
$cd $ORACLE_HOME/dbs
$orapwd file=orapwDUPDB password=oracle

3)
Create initDUPDB.ora file under $ORACLE_HOME/dbs for Linux and %ORACLE_HOME%\database directory for Windows platform. Add following parameters in this file (modify paths to match your OS platform)

*.audit_file_dest='d:\oracle\admin\DUPDB\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='d:\oracle\oradata\DUPDB\control01.ctl','d:\oracle\fast_recovery_area\DUPDB\control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='DUPDB'
*.db_recovery_file_dest='d:\oracle\fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.diagnostic_dest='d:\oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DUPDBXDB)'
*.open_cursors=300
*.pga_aggregate_target=288358400
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.memory_target=13213106176
*.undo_tablespace='UNDOTBS1'
*.db_create_file_dest='d:\oracle\oradata'

4)
Create following directories on duplicate database hot; as specified in the parameters of init file above. Change paths if you are using Linux
d:\oracle\admin\DUPDB\adump
d:\oracle\oradata\DUPDB
d:\oracle\fast_recovery_area\DUPDB

5)
Under your duplicate database home TNSNAMES.ORA, create 2 TNS entries. One will resolve to the source database in order to connect during execution of “duplicate” command, and other will resolve to the duplicate database

DEVDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <SOURCE_HOST_NAME>)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DEVDB)
    )
  )

DUPDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <DUPLICATE_HOST_NAME>)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DUPDB)
    )
  )

6)
On duplicate database host, register duplicate database statically with the listener by adding following lines in listener.ora file

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = DUPDB)
      (ORACLE_HOME = d:\oracle\product\11.2.0\dbhome_1)
      (SID_NAME = DUPDB)
    )
  )

7)
If your source database is running in archived log mode, skip this step and go to next step. Otherwise stop your source database and start in mount mode.

C:\>set oracle_sid=DEVDB
SQL> shutdown immediate
SQL> startup mount

8)
Start duplicate database in nomount mode (using pfile created above)

C:\>set oracle_sid=DUPDB
SQL> startup mount

9)
Start RMAN and connect to target and duplicate (auxiliary) and execute “duplicate command”. You should know SYS user password of source database. Duplicate database password is “oracle” in this case.

C:\Users\salmqure>rman target sys/salman@devdb auxiliary sys/oracle@dupdb

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jul 1 09:46:08 2015

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

connected to target database: DEVDB (DBID=3275747663)
connected to auxiliary database: DUPDB (not mounted)

RMAN> duplicate target database to DUPDB from active database;
D:\Users\salmqure>rman target sys/oracle@DEVDB auxiliary sys/oracle@dupdb

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jul 1 09:53:01 2015

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

connected to target database: DEVDB (DBID=3275747663)
connected to auxiliary database: DUPDB (not mounted)

RMAN> duplicate target database to DUPDB from active database;

Starting Duplicate Db at 01-JUL-15
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=129 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   13161414656 bytes

Fixed Size                     2267584 bytes
Variable Size               2147485248 bytes
Database Buffers           11005853696 bytes
Redo Buffers                   5808128 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''DEVDB'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''DUPDB'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   backup as copy current controlfile auxiliary format  'D:\ORACLE\ORADATA\DUPDB\CONTROL01.CTL';
   restore clone controlfile to  'D:\ORACLE\FAST_RECOVERY_AREA\DUPDB\CONTROL02.CTL' from
 'D:\ORACLE\ORADATA\DUPDB\CONTROL01.CTL';
   alter clone database mount;
}
executing Memory Script

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

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

Oracle instance shut down

Oracle instance started

Total System Global Area   13161414656 bytes

Fixed Size                     2267584 bytes
Variable Size               2147485248 bytes
Database Buffers           11005853696 bytes
Redo Buffers                   5808128 bytes

Starting backup at 01-JUL-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=191 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=D:\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFDEVDB.ORA tag=TAG20150701T095343 RECID=4 STAMP=883907624
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 01-JUL-15

Starting restore at 01-JUL-15
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=63 device type=DISK

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 01-JUL-15

database mounted

contents of Memory Script:
{
   set newname for clone datafile  1 to new;
   set newname for clone datafile  2 to new;
   set newname for clone datafile  3 to new;
   set newname for clone datafile  4 to new;
   backup as copy reuse
   datafile  1 auxiliary format new
   datafile  2 auxiliary format new
   datafile  3 auxiliary format new
   datafile  4 auxiliary format new
   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 01-JUL-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=D:\ORACLE\ORADATA\DEVDB\SYSTEM01.DBF
output file name=D:\ORACLE\ORADATA\DUPDB\DATAFILE\O1_MF_SYSTEM_DATA_D-DEVDB_I-3275747663_TS-SYSTEM_FNO-1_07QAUN1H_.DBF tag=TAG20150701T095353
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=D:\ORACLE\ORADATA\DEVDB\SYSAUX01.DBF
output file name=D:\ORACLE\ORADATA\DUPDB\DATAFILE\O1_MF_SYSAUX_DATA_D-DEVDB_I-3275747663_TS-SYSAUX_FNO-2_08QAUN2A_.DBF tag=TAG20150701T095353
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=D:\ORACLE\ORADATA\DEVDB\UNDOTBS01.DBF
output file name=D:\ORACLE\ORADATA\DUPDB\DATAFILE\O1_MF_UNDOTBS1_DATA_D-DEVDB_I-3275747663_TS-UNDOTBS1_FNO-3_09QAUN33_.DBF tag=TAG20150701T095353
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=D:\ORACLE\ORADATA\DEVDB\USERS01.DBF
output file name=D:\ORACLE\ORADATA\DUPDB\DATAFILE\O1_MF_USERS_DATA_D-DEVDB_I-3275747663_TS-USERS_FNO-4_0AQAUN36_.DBF tag=TAG20150701T095353
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 01-JUL-15

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "D:\ORACLE\ARCHIVE\DEVDB\ARC0000000009_0880976656.0001" auxiliary format
 "D:\ORACLE\FAST_RECOVERY_AREA\DUPDB\ARCHIVELOG\2015_07_01\O1_MF_1_9_%U_.ARC"   ;
   catalog clone recovery area;
   switch clone datafile all;
}
executing Memory Script

Starting backup at 01-JUL-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=9 RECID=2 STAMP=883907688
output file name=D:\ORACLE\FAST_RECOVERY_AREA\DUPDB\ARCHIVELOG\2015_07_01\O1_MF_1_9_ARCH_D-DEVDB_ID-3275747663_S-9_T-1_A-880976656_0BQAUN38_.ARC RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 01-JUL-15

searching for all files in the recovery area

List of Files Unknown to the Database
=====================================
File Name: D:\ORACLE\FAST_RECOVERY_AREA\DUPDB\ARCHIVELOG\2015_07_01\O1_MF_1_9_ARCH_D-DEVDB_ID-3275747663_S-9_T-1_A-880976656_0BQAUN38_.ARC
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: D:\ORACLE\FAST_RECOVERY_AREA\DUPDB\ARCHIVELOG\2015_07_01\O1_MF_1_9_ARCH_D-DEVDB_ID-3275747663_S-9_T-1_A-880976656_0BQAUN38_.ARC

List of files in Recovery Area not managed by the database
==========================================================
File Name: D:\ORACLE\FAST_RECOVERY_AREA\DUPDB\CONTROL02.CTL
  RMAN-07526: Reason: File is not an Oracle Managed File

number of files not managed by recovery area is 1, totaling 9.28MB

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=883907689 file name=D:\ORACLE\ORADATA\DUPDB\DATAFILE\O1_MF_SYSTEM_DATA_D-DEVDB_I-3275747663_TS-SYSTEM_FNO-1_07QAUN1H_.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=5 STAMP=883907689 file name=D:\ORACLE\ORADATA\DUPDB\DATAFILE\O1_MF_SYSAUX_DATA_D-DEVDB_I-3275747663_TS-SYSAUX_FNO-2_08QAUN2A_.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=883907689 file name=D:\ORACLE\ORADATA\DUPDB\DATAFILE\O1_MF_UNDOTBS1_DATA_D-DEVDB_I-3275747663_TS-UNDOTBS1_FNO-3_09QAUN33_.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=883907689 file name=D:\ORACLE\ORADATA\DUPDB\DATAFILE\O1_MF_USERS_DATA_D-DEVDB_I-3275747663_TS-USERS_FNO-4_0AQAUN36_.DBF

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

executing command: SET until clause

Starting recover at 01-JUL-15
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 9 is already on disk as file D:\ORACLE\FAST_RECOVERY_AREA\DUPDB\ARCHIVELOG\2015_07_01\O1_MF_1_9_ARCH_D-DEVDB_ID-3275747663_S-9_T-1_A-880976656_0BQAUN38_.ARC
archived log file name=D:\ORACLE\FAST_RECOVERY_AREA\DUPDB\ARCHIVELOG\2015_07_01\O1_MF_1_9_ARCH_D-DEVDB_ID-3275747663_S-9_T-1_A-880976656_0BQAUN38_.ARC thread=1 sequence=9
media recovery complete, elapsed time: 00:00:01
Finished recover at 01-JUL-15
Oracle instance started

Total System Global Area   13161414656 bytes

Fixed Size                     2267584 bytes
Variable Size               2147485248 bytes
Database Buffers           11005853696 bytes
Redo Buffers                   5808128 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''DUPDB'' 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

sql statement: alter system set  db_name =  ''DUPDB'' 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   13161414656 bytes

Fixed Size                     2267584 bytes
Variable Size               2147485248 bytes
Database Buffers           11005853696 bytes
Redo Buffers                   5808128 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUPDB" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1  SIZE 50 M ,
  GROUP   2  SIZE 50 M ,
  GROUP   3  SIZE 50 M
 DATAFILE
  'D:\ORACLE\ORADATA\DUPDB\DATAFILE\O1_MF_SYSTEM_DATA_D-DEVDB_I-3275747663_TS-SYSTEM_FNO-1_07QAUN1H_.DBF'
 CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
   set newname for clone tempfile  1 to new;
   switch clone tempfile all;
   catalog clone datafilecopy  "D:\ORACLE\ORADATA\DUPDB\DATAFILE\O1_MF_SYSAUX_DATA_D-DEVDB_I-3275747663_TS-SYSAUX_FNO-2_08QAUN2A_.DBF",
 "D:\ORACLE\ORADATA\DUPDB\DATAFILE\O1_MF_UNDOTBS1_DATA_D-DEVDB_I-3275747663_TS-UNDOTBS1_FNO-3_09QAUN33_.DBF",
 "D:\ORACLE\ORADATA\DUPDB\DATAFILE\O1_MF_USERS_DATA_D-DEVDB_I-3275747663_TS-USERS_FNO-4_0AQAUN36_.DBF";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to D:\ORACLE\ORADATA\DUPDB\DATAFILE\O1_MF_TEMP_%U_.TMP in control file

cataloged datafile copy
datafile copy file name=D:\ORACLE\ORADATA\DUPDB\DATAFILE\O1_MF_SYSAUX_DATA_D-DEVDB_I-3275747663_TS-SYSAUX_FNO-2_08QAUN2A_.DBF RECID=1 STAMP=883907722
cataloged datafile copy
datafile copy file name=D:\ORACLE\ORADATA\DUPDB\DATAFILE\O1_MF_UNDOTBS1_DATA_D-DEVDB_I-3275747663_TS-UNDOTBS1_FNO-3_09QAUN33_.DBF RECID=2 STAMP=883907722
cataloged datafile copy
datafile copy file name=D:\ORACLE\ORADATA\DUPDB\DATAFILE\O1_MF_USERS_DATA_D-DEVDB_I-3275747663_TS-USERS_FNO-4_0AQAUN36_.DBF RECID=3 STAMP=883907722

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=883907722 file name=D:\ORACLE\ORADATA\DUPDB\DATAFILE\O1_MF_SYSAUX_DATA_D-DEVDB_I-3275747663_TS-SYSAUX_FNO-2_08QAUN2A_.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=883907722 file name=D:\ORACLE\ORADATA\DUPDB\DATAFILE\O1_MF_UNDOTBS1_DATA_D-DEVDB_I-3275747663_TS-UNDOTBS1_FNO-3_09QAUN33_.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=883907722 file name=D:\ORACLE\ORADATA\DUPDB\DATAFILE\O1_MF_USERS_DATA_D-DEVDB_I-3275747663_TS-USERS_FNO-4_0AQAUN36_.DBF

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

database opened
Finished Duplicate Db at 01-JUL-15

RMAN>


Important Points to be Noted
If we don’t use “*.db_create_file_dest='d:\oracle\oradata'” parameter in the init file, duplicate datafiles would be created under same directory structure as source database and we should make sure that same directory structure exists on the duplicate database host to have duplicate command completed successfully
If duplicate database is being created on the same server as source database and we don’t use “*.db_create_file_dest='d:\oracle\oradata'”, duplicate command will try to overwrite existing datafiles of source database and would fail with errors as follows

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 07/01/2015 10:44:51
RMAN-05501: aborting duplication of target database
RMAN-05001: auxiliary file name D:\ORACLE\ORADATA\DEVDB\USERS01.DBF conflicts with a file used by the target database
RMAN-05001: auxiliary file name D:\ORACLE\ORADATA\DEVDB\UNDOTBS01.DBF conflicts with a file used by the target database
RMAN-05001: auxiliary file name D:\ORACLE\ORADATA\DEVDB\SYSAUX01.DBF conflicts with a file used by the target database
RMAN-05001: auxiliary file name D:\ORACLE\ORADATA\DEVDB\SYSTEM01.DBF conflicts with a file used by the target database

If any of the required directories is not created manually as mentioned in step 4 above, duplicate command would fail with the error messages similar to the following
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 07/01/2015 09:51:09
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 07/01/2015 09:51:09
ORA-17628: Oracle error 19505 returned by remote Oracle server

If we don’t want to use Oracle Managed Files (OMF) for duplicate database and want to specify our own paths and names for the datafiles and redo log files, we can remove “*.db_create_file_dest='d:\oracle\oradata'” parameter from the init file, but our duplicate command should use following syntax to specify the locations and names of the duplicate database datafiles and log files. For SET NEWNAME, we can get (data)file#  from v$database of source database and then mention its path on the duplicate database host.
RMAN> run{
set newname for datafile 1 to 'D:\oracle\oradata\DUPDB\system01.dbf';
set newname for datafile 2 to 'D:\oracle\oradata\DUPDB\sysaux01.dbf';
set newname for datafile 3 to 'Dc:\oracle\oradata\DUPDB\undotbs01.dbf';
set newname for datafile 4 to 'D:\oracle\oradata\DUPDB\users01.dbf';
set newname for tempfile 1 to 'D:\oracle\oradata\DUPDB\temp01.dbf';
duplicate target database to DUPDB from active database
logfile group 1('D:\oracle\oradata\DUPDB\redo01.log') size 100m,
group 2('D:\oracle\oradata\DUPDB\redo02.log') size 100m;
}

No comments:

Post a Comment