Thursday, September 29, 2011

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

SQL> startup
ORACLE instance started.

Total System Global Area 8551575552 bytes
Fixed Size                  2198344 bytes
Variable Size            1090520248 bytes
Database Buffers         7449083904 bytes
Redo Buffers                9773056 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10: '/oracle/data/data01/RAIDTEST/users02.dbf'


It could be that an OS process is locking the file and because of this oracle is throwing this error, in this case a System reboot would fix the issue.
Any how I followed the action plan as below:.

ls –al /oracle/data/data01/RAIDTEST/users02.dbf

/oracle/data/data01/RAIDTEST/users02.dbf not found

ls –ltr /oracle/data/data01/RAIDTEST/

$ ls -lrt /oracle/data/data01/RAIDTEST/
total 163308512
-rw-r-----   1 oracle     oinstall   9437192192 Aug 19 16:18 temp01.dbf
-rw-r-----   1 oracle     oinstall   20971528192 Aug 19 16:27 raid_TEST_t3_psys_01.dbf
-rw-r-----   1 oracle     oinstall   10485768192 Aug 19 16:28 raid_TEST_i3_psys_01.dbf
-rw-r-----   1 oracle     oinstall   10485768192 Sep 29 10:52 raid_TEST_l02.dbf
-rw-r-----   1 oracle     oinstall   6815752192 Sep 29 10:52 raid_TEST_i02.dbf
-rw-r-----   1 oracle     oinstall   314580992 Sep 29 10:52 raid_TEST_i01.dbf
-rw-r-----   1 oracle     oinstall   11534344192 Sep 29 10:52 raid_TEST_t02.dbf
-rw-r-----   1 oracle     oinstall   524296192 Sep 29 10:52 raid_TEST_t01.dbf
-rw-r-----   1 oracle     oinstall   209723392 Sep 29 10:52 users01.dbf
-rw-r-----   1 oracle     oinstall   20971528192 Sep 29 10:52 undotbs01.dbf
-rw-r-----   1 oracle     oinstall   545267712 Sep 29 10:52 sysaux01.dbf
-rw-r-----   1 oracle     oinstall   744497152 Sep 29 10:52 system01.dbf
-rw-r-----   1 oracle     oinstall   9748480 Sep 29 11:52 control01.ctl

select DISTINCT segment_name,segment_type from dba_extents where file_id = (select file_id from dba_data_files where FILE_name = '/oracle/data/data01/RAIDTEST/users02.dbf')

ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only

After looking at the above outputs, I found that the data file does not exist and there are no users in the file.
So I did following and it fixed the issue.

SQL>startup mount;

SQL>alter database datafile 10 OFFLINE DROP;

SQL>alter database open;

2 comments:

  1. Thanks a lot!!! I found a lot of posts with a lot of steps or complex operations that I did not need. Yours was great and just what I needed!

    ReplyDelete