Duplicating with rman

The assumption is to create a copy of the database on the same host in order to run 2 independent database instances. It can be done in many ways:

• with RMAN 
• with expdp and standard dump
• with expdp and transportable tablespaces
• with cp command and dbnewid tool - look here

I present here one of the possible ways with the RMAN utility:

1. check if the database is set in ARCHIVELOG

   select log_mode from v$database;
   -- if the answer is NOARCHIVELOG then
   shutdown immediate; 
   startup mount
   alter database archivelog;
   alter database open;
   

2. prepare auxiliary instance
   • create directories for the clone database
     Here I am not sure entirely at the moment how it works. I provided explicite in the parameter file the value for the control_files and am quite certain that have to create the directory myself. In case of datafiles and redologs, if one first sets new names to new, then the RMAN creates directories according to the OMF methodology.

     mkdir -p /oracle/oradata/NEWSID/controlfile
     mkdir -p /oracle/oradata/NEWSID/datafile #optional
     mkdir -p /oracle/oradata/NEWSID/onlinelog #optional
     

   • create spfile for the clone database (if this is not a standby then the only requirement is DB_NAME)

     export ORACLE_SID=NEWSID
     sqlplus / as sysdba
     

     All the OMF settings indicate the same location - it does not make sense to set both db_create_online_log_dest_1 and db_create_online_log_dest_2 to the same value unless one works on some kind of enterprise class storage.

     create pfile='NEWSID.pfile' from spfile;
     

     *.audit_file_dest='/home/oracle/admin/NEWSID/adump'
     *.compatible='11.2.0.3.0'
     *.control_files='/oracle/oradata/NEWSID/controlfile/control01.ctl'
     *.db_block_size=8192
     *.db_create_file_dest='/oracle/oradata/'
     *.db_create_online_log_dest_1='/oracle/oradata/' 
     *.db_create_online_log_dest_2='/oracle/oradata/'
     *.db_domain=example.com
     *.db_file_multiblock_read_count=16
     *.db_files=800
     *.db_name=NEWSID
     *.diagnostic_dest='/oracle'
     *.job_queue_processes=1000
     *.nls_language='POLISH'
     *.nls_territory='POLAND'
     *.open_cursors=300
     *.pga_aggregate_target=512M
     *.processes=1000
     *.remote_login_passwordfile='EXCLUSIVE'
     *.service_names='NEWSID'
     *.sessions=1105
     *.sga_target=800M
     *.undo_management='AUTO'
     *.undo_tablespace='UNDOTBS1'
     

     create spfile from pfile='NEWSID.pfile';
     

   • copy password file

     cp orapwOLDSID orapwNEWSID

   • add static definition of the clone database to listener.ora

     # static definitions
     SID_LIST_LISTENER =
       (SID_LIST =
         ## this part is not necessary
         (SID_DESC =
           (GLOBAL_DBNAME = OLDSID.dev.example.com)
           (ORACLE_HOME = /oracle/product/11.2.0.2)
           (SID_NAME = OLDSID)
         )
         ## this part is mandatory
         (SID_DESC =
           (GLOBAL_DBNAME = NEWSID.dev.example.com)
           (ORACLE_HOME = /oracle/product/11.2.0.2)
           (SID_NAME = NEWSID)
         )
       )
     
     # listener definition prepared for dynamic registration
     LISTENER=
       (DESCRIPTION=
           (ADDRESS=(PROTOCOL=tcp)(HOST=test-dbcluster-01)(PORT=1521)))
     
     

     After such changes in the listener definition it is not enough simply reload. One have to stop and start the listener.
   • run the clone instance

     startup mount

3. run the duplicate command in RMAN

   connect target sys/sys01@OLDSID.example.com
   connect auxiliary sys/sys01@NEWSID.example.com
   run {
     ## this setting sets all the files to indicated directory
     ## and changes file names to %U format
     #SET NEWNAME FOR DATABASE TO '/oracle/oradata/%U';
     ## this setting is compatible with the OMF
     SET NEWNAME FOR DATABASE TO NEW; 
     DUPLICATE TARGET DATABASE to transpl
     FROM ACTIVE DATABASE;
   }
   

Duplicating database manually on the same host

The assumption is to create a copy of the database on the same host in order to run 2 independent database instances. It can be done in many ways:

• with RMAN 
• with expdp and standard dump
• with expdp and transportable tablespaces
• with cp command and dbnewid tool

I present here the last way.

1. --shutdown cleanly a database to be copied
   shutdown immediate; 
   

2. change the database directory name to something different - it is needed in order to preserve original datafiles, when we would change their paths. By default the Oracle RDBMS deletes original data files when they exist under both paths (ie. old one and the new one; we use OMF) - by directory change we prevent this behaviour - for example new directory name would be TEST.ORIG. After whole operation we return back the original name.
3. copy all the datafiles, logfiles and controlfiles to a new directory with cp command

   ## for example 
   cp -r TEST.ORIG TES2
   

4. create new init.ora with new path to the controlfiles, new unique name and other paths with would conflict with   - for example create pfile='inittes2.ora' from spfile
5. startup nomount - fix possible errors
6. mount the database - fix possible errors
7. change the data files paths -

   select 'alter database rename file '''||name||''' to '''
     ||regexp_replace(name, '[old prefix]', '[new prefix]')
     ||''';' ddl1 
   from v$datafile 
   union all
   select 'alter database rename file '''||name||''' to '''
     ||regexp_replace(name, '[old prefix]', '[new prefix]')
     ||''';' ddl1 
   from v$tempfile 
   union all
   select 'alter database rename file '''||member||''' to '''
     ||regexp_replace(member, '[old prefix]', '[new prefix]')
     ||''';' ddl1 
   from v$logfile
   ;
   
   

8. shutdown immediate

9. ## ensure open_cursors are set to bigger number than 
   ## all files of the database
   ## (apparently the nid utility opens them all at once)
   nid target=sys dbname=cd2;
   

10. change parameter file db_name to new name

11. startup mount
    alter database open resetlogs;
    

12. restore old files to the old path and startup the original database

Summarizing there are only 2 things, which one must care about - implicit removal of database files in old location (which must be prevented; not sure if this happens without OMF) and the open_cursors parameter, which must be set higher than the number of database files (not sure but better to count temporary and log files as well).

Duplicating for standby

On the start I have a target database configured with OMF and specified unique db name. On the standby side I created an instance, started it and that's it. Oh, yeah - I copied password file from primary side to be able to connect to standby and configured listener with GLOBAL_DBNAME, which allows for registering a service for use in tnsnames.ora on both hosts. Of course I specified proper entries in tnsnames.ora as well.
There are possible many configurations for this duplication - combinations of a target database, a standby database and a catalog - of course a standby one is a must, but the rest is optional and may be avoided, although it complicates the matter a little. I choose to use additionally to standby database also active target database. This avoids the trouble of applying all the redo logs from last backup.

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE;

First failure: the command failed with ORA-17628: Oracle error 19505 returned by remote Oracle server on primary and ORA-19505: failed to identify file "/oracle/oradata/ZABBIX/controlfile/o1_mf_855ys7j8_.ctl"
ORA-27040: file create error, unable to create file as I did not create path for controlfile (so now mkdir -p /oracle/oradata/ZABBIX/controlfile/o1_mf_855ys7j8_.ctl).

Second start and failure:

RMAN-03002: failure of Duplicate Db command at 11/13/2012 10:14:19
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
ORA-06550: line 1, column 17:
PLS-00201: identifier 'DBMS_RCVCAT.GETDBID' must be declared
ORA-06550: line 1, column 7:

I found at http://blog.csdn.net/tianlesoftware/article/details/6232292 that it is due to not specifying nocatalog when connecting with rman. And that really helped.
Third try and this time I moved much ahead - I was wrong with assuming the online logs will be created according to the db_create_file_dest (so I should set db_create_online_log_dest_n at least to overcome this). However this time duplication is going forward - the data files are created according to the plan.
Because copy is done with backup as copy reuse datafile X auxiliary format new then no useless copying to storage local to primary and all bytes goes directly to the standby files, which is nice

To finish the standby I need now to add standby log files (though this is optional) and set log_archive_dest_state_2 to enable. The managed standby is already run by the duplicate command. One may want to switch this to recovery using current logfile.