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.
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.
Various factors about your business can help to determine your backup strategy.
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.
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 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.
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 (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.
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.
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.
Online backups can be scripted and consist of the following steps.
ALTER TABLESPACE tablespace_name BEGIN BACKUP;
ALTER TABLESPACE tablespace_name END BACKUP;
ALTER DATABASE BACKUP CONTROLFILE TO 'full-path/controlfile.ctl'; ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
ALTER SYSTEM ARCHIVE LOG CURRENT;
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.
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)
Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
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
Recovery implieas restoring a database to full operation at some previous point in time.
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 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.
Restoration and recovery to the point of failure.
Restoration and recovery to a point prior to the point of failure since complete recovery is not possible. Incomplete recovery falls into four categories.
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 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 TABLESPACE tablespace_name DATAFILE 'filename' DEFAULT STORAGE (PCTINCREASE 0); 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;
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)
Example: IMP SCOTT/TIGER IGNORE=Y TABLES=(EMP,DEPT) FULL=N
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 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.