We are going to have a look at recovery in Oracle database. it’s time to cause a fault in the database so we need to recover it. The scenario is the following: what does it happen and how can I recover a database in NOARCHIVELOG mode when a disk failure occurs and I lost my current control file, all my datafiles and only multiplexed control files are available?
Let’s start up the instance and create a table.
[oracle@localhost ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Tue Jul 17 07:05:13 2012 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 456146944 bytes Fixed Size 1344840 bytes Variable Size 352324280 bytes Database Buffers 96468992 bytes Redo Buffers 6008832 bytes Database mounted. Database opened. SQL> create table hr.after_backup_table (a number); Table created.
My datafiles are located here.
SQL> select name from V$DATAFILE; NAME -------------------------------------------------------------------------------- /home/oracle/app/oracle/oradata/orcl/system01.dbf /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf /home/oracle/app/oracle/oradata/orcl/users01.dbf /home/oracle/app/oracle/oradata/orcl/example01.dbf /home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf
Now let’s see what happens when I move all datafiles, redo log and control files to another directory simulating a disks failure. I assume for this scenario to be able to get back with a consistent copy of at least one multiplexed control files. My control files are all in the same path on my test machine.
[oracle@localhost ~]$ cd /home/oracle/app/oracle/oradata/orcl [oracle@localhost orcl]$ ls control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf APEX_1930613455248703.dbf example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf [oracle@localhost orcl]$ mkdir old [oracle@localhost orcl]$ mv *ctl *log *dbf old/
The instance is still running
[oracle@localhost orcl]$ ps -ef|grep smon oracle 4379 1 0 07:05 ? 00:00:01 ora_smon_orcl oracle 4560 2820 0 07:15 pts/1 00:00:00 grep smon
From the alert log the instance begins to trace some errors
[oracle@localhost orcl]$ tail -f /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log ... Tue Jul 17 07:15:35 2012 Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_4563.trc: ORA-01116: error in opening database file 1 ORA-01110: data file 1: '/home/oracle/app/oracle/oradata/orcl/system01.dbf' ORA-27041: unable to open file Linux Error: 2: No such file or directory Additional information: 3 ...
Let’s try to shutdown our instance
[oracle@localhost orcl]$ sqlplus / as sysdba SQL> shutdown immediate; ORA-00210: cannot open the specified control file ORA-00202: control file: '/home/oracle/app/oracle/oradata/orcl/control01.ctl' ORA-27041: unable to open file Linux Error: 2: No such file or directory Additional information: 3
Mmmmm… it doesn’t work… Of course the instance is not able to find and write any SCN on the current control file. So issue the following command
SQL> shutdown abort; ORACLE instance shut down.
Let’s see what happens when an instance is not able to find a control file. The instance is not able to be open in MOUNT mode
[oracle@localhost old]$ sqlplus / as sysdba SQL> startup mount; ORACLE instance started. Total System Global Area 456146944 bytes Fixed Size 1344840 bytes Variable Size 352324280 bytes Database Buffers 96468992 bytes Redo Buffers 6008832 bytes ORA-00205: error in identifying control file, check alert log for more info SQL> shutdown immediate; ORA-01507: database not mounted ORACLE instance shut down. SQL> exit
From the alert log we can see the instance is not able to read the control file
[oracle@localhost orcl]$ tail -f /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log ... ALTER DATABASE MOUNT ORA-00210: cannot open the specified control file ORA-00202: control file: '/home/oracle/app/oracle/oradata/orcl/control01.ctl' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 ...
Imagine you have lost all your database files except one control file that was wisely multiplexed on another disk (in my case it’s in the flash recovery area path). We can use it to mount the database, copying it to the original location.
[oracle@localhost old]$ ls control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf APEX_1930613455248703.dbf example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf [oracle@localhost old]$ cp /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl ../control01.ctl
Now the instance is able to open in mount mode using the CURRENT control file (compared with a next scenario where I will use the control file from backup).
[oracle@localhost old]$ sqlplus / as sysdba SQL> startup mount; ORACLE instance started. Total System Global Area 456146944 bytes Fixed Size 1344840 bytes Variable Size 352324280 bytes Database Buffers 96468992 bytes Redo Buffers 6008832 bytes Database mounted. SQL> select controlfile_type from v$database; CONTROL ------- CURRENT
Now it’s time to restore the datafiles from the backup using RMAN
[oracle@localhost ~]$ rman target / Recovery Manager: Release 11.2.0.2.0 - Production on Tue Jul 17 07:31:04 2012 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1229390655, not open) RMAN> restore database; Starting restore at 17-07-2012 07:34:44 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=20 device type=DISK 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 /home/oracle/app/oracle/oradata/orcl/system01.dbf channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/app/oracle/oradata/orcl/users01.dbf channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/app/oracle/oradata/orcl/example01.dbf channel ORA_DISK_1: restoring datafile 00006 to /home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_07_15/o1_mf_nnndf_TAG20120715T054745_805gzldn_.bkp channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_07_15/o1_mf_nnndf_TAG20120715T054745_805gzldn_.bkp tag=TAG20120715T054745 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:04:07 Finished restore at 17-07-2012 07:38:54
On the alert log you can see all the steps
[oracle@localhost orcl]$ tail -f /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log ... Full restore complete of datafile 6 /home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf. Elapsed time: 0:00:01 checkpoint is 13565488 last deallocation scn is 754490 Full restore complete of datafile 3 /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf. Elapsed time: 0:00:04 checkpoint is 13565488 last deallocation scn is 13460761 Undo Optimization current scn is 13509396 Tue Jul 17 07:35:29 2012 Full restore complete of datafile 5 /home/oracle/app/oracle/oradata/orcl/example01.dbf. Elapsed time: 0:00:26 checkpoint is 13565488 last deallocation scn is 985357 Tue Jul 17 07:36:21 2012 Full restore complete of datafile 4 /home/oracle/app/oracle/oradata/orcl/users01.dbf. Elapsed time: 0:01:27 checkpoint is 13565488 last deallocation scn is 13511135 Tue Jul 17 07:38:22 2012 Full restore complete of datafile 1 /home/oracle/app/oracle/oradata/orcl/system01.dbf. Elapsed time: 0:03:31 checkpoint is 13565488 last deallocation scn is 12237706 Undo Optimization current scn is 13509396 Tue Jul 17 07:38:43 2012 Full restore complete of datafile 2 /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf. Elapsed time: 0:03:49 checkpoint is 13565488 last deallocation scn is 13413218 ...
And indeed on the file system I can see all my datafiles
[oracle@localhost ~]$ cd app/oracle/oradata/orcl/ [oracle@localhost orcl]$ ll total 2356872 -rw-rw---- 1 oracle oracle 7348224 Jul 17 07:34 APEX_1930613455248703.dbf -rw-r----- 1 oracle oracle 9748480 Jul 17 07:40 control01.ctl -rw-rw---- 1 oracle oracle 85991424 Jul 17 07:35 example01.dbf drwxrwxr-x 2 oracle oracle 4096 Jul 17 07:33 old -rw-rw---- 1 oracle oracle 1158684672 Jul 17 07:38 sysaux01.dbf -rw-rw---- 1 oracle oracle 871374848 Jul 17 07:38 system01.dbf -rw-rw---- 1 oracle oracle 41951232 Jul 17 07:34 undotbs01.dbf -rw-rw---- 1 oracle oracle 235937792 Jul 17 07:36 users01.dbf
Now it’s time to open the database using the resetlogs option, but…
SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01139: RESETLOGS option only valid after an incomplete database recovery
[oracle@localhost orcl]$ tail -f /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log ... alter database open resetlogs ORA-1139 signalled during: alter database open resetlogs... Tue Jul 17 07:41:30 2012 Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_5091.trc: ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/home/oracle/app/oracle/oradata/orcl/redo01.log' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_5091.trc: ORA-00313: open failed for members of log group 2 of thread 1 ORA-00312: online log 2 thread 1: '/home/oracle/app/oracle/oradata/orcl/redo02.log' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_5091.trc: ORA-00313: open failed for members of log group 3 of thread 1 ORA-00312: online log 3 thread 1: '/home/oracle/app/oracle/oradata/orcl/redo03.log' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 ...
The following error happens because on the restored datafiles is written an SCN that was valid at the time the backup was taken and equal to the control file of that moment. The control file we are using is ahead compared with the SCN written on the datafiles… You have to perform (“simulate”) an incomplete recovery, typing CANCEL when requested
SQL> recover database until cancel;
ORA-00279: change 13565488 generated at 07/15/2012 05:44:01 needed for thread
1
ORA-00289: suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_17/o1_mf_1_5
82_%u_.arc
ORA-00280: change 13565488 for thread 1 is in sequence #582
Specify log: {=suggested | filename | AUTO | CANCEL}
Type CANCEL
CANCEL
Media recovery cancelled.
On alert log you an read
[oracle@localhost orcl]$ tail -f /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log ... ORA-279 signalled during: ALTER DATABASE RECOVER database until cancel ... ALTER DATABASE RECOVER CANCEL Media Recovery Canceled Completed: ALTER DATABASE RECOVER CANCEL ...
Still any redo log files are available
[oracle@localhost orcl]$ ll total 2356872 -rw-rw---- 1 oracle oracle 7348224 Jul 17 07:42 APEX_1930613455248703.dbf -rw-r----- 1 oracle oracle 9748480 Jul 17 07:43 control01.ctl -rw-rw---- 1 oracle oracle 85991424 Jul 17 07:42 example01.dbf drwxrwxr-x 2 oracle oracle 4096 Jul 17 07:33 old -rw-rw---- 1 oracle oracle 1158684672 Jul 17 07:42 sysaux01.dbf -rw-rw---- 1 oracle oracle 871374848 Jul 17 07:42 system01.dbf -rw-rw---- 1 oracle oracle 41951232 Jul 17 07:42 undotbs01.dbf -rw-rw---- 1 oracle oracle 235937792 Jul 17 07:42 users01.dbf
untill the following command completes
SQL> alter database open resetlogs; Database altered.
On the alert log your database is waiting for the redo log files… Wait some minutes and then you have successfully recovered your database.
[oracle@localhost orcl]$ tail -f /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log ... alter database open resetlogs RESETLOGS after incomplete recovery UNTIL CHANGE 13565488 Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4965.trc: ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/home/oracle/app/oracle/oradata/orcl/redo01.log' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 Clearing online redo logfile 1 /home/oracle/app/oracle/oradata/orcl/redo01.log Clearing online log 1 of thread 1 sequence number 580 Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4965.trc: ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/home/oracle/app/oracle/oradata/orcl/redo01.log' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4965.trc: ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/home/oracle/app/oracle/oradata/orcl/redo01.log' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 Tue Jul 17 07:44:04 2012 Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_5107.trc: ORA-00322: log 1 of thread 1 is not current copy ORA-00312: online log 1 thread 1: '/home/oracle/app/oracle/oradata/orcl/redo01.log' Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_5107.trc: ORA-00313: open failed for members of log group 2 of thread 1 ORA-00312: online log 2 thread 1: '/home/oracle/app/oracle/oradata/orcl/redo02.log' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_5107.trc: ORA-00313: open failed for members of log group 3 of thread 1 ORA-00312: online log 3 thread 1: '/home/oracle/app/oracle/oradata/orcl/redo03.log' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 Clearing online redo logfile 1 complete Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4965.trc: ORA-00313: open failed for members of log group 2 of thread 1 ORA-00312: online log 2 thread 1: '/home/oracle/app/oracle/oradata/orcl/redo02.log' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 Clearing online redo logfile 2 /home/oracle/app/oracle/oradata/orcl/redo02.log Clearing online log 2 of thread 1 sequence number 581 Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4965.trc: ORA-00313: open failed for members of log group 2 of thread 1 ORA-00312: online log 2 thread 1: '/home/oracle/app/oracle/oradata/orcl/redo02.log' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4965.trc: ORA-00313: open failed for members of log group 2 of thread 1 ORA-00312: online log 2 thread 1: '/home/oracle/app/oracle/oradata/orcl/redo02.log' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 Clearing online redo logfile 2 complete Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4965.trc: ORA-00313: open failed for members of log group 3 of thread 1 ORA-00312: online log 3 thread 1: '/home/oracle/app/oracle/oradata/orcl/redo03.log' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 Clearing online redo logfile 3 /home/oracle/app/oracle/oradata/orcl/redo03.log Clearing online redo logfile 3 complete Resetting resetlogs activation ID 1229364031 (0x49469b3f) Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4965.trc: ORA-00367: checksum error in log file header ORA-00322: log 1 of thread 1 is not current copy ORA-00312: online log 1 thread 1: '/home/oracle/app/oracle/oradata/orcl/redo01.log' Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4965.trc: ORA-00367: checksum error in log file header ORA-00322: log 2 of thread 1 is not current copy ORA-00312: online log 2 thread 1: '/home/oracle/app/oracle/oradata/orcl/redo02.log' Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4965.trc: ORA-00313: open failed for members of log group 3 of thread 1 ORA-00312: online log 3 thread 1: '/home/oracle/app/oracle/oradata/orcl/redo03.log' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4965.trc: ORA-00313: open failed for members of log group 3 of thread 1 ORA-00312: online log 3 thread 1: '/home/oracle/app/oracle/oradata/orcl/redo03.log' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 Tue Jul 17 07:44:07 2012 Setting recovery target incarnation to 3 Tue Jul 17 07:44:07 2012 Assigning activation ID 1316687987 (0x4e7b1073) Thread 1 opened at log sequence 1 Current log# 1 seq# 1 mem# 0: /home/oracle/app/oracle/oradata/orcl/redo01.log Successful open of redo thread 1
