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

   SELECT NAME FROM V$DATAFILE;
   

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

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

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

   SELECT NAME FROM V$CONTROLFILE;
   

   You only need to back up one copy of a multiplexed control file.

3. If you plan to perform control file backup with the ALTER DATABASE BACKUP CONTROLFILE TO 'filename' statement, then save a list of all data files 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.

Identify the data files associated with a tablespace before creating a user-managed backup of the tablespace.

1. Open SQL*Plus.
2. Perform one of the following operations:

   • To back up offline tablespaces in the root, connect to the root as a common user with the SYSDBA or SYSBACKUP system privilege.

   • To back up offline tablespaces in a PDB, connect to the PDB as a common user or local user with SYSDBA or SYSBACKUP system privilege.

3. Before beginning a backup of a tablespace, identify the tablespace's data files 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 file name corresponding to the data file in the users tablespace.

4. 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;
   

5. Back up the offline data files. For example:

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

6. 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.

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

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

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

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

2. Back up the database's control file, specifying a file name 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.

1. Mount or open the database.
2. Execute the following SQL statement:

   ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

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.

Usually, you do not need to use SUSPEND/RESUME to make split mirror backups, 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 data file headers, data files, and control files. When the database is suspended, all preexisting 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 an Oracle 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.

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;

   Caution:

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

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 statement:

   ALTER SYSTEM SUSPEND;
   

3. Verify that the database is suspended by querying the V$INSTANCE view. Run only this query in a separate SQL session.

   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. Establish that the database is active by querying the V$INSTANCE view. 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 statement 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.

   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

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

Using dd effectively requires that you specify the correct options, based on your database. Table 29-1 lists details about your database that affect the options you use for dd.

The information in Table 29-1 enables you to set the dd options specified in Table 29-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 29-3.

The best way to test the usability of data file 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.