Active database Duplication using RMAN 11gR2

Using "Active database Duplication" method Incomplete-recovery "NOT" possible ( SET UNTIL time/sequence/cancel). Because this duplication method only based on  the logs are archived in the target database location . Even content in the online redologs  from the target database. So we no need to take the backup of  target database . but target database must be in archive log mode

 if the database size in TB's with network connection between database enabled. we can use this method.



target database  a.k.a (primary/source database)
auxiliary database a.k.a (destination/clone database)

Target database name: delta
Auxiliary database name:  delclon


Overview:
            
1. Copy the password file from Target database to auxiliary database location
2. Create the pfile from target database for Auxilary database( if target has spfile create pfile from spfile)
3. Configure the linster & tnsname on both target database and auxiliary database 
4. Create the required directory structure in  Auxillary db location (based on pfile )
5. Open the database in mount mode using modified pfile
6. Use rman command, RMAN will perform restore and recover the database.
$rman target sys/oracle@<target_dbname>
 RMAN> connect auxiliary sys/oracle@<auxiliary_dbname>
(or)
rman target sys/oracle@<target_dbname>  auxiliary sys/oracle@<auxiliary_dbname>
 5.RMAN>duplicate target database to <auxiliary_dbname> from active database nofilenamecheck;

After "Active database Duplication"  
1.Auxiliary DB has unique DBID
2.Auxiliary DB has Copy of data files & archived log files from target database 
3.Recreate the new control files for auxiliary database
4.Recreates the online redo log files.
5.Restart the auxiliary instance.
6.Open the database with RESETLOGS.
7. enable the database in archivelog mode

STEP 1: Copy the password file from Target database to auxiliary database location

[oracle@nyc dbs]$ scp orapwdelta oracle@192.168.168.2:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwdelclon
oracle@192.168.168.2's password:
orapwdelta                                                                                 100% 1536     1.5KB/s   00:00   
[oracle@nyc dbs]$


STEP 2: Create the pfile from target database for Auxilary database( if target has spfile create pfile from spfile)

From Target  database location

SQL> conn /as sysdba
Connected.
SQL> show parameter spfile;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0/db_1/dbs/spfiledelta.ora

SQL> create pfile='/tmp/initdelclon.ora' from spfile;
File created.

[oracle@nyc dbs]$ cd /tmp
[oracle@nyc tmp]$ scp initdelclon.ora oracle@192.168.168.2:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
oracle@192.168.168.2's password:
initdelclon.ora                                                                              100% 1478     1.4KB/s   00:00   
[oracle@nyc tmp]$


From Auxiliary database location
 
[oracle@sfo dbs]$ vi initdelclon.ora
delta.__db_cache_size=335544320
delta.__java_pool_size=16777216
delta.__large_pool_size=16777216
delta.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
delta.__pga_aggregate_target=469762048
delta.__sga_target=771751936
delta.__shared_io_pool_size=0
delta.__shared_pool_size=369098752
delta.__streams_pool_size=16777216
*.audit_file_dest='/u01/app/oracle/admin/delta/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/delta//delta/control01.ctl','/u01/app/oracle/flash_recovery_area/delta/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/u01/app/oradata/deccan/','/u01/app/oracle/oradata/delta/delta'
*.db_name='delta'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=deltaXDB)'
*.fal_client='DELTA'
*.fal_server='DECCAN'
*.log_archive_config='dg_config=(delta, deccan)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/delta/archive'
*.log_archive_dest_2='service=deccan valid_for=(online_logfile,primary_role) db_unique_name=deccan'
*.log_archive_dest_state_2='defer'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='/u01/app/oradata/deccan/','/u01/app/oracle/oradata/delta/delta'
*.memory_target=1234173952
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='auto'
*.undo_tablespace='UNDOTBS1'

edited intidelclon.ora file

[oracle@sfo dbs]$ cat initdelclon.ora
*.audit_file_dest='/u01/app/oracle/admin/delclon/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/delclon/delclon/control01.ctl','/u01/app/oracle/flash_recovery_area/delclon/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/u01/app/oracle/oradata/delta/delta','/u01/app/oracle/oradata/delclon/delclon'
*.db_name='delclon'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=delclonXDB)'
*.fal_client='DELCLON'
*.fal_server='DELTA'
*.log_archive_config='dg_config=(delclon, delta)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/delclon/archive'
*.log_archive_dest_2='service=deccan valid_for=(online_logfile,primary_role) db_unique_name=delta'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='/u01/app/oracle/oradata/delta/delta','/u01/app/oracle/oradata/delclon/delclon'
*.memory_target=1234173952
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='auto'
*.undo_tablespace='UNDOTBS1'



STEP 3: Configure the linster & tnsname on both target database and auxiliary database

Listener.ora (following  lines need to added in listerner.ora) (In TARGET db location)

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = delta)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = delta)
    )
  )

