Recreate Physical Standby Database after Recovery of Primary Database

 

In Oracle 11g it is possible to create a Physical Standby Database from the Primary Database using RMAN. Furthermore, the same procedure can be adopted in an existing Data Guard environment to recreate the Physical Standby Database following recovery of the Primary Database.

 

A number of reasons may influence your decision to recreate the Standby Database, these include:

 

 

All the above have one thing in common; data synchronisation required between Primary and Standby. In terms of restoration time, it may be advantageous to recreate the Standby Database from the Primary. This is easily accomplished using RMAN’s Oracle 11g new feature;

duplicate target database for standby from active database”.

 

For this exercise, the assumptions are as follows:

 

 

In this example, both Physical and Standby databases are 2 node RAC using ASM shared storage.

Delete Standby Database Files From ASM (if existing)

1.      Ensure the Standby Database is shutdown (if available). As oracle user logon to Standby Database Node A and type following command:

ps –ef | grep pmon | grep –v grep

 

If text similar to the following is returned by the above command, shutdown the database.

oracle   20844     1  0 Feb16 ?        00:00:07 ora_pmon_PRDSBY1

To shutdown the Standby Database issue the following command as oracle user:

srvctl stop database -d PRDSBY

 

2.      Logon to Standby Database Node A as oracle user and set ASM environment variables as follows:

. oraenv

ORACLE_SID = [PRDSBY1] ? +ASM1

 

3.      Delete the database files from ASM +DATA diskgroup using ASMCMD utility:

asmcmd rm +DATA/PRDSBY/CONTROLFILE/*’

asmcmd rm +DATA/PRDSBY/DATAFILE/*’

asmcmd rm +DATA/PRDSBY/ONLINELOG/*’

asmcmd rm +DATA/PRDSBY/TEMPFILE/*’

asmcmd rm +DATA/PRDSBY/PARAMETERFILE/*’

 

4.      Delete the database files from ASM +FLASH diskgroup using ASMCMD utility:

asmcmd rm +FLASH/PRDSBY/CONTROLFILE/*’

asmcmd rm +FLASH/PRDSBY/BACKUPSET/*’

asmcmd rm +FLASH/PRDSBY/ONLINELOG/*’

 

 

Tip:

RMAN will not overwrite database files on the Standby, they must be removed first.

ASM will not allow the deletion of files if the database is open (files are accessed)

ASM will implicitly remove empty sub-directories, so only need to delete the files.

 

Recreate Standby Database

1.      Logon to Standby Database Node A as oracle user and set Oracle environment variables as follows:

. oraenv

ORACLE_SID = [PRDSBY1] ? PRDSBY1

 

2.      Create a controlfile for the Standby Database from the Primary Database. As the oracle user, logon to Primary node A and execute the following commands:

srvctl stop database –d PRD

export ORACLE_SID=PRD1

sqlplus '/as sysdba'

SQL> STARTUP MOUNT;

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/PRDSBY.ctl';

SQL> ALTER DATABASE OPEN;

SQL> exit

3.      As the oracle user, copy the control file to the Standby site node A:

scp /tmp/PRDSBY.ctl stby-nodea-vip:/tmp/PRDSBY.ctl

 

4.      Temporarily defer the shipping of logs to the Standby nodes. As oracle user, logon to Primary node A and execute the following commands:

export ORACLE_SID=PRD1

sqlplus '/as sysdba'

SQL> alter system set log_archive_dest_state_2=defer scope=memory sid='*';

 

5.      As the oracle user, logon to Standby node A and create a temporary database initialisation file as follows:

vi /tmp/initPRDSBY.ora

db_name=PRD

remote_login_passwordfile=EXCLUSIVE

compatible=11.1.0

6.      As the oracle user, logon to Standby node A and startup nomount Standby instance 1 using the following commands:

export ORACLE_SID='PRDSBY1'

sqlplus '/ as sysdba'

startup nomount pfile='/tmp/initPRDSBY.ora'

 

7.      On Standby Node A, rename any existing PFILE  in $ORACLE_HOME/dbs (default location for database initialisation files)

mv $ORACLE_HOME/dbs/initPRDSBY1.ora $ORACLE_HOME/dbs/initPRDSBY1.bak

 

Tip: The original $ORACLE_HOME/dbs/initPRDSBY1.ora file contains the following text:

SPFILE='+DATA/PRDSBY/spfilePRDSBY.ora'. This currently does not exist on the shared ASM storage. Therefore rename the file to prevent RMAN using it to start the database instance. Otherwise RMAN will fail to the following error stack:

 

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 02/24/2010 08:43:43

RMAN-03015: error occurred in stored script Memory Script

RMAN-03009: failure of backup command on prmy2 channel at 02/24/2010 08:43:43

ORA-17628: Oracle error 19505 returned by remote Oracle server

 

ORA-19505 error is “failed to identify file”.

 

8.      Ensure the Standby node A has a similar SID List configured in $ORACLE_HOME/network/admin/listener.ora  file to that shown below, and restart the database listener if necessary:

SID_LIST_LISTENER_SBY_NODEA =
(SID_LIST =
        (SID_DESC =
                (GLOBAL_DBNAME=PRDSBY_DGMGRL)
                (SID_NAME = PRDSBY1)
                (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
        )
)

 

9.      Ensure the Primary node A has a similar TNS names entry in $ORACLE_HOME/network/admin/tnsnames.ora file to that shown below:

STBY_PRD_DGMGRL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = stby-nodea-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PRDSBY_DGMGRL)
    )
  )

 

10.  As oracle user, logon to Primary node A and test the connection to the Standby instance:

sqlplus sys/<password>@STBY_PRD_DGMGRL as sysdba

 

11.  If connection succeeds, use RMAN to create a Physical Standby Database from the active PRD (Primary) Database.

As oracle user, connect to the Primary Database using RMAN:

export ORACLE_SID=PRD1

rman target / auxiliary sys/<password>@STBY_PRD_DGMGRL

 

12.  Then execute the following RMAN script to duplicate the Primary Database across the network to the Standby:

run {

      allocate channel prmy1 type disk;

        allocate channel prmy2 type disk;

        allocate channel prmy3 type disk;

        allocate channel prmy4 type disk;

        allocate auxiliary channel stby type disk;

        duplicate target database for standby from active database

      spfile

            set db_unique_name='PRDSBY'

            set control_files='/tmp/PRDSBY.ctl'

            set instance_number='1'

            set audit_file_dest='/u01/app/oracle/admin/PRD/adump'

            set remote_listener='LISTENERS_PRDSBY'

            nofilenamecheck;

     }

exit

 

See example output in Appendix B of this document.

13.  Once RMAN completes the operation, logon to Standby node A as oracle user and shutdown the Standby Database as follows:

export ORACLE_SID=PRDSBY1

sqlplus '/as sysdba'

SQL>  shutdown

 

14.  Then startup mount the Standby Instance 1, create SPFILE on ASM shared storage from temporary PFILE (see example in Appendix A) and shutdown the Instance:

SQL> startup mount pfile='/tmp/initPRDSBY1.ora'

SQL> create spfile='+DATA/PRDSBY/spfilePRDSBY.ora' from pfile='/tmp/initPRDSBY1.ora';

SQL> shutdown

SQL> exit

 

15.  Now rename the SPFILE created by RMAN to prevent Oracle using it to start the Standby instance. As oracle user execute the following command on Standby Node A:

mv $ORACLE_HOME/dbs/spfilePRDSBY1.ora $ORACLE_HOME/dbs/spfilePRDSBY1.bak

16.  As oracle user logon to Standby nodes A and B and check the database initialisation file contains the following.

Tip: Remember to rename the PFILE back to original name on Node A.

Node A

mv $ORACLE_HOME/dbs/initPRDSBY1.bak $ORACLE_HOME/dbs/initPRDSBY1.ora

cat $ORACLE_HOME/dbs/initPRDSBY1.ora

 

SPFILE='+DATA/PRDSBY/spfilePRDSBY.ora'

 

Node B

cat $ORACLE_HOME/dbs/initPRDSBY2.ora

 

SPFILE='+DATA/PRDSBY/spfilePRDSBY.ora'

17.  Startup nomount the Standby Database Instance again:

 

export ORACLE_SID=PRDSBY1

sqlplus '/as sysdba'

SQL> startup nomount

18.  Then change the value of the control_files Standby Database initialisation parameter to ‘+DATA’,’ +FLASH’:

SQL> alter system set control_files=’+DATA’,’+FLASH’ scope=spfile;

SQL> exit

 

19.  Remaining on Standby node A, use RMAN to restore the Physical Standby Database controlfile to ASM shared storage [+DATA and +FLASH diskgroups]:

 

rman target /

restore controlfile from ’/tmp/PRDSBY.ctl

exit

 

20.  From Standby node A shutdown the Standby Database as SYSDBA:

export ORACLE_SID=PRDSBY1

sqlplus '/as sysdba'

SQL> shutdown

SQL> exit

 

21.  Then startup Standby Database cluster using srvctl utility:

srvctl start database -d PRDSBY

srvctl status database –d PRDSBY

 

22.  From Standby node 1, place the Standby Database into Automatic Managed Recovery mode, known as starting the Managed Recovery Process (MRP):

 

export ORACLE_SID=PRDSBY1

sqlplus '/as sysdba'

SQL> alter database recover managed standby database disconnect from session;
SQL> exit

23.  Logon to Primary Database instance1 as SYSDBA and re-enable log shipping for Data Guard as follows:

export ORACLE_SID=PRD1

sqlplus '/as sysdba'

SQL> alter system set log_archive_dest_state_2=enable scope=memory sid='*';

SQL> alter system archive log current;

SQL> exit

 

24.  After a short while (enough time for Standby Database to synchronise with the Primary), stop the Managed Recovery Process as follows:

 

export ORACLE_SID=PRDSBY1

sqlplus '/as sysdba'

SQL> alter database recover managed standby database cancel;

25.  Then open the database and restart MRP:

 

SQL> alter database open;

SQL> alter database recover managed standby database disconnect from session;
SQL> exit

 

Tip: Only 1 Instance can run the Managed Recovery Process, otherwise the above alter database command will fail to:

ORA-01153: an incompatible media recovery is active

 

26.  Logon Standby node 2 and open the database from instance 2:

export ORACLE_SID=PRDSBY2

sqlplus '/as sysdba'

SQL> alter database open;

SQL> exit

 

27.  Then start the Standby Database services using srvctl:

 

srvctl start service –d PRDSBY –s PRD_ACTIVE

 

28.  As oracle user, log back onto Primary node A and start database instance 2 using srvctl:

 

srvctl start instance –d PRD –i PRD2

 

29.  Then start the Primary Database services using srvctl:

 

srvctl start service –d PRD –s PRD_ACTIVE

 

 

Check Data Guard is Shipping and Applying Logs on Standby Database

 

30.  Execute the following SQL on the Standby Database to verify that redo data is being received from the Primary, then registered and applied on the Standby :

 

export ORACLE_SID=PRDSBY1

sqlplus '/as sysdba'

SQL> alter session set nls_date_format=’YYYY-MON-DD HH24:MI:SS’;

SQL> SELECT SEQUENCE#, THREAD#, FIRST_TIME, NEXT_TIME, APPLIED

     FROM V$ARCHIVED_LOG

     ORDER BY THREAD#, SEQUENCE#;

 

Example:

SEQUENCE# THREAD# FIRST_TIME NEXT_TIME             APPLIED

---------- ---------- -------------------- ----------------------------

        1           1 2009-MAR-30 18:11:04 2009-MAR-30 18:14:30  YES

        2           1 2009-MAR-30 18:14:30 2009-MAR-31 13:49:18  YES

        3           1 2009-MAR-31 14:33:18 2009-MAR-31 14:33:28  YES

        4           1 2009-MAR-31 14:33:28 2009-MAR-31 15:28:26  YES

        5           1 2009-MAR-31 15:45:44 2009-MAR-31 15:45:50  YES

        6           1 2009-MAR-31 15:45:50 2009-MAR-31 15:53:17  YES

        7           1 2009-MAR-31 15:53:17 2009-MAR-31 16:00:37  YES

 

31.  Also check the Standby Database instance 1 alert log for errors:

 

tail –f /u01/app/oracle/diag/rdbms/PRDSBY/PRDSBY1/trace/alert_PRDSBY1.log

 

What if my terminal session times-out before the RMAN clone completes?

Depending on the size of your Primary Database and the speed of your network, it is possible that your terminal session (such as PuTTy) times-out whilst executing the RMAN clone script.

 

Tip: Of course, the time out issue can be completely avoided by running the RMAN clone commands within a shell script in the background. See Appendix C for an example script.

 

E.g.

To run the script in the background:

nohup RMAN_clone_script.ksh &

 

To view the output during execution:

tail –f nohup.out

 

Should the time-out occur during the foreground execution, RMAN will continue to copy the datafiles from the Primary site to the Standby site. However, RMAN will be unable to conduct the post datafile copy operations which include switching to the datafile copy on the Standby site.

 

Let’s take a closer look at the datafile names and what has to done before the Physical Standby database can be opened.

 

Below is an extract from the Physical Standby instance 1 alert log when attempting to start the Managed Recovery Process (MRP):

 

alert_PRDSBY1.ora

-----------------

Completed: ALTER DATABASE MOUNT

Tue May 04 18:04:28 2010

alter database recover managed standby database disconnect from session

Attempt to start background Managed Standby Recovery process (PRDSBY1)

Tue May 04 18:04:28 2010

MRP0 started with pid=36, OS id=2762

MRP0: Background Managed Standby Recovery process started (PRDSBY1)

Fast Parallel Media Recovery enabled

Tue May 04 18:04:33 2010

Managed Standby Recovery not using Real Time Apply

Tue May 04 18:04:33 2010

Errors in file /u01/app/oracle/diag/rdbms/prdsby/PRDSBY1/trace/PRDSBY1_dbw0_1357.trc:

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '+DATA/PRD/datafile/system.263.701555551'

ORA-17503: ksfdopn:2 Failed to open file +DATA/PRD/datafile/system.263.701555551

ORA-15012: ASM file '+DATA/PRD/datafile/system.263.701555551' does not exist

 

Note that Oracle cannot identify data file 1 that supports the System tablespace.

 

When we query v$datafile from the mounted instance we see all files have zero bytes:

 

sqlplus / as sysdba

SQL> select file#, bytes/(1024*1024) mbytes from v$datafile;

 

     FILE# MBYTES

---------- ----------

         1          0

         2          0

         3          0

         4          0

         5          0

         6          0

         7          0

         8          0

         9          0

        10          0

        11          0

        12          0

        13          0

        14          0

 

This is because the datafiles need to be renamed to match those in the Standby control file (used to start the instance). The original datafile and the output filenames can be found in the stdout from the RMAN clone script. (See Appendix B)

 

E.g.

 

channel prmy4: starting datafile copy

input datafile file number=00001 name=+DATA/prd/datafile/system.263.701555551

output file name=+DATA/PRDSBY/datafile/system.280.718100033

 

Another method to obtain the required datafile names is to look in ASM on the Standby site at the physical files, using ASM command line utility asmcmd:

 

$ . oraenv
ORACLE_SID = [PRDSBY1] ? +ASM1
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.1.0/asm is /u01/app/oracle
$ asmcmd
ASMCMD> cd data/prdsby/datafile
ASMCMD> ls
PRODUCTION_ARCHIVE_DATA.287.718097165
PRODUCTION_DATA.
262.718097165
PRODUCTION_INDEX.286.718097167
PRODUCTION_LOB.266.718104189
SNMP_DATA.276.718104929
SNMP_INDEX.277.718105127
STREAMS.294.718105201
SYBASE_DATA.275.718104775
SYBASE_INDEX.295.718105287
SYSAUX.268.718103803
SYSTEM.280.718100033
UNDOTBS1.285.718097167
UNDOTBS2.292.718102707
USERS.278.718105353
ASMCMD> exit

 

Steps to rename datafiles on Physical Standby database

  1. Before we can rename the datafiles on the Standby site, the standby_file_management database initialisation parameter must be set to MANUAL:

 

$ . oraenv
ORACLE_SID = [+ASM1] ? PRDSBY1
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1 is /u01/app/oracle

sqlplus / as sysdba

SQL> alter system set standby_file_management=MANUAL scope=spfile sid='*';

 

System altered.

 

SQL> shutdown

..

SQL> startup mount

 

  1. Now rename the datafiles.

 

E.g.

 

alter database rename file '+DATA/prd/datafile/production_data.293.702848931' to '+DATA/prdsby/datafile/production_data.262.718097165';

alter database rename file '+DATA/prd/datafile/production_archive_data.296.702848955' to '+DATA/prdsby/datafile/production_archive_data.287.718097165';

alter database rename file '+DATA/prd/datafile/production_index.294.702848943' to '+DATA/prdsby/datafile/production_index.286.718097167';

alter database rename file '+DATA/prd/datafile/undotbs1.288.701555555' to '+DATA/prdsby/datafile/undotbs1.285.718097167';

alter database rename file '+DATA/prd/datafile/system.263.701555551' to '+DATA/prdsby/datafile/system.280.718100033';

alter database rename file '+DATA/prd/datafile/undotbs2.286.701555565' to '+DATA/prdsby/datafile/undotbs2.292.718102707';

alter database rename file '+DATA/prd/datafile/sysaux.289.701555553' to '+DATA/prdsby/datafile/sysaux.268.718103803';

alter database rename file '+DATA/prd/datafile/production_lob.295.702848949' to '+DATA/prdsby/datafile/production_lob.266.718104189';

alter database rename file '+DATA/prd/datafile/sybase_data.291.702848847' to '+DATA/prdsby/datafile/sybase_data.275.718104775';

alter database rename file '+DATA/prd/datafile/snmp_data.297.703094083' to '+DATA/prdsby/datafile/snmp_data.276.718104929';

alter database rename file '+DATA/prd/datafile/snmp_index.298.703094085' to '+DATA/prdsby/datafile/snmp_index.277.718105127';

alter database rename file '+DATA/prd/datafile/streams.290.701571837' to '+DATA/prdsby/datafile/streams.294.718105201';

alter database rename file '+DATA/prd/datafile/sybase_index.292.702848849' to '+DATA/prdsby/datafile/sybase_index.295.718105287';

alter database rename file '+DATA/prd/datafile/users.285.701555565' to '+DATA/prdsby/datafile/users.278.718105353';

 

  1. Set the standby_file_management database initialisation parameter back to AUTO:

 

SQL> alter system set standby_file_management=AUTO scope=both sid='*';

 

System altered.

 

  1. Query v$datafile to see if Oracle can access the files:

 

SQL> select file#, bytes/(1024*1024) mbytes from v$datafile;

 

     FILE# MBYTES

---------- ----------

         1 6280

         2 1095.125

         3 8300

         4 3165

         5 5

         6 100

         7 500

         8 100

         9 149052

        10 115054.75

        11 1024

        12 136777.063

        13 259.125

        14 139.875

 

  1. Yes, we have filesizes! Now start the MRP on Physical Standby database instance 1:

 

SQL> alter database recover managed standby database disconnect from session;

 

Database altered.

 

  1. To open the database read-only, the MRP has to be stopped, database opened and MRP restarted. This is dependant on the Primary and Standby databases being synchronised:

 

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; -- stop MRP

 

Database altered.

 

SQL> ALTER DATABASE OPEN; -- Open DB Read Only

 

Database altered.

 

SQL> alter database recover managed standby database disconnect from session;

 

Database altered.

 

  1. You do not need to rename the database tempfile as this is implicitly created by Oracle on instance startup, as seen in the Physical Standby instance 1 alert log:

 

alert_PRDSBY1.ora

-----------------

ORA-01186: file 201 failed verification tests

ORA-01157: cannot identify/lock data file 201 - see DBWR trace file

ORA-01110: data file 201: '+DATA'

File 201 not verified due to error ORA-01157

Dictionary check complete

Re-creating tempfile +DATA as +DATA/prdsby/tempfile/temp.301.718189579

 

Another Gotcha!

Despite successfully cloning your Primary Database using RMAN, it is possible that when you attempt to start the Managed Recovery Process (MRP) on the Physical Standby, it fails to the following error seen in the database instance alert log:

 

ORA-19909: datafile 1 belongs to an orphan incarnation

 

This is due to the Primary Database having only 1 incarnation, while the Standby has many, as seen below:

 

RMAN> list incarnation;

 

using target database control file instead of recovery catalog

 

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       PRD      2067014306       PARENT  1          29-OCT-09
3       3       PRD      2067014306       ORPHAN  1385720549 28-APR-10
2       2       PRD      2067014306       CURRENT 13173291621 28-JUN-10

 

The ORA-19909 error can occur if the RMAN clone has been performed before, or the Standby Database has been flashed back.

 

Solution

The solution is to find out the CURRENT incarnation from the Primary Database and reset the Standby Database incarnation to match, using the following RMAN command:

 

reset database to incarnation <key>
then perform Recovery.

E.g.
On the Standby Site:

 

rman target=/
RMAN > reset database to incarnation 1
RMAN > exit

sqlplus / as sysdba
SQL> alter database recover managed standby database disconnect from session;

 

Appendix A

Standby Database Initialisation Parameter File

*.archive_lag_target=0

*.audit_file_dest='/u01/app/oracle/admin/PRD/adump'

*.audit_trail='db'

*.cluster_database_instances=2

*.cluster_database=TRUE

*.compatible='11.1.0.0.0'

*.control_files='/tmp/PRDSBY.ctl'

*.db_block_size=8192

*.db_create_file_dest='+DATA'

*.db_domain=''

*.db_name='PRD'

*.db_recovery_file_dest='+FLASH'

*.db_recovery_file_dest_size=822486237184

*.db_unique_name='PRDSBY'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=PRDSBYXDB)'

*.fal_client='PRDSBY'

*.fal_server='PRD'

PRD2.instance_number=2

PRD1.instance_number=1

PRDSBY1.instance_number=1

PRDSBY2.instance_number=2

PRDSBY1.local_listener='LISTENER_PRDSBY1'

PRDSBY2.local_listener='LISTENER_PRDSBY2'

*.log_archive_config='SEND, RECEIVE, DG_CONFIG=(PRD,PRDSBY)'

*.log_archive_dest_1='LOCATION=+FLASH VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRDSBY'

*.log_archive_dest_2='SERVICE=PRD LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRD'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_format='prd_arch_%t_%s_%r.log'

*.log_archive_max_processes=4

*.max_dump_file_size='100M'

*.memory_target=21474836480

*.nls_language='ENGLISH'

*.nls_territory='UNITED KINGDOM'

*.open_cursors=300

*.processes=1000

*.remote_listener='LISTENERS_PRDSBY'

*.remote_login_passwordfile='exclusive'

*.service_names=''

*.sessions=1105

*.standby_file_management='AUTO'

*.streams_pool_size=536870912

PRD2.thread=2

PRD1.thread=1

*.undo_retention=3600

PRD1.undo_tablespace='UNDOTBS1'

PRD2.undo_tablespace='UNDOTBS2'

 

Appendix B

Successful completion of RMAN duplicate database script

 

Recovery Manager: Release 11.1.0.7.0 - Production on Wed Feb 24 09:05:53 2010

 

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

 

connected to target database: PRD (DBID=2066975734)

connected to auxiliary database: PRD (not mounted)

 

RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15>

using target database control file instead of recovery catalog

allocated channel: prmy1

channel prmy1: SID=1040 instance=PRD1 device type=DISK

 

allocated channel: prmy2

channel prmy2: SID=1035 instance=PRD1 device type=DISK

 

allocated channel: prmy3

channel prmy3: SID=1034 instance=PRD1 device type=DISK

 

allocated channel: prmy4

channel prmy4: SID=1033 instance=PRD1 device type=DISK

 

allocated channel: stby

channel stby: SID=93 device type=DISK

 

Starting Duplicate Db at 24-02-2010 09:06:00

 

contents of Memory Script:

{

   backup as copy reuse

   file '/u01/app/oracle/product/11.1.0/db_1/dbs/orapwPRD1' auxiliary format

 '/u01/app/oracle/product/11.1.0/db_1/dbs/orapwPRDSBY1'   file

 '+DATA/prd/spfileprd.ora' auxiliary format

 '/u01/app/oracle/product/11.1.0/db_1/dbs/spfilePRDSBY1.ora'   ;

   sql clone "alter system set spfile= ''/u01/app/oracle/product/11.1.0/db_1/dbs/spfilePRDSBY1.ora''";

}

executing Memory Script

 

Starting backup at 24-02-2010 09:06:00

Finished backup at 24-02-2010 09:06:01

 

sql statement: alter system set spfile= ''/u01/app/oracle/product/11.1.0/db_1/dbs/spfilePRDSBY1.ora''

 

contents of Memory Script:

{

   sql clone "alter system set  db_unique_name =

 ''PRDSBY'' comment=

 '''' scope=spfile";

   sql clone "alter system set  control_files =

 ''/tmp/PRDSBY.ctl'' comment=

 '''' scope=spfile";

   sql clone "alter system set  instance_number =

 1 comment=

 '''' scope=spfile";

   sql clone "alter system set  audit_file_dest =

 ''/u01/app/oracle/admin/PRD/adump'' comment=

 '''' scope=spfile";

   sql clone "alter system set  remote_listener =

 ''LISTENERS_PRDSBY'' comment=

 '''' scope=spfile";

   shutdown clone immediate;

   startup clone nomount ;

}

executing Memory Script

 

sql statement: alter system set  db_unique_name =  ''PRDSBY'' comment= '''' scope=spfile

 

sql statement: alter system set  control_files =  ''/tmp/PRDSBY.ctl'' comment= '''' scope=spfile

 

sql statement: alter system set  instance_number =  1 comment= '''' scope=spfile

 

sql statement: alter system set  audit_file_dest =  ''/u01/app/oracle/admin/PRD/adump'' comment= '''' scope=spfile

 

sql statement: alter system set  remote_listener =  ''LISTENERS_PRDSBY'' comment= '''' scope=spfile

 

Oracle instance shut down

 

connected to auxiliary database (not started)

Oracle instance started

 

Total System Global Area    1603411968 bytes

 

Fixed Size                     2160112 bytes

Variable Size                855640592 bytes

Database Buffers             738197504 bytes

Redo Buffers                   7413760 bytes

allocated channel: stby

channel stby: SID=1077 instance=PRDSBY1 device type=DISK

 

contents of Memory Script:

{

   backup as copy current controlfile for standby auxiliary format  '/tmp/PRDSBY.ctl';

   sql clone 'alter database mount standby database';

}

executing Memory Script

 

Starting backup at 24-02-2010 09:06:13

channel prmy1: starting datafile copy

copying standby control file

output file name=/u01/app/oracle/product/11.1.0/db_1/dbs/snapcf_PRD1.f tag=TAG20100224T090613 RECID=6 STAMP=711795975

channel prmy1: datafile copy complete, elapsed time: 00:00:07

Finished backup at 24-02-2010 09:06:21

 

sql statement: alter database mount standby database

RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

 

contents of Memory Script:

{

   set newname for tempfile  1 to

 "+data";

   switch clone tempfile all;

   set newname for datafile  1 to

 "+data";

   set newname for datafile  2 to

 "+data";

   set newname for datafile  3 to

 "+data";

   set newname for datafile  4 to

 "+data";

   set newname for datafile  5 to

 "+data";

   set newname for datafile  6 to

 "+data";

   set newname for datafile  7 to

 "+data";

   set newname for datafile  8 to

 "+data";

   set newname for datafile  9 to

 "+data";

   set newname for datafile  10 to

 "+data";

   set newname for datafile  11 to

 "+data";

   set newname for datafile  12 to

 "+data";

   set newname for datafile  13 to

 "+data";

   set newname for datafile  14 to

 "+data";

   backup as copy reuse

   datafile 1 auxiliary format

 "+data"   datafile

 2 auxiliary format

 "+data"   datafile

 3 auxiliary format

 "+data"   datafile

 4 auxiliary format

 "+data"   datafile

 5 auxiliary format

 "+data"   datafile

 6 auxiliary format

 "+data"   datafile

 7 auxiliary format

 "+data"   datafile

 8 auxiliary format

 "+data"   datafile

 9 auxiliary format

 "+data"   datafile

 10 auxiliary format

 "+data"   datafile

 11 auxiliary format

 "+data"   datafile

 12 auxiliary format

 "+data"   datafile

 13 auxiliary format

 "+data"   datafile

 14 auxiliary format

 "+data"   ;

   sql 'alter system archive log current';

}

executing Memory Script

 

executing command: SET NEWNAME

 

renamed tempfile 1 to +data in control file

 

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

 

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 backup at 24-02-2010 09:06:26

channel prmy1: starting datafile copy

input datafile file number=00009 name=+DATA/prd/datafile/production_data.284.708792257

channel prmy2: starting datafile copy

input datafile file number=00010 name=+DATA/prd/datafile/production_index.283.708792273

channel prmy3: starting datafile copy

input datafile file number=00014 name=+DATA/prd/datafile/production_archive_data.285.708792293

channel prmy4: starting datafile copy

input datafile file number=00003 name=+DATA/prd/datafile/undotbs1.265.701517025

output file name=+DATA/PRDSBY/datafile/undotbs1.273.711795991 tag=TAG20100224T090627 RECID=0 STAMP=0

channel prmy4: datafile copy complete, elapsed time: 00:02:15

channel prmy4: starting datafile copy

input datafile file number=00001 name=+DATA/prd/datafile/system.263.701517017

output file name=+DATA/PRDSBY/datafile/system.272.711796123 tag=TAG20100224T090627 RECID=0 STAMP=0

channel prmy4: datafile copy complete, elapsed time: 00:02:25

channel prmy4: starting datafile copy

input datafile file number=00004 name=+DATA/prd/datafile/undotbs2.267.701517037

output file name=+DATA/PRDSBY/datafile/undotbs2.271.711796269 tag=TAG20100224T090627 RECID=0 STAMP=0

channel prmy4: datafile copy complete, elapsed time: 00:01:05

channel prmy4: starting datafile copy

input datafile file number=00002 name=+DATA/prd/datafile/sysaux.264.701517021

output file name=+DATA/PRDSBY/datafile/sysaux.270.711796335 tag=TAG20100224T090627 RECID=0 STAMP=0

channel prmy4: datafile copy complete, elapsed time: 00:00:56

channel prmy4: starting datafile copy

input datafile file number=00013 name=+DATA/prd/datafile/production_lob.282.708792283

output file name=+DATA/PRDSBY/datafile/production_lob.269.711796391 tag=TAG20100224T090627 RECID=0 STAMP=0

channel prmy4: datafile copy complete, elapsed time: 00:00:45

channel prmy4: starting datafile copy

input datafile file number=00007 name=+DATA/prd/datafile/sybase_data.280.708792111

output file name=+DATA/PRDSBY/datafile/sybase_data.262.711796435 tag=TAG20100224T090627 RECID=0 STAMP=0

channel prmy4: datafile copy complete, elapsed time: 00:00:25

channel prmy4: starting datafile copy

input datafile file number=00011 name=+DATA/prd/datafile/snmp_data.278.701874127

output file name=+DATA/PRDSBY/datafile/snmp_data.261.711796461 tag=TAG20100224T090627 RECID=0 STAMP=0

channel prmy4: datafile copy complete, elapsed time: 00:00:15

channel prmy4: starting datafile copy

input datafile file number=00006 name=+DATA/prd/datafile/streams.277.701534093

output file name=+DATA/PRDSBY/datafile/streams.260.711796477 tag=TAG20100224T090627 RECID=0 STAMP=0

channel prmy4: datafile copy complete, elapsed time: 00:00:15

channel prmy4: starting datafile copy

input datafile file number=00008 name=+DATA/prd/datafile/sybase_index.281.708792115

output file name=+DATA/PRDSBY/datafile/sybase_index.259.711796491 tag=TAG20100224T090627 RECID=0 STAMP=0

channel prmy4: datafile copy complete, elapsed time: 00:00:15

channel prmy4: starting datafile copy

input datafile file number=00012 name=+DATA/prd/datafile/snmp_index.279.701874129

output file name=+DATA/PRDSBY/datafile/snmp_index.258.711796507 tag=TAG20100224T090627 RECID=0 STAMP=0

channel prmy4: datafile copy complete, elapsed time: 00:00:07

channel prmy4: starting datafile copy

input datafile file number=00005 name=+DATA/prd/datafile/users.268.701517039

output file name=+DATA/PRDSBY/datafile/users.257.711796513 tag=TAG20100224T090627 RECID=0 STAMP=0

channel prmy4: datafile copy complete, elapsed time: 00:00:07

 

output file name=+DATA/PRDSBY/datafile/production_archive_data.274.711795991 tag=TAG20100224T090627 RECID=0 STAMP=0

channel prmy3: datafile copy complete, elapsed time: 00:29:31

 

output file name=+DATA/PRDSBY/datafile/production_index.266.711795991 tag=TAG20100224T090627 RECID=0 STAMP=0

channel prmy2: datafile copy complete, elapsed time: 00:47:21

output file name=+DATA/PRDSBY/datafile/production_data.275.711795991 tag=TAG20100224T090627 RECID=0 STAMP=0

channel prmy1: datafile copy complete, elapsed time: 00:52:21

Finished backup at 24-02-2010 09:58:48

 

sql statement: alter system archive log current

 

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

 

datafile 1 switched to datafile copy

input datafile copy RECID=6 STAMP=711799130 file name=+DATA/PRDSBY/datafile/system.272.711796123

datafile 2 switched to datafile copy

input datafile copy RECID=7 STAMP=711799130 file name=+DATA/PRDSBY/datafile/sysaux.270.711796335

datafile 3 switched to datafile copy

input datafile copy RECID=8 STAMP=711799131 file name=+DATA/PRDSBY/datafile/undotbs1.273.711795991

datafile 4 switched to datafile copy

input datafile copy RECID=9 STAMP=711799131 file name=+DATA/PRDSBY/datafile/undotbs2.271.711796269

datafile 5 switched to datafile copy

input datafile copy RECID=10 STAMP=711799131 file name=+DATA/PRDSBY/datafile/users.257.711796513

datafile 6 switched to datafile copy

input datafile copy RECID=11 STAMP=711799131 file name=+DATA/PRDSBY/datafile/streams.260.711796477

datafile 7 switched to datafile copy

input datafile copy RECID=12 STAMP=711799131 file name=+DATA/PRDSBY/datafile/sybase_data.262.711796435

datafile 8 switched to datafile copy

input datafile copy RECID=13 STAMP=711799131 file name=+DATA/PRDSBY/datafile/sybase_index.259.711796491

datafile 9 switched to datafile copy

input datafile copy RECID=14 STAMP=711799131 file name=+DATA/PRDSBY/datafile/production_data.275.711795991

datafile 10 switched to datafile copy

input datafile copy RECID=15 STAMP=711799131 file name=+DATA/PRDSBY/datafile/production_index.266.711795991

datafile 11 switched to datafile copy

input datafile copy RECID=16 STAMP=711799131 file name=+DATA/PRDSBY/datafile/snmp_data.261.711796461

datafile 12 switched to datafile copy

input datafile copy RECID=17 STAMP=711799131 file name=+DATA/PRDSBY/datafile/snmp_index.258.711796507

datafile 13 switched to datafile copy

input datafile copy RECID=18 STAMP=711799131 file name=+DATA/PRDSBY/datafile/production_lob.269.711796391

datafile 14 switched to datafile copy

input datafile copy RECID=19 STAMP=711799131 file name=+DATA/PRDSBY/datafile/production_archive_data.274.711795991

Finished Duplicate Db at 24-02-2010 09:58:51

released channel: prmy1

released channel: prmy2

released channel: prmy3

released channel: prmy4

released channel: stby

 

RMAN>

 

Recovery Manager complete.

 

Appendix C

Example shell script for executing the RMAN clone  (RMAN_clone_script.ksh)

 

#!/bin/ksh

 

export ORACLE_SID=PRD1

export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1

 

rman target / auxiliary sys/<password>@STBY_PRD_DGMGRL <<EOF

run {

      allocate channel prmy1 type disk;

        allocate channel prmy2 type disk;

        allocate channel prmy3 type disk;

        allocate channel prmy4 type disk;

        allocate auxiliary channel stby type disk;

        duplicate target database for standby from active database

      spfile

            set db_unique_name='PRDSBY'

            set control_files='/tmp/PRDSBY.ctl'

            set instance_number='1'

            set audit_file_dest='/u01/app/oracle/admin/PRD/adump'

            set remote_listener='LISTENERS_PRDSBY'

            nofilenamecheck;

     }

exit

EOF

_______________________________________________________________________________

Did you find the article useful?

Please provide your feedback by voting now.

If you have a comment or question, please complete and submit the form below.

_______________________________________________________________________________