Physical Backups  «Prev  Next»

Lesson 7 Open database backup options
Objective Explain the options for tablespace backups.

Oracle Open Database Backup Options

One of the advantages of open database backup is its flexibility. A DBA can choose to backup all datafiles for a tablespace or just one datafile for that tablespace. An additional advantage is that the database remains accessible during the backup process. Tablespace and datafile backups should only be performed when the database is operating in ARCHIVELOG mode. Such backups cannot be used to restore a database running in NOARCHIVELOG mode.
  1. Backing up a tablespace: It is important to frequently back up tablespaces with rollback segments and tablespaces that contain important data such as system data. Tablespaces containing only temporary segments do not need to be backed up.
  2. Backing up an individual datafile: One reason a DBA may choose to frequently backup the datafiles of extensively used tablespaces is to reduce database recovery time. If recovery is required and a more recent copy of the datafile is used to restore the damaged one, the amount of time a datafile remains in "Fuzzy" state is minimized. Because of this, fewer archived redo logs need to be applied to the restored datafiles to roll them forward to the time of the failure.

FUZZY Backup Problem

The "Fuzzy" backup problem often appears during the process of an open database backup. For example, let us say a situation arises where the operating system backup starts at the beginning of the datafile and midway through the copy operation, a change occurs with bytes behind the read/write head. Bytes behind the head are missed but are vital to the change. The resulting imperfect copy must be recovered using the Redo Logs in use at the time of the backup. The following series of images demonstrate the Fuzzy problem in an open database backup.


1) At Time T1 a backup of a datafile is in progress and the current redo log sequence is 76.
1) At Time T1 a backup of a datafile is in progress and the current redo log sequence is 76.

2) At time T2 a log switch occurs and the current redo log sequence is #77
2) At time T2 a log switch occurs and the current redo log sequence is #77

3) At time T3 a data change in one transaction is affecting table 1 and table 2. The changed data for Table 1 is stored in the file.dbf. However, it is not copied into the copy of file.dbf because the read-write head already passed that area. The changes are recorded in the current redo log #77 as X1 and X2.
3) At time T3 a data change in one transaction is affecting table 1 and table 2. The changed data for Table 1 is stored in the file.dbf. However, it is not copied into the copy of file.dbf because the read-write head already passed that area. The changes are recorded in the current redo log #77 as X1 and X2.

4) At time T4 the read-write head only copies change X2.
4) At time T4 the read-write head only copies change X2.

5) The copy of the file.dbf is in a fuzzy state because it does not contain all the information about the latest transaction . When a recovery is necessary, the redo log #77 (now written into Archive Log #77) has to be applied to restore the entire information about this latest transaction.
5) The copy of the file.dbf is in a fuzzy state because it does not contain all the information about the latest transaction . When a recovery is necessary, the redo log #77 (now written into Archive Log #77) has to be applied to restore the entire information about this latest transaction.

Fuzzy Backup Problems in Oracle

Problem: Due to electric failure my database server went down. When I restarted it returned an ORA-00214 as shown below: 
SQL> startup

ORACLE instance started.
Total System Global Area 83605532 bytes
Fixed Size 75804 bytes
Variable Size 57016320 bytes
Database Buffers 26435584 bytes
Redo Buffers 77824 bytes


ORA-00214: controlfile 'D:\ORACLE\ORADATA\DATA\CONTROL01.CTL' version 11541 
inconsistent with file 'D:\ORACLE\ORADATA\DATA\CONTROL03.CTL' version 11538

Question: What is the cause of ORA-00214 and what is the solution?
Answer: The docs note this on the ORA-00214 error:
ORA-00214: control file "string" version string inconsistent with file "string" version string.
Cause: An inconsistent set of control files, datafiles/logfiles, and redo files was used.
Resolution: Use a consistent set of control files, datafiles/logfiles, and redo log files. That is, all the files must map to the same database and be from the same time period. The primary reasons an ORA-00214 error include forgetting to replace ALL OF the current control files in all locations specified in the init.ora control_files parameter. Remember, all of the control files, in all locations, must match exactly.
All copies of the control file must have the same internal sequence number[1] for Oracle to start up the database or shut it down in normal or immediate mode. If the database is running and the checkpoint in the file header could not be advanced the datafile will be taken offline. Typical scenarios in which you may receive an ORA-00214 include:
  1. You have restored the control file from backup, but forgot to copy it onto all of the mirrored copies of the control file as listed in the "CONTROL_FILES" parameter in the init.ora file for this instance
  2. You have moved one or more copies of the control file to a different location while the database was up and running.
  3. You accidentally overwrote one of the copies of the control file with an old copy.
  4. The database or the system crashed while the mirrored copies of the control file were being updated, causing them to be out of sync.
  5. You are restoring a database backup that was improperly taken with the database up and running ("fuzzy" backup).

The next lesson shows you how to perform an open database backup.

[1]internal sequence number: The internal sequence number in an Oracle control file is a value that is incremented each time the control file is modified. It is used to detect corruption of the control file.

SEMrush Software