Creating a Physical Standby Single Instance using RMAN DUPLICATE FROM ACTIVE DATABASE

Creating a Physical Standby Single Instance using RMAN DUPLICATE FROM ACTIVE DATABASE

By Arif Malik
Categories: Data Guard
Tagged: active, clone, duplicate
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
     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>
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.