Oracle Error: ORA-1157
An ORA-01157 is issued whenever Oracle attempts to access a file but cannot find or lock the file.
Error Explanation:
01157, 00000, "cannot identify/lock data file %s - 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 the operating system make the file available to the database. Then either open the database or do ALTER SYSTEM CHECK DATAFILES.
ORA-01157 errors are usually followed by ORA-01110 and possibly an Oracle operating system layer error such as ORA-07360. A DBWR trace file is generated in the background_dump_dest directory. For Example, on Solaris platform, the following errors will appear:
ORA-01157: cannot identify/lock data file 19 - see DBWR trace file ORA-01110: data file 19: '/app/Oracle/oradata/users02.dbf'
From the DBWR trace file:
ORA-01157: cannot identify/lock data file 19 - see DBWR trace file ORA-01110: data file 19: '/app/Oracle/oradata/users02.dbf' ORA-27037: unable to obtain file status SVR4 Error: 2: No such file or directory Additional information: 3
Common Causes and Solutions for ORA-1157
1. The datafile does exist, but Oracle cannot find it.
The datafile may have been renamed at the operating system level, moved to a different directory or disk drive either intentionally or unintentionally.
In this case, restore and recover the datafile or move the datafile to its original name.
2. The datafile does not exist or is unusable by Oracle. The datafile has been physically removed or damaged to an extent that Oracle cannot recognize it anymore.
For example, the datafile might be truncated or overwritten, in which case ORA-27046 will accompany ORA-1157 error. For example:
ORA-27046: file size is not a multiple of logical block size
In this case, the user has two options:
1. Recreate the tablespace that the datafile belongs to
This option is best suited for USERS, INDEX, TEMPORARY tablespaces. It is also recommended for UNDO tablespaces if the database had been SHUTDOWN CLEANLY so that no active transactions are there in the rollback segments of this tablespace. If the tablespace is SYSTEM tablespace, then this amounts to recreating or rebuilding the database. This method is best suited for temporary tablespaces (since they do not contain important data), but can be used for USERS tablespaces and INDEXES tablespaces.
This method would be helpful wherein reasonably recent exports of the objects in the tablespace are available, or that the tables in the tablespace can be repopulated by running a script or program, loading the data through SQL*Loader, etc. The steps involved are:
1. If the database is down, mount it.
SQL> STARTUP MOUNT;
2. Offline drop the datafile.
SQ> ALTER DATABASE DATAFILE 'full_path_file_name' OFFLINE DROP;
3. If the database is at mount, open it.
SQL> ALTER DATABASE OPEN;
4. Drop the user tablespace.
SQL> DROP TABLESPACE tablespace_name INCLUDING CONTENTS;
5. Recreate the tablespace.
SQL> CREATE TABLESPACE tablespace_name DATAFILE 'datafile_full_path_name' SIZE required_size;
6. Recreate all the previously existing objects in the tablespace. This can be done using the creation scripts for the objects in that tablespace or using the recent export dump available for that tablespace objects.
2. Recover the datafile using normal recovery procedures
This option is best suited for READ ONLY tablespaces and for USERS, INDEX tablespaces where recreating is not a feasible option. If the tablespace is of type UNDO, then this is the method to be used if the database was not SHUTDOWN CLEANLY. (that is if shutdown abort had been used or the database had crashed). If the tablespace is SYSTEM, then this is the recommended method, if there are backups and archivelogs are available. If the database is in NOARCHIVELOG mode, then you can recover only if the required changes are present in the ONLINE redologs.
In many situations, recreating the user tablespace is impossible or too laborious. The solution then is to restore the lost datafile from a backup and do media recovery on it. If the database is in NOARCHIVELOG mode, you will only succeed in recovering the datafile if the redo to be applied to the datafile is within the range of the online logs.
This method would be ideal for READ ONLY tablespaces. If the tablespace was not switched to READ-WRITE after backup was taken and if the tablespace was READ ONLY at the time of backup, then recovery is just restoring the backup of this tablespace. These are the steps:
1. Restore the lost file from a backup.
2. If the database is down, mount it.
SQL> STARTUP MOUNT;
3. Issue the following query:
SQL> SELECT V1.GROUP#, MEMBER, SEQUENCE#, FIRST_CHANGE# FROM V$LOG V1, V$LOGFILE V2 WHERE V1.GROUP# = V2.GROUP#;
This will list all your online redolog files and their respective sequence and first change numbers.
4. If the database is in NOARCHIVELOG mode, issue the query:
SQL> SELECT FILE#, CHANGE# FROM V$RECOVER_FILE;
If the CHANGE# is GREATER than the minimum FIRST_CHANGE# of your logs, the datafile can be recovered. Just keep in mind that all the logs to applied will be online logs, and move on to step 5. If the CHANGE# is LESS than the minimum FIRST_CHANGE# of your logs, the file cannot be recovered. Your options at this point would be to restore the most recent full backup (and thus lose all changes to the database since) or recreate the tablespace as explained in scenario 1.
5. Recover the datafile:
SQL> RECOVER DATAFILE 'full_path_file_name';
6. Confirm each of the logs that you are prompted for until you receive the message “Media Recovery Complete”. If you are prompted for a non-existing archived log, Oracle probably needs one or more of the online logs to proceed with the recovery. Compare the sequence number referenced in the ORA-280 message with the sequence numbers of your online logs. Then enter the full path name of one of the members of the redo group whose sequence number matches the one you are being asked for. Keep entering online logs as requested until you receive the message “Media Recovery Complete” .
7. If the database is at mount stage, open it.
Operating Systems Tempfiles missing
When using TEMPORARY tablespaces with tempfiles, the absence of the tempfile at the OS level can cause ORA-1157. Since Oracle does not checkpoint tempfiles, the database can be opened even with missing tempfiles.
The solution in this case would be to drop the logical tempfile and add a new one. For example:
select * from dba_objects order by object_name; select * from dba_objects order by object_name; * ERROR at line 1: ORA-01157: cannot identify/lock data file 1026 - see DBWR trace file ORA-01110: data file 1026: '/Oracle/oradata/temp2_01.tmp'
Solution:
sql> alter database tempfile '/Oracle/oradata/temp2_01.tmp' drop; sql> select tablespace_name, file_name from dba_temp_files; sql> alter tablespace temp2 add tempfile '/Oracle/oradata/temp2_01.tmp' size 5m;