RMAN 'Duplicate Database' Feature in 11G [ID 452868.1]

RMAN 'Duplicate Database' Feature in 11G [ID 452868.1]
	Modified 14-MAY-2010Type BULLETINStatus PUBLISHED

In this Document
Purpose
Scope and Application
RMAN 'Duplicate Database' Feature in 11G
Basic Steps to ACTIVE database duplication :
References

Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.6 and later[Release: 11.1 and later ]
Information in this document applies to any platform.

Purpose

Thescope of thisbulletin is todiscuss the different typeof RMAN 'duplicate database' featurein Oracle 11G.

This note is only applicable for oracle 11g only.

Scope and Application

This note is intended for DBAs and Support Personnel.

RMAN 'Duplicate Database' Feature in 11G

You can create a duplicate database using the RMAN duplicate command. The duplicate database has a different DBID from the source database and functions entirely independently.Starting from 11g you can do duplicate database in 2 ways.

1. Active database duplication
2. Backup-based duplication

Active database duplication copies the live target database over the network to the auxiliary destination and then creates the duplicate database.Only difference is that you don't need to have the pre-existing RMAN backups and copies.The duplicationworkis performed by an auxiliary channel.This channelcorresponds to a server session on the auxiliary instance on theauxiliary host.

As part of the duplicating operation, RMAN automates the following steps:

1. Creates a control file for the duplicate database
2. Restarts the auxiliary instance and mounts the duplicate control file
3. Creates the duplicate datafiles and recovers them with incremental backups and archived redo logs.
4. Opens the duplicate database with the RESETLOGS option

For the active database duplication, RMAN does one extra step .i.e. copy the target database datafiles over the network to the auxiliary instance

A RAC TARGET database can be duplicated as well. The procedure is the same as below.If the auxiliary instance needs to be a RAC-database as well, than start the duplicate procedure for to a single instance and convert the auxiliary to RAC after the duplicate has succeeded.

Scope of this note is restricted to Active database duplication . For the Backup-base duplication refer Note 259694.1 Oracle10G RMAN Database Duplication.

Basic Steps to ACTIVE database duplication :

1. Prepairing the auxiliary instance :

1.1 Creating initialization Parameter file for the Auxiliary instance

If you are using SPFILE then only parameter required for the duplicate database is DB_NAME . Rest other parameters can be set in the DUPLICATE command itself.If you are not using the SPFILE technique, then you need to set initialization parameters to set in the initialization parameter file. Required parameters are :

DB_NAME
CONTROL_FILES
DB_BLOCK_SIZE
DB_FILE_NAME_CONVERT
LOG_FILE_NAME_CONVERT
DB_RECOVERY_FILE_DEST

In thisbulletinfor simplicity we arespecifying all the required parameters in thepfile

1.2 Create an Oracle Password File for the Auxiliary Instance

Password file is must for the Active database duplication.A password file is not required for backup-based duplication. For Active database duplication it connects directly to the auxiliary instance using the password file with the same SYSDBA password as target database. In case you are using password file make sure to havesame SYSDBA password as thetarget database.Also you can specify the PASSWORD FILE option on the DUPLICATE command.In this case, RMAN copies the source database password file to the destination host and overwrites any existing password file for the auxiliary instance.

In this bulletin we are using password file option in the duplicate command .

1.3 Establish Oracle Net Connectivity to the Auxiliary Instance

Auxiliary instance must be available through Oracle Net if you are duplicating from anACTIVE database.

1.4 Start the Auxiliary instance from Sqlplus

Use SQL*Plus to connect to the auxiliary instance and start it in NOMOUNT mode.

STEP S

In Auxiliary server :

1. Create theparameter file. Look at the example :

initTEST.ora
------------
DB_NAME=TEST
diagnostic_dest='E:/oracle'
DB_FILE_name_CONVERT=('I:/app/apadhi/oradata/amar','E:/oracle/oradata/test')
LOG_FILE_NAME_CONVERT=( 'I:/app/apadhi/oradata/amar','E:/oracle/oradata/test')
SGA_TARGET=262144000
CONTROL_FILES='E:/oracle/oradata/TEST/control01.dbf'
COMPATIBLE= 11.1.0.0.0

2. Create the database service ( only for windows ) and password file . Look at the example :

% set ORACLE_SID=TEST
% set ORACLE_HOME=E:/oracle/product/11.1.0/db_1
% oradim -NEW -SID TEST
% orapwd FILE=E:/oracle/product/11.1.0/db_1/database/PWDTEST.ora PASSWORD=sys

For unix/Linux no need to create the service. Rest other steps are same.

3. Start the Auxiliary instance . Look at the example :

% sqlplus/assysdba

SQL*Plus:Release11.1.0.6.0-ProductiononWedAug120:33:302007

