Scribd
Upload
211 views

Ora 01157 Cannot Identifylock Data File

The document discusses troubleshooting an Oracle database error where a data file is missing. It describes checking the file location, and provides two solutions: making the file offline and dropping the tablespace, or restoring the file from backup and recovering it using RMAN.

Uploaded by

kamakom78
Copyright
© © All Rights Reserved
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
211 views

Ora 01157 Cannot Identifylock Data File

The document discusses troubleshooting an Oracle database error where a data file is missing. It describes checking the file location, and provides two solutions: making the file offline and dropping the tablespace, or restoring the file from backup and recovering it using RMAN.

Uploaded by

kamakom78
Copyright
© © All Rights Reserved
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 6

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

Few days ago when I was trying to start my database , I faced this error. PFB details:SQL> startup
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size
1250428 bytes
Variable Size
209718148 bytes
Database Buffers
394264576 bytes
Redo Buffers
7135232 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\AMIT01.DBF'
Cause :I checked the following location( C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT) and
found out that the file AMIT01.dbf was not present.
Solution :There are two solutions to the above problem.
1. If data in this file is not that much important that remove this file information from
database and open your database.
2. If data in this file is important and you have backup available for this file and if your
database is in archivelog mode than you can recover the data in this file upto the last
commit.But if database is in NOARCHIVELOG mode then restoration of data possible
only upto the last backup.
Solution 1 :- Make the file Offline and then open your database after that drop that
tablespace.
SQL> set lin 400
SQL> col name for a55
SQL> select a.file#,a.name as FILE_NAME,b.name as TABLESPACE_NAME ,status from
v$datafile a ,v$tablespace b where a.ts#=b.ts#;
FILE# FILE_NAME
TABLESPACE_NAME
STATUS
---------- -------------------------------------------------------------------------------------------------------------------------------------------1 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\SYSTEM01.DBF
SYSTEM
2 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\UNDOTBS01.DBF
ONLINE
3 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\SYSAUX01.DBF
ONLINE

SYSTEM
UNDOTBS1
SYSAUX

4 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\USERS01.DBF
ONLINE
5 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\AMIT01.DBF
ONLINE

USERS
AMIT

SQL> drop tablespace AMIT including contents and datafiles;


drop tablespace AMIT including contents and datafiles
*
ERROR at line 1:
ORA-01109: database not open
SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\AMIT01.DBF'
offline drop;
Database altered.
SQL> alter database open;
Database altered.
SQL> select a.file#,a.name as FILE_NAME,b.name as TABLESPACE_NAME ,status from
v$datafile a ,v$tablespace b where a.ts#=b.ts#;
FILE# FILE_NAME
TABLESPACE_NAME
STATUS
---------- -------------------------------------------------------------------------------------------------------------------------------------------1 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\SYSTEM01.DBF
SYSTEM
SYSTEM
2 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\UNDOTBS01.DBF UNDOTBS1
ONLINE
3 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\SYSAUX01.DBF
SYSAUX
ONLINE
4 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\USERS01.DBF
USERS
ONLINE
5 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\AMIT01.DBF
AMIT
OFFLINE
SQL> drop tablespace AMIT including contents and datafiles;
Tablespace dropped.
SQL> select open_mode from v$database;
OPEN_MODE
---------READ WRITE
Solution 2 :- Restore and recover the lost file through RMAN
C:\Documents and Settings\amit.rath>rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Feb 26 12:43:18 2013


Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: AMIT (DBID=2171747492, not open)
RMAN> list backup of datafile 5;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size
Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------19
Full 152.00K DISK
00:00:23 26-FEB-13
BP Key: 19 Status: AVAILABLE Compressed: YES Tag: TAG20130226T123606
Piece Name:
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\AMIT\BACKUPSET\2013_02_26\
O1_MF_NNNDF_TAG20130226T123606_8LRQPZKC_.BKP
List of Datafiles in backup set 19
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ---5
Full 1028343 26-FEB-13 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\AMIT01.DBF
RMAN> restore datafile 5 preview;
Starting restore at 26-FEB-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
List of Backup Sets
===================
BS Key Type LV Size
Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------19
Full 152.00K DISK
00:00:23 26-FEB-13
BP Key: 19 Status: AVAILABLE Compressed: YES Tag: TAG20130226T123606
Piece Name:
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\AMIT\BACKUPSET\2013_02_26\
O1_MF_NNNDF_TAG20130226T123606_8LRQPZKC_.BKP
List of Datafiles in backup set 19
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ---5
Full 1028343 26-FEB-13 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\AMIT01.DBF
using channel ORA_DISK_1
List of Backup Sets
===================

BS Key Size
Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------26
2.50K
DISK
00:00:01 26-FEB-13
BP Key: 26 Status: AVAILABLE Compressed: YES Tag: TAG20130226T123711
Piece Name:
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\AMIT\BACKUPSET\2013_02_26\
O1_MF_ANNNN_TAG20130226T123711_8LRQS0JY_.BKP
List of Archived Logs in backup set 26
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- --------1 20
1028338 26-FEB-13 1028364 26-FEB-13
Media recovery start SCN is 1028343
Recovery must be done beyond SCN 1028343 to clear data files fuzziness
Finished restore at 26-FEB-13
RMAN> restore datafile 5;
Starting restore at 26-FEB-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\AMIT01.DBF
channel ORA_DISK_1: reading from backup piece
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\AMIT\BACKUPSET\2013_02_26\
O1_MF_NNNDF_TAG20130226T123606_8LRQPZKC_.BKP
channel ORA_DISK_1: restored backup piece 1
piece
handle=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\AMIT\BACKUPSET\2013_
02_26\O1_MF_NNNDF_TAG20130226T123606_8LRQPZKC_.BKP
tag=TAG20130226T123606
channel ORA_DISK_1: restore complete, elapsed time: 00:00:27
Finished restore at 26-FEB-13
RMAN> recover datafile 5 ;
Starting recover at 26-FEB-13
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:01:02
Finished recover at 26-FEB-13
RMAN> exit
Recovery Manager complete.
C:\Documents and Settings\amit.rath>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Feb 26 12:46:12 2013


Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select open_mode from v$database;
OPEN_MODE
---------MOUNTED
SQL> alter database open;
Database altered.
SQL> set lin 400
SQL> col name for a55
SQL> select a.file#,a.name as FILE_NAME,b.name as TABLESPACE_NAME ,status from
v$datafile a ,v$tablespace b where a.ts#=b.ts#;
FILE# FILE_NAME
TABLESPACE_NAME
STATUS
---------- -------------------------------------------------------------------------------------------------------------------------------------------1 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\SYSTEM01.DBF
SYSTEM
SYSTEM
2 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\UNDOTBS01.DBF UNDOTBS1
ONLINE
3 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\SYSAUX01.DBF
SYSAUX
ONLINE
4 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\USERS01.DBF
USERS
ONLINE
5 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\AMIT01.DBF
AMIT
ONLINE
NOTE :- If database is in NOARCHIVELOG mode and you want to restore and recover
your lost file, then after restoration of file from backup if recovery is possible through the
redo logs currently available then you can open your database but if while in the process
of recovery it asks for archive logs then you cannot open your database, you have to
restore your full database from the backup to open your database. For Recovery upto
last commit keep your database in ARCHIVELOG mode.
I hope this article helped you.

Regards,
Amit Rath

You might also like