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

October 10, 2008

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>

Comments (4)

4 Comments »

its really good work but i’ve any problem for my datafile undotbs01.dbf
this is the message :
ORA-01113 : file 2 needs media recovery
ORA-01110 : data file 2: ‘/u02/oracle/oradata/SIMDB/undotbs01.dbf’
please help me…
thanks

regards,

adnan

Comment by adnan — February 24, 2009 @ 6:42 pm | Reply
- Hello Adnan,
  
  Your undo datafile is an old one, so you need to recover it.
  sql>select * from v$recover_file;
  sql> recover datafile ;
  
  Recovery of undo tablespace should be same as any other non_system_datafile recovery also check this
  
  Comment by John Jacob — February 24, 2009 @ 10:29 pm | Reply
[...] If the database is in Archivelog mode, the recovery of an Undo tablespace is same as that of any Non-System tablespace,but how to recover if the database is in Non Archivelog Mode [...]

Pingback by Recovery of UNDO tablespace in a Non Archive Log mode Database « My confrontations with oracle — April 6, 2009 @ 2:47 pm | Reply
[...] Recovery of Non System datafile [ while the database is closed ] [...]

Pingback by Index « My confrontations with oracle — April 6, 2009 @ 2:49 pm | Reply

RSS feed for comments on this post. TrackBack URI

My confrontations with oracle

October 10, 2008

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

4 Comments »

Leave a comment

Archives

Blog Stats

Meta

Recent Posts

Blogroll

My Visitors

October 2008
S	M	T	W	T	F	S
« Jul				Nov »
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31