• Arun Kumar

Convert Physical Standby into Snapshot Standby

Let us assume there is a requirement from application team to test something and there is a need to clone production database. We can convert existing physical standby database into snapshot standby database, do the testing and convert it back to physical standby.


We can always revert back snapshot standby to the point when we converted it to snapshot standby from physical standby. This way we can repeat the cycle any number of times, perform testing, convert back to physical standby and sync back again with production.


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 enabled at database level

On standby:
===========
SQL> select name, guarantee_flashback_database from v$restore_point;
SQL> create table student(sno number(2), s_name varchar2(10));
SQL> insert into student values(1,'RAM');
SQL> insert into student values (2,'Max');
SQL> commit;
SQL> select * from student;

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;


Recent Posts

See All

Oracle Data Guard Protection Modes

A Data Guard configuration always runs in one of three data protection modes (also called as redo transport rules): Maximum Protection Maximum Availability Maximum Performance (default mode of operati

Oracle Data Guard Physical Standby Configuration

There are various steps in which you can configure physical standby database. We need to make several changes to the primary database before we can even setup the standby database. This article applie

Client Connectivity in Data Guard Configuration

When you have a physical standby, you must make sure client connectivity is set properly so that when you perform failover or switchover, client must smoothly connect to the new primary. Create a data

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis

Feedback