Using Snapshot Standby for DR drill(s)

Snapshot Standby Database functionality (that comes with EE licenses) can be used for yearly DR drills on 11g/12c databases. Here are the technical steps used for the same. 

Convert the Physical Standby Database to Snapshot database.

Please differ the dest_2 parameter in standby server.

SQL> alter system set log_archive_dest_state_2=DEFER scope=both sid=’*’;

$sqlplus / as sysdba

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;  (make sure MRP is stopped)

SQL> select flashback_on from v$database;

SQL> alter database flashback on;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
——————
YES

Activate the Physical Standby Database

$ sqlplus / as sysdba

SQL> select database_role,name,controlfile_type from v$database;

DATABASE_ROLE    NAME      CONTROL
—————- ——— ——-
PHYSICAL STANDBY NASCXTHZ   STANDBY

SQL> alter database convert to snapshot standby; (converting the physical standby database to snapshot database)

SQL> alter database open;
Database altered.

SQL> select database_role,name,controlfile_type,flashback_on from v$database;

DATABASE_ROLE    NAME      CONTROL FLASHBACK_ON
—————- ——— ——- ——————
SNAPSHOT STANDBY NASCXTHZ   CURRENT RESTORE POINT ONLY

SQL> select open_mode from v$database;

OPEN_MODE
——————–
READ WRITE

SQL> set pages 200 lines 200
SQL> select * from v$restore_point;

Below two commands will show how much flashback data has been generated.

SQL> select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
——————– ——— —————- ————– ————————
6.7014E+12 23-APR-15             1440       63750144                        0

SQL> select * from v$flashback_database_stat;

BEGIN_TIM END_TIME  FLASHBACK_DATA    DB_DATA  REDO_DATA ESTIMATED_FLASHBACK_SIZE
——— ——— ————– ———- ———- ————————
23-APR-17 23-APR-17          49152     622592          0                        0

SQL> show parameter db_rec

SQL> select database_role,name,controlfile_type,flashback_on from v$database;

DATABASE_ROLE    NAME      CONTROL FLASHBACK_ON
—————- ——— ——- ——————
SNAPSHOT STANDBY NASCXTHZ   CURRENT RESTORE POINT ONLY

SQL> select open_mode from v$database;

OPEN_MODE
——————–
READ WRITE

Convert the Snapshot Database to Physical Standby database.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
Total System Global Area  830930944 bytes
Fixed Size                  2217912 bytes
Variable Size             603981896 bytes
Database Buffers          222298112 bytes
Redo Buffers                2433024 bytes
Database mounted.
SQL>

Next convert the snapshot to a physical standby.

SQL> alter database convert to physical standby;
Database altered.

SQL> select * from v$restore_point;

no rows selected

SQL> select * from v$flashback_database_log;

no rows selected

SQL> select * from v$flashback_database_stat;

no rows selected

SQL> alter database recover managed standby database disconnect from session;

Post DG Sync Validation

Run the following query on Primary DB

SQL> select thread#, max(sequence#) “Last Primary Seq Generated”
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;

Run the following query on Standby DB

SQL> select al.thrd “Thread”, almax “Last Seq Received”, lhmax “Last Seq Applied”
from (select thread# thrd, max(sequence#) almax
from v$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) al,
(select thread# thrd, max(sequence#) lhmax
from v$log_history
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) lh
where al.thrd = lh.thrd;

Ensure “Last Primary Seq Generated” on Primary matches “Last Seq Applied” on each Standby

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s