ORA-1157 and ORA-01110 error practices

ORA-1157 and ORA-01110 error practices often encounter related ORA-1157OR1110 errors in the backup experiment, related error information, nothing more than data files, log files, and control files and a series of related file problems, in general, as long as we connect the relationship between various files, of course, we must also let the oracle system recognize the relevant files we are using. This ORA error message will be greatly reduced! 10.14 I conducted a test (NORMAL, TEMPORARY, IMMEDIATE) with different offline options. Data Files always have problems, or oracle does not recognize my reconstructed data files, either the checkpoint of the control file is too old to OPEN the database. In general, we still have some experience in these experiments. Here are the relevant records and share them with you! ORA-1157, "cannot identify/lock data file % s-see DBWR trace file" cause: because the data file is already in use, the background process of the database cannot find the corresponding data file or lock the corresponding data file, in this way, the database will prohibit access to these data files, while other data files will not be affected. With this error, the operating system will prompt which data file cannot be identified. ORA-01157 errors typically occur together with ORA-01110 errors, often at the operating system level, such as ORA-07360, while a DBWR trace file is generated under the directory of background_dump_dest. For example, on the Solaris platform, the following error message is displayed: more .. less .. ORA-01157: cannot identify/lock data file 5-see DBWR trace file ORA-01110: data file 5: '/export/home/Oracle/oradata/817/users01.dbf' and then view the trace file content of DBWR with the following content: 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 s Uch file or directory Additional information: 3 The following is a few aspects of easy to generate ORA-1157 errors in detail: 2. the common cause and solution 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 cannot recognize it. In this case, the data file may be renamed or moved to a new partition or location on the operating system level. This situation 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. How to rename A data file to A new location/directory:. 1) check which data files are contained in the tablespace where the data file is located and execute the following query:

SELECT FILE_NAME, STATUS FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = ''；

 

2) determine that the status of all data files is available. 3) change the tablespace to a 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) run the operating system command to copy the data file to a new location. After the copy is completed, the entire tablespace is OFFLINE. At this time, the user cannot access the tablespace:

ALTER TABLESPACE OFFLINE；

 

6) Rename the data file to a new location. This operation will automatically update the content in 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 table space:

ALTER TABLESPACE YOUR_TABLESPACE_NAME ONLINE；

 

8) set the tablespace to read/write:

ALTER TABLESPACE YOUR_TABLESPACE_NAME READ WRITE；

 

9) Delete the old data file at the operating system level. B. The database is closed. 1) shut down the database normally. 2) run the operating system command to copy the data file to a new location. 3) MOUNT the database, which will read the control file but will not read the data file: STARTUP MOUNT4) Rename the data file to a new location, this operation will automatically update the content in 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 the data file that is unavailable in Oracle is physically removed or damaged, which makes Oracle unable to recognize it. For example, the data file is truncated or overwritten, generally there will be a ORA-27046, ORA-1157 error prompt: ORA-27046: file size is not A multiple of logical block size in this case there are two options to solve the problem:. the tablespace to which the data file belongs. This method is more suitable for USERS, TEMP, and INDEX tablespaces. If the database is shut down normally, that is, the tablespace transactions that are not activated in the rollback segment, this method is also recommended. If it is a SYSTEM tablespace, the database needs to be rebuilt. The procedure is as follows:

1) mount database: startup mount pfile = ''; 2) offline drop Data File: alter database datafile'' offline drop; 3) open database: alter database open; 4) delete TABLESPACE: drop tablespace including contents; 5) recreate TABLESPACE: CREATE TABLESPACE DATAFILE

 

'Size; 6) recreate all previously existing objects in the tablespace: You can use the script of the previously created object or use the recently available export dump to recreate the previously existing objects. B. Restore data files using the normal recovery process. This method is applicable to read-only tablespaces or USERS and INDEX tablespaces that cannot be used to reconstruct tablespaces. If the tablespace is a rollback segment, the database must be shut down before this method can be used. If it is a SYSTEM tablespace with full backup and all archive logs, we strongly recommend that you use this method for recovery. However, if it is not an archive method, then, you can only use all the current online logs for recovery. In many cases, it is impossible or time-consuming to reconstruct the tablespace. Therefore, restoring data files from backup and archive logs is a good method, especially for read-only tablespaces, because no data is written or changed, direct backup is the fastest and most convenient to restore. The procedure is as follows: 1) recover lost or damaged data files from the backup. 2) MOUNT the database:

STARTUP MOUNT PFILE=''；

 

3) execute the following query:

SELECT V1.GROUP#, MEMBER, SEQUENCE#, FIRST_CHANGE# FROM V$LOG V1, V$LOGFILE V2 WHERE V1.GROUP# = V2.GROUP#；

 

This query will list all online redo logs and the SEQUENCE and first change NUMBER.4 they represent.) If the database is not archived, perform the following query:

SELECT FILE#, CHANGE# FROM V$RECOVER_FILE；

 

If CHANGE # is greater than FIRST_CHANGE # Of the smallest online redo log file, the data file can be restored. Remember to apply all online redo log files when restoring the data file, and then go to step 3. If CHANGE # is smaller than FIRST_CHANGE # Of the smallest online redo log file, the data file cannot be recovered, the tablespace to which the data file belongs can only be restored from the latest full backup of the database or rebuilt. 5) RECOVER the data file: recover datafile ''; 6) confirm that all archived logs are applied until the message" Media recovery complete "appears, if Oracle prompts that there is a non-existing archive log file, it is possible to apply all online redo log files to restore until the message "Media recovery complete" appears. 7) OPEN the DATABASE: alter database open; 3. DATABASE temporary tablespace data file loss when the DATABASE temporary tablespace data file loss will also cause ORA-01157 errors. Because the database does not check the data files in the temporary tablespace, the database can be opened at this time. In this case, the solution is to logically Delete the data file of the temporary tablespace and add a new data file of the temporary tablespace. 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 5 - see DBWR trace file ORA-01110: data file 5: '/Oracle/oradata/temp01.dbf' ALTER DATABASE TEMPFILE ‘/Oracle/oradata/temp01.dbf‘ DROP；SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_TEMP_FILES；ALTER TABLESPACE TEMP ADD TEMPFILE ‘/Oracle/oradata/temp01.dbf‘ SIZE 100M；

 

III. ORA-01157 error due to operating system problems or third-party software problems 1. when vxfddstat is used to access quick I/O or other applications, the "Cannot open file" error is returned, and Oracle returns the following error: ORA-01157: cannot identify data file 1-file not found ORA-01110: data file 1: ''at this time the user should contact the technical support of Veritas, Technical Support site URL is http://support.veritas.com /. 2. on a machine in the HP-UNIX, if the system core parameter nflock settings are not large enough, this may cause Oracle not to lock the required data file and cause the error: ORA-27086: skgfglk: unable to lock file-already in use or error: ORA-01157: cannot identify/lock data file 4-see DBWR trace file ORA-0110: data file 4: '/Oracle/oradata/user01.dbf'