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
Advertisements

5 thoughts on “Recovery of system01.dbf datafile

  1. Pingback: Index « My confrontations with oracle

  2. Hi Guru,
    I Got this error…ORA-01157: cannot identify/lock data file 1 – see DBWR trace file
    ORA-01110: data file 1: ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDOV\SYSTEM01.DBF’
    But I am unable to recover my system.dbf file.

    Actual My File is in : ‘E:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDOV\SYSTEM01.DBF’

    I want to rename ..How i can do it… as i can only mount the database

  3. Pingback: Memindahkan Database Oracle dari server A ke server B dengan directory yang berbeda « Oracle, Web, Script, SQLserver, Tips & Trick

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