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;
---- -------------- ---------- ------------- ------------------- -------
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 !
Very useful
ReplyDeleteA little correction. You can convert physical standby to sanpshot standby even the db is read only mode.
ReplyDeleteYes, correct. We can convert snapshot db while our standby db is open in read only mode.
DeleteUseful content for quick recap
ReplyDeleteGood and detailed one.
ReplyDelete