Copyright(c)1982,2007,Oracle.Allrightsreserved.

Connectedtoanidleinstance.

SQL>startupNOMOUNTpfile=E:/oracle/product/11.1.0/db_1/database/initTEST.ora
ORACLEinstancestarted.

TotalSystemGlobalArea150667264bytes
FixedSize1331732bytes
VariableSize92278252bytes
DatabaseBuffers50331648bytes
RedoBuffers6725632bytes
SQL>exit

4. Create the necessary oracle NET connectivity in the listener.ora and the tnsnames.ora file. Look at the example :

Listener.ora
------------
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=TEST)
(ORACLE_HOME=E:/oracle/product/11.1.0/db_1)
(SID_NAME=TEST)
)
)


LISTENER=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=apadhi-idc)(PORT=1521))
)

tnsname.ora
-----------
## For the Auxiliary database TEST ##
TEST=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=apadhi-idc)(PORT=1521))
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=TEST)
)
)

## For the target database AMAR ##
AMAR=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=152.69.210.76)(PORT=1521))
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=AMAR)
)
)

## Similary add entry for the catalog database ( Optional)

Confirm the connection to both the target and the auxiliary instance using sqlplus. In this example we are creating the duplicate database from the Auxiliary server.

2. Start RMAN and Connect to the Database Instances

Start RMAN and connect to the source database as TARGET, the duplicate database instance as AUXILIARY, and, if applicable, the recovery catalog database.You can start the RMAN client on any host so long as it can connect to all of the database instances. If the auxiliary instance requires a text-based initialization parameter file, then this file must exist on the same host that runs the RMAN client application.

.In this bulletin we are doing duplicate database from the auxiliary server.Look at the example :

% rman

Recovery Manager: Release 11.1.0.6.0 - Production on Wed Aug 1 21:06:49 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.

RMAN> connect TARGETsys / sys @ AMAR; ## Target database ##

connected to target database: AMAR (DBID=1450838187)

RMAN> connect AUXILIARYsys /sys @ TEST ; ## Auxiliary database ##

connected to auxiliary database: TEST (not mounted)

RMAN> connect CATALOGrman / rman @ CAT; ## Catalog database .Optional ##

connected to recovery catalog database

3. Run the DUPLICATE database command :

The simplest case is to use active database duplication to duplicate the database to a different host and use the differentdirectory structure.Look at the example :

This example assumes

This example assumes the following:

* Using a recovery catalog.
* The target database is on host1 and contains4 datafiles.
* Duplicate the target to database TEST on thedifferent host having different file structure.
* Tablespace USERS intarget is read-only tablespace.
*Running duplicate databasefrom the Auxiliary site.

RMAN>DUPLICATETARGETDATABASE
TO'TEST'
FROMACTIVEDATABASE
DB_FILE_NAME_CONVERT'I:/app/apadhi/oradata/amar','E:/oracle/oradata/test';

And this is what is going on:

StartingDuplicateDbat02-AUG-07
usingtargetdatabasecontrolfileinsteadofrecoverycatalog
allocatedchannel:ORA_AUX_DISK_1
channelORA_AUX_DISK_1:SID=97devicetype=DISK

contentsofMemoryScript:
{
setnewnamefordatafile1to
"E:/ORACLE/ORADATA/TEST/SYSTEM01.DBF";
setnewnamefordatafile2to
"E:/ORACLE/ORADATA/TEST/SYSAUX01.DBF";
setnewnamefordatafile3to
"E:/ORACLE/ORADATA/TEST/UNDOTBS01.DBF";
setnewnamefordatafile4to
"E:/ORACLE/ORADATA/TEST/USERS01.DBF";
backupascopyreuse
datafile1auxiliaryformat
"E:/ORACLE/ORADATA/TEST/SYSTEM01.DBF"datafile
2auxiliaryformat
"E:/ORACLE/ORADATA/TEST/SYSAUX01.DBF"datafile
3auxiliaryformat
"E:/ORACLE/ORADATA/TEST/UNDOTBS01.DBF"datafile
4auxiliaryformat
"E:/ORACLE/ORADATA/TEST/USERS01.DBF";
sql'altersystemarchivelogcurrent';
}
executingMemoryScript
executingcommand:SETNEWNAME
executingcommand:SETNEWNAME
executingcommand:SETNEWNAME
executingcommand:SETNEWNAME

