Shulou(Shulou.com)06/01 Report--

How to solve ORA-01157 errors, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

ORA-1157 error Resolution Manual

I. error description

ORA-1157, "cannot identify/lock data file% s-see DBWR trace file"

The cause:

Because the data files are already in use, the background process of the database can not find the corresponding data files or lock the corresponding data files, so that the database will prohibit access to these data files while other data files will not be affected. With this error, the operating system will indicate which data file is not recognized.

ORA-01157 errors generally occur with ORA-01110 errors, often at the operating system level, such as ORA-07360, and a DBWR trace file is generated in the background_dump_dest directory. For example, on Solaris's platform, the following error message is displayed:

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

ORA-01110: data file 5:'/ export/home/Oracle/oradata/817/users01.dbf'

Then look at the contents of the trace file of DBWR, and you will have the following:

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

ORA-01110: data file 5:'/ export/home / Oracle/oradata/817/users01.dbf'

ORA-27037: unable to obtain file status

SVR4 Error: 2: No such file or directory

Additional information: 3

Here are a few aspects that can easily lead to ORA-1157 errors:

II. The causes and solutions of ORA-1157 errors

If you are using Oracle9i, use SQLPLUS instead of SVRMGRL to execute the following command.

1. The data file exists, but Oracle does not recognize it.

This situation may be that the data file has been renamed or moved to a new partition or location at the operating system level, which is relatively simple. You just need to restore the data file to the original data file name or rename the data file to a new location / directory to solve the problem.

Method of renaming data files to a new location / directory:

a. The database is open

1) check which other data files are contained in the tablespace in which the data file resides, and execute the following query:

SELECT FILE_NAME, STATUS FROM DBA_DATA_FILES

WHERE TABLESPACE_NAME =''

2) make sure that the status of all data files is available.

3) change the tablespace to read-only tablespace:

ALTER TABLESPACE READ ONLY

4) make sure that the tablespace is displayed as read-only in the data dictionary:

SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES

WHERE TABLESPACE_NAME =''

TABLESPACE_NAME STATUS

READ ONLY

5) use the operating system command to copy the data file to a new location, and after the copy is completed, the entire tablespace

OFFLINE, users cannot access this table space at this time:

ALTER TABLESPACE OFFLINE

6) rename the data file to a new location, this operation will automatically update the contents of the control file:

ALTER DATABASE RENAME FILE

'/ FULL_PATH_OF_OLD_LOCATION/AND_DATAFILE_NAME.DBF'

TO

'/ FULL_PATH_OF_NEW_LOCATION/AND_DATAFILE_NAME.DBF'

7) ONLINE this tablespace:

ALTER TABLESPACE YOUR_TABLESPACE_NAME ONLINE

8) make the tablespace readable and writable:

ALTER TABLESPACE YOUR_TABLESPACE_NAME READ WRITE

9) delete the old data files at the operating system level.

B. the database is closed

1) shut down the database normally first.

2) use operating system commands to copy the data file to a new location.

3) MOUNT database, which reads the control file, but not the data file:

STARTUP MOUNT

4) rename the data file to a new location, this operation will automatically update the contents of the control file:

ALTER DATABASE RENAME FILE

'/ FULL_PATH_OF_OLD_LOCATION/AND_DATAFILE_NAME.DBF'

TO

'/ FULL_PATH_OF_NEW_LOCATION/AND_DATAFILE_NAME.DBF'

5) Open the database:

ALTER DATABASE OPEN

2. The data file does not exist or is not available for Oracle

The data file is physically removed or corrupted so that the Oracle can no longer recognize it. For example, if the data file is truncated or overwritten, errors such as ORA-27046 and ORA-1157 will appear:

ORA-27046: file size is not a multiple of logical block size

In this case, there are two options to solve the problem:

A. rebuild the tablespace to which the data file belongs

This method is more suitable for USERS, TEMP, and INDEX tablespaces. It is also recommended if the database is normally closed, that is, there are no active tablespace transactions in the rollback segment. If it is an SYSTEM tablespace, the database needs to be rebuilt.

The specific steps are as follows:

1) MOUNT database:

STARTUP MOUNT PFILE=''

2) OFFLINE DROP data file:

ALTER DATABASE DATAFILE''OFFLINE DROP

3) Open the database:

ALTER DATABASE OPEN

4) Delete the tablespace:

DROP TABLESPACE INCLUDING CONTENTS

5) rebuild the tablespace:

CREATE TABLESPACE DATAFILE

'

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.