Oracle Data Guard Physical standby database features

The following scripts show how to turn on and enable standby database features, it's for 11gr2 database
1. convert standby database to snapshot standby and convert it back to physical standby.

--To turn on snapshot read-write database

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SELECT flashback_on FROM v$database;
--FLASHBACK_ON
------------------
--NO
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
ALTER DATABASE OPEN;
SELECT flashback_on FROM v$database;
--FLASHBACK_ON
------------------
--RESTORE POINT ONLY

--To flashback database and resume standby
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
SELECT flashback_on FROM v$database;
--FLASHBACK_ON
------------------
--NO

2. Read Only standby

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE OPEN READ ONLY;

3. active dataguard, require special license.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

ALTER DATABASE OPEN;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

4. To fail over to Standby database

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

ALTER DATABASE ACTIVATE STANDBY DATABASE;

5. To switch over between standby and primary database

-- Convert primary database to standby

CONNECT / AS SYSDBA

ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;

-- Shutdown primary database

SHUTDOWN IMMEDIATE;

-- Mount old primary database as standby database

STARTUP NOMOUNT;

ALTER DATABASE MOUNT STANDBY DATABASE;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

-- Convert standby database to primary

CONNECT / AS SYSDBA

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

-- Shutdown standby database

SHUTDOWN IMMEDIATE;

-- Open old standby database as primary

STARTUP;

6. Frequently used commands

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE disconnect;

select * from v$dataguard_stats

select * from GV$ARCHIVE_DEST_STATUS where dest_id=2;

select * from gv$archive_gap;

select * from (select * from GV$DATAGUARD_STATUS  order by timestamp desc) where rownum < 19

accept ddd prompt " run on standby database "

set lines 155 pages 9999

col thread# for 9999990

col sequence# for 999999990

col grp for 990

col fnm for a50 head "File Name"

col "Fisrt SCN Number" for 999999999999990

break on thread

select a.thread#

,a.sequence#

,a.group# grp

, a.bytes/1024/1024 Size_MB

,a.status

,a.archived

,a.first_change# "First SCN Number"

,to_char(FIRST_TIME,'DD-Mon-RR HH24:MI:SS') "First SCN Time"

,to_char(LAST_TIME,'DD-Mon-RR HH24:MI:SS') "Last SCN Time" from

v$standby_log a order by 1,2,3,4

select sequence#,applied,archived,name,completion_time  from v$archived_log o where not exists (select 1 from  v$archived_log i where i.sequence#=o.sequence# and applied='YES') and archived='YES' and applied='NO'

and completion_time>sysdate-1

order by completion_time

select * from v$archive_dest_status where dest_id=2