Startingbackupat02-AUG-07
allocatedchannel:ORA_DISK_1
channelORA_DISK_1:SID=123devicetype=DISK
channelORA_DISK_1:startingdatafilecopy
inputdatafilefilenumber=00001name=I:/APP/APADHI/ORADATA/AMAR/SYSTEM01.DBF
outputfilename=E:/ORACLE/ORADATA/TEST/SYSTEM01.DBFtag=TAG20070802T114254RECID=0STAMP=0
channelORA_DISK_1:datafilecopycomplete,elapsedtime:00:08:22
channelORA_DISK_1:startingdatafilecopy
inputdatafilefilenumber=00002name=I:/APP/APADHI/ORADATA/AMAR/SYSAUX01.DBF
outputfilename=E:/ORACLE/ORADATA/TEST/SYSAUX01.DBFtag=TAG20070802T114254RECID=0STAMP=0
channelORA_DISK_1:datafilecopycomplete,elapsedtime:00:05:59
channelORA_DISK_1:startingdatafilecopy
inputdatafilefilenumber=00003name=I:/APP/APADHI/ORADATA/AMAR/UNDOTBS01.DBF
outputfilename=E:/ORACLE/ORADATA/TEST/UNDOTBS01.DBFtag=TAG20070802T114254RECID=0STAMP=0
channelORA_DISK_1:datafilecopycomplete,elapsedtime:00:02:57
channelORA_DISK_1:startingdatafilecopy
inputdatafilefilenumber=00004name=I:/APP/APADHI/ORADATA/AMAR/USERS01.DBF
outputfilename=E:/ORACLE/ORADATA/TEST/USERS01.DBFtag=TAG20070802T114254RECID=0STAMP=0
channelORA_DISK_1:datafilecopycomplete,elapsedtime:00:00:15
Finishedbackupat02-AUG-07

sqlstatement:altersystemarchivelogcurrent
sqlstatement:CREATECONTROLFILEREUSESETDATABASE"TEST"RESETLOGSARCHIVELOG
MAXLOGFILES16
MAXLOGMEMBERS3
MAXDATAFILES100
MAXINSTANCES8
MAXLOGHISTORY292
LOGFILE
GROUP1('E:/ORACLE/ORADATA/TEST/REDO01.LOG')SIZE50MREUSE,
GROUP2('E:/ORACLE/ORADATA/TEST/REDO02.LOG')SIZE50MREUSE,
GROUP3('E:/ORACLE/ORADATA/TEST/REDO03.LOG')SIZE50MREUSE
DATAFILE
'E:/ORACLE/ORADATA/TEST/SYSTEM01.DBF'
CHARACTERSETAL32UTF8

contentsofMemoryScript:
{
backupascopyreuse
archiveloglike"I:/APP/APADHI/PRODUCT/11.1.0/DB_1/RDBMS/ARC00042_0629061547.001"auxiliaryformat
"E:/ORACLE/PRODUCT/11.1.0/DB_1/RDBMS/ARC00042_0629061547.001"archiveloglike
"I:/APP/APADHI/FLASH_RECOVERY_AREA/AMAR/ARCHIVELOG/2007_08_02/O1_MF_1_42_3C2YJNP7_.ARC"auxiliaryformat
"E:/ORACLE/PRODUCT/11.1.0/DB_1/RDBMS/ARC00042_0629061547.001";
catalogclonearchivelog"E:/ORACLE/PRODUCT/11.1.0/DB_1/RDBMS/ARC00042_0629061547.001";
catalogclonearchivelog"E:/ORACLE/PRODUCT/11.1.0/DB_1/RDBMS/ARC00042_0629061547.001";
switchclonedatafileall;
}
executingMemoryScript

Startingbackupat02-AUG-07
usingchannelORA_DISK_1
channelORA_DISK_1:startingarchivedlogcopy
inputarchivedlogthread=1sequence=42RECID=35STAMP=629553646
outputfilename=E:/ORACLE/PRODUCT/11.1.0/DB_1/RDBMS/ARC00042_0629061547.001RECID=0STAMP=0
channelORA_DISK_1:archivedlogcopycomplete,elapsedtime:00:00:01
channelORA_DISK_1:startingarchivedlogcopy
inputarchivedlogthread=1sequence=42RECID=36STAMP=629553646
outputfilename=E:/ORACLE/PRODUCT/11.1.0/DB_1/RDBMS/ARC00042_0629061547.001RECID=0STAMP=0
channelORA_DISK_1:archivedlogcopycomplete,elapsedtime:00:00:01
Finishedbackupat02-AUG-07

catalogedarchivedlog
archivedlogfilename=E:/ORACLE/PRODUCT/11.1.0/DB_1/RDBMS/ARC00042_0629061547.001RECID=1STAMP=629553800

catalogedarchivedlog
archivedlogfilename=E:/ORACLE/PRODUCT/11.1.0/DB_1/RDBMS/ARC00042_0629061547.001RECID=2STAMP=629553800

datafile2switchedtodatafilecopy
inputdatafilecopyRECID=1STAMP=629553800filename=E:/ORACLE/ORADATA/TEST/SYSAUX01.DBF
datafile3switchedtodatafilecopy
inputdatafilecopyRECID=2STAMP=629553801filename=E:/ORACLE/ORADATA/TEST/UNDOTBS01.DBF

contentsofMemoryScript:
{
setuntilscn833606;
recover
clonedatabase
deletearchivelog
;
}
executingMemoryScript

executingcommand:SETuntilclause

Startingrecoverat02-AUG-07
usingchannelORA_AUX_DISK_1
datafile4notprocessedbecausefileisread-only

startingmediarecovery

archivedlogforthread1withsequence42isalreadyondiskasfileE:/ORACLE/PRODUCT/11.1.0/DB_1/RDBMS/ARC00042_0629061547.001
archivedlogfilename=E:/ORACLE/PRODUCT/11.1.0/DB_1/RDBMS/ARC00042_0629061547.001thread=1sequence=42
mediarecoverycomplete,elapsedtime:00:00:01
Finishedrecoverat02-AUG-07

contentsofMemoryScript:
{
shutdowncloneimmediate;
startupclonenomount;
}
executingMemoryScript

databasedismounted
Oracleinstanceshutdown

connectedtoauxiliarydatabase(notstarted)
Oracleinstancestarted

TotalSystemGlobalArea263639040bytes

FixedSize1332544bytes
VariableSize83888832bytes
DatabaseBuffers171966464bytes
RedoBuffers6451200bytes
sqlstatement:CREATECONTROLFILEREUSESETDATABASE"TEST"RESETLOGSARCHIVELOG
MAXLOGFILES16
MAXLOGMEMBERS3
MAXDATAFILES100
MAXINSTANCES8
MAXLOGHISTORY292
LOGFILE
GROUP1('E:/ORACLE/ORADATA/TEST/REDO01.LOG')SIZE50MREUSE,
GROUP2('E:/ORACLE/ORADATA/TEST/REDO02.LOG')SIZE50MREUSE,
GROUP3('E:/ORACLE/ORADATA/TEST/REDO03.LOG')SIZE50MREUSE
DATAFILE
'E:/ORACLE/ORADATA/TEST/SYSTEM01.DBF'
CHARACTERSETAL32UTF8


contentsofMemoryScript:
{
setnewnamefortempfile1to
"E:/ORACLE/ORADATA/TEST/TEMP01.DBF";
switchclonetempfileall;
catalogclonedatafilecopy"E:/ORACLE/ORADATA/TEST/SYSAUX01.DBF";
catalogclonedatafilecopy"E:/ORACLE/ORADATA/TEST/UNDOTBS01.DBF";
switchclonedatafileall;
}
executingMemoryScript

executingcommand:SETNEWNAME

renamedtempfile1toE:/ORACLE/ORADATA/TEST/TEMP01.DBFincontrolfile

catalogeddatafilecopy
datafilecopyfilename=E:/ORACLE/ORADATA/TEST/SYSAUX01.DBFRECID=1STAMP=629553825

catalogeddatafilecopy
datafilecopyfilename=E:/ORACLE/ORADATA/TEST/UNDOTBS01.DBFRECID=2STAMP=629553826

datafile2switchedtodatafilecopy
inputdatafilecopyRECID=1STAMP=629553825filename=E:/ORACLE/ORADATA/TEST/SYSAUX01.DBF
datafile3switchedtodatafilecopy
inputdatafilecopyRECID=2STAMP=629553826filename=E:/ORACLE/ORADATA/TEST/UNDOTBS01.DBF

contentsofMemoryScript:
{
Alterclonedatabaseopenresetlogs;
}
executingMemoryScript

databaseopened

contentsofMemoryScript:
{
catalogclonedatafilecopy"E:/ORACLE/ORADATA/TEST/USERS01.DBF";
switchclonedatafile4todatafilecopy
"E:/ORACLE/ORADATA/TEST/USERS01.DBF";
#onlinethereadonlytablespace
sqlclone"altertablespaceUSERSonline";
}
executingMemoryScript

catalogeddatafilecopy
datafilecopyfilename=E:/ORACLE/ORADATA/TEST/USERS01.DBFRECID=3STAMP=629553870

datafile4switchedtodatafilecopy
inputdatafilecopyRECID=3STAMP=629553870filename=E:/ORACLE/ORADATA/TEST/USERS01.DBF

sqlstatement:altertablespaceUSERSonline
FinishedDuplicateDbat02-AUG-07

References

Chapter 23 Backup and Recovery User's Guide ( 11g Release 1 (11.1))

------------------------------------------------------------------------------

Blog： http://blog.csdn.net/tianlesoftware

网上资源： http://tianlesoftware.download.csdn.net

相关视频：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx

DBA1 群：62697716(满); DBA2 群：62697977(满)

DBA3 群：62697850 DBA 超级群：63306533;

聊天 群：40132017

--加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请