Dataguard : converting physical standby to snapshot standby

snapshot standby which is 11g new feature along with active dataguard. regularly all standby databases are in mount stage and where DBA start MRP process in mount stage. standby database cannot be updated or opened. If you try to update and open standby database stage, standby database will get corrupted. Snapshot feature gives us flexibility to convert physical standby to snapshot standby database and can update snapshot standby database. 

when you want to convert physical standby to snapshot standby, we have to stop MRP and need to convert physical standby to snapshot standby in mount stage. when you convert physical standby to snapshot standby, Internally restore point is created on physical standby database. when we start updating the snapshot standby database flash logs will be created and  oracle uses these flash logs in FRA  to rollback the database until the restore point when you convert back from snapshot standby to physical standby database. Make sure you have enough space in FRA before you convert the physical standby to snapshot database. 

To test new application functionality on real time data, snapshot database feature is best option. 

procedure to convert physical standby to snap standby database  #
1#Make sure Flashback is enabled on standby
2#stop the MRP if it is up and running 
3#Make sure we have enough space to store flash logs in FRA 
4#Convert the physical standby to snapshot standby
5#check database role and validate snapshot standby & open snapshot database 

1#Make sure Flashback is enabled on standby
sql> select db_name,db_unique_name,open_mode,flashback_on from v$database;

2#stop the MRP if it is up and running 
sql> alter database recover managed standby database cancel;

3#Make sure we have enough space to store flash logs in FRA 
check parameter db_recovery_file_dest_size, if the space is available on the mount, increase db_recovery_file_dest_size to max value. Investigate how much space left in FRA

4#Convert the physical standby to snapshot standby
sql>select db_name,db_unique_name,open_mode,database_role from v$database;
sql>alter database convert to snapshot standby;

Here restore point is created and now standby database create flash logs 
We can query and check for restore point from v$restore_point
sql> select * from v$restore_point;

5#check database role and validate snapshot standby & open snapshot database 
sql>  select db_name,db_unique_name,open_mode,database_role from v$database;

sql> alter database open;

After regression testing, snapshot database can be converted into physical standby.
The timetaken to convert to physical standby depends up on type of operations performed when physical standby database is converted to snapshot standby. when we convert to snapshot to physical standby, oracle has to rollback the snapshot database to restore point recreated and convert backs to physical standby.procedure as follows 

1 # shut down the snapshot database
2 # Bring to mount stage 
3 # convert back to snapshot standby
4 # start the MRP process 

SQL> Shut immediate;
SQL> startup mount;
SQL> alter database convert to physical standby;

SQL> alter database recover managed standby disconnect from session;

--Nikhil Tatineni--
--Dataguard #