Backup Procedures in Oracle

This page contains materials that were copied from Oracle's on-line documents.

User-managed backups can be either logical or physical. You can use the Export utility to make backups of logical objects such as tables, views, and stored procedures, and use the Import utility to restore these objects. A physical backup is a backup of an Oracle database file or archived redo log located on the operating system. Note that these files can either be manually-managed database files or Oracle-managed files.

The basic method for taking user-managed backups of the whole database is as follows:

Identify the datafiles, control files, and archived redo logs to be backed up by querying dynamic performance views or data dictionary tables.

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;

Obtain the filenames of online redo log files by querying the V$LOGFILE view.

SELECT MEMBER FROM V$LOGFILE;

Obtain the filenames of the current control files by querying the V$CONTROLFILE view..

SELECT NAME FROM V$CONTROLFILE;

Use one of the following command to shut down the database if performing under NOARCHIVE mode.

SHUTDOWN NORMAL
SHUTDOWN IMMEDIATE
SHUTDOWN TRANSACTIONAL

Use an operating system command to back up datafiles and archived redo logs.
Use a SQL statement to back up the control file. (refer to "Making User-Managed Backups of the Control File" for procedures).
Use an operating system command to back up configuration files. You should always back up initialization parameter files, networking and configuration files, and password files. If a media failure destroys these files, then you may have difficulty re-creating your original environment. As a general rule, you should back up miscellaneous Oracle files after changing them. The easiest way to find configuration files is to start in the Oracle home directory and do a recursive search for all files ending in the .ora extension.

You cannot offline the SYSTEM tablespace or a tablespace with active rollback segments. The basic method for taking user-managed backups of the offline tablespaces and datafiles is as follows:

Before beginning a backup of a tablespace, identify the tablespace's datafiles by querying the DBA_DATA_FILES view:
```
SELECT TABLESPACE_NAME, FILE_NAME
  FROM SYS.DBA_DATA_FILES
  WHERE TABLESPACE_NAME = 'users';
```
Take the tablespace offline using normal priority if possible. Normal priority is recommended because it guarantees that you can subsequently bring the tablespace online without the requirement for tablespace recovery:
```
ALTER TABLESPACE users OFFLINE NORMAL;
```
Back up the offline datafiles via Operating System utility.
Bring the tablespace online:
```
ALTER TABLESPACE users ONLINE;
```

Archive the unarchived redo logs so that the redo required to recover the tablespace backup is archived:
```
ALTER SYSTEM ARCHIVE LOG CURRENT;
```

The basic method for taking user-managed backups of the online read/write tablespaces is as follows:

Before beginning a backup of a tablespace, identify all of the datafiles in the tablespace with the DBA_DATA_FILES data dictionary view:
```
SELECT TABLESPACE_NAME, FILE_NAME
FROM SYS.DBA_DATA_FILES
WHERE TABLESPACE_NAME = 'users';
```
Mark the beginning of the online tablespace backup:
```
ALTER TABLESPACE users BEGIN BACKUP;
```

Back up the online datafiles of the online tablespace with operating system commands.
After backing up the datafiles of the online tablespace, indicate the end of the online backup by using the SQL statement ALTER TABLESPACE with the END BACKUP option:

ALTER TABLESPACE users END BACKUP;

Archive the unarchived redo logs so that the redo required to recover the tablespace backup is archived:
```
ALTER SYSTEM ARCHIVE LOG CURRENT;
```

The basic method for taking user-managed backups of the online read-only tablespaces is as follows:

Query the DBA_TABLESPACES view to determine which tablespaces are read-only:

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

Before beginning a backup of a read-only tablespace, identify all of the tablespace's datafiles by querying the DBA_DATA_FILES data dictionary view. For example, assume that you want to back up the history tablespace. Enter the following:
```
SELECT TABLESPACE_NAME, FILE_NAME
FROM SYS.DBA_DATA_FILES
WHERE TABLESPACE_NAME = 'users';
```
Back up the online datafiles of the read-only tablespace with operating system commands. You do not have to take the tablespace offline or put the tablespace in backup mode because users are automatically prevented from making changes to the read-only tablespace.

Back to CS643 schedule