Steps to convert 2 Node RAC Physical standby database to snapshot Standby
Here in this scenario we have 2 node Primary and 2 node standby resources :
Prod Db name: Shamsprd0
Standby Db name : Shamssty0
Primary DB : Shamsprd01 & Shamsprd02
Standby DB : Shamssty01 & Shamssty02
STEPS :
A. Check if the standby database is in sync and there are no gaps
TO check on standby server:
Select a.thread#,b.last_seq,a.applied_seq,a.last_app_timestamp,b.last_seq-a.applied_seq
ARC_DIFF from (Select thread#,MAX(sequence#) applied_seq,MAX(next_time) last_app_timestamp FROM gv$archived_log
where applied='YES' GROUP BY thread#) a,(SELECT thread#,MAX(sequence#) last_seq FROM gv$archived_log GROUP by thread#)
b WHERE a.thread#=b.thread#;
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 +RECO
db_recovery_file_dest_size big integer 2000G
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.
B. Check the Status of your Standby Database and cancel the MRP
SQL> select status,instance_name,database_role,open_mode from gv$database,gv$instance;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
------------ ---------------- ---------------- -------------------------------------------------
OPEN Shamssty01 PHYSICAL STANDBY READ ONLY WITH APPLY
OPEN Shamssty02 PHYSICAL STANDBY READ ONLY WITH APPLY
Database altered.
C. Bounce the Database to Mount mode
srvctl stop database -d Shamssty0
srvctl start database -d Shamssty0 -o mount
D. Convert the database to Snapshot Standby Database
SQL> alter database convert to snapshot standby;
E. 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 Shamssty01 SNAPSHOT STANDBY MOUNTED
SQL> alter database open;
Note: Handover the Database for testing team to test from their end
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
A. Shut down the database
$srvctl stop database -d Shamssty0
B. 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
DONOT PERFORM below step
$srvctl start database -d Shamssty0
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
Started database from 1st node
$srvctl start instance -d Shamssty0 -i Shamssty01 -o mount
C. 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 Shamssty0 SNAPSHOT STANDBY MOUNTED
SQL> alter database convert to physical standby;
Database altered.
D. Bounce the database
This restart is required to be on the safer side and start instances from all nodes
$srvctl stop database -d Shamssty0
$srvctl start database -d Shamssty0 -o mount
SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
------------ ---------------- ---------------- --------------------
MOUNTED Shamssty0 PHYSICAL STANDBY MOUNTED
E. 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;
TO check on standby server:
Select a.thread#,b.last_seq,a.applied_seq,a.last_app_timestamp,b.last_seq-a.applied_seq
ARC_DIFF from (Select thread#,MAX(sequence#) applied_seq,MAX(next_time) last_app_timestamp FROM gv$archived_log
where applied='YES' GROUP BY thread#) a,(SELECT thread#,MAX(sequence#) last_seq FROM gv$archived_log GROUP by thread#)
b WHERE a.thread#=b.thread#;
Enjoy the work and do like me if you like this article!!!.
