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, 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:
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 not 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 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:
The following methods are valid for backing-up an Oracle database:
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.
Do the following queries to get a list of all files that need to be backed 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;
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;
Recovery Manager command:
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.
Free eBookSubscribe to my newsletter and get my ebook on Entity Relationship Modeling Principles as a free gift:
What visitors say...
"I just stumbled accross your site looking for some normalization theory and I have to say it is fantastic.Read more Testimonials
I have been in the database field for 10+ years and I have never before come across such a useful site. Thank you for taking the time to put this site together."
Theory & Practice
SQL Server DBA
Install SQL Server
Database Normalization eBook:
Copyright © www.databasedesign-resource.com / Alf A. Pedersen
All rights reserved.
All information contained on this website is for informational purposes only.
Disclaimer: www.databasedesign-resource.com does not warrant any company, product, service or any content contained herein.
Return to top
The name Oracle is a trademark of Oracle Corporation.