In a real
time, Usually DBA receive the request from application team to convert the
physical standby database into snapshot standby so that they can perform their
testing into the new snapshot standby database and to execute that kind of task
DBA need to perform some basic checks and also need to execute snapshot standby
conversion command.
Step 1st: validate if Physical Standby is in sync
The first
step DBA needs to perform is to check whether primary is in sync with is
physical standby database and if not then we’ve to resolve the gap and sync it with
its primary database.
SQL>select
name,role,thread#,sequence#,block# from v$dataguard_process
NAME ROLE THREAD#
SEQUENCE# BLOCK#
-----
------------------------ ---------- ---------- ----------
PR02 recovery apply slave 0 0 0
PR01 recovery apply slave 0 0 0
MRP0 managed recovery 0 0 0
rfs RFS async 1
14 294
rfs RFS archive 0
0 0
PR00 recovery logmerger 1 14 294
rfs RFS ping 1
14 0
rfs RFS archive 0
0 0
ARC3 archive redo 0
0 0
ARC2 archive local 0
0 0
ARC1 archive redo 0
0 0
ARC0 archive redo 0
0 0
TT01 redo transport timer 0 0 0
TMON redo transport monitor 0 0 0
rfs RFS archive 0
0 0
LGWR log writer 0
0 0
TT00 gap manager 0
0 0
NOTE: From 12c
onward, we can use v$dataguard_process view to validate the sync status.
Step 2nd: Stop MRP process in standby database
SQL> alter database recover managed standby database cancel;
Database
altered.
SQL> select name,role,thread#,sequence#,block# from v$dataguard_process where role like'%MRP%';
no rows
selected
Step
3rd:
Break the redo shipping between primary and standby database
SQL> show parameter log_archive_dest_state_2
NAME |TYPE |VALUE
------------------------------------|-----------|------------------------------
log_archive_dest_state_2
|string |ENABLE
SQL> alter system set log_archive_dest_state_2=defer;
System
altered.
SQL> show parameter log_archive_dest_state_2
------------------------------------|-----------|------------------------------
log_archive_dest_state_2
|string |DEFER
NOTE:
In
A real time, Requirement changes from environment to environment, in some
environment application team ask to bring down the primary database until the
testing will be done into the new snapshot standby database and in some
environment stop redo shipping between primary and physical standby is enough.
Step
4th: Do 2-4 log switch and validate whether redo
stop shipping into standby database
SQL>
alter system switch logfile;
System
altered.
SQL> /
System
altered.
SQL> /
System
altered.
SQL> /
System
altered.
V$dataguard_process
view output from primary database
SQL>
select name,role,thread#,sequence#,block# from v$dataguard_process;
NAME
|ROLE |
THREAD#| SEQUENCE#| BLOCK#
-----|------------------------|----------|----------|----------
LGWR |log
writer | 0|
0| 0
TMON
|redo transport monitor | 0| 0| 0
TT00 |gap manager | 1|
14| 0
TT01
|redo transport timer | 0| 0| 0
ARC0
|archive local | 0|
0| 0
ARC1
|archive redo | 0|
0| 0
ARC2
|archive redo | 0|
0| 0
ARC3
|archive redo | 0|
0| 0
TT04
|heartbeat redo informer | 0|
0| 0
TT05
|controlfile update | 0| 0| 0
NOTE: In above output that async ORL multi process is no longer exist in the database which means that the redo shipping between primary and standby is successfully stopped.
Step 5th: Convert physical standby into snapshot standby
NAME DATABASE_ROLE OPEN_MODE
---------
---------------- --------------------
US_PRIM PHYSICAL
STANDBY MOUNTED
SQL>
show parameter db_unique_name
NAME TYPE
VALUE
------------------------------------
----------- ------------------------------
db_unique_name string
US_PRIM
SQL>
alter database convert to snapshot standby;
Database
altered.
--------- ---------------- --------------------
US_PRIM
SNAPSHOT STANDBY
MOUNTED
NOTE: When we
convert physical standby into snapshot standby then internally it creates a
guarantee restore point, so that in future it can flashback the database and
convert the snapshot standby into physical standby when require.
alert
log of physical standby after snapshot conversion
2024-01-26T19:07:15.378492+05:30
alter
database convert to snapshot standby
Created
guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_01/26/2024
Step
6th:
open snapshot standby in read write mode and do some testing
SQL> alter database open;
Database
altered.
SQL> select name,database_role,open_mode from v$database;
NAME DATABASE_ROLE OPEN_MODE
--------- ---------------- --------------------
US_PRIM SNAPSHOT
STANDBY READ WRITE
SQL>
show parameter db_unique
NAME TYPE VALUE
------------------------------------
----------- -------------------------
db_unique_name string
US_PRIM
SQL>
show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
----------
------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
SQL>
alter session set container=PDB1;
Session
altered.
SQL> create user test identified by Test123;
User
created.
SQL>select username,account_status ,created from dba_users where username='TEST';
USERNAME
ACCOUNT_STATUS CREATED
--------------------------------------------------------------------------------------------------
TEST
OPEN 26-JAN-24
