Uncategorized

Physical Standby Build using RMAN Active Duplicate

February 11, 2017

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.