Oracle Backup and Recovery

What is Backup and Recovery ?

Backup and recovery encompasses the methods and strategies of protecting databases against system failure plus the capability to reconstruct databases in the event of failure. Failure can be minor in nature or completely catastrophic and involve complete loss.

Importance of Backup and Recovery

Backup and recovery is important due to potential data loss. Data loss on production databases can cause serious problems for the company and/or persons responsible for that data. Thus emplacement of backup and recovery plans, schemes and implementations is absolutely essential for any database installation.

Potential Sources of Failure

  1. Statement failure - syntax error failures are automatically rolled back.
  2. Process failure - abnormal user process termination. The PMON process automatically recovers from process failure.
  3. Oracle instance failure - abnormal database shutdown. Automatic recovery after instance failure is instantiated by Oracle on subsequent database startup.
  4. User/application errors - accidental data deletion is recoverable if backups have been made. Also note that applications running against an Oracle database can potentially cause Oracle database failure at the server itself.
  5. Media failure - damaged or corrupted files could require media recovery involving restoring of database files and database recovery.

Defining Backup Strategy

Various factors about your business can help to determine your backup strategy.

The Facts about Backups and Restoration

Assessing Failure Before Attempting Database Recovery

There are many possible scenarios for failure which may require recovery. Some failures are accidental user deletions and recovery is simple. Other failures can be complete and may require copying of all database files with the database shutdown. Some failures may even lose portions of database changes which may have to be abandoned due to the ability to only recover the database upto a certain point in time.

Accidentally removed records can be restored from an export. If log or control files are lost simply shutdown, recopy a backup version and restart the database. If archive log files are lost perform another backup since archive log files are not required for database restoration unless those archived redo logs contain transactions to be re-executed.Obviously loss of archive logs without loss of anything else is no problem. Media recovery is the most potentially perlexing problem. With a NOARCHIVELOG database the option is restoration of the entire database from a previous physical offline copy of the database. For an ARCHIVELOG mode database simply restore the lost files and recover the database with the RECOVER command. The RECOVER command will rebuild the database back to the state the database was in prior to media failure by re-execution of lost but archived transactions. Obviously if a tablespace was in backup mode at the point of media failure then entirem blocks will be recovered from the archived redo logs for the tablespace which was in backup mode at the time of the media failure.

Access the v$recover_file view in order to assess what requires recovery and why.

The RESETLOGS option  is required for use with the RECOVER command in the cases of incomplete recovery, using a bvackup control file or using a control file created with RESETLOGS option.

Types of Backups

Logical Backup

Logical backup copies the data and logical database structures using the Oracle export utility for backup and the import utility for restoration. An export file is an propietary Oracle binary formatted file cointaining data and DDL (data definition language) of all database objects. The Oracle import utility will restore from this same dump file. Use of Oracle constructed software is probably the simplest and most reliable method since Oracle is bound to understand Oracle better than anyone other company. In general, full database exports are not recommended.

Export basically allows for backup and restoration without the need to shut down the database or taken any tablespaces offline. Obviously a full database export is much slower than the export and backup of parts of that database. Setting the CONSISTENT=Y parameter for the export utility will accomplish this. Obviously the neceessity for this parameter can be avoided by shutting down the database. Using the consistent option, database transactions will be held in rollback segments until export completion. Check rollback segment size in use. On completion of export, export will read the rollback segment to fin the image of the table being exported. Note that export can not specifiy which rollback segment to use.

Export will allow for compression. Compession reorganises fragmented data segments by writing those data segments to the export dump file as single extents.`

Export can backup imcrementally since the last recorded export on a table-wide basis. This implies that exports are executed incrementally for a whole table if anything has been altered in that table since the previous export. Note that incremental exports are not executed by table rows.

Physical Backup

Physical backups are operating system level file system backups. Since placement of tablespaces offline and shutting down of the database requires DBA intervention and interactivity or scheduled scripting there is therefore no advantage to use of cold or offline backups over that of use of export and import utility logical backups.

To ensure all files covered use the following $ (Dollar) views to select details from to verify that all files are copied. In general the files required to be copied are datafiles, online redo logs, control files and it would probably be wise to copy the current archived redo logs depending on the degree of change of the database. Leaving out the archived redo log files could ultimately leave to a non-recoverable database.

  1. v$datafile
  2. v$controlfile
  3. v$logfile

Configuration files such as init.ora (initialisation parameters file) and the listener.ora (database network listener configuration - listens for clients and other database over the network).

Offline Physical Backups

Offline (cold) backups are more reliable than online (hot) backups. An offline backup implies that all tablespaces are offline and the database is shutdown. An offline backup creates a consistent image of the database since the backup is performed on the database at a specific point in time and most importantly, there is not database activity at the point of the backup.

Generally the safest way to execute a physical backup is to shutdown, copy and then restart the database once the backup is complete.

Online Physical Backups

Backup of tablespaces online implies that users are potentially accessing and altering data during the backup process. Online backups create an inconsistent image of the database if database changes are made during the backup process. The inconsistent database image can be recovered using the archived redo logs. Therefore archiving is a definite requirement for the performance on online physical backups. Effectively online backups will construct all transactions into redo logs without affecting the database backup in order to get a point in time database image for the backup. Thus those redo logs will be used by Oracle to instantiate changes made to the database during the backup. These changes will not be reflected in the database backup since these alterations were only written to the redo logs for later completion.

Online backups should be performed in periods of low activity because I/O and CPU will increase dramatically due not only to the backup of files but also due to the required increase in Oracle database activity in order to allow that online backup process to take place. Oracle does a lot more work that during normal operations when online backup occurs.

Note that online backups can not be performed on read-only tablespaces.

It is necessary to place tablespaces into what is called backup mode in order to allow backup of an Oracle database whilst that database remains online and generally available. When a tablespace is placed into backup mode a number of things apply.

  1. Placing a tablespace into backup mode will cause an entire database block to be recorded in redo logs rather than simply the transaction record when a database change is made. This means that in backup mode, the tablespace or datafiles themselves are not changed during the backup, the change per block rather than per row/transaction is recorded in the redo logs for later database update after the tablespace is placed back into normal/non-backup mode.
  2. Note that tablespaces must not ever be left in backup mode. The redo logs could get seriously big very, very quickly.
  3. The database can not be shut down whilst a tablespace is in backup mode.
  4. Only one tablespace at once can be placed into backup mode.
  5. The instance can not be shutdown whilst a tablespace is in backup mode.

Since online backups use the redo logs to record transactions during online backup (whilst a tablespace is in backup mode) then as a result the redo logs themselves are not required to be backup up. Files required for backup copies for online backup are as follows.

  1. Datafiles.
  2. Control file image.
  3. Archived redo logs.

Online backups can be scripted and consist of the following steps.

  1. For each tablespace in the database.

    1. Place tablespace in backup mode.
      ALTER TABLESPACE tablespace_name BEGIN BACKUP;
    2. Copy database datafiles for that tablespace.
    3. Take tablespace out of backup mode.
      ALTER TABLESPACE tablespace_name END BACKUP;
  2. Backup the control file. Note that the TO TRACE option will create an ASCII text version of the controlfile which can be used to regenerate the controlfile back into binary form if for any reason the binary version of the control file should become lost or unrecoverable.
    ALTER DATABASE BACKUP CONTROLFILE TO 'full-path/controlfile.ctl';
  3. Force an archive log switch manually. Performing the manual log switch will switch the current redo log file as being the active redo log file to the redo log file in the redo log file list. The enforced switch will capture all changes made during backup. It seems logical then that there should be not only enough log files for this process but that those redo log files should be of sufficient size. If a log file is filled during back will the backup force a log file switch ? Also if the log file is switched will taking the tablespace out of backup mode cause all redo logs written to during the backup to be switched from and thus their contents written to the database.
Archive Logging

Archive logging is essential to maintaining security in the provision of backups and the ability to recover from backups in the event of failure or error. Oracle can maintain multiple online redo logs. Those are switched when full. Thus when the first redo log is full then the seconds redo log will be started. This is called a log switch. When all redo logs are filled Oracle will cycle the redo logs. This means that if an Oracle database has three redo logs and the third is full, then the first redo log will be purged and its space used for subsequent requirements until it is once again full. The ARCH or archiver background process copies filled redo logs to the archive location.A filled archive destination has to be physically purged. Oracle Enterprise Manager can probaly detect this event and perform this function. Oracle will always wait for archiver process completion prior to writing to the redo logs once again.

Arhive logs can be written to a single file or upto five locations simultaneously.

The Export Utility

Type exp help=y to view the available commands for the export utility as show below.

     Format:  EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
               or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

Keyword  		Description (Default)        
USERID   		username/password            
BUFFER   		size of data buffer          
FILE     		output file (EXPDAT.DMP)     
COMPRESS 		import into one extent (Y)   
GRANTS   		export grants (Y)            
INDEXES  		export indexes (Y)           
ROWS     		export data rows (Y)         
CONSTRAINTS 		export constraints (Y)    
LOG      		log file of screen output    
FULL         		export entire file (N)
OWNER        		list of owner usernames
TABLES       		list of table names
RECORDLENGTH 		length of IO record
INCTYPE      		incremental export type
RECORD       		track incr. export (Y)
PARFILE      		parameter filename
CONSISTENT   		cross-table consistency
STATISTICS   		analyze objects (ESTIMATE)
DIRECT   		direct path (N)
FEEDBACK 		display progress every x rows (0)
POINT_IN_TIME_RECOVER   Tablespace Point-in-time Recovery (N)
RECOVERY_TABLESPACES    List of tablespace names to recover

Database Recovery

Recovery implieas restoring a database to full operation at some previous point in time.

Instance Recovery

Performed at startup by application of online redo logs in order to roll forward transactions up to the point of failure. At that point uncommitted transactions are rolled back by application of undo records recorded in rollback segments.

Media Recovery

Media recovery involves restoration by use of specialised recovery commands. Thus media recovery involves the two steps of restoring a database and then the recovery of that database.

Complete and Incomplete Recovery

Complete Recovery

Restoration and recovery to the point of failure.

Incomplete Recovery

Restoration and recovery to a point prior to the point of failure since complete recovery is not possible. Incomplete recovery falls into four categories.

  1. Time based - recovers to a specified point in time.
  2. Cancel based - recovers until CANCEL command used.
  3. Change based - recovers until specified SCN (system change number) reached in the archive logs.
  4. Log sequence recovery - Recovery Manager option which recovers until a specified log sequence number.

The Recovery Manager

The Recovery Manager is a utility used for performing backup and recovery operations. This utility is either a command line utility or a GIU tool as part of the Oracle Enterprise Manager.

The Recovery Manager stores information about backup and recovery activities in a catalogue or in the target database control file. This secondary catalogue containing database should also be backed up. The Recovery Manager executes operating system commands using server sessions and the PL/SQL interface for calls to the database. Thus backup and recovery become automated, schedulable and cross-platform capable. The Recovery Manager can utilise a form of compression or imcremental backup where only changed blocks will be written to backups or added onto the previous backups.

The Recovery Catalogue

The Recovery Catalogue maintains the backups and the automation of those backups. The Recovery Catalogue database can be backup up using any online or offline method other than itself of course.

Thus create the Recovery Catalogue in a separate database on a separate machine as shown below.

CREATE USER username IDENTIFIED BY password DEFAULT TABLESPACE tablespace_name TEMPORARY TABLESPACE tablespace_name QUOTE UNLIMITED on tablespace_name;
GRANT connect, resource, recovery_catalog_owner to username;

The Import Utility

Type imp help=y to view the available commands for the import utility as show below.

     Format:  IMP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
               or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

Keyword  Description (Default)       
USERID   		username/password           
BUFFER   		size of data buffer         
FILE     		output file (EXPDAT.DMP)    
SHOW     		just list file contents (N) 
IGNORE   		ignore create errors (N)   
GRANTS   		import grants (Y)           
INDEXES  		import indexes (Y)          
ROWS     		import data rows (Y)        
LOG      		log file of screen output
FULL         		import entire file (N)
FROMUSER     		list of owner usernames
TOUSER       		list of usernames
TABLES       		list of table names
RECORDLENGTH 		length of IO record
INCTYPE      		incremental import type
COMMIT       		commit array insert (N)
PARFILE      		parameter filename
DESTROY  		overwrite tablespace data file (N)
INDEXFILE 		write table/index info to specified file
CHARSET  		character set of export file (NLS_LANG)
POINT_IN_TIME_RECOVER	Tablespace Point-in-time Recovery (N)
SKIP_UNUSABLE_INDEXES	skip maintenance of unusable indexes (N)
ANALYZE  		execute ANALYZE statements in dump file (Y)
FEEDBACK 		display progress every x rows(0)

The Recovery Manager

The command line Recovery Manager utility is called rman80. Type rman help=y to show the help screen as shown below.

Argument     Value          Description
target       quoted-string  connect-string for target database (required)
rcvcat       quoted-string  connect-string for recovery catalog
debug        none           if specified, activate debuggin mode
cmdfile      quoted-string  name of input command file
msglog       quoted-string  name of output message log file
trace        quoted-string  name of output debugging message log file
append       none           if specified, msglog opened in append mode
Both single and double quotes (' or ") are accepted for a quoted-string.
On some operating systems, you must escape quotes from the shell.