27 Making User-Managed Database Backups

Listing Database Files Before a Backup

Use V$DATAFILE and V$CONTROLFILE to identify the datafiles 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 and control files:

1. Start SQL*Plus and query V$DATAFILE to obtain a list of datafiles. For example, enter:

   SELECT NAME FROM V$DATAFILE;
   

   You can also join the V$TABLESPACE and V$DATAFILE views to obtain a listing of datafiles along with their associated tablespaces:

   SELECT   t.NAME "Tablespace", f.NAME "Datafile"
   FROM     V$TABLESPACE t, V$DATAFILE f
   WHERE    t.TS# = f.TS#
   ORDER BY t.NAME;
   

2. Obtain the filenames of the current control files by querying the V$CONTROLFILE view. For example, issue the following query:

   SELECT NAME FROM V$CONTROLFILE;
   

   Note that you only need to back up one copy of a multiplexed control file.

3. If you plan to take a control file backup with the ALTER DATABASE BACKUP CONTROLFILE TO 'filename' statement, then save a list of all datafiles and online redo log files with the control file backup. Because the current database structure may not match the database structure at the time a given control file backup was created, saving a list of files recorded in the backup control file can aid the recovery procedure.

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:

1. If the database is open, then use SQL*Plus to shut down the database with the NORMAL, IMMEDIATE, or TRANSACTIONAL options.

2. Use an operating system utility to make backups of all datafiles as well as all control files specified by the CONTROL_FILES parameter of the initialization parameter file. Also, back up the initialization parameter file and other Oracle product initialization files. To find these files, do a search for *.ora starting in your Oracle home directory and recursively search all of its subdirectories.

   For example, you can back up the datafiles, control files and archived logs to /disk2/backup as follows:

   % cp $ORACLE_HOME/oradata/trgt/*.dbf /disk2/backup
   % cp $ORACLE_HOME/oradata/trgt/arch/* /disk2/backup/arch
    
   

3. Restart the database with the STARTUP command in SQL*Plus.

   See Also:

   Oracle Database Administrator's Guide for more information on starting up and shutting down a database

Making User-Managed Backups of Offline Tablespaces and Datafiles

Note the following guidelines when backing up offline tablespaces:

• You cannot offline the SYSTEM tablespace or a tablespace with active undo segments. The following technique cannot be used for such tablespaces.

• Assume that a table is in tablespace Primary and its index is in tablespace Index. Taking tablespace Index offline while leaving tablespace Primary online can cause errors when DML is issued against the indexed tables located in Primary. The problem only manifests when the access method chosen by the optimizer needs to access the indexes in the Index tablespace.

To back up offline tablespaces:

1. Before beginning a backup of a tablespace, identify the tablespace's datafiles by querying the DBA_DATA_FILES view. For example, assume that you want to back up the users tablespace. Enter the following statement in SQL*Plus:

   SELECT TABLESPACE_NAME, FILE_NAME
     FROM SYS.DBA_DATA_FILES
     WHERE TABLESPACE_NAME = 'USERS';
    
   TABLESPACE_NAME                   FILE_NAME
   -------------------------------   --------------------------------
   USERS                             /oracle/oradata/trgt/users01.dbf
   

   In this example, /oracle/oradata/trgt/users01.dbf is a fully specified filename corresponding to the datafile in the users tablespace.

2. Take the tablespace offline using normal priority if possible because it guarantees that you can subsequently bring the tablespace online without having to recover it. For example:

   SQL> ALTER TABLESPACE users OFFLINE NORMAL;
   

3. Back up the offline datafiles. For example:

   % cp /oracle/oradata/trgt/users01.dbf /d2/users01_'date "+%m_%d_%y"'.dbf
   

4. Bring the tablespace online. For example:

   ALTER TABLESPACE users ONLINE;
   

   Note:

   If you took the tablespace offline using temporary or immediate priority, then you cannot bring the tablespace online unless you perform tablespace recovery.

5. Archive the unarchived redo logs so that the redo required to recover the tablespace backup is archived. For example, enter:

   ALTER SYSTEM ARCHIVE LOG CURRENT;
   

Making User-Managed Backups of Online Tablespaces and Datafiles

You can back up all or only specific datafiles of an online tablespace while the database is open. The procedure differs depending on whether the online tablespace is read/write or read-only.

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

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). If COMPATIBLE is 10.2 or higher, binary control file backups include tempfile entries.

To back up the control file after a structural change:

1. Make the desired change to the database. For example, you may create a new tablespace:

   CREATE TABLESPACE tbs_1 DATAFILE 'file_1.f' SIZE 10M;
   

2. Back up the database's control file, specifying a filename for the output binary file. The following example backs up a control file to /disk1/backup/cf.bak:

   ALTER DATABASE BACKUP CONTROLFILE TO '/disk1/backup/cf.bak' REUSE;
   

   Specify REUSE to make the new control file overwrite one that currently exists.

Backing Up the Control File to a Trace File

You can back up the control file to a text file that contains a CREATE CONTROLFILE statement. You can edit the trace file to create a script that creates a new control file based on the control file that was current when you created the trace file.

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.

To avoid recovering offline normal or read-only tablespaces, edit them out of the CREATE CONTROLFILE statement. When you open the database with the re-created control file, the database marks these omitted files as MISSING. You can run an ALTER DATABASE RENAME FILE statement to rename them to their original filenames.

To back up the control file to a trace file:

1. Mount or open the database.

2. Execute the following SQL statement:

   ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
   

   The trace files are stored in a subdirectory determined by the DIAGNOSTIC_DEST initialization parameter. To locate the directory for trace files, query the name and value columns of the V$DIAG_INFO dynamic performance view.

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/Os 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:

1. Place the database tablespaces in backup mode. For example, to place tablespace users in backup mode enter:

   ALTER TABLESPACE users BEGIN BACKUP;
   

   If you are backing up all of the tablespaces for your database, you can instead use:

   ALTER DATABASE BEGIN BACKUP;
   

2. If your mirror system has problems with splitting a mirror while disk writes are occurring, then suspend the database. For example, issue the following:

   ALTER SYSTEM SUSPEND;
   

3. Check to make sure that the database is suspended by querying V$INSTANCE. For example:

   SELECT DATABASE_STATUS FROM V$INSTANCE;
   
   DATABASE_STATUS 
   ----------------- 
   SUSPENDED 
   

4. Split the mirrors at the operating system or hardware level.

5. End the database suspension. For example, issue the following statement:

   ALTER SYSTEM RESUME;
   

6. Check to make sure that the database is active by querying V$INSTANCE. For example, enter:

   SELECT DATABASE_STATUS FROM V$INSTANCE;
   
   DATABASE_STATUS 
   ----------------- 
   ACTIVE 
   

7. Take the specified tablespaces out of backup mode. For example, enter the following to take tablespace users out of backup mode:

   ALTER TABLESPACE users END BACKUP;
   

8. Copy the control file and archive the online redo logs as usual for a backup.

   Caution:

   Do not use the ALTER SYSTEM SUSPEND statement as a substitute for placing a tablespace in backup mode.

   See Also:

   • "Making Split Mirror Backups with RMAN"

   • Oracle Database Administrator's Guide for more information about the SUSPEND/RESUME feature

   • Oracle Database SQL Language Reference for the ALTER SYSTEM SUSPEND syntax

Backing Up to Raw Devices on Linux and UNIX

When backing up to or from raw devices, the dd command on Linux and UNIX 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. Table 27-1 lists details about your database that affect the options you use for dd.

The information in Table 27-1 enables you to set the dd options specified in Table 27-2.

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 27-3.

% 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 Linux and 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 Linux and UNIX, each raw partition on Windows is mapped to a single file.

Windows differs from Linux and UNIX in the naming convention for Oracle files. On Windows, 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 Windows file system, except that you should use the Oracle OCOPY utility rather than the Windows-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:" E:\

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

Testing the Restore of Datafile Backups

The best way to test the usability of datafile 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 users01.dbf on Linux or UNIX, run the dbv command as follows:

% dbv file=users01.dbf

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