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:
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     2
Next log sequence to archive   4
  • Find out names of the control file, online redo log files and data files:
SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/test/control01.ctl

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/test/system01.dbf
/u01/app/oracle/oradata/test/sysaux01.dbf
/u01/app/oracle/oradata/test/undotbs01.dbf
/u01/app/oracle/oradata/test/users01.dbf
/u01/app/oracle/oradata/test/example01.dbf

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/test/redo03.log
/u01/app/oracle/oradata/test/redo02.log
/u01/app/oracle/oradata/test/redo01.log
  • Find out the current SCN and status of redo log groups:
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     817881

SQL> select group#, sequence#, status from v$log;

    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
         1          4 CURRENT
         2          2 INACTIVE
         3          3 INACTIVE
  • Create test table HR.EMP which is copy of HR.EMPLOYEES table. Note down initial total SALARY.
SQL> drop table hr.emp purge;        
     create table hr.emp as select * from hr.employees;
     select sum(salary) from hr.emp;

SUM(SALARY)
-----------
     691416
  • Take a cold backup of the database and control file using RMAN:
SQL> shu immediate;
     startup mount;

[oracle@node1 orcl]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jun 23 14:36:31 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST (DBID=2213428959, not open)

RMAN> backup database;

Starting backup at 23-JUN-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/test/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/test/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/test/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/test/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/test/users01.dbf
channel ORA_DISK_1: starting piece 1 at 23-JUN-16
channel ORA_DISK_1: finished piece 1 at 23-JUN-16
piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2016_06_23/o1_mf_nnndf_TAG20160623T143738_cpq9pv2z_.bkp tag=TAG20160623T143738 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:11
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 23-JUN-16
channel ORA_DISK_1: finished piece 1 at 23-JUN-16
piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2016_06_23/o1_mf_ncsnf_TAG20160623T143738_cpq9s3gw_.bkp tag=TAG20160623T143738 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 23-JUN-16

RMAN> backup current controlfile;

Starting backup at 23-JUN-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 23-JUN-16
channel ORA_DISK_1: finished piece 1 at 23-JUN-16
piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2016_06_23/o1_mf_ncnnf_TAG20160623T144001_cpq9vb51_.bkp tag=TAG20160623T144001 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 23-JUN-16
  • Open the database and note down the currently-active redo log group / sequence#:
SQL> alter database open;

SQL> select group#, sequence#, status from v$log;

    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
         1          4 CURRENT
         2          2 INACTIVE
         3          3 INACTIVE
  • Update SALARY = 0 in HR.EMP and note down the current SCN#:
SQL>update hr.emp set salary = 0;
    commit;
    select sum(salary) from hr.emp;

SUM(SALARY)
-----------
          0

SQL>select current_scn from v$database;

CURRENT_SCN
-----------
     818237
  • Switch the log so that log file with sequence# 4 having total SALARY = 0 is archived:
SQL> alter system switch logfile; 
     select name, FIRST_CHANGE#, SEQUENCE# from v$archived_log;

NAME
--------------------------------------------------------------------------------
FIRST_CHANGE#  SEQUENCE#
------------- ----------
/u01/app/oracle/flash_recovery_area/TEST/archivelog/2016_06_23/o1_mf_1_3_cpq9fqx
n_.arc
       792758          3

/u01/app/oracle/flash_recovery_area/TEST/archivelog/2016_06_23/o1_mf_1_4_cpqb0jm
y_.arc
       817738          4
  • Note down the currently active redo log group / sequence#:
SQL> select group#, sequence#, status from v$log;

    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
         1          4 ACTIVE
         2          5 CURRENT
         3          3 INACTIVE
  • Update SALARY = 1000 in HR.EMP and note down current SCN#:
SQL>update hr.emp set salary = 1000;
    commit;
    select sum(salary) from hr.emp;

SUM(SALARY)
-----------
     107000

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     818267
  • Switch the log so that the log file with sequence# 5 containing total SALARY= 107000 is archived:
SQL> alter system switch logfile;
     select name, FIRST_CHANGE#, SEQUENCE# from v$archived_log;

NAME
--------------------------------------------------------------------------------
FIRST_CHANGE#  SEQUENCE#
------------- ----------
/u01/app/oracle/flash_recovery_area/TEST/archivelog/2016_06_23/o1_mf_1_3_cpq9fqx
n_.arc
       792758          3

/u01/app/oracle/flash_recovery_area/TEST/archivelog/2016_06_23/o1_mf_1_4_cpqb0jm
y_.arc
       817738          4

/u01/app/oracle/flash_recovery_area/TEST/archivelog/2016_06_23/o1_mf_1_5_cpqb2jv
3_.arc
       818245          5
  • Simulate loss of the database and restart the database – Database reaches the nomount stage as its control file is missing:
[oracle@node1 ~]$ mkdir -p /u01/app/oracle/oradata/test_107000
                  mv /u01/app/oracle/oradata/test/* /u01/app/oracle/oradata/test_107000/

SQL> alter tablespace example offline;

     alter tablespace example online;
alter tablespace example online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/app/oracle/oradata/test/example01.dbf'

SQL> shu abort;
     startup
ORACLE instance started.

Total System Global Area  385003520 bytes
Fixed Size                  1336708 bytes
Variable Size             125831804 bytes
Database Buffers          251658240 bytes
Redo Buffers                6176768 bytes
ORA-00205: error in identifying control file, check alert log for more info
  • Restore the control file from cold backup:
Recovery Manager complete.
[oracle@node1 orcl]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jun 23 14:45:14 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST (not mounted)


RMAN> restore controlfile from '/u01/app/oracle/flash_recovery_area/TEST/backupset/2016_06_23/o1_mf_ncnnf_TAG20160623T144001_cpq9vb51_.bkp';

Starting restore at 23-JUN-16
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/test/control01.ctl
Finished restore at 23-JUN-16
  • Mount the database and restore data files:
SQL> alter database mount;

RMAN> restore database;

Starting restore at 23-JUN-16
released channel: ORA_DISK_1
Starting implicit crosscheck backup at 23-JUN-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
Crosschecked 2 objects
Finished implicit crosscheck backup at 23-JUN-16

Starting implicit crosscheck copy at 23-JUN-16
using channel ORA_DISK_1
Finished implicit crosscheck copy at 23-JUN-16

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/TEST/archivelog/2016_06_23/o1_mf_1_5_cpqb2jv3_.arc
File Name: /u01/app/oracle/flash_recovery_area/TEST/archivelog/2016_06_23/o1_mf_1_4_cpqb0jmy_.arc
File Name: /u01/app/oracle/flash_recovery_area/TEST/backupset/2016_06_23/o1_mf_ncnnf_TAG20160623T144001_cpq9vb51_.bkp

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/test/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/test/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/test/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/test/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/test/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/TEST/backupset/2016_06_23/o1_mf_nnndf_TAG20160623T143738_cpq9pv2z_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2016_06_23/o1_mf_nnndf_TAG20160623T143738_cpq9pv2z_.bkp tag=TAG20160623T143738
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:20
Finished restore at 23-JUN-16

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.
RMAN> recover database until scn 818237;

Starting recover at 23-JUN-16
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2016_06_23/o1_mf_1_4_cpqb0jmy_.arc
archived log file name=/u01/app/oracle/flash_recovery_area/TEST/archivelog/2016_06_23/o1_mf_1_4_cpqb0jmy_.arc thread=1 sequence=4
media recovery complete, elapsed time: 00:00:00
Finished recover at 23-JUN-16

SQL> alter database open read only;

     select sum(salary) from hr.emp;

SUM(SALARY)
-----------
          0

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.

SQL> shu immediate;
     startup mount;
SQL> recover database until cancel using backup controlfile;

ORA-00279: change 818245 generated at 06/23/2016 14:42:48 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/TEST/archivelog/2016_06_23/o1_mf_1_5_cpqb2jv
3_.arc
ORA-00280: change 818245 for thread 1 is in sequence #5


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 818274 generated at 06/23/2016 14:43:52 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/TEST/archivelog/2016_06_23/o1_mf_1_6_%u_.arc
ORA-00280: change 818274 for thread 1 is in sequence #6
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/TEST/archivelog/2016_06_23/o1_mf_1_5_cpqb2j
v3_.arc' no longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log
'/u01/app/oracle/flash_recovery_area/TEST/archivelog/2016_06_23/o1_mf_1_6_%u_.ar
c'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
  • Let us cancel the recovery, since the archived log for sequence 6 is not available:
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 818274 generated at 06/23/2016 14:43:52 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/TEST/archivelog/2016_06_23/o1_mf_1_6_%u_.arc
ORA-00280: change 818274 for thread 1 is in sequence #6


Specify log: {=suggested | filename | AUTO | CANCEL}
cancel 
Media recovery cancelled.
  • 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:
SQL> alter database open resetlogs;

SQL> select sum(salary) from hr.emp;

SUM(SALARY)
-----------
     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.

Tags: ,