Convert Physical Standby to Snapshot Standby using Data Guard Broker

Overview:

This article explain the process of converting Physical Standby which is in Read Only mode to a Snapshot Standby which would be in Read Write mode for the needs of disaster recovery testing.  This is to allow the database in DR site to be accessed by applications and perform tests on how the database could be accessed and application purposes be met during a disaster scenario while the actual primary database is not shutdown completely.

Environment Details:

Hostname	Node1 and Node2
Operating system	Linux
Environment	Production(orcl01 / orcl02) and Standby(orcldr01/orcldr02)
Oracle Home	/u01/app/oracle/product/11.2.0/db

Prerequisites for converting the database

• Ensure the FRA is configured.

SQL> sho parameter recover

Note:- Snapshot standby need not have Flashback enabled at db level but if it is enabled then FRA would have already been configured

• Ensure the redo transport parameters for the primary and redo apply for the standby are configured:

SQL> set lin 999

SQL> column NAME format a30

SQL> column VALUE format a84

SQL> select NAME, VALUE from v$parameter

where NAME in ('db_name', 'db_unique_name', 'control_files', 'log_archive_config','fal_client', 'fal_server',

'log_archive_dest_1', 'log_archive_dest_2', 'log_archive_dest_3', 'log_archive_dest_4',

'log_archive_dest_state_1','log_archive_dest_state_2','log_archive_dest_state_3', 'log_archive_dest_state_4','log_file_name_convert', 'remote_login_passwordfile');

• Ensure the standby redo log files are configured on the primary and standby 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#;

Checks on the day of planned DR Test

• Run the following query to pull information about the database on both Primary and standby

SQL> set lin 175

SQL> column NAME format a10

SQL> column LOG_MODE format a10

SQL> column OPEN_MODE format a10

SQL> COLUMN CONTROLFILE_TYPE format a16

SQL> column PROTECTION_MODE format a20

SQL> column PROTECTION_LEVEL format a20

SQL> column DATABASE_ROLE format a20

SQL> column GUARD_STATUS format a12

SQL> column DATAGUARD_BROKER format a16

SQL> select NAME,DATABASE_ROLE,LOG_MODE,CONTROLFILE_TYPE, OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,GUARD_STATUS,FORCE_LOGGING,DATAGUARD_BROKER from v$database;

• Verifying That the Standby Has Received All Redo, you can carry your individual steps.

On Primary and on standby --> To check if the scn's are close.

SQL> set numwidth 20

SQL> select current_scn from v$database;

On Standby --> To check if the MRP is running

SQL> select status,process from v$managed_standby where process like '%MRP%';

STATUS                                           PROCESS

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

APPLYING_LOG                                     MRP0

On standby  --> to Check if all or near to all logs are applied

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

And to check real time apply On primary / standby --> It should say "MANAGED REAL TIME APPLY" in one of the rows.

SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;

SQL> SELECT CLIENT_PROCESS,PROCESS,SEQUENCE#,STATUS FROM V$MANAGED_STANDBY;

Compare the sequence# from primary to standby query.  If the standby is not receiving the current redo, you cannot convert/switchover. Note that if your primary is a RAC, you should see multiple LGWR to RFS connections, one for each primary thread. You must validate that each primary thread is caught up.

• Check the apply is caught up

Once determined the redo stream is current, ensure all redo has been applied to standby database.

On the standby database:

SQL> SELECT CLIENT_PROCESS,PROCESS,SEQUENCE#,STATUS FROM V$MANAGED_STANDBY;

Note: if you don’t see MRP0, the apply is not running

To convert/switchover, you must start the apply service and wait for it to catch up with the current redo stream before starting. If you see the MRP0 line but it has a status of WAIT_FOR_GAP, you cannot convert/switchover until the gap has been resolved.

• Verify there are no large Gaps

Identify the current sequence number for each thread on the primary database.

On the Primary:

SQL> SELECT THREAD#,SEQUENCE#,STATUS FROM V$LOG;

Verify the target physical standby database has applied up to, but not including the logs from the primary query. On the standby the following query should be within 1 or 2 of the primary query result.

On the Standby database:

SQL> SELECT THREAD#, MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE APPLIED = 'YES' AND RESETLOGS_CHANGE# = SELECTRESETLOGS_CHANGE# FROM V$DATABASE_INCARNATION WHERE STATUS ='CURRENT') GROUP BY THREAD#;

Data Guard Broker Configuration completeness check

• Ensure Server Parameter File (SPFILE) is used on both Primary and standby database so that the broker can persistently reconcile values between broker properties and any related initialization parameter values:

SQL> show parameter spfile;

• Verify value of the DG_BROKER_START initialization parameter is set to TRUE on both primary and standby DB.

   SQL> Show parameter DG_BROKER_START;

 The data Guard Monitor (DMON) - Broker-controlled process is the main Broker process that is responsible for coordinating all Broker actions as well as maintaining the Broker configuration files.  This process is enabled and disabled With DG_BROKER_START. To verify if DMON process running on both Primary and standby:

    

 $ps -ef|grep dmon|grep -v grep

•  Verify DG_BROKER_CONFIG_FILEn initialization parameters files exists on shared disk for all databases in the Oracle RAC database configuration on Primary and standby: 

   SQL> show parameter dg_broker_config_file;

