Convert Physical Standby To Snapshot Standby Database
What is snapshot standby:
Snapshot standby is a feature in oracle 11g that allows to do read write operation on standby database. i. e we can convert the physical standby database to snapshot standby . On that we can do all types of testing or can be used as a development database (which is an exact replication of production ) . Once the testing is over we can again convert the snapshot database to physical standby. Once the it is converted physical standby database, whatever changes were done to the snapshot standby will be reverted.
STANDBY> select open_mode from v$database;
OPEN_MODE
——————–
READ ONLY WITH APPLY
STANDBY> select database_role from v$database;
DATABASE_ROLE
—————-
PHYSICAL STANDBY
Cancel the recovery process and enable flashback mode:
STANDBY> alter database recover managed standby database cancel;
STANDBY> shut immediate;
STANDBY> startup mount;
STANDBY> alter database flashback on;
STANDBY> select flashback_on from v$database;
STANDBY> select status from v$instance;
Now the below command will convert it to snapshot standby:
STANDBY> alter database convert to snapshot standby;
STANDBY> alter database open;
STANDBY> select database_role from v$database;
STANDBY> select open_mode from v$database;
STANDBY> select NAME,GUARANTEE_FLASHBACK_DATABASE from v$restore_point;
Lets do some DML changes in this snapshot standby:
Create a table and insert some rows...
Alter one of the existing table and add one additional column to the table and perform some insert operations.
We can see we are able to do write operation on snapshot database also.
Let convert it again to physical standby again:
STANDBY> shut immediate;
STANDBY> startup mount
STANDBY> select FLASHBACK_ON from v$database;
STANDBY> alter database convert to physical standby;
STANDBY> select database_role from v$database;
STANDBY> select status from v$instance;
STANDBY> shut immediate
STANDBY> startup
STANDBY>alter database recover managed standby database using current logfile disconnect;
STANDBY> select open_mode from v$database;
OPEN_MODE
——————–
READ ONLY
STANDBY> select database_role from v$database;
DATABASE_ROLE
—————-
PHYSICAL STANDBY
Lets check whether DML changes we did previously has been reverted or not:
STANDBY> select * from <table_name>; (The table which was created during snapshot standby)
Snapshot standby is a feature in oracle 11g that allows to do read write operation on standby database. i. e we can convert the physical standby database to snapshot standby . On that we can do all types of testing or can be used as a development database (which is an exact replication of production ) . Once the testing is over we can again convert the snapshot database to physical standby. Once the it is converted physical standby database, whatever changes were done to the snapshot standby will be reverted.
STANDBY> select open_mode from v$database;
OPEN_MODE
——————–
READ ONLY WITH APPLY
STANDBY> select database_role from v$database;
DATABASE_ROLE
—————-
PHYSICAL STANDBY
Cancel the recovery process and enable flashback mode:
STANDBY> alter database recover managed standby database cancel;
STANDBY> shut immediate;
STANDBY> startup mount;
STANDBY> alter database flashback on;
STANDBY> select flashback_on from v$database;
STANDBY> select status from v$instance;
Now the below command will convert it to snapshot standby:
STANDBY> alter database convert to snapshot standby;
STANDBY> alter database open;
STANDBY> select database_role from v$database;
STANDBY> select open_mode from v$database;
STANDBY> select NAME,GUARANTEE_FLASHBACK_DATABASE from v$restore_point;
Lets do some DML changes in this snapshot standby:
Create a table and insert some rows...
Alter one of the existing table and add one additional column to the table and perform some insert operations.
We can see we are able to do write operation on snapshot database also.
Let convert it again to physical standby again:
STANDBY> shut immediate;
STANDBY> startup mount
STANDBY> select FLASHBACK_ON from v$database;
STANDBY> alter database convert to physical standby;
STANDBY> select database_role from v$database;
STANDBY> select status from v$instance;
STANDBY> shut immediate
STANDBY> startup
STANDBY>alter database recover managed standby database using current logfile disconnect;
STANDBY> select open_mode from v$database;
OPEN_MODE
——————–
READ ONLY
STANDBY> select database_role from v$database;
DATABASE_ROLE
—————-
PHYSICAL STANDBY
Lets check whether DML changes we did previously has been reverted or not:
STANDBY> select * from <table_name>; (The table which was created during snapshot standby)