Re: Partitions of table read only

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, June 19, 2003 5:59 AM

> Jack, maybe this has been covered. I seem to recall from the B&R module
> (knew it would prove useful sometime) that after you make a tablespace
> read-only that you should take a backup. Recovering a database with
> tablespaces that were read-write when backed up but are read-only now
> requires an extra step or two (something I never like in a recovery).
>

Hi Dennis

Backing up the tablespace files (and lets not forget the control file) is certainly not a bad idea.

Something else that many don't consider is to select from all objects within the tablespace with full scans *before* making the tablespace read only. This has the effect of performing all the necessary block cleanouts (ie. for all the blocks written to disk before they could be committed and cleaned out in memory) while Oracle still can.

If the tablespace is made read only and some poor blocks haven't been cleaned out, upon reading the block Oracle has no choice but to go to the rollback/undo segments in it's attempt to confirm the consistency of the block. However upon confirming that indeed the transaction is long gone and block cleanout can take place with the "latest possible SCN", it can't actually perform the necessary block changes because, you guessed it, the tablespace is currently read only.

This means that the overhead of checking for consistency but failing to actually perform the block cleanout continues on and on and on ...

Hence the suggestion to guarantee block cleanout while the tablespace is in a position to do so (in read/write mode).

Cheers

Richard Foote

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Richard Foote
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).