Synopsis. Oracle 10g offers significant
enhancements that help insure the high availability of any Oracle database,
especially in the arena of disaster recovery. This article - the first in a
series - concentrates on several new features available for backup,
restoration, and recovery of Oracle databases, especially when using Oracle
Recovery Manager (RMAN).
If you have read my earlier
article about setting up disaster recovery for Oracle databases, you
already know that I sincerely enjoy experimenting with the myriad robust
features of Oracle Recovery Manager (RMAN). I am willing to bet that any
seasoned Oracle DBA sighs knowingly and thankfully when she thinks of a
potentially disastrous loss of data that has been averted by a well-planned
backup and recovery strategy that incorporates RMAN.
Oracle 10g expands significantly the RMAN backup,
restoration, and recovery features that I have grown to appreciate. Flash
Backup and Recovery appears to be the most exciting improvement, and I will
cover that in greater detail in the next article in this series, but for now,
this article will focus on numerous significant features that beg for
Expanded Image Copying Features. A standard RMAN backup
set contains one or more backup pieces, and each of these pieces
consists of the data blocks for a particular datafile stored in a special
compressed format. When a datafile needs to be restored, therefore, the entire
datafile essentially needs to be recreated from the blocks present in the
An image copy of a datafile, on the other hand, is
much faster to restore because the physical structure of the datafile already
exists. Oracle 10g now permits image copies to be created at the database, tablespace,
or datafile level through the new RMAN directive BACKUP AS COPY. For example, here is a command
script to create image copies for all datafiles in the entire database:
# Set the default channel configuration. Note the use of the
# %U directive to insure unique file names for the image copies
ALLOCATE CHANNEL dbkp1 DEVICE TYPE DISK FORMAT 'c:\oracle\rmanbkup\U%';
# Create an image copy of all datafiles in the database
BACKUP AS COPY DATABASE;
See Listing 1.1 for additional RMAN script examples
of this new feature.
Incrementally Updated Backups. As I explained in the
previous section, it is now much simpler to create image copy backups of the
database. Another new Oracle 10g feature, incrementally updated backups,
allows me to apply incremental database changes to the corresponding image copy
backup - also known as rolling forward the datafile image copy -- of any
datafile in the database. Since image copy backups are much faster to restore
in a media recovery situation, this new feature gives me the option to have
updated image copies ready for restoration without having to recreate the
image copies on a regular basis.
To utilize this feature, I will need to use the new BACKUP ... FOR RECOVER OF COPY
command to create the incremental level 1 backups to roll forward the changes
to the image copy of the datafiles, and use the new RMAN RECOVER COPY OF DATABASE command
to apply the incremental backup to the image copies of the datafiles. Note that
the TAG directive becomes extremely important to this implementation, as it is
used to identify to which image copies the changes are to be rolled forward.
Here is a script that illustrates a daily cycle of creation
and application of the incrementally updated backups. This would be appropriate
for a database that has sufficient disk space for storage of image copies, and
has a relatively high need for quick restoration of media:
# Roll forward any available changes to image copy files
# from the previous set of incremental Level 1 backups
COPY OF DATABASE
WITH TAG 'img_cpy_upd';
# Create incremental level 1 backup of all datafiles in the database
# for roll-forward application against image copies
INCREMENTAL LEVEL 1
FOR RECOVER OF COPY WITH TAG 'img_cpy_upd'
Though this appears a bit counter-intuitive at first, here
is an explanation of what happens during the initial run of this script:
The RECOVER command actually has no effect, because it cannot
find any incremental backups with a tag of img_cpy_upd.
However, the BACKUP command will create a new Incremental
Level 0 backup that is labeled with a tag of img_cpy_upd because no backups have been created
yet with this tag.
And during the second run of this script:
The RECOVER command still will have no effect, because it cannot
find any Level 1 incremental backups with a tag of img_cpy_upd.
The BACKUP command will create its first Incremental Level
1 backup that is labeled with a tag of img_cpy_upd.
But during the third and subsequent runs of
The RECOVER command finds the incremental level 1 image copy
backups from the previous night's run tagged as img_cpy_upd, and applies them to the existing datafile image
The BACKUP command will create the next Incremental Level
1 backup that is labeled with a tag of img_cpy_upd.
After the third run of this script, RMAN would then choose
the following files during a media recovery scenario: the image copy of
the database for tag img_cpy_upd from the
previous night, the most recent incremental level 1 backup, and all archived
redo logs since the image copy was taken. This strategy offers a
potentially quick and flexible recovery, since the datafile image copies will
be relatively quick to restore, and the incremental level 1 backup plus all
archived redo logs can be used to perform either a point-in-time or a
See Listing 1.2 for an example of how this new
feature could be implemented for a weekly backup strategy.
Improved Incremental Backup Performance With Change
Tracking. Another new Oracle 10g optional feature, change tracking,
promises to improve the performance of incremental backup creation
significantly. When an incremental backup was being taken prior to 10g, all the
blocks in each datafile being backed up needed to be scanned to determine if the
block had changed since the last incremental backup to determine if it needed
to be included in the new incremental backup.
With the new change tracking feature enabled, however, now
only the first Level 0 incremental backup needs to be completely scanned, and
the IDs of any changed blocks are written instead to a change tracking file.
All subsequent incremental backups will query the change tracking file to
determine if there are any changed blocks that need to be backed up. Oracle
automatically stores enough incremental backup metadata to insure that any of
the eight most recent incremental backups can be used as the "parent" of a new
Each Oracle database has only one change tracking file, and
if the database has been configured for Oracle Managed Files (OMF) it will be
automatically created based on the specification for DB_CREATE_FILE_DEST. However, if OMF is not
enabled for the database, the location of the change tracking file can be
specified manually. The initial size of the change tracking file is 10MB, and
it grows in 10MB increments, but Oracle notes that the 10MB initial extent
should be sufficient to store change tracking information for any database up
to one terabyte in size.
If the location needs to be moved, change tracking can be
disabled, and a new change tracking file can be created, but this causes the
database to lose all change tracking information. Moreover, unfortunately the change
tracking file cannot be moved without shutting down the database, moving it
with the appropriate ALTER DATABASE RENAME FILE <filename> command, and
then restarting the database.
Oracle does recommend that this feature be activated for any
database whose disaster recovery plan utilizes incremental backups of differing
levels. Oracle also notes that theirs is a small performance hit during normal
operations, but that hit should be discounted against the need to avoid scans
of datafiles during restoration and recovery operations.
See Listing 1.3 for more extensive RMAN script examples
of this new feature.
Improved Backup Resource Management. Oracle 9i added
new features to help DBAs to automatically manage backup file retention
with the RETENTION POLICY directives
of the CONFIGURE
command set. Oracle 10g has improved RMAN resource management even further with
directive of the BACKUP
command: It is now possible to tell RMAN exactly how much system resources
should be allocated to accomplish a backup task so that it completes within a
specified time frame.
For example, my client's primary production database is
scheduled to begin at 00:15 every day, and needs to complete before batch
processing commences at 03:00 every day. In my daily backup RMAN script, I can
specify that the backups must complete in 2.5 hours, and RMAN will begin
backing up the specified database files:
BACKUP DURATION 2:30 DATABASE;
If the backup cannot complete within this time frame, the
RMAN script being executed will return an error and terminate the backup - not
necessarily a desirable outcome! However, if I specify the PARTIAL
directive, RMAN will not return an error, but will back up as many files as it
can in that time frame, starting with the least recently used backed-up files
(a feature of using DURATION):
BACKUP DURATION PARTIAL 2:30 DATABASE FILESPERSET 1;
In this case, any files that could not be backed up will be
logged as errors from the RMAN script, but all other backup files will be
retained. Oracle does recommend setting FILESPERSET to 1 when using DURATION
PARTIAL to insure that any files for which backups succeeded are retained. I
can also tune backup performance so RMAN will try to complete the backups as
quickly as possible by specifying the MINIMIZE TIME directive:
BACKUP DURATION PARTIAL 2:00 MINIMIZE TIME DATABASE FILESPERSET 1;
If I specify the MINIMIZE LOAD directive, on the other hand,
RMAN will instead "stretch out" backup operations so that fewer resources are
utilized during that time:
BACKUP DURATION PARTIAL 3:30 MINIMIZE LOAD DATABASE FILESPERSET 1;
Server Parameter File (SPFILE) AutoBackups. Oracle 9i
added the ability to configure automatic control file backups to occur whenever
specific RMAN operations happened, or when the DBA performed a significant
modification of the database's logical or physical structure that affected the
control file (e.g. adding a new tablespace, or renaming a datafile).
Oracle 10g expands this feature to include the auto-backup
of the database's server parameter file - the binary copy of the
initialization parameter file -- as well. Though I have to admit that I am
still a fan of the initialization parameter file - old habits do die hard, dang
it! - it is obvious that Oracle views the SPFILE as the future basis for
controlling database parameter configuration.
Enhanced BEGIN BACKUP. Finally, here is a neat
enhancement for user-managed backups: The BEGIN BACKUP command that is used to
take tablespaces offline one at a time has been enhanced so that all of the
database's tablespaces can be taken offline at once:
-- Take all datafiles offline before starting user-managed backup
ALTER DATABASE BEGIN BACKUP;
-- Bring all datafiles back online after completing user-managed backup
ALTER DATABASE END BACKUP;
Though our shop uses RMAN for all production database
backups, this command certainly has value for smaller but no less
mission-critical databases like OEM or RMAN recovery catalog repositories.
Automatic Channel Failover. For those of you who
create RMAN backups directly on tape via a Media Management Layer (MML), Oracle
10g adds a new feature: If multiple channels have been allocated for the backup
step, but any one channel fails during that step, RMAN will automatically try
to use one of the other available channels to continue processing the backups.
Though I have had limited experience with using MML in conjunction with RMAN,
this feature appears to increase the flexibility and stability of directly
backing up to alternate media.
Restoration and Recovery Enhancements
RESTORE Failover. Oracle 10g has also significantly
improved the restoration process during initial restoration and recovery
If RMAN should detect a damaged or missing backup file, it will
automatically attempt to locate another usable copy of the image copy or
backup piece, either at the default location or at an alternate
If it cannot find a usable current copy, it then looks at prior
backup pieces or image copies and attempts to restore from those files.
If RMAN cannot locate any appropriate backup or image copy, only
then will it issue an error and terminate the RMAN session.
RESTORE ... PREVIEW. If you have ever wondered exactly
what backup files or image copies RMAN will use to perform restoration, Oracle
10g now offers the RESTORE ... PREVIEW command set to show exactly what backup
pieces or image copies RMAN plans to utilize.
For example, if I wanted to explore exactly what RMAN will
choose if I want to restore the database's SYSTEM tablespace, from within an
RMAN session, I can issue the RESTORE DATAFILE 1 PREVIEW; command and RMAN will
return the following output:
See Listing 1.4 for additional examples of this
Automatic Creation of Missing Datafiles. Consider
this scenario: Your junior DBA has just added a new tablespace to the
production database, but she neglected to take a full backup of the database
immediately after adding the tablespace. Then, as luck would have it, a media
failure occurs on the same disk where the new tablespace's datafile resides.
Here's the good news: With Oracle 9i, it's definitely
possible to recreate the datafile for the new tablespace - as long as all the
archived redo logs and online redo logs that were generated since the creation
of the new tablespace are available, that is. Once the datafile has been taken
offline, the ALTER DATABASE CREATE DATAFILE <datafile
name>; command is issued to recreate the datafile. Then
the RECOVER DATAFILE <datafile
name>; command is issued to recover the datafile, and the datafile's
tablespace can be brought back online.
Moreover, here is the better news: Oracle 10g is now
smart enough to handle this situation without DBA intervention. If the database
encounters a redo log entry for the creation of the datafile, it will
automatically add the new datafile to the database.
Improved Access To RMAN Metadata. Oracle 10g provides
some new dynamic views that offer a DBA the ability to see what's really
happening during and after a set of RMAN tasks have been completed, thus saving
the effort of having to constantly monitor a command window or log file to
determine their status.
will show the status of an ongoing RMAN job. For example, here is some
sample output from the example of the full database image copy backup run:
Results of currently active Recovery Manager sessions
connected to target database: ZDCDB (DBID=1863541959)
using target database controlfile instead of recovery catalog
allocated channel: dbkp1
channel dbkp1: sid=145 devtype=DISK
Starting backup at 20-NOV-04
channel dbkp1: starting datafile copy
input datafile fno=00001 name=C:\ORACLE\ORADATA\ZDCDB\SYSTEM01.DBF
SYSTEM_FNO-1_2OG5IGDQ tag=TAG20041120T114042 recid=2 stamp=54272
channel dbkp1: datafile copy complete, elapsed time: 00:01:19
channel dbkp1: starting datafile copy
input datafile fno=00013 name=C:\ORACLE\ORADATA\ZDCDB\SYSAUX01.D
... (Some detail removed for brevity) ...
channel dbkp1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 20-NOV-04
released channel: dbkp1
66 rows selected.
In addition, V$RMAN_STATUS lists the historical status
of all RMAN jobs. Here is the resulting output from my (not always successful!)
experiments with image backups:
Results of most recent Recovery Manager sessions
TimeStamp Session Action Status
-------------------- -------- -------- ------------------------
2004-11-20T11:40:33 COMMAND BACKUP COMPLETED
2004-11-20T11:40:33 SESSION RMAN COMPLETED
2004-11-20T11:39:50 SESSION RMAN COMPLETED WITH ERRORS
2004-11-20T11:39:50 COMMAND BACKUP FAILED
2004-11-20T11:33:49 COMMAND BACKUP FAILED
2004-11-20T11:33:49 SESSION RMAN COMPLETED WITH ERRORS
6 rows selected.
See Listing 1.5 for the queries used to create this
Improved Recovery Catalog Maintenance. Oracle 10g
offers a new catalog maintenance command, UNREGISTER DATABASE, to remove all information
about an Oracle database from an RMAN repository.
Dropping a Database Completely. If you really must
drop an entire database, the new DROP DATABASE command will remove all of the
specified database's physical files, including control files, datafiles,
online redo log members, and server parameter files (if any exist). Note that
the database must be mounted in exclusive, restricted mode for this command to
Oracle 10g's new Recovery Manager features greatly expand
the flexibility and reliability of any Oracle DBA's tool kit for disaster
recovery planning, backup strategies and failure recovery scenarios. And I've
just scratched the surface! As promised, the next article in this series will
focus on one of the most intriguing new availability features: Flash
Backup and Recovery.
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:
B10734-01 Oracle Database
Backup and Recovery Advanced User's Guide
B10735-01 Oracle Database Backup
and Recovery Basics
B10750-01 Oracle Database
New Features Guide
B10770-01 Oracle Database
Recovery Manager Reference
See All Articles by Columnist Jim Czuprynski