Recovery of non system datafile [ without backup ]

Environment: 10gR2 on CentOS 4

Assumptions:
1]The database is in archive log mode.

A non-system datafile can be recovered even if no backups are available for that datafile, provided all the archive logs since the creation of the datafile are available and the datafile is added after the control file is created. Datafiles created before the creation of the controlfile cannot be recoverd by this method.

[oracle@spade ~]$ sqlplus scott/tiger

SQL> insert into j_emp select * from j_emp;
insert into j_emp select * from j_emp
*
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/u01/burp/oradata/booms.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

When checked the datafile booms.dbf is missing. But there is not any backup for this datafile
to perform open recovery

Steps for Database in OPEN mode
===============================
1] Take tablespace offline with immediate option;
2] SQL> alter database create datafile ‘<datafile_name>’;
3] Recover the tablespace
4] Online the tablespace

Steps for Database in SHUTDOWN
===============================
1] Mount the database
2] Offline the datafile [SQL>alter database datafile 5 offline;]
3] Open the database
4] SQL> alter database create datafile ‘<datafile_name>’;
5] Recover the tablespace
6] Online the tablespace
To restore the datafile to a different location:

SQL>alter database create datafile '<datafile_name_old>' as '<datafile_name_new>';

**examples down are for the database in ‘open’ mode.

===ARENA===

SQL> select name, open_mode, checkpoint_change#, ARCHIVE_CHANGE# from v$database;

NAME      OPEN_MODE  CHECKPOINT_CHANGE# ARCHIVE_CHANGE#
--------- ---------- ------------------ ---------------
BURP      READ WRITE             208496          206897

SQL> select substr(name,1,50), recover, fuzzy, checkpoint_change# from v$datafile_header;

SUBSTR(NAME,1,50)                                  REC FUZ CHECKPOINT_CHANGE#
-------------------------------------------------- --- --- ------------------
/u01/burp/oradata/system01.dbf                     NO  YES             208496
/u01/burp/oradata/undotbs01.dbf                    NO  YES             208496
/u01/burp/oradata/sysaux01.dbf                     NO  YES             208496
/u01/burp/oradata/users01.dbf                      NO  YES             208496
                                                                            0
SQL>

SQL> select HXFIL File_num,substr(HXFNM,1,40) File_name,FHTYP Type,HXERR Validity,FHSCN CHK, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence from X$KCVFH;

  FILE_NUM FILE_NAME                                      TYPE   VALIDITY CHK              TABLESPACE_NAME          STATUS   SEQUENCE
---------- ---------------------------------------- ---------- ---------- ---------------- -------------------- ---------- ----------
         1 /u01/burp/oradata/system01.dbf                    3          0 208496           SYSTEM                     8196        148
         2 /u01/burp/oradata/undotbs01.dbf                   3          0 208496           UNDOTBS1                      4        148
         3 /u01/burp/oradata/sysaux01.dbf                    3          0 208496           SYSAUX                        4        148
         4 /u01/burp/oradata/users01.dbf                     3          0 208496           USERS                         4        148
         5                                                   0          5 0                                              0          0
SQL>

Since the datafile isn’t available we need to take the tablespace offline with IMMEDIATE option

SQL> alter tablespace booms offline immediate;

Tablespace altered.

Now we will create the datafile

SQL> alter database create datafile '/u01/burp/oradata/booms.dbf';

Database altered

The datafile is created but the checkpoint is lagging behind other datafiles.

SQL> select HXFIL File_num,substr(HXFNM,1,40) File_name,FHTYP Type,HXERR Validity,FHSCN CHK, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence from X$KCVFH;

  FILE_NUM FILE_NAME                                      TYPE   VALIDITY CHK              TABLESPACE_NAME          STATUS   SEQUENCE
---------- ---------------------------------------- ---------- ---------- ---------------- -------------------- ---------- ----------
         1 /u01/burp/oradata/system01.dbf                    3          0 208496           SYSTEM                     8196        148
         2 /u01/burp/oradata/undotbs01.dbf                   3          0 208496           UNDOTBS1                      4        148
         3 /u01/burp/oradata/sysaux01.dbf                    3          0 208496           SYSAUX                        4        148
         4 /u01/burp/oradata/users01.dbf                     3          0 208496           USERS                         4        148
         5 /u01/burp/oradata/booms.dbf                       3          0 205403           BOOMS                         0        139

SQL> recover tablespace booms;

