回复： Does dropping old undo tablespace clear old segments and update block headers?
Date: Wed, 24 Nov 2010 08:30:48 +0800 (CST)
Message-ID: <130681.22047.qm_at_web15808.mail.cnb.yahoo.com>
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_at_prospectiv.com> 写道：
发件人: Guang Mei <gmei_at_prospectiv.com> 主题: Does dropping old undo tablespace clear old segments and update block headers? 收件人: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> 日期: 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 -- http://www.freelists.org/webpage/oracle-lReceived on Tue Nov 23 2010 - 18:30:48 CST