Convert Physical Standby into Snapshot Standby

 

Enable testing on standby with Oracle Snapshot Standby.

You can easily switch between a Snapshot and Physical Standby as needed. Learn more about transitioning from Physical to Logical Standby or leveraging Active Data Guard for real-time read access.

Convert physical standby to snapshot standby: We will now convert the physical standby database to snapshot standby

On standby:
===========
SQL> alter database recover managed standby database cancel;
SQL> select name, open_mode from v$database; 		>> make sure its mounted
SQL> alter database convert to snapshot standby;
SQL> alter database open;				>> open the DB
SQL> select name, open_mode, database_role from v$database;

Verifying snapshot standby: Now you must be able to read-write on snapshot standby. Meanwhile, we can even check the standby alert log. The archives received from primary are not applied on standby. We can even check that there is a guaranteed restore point has been created. So that when you convert snapshot back to physical standby, it will be used.

Also Note: For this snapshot standby, you do not need Flashback database enabled.

On standby:
===========
SQL> select name, guarantee_flashback_database from v$restore_point;
SQL> create table employees(eno number(2), s_name varchar2(10));
SQL> insert into employees values(1,'KAB');
SQL> insert into employees values (2,'SAM');
SQL> commit;
SQL> select * from employees
;

Revert back snapshot standby to physical standby: Once application testing is done, you can revert back snapshot standby to same point when it was converted from physical standby to snapshot standby

On standby:
===========
SQL> select name, open_mode, database_role from v$database;
SQL> shut 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;
SQL> select * from student;