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

Yes, it is the correct solution. This is caused due to offline files for the 
old undo tablespace, as oracle would still require the old undo tablespace to 
know the time a transaction committed at. This is attributed  to delayed block 
cleanout. Please refer to chapter 6 of DSI 402 for more details on delayed 
block cleanout.

Some details are:
When an undo segment is dropped, the corresponding row in the UNDO$ table is 
not 
physically deleted. The row is modified: the STATUS$ column is set to 1 
(meaning 
invalid); the SCNBAS and SCNWRP columns are set to the SCN of the most recent 
commit transaction among the transactions of the undo segment before the drop.
As the undo segment had no active transactions (thereby allowing the user 
process to drop it) means that the transaction in the ITL entry of the block 
had been committed in the undo 
segment. Because the exact commit SCN is not known, an approximate one 
(corresponding to the highest SCN just before the drop of the undo segment) is 
stored in the ITL entry. This is indicated by setting C: Commit and U: Upper 
bound flags in the ITL entry（equals to 0xa0）, indicating that the transaction 
was committed before this SCN.
So if an undo segment is dropped, oracle only needs to select UNDO$ to know the 
upper bound commit SCN for delay block cleanout, that is why this undo segment 
is not needed any more.

Best Regards 
dbsnake

--- 10年11月23日，周二, Guang Mei <gmei@xxxxxxxxxxxxxx> 写道：


发件人: Guang Mei <gmei@xxxxxxxxxxxxxx>
主题: Does dropping old undo tablespace clear old segments and update block 
headers?
收件人: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
日期: 2010年11月23日,周二,下午10:28


Oracle 10.2.0.1.0 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?

TIA.

--
http://www.freelists.org/webpage/oracle-l

Other related posts: