>Perhaps you know this situation: A developper or application owner quickly needs a test database to test new code or to validate changed code before implementing it. Sounds like a lot of work, but if you have Data Guard 11g, you can simply use the command “CONVERT TO SNAPSHOT STANDBY”.
How to activate a test Database with Data Guard 11g (convert to snapshot standby)
Before you convert the physical standby database, you need to verify your Data Guard configuration is running successfully:
DGMGRL> show configuration Configuration - DBITEST Protection Mode: MaxAvailability Databases: DBITEST_SITE1 - Primary database DBITEST_SITE2 - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS
Flashback database will be used, however, only the parameter db_recovery_file_dest and db_recovery_file_dest_size must be configured on the standby database:
SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ NO SQL> show parameter recovery NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u02/fast_recovery_area db_recovery_file_dest_size big integer 8G
Now, you can convert your physical standby database to a snapshot standby database.
As soon as this command is completed, you will have a test database available with the current data.
DGMGRL> convert database 'DBITEST_SITE2' to snapshot standby; Converting database "DBITEST_SITE2" to a Snapshot Standby database, please wait... Database "DBITEST_SITE2" converted successfully DGMGRL> show configuration Configuration - DBITEST Protection Mode: MaxAvailability Databases: DBITEST_SITE1 - Primary database DBITEST_SITE2 - Snapshot standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS
Once you have changed the role of your physical standby database to snapshot standby database, it will still continue to get the changes from the primary database in order to keep it synchronized (no transport lag). However, in case of a snapshot standby database, these changes are not applied (Apply lag):
DGMGRL> show database 'DBITEST_SITE2'; Database - DBITEST_SITE2 Role: SNAPSHOT STANDBY Intended State: APPLY-OFF Transport Lag: 0 seconds Apply Lag: 3 minutes 31 seconds Instance(s): DBITEST Database Status: SUCCESS
If not done yet, flashback database will be automatically activated once you convert a physical standby database to a snapshot standby database. This ensures the flashback to physical standby database once the tests are finished.
Be careful: in this case, flashback database is only activated for this restore point:
SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ RESTORE POINT ONLY
Snapshot standby database information
When you start a Snapshot Standby Database with the convert command, a new incarnation with a new resetlogs_id is created, and the archivelog files sequence# will restart with 1. In parallel, you will always get all changes from the primary database in order to ensure the high availability of your environement.
SQL> alter system archive log current;
SQL> alter system archive log current;
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3
SQL> select NAME,SEQUENCE# ,RESETLOGS_ID,applied from v$archived_log
NAME SEQUENCE# RESETLOGS_ID APPLIED
--------------------------------------------------- --------- ------------ -------
/../archivelog/2011_01_10/o1_mf_1_369_6lq2ogcr_.arc 369 732132864 YES
/../archivelog/2011_01_10/o1_mf_1_370_6lq2ykj7_.arc 370 732132864 YES
/../archivelog/2011_01_11/o1_mf_1_371_6lr4tq8w_.arc 371 732132864 NO
/../archivelog/2011_01_11/o1_mf_1_372_6lr4tdr8_.arc 372 732132864 NO
/../archivelog/2011_01_11/o1_mf_1_373_6lr4txwb_.arc 373 732132864 NO
/../archivelog/2011_01_11/o1_mf_1_1_6lr60nx6_.arc 1 740136070 NO
/../archivelog/2011_01_11/o1_mf_1_2_6lr60pkx_.arc 2 740136070 NO
Blue: Archivelog sequence Information for the physical standby database
You can see that the sequence# 371, 372, 373 are not applied, because the snapshot standby was activated between sequence# 370 and 371
Orange: Archive log sequence information from the snapshot standby database. These archivelog files are created by the snapshot standby database, which runs as a primary database and therefore creates its own archivelog files.
With a snapshot standby database, you will be in a special situation where the online redolog and the standby redolog files are used together. Standby redolog files are used for the physical standby database in order to keep it synchronized with the primary database, while the online redolog files are used for the snapshot standby database to log the current transactions activity.
- Online redolog file Information for the snapshot standby database
SQL> select GROUP#,SEQUENCE#,ARCHIVED,STATUS from v$log;
GROUP# SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
1 1 YES INACTIVE
2 2 YES INACTIVE
3 3 NO CURRENT
- Standby redolog file information for the Physical Standby Database
SQL> select GROUP#,DBID,sequence#,ARCHIVED,STATUS from v$standby_log;
GROUP# DBID SEQUENCE# ARC STATUS
---------- --------------- ---------- --- ----------
10 UNASSIGNED 0 NO UNASSIGNED
11 UNASSIGNED 0 NO UNASSIGNED
12 449844864 374 YES ACTIVE
13 UNASSIGNED 0 YES UNASSIGNED
Snapshot Standby Database Explained Graphicaly
Database service for the snapshot standby database
In order to allow only specific users to connect to this snapshot standby database, a separate database service will be created for the snapshot standby database users:
SQL> execute DBMS_SERVICE.CREATE_SERVICE (- service_name => 'DBITEST_SNAPSHOT.dbi-services.com',- network_name => 'DBITEST_SNAPSHOT.dbi-services.com',- failover_method => 'BASIC',- failover_type => 'SELECT',- failover_retries => 1800,- failover_delay => 1 );
To activate the new created service DBITEST_SNAPSHOT, a startup trigger will be used. This trigger is activated when the database role is changed to SNAPSHOT STANDBY:
SQL> CREATE OR REPLACE TRIGGER snapshot_standby_trigger
AFTER STARTUP ON DATABASE
DECLARE
database_role VARCHAR(25);
BEGIN
SELECT database_role INTO database_role FROM v$database;
IF database_role = 'SNAPSHOT STANDBY'
THEN
DBMS_SERVICE.START_SERVICE('DBITEST_SNAPSHOT.dbi-services.com');
END IF;
END;
/
Here is the corresponding OracleNet configuration for accessing to the snapshot standby database:
DBITEST_SNAPSHOT =
(DESCRIPTION =
(FAILOVER = ON)
(LOAD_BALANCE = OFF)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DBITEST_SNAPSHOT.dbi-services.com)
)
)
What happens in a disaster case ?
In case your primary database is not longer available (crash) and your Data Guard environment must be failovered to a standby database, you can directly trigger a failover to the snapshot standby database. The failover will only take longer, because the database first needs to be converted back to a physical standby database before it can be activated as new primary database.
DGMGRL> failover to 'DBITEST_SITE2';
Converting database "DBITEST_SITE2" to a Physical Standby database, please wait...
Operation requires shutdown of instance "DBITEST" on database "DBITEST_SITE2"
Shutting down instance "DBITEST"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "DBITEST" on database "DBITEST_SITE2"
Starting instance "DBITEST"...
ORACLE instance started.
Database mounted.
Continuing to convert database "DBITEST_SITE2" ...
Operation requires shutdown of instance "DBITEST" on database "DBITEST_SITE2"
Shutting down instance "DBITEST"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "DBITEST" on database "DBITEST_SITE2"
Starting instance "DBITEST"...
ORACLE instance started.
Database mounted.
Database "DBITEST_SITE2" converted successfully
Performing failover NOW, please wait...
Failover succeeded, new primary is "DBITEST_SITE2"
DGMGRL>
Conclusion
If you need a test database to tests some scripts for a short time span, you can safely use a physical standby database and convert it to a snapshot standby database with Data Guard. You will not compromise the high availability of your database. However, if a disaster happens during your tests, the failover will take a little longer.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/HER_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2024/04/SIT_web.png)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/JDU_web-min-scaled.jpg)