Convert Physical Standby to Snapshot standby

Overview:

This article explain the process of converting Physical Standby in Read Only mode to a Snapshot Standby.Snapshot Standby open DB in Read Write mode for 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 and Standby
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 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#;

Convert to Physical standby to Snapshot Standby manual way

•  On standby to be converted to snapshot mode.

Tail the Alert Logs (optional) on all instances:

Locate alert logs by showing database parameter background_dump_dest:

SQL> SHOW PARAMETER background_dump_dest;

% tail -f /u01/app/diag/rdbms/standby/standby1/trace/alert*

• Stop MRP

SQL> alter database recover managed standby database cancel;

Database alerted

• Record SCN and timestamp

SQL> col current_scn format 99999999999999

SQL> select current_scn from v$database;

•  Manual SQLPLUS Convert to snapshot standby:

                For standalone

SQL> shutdown immediate

SQL> startup mount

                For RAC

$srvctl stop database -d DBNAME

$srvctl start database -d DBNAME -o mount

     

SQL> select open_mode, database_role from v$database;

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

SQL> shutdown immediate

SQL> startup mount

SQL> select open_mode, database_role from v$database;

SQL> ALTER DATABASE OPEN;

SQL> select open_mode, database_role from v$database;

Convert to  Snapshot Standby back to Physical standby manual way

For RAC

$srvctl stop database -d DBNAME

$srvctl startup database -d DBNAME -o mount

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

srvctl stop database -d DBNAME

For Standalone

SQL> shutdown immediate

SQL> startup mount

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

SQL> shutdown immediate

Restart database

For standalone

  SQL> startup mount

For RAC

srvctl start database -d DBNAME -o mount

• Start MRP and open read only

SQL> alter database open read only;

SQL> alter database recover managed standby database using current logfile disconnect;

•  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 manual method.