Active Data Guard and Snapshot Standby Database

Active Data Guard

All query needs to be done on physical standby

In this physical database is open in read only mode . Still archive log gets shipped and applied

Step 1) Check if media recovery is enabled or not

sql > select process,status,client_process,sequence#,block#,active_agents,known_agents from v$managed_standby;

Check for MRP0 process in output

Step 2) Cancel the recovery

SQL> alter database recover managed standby database cancel;

Step 3) Shutdown the database

sql > shutdown immediate

Step 4) Mount the database

sql > startup mount

Step 5) Bring the database in read only mode

SQL> alter database open read only;

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

Step 6) Resume the media recovery in read-only mode database

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

SQL> select process,status,client_process,sequence#,block#,active_agents,known_agents from v$managed_standby;

To revert back the active data guard

Step 1) cancel the media recovery

Sql > alter database recover managed standby database cancel;

Step 2) shutdown database

Sql > shutdown immediate

Step 3) start the database in mount mode

Sql > startup mount

Step 4) Enable the recovery process

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

Step 5) Check the recovery mode

sql > select process,status,client_process,sequence#,block#,active_agents,known_agents from v$managed_standby;

Step 6) Do alter system switch in primary and query about log applied on physical

SQL> select max(sequence#) from v$archived_log where applied='YES';

Step 7) Check the open_mode of the database

Sql > select name,open_mode,database_role from v$database

Snapshot database

Execute the below query in physical standby database

step 1) Cancel the managed recovery

Sql > alter database recover managed standby database cancel;

Step 2) shutdown the database

sql > shutdown immediate

Step 3) startup the database in mount mode

sql > startup mount

Step 4) Check flashback_on parameter from v$database

Sql > select flashback_on from v$database;

Value if no not an issue

Step 5) Execute the below query to convert physical standby to snapshot database

SQL> alter database convert to snapshot standby;

SQL> alter database open;

SQL> select flashback_on from v$database;

FLASHBACK_ON

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

RESTORE POINT ONLY

1 row selected.

SQL>

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

NAME                           OPEN_MODE            DATABASE_ROLE

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

PRD01                          READ WRITE           SNAPSHOT STANDBY

1 row selected.

Converting the snapshot standby database back to physical database

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 flashback_on from v$database;
sql > > select process,status,client_process,sequence#,block#,active_agents,known_agents from v$managed_standby;