ORA-01157 tips

Oracle Database Tips by Donald BurlesonDecember 15, 2015

Question: I'm getting the ORA-01157 and ORA-01110 when trying to bring my tablespaces online:

SQL> alter tablespace example online;
alter tablespace example online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF'

How do I fix this ORA-01157 error?

Answer: First, the Oracle docs note this on the ORA-01157 error:

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

Cause: The background process was either unable to find one of the data files or failed to lock it because the file was already in use.

The database will prohibit access to this file but other files will be unaffected. However the first instance to open the database will need to access all online data files. Accompanying error from the operating system describes why the file could not be identified.

Action: Have operating system make file available to database. Then either open the database or do ALTER SYSTEM CHECK DATAFILES.

As we see, the ORA-01157 is caused by a locking issue with the database writer (DBWR) background process. During a recovery, this can be caused by a unopened data files (i.e. database mounted but not open), a missing file, a permission problem in the file (e.g. no write permissions 770 on the files owned by Oracle).

If the background process is unable to reach a data file, or is unable to lock it because it is in use , ORA-01157 is thrown because the database does not allow access. The other files will not be affected, but it is important to know that opening the database using the first instance will need to use online data files. there should be other errors which appear along with ORA-01157 to aid in resolving the problem.

Straightening out ORA-01157 consists of making the files of the database available, and either regularly opening the database, or use ALTER SYSTEM CHECK DATAFILES .

If you like Oracle tuning, you might enjoy my book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata? Oracle technology is changing and we strive to update our BC Oracle support information. If you find an error or have a suggestion for improving our content, we would appreciate your feedback. Just e-mail:

and include the URL for the page.

Burleson Consulting

The Oracle of Database Support
Oracle Performance Tuning
Remote DBA Services

��