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 11gs new Recovery Manager features.
Ive been using Oracles 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 Id 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 Oracles maximum availability architecture (MAA).
When I attended Oracle OpenWorld in 2008, however, Oracle Database 11gs myriad new Data Guard capabilities opened my eyes to a whole new world of using the Data Guard architecture beyond disaster recovery. Ive summarized many of these features in prior article series, but Im going to dive into the deep end of the Data Guard pool during these next articles. Heres a quick summary of the areas Ill 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 Ill 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 wouldve taken in earlier releases, and its especially powerful in a Real Application Clusters (RAC) database environment, as Ill 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 theyre 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 its been enhanced significantly in Oracle 11g to permit much finer-grained control over the conditions under which a fast-start failover would be initiated. Ill 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 its 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 Ill need an Oracle 11g Data Guard environment to demonstrate the features Ive described above, Im 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, Ive chosen Oracle Enterprise Linux (OEL) 4.5.1 (Linux kernel version 2.6.9-18.104.22.168.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 (22.214.171.124) on both nodes. Finally, I constructed the standard 11gR1 seed database, including the standard sample schemas, on the primary node. This databases ORACLE_SID is orcl. Im 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, Ill need to make some adjustments to the primary database itself. Ive described the steps below in no particular order; as long as theyre 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 its possible to lose changes to objects whose changes arent being logged because their storage attribute is set to NOLOGGING. To insure that all changes are logged, Ill 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 its 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. Ive shown the final settings for these initialization parameters in Listing 1.3:
DB_UNIQUE_NAME. Ill 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 itll 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. Ive set it up to reflect my current Data Guard databases, orcl and stdby.
STANDBY_FILE_MANAGEMENT. Ive 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. Ill set up two archiving destinations:
Ill also use two other directives for these two archived redo log transmission parameters:
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 servers network configuration is the addition of the standby databases instance to the local naming configuration file (TNSNAMES.ORA). The standby database servers LISTENER.ORA configuration file also requires a listener with a static listening endpoint for the standby databases 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. Ill need to create the appropriate destination directories for the databases control files, data files, online redo logs, and standby redo logs. Ill also create an appropriate directory for the databases audit trails.
Set Up Password File. Since the primary database will need to communicate with the standby database using remote authentication, Ill 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 databases site to the standby databases primary site.)
Create Standby Initialization Parameter File. Finally, Ill 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.
Ive 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, Ill start the standby sites listener, and then Ill 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
Its 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 Ill use to clone my primary sites database to the standby site. Note that Ive added a few additional parameters that arent exact counterparts of the primary database and tweaked a few others appropriately:
At last ... let the cloning commence! First, Ill 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 126.96.36.199.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, Ill 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. Heres what this RMAN command block does:
Ive 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 databases alert log entries generated during the cloning operation.
Post-Cloning: Cleanup and Verification
Now that the cloning is completed, Ill 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, Ill perform a redo log switch on the primary database:
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
And heres the resulting proof from the standby databases 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: Assigned to RFS process 8492 RFS: 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: Assigned to RFS process 8506 RFS: Identified database type as 'physical standby' Primary database is in MAXIMUM PERFORMANCE mode Primary database is in MAXIMUM PERFORMANCE mode RFS: 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: Assigned to RFS process 8512 RFS: Identified database type as 'physical standby' kcrrvslf: active RFS archival for log 4 thread 1 sequence 111 RFS: Successfully opened standby log 5: '/u01/app/oracle/oradata/stdby/srl02.log' Sat Apr 18 06:42:53 2009
In the next article in this series, Ill 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 Im hopeful that Ive given you a thorough grounding in the technical aspects of the features Ive discussed in this article, Im also sure that there may be better documentation available since its 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 Ive 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