Home Database Tips Oracle Snapshot Standby Testing Best Practice

Oracle Snapshot Standby Testing Best Practice

2023-11-09 | Iris Lee

Table of contents
  • What is Oracle Snapshot Standby?
  • Oracle Snapshot Standby testing best practice
  • Protect your Oracle database with a professional solution
  • Conclusion
Are you looking for a robust Oracle database backup solution? Try Vinchin Backup & Recovery!↘ Download Free Trial

What is Oracle Snapshot Standby?

Oracle 11g’s Data Guard brings us not only the remarkable feature of real-time querying through Active Data Guard but also an additional delight known as the Snapshot Standby database functionality. This functionality allows the standby database to be placed in a “read-write” mode, serving as an environment for testing purposes that may be inconvenient to conduct directly on the production primary database, such as simulating online deployments and other tasks. Once the tasks are completed in the read-write state, it becomes effortless to transition the snapshot standby database role back to its standby database role, resuming synchronization with the primary database. While in the snapshot standby database state, it can receive the primary database’s logs, but the changes cannot be applied to the standby database.

Oracle Snapshot Standby testing best practice

1. Ceasing the Redo Apply process

If the standby database is currently engaged in the Redo Apply procedure, it is necessary to terminate it first.

sys@ora11gdg> alter database recover managed standby database cancel;
Database altered.

2. Examining the current status of the standby database to ensure it is in the MOUNTED state.

sys@ora11gdg> select database_role,open_mode from v$database;

At this moment, the standby database assumes the role of a physical standby and operates in the MOUNTED mode.

3. Ensuring that the flashback recovery area has been designated.

Friendly reminder: Enabling the Snapshot Standby database functionality does not require enabling the Flashback Database feature on both the primary and standby databases. It is independent of whether the flashback database feature is enabled.

sys@ora11gdg> show parameter db_recovery_file_dest
db_recovery_file_dest string /u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size big integer 3852M

Confirm that the flashback feature is not enabled on the primary database.

sys@ora11g> select FLASHBACK_ON from v$database;

Confirm that the flashback feature is not enabled on the standby database.

sys@ora11gdg> select FLASHBACK_ON from v$database;

4. Adjusting the standby database to the Snapshot Standby state by executing a single straightforward SQL command.

sys@ora11gdg> alter database convert to snapshot standby;
Database altered.
sys@ora11gdg> select database_role,open_mode from v$database;

5. Placing the standby database in a read-write state for external access.

sys@ora11gdg> alter database open;
Database altered.
sys@ora11gdg> select database_role,open_mode from v$database;

A brand new read-write database unfolds before us.

6. Analyzing the log information during the switchover process.

Primary database alert log for Oracle 11g:

Mon Mar 19 18:46:28 2012
LNS: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3135)
LNS: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_nsa2_27302.trc:
ORA-03135: connection lost contact
Error 3135 for archive log file 2 to 'ora11gdg'
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_nsa2_27302.trc:
ORA-03135: connection lost contact
LNS: Failed to archive log 2 thread 1 sequence 50 (3135)
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_nsa2_27302.trc:
ORA-03135: connection lost contact

Standby database alert log for Oracle 11g:

Mon Mar 19 18:46:26 2012
alter database convert to snapshot standby
Starting background process RVWR
Mon Mar 19 18:46:26 2012
RVWR started with pid=26, OS id=8824
Allocated 3981204 bytes in shared pool for flashback generation buffer
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_03/19/2012 18:46:26
krsv_proc_kill: Killing 3 processes (all RFS)
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after complete recovery through change 1472476
Resetting resetlogs activation ID 4174194338 (0xf8cd26a2)
Online log /u01/app/oracle/oradata/ora11gdg/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u01/app/oracle/oradata/ora11gdg/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u01/app/oracle/oradata/ora11gdg/redo03.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 1472474
Mon Mar 19 18:46:29 2012
Setting recovery target incarnation to 5
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby
Completed: alter database convert to snapshot standby

The crucial line of information states, "Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_03/19/2012 18:46:26." This indicates the moment at which we transformed into a snapshot, facilitating the subsequent reversion.

7. Testing the snapshot standby database’s reception of primary database logs.

Even when the primary database switches logs, the standby database continues to receive the logs, albeit without applying them.

1) Log switch on the primary database.

sys@ora11g> alter system switch logfile;
System altered.

2)The alert log content recorded by the primary database

Primary database alert log for Oracle 11g:

Mon Mar 19 18:52:00 2012
Thread 1 cannot allocate new log, sequence 52
Private strand flush not complete
Current log# 3 seq# 51 mem# 0: /u01/app/oracle/oradata/ora11g/redo03.log
Mon Mar 19 18:52:00 2012
ARC3: Standby redo logfile selected for thread 1 sequence 50 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 52 (LGWR switch)
Current log# 1 seq# 52 mem# 0: /u01/app/oracle/oradata/ora11g/redo01.log
Mon Mar 19 18:52:03 2012
Archived Log entry 91 added for thread 1 sequence 51 ID 0xf8cd26a2 dest 1:
Mon Mar 19 18:52:03 2012
LNS: Standby redo logfile selected for thread 1 sequence 51 for destination LOG_ARCHIVE_DEST_2
LNS: Standby redo logfile selected for thread 1 sequence 52 for destination LOG_ARCHIVE_DEST_2

Standby database alert log for Oracle 11g:

Mon Mar 19 18:52:00 2012
RFS[5]: Assigned to RFS process 9174
RFS[5]: Identified database type as 'snapshot standby': Client is ARCH pid 27296
Mon Mar 19 18:52:00 2012
RFS[6]: Assigned to RFS process 9176
RFS[6]: Identified database type as 'snapshot standby': Client is ARCH pid 27300
RFS[6]: Selected log 4 for thread 1 sequence 50 dbid -120744030 branch 778023141
Mon Mar 19 18:52:00 2012
Archived Log entry 47 added for thread 1 sequence 50 ID 0xf8cd26a2 dest 1:
Mon Mar 19 18:52:03 2012
RFS[7]: Assigned to RFS process 9180
RFS[7]: Identified database type as 'snapshot standby': Client is LGWR ASYNC pid 27302
RFS[7]: Selected log 4 for thread 1 sequence 51 dbid -120744030 branch 778023141
Mon Mar 19 18:52:04 2012
Archived Log entry 48 added for thread 1 sequence 51 ID 0xf8cd26a2 dest 1:
RFS[7]: Selected log 4 for thread 1 sequence 52 dbid -120744030 branch 778023141

3) Examining the content of log files in the archive directories of the primary and standby databases

(1) Archive log files of the primary database:

ora11g@secdb /home/oracle/arch/ora11g$ ls -ltr
total 879M
-rw-r----- 1 oracle oinstall 1.1M Mar 19 18:51 1_50_778023141.arc
-rw-r----- 1 oracle oinstall 363K Mar 19 18:52 1_51_778023141.arc

(2) Archive log files of the standby database:

ora11g@secdb /home/oracle/arch/ora11gdg$ ls -ltr
total 847M
-rw-r----- 1 oracle oinstall 1.1M Mar 19 18:52 1_50_778023141.arc
-rw-r----- 1 oracle oinstall 363K Mar 19 18:52 1_51_778023141.arc

As observed, the standby database has successfully received the log files sent from the primary database.

8. Creating a user, tables, and initializing data in a snapshot standby database.

sys@ora11gdg> create user ocmu identified by ocmu;
User created.
secooler@ora11gdg> grant dba to ocmu;
Grant succeeded.
secooler@ora11gdg> conn ocmu/ocmu
ocmu@ora11gdg> create table t (x varchar2(8));
Table created.
ocmu@ora11gdg> insert into t values ('Secooler');
1 row created.
ocmu@ora11gdg> commit;
Commit complete.
ocmu@ora11gdg> select * from t;

At this moment, the standby database is in a modifiable and adjustable state, which corresponds to the desired “READ WRITE” mode.

It is crucial to note that the implementation of Snapshot Standby database functionality is fundamentally based on the principle of flashback data. Therefore, any action that may hinder the ability to flashback the database should be avoided here. Otherwise, the snapshot standby database will be unable to return to its previous standby recovery state.

9. Restoring the snapshot standby database to a physical standby database

1) Restart the standby database to the MOUNTED state.

ocmu@ora11gdg> conn / as sysdba
sys@ora11gdg> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ora11gdg> startup mount
ORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1336232 bytes
Variable Size 268438616 bytes
Database Buffers 37748736 bytes
Redo Buffers 6336512 bytes
Database mounted.
sys@ora11gdg> select database_role,open_mode from v$database;

2) Execute a command to restore the original identity of the physical standby database.

sys@ora11gdg> alter database convert to physical standby;
Database altered.

3) The alert log of the standby database meticulously documents this transition process.

Mon Mar 19 19:30:24 2012
alter database convert to physical standby
Flashback Restore Start
Flashback Restore Complete
Stopping background process RVWR
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORA11GDG/flashback/o1_mf_7pg3n2jc_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORA11GDG/flashback/o1_mf_7pg52yst_.flb
Guaranteed restore point dropped
Clearing standby activation ID 4174523254 (0xf8d22b76)
The primary database controlfile was created using the
'MAXLOGFILES 30' clause.
There is space for up to 27 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
Completed: alter database convert to physical standby

From the alert log, it can be inferred that the recovery method used the Flashback Database feature. This implies that even if the standby database was not running in flashback database mode, the role transition of the standby database can still be accomplished using Flashback Database functionality.

4) Restart the standby database in the automatic recovery log mode.

(1) At this point, the database is in NOMOUNTED state and needs to be restarted.

Please note that it requires a database restart instead of using the ALTER command, otherwise the following error message will be encountered:

sys@ora11gdg> alter database mount;
alter database mount
l ERROR at line 1:
ORA-00750: database has been previously mounted and dismounted
sys@ora11gdg> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
sys@ora11gdg> startup mount;
ORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1336232 bytes
Variable Size 268438616 bytes
Database Buffers 37748736 bytes
Redo Buffers 6336512 bytes
Database mounted.
sys@ora11gdg> alter database recover managed standby database disconnect;
Database altered.

(2) By examining the alert log of the standby database, the recovery process can be observed in a lucid manner.

Mon Mar 19 19:43:48 2012
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 4 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Clearing online redo logfile 1 /u01/app/oracle/oradata/ora11gdg/redo01.log
Clearing online log 1 of thread 1 sequence number 1
Completed: alter database recover managed standby database disconnect
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /u01/app/oracle/oradata/ora11gdg/redo02.log
Clearing online log 2 of thread 1 sequence number 2
Clearing online redo logfile 2 complete
Media Recovery Log /home/oracle/arch/ora11gdg/1_49_778023141.arc
Media Recovery Log /home/oracle/arch/ora11gdg/1_50_778023141.arc
Media Recovery Log /home/oracle/arch/ora11gdg/1_51_778023141.arc
Media Recovery Log /home/oracle/arch/ora11gdg/1_52_778023141.arc
Media Recovery Log /home/oracle/arch/ora11gdg/1_53_778023141.arc
Media Recovery Log /home/oracle/arch/ora11gdg/1_54_778023141.arc
Media Recovery Waiting for thread 1 sequence 55

(3) Monitor the log application status by inspecting the dynamic performance view V$ARCHIVED_LOG.

sys@ora11gdg> select sequence#, first_time, next_time, applied from v$archived_log order by sequence#;
49 20120319 18:32:32 20120319 18:38:03 YES
50 20120319 18:38:03 20120319 18:51:00 YES
51 20120319 18:51:00 20120319 18:52:03 YES
52 20120319 18:52:03 20120319 19:09:57 YES
53 20120319 19:09:57 20120319 19:10:15 YES
54 20120319 19:10:15 20120319 19:10:25 YES
52 rows selected.

10. Enabling the standby database to enter the READ ONLY state to verify that the operations performed on the snapshot standby database before have been rolled back.

sys@ora11gdg> alter database recover managed standby database cancel;
Database altered.
sys@ora11gdg> alter database open read only;
Database altered.
sys@ora11gdg> select database_role,open_mode from v$database;
sys@ora11gdg> select username from dba_users where username = 'OCMU';
no rows selected

The conclusion has been confirmed as the test user “OCMU” that was previously created does not exist.

Protect your Oracle database with a professional solution

Oracle Snapshot Standby is very useful for testing purposes. However, to further protect your database environment, it is recommended to backup your Oracle database with a professional backup and disaster recovery solution.

Vinchin Backup & Recovery

Vinchin Backup & Recovery delivers powerful functionality to protect your databases in both virtual machines and physical servers, which is quite automatic, flexible and efficient. It provides multi-type database protection of Oracle DB, MySQL, SQL Server, Postgres Pro and MariaDB, supporting database compression, centralized job management, smart backup strategies, hot database backup and advanced SQL Server/Oracle support. Besides, it also supports powerful ransomware protection feature and V2V migration across 10+ virtual platforms.

Vinchin Backup & Recovery has been selected by thousands of companies and you can also start to use this powerful system with a 60-day full-featured trial! Also, contact us and leave your needs, and then you will receive a solution according to your IT environment.


The remarkable feature of the “Snapshot Standby database” allows the standby database to temporarily become a read-write independent database. This greatly expands the application possibilities of the standby database. By leveraging this special functionality of the standby database, you can safely test and reproduce issues that might be "risky" to simulate and reproduce in the production environment. Once the testing is completed, you can restore its identity as a physical standby database and proceed with log recovery.

Share on:

Categories: Database Tips
You May Also Like...