Oracle 11g Data Guard: Building a Physical Standby Database

April 29, 2009

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.

SQL Apply Enhancements. Logical standby databases are obviously central to these new role transition features, but they use SQL Apply technology to apply change vectors to data. It therefore makes sense that Oracle Database 11g provides significant improvements to this crucial part of Data Guard architecture. SQL Apply now supports parallel DDL execution, Fine-Grained Auditing (FGA), Virtual Private Database (VPD), and Transparent Data Encryption (TDE), as well as simpler real-time SQL Apply reconfiguration and tuning.

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 to set 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:

• 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:

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:

Create Required Directories. I’ll need to create the appropriate destination directories for the database’s control files, data files, online redo logs, and standby redo logs. I’ll also create an appropriate directory for the database’s audit trails.

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:

$> export ORACLE_SID=stdby
$> sqlplus / as sysdba
SQL> startup nomount pfile='/home/oracle/init_stdby.ora';

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:

oracle@training> rman target / auxiliary sys/oracle@stdby

Recovery Manager: Release 11.1.0.6.0 - Production on Tue Apr 14 19:29:25 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: ORCL (DBID=1210321736)
connected to auxiliary database: STDBY (not mounted)

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 lists the 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:

Completed: alter database clear logfile group 6
RFS connections are allowed
Sat Apr 18 06:29:58 2009
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[1]: Assigned to RFS process 8492
RFS[1]: Identified database type as 'physical standby'
RFS LogMiner: Client disabled from further notification
Sat Apr 18 06:35:39 2009
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[2]: Assigned to RFS process 8506
RFS[2]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: Successfully opened standby log 4: '/u01/app/oracle/oradata/stdby/srl01.log'
Sat Apr 18 06:36:28 2009
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[3]: Assigned to RFS process 8512
RFS[3]: Identified database type as 'physical standby'
kcrrvslf: active RFS archival for log 4 thread 1 sequence 111
RFS[3]: Successfully opened standby log 5: '/u01/app/oracle/oradata/stdby/srl02.log'
Sat Apr 18 06:42:53 2009

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:

568034.1 11g: Active Database Duplication

» See All Articles by Columnist Jim Czuprynski