RE: read_only_open_delayed

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Fri, 8 Apr 2022 07:19:23 -0400
Message-ID: <00aa01d84b3a$809a93b0$81cfbb10$_at_rsiz.com>

Humor me: Time a full table scan (or partition scan) on something contained within the read only files counting the unique blocks. Compute the fraction of occupied blocks in the read only files that represents and multiply that fraction times 8 minutes. If that was approximately the time to complete of the full table scan, then I suggest that something slightly less than correct was done in making the read only files read only and they are being scanned and verified prior to the open (probably to be certain no blocks require any undo or redo to be applied.

That is a complete guess, but it is based on an enhancement request (granted circa 1991, and it might have been the first) from the VLDB which originally allowed the system to open before every tablespace was verified and brought on line after a crash.

It might not be exactly the same, but it surely seems similar. I’m not certain whether there is a warm start trace setting (or if something in the alert log is already there by default) that will tell you exactly what is going on.

I would probably start by looking in all the alert logs in the non-delayed opening at the eight-ish minute part for messages that say approximately “xxxx complete tablespace yyyy being brought on line.”

On your test system I would probably try making each one of those files read-write to see if something goes bump, and then making them extremely cleanly read only again to see whether the next test start completes in the expected time. IF the new test to make them read only again notes a problem you cannot fix without a time machine, there is a slight possibility you will need to copy the contents of your read only files somewhere else (or leave the delay start on). That’s not a horrible idea to do from time to time (annually offset from year and quarter ends and in slack times?) just to verify you can in fact access 100% of the actual blocks containing data in your read only tablespaces.

Good luck. Sorry for the rambling stream of consciousness. My coffee is now brewed.

mwf

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis Sent: Friday, April 08, 2022 5:09 AM
To: ORACLE-L
Subject: Re: read_only_open_delayed

I think the last time I used this feature was on an Oracle 10g system, and I don't recall seeing (or hearing later) any problems with it. It was part of handling some very large range-partitioned tables where each tablespace held the partitions for a given date (can't remember if it was daily or weekly now) and I think the point was that the oldest partitions /tablespaces were read-only and not queried very much hence the obvious choice of strategy.

Regards

Jonathan Lewis

On Tue, 5 Apr 2022 at 00:54, Fairlie Rego <fairlie.rego_at_gmail.com> wrote:

Hi all,

I have a 19.10 database with a large number of read only files which takes 21 minutes to open in production when a database switchover happens.

Unfortunately this does not meet our RTO and we have tested the switchover/open with the read_only_open_delayed parameter in a test environment which is not an exact prod replica but as close to it as possible.

So in the test environment the open time reduces from 8 minutes to 22 seconds.

SQL> select enabled,count(*) from v$datafile group by enabled;

ENABLED COUNT(*)
---------- ----------

READ ONLY 1091
READ WRITE 496 Keen to understand if anyone else is using this since this parameter has been around since 10.1

Cheers

Fairlie Rego
Executive Database Architect

www.technoconsulting.com.au