Database Backup

So often the database will undergo situations that require a recovery for instance a fire outbreak on servers, hard disk failure, user deleting data unintentionally, hackers, etc. To avert from such situations, you will need to have an extra copy of data that you can use to recover from a database failure. This leads us to a concept called database backup.

Database backup refers to making copies of the database files. There are a number of reasons for database backup. These include facilitating database reconstruction after a failure, protecting the reputation of the firm, averting possible costs in case of a failure, conforming with the legal requirements, to increase MTTR (Mean Time To Recover – time between a database failure and recovery), to reduce MTBF (Mean Time Before Failure – time between one database failure and the next), etc.

As a DBA, expect your database to undergo situations that will lead to its failure and you’ll need to recover it so as to continue with the normal business operations. Take a look at the prestigious university getting caught in fire and all the database servers are burnt down. You can read for yourself here. This is just one of the examples that you should pay attention to as DBA. Lets learn about database backup.

Backup Terminologies

As a DBA, you can backup the entire database (whole backup) or just part of it (partial backup). Whole backup and partial backup are known as Oracle’s backup strategies. Backup types include full backup and incremental backup. Depending on whether you make your database backup when the database is open or closed, backup is further divided into backup modes i.e. consistent backup and inconsistent backup. Let us see what these terminologies mean in detail.

Whole database backup. This backup includes backing up of all data files and at least one control file. Only one of the control files needs to be backed up.

Partial database backup. With this backup, it includes some of the data files but not all. Control files are optional to be backed up.

Full database backup. A full backup includes backup of all data on every data file backed up in whole or partial backup.

Incremental backup. This backup includes only changed data blocks on every data file. Only changed data is backed up here.

Consistent backup. This backup is done when the database is closed and not available for use to users. This backup is consistent because the SCN (System Change Number – whenever the CKPT process updates the control file and data file headers, it registers SCN for every data movement done on the data file) on a control file matches the one on data file headers because no new data is done when the database is off. This backup is sometimes called offline or cold backup.

Inconsistent backup. This is backup done when the database is open and available for use to users. It is inconsistent because the SCN on control file is most likely to be out of sync with that on data file headers. Although this backup sounds as something you should avoid, it is the only option left for databases that should be available 24/7 to be backed up. It is sometimes called online or hot backup.

Recovery Components

A number of structures and events in the database directly support backup and recovery operations. These include the control file, the checkpoint process, redo log file, archived redo log file, and the flash recovery area. Most if not all of these tools were looked at in detail when we were looking at Managing Storage Structures and therefore, we are not going to take much time talking about them. If you need to understand more about them, you’re encouraged to read about them here.

Control File.

It is one of the smallest files yet one of the most critical files in the database. It contains all important information about the database. Such information include name of the database, name and location of different files (data files, redo log files, archived redo log files), most recent checkpoint information, etc. This file is needed in order to startup the database i.e. startup mount stage (You can read about Instance Management). It is regularly updated by the checkpoint process and it should always be available. Without this file, we forget about the database. With such a huge role in database operation, it goes without saying that it should always be multiplexed as well as doing a backup of this file. It is recommended to configure an autobackup for this file such that each it there is a backup, it is automatically backed up.

Checkpoint Process

This is a background process charged with updating of the control files and data file headers about checkpoint information by recording the SCN (System Change Number – a number assigned to each transaction in the database). This number is recorded in the control files and data file headers. This process controls the amount of time needed for instance recovery.

Redo Log File

This file stores all SQL statements that lead to a data change. This information is extremely relevant in case of an instance recovery. Ensuring that you have at least two members for each redo log group reduces the likelihood of data loss because the database continues to operate if one member is lost. The LGWR continuously updates this file whenever a user commits a transaction, when the redo log buffer becomes one-third full, etc. This file should be multiplexed. Read more about this file here.

Archived Redo Log File

An archived redo log file is a copy of a redo log file before it is overwritten by the new redo information. Because of redo log files are written on in a circular fashion (read about this here) by the LGWR, there is no way you can backup the redo log files. The data on the redo log files is moved to the archived redo log files by the archiver process (ARCn). The process of copying is known as archiving. When the ARCn process is enabled, the database is enabled to be in ARCHIVELOG mode and the opposite is NOARCHIVELOG mode. The archived redo log file can be used later for database recovery, updating the database, database auditing, etc. Because of its significance, it should be backed up regularly. Read more about this file here.

Flash Recovery Area

This is a central location for all recovery related files in the database. These include archived redo log files, control file copies, flashback logs, control file and sp file auto backup copies and data file copies. Whenever you carryout a backup using the RMAN tool, the backup copies are stored in this place. This place acts as a run-to for DBAs whenever they need files relevant for recovery.

These are the recovery components that should be paid attention to by DBAs. After understanding them and how critical they are, lets see how backup is performed.

Performing Backup

There are two methods of performing Oracle database backup i.e. user managed backup and use of RMAN (Recovery Manager – Oracle’s backup and recovery tool). User managed backups are manually done by the DBA. Even though used, it is not the recommended method due to some drawbacks like human errors, lack of file compression, etc. Oracle’s RMAN is the recommended method of backup and it is the one we’re going to pay attention to here.

To carry out backup using RMAN, the database should be in ARCHIVELOG mode. To check if your database is in ARCHIVELOG mode, we use the command in the snippet.

The database above is in archive log mode. To put the database in ARCHIVELOG mode, we first need to shutdown the database. Then start it up in mount mode in order to make the changes to the database and then after we open it. Here is the code snippet.

Now that the database has been put in ARCHIVELOG mode, we can start the backup. We are going to start with backing up the whole backup using the RMAN tool. To access this tool, we search the tool in installed programs. We connect to the target database and run the command backup database.

The backup in the snippet is a whole backup since it included backing up of all data files and a control file. Now lets backup just a tablespace for users.

We can also decide to backup only a specific data file. Here we specify the name of the data file we want to backup.

Like we saw among the recovery components, the control file is a very important file and we need to back it up always. We can configure this file to auto backup such that each time we carry out a backup, it is automatically backed up as well.

We can also do a backup of inititalisation parameter files. Let us backup the SP file.

We can as well carry out an incremental backup. Let us see how we can do this.

Let us finish with knowing how we can backup the database as well as archivelog.

All the backups are stored in the flash [fast] recovery area on the oraclexe folder on Windows. That is where they can be accessed.

Now you can confidently say you know a thing or two about database backup. Congratulations 🎈

Summary

1. Backup looks at making copies of the database files to facilitate reconstruction of the database after a failure.

2. Backup can be whole or partial, full or incremental, and cold or hot.

3. Recovery components include redo file, archived redo log file, flash recovery, control file and checkpoint.

4. We can perform various backups on the database using the RMAN tool.