All about Oracle User-Managed Database Backups

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.

===========================================================================

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';

===========================================================================

Making User-Managed Backups of Tablespaces and Datafiles

########################################################

The technique for making user-managed backups of tablespaces and datafiles depends on whether the files are offline or online.

To back up offline tablespaces

==============================

SQL> ALTER TABLESPACE users OFFLINE NORMAL;

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

ALTER TABLESPACE users ONLINE;

ALTER SYSTEM ARCHIVE LOG CURRENT;

alter database datafile offline [for drop];

===========================================

OFFLINE

Specify OFFLINE to take the datafile offline. If the database is open, you must perform media recovery on the datafile before bringing it back online, because a checkpoint is not performed on the datafile before it is taken offline.

FOR DROP

If the database is in noarchivelog mode, you must specify FOR DROP clause to take a datafile offline. However, this clause does not remove the datafile from the database. To do that, you must use an operating system command or drop the tablespace in which the datafile resides. Until you do so, the datafile remains in the data dictionary with the status RECOVER or OFFLINE.

If the database is in archivelog mode, Oracle Database ignores the FOR DROP clause.

alter tablespace offline;

=========================

Specify ONLINE to bring the tablespace online. Specify OFFLINE to take the tablespace offline and prevent further access to its segments. When you take a tablespace offline, all of its datafiles are also offline.

OFFLINE NORMAL

Specify NORMAL to flush all blocks in all datafiles in the tablespace out of the system global area (SGA). You need not perform media recovery on this tablespace before bringing it back online. This is the default.

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

============================================================

SQL> ALTER TABLESPACE users BEGIN BACKUP;

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

SQL> ALTER TABLESPACE users END BACKUP;

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

To back up online tablespaces in parallel

=========================================

SQL> ALTER DATABASE BEGIN BACKUP;

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

SQL> ALTER DATABASE END BACKUP;

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

Caution: Do not use ALTER DATABASE END BACKUP if you have restored any of the affected files from a backup.

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

===========================================================

SELECT TABLESPACE_NAME, STATUS 
FROM DBA_TABLESPACES
WHERE STATUS = 'READ ONLY';

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

Optionally, export the metadata in the read-only tablespace. Using the transportable tablespace feature

% expdp DIRECTORY=dpump_dir1 DUMPFILE=hs.dmp TRANSPORT_TABLESPACES=history LOGFILE=tts.log

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.

To make a split mirror backup in SUSPEND mode

=============================================

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;

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;

SELECT DATABASE_STATUS FROM V$INSTANCE;

DATABASE_STATUS 
----------------- 
SUSPENDED 

Split the mirrors at the operating system or hardware level.

ALTER SYSTEM RESUME;

SELECT DATABASE_STATUS FROM V$INSTANCE;

DATABASE_STATUS 
----------------- 
ACTIVE 

ALTER TABLESPACE users END BACKUP;

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

Making User-Managed Backups to Raw Devices

==========================================

In the following example, you back up from one raw device to another raw device:

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

In the following example, you back up from a raw device to a file system:

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

In the following example, you back up from a file system to a raw device:

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

In the following example, you back up from a file system to a file system, and so can set the block size to a high value to boost I/O performance:

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