Creating a Physical Standby with RMAN Active Duplicate in 11.2.0.3

May 28, 2013 / By Rene Antunez

Tags:

Other DBAs have written about this topic, but I wanted it to be available on Pythian’s blog. When I searched for how this was done, other sites were either not very clear on the steps they did, assumed that you already knew what you are doing, or went through the steps too quickly.

If this is your first time building a standby, there is some terminology you need to know before going into any of the steps in creating your physical standby. It will help you to better understand your dataguard environment and what is being done, instead of simply copying a number of steps. These are just the definitions in Oracle’s documentation, but they will help you avoid the arduous search.

  • LOG_ARCHIVE_DEST_n .- It controls different aspects of how redo transport services transfer redo data from primary database destination to a standby.
    This parameter has several attributes that are needed to setup your Dataguard environment, I will only mention the critical ones:

    • ASYNC .-This is the default, the redo data generated by a transaction need not have been received at a destination which has this attribute before that transaction can commit.
      or
    • SYNC .-The redo data generated by a transaction must have been received by every enabled destination that has this attribute before that transaction can commit.
    • AFFIRM and NOAFFIRM .- Control whether a redo transport destination acknowledges received redo data before or after writing it to the standby redo log. The default is NOAFFIRM.
    • DB_UNIQUE_NAME .- Specifies a unique name for the database at this destination. You must specify a name; there is no default value.
    • VALID_FOR .-Identifies when redo transport services can transmit redo data to destinations based on the following factors:
      • redo_log_type .-whether online redo log files, standby redo log files, or both are currently being archived on the database at this destination
      • database_role .-whether the database is currently running in the primary or the standby role
  • FAL_SERVER .-Specifies the FAL (fetch archive log) server for a standby database. The value is an Oracle Net service name.
  • FAL_CLIENT .-Specifies the FAL (fetch archive log) client name that is used by the FAL service, configured through the FAL_SERVER initialization parameter, to refer to the FAL client.
    The value is an Oracle Net service name, which is assumed to be configured properly on the FAL server system to point to the FAL client (standby database).
  • LOG_ARCHIVE_CONFIG .- Enables or disables the sending of redo logs to remote destinations and the receipt of remote redo logs.
    This parameter has several attributes, the most important for this exercise is below:

    • DG_CONFIG .- Specifies a list of up to 30 unique database names (defined with the DB_UNIQUE_NAME initialization parameter) for all of the databases in the Data Guard configuration.

Now that we have the definitions out of the way (which you can find at Oracle 11.2. Documentation), we will continue with the setup of our Physical Standby.

For this exercise, I have the following :

  • Primary : testgg1   Server : dlabvm13
  • Standby : testgg2   Server : dlabvm14

The first thing that we need to do is find where the Redo Logs and Datafiles reside in the Primary and where will they reside in the Standby so that you can set your parameters LOG_FILE_NAME_CONVERT and DB_FILE_NAME_CONVERT properly. Make sure that these directories have the necessary space to hold the Primary database. If you don’t have this space, then do not continue.

Next, assure that you are in archivelog mode, and that force logging is enabled in your primary.

If your database is not in archive log mode, do the following:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 534462464 bytes
Fixed Size 2230072 bytes
Variable Size 163580104 bytes
Database Buffers 364904448 bytes
Redo Buffers 3747840 bytes
Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter database force logging;

Database altered.

SQL> select log_mode,force_logging from v$database;

LOG_MODE FOR
------------ ---
ARCHIVELOG YES

Now that we are running in archive log mode and force logging is set for the primary, make sure that the Listener/ Tns entries are set correctly and that you can tnsping them both from the primary/standby.

For more details on how to set them up, go to Kamran’s blog entry ( Step 2 ).

The result in both the primary and standby servers should be similar to below:

oracle@dlabvm13.dlab.pythian.com [testgg1] /home/oracle/bin
oracle $ tnsping testgg1

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 14-MAY-2013 09:44:34
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dlabvm13.dlab.pythian.com)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = testgg1)))

OK (0 msec)

oracle@dlabvm13.dlab.pythian.com [testgg1] /home/oracle/bin
oracle $ tnsping testgg2
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 14-MAY-2013 09:44:40
Copyright (c) 1997, 2011, Oracle. All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dlabvm14.dlab.pythian.com)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = testgg2)))

OK (10 msec)

Then, create and replicate the password file from the primary $ORACLE_HOME/dbs, and rename it to the standby database name. The password file name must match the ORACLE_SID used at the standby site, not the DB_NAME.

oracle@dlabvm13.dlab.pythian.com [testgg1] /u01/app/oracle/product/11.2.0/dbhome_1/dbs
oracle $ orapwd file=orapwtestgg1 password=test entries=5

oracle@dlabvm13.dlab.pythian.com [testgg1] /u01/app/oracle/product/11.2.0/dbhome_1/dbs
oracle $ scp orapwtestgg1 dlabvm14:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwtestgg2

orapwtestgg1 100% 2048 2.0KB/s 00:00

Now that the password file has been created, you can setup the init file for the Primary Database. As you can see at the end, it’s the parameters we explained at the beginning of this entry.


SQL> !cat dg.ora
*.audit_file_dest='/u01/app/oracle/oradata/dump/testgg1/adump'
*.audit_trail='db'
*.compatible='11.2.0'
*.control_files='/u01/app/oracle/oradata/testgg1/control/control01.ctl','/u01/app/oracle/oradata/testgg1/control/control02.ctl'
*.db_block_size=8192
*.db_cache_size=128M
*.db_domain=''
*.db_name='testgg1'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=2G
*.db_unique_name='testgg1'
*.diagnostic_dest='/u01/app/oracle/oradata/dump/testgg1'
*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=testgg1'
*.log_archive_dest_2='SERVICE=testgg2 ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testgg2'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.open_cursors=300
*.pga_aggregate_target=128M
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=512M
testgg11.UNDO_TABLESPACE='UNDOTBS1'
FAL_SERVER=testgg2;
FAL_CLIENT=testgg1;
DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/','/u01/app/oracle/oradata/testgg1/TESTGG1/datafile/'
LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/testgg2/TESTGG2/onlinelog/','/u01/app/oracle/oradata/testgg1/TESTGG1/onlinelog/'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(testgg1,testgg2)'

One of the coolest things about this method is that almost all of the work we will do will be in the Primary database server. The only thing you have to do in the Standby Server is create the locations of my diagnostic files/redo/datafiles/control files, verify the connectivity between the Primary and the Standby and just start the Standby Instance, which is our next step.

The next step is to set the ORACLE_SID, ORACLE_HOME, and ORACLE_BASE  for the Standby Instance and open it with minimal options:


oracle@dlabvm14.dlab.pythian.com [testgg2] /home/oracle/bin
oracle $ export ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1"

oracle@dlabvm14.dlab.pythian.com [testgg2] /home/oracle/bin
oracle $ export ORACLE_BASE=/u01/app/oracle

oracle@dlabvm14.dlab.pythian.com [testgg2] /home/oracle/bin
oracle $ export ORACLE_SID=testgg2

oracle@dlabvm14.dlab.pythian.com [testgg2] /home/oracle/bin
oracle $ cat dg.ora

DB_NAME=testgg1
DB_UNIQUE_NAME=testgg2
DB_BLOCK_SIZE=8192

oracle@dlabvm14.dlab.pythian.com [testgg2] /home/oracle/bin
oracle $ sqlplus
SQL*Plus: Release 11.2.0.3.0 Production on Tue May 14 09:53:30 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Enter user-name: /as sysdba

Connected to an idle instance.

SQL> startup nomount pfile='/home/oracle/bin/dg.ora';
ORACLE instance started.
Total System Global Area  238034944 bytes
Fixed Size              2227136 bytes
Variable Size            180356160 bytes
Database Buffers        50331648 bytes
Redo Buffers              5120000 bytes

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Now let’s get back to the Primary database server, where the next thing to do is add the standby logs. In this case, I created two with the same size as the Online Redo Logs that I have in my Primary.

