ORA-01157: cannot identify/lock data file n fault case

Recently in the use of swingbench encountered a ORA-01157 fault, the following is the specific description and solution.

1. Fault Phenomenon -- ORA-01157 fault occurs when query view dba_data_files SQL> select file_name, tablespace_name from dba_data_files where tablespace_name = 'soe'; select file_name, tablespace_name from dba_data_files where tablespace_name = 'soe' * error at line 1: ORA-01157: cannot identify/lock data file 6-see dbwr trace fileORA-01110: data file 6: '/u01/Oracle/DB/dbs/soe. dbf' -- try to drop tablespace and receive the same error SQL> drop tablespace SOE includin G contents and datafiles; drop tablespace SOE including contents and datafiles * error at line 1: ORA-01157: cannot identify/lock data file 6-see dbwr trace fileORA-01110: data file 6: '/u01/Oracle/DB/dbs/soe. dbf' 2. analysis-the text after the error code can be used to determine that dbwr cannot recognize or lock file number 6, the following ORA-01110 gives the specific file location-the following is the specific description of the error code corresponding to SQL> Ho oerr ora 0115701157,000 00, "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 cocould not be identified. // * Action: Have operating system make file available to database. then either // open the database or do alter system check datafiles. -- the preceding description indicates that the background process cannot find the data file or dbwr cannot lock the file because it is used by other processes. -- Access to file databases like this will be prohibited, while other data files will not be affected. -- The final solution is to confirm whether the data file after the error code exists or is available, and execute the alter system check datafiles command in the open state 3. Solve the problem by trying to execute alter system check datafilessql> alter system check datafiles; System altered. -- The failure after execution is still as follows: SQL> select file_name, tablespace_name from dba_data_files where tablespace_name = 'soe'; select file_name, tablespace_name from dba_data_files where tablespace_name = 'soe' * error at line 1: ORA-01157: cannot identify/lock data file 6-see Dbwr trace fileORA-01110: data file 6: '/u01/Oracle/DB/dbs/soe. dbf' -- view the data dictionary record information to indicate that the current tablespace is in the online status. SQL> select tablespace_name, status, contents from dba_tablespaces where tablespace_name = 'soe '; tablespace_name status contents ------------------------------ --------- SOE online permanent -- view the Alert Log File and provide the error message, prompting the tarce file Oracle @ v2048db01p: /u01/Oracle/admin/syisdb/bdump> tail-8 alert_syisdb1. Logadditional information: 3tue Nov 13 09:43:17 2012 errors in file/u01/Oracle/admin/syisdb/bdump/syisdb1_dbw0_5925.trc: ORA-01186: file 6 failed verification testsORA-01157: cannot identify/lock data file 6-see dbwr trace fileORA-01110: data file 6: '/u01/Oracle/DB/dbs/soe. DBF 'tue Nov 13 09:43:17 2012 file 6 not verified due to error ORA-01157 -- check whether the data file corresponding to the tablespace SOE exists in Oracle @ v2048db01p: ~> Export oracle_sid = + asm1oracle @ v2048db01p: ~> Asmcmdasmcmd> Cd + dg2/syisdb/datafileasmcmd> lsccdata.289.799174049sysaux. latency> ls * SOE * -- # There is no SOE data file asmcmd: entry '* SOE *' does not exist in directory' + dg2/syisdb/datafile/'asmcmd> ls * SOE * -- # No SOE-containing data file, we can see that the data file corresponding to the tablespace SOE has been lost asmcmd: entry '* SOE *' does not exist in directory '+ dg2/syisdb/datafile/' -- delete the tablespace and data file directly, we do not recommend that you operate SQL> alter database datafile 6 offline drop; database altered in the production environment. -- View data dictionary information again, still online -- Author: Robinson -- Blog: http://blog.csdn.net/robinson_0612SQL> select tablespace_name, status, contents from dba_tablespaces where tablespace_name = 'soe '; tablespace_name status contents ------------------------------ --------- SOE online permanent -- The following query also seems to have a problem. The corresponding data file has been cleared in the previous command, at this time, availablesql> Col file_name format a55sql> set linesize 160sql> select file_name, tablespace_name, status from dba_data_files where tablespace_name = 'soe' is still displayed '; file_name tablespace_name status ---------------------------------------- ---------------------------- -----------/u01/Oracle/DB/dbs/soe. dbf soe available -- try to create an object in the tablespace and receive the ORA-01658 error SQL> Create Table t tablespace SOE as select * From dba_objects; create Table t tablespace SOE as select * From dba_objects * error at line 1: ORA-01658: unable to create initial extent for segment in tablespace SOE -- view the corresponding error message -- the error message indicates that there is not enough continuous space to allocate the initial extent. SQL> Ho oerr ora 0165801658,000 00, "unable to create initial extent for segment in tablespace % s" // * cause: failed to find sufficient contiguous space to allocate initial // extent for Segment being created. // * Action: Use alter tablespace add datafile to add additional space to the // tablespace or retry with a smaller value for initial -- view the status again, the corresponding data file is recoversql> Col file_name format a40sql> select file_name, tablespace_name, status, online_status from dba_data_files where tablespace_name = 'soe '; file_name tablespace_name Status Online _ ------------------------------------------------------------------ -----------/u01/Oracle/DB/dbs/soe. dbf soe available recover -- view the V $ recover_file view. The offline file not foundsql> select * from V $ recover_file is not found; file # online _ error change # Time ---------- ------- hour ---------- -------------------- 6 offline file not found 0 -- check that the corresponding data file does not exist. SQL> Ho LS-hltr/u01/Oracle /DB/dbs/soe. dbfls:/u01/Oracle/DB/dbs/soe. DBF: no such file or directory -- delete the entire tablespace and data file SQL> drop tablespace SOE including contents and datafiles; tablespace dropped. -- The following query indicates that SOE in the tablespace has been completely cleared. SQL> select * from V $ recover_file; No rows selectedsql> select file_name, tablespace_name, status, online_status from dba_data_files where tablespace_name = 'soe'; No rows selected

