On ORA-00600: internal error code, arguments: [kzdlk_zt2 err]

Today looking at one of my alert file I discovered the following error:

Wed Aug 28 10:44:02 2012
Errors in file /opt/app/oracle/admin/DB_NAME/udump/INSTANCE_NAME2_ora_22435.trc:
ORA-00600: internal error code, arguments: [kzdlk_zt2 err], [18446744073709551603], [], [], [], [], [], []

On Oracle metalink there's a very valuable tool to debug and analyze some ora-600 error: it's called "ORA-600/ORA-7445 Error Look-up Tool" and you can find it looking for the ID 153788.1
From this web page I selected "ORA-600" as Error Code, "kzdlk_zt2 err" as Error Code First Argument and finally 10.2.0.4 as Oracle RDBMS Version.

It soon prompted me asking to be redirected on ORA-600 [kzdlk_zt2 err] [ID 1309705.1] document.

The note states that the error indicates the wrong syntax has been used to create a database link.
When the database link was created, the password was established using the syntax IDENTIFIED BY VALUES as compared to the document syntax of IDENTIFIED BY.
It states also that the use of IDENTIFIED BY VALUES is reserved for internal Oracle use only. While earlier Oracle releases allowed the use of IDENTIFIED BY VALUES, this is not documented as being valid syntax.
From Oracle release 10gR2, database links must be created using the documented syntax.

 And the solution is: recreate the database link using valid syntax,

CREATE DATABASE LINK dblink
      CONNECT TO user IDENTIFIED BY password
      USING 'connect_string';

That's all.

How to switch back to the original location of the previously corrupted/damaged/lost/canceled datafile

In the previous post I used to reduce the restore time simply pointing to an image copy of my datafile. If you need to restore and recover your database as soon as possible that way is a great solution you can use. Now it's time to swith back your datafile to the original location. We don't want to use a location for all our datafiles and the flash recovery area just for one datafile... Let's look first at your current report of database schema.

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    831      SYSTEM               *** /home/oracle/app/oracle/oradata/orcl/system01.dbf
2    1105     SYSAUX               *** /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3    40       UNDOTBS1             *** /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
4    225      USERS                *** /home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_80j7lkp7_.dbf
5    82       EXAMPLE              *** /home/oracle/app/oracle/oradata/orcl/example01.dbf
6    7        APEX_1930613455248703 *** /home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767 /home/oracle/app/oracle/oradata/orcl/temp01.dbf

As you can see the datafile of USERS tablespace is currently located in the flash recovery area. And there is no USERS datafile on the location where other datafiles are.

[oracle@localhost orcl]$ pwd
/home/oracle/app/oracle/oradata/orcl
[oracle@localhost orcl]$ ll
total 2511684
-rw-rw---- 1 oracle oracle    7348224 Jul 20 05:50 APEX_1930613455248703.dbf
-rw-rw-r-- 1 oracle oracle          0 Jul 19 07:53 backup
-rw-rw---- 1 oracle oracle    9748480 Jul 20 06:18 control01.ctl
-rw-rw---- 1 oracle oracle   85991424 Jul 20 05:50 example01.dbf
-rw-rw---- 1 oracle oracle   52429312 Jul 20 05:50 redo01.log
-rw-rw---- 1 oracle oracle   52429312 Jul 20 06:18 redo02.log
-rw-rw---- 1 oracle oracle   52429312 Jul 20 05:50 redo03.log
-rw-rw---- 1 oracle oracle 1158684672 Jul 20 06:17 sysaux01.dbf
-rw-rw---- 1 oracle oracle  871374848 Jul 20 06:15 system01.dbf
-rw-rw---- 1 oracle oracle   20979712 Jul 20 05:51 temp01.dbf
-rw-rw---- 1 oracle oracle   41951232 Jul 20 06:18 undotbs01.dbf
-rw-rw---- 1 oracle oracle  235937792 Jul 19 07:48 users01_damaged.dbf

First thing to do is to copy your datafile using the format clause, specifying where you want to create your datafile copy.

