ORA-01110 data file 1: system01.dbf recovery

Hello DBA-Mates,

We are back after a long time with ORA-01110: data file 1: '/u01/oracle/oradata/ORA-DATA3/system01.dbf'. Got so many messages and comments as why stopped writing but we didn’t stopped , there was some bad time which slowly passing away and we came back.

Thank you all for your supporting and loving.

Recently, one of our friend faced this below issue and they were not able to recover the database. So, friends before playing with database take the backup always either online or offline.

Here, we have just shown as if we missed system datafile or deleted, how can we recover it and start the database.

1.

Take the offline backup.

SQL> select name,open_mode,log_mode,flashback_on from v$database;

NAME      OPEN_MODE  LOG_MODE     FLASHBACK_ON

--------- ---------- ------------ ------------------

ORA-DATA3      READ WRITE ARCHIVELOG   NO

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     2

Next log sequence to archive   4

Current log sequence           4

******************************************

Make some transactions inorder to generate archive logs

(if account is locked then):

SQL> alter user scott identified by tiger account unlock;

SQL> conn scott/tiger

Connected.

SQL> create table student(name varchar2(50)) tablespace users;

Table created.

SQL> begin

  2  for i in 1..300000 loop

  3  insert into student values('10g DBA Training and R12 Apps DBA Training student');

  4  end loop;

  5  commit;

  6  end;

  7  /

*************************************************************

sql>conn /as sysdba;

SQL>  select * from v$recover_file;

no rows selected

SQL> column name format a40

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

NAME                                          FILE# STATUS

---------------------------------------- ---------- -------

/u01/oracle/oradata/ORA-DATA3/system01.dbf             1 SYSTEM

/u01/oracle/oradata/ORA-DATA3/undotbs01.dbf            2 ONLINE

/u01/oracle/oradata/ORA-DATA3/sysaux01.dbf             3 ONLINE

/u01/oracle/oradata/ORA-DATA3/users01.dbf              4 ONLINE

SIMULATE THE FAILURE OF SYSTEM DATAFILE LOSS

[oracle@sku ORA-DATA3]$ clear

[oracle@sku ORA-DATA3]$ pwd

/u01/oracle/oradata/ORA-DATA3

[oracle@sku ORA-DATA3]$ ls -l syst*

-rw-r----- 1 oracle oinstall 503324672 Sep 14 23:37 system01.dbf

[oracle@sku ORA-DATA3]$ ls

control01.ctl      control04.ctl      redo01.log   redo05a.log  sysaux01.dbf

control01.ctl_old  control04.ctl_old  redo02a      redo05.log   system01.dbf

control02.ctl      example01.dbf      redo02a.log  redo06a.log  temp01.dbf

control02.ctl_old  indx01.dbf         redo02.log   redo06.log   undotbs01.dbf

control03.ctl      redo01a            redo03a.log  redo07a.log  users01.dbf

control03.ctl_old  redo01a.log        redo03.log   redo07.log

[oracle@sku ORA-DATA3]$ mv system01.dbf system01.dbf_old

[oracle@sku ORA-DATA3]$

*********************************************

SQL> shutdown immediate

ORA-01116: error in opening database file 1

ORA-01110: data file 1: '/u01/oracle/oradata/ORA-DATA3/system01.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

SQL> shutdown abort

ORACLE instance shut down.

SQL>

SQL> startup

ORACLE instance started.

Total System Global Area 1090519040 bytes

Fixed Size                  2020128 bytes

Variable Size             301993184 bytes

Database Buffers          771751936 bytes

Redo Buffers               14753792 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '/u01/oracle/oradata/ORA-DATA3/system01.dbf'

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1090519040 bytes

Fixed Size                  2020128 bytes

Variable Size             301993184 bytes

Database Buffers          771751936 bytes

Redo Buffers               14753792 bytes

Database mounted.

SQL>

SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_

---------- ------- -------

ERROR                                                                CHANGE#

----------------------------------------------------------------- ----------

TIME

---------

         1 ONLINE  ONLINE

                                                                      535300

15-SEP-11

Restore the system01.dbf from Offline Backup

[oracle@sku ORA-DATA3]$ pwd

/u01/oracle/oradata/ORA-DATA3

$cp /u01/oracle/offline_backup/system01.dbf .   ##(This will also work if you restore system01.dbf from online backup)

[oracle@sku ORA-DATA3]$ ls -l *dbf

-rw-r----- 1 oracle oinstall 104865792 Sep 14 23:50 example01.dbf

-rw-r----- 1 oracle oinstall   4202496 Sep 14 23:50 indx01.dbf

-rw-r----- 1 oracle oinstall 251666432 Sep 14 23:50 sysaux01.dbf

-rw-r----- 1 oracle oinstall 503324672 Sep 14 23:56 system01.dbf

-rw-r----- 1 oracle oinstall  20979712 Sep 14 20:12 temp01.dbf

-rw-r----- 1 oracle oinstall  26222592 Sep 14 23:50 undotbs01.dbf

-rw-r----- 1 oracle oinstall  10493952 Sep 14 23:50 users01.dbf

******************

[oracle@sku 2011_09_14]$ pwd

/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_15

[oracle@sku 2011_09_15]$ ls -alrt

total 33708

drwxr-x--- 3 oracle oinstall     4096 Sep 15 02:47 ..

-rw-r----- 1 oracle oinstall 34460160 Sep 15 02:47 o1_mf_1_1_7726hlvy_.arc

-rw-r----- 1 oracle oinstall     1024 Sep 15 02:48 o1_mf_1_2_7726j9bo_.arc

drwxr-x--- 2 oracle oinstall     4096 Sep 15 02:48 .

*************************************

   

SQL> recover database;

ORA-00279: change 680758 generated at 09/14/2011 20:31:38 needed for thread 1

ORA-00289: suggestion :

/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_15/o1_mf_1_9_%u_.arc

ORA-00280: change 680758 for thread 1 is in sequence #9

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

AUTO

ORA-00279: change 681408 generated at 09/14/2011 20:45:01 needed for thread 1

ORA-00289: suggestion :

/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_15/o1_mf_1_10_%u_.arc

ORA-00280: change 681408 for thread 1 is in sequence #10

ORA-00278: log file

'/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_14/o1_mf_1_9_771kpqbq_.

arc' no longer needed for this recovery

ORA-00279: change 681427 generated at 09/14/2011 20:53:03 needed for thread 1

ORA-00289: suggestion :

/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_15/o1_mf_1_11_%u_.arc

ORA-00280: change 681427 for thread 1 is in sequence #11

ORA-00278: log file

'/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_14/o1_mf_1_10_771kpqrc_

.arc' no longer needed for this recovery

ORA-00279: change 684925 generated at 09/14/2011 22:00:07 needed for thread 1

ORA-00289: suggestion :

/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_15/o1_mf_1_12_%u_.arc

ORA-00280: change 684925 for thread 1 is in sequence #12

ORA-00278: log file

'/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_14/o1_mf_1_11_771onh9m_

.arc' no longer needed for this recovery

Log applied.

Media recovery complete.

SQL> alter database open;

Database altered.

SQL> select * from v$recover_file;

no rows selected

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

     FILE# NAME                                     STATUS

---------- ---------------------------------------- -------

         1 /u01/oracle/oradata/ORA-DATA3/system01.dbf    SYSTEM

         2 /u01/oracle/oradata/ORA-DATA3/undotbs01.dbf   ONLINE

         3 /u01/oracle/oradata/ORA-DATA3/sysaux01.dbf    ONLINE

         4 /u01/oracle/oradata/ORA-DATA3/users01.dbf     ONLINE

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

IF

Database mounted.

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '/opt/oracle/oradata/orcl/system01.dbf'

SQL> recover datafile 1;

Media recovery complete.

SQL> alter database open;

Database altered.

SQL>

Some more useful links:

Sharing: A Company's Experience

How to check and get details about patch applied

Important DBA Scripts

R12.2 ADOP Patching Phases Finalize and Cutover

TKPROF Concepts with Syntax and Real time example

Why We are not Rich