Hello Friend's,
Oracle Data Guard provides an efficient solution to disaster recovery and high availability, equally essential for maintaining a synchronized replica of the primary database, also referred to as a physical standby. Nonetheless, there are occasions that arise when you need to test changes, run reports, or perform development activities on the standby database temporarily without impacting the primary database. Oracle offers the ability to convert a physical standby to a snapshot standby which enables you to perform these tasks and then reverse the process back to a physical standby.
In this article, I will guide you through the steps to convert a physical standby database into a snapshot standby and reconvert the snapshot standby.
What is a Standby Snapshot?
A snapshot standby is a physical standby database that is put in read/write mode temporarily. It is unlike a regular physical standby because a snapshot standby can be taken out of standby mode, making the database accessible for testing or development purposes. You can test your application without affecting the read/write application because the current redo data will continue to be archived in the snapshot standby, which gives the advantage of having a complete, up-to-date, and version of the production database.
Prerequisites
Before you start, consider the following:
Oracle Data Guard should be in place: You should have a physical standby database that is already set up and is in sync with the primary database.
Disk Space: Make sure that there is enough disk space to accommodate the additional redo logs that will accumulate during the snapshot period.
Back-up: It is always a good practice to back-up prior to making modifications to your Data Guard environment.
Step 1: Convert Physical Standby to Snapshot Standby
Follow these steps to convert your physical standby database to a snapshot standby:
I : Before converting to snapshot standby, you must stop Redo Apply on the standby physical standby.
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 PHYSICAL STANDBY MOUNTED 19.0.0.0.0 ALLOWED ARCHIVELOG 10-AUG-2024 16:13:25
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> alter database recover managed standby database cancel;
Database altered.
II: Convert to Snapshot Standby: Now, convert the physical standby to a snapshot standby.
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +RECO
db_recovery_file_dest_size big integer 50G
recovery_parallelism integer 0
remote_recovery_file_dest string
SQL> alter database convert to snapshot standby;
Database altered.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
RESTORE POINT ONLY
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 SNAPSHOT STANDBY MOUNTED 19.0.0.0.0 ALLOWED ARCHIVELOG 10-AUG-2024 16:13:25
III : Open the Database: After the conversion, you need to open the snapshot standby database in read/write mode.
SQL> alter database open;
Database altered.
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 SNAPSHOT STANDBY READ WRITE 19.0.0.0.0 ALLOWED ARCHIVELOG 10-AUG-2024 16:13:25
When you convert a physical standby to a snapshot standby, you have the freedom to test, develop, or undertake other activities on a complete copy of your production system that can be updated without impacting your primary environment. During the conversion back to a physical standby, all changes that were made during the snapshot period are discarded, and the standby is made consistent with the primary.
This flexibility provided by Oracle Data Guard in transforming a standby database also extends to snapshot standbys, allowing a high degree of flexibility in managing your standby database in different scenarios for both high availability and robust testing environments.
Remember to monitor disk space and redo log accumulations during the snapshot period, and once your testing is complete, reconvert it back to a physical standby so that you are able to sustain the performance and synchronization of the primary database.
------Alert Log for Conversion process-------------
2024-08-12T11:17:23.835906+03:00
alter database recover managed standby database cancel
2024-08-12T11:17:23.843743+03:00
PR00 (PID:4714): MRP0: Background Media Recovery cancelled with status 16037
2024-08-12T11:17:23.844124+03:00
Errors in file /u01/app/oracle/diag/diag/rdbms/FLYDB2dr/FLYDB2/trace/FLYDB2_pr00_4714.trc:
ORA-16037: user requested cancel of managed recovery operation
PR00 (PID:4714): Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 10697236627634
Stopping change tracking
2024-08-12T11:17:24.113675+03:00
Errors in file /u01/app/oracle/diag/diag/rdbms/FLYDB2dr/FLYDB2/trace/FLYDB2_pr00_4714.trc:
ORA-16037: user requested cancel of managed recovery operation
2024-08-12T11:17:24.242676+03:00
Background Media Recovery process shutdown (FLYDB2)
2024-08-12T11:17:24.844442+03:00
Managed Standby Recovery Canceled (FLYDB2)
Completed: alter database recover managed standby database cancel
2024-08-12T11:18:32.850465+03:00
ALTER SYSTEM SET db_recovery_file_dest_size=50G SCOPE=BOTH;
2024-08-12T11:18:47.912212+03:00
alter database convert to snapshot standby
Starting background process RVWR
2024-08-12T11:18:48.046388+03:00
RVWR started with pid=44, OS id=1673
2024-08-12T11:18:49.115408+03:00
Allocating 15937344 bytes in shared pool for flashback generation buffer.
Allocated 15937344 bytes in shared pool for flashback generation buffer
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_08/12/2024 11:18:47
.... (PID:24475): Killing 3 processes (PIDS:32091,32186,32095) (all RFS) in order to disallow current and future RFS connections. Requested by OS process 24475
2024-08-12T11:18:49.331706+03:00
Process termination requested for pid 32186 [source = rdbms], [info = 2] [request issued by pid: 24475, uid: 1003]
2024-08-12T11:18:49.331815+03:00
Process termination requested for pid 32095 [source = rdbms], [info = 2] [request issued by pid: 24475, uid: 1003]
2024-08-12T11:18:49.331925+03:00
Process termination requested for pid 32091 [source = rdbms], [info = 2] [request issued by pid: 24475, uid: 1003]
2024-08-12T11:18:49.364992+03:00
.... (PID:24475): Database role changed from PHYSICAL STANDBY to SNAPSHOT STANDBY [kcvs.c:3246]
.... (PID:24475): Begin: SRL archival
.... (PID:24475): End: SRL archival
RESETLOGS after incomplete recovery UNTIL CHANGE 10697236627634 time 08/12/2024 11:17:23
Resetting resetlogs activation ID 1112655294 (0x4251c5be)
Online log +RECO/FLYDB2DR/ONLINELOG/group_1.841.1176655653: Thread 1 Group 1 was previously cleared
Online log +RECO/FLYDB2DR/ONLINELOG/group_2.842.1176655657: Thread 1 Group 2 was previously cleared
Online log +RECO/FLYDB2DR/ONLINELOG/group_3.843.1176655665: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 10697236627632
2024-08-12T11:18:49.620315+03:00
Setting recovery target incarnation to 3
2024-08-12T11:18:49.797536+03:00
.... (PID:24475): 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:24475): Redo network throttle feature is disabled at mount time
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby
Completed: alter database convert to snapshot standby
2024-08-12T11:19:12.405955+03:00
ARC0 (PID:31486): Becoming the 'no SRL' ARCH
alert log after alter database open .
2024-08-12T11:21:30.767210+03:00
alter database open
2024-08-12T11:21:30.823968+03:00
KZAUDIT: AUDIT_TRAIL initialization parameter is changed to OS, as DB is NOT compatible for database opened with read-only access
Smart fusion block transfer is disabled:
instance mounted in exclusive mode.
Endian type of dictionary set to little
2024-08-12T11:21:30.983736+03:00
Assigning activation ID 1240080343 (0x49ea1fd7)
Redo log for group 1, sequence 1 is not located on DAX storage
Thread 1 opened at log sequence 1
Current log# 1 seq# 1 mem# 0: +RECO/FLYDB2DR/ONLINELOG/group_1.841.1176655653
Successful open of redo thread 1
2024-08-12T11:21:31.117673+03:00
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Stopping change tracking
Undo initialization recovery: Parallel FPTR failed: start:1145541328 end:1145541332 diff:4 ms (0.0 seconds)
Undo initialization recovery: err:0 start: 1145541326 end: 1145541380 diff: 54 ms (0.1 seconds)
[24475] Successfully onlined Undo Tablespace 2.
Undo initialization online undo segments: err:0 start: 1145541381 end: 1145541604 diff: 223 ms (0.2 seconds)
2024-08-12T11:21:31.765573+03:00
Undo initialization finished serial:0 start:1145541326 end:1145541625 diff:299 ms (0.3 seconds)
Dictionary check beginning
2024-08-12T11:21:32.014820+03:00
Errors in file /u01/app/oracle/diag/diag/rdbms/FLYDB2dr/FLYDB2/trace/FLYDB2_dbw0_31229.trc:
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '+DATA/FLYDB2DR/DATAFILE/temp01.dbf'
ORA-17503: ksfdopn:2 Failed to open file +DATA/FLYDB2DR/DATAFILE/temp01.dbf
ORA-15173: entry 'temp01.dbf' does not exist in directory 'DATAFILE'
2024-08-12T11:21:32.015141+03:00
Errors in file /u01/app/oracle/diag/diag/rdbms/FLYDB2dr/FLYDB2/trace/FLYDB2_dbw0_31229.trc:
ORA-01186: file 201 failed verification tests
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '+DATA/FLYDB2DR/DATAFILE/temp01.dbf'
2024-08-12T11:21:32.015200+03:00
File 201 not verified due to error ORA-01157
2024-08-12T11:21:32.032180+03:00
Dictionary check complete
2024-08-12T11:21:32.053353+03:00
Starting background process SMCO
2024-08-12T11:21:32.076445+03:00
SMCO started with pid=30, OS id=9983
Re-creating tempfile +DATA/FLYDB2DR/DATAFILE/temp01.dbf
Database Characterset is WE8MSWIN1252
No Resource Manager plan active
2024-08-12T11:21:33.737811+03:00
**********************************************************
WARNING: Files may exists in db_recovery_file_dest
that are not known to the database. Use the RMAN command
CATALOG RECOVERY AREA to re-catalog any such files.
If files cannot be cataloged, then manually delete them
using OS command.
One of the following events caused this:
1. A backup controlfile was restored.
2. A standby controlfile was restored.
3. The controlfile was re-created.
4. db_recovery_file_dest had previously been enabled and
then disabled.
**********************************************************
joxcsys_required_dirobj_exists: directory object exists with wrong path D:\ORACLE\APP\PRODUCT\19.3.0\DB_1\JAVAVM\ADMIN\, required path is /u01/app/oracle/product/19.0.0/dbhome_4/javavm/admin/, pid 24475 cid 0
joxcsys_ensure_directory_object: created directory object with path /u01/app/oracle/product/19.0.0/dbhome_4/javavm/admin/, pid 24475 cid 0
replication_dependency_tracking turned off (no async multimaster replication found)
2024-08-12T11:21:34.808581+03:00
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Starting background process AQPC
2024-08-12T11:21:34.901960+03:00
AQPC started with pid=47, OS id=10000
2024-08-12T11:21:36.696469+03:00
Starting background process CJQ0
2024-08-12T11:21:36.892742+03:00
CJQ0 started with pid=49, OS id=10138
Completed: alter database open
2024-08-12T11:21:36.961713+03:00
QPI : Found directory objects and ORACLE_HOME out of sync
QPI : Trying to patch with the current ORACLE_HOME
QPI: ------QPI Old Directories -------
QPI: OPATCH_SCRIPT_DIR:D:\oracle\app\product\19.3.0\db_1\QOpatch
QPI: OPATCH_LOG_DIR:D:\oracle\app\product\19.3.0\db_1\rdbms\log
QPI: OPATCH_INST_DIR:D:\oracle\app\product\19.3.0\db_1\OPatch
QPI: op_scpt_path /u01/app/oracle/product/19.0.0/dbhome_4/QOpatch
QPI: Found QPI install at :/u01/app/oracle/product/19.0.0/dbhome_4/QOpatch
QPI: Self adjusting the directories
QPI: ------QPI New Directories-------
QPI: OPATCH_SCRIPT_DIR:/u01/app/oracle/product/19.0.0/dbhome_4/QOpatch
QPI: OPATCH_LOG_DIR:/u01/app/oracle/product/19.0.0/dbhome_4/rdbms/log
QPI: OPATCH_INST_DIR:/u01/app/oracle/product/19.0.0/dbhome_4/OPatch
QPI: opatch file present, opatch
QPI: qopiprep.bat file present
2024-08-12T11:21:42.015816+03:00
Shared IO Pool defaulting to 128MB. Trying to get it from Buffer Cache for process 31287.
2024-08-12T11:21:43.036143+03:00
===========================================================
Dumping current patch information
===========================================================
Patch Id: 29585399
Unique Patch Id: 22840393
Patch Description: OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
Patch Apply Time: 2019-04-18T10:21:33+03:00
Patch Type: Release Update
Patch Id: 35643107
Unique Patch Id: 25405995
Patch Description: Database Release Update : 19.21.0.0.231017 (35643107)
Patch Apply Time: 2024-05-24T13:38:41+03:00
Patch Type: Release Update
===========================================================
ConversionConversion EmoticonEmoticon