Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Tuesday, April 02, 2013

Creating duplicate database using rman backup 11gR2 (Single instnace)

Scenerio:
Duplication required for a single instance database (11gR2) on the same server. OS environment Windows 64bit.




1. Backup source database

C:\Windows\system32>rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Oct 17 01:23:24 2012

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

connected to target database: PROD (DBID=205599179)

Check if controlfile autobackup is not on, configure rman to take controlfile auto backup

RMAN> configure controlfile autobackup on;

using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored


RMAN> backup database plus archivelog;
2. Update Network files
Add entries for both Source (PROD) and clone (HOME) databases in Listener.ora and tnsnames.ora

listener.ora
==============================
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = prod)
(ORACLE_HOME = E:\app\inam\product\11.2.0\dbhome_1)
(SID_NAME =prod)
)
(SID_DESC =
(GLOBAL_DBNAME = homedb)
(ORACLE_HOME = E:\app\
inam\product\11.2.0\dbhome_1)
(SID_NAME =homedb)
)
)


tnsnames.ora
========================
PROD =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=
myhost)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=PROD)
(INSTANCE_NAME=PROD)
)
)
homedb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
myhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = homedb)
)
)


3. Create parameter file
create a dummy parameter file to open duplicate/clone (HOMEDB) database in nomount state. Add paths for controlfiles of clone database and source and destination paths information for datafiles and redologs.

initHOMEDB.ora
========================
db_name = HOMEDB
db_block_size = 8192
compatible = 11.2.0.0.0
remote_login_passwordfile = exclusive
control_files = 'E:\app\inam\oradata\HOMEDB\CONTROL01.CTL',
'E:\app\inam\flash_recovery_area\HOMEDB\CONTROL02.CTL'
db_file_name_convert = 'E:\APP\INAM\ORADATA\PROD',
'E:\APP\INAM\ORADATA\HOMEDB'
log_file_name_convert = 'E:\APP\INAM\ORADATA\PROD',
'E:\APP\INAM\ORADATA\HOMEDB'


4.Create Windows service for duplicate database
C:\Windows\system32>oradim -new -sid homedb
Instance created.

5. Connect to clone DB and start it in NOMOUNT state

C:\Windows\system32>set oracle_sid=homedb

C:\Windows\system32>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 17 01:02:05 2012

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

Connected to an idle instance.


SQL> startup nomount pfile =E:\app\inam\oradata\HOMEDB\initHOMEDB.ora
ORACLE instance started.


Total System Global Area 217157632 bytes
Fixed Size 2174320 bytes
Variable Size 159384208 bytes
Database Buffers 50331648 bytes
Redo Buffers 5267456 bytes
SQL>

6.  Start RMAN and connect to both source (PROD) and clone DB (HOMEDB)
C:\Users\inam>rman
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Oct 17 01:45:51 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target
connected to target database: PROD (DBID=205599179)
RMAN> connect auxiliary sys/oracle@homedb
connected to auxiliary database: HOMEDB (not mounted)


7. Run DUPLICATE command

RMAN> duplicate target database to homedb;

Your job finished here, let rman do everything from here onward It will start cloning process and complete it automatically. below is rman output generated during this process just for information.


Starting Duplicate Db at 17-OCT-12
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=5 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 2174320 bytes
Variable Size 159384208 bytes
Database Buffers 50331648 bytes
Redo Buffers 5267456 bytes

contents of Memory Script:
{
sql clone "alter system set db_name =
''PROD'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''HOMEDB'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile;
alter clone database mount;
}
executing Memory Script

sql statement: alter system set db_name = ''PROD'' comment= ''Modified by RMAN duplicate'' scope=s
pfile

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

Oracle instance shut down

Oracle instance started

Total System Global Area 217157632 bytes

Fixed Size 2174320 bytes
Variable Size 159384208 bytes
Database Buffers 50331648 bytes
Redo Buffers 5267456 bytes

Starting restore at 17-OCT-12
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=130 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece E:\APP\INAM\FLASH_RECOVERY_AREA\PROD\AUTOBACKUP\
2012_10_17\O1_MF_S_796896748_87WHKXJC_.BKP
channel ORA_AUX_DISK_1: piece handle=E:\APP\INAM\FLASH_RECOVERY_AREA\PROD\AUTOBACKUP\2012_10_17\O1
_MF_S_796896748_87WHKXJC_.BKP tag=TAG20121017T081228
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=E:\APP\INAM\ORADATA\HOMEDB\CONTROL01.CTL
output file name=E:\APP\INAM\FLASH_RECOVERY_AREA\HOMEDB\CONTROL02.CTL
Finished restore at 17-OCT-12

database mounted