[oracle@nyc ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 27-SEP-2012 03:29:50

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                27-SEP-2012 02:38:23
Uptime                    0 days 0 hr. 51 min. 32 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/nyc/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=nyc.saravanan.com)(PORT=1521)))
Services Summary...
Service "delta" has 2 instance(s).
  Instance "delta", status UNKNOWN, has 1 handler(s) for this service...
  Instance "delta", status READY, has 1 handler(s) for this service...
Service "deltaXDB" has 1 instance(s).
  Instance "delta", status READY, has 1 handler(s) for this service...
The command completed successfully


tnsnames.ora(following  lines need to added in tnsnames.ora) (In TARGET db location)

DELCLON =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = sfo.saravanan.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = delclon)
    )
  )

[oracle@nyc ~]$ tnsping delclon
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 26-SEP-2012 22:21:26
Copyright (c) 1997, 2009, Oracle.  All rights reserved.
Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = sfo.saravanan.com)(PORT = 1521))) (CONNECT_DATA = (SID = delcon)))
OK (50 msec)



Listener.ora (following  lines need to added in listerner.ora) (In Auxillary db location)

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = delclon)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = delclon)
    )
  )

[oracle@sfo ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 26-SEP-2012 18:57:48
Copyright (c) 1991, 2009, Oracle.  All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                26-SEP-2012 18:57:33
Uptime                    0 days 0 hr. 0 min. 15 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/sfo/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sfo.saravanan.com)(PORT=1521)))
Services Summary...
Service "delclon" has 1 instance(s).
  Instance "delclon", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully


tnsnames.ora(following  lines need to added in tnsnames.ora (In Auxillary db location)
      
DELTA=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = nyc.saravanan.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = delta)
    )
  )

DELCLON =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = sfo.saravanan.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = delclon)
    )
  )
[oracle@sfo ~]$ tnsping delta
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 26-SEP-2012 19:33:25
Copyright (c) 1997, 2009, Oracle.  All rights reserved.
Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = nyc.saravanan.com)(PORT = 1521))) (CONNECT_DATA = (SID = delta)))
OK (60 msec)

[oracle@sfo ~]$ tnsping delclon
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 26-SEP-2012 22:21:26
Copyright (c) 1997, 2009, Oracle.  All rights reserved.
Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = sfo.saravanan.com)(PORT = 1521))) (CONNECT_DATA = (SID = delclon)))
OK (50 msec)

STEP 4: Create the required directory structure in  Auxillary db location

create the nessary directory structure based on the init file.
mkdir -p /u01/app/oracle/admin/delclon/adump
mkdir -p /u01/app/oracle/oradata/delclon/delclon
mkdir -p /u01/app/oracle/flash_recovery_area/delclon
mkdir -p /u01/app/oracle/oradata/delclon/delclon
mkdir -p /u01/app/oracle/oradata/delclon/archive

STEP 6. Open the database in mount mode using modified pfile

In Auxillary database location add entry in /etc/oratab

[oracle@sfo flash_recovery_area]$ . oraenv
ORACLE_SID = [11gR2_NEW] ? delclon
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle

[oracle@sfo dbs]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 26 22:43:52 2012

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

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1235959808 bytes
Fixed Size                  2212896 bytes
Variable Size             889195488 bytes
Database Buffers          335544320 bytes
Redo Buffers                9007104 bytes
SQL>

Check the network work connection between two servers(Target & Auxillary)

SQL> conn sys/oracle@delta as sysdba;
Connected.
SQL> conn sys/oracle@delclon as sysdba;
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Warning: You are no longer connected to ORACLE.

[NOTE: To resolve above issue oracle blocking new connections so shutdown the Auxiliary database "delclon"  and startup in nomount stage the standby database again will resolve TNS issue iautomatically.]

SQL> shut immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1235959808 bytes
Fixed Size                  2212896 bytes
Variable Size             889195488 bytes
Database Buffers          335544320 bytes
Redo Buffers                9007104 bytes
SQL>


SQL> conn sys/oracle@delta as sysdba;
Connected.
SQL> conn sys/oracle@delclon as sysdba;
Connected.

STEP 6. Use rman command, RMAN will perform restore and recover the database

$rman target sys/oracle@delta

[oracle@sfo ~]$ rman target sys/oracle@delta

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Sep 27 03:28:18 2012

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

connected to target database: DELTA (DBID=3056476564)

 RMAN> connect auxiliary sys/oracle@delclon
.
.
.


RMAN>duplicate target database to clonedb from active database nofilenamecheck;
database opened
Finished Duplicate delclon at 27-SEP-12

Verify the status of auxiliary database status 

SQL> select name,open_mode from v$database;

NAME             OPEN_MODE
---------            ----------
DELCLON       READ WRITE