Physical Standby Build using RMAN Active Duplicate

Overview:

In this article we explain the process of  build a physical standby database using RMAN active duplicate.

Standby database is exact copy of production in read only mode with apply. In case if the production goes down for any outage. Standby can failover and make as primary. 

Environment Details

Hostname	Primary server :–Primary Standby Server :– Standby
Operating system	Linux
Database Environment	NSMPRD and NSMDR

NSMPRD@PRIMARY

Determine if the FORCE LOGGIN is enabled.

SQL> select force_logging from v$database;

For

------

YES

If the output of the query is YES, then proceed with the next step. If the output of the above query is NO then enable the FORCE LOGGING at the database level.

SQL> alter database force logging;

 Set the primary database initialization parameters

SQL> SHOW PARAMETER DB_NAME

NAME                       TYPE                        VALUE

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

db_name                    string                    nsmprd

SQL> ALTER SYSTEM SET log_archive_config='dg_config=(NSMPRD,NSMDR)';

   

System altered

SQL> show parameter log_archive_config

NAME                    TYPE                        VALUE

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

log_archive_config      string            'dg_config=(NSMPRD,NSMDR)

  

Set the archive loctions on primary 

SQL> alter system set log_archive_dest_1='LOCATION=/u01/oraarch1/nsmprd valid_for=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=nsmprd REOPEN=30 MAX_FAILURE=3 scope=both;

SYSTEM ALTERED

SQL> alter system set log_archive_dest_2='service="nsmdr"','LGWR SYNC AFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=10 db_unique_name="nsmdr" net_timeout=30','template="/u02/oraarch2/nsmdr_standby/LOG_%r_%s_%t.ARC" valid_for=(all_logfiles,primary_role)' scope=both;

System altered

SQL> alter system set log_archive_dest_state_3='ENABLE';

System altered

SQL> alter system fal_client= NSMPRD;

System altered

SQL> alter system fal_server= NSMDR;

System altered

SQL> Show parameter fal

NAME                    TYPE                        VALUE

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

fal_client           string                         NSMPRD

fal_server           sting                         NSMDR

SQL> ALTER SYSTEM SET log_archive_dest_state_2=defer;

System altered

These parameters are dynamic and will take effect immediately. Check the parameter to make sure it points to the correct locations as specified.

SQL> SHOW PARAMETER LOG_ARCHIVE_DEST_2

NAME                    TYPE                        VALUE

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

LOG_ARCHIVE_DEST_2    string                 service="nsmdr"','LGWR  

                                             SYNC AFFIRM delay=0

                                             Optional 

                                             compression=disable

                                            max_failure=0

                                            max_connections=1

                                            reopen=10                                             db_unique_name="nsmdr"                                            

net_timeout=30','template="/u02/oraarch2/nsmdr_standby/LOG_%r_%s_%t.ARC" valid_for=(all_logfiles,primary_role)'

SQL> SHOW PARAMETER REMOTE_LOGIN_PASSWORDFILE  ( this must be set to “EXCLUSIVE” (default))

NAME                        TYPE                  VALUE

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

REMOTE_LOGIN_PASSWORDFILE  string                EXCLUSIVE            

Create standby redo log files on the primary database.

SQL>column member format a50

SQL>SELECT a.group#, a.member, b.bytes/1024/1024 bytes_MB

FROM v$logfile a, v$log b WHERE a.group# = b.group#

Union select a.group#, a.member, b.bytes/1024/1024 bytes_MB

from v$logfile a, v$standby_log b  WHERE a.group# = b.group#;

GROUP#                     MEMBER                BYTES_MB

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

1     /u01/oradata1/nsmprd/nsmprd_redo01.log    50MB

2     /u01/oradata1/nsmprd/nsmprd_redo02.log    50MB

3     /u01/oradata1/nsmprd/nsmprd_redo03.log    50MB

4     /u01/oradata1/nsmprd/nsmprd_redo04.log    50MB

SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5  '/u01/oradata1/nsmprd/nsmprd_sb_redo05.log' SIZE 50M;

Database altered

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6  '/u01/oradata1/nsmprd/nsmprd_sb_redo06.log' SIZE 50M;

Database altered

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7  '/u01/oradata1/nsmprd/nsmprd_sb_redo07.log' SIZE 50M;

Database altered

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 8  '/u01/oradata1/nsmprd/nsmprd_sb_redo08.log' SIZE 50M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 9  '/u01/oradata1/nsmprd/nsmprd_sb_redo09.log' SIZE 50M;

Database altered

SQL>column member format a50

SQL>SELECT a.group#, a.member, b.bytes/1024/1024 bytes_MB

FROM v$logfile a, v$log b WHERE a.group# = b.group#

Union select a.group#, a.member, b.bytes/1024/1024 bytes_MB

from v$logfile a, v$standby_log b  WHERE a.group# = b.group#;

GROUP#                     MEMBER                BYTES_MB

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

1     /u01/oradata1/nsmprd/nsmprd_redo01.log       50MB

2     /u01/oradata1/nsmprd/nsmprd_redo02.log       50MB

3     /u01/oradata1/nsmprd/nsmprd_redo03.log       50MB

4     /u01/oradata1/nsmprd/nsmprd_redo04.log       50MB

5     /u01/oradata1/nsmprd/nsmprd_sb_redo05.log    50MB

6     /u01/oradata1/nsmprd/nsmprd_sb_redo06.log    50MB

7     /u01/oradata1/nsmprd/nsmprd_sb_redo07.log    50MB

8     /u01/oradata1/nsmprd/nsmprd_sb_redo08.log    50MB

9     /u01/oradata1/nsmprd/nsmprd_sb_redo09.log    50MB

Verify the standby redo log file groups were created (do this after the creation of standby database)

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

    GROUP#    THREAD#  SEQUENCE# ARC STATUS

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

         5          0          0 YES UNASSIGNED

         6          0          0 YES UNASSIGNED

         7          0          0 YES UNASSIGNED

         8          0          0 YES UNASSIGNED

         9          0          0 YES UNASSIGNED

Configuring TNS for primary and physical standby database:

NSMPRD@PRIMARY

NSMDR =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = nsmdr)

    )

  )

NSMDR@STANDBY

NSMPRD =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1 )(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = nsmprd)

    )

  )

NSMDR =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = nsmdr)

    )

  )

LISTENER_NSMDR=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.2)(PORT=1521))

Creating the physical standby database:

NSMDR@STANDBY

Configure listener entry for the standby database in the Listener.ora file on standby server.

$cat listener.ora

SID_LIST_LISTENER =

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=nsmdr)

(ORACLE_HOME= /u01/app/oracle/product/11.2.0.4/db)

(SID_NAME=nsmdr))

 )

$tnsping nsmprd

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 05-FEB-2017 09:42:43

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:

/u01/app/oracle/product/11.2.0.4/db/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = nsmprd)))

OK (0 msec)

$tnsping nsmdr

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 05-FEB-2017 09:42:51

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:

/u01/app/oracle/product/11.2.0.4/db/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = nsmdr)))

OK (0 msec)

NSMPRD@PRIMARY

create password file on Primary 

$orapwd file=orapwnsmprd password=******  force=y entries=20

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

$scp $ORACLE_HOME/dbs/ orapwnsmprd oracle@STANDBY: $ORACLE_HOME/dbs

 NSMDR@STANDBY

In the $ORACLE_HOME/dbs directory of the standby database server create an initialization parameter file named initnsmdr.ora containing only one parameter DB_NAME.

$cd $ORACLE_HOME/dbs

$vi initnsmdr.ora

DB_NAME= NSMPRD

:wq (save and exit the file)

On the standby database server go to ORACLE_BASE/admin and create a directory as your DB_UNIQUE_NAME.

