Oracle Backup: An introduction. When performing an Oracle backup, you create a representative copy of the present original data. If/when the original data is lost, the DBA can use the backup to reconstruct lost information. This database copy includes important parts of the database, d atabase, such as the control file, archive logs and datafiles-structures. In the event of a media failure, the database backup is the key to successfully recovering data. A few common questions which are related to database backup in general are: • • •
The frequency of the backup Choosing a strategy for the backup Type of backup
Frequent and regular whole database or tablespace backups are essential for any recovery scheme. The frequency of backups should be based on the rate or frequency of changes to database data such as insertions, updates, and deletions of rows in existing tables, and addition of new tables. If a database's data is changed at a high rate, the database backup frequency should be proportionally high. When the Oracle database is created, the DBA has to plan beforehand for the protection of the database against potential failures. There are two modes of handling an Oracle backup according to which the DBA can choose an appropriate strategy: NOARCHIVELOG mode : If it is acceptable to lose a limited amount of data if there is a disk failure, you can operate the database in NOARCHIVELOG mode and avoid the extra work required to archive filled online redo log files. ARCHIVELOG mode : If it is not acceptable to lose any data, the database must be operated in ARCHIVELOG mode, ideally with a multiplexed online redo log. If it is needed to recover to a past point in time to correct a major operational or programmatic change to the database, be sure to run in ARCHIVELOG mode and perform control file backups whenever making structural changes.
Recovery to a past point in time is facilitated by having a backup control file that reflects the database structure at the desired point-in-time. If so, do no t operate the database in NOARCHIVELOG mode because the required whole database backups, taken while the database is shutdown, cannot be made frequently. Therefore, high-availability databases always operate in ARCHIVELOG mode to take advantage of open datafile backups.
Backup Strategies in NOARCHIVELOG Mode
If a database is operated in NOARCHIVELOG mode, filled groups of online redo log files are not archived. Therefore, the only protection against a disk failure is the most recent whole backup of the database. Whenever you alter the physical structure of a database da tabase operating in NOARCHIVELOG mode, immediately take a consistent whole database backup. A whole database backup fully reflects the new structure of the database. Backup Strategies in ARCHIVELOG Mode
If a database is operating in ARCHIVELOG mode, filled groups of online redo log files are being archived. Therefore, the archived redo log coupled with the online redo log and datafile backups can protect the database from a disk failure, providing for complete recovery from a disk failure to the instant that the failure occurred (or, to the desired past point-in-time). Following are common backup strategies for a database operating in ARCHIVELOG mode: •
•
•
When the database is initially created, perform a whole database, closed backup of the entire database. This initial whole database backup is the foundation of backups because it provides backups of all datafiles and the control file of the associated database. Subsequent whole database backups are not required, and if a database must remain open at all times, whole database, closed backups are not feasible. Instead, the DBA can take open database or tablespace backups to keep database backups up-to-date. Every time a structural change is made to the database, take a control file backup. If operating in ARCHIVELOG mode and the database is open, use either Recovery Manager or the ALTER DATABASE command c ommand with the BACKUP CONTROLFILE option.
The following methods are valid for backing-up an Oracle database: •
•
•
Export/Import - Oracle exports are "logical" database backups (not physical) as they extract data and logical definitions from the database into a file. Other Oracle backup strategies normally back-up the physical data files. In exports one can selectively re-import tables but cannot roll-forward from a restored export file. To completely restore a database from an export file one practically needs to recreate the entire database. Full exports include more information about the database in the export file as compared to user level exports.
1. Shut down the the database database from sqlplus sqlplus or server manager. manager.
2. Backup all files files to secondary secondary storage storage (eg. tapes). tapes). Ensure Ensure that you you backup all data data files, all all control files and all log files. 3. When When complet completed, ed, resta restart rt your your datab database ase..
The Oracle Export utility creates an Oracle backup by writing data from an Oracle database to operating system files in an Oracle database format. Export files store information about schema objects created for a database. Database exports are not a substitute for a whole Oracle backup and don't provide the same recovery advantages that the built-in functionality of Oracle offers. •
•
•
•
•
•
•
Cold or Off-line Oracle backup - Shut the database down and backup up ALL data, log, and control files. A cold backup is a backup performed while the database is off-line and unavailable to its users Hot or On-line Oracle Backup - A hot backup is a backup performed while the database is online and available for read/write. If the database is available and in ARCHIVELOG mode, set the tablespaces into backup mode and backup their files. Also remember to backup the control files and archived redo log files. Except for Oracle exports, one can only do on-line Oracle backup when running in ARCHIVELOG mode. RMAN Backup - While the database is off-line or on-line, use the "rman" utility to backup the database. The Recovery Manager utility manages the Oracle backup, restore and recovery operations of Oracle databases. Recovery Manager uses information about the database to automatically locate, then back up, restore and recover datafiles, control files and archived redo logs. ecovery Manager gets the required information from either the databases' control file, or via a central repository of information called a recovery catalog, which is maintained by Recovery Manager.
•
•
You can perform Recovery Manager backups using Oracle Enterprise Manager. Oracle Enterprise Manager-Backup Manager is a GUI interface to Recovery Manager that enables you to perform backup and recovery via a point-and-click method. Recovery Manager is a command line interface (CLI) that directs an Oracle server process to back up, restore or recover the database it is connected to. The Recovery Manager program issues commands to an Oracle server process. The Oracle server process reads the datafile, control file or archived redo log being backed up, or writes the datafile, control file or archived redo log being restored or recovered.
Do the following queries to get a list of all files that need to be backed ba cked up: select member from sys.v_$datafile; select member from sys.v_$logfile; select name from sys.v_$controlfile; Sometimes Oracle takes forever to shutdown with the "immediate" option. As a workaround to this problem, shutdown using these commands: alter system checkpoint; shutdown abort startup restrict shutdown immediate Each tablespace that needs to be backed-up must be switched into backup mode before copying the files out to secondary storage. This can be done as shown below: ALTER TABLESPACE xyz BEGIN BACKUP; ! cp xyfFile1 /backupDir/ ALTER TABLESPACE xyz END BACKUP;
Recovery Manager command: run { allocate channel t1 type `SBT_TAPE'; backup format `df_%s_%t' (datafile 10); } When Recovery Manager executes the above command, it sends the Oracle backup request to the Oracle server performing the backup. The Oracle server process identifies the output channel as the type `SBT_TAPE', and requests the Media Management Library to load a tape and write the output specified. Return from Oracle Backup to Oracle DBA
