Creating a Physical Standby using RMAN DUPLICATE FROM ACTIVE DATABASE

Prior to Oracle Database 11g duplication was performed from an existing RMAN backup. Oracle Database 11g introduces Active database duplication which copies the live source database over the network without the need of an existing backup. As was the case in the backup based duplication, active duplication can be used to create a standby database.

This document will document the steps to use the RMAN command DUPLICATE FROM ACTIVE DATABASE to create a physical standby. Both the primary and standby servers have Oracle Enterprise Linux 5.4 64 bit with Oracle Enterprise Database 11g R2 base installed.

Primary database information
Host: ocm1.odlabs.net
DB_NAME = pritst
DB_UNIQUE_NAME=pritst

Standby database information
Host: ocm2.odlabs.net
DB_NAME = pritst
DB_UNIQUE_NAME = stbytst

Primary Preparations

Ensure primary database is in archive log mode

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/oradata/pritst/arch
Oldest online log sequence     2
Next log sequence to archive   4
Current log sequence           4
SQL>

If your output does not have Archive Mode for the Database log mode then you will need to configure your database for archive log mode. See the post Enable/Disable Archive Log Mode 10g/11g

Verify that forced logging is enabled and enable it if it not.

SQL> select force_logging from v$database;

FOR
---
NO

SQL> alter database force logging;

Database altered.

SQL>

Standby redo logs should be created in addition to the redo logs. The standby redo logs need to be as large as the largest redo log and there should be one extra group. On this system there are three log groups and the redo logs are 50MB in size so four standby redo logs will be added each at 50MB.

SQL> select group#, thread#, bytes/1024/1024
  2  from v$log;

    GROUP#    THREAD# BYTES/1024/1024
---------- ---------- ---------------
         1          1              50
         2          1              50
         3          1              50

SQL> alter database add standby logfile '/u01/app/oracle/oradata/pritst/stby_redo01.log' size 50M;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/pritst/stby_redo02.log' size 50M;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/pritst/stby_redo03.log' size 50M;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/pritst/stby_redo04.log' size 50M;

Database altered.

SQL>

Initialization Parameters needed for Dataguard

This section will detail the initialization parameters used in a dataguard configuration. The primary database will be configured in this section while the standby parameters will be set at the time of duplication.

The DB_NAME parameter will be pritst for the both the primary database and the standby database. The DB_UNIQUE_NAME will be pritst for the primary database and stbytst for the standby.

SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      pritst
SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      pritst
SQL>

The DB_UNIQUE_NAME will be used in the LOG_ARCHIVE_CONFIG parameter to enable the sending and receiving of redo logs to remote destinations.

SQL> show parameter LOG_ARCHIVE_CONFIG

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string
SQL> alter system set log_archive_config='DG_CONFIG=(pritst,stbytst)';

System altered.

SQL>

Set value for LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_DEST_2

SQL> show parameter log_archive_dest_1

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      LOCATION=/u01/app/oracle/orada
                                                 ta/pritst/arch
log_archive_dest_10                  string
log_archive_dest_11                  string
log_archive_dest_12                  string
log_archive_dest_13                  string
log_archive_dest_14                  string
log_archive_dest_15                  string
log_archive_dest_16                  string
log_archive_dest_17                  string
log_archive_dest_18                  string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_19                  string
SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/pritst/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pritst';

System altered.

SQL>

The redo transport mode is set in the parameter LOG_ARCHIVE_DEST_2. Below we configure redo transport mode for Maximum performance by setting ASYNC NOAFFIRM. Instead of a location a service name is provided to send the archive logs.

SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string
log_archive_dest_20                  string
log_archive_dest_21                  string
log_archive_dest_22                  string
log_archive_dest_23                  string
log_archive_dest_24                  string
log_archive_dest_25                  string
log_archive_dest_26                  string
log_archive_dest_27                  string
log_archive_dest_28                  string
log_archive_dest_29                  string
SQL> alter system set log_archive_dest_2='SERVICE=stbytst LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stbytst';

System altered.

SQL>

The FAL_SERVER specifies the fetch archive log (FAL) server for the standby database. The FAL_CLIENT specifies the FAL client name. Both are used by the FAL service. The FAL_SERVER is the primary and FAL_CLIENT is the standby.

SQL> alter system set fal_server=pritst;

System altered.

SQL> alter system set fal_client=stbytst;

System altered.

SQL>

Operating system file additions and deletions can be replicated to the physical standby database setting STANDBY_FILE_MANAGEMENT to auto.

SQL> alter system set standby_file_management=auto;

System altered.

SQL> 

Remote Login

SQL> alter system set remote_loging_passwordfile=exclusive;

System altered.

SQL> 

Network Connectivity

The primary database should be statically registered with its listener.

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

Add TNS Names Entries for both pritst and stbytst databases on both servers

[oracle@ocm2 dbs]$ cat /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora 
PRITST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ocm1.odlabs.net)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pritst)
    )
  )

STBYTST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ocm2.odlabs.net)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pritst)
    )
  )

[oracle@ocm2 dbs]$

Create Standby Shell

The work on the primary server is complete now the standby server needs to be configured. First start off by creating the directories on the files system to hold the data and archive log files along with the admin directories to support auditing.

[oracle@ocm2 ~]$ mkdir oradata
[oracle@ocm2 ~]$ mkdir oradata/pritst
[oracle@ocm2 ~]$ mkdir oradata/pritst/arch
[oracle@ocm2 ~]$ mkdir /u01/app/oracle/admin
[oracle@ocm2 ~]$ mkdir /u01/app/oracle/admin/pritst
[oracle@ocm2 ~]$ mkdir /u01/app/oracle/admin/pritst/adump
[oracle@ocm2 ~]$ mkdir /u01/app/oracle/admin/pritst/bdump
[oracle@ocm2 ~]$ mkdir /u01/app/oracle/admin/pritst/dpdump
[oracle@ocm2 ~]$ mkdir /u01/app/oracle/admin/pritst/pfile

Add the following line to /etc/oratab on the standby server.

pritst:/u01/app/oracle/product/11.2.0/dbhome_1:N

Create the listener and statically register the standby database.

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


LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ocm2.odlabs.net)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@ocm2 ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 09-JUL-2012 14:02:44

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

Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/ocm2/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ocm2.odlabs.net)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ocm2.odlabs.net)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                09-JUL-2012 14:02:44
Uptime                    0 days 0 hr. 0 min. 10 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/ocm2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ocm2.odlabs.net)(PORT=1521)))
Services Summary...
Service "pritst" has 1 instance(s).
  Instance "pritst", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@ocm2 ~]$ 

Also insure that the TNS entries for both the primary and standby are in the ORACLE_HOME/network/admin/tnsnames.ora file.

PRITST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ocm1.odlabs.net)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pritst)
    )
  )

STBYTST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ocm2.odlabs.net)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pritst)
    )
  )

Create a basic PFILE in ORACLE_HOME/dbs with the following parameters.

[oracle@ocm2 dbs]$ cat initpritst.ora 
DB_NAME=pritst
DB_UNIQUE_NAME=stbytst
[oracle@ocm2 dbs]$

The rest of the parameters will be set in the duplicate command and carried over from the primary.

Copy the password file from the primary server to the standby server

[oracle@ocm2 ~]$ scp oracle@ocm1:$ORACLE_HOME/dbs/orapwpritst $ORACLE_HOME/dbs

With the network configuration made and the PFILE and password file in place the standby instance can now be started in nomount.

[oracle@ocm2 ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 28 10:40:29 2011
 
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 
Connected to an idle instance.
 
SQL> startup nomount
ORACLE instance started.
 
Total System Global Area  146472960 bytes
Fixed Size                  1335080 bytes
Variable Size              92274904 bytes
Database Buffers           50331648 bytes
Redo Buffers                2531328 bytes
SQL> exit
[oracle@ocm2 ~]$

Make sure you exit after starting the database in nomount. The duplicate procedure will restart the database and an open connection can cause the process to fail.

Duplicate for standby

Below is the RMAN run command to perform the database duplication from and active database along with brief explanations for the options used in the duplicate.

FOR STANDBY – the duplicate is for use as a standby so a DBID change will not be forced.
FROM ACTIVE DATABASE – instructs RMAN to use the active target database instead of disk based backups.
DORECOVER – do recovery bringing the standby database up to the current point in time.
SPFILE – values for parameters specific to the auxiliary instance can be set here.
NOFILENAMECHECK – this option is added because the duplicate database files uses the same name as the source database.

[oracle@ocm2 ~]$ cat dupstby.cmd 
run {
 allocate channel pri1 type disk;
 allocate channel pri2 type disk;
 allocate channel prmy4 type disk;
 allocate auxiliary channel stby type disk;
 
 duplicate target database 
  for standby 
  from active database
  dorecover
  spfile
    set db_unique_name='stbytst'
    set control_files='/u01/app/oracle/oradata/pritst/control01.ctl','/u01/app/oracle/oradata/pritst/control02.ct'
    set fal_client='pritst'
    set fal_server='stbytst'
    set standby_file_management='AUTO'
    set log_archive_config='dg_config=(pritst,stbytst)'
    set log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/pritst/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stbytst'
    set log_archive_dest_2='service=pritst ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=pritst'
  nofilenamecheck;
  }

[oracle@ocm2 ~]$ 

On standby connect to both the target (pritst) and the auxiliary (stbytst) in RMAN.

[oracle@ocm2 ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jul 13 16:47:46 2012

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

RMAN> connect target sys/password@pritst 

connected to target database: PRITST (DBID=3720300117)

RMAN> connect auxiliary sys/password@stbytst

connected to auxiliary database: PRITST (not mounted)

RMAN> @dupstby.cmd

RMAN> run {
2>  allocate channel pri1 type disk;
3>  allocate channel pri2 type disk;
4>  allocate channel prmy4 type disk;
5>  allocate auxiliary channel stby type disk;
6>  
7>  duplicate target database 
8>   for standby 
9>   from active database
10>   dorecover
11>   spfile
12>     set db_unique_name='stbytst'
13>     set control_files='/u01/app/oracle/oradata/pritst/control01.ctl','/u01/app/oracle/oradata/pritst/control02.ct'
14>     set fal_client='pritst'
15>     set fal_server='stbytst'
16>     set standby_file_management='AUTO'
17>     set log_archive_config='dg_config=(pritst,stbytst)'
18>     set log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/pritst/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stbytst'
19>     set log_archive_dest_2='service=pritst ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=pritst'
20>   nofilenamecheck;
21>   }
using target database control file instead of recovery catalog
allocated channel: pri1
channel pri1: SID=42 device type=DISK

allocated channel: pri2
channel pri2: SID=36 device type=DISK

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

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

Starting Duplicate Db at 13-JUL-12

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

Starting backup at 13-JUL-12
Finished backup at 13-JUL-12

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

contents of Memory Script:
{
   sql clone "alter system set  db_unique_name = 
 ''stbytst'' comment=
 '''' scope=spfile";
   sql clone "alter system set  control_files = 
 ''/u01/app/oracle/oradata/pritst/control01.ctl'', ''/u01/app/oracle/oradata/pritst/control02.ct'' comment=
 '''' scope=spfile";
   sql clone "alter system set  fal_client = 
 ''pritst'' comment=
 '''' scope=spfile";
   sql clone "alter system set  fal_server = 
 ''stbytst'' 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=(pritst,stbytst)'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_dest_1 = 
 ''LOCATION=/u01/app/oracle/oradata/pritst/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stbytst'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_dest_2 = 
 ''service=pritst ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=pritst'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

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

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

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

sql statement: alter system set  fal_server =  ''stbytst'' 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=(pritst,stbytst)'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_dest_1 =  ''LOCATION=/u01/app/oracle/oradata/pritst/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stbytst'' comment= '''' scope=spfile

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

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1653518336 bytes

Fixed Size                     2213896 bytes
Variable Size                956303352 bytes
Database Buffers             687865856 bytes
Redo Buffers                   7135232 bytes
allocated channel: stby
channel stby: SID=17 device type=DISK

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

Starting backup at 13-JUL-12
channel pri1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_pritst.f tag=TAG20120713T165706 RECID=2 STAMP=788547466
channel pri1: datafile copy complete, elapsed time: 00:01:05
Finished backup at 13-JUL-12

Starting restore at 13-JUL-12

channel stby: copied control file copy
Finished restore at 13-JUL-12

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

sql statement: alter database mount standby database
RMAN-05538: WARNING: implicitly using DB_FILE_NAME_CONVERT

contents of Memory Script:
{
   set newname for tempfile  1 to 
 "/u01/app/oracle/oradata/pritst/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to 
 "/u01/app/oracle/oradata/pritst/system01.dbf";
   set newname for datafile  2 to 
 "/u01/app/oracle/oradata/pritst/sysaux01.dbf";
   set newname for datafile  3 to 
 "/u01/app/oracle/oradata/pritst/undotbs01.dbf";
   set newname for datafile  4 to 
 "/u01/app/oracle/oradata/pritst/users01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format 
 "/u01/app/oracle/oradata/pritst/system01.dbf"   datafile 
 2 auxiliary format 
 "/u01/app/oracle/oradata/pritst/sysaux01.dbf"   datafile 
 3 auxiliary format 
 "/u01/app/oracle/oradata/pritst/undotbs01.dbf"   datafile 
 4 auxiliary format 
 "/u01/app/oracle/oradata/pritst/users01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/pritst/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 13-JUL-12
channel pri1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/pritst/system01.dbf
channel pri2: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/pritst/sysaux01.dbf
channel prmy4: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/pritst/undotbs01.dbf
output file name=/u01/app/oracle/oradata/pritst/sysaux01.dbf tag=TAG20120713T165818
channel pri2: datafile copy complete, elapsed time: 00:01:25
channel pri2: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/pritst/users01.dbf
output file name=/u01/app/oracle/oradata/pritst/undotbs01.dbf tag=TAG20120713T165818
channel prmy4: datafile copy complete, elapsed time: 00:01:26
output file name=/u01/app/oracle/oradata/pritst/system01.dbf tag=TAG20120713T165818
channel pri1: datafile copy complete, elapsed time: 00:01:51
output file name=/u01/app/oracle/oradata/pritst/users01.dbf tag=TAG20120713T165818
channel pri2: datafile copy complete, elapsed time: 00:00:45
Finished backup at 13-JUL-12

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "/u01/app/oracle/oradata/pritst/arch/1_13_788094679.dbf" auxiliary format 
 "/u01/app/oracle/oradata/pritst/arch/1_13_788094679.dbf"   ;
   catalog clone archivelog  "/u01/app/oracle/oradata/pritst/arch/1_13_788094679.dbf";
   switch clone datafile all;
}
executing Memory Script

Starting backup at 13-JUL-12
channel pri1: starting archived log copy
input archived log thread=1 sequence=13 RECID=12 STAMP=788547629
output file name=/u01/app/oracle/oradata/pritst/arch/1_13_788094679.dbf RECID=0 STAMP=0
channel pri1: archived log copy complete, elapsed time: 00:00:35
Finished backup at 13-JUL-12

cataloged archived log
archived log file name=/u01/app/oracle/oradata/pritst/arch/1_13_788094679.dbf RECID=1 STAMP=788547664

datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=788547665 file name=/u01/app/oracle/oradata/pritst/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=788547665 file name=/u01/app/oracle/oradata/pritst/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=788547665 file name=/u01/app/oracle/oradata/pritst/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=788547665 file name=/u01/app/oracle/oradata/pritst/users01.dbf

contents of Memory Script:
{
   set until scn  1205479;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 13-JUL-12

starting media recovery

archived log for thread 1 with sequence 13 is already on disk as file /u01/app/oracle/oradata/pritst/arch/1_13_788094679.dbf
archived log file name=/u01/app/oracle/oradata/pritst/arch/1_13_788094679.dbf thread=1 sequence=13
media recovery complete, elapsed time: 00:00:00
Finished recover at 13-JUL-12
Finished Duplicate Db at 13-JUL-12
released channel: pri1
released channel: pri2
released channel: prmy4
released channel: stby

RMAN> 
RMAN> **end-of-file**

RMAN> 

On the standby start the managed recovery process.

[oracle@ocm2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 13 17:17:15 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

Database altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ocm2 ~]$ 

At this point the duplicate is complete along with the dataguard environment. You can validate that that logs are shipping and applying. On the standby issue the following query.

SQL> select sequence#, first_time, next_time, applied
  2  from v$archived_log
  3  order by sequence#;

 SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
	24 15-JUL-12 15-JUL-12 YES
	25 15-JUL-12 15-JUL-12 YES
	26 15-JUL-12 16-JUL-12 YES

3 rows selected.

SQL> 

On the primary database switch the logs a few times and use archive log list to information on the oldest, current and next log sequence.

[oracle@ocm1 ~]$ . oraenv
ORACLE_SID = [oracle] ? pritst
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle
[oracle@ocm1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 16 08:58:21 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> archive log list
Database log mode	         Archive Mode
Automatic archival	         Enabled
Archive destination	         /u01/app/oracle/oradata/pritst/arch/
Oldest online log sequence     28
Next log sequence to archive   30
Current log sequence	         30
SQL> 

On the primary we see that after the log switches the current log sequence is 30 and the next sequence to archive is 30. On the standby re-issue the query from above to see what sequences have been applied to the standby.

SQL> select sequence#, first_time, next_time, applied
  2  from v$archived_log
  3  order by sequence#;

 
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
	24 15-JUL-12 15-JUL-12 YES
	25 15-JUL-12 15-JUL-12 YES
	26 15-JUL-12 16-JUL-12 YES
	27 16-JUL-12 16-JUL-12 YES
	28 16-JUL-12 16-JUL-12 YES
	29 16-JUL-12 16-JUL-12 YES

6 rows selected.

SQL> 

From the output above we can see that the standby has received and applied log sequence 29.

14 thoughts on “Creating a Physical Standby using RMAN DUPLICATE FROM ACTIVE DATABASE”

  1. Great Work…Could you please post creation of Physical Standby with RMAN on Diffferent host with different directory structure??

  2. Hi Eric,

    Since several days, I’m going through your articles on ‘Oracle Distilled’. You have done a great work particularly on ‘RMAN’. I will visit this site frequently to learn more.

    Thanks a lot.

    Tirou

  3. Hi Eric,

    a typo :

    it is “alter system set remote_login_passwordfile=exclusive;”

    Cheers René

  4. hello there,
    i am getting this error while perfroming active duplicate.pl help me out of tis.

    Starting Duplicate Db at 09-JUL-14
    released channel: pri1
    released channel: pri2
    released channel: prmy4
    released channel: stby
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of Duplicate Db command at 07/09/2014 01:27:33
    RMAN-06217: not connected to auxiliary database with a net service name

    RMAN> **end-of-file**

  5. Hi,
    you made good presentation, however, you forgot you have not created Dataguard configuration, and nor started its daemon(DMON) plus you need to add database to the configuration.
    What you have now is a standby database..Dataguard recovery session would conflict with you last command:
    recover physical standby databsae disconnect from …

  6. To be precise, if you go ahead and create dataguard configuration, then you would get following error:
    ORA-01153: an incompatible media recovery is active.
    you will then have to cancel the current recovery session:
    alter database recover managed standby database cancel;

    Then issue a compatible recovery command for Dataguard as follows:
    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

    regards,

    nazir

  7. This is awesome document for learning for creating standy vis Rman.i am highly appreciating your time and efforts.

    Dileep Tripathi

  8. Hi,
    I am bit confuse here. At the start of ‘Create Standby Shell’ when creating directory structure, why creating complete directory structure again for primary database? Could you please explain on this.

    Thanks,
    Sumit

  9. I think no need to create directory structure on primary. Moreover Author also shone to create directory structure on DR server only

  10. Thanks… Nice article

    for RAC useres diable scan by running

    srvctl stop scan
    srvctl stop scan_listener
    create static listener

    & rock on 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *