17 Making User-Managed Backups

Listing Database Files Before a Backup

Use V$DATAFILE, V$LOGFILE and V$CONTROLFILE to identify the datafiles, log files and control files for your database. This same procedure works whether you named these files manually or allowed Oracle Managed Files to name them.

To list datafiles, online redo logs, and control files:

Determining Datafile Status for Online Tablespace Backups

To check whether a datafile is part of a current online tablespace backup, query the V$BACKUP view.

This view is useful only for user-managed online tablespace backups, because neither RMAN backups nor offline tablespace backups require the datafiles of a tablespace to be in backup mode.

The V$BACKUP view is most useful when the database is open. It is also useful immediately after an instance failure because it shows the backup status of the files at the time of the failure. Use this information to determine whether you have left any tablespaces in backup mode.

V$BACKUP is not useful if the control file currently in use is a restored backup or a new control file created after the media failure occurred. A restored or re-created control file does not contain the information the database needs to populate V$BACKUP accurately. Also, if you have restored a backup of a file, this file's STATUS in V$BACKUP reflects the backup status of the older version of the file, not the most current version. Thus, this view can contain misleading data about restored files.

For example, the following query displays which datafiles are currently included in a tablespace that has been placed in backup mode:

SELECT t.name AS "TB_NAME", d.file# as "DF#", d.name AS "DF_NAME", b.status
FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b
WHERE d.TS#=t.TS#
AND b.FILE#=d.FILE#
AND b.STATUS='ACTIVE';

The following sample output shows that the tools and users tablespaces currently have ACTIVE status:

TB_NAME                 DF#        DF_NAME                           STATUS
----------------------  ---------- --------------------------------  ------
TOOLS                   7          /oracle/oradata/trgt/tools01.dbf  ACTIVE
USERS                   8          /oracle/oradata/trgt/users01.dbf  ACTIVE

In the STATUS column, NOT ACTIVE indicates that the file is not currently in backup mode (that is, you have not executed the ALTER TABLESPACE ... BEGIN BACKUP or ALTER DATABASE BEGIN BACKUP statement), whereas ACTIVE indicates that the file is currently in backup mode.

Making Consistent Whole Database Backups

This section describes how to back up the database with an operating system utility.

To make a consistent whole database backup:

Making User-Managed Backups of Online Read/Write Tablespaces

You must put a read/write tablespace in backup mode to make user-managed datafile backups when the tablespace is online and the database is open. The ALTER TABLESPACE ... BEGIN BACKUP statement places a tablespace in backup mode. In backup mode, the database copies whole changed data blocks into the redo stream. After you take the tablespace out of backup mode with the ALTER TABLESPACE ... END BACKUP or ALTER DATABASE END BACKUP statement, the database advances the datafile header to the current database checkpoint.

When restoring a datafile backed up in this way, the database asks for the appropriate set of redo log files to apply if recovery be needed. The redo logs contain all changes required to recover the datafiles and make them consistent.

To back up online read/write tablespaces in an open database:

Making Multiple User-Managed Backups of Online Read/Write Tablespaces

When backing up several online tablespaces, you can back them up either serially or in parallel. Use either of the following procedures depending on your needs.

Ending a Backup After an Instance Failure or SHUTDOWN ABORT

The following situations can cause a tablespace backup to fail and be incomplete:

• The backup completed, but you did not run the ALTER TABLESPACE ... END BACKUP statement.

• An instance failure or SHUTDOWN ABORT interrupted the backup.

Whenever crash recovery is required, if a datafile is in backup mode when an attempt is made to open it, then the database will not open the database until either a recovery command is issued, or the datafile is taken out of backup mode.

For example, the database may display a message such as the following at startup:

ORA-01113: file 12 needs media recovery
ORA-01110: data file 12: '/oracle/dbs/tbs_41.f'

If the database indicates that the datafiles for multiple tablespaces require media recovery because you forgot to end the online backups for these tablespaces, then so long as the database is mounted, running the ALTER DATABASE END BACKUP statement takes all the datafiles out of backup mode simultaneously.

In high availability situations, and in situations when no DBA is monitoring the database, the requirement for user intervention is intolerable. Hence, you can write a crash recovery script that does the following:

An automated crash recovery script containing ALTER DATABASE END BACKUP is especially useful in the following situations:

• All nodes in an Oracle Real Application Clusters (RAC) configuration fail.

• One node fails in a cold failover cluster (that is, a cluster that is not a RAC configuration in which the secondary node must mount and recover the database when the first node fails).

Alternatively, you can take the following manual measures after the system fails with tablespaces in backup mode:

• Recover the database and avoid issuing END BACKUP statements altogether.

• Mount the database, then run ALTER TABLESPACE ... END BACKUP for each tablespace still in backup mode.

Making User-Managed Backups of Read-Only Tablespaces

When backing up an online read-only tablespace, you can simply back up the online datafiles. You do not have to place the tablespace in backup mode because the database is not permitting changes to the datafiles.

If the set of read-only tablespaces is self-contained, then in addition to backing up the tablespaces with operating system commands, you can also export the tablespace metadata with the transportable tablespace functionality. In the event of a media error or a user error (such as accidentally dropping a table in the read-only tablespace), you can transport the tablespace back into the database.

To back up online read-only tablespaces in an open database:

Backing Up the Control File to a Binary File

The primary method for backing up the control file is to use a SQL statement to generate a binary file. A binary backup is preferable to a trace file backup because it contains additional information such as the archived log history, offline range for read-only and offline tablespaces, and backup sets and copies (if you use RMAN). Note that binary control file backups do not include tempfile entries.

To back up the control file after a structural change:

Backing Up the Control File to a Trace File

To back up the control file to a trace file, mount or open the database and issue the following SQL statement:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

If you specify neither the RESETLOGS nor NORESETLOGS option in the SQL statement, then the resulting trace file contains versions of the control file for both RESETLOGS and NORESETLOGS options. Tempfile entries are included in the output using "ALTER TABLESPACE... ADD TEMPFILE" statements.

ALTER DATABASE BACKUP CONTROLFILE TO TRACE NORESETLOGS;

# The following statements will create a new control file and use it to open the 
# database. Log history and RMAN metadata will be lost. Additional logs may be 
# required for media recovery of offline datafiles. Use this only if the current 
# version of all online logs are available.

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE SALES NORESETLOGS ARCHIVELOG
     MAXLOGFILES 32
     MAXLOGMEMBERS 2
     MAXDATAFILES 32
     MAXINSTANCES 16
     MAXLOGHISTORY 1600
LOGFILE
     GROUP 1
       '/diska/prod/sales/db/log1t1.dbf',
       '/diskb/prod/sales/db/log1t2.dbf'
     )  SIZE 100K
    GROUP 2 
       '/diska/prod/sales/db/log2t1.dbf',
        '/diskb/prod/sales/db/log2t2.dbf'
    ) SIZE 100K,
    GROUP 3 
       '/diska/prod/sales/db/log3t1.dbf',
       '/diskb/prod/sales/db/log3t2.dbf'
    ) SIZE 100K
DATAFILE
    '/diska/prod/sales/db/database1.dbf',
    '/diskb/prod/sales/db/filea.dbf'
;

# This datafile is offline, but its tablespace is online. Take the datafile 
# offline manually.
ALTER DATABASE DATAFILE '/diska/prod/sales/db/filea.dbf' OFFLINE;

# Recovery is required if any datafiles are restored backups,
# or if the most recent shutdown was not normal or immediate.
RECOVER DATABASE;

# All redo logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;

# The database can now be opened normally.
ALTER DATABASE OPEN;

# The backup control file does not list read-only and normal offline tablespaces 
# so that Oracle can avoid performing recovery on them. Oracle checks the data 
# dictionary and finds information on these absent files and marks them
# 'MISSINGxxxx'. It then renames the missing files to acknowledge them without 
# having to recover them.
ALTER DATABASE RENAME FILE 'MISSING0002'
     TO '/diska/prod/sales/db/fileb.dbf';

About the Suspend/Resume Feature

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.

With 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 backup mode functionality, you can suspend database I/O so that no new I/O can be performed. You can then access the suspended database to make backups without I/O interference.

You do not need to use SUSPEND/RESUME to make split mirror backups in most cases, although it is necessary if your system requires the database cache to be free of dirty buffers before a volume can be split. Some RAID devices benefit from suspending writes while the split operation is occurring; your RAID vendor can advise you on whether your system would benefit from this feature.

The ALTER SYSTEM SUSPEND statement suspends the database by halting I/Os to datafile headers, datafiles, and control files. When the database is suspended, all pre-existing I/O operations can complete; however, any new database I/O access attempts are queued.

The ALTER SYSTEM SUSPEND and ALTER SYSTEM RESUME statements operate on the database and not just the instance. If the ALTER SYSTEM SUSPEND statement is entered on one system in a RAC configuration, then the internal locking mechanisms propagate the halt request across instances, thereby suspending I/O operations for all active instances in a given cluster.

Making Backups in a Suspended Database

After a successful database suspension, you can back up the database to disk or break the mirrors. Because suspending a database does not guarantee immediate termination of I/O, Oracle recommends that you precede the ALTER SYSTEM SUSPEND statement with a BEGIN BACKUP statement so that the tablespaces are placed in backup mode.

You must use conventional user-managed backup methods to back up split mirrors. RMAN cannot make database backups or copies because these operations require reading the datafile headers. After the database backup is finished or the mirrors are re-silvered, then you can resume normal database operations using the ALTER SYSTEM RESUME statement.

Backing up a suspended database without splitting mirrors can cause an extended database outage because the database is inaccessible during this time. If backups are taken by splitting mirrors, however, then the outage is nominal. The outage time depends on the size of cache to flush, the number of datafiles, and the time required to break the mirror.

Note the following restrictions for the SUSPEND/RESUME feature:

• In a RAC configuration, you should not start a new instance while the original nodes are suspended.

• No checkpoint is initiated by the ALTER SYSTEM SUSPEND or ALTER SYSTEM RESUME statements.

• You cannot issue SHUTDOWN with IMMEDIATE, NORMAL, or TRANSACTIONAL options while the database is suspended.

• Issuing SHUTDOWN ABORT on a database that was already suspended reactivates the database. This prevents media recovery or crash recovery from hanging.

To make a split mirror backup in SUSPEND mode:

Backing Up to Raw Devices on UNIX

When backing up to or from raw devices, the UNIX dd command is the most common backup utility. See your operating system specific documentation for complete details about this utility.

Using dd effectively requires specifying the correct options, based on your database. Details about your database that affect the options you use for dd are listed in the following table.

The information in the preceding table enables you to set the dd options specified in Table 17-1.

Because a raw device can be the input or output device for a backup, you have four possible scenarios for the backup. The possible options for dd depend on which scenario you choose, as illustrated in Table 17-2.

% dd if=/dev/rsd1b of=/dev/rsd2b bs=8k skip=8 seek=8 count=3841

% dd if=/dev/rsd1b of=/backup/df1.dbf bs=8k skip=8 count=3841

% dd if=/backup/df1.dbf of=/dev/rsd2b bs=8k seek=8

% dd if=/oracle/dbs/df1.dbf of=/backup/df1.dbf bs=1024k

Backing Up to Raw Devices on Windows

Like UNIX, Windows supports raw disk partitions in which the database can store datafiles, online logs, and control files. Each raw partition is assigned either a drive letter or physical drive number and does not contain a file system. As in UNIX, each raw partition on NT is mapped to a single file.

NT differs from UNIX in the naming convention for Oracle files. On NT, raw datafile names are formatted as follows:

\\.\drive_letter:
\\.\PHYSICALDRIVEdrive_number

For example, the following are possible raw filenames:

\\.\G:
\\.\PHYSICALDRIVE3

Note that you can also create aliases to raw filenames. The standard Oracle database installation provides a SETLINKS utility that can create aliases such as \\.\Datafile12 that point to filenames such as \\.\PHYSICALDRIVE3.

The procedure for making user-managed backups of raw datafiles is basically the same as for copying files on an NT file system, except that you should use the Oracle OCOPY utility rather than the NT-supplied copy.exe or ntbackup.exe utilities. OCOPY supports 64-bit file I/O, physical raw drives, and raw files. Note that OCOPY cannot back up directly to tape.

To display online documentation for OCOPY, enter OCOPY by itself at the Windows prompt. Sample output follows:

Usage of OCOPY:
     ocopy from_file [to_file [a | size_1 [size_n]]]
     ocopy -b from_file to_drive
     ocopy -r from_drive to_dir

Note the important OCOPY options described in the following table.

OCOPY "\\.G:" C:\backup\datafile12.bak

# first argument is filename, second argument is drive
OCOPY -b "\\.\G:" A:\

# first argument is drive, second argument is directory
OCOPY -r A:\ "\\.\G:"

Testing the Restore of Backups

The best way to test the usability of backups is to restore them to a separate host and attempt to open the database, performing media recovery if necessary. This option requires that you have a separate host available for the restore procedure.

Running the DBVERIFY Utility

The DBVERIFY program is an external command-line utility that performs a physical data structure integrity check on an offline datafile. Use DBVERIFY primarily when you need to ensure that a user-managed backup of a 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, to perform an integrity check on datafile tbs_52.f on UNIX, you can run the dbv command as follows:

% dbv file=tbs_52.f

Sample dbv output follows:

DBVERIFY - Verification starting : FILE = users01.dbf


DBVERIFY - Verification complete

Total Pages Examined         : 250
Total Pages Processed (Data) : 1
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 2
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 247
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0

Recording the Locations of Datafiles, Control Files, and Online Redo Logs

The following useful SQL script displays the location of all control files, datafiles, and online redo log files for the database:

SELECT NAME FROM V$DATAFILE
UNION ALL
SELECT MEMBER FROM V$LOGFILE
UNION ALL
SELECT NAME FROM V$CONTROLFILE;

Recording the Locations of Archived Redo Logs

You can determine the location of the default archived log destinations by executing the following SQL script:

SELECT NAME, VALUE 
FROM V$PARAMETER 
WHERE NAME LIKE log_archive_dest%
AND VALUE IS NOT NULL;

NAME                               VALUE
---------------------------------- -------------------------------------------
log_archive_dest_1                 LOCATION=/oracle/work/arc_dest/arc
log_archive_dest_state_1           enable

Determine the format for archived logs by running SHOW as follows:

SHOW PARAMETER LOG_ARCHIVE_FORMAT

To see a list of all the archived logs recorded in the control file, issue this query:

SELECT NAME FROM V$ARCHIVED_LOG;

Recording the Locations and Dates of Backup Files

It is not enough to merely record the location of backup files: you must correlate the backups with the original files. If possible, name the backups with names derived from the name of the primary file. Whatever naming system you use, keep a table containing the relevant information. For example, you could keep the following table as a record of database file locations in case of a restore emergency.