Using Oracle 11g's Active Data Guard and Snapshot Standby FeaturesAugust 21, 2009 Synopsis. Oracle Database 11g expands dramatically the ability to leverage a physical standby database for simultaneous support of both disaster recovery as well as intensive query activity. This article – the fifth in this ongoing series – also illustrates how Oracle 11g’s new snapshot standby features leverage a physical standby database for application quality assurance testing, including the new Real Application Testing suite. The prior article in this series explored how to:
This article will discuss:
Active Data Guard: Real-Time Queries During Managed RecoveryPrior to Oracle Database 11g, the DBA could opt for one of two mutually-exclusive choices for a physical standby databases operating mode:
While it was certainly possible to maintain two physical standby databases – one in managed recovery mode, and another in READ ONLY mode – to satisfy both requirements, it also potentially increased licensing costs and the complexity of the Data Guard environment. Thankfully, Oracle 11g now offers the capability to satisfy both modes within one physical standby database with Real Time Query, part of the separately licensable Active Data Guard option. Enabling Active Data Guard. To activate this feature, I’ll first disable application of redo on the physical standby database. Since I’ve already enabled a Data Guard configuration, I’ll issue the appropriate commands via the DGMGRL command line utility against that database:
[oracle@11gPrimary ~]$ echo $ORACLE_SID
orcl_stdby1
$> dgmgrl
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle
Connected.
DGMGRL> edit database orcl_stdby1 set state=apply-off;
Succeeded.
DGMGRL> show database orcl_stdby1
Database
Name: orcl_stdby1
Role: PHYSICAL STANDBY
Enabled: YES
Intended State: APPLY-OFF
Instance(s):
orcl_stdby1
Next, I’ll connect to the physical standby database and issue the ALTER DATABASE OPEN; command from within a SQL*Plus session to activate Active Data Guard. I’ve shown the results of issuing this command reflected in the alert log of the ORCL_STDBY1 database in Listing 5.1. Finally, I’ll reactivate redo application via DGMRGL commands:
DGMGRL> edit database orcl_stdby1 set state=apply-on;
Succeeded.
DGMGRL> show database orcl_stdby1;
Database
Name: orcl_stdby1
Role: PHYSICAL STANDBY
Enabled: YES
Intended State: APPLY-ON
Instance(s):
orcl_stdby1
At this point, the physical standby database is open in READ ONLY mode, but it’s simultaneously receiving and applying redo from its counterpart primary database. Disabling Active Data Guard. Once activated, Active Data Guard’s Real-Time Query feature is equally simple to deactivate. All the DBA needs to do is:
Since the physical standby database has been already configured for managed recovery via Data Guard Broker, the database’s DMON background process will automatically restart the Managed Recovery Process (MRP0) once redo application has been re-established. Leveraging Snapshot Standby Database for Effective Application TestingWhile I’ve got to admit that the new Active Data Guard features are impressive, Oracle 11g Data Guard also offers a new type of standby database - a snapshot standby - that simultaneously provides disaster recovery capabilities while also performing quality assurance testing against the same database because it’s also open in READ WRITE mode. A snapshot standby database therefore promises several advantages not available in prior releases: Reduced database licensing costs. Since I can now easily transform a physical standby database from its disaster recovery mode into application testing mode, this translates into one less Oracle 11g database that needs to be licensed. Reduced hardware costs. One less Oracle database also means one less database server. Now instead of having to justify the additional hardware costs of providing a QA environment, I’m able to provide it for essentially the same cost as the disaster recovery environment. And I can take advantage of those savings to justify duplicating the hardware environment for the primary database server because it will eliminate one additional variable – the system configuration itself - when I’m deep in the throes of system or integration testing of a new application software release. Real Application Testing. A snapshot standby database is a perfect complement to Oracle 11g’s new Real Application Testing suite because it’s an excellent platform on which to replay a previously-recorded workload. The DBA can capture a representative workload from any Oracle 9iR2 (release 9.2.0.8) Oracle 10gR2 (release 10.2.0.4), or Oracle 11gR1 (release 11.1.0.6) database and then play it back on a snapshot standby database that’s configured in what I’ve termed “production plus one” (P+1) mode to determine if any performance, data, or error regression might occur when transitioning the application workload to Oracle 11g. (Please see my article series on Database Workload Capture and Replay for an in-depth discussion of this topic.) Preconditions. It’s important to note some key prerequisites for creating a standby database:
Creating a Snapshot Standby Database: An ExampleOracle Database 11g leverages the powerful Flashback Database features introduced in Oracle 10g to implement the transition between physical standby and snapshot standby modes, and the Data Guard Broker accomplishes the transition with a single command: CONVERT DATABASE. Transitioning from physical standby mode to snapshot standby mode involves three phases: 1.) Managed recovery is terminated. First, the physical standby database’s Managed Recovery Process (MRP0) is shut down, and then the physical standby database is shut down. Note that redo logs will still be received at the standby site – they just aren’t going to be applied until after physical standby mode is restored at a later time. 2.) A guaranteed restore point is created. Next, the physical standby database is opened in MOUNT mode and a guaranteed restore point is created. Once a guaranteed restore point is created, Oracle literally guarantees that all files necessary to perform a point-in-time recovery of the database to that specified point in time will be retained until the guaranteed restore point is destroyed. (Without a guaranteed restore point, Oracle might start deleting Flashback Logs in FIFO order when the database’s Flash Recovery Area approached overflow capacity, and that would make it impossible to “rewind” the physical standby database to the state it was in prior to becoming a snapshot standby database.) 3) A newly-incarnated snapshot standby database is opened. Finally, the physical standby database is opened in READ WRITE mode as a snapshot standby by incrementing its current incarnation. At this point, any user can now access the snapshot standby database if it were an exact copy of the production database – which, of course, it is! – and perform any desired application testing until either the testing phase is completed, or disaster recovery is required. To illustrate how to transmogrify a physical standby into a snapshot standby database via Data Guard Broker, I’ll first create a few new database objects in my production database so I can modify them after the snapshot database has been activated:
-----
-- Create new sequence
-----
DROP SEQUENCE hr.seq_empl_id;
CREATE SEQUENCE hr.seq_empl_id
MINVALUE 0
MAXVALUE 999999999999
START WITH 100
CACHE 1000
NOCYCLE;
-----
-- Create new table
-----
DROP TABLE hr.new_employees PURGE;
CREATE TABLE hr.new_employees
TABLESPACE example
AS
SELECT employee_id, last_name, first_name, salary
FROM hr.employees
WHERE 1 = 0;
-----
-- Now populate additional data into the table
-----
INSERT INTO hr.new_employees
SELECT hr.seq_empl_id.NEXTVAL, last_name, first_name, salary FROM hr.employees;
INSERT INTO hr.new_employees
SELECT hr.seq_empl_id.NEXTVAL, last_name, first_name, salary FROM hr.new_employees;
INSERT INTO hr.new_employees
SELECT hr.seq_empl_id.NEXTVAL, last_name, first_name, salary FROM hr.new_employees;
INSERT INTO hr.new_employees
SELECT hr.seq_empl_id.NEXTVAL, last_name, first_name, salary FROM hr.new_employees;
INSERT INTO hr.new_employees
SELECT hr.seq_empl_id.NEXTVAL, last_name, first_name, salary FROM hr.new_employees;
INSERT INTO hr.new_employees
SELECT hr.seq_empl_id.NEXTVAL, last_name, first_name, salary FROM hr.new_employees;
INSERT INTO hr.new_employees
SELECT hr.seq_empl_id.NEXTVAL, last_name, first_name, salary FROM hr.new_employees;
COMMIT;
-----
-- Create primary key (PK) index and constraint
-----
ALTER TABLE hr.new_employees
ADD CONSTRAINT new_employees_pk
PRIMARY KEY (employee_id)
USING INDEX (
CREATE INDEX hr.new_employees_pk_idx
ON hr.new_employees (employee_id)
TABLESPACE example
);
Here’s the results of some queries run against the production database’s data dictionary and the new table I created there:
SELECT
owner
,index_name
FROM dba_indexes
WHERE table_owner = 'HR'
AND table_name = 'NEW_EMPLOYEES';
OWNER INDEX_NAME
------------------------------ ------------------------------
HR NEW_EMPLOYEES_PK_IDX
-----
-- Capture some statistics from the newly-created table
-----
SELECT
COUNT(*) tot_emps
,MIN(employee_id)
,MAX(employee_id)
,AVG(salary)
,MIN(salary)
,MAX(salary)
FROM hr.new_employees;
TOT_EMPS MIN(EMPLOYEE_ID) MAX(EMPLOYEE_ID) AVG(SALARY) MIN(SALARY) MAX(SALARY)
---------- ---------------- ---------------- ----------- ----------- -----------
6848 100 6947 6517.75701 2100 30000
Next, I’ll open a DGMGRL session, connect to my primary database, and issue the appropriate flavor of the CONVERT DATABASE command to complete the transformation of the current physical standby database (ORCL_STDBY1) to a snapshot standby database:
[oracle@11gPrimary ~]$ dgmgrl
DGMGRL for Linux: Version 11.1.0.6.0 - Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle
Connected.
DGMGRL> show configuration
Configuration
Name: MAA_orcl
Enabled: YES
Protection Mode: MaxPerformance
Databases:
orcl_primary - Primary database
orcl_stdby1 - Physical standby database
Fast-Start Failover: DISABLED
Current status for "MAA_orcl":
SUCCESS
DGMGRL> CONVERT DATABASE orcl_stdby1 TO SNAPSHOT STANDBY;
Converting database "orcl_stdby1" to a Snapshot Standby database, please wait...
Database "orcl_stdby1" converted successfully
DGMGRL> show configuration
Configuration
Name: MAA_orcl
Enabled: YES
Protection Mode: MaxPerformance
Databases:
orcl_primary - Primary database
orcl_stdby1 - Snapshot standby database
Fast-Start Failover: DISABLED
Current status for "MAA_orcl":
SUCCESS
As shown in Listing 5.2, the successful conversion of the original physical standby database to a snapshot standby database is also reflected in the database’s alert log. I can also confirm that the snapshot standby database is indeed a separate incarnation of its “parent” physical standby database by querying the V$DATABASE_INCARNATION and V$DATABASE dynamic views:
TTITLE 'Database Incarnations|(from V$DATABASE_INCARNATION)'
COL incarnation# FORMAT 9999999 HEADING 'Incar-|nation'
COL resetlogs_id FORMAT 99999999999 HEADING 'ResetLogs|ID'
COL prior_incarnation# FORMAT 9999999 HEADING 'Prior|Incar-|nation'
COL status FORMAT A16 HEADING 'Status'
COL rsl_dtm FORMAT A11 HEADING 'Reset|Logs|Timestamp' WRAP
COL prior_rsl_dtm FORMAT A11 HEADING 'Prior|Reset|Logs|Timestamp' WRAP
COL fba_flag FORMAT A05 HEADING 'Flash|Back|DB|Alwd?'
SELECT
incarnation#
,resetlogs_id
,resetlogs_change# rsl_chg#
,TO_CHAR(resetlogs_time, 'yyyy-mm-dd hh24:mi:ss') rsl_dtm
,prior_incarnation#
,TO_CHAR(prior_resetlogs_time, 'yyyy-mm-dd hh24:mi:ss') prior_rsl_dtm
,status
,flashback_database_allowed fba_flag
FROM v$database_incarnation
;
TTITLE OFF
Database Incarnations
(from V$DATABASE_INCARNATION)
Prior Flash
Reset Prior Reset Back
Incar- ResetLogs Logs Incar- Logs DB
nation ID RSL_CHG# Timestamp nation Timestamp Status Alwd?
-------- ------------ ---------- ----------- -------- ----------- ---------------- -----
1 629600782 1 2007-08-03 0 PARENT NO
01:06:22
2 682541003 522753 2009-03-26 1 2007-08-03 PARENT NO
18:43:23 01:06:22
3 692737822 4458754 2009-07-20 2 2009-03-26 ORPHAN NO
19:10:22 18:43:23
4 692911765 4957899 2009-07-22 2 2009-03-26 ORPHAN YES
19:29:25 18:43:23
5 693763762 4970489 2009-08-01 2 2009-03-26 PARENT NO
16:09:22 18:43:23
6 694880645 5045967 2009-08-14 5 2009-08-01 CURRENT NO
14:24:05 16:09:22
6 rows selected.
Prior Prior
SQL> SQL> TTITLE 'Database Status|(From V$DATABASE)'
COL name FORMAT A12 HEADING 'Database|Name'
COL current_scn FORMAT 99999999 HEADING 'Current SCN'
COL rlc_nbr FORMAT 9999999 HEADING 'ResetLogs|Change #'
COL rlc_dtm FORMAT A11 HEADING 'ResetLogs|Timestamp' WRAP
COL prlc_nbr FORMAT 9999999 HEADING 'Prior|ResetLogs|Change #'
COL prlc_dtm FORMAT A11 HEADING 'Prior|ResetLogs|Timestamp' WRAP
SELECT
name
,current_scn
,resetlogs_change# rlc_nbr
,TO_CHAR(resetlogs_time, 'yyyy-mm-dd hh24:mi:ss') rlc_dtm
,prior_resetlogs_change# prlc_nbr
,TO_CHAR(prior_resetlogs_time, 'yyyy-mm-dd hh24:mi:ss') prlc_dtm
FROM v$database
;
TTITLE OFF
Database Status
(From V$DATABASE)
Prior Prior
Database ResetLogs ResetLogs ResetLogs ResetLogs
Name Current SCN Change # Timestamp Change # Timestamp
------------ ----------- --------- ----------- --------- -----------
ORCL 5046338 5045967 2009-08-14 4970489 2009-08-01
14:24:05 16:09:22
Now that my snapshot standby database is available for READ WRITE access, I’ll simulate the deployment of required application changes. I’ll add two new indexes against the HR.NEW_EMPLOYEES table and insert a few more rows into the table, then query the data dictionary and the table to prove these changes are in place as expected:
-----
-- Add new indexes on Last Name and Salary columns
-----
CREATE INDEX hr.new_employees_salary
ON hr.new_employees (salary)
TABLESPACE example;
CREATE INDEX hr.new_employees_ln
ON hr.new_employees (last_name)
TABLESPACE example;
-----
-- Add some more data using the table as its own source
-----
INSERT INTO hr.new_employees
SELECT hr.seq_empl_id.NEXTVAL, last_name, first_name, salary FROM hr.new_employees;
INSERT INTO hr.new_employees
SELECT hr.seq_empl_id.NEXTVAL, last_name, first_name, salary FROM hr.new_employees;
COMMIT;
-----
-- Show the results of the reconfiguration
-----
SELECT
COUNT(*) tot_emps
,MIN(employee_id)
,MAX(employee_id)
,AVG(salary)
,MIN(salary)
,MAX(salary)
FROM hr.new_employees;
TOT_EMPS MIN(EMPLOYEE_ID) MAX(EMPLOYEE_ID) AVG(SALARY) MIN(SALARY) MAX(SALARY)
---------- ---------------- ---------------- ----------- ----------- -----------
27392 100 27643 6517.75701 2100 30000
SELECT
owner
,index_name
FROM dba_indexes
WHERE table_owner = 'HR'
AND table_name = 'NEW_EMPLOYEES';
OWNER INDEX_NAME
------------------------------ ------------------------------
HR NEW_EMPLOYEES_PK_IDX
HR NEW_EMPLOYEES_SALARY
HR NEW_EMPLOYEES_LN
At this point, I could continue testing my application with the changes in place, but roll them back at any time to the original guaranteed restore point and start my testing all over again. I could also replay a pre-recorded application workload using Oracle 11g’s Real Application Testing suite via calls to the DBMS_WORKLOAD_REPLAY package or via Enterprise Manager. Switching a Snapshot Standby Database Back To Physical Standby ModeBut what if I need to perform disaster recovery via a failover at this point in my application testing? Or, in a more likely scenario, what if I need to deploy critical patch updates or perform a rolling upgrade by performing a role transition? Here’s the beauty of the snapshot standby technology: Transforming a database that’s currently operating in snapshot standby mode back to physical standby mode essentially uses the same principles in reverse: 1.) Snapshot standby mode is terminated. First, Oracle 11g discards any changes that have been made to the snapshot standby database. It does this by applying flashback logs to rewind the standby database back to the previously-created guaranteed restore point. 2.) The original physical standby database is reincarnated. The physical standby database is restored to its previous incarnation. All changes that were made during snapshot standby operations are reflected in an orphaned incarnation. 3.) Managed recovery is reinstated. Since the reincarnated physical standby database is now in MOUNT mode, Oracle 11g simply applies the change vectors from the previously received (but as yet unapplied!) redo logs to roll forward all changes that pertain to the original incarnation, and then the Managed Recovery Process (MRP0) is reactivated. To illustrate the reversion of a snapshot standby database back to physical standby mode, I’ll once again use DGMGRL to connect to my primary database and issue the CONVERT DATABASE command to transform database ORCL_STDBY1 into its original physical standby role: DGMGRL> CONVERT DATABASE orcl_stdby1 TO PHYSICAL STANDBY Converting database "orcl_stdby1" to a Physical Standby database, please wait... Operation requires shutdown of instance "orcl_stdby1" on database "orcl_stdby1" Shutting down instance "orcl_stdby1"... Database closed. Database dismounted. ORACLE instance shut down. Operation requires startup of instance "orcl_stdby1" on database "orcl_stdby1" Starting instance "orcl_stdby1"... ORACLE instance started. Database mounted. Continuing to convert database "orcl_stdby1" ... Operation requires shutdown of instance "orcl_stdby1" on database "orcl_stdby1" Shutting down instance "orcl_stdby1"... ORA-01109: database not open Database dismounted. ORACLE instance shut down. Operation requires startup of instance "orcl_stdby1" on database "orcl_stdby1" Starting instance "orcl_stdby1"... ORACLE instance started. Database mounted. Database "orcl_stdby1" converted successfully The results of converting the snapshot standby database back to its original physical standby state are also reflected in the database’s alert log as shown in Listing 5.3. The following query results prove that the physical standby database has been restored to its prior state:
Database Incarnations
(from V$DATABASE_INCARNATION)
Prior Flash
Reset Prior Reset Back
Incar- ResetLogs Logs Incar- Logs DB
nation ID RSL_CHG# Timestamp nation Timestamp Status Alwd?
-------- ------------ ---------- ----------- -------- ----------- ---------------- -----
1 629600782 1 2007-08-03 0 PARENT NO
01:06:22
2 682541003 522753 2009-03-26 1 2007-08-03 PARENT NO
18:43:23 01:06:22
3 692737822 4458754 2009-07-20 2 2009-03-26 ORPHAN NO
19:10:22 18:43:23
4 692911765 4957899 2009-07-22 2 2009-03-26 ORPHAN YES
19:29:25 18:43:23
5 693763762 4970489 2009-08-01 2 2009-03-26 CURRENT NO
16:09:22 18:43:23
6 694880645 5045967 2009-08-14 5 2009-08-01 ORPHAN NO
14:24:05 16:09:22
6 rows selected.
Database Status
(From V$DATABASE)
Prior Prior
Database ResetLogs ResetLogs ResetLogs ResetLogs
Name Current SCN Change # Timestamp Change # Timestamp
------------ ----------- --------- ----------- --------- -----------
ORCL 5046662 4970489 2009-08-01 522753 2009-03-26
16:09:22 18:43:23
SELECT
COUNT(*) tot_emps
,MIN(employee_id)
,MAX(employee_id)
,AVG(salary)
,MIN(salary)
,MAX(salary)
FROM hr.new_employees;
TOT_EMPS MIN(EMPLOYEE_ID) MAX(EMPLOYEE_ID) AVG(SALARY) MIN(SALARY) MAX(SALARY)
---------- ---------------- ---------------- ----------- ----------- -----------
6848 100 6947 6517.75701 2100 30000
SELECT
owner
,index_name
FROM dba_indexes
WHERE table_owner = 'HR'
AND table_name = 'NEW_EMPLOYEES';
OWNER INDEX_NAME
------------------------------ ------------------------------
HR NEW_EMPLOYEES_PK_IDX
Snapshot Standby Databases: Some CaveatsEven though snapshot standby databases offer some obvious advantages for application testing, it’s important to be aware of at least two possible situations that could delay a speedy reincarnation of the physical standby database environment:
Next StepsIn the next article in this series, I'll delve into how Oracle 11g handles the ultimate worst-case scenario -- the loss of the primary database -- by showing how an Oracle DBA can initiate a failover operation to a physical standby database, as well as resurrect a failed primary database back to physical standby with Data Guard's REINSTATE command. References and Additional ReadingWhile I’m hopeful that I’ve given you a thorough grounding in the technical aspects of the features I’ve discussed in this article, I’m also sure that there may be better documentation available since it’s been published. I therefore strongly suggest that you take a close look at the corresponding Oracle documentation on these features to obtain crystal-clear understanding before attempting to implement them in a production environment. Please note that I’ve drawn upon the following Oracle Database 11g documentation for the deeper technical details of this article: B28279-02 Oracle Database 11g New Features Guide B28294-03 Oracle Database 11g Data Guard Concepts and Administration B28295-03 Oracle Database 11g Data Guard Broker B28320-01 Oracle Database 11g Reference Guide B28419-02 Oracle Database 11g PL/SQL Packages and Types Reference |
|
|
The Network for Technology Professionals
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