Excerpt from alert.log
==============

ALTER DATABASE RECOVER  tablespace booms
Wed Oct  8 11:38:39 2008
Media Recovery Start
ORA-279 signalled during: ALTER DATABASE RECOVER  tablespace booms  ...
Wed Oct  8 11:38:56 2008
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Wed Oct  8 11:38:56 2008
Media Recovery Log /u01/burp/arch/1_139_667494933.arch
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
:
:
Wed Oct  8 11:38:56 2008
Media Recovery Log /u01/burp/arch/1_144_667494933.arch
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
Wed Oct  8 11:38:56 2008
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Wed Oct  8 11:38:56 2008
Media Recovery Log /u01/burp/arch/1_145_667494933.arch
Wed Oct  8 11:38:57 2008
Recovery of Online Redo Log: Thread 1 Group 2 Seq 146 Reading mem 0
  Mem# 0 errs 0: /u01/burp/oradata/redo02.log
Wed Oct  8 11:38:57 2008
Recovery of Online Redo Log: Thread 1 Group 3 Seq 147 Reading mem 0
  Mem# 0 errs 0: /u01/burp/oradata/redo03.log
Wed Oct  8 11:38:57 2008
Recovery of Online Redo Log: Thread 1 Group 1 Seq 148 Reading mem 0
  Mem# 0 errs 0: /u01/burp/oradata/redo01.log
Wed Oct  8 11:38:58 2008
Media Recovery Complete (burp)
Completed: ALTER DATABASE RECOVER    CONTINUE DEFAULT

==============================

SQL> select HXFIL File_num,substr(HXFNM,1,40) File_name,FHTYP Type,HXERR Validity,FHSCN CHK, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence from X$KCVFH;

  FILE_NUM FILE_NAME                                      TYPE   VALIDITY CHK              TABLESPACE_NAME          STATUS   SEQUENCE
---------- ---------------------------------------- ---------- ---------- ---------------- -------------------- ---------- ----------
         1 /u01/burp/oradata/system01.dbf                    3          0 208496           SYSTEM                     8196        148
         2 /u01/burp/oradata/undotbs01.dbf                   3          0 208496           UNDOTBS1                      4        148
         3 /u01/burp/oradata/sysaux01.dbf                    3          0 208496           SYSAUX                        4        148
         4 /u01/burp/oradata/users01.dbf                     3          0 208496           USERS                         4        148
         5 /u01/burp/oradata/booms.dbf                       3          0 208695           BOOMS                         0          0

SQL> select substr(name,1,50), recover, fuzzy, checkpoint_change# from v$datafile_header;

SUBSTR(NAME,1,50)                                  REC FUZ CHECKPOINT_CHANGE#
-------------------------------------------------- --- --- ------------------
/u01/burp/oradata/system01.dbf                     NO  YES             208496
/u01/burp/oradata/undotbs01.dbf                    NO  YES             208496
/u01/burp/oradata/sysaux01.dbf                     NO  YES             208496
/u01/burp/oradata/users01.dbf                      NO  YES             208496
/u01/burp/oradata/booms.dbf                        NO  NO              208695
SQL>

The archive logs until 148 were applied and booms.dbf has become consistent and can be ONLINED

SQL> alter tablespace booms online;

Tablespace altered.

SQL>  select HXFIL File_num,substr(HXFNM,1,40) File_name,FHTYP Type,HXERR Validity,FHSCN CHK, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence from X$KCVFH;

  FILE_NUM FILE_NAME                                      TYPE   VALIDITY CHK              TABLESPACE_NAME          STATUS   SEQUENCE
---------- ---------------------------------------- ---------- ---------- ---------------- -------------------- ---------- ----------
         1 /u01/burp/oradata/system01.dbf                    3          0 208496           SYSTEM                     8196        148
         2 /u01/burp/oradata/undotbs01.dbf                   3          0 208496           UNDOTBS1                      4        148
         3 /u01/burp/oradata/sysaux01.dbf                    3          0 208496           SYSAUX                        4        148
         4 /u01/burp/oradata/users01.dbf                     3          0 208496           USERS                         4        148
         5 /u01/burp/oradata/booms.dbf                       3          0 208973           BOOMS                         4        148

SQL>

Recovery of Non System datafile [ while the database is closed ]

Environment: 10gR2 on CentOS 4

Assumptions:
1]The database is in archive log mode.
2]A solid cold backup is available

