Oracle Dataguard Snapshot Standby Testing

 

This is something every dba will know but still documenting for handy steps .

Automated 

*********

To snapshot standby -

alter database recover managed standby database cancel;

alter database convert to snapshot standby;

SQL> select CURRENT_SCN, SWITCHOVER_STATUS, DATABASE_ROLE, open_mode from v$database;

CURRENT_SCN SWITCHOVER_STATUS DATABASE_ROLE   OPEN_MODE

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

  0 NOT ALLOWED SNAPSHOT STANDBY MOUNTED

To physical standby -

alter database close;

select CURRENT_SCN, SWITCHOVER_STATUS, DATABASE_ROLE, open_mode from v$database;

CURRENT_SCN SWITCHOVER_STATUS DATABASE_ROLE   OPEN_MODE

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

  0 NOT ALLOWED SNAPSHOT STANDBY MOUNTED

alter database convert to physical standby;

shut immediate

startup mount

alter database recover managed standby database using current logfile disconnect from session;

SQL> select CURRENT_SCN, SWITCHOVER_STATUS, DATABASE_ROLE, open_mode from v$database;

CURRENT_SCN SWITCHOVER_STATUS DATABASE_ROLE   OPEN_MODE

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

     378629 NOT ALLOWED PHYSICAL STANDBY MOUNTED

MANUAL

******

PRIMARY

=======

-- Archive the current log and defer the log_archive_dest_2

alter system archive log current;

alter system set log_archive_dest_state_2=DEFER;

STANDBY

=======

-- Activating the standby

-- Stop managed recovery, create a guaranteed restore point and activate the standby. Ensure db_recovery_file_dest is set.

alter database recover managed standby database cancel;

alter system set log_archive_dest_state_2=DEFER;

create restore point before_testing guarantee flashback database;

alter database activate physical standby database;

alter database open;

select CURRENT_SCN, SWITCHOVER_STATUS, DATABASE_ROLE, open_mode from v$database;

select CONTROLFILE_TYPE from v$database; 

-- Converting back to standby

startup mount force

flashback database to restore point before_testing;

alter database convert to physical standby;

startup mount force

drop restore point before_testing;

alter database recover managed standby database using current logfile disconnect from session;

Reference -

How To Open Physical Standby For Read Write Testing and Flashback (Doc ID 805438.1)