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;
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;
No comments:
Post a Comment