Does dropping old undo tablespace clear old segments and update block headers?

  • From: Guang Mei <gmei@xxxxxxxxxxxxxx>
  • To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 23 Nov 2010 09:28:38 -0500

Oracle on linux.

We created and set a new undo tablespace (called undo2). We also edited the init.ora file (BTW we are not using pfile). We offlined the old undo tablespace after a week. A few days later, we start getting errors that transactions are trying to access the old undo tablespace.

> 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: '/d60/oradata/ES/UNDO/undo.dbf'

but we were able to online the old undo tablespace to get things working again. This occurs even after a db re-start.

As per Note 427801.1, the issue arose because the old UNDO datafiles were off-lined, instead of being dropped. If you had dropped them, then Oracle would clear the old segments and would update the block headers with the new SCN.

Can anyone confirm that this is the correct solution (dropping instead of offline the old undo)? And your experience?



Other related posts: