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;
   }