We will be greeted with the ORA-01157 and ORA-01110 messages by Oracle, if any of the datafiles are missing.

SQL> startup;
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              67111156 bytes
Database Buffers           96468992 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/oradata/burp/users01.dbf'

Steps
=====
1] Mount the database
2] Offline the datafile.
3] Open the database.
4] Restore the missing file the backup.
5] Recover the datafile.
6] Online the datafile.

ARENA
=======
Mount the database

SQL> startup mount;

Now, let’s fetch some details.

SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
         4 ONLINE  ONLINE  FILE NOT FOUND                                                             0
SQL>
SQL> select name, open_mode, checkpoint_change#, ARCHIVE_CHANGE# from v$database;

NAME      OPEN_MODE  CHECKPOINT_CHANGE# ARCHIVE_CHANGE#
--------- ---------- ------------------ ---------------
BURP      MOUNTED                508539          495261

SQL> select substr(name,1,50), recover, fuzzy, checkpoint_change# from v$datafile_header;

SUBSTR(NAME,1,50)                                  REC FUZ CHECKPOINT_CHANGE#
-------------------------------------------------- --- --- ------------------
/u01/oradata/burp/system01.dbf                     NO  NO              508539
/u01/oradata/burp/undotbs01.dbf                    NO  NO              508539
/u01/oradata/burp/sysaux01.dbf                     NO  NO              508539
                                                                            0
/u01/oradata/burp/example01.dbf                    NO  NO              508539

SQL>  select HXFIL File_num,substr(HXFNM,1,40) File_name,FHTYP Type,HXERR Validity,
FHSCN CHK, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence
from X$KCVFH;  2    3

FILE_NUM FILE_NAME                                      TYPE   VALIDITY CHK              TABLESPACE_NAME     STATUS SEQUENCE
-------- ---------------------------------------- ---------- ---------- ---------------- --------------- ---------- --------
       1 /u01/oradata/burp/system01.dbf                    3          0 508539           SYSTEM                8192       19
       2 /u01/oradata/burp/undotbs01.dbf                   3          0 508539           UNDOTBS1                 0       19
       3 /u01/oradata/burp/sysaux01.dbf                    3          0 508539           SYSAUX                   0       19
       4                                                   0          4 0                                         0        0
       5 /u01/oradata/burp/example01.dbf                   3          0 508539           EXAMPLE                  0       19

Since the database is in mounted state, we cannot ‘offline’ the tablespace, only offlining of datafile is possible.

SQL> alter database datafile 4 offline;

Database altered.

SQL>

Restore the missing datafile from the latest backup.

[spade]$cp -rp /u01/oradata/backup/users01.dbf /u01/oradata/burp/

Now, we will query to see the checkpoint of the restored file.

SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
         4 OFFLINE OFFLINE                                                                       494404 20-SEP-08
SQL>

SQL> select substr(name,1,50), recover, fuzzy, checkpoint_change# from v$datafile_header;

SUBSTR(NAME,1,50)                                  REC FUZ CHECKPOINT_CHANGE#
-------------------------------------------------- --- --- ------------------
/u01/oradata/burp/system01.dbf                     NO  NO              508539
/u01/oradata/burp/undotbs01.dbf                    NO  NO              508539
/u01/oradata/burp/sysaux01.dbf                     NO  NO              508539
/u01/oradata/burp/users01.dbf                      YES NO              494404
/u01/oradata/burp/example01.dbf                    NO  NO              508539

SQL> select HXFIL File_num,substr(HXFNM,1,40) File_name,FHTYP Type,HXERR Validity,
FHSCN CHK, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence
from X$KCVFH;  2    3

FILE_NUM FILE_NAME                                      TYPE   VALIDITY CHK              TABLESPACE_NAME     STATUS SEQUENCE
-------- ---------------------------------------- ---------- ---------- ---------------- --------------- ---------- --------
       1 /u01/oradata/burp/system01.dbf                    3          0 508539           SYSTEM                8192       19
       2 /u01/oradata/burp/undotbs01.dbf                   3          0 508539           UNDOTBS1                 0       19
       3 /u01/oradata/burp/sysaux01.dbf                    3          0 508539           SYSAUX                   0       19
       4 /u01/oradata/burp/users01.dbf                     3          0 494404           USERS                    0       12
       5 /u01/oradata/burp/example01.dbf                   3          0 508539           EXAMPLE                  0       19

As per the above query, we need to apply archive logs(or redo logs) from sequence 12 to 19 to make it consistent.

SQL> select * from v$recovery_log;

   THREAD#  SEQUENCE# TIME      ARCHIVE_NAME
---------- ---------- --------- --------------------------------------------------
         1         12 20-SEP-08 /u01/oradata/arch/1_12_665715452.dbf
         1         13 01-OCT-08 /u01/oradata/arch/1_13_665715452.dbf
         1         14 01-OCT-08 /u01/oradata/arch/1_14_665715452.dbf
         1         15 01-OCT-08 /u01/oradata/arch/1_15_665715452.dbf
         1         16 01-OCT-08 /u01/oradata/arch/1_16_665715452.dbf

SQL> select GROUP#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log;

    GROUP#    THREAD#  SEQUENCE#    MEMBERS ARC STATUS           FIRST_CHANGE#
---------- ---------- ---------- ---------- --- ---------------- -------------
         1          1         17          1 YES INACTIVE                495261
         3          1         19          1 NO  CURRENT                 508504
         2          1         18          1 YES INACTIVE                495416

Logs 12 to 16 are archived and 17,18 and 19 are still with the redo logs.

SQL> recover datafile 4;

Excerpt from alert.log
=================================================================
Tue Oct  7 01:21:11 2008
ALTER DATABASE RECOVER  datafile 4
Tue Oct  7 01:21:11 2008
Media Recovery Start
ORA-279 signalled during: ALTER DATABASE RECOVER  datafile 4  ...
Tue Oct  7 01:21:15 2008
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Tue Oct  7 01:21:15 2008
Media Recovery Log /u01/oradata/arch/1_12_665715452.dbf
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
Tue Oct  7 01:21:15 2008
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Tue Oct  7 01:21:15 2008
Media Recovery Log /u01/oradata/arch/1_13_665715452.dbf
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
Tue Oct  7 01:21:15 2008
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Tue Oct  7 01:21:15 2008
Media Recovery Log /u01/oradata/arch/1_14_665715452.dbf
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
Tue Oct  7 01:21:15 2008
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Tue Oct  7 01:21:15 2008
Media Recovery Log /u01/oradata/arch/1_15_665715452.dbf
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
Tue Oct  7 01:21:18 2008
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Tue Oct  7 01:21:18 2008
Media Recovery Log /u01/oradata/arch/1_16_665715452.dbf
Tue Oct  7 01:21:19 2008
Recovery of Online Redo Log: Thread 1 Group 1 Seq 17 Reading mem 0
  Mem# 0 errs 0: /u01/oradata/burp/redo01.log
Tue Oct  7 01:21:19 2008
Recovery of Online Redo Log: Thread 1 Group 2 Seq 18 Reading mem 0
  Mem# 0 errs 0: /u01/oradata/burp/redo02.log
Tue Oct  7 01:21:19 2008
Recovery of Online Redo Log: Thread 1 Group 3 Seq 19 Reading mem 0
  Mem# 0 errs 0: /u01/oradata/burp/redo03.log
Tue Oct  7 01:21:19 2008
Media Recovery Complete (burp)
Completed: ALTER DATABASE RECOVER    CONTINUE DEFAULT
===================================================

SQL> select * from v$recover_file;

no rows selected

SQL> select substr(name,1,50), recover, fuzzy, checkpoint_change# from v$datafile_header;

SUBSTR(NAME,1,50)                                  REC FUZ CHECKPOINT_CHANGE#
-------------------------------------------------- --- --- ------------------
/u01/oradata/burp/system01.dbf                     NO  NO              508539
/u01/oradata/burp/undotbs01.dbf                    NO  NO              508539
/u01/oradata/burp/sysaux01.dbf                     NO  NO              508539
/u01/oradata/burp/users01.dbf                      NO  NO              508538
/u01/oradata/burp/example01.dbf                    NO  NO              508539

SQL>  select HXFIL File_num,substr(HXFNM,1,40) File_name,FHTYP Type,HXERR Validity,
FHSCN CHK, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence
from X$KCVFH;  2    3

FILE_NUM FILE_NAME                                      TYPE   VALIDITY CHK              TABLESPACE_NAME     STATUS SEQUENCE
-------- ---------------------------------------- ---------- ---------- ---------------- --------------- ---------- --------
       1 /u01/oradata/burp/system01.dbf                    3          0 508539           SYSTEM                8192       19
       2 /u01/oradata/burp/undotbs01.dbf                   3          0 508539           UNDOTBS1                 0       19
       3 /u01/oradata/burp/sysaux01.dbf                    3          0 508539           SYSAUX                   0       19
       4 /u01/oradata/burp/users01.dbf                     3          0 508538           USERS                    0       19
       5 /u01/oradata/burp/example01.dbf                   3          0 508539           EXAMPLE                  0       19
SQL>

Since we have recovered the lost datafile completely, we can ONLINE the datafile and the users should be able to access data in that.

SQL>  select file#,status from v$datafile;

     FILE# STATUS
---------- -------
         1 SYSTEM
         2 ONLINE
         3 ONLINE
         4 OFFLINE
         5 ONLINE

SQL> alter database datafile 4 online;

Database altered.

SQL>  select file#,status from v$datafile;

     FILE# STATUS
---------- -------
         1 SYSTEM
         2 ONLINE
         3 ONLINE
         4 ONLINE
         5 ONLINE
SQL>

Recovery of Non System datafile [ while the database is open ]

Environment: 10gR2 on CentOS 4

Assumptions:
1]The database is in archive log mode.
2]A solid cold backup is available.
3]The database is in OPEN mode.

$sqlplus scott/tiger
SQL> insert into j_emp select * from emp;
insert into j_emp select * from emp
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/oradata/burp/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

Since the missing datafile is a non-system, the recovery can be done online.
Users that does not use this tablespace data will continue to work as normal.

Steps
=====
1]Offline the tablespace with immediate option.
2]Restore the missing datafile from the latest backup.
3]Recover the datafile.
4]Online the tablespace.

ARENA
======

SQL> select name, open_mode, checkpoint_change#, ARCHIVE_CHANGE# from v$database;

NAME      OPEN_MODE  CHECKPOINT_CHANGE# ARCHIVE_CHANGE#
--------- ---------- ------------------ ---------------
BURP      READ WRITE             506486          495256

SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
         4 OFFLINE OFFLINE FILE NOT FOUND                                                             0

SQL> select substr(name,1,50), recover, fuzzy, checkpoint_change# from v$datafile_header;

SUBSTR(NAME,1,50)                                  REC FUZ CHECKPOINT_CHANGE#
-------------------------------------------------- --- --- ------------------
/u01/oradata/burp/system01.dbf                     NO  YES             506486
/u01/oradata/burp/undotbs01.dbf                    NO  YES             506486
/u01/oradata/burp/sysaux01.dbf                     NO  YES             506486
                                                                            0
/u01/oradata/burp/example01.dbf                    NO  YES             506486

SQL>

The tablespace should be taken offline with immediate option, as just ‘offline’ would yield us the below.

SQL> alter tablespace users offline;
alter tablespace users offline
*
ERROR at line 1:
ORA-01191: file 4 is already offline - cannot do a normal offline
ORA-01110: data file 4: '/u01/oradata/burp/users01.dbf'

SQL> alter tablespace users offline immediate;

Tablespace altered.

SQL>

Restore the missing datafile to the oradata location.

cp -rp /u01/oradata/backup/users01.dbf /u01/oradata/burp/
SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
         4 OFFLINE OFFLINE                                                                       494404 20-SEP-08

SQL>  select substr(name,1,50), recover, fuzzy, checkpoint_change# from v$datafile_header;

SUBSTR(NAME,1,50)                                  REC FUZ CHECKPOINT_CHANGE#
-------------------------------------------------- --- --- ------------------
/u01/oradata/burp/system01.dbf                     NO  YES             506486
/u01/oradata/burp/undotbs01.dbf                    NO  YES             506486
/u01/oradata/burp/sysaux01.dbf                     NO  YES             506486
/u01/oradata/burp/users01.dbf                      YES NO              494404
/u01/oradata/burp/example01.dbf                    NO  YES             506486

SQL> select HXFIL File_num,substr(HXFNM,1,40) File_name,FHTYP Type,HXERR Validity,
FHSCN CHK, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence
from X$KCVFH;    2    3

FILE_NUM FILE_NAME                                      TYPE   VALIDITY CHK              TABLESPACE_NAME     STATUS SEQUENCE
-------- ---------------------------------------- ---------- ---------- ---------------- --------------- ---------- --------
       1 /u01/oradata/burp/system01.dbf                    3          0 506486           SYSTEM                8196       18
       2 /u01/oradata/burp/undotbs01.dbf                   3          0 506486           UNDOTBS1                 4       18
       3 /u01/oradata/burp/sysaux01.dbf                    3          0 506486           SYSAUX                   4       18
       4 /u01/oradata/burp/users01.dbf                     3          0 494404           USERS                    0       12
       5 /u01/oradata/burp/example01.dbf                   3          0 506486           EXAMPLE                  4       18

SQL> select GROUP#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log;

    GROUP#    THREAD#  SEQUENCE#    MEMBERS ARC STATUS           FIRST_CHANGE#
---------- ---------- ---------- ---------- --- ---------------- -------------
         1          1         17          1 YES INACTIVE                495261
         2          1         18          1 NO  CURRENT                 495416
         3          1         16          1 YES INACTIVE                495197

SQL>

We only need archive logs from sequence 12,13,14 and 15; as sequence 16,17 and 18 are still with the redo logs. Confirm that the archive logs from sequence 12,13,14 and 15 are present in the archive log destination.

SQL> recover datafile 4;

Excerpt from alert.log file
=========================================================
Tue Oct  7 00:48:01 2008
ALTER DATABASE RECOVER  datafile 4
Tue Oct  7 00:48:01 2008
Media Recovery Start
ORA-279 signalled during: ALTER DATABASE RECOVER  datafile 4  ...
Tue Oct  7 00:48:15 2008
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Tue Oct  7 00:48:15 2008
Media Recovery Log /u01/oradata/arch/1_12_665715452.dbf
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
Tue Oct  7 00:48:15 2008
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Tue Oct  7 00:48:15 2008
Media Recovery Log /u01/oradata/arch/1_13_665715452.dbf
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
Tue Oct  7 00:48:15 2008
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Tue Oct  7 00:48:15 2008
Media Recovery Log /u01/oradata/arch/1_14_665715452.dbf
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
Tue Oct  7 00:48:16 2008
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Tue Oct  7 00:48:16 2008
Media Recovery Log /u01/oradata/arch/1_15_665715452.dbf
Tue Oct  7 00:48:22 2008
Recovery of Online Redo Log: Thread 1 Group 3 Seq 16 Reading mem 0
  Mem# 0 errs 0: /u01/oradata/burp/redo03.log
Tue Oct  7 00:48:23 2008
Recovery of Online Redo Log: Thread 1 Group 1 Seq 17 Reading mem 0
  Mem# 0 errs 0: /u01/oradata/burp/redo01.log
Tue Oct  7 00:48:23 2008
Recovery of Online Redo Log: Thread 1 Group 2 Seq 18 Reading mem 0
  Mem# 0 errs 0: /u01/oradata/burp/redo02.log
Tue Oct  7 00:48:23 2008
Media Recovery Complete (burp)
Completed: ALTER DATABASE RECOVER    CONTINUE DEFAULT
===========================================================

SQL> select substr(name,1,50), recover, fuzzy, checkpoint_change# from v$datafile_header;

SUBSTR(NAME,1,50)                                  REC FUZ CHECKPOINT_CHANGE#
-------------------------------------------------- --- --- ------------------
/u01/oradata/burp/system01.dbf                     NO  YES             506486
/u01/oradata/burp/undotbs01.dbf                    NO  YES             506486
/u01/oradata/burp/sysaux01.dbf                     NO  YES             506486
/u01/oradata/burp/users01.dbf                      NO  NO              506487
/u01/oradata/burp/example01.dbf                    NO  YES             506486

SQL>

The checkpoint of all the datafile are the same, so we can ‘online’ the tablespace.

SQL> alter tablespace users online;

Tablespace altered.

SQL>  select substr(name,1,50), recover, fuzzy, checkpoint_change# from v$datafile_header;

SUBSTR(NAME,1,50)                                  REC FUZ CHECKPOINT_CHANGE#
-------------------------------------------------- --- --- ------------------
/u01/oradata/burp/system01.dbf                     NO  YES             506486
/u01/oradata/burp/undotbs01.dbf                    NO  YES             506486
/u01/oradata/burp/sysaux01.dbf                     NO  YES             506486
/u01/oradata/burp/users01.dbf                      NO  YES             507489
/u01/oradata/burp/example01.dbf                    NO  YES             506486

SQL>

Recovery of system01.dbf datafile

Environment: 10gR2 on CentOS 4

Assumptions:
1]The database is in archive log mode.
3]A good cold backup is available
3]A clean shutdown was performed.

SQL> startup;
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              67111156 bytes
Database Buffers           96468992 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/oradata/burp/system01.dbf'

SQL>

The datafile is either missing or corrupted.
Since it’s the system datafile, we cannot open the database without recovering it.
[Had it been for other datafiles we could offline that tablespace/datafile and open the database]

1] Shutdown the database
2] Restore the system01.dbf from the latest backup to the datafile location.
3] Mount the database
4] SQL>Recover datafile 1;
5] SQL>Alter database open

==ARENA==

I have shutdown the database and restored the datafile from the latest cold backup and mounted it
Lets query for the status of the DB.

 SQL> select name, open_mode, checkpoint_change#, ARCHIVE_CHANGE# from v$database;

NAME      OPEN_MODE  CHECKPOINT_CHANGE# ARCHIVE_CHANGE#
--------- ---------- ------------------ ---------------
BURP      MOUNTED                497094          495256

Query the view v$recover_file to see which file needs recovery(of course in our case its system01.dbf).

 SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR              CHANGE# TIME
---------- ------- ------- --------------- ---------- ---------
         1 ONLINE  ONLINE                     494404 20-SEP-08

Since we have restored the system01.dbf from the latest backup, we will need the archivelogs to recover
the datafile. So query the v$datafile_header to see at what checkpoint our new system01.dbf datafile is.

SQL> select substr(name,1,50), recover, fuzzy, checkpoint_change# from v$datafile_header;

SUBSTR(NAME,1,50)                                  REC FUZ CHECKPOINT_CHANGE#
-------------------------------------------------- --- --- ------------------
/u01/oradata/burp/system01.dbf                     YES NO              494404
/u01/oradata/burp/undotbs01.dbf                    NO  NO              497094
/u01/oradata/burp/sysaux01.dbf                     NO  NO              497094
/u01/oradata/burp/users01.dbf                      NO  NO              497094
/u01/oradata/burp/example01.dbf                    NO  NO              497094

Except system01.dbf all other datafiles are at checkpoint 497094, so we need to apply the archive logs and
bring system01.dbf from 494404 to 497094.

SQL> col tablespace_name format a15
SQL> col sequence format 9999
SQL> col file_num format 99

SQL> select HXFIL File_num,substr(HXFNM,1,40) File_name,FHTYP Type,HXERR Validity,
FHSCN CHK, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence
from X$KCVFH;  

FILE_NUM FILE_NAME                                      TYPE   VALIDITY CHK              TABLESPACE_NAME     STATUS SEQUENCE
-------- ---------------------------------------- ---------- ---------- ---------------- --------------- ---------- --------
       1 /u01/oradata/burp/system01.dbf                    3          0 494404           SYSTEM                8192       12
       2 /u01/oradata/burp/undotbs01.dbf                   3          0 497094           UNDOTBS1                 0       18
       3 /u01/oradata/burp/sysaux01.dbf                    3          0 497094           SYSAUX                   0       18
       4 /u01/oradata/burp/users01.dbf                     3          0 497094           USERS                    0       18
       5 /u01/oradata/burp/example01.dbf                   3          0 497094           EXAMPLE                  0       18

The system01.dbf need logs from sequence 12(CHK 494404) to sequence 18(CHK 497094) to make it consistent.

Now, check the archive logs.

SQL> select * from v$recovery_log;

   THREAD#  SEQUENCE# TIME      ARCHIVE_NAME
---------- ---------- --------- --------------------------------------------------
         1         12 20-SEP-08 /u01/oradata/arch/1_12_665715452.dbf
         1         13 01-OCT-08 /u01/oradata/arch/1_13_665715452.dbf
         1         14 01-OCT-08 /u01/oradata/arch/1_14_665715452.dbf
         1         15 01-OCT-08 /u01/oradata/arch/1_15_665715452.dbf

Logs until sequence 15 are archived and what about 16,17 and 18 ?
Let us query the v$log:

 SQL> select GROUP#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log;

    GROUP#    THREAD#  SEQUENCE#    MEMBERS ARC STATUS           FIRST_CHANGE#
---------- ---------- ---------- ---------- --- ---------------- -------------
         1          1         17          1 YES INACTIVE                495261
         3          1         16          1 YES INACTIVE                495197
         2          1         18          1 NO  CURRENT                 495416

The 16,17 and 18 are still with the redo logs. (The view shows that 16 and 17 are archived, so it should be in the archive destination)

Now we are ready to recover the system01.dbf datafile.

SQL> recover datafile 1;

Excerpt from the alert.log file

============================================================
Completed: ALTER DATABASE   MOUNT
Wed Oct  1 01:23:46 2008
ALTER DATABASE RECOVER  datafile 1
Wed Oct  1 01:23:46 2008
Media Recovery Start
ORA-279 signalled during: ALTER DATABASE RECOVER  datafile 1  ...
Wed Oct  1 01:23:49 2008
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Wed Oct  1 01:23:49 2008
Media Recovery Log /u01/oradata/arch/1_12_665715452.dbf
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
Wed Oct  1 01:23:51 2008
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Wed Oct  1 01:23:51 2008
:
:
Wed Oct  1 01:23:52 2008
Media Recovery Log /u01/oradata/arch/1_15_665715452.dbf
Wed Oct  1 01:23:53 2008
Recovery of Online Redo Log: Thread 1 Group 3 Seq 16 Reading mem 0
  Mem# 0 errs 0: /u01/oradata/burp/redo03.log
Wed Oct  1 01:23:53 2008
Recovery of Online Redo Log: Thread 1 Group 1 Seq 17 Reading mem 0
  Mem# 0 errs 0: /u01/oradata/burp/redo01.log
Wed Oct  1 01:23:53 2008
Recovery of Online Redo Log: Thread 1 Group 2 Seq 18 Reading mem 0
  Mem# 0 errs 0: /u01/oradata/burp/redo02.log
Wed Oct  1 01:23:53 2008
Media Recovery Complete (burp)
Completed: ALTER DATABASE RECOVER    CONTINUE DEFAULT
===============================================================

Oracle used the archive logs 12 to 15 and then the redo logs 16,17 and 18.

SQL> select * from v$recover_file;

no rows selected

There are no files to be recovered.

SQL> select substr(name,1,50), recover, fuzzy, checkpoint_change# from v$datafile_header;

SUBSTR(NAME,1,50)                                  REC FUZ CHECKPOINT_CHANGE#
-------------------------------------------------- --- --- ------------------
/u01/oradata/burp/system01.dbf                     NO  NO              497093
/u01/oradata/burp/undotbs01.dbf                    NO  NO              497094
/u01/oradata/burp/sysaux01.dbf                     NO  NO              497094
/u01/oradata/burp/users01.dbf                      NO  NO              497094
/u01/oradata/burp/example01.dbf                    NO  NO              497094

SQL> select HXFIL File_num,substr(HXFNM,1,40) File_name,FHTYP Type,HXERR Validity,
FHSCN CHK, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence
from X$KCVFH; 

FILE_NUM FILE_NAME                                      TYPE   VALIDITY CHK              TABLESPACE_NAME     STATUS SEQUENCE
-------- ---------------------------------------- ---------- ---------- ---------------- --------------- ---------- --------
       1 /u01/oradata/burp/system01.dbf                    3          0 497093           SYSTEM                8192       18
       2 /u01/oradata/burp/undotbs01.dbf                   3          0 497094           UNDOTBS1                 0       18
       3 /u01/oradata/burp/sysaux01.dbf                    3          0 497094           SYSAUX                   0       18
       4 /u01/oradata/burp/users01.dbf                     3          0 497094           USERS                    0       18
       5 /u01/oradata/burp/example01.dbf                   3          0 497094           EXAMPLE                  0       18

All the datafile seems to be consistent.
Now we can ‘open’ the database.

SQL> alter database open; 

SQL>select HXFIL File_num,substr(HXFNM,1,40) File_name,FHTYP Type,HXERR Validity,
FHSCN CHK, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence
from X$KCVFH;

FILE_NUM FILE_NAME                                      TYPE   VALIDITY CHK              TABLESPACE_NAME     STATUS SEQUENCE
-------- ---------------------------------------- ---------- ---------- ---------------- --------------- ---------- --------
       1 /u01/oradata/burp/system01.dbf                    3          0 497095           SYSTEM                8196       18
       2 /u01/oradata/burp/undotbs01.dbf                   3          0 497095           UNDOTBS1                 4       18
       3 /u01/oradata/burp/sysaux01.dbf                    3          0 497095           SYSAUX                   4       18
       4 /u01/oradata/burp/users01.dbf                     3          0 497095           USERS                    4       18
       5 /u01/oradata/burp/example01.dbf                   3          0 497095           EXAMPLE                  4       18