Requirements for Open database backup in Oracle

If a business requires its database to run 24 hours a day, 7 days a week, there are important implications for backup and recovery. If the database cannot be shut down to perform backups, open database backup must be selected as the most prudent backup and recovery strategy. An open database backup allows the DBA to perform backups of all the tablespaces or individual datafiles while the database is in use.

Before a DBA can perform backups of tablespaces or a specific datafile while the database is online, two criteria must be met:

1. The database must be operating in ARCHIVELOG mode; and
2. The ARCH process must be enabled. This can be done automatically at instance startup or manually following instance startup.

In addition, the DBA must know which files need to be backed up and where these files are located. The following files must be backed up:

The image below shows the routine a DBA must follow to make an open database backup. The DBA backs up

1. the tablespace USER_DATA once a week,
2. the tablespace SYSTEM once a month, and
3. the tablespace TEMP once every two weeks.

All the backup actions are recorded respectively in the redo logs.

Information about the required files is found in data dictionary views. You can use V$DATAFILE, V$CONTROLFILE, and DBA_DATA_FILES just like you did for closed database backups. Because an open database backup is performed by backing up tablespaces or individual files, make sure you query DBA_DATA_FILES to find the required datafiles and their respective tablespaces. To obtain information about archived redo log files, query data dictionary views

1. V$ARCHIVED_LOG,
2. V$ARCHIVE_DEST,
3. V$LOG_HISTORY, and
4. V$DATABASE.

An alternative to using the data dictionary view is to issue the command ARCHIVE LOG LIST in the Server Manager. This command gives the DBA information on the current log mode and status of archiving for the database.
The next lesson explains open database backup options.