• Check whether StaticConnectIdentifier is configured on ALL NODES
• To enable DGMGRL to restart instances during the course of the switchover / conversion, a static service is required. This can be supplied by explicitly setting the Broker Instance Property StaticConnectIdentifier. 

     Below static entry should be on all the nodes as below.

  --Primary 

   Node1

  SID_LIST_LISTENER =

 SID_LIST=

 SID_DESC=

 GLOBAL_DBNAME=orcl_DGMGRL.localdomain.com)

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

 SID_NAME=orcl01))

   )

  Node 2

  SID_LIST_LISTENER =

 (SID_LIST=

 (SID_DESC=

 (GLOBAL_DBNAME=orcl_DGMGRL.localdomain.com)

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

 (SID_NAME=orcl02))

 )

  

- Standby  

   

   Node1

  SID_LIST_LISTENER =

 (SID_LIST=

 (SID_DESC=

 (GLOBAL_DBNAME=orcldr_DGMGRL.localdomain.com)

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

 SID_NAME=orcldr1))

 )

  Node 2

  SID_LIST_LISTENER =

 (SID_LIST=

 (SID_DESC=

 (GLOBAL_DBNAME=orcl01_DGMGRL.localdomain.com)

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

 (SID_NAME=orcldr2))

   )

 **This entry makes it possible for the Broker to connect to an idle instance using a remote SYSDBA connection and perform the necessary startup

•    Ensure Broker configuration is enabled and state of all members as intended is ONLINE

   $ dgmgrl /

 DGMRL> show configuration
 DGMRL> show database verbose orcl

 DGMRL> show database verbose orcldr

       On the standby, ensure PROPERTY 'DELAYMINS'='0'

       Ensure property LogXptMode to be same on both Primary and Standby either set to 'SYNC' or ‘ASYNC’.

    On the standby and primary, ensure all other properties are consisted with Data Guard and Data Guard broker

•    Verify remote_login_passwordfile is set to 'EXCLUSIVE' on both Primary and Standby:

    sql>show parameter remote_login_passwordfile

   

   Conversion to Snapshot Standby and back to Physical standby using Data Guard Broker

•   Converting a physical Standby to Snapshot Standby

    Once completed the mandatory & prerequisites checks, connect to the primary database and disable all services so the application cannot establish any sessions to the database.

   On standby to be converted to snapshot mode.

   Tail Broker and Alert Logs (optional) on all instances:

  Locate Broker logs by showing database parameter background_dump_dest:

  SQL> SHOW PARAMETER background_dump_dest;

 NAME                                 TYPE   VALUE

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

background_dump_dest       string  /u01/admin/diag/rdbms/orcldr/orcldr01/trace

                                                                   

  Tail the broker logs:

   tail -f /u01/admin/diag/rdbms/orcldr/orcldr01/trace/dr*

•   DGMGRL Convert to snapshot standby: Log onto dgmgrl from primary database server

   On standby database, Issue the convert command:

  % dgmgrl

  DGMGRL> CONNECT SYS/password@standby

  DGMGRL> convert database 'orcldr'  to snapshot standby;

  DGMGRL for Linux: Version 11.1.0.7.0 - 64bit Production

  Copyright (c) 2000, 2005, Oracle. All rights reserved.

  Welcome to DGMGRL, type "help" for information.

  DGMGRL> 

  DGMGRL> connect sys@orcldr

  Password:

  Connected.

  DGMGRL> convert database 'orcldr' to snapshot standby;

•  Convert back to Physical Standby

1 Make sure all applications connecting to snapshot standby are down

  and then

  Tail the broker logs:

  %tail -f /u01/admin/diag/rdbms/orcldr/orcldr01/trace/dr*

  Convert to Physical standby:

  % dgmgrl *** log into dgmgrl from primary database server

 DGMGRL> CONNECT SYS/password@standby database

 DGMGRL> CONVERT DATABASE orcldr TO PHYSICAL STANDBY ;

 DGMGRL for Linux: Version 11.1.0.7.0 - 64bit Production  Copyright (c) 2000, 2005, Oracle. All rights reserved.

  Welcome to DGMGRL, type "help" for information.

  DGMGRL> 

  DGMGRL> connect sys@orcldr

  Password:

  Connected.

  DGMGRL> convert database 'orcldr' to physical standby;

•  Ensure Broker configuration is enabled and state of all members as intended is ONLINE:

  $ dgmgrl /

  DGMRL> show configuration

  DGMRL> show database verbose orcl

  DGMRL> show database verbose orcldr

•  Perform the Physical standby check out

 On Primary:

Check the alert log of primary database and tail the alert.

Check the archive error destination

SQL> col DEST_NAME for a40

SQL> set lines 200

SQL> select DEST_ID,DEST_NAME,STATUS,ERROR from v$archive_dest;

Check the current archive log (sequence)

SQL> select thread#,max(sequence#) from v$log where status='CURRENT' group by thread# order by 1;

On Standby:

Check the alert log of standby database and tail the alert.

Check the current archive log (sequence) applied.

SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread# order by 1;

Check the status of MRP

$ps -ef |grep mrp

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

PROCESS                        THREAD#  SEQUENCE# STATUS

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

MRP0                                 1     279486 WAIT_FOR_LOG

Conclusion: 

From this article we have learnt how to convert Physical standby database to Snapshot standby database using Data guard Broker method.