How to Backup and Restore an Oracle Database without RMAN

Norbert Debes

The following article is an excerpt from Secrets of the Oracle Database, published by APress.

The package DBMS_BACKUP_RESTORE is undocumented in the Oracle9i Supplied PL/SQL Packages
and Types Reference as well as in Oracle Database PL/SQL Packages and Types Reference of Oracle10g and subsequent releases. Restoring database files with Recovery Manager (RMAN) requires either that a control file with bookkeeping information on the files to restore is mounted or that a database session to an RMAN catalog with like information is available. DBMS_BACKUP_RESTORE makes possible a restore without RMAN in a disaster scenario. Such a scenario is characterized by the loss of all current control files, and the lack of or unavailability of a recovery catalog or control file backup that contains records of the most recent data file and archived redo log backups. Note that bookkeeping information on backups is aged out of the control file depending on the setting of the initialization parameter CONTROL_FILE_RECORD_KEEP_TIME. The default setting of this parameter is merely seven days.

Recovery Manager (RMAN) is a backup and recovery utility that was introduced with Oracle8. RMAN is a database client just like SQL*Plus, Data Pump Export/Import, SQL*Loader, or any other program that can interact with an Oracle instance through Oracle Call Interface (OCI).

RMAN itself does not back up or restore any part of an Oracle database. If you recall that RMAN can run on any machine within a network, connect to an Oracle instance on a remote database server, and perform backup and recovery, it is quite clear that RMAN neither needs to nor has the capability to read or write any database files. Instead, RMAN uses the undocumented package DBMS_BACKUP_RESTORE to instruct an Oracle instance to perform backup and restore operations.

The Oracle DBMS supports mounted file systems and so-called media managers for storage of backups. A media manager is software that integrates with an Oracle instance through the System Backup to Tape SBT) interface and is capable of controlling tape devices for storing backups. The SBT interface is a specification by Oracle Corporation. The specification is disseminated to software companies wishing to support RMAN-based backup and recovery. The SBT interface is not documented in ORACLE DBMS documentation and is usually implemented as a shared library (a.k.a. dynamic link library or DLL), which is used by the program $ORACLE_HOME/bin/ oracle (or oracle.exe on Windows) that implements an Oracle instance. Oracle provides an implementation of the SBT interface that is linked with the executable oracle[.exe] by default. Usually, this executable is relinked with a shared library shipped with media management software, to enable an Oracle instance to talk to a media manager.

Oracle recently entered the circle of companies that provide media management software by offering Oracle Secure Backup. Other well-known players are Hewlett-Packard with OmniBack, Symantec with NetBackup and Tivoli Software with Tivoli Storage Manager.

DBMS_BACKUP_RESTORE makes extensive use of the control file to keep track of what was backed up, when, and how. Here, when means at what time the backup started and when it ended. What means what type of file, such as control file, data file, or archived redo log file. How refers to the medium that holds the backup, such as a file system mounted on the database server or a media manager as well as the incremental level at which the backup was taken. Note that Oracle10g only supports incremental levels 0 and 1, whereas Oracle9i and previous releases supported levels 0 to 4. Considering that there are cumulative backups on top of the default differential backups, there is no truly compelling argument for more than two levels.

In what follows, the data inside the control file representing the when, what, and how will be called backup metadata. Please refer to the following table for an overview of some of the V$ views that provide access to the metadata.


V$ View Purpose Related RMAN Command


Archived redo log files, their thread number, sequence number, and status (available, deleted, expired, or unavailable) BACKUP ARCHIVELOG






Backups of data files (FILE# > 0) and control files (CONTROLFILE_TYPE IS NOT NULL); both types of files may be present in the same backup piece since a backup of file 1, which belongs to tablespace SYSTEM, also backs up the control file, unless automatic control file backups are enabled (AUTOBACKUP) BACKUP DATABASE


Backup pieces with creation time, device type, status, and path name, (column HANDLE)
V$BACKUP_SET Backup sets consist of one or more backup pieces, in case the files contained in a backup set exceed the maximum piece size BACKUP
Backups of the server parameter file BACKUP SPFILE
Backups of archived redo logs BACKUP ARCHIVELOG

Oracle DBMS releases up to Oracle8i release 3 (8.1.7) had a considerable vulnerability due to the fact that the database - the control file, to be precise - is used to keep track of backups. Of course, RMAN supported a recovery catalog for duplicating recovery-related information in the control file right from the first release with Oracle8. The recovery catalog enables restore operations when no control file is available. Contrary to the control file, backup metadata in a recovery catalog are exempt from overwrites (parameter CONTROLFILE_RECORD_KEEPTIME). This left the architecture with the following two vulnerabilities:

. Metadata on backups that were taken without connecting to a catalog (NOCATALOG command line switch) and were never propagated by a subsequent run of RMAN are not registered in the recovery catalog. Let's say a backup script is smart enough to check the availability of the catalog and to run RMAN with the NOCATALOG option, should the availability test fail. Should you lose the most recent control file before the catalog and control file are resynchronized, you would not be able to restore the most recent archived redo log files, even if the recovery catalog outage was already over.

. After the loss of the most recent control file, recovery will not be possible while there is
an outage of the recovery catalog.

No special action is required to resynchronize backup metadata from the control file with the recovery catalog. If needed, RMAN automatically propagates metadata records from the control file to the recovery catalog whenever an RMAN command is run while connected to a catalog. This feature may be leveraged to synchronize the control file metadata with two or more recovery catalogs on different database servers, thus achieving higher availability of the catalog without resorting to clustering or replication technologies. Merely run RMAN one more time after the backup has finished, connect to an additional recovery catalog (e.g., with CONNECT CATALOG), and execute the RMAN command RESYNC CATALOG.

The aforementioned vulnerabilities could be worked around by creating a control file copy (ALTER DATABASE BACKUP CONTROLFILE TO 'path') and backing up that copy with a file system backup utility. The downside of this approach is that the administrator performing the restore will need to be familiar with and privileged for use of both RMAN and file system restore utilities. Often, privileges for file system restores are available only to privileged operating system users such as root on UNIX systems or the group Administrators on Windows systems. Thus, a DBA would need assistance from a system administrator to perform restore operations.

Oracle9i RMAN shipped with the new automatic control file backup functionality, which addresses the issues discussed above. Automatic backup of the control file after each backup makes sure that the most recent copy of the control file, i.e., the only copy that contains all the bookkeeping information required to restore the last backup, is either backed up to disk or to a media manager. However, this feature is disabled by default, such that databases remain vulnerable, unless the DBA enables this new feature.

For the first time, thanks to automatic control file backup, the most recent control file can be restored without accessing a recovery catalog. An example of how to restore a control file from an automatic control file backup is shown in the next code segment. The database identifier (V$DATABASE.DBID), which is stored inside the control file, is required by restore operations.

To restore an automatic control file backup after the loss of all current control files, the database identifier needs to be set with the command SET DBID.

C:> rman target /
Recovery Manager: Release - Production on Mon Jul 2 18:32:08 2007
connected to target database: TEN (not mounted)
RMAN> SET DBID 2848896501;
executing command: SET DBID
Starting restore at 02.07.07 18:32
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: looking for autobackup on day: 20070702
channel ORA_DISK_1: autobackup found: c-2848896501-20070702-02
channel ORA_DISK_1: control file restore from autobackup complete
output filename=C:\ORADATA\TEN\CONTROL01.CTL
Finished restore at 02.07.07 18:32

The default format for automatic control file backups is %F. This translates to C-database_id-YYYYMMDD-QQ, where QQ represents a hexadecimal sequence number between 0 and FF (0-256 in decimal). The remaining format string adheres to the well-known SQL date and time format models. In case a custom format has been configured - which I strongly discourage - a RESTORE command for an automatic control file backup must be preceded by the command SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE device_type TO 'autobackup_format', to let RMAN know the non-default format. By the way, it is undocumented how RMAN uses the hexadecimal sequence number QQ. It serves to generate unique file names for automatic control file backups within a single day. Remember that due to the date format 'YYYYMMDD' without a time component, all automatic control file backups within a single day would have the same filename or handle (V$BACKUP_PIECE.HANDLE). As the clock strikes 12 and a new day is about to dawn, RMAN begins using the new value of 'YYYYMMDD' and resets QQ to zero. But what if more than 256 automatic control file backups were written within a single day? Then RMAN leaves QQ at FF and overwrites the backup piece with sequence FF (QQ=FF). Here is the proof:

SQL> SELECT completion_time, handle
FROM v$backup_piece
WHERE handle LIKE '%FF';
--------------- ------------------------
02.07.07 22:04 C-2848896501-20070702-FF

After another automatic control file backup on the same day, the backup piece name has been reused and the query yields:

SQL> SELECT completion_time, handle
FROM v$backup_piece
WHERE handle LIKE '%FF';
--------------- ------------------------
02.07.07 22:30 C-2848896501-20070702-FF

The first control file autobackup one day later again uses the hexadecimal sequence number 00.

SQL> SELECT completion_time, handle
FROM v$backup_piece
WHERE handle like '%00';
--------------- ------------------------
02.07.07 17:17 C-2848896501-20070702-00
03.07.07 22:51 C-2848896501-20070703-00

This is the only situation that I am aware of, where RMAN overwrites existing backup pieces with new data. Under all other circumstances, RMAN refuses to overwrite existing files and aborts the command BACKUP with 'ORA- 19506: failed to create sequential file, name="string", parms="string" for device type SBT_TAPE' or 'ORA-19504: failed to create file "string" for device type disk' and 'ORA-27038: created file already exists'.

This behavior sheds some light on the algorithm RMAN uses to generate backup piece handles in its quest for a control file from an automatic control file backup. Among others, the settings of the optional parameters MAXDAYS and MAXSEQ in the command RESTORE CONTROLFILE FROM AUTOBACKUP determine the handle names RMAN generates. If MAXSEQ is not set, RMAN uses the default of 256, translates it to hexadecimal FF, and builds a handle name according to the %F format presented earlier. If no such backup piece exists, the sequence number (QQ) is decremented by 1 until it reaches 0. If no control file backup is found during this process, RMAN moves on to the previous day and recommences the process with a hexadecimal sequence number of FF. The default for MAXDAYS is 7 with a permissible range between 1 and 366. If, after MAXDAYS days before the current day have been tried, RMAN still fails to locate a suitable backup piece, the RESTORE command terminates with the error 'RMAN-06172: no autobackup found or specified handle is not a valid copy or piece'. The search may be accelerated somewhat by using a lower MAXSEQ such as 10 in cases where it is known that no more than 10 BACKUP commands get executed per day due to data file and archived log backups.

The new automatic control file backup functionality - which also backs up the server parameter file (SPFILE), if present - is disabled by default (page 2-69 of the Oracle Database Backup and Recovery Reference 10g Release 2). There certainly are production systems that are still vulnerable, since no backup - automatic or not - of the control file with the most recent recovery-related metadata exists.

Undoubtedly, the likelihood of scenarios 1 and 2 described earlier is low. Nonetheless it is
reassuring to learn that even on systems where the quality of backup scripts is insufficient, the
road to full recovery is still open when following the advice on the undocumented package

Apress is a technical publisher devoted to meeting the needs of IT professionals, software developers, and programmers, with more than 700 books in print and a continually expanding portfolio of publications. Apress provides high-quality, no-fluff content in print and electronic formats that help serious technology professionals build a comprehensive pathway to career success.

Add Comment      Leave a comment on this blog post

Post a comment





(Maximum characters: 1200). You have 1200 characters left.




Subscribe to our Newsletters

Sign up now and get the best business technology insights direct to your inbox.