Oracle 12c Dataguard Setup ( Physical Standby )

Create Standby Database using 12c DUPLICATE FROM ACTIVE DATABASE

Purpose

This note explains the procedure of creating a Physical Standby database using 12c RMAN DUPLICATE FROM ACTIVE DATABASE feature which is available since 11g Release 1 onwards. This enables us to create a physical standby database without having to take a backup of the primary database as a prerequisite step.

Environment

Primary Database DB_UNIQUE_NAME: dgtest1_pri
Standby Database DB_UNIQUE_NAME: dgtest1_sby

ORACLE_SID: dgtest1

Primary hostname: kphqdbrm01ord
Standby hostname: kphqdbrm02ord

Oracle software version: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit

Enable Force Logging on the Primary database

SQL> alter database force logging;

Database altered.

Create the password file on the Standby host

Note  - ensure that the same password is used as the one used while creating the password file on the Primary host

dgtest1:/app/oracle/product/12.2.0/dbhome_1/dbs> orapwd file=orapwdgtest1 password=Password1


Add Standby Log files on the primary

ALTER DATABASE ADD STANDBY LOGFILE ('/data/dgtest1/standby_redo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/data/dgtest1/standby_redo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/data/dgtest1/standby_redo03.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/data/dgtest1/standby_redo04.log') SIZE 50M;


Update network configuration files

Add the following entries to the tnsnames.ora file on both Primary as well as Standby hosts. 


Please Note : This becomes necessary only if - The listener has been configured to run from the ASM home on the server, so we would need to update the tnsnames.ora file in both the database as well as ASM Oracle homes on both machines.
If do not have ASM here so no need to worry


dgtest1_pri =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = kphqdbrm01ord)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dgtest1_pri)
    )
  )

dgtest1_sby =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = kphqdbrm02ord)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dgtest1_sby)
    )
  )

On the Standby host, add a static entry in the listener.ora file and reload or restart the listener.

LISTENER_DG =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = kphqdbrm02ord.netr.ecis.police.uk)(PORT = 1522))
    )
  )

SID_LIST_LISTENER_DG =
  (SID_LIST =
        (SID_DESC =
        (GLOBAL_DBNAME = dgtest1_sby)
        (ORACLE_HOME = /u01/oracle/product/11.1.0/db_1)
        (SID_NAME = dgtest1)
        )
)

The database initially had a service_names value of 'dgtest1'.  We have defined the network configuration files using distinct service names to match the db_unique_name values on both Primary as well as Standby locations. We now need to change the service_names parameter and then we can test connectivity from the using the TNS aliases that we set up in the earlier step.

SQL> alter system set service_names='dgtest1_pri' scope=both;

System altered.


SQL> show parameter service

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      DGTEST1_PRI



Test the connection from primary

/home/oracle=> lsnrctl status listener_dg

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 18-JAN-2018 12:15:32

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=kphqdbrm01ord.netr.ecis.police.uk)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     listener_dg
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                11-JAN-2018 11:22:38
Uptime                    7 days 0 hr. 52 min. 53 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /app/oracle/diag/tnslsnr/kphqdbrm01ord/listener_dg/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=kphqdbrm01ord.netr.ecis.police.uk)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
Services Summary...
Service "dgtest1_pri" has 1 instance(s).
  Instance "dgtest1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
/home/oracle=> sqlplus system/Password1@dgtest1_pri

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 18 12:15:39 2018

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

Last Successful login time: Thu Dec 21 2017 10:37:26 +00:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select host_name from v$instance;

HOST_NAME
----------------------------------------------------------------
kpxxxxx01ord


Create a "scratch" init.ora file on the Standby host with just a single parameter

dgtest1:/app/oracle/product/12.2.0/dbhome_1/dbs> vi initdgtest1.ora

"initdgtest1.ora" [New file]
DB_NAME=dgtest1


Create the required directories on the Standby host

Check the value of the parameter audit_file_dest  on the Primary database. Ensure that this directory structure also exists on the Standby host. If the directory structure differs in any way on the Primary and Standby server, we will have to ensure that the RMAN rcv file to create the Standby database is amended to reflect this as well.

On Primary database:

SQL> show parameter audit_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /app/oracle/admin/dgtest1/adump
 
On Standby host:
dgtest1:/app/oracle/admin> mkdir dgtest1
dgtest1:/app/oracle/admin> cd dgtest1
dgtest1:/app/oracle/admin/dgtest1> mkdir adump
Create the active_standby.rcv file
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET DB_UNIQUE_NAME='dgtest1_sby'
SET LOG_ARCHIVE_DEST_2='service=dgtest1_pri LGWR ASYNC REGISTER VALID_FOR=(online_logfile,primary_role)'
Set STANDBY_FILE_MANAGEMENT='AUTO'
SET FAL_SERVER='dgtest1_pri'
SET FAL_CLIENT='dgtest1_sby'
NOFILENAMECHECK;
 
Start the Standby database instance in NOMOUNT state
From the Standby host, run the following RMAN command to create the Standby Database
rman target sys/Password1@dgtest1_pri auxiliary sys/Password1@dgtest1_sby cmdfile=active_standby.rcv log=cre_actv_stndby.log

Change the init.ora parameters related to redo transport and redo apply
On primary :

SQL> alter system set fal_server='dgtest1_sby' scope=both;
System altered.
SQL> alter system set fal_client='dgtest1_pri' scope=both;
System altered.
SQL> alter system set standby_file_management=AUTO scope=both;
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dgtest1_pri';

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=dgtest1_sby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dgtest1_sby';

Shutdown the Standby database, add the Standby log files and then start real time recovery

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
SQL> alter database add standby logfile group 4 size 500m;
Database altered.
SQL> alter database add standby logfile group 5 size 500m;
Database altered.
SQL> alter database add standby logfile group 6 size 500m;
Database altered.
SQL>  alter database add standby logfile group 7 size 500m;
Database altered.


 SQL > alter database recover managed standby database disconnect from session;
Media recovery complete.
 or 
SQL> recover managed standby database using current logfile disconnect;