Hello Friend's,
The purpose of this blog post is to assist you with the process of converting a snapshot standby database back to a physical standby database and getting it synchronized with the primary database again to prepare for resuming its role in Oracle Data Guard.
What is a Snapshot Standby?
A snapshot standby database is an upgradable standby database that has been switched to a snapshot standby role temporarily from a physical standby database. While in snapshot standby mode, the database continues to receive and archive the redo data that is sent by the primary database; however, it is not applying this redo. At the point when you switch the snapshot database back to a physical standby, any changes made during the snapshot period will be discarded. When you put it back in physical standby mode and start it, the standby database will then apply all of the accumulated redo data.
Prerequisites
Before converting the snapshot standby, ensure the following:
All Testing/Development Completed: Verify that all testing, development, or reporting activity is completed, as all changes made on the snapshot standby will be lost.
Enough Disk Space: Make sure you have enough disk space available to manage the accumulated redo logs during the time that the snapshot has been taken.
Backup: As always, back up, before making any changes to your Data Guard configuration at all.
Step-by-Step Instructions to Convert Snapshot Standby to a Physical Standby
Step 1: Shut Down the Snapshot Standby Database
I : To convert a snapshot standby back to a physical standby, you must shut down the database.
SQL> col DB_UPTIME for a35
col DATABASE_ROLE for a20
col DB_VERSION for a20
set lines 200
col LOG_MODE for a20
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DSQL> B_VERSION,LOGINS,LOG_MODE,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB_UPTIME" from v$database,gv$instance;SQL> SQL> SQL> SQL> SQL> SQL> SQL>
DB_NAME HOST_NAME DATABASE_ROLE OPEN_MODE DB_VERSION LOGINS LOG_MODE DB_UPTIME
--------- ------------------------------ -------------------- ---------- -------------------- ---------- -------------------- -----------------------------------
FLYDB2 dm01db01 SNAPSHOT STANDBY READ WRITE 19.0.0.0.0 ALLOWED ARCHIVELOG 10-AUG-2024 16:13:25
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
This step helps to guarantee that all transactions are completed and the database is closed properly.
Step 2 :Now, you may start the database in mount mode to prepare it for physical standby.
SQL> startup mount
ORACLE instance started.
Total System Global Area 3221222440 bytes
Fixed Size 8930344 bytes
Variable Size 704643072 bytes
Database Buffers 2483027968 bytes
Redo Buffers 24621056 bytes
Database mounted.
SQL> col DB_UPTIME for a35
col DATABASE_ROLE for a20
col DB_VERSION for a20
set lines 200
col LOG_MODE for a20
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DB_VERSION,LOGINS,LOG_MODE,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB_UPTIME" from v$database,gv$instance;SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>
DB_NAME HOST_NAME DATABASE_ROLE OPEN_MODE DB_VERSION LOGINS LOG_MODE DB_UPTIME
--------- ------------------------------ -------------------- ---------- -------------------- ---------- -------------------- -----------------------------------
FLYDB2 dm01db01 SNAPSHOT STANDBY MOUNTED 19.0.0.0.0 ALLOWED ARCHIVELOG 12-AUG-2024 18:53:46
In mount mode, the database is not open for read/write activities, but it is ready to do tasks such as applying redo logs or converting.
Step 3: Switch from Snapshot Standby to Physical Standby.v
To convert the snapshot standby back to a physical standby, use the following command:
SQL> alter database convert to physical standby;
Database altered.
SQL>
SQL> select name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DB_VERSION,LOGINS,LOG_MODE,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB_UPTIME" from v$database,gv$instance;
DB_NAME HOST_NAME DATABASE_ROLE OPEN_MODE DB_VERSION LOGINS LOG_MODE DB_UPTIME
--------- ------------------------------ -------------------- ---------- -------------------- ---------- -------------------- -----------------------------------
FLYDB2 dm01db01 PHYSICAL STANDBY MOUNTED 19.0.0.0.0 ALLOWED ARCHIVELOG 12-AUG-2024 18:53:46
This command undoes any modifications done while the database was in snapshot standby mode and returns the database to its previous status as a physical standby.
Step 4: Begin the Redo Apply process.
After converting the database back to a physical standby, initiate the redo apply process to synchronize the standby with the primary database.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CONNECTED 0
DGRD ALLOCATED 0
DGRD ALLOCATED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
RFS IDLE 0
RFS IDLE 22970
MRP0 APPLYING_LOG 22970
9 rows selected.
-------------------Alert Log for revert back process -----------------
2024-08-12T18:54:51.207252+03:00
alter database convert to physical standby
2024-08-12T18:54:51.207459+03:00
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (FLYDB2)
2024-08-12T18:54:51.216909+03:00
.... (PID:1545): Killing 2 processes (PIDS:1568,1609) (all RFS) in order to disallow current and future RFS connections. Requested by OS process 1545
2024-08-12T18:54:51.217416+03:00
Process termination requested for pid 1568 [source = rdbms], [info = 2] [request issued by pid: 1545, uid: 1003]
2024-08-12T18:54:51.217507+03:00
Process termination requested for pid 1609 [source = rdbms], [info = 2] [request issued by pid: 1545, uid: 1003]
2024-08-12T18:54:53.452356+03:00
Flashback Restore Start
Flashback Restore Complete
Drop guaranteed restore point
Stopping background process RVWR
Deleted Oracle managed file +RECO/FLYDB2DR/FLASHBACK/log_1.933.1176808729
2024-08-12T18:54:54.471076+03:00
Deleted Oracle managed file +RECO/FLYDB2DR/FLASHBACK/log_2.934.1176808729
Guaranteed restore point dropped
2024-08-12T18:54:54.502201+03:00
.... (PID:1545): Database role cleared from SNAPSHOT STANDBY [kcvs.c:8973]
Clearing standby activation ID 1240080343 (0x49ea1fd7)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 209715200;
.... (PID:1545): Database role changed from PRIMARY to PHYSICAL STANDBY [kcvs.c:8978]
.... (PID:1545): RT: Role transition work is not done
.... (PID:1545): WARNING: STANDBY_FILE_MANAGEMENT initialization parameter is
not set to the value "AUTO".
This may cause recovery of the standby database to terminate
prior to applying all available redo data.
It may be necessary to use the ALTER DATABASE CREATE DATAFILE
command to add datafiles created on the primary database.
.... (PID:1545): Redo network throttle feature is disabled at mount time
Physical Standby Database mounted.
2024-08-12T18:54:54.547729+03:00
In-memory operation on ADG is currently only supported on Engineered systems and PaaS.
inmemory_adg_enabled is turned off automatically.
Please contact our support team for EXADATA solutions
CONVERT TO PHYSICAL STANDBY: Complete - Database mounted as physical standby
Completed: alter database convert to physical standby
2024-08-12T18:54:54.743944+03:00
rfs (PID:1768): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is Foreground (PID:8160)
2024-08-12T18:54:54.753740+03:00
rfs (PID:1770): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is ASYNC (PID:1736)
rfs (PID:1770): Primary database is in MAXIMUM PERFORMANCE mode
2024-08-12T18:54:54.887829+03:00
rfs (PID:1770): Selected LNO:4 for T-1.S-22970 dbid 1112675262 branch 1049281859
2024-08-12T18:56:39.513597+03:00
alter database recover managed standby database disconnect from session
2024-08-12T18:56:39.528025+03:00
Attempt to start background Managed Standby Recovery process (FLYDB2)
Starting background process MRP0
2024-08-12T18:56:39.550238+03:00
MRP0 started with pid=55, OS id=2221
2024-08-12T18:56:39.551373+03:00
Background Managed Standby Recovery process started (FLYDB2)
2024-08-12T18:56:44.580119+03:00
Started logmerger process
2024-08-12T18:56:44.604200+03:00
PR00 (PID:2225): Managed Standby Recovery starting Real Time Apply
2024-08-12T18:56:45.204699+03:00
Parallel Media Recovery started with 32 slaves
2024-08-12T18:56:45.314775+03:00
Stopping change tracking
2024-08-12T18:56:45.366337+03:00
TT02 (PID:2291): Waiting for all non-current ORLs to be archived
2024-08-12T18:56:45.366541+03:00
TT02 (PID:2291): All non-current ORLs have been archived
TT02 (PID:2291): Clearing ORL LNO:1 +RECO/FLYDB2DR/ONLINELOG/group_1.841.1176655653
Clearing online log 1 of thread 1 sequence number 4
2024-08-12T18:56:45.509023+03:00
PR00 (PID:2225): Media Recovery Waiting for T-1.S-22970 (in transit)
2024-08-12T18:56:45.545629+03:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 22970 Reading mem 0
Mem# 0: +RECO/FLYDB2DR/ONLINELOG/group_4.856.1176655925
2024-08-12T18:56:45.556869+03:00
Completed: alter database recover managed standby database disconnect from session
2024-08-12T18:56:46.283599+03:00
TT02 (PID:2291): Clearing ORL LNO:1 complete
TT02 (PID:2291): Clearing ORL LNO:2 +RECO/FLYDB2DR/ONLINELOG/group_2.842.1176655657
Clearing online log 2 of thread 1 sequence number 5
2024-08-12T18:56:46.969838+03:00
TT02 (PID:2291): Clearing ORL LNO:2 complete
TT02 (PID:2291): Clearing ORL LNO:3 +RECO/FLYDB2DR/ONLINELOG/group_3.843.1176655665
Clearing online log 3 of thread 1 sequence number 3
2024-08-12T18:56:47.686017+03:00
TT02 (PID:2291): Clearing ORL LNO:3 complete
2024-08-12T18:56:48.736567+03:00
TT02 (PID:2291): Waiting for all non-current ORLs to be archived
2024-08-12T18:56:48.736625+03:00
TT02 (PID:2291): All non-current ORLs have been archived
ConversionConversion EmoticonEmoticon