English
Oracle Snapshot Standby Testing Best Practice
2023-11-09 | Iris Lee
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; DATABASE_ROLE OPEN_MODE PHYSICAL STANDBY MOUNTED
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 NAME TYPE VALUE 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; FLASHBACK_ON NO
Confirm that the flashback feature is not enabled on the standby database.
sys@ora11gdg> select FLASHBACK_ON from v$database; FLASHBACK_ON NO
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; DATABASE_ROLE OPEN_MODE SNAPSHOT STANDBY MOUNTED
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; DATABASE_ROLE OPEN_MODE SNAPSHOT STANDBY READ WRITE
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 ...omitted... -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 ...omitted... -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
Connected.
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;
X
SecoolerAt 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 Connected. 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; DATABASE_ROLE OPEN_MODE SNAPSHOT STANDBY MOUNTED
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 ALTER DATABASE CONVERT TO PHYSICAL STANDBY (ora11gdg) 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: ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800; 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#; SEQUENCE# FIRST_TIME NEXT_TIME APPLIED ...omitted... 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; DATABASE_ROLE OPEN_MODE PHYSICAL STANDBY READ ONLY 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 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.
Conclusion
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:
