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
