Featured Database Articles

Oracle 11g Data Guard: Building a Physical Standby Database

Synopsis.
Oracle Data Guard is a crucial part of the insurance policy that guarantees
against unrecoverable disasters. Each new release of Oracle has augmented these
disaster recovery features, and Oracle Database 11g expands them dramatically
to include the capability to keep a standby database open for read-only queries
while still accepting change vectors from the primary database. This article 
the first in an ongoing series  explains how to set up a standby database
environment using Oracle 11gs new Recovery Manager features.

Ive
been using Oracles Data Guard features even before it was officially known as
Data Guard. I helped pioneer the use of a standby database as a potential
reporting platform in early Oracle Database 8i,
with limited success. When Oracle 9i
Release 2 rolled out, I also experimented with switching back and forth between
primary and standby databases - again with limited success, mainly because Id
decided not to implement the Data Guard Broker instrumentation. So when Oracle
10g rolled out, I was encouraged
by the many new manageability features that it provided and how well it
integrated with Real Application Cluster
(RAC) databases as part of Oracles maximum
availability architecture (MAA).

When
I attended Oracle OpenWorld in 2008, however, Oracle Database 11gs myriad new Data Guard capabilities opened
my eyes to a whole new world of using the Data Guard architecture beyond
disaster recovery. Ive summarized many of these features in prior
article series, but Im going to dive into the deep end of the Data Guard
pool during these next articles. Heres a quick summary of the areas Ill be
exploring:

Real-Time Query. In Oracle Database 8i it was possible to bring a standby
database into READ
ONLY mode so that it could be used for reporting purposes,
but it was necessary to switch it back to standby mode for reapplication of
pending change vectors from the archived redo logs transported from the primary
database. Oracle Database 11g now
lets me run queries in real time against any physical standby database without
any disturbance to receipt and application of redo.

Snapshot Standby Databases. Oracle Database
11g offers another intriguing
prospect: the ability to open a physical
standby database for testing or QA purposes while simultaneously collecting
production changes for immediate reapplication in case disaster recovery is
required. This snapshot standby
database still accepts redo information from its primary, but unlike the first
two standby types, it does not apply the
redo to the database immediately; instead, the redo is only applied
when the snapshot standby database is reconverted back into a physical standby.
This offers significant leverage because in theory, a QA environment that
requires specifically dedicated, identical hardware is no longer required.

Improved Handling of Role Transitions. The
addition of standby snapshot databases brings the total of different Data Guard
standby database types to three (physical, logical, and snapshot), so Oracle
Database 11g also makes it much
easier to transition between these different roles via either Data Guard Broker (DGB) command line
execution or Enterprise Manager Grid Control.
As Ill demonstrate in later articles, role transitions are simpler to execute and complete more quickly than in earlier
releases.

Improvements to Rolling Database Upgrades.
Oracle Database 11g supports
rolling database upgrades to be performed against a physical standby database by first transforming it into a logical
standby database with a few simple commands before the upgrade begins. Once the
upgrade is done, the logical standby database is reverted to its original
physical standby state. Oracle 11g
leverages this capability as well as the improved speed and simplicity of role
transitions to perform system and database patching in a fraction of the time
it wouldve taken in earlier releases, and its especially powerful in a Real
Application Clusters (RAC) database environment, as Ill demonstrate in a
future article.

Enhanced Redo Logs Transport. Physical
standby databases have always used archived
redo logs for application of change vectors to data. Oracle Database
11g augments redo transport with
some long-overdue features, including compression
and SSL authentication of redo
logs while theyre being transmitted between the primary and standby sites.

Heterogeneous DataGuard., Oracle Database
11g allows the primary and
standby databases to use different operating
systems (for example, Windows 2003 Server and Oracle Enterprise
Linux) as long as both operating systems support the same endianness.

Fast Start Failover Improvements. Oracle
introduced this feature set in Release 10gR2,
but its been enhanced significantly in Oracle 11g to permit much finer-grained control over the conditions
under which a fast-start failover would be initiated. Ill demonstrate how an
Oracle DBA can set up, control, and even force a fast-start failover to occur
in a later article in this series.

Live Cloning of Standby Databases. Finally,
Oracle 11g has made it extremely
simple toset up a standby database
environment because Recovery Manager
(RMAN) now supports the ability to clone the existing primary database directly
to the intended standby database site over
the network via the DUPLICATE DATABASE command set while the target database is active. This
means its no longer necessary to first generate, then transmit, and finally
restore and recover RMAN backups of the primary database on the standby site
via tedious (and possibly error-prone!) manual methods; instead, RMAN automatically
generates a conversion script in memory on the primary site and uses that
script to manage the cloning operation on the standby site with virtually no
DBA intervention required.

Standby Database Live Cloning: A Demonstration

Since
Ill need an Oracle 11g Data
Guard environment to demonstrate the features Ive described above, Im going
to focus on the new live cloning feature for the remainder of this article.
My hardware is a dual-core AMD Athlon 64-bit CPU (Winchester 4200) with 4GB of
memory using Windows XP as my host server to run VMWare Server 1.0.8 to access
a virtualized database server environment. Each virtual machine uses one
virtual CPU and 1200MB of memory, and for this iteration, Ive chosen Oracle
Enterprise Linux (OEL) 4.5.1 (Linux kernel version 2.6.9-55.0.0.0.2.ELsmp) for my operating system on both
guest virtual machines.

Once each
VMWare virtual machine was configured, I established network connectivity
between my primary site (training) and the standby site (11gStdby)
via appropriate entries in /etc/hosts on each VM. I then installed the database
software for Oracle Database 11g Release
1 (11.0.1.6) on both nodes. Finally, I constructed the standard 11gR1 seed
database, including the standard sample schemas, on the primary node. This
databases ORACLE_SID
is orcl.
Im now ready to perform the live cloning operation

Preparing to Clone: Adjusting the Primary Database

Before
I can clone my primary database to its corresponding standby environment, Ill
need to make some adjustments to the primary database itself. Ive described
the steps below in no particular order; as long as theyre all completed before
I issue the DUPLICATE DATABASE statement, I should have no surprises
during the cloning operation.

Force Logging of All Transactions. A major
reason that most organizations implement a Data Guard configuration is to
insure that not one transaction will be lost. By default, however, an Oracle
database is in NOFORCE
LOGGING mode, and this implies that its possible to lose
changes to objects whose changes arent being logged because their storage
attribute is set to NOLOGGING. To insure that all changes are logged,
Ill execute the ALTER DATABASE FORCE LOGGING; command just before
I bring the database into ARCHIVELOG mode via the ALTER DATABASE ARCHIVELOG;
command. These commands are shown in Listing 1.1.

Set Up Standby Redo Log Groups. Oracle has
recommended the configuration of standby
redo log (SRL) groups
since they became available in Oracle 9i
Release 2. SRLs are required for the Real
Time Apply feature or if the DBA wants to implement the ability to cascade redo log destinations; otherwise, they
are still optional for configuration of a standby database. Another advantage
of Oracle 11g is that if SRLs
have already been configured on the primary database, then the DUPLICATE DATABASE
command will automatically create them on
the standby database during execution of its memory script. Listing
1.2 shows the commands I issued to create SRLs on the primary
site; notice that I also multiplexed
the SRL files to protect against the loss of a complete SRL group, just as is
recommended for online redo log groups.

File Name Conversions. Usually a standby
database is created on a host other than that of the primary database;
otherwise, in a disaster, both standby and primary databases would be
compromised (if not destroyed!). A recommended best practice is to name the
directories and file names of the corresponding standby database identically.
In cases when directory names might need to change because of different mount
points, however, then its necessary to map out the scheme for this conversion
with the DB_FILE_NAME_CONVERT
and LOG_FILE_NAME_CONVERT
initialization parameters.

Modify Primary Site Initialization Parameters. Setting
the following initialization parameters for the primary database instance
insures that the DUPLICATE DATABASE command configures the standby
database instance as well. Ive shown the final settings for these
initialization parameters in Listing 1.3:

DB_UNIQUE_NAME.
Ill set this parameter to define a unique name for the primary database instance. This assignment
makes it much simpler to identify the original primary and standby instances regardless of role exchange. Since this is
a static parameter, I set it with SCOPE=SPFILE in Listing 1.1 so that itll take effect
during the bounce of the primary database instance.

