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
-- ORA-01157 fault occurred when querying 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 file
ORA-01110: data file 6: '/u01/Oracle/db/dbs/soe. dbf'

-- Try to drop tablespace and receive the same error
SQL> drop tablespace soe including 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 file
ORA-01110: data file 6: '/u01/oracle/db/dbs/soe. dbf'

2. Analysis
-- From the text after the error number can be determined that DBWR cannot identify or lock file number 6, the ORA-01110 behind the given specific file location
-- The following describes the error code.
SQL> ho oerr ora 01157
01157,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
// 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.

1 2 Next Page