Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Feb 24, 2005

Oracle 10g Availability Enhancements, Part 4: LogMiner and Data Guard - Page 2

By Jim Czuprynski

Data Guard Enhancements

Though it is apparently not in wide use in many Oracle shops, Oracle Data Guard is a valuable set of high-availability tools for providing rapid switchover in a disaster recovery situation to a standby database - a separate database instance that is usually in a different physical location from the primary database. Depending on your client's needs, Data Guard may be an important part of your disaster recovery plan. (See my prior article, Becoming the Master Of Disaster, for some practical suggestions and considerations for constructing a disaster recovery plan.)

Our shop has been using Data Guard since Oracle, and I have performed a failover - the process of activating the standby database in case of a disaster - at least a dozen times over the past two years during regular evaluations of the standby database's viability. I have also noticed that Data Guard's stability improved in Oracle 9iR2. That release also added capabilities to perform a switchover (when the primary and standby databases reverse their database roles) and a switchback (when the original primary and standby database revert to their original roles).

Oracle 10g has improved upon this already-powerful suite of database high-availability capabilities. Based on my Data Guard experience and exposure, here is a discussion of several new features that offer significant improvements:

Reversing Errors Propagated to Standby Database. Oracle 10g has significantly improved the recovery process during an ALTER DATABASE OPEN RESETLOGS; operation (see the prior article in this series). Another advantage of this enhancement is that it is now possible to recover from an error that has occurred on the primary database that has already been propagated to the standby database.

In this situation, Oracle 10g permits the DBA to flash the primary database back to a state prior to the error with the FLASHBACK DATABASE command, perform a recovery on the primary database through the ALTER DATABASE OPEN RESETLOGS; command, and then flash back the standby database to the appropriate state via the FLASHBACK STANDBY DATABASE command. This new feature improves upon the traditional method of preventing application of erroneous transactions to the standby database: setting a delay factor in the transmission of the archived redo logs to the standby site, and then halting the application of the redo entries at the standby.

Standby Database Recovery Improvements. Prior to Oracle 10g, if the primary database underwent a point-in-time incomplete recovery via the ALTER DATABASE OPEN RESETLOGS; command, the standby database had to be recreated from primary database backups. However, Oracle 10g now permits restoration of the standby database without having to create a new backup of the primary database and restore it to the standby site. As long as the FLASHBACK DATABASE option has been enabled on both the primary and standby databases, the DBA only needs to flash the original standby database back to the point in time prior to the issuance of the ALTER DATABASE OPEN RESETLOGS; command on the primary database, and then re-establish the transmittal and application of logged transactions from the primary database to the standby database.

New Default Behavior for Physical Standby Database Administration. The traditional standby database is also called a physical standby database; it is essentially a "hot clone" that replicates the entire contents of its corresponding primary database. The physical standby database can also be opened in read-only mode for reporting or validation purposes, then restored back to its standby role, with a few simple commands.

Oracle 10g has made administration simpler by changing the default behaviors for the commands to start, mount, and open a physical standby database:

  • If the standby database has already been started but not mounted via the STARTUP NOMOUNT command, the ALTER DATABASE MOUNT; command will automatically bring the standby database into standby mount mode. Prior to this release, the ALTER DATABASE MOUNT STANDBY DATABASE;command had to be issued explicitly.
  • When the physical standby database has already been mounted, issuing the ALTER DATABASE OPEN; command automatically brings it into read-only mode. Prior to this release, the ALTER DATABASE OPEN READ ONLY;command had to be issued explicitly.
  • Finally, if just the STARTUP command is issued, Oracle 10g now assumes that the database is going to be opened in read-only mode. This typically required from one to three commands in past releases.

Real-Time Application of Standby Redo Logs. The transmission and application of archived redo logs is at the heart of a Data Guard configuration. Oracle 9iR2 added the capability to write redo entries to the primary database's online redo logs and simultaneously to a set of standby redo logs that are set up on the standby database server. Standby redo logs therefore help to limit data loss because normally a standby database waits for the transmission of a complete archived redo log from the primary database before the redo entries stored within are readied for application against the standby database.

Oracle 10g significantly increases the availability of the standby database with the new Real-Time Apply feature. As redo entries are written to the standby redo logs, Real-Time Apply automatically applies the changes immediately to the standby database. This insures that the time required for activation, switchover, and switchback of the standby database is significantly reduced. This feature is activated by specifying the USING CURRENT LOGFILE directive when the Log Apply service is started on the standby database:

-- Start Real-Time Apply

Simpler Archive Log Destination Management. Probably one of the more tedious tasks in setting up the Data Guard environment is the specification of the optional parameters for the archive log destination initialization parameters (LOG_ARCHIVE_DEST_n).

Oracle 10g has added the new VALID_FOR directive for these parameters that greatly simplifies their configuration and makes it possible to have a single initialization parameter file for both primary and standby databases. VALID_FOR has two components: the redo log type (ALL_LOGFILES, ONLINE_LOGFILES, or STANDBY_LOGFILES) and the database role (ALL_ROLES, PRIMARY_ROLES, or STANDBY_ROLES). See Listing 4.4 for an example of how to use this new directive.

In addition, when server parameter files (SPFILEs) are used for storage of the primary and standby databases' initialization parameters, switchover and switchback between the primary and standby database roles is greatly simplified because it is no longer necessary to maintain a separate SPFILE for each Data Guard role on the primary and standby databases. Note that the use of SPFILEs is required, by the way, if you intend to utilize the Data Guard Broker and Oracle Enterprise Manager (OEM) to manage a Data Guard configuration.

Tightened Security for Redo Log Transmissions. Oracle 10g has also beefed up security for archive log transmissions between the primary and standby site. A few simple requirements need to be fulfilled to activate this feature:

  • The primary and standby databases must have the same SYS password.
  • The primary and standby databases both need to have a password fileset up using the ORAPWD utility that stores an encrypted version of the SYS password.
  • The primary and standby databases also need to have the REMOTE_LOGIN_PASSWORDFILE initialization parameter set appropriately (either EXCLUSIVE or SHARED).

Once these configuration tasks are completed, Data Guard will only transmit redo data from the primary to the standby site once the SYS user's credentials are successfully verified. In addition, Oracle Advanced Security can still be implemented to insure that redo log information is fully encrypted during its transmission across a network to the standby site.

Improved Support for Real Application Clusters (RAC). The growing popularity of Real Application Clusters (RAC) for extremely high-availability database configurations is finally acknowledged with Oracle 10g's enhancements to the Data Guard Broker. A DBA can now easily manage a Data Guard configuration that includes a RAC instance as either the primary or the standby database.

A Warning on Costs. Finally, this caveat from personal experience: Implementing a full-blown Data Guard environment is not inexpensive. For many shops, the cost of the redundant hardware needed to sufficiently duplicate or support the standby database instance alone is cost-prohibitive. Also, be aware that if you decide to apply online redo logs in real time against a Data Guard standby database instance, Oracle does require licensing that standby database instance as if it were a full-fledged, live database. These costs need to be factored into the cost-benefit analysis for your company's disaster recovery plans.


Oracle 10g's enhancements to LogMiner provide an Oracle DBA with even more flexibility when mining and presenting redo log information, and the new DataGuard features show that Oracle is concentrating on making good its promise of databases that are truly "always available."

References and Additional Reading

While there is no substitute for direct experience, reading the manual is not a bad idea, either. I have drawn upon the following Oracle 10g documentation for the deeper technical details of this article:

B10750-01 Oracle Database New Features Guide

B10823-01 Oracle Data Guard Concepts and Administration

B10825-01 Oracle Database Utilities

» See All Articles by Columnist Jim Czuprynski

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM