Oracle Database Blog: Experiments & Learnings

August 12, 2011

11gR2 Physical Standby Setup : Primary Database 2 Node RAC and Standalone Standby Database

The following demonstration is a step by step procedure to create a hybrid Disaster Recovery environment :  Primary database is 2 node RAC and Standby database is Standalone. This procedure gives a general idea and there are some steps that can be optimized.

Environment

Primary Database 2 Node RAC

Node Names: node1, node2
DB Name: ORCL
DB Unique Name: ORCL
DB Version: 11.2.0.1
Grid Infrastructure (CRS + ASM).
SCAN settings in /etc/hosts file, so SCAN listener only running on one node (Node2 for this demo)
ASM Diskgroups: +DATA, +FRA

Standby Database Single Instance:

ASM Diskgroup : +DG_DATA   (For datafiles  and FRA)
DB Unique Name: STDBY
DB Name: ORCL

Preparations at the Primary Site

GI_HOME=/u02/app/oracle/11.2.0/grid

ORACLE_HOME=/u02/app/oracle/product/11.1.0/db_1

Ensure force logging is enabled. Then following steps are needed:

a.) Dedicated Listener for duplication on Primary Site

b.) Prepare initialization Parameters

c.) Add Standby redo logs.

Why Dedicated listener for Duplicate database command ?

When duplicate database command is issued from RMAN, it copies the password file from Primary database host to Standby database host.  If this operation is performed from remote client (with tnsnames.ora having SCAN name), it would cause issues when password file gets copied from the Primary host to Standby host. This is because SCAN directs connection to a node based on load.  Here is the error:

RMAN>  duplicate target database for standby from active database;

Starting Duplicate Db at 12-AUG-11
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=26 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u02/app/oracle/product/11.1.0/db_1/dbs/orapwORCL1' auxiliary format
 '/u02/app/oracle/product/11.1.0/db_1/dbs/orapwSTDBY'   ;
}
executing Memory Script

Starting backup at 12-AUG-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 instance=ORCL2 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 08/12/2011 14:38:59
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 08/12/2011 14:38:59
ORA-19505: failed to identify file "/u02/app/oracle/product/11.1.0/db_1/dbs/orapwORCL1"
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

To Avoid this, create a dedicated Listener on a source node [node1] for this operation.

Here are the Settings from both nodes:

Node 1

</span>
<pre>[root@node1 bin]# ./srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1530
[root@node1 bin]# ./srvctl config scan
SCAN name: cluster1, Network: 1/192.168.10.0/255.255.255.0/
SCAN VIP name: scan1, IP: /cluster1.home.com/192.168.10.30

SQL> show parameter listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
listener_networks                    string
local_listener                       string      (DESCRIPTION=(ADDRESS_LIST=(AD
                                                 DRESS=(PROTOCOL=TCP)(HOST=192.
                                                 168.10.9)(PORT=1521))))
remote_listener                      string      cluster1.home.com:1530

[oracle@node1 admin]$ ps -ef |grep tns
oracle    7072     1  0 10:19 ?        00:00:00 /u02/app/11.2.0/grid/bin/tnslsnr LISTENER -inherit
oracle   12670  8486  0 10:51 pts/1    00:00:00 grep tns
[oracle@node1 admin]$ lsnrctl status listener

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 12-AUG-2011 10:52:04

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                12-AUG-2011 10:19:01
Uptime                    0 days 0 hr. 33 min. 3 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u02/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u02/app/oracle/diag/tnslsnr/node1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.2)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.8)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "ORCL" has 1 instance(s).
  Instance "ORCL1", status READY, has 1 handler(s) for this service...
The command completed successfully

Node 2

[oracle@node2 ~]$ ps -ef |grep tns
oracle    5401     1  0 10:18 ?        00:00:00 /u02/app/11.2.0/grid/bin/tnslsnr LISTENER -inherit
oracle    5403     1  0 10:18 ?        00:00:00 /u02/app/11.2.0/grid/bin/tnslsnr LISTENER_SCAN1 -inherit
oracle    7684  7135  0 10:52 pts/1    00:00:00 grep tns
Local Listener: on port 1521
SCAN Listener: on port 1530

