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 Nov 24, 2004

Oracle 10g Availability Enhancements, Part 1: Backup and Recovery Improvements - Page 2

By Jim Czuprynski

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


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


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:


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:


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

-- Bring all datafiles back online after completing user-managed 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 efforts:

  • 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 multiplexed location.
  • 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 command set.

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.

Other Enhancements

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.

V$RMAN_OUTPUT 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
output filename=C:\ORACLE\RMANBKUP\DATA_D-ZDCDB_I-1863541959_TS-
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  COMMAND  BACKUP   FAILED
2004-11-20T11:33:49  COMMAND  BACKUP   FAILED

6 rows selected.

See Listing 1.5 for the queries used to create this output.

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 succeed.


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

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