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.

The focus in Oracle Database backup and recovery is on the physical backup of database files, which permits you to reconstruct your database. Oracle Recovery Manager (RMAN), a command-line tool, is the method preferred by Oracle for efficiently backing up and recovering your Oracle database. The files protected by the backup and recovery facilities built into RMAN include

1. data files,
2. control files,
3. server parameter files, and
4. archived redo log files.

With these files you can reconstruct your database. RMAN is designed to work intimately with the server, providing block-level corruption detection during backup and restore. RMAN optimizes performance and space consumption during backup with file multiplexing and backup set compression, and integrates with leading tape and storage media products. The backup mechanisms work at the physical level to protect against file damage, such as the accidental deletion of a data file or the failure of a disk drive. RMAN can also be used to perform point-in-time recovery to recover from logical failures when other techniques such as flashback cannot be used. Logical backups, such as exporting database objects such as tables or tablespaces, are a useful supplement to physical backups, but cannot protect your whole database. An effective backup strategy must be based on physical backups. The Oracle Database flashback features provide a range of physical and logical data recovery tools as efficient, easy-to-use alternatives to physical and logical backups. The flashback features enable you to reverse the effects of unwanted database changes without restoring data files from backup.

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:

1. Datafiles
2. Control files
3. Parameter files
4. Password files
5. Archived redo log files

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.