Oracle 无法打开数据库: ORA-01157: 无法识别/锁定数据文件

首先运行命令startup nomount，done，然后运行查询，alter database mount，done，也打开控制文件，然后以以下方式运行查询:
ALTER DATABASE OPEN，给出以下问题:
SQL> alter database open;
alter database open
*
第1行的错误:
ORA-01157: 无法识别/锁定数据文件4-参见DBWR跟踪文件
ORA-01110: 数据文件4: '/u01/app/oracle/orcl/users01.dbf'

Oracle无法访问该文件，要么是因为它不存在，要么是因为其他地方出了问题!

MOS note 184327.1讨论原因和解决方案:


Common Causes and Solutions for ORA-1157

Note: Throughout this note we refer to "backups" but if you have a valid physical standby database
you may also use the standby database's datafiles to recover the primary database.

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:

A 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.

STARTUP MOUNT;

2. Offline drop the datafile.

ALTER DATABASE DATAFILE 'full_path_file_name' OFFLINE DROP;

3. If the database is at mount, open it.

ALTER DATABASE OPEN;

4. Drop the user tablespace.

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.

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.


B. 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.

STARTUP MOUNT;

3. Issue the following query:

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:

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 a.


5. Recover the datafile:

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 point, open it.

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:

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:

alter database tempfile '/Oracle/oradata/temp2_01.tmp' drop;

select tablespace_name, file_name from dba_temp_files;

alter tablespace temp2 add tempfile '/Oracle/oradata/temp2_01.tmp' size 5m;


还有其他一些可能性。如果以上内容没有帮助，请转到我的Oracle支持并查看doc 184327.1