RMAN Duplicate database from Active database with ASM Migration

Oracle 11g introduced the new feature to Duplicate the database from active database. Duplicating from an active database doesn’t require any RMAN backup to be taken from the source database. It directly reads from active database. But this might cause negative performance impact for source database while duplicating the database. It could also cause network traffic between source and target database.  You can take this approach if your environment is accepting these risks. The RDBMS software should be installed on the auxiliary database server and the DB version should be same as target database server.

High Level steps :

1. Create all the directories in auxiliary server
2. Copy password file  & pfile from target server to auxiliary server
3. Start the instance(with nomount) in auxiliary instance
4. Check the connectivity between source and target
5. Duplicate the database in auxiliary instance.
6. create the spfile in ASM disk
7. Database verification and Validation

Target  DB info :

Step1  Create the necessary folders in auxiliary database.  

Here i  am migrating the database from file system to ASM disk. This case, we don't need to create directory in the file system. I created the directory in ASM disk.

[oracle@RACTEST1 ~]$ asmcmd
ASMCMD> cd +DATA
ASMCMD> mkdir USBEN

Step2 Copy the password file and parameter file from target database server to auxiliary database server. Add entry in /etc/oratab for usben database.

Modify  the parameter file in auxiliary database according to the environment. Here is the modified pfile in auxiliary database.

initusben.ora

usben.__data_transfer_cache_size=0 usben.__db_cache_size=536870912 usben.__java_pool_size=16777216 usben.__large_pool_size=150994944 usben.__oracle_base='/ora/app/oracle' usben.__pga_aggregate_target=671088640 usben.__sga_target=989855744 usben.__shared_io_pool_size=50331648 usben.__shared_pool_size=218103808 usben.__streams_pool_size=0 *.compatible='12.1.0.2.0' *.control_files='+DATA','+DATA' *.db_block_size=8192 *.db_domain='localdomain' *.db_name='usben' *.db_create_file_dest='+DATA' *.db_recovery_file_dest='+DATA' *.db_recovery_file_dest_size=4560m *.diagnostic_dest='/ora/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=usbenXDB)' *.log_archive_dest_1='location=/backup/usben/arch' *.log_archive_dest_state_1='enable' *.open_cursors=300 *.processes=300 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1'

Step 3  Start the instance using the above parameter with no mount mode:

Step 4  Check the connectivity between source and target.  

Add entry in /etc/oratab for auxiliary  database. 

Adding the below entries in auxiliary database. DO NOT MAKE any changes in target database.

tnsnames.ora 

usdup =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = usben.localdomain)
    )
  )

usben =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = usbenhost.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = usben.localdomain)
    )
  )

listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = usben.localdomain)
      (ORACLE_HOME = /ora/app/oracle/product/12.1.0.1/db_1)
      (SID_NAME = usben)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = RACTEST1.localdomain)(PORT = 1522))
    )

  )

Start the listener in auxiliary database server.

[oracle@RACTEST1 dbs]$ lsnrctl LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 17-FEB-2016 17:32:52 Copyright (c) 1991, 2014, Oracle.  All rights reserved. Welcome to LSNRCTL, type "help" for information. LSNRCTL> start Starting /ora/app/oracle/product/12.1.0.1/db_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 12.1.0.2.0 - Production System parameter file is /ora/app/oracle/product/12.1.0.1/db_1/network/admin/listener.ora Log messages written to /ora/app/oracle/diag/tnslsnr/RACTEST1/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=RACTEST1.localdomain)(PORT=1522))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=RACTEST1.localdomain)(PORT=1522))) STATUS of the LISTENER ------------------------ Alias                     LISTENER Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date                17-FEB-2016 17:32:54 Uptime                    0 days 0 hr. 0 min. 0 sec Trace Level               off Security                  ON: Local OS Authentication SNMP                      OFF Listener Parameter File   /ora/app/oracle/product/12.1.0.1/db_1/network/admin/listener.ora Listener Log File         /ora/app/oracle/diag/tnslsnr/RACTEST1/listener/alert/log.xml Listening Endpoints Summary...   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=RACTEST1.localdomain)(PORT=1522))) Services Summary... Service "usben.localdomain" has 1 instance(s).   Instance "usben", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully LSNRCTL>

Now check the connectivity between auxiliary and target database server.

restore_connectivity.sh

Here is the log file content for above shell script.

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Feb 17 09:05:45 2016 Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved. RMAN> connected to target database: USBEN (DBID=2379284599) RMAN> connected to auxiliary database: USBEN (not mounted) RMAN> 2> 3> 4> 5> using target database control file instead of recovery catalog allocated channel: t1 channel t1: SID=58 device type=DISK allocated channel: a1 channel a1: SID=30 device type=DISK released channel: t1 released channel: a1 RMAN> Recovery Manager complete.

The connectivity seems successful. I am proceeding on actual restore now.

Step 5   Duplicate the database.

restore.sh

Start running the above script in background mode.

[oracle@RACTEST1 usben]$ nohup ./restore.sh &

[1] 15411

[oracle@RACTEST1 usben]$

Tailing the log file while duplicate the database.
tail -f  restore.log


Here is the complete restore log file.

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Feb 8 16:39:09 2016 Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved. RMAN> connected to target database: USBEN (DBID=2379284599) RMAN> connected to auxiliary database: USBEN (not mounted) RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> using target database control file instead of recovery catalog allocated channel: t1 channel t1: SID=43 device type=DISK allocated channel: a1 channel a1: SID=33 device type=DISK Starting Duplicate Db at 08-FEB-16 current log archived contents of Memory Script: {    sql clone "create spfile from memory"; } executing Memory Script sql statement: create spfile from memory contents of Memory Script: {    shutdown clone immediate;    startup clone nomount; } executing Memory Script Oracle instance shut down connected to auxiliary database (not started) Oracle instance started Total System Global Area     281018368 bytes Fixed Size                     2923440 bytes Variable Size                222299216 bytes Database Buffers              50331648 bytes Redo Buffers                   5464064 bytes allocated channel: a1 channel a1: SID=33 device type=DISK contents of Memory Script: {    sql clone "alter system set  control_files =   ''+DATA/USBEN/CONTROLFILE/current.304.903285577'', ''+DATA/USBEN/CONTROLFILE/current.294.903285577'' comment=  ''Set by RMAN'' scope=spfile";    sql clone "alter system set  db_name =  ''USBEN'' comment=  ''Modified by RMAN duplicate'' scope=spfile";    sql clone "alter system set  db_unique_name =  ''USBEN'' comment=  ''Modified by RMAN duplicate'' scope=spfile";    shutdown clone immediate;    startup clone force nomount    restore clone from service  'usben' primary controlfile;    alter clone database mount; } executing Memory Script sql statement: alter system set  control_files =   ''+DATA/USBEN/CONTROLFILE/current.304.903285577'', ''+DATA/USBEN/CONTROLFILE/current.294.903285577'' comment= ''Set by RMAN'' scope=spfile sql statement: alter system set  db_name =  ''USBEN'' comment= ''Modified by RMAN duplicate'' scope=spfile sql statement: alter system set  db_unique_name =  ''USBEN'' comment= ''Modified by RMAN duplicate'' scope=spfile Oracle instance shut down Oracle instance started Total System Global Area     281018368 bytes Fixed Size                     2923440 bytes Variable Size                222299216 bytes Database Buffers              50331648 bytes Redo Buffers                   5464064 bytes allocated channel: a1 channel a1: SID=33 device type=DISK Starting restore at 08-FEB-16 channel a1: starting datafile backup set restore channel a1: using network backup set from service usben channel a1: restoring control file channel a1: restore complete, elapsed time: 00:00:04 output file name=+DATA/USBEN/CONTROLFILE/current.304.903285577 output file name=+DATA/USBEN/CONTROLFILE/current.294.903285577 Finished restore at 08-FEB-16 database mounted contents of Memory Script: {    set newname for clone datafile  1 to new;    set newname for clone datafile  2 to new;    set newname for clone datafile  3 to new;    set newname for clone datafile  4 to new;    set newname for clone datafile  5 to new;    set newname for clone datafile  6 to new;    set newname for clone datafile  7 to new;    restore    from service  'usben'   clone database    ;    sql 'alter system archive log current'; } executing Memory Script executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting restore at 08-FEB-16 channel a1: starting datafile backup set restore channel a1: using network backup set from service usben channel a1: specifying datafile(s) to restore from backup set channel a1: restoring datafile 00001 to +DATA channel a1: restore complete, elapsed time: 00:03:05 channel a1: starting datafile backup set restore -- trimming the info here for brevity channel a1: starting datafile backup set restore channel a1: using network backup set from service usben channel a1: specifying datafile(s) to restore from backup set channel a1: restoring datafile 00007 to +DATA channel a1: restore complete, elapsed time: 00:00:03 Finished restore at 08-FEB-16 sql statement: alter system archive log current current log archived contents of Memory Script: {    restore clone force from service  'usben'            archivelog from scn  2241689;    switch clone datafile all; } executing Memory Script Starting restore at 08-FEB-16 channel a1: starting archived log restore to default destination channel a1: using network backup set from service usben channel a1: restoring archived log archived log thread=1 sequence=55 channel a1: restore complete, elapsed time: 00:00:01 channel a1: starting archived log restore to default destination channel a1: using network backup set from service usben channel a1: restoring archived log archived log thread=1 sequence=56 channel a1: restore complete, elapsed time: 00:00:01 Finished restore at 08-FEB-16 datafile 1 switched to datafile copy input datafile copy RECID=8 STAMP=903285999 file name=+DATA/USBEN/DATAFILE/system.309.903285615 -- trimming the info here for brevity input datafile copy RECID=14 STAMP=903286000 file name=+DATA/USBEN/DATAFILE/usben_indx.296.903285995 contents of Memory Script: {    set until scn  2242137;    recover    clone database     delete archivelog    ; } executing Memory Script executing command: SET until clause Starting recover at 08-FEB-16 starting media recovery archived log for thread 1 with sequence 55 is already on disk as file /backup/usben/arch/1_55_902782329.dbf archived log for thread 1 with sequence 56 is already on disk as file /backup/usben/arch/1_56_902782329.dbf archived log file name=/backup/usben/arch/1_55_902782329.dbf thread=1 sequence=55 archived log file name=/backup/usben/arch/1_56_902782329.dbf thread=1 sequence=56 media recovery complete, elapsed time: 00:00:00 Finished recover at 08-FEB-16 Oracle instance started Total System Global Area     281018368 bytes Fixed Size                     2923440 bytes Variable Size                222299216 bytes Database Buffers              50331648 bytes Redo Buffers                   5464064 bytes contents of Memory Script: {    sql clone "alter system set  db_name =  ''USBEN'' comment=  ''Reset to original value by RMAN'' scope=spfile";    sql clone "alter system reset  db_unique_name scope=spfile"; } executing Memory Script sql statement: alter system set  db_name =  ''USBEN'' comment= ''Reset to original value by RMAN'' scope=spfile sql statement: alter system reset  db_unique_name scope=spfile Executing: create pfile='/ora/app/oracle/product/12.1.0.1/db_1/dbs/initusben.ora' from spfile Oracle instance started Total System Global Area     281018368 bytes Fixed Size                     2923440 bytes Variable Size                222299216 bytes Database Buffers              50331648 bytes Redo Buffers                   5464064 bytes sql statement: CREATE CONTROLFILE REUSE SET DATABASE "USBEN" RESETLOGS ARCHIVELOG   MAXLOGFILES     16   MAXLOGMEMBERS      3   MAXDATAFILES      100   MAXINSTANCES     8   MAXLOGHISTORY      292  LOGFILE   GROUP   1 ( '+DATA', '+DATA' ) SIZE 200 M ,   GROUP   2 ( '+DATA', '+DATA' ) SIZE 200 M  DATAFILE   '+DATA/USBEN/DATAFILE/system.309.903285615'  CHARACTER SET WE8MSWIN1252 contents of Memory Script: {    set newname for clone tempfile  2 to new;    switch clone tempfile all;    catalog clone datafilecopy  "+DATA/USBEN/DATAFILE/usben_data.306.903285801",  "+DATA/USBEN/DATAFILE/sysaux.302.903285803",  "+DATA/USBEN/DATAFILE/undotbs1.297.903285909",  "+DATA/USBEN/DATAFILE/example.299.903285917",  "+DATA/USBEN/DATAFILE/users.307.903285991",  "+DATA/USBEN/DATAFILE/usben_indx.296.903285995";    switch clone datafile all; } executing Memory Script executing command: SET NEWNAME renamed tempfile 2 to +DATA in control file cataloged datafile copy datafile copy file name=+DATA/USBEN/DATAFILE/usben_data.306.903285801 RECID=1 STAMP=903286025 cataloged datafile copy -- trimming the info here for brevity cataloged datafile copy datafile copy file name=+DATA/USBEN/DATAFILE/usben_indx.296.903285995 RECID=6 STAMP=903286026 datafile 2 switched to datafile copy input datafile copy RECID=1 STAMP=903286025 file name=+DATA/USBEN/DATAFILE/usben_data.306.903285801 -- trimming the info here for brevity datafile 7 switched to datafile copy input datafile copy RECID=6 STAMP=903286026 file name=+DATA/USBEN/DATAFILE/usben_indx.296.903285995 contents of Memory Script: {    Alter clone database open resetlogs; } executing Memory Script database opened Finished Duplicate Db at 08-FEB-16 released channel: t1 RMAN> Recovery Manager complete.

Step 6 Create the spfile in ASM disk.  We could do this step at the beginning too.  However, i do this step at the end for my convenience. 

sys@usben> show parameter spfile; NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ spfile                               string -- Creating spfile in ASM disk sys@usben> create spfile='+DATA/USBEN/PARAMETERFILE/spfileusben.ora' from PFILE; File created. sys@usben> ! -- Here i updated the parameter file to point to SPFILE in ASM disk. [oracle@RACTEST1 dbs]$ cat initusben.ora SPFILE='+DATA/USBEN/PARAMETERFILE/spfileusben.ora' [oracle@RACTEST1 dbs]$ -- Restart the database with spfile. sys@usben> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. sys@usben> startup ORACLE instance started. Total System Global Area  281018368 bytes Fixed Size                  2923440 bytes Variable Size             222299216 bytes Database Buffers           50331648 bytes Redo Buffers                5464064 bytes Database mounted. Database opened. sys@usben> set echo on sys@usben> set feedback on sys@usben> show parameter spfile NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ spfile                               string      +DATA/USBEN/PARAMETERFILE/spfileusben.ora sys@usben>

Step 7  Verify the restored database.

The database restored successfully and migrated to ASM instance.