Oracle Database Backup Information

Contents

Options
Backup Modes:
             Offline or Cold Backup
             Online or Hot Backup

Which files to backup
Archive Log Mode
NoArchivelog
Implement a Backup strategy
Guidelines for scheduling Routine backups
Implement Non-routine Backups
Performance
Procedures to enable Archiving Log Mode

Options

A quick review of Oracle backup and recovery options.
 

Backup Modes	Backup Types
	Logical	Physical	Offline	Online
Export	Y			Y
Cold		Y	Y
Hot		Y		Y

Online backups are performed while the database is still running, off-line backups are performed while the database is down. Logical backups can also be used for migrating between database versions and Oracle platforms. In Oracle these are performed using the IMPORT/EXPORT utilities.

Backup Modes

a) Offline or Cold Backup

Copying of the datafiles, control file and online redo log files must be done by using an operating system copy utility. This is a considered as a complete backup of the database. Any changes made after this backup will be unrecoverable if the database is running in NOARCHIVELOG mode. All transactions are recorded in online redo log files whether archiving or not. When redo logs are archived (ARCHIVELOG mode), ORACLE allows you to apply these transactions after restoring files that were damaged (assuming an active Redo log file was not among the files damaged). Whenever the schema of the database is changed i.e., a new datafile is added or a file is renamed or a tablespace is created or dropped, shutdown the database and at least make a copy of the control file and the newly added datafile. A complete backup of the database is preferred.

Procedure
1. Backup the control file to trace file.
2. Shut down application (where applicable).
3. Shut Oracle down cleanly. If a SHUTDOWN ABORT has been issued, the instance must be restarted and closed again.
4. Copy all relevant database files to a staging area. These comprise:

• Data files
• All Redo Log files, including all mirror group members
• Database control file
  
• All associated parameter files ( database initialization parameter file, init.ora, )
  
• The database password file

5. Restart database.
6. Copy files created to offline storage media (i.e. tape). Ideally these copies will be left on disk to minimise the time taken to recover the database.
 

b) Online or Hot Backup

At sites where database must operate 24 hours per day and when it is not feasible to take offline backups, then an alternative is provided by ORACLE to perform physical backups while the database remains available for both reading and updating. For this kind of backup the database must be in ARCHIVELOG mode. Only data files and current control file need to be backed up. Unlike offline backups, the unit of a online backup is tablespace, and any or all tablespaces can backed up whenever needed. Different datafiles can be backed up at different times. This process should be fully automated if possible. You MUST NEVER BACKUP THE ONLINE REDO LOG FILES ON HOT BACKUPS!!!

 Procedure:

• Run script to backup all relevant database files, comprising :-
  • Data files are grouped into Tablespaces, and before any file is backed up, its tablespace must be marked by running the command:
    ALTER TABLESPACE xxxx BEGIN BACKUP;
  • Copy the datafiles that belongs to that tablespace.
  • End of backup is marked by the command:
    ALTER TABLESPACE xxxx END BACKUP;
  • Database control file (physical backup, also copied to trace file);
    
  • All associated parameter files ( database initialization parameter file, init.ora);
    
  • The database password file (if used);
    
  • Any required application data files.
  • Perform a 'alter system archive log current;'
  • Archive Redo Log Files (but NOT the Online Redo Log Files, you should query the v$archived_log view )
• Copy files created before to offline storage media (i.e tape).

Not all data files need be backed up in this operation, for example read-only tablespaces only need to be backed-up once after the tablespace is made read-only. All of the above processing can be performed while the database is open. Ideally it should occur during a period of low transaction activity.

What happens between BEGIN BACKUP and END BACKUP?

Once the ALTER TABLESPACE ts_name BEGIN BACKUP is issued, two things happen:

(1) Extra information is recorded in the redo logs. Because of this, is it important that on-line backups are done as quickly as possible, and also, if possible, during a quieter period when there is less update activity on the database.

(2) The status in the datafile header is changed to indicate that the datafile is being backed up. Oracle stops recording the occurrence of checkpoints in the header of the database files. This means that when a database file is restored, it will have knowledge of the most recent checkpoint that occurred BEFORE the backup, not any that occurred during the backup. This way, the system will ask for the appropriate set of redo log files to apply should recovery be needed. Since vital information needed for recovery is recorded in the Redo logs, these REDO LOGS are considered as part of the backup. Hence, while backing up the database in this way the database must be in ARCHIVELOG mode. Status in the datafile header is not reset until END BACKUP is issued.

On END BACKUP, the system ceases writing this extra information to the redo-logs, and recommences noting the occurrence of the checkpoints in each file of the database. The checkpoint in the datafile header is changed during the next log switch after END BACKUP is issued. The above information will allow the tablespace to be recovered as if the database had been offline when the backup took place.

Which files to backup

All the files belonging the database are important. Also backup the control file and datafile immediately after adding it to a tablespace or after creating tablespace if archiving is enabled. If media failure damages a datafile that has not been backed up, recovering its tablespace is not possible. After backing up the newly added datafile, include it in the regular datafile backup rotation.

Identifying the files to backup: Use the 'v$' (dynamic performance tables) to find the file names:

Archive Log Mode

Archiving is the process of writing data from a filled online redo log file to an archive log file.
An Oracle database can operate in either NOARCHIVELOG or ARCHIVELOG mode. Oracle writes to the current online redo log file until it fills up, it then begins writing to the next one. When the last online redo log file is full, Oracle cycles back to the first one. In ARCHIVELOG mode each log file is saved before being overwritten. The archive process will backup each redo log file as soon as it is filled. The ARCHIVELOG function enables a whole range of possible backup options:

• provides protection against media and instance failure
• enables 24-hour availability and is mandatory for hot backups
• allows point-in-time recovery
  
• recovery possible without losing any data
  
• allow the database to stay up, preserving the data in the SGA
  
• Archiving can be done manually or automatically.

Points to note:

• Archived redo log files can be used to help recover an Oracle database using any of the backup modes listed above, assuming that all redo log files that have been used since the backup have been archived
• After each redo log is filled the redo log is archived to a backup device
  
• A failed transaction is still a transaction; redo log files keep track of rollback segment extents, so rolled back inserts or deletes affect it just like completed transactions
  
• Repetitive failed load attempts can create massive numbers of redo log files
  
• Until an online redo file is archived it cannot be reused
  
• When it gets stuck, i.e. none of the redo log files are ready for reuse the database comes to a halt
  
• Administration is more complex as the DBA has to keep track of multiple archive log files

Recommendations:

• Use ARCHIVELOG MODE. It is mandatory for full database recovery without loss of data. It is required to support Hot Backups and to recover transactions lost since the last backup.
  
• Size your log files carefully.
  Too small and the database will be archiving too frequently. This means a performance overhead due to frequent check-pointing. It will result in the creation of a large number of small archive log files making file management and recovery more complex. Too large and the archive process will take too long and the archive log files may end up spanning multiple tape volumes.
• Archive to disk and then back up to tape overnight. Archiving directly to tape is complicated and prone to problems.
  
• When recovering. Try to ensure that all of the archive log files that will be needed are on disk. This will greatly speed up the process.

NOARCHIVELOG:

• Provides instance recovery only
• Online redo log files are overwritten when needed Only the most recent changes to the database (or those that are in the current online redo log files) are available at any give time
  
• Recovery will involve the loss of transactions processed since the last backup or export was done
  
• Hot Backups not supported. The database will be unavailable during backups.

Implement a Backup strategy

Its important to identify exactly what recovery facilities are required by the site This will determine the frequency and type of backup that will have to be taken. The answers to several questions must be established before attempting to implement any backup procedures.

• How much data can you afford to lose?
• How often and for how long, can the database be off-line to perform backups?
• Should recovery be needed, how quickly do you need to recover your data?
  
• Do you need the capability to reverse changes made to the database?

Implementing a backup strategy requires consideration of the following.

• How to minimize vulnerabilities during backup and recovery?
• How can physical and logical errors be monitored and checked for?

How much data can you afford to lose?
If you can afford to lose up to a day's worth of data, then running in NOARCHIVELOG mode and taking nightly off-line backups is sufficient. Any changes applied to the database after the most recent backup will be unrecoverable.
If you cannot afford to lose transactions performed since the last backup then you must run in ARCHIVELOG mode. The archived redo log files contain a record of all transactions applied since the last backup. These transactions can be reapplied after restoring from backup any datafiles that were damaged.

How often and for how long, can the database be off-line to perform backups?
Many sites operate 24 hours per day. Even at sites where the database can be shutdown for maintenance, a complete backup may take longer than the window available. When it is not feasible to take off-line backups often enough to provide the protection from data loss the site requires, then online backups are the only option.

Should recovery be needed, how quickly do you need to recover your data?
The cost of downtime (or database outage) may be a key factor in configuring the backup strategy. Recovery time using:

Logical backups (export) take the longest

• Recovery using an Export file requires initializing the database and rebuilding the system tablespace prior to importing the data.
• If the backup contains incremental export files, multiple export files may have to be read in order to reconstruct the database. The more recent your full backup, the faster the recovery will be.

Physical backups provide the fastest recovery times.

• Physical backups depends on the type of backup storage device. Maintaining as much of the backup as possible online will help minimise downtime.
• Cold backups take as long as it takes to restore all the database files
  
• Hot backups depends on the age of the backed up datafile(s) that have to be restored and hence the number of archived redo log files that have to be applied. The number of redo log files will also be determined by their size.(NB. 100Mb redo log files can be applied in 1/2 hour on an HP98XX) If all archived redo log files can be located in the directory specified by the ARCHIVE_LOG_DEST parameter in the init.ora file, then automatic recovery will not require to prompt for redo log file names.

Do you need the capability to reverse changes made to the database?
Do you wish to be able to protect against inadvertent changes made to both the content and structure of the database. For example, bringing back a dropped table, or removing a datafile added to the wrong tablespace.

How to minimize vulnerabilities during backup and recovery?

• backup and recovery procedures must be as simple as possible to reduce the possibility of error.
• Any scripts used to implement a backup strategy must test for any read or write failures, must rollback on error and report errors to the console, via a log file and through mail.
  
• The scripts must be able to be interrupted and restarted at any time without causing any 'holes' in the backup.
  
• A desirable feature is the capability to write a header and label onto each tape used for backups as well as producing a file listing of the tape's contents and a printed tape label. The tape label should be attempted to be read prior to any attempt to write to the tape.
  
• If the database structure changes it is less desirable to have a hard coded list of datafiles used by the backup. Ideally the backup script should query the database to find out which datafiles are currently in use. This option may be compromised if only certain datafiles ot tablespaces are backed up on given days.
  
• Since loss of an archived redo log file disables recovery from that point on, maintaining multiple copies of archived redo log files will allow recovery from multiple media failures.
  
• The archived redo log files for a database ought not reside on the same physical disk device as any database file or online redo log file.
  
• If database files are being backed up to disk, a database file residing on the same physical device as its backup copy is not adequately backed up.

How can physical and logical errors be monitored and checked for?

• Routinely check the database can be restarted
• There are certain parameters that are only checked on startup; errors with rollback segments in particular may only show up during startup operations.
  
• Perform a full export periodically A full system export picks up information that user exports do not; this includes third-party indexes and grants. Export checks that database files are logically readable. [NB: This does not imply that they are logically importable; corrupt records may be exported into the dump file, preventing import.]
  
• The export dump file can be used to retrieve particular tables/users if needed. Its worth running scripts to map the tablespaces to owners, and owners to tablespaces immediately following the export. In the event of a tablespace loss, you would then be able to quickly determine what users/systems will be affected.

Guidelines for scheduling Routine backups

• Backup procedures should be automated and scheduled. Do not rely on ad-hoc backups.
• Have a valid backup for all tablespaces. Do not forget to backup the system tablespace.
  
• Read only tablespaces need to be backed up once
  
• Backup frequency should be determined by the update activity of each tablespace. More frequent backups of heavily used tablespaces will reduce recovery time in the roll forward phase.
  
• Keep archive logs long enough to recover all changes made to the database since the last valid backup.
  
• Keep two copies of archive logs. If only one copy is available then loss will prevent full recovery.
  
• Send backup files off site to prepare for disaster recovery.
  
• If your production schedule allows, take a regular FULL COLD database backup. This is the easiest state to recover to
  
• Test your backup procedures to make sure that they work. Know how long full database recovery and recovery by tablespace takes. Test your disaster recovery strategy.
  
• Make sure all DBAs responsible for recovery understand the process and are involved in the test exercise.

Implement Non-routine Backups

Non routine backups are required in the following circumstances:

• Database Upgrades
• Database Schema Changes
• Whenever the database structure is changed by adding, renaming or dropping a tablespace, datafile, or log file, a control file backup should be performed. The ALTER DATABASE BACKUP CONTROLFILE TO <filename> command can be used to take an online backup of a control file.
  
• When a datafile is added to the database, a backup of the new file be taken immediately. The new file should also be added to any automated backup procedures.
  
• The database can maintain multiple copies of the control file, and so a copy of the control file should be placed on several different disk devices. A control file can be added to the database by shutting down the database, copying the control file, altering the INIT.ORA parameter CONTROL_FILES, and restarting.

Performance

Backup and recovery performance can be improved using the following guidelines:

• Backing up database files to disk can speed recovery, since the file need not be restored from tape. Also, backing up to disk often allows backup procedures that run in a shorter amount of time.
• The procedure of rolling forward a database or database file from a backup can in many cases be simplified and made faster by keeping on disk all archived redo log files needed to roll forward the least recently backed up database file of a database. For many systems, much of the time necessary for recovery is spent loading archived redo log files from tape.
  
• At times maintenance procedures might be performed that would generate large amounts of archived redo logs. Such procedures might benefit from having archiving disabled during their duration.
  
• Using hot backups, only one tablespace at a time should be in backup mode.
  
• Hot backups should be performed during low user activity. When in backup state, a tablespace's activity is still written to the archive logs. However, it's written block-by-block rather than byte-by-byte. So changing one record in a tablespace that's being backed up will result in that record's entire block being written to the archive area.

Implementing a Backup Strategy Using ARCHIVELOG

• The number of online redo log files provide a window of time should the archive destination become full.
• When you start archiving, archived logs will be written every time the redo operation is about to overwrite a previously written log file. It will write it to the directory indicated by the LOG_ARCHIVE_DEST parameter in your init.ora file. They will all be the same size (in V6; V7 can have variably sized archive logs) as your redo logs. They will increase in number until they run out of space on their destination device. At that point the database will freeze until you clear more space for them in the LOG_ARCHIVE_DEST location. SO, have a second location ready to receive them.
  
• Do each tablespace one at a time. That is, rather than setting them all offline, then backing them up, then setting them back online, do them each separately. You don't want to risk having a system crash while the entire database is in begin backup state; recovery is a mess. Minimize your window of vulnerability by having only one tablespace in backup state at any one time.
  
• Before you backup the control file, force an archive log switch (use 'alter system archive log current;' instead of ‘alter system switch logfile;’ ). This will update the header information in the control file.

 
Procedures to enable Archiving Log Mode

Enabling automatic archiving (init.ora) method, Connect to sqlplus as SYS and perform the following:
create pfile form spfile;
Edit the init.ora File and add the following lines:
*.Log_archive_start    = True
*.Log_archive_format = "ARCH_%t_%s_%r.dbf"
l*.og_archive_dest_1 = "location=/u01/oradata/TICPBT09/ARCH MANDATORY"
*.log_archive_dest_state_1 = enable
*.log_archive_dest_2 = "service=TICPBP02 OPTIONAL reopen=60"
*.log_archive_dest_state_2 = enable
*.log_archive_min_succeed_dest = 1

For 10g
The LOG_ARCHIVE_START init.ora parameter has been rendered obsolete. In Oracle 10g, if the LOG_ARCHIVE_DEST is not set, archiving will be directed to the flashback recovery area automatically when the database is switch to ARCHIVELOG mode.
*.log_archive_format='ARCH_%t_%s_%r.dbf'
*.log_archive_dest_1='LOCATION=/u02/oradata/ARCH'
*.log_archive_dest_state_1 = enable
*.log_archive_dest_2 = "service=TICPBP02 OPTIONAL reopen=60"
*.log_archive_dest_state_2 = enable
*.log_archive_min_succeed_dest = 1

Shutdown the database:
shutdown immediate;

• Start up a new instance and mount, but do not open the database.
    create spfile from pfile;
    startup mount

•  Switch the database's archiving mode and open it
  alter database archivelog;
  alter database open;

• Verify your database is now in archivelog mode.
  archive log list;


• Archive all your redo logs at this point.
  archive log all;
  or
  alter system switch logfile;
  or
  alter system archive log current;
  
  
• Ensure these newly created Archive log files are added to the backup process