RMAN> backup as copy datafile 4 format='/home/oracle/app/oracle/oradata/orcl/users01.dbf';

Starting backup at 20-07-2012 06:20:35
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_80j7lkp7_.dbf
output file name=/home/oracle/app/oracle/oradata/orcl/users01.dbf tag=TAG20120720T062036 RECID=8 STAMP=789114078
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
Finished backup at 20-07-2012 06:21:22

Starting Control File and SPFILE Autobackup at 20-07-2012 06:21:23
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_20/o1_mf_s_789114083_80lptnhd_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 20-07-2012 06:21:26

Now you have to switch to your new datafile location, putting that datafile offline.

RMAN> sql 'alter database datafile 4 offline';

sql statement: alter database datafile 4 offline

Switch to your current datafile copy. RMAN knows exactly where it's located.

RMAN> switch datafile 4 to copy;

datafile 4 switched to datafile copy "/home/oracle/app/oracle/oradata/orcl/users01.dbf"

Recover your datafile because many transactions could be occurred between your "backup as copy datafile" command and putting your datafile offline.

RMAN> recover datafile 4;

Starting recover at 20-07-2012 06:30:44
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 20-07-2012 06:30:49

Let your datafile be available to all the users again.

RMAN> sql 'alter database datafile 4 online';

sql statement: alter database datafile 4 online

Let's see what the report schema command says now:

RMAN> report schema;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    831      SYSTEM               *** /home/oracle/app/oracle/oradata/orcl/system01.dbf
2    1105     SYSAUX               *** /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3    40       UNDOTBS1             *** /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
4    225      USERS                *** /home/oracle/app/oracle/oradata/orcl/users01.dbf
5    82       EXAMPLE              *** /home/oracle/app/oracle/oradata/orcl/example01.dbf
6    7        APEX_1930613455248703 *** /home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767 /home/oracle/app/oracle/oradata/orcl/temp01.dbf

As you can see now in the default location of all datafiles of my database there's also the new USERS datafile.

[oracle@localhost orcl]$ ll
total 2742324
-rw-rw---- 1 oracle oracle    7348224 Jul 20 05:50 APEX_1930613455248703.dbf
-rw-rw-r-- 1 oracle oracle          0 Jul 19 07:53 backup
-rw-rw---- 1 oracle oracle    9748480 Jul 20 06:34 control01.ctl
-rw-rw---- 1 oracle oracle   85991424 Jul 20 05:50 example01.dbf
-rw-rw---- 1 oracle oracle   52429312 Jul 20 05:50 redo01.log
-rw-rw---- 1 oracle oracle   52429312 Jul 20 06:33 redo02.log
-rw-rw---- 1 oracle oracle   52429312 Jul 20 05:50 redo03.log
-rw-rw---- 1 oracle oracle 1158684672 Jul 20 06:32 sysaux01.dbf
-rw-rw---- 1 oracle oracle  871374848 Jul 20 06:32 system01.dbf
-rw-rw---- 1 oracle oracle   20979712 Jul 20 05:51 temp01.dbf
-rw-rw---- 1 oracle oracle   41951232 Jul 20 06:33 undotbs01.dbf
-rw-rw---- 1 oracle oracle  235937792 Jul 19 07:48 users01_damaged.dbf
-rw-rw---- 1 oracle oracle  235937792 Jul 20 06:32 users01.dbf

Now it's time to create a new image copy in the flash recovery area of the USERS datafile. Let's see the current situation:

RMAN> list copy of datafile 4;

List of Datafile Copies
=======================

Key     File S Completion Time     Ckp SCN    Ckp Time           
------- ---- - ------------------- ---------- -------------------
9       4    A 20-07-2012 06:30:29 13599396   20-07-2012 06:20:37
Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_80j7lkp7_.dbf
Tag: TAG20120719T074531

Issue the familiar "backup as copy" command to create a new image copy.

RMAN> backup as copy datafile 4;

Starting backup at 20-07-2012 06:35:32
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/home/oracle/app/oracle/oradata/orcl/users01.dbf
output file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_80lqo53d_.dbf tag=TAG20120720T063532 RECID=10 STAMP=789114947
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:16
Finished backup at 20-07-2012 06:35:48

Starting Control File and SPFILE Autobackup at 20-07-2012 06:35:48
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_20/o1_mf_s_789114949_80lqooxw_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 20-07-2012 06:35:51

Your "list copy" command shows now another available image copy of your USERS datafile.

RMAN> list copy of datafile 4;

List of Datafile Copies
=======================

Key     File S Completion Time     Ckp SCN    Ckp Time           
------- ---- - ------------------- ---------- -------------------
10      4    A 20-07-2012 06:35:47 13600245   20-07-2012 06:35:33
Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_80lqo53d_.dbf
Tag: TAG20120720T063532

9       4    A 20-07-2012 06:30:29 13599396   20-07-2012 06:20:37
Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_80j7lkp7_.dbf
Tag: TAG20120719T074531

That's all.

How to recover a corrupted/damaged/lost/canceled NONSYSTEM datafile switching to an image copy (database in ARCHIVELOG mode)

Today I will recover a lost datafile using an image copy as I told in advance at the end of this previous post .

So the first thing I must have is an image copy of my datafiles: to do that I issued the following commands.

[oracle@localhost orcl]$ rman target /
RMAN> backup as copy database;

Starting backup at 19-07-2012 07:45:31
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
output file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_sysaux_80j7dd8l_.dbf tag=TAG20120719T074531 RECID=2 STAMP=789032830
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:40
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/home/oracle/app/oracle/oradata/orcl/system01.dbf
output file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_system_80j7hk9c_.dbf tag=TAG20120719T074531 RECID=3 STAMP=789032923
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:36
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/home/oracle/app/oracle/oradata/orcl/users01.dbf
output file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_80j7lkp7_.dbf tag=TAG20120719T074531 RECID=4 STAMP=789032942
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/home/oracle/app/oracle/oradata/orcl/example01.dbf
output file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_example_80j7m0w4_.dbf tag=TAG20120719T074531 RECID=5 STAMP=789032948
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
output file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_undotbs1_80j7m8fq_.dbf tag=TAG20120719T074531 RECID=6 STAMP=789032955
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf
output file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_apex_193_80j7mhmf_.dbf tag=TAG20120719T074531 RECID=7 STAMP=789032959
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 19-07-2012 07:49:20

Starting Control File and SPFILE Autobackup at 19-07-2012 07:49:21
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_19/o1_mf_s_789032961_80j7mkvb_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-07-2012 07:49:22

Now let's simulate a lost datafile of a NONSYSTEM tablespace

[oracle@localhost orcl]$ mv /home/oracle/app/oracle/oradata/orcl/users01.dbf /home/oracle/app/oracle/oradata/orcl/users01_damaged.dbf

The RMAN report command is no more able to identify the size of the lost datafile

[oracle@localhost orcl]$ rman target /
RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    831      SYSTEM               *** /home/oracle/app/oracle/oradata/orcl/system01.dbf
2    1105     SYSAUX               *** /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3    40       UNDOTBS1             *** /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
4    0        USERS                *** /home/oracle/app/oracle/oradata/orcl/users01.dbf
5    82       EXAMPLE              *** /home/oracle/app/oracle/oradata/orcl/example01.dbf
6    7        APEX_1930613455248703 *** /home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767 /home/oracle/app/oracle/oradata/orcl/temp01.dbf

In the alert log we can see errors like the following:

...
Thu Jul 19 08:04:52 2012
Errors in file
/home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_8591.trc:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/home/oracle/app/oracle/oradata/orcl/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
...

It's time to restore and recover our datafile. First put it offline

RMAN> sql 'alter database datafile 4 offline';

sql statement: alter database datafile 4 offline

Then tell to use the image copy (skipping at least to copy it in the default location)

RMAN> switch datafile 4 to copy;

datafile 4 switched to datafile copy
"/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_80j7lkp7_.dbf"

Recover the datafile

RMAN> recover datafile 4;

Starting recover at 19-07-2012 08:06:42
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=46 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 19-07-2012 08:06:44

Bring it online

RMAN> sql ' alter database datafile 4 online';

sql statement:  alter database datafile 4 online

From the alert log you can see:

...
Thu Jul 19 08:06:18 2012
alter database datafile 4 offline
Completed: alter database datafile 4 offline
Thu Jul 19 08:06:29 2012
Errors in file
/home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_8535.trc:
ORA-19625: error identifying file
/home/oracle/app/oracle/oradata/orcl/users01.dbf
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
WARNING: switching recovery area datafile copy
/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_80j7lkp7_.dbf
as database area datafile.
This datafile is no more accounted into used space. Consider decrementing
db_recovery_file_dest_size parameter value by 235929600 bytes.
Switch of datafile 4 complete to datafile copy 
checkpoint is 13595062
Thu Jul 19 08:06:43 2012
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover if needed
datafile 4
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo02.log
Media Recovery Complete (orcl)
Completed: alter database recover if needed
datafile 4
Thu Jul 19 08:06:56 2012
alter database datafile 4 online
Completed:  alter database datafile 4 online
...

Now the RMAN report command is able again to obtain information about your datafile

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    831      SYSTEM               *** /home/oracle/app/oracle/oradata/orcl/system01.dbf
2    1105     SYSAUX               *** /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3    40       UNDOTBS1             *** /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
4    225      USERS                *** /home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_80j7lkp7_.dbf
5    82       EXAMPLE              *** /home/oracle/app/oracle/oradata/orcl/example01.dbf
6    7        APEX_1930613455248703 *** /home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767 /home/oracle/app/oracle/oradata/orcl/temp01.dbf

As you can easily see, with this scenario time makes the difference compared with the previous post In this scenario it took only 38 seconds to get back online with the database.

TOTAL TIME: 38
Thu Jul 19 08:06:18 2012
Thu Jul 19 08:06:56 2012

Of course I used a simple test machine, but compared with the previous scenario (one minute and 34 seconds) to get back online a datafile of only 250MB, I spent now only 38 seconds. You should consider this approach if you want to get back your database online as soon as possible. You should test your timesavings, but it could let you save even hours... In the next scenario I will move back that datafile located in the flash recovery area to the original location. That's all.

How to recover a corrupted/damaged/lost/canceled NONSYSTEM datafile (database in ARCHIVELOG mode)

The following scenario describes what happens when a nonsystem datafile is lost. Before proceeding be sure to have a complete backup of your test database and be sure your database is in ARCHIVELOG mode. Let's start. My database is already running.

[oracle@localhost orcl]$ ps -ef|grep smon
oracle    7200  2820  0 06:57 pts/1    00:00:00 grep smon

And it's in ARCHIVELOG mode

[oracle@localhost orcl]$ sqlplus / as sysdba
SQL> startup
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   2
Current log sequence           2

Connecting through RMAN I can receive information about my datafiles

[oracle@localhost orcl]$ rman target /
RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    831      SYSTEM               *** /home/oracle/app/oracle/oradata/orcl/system01.dbf
2    1105     SYSAUX               *** /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3    40       UNDOTBS1             *** /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
4    225      USERS                *** /home/oracle/app/oracle/oradata/orcl/users01.dbf
5    82       EXAMPLE              *** /home/oracle/app/oracle/oradata/orcl/example01.dbf
6    7        APEX_1930613455248703 *** /home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767 /home/oracle/app/oracle/oradata/orcl/temp01.dbf

I'm of course able to query the dictionary tables and see in which datafiles some tables are located.

SQL> set pagesize 999
SQL> set linesize 180
SQL> select OWNER, TABLE_NAME, TABLESPACE_NAME from dba_tables
2  where owner = 'HR';

OWNER                          TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ -----------------------
HR                             REGIONS                        USERS
HR                             LOCATIONS                      USERS
HR                             DEPARTMENTS                    USERS
HR                             JOBS                           USERS
HR                             EMPLOYEES                      USERS
HR                             JOB_HISTORY                    USERS
HR                             COUNTRIES

What does it happens when I delete the datafile where USERS tablespace is based on.

[oracle@localhost orcl]$ mv /home/oracle/app/oracle/oradata/orcl/users01.dbf /home/oracle/app/oracle/oradata/orcl/users01_damaged.dbf

I'm still able to query the dictionary tables for example but...

SQL> select count(*) from dba_tables;                 

COUNT(*)
----------
3013

I obtain an error when I try to select some rows from the HR.EMPLOYEES table.

SQL> select count(*) from hr.EMPLOYEES;
select count(*) from hr.EMPLOYEES
  *
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/home/oracle/app/oracle/oradata/orcl/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

Looking at the log file, there's the same clear error: the systen is not able to obtain status information on file '/home/oracle/app/oracle/oradata/orcl/users01.dbf'.

[oracle@localhost orcl]$ tail -f /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log 
...
Thu Jul 19 07:07:49 2012
Errors in file
/home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_7476.trc:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/home/oracle/app/oracle/oradata/orcl/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
...

If you try to connect with RMAN the REPORT SCHEMA command is now unable to correctly size the USERS tablespace. RMAN says its size is 0.

[oracle@localhost orcl]$ rman target /
RMAN> report schema;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    831      SYSTEM               *** /home/oracle/app/oracle/oradata/orcl/system01.dbf
2    1105     SYSAUX               *** /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3    40       UNDOTBS1             *** /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
4    0        USERS                *** /home/oracle/app/oracle/oradata/orcl/users01.dbf
5    82       EXAMPLE              *** /home/oracle/app/oracle/oradata/orcl/example01.dbf
6    7        APEX_1930613455248703 *** /home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767 /home/oracle/app/oracle/oradata/orcl/temp01.dbf

Because the USERS tablespace is not a system tablespace, we can recover it just putting it in offline mode.

RMAN> sql 'alter database datafile 4 offline';

sql statement: alter database datafile 4 offline

RMAN> restore datafile 4;

Starting restore at 19-07-2012 07:30:12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 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 00004 to
/home/oracle/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: reading from backup piece
/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_07_17/o1_mf_nnndf_TAG20120717T090114_80c32cxk_.bkp
channel ORA_DISK_1: piece
handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_07_17/o1_mf_nnndf_TAG20120717T090114_80c32cxk_.bkp
tag=TAG20120717T090114
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 19-07-2012 07:31:10

RMAN> recover datafile 4;

Starting recover at 19-07-2012 07:31:20
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 19-07-2012 07:31:22

RMAN> sql 'alter database datafile 4 online';

sql statement: alter database datafile 4 online

RMAN> 

In the alert log you can see how the recovery process proceedes

[oracle@localhost orcl]$ tail -f /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
...
Thu Jul 19 07:30:01 2012
alter database datafile 4 offline
Completed: alter database datafile 4 offline
Thu Jul 19 07:30:01 2012
Starting background process SMCO
Thu Jul 19 07:30:02 2012
SMCO started with pid=46, OS id=8257 
Thu Jul 19 07:31:03 2012
Full restore complete of datafile 4
/home/oracle/app/oracle/oradata/orcl/users01.dbf.  Elapsed time: 0:00:47 
checkpoint is 13569948
last deallocation scn is 13511135
Thu Jul 19 07:31:21 2012
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover if needed
datafile 4
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo01.log
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo02.log
Media Recovery Complete (orcl)
Completed: alter database recover if needed
datafile 4
Thu Jul 19 07:31:35 2012
alter database datafile 4 online
Completed: alter database datafile 4 online
...

Just note how long the restore/recover process lasted: it began at Thu Jul 19 07:30:01 2012 to end at Thu Jul 19 07:31:35 2012 for a TOTAL TIME of 1:34 (one minute and 34 seconds). I'll compare this result with another restore/recover approach in the next recovery scenario. Now I'm able to query again my hr.employees table

SQL> select count(*) from hr.employees;

COUNT(*)
----------
107

and even RMAN is able to size correctly the USERS tablespace

RMAN> report schema;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    831      SYSTEM               ***
/home/oracle/app/oracle/oradata/orcl/system01.dbf
2    1105     SYSAUX               ***
/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3    40       UNDOTBS1             ***
/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
4    225      USERS                ***
/home/oracle/app/oracle/oradata/orcl/users01.dbf
5    82       EXAMPLE              ***
/home/oracle/app/oracle/oradata/orcl/example01.dbf
6    7        APEX_1930613455248703 ***
/home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767
/home/oracle/app/oracle/oradata/orcl/temp01.dbf

In the next recovery scenario I'll use an image copy previously created from RMAN to restore and recover the same tablespace of today. That's all.

How to restore a NOARCHIVELOG database using a backup controlfile

Let's simulate a different restore scenario for a NOARCHIVELOG database compared with this previous post. Be sure to have set the backup controlfile option in RMAN to ON, before taking your first backup as I did. This approach, using the controlfile coming from the autobackup, is different compared with the copy of an available multiplexed copy of a current controlfile because there you have to perform an incomplete recovery of your database, while using the autobackup controlfile you have already a controlfile with SCN consistent with all your datafiles

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

I shutted down my database...

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

and then removed all my datafiles, redolog and current controlfile

[oracle@localhost orcl]$ ls
APEX_1930613455248703.dbf  example01.dbf  redo02.log  sysaux01.dbf  temp01.dbf
users01.dbf
control01.ctl              redo01.log     redo03.log  system01.dbf
undotbs01.dbf
[oracle@localhost orcl]$ rm *

From rman console I typed the following commands:

[oracle@localhost orcl]$ rman target /
RMAN> startup nomount;
RMAN> restore controlfile from autobackup;

Starting restore at 17-JUL-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

recovery area destination: /home/oracle/app/oracle/flash_recovery_area
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: AUTOBACKUP
/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_17/o1_mf_s_788864449_80c39jlo_.bkp
found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP
/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_17/o1_mf_s_788864449_80c39jlo_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/home/oracle/app/oracle/oradata/orcl/control01.ctl
output file
name=/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl
Finished restore at 17-JUL-12

And now I'm able to at least mount my database

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

and check that my database is using a backup controlfile

[oracle@localhost orcl]$ sqlplus / as sysdba
SQL> select controlfile_type from V$DATABASE;

CONTROL
-------
BACKUP

From the rman console issue the restore command.

RMAN> restore database;

Starting restore at 17-JUL-12
Starting implicit crosscheck backup at 17-JUL-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
Crosschecked 4 objects
Finished implicit crosscheck backup at 17-JUL-12

Starting implicit crosscheck copy at 17-JUL-12
using channel ORA_DISK_1
Finished implicit crosscheck copy at 17-JUL-12

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

List of Cataloged Files
=======================
File Name:
/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_17/o1_mf_s_788864449_80c39jlo_.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
/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_17/o1_mf_nnndf_TAG20120717T090114_80c32cxk_.bkp
channel ORA_DISK_1: piece
handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_07_17/o1_mf_nnndf_TAG20120717T090114_80c32cxk_.bkp
tag=TAG20120717T090114
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:04:07
Finished restore at 17-JUL-12

Finally open your database with the resetlogs option

RMAN> alter database open resetlogs;

database opened

Your database is now available again. These two posts are really unusual real world scenario because (I hope) you are not running your database in noarchivelog mode... So in the next posts my database will be set in ARCHIVELOG mode and several recovery scenario will be solved. That's all.

How to restore a NOARCHIVELOG database (ORA-01139: RESETLOGS option only valid after an incomplete database recovery) using current control file

What we have done in the previous post was to simply create a backup of our database (put in NOARCHIVELOG mode). Now it's time to cause a fault on 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. Of course the hr.after_backup_table table is lost because it was created after the backup.

[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
...

That's all.