In this Blog we will see how to convert a 2 Node Physical Standby Database to a Snapshot Standby Database.
Before proceeding, let us first know what is a Snapshot Standby Database and why is it needed ?
A snapshot standby database is a fully updateable standby database created by converting a physical standby database into a snapshot standby database.
A snapshot standby database receives and archives, but does not apply, redo data from a primary database. The redo data received from the primary database is applied once a snapshot standby database is converted back into a physical standby database, after discarding all local updates to the snapshot standby database.
This means that you need to break the DG connectivity with your Primary database, once you convert your Physical Standby Database to Snapshot Standby DB. The Application would be able to connect to the Snapshot Standby database and perform WRITE operations on it. But once it is again converted to Physical Standby, the changes would be lost and the Physical Standby DB would again be in SYNC with the Primary
Please click on the INDEX and browse for more interesting posts.
Resources :
Primary DB : INFRA
Standby DB : INFRA_STBY
STEPS :
1. Verify whether flashback is ON, or else start Flashback
If Flashback is Turned OFF, then you need to cancel your MRP and TURN ON Flashback, after which the MRP can be turned ON again
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> select log_mode,flashback_on from gv$database;
LOG_MODE FLASHBACK_ON
------------ ------------------
ARCHIVELOG NO
ARCHIVELOG NO
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +FRA
db_recovery_file_dest_size big integer 20G
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database flashback on;
Database altered.
SQL> select log_mode,flashback_on from gv$database;
LOG_MODE FLASHBACK_ON
------------ ------------------
ARCHIVELOG YES
ARCHIVELOG YES
SQL> alter database recover managed standby database disconnect from session;
Database altered.
2. Check the Status of your Standby Database and cancel the MRP
SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
------------ ---------------- ---------------- --------------------
MOUNTED INFRA1 PHYSICAL STANDBY MOUNTED
SQL> alter database recover managed standby database cancel;
Database altered.
3. Bounce the Database to Mount mode
srvctl stop database -d INFRA_STBY
srvctl start database -d INFRA_STBY -o mount
4. Convert the database to Snapshot Standby Database
SQL> alter database convert to snapshot standby;
5. Check the Database Role and then open the Database in Read/Write mode
SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
------------ ---------------- ---------------- --------------------
MOUNTED INFRA1 SNAPSHOT STANDBY MOUNTED
SQL> alter database open;
6. Handover the Database for testing
Convert the database from Snapshot Standby to Physical Standby
Once the testing is complete, we need to revert back the database to Physical standby again
1. Shut down the database
$srvctl stop database -d INFRA_STBY
2. Start the database from only 1 Node
This is because, if instances from all nodes are started, it wont let you convert from snapshot to physical standby database
$srvctl start database -d INFRA_STBY ---> DONOT PERFORM
SQL> alter database convert to physical standby;
alter database convert to physical standby
*
ERROR at line 1:
ORA-38777: database must not be started in any other instance
$srvctl start instance -d INFRA_STBY -i INFRA1 -o mount --> Started database from 1 node
3. Check the status and convert to Physical Standby
SQL> select status,instance_name,database_role,open_mode from gv$database,gv$Instance;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
------------ ---------------- ---------------- --------------------
MOUNTED INFRA1 SNAPSHOT STANDBY MOUNTED
SQL> alter database convert to physical standby;
Database altered.
4. Bounce the database
This restart is required to be on the safer side and start instances from all nodes
$srvctl stop database -d INFRA_STBY
$srvctl start database -d INFRA_STBY -o mount
SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
------------ ---------------- ---------------- --------------------
MOUNTED INFRA1 PHYSICAL STANDBY MOUNTED
5. Start the recovery and check whether the Physical standby database is in SYNC with the primary or not
SQL> alter database recover managed standby database disconnect from session;