Snapshot Standby database in Oracle 19c

Environment Details:

Database: Oracle Database 19.0.0.0 EE

Operating System: Windows 11 Pro

What is Snapshot Standby database? Snapshot Standby database is a read-write copy of physical standby database created by converting physical standby database into Snapshot Standby. Snapshot Standby database is needed when we require temporary updatable database copy for testing purpose. The updatable copy will be discarded once testing is done before the snapshot standby database is converted back into physical standby database. Snapshot Standby database receives redo from Primary database but does not apply until the snapshot standby is converted back into physical standby.

What we cannot do in Snapshot Standby database? - We cannot perform a switchover activity to a snapshot standby database. Its possible after converting back into physical standby database. - We can not configure a snapshot standby database as a fast-start failover target. - We cannot apply logs or start MRP in Snapshot standby database.

Steps to convert physical standby database into Snapshot Standby database: In earlier database release i.e. in 10g, we used Flashback Technique with guaranteed restore point to open physical standby in read-write mode, but from 11g onwards, restore point is implicitly created when you execute snapshot database command.   If you want to go with manual method i.e. old flashback technique(Oracle 10g), then refer "How to open physical standby database in read write mode using Flashback Technique" OR  Use below snapshot standby method which will automatically create guaranteed restore point. Step1: Ensure your primary and standby database are in sync. Primary: SQL> set lines 300 pages 3000 SQL> select name,db_unique_name,open_mode,database_role,protection_mode,controlfile_type from v$database; NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE CONTROL ---- -------------- ---------- ------------- ------------------- ------- PR PR READ WRITE PRIMARY MAXIMUM PERFORMANCE CURRENT SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) --------------            370 Standby: SQL> set lines 300 pages 3000 SQL> select name,db_unique_name,open_mode,database_role,protection_mode,controlfile_type from v$database; NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE    PROTECTION_MODE     CONTROL ---- -------------- --------- ---------------- ------------------- ------- PR   DR             READ ONLY PHYSICAL STANDBY MAXIMUM PERFORMANCE STANDBY SQL> select max(sequence#) from v$archived_log where applied='YES'; MAX(SEQUENCE#) --------------            370

Step 2: You can convert physical standby database into snapshot standby while your physical standby is in either mounted mode or in read only mode.  SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 2147482432 bytes Fixed Size                  9030464 bytes Variable Size             452984832 bytes Database Buffers         1677721600 bytes Redo Buffers                7745536 bytes Database mounted. OR SQL> select name,db_unique_name,open_mode,database_role,protection_mode,controlfile_type from v$database; NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE    PROTECTION_MODE     CONTROL ---- -------------- --------- ---------------- ------------------- ------- PR   DR             READ ONLY PHYSICAL STANDBY MAXIMUM PERFORMANCE STANDBY

Step 3: Set below parameters to generate flashback logs. Set db_recovery_file_dest_size first and then db_recovery_file_dest parameters.  SQL> alter system set db_recovery_file_dest_size=2g; System altered. SQL> alter system set db_recovery_file_dest='D:\RUPESH\Setups\Standby\FLB'; System altered. SQL> show parameter db_recovery NAME                         TYPE        VALUE ---------------------------- ----------- ------------------------------ db_recovery_file_dest        string      D:\RUPESH\Setups\Standby\FLB db_recovery_file_dest_size   big integer 2G

Step 4: Now enable the flashback in physical standby database. To turn on the flashback, MRP must be down. You can convert the snapshot standby database without enabling flashback as well as the convert command will automatically enable flashback with status "RESTORE POINT ONLY" which means Flashback is on but you can only flashback to guaranteed restore points.  SQL> recover managed standby database cancel; Media recovery complete. SQL> alter database flashback on; Database altered. SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ YES SQL> select name from v$restore_point; no rows selected

Step 5: Now its time to convert physical standby database into snapshot standby database. SQL> alter database convert to snapshot standby; Database altered. SQL> col name for a40 SQL> col TIME for a34 SQL> select scn,GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE,TIME,NAME from v$restore_point;    SCN   GUA STORAGE_SIZE TIME                          NAME -------- --- ------------ -------------------------------  --------------------------------------------- 10179700 YES 209715200    20-AUG-23 04.14.53.000000000 PM   SNAPSHOT_STANDBY_REQUIRED_08/20/2023 16:14:53    Physical Standby Alert Log File: 2023-08-20T16:14:53.435650+05:30 alter database convert to snapshot standby 2023-08-20T16:14:53.609248+05:30 Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_08/20/2023 16:14:53 .... (PID:13588): Killing 2 processes (PIDS:7348,12632) (all RFS) in order to disallow current and future RFS connections. Requested by OS process 13588 2023-08-20T16:14:54.988602+05:30 .... (PID:13588): Database role changed from PHYSICAL STANDBY to SNAPSHOT STANDBY [kcvs.c:3243] .... (PID:13588): Begin: SRL archival .... (PID:13588): End: SRL archival RESETLOGS after incomplete recovery UNTIL CHANGE 10179701 time 08/20/2023 16:07:58 Resetting resetlogs activation ID 2835307338 (0xa8ff5b4a) Online log D:\RUPESH\SETUPS\STANDBY\DB\DR\REDO01.LOG: Thread 1 Group 1 was previously cleared Online log D:\RUPESH\SETUPS\STANDBY\DB\DR\REDO02.LOG: Thread 1 Group 2 was previously cleared Online log D:\RUPESH\SETUPS\STANDBY\DB\DR\REDO03.LOG: Thread 1 Group 3 was previously cleared Standby became primary SCN: 10179699 2023-08-20T16:14:55.478836+05:30 Setting recovery target incarnation to 3 2023-08-20T16:14:55.542745+05:30 .... (PID:13588): 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 2023-08-20T16:15:24.888621+05:30 Now check the database status and open the database. SQL> select name,open_mode,database_role,controlfile_type from v$database; NAME    OPEN_MODE   DATABASE_ROLE    CONTROL ------- ----------- ---------------- ------- PR      MOUNTED     SNAPSHOT STANDBY CURRENT Note that once you converted the physical standby database into snapshot and you want to go back to physical standby immediately without opening the database into read-write, you must open the database into read-write mode first, otherwise you will face below error message. SQL> alter database convert to physical standby; alter database convert to physical standby * ERROR at line 1: ORA-16433: The database or pluggable database must be opened in read/write mode. SQL> alter database open; Database altered. Physical Standby Alert Log File: 2023-08-20T16:18:29.373019+05:30 alter database open 2023-08-20T16:18:29.404271+05:30 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 2023-08-20T16:18:29.591755+05:30 Assigning activation ID 2839889992 (0xa9454848) 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: D:\RUPESH\SETUPS\STANDBY\DB\DR\REDO01.LOG Successful open of redo thread 1 2023-08-20T16:18:29.701147+05:30 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Stopping change tracking Undo initialization recovery: Parallel FPTR failed: start:64382421 end:64382437 diff:16 ms (0.0 seconds) Undo initialization recovery: err:0 start: 64382421 end: 64382468 diff: 47 ms (0.0 seconds) [13588] Successfully onlined Undo Tablespace 2. Undo initialization online undo segments: err:0 start: 64382484 end: 64382562 diff: 78 ms (0.1 seconds) Undo initialization finished serial:0 start:64382421 end:64382578 diff:157 ms (0.2 seconds) Dictionary check beginning Dictionary check complete Database Characterset is AL32UTF8 2023-08-20T16:18:30.375392+05:30 No Resource Manager plan active 2023-08-20T16:18:30.953780+05:30 ********************************************************** 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 required path D:\RUPESH\SETUPS\WINDOWS.X64_193000_DB_HOME\JAVAVM\ADMIN\, pid 3392 cid 0 replication_dependency_tracking turned off (no async multimaster replication found) 2023-08-20T16:18:31.834112+05:30 LOGSTDBY: Validating controlfile with logical metadata LOGSTDBY: Validation complete Starting background process AQPC 2023-08-20T16:18:31.942898+05:30 AQPC started with pid=40, OS id=18668  Starting background process CJQ0 2023-08-20T16:18:32.445918+05:30 CJQ0 started with pid=42, OS id=4744  2023-08-20T16:18:33.456076+05:30 Completed: alter database open Check the database status again. SQL>  select name,open_mode,database_role,controlfile_type from v$database; NAME OPEN_MODE    DATABASE_ROLE    CONTROL ---- ------------ ---------------- ------- PR   READ WRITE   SNAPSHOT STANDBY CURRENT

Step 6: You can perform read-write testing now on Snapshot Standby database. During the testing, if you want to create new tablespace or add any data file then you can do the same which will be dropped/deleted or discarded before converting back into physical standby database once you execute "alter database convert to physical standby" command.

Step 7: Once all testing is done then refer below steps to convert the snapshot standby database back into physical standby database. Note that your database must be in mounted mode to convert back into physical standby database otherwise you will face below error message. NAME DB_UNIQUE_NAME OPEN_MODE  DATABASE_ROLE    PROTECTION_MODE     CONTROL ---- -------------- ---------- ---------------- ------------------- ------- PR   DR             READ WRITE SNAPSHOT STANDBY MAXIMUM PERFORMANCE CURRENT SQL> alter database convert to physical standby; alter database convert to physical standby * ERROR at line 1: ORA-01126: database must be mounted in this instance and not open in any instance Shut down the snapshot standby database first and start it in mount mode. SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 2147482432 bytes Fixed Size                  9030464 bytes Variable Size             452984832 bytes Database Buffers         1677721600 bytes Redo Buffers                7745536 bytes Database mounted. SQL> alter database convert to physical standby; Database altered. Standby Alert Log File: 2023-08-20T16:33:12.298520+05:30 alter database convert to physical standby 2023-08-20T16:33:12.558173+05:30 Flashback Restore Start 2023-08-20T16:33:13.310460+05:30 Deleted file D:\RUPESH\SETUPS\STANDBY\DB\DR\USERS02.DBF Flashback: deleted datafile #2 in tablespace #4 of pdb #0 from control file. Deleted file D:\RUPESH\SETUPS\STANDBY\DB\DR\TEST_FLB01.DBF Flashback: deleted datafile #5 in tablespace #6 of pdb #0 from control file. Flashback: dropped tablespace #6: 'TEST_FLB' of pdb #0 from the control file. 2023-08-20T16:33:13.624656+05:30 Flashback Restore Complete 2023-08-20T16:33:14.403434+05:30 Drop guaranteed restore point  Guaranteed restore point  dropped 2023-08-20T16:33:14.485667+05:30 .... (PID:16464): Database role cleared from SNAPSHOT STANDBY [kcvs.c:8969] Clearing standby activation ID 2839889992 (0xa9454848) 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:16464): Database role changed from PRIMARY to PHYSICAL STANDBY [kcvs.c:8974] .... (PID:16464): RT: Role transition work is not done .... (PID:16464): Redo network throttle feature is disabled at mount time Physical Standby Database mounted. 2023-08-20T16:33:15.063275+05:30 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 2023-08-20T16:33:54.724299+05:30 You can see in the above logs that newly created tablespace and data files are dropped from snapshot standby database. Turn the flashback off to delete flashback logs to free up the FRA space. The recover file destination parameters will be reset after converting to physical standby database. SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ YES SQL> select name from v$restore_point; no rows selected SQL>  alter database flashback off; Database altered. SQL> show parameter db_recovery NAME                                 TYPE        VALUE ------------------------------------ ----------- ------ db_recovery_file_dest                string db_recovery_file_dest_size           big integer 0 SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ NO You can start the MRP process and monitor the gap between Primary and Standby database. SQL> recover managed standby database disconnect from session; Media recovery complete. Standby Alert Log File: 2023-08-20T16:43:03.054049+05:30  alter database flashback off Stopping background process RVWR 2023-08-20T16:43:04.079890+05:30 Deleted Oracle managed file D:\RUPESH\SETUPS\STANDBY\FLB\DR\FLASHBACK\O1_MF_LG3V8BOZ_.FLB Deleted Oracle managed file D:\RUPESH\SETUPS\STANDBY\FLB\DR\FLASHBACK\O1_MF_LG3V8CKV_.FLB Flashback Database Disabled  Completed:  alter database flashback off 2023-08-20T16:43:11.942341+05:30 ALTER DATABASE RECOVER  managed standby database disconnect from session   Starting background process MRP0 2023-08-20T16:43:12.005824+05:30 MRP0 started with pid=30, OS id=10128  2023-08-20T16:43:17.069644+05:30  Started logmerger process 2023-08-20T16:43:17.101037+05:30 PR00 (PID:14820): Managed Standby Recovery starting Real Time Apply 2023-08-20T16:43:17.447203+05:30 Parallel Media Recovery started with 8 slaves 2023-08-20T16:43:17.479029+05:30 Stopping change tracking 2023-08-20T16:43:17.542208+05:30 TT02 (PID:6992): Waiting for all non-current ORLs to be archived 2023-08-20T16:43:17.557223+05:30 TT02 (PID:6992): All non-current ORLs have been archived TT02 (PID:6992): Clearing ORL LNO:1 D:\RUPESH\SETUPS\STANDBY\DB\DR\REDO01.LOG Clearing online log 1 of thread 1 sequence number 1 2023-08-20T16:43:17.732264+05:30 PR00 (PID:14820): Media Recovery Log D:\RUPESH\SETUPS\STANDBY\ARCH\1_374_1134494132.ARC 2023-08-20T16:43:18.048409+05:30 PR00 (PID:14820): Media Recovery Log D:\RUPESH\SETUPS\STANDBY\ARCH\1_375_1134494132.ARC 2023-08-20T16:43:18.095970+05:30 TT02 (PID:6992): Clearing ORL LNO:1 complete 2023-08-20T16:43:18.255486+05:30 PR00 (PID:14820): Media Recovery Log D:\RUPESH\SETUPS\STANDBY\ARCH\1_376_1134494132.ARC 2023-08-20T16:43:18.446231+05:30 PR00 (PID:14820): Media Recovery Log D:\RUPESH\SETUPS\STANDBY\ARCH\1_377_1134494132.ARC 2023-08-20T16:43:18.684156+05:30 PR00 (PID:14820): Media Recovery Log D:\RUPESH\SETUPS\STANDBY\ARCH\1_378_1134494132.ARC 2023-08-20T16:43:18.842589+05:30 PR00 (PID:14820): Media Recovery Log D:\RUPESH\SETUPS\STANDBY\ARCH\1_379_1134494132.ARC PR00 (PID:14820): Media Recovery Waiting for T-1.S-380 (in transit) 2023-08-20T16:43:19.030523+05:30 Recovery of Online Redo Log: Thread 1 Group 4 Seq 380 Reading mem 0   Mem# 0: D:\RUPESH\SETUPS\STANDBY\SRLS\STANDBY01.LOG 2023-08-20T16:43:19.046167+05:30 Completed: ALTER DATABASE RECOVER  managed standby database disconnect from session   2023-08-20T16:43:23.205912+05:30 TT02 (PID:6992): Waiting for all non-current ORLs to be archived 2023-08-20T16:43:23.238704+05:30 TT02 (PID:6992): All non-current ORLs have been archived On Primary: SQL> set lines 300 pages 3000 SQL> select name,db_unique_name,open_mode,database_role,protection_mode,controlfile_type from v$database; NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE    PROTECTION_MODE     CONTROL ---- -------------- --------- ---------------- ------------------- ------- PR   DR             MOUNTED   PHYSICAL STANDBY MAXIMUM PERFORMANCE STANDBY SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) --------------            379 On Standby: SQL> set lines 300 pages 3000 SQL> select name,db_unique_name,open_mode,database_role,protection_mode,controlfile_type from v$database; NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE    PROTECTION_MODE     CONTROL ---- -------------- --------- ---------------- ------------------- ------- PR   DR             READ ONLY PHYSICAL STANDBY MAXIMUM PERFORMANCE STANDBY SQL> select max(sequence#) from v$archived_log where applied='YES'; MAX(SEQUENCE#) --------------            379

Thanks for reading this post ! Please comment if you like this post ! Click on FOLLOW to get future blog updates !