Oracle Data Guard - Snapshot Standby

Oracle Data Guard – DR Testing


1) Submit blockers on the Database, production server, and OOR server

2) Change the monitor flag in DB Registration to “N”

3) On Primary
sho parameter log_archive_dest – check if there are services to other databases
alter system switch logfile;
set log_archive_dest_state_<#> = defer    (if this situation exists)

Verify the standby is in sync

alter system switch logfile;   (repeat a couple of times)

select thread#, max(sequence#) from v$log_history group by thread#;


4)  On Stanby Site Change parameters if required

Convert the standby to a snapshot standby. 
It will still be a standby, opened in read/write mode, still receiving archived logs, but not applying them.

Set DB parameters

SQL> show parameter db_recovery   (check if they are already set)

db_recovery_file_dest_size – set to size needed
db_recovery_file_dest – set to a directory with a lot of space

If OOR database is RAC, note these different steps

db_recovery_file_dest - put on ASM disk group
db_recovery_file_dest='+ARCHIVE' or '+DATA'

$mkdir <directory path>/oor_test

Example:  (substitute your path and sizes as needed)
alter system set db_recovery_file_dest='/export/appl/edbcon/datapump/oor_test' scope=both
alter system set db_recovery_file_dest_size='100G' scope=both;

5) Opne DB in Read wite mode

SQL> alter database recover managed standby database cancel;
SQL> shutdown immediate;
SQL> startup mount;
SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
SQL> alter database open;
SQL> select name,guarantee_flashback_database from v$restore_point;
SQL> select name, open_mode, database_role, flashback_on, controlfile_type from v$database;
Expected values:
(open_mode=READ WRITE; database_role=SNAPSHOT STANDBY; flashback_on=RESTORE POINT ONLY;  CONTROLFILE_TYPE=CURRENT)

The database should be open with a restore point set

Shakeout database

select open_mode from v$database

select file_name, bytes/1024/1024, status, online_status from dba_data_files;

Review the alert<DB>.log for any errors during startup



6) Monitor Archivelogs generation

Monitor archivelog space defined in log_archive_dest_1 and flashback space defined in db_recovery_file_dest

Change the log_archive_dest_1 to a different directory as needed


set lines 100
col name format a60
select name, floor(space_limit/1024/1024/1024) "Size GB",
ceil(space_used/1024/1024/1024) "Used GB"
from 
v$recovery_file_dest order by name;


7) Take Export Backup 


8) Convert the snapshot standby to a regular standby.  


SQL> shutdown immediate;
SQL> startup mount;
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database recover managed standby database disconnect from session;

SQL> select name,guarantee_flashback_database from v$restore_point;
(prior restore point should be gone)

SQL> select name, open_mode, database_role, flashback_on, controlfile_type from v$database;
Expected values:               
(open_mode=MOUNTED; database_role=PHYSICAL STANDBY; flashback_on=NO;  CONTROLFILE_TYPE=STANDBY)



Startup the production database, listener, and management agent

Shakeout production database

select open_mode from v$database

select file_name, bytes/1024/1024, status, online_status from dba_data_files;

Review the alert<DB>.log for any errors during startup

Connect to the database from a remote server (i.e. dsysadm15)
select INSTANCE_NAME, HOST_NAME from v$instance; 
(verify you connect to the production server)

Verify the standby is in sync

alter system switch logfile;
select thread#, max(sequence#) from v$log_history group by thread#;