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

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

ORA-01110: data file 5: ‘D:\ORACLE11204\ORADATA\PEGA\EXAMPLE01.DBF’

Error occurred due to DBWR process not able to lock the file for its use during startup of database.

SQL> alter database open;
alter database open
ERROR at line 1:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: 'D:\ORACLE11204\ORADATA\PEGA\EXAMPLE01.DBF'

So, We need to check which process caused lock on the datafiles. if we did not found it may be corrupted we need to restore from backup.

SQL> alter database datafile 5 offline;
Database altered.

SQL> alter database open;
Database altered.

Recover and restore the datafile 5 though rman process:

RMAN> restore datafile 5;

Starting restore at 04-FEB-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to D:\ORACLE11204\ORADATA\PEGA\EXAMPLE01.DBF
channel ORA_DISK_1: reading from backup piece D:\BACKUP\FULL_DB_902883257_681P1
channel ORA_DISK_1: piece handle=D:\BACKUP\FULL_DB_902883257_681P1 tag=TAG20160204T005417
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 04-FEB-16

RMAN> recover datafile 5;
Starting recover at 04-FEB-16
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 04-FEB-16

Open the datafile in read and write mode.

RMAN> sql "alter database datafile 5 online";
sql statement: alter database datafile 5 online

Note: If you have downtime then do once clean shutdown and startup database
Take fresh backup of database after datafile 5 online.


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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.