With SCAN listener in place, client’s tnsnames.ora file looks like:

orcl=
        (description=
                (address=(port=1530)(host=cluster1.home.com)(protocol=tcp))
        (connect_data=
        (service_name=ORCL))
        )

As mentioned above, duplicate database command fails in case the command is issued from a client with tnsnames.ora file entry as above.  To workaround this, create a dedicated Listener on a source node [node1] for this operation. Lets configure ORCL1 that runs from DB_HOME.

[oracle@node1 admin]$ lsnrctl start ORCL1

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 12-AUG-2011 14:47:54

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

Starting /u02/app/oracle/product/11.1.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u02/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
Log messages written to /u02/app/oracle/product/11.1.0/db_1/log/diag/tnslsnr/node1/orcl1/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node1.home.com)(PORT=1523)))

So, the remote client’s tnsnames.ora file would look like:

orcl=
        (description=
                (address=(port=1530)(host=cluster1.home.com)(protocol=tcp))
        (connect_data=
        (service_name=ORCL))
        )

STDBY=
        (description=
                (address=(port=1522)(host=node1.home.com)(protocol=tcp))
        (connect_data=
        (service_name=STDBY))
        )
ORCL1=
        (description=
                (address=(port=1523)(host=node1.home.com)(protocol=tcp))
        (connect_data=
        (service_name=ORCL1))
        )

Initialization Parameters On Primary

Set Archiving Destinations and defer 2nd destination for the time being to avoid any errors.

SQL> alter system set log_archive_dest_1='LOCATION=+FRA valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=ORCL';

System altered.
SQL> alter system set
2 log_archive_dest_2='service=stdby lgwr sync Valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=STDBY';

System altered.
SQL> alter system set log_archive_dest_state_2=defer;

System altered.
SQL> alter system set standby_file_management=auto;

System altered.

Add Standby Redo Logs on the Primary

SQL> alter database add standby logfile thread 1 '+FRA';

Database altered.

SQL> alter database add standby logfile thread 1 '+FRA';

Database altered.

SQL> alter database add standby logfile thread 2 '+FRA';

Database altered.

SQL> alter database add standby logfile thread 2 '+FRA';

Database altered.

Preparations at the Standby Site

a.) Configure Listener

b.) Initialization parameters

Listener for Standby database has to be configured.  For this Demo, following has been configured:

[oracle@node1 admin]$ cat listener.ora
STDBY=
        (description=
                (address=(protocol=tcp)(host=node1.home.com)(port=1522))
        )
SID_LIST_STDBY=
        (SID_LIST=
        (SID_DESC=
        (SID_NAME=STDBY)
        (ORACLE_HOME=/u02/app/oracle/product/11.1.0/db_1))
        )

Initialization Parameters On Standby

db_name=ORCL
db_unique_name=STDBY
cluster_database=false
fal_server=orcl
fal_client=stdby
log_archive_dest_1='location=+DG_DATA valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STDBY'
log_archive_dest_2='service=orcl valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL'
compatible='11.1.0.0.0'
db_create_file_dest='+DG_DATA'
db_create_online_log_dest_1='+DG_DATA'

Since OMF is in use, we just Specify DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n parameters.  There is no need to specify DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT parameters. Also note that COMPATIBLE parameter has to be set explicitly in order to avoid errors during Duplicate database command.

Duplication

a.) Copy password file from primary host to Standby host and then rename it on standby host. This is just to ensure that duplication can work from a remote client. Otherwise, password file is copied as a part of duplication process.

$ scp orapwORCL1  node1:$ORACLE_HOME/dbs

$ mv orapwORCL1 orapwSTDBY

Beware:  If you are explicitly creating password file on standby site, it would give errors while shipping the logs. Only copying of password file would work.

Run the duplicate command:

