Oracle 10g offers significant enhancements that help insure the high availability of any Oracle database, as well as improvements in the database disaster recovery arena. This article - the final in a series - focuses on new functionalities provided by the Data Guard and LogMiner tool suites.
Synopsis. Oracle 10g offers significant
enhancements that help insure the high availability of any Oracle database, as
well as improvements in the database disaster recovery arena. This article -
the final in a series - focuses on new functionalities provided by the Data
Guard and LogMiner tool suites.
article in this series delved into the new Oracle 10g Logical Flashback
features that make it simpler for a DBA to recover database objects within
ranges of prior versions, transactions, and logical operations - even when a
table has been dropped in error. Oracle 10g also added some welcome enhancements
to two sets of utilities that can be invaluable on a DBA's toolbelt: LogMiner,
a set of utilities for mining redo and undo information from online and
archived redo logs; and Data Guard, a toolset that allows a DBA to
create a highly available clone of a production database for disaster recovery
The LogMiner tool suite lets a DBA scan through online redo
logs or archived redo logs to obtain actual DML SQL statements that have been
issued to the database server to create the redo change entries. LogMiner can
also return the SQL statements needed to undo the DML that has been issued.
Though I have had limited opportunity to utilize LogMiner in the past, almost
every time I have used it, it has come through with the information I needed to
help our development team or application users to recover from a potentially
However, LogMiner did have a few drawbacks: Even with the
Oracle Enterprise Manager user interface, it could take some wrangling to get
LogMiner to return the information needed for recovery. In addition, it did not
support retrieval of data from columns with Large Object (LOB) datatypes. The
good news is that Oracle 10g has enhanced the LogMiner tool suite to overcome
many of these issues:
Automated Determination of Needed Log Files. Prior to
Oracle 10g, one of the more tedious tasks before initiating a LogMiner
operation was to determine which archived redo logs were appropriate targets
for mining. Since one of our production databases had rather large redo log
file members (128MB), this was important to limit the amount of server
resources - especially processor resources - needed to scan the log files.
I usually handled this by querying the V$ARCHIVED_LOG view to
determine which archived redo log files might fulfill my LogMiner query based
on their start and end time periods, and then used the DBMS_LOGMNR.ADD_LOGFILE procedure to query
against just those log files. Oracle 10g has greatly simplified this by
scanning the control file of the target database to determine which redo logs
will fulfill the requested timeframe or SCN range.
4.1 shows an example of the new CONTINUOUS_MINE directive of procedure DBMS_LOGMNR.START
that directs Oracle to determine what log files are needed based on the ranges
specified. It also illustrates that the DBMS_LOGMNR.START procedure can be executed
multiple times within a LogMiner session to effectively limit the range of log
files required for the mining request
How Pretty Is My SQL? As nice as it is to be able to
see the actual SQL redo and undo statements, I have found myself frustrated by
how difficult it can be to parse them visually or for re-execution. In addition
to the existing directive, NO_SQL_DELIMITER that removes semicolons from the
final display, Oracle 10g also adds a new directive, PRINT_PRETTY_SQL that formats the SQL into a more legible
format. In addition, another new directive, NO_ROWID_IN_STMT, will omit the ROWID clause from
the reconstructed SQL when the DBA intends to reissue the generated SQL -
especially when it is going to be executed against a different database with
different ROWIDs. See Listing
4.2 for examples of these directives.
Expanded Support for Additional Datatypes. LogMiner
now supports retrieval of SQL Redo and Undo information for Large Objects (LOBs)
including multibyte CLOBs and NCLOBS. Data stored in Index-Organized Tables (IOTs)
is now also retrievable, so long as the IOT does not contain a LOB.
Storing the LogMiner Data Dictionary in Redo Logs.
LogMiner needs to have access to the database's data dictionary so that it can
make sense of the redo entries stored in the log files. Prior to Oracle 10g,
only two options were available. The database's data dictionary can be used as
long as the database instance is accessible. Another option is to store the
LogMiner data dictionary in a flat file created by the DBMS_LOGMNR_D.BUILD procedure. This offers the
advantage of being able to transport the data dictionary flat file and copies
of the database's log files to another, possibly more powerful or more
available server for LogMiner analysis. However, this option does take some
extra time and consumes a lot of resources while the flat file is created.
Oracle 10g now offers a melding of these two options: the
capability to store the LogMiner data dictionary within the active
database's redo log files. The advantage to this approach is that the data
dictionary listing is guaranteed to be consistent, and it is faster than
creating the flat file version of the data dictionary. The resulting log files
can then be specified as the source of the LogMiner data dictionary during
mining operations. Listing
4.3 shows an example of how to implement this option.
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 126.96.36.199.3,
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
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
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
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
RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE;
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,
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
The primary and standby databases both need to have a password
file set 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
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
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
See All Articles by Columnist Jim Czuprynski