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>
Advertisements

3 thoughts on “Recovery of Non System datafile [ while the database is open ]

  1. Pingback: Recovery of non system datafile [ without backup ] « My confrontations with oracle

  2. Pingback: Index « My confrontations with oracle

  3. Following steps I used and worked perfectly
    alter tablespace AMBDATA offline immediate;
    recover datafile 75;
    alter database rename file ‘/binaries/app/oracle/product/10201/dbs/MISSING00075’ to ‘/oradata/amb/db1/ambdata01.dbf’;
    recover datafile 75;
    alter tablespace AMBDATA online;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s