SQL> select instance_name,host_name from v$instance;

INSTANCE_NAME HOST_NAME
---------------- ----------------------------------------------------------------
testgg1 dlabvm13.dlab.pythian.com

SQL> select name from v$database;

NAME
---------
TESTGG1

SQL> select GROUP#,THREAD#,MEMBERS,BYTES FROM V$LOG;

GROUP# THREAD# MEMBERS BYTES
---------- ---------- ---------- ----------
1 1 1 104857600
2 1 1 104857600

SQL> ALTER DATABASE ADD STANDBY LOGFILE SIZE 104857600;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE SIZE 104857600;

Database altered.

SQL> SELECT GROUP#, BYTES FROM V$STANDBY_LOG;

GROUP# BYTES
---------- ----------
 3 104857600
 4 104857600

Once we have created the Standby Logs in my Primary Database, we will create the following RMAN run commands and  the changes to the standby’s spfile in this RMAN block.

oracle@dlabvm13.dlab.pythian.com [testgg1] /home/oracle/bin
oracle $ cat duplicate.rmn

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
 parameter_value_convert 'testgg1','testgg2'
 set db_unique_name='testgg2'
 set db_file_name_convert='/u01/app/oracle/oradata/testgg1/TESTGG1/datafile/','/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/'
 set log_file_name_convert='/u01/app/oracle/oradata/testgg1/TESTGG1/onlinelog/','/u01/app/oracle/oradata/testgg2/TESTGG2/onlinelog/'
 set control_files='/u01/app/oracle/oradata/testgg2/control/control01.ctl','/u01/app/oracle/oradata/testgg2/control/control02.ctl'
 set log_archive_max_processes='10'
 set fal_client='testgg2'
 set fal_server='testgg1'
 set standby_file_management='AUTO'
 set log_archive_config='dg_config=(testgg1,testgg2)'
 set log_archive_dest_2='service=testgg1 ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=testgg1'
;
}

Now that the the RMAN command file has been created, you just need to run this command in the target database, which is the Primary Database , connecting to the Standby as the auxiliary.


oracle@dlabvm13.dlab.pythian.com [testgg1] /home/oracle/bin
oracle $ rman target sys/test@testgg1 auxiliary sys/test@testgg2

Recovery Manager: Release 11.2.0.3.0 - Production on Thu May 16 08:42:46 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: TESTGG1 (DBID=839852638)
connected to auxiliary database: TESTGG1 (not mounted)

RMAN> @duplicate.rmn

RMAN>
RMAN> run {
2> allocate channel prmy1 type disk;
3> allocate channel prmy2 type disk;
4> allocate channel prmy3 type disk;
5> allocate channel prmy4 type disk;
6> allocate auxiliary channel stby type disk;
7> duplicate target database for standby from active database
8> spfile
9> parameter_value_convert 'testgg1','testgg2'
10> set db_unique_name='testgg2'
11> set db_file_name_convert='/u01/app/oracle/oradata/testgg1/TESTGG1/datafile/','/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/'
12> set log_file_name_convert='/u01/app/oracle/oradata/testgg1/TESTGG1/onlinelog/','/u01/app/oracle/oradata/testgg2/TESTGG2/onlinelog/'
13> set control_files='/u01/app/oracle/oradata/testgg2/control/control01.ctl','/u01/app/oracle/oradata/testgg2/control/control02.ctl'
14> set log_archive_max_processes='10'
15> set fal_client='testgg2'
16> set fal_server='testgg1'
17> set standby_file_management='AUTO'
18> set log_archive_config='dg_config=(testgg1,testgg2)'
19> set log_archive_dest_2='service=testgg1 ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=testgg1'
20> ;
21> }
using target database control file instead of recovery catalog
allocated channel: prmy1
channel prmy1: SID=32 device type=DISK

allocated channel: prmy2
channel prmy2: SID=33 device type=DISK

allocated channel: prmy3
channel prmy3: SID=34 device type=DISK

allocated channel: prmy4
channel prmy4: SID=35 device type=DISK

allocated channel: stby
channel stby: SID=19 device type=DISK

Starting Duplicate Db at 16-MAY-13

contents of Memory Script:
{
 backup as copy reuse
 targetfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwtestgg1' auxiliary format
 '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwtestgg2' targetfile
 '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfiletestgg1.ora' auxiliary format
 '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfiletestgg2.ora' ;
 sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfiletestgg2.ora''";
}
executing Memory Script

Starting backup at 16-MAY-13
Finished backup at 16-MAY-13

sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfiletestgg2.ora''

contents of Memory Script:
{
 sql clone "alter system set audit_file_dest =
 ''/u01/app/oracle/oradata/dump/testgg2/adump'' comment=
 '''' scope=spfile";
 sql clone "alter system set diagnostic_dest =
 ''/u01/app/oracle/oradata/dump/testgg2'' comment=
 '''' scope=spfile";
 sql clone "alter system set log_archive_dest_1 =
 ''location=use_db_recovery_file_dest valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=testgg2'' comment=
 '''' scope=spfile";
 sql clone "alter system set db_unique_name =
 ''testgg2'' comment=
 '''' scope=spfile";
 sql clone "alter system set db_file_name_convert =
 ''/u01/app/oracle/oradata/testgg1/TESTGG1/datafile/'', ''/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/'' comment=
 '''' scope=spfile";
 sql clone "alter system set log_file_name_convert =
 ''/u01/app/oracle/oradata/testgg1/TESTGG1/onlinelog/'', ''/u01/app/oracle/oradata/testgg2/TESTGG2/onlinelog/'' comment=
 '''' scope=spfile";
 sql clone "alter system set control_files =
 ''/u01/app/oracle/oradata/testgg2/control/control01.ctl'', ''/u01/app/oracle/oradata/testgg2/control/control02.ctl'' comment=
 '''' scope=spfile";
 sql clone "alter system set log_archive_max_processes =
 10 comment=
 '''' scope=spfile";
 sql clone "alter system set fal_client =
 ''testgg2'' comment=
 '''' scope=spfile";
 sql clone "alter system set fal_server =
 ''testgg1'' comment=
 '''' scope=spfile";
 sql clone "alter system set standby_file_management =
 ''AUTO'' comment=
 '''' scope=spfile";
 sql clone "alter system set log_archive_config =
 ''dg_config=(testgg1,testgg2)'' comment=
 '''' scope=spfile";
 sql clone "alter system set log_archive_dest_2 =
 ''service=testgg1 ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=testgg1'' comment=
 '''' scope=spfile";
 shutdown clone immediate;
 startup clone nomount;
}
executing Memory Script

sql statement: alter system set audit_file_dest = ''/u01/app/oracle/oradata/dump/testgg2/adump'' comment= '''' scope=spfile

sql statement: alter system set diagnostic_dest = ''/u01/app/oracle/oradata/dump/testgg2'' comment= '''' scope=spfile

sql statement: alter system set log_archive_dest_1 = ''location=use_db_recovery_file_dest valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=testgg2'' comment= '''' scope=spfile

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

sql statement: alter system set db_file_name_convert = ''/u01/app/oracle/oradata/testgg1/TESTGG1/datafile/'', ''/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/'' comment= '''' scope=spfile

sql statement: alter system set log_file_name_convert = ''/u01/app/oracle/oradata/testgg1/TESTGG1/onlinelog/'', ''/u01/app/oracle/oradata/testgg2/TESTGG2/onlinelog/'' comment= '''' scope=spfile

sql statement: alter system set control_files = ''/u01/app/oracle/oradata/testgg2/control/control01.ctl'', ''/u01/app/oracle/oradata/testgg2/control/control02.ctl'' comment= '''' scope=spfile

sql statement: alter system set log_archive_max_processes = 10 comment= '''' scope=spfile

sql statement: alter system set fal_client = ''testgg2'' comment= '''' scope=spfile

sql statement: alter system set fal_server = ''testgg1'' comment= '''' scope=spfile

sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile

sql statement: alter system set log_archive_config = ''dg_config=(testgg1,testgg2)'' comment= '''' scope=spfile

sql statement: alter system set log_archive_dest_2 = ''service=testgg1 ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=testgg1'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 534462464 bytes

Fixed Size 2230072 bytes
Variable Size 163580104 bytes
Database Buffers 364904448 bytes
Redo Buffers 3747840 bytes
allocated channel: stby
channel stby: SID=18 device type=DISK

contents of Memory Script:
{
 backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/testgg2/control/control01.ctl';
 restore clone controlfile to '/u01/app/oracle/oradata/testgg2/control/control02.ctl' from
 '/u01/app/oracle/oradata/testgg2/control/control01.ctl';
}
executing Memory Script

Starting backup at 16-MAY-13
channel prmy1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_testgg1.f tag=TAG20130516T084319 RECID=7 STAMP=815561001
channel prmy1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 16-MAY-13

Starting restore at 16-MAY-13

channel stby: copied control file copy
Finished restore at 16-MAY-13

contents of Memory Script:
{
 sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
 set newname for tempfile 1 to
 "/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_temp_8qbok7fk_.tmp";
 switch clone tempfile all;
 set newname for datafile 1 to
 "/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_system_8ocl7bho_.dbf";
 set newname for datafile 2 to
 "/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_sysaux_8ocl7k6n_.dbf";
 set newname for datafile 3 to
 "/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_sys_undo_8ocl7q6c_.dbf";
 set newname for datafile 4 to
 "/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_users_8qbolh3g_.dbf";
 backup as copy reuse
 datafile 1 auxiliary format
 "/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_system_8ocl7bho_.dbf" datafile
 2 auxiliary format
 "/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_sysaux_8ocl7k6n_.dbf" datafile
 3 auxiliary format
 "/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_sys_undo_8ocl7q6c_.dbf" datafile
 4 auxiliary format
 "/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_users_8qbolh3g_.dbf" ;
 sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_temp_8qbok7fk_.tmp in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 16-MAY-13
channel prmy1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/testgg1/TESTGG1/datafile/o1_mf_system_8ocl7bho_.dbf
channel prmy2: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/testgg1/TESTGG1/datafile/o1_mf_sysaux_8ocl7k6n_.dbf
channel prmy3: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/testgg1/TESTGG1/datafile/o1_mf_sys_undo_8ocl7q6c_.dbf
channel prmy4: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/testgg1/TESTGG1/datafile/o1_mf_users_8qbolh3g_.dbf
output file name=/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_sys_undo_8ocl7q6c_.dbf tag=TAG20130516T084335
channel prmy3: datafile copy complete, elapsed time: 00:00:45
output file name=/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_users_8qbolh3g_.dbf tag=TAG20130516T084335
channel prmy4: datafile copy complete, elapsed time: 00:00:55
output file name=/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_system_8ocl7bho_.dbf tag=TAG20130516T084335
channel prmy1: datafile copy complete, elapsed time: 00:01:05
output file name=/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_sysaux_8ocl7k6n_.dbf tag=TAG20130516T084335
channel prmy2: datafile copy complete, elapsed time: 00:01:05
Finished backup at 16-MAY-13

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=7 STAMP=815561103 file name=/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_system_8ocl7bho_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=815561104 file name=/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_sysaux_8ocl7k6n_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=815561104 file name=/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_sys_undo_8ocl7q6c_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=815561104 file name=/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_users_8qbolh3g_.dbf
Finished Duplicate Db at 16-MAY-13
released channel: prmy1
released channel: prmy2
released channel: prmy3
released channel: prmy4
released channel: stby

RMAN> **end-of-file**

RMAN> exit

As you can see, that was as easy as pie. Now we can just start the recovery process in the Standby Database. In this case, I used Active Dataguard so that I could show you that it is actually working, but just be aware that this is a Licensable Option.

SQL> alter database open;

Database altered.

SQL> alter database recover managed standby database disconnect;

Database altered.

SQL> select protection_mode,protection_level,database_role,name from v$database;

PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE NAME
-------------------- -------------------- ---------------- ---------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PHYSICAL STANDBY TESTGG1

SQL> select instance_name,host_name from v$instance;

INSTANCE_NAME HOST_NAME
---------------- ----------------------------------------------------------------
testgg2 dlabvm14.dlab.pythian.com

SQL> select * from sender.test;
select * from sender.test
 *
ERROR at line 1:
ORA-00942: table or view does not exist

Now, I go back to the primary and create a table called test in the schema sender. As you can see above, this table doesn’t exist in the Standby database.

SQL> select instance_name,host_name from v$instance;

INSTANCE_NAME HOST_NAME
---------------- ----------------------------------------------------------------
testgg1 dlabvm13.dlab.pythian.com

SQL> select protection_mode,protection_level,database_role,name from v$database;

PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE NAME
-------------------- -------------------- ---------------- ---------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PRIMARY TESTGG1

SQL> create table sender.test(id number);

Table created.

SQL> insert into sender.test values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

I go back to the Standby, and we can see that we have the table that we just created.

SQL> select protection_mode,protection_level,database_role,name from v$database;

PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE NAME
-------------------- -------------------- ---------------- ---------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PHYSICAL STANDBY TESTGG1

SQL> select instance_name,host_name from v$instance;

INSTANCE_NAME HOST_NAME
---------------- ----------------------------------------------------------------
testgg2 dlabvm14.dlab.pythian.com

SQL> select * from sender.test;

ID
----------
 1

There are various ways to see if this is working or not. You can use it in the following query to see what was the last archive received/applied in the Standby Database.

SQL> SELECT 'Last Applied : ' Logs,
TO_CHAR(next_time,'DD-MON-YY:HH24:MI:SS') TIME,thread#,sequence#
FROM v$archived_log
WHERE sequence# =
(SELECT MAX(sequence#) FROM v$archived_log WHERE applied='YES'
)
UNION
SELECT 'Last Received : ' Logs,
TO_CHAR(next_time,'DD-MON-YY:HH24:MI:SS') TIME,thread#,sequence#
FROM v$archived_log
WHERE sequence# =
(SELECT MAX(sequence#) FROM v$archived_log );

LOGS TIME THREAD# SEQUENCE#
---------------- ------------------ ---------- ----------
Last Applied : 16-MAY-13:11:42:52 1 65
Last Received : 16-MAY-13:11:42:52 1 65

While in the Primary Database, you can check what is the current sequence and what is the pending sequence to be applied.

SQL> SELECT
 (SELECT name FROM V$DATABASE
 ) name,
 (SELECT MAX (sequence#) FROM v$archived_log WHERE dest_id = 1
 ) Current_primary_seq,
 (SELECT MAX (sequence#)
 FROM v$archived_log
 WHERE TRUNC(next_time) > SYSDATE - 1
 AND dest_id = 2
 ) max_stby,
 (SELECT NVL (
 (SELECT MAX (sequence#) - MIN (sequence#)
 FROM v$archived_log
 WHERE TRUNC(next_time) > SYSDATE - 1
 AND dest_id = 2
 AND applied = 'NO'
 ), 0)
 FROM DUAL
 ) "To be applied",
 (
 (SELECT MAX (sequence#) FROM v$archived_log WHERE dest_id = 1
 ) -
 (SELECT MAX (sequence#) FROM v$archived_log WHERE dest_id = 2
 )) "To be Shipped"
FROM DUAL;

NAME CURRENT_PRIMARY_SEQ MAX_STBY To be applied To be Shipped
--------- ------------------- ---------- ------------- -------------
TESTGG1 65 65 0 0

I hope this little guide helps you out when you are trying to build your Physical Standby from an Active Duplicate. As always, test anything that I have said or mentioned before trying it in a production environment.