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

Source

About The Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.