Summary:
The ORA-01157 is usually generated by the background process dbwr locking.
This prompt is displayed if one or more data files cannot be opened, and the database cannot be opened, if the database has been mounted during the restoration period.
Data File loss, data file licensing issues, such as data file oracle users do not have write permissions will produce ORA-01157.
Data Files not listed by the ORA-01157 will not be affected if they are open.

 

Note:
A careful friend may have discovered that when checking the corresponding data file, he only checked whether there was a corresponding data file on the ASM disk.
Because the error database is RAC, the data file corresponding to the file system in the check prompt is ignored. Not careful enough ......
The reason should be that when swingbench is used, when SOE tablespace is created, the next step is taken directly, resulting in the creation of data files to the file system, while the file system is not shared. (RAC environment)

 

For more information, see:

ORA-01157 troubleshooting

For more information about the basics and concepts of Oracle network configuration, see:
Configure the Oracle client to connect to the database
Configure dynamic service registration for non-default ports
Configure sqlnet. ora to restrict IP Access to Oracle
Configure and manage Oracle listener logs
Set the Oracle listener password (listener)
Oracle RAC Listener Configuration
Non-Default port listening configuration in Oracle RAC (listener. ora tnsnames. ora)

Load Balance)

Configure RAC load balancing and Failover

For more information about user-managed backup and recovery, see

Oracle cold backup

Oracle Hot Backup

Concept of Oracle backup recovery

Oracle instance recovery

Oracle recovery based on user management

System tablespace management and Backup Recovery

Sysaux tablespace management and recovery

Oracle backup control file recovery (unsing backup controlfile)

 

For information on RMAN backup recovery and management, see

RMAN overview and architecture

RMAN configuration, Monitoring and Management

Detailed description of RMAN backup

RMAN restoration and recovery

Create and use RMAN catalog

Create RMAN storage script based on catalog

Catalog-based RMAN backup and recovery

RMAN backup path confusion

 

For the Oracle architecture, see

Oracle tablespace and data files

Oracle Password File

Oracle parameter file

Oracle online redo log file)

Oracle Control File)

Oracle archiving logs

Oracle rollback and undo)

Oracle database instance startup and Shutdown Process

Automated Management of Oracle 10g SGA

Oracle instances and Oracle databases (Oracle Architecture)