LOG_ARCHIVE_CONFIG.
This parameter controls whether a primary or standby database should accept and/or send archived redo logs that have been transmitted from a
remote source. It allows me to encompass all Data Guard primary and standby
databases to be managed because it lists the DB_UNIQUE_NAME values for all
databases within the configuration. Ive set it up to reflect my current Data
Guard databases, orcl and stdby.

STANDBY_FILE_MANAGEMENT.
Ive set this parameter to a value of AUTO so that Oracle will automatically manage the creation or
deletion of corresponding database files on the standby database whenever a new file is created or an
existing file is deleted on the primary
database  for example, when a new online redo log group is added, or a
tablespace is dropped.

LOG_ARCHIVE_DEST_n.
This parameter is crucial to exchanging archived redo logs from the primary
database to its counterpart physical standby database. Ill set up two
archiving destinations:

Destination LOG_ARCHIVE_DEST_1 designates the
physical location for the primary
databases archived redo logs. Note that Im using the Flash Recovery Area for
the database as a target.

Destination LOG_ARCHIVE_DEST_2 designates the
network service address that
corresponds to the standby database instance (STDBY), and this insures that
archived redo logs are transmitted automatically to the standby site for
eventual application against the standby database.

Ill
also use two other directives for these two archived redo log transmission
parameters:

Directive VALID_FOR dramatically simplifies
what types of redo log
transmission are acceptable when the database is acting in a specific role. This is especially critical to the
proper handling archived redo logs when the primary and standby databases have
exchanged roles. Table 1.1 lists
the permitted values for this directive and what they control:

Table 1-1. VALID_FOR
Directive Values

Setting

Meaning

ALL_LOGFILES

(Default)
Destination is valid for either online or
standby redo log files

ONLINE_LOGFILE

Destination
is valid for archiving onlyonline redo log files

STANDBY_LOGFILE

Destination
is valid for archiving onlystandby redo log files

ALL_ROLES

(Default)
Destination is valid when database is operating in either primary orstandby
role

PRIMARY_ROLE

Destination
is valid when database is operating only
in primary role

STANDBY_ROLE

Destination
is valid when database is operating only
in standby role

Ill also identify how archived redo logs are to be
transmitted from the primary to the standby database by specifying an
appropriate redo transport mode. Table 1.2 lists the permitted values for
this directive:

Table 1-2. Redo
Transport Modes

Setting

Meaning

ASYNC

(Default)
The redo for a transaction may not have
been received by all enabled destination(s) before the transaction
is allowed to COMMIT

SYNC

The
redo for a transaction must
have been received by all enabled destination(s) before the transaction
is allowed to COMMIT

AFFIRM

The
destination for redo transport will acknowledge the receipt of redo data only
after its been written to the standby redo log; implied by SYNC setting

NOAFFIRM

The
destination for redo transport will acknowledge the receipt of redo data before
its been written to the standby redo log; implied by ASYNC setting

Network Configuration Changes. Finally, I
need to insure that the primary and standby databases can communicate over the
network. The only required change to the primary database servers network configuration
is the addition of the standby databases instance to the local naming
configuration file (TNSNAMES.ORA). The standby database servers LISTENER.ORA
configuration file also requires a listener with a static listening endpoint
for the standby databases instance. These changes are shown in Listing
1.4.

Preparing to Clone: Preparing the Standby Site

Now
that the primary site is ready for cloning, I need to make some additional
adjustments to its corresponding standby site:

Set Up Password File. Since the primary
database will need to communicate with the standby database using remote
authentication, Ill create a new password file via the orapwd
utility, making sure that the password for SYS matches that of the primary
database. (Note that I could have also copied it directly from the primary
databases site to the standby databases primary site.)

Create Standby Initialization Parameter File. Finally,
Ill need to create an initialization parameter file (PFILE) just to allow me
to start the standby database instance, and it only requires one parameter: DB_NAME.
When the DUPLICATE
DATABASE command script completes, it will have created a new
server parameter file (SPFILE) containing only the appropriate initialization
parameter settings.

