Convert Snapshot standby into physical standby database is one of the important steps when application team approaches DBAs after the testing to revert back the snapshot standby database into its original state and during that time DBA need to perform several steps.
In real-time, Prior to start snapshot conversion into physical standby database we’ve to create blackout for the database so that we can prevent unnecessary alert.
Step 2nd: Bring the snapshot standby into mount mode
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount
ORACLE instance started.
Total System Global Area
1728050736 bytes
Fixed Size 9135664
bytes
Variable Size
419430400 bytes
Database Buffers 1291845632 bytes
Redo Buffers
7639040 bytes
Database mounted.
SQL> select
name,database_role,open_mode from v$database;
NAME
DATABASE_ROLE OPEN_MODE
--------- ----------------
--------------------
US_PRIM SNAPSHOT STANDBY MOUNTED
Step 3rd: Convert
snapshot standby into physical standby database
SQL> alter database convert to
physical standby;
Database altered.
SQL> select
name,database_role,open_mode from v$database;
NAME
DATABASE_ROLE OPEN_MODE
--------- ----------------
--------------------
US_PRIM PHYSICAL STANDBY MOUNTED
alter database convert to
physical standby
2024-01-26T19:15:52.520855+05:30
ALTER DATABASE CONVERT
TO PHYSICAL STANDBY (USPRIM)
2024-01-26T19:15:52.621725+05:30
Flashback Restore Start
2024-01-26T19:15:54.703247+05:30
Flashback Restore
Complete
Drop guaranteed restore point
Guaranteed restore
point dropped
2024-01-26T19:15:54.815169+05:30
.... (PID:7591): Database role
cleared from SNAPSHOT STANDBY [kcvs.c:8837]
NOTE: To
convert snapshot standby into physical standby database, oracle uses guarantee
restore point that it creates during the conversion of physical standby to snapshot
standby and with the help of that restore point oracle flashback database to
its original state. In addition to flashback, it also drops the guarantee
restore point.
step 4th: Login into Primary Database and enable log shipping for standby from primary database
SQL> alter system set log_archive_dest_state_2=enable;
System altered.
SQL> show parameter
log_archive_dest_state_2
NAME |TYPE |VALUE
------------------------------------|-----------|------------------------------
log_archive_dest_state_2
|string |ENABLE
Step 5th: Start Database
in read only and also start MRP as well in physical standby
SQL> alter database open read
only;
Database altered.
SQL> alter database recover
managed standby database disconnect from session;
Database altered.
Step 6th: Validate
if physical standby is in sync with its primary database now.
SQL>select
role,name,thread#,sequence#,block# from v$dataguard_process
ROLE NAME THREAD#
SEQUENCE# BLOCK#
------------------------ -----
---------- ---------- ----------
log writer LGWR 0
0 0
redo transport monitor TMON 0 0 0
gap manager TT00 0 0 0
redo transport timer TT01 0
0 0
archive local ARC0 0 0 0
archive redo ARC1 0 0 0
archive redo ARC2 0 0 0
archive redo ARC3 0 0 0
RFS ping rfs 1
22 0
RFS async rfs 1 22 1497
RFS archive rfs 0 0 0
RFS archive rfs 0 0 0
RFS archive rfs 0 0 0
managed recovery MRP0 0
0 0
recovery logmerger PR00 1
22 1497
recovery apply slave PR01 0
0 0
recovery apply slave PR02 0
0 0
Step 7th: Release physical
standby database for the application team
Step 8th: Enable
All database jobs
Step 9th: Remove
blackout from the database
