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:
1 2 3 4 5 6 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> select current_scn from v$database; CURRENT_SCN ----------- <span style="color: red;"><strong>817881</strong></span> 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.
1 2 3 4 5 6 7 |
SQL> drop table hr.emp purge; create table hr.emp as select * from hr.employees; select sum(salary) from hr.emp; SUM(SALARY) ----------- <span style="color: red;"><strong>691416</strong></span> |
- Take a cold backup of the database and control file using RMAN:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
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, <span style="color: red;"><strong>not open</strong></span>) RMAN> backup <span style="color: red;"><strong>database</strong></span>; 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 <span style="color: red;"><strong>handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2016_06_23/o1_mf_nnndf_TAG20160623T143738_cpq9pv2z_.bkp</strong></span> 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 <span style="color: red;"><strong>controlfile</strong></span>; 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 <span style="color: red;"><strong>handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2016_06_23/o1_mf_ncnnf_TAG20160623T144001_cpq9vb51_.bkp</strong></span> 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#:
1 2 3 4 5 6 7 8 9 |
SQL> alter database open; SQL> select group#, sequence#, status from v$log; GROUP# SEQUENCE# STATUS ---------- ---------- ---------------- <span style="color: red;"><strong>1 4 CURRENT</strong></span> 2 2 INACTIVE 3 3 INACTIVE |
- Update SALARY = 0 in HR.EMP and note down the current SCN#:
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL>update hr.emp set salary = 0; commit; select sum(salary) from hr.emp; SUM(SALARY) ----------- <span style="color: red;"><strong>0</strong></span> SQL>select current_scn from v$database; CURRENT_SCN ----------- <span style="color: red;"><strong>818237</strong></span> |
- Switch the log so that log file with sequence# 4 having total SALARY = 0 is archived:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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 <span style="color: red;"><strong>/u01/app/oracle/flash_recovery_area/TEST/archivelog/2016_06_23/o1_mf_1_4_cpqb0jm</strong></span> <span style="color: red;"><strong>y_.arc</strong></span> <span style="color: red;"><strong>817738 4</strong></span> |
- Note down the currently active redo log group / sequence#:
1 2 3 4 5 6 7 |
SQL> select group#, sequence#, status from v$log; GROUP# SEQUENCE# STATUS ---------- ---------- ---------------- 1 4 ACTIVE <span style="color: red;"><strong>2 5 CURRENT</strong></span> 3 3 INACTIVE |
- Update SALARY = 1000 in HR.EMP and note down current SCN#:
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL>update hr.emp set salary = 1000; commit; select sum(salary) from hr.emp; SUM(SALARY) ----------- <span style="color: red;"><strong>107000</strong></span> SQL> select current_scn from v$database; CURRENT_SCN ----------- <span style="color: red;"><strong>818267</strong></span> |
- Switch the log so that the log file with sequence# 5 containing total SALARY= 107000 is archived:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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 <span style="color: red;"><strong>/u01/app/oracle/flash_recovery_area/TEST/archivelog/2016_06_23/o1_mf_1_5_cpqb2jv</strong></span> <span style="color: red;"><strong>3_.arc</strong></span> <span style="color: red;"><strong>818245 5</strong></span> |
- Simulate loss of the database and restart the database – Database reaches the nomount stage as its control file is missing:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
[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 <span style="color: red;"><strong>ORA-00205: error in identifying control file, check alert log for more info</strong></span> |
- Restore the control file from cold backup:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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 (<span style="color: red;"><strong>not mounted</strong></span>) 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: <span style="color: red;"><strong>restoring control file</strong></span> channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/u01/app/oracle/oradata/test/control01.ctl <span style="color: red;"><strong>Finished restore</strong></span> at 23-JUN-16 |
- Mount the database and restore data files:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
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: <span style="color: red;"><strong>reading from backup piece /u01/app/oracle/flash_recovery_area/TEST/backupset/2016_06_23/o1_mf_nnndf_TAG20160623T143738_cpq9pv2z_.bkp</strong></span> 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: <span style="color: red;"><strong>restore complete</strong></span>, 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
RMAN> recover database until scn <span style="color: red;"><strong>818237</strong></span>; 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 <span style="color: red;"><strong>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</strong></span> 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) ----------- <span style="color: red;"><strong>0</strong></span> |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
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: <span style="color: red;"><strong>change 818245 for thread 1 is in sequence #5</strong></span> 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 <span style="color: red;"><strong>'/u01/app/oracle/flash_recovery_area/TEST/archivelog/2016_06_23/o1_mf_1_5_cpqb2j</strong></span> <span style="color: red;"><strong>v3_.arc' no longer needed for this recovery</strong></span> Specify log: {=suggested | filename | AUTO | CANCEL} ORA-00308: <span style="color: red;"><strong>cannot open archived log</strong></span> <span style="color: red;"><strong>'/u01/app/oracle/flash_recovery_area/TEST/archivelog/2016_06_23/o1_mf_1_6_%u_.ar</strong></span> <span style="color: red;"><strong>c'</strong></span> 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:
1 2 3 4 5 6 7 8 9 10 |
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 : <span style="color: red;"><strong>/u01/app/oracle/flash_recovery_area/TEST/archivelog/2016_06_23/o1_mf_1_6_%u_.arc</strong></span> <span style="color: red;"><strong>ORA-00280: change 818274 for thread 1 is in sequence #6</strong></span> Specify log: {=suggested | filename | AUTO | CANCEL} <span style="color: red;"><strong>cancel </strong></span> <span style="color: red;"><strong>Media recovery cancelled</strong></span>. |
- 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:
1 2 3 4 5 6 7 |
SQL> alter database open resetlogs; SQL> select sum(salary) from hr.emp; SUM(SALARY) ----------- <span style="color: red;"><strong>107000</strong></span> |
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.
Load comments