Common Causes and Solutions for ORA-1157
SQL> startup
ORACLE instance started.
Total System Global Area 202868968 bytes
Fixed Size 731368 bytes
Variable Size 167772160 bytes
Database Buffers 33554432 bytes
Redo Buffers 811008 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: ‘/opt/oracle/product/9.2/dbs/datafile.dbf’
1. The datafile does exist, but Oracle cannot find it.
2. The datafile does not exist or unsuable by Oracle. The datafile has been physically removed or damaged to an extent that Oracle cannot recognize it anymore.
a. Recreate the tablespace that the datafile belongs to.
1. If the database is down, mount it. SVRMGR> STARTUP MOUNT PFILE=’<location_of_pfile>’;
2. Offline drop the datafile. SVRMGR> ALTER DATABASE DATAFILE ‘<full_path_file_name>’ OFFLINE DROP;
3. If the database is at mount point, open it. SVRMGR> ALTER DATABASE OPEN;
4. Drop the user tablespace. SVRMGR> DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS;
Note: The users can stop with this step if they do not want the tablespace anymore in the database.
5. Recreate the tablespace. SVRMGR> CREATE TABLESPACE <tablespace_name> DATAFILE ‘<datafile_full_path_name’> SIZE <required_size>;
6. Recreate all the previously existing objects in the tablespace.
b. Recover the datafile using normal recovery procedures.
1. Restore the lost file from a backup.
2. If the database is down, mount it.
SQL>STARTUP MOUNT PFILE=<location_of_pfile>;
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. issue the query:
SQL> SELECT FILE#, CHANGE# FROM V$RECOVER_FILE;
This query will list the files needing to recover.
5. use rman to restore and recover the datafile.
3. Operating Systems (OS) 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:
SQL> 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/V901/temp2_01.tmp’
SQL> alter database tempfile ‘/Oracle/oradata/V901/temp2_01.tmp’ drop;
SQL> select tablespace_name, file_name from dba_temp_files;
SQL> alter tablespace temp2
add tempfile ‘/Oracle/oradata/V901/temp2_01.tmp’ size 5m;
The article is mainly from metalink Note:184327.1