Ive
illustrated these commands and the contents of the temporary standby database
initialization parameter file in Listing 1.5.
To give DUPLICATE DATABASE a target for the cloning
operation, Ill start the standby sites listener, and then Ill start the
standby database instance in NOMOUNT mode using the PFILE I created above:

Cloning the Standby Database Via DUPLICATE DATABASE

Its
finally time to issue the DUPLICATE DATABASE command from
within an RMAN session on the primary database server. As I mentioned earlier,
the best part of using DUPLICATE DATABASE in Oracle 11g is that I can
clone the primary database to the standby site directly across the network. As
part of the setup of the standby database, I can also specify values for all
required initialization parameters, and DUPLICATE DATABASE will create a
new SPFILE on the standby server that captures those values.

Listing
1.6 shows the DUPLICATE DATABASE statement Ill
use to clone my primary sites database to the standby site. Note that Ive
added a few additional parameters that arent exact counterparts of the primary
database and tweaked a few others appropriately:

DB_UNIQUE_NAME. Ive set this value to stdby
for the standby database.

CONTROL_FILES. Ive specified just one control
file for the standby database; I will multiplex it after cloning is completed.

FAL_CLIENT and FAL_SERVER. These parameters
establish which database services will act as the fetch archive log (FAL) client and server, respectively. For
example, whenever a network outage occurs between the primary and standby
servers, or if the standby database has been shut down for a significant length
of time, its possible that one or more archived redo logs havent been
transmitted to the standby server. This situation is called an archive log gap, and these two FAL service
names establish which server maintains the master list of all archived redo
logs (FAL_SERVER)
and which server(s) requests resolution of the potential archive log gap (FAL_CLIENT).
In our Data Guard setup, Ive configured the standby server to be the FAL client,
and the primary server to be the FAL server.

LOG_FILE_NAME_CONVERT. Ive translated the
primary databases destinations for its archive redo logs and standby redo logs
with this parameter to insure that RMAN will automatically create appropriate
counterparts on the standby database during the cloning operation.

LOG_ARCHIVE_DEST_n. Just as with the primary
database, Ive set up two archive logging destinations: a primary (LOG_ARCHIVE_DEST_1) for archive
redo logging, and a secondary (LOG_ARCHIVE_DEST_2)
that will handle the transmission of the standby
sites archived redo logs back to the original
primary database when these two databases exchange roles in the
future. (Ill be demonstrating this in later articles.)

At last
... let the cloning commence! First, Ill initiate an RMAN session on the primary
database server, connecting to the primary database as the target and the
standby database instance as the auxiliary:

For
faster processing, Ill establish two auxiliary
channels and two normal channels
via the ALLOCATE CHANNEL command and initiate the cloning
with DUPLICATE
DATABASE in the same RUN block. Heres what this RMAN command
block does:

It creates a new
SPFILE for the standby database using the current primary databases
server parameter files as a template, but makes the appropriate changes as
specified in the SET commands of the DUPLICATE DATABASE run block.

It then shuts
down the standby database and opens it in NOMOUNT mode with the new SPFILE.

Next, it creates a copy of the primary databases
control file, modifying it so that all file names match those of the standby
database, copies the new control file to the standby database, and MOUNTs
the standby database using the new control file.

It then creates image copy backups of each primary
databases database file directly on the standby database.

Finally, it uses the current archived redo log on
the primary database to perform any necessary recovery on the standby database,
and brings the standby database into managed
recovery mode.

Ive
posted the results of the cloning operation in Listing 1.7,
which shows the output from the RMAN command, and in Listing 1.8,
which liststhe standby databases
alert log entries generated during the cloning operation.

Post-Cloning: Cleanup and Verification

Now
that the cloning is completed, Ill need to insure that the standby database is
actually ready to receive archived redo logs from the primary database. To
verify that the primary and standby databases are indeed communicating, Ill
perform a redo log switch on the primary database:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

And
heres the resulting proof from the standby databases alert log that the
online redo log was successfully transmitted to and applied at the standby
database:

Next Steps

In
the next article in this series, Ill explore how to use the Data Guard Broker (DGB) command set to
control both the primary and standby database, as well as demonstrate how to
perform a simple role transition  a switchover - between the primary and
standby databases.

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

Also,
the following MetaLink documentation helps clarify this feature set: