Incomplete / Complete recovery using RMAN cold backup

Offline backups (also known as Cold or consistent backups) are taken when the database is in a consistent state, i.e. the database has been shut down with the SHUTDOWN NORMAL, SHUTDOWN IMMEDIATE, or SHUTDOWN TRANSACTIONAL commands. As part of a consistent shutdown, redo has been applied to the data files and the System Change number (SCN) in the data file

Offline backups (also known as Cold or consistent backups) are taken when the database is in a consistent state, i.e. the database has been shut down with the SHUTDOWN NORMAL, SHUTDOWN IMMEDIATE, or SHUTDOWN TRANSACTIONAL commands. As part of a consistent shutdown, redo has been applied to the data files and the System Change number (SCN) in the data file headers matches the SCN in control files at the time of backup. Hence, after restoring consistent backups, no media recovery is required and the state of the database is the same as it was at the time of backup. In this case, any transactions made after the backup are lost.

In this article I will demonstrate that offline backup of a database in archivelog mode can be employed to perform:

  • Point in time recovery
  • Complete recovery

…of the database beyond the time of backup by applying appropriate archive logs. For this demonstration I will be using the HR sample schema.

Overview

  • Verify that the database is in archivelog mode
  • Create test table HR.EMP which is copy of HR.EMPLOYEES table. Note down initial total SALARY.
  • Take a cold backup of the database and control file using RMAN
  • Update SALARY = 0 in HR.EMP and note down current SCN#
  • Switch the log so that a log file with sequence# where total SALARY = 0 in HR.EMP is archived
  • Update SALARY = 1000 in HR.EMP and note down current SCN#
  • Switch the log so that a log file with sequence# containing total SALARY= 107000 in HR.EMP is archived
  • Simulate loss of the database and restart database – Database reaches nomount stage as control file is missing
  • Restore the control file from cold backup
  • Mount the database and restore data files from cold backup
  • Perform incomplete recovery until SCN# when total SALARY in HR.EMP is 0
  • Perform complete recovery by applying all of the available archived logs to the database so that total SALARY in HR.EMP is at its final value of 107000

Demonstration

  • Verify that the database is in archivelog mode:

  • Find out names of the control file, online redo log files and data files:

  • Find out the current SCN and status of redo log groups:

  • Create test table HR.EMP which is copy of HR.EMPLOYEES table. Note down initial total SALARY.

  • Take a cold backup of the database and control file using RMAN:

  • Open the database and note down the currently-active redo log group / sequence#:

  • Update SALARY = 0 in HR.EMP and note down the current SCN#:

  • Switch the log so that log file with sequence# 4 having total SALARY = 0 is archived:

  • Note down the currently active redo log group / sequence#:

  • Update SALARY = 1000 in HR.EMP and note down current SCN#:

  • Switch the log so that the log file with sequence# 5 containing total SALARY= 107000 is archived:

  • Simulate loss of the database and restart the database – Database reaches the nomount stage as its control file is missing:

  • Restore the control file from cold backup:

  • Mount the database and restore data files:

Now the data in the database is as it was at the time when the cold backup was taken, and the total SALARY in HR.EMP was at its initial value of 691416. Since the cold backup has been restored, no media recovery is needed and the database can be opened. But if we do so, we will lose the transactions made after the backup was taken. Since the database is in archivelog mode, we can roll the database forward to the time after the backup by applying the archived logs.

  • Perform incomplete recovery till SCN# = 818237 when total SALARY in HR.EMP was 0. Note that archived log for sequence# 4 which contained the above change of setting salary to 0 has been applied.

Thus we have successfully performed point in time recovery by applying relevant archived logs to the datafiles restored from cold backup.

Now we will perform complete recovery by applying all of the available archived logs to the database and bringing it to the point when total SALARY in HR.EMP was finally set to 107000. It can be seen that only one archived log for sequence# 5 is available and it has been applied.

  • Let us cancel the recovery, since the archived log for sequence 6 is not available:

  • Open the database and verify that it has been recovered completely, i.e. that SALARY in HR.EMP is at its final value of 107000:

Thus we have successfully performed complete recovery of the database by applying all the available archived logs to the datafiles restored from cold backup.

Summary:

  • Offline backups, also known as Cold or consistent backups, are taken when the database is in a consistent state.
  • After restoring consistent backups, the database can be opened without performing media recovery is required and the state of the database is as it was at the time of backup. But in this case, any transactions made after the backup are lost.
  • Offline backup of a database in archivelog mode can be employed to perform:
    • Point in time recovery
    • Complete recovery

…of the database beyond the time of backup by applying appropriate archive logs.