Ora-01116 Error Resolution method

Database error:

Ora-01116:eror in opening database file 201

Ora-01110:data file 201: '/HOME/APP/ORACLE/ORADATA/TESTMOD/TEMP02.DBF '

Ora-27041:unable to open File

linux-x86_64 error:2: No such file or directory

Or

Ora-01122:database File 201 failed verification Check

Processing ideas:

1 See if the data file exists on the operating system

TEMP02.DBF file does not exist

2) View Aler.log

The temp table space was created with the addition of a data file temp02.dbf, but there was no deletion of the data file in the log, and suspected data files were deleted at the operating system level using the RM command.

3 View temporary table space status-Tablespace_name

Temporary table space status is online, normal

--This is not accurate, the table space state is normal, but the data file may have been lost

4 View the temporary table space data file status-dba_temp_files

Error ora-01116/01110/27041--

5 offline temporary table space data files or offline temporary tablespace

--offline TempData or temporary tablespace does not cause loss of source data

Alter Databae tempfile '/HOME/APP/ORACLE/ORADATA/TESTMOD/TEMP02.DBF ' offline;

6) Add data file

Alter tablespace add tempfile '/patch/to/datafile.dbf ' size 10M;

Because the data file for the missing temporary tablespace does not cause data loss, you can take the missing data file offline directly (this data file, no longer exists at the operating system level), and then add a file to restore Oracle to its normal state.

Reason:

The operation is not standard, the data file is removed from the operating system level, resulting in the database state exception, there are ORA-01116 errors.

Summarize:

If you want to delete the data file, follow the Oracle Usage specification (official manual).

Data file offline, and then removed from the operating system level, in fact, the data file information is also stored in the data dictionary! Offline data files for a long time, maybe this data file will never be online again.

Resources:

To delete a data file:

The following example drops the datafile identified by the alias Example_df3.f in the ASM disk group DGROUP1. The datafile belongs to the example tablespace.

ALTER tablespace Example DROP datafile ' +dgroup1/example_df3.f ';

The next example drops the Tempfile lmtemp02.dbf, which belongs to the Lmtemp tablespace.

ALTER tablespace lmtemp DROP tempfile '/u02/oracle/data/lmtemp02.dbf ';

This is equivalent to the following statement:

ALTER DATABASE tempfile '/u02/oracle/data/lmtemp02.dbf ' DROP

including datafiles;

Restrictions for dropping datafiles

The following are restrictions for dropping datafiles and tempfiles:

The database must be open.

If a datafile is isn't empty, it cannot be dropped.

If you must remove a datafile this is isn't empty and that cannot being made empty by dropping schema objects, you must drop th E tablespace that contains the datafile.

You cannot drop the "the" or only datafile in a tablespace.

Therefore, DROP datafile cannot is used with a bigfile tablespace.

You are cannot drop datafiles in a read-only tablespace this is migrated from dictionary managed to locally. Dropping a data file from all other read-only tablespaces is supported.

You are cannot drop datafiles in the SYSTEM tablespace.

If a datafile in a locally managed tablespace are offline, it cannot be dropped.

Link:

http://docs.oracle.com/cd/E11882_01/server.112/e25494/dfiles006.htm#ADMIN11435

This column more highlights: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/