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;