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

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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.