Re: Oracle tries to read RBS in Automatic Undo Mode?

"Thorsten Jens" <thojens_at_gmx.de> wrote in message news:1114668705.825651.188040_at_z14g2000cwz.googlegroups.com...
> Hi,
>
> Oracle: 9.2.0.6.0/64-bit on Solaris 8 (Sparc).
>
> I set our database to use AUM two days ago:
>
> ###########################################
> # Automatic Undo Management Mode
> ###########################################
> undo_management = auto
> # Retention period: 2 hours
> undo_retention = 7200
> # Suppress error messages when trying to use manual undo management
> statements
> undo_suppress_errors = true
> undo_tablespace = undotbs1
>
> After restarting, I set the tablespace with the old rollback segments
> offline. Worked well so far.
>
> At night, we load a lot of data using SQL*Loader + some SQL scripts. It
> ran into this:
>
> ORA-00604: error occurred at recursive SQL level 1
> ORA-00376: file 32 cannot be read at this time
> ORA-01110: data file 32: '/oracle/ts3/rollback/rbs02.dbf'
>
> rbs02.dbf is a file containing the old rolllback segments! Why did
> Oracle try to access those? The files in UNDOTBS1 are far from their
> seize limit, so it could not have been a size problem.
>
> This really bugs me, as I have to do a huge TSPITR now :-(
>
> Thorsten
>

One thought that should really be irrelevant:

Undo (rollback) can be required for delayed block cleanout a long time after the transaction that made the change has committed.

e.g.
I visit a block - I can see that a transaction has 'recently' changed the row that I want to change because there is a lock byte set on the row and a matching entry in the ITL.

I have to find out IF that transaction has committed, or whether it is still active- so I first check v$transaction to see that it no longer exists. Then I may need to know WHEN that transaction commited, in case it commited after I started work - in which case I should see an older version of the data. To do this, I go to the undo segment identified by the ITL to check the transaction commit SCN. That is the point at which you could get your 'unable to read' problem.

However (a) it was two days ago, so the probability of still needing to do that type of thing in the overnight batch seems to be a bit low, and (b) you've restarted the database and offlined the file successfully - which means Oracle should know that any transactions that relate to segments in that tablespace must have been committed before the file was offlined, and therefore the only transactions that could cause a problem would be ones that started before the 'offline' call - but you wouldn't have been starting transactions in that rollback segment since you restarted the database with AUM two previously.

Having put that in writing - I think I can see a window of opportunity for error. If your shutdown was not a clean shutdown, if you are not running in archivelog mode, and if you then forced the rollback file offline, I think you could be in a position where instance recovery could make the rollback file necessary at a future point. But I'd have to think about it for a couple of hours before I could work out if that sequence of events could really cause a problem.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated April 5th 2005