There is a decent share of blogs and articles about database duplication but I thought its worth to give my share too. Recently I did quite simple database copy for testing purposes and I ran into a few bumps so I thought it might be interesting.
Goal was, as usual, to create a duplicate database with different SID. In my case the problem was little more complex since source database was RAC and we wanted the new one to be also. As of now only way to do that is to copy you database as single and then convert it to RAC. But we will not go into that today.
Create PFILE (initnewSID.ora) for your auxiliary database
Important parameter are:
db_name – Axiliary database SID
db_block_size – Database block size
db_create_file_dest – File destination for OMF
db_file_name_convert – Conversion of location for files
log_file_name_convert – Conversion of location for redlogs
Mine looked like this:
db_name=newSID
db_block_size=8192
db_create_file_dest=+DATA3
db_file_name_convert=(+DATA,+DATA3)
log_file_name_convert=(+DATA,+DATA3)
compatible='11.2.0.3'
db_block_size=8192
db_create_file_dest=+DATA3
db_file_name_convert=(+DATA,+DATA3)
log_file_name_convert=(+DATA,+DATA3)
compatible='11.2.0.3'
You can get RMAN-06136: ORACLE error from auxiliary database: ORA-00201: control file version 11.2.0.3.0 incompatible with ORACLE version 11.2.0.0.0 error, if you are using compatible parameter in your target database. Then you’ll have to add it into your auxiliary PFILE
Create password file
$ orapwd file=orapwnewSID password=MyPassworrd entries=20
Start auxiliary database in nomount
$ sqlplus / as sysdba
SQL> startup nomount
SQL> startup nomount
Start target database in mount
$ sqlplus / as sysdba
SQL> startup mount;
SQL> startup mount;
Now, before you move forward, I do suggest you edit tnsnames.ora and listener.ora to register your auxiliary database
Example:
cd /opt/oracle/product/11.2.0/dbhome_4/network/admin
vi tnsnames.ora
newSID =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = newSID.mydomain.com)
)
)
vi listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = newSID.mydomain.com)
(SID_NAME = SCVON)
(ORACLE_HOME = /opt/oracle/product/11.2.0/dbhome_4)
)
)
$ lsnrctl reload
vi tnsnames.ora
newSID =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = newSID.mydomain.com)
)
)
vi listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = newSID.mydomain.com)
(SID_NAME = SCVON)
(ORACLE_HOME = /opt/oracle/product/11.2.0/dbhome_4)
)
)
$ lsnrctl reload
Run rman and connect to both target and auxiliary database
$ rman nocatalog
RMAN> connect target sys@oldSID
RMAN> connect auxiliary sys@newSID
RMAN> connect target sys@oldSID
RMAN> connect auxiliary sys@newSID
Start duplication
duplicate target database to newSID from active database;
If you get error like ORA-01103: database name 'oldSID' in control file is not 'newSID', when starting new database, just check your PFILE, if it did not get messed up by rman. If so, just correct the values in PFILE and try to restart your new database.