$ cd $ORACLE_BASE/admin

$mkdir NSMDR

On the standby server se the ORACLE_SID for the standby database.

$vi /etc/oratab       -->file and entry as below

nsmdr:/u01/app/oracle/product/11.2.0.4/db:N

$ . oraenv

ORACLE_SID = [oracle] ? nsmdr

The Oracle base remains unchanged with value /u01/app/oracle

$ echo $ORACLE_SID=nsmdr

nsmdr

Connect as sysdba and start the database in NOMOUNT state.

SQL> STARTUP NOMOUNT PFILE=’/u01/app/oracle/product/11.2.0.4/db/dbs/ initnsmdr.ora’;

Total System Global Area 225509376 bytes

Fixed Size                 2252896 bytes

Variable Size              167773064 bytes

Redo Buffers                 5152768 bytes

SQL> EXIT

NSMPRD@PRIMARY

On the primary database server set the ORACLE_SID for primary database.

$ echo $ORACLE_SID

nsmprd

On the primary database server CHECK RMAN CONNECTION and connect to the primary database and auxiliary database as sys.

$ rman

RMAN>connect target sys

target database Password:

connected to target database : NSMPRD (DBID=139468621)

RMAN>connect auxiliary sys@nsmdr  ( Make sure listener is running on standby)

auxiliary database Password:

connected to auxiliary database: NSMDR(not mounted)

RMAN>

 Now execute the below script from RMAN on the primary database server. When this script finishes you will have a new standby database that was created over the network without any interim storage.

RMAN>duplicate target database for standby from active database

spfile

SET audit_file_dest=' /u01/oraadmin1/nsmdr/adump’

SET DB_UNIQUE_NAME 'NSMDR'

SET log_archive_dest_2 ''

SET log_archive_dest_3 ''

SET dg_broker_config_file1 ''

SET dg_broker_config_file2 ''

SET dg_broker_start 'FALSE'

nofilenamecheck;

After DR restore please set the standby parameters.

NSMDR@STANDBY

 Update standby database parameter.

SQL> ALTER SYSTEM SET log_archive_config='dg_config=(NSMPRD,NSMDR)';

System altered

SQL> ALTER SYSTEM SET log_archive_dest_state_2=defer;

System altered

ALTER SYSTEM SET log_archive_dest_2='service="nsmprd"','LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="nsmprd" net_timeout=30','template="/u01/oraarch2/nsmprd_standby/LOG_%r_%s_%t.ARC" valid_for=(all_logfiles,primary_role)' scope=both;

System altered

SQL> alter system set fal_client= NSMDR;

System altered

SQL> alter system set fal_server= NSMPRD;

System altered

Enable the log_archive_dest_state_2 on primary

SQL> ALTER SYSTEM SET log_archive_dest_state_2=ENABLE;

System altered

Perform a log switch on the primary database and redo will start being sent to the standby.

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered

SQL>/

System altered

On the standby database set the database SID and start the manage recovery process.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Database altered

Verify that the standby database is performing correctly:

On the primary database switch the logfile.

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered

Check the log sequence on the primary database.

SQL>  SELECT THREAD#, MAX(SEQUENCE#) FROM V$LOG_HISTORY GROUP BY THREAD#;

THREAD#   MAX(SEQUENCE#)

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

     1    199

  On the standby database execute the above SQL statement to check the log sequence.

SQL>  SELECT THREAD#, MAX(SEQUENCE#) FROM V$LOG_HISTORY GROUP BY THREAD#;

THREAD#   MAX(SEQUENCE#)

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

     1    198

SQL> select PROCESS,THREAD#,SEQUENCE#,STATUS from v$managed_standby where process='MRP0';

PROCESS                        THREAD#  SEQUENCE# STATUS

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

MRP0                                 1    199     WAIT_FOR_LOG

Conclusion

In this article we have learnt the steps to build a physical standby database using RMAN active duplicate.