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:
-
Use a recovery
catalog to catalog backups made on the standby database
-
Create incrementally
updateable image copy backups from a standby database
-
Implement block
change tracking on a standby database
This
article will discuss:
-
How to leverage Oracle 11g Data Guard Real Time
Query features
-
How Oracle 11g
Data Guard snapshot standby
database features simultaneously provide disaster recovery and quality
assurance testing environments
Active Data Guard: Real-Time Queries During Managed Recovery
Prior
to Oracle Database 11g, the DBA could
opt for one of two mutually-exclusive choices for a physical standby databases
operating mode:
-
The database could operate in Managed Recovery mode, in which redo data
would be applied as soon as it was received from the corresponding primary
database. The physical standby would thus be available immediately for transition
to the primary role during either a switchover or a failover operation.
-
Alternatively, the database could be opened in READ ONLY
mode so that applications could execute queries against it. This offered the
ability to leverage the physical standby database platform for the intensive
read operations that data warehousing, DSS, and OLAP applications typically
perform, thus offloading that workload from the production database.
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:
-
Disable
application of redo on the physical standby database using DGMGRL
-
Shut down
the physical standby database
-
Restart
the physical standby database instance in MOUNT mode
-
Re-enable
application of redo on the physical standby database using DGMGRL
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 Testing
While
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:
-
Only a physical
standby database can become a snapshot standby database. A logical standby
database cannot be transformed into a snapshot standby for rather obvious
reasons: Logical standby databases aren’t required to be an exact duplicate of
the primary database, and they use a totally different method – SQL Apply – to
apply redo information.
-
Flashback Logging
must be enabled. Flashback Logging must have been enabled on both
the primary database as well as the
corresponding physical standby
database that’s the target of the conversion. This means that a suitably-sized
Flash Recovery Area is present, and it must be large enough to retain all
recovery files necessary to return the original physical standby database to
its prior state before being transformed to a snapshot standby database.
Creating a Snapshot Standby Database: An Example
Oracle
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 Mode
But
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 Caveats
Even
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:
-
Reincarnation may
take a long time. Depending on how much READ WRITE activity has taken
place on the snapshot standby incarnation of the database, it could take an
extremely long time to rewind
those changes via Flashback Database to the original guaranteed restore point …
and an equally long time to roll forward
all the pending change data from the archived redo logs that have been received
since the standby database was incarnated.
-
Delayed
discovery of corrupted archived redo logs. Remember that even though
archived redo logs are still being received
at the snapshot standby database’s site, those logs will not be applied until after the snapshot standby
is reincarnated as a physical standby database. If the primary database is
unavailable when reincarnation is attempted, and one or more of the as-yet
unapplied archived redo logs are corrupted or missing on the standby site,
reincarnation isn’t possible until those logs have been recovered from some
other source – perhaps even from tape backups.
Next Steps
In 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 Reading
While
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
»
See All Articles by Columnist Jim Czuprynski
» 
Comment and Contribute
