ORA-376/ORA-1110 Encountered While Dropping Old Undo Tablespace / Undo Segments (Doc ID 963102.1)

Last updated on DECEMBER 06, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.7 to 11.1.0.7 [Release 11.1]
Information in this document applies to any platform.
***Checked for relevance on 09-Sep-2016***

Symptoms

An ORA-376 & ORA-1110 are encountered during a drop of undo tablespace/segments while the tablespace is offline

ORA-376   "file %s cannot be read at this time"
ORA-1110 "data file %s: '%s'"


The datafile(s) of the undo tablespace have been offline dropped.

(this situation may have been as a result of encountering <BUG:8885304> during migration of 9.2.0.8.0 to 11.1.0.7.0).

Manifestation of the errors are observed in alert.log + interactive in SQL*Plus:

EXAMPLE:

Errors in file /...../..._mmon_25432.trc:
ORA-04045: errors during recompilation/revalidation of SYS.DBMS_SERVER_ALERT
ORA-00376: file 2 cannot be read at this time
+
drop tablespace undotbs
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/.../dbs/MISSING00002'
+
SQL> drop rollback segment "....";
drop rollback segment "...."
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/.../dbs/MISSING00002'

Changes

Target of whole action is migration of an 9.2.0.8.0 database to 11.1.0.7.0.
During this migration <BUG:8885304> was encountered. As attempt to resolve this issue/bug all datafiles of the undo tablespace have been offline dropped (logged in alert.log).

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms