Pros and cons of cold backup and hot backup

Cold backup

Cold backup occurs when the database has been shut down normally, and a complete database will be provided to us when it is shut down normally. It’s a term for copying critical files to another location.

Here’re the key advantages of cold backup.

1. A very fast backup method with only file copying needed.

2. Easy to archive.

3. Easy to restore to a certain point in time by simply copying files back.

4. It can be combined with the archive functionality to realize the best database recovery.

5. Ensure high database security with low maintenance cost.

But cold backup also has the following shortcomings.

1. When cold backup is deployed alone, it can only perform recovery based on a single time point.

2. When cold backup is processing, database can do nothing but backup. In other words, users have to temporarily disable database.

3. If there’s no enough disk space, data can only be copied to other external storage devices such as tapes with very slow speed.

4. Data cannot be restored by table or user.

For the best practice, information should be backed up to disk, then the database should be re-started for users to operate, and the backup information should be copied to tape, which means while copying, the database is also running.

It is worth noting that the cold backup must be performed when the database is closed. When the database is open, backup of database file system is invalid.

Hot backup

Hot backup is a method of backing up the database in archivelog mode when the database is running. For example, if you have a cold backup file from last night and a hot backup file from today, you’re able to use both types of backup data to restore more information when a problem occurs. The process of hot backup not only requires database to be in Archivelog mode, but also a lot of file space. Once the database is running in the archivelog mode, hot backup can get started.

Here are some advantages of using alter database backup controlfile command to backup control files through hot backup.

1. table space or database file-level backup can be achieved with faster backup speed.

2. Database can still be used during backup process.

3. Second-level recovery to a certain time point.

4. Capable to restore almost all database entities.

5. Fast recovery with database still running in most cases.

The shortcomings of hot backup are:

1. 0 fault-tolerant capacity, any tiny error can cause serious consequences.

2. If a hot backup fails, existing backed-up data cannot be used for restore-point-level recovery.

3. The maintenance cost of hot backup is rather high, which requires operators to be extremely careful during deployment.