contents of Memory Script:
{
set until scn 1941837;
set newname for datafile 1 to
"E:\APP\INAM\ORADATA\HOMEDB\SYSTEM01.DBF";
set newname for datafile 2 to
"E:\APP\INAM\ORADATA\HOMEDB\SYSAUX01.DBF";
set newname for datafile 3 to
"E:\APP\INAM\ORADATA\HOMEDB\UNDOTBS01.DBF";
set newname for datafile 4 to
"E:\APP\INAM\ORADATA\HOMEDB\USERS01.DBF";
restore
clone database
;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 17-OCT-12
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to E:\APP\INAM\ORADATA\HOMEDB\SYSTEM01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00002 to E:\APP\INAM\ORADATA\HOMEDB\SYSAUX01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00003 to E:\APP\INAM\ORADATA\HOMEDB\UNDOTBS01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00004 to E:\APP\INAM\ORADATA\HOMEDB\USERS01.DBF
channel ORA_AUX_DISK_1: reading from backup piece E:\APP\INAM\FLASH_RECOVERY_AREA\PROD\BACKUPSET\2
012_10_17\O1_MF_NNNDF_TAG20121017T081141_87WHJFQW_.BKP
channel ORA_AUX_DISK_1: piece handle=E:\APP\INAM\FLASH_RECOVERY_AREA\PROD\BACKUPSET\2012_10_17\O1_
MF_NNNDF_TAG20121017T081141_87WHJFQW_.BKP tag=TAG20121017T081141
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 17-OCT-12

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

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=796897550 file name=E:\APP\INAM\ORADATA\HOMEDB\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=796897550 file name=E:\APP\INAM\ORADATA\HOMEDB\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=796897550 file name=E:\APP\INAM\ORADATA\HOMEDB\UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=796897550 file name=E:\APP\INAM\ORADATA\HOMEDB\USERS01.DBF

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

executing command: SET until clause

Starting recover at 17-OCT-12
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 77 is already on disk as file E:\APP\INAM\FLASH_RECOVERY_A
REA\PROD\ARCHIVELOG\2012_10_17\O1_MF_1_77_87WHKTOW_.ARC
archived log file name=E:\APP\INAM\FLASH_RECOVERY_AREA\PROD\ARCHIVELOG\2012_10_17\O1_MF_1_77_87WHK
TOW_.ARC thread=1 sequence=77
media recovery complete, elapsed time: 00:00:01
Finished recover at 17-OCT-12

contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set db_name =
''HOMEDB'' 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 2174320 bytes
Variable Size 159384208 bytes
Database Buffers 50331648 bytes
Redo Buffers 5267456 bytes

sql statement: alter system set db_name = ''HOMEDB'' comment= ''Reset to original value by RMAN'' s
cope=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 2174320 bytes
Variable Size 159384208 bytes
Database Buffers 50331648 bytes
Redo Buffers 5267456 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "HOMEDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( 'E:\APP\INAM\ORADATA\HOMEDB\REDO01.LOG' ) SIZE 50 M REUSE,
GROUP 2 ( 'E:\APP\INAM\ORADATA\HOMEDB\REDO02.LOG' ) SIZE 50 M REUSE,
GROUP 3 ( 'E:\APP\INAM\ORADATA\HOMEDB\REDO03.LOG' ) SIZE 50 M REUSE
DATAFILE
'E:\APP\INAM\ORADATA\HOMEDB\SYSTEM01.DBF'
CHARACTER SET AR8MSWIN1256


contents of Memory Script:
{
set newname for tempfile 1 to
"E:\APP\INAM\ORADATA\HOMEDB\TEMP01.DBF";
switch clone tempfile all;
catalog clone datafilecopy "E:\APP\INAM\ORADATA\HOMEDB\SYSAUX01.DBF",
"E:\APP\INAM\ORADATA\HOMEDB\UNDOTBS01.DBF",
"E:\APP\INAM\ORADATA\HOMEDB\USERS01.DBF";
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to E:\APP\INAM\ORADATA\HOMEDB\TEMP01.DBF in control file

cataloged datafile copy
datafile copy file name=E:\APP\INAM\ORADATA\HOMEDB\SYSAUX01.DBF RECID=1 STAMP=796897589
cataloged datafile copy
datafile copy file name=E:\APP\INAM\ORADATA\HOMEDB\UNDOTBS01.DBF RECID=2 STAMP=796897589
cataloged datafile copy
datafile copy file name=E:\APP\INAM\ORADATA\HOMEDB\USERS01.DBF RECID=3 STAMP=796897589

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=796897589 file name=E:\APP\INAM\ORADATA\HOMEDB\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=796897589 file name=E:\APP\INAM\ORADATA\HOMEDB\UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=796897589 file name=E:\APP\INAM\ORADATA\HOMEDB\USERS01.DBF

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

database opened
Finished Duplicate Db at 17-OCT-12

RMAN>

No comments: