Infrastructure at your Service

Hervé Schweitzer

A test database in one click – with Oracle Data Guard 11g

>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.

One Comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Hervé Schweitzer
Hervé Schweitzer

Chief Technology Officer (CTO) and Principal Consultant