</span>
<pre>[oracle@node1 dbs]$ rman target sys/Oracle123@orcl1 auxiliary sys/Oracle123@stdby

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Aug 12 15:29:11 2011

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

connected to target database: ORCL (DBID=1286546160)
connected to auxiliary database: ORCL (not mounted)

RMAN> duplicate target database for standby from active database;

Starting Duplicate Db at 12-AUG-11
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=25 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u02/app/oracle/product/11.1.0/db_1/dbs/orapwORCL1' auxiliary format
 '/u02/app/oracle/product/11.1.0/db_1/dbs/orapwSTDBY'   ;
}
executing Memory Script

Starting backup at 12-AUG-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 instance=ORCL1 device type=DISK
Finished backup at 12-AUG-11

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '+DG_DATA/stdby/controlfile/current.256.758993371';
   sql clone "create spfile from memory";
   shutdown clone immediate;
   startup clone nomount;
   sql clone "alter system set  control_files = ''+DG_DATA/stdby/controlfile/current.256.758993371'' comment=
                                                ''Set by RMAN'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Starting backup at 12-AUG-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u02/app/oracle/product/11.1.0/db_1/dbs/snapcf_ORCL1.f tag=TAG20110812T152936 RECID=6 STAMP=758993377
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
Finished backup at 12-AUG-11

sql statement: create spfile from memory

Oracle instance shut down

connected to auxiliary database (not started)
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 statement: alter system set  control_files =   ''+DG_DATA/stdby/controlfile/current.256.758993371''
comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
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

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 clone tempfile  1 to new;
   switch clone tempfile all;
   set newname for clone datafile  1 to new;
   set newname for clone datafile  2 to new;
   set newname for clone datafile  3 to new;
   set newname for clone datafile  4 to new;
   backup as copy reuse
   datafile  1 auxiliary format new
   datafile  2 auxiliary format new
   datafile  3 auxiliary format new
   datafile  4 auxiliary format new
   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DG_DATA in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 12-AUG-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/orcl/datafile/system.256.758765445
output file name=+DG_DATA/stdby/datafile/system.257.758993419 tag=TAG20110812T153017
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:26
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/orcl/datafile/sysaux.284.758765459
output file name=+DG_DATA/stdby/datafile/sysaux.258.758993507 tag=TAG20110812T153017
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/orcl/datafile/undotbs1.285.758765475
output file name=+DG_DATA/stdby/datafile/undotbs1.259.758993583 tag=TAG20110812T153017
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/orcl/datafile/undotbs2.287.758768259
output file name=+DG_DATA/stdby/datafile/undotbs2.260.758993599 tag=TAG20110812T153017
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 12-AUG-11

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=758993609 file name=+DG_DATA/stdby/datafile/system.257.758993419
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=758993609 file name=+DG_DATA/stdby/datafile/sysaux.258.758993507
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=758993609 file name=+DG_DATA/stdby/datafile/undotbs1.259.758993583
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=758993609 file name=+DG_DATA/stdby/datafile/undotbs2.260.758993599
Finished Duplicate Db at 12-AUG-11

Now enable the managed recovery on Standby host:

SQL> recover managed standby database disconnect from session;
 Media recovery complete.

And we are done. Few checks done to check the setup:

On Primary Site:

SQL> -- edited later to get output from GV$ view

SQL> select INST_ID,DEST_NAME,STATUS,RECOVERY_MODE,ARCHIVED_THREAD#,ARCHIVED_SEQ#,APPLIED_THREAD#,APPLIED_SEQ#,DB_UNIQUE_NAME
  2  from GV$ARCHIVE_DEST_STATUS
  3  where DEST_NAME in ('LOG_ARCHIVE_DEST_2');

INST_ID DEST_NAME            STATUS    RECOVERY ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ# DB_UNIQUE
------- -------------------- --------- -------- ---------------- ------------- --------------- ------------ ---------
   1    LOG_ARCHIVE_DEST_2   VALID     MANAGED                 1            25               2           24 STDBY
   2    LOG_ARCHIVE_DEST_2   VALID     MANAGED                 1            25               2           24 STDBY

On the Standby Site:

SQL>  select thread#,sequence#,applied from v$archived_log order by sequence#;

   THREAD#  SEQUENCE# APPLIED
---------- ---------- ---------
         2          7 YES
         2          8 YES
         2          9 YES
         2         10 YES
         2         11 YES
         2         12 YES
         2         13 YES
         2         14 YES
         2         15 YES
         2         16 YES
         1         20 YES
         1         21 YES
         1         22 YES
         1         23 YES
         1         24 YES
...
SQL> -- Output cut short for readability.
Advertisements

4 Comments »

  1. Hi Manroy,

    Thanks for sharing. Can you please help me with the following issue.

    I have the same HW configuration (2 node Rac cluster, DB_name=ORCL) and 1 node for standby. Oracle SW is 11.2.0.1.0 everywhere.
    I try to set up the standby DB (DB_NAME=ORCLS) since a few days without success. The Duplicate command fails at the very beginning (see below).

    After reading your blog i created a dedicated listener ORCLP (on the first node raclinux1) and try the RMAN DUPLICATE command
    again but it resulted in the same error. The error message is not very specific and I could not find any
    help on google.

    Please note that with the cmd “sqlplus sys/passwd@orclp as sysdba” i can connect no problem to ORCL1 from all the 3 nodes.

    Hoping you can help me with this issue. Many thanks in advance.
    Regards
    Tran

    [oracle@raclinux1 admin]$ rman target sys/passwd@orclp auxiliary sys/passwd@orcls


    connected to target database: ORCL (DBID=1301828355)
    connected to auxiliary database: ORCLS (not mounted)

    RMAN> duplicate target database for standby from active database;

    Starting Duplicate Db at 13-MAR-12
    using target database control file instead of recovery catalog
    allocated channel: ORA_AUX_DISK_1
    channel ORA_AUX_DISK_1: SID=26 device type=DISK

    contents of Memory Script:
    {
    backup as copy reuse
    targetfile ‘/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwORCL1’ auxiliary format
    ‘/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwORCLS’ ;
    }
    executing Memory Script

    Starting backup at 13-MAR-12
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=34 instance=ORCL1 device type=DISK
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of Duplicate Db command at 03/13/2012 07:19:28
    RMAN-03015: error occurred in stored script Memory Script
    RMAN-03009: failure of backup command on ORA_DISK_1 channel at 03/13/2012 07:19:28
    ORA-17627: ORA-12577: Message 12577 not found; product=RDBMS; facility=ORA

    Comment by Tran — March 13, 2012 @ 12:35 pm

    • Hello Tran,

      Looks like this is due to the wordsize difference between primary and standby server. Have a look at MOS note: 1344615.1

      Regards,
      Saurabh Manroy

      Comment by Saurabh Manroy — March 13, 2012 @ 2:02 pm

      • Hi Manroy,

        Thanks for prompt reply. I unfortunately do not have access to MOS. I’m doing for self training.

        Actually the RAC servers are virtual machines which were installed using Oracle VM template OVM_EL5U4_X86_64_PVM_4GB OS-wise and using template OVM_EL5U4_X86_64_11201RAC_PVM for Clusterware and RAC DB

        The standby server is also Oracle installed using the same above OS VM template. But grid and Oracle 11gR2 were installed manually from downloaded zip files. They all have the same version and all run on my laptop (thus no hardware incompatibility).

        I’m wondering if I use one of the cluster node as standby for testing purpose, maybe I have a better chance to make it work.

        Regards
        Tran.

        Comment by Tran — March 13, 2012 @ 4:16 pm

  2. Hi Manory,

    How to perform switchover or failover from standalone standby to 2 node rac?
    Primary is 2 node rac & standby is standalone and I’ve to perform switchover/failover.

    Comment by SOMAR — July 1, 2015 @ 9:00 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: