1
Introduction to User-Managed Backup and Recovery

This chapter introduces database concepts that are fundamental to user-managed backup and recovery.

This chapter includes the following topics:

• About User-Managed Backup and Recovery
• Why Use User-Managed Backup and Recovery Methods?
• Overview of User-Managed Backup and Recovery

  See Also: Oracle9i Backup and Recovery Concepts for a conceptual overview of essential backup and recovery concepts

About User-Managed Backup and Recovery

User-managed backup and recovery is any strategy in which Recovery Manager (RMAN) is not used as the principal backup and recovery tool. The basic user-managed backup strategy is to make periodic backups of datafiles and archived logs with operating system commands.

The basic user-managed procedure for recovering from a media failure is as follows:

1. Restore database file backups with operating system commands.
2. Recover restored datafiles with the SQL*Plus RECOVER statement.
3. If the database is closed, then open it for normal use; if it is open, then bring the recovered tablespaces back online.

Why Use User-Managed Backup and Recovery Methods?

Oracle Corporation recommends using RMAN as the foundation of an enterprise backup and recovery strategy, but user-managed methods (that is, methods that do not involve RMAN) are also just as effective. Note that some features such as block media recovery can only be performed with RMAN.

The following are possible circumstances in which you may choose to employ user-managed methods rather than use RMAN:

• You are migrating from an older version of the database to the current version and do not immediately want to update your legacy backup scripts.
• You maintain a network containing Oracle7 and later databases and want a single backup and recovery method to handle all databases in the same way. RMAN only supports Oracle databases of release 8.0 or greater.
• All your RMAN backup are lost and you are forced to restore user-managed backups and perform recovery with the SQL*Plus RECOVER command.

Oracle Corporation supports user-managed backup and recovery as a viable alternative to RMAN.

Overview of User-Managed Backup and Recovery

This section contains these topics:

• About User-Managed Backups
• About User-Managed Restore and Recovery

About User-Managed Backups

User-managed backups can be either logical or physical. You can use the Export utility to make backups of logical objects such as tables, views, and stored procedures, and use the Import utility to restore these objects.

If you do not use RMAN, then you can use operating system utilities to make physical backups. A physical backup is a backup of an Oracle database file or archived redo log located on the operating system. Note that these files can either be manually-managed database files or Oracle-managed files. If you use the Oracle Managed Files feature, then Oracle names the files for you and also deletes them for you when you drop a tablespace. From the point of view of backup and recovery, Oracle managed files are no different from user-managed files.

The following table illustrates the main types of physical backups and the non-RMAN methods for performing these backups.

Backup Object	Backup Method	Example
Datafiles	Operating system utility	% cp df3.f df3.bak
Archived logs	Operating system utility	% cp log_1_23.arc log_1_23.bak
Control files	SQL statement	SQL> ALTER DATABASE BACKUP CONTROLFILE TO cf1.bak
Initialization parameter file	SQL statement	SQL> CREATE PFILE = init.ora.bak FROM SPFILE;
Network and password files	Operating system utility	% cp tnsnames.ora tnsnames.bak C:\> copy tnsnames.ora tnsnames.bak
Logical objects (tables, indexes, PL/SQL units)	Export utility	% export SYSTEM/manager TABLE=hr.emp FILE=emp.dmp

Basic Backup Methodology

The basic method for taking user-managed backups of the whole database is as follows:

1. Identify the datafiles, control files, and archived redo logs to be backed up by querying dynamic performance views or data dictionary tables (refer to "Querying V$ Views to Obtain Backup Information" for procedures).
2. Use an operating system command such as the UNIX cp command to back up datafiles and archived redo logs (refer to "Making User-Managed Backups of the Whole Database" for procedures).
3. Use a SQL statement to back up the control file (refer to "Making User-Managed Backups of the Control File" for procedures).
4. Use an operating system command such as the UNIX cp command to back up configuration files (refer to"Making User-Managed Backups of Miscellaneous Oracle Files" for procedures).

   Caution: Do not back up online redo logs. If you reset the online logs after media recovery, and then accidentally apply the backed up logs to the database, then you can corrupt the database.

Consistent and Inconsistent User-Managed Backups

You can use RMAN or operating system commands to make an inconsistent backup or a consistent backup. An inconsistent backup is a backup of one or more database files made while the database is open or after the database has not been shut down normally. A consistent backup is a backup of one or more database files that you make after the database has been shut down normally. Unlike an inconsistent backup, a consistent backup does not require recovery after it is restored.

A consistent whole database backup is the only valid backup option for databases running in NOARCHIVELOG mode, because otherwise redo needed for recovery is not available. In NOARCHIVELOG mode, Oracle overwrites redo records without archiving them first.

If you run the database in ARCHIVELOG mode, then you can back up database files while the database is open. These backups are inconsistent, but as long as you have the necessary archived redo logs you can recover these backups. You can either take a tablespace offline and back up its datafiles, or perform an online backup. An online backup occurs when the tablespace is still online. To perform an online backup, you must begin and end the backup with SQL statements that place the tablespace in and take the tablespace out of backup mode.

Backups in SUSPEND Mode

Some third-party tools allow you to mirror a set of disks or logical devices, that is, maintain an exact duplicate of the primary data in another location, and then split the mirror. Splitting the mirror involves separating the copies so that you can use them independently.

Using the SUSPEND/RESUME functionality, you can suspend I/O to the database, then split the mirror and make a backup of the split mirror. By using this feature, which complements the online backup functionality, you can quiesce the database so that no new I/O can be performed. You can then access the suspended database to make backups without I/O interference.

Verification of Backups

The best method for backup verification is to perform a test restore and recover of the database to another location. If you successfully perform this operation, then you know that the backup is valid.

You can also use the DBVERIFY utility to test backups for corruption. DBVERIFY is an external command-line utility that performs a physical data structure integrity check on offline datafiles. Use DBVERIFY primarily when you need to ensure that a backup datafile is valid before it is restored or as a diagnostic aid when you have encountered data corruption problems. The name and location of DBVERIFY is dependent on your operating system (for example, dbv on Sun/Sequent systems).

About User-Managed Restore and Recovery

When a media failure occurs that damages datafiles, you must restore backups of the affected datafiles using operating system commands and then perform recovery with the SQL*Plus RECOVER command. You can either restore only some datafiles and perform recovery of the tablespaces containing the restored datafiles, or restore and recover the entire database. You should keep careful records of your backups so that you know the original locations of the datafiles as well as the locations of the backups.

To begin media recovery operations when your database is running in ARCHIVELOG mode, use the SQL*Plus RECOVER command. The two basic types of media recovery are complete recovery, in which all redo generated on the database is applied, and incomplete recovery, in which not all the existing redo is applied. Incomplete recovery is only valid for restore and recovery of the entire database. A special procedure for performing incomplete recovery of an individual tablespace is called tablespace point-in-time recovery (TSPITR).

Basic Restore and Recovery Methodology

The basic user-managed restore and recovery strategy is as follows:

1. Determine what you need to restore and recover (refer to "Determining Which Datafiles Require Recovery" for procedures).
2. Restore backups of files permanently damaged by media failure by using an operating system utility. If you cannot restore a datafile to its original location, then relocate the restored datafile and change the location in the control file (refer to "Restoring Datafiles" for procedures).
3. Restore any necessary archived redo log files with an operating system utility (refer to "Restoring Archived Redo Logs" for procedures).
4. Use the SQL*Plus RECOVER command to recover the files, as described in "Performing User-Managed Media Recovery: Overview".

Implications of the Archiving Mode for Media Recovery

The archiving mode of the database determines the type of recovery that you can perform. For example, if a database is in NOARCHIVELOG mode and a media failure damages some or all of the datafiles, then usually the only option for recovery is to restore the most recent consistent, whole database backup and open it.

The disadvantage of NOARCHIVELOG mode is that to recover the database from the time of the most recent full backup up to the time of the media failure, you have to reenter manually all of the changes executed in that interval. If your database is in ARCHIVELOG mode, and the redo logs covering this interval are available as archived log files or online log files, then you can use complete or incomplete recovery to reconstruct your database, thereby minimizing the number of lost changes.

User-Managed Tablespace Point-in-Time Recovery (TSPITR)

User-managed tablespace point-in-time recovery (TSPITR) enables you to quickly recover one or more tablespaces (other than the SYSTEM tablespace) to a time that is different from that of the rest of the database.

User-managed TSPITR is most useful for recovering the following:

• An erroneous DROP TABLE or TRUNCATE TABLE operation.
• A table that is logically corrupted.
• An incorrect batch job or other DML statement that has affected only a subset of the database.
• A logical schema to a point different from the rest of the physical database when multiple schemas exist in separate tablespaces of one physical database.
• A tablespace in a VLDB (very large database) when TSPITR is more efficient than restoring the whole database from a backup and rolling it forward (see "Preparing for Tablespace Point-in-Time Recovery: Basic Steps" before making any decisions).

  See Also: Chapter 7, "Performing User-Managed TSPITR"