RE: hanging shutdowns, excellent case for a standby recovery database and associated frozen rename target
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: "Oracle-L@Freelists" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 28 Feb 2006 15:55:08 -0500
I have a minor issue with one point in Mark Farnham's response. I think
Mark has confused the ability of the Oracle rdbms to open and accept
connections prior to performing all rollbacks after a crash with the
shutdown process. If you do a shutdowm immediate all current
transactions are rolled back. The cold backup will not contain any
"pending" rollbacks. Delayed block cleanout may have to be done, but
all transaction rollbacks are complete. I do not consider delayed block
cleanout an issue though I guess it is possible legal was told they
could extract the time of the last checkpoint from the file header. I
would expect the OS file timestamp to match the time the file was
created from the backup tape.
From the 10gR2 DBA Administration Guide: >>>
Immediate database shutdown proceeds with the following conditions:
No new connections are allowed, nor are new transactions allowed to be
started, after the statement is issued.
Any uncommitted transactions are rolled back. (If long uncommitted
transactions exist, this method of shutdown might not complete quickly,
despite its name.)
Oracle Database does not wait for users currently connected to the
database to disconnect. The database implicitly rolls back active
transactions and disconnects all connected users.
The next startup of the database will not require any instance recovery
procedures.
<<<
HTH -- Mark D Powell --
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Robyn
Sent: Tuesday, February 28, 2006 2:01 PM
To: Mark W. Farnham
Cc: Oracle-L@Freelists
Subject: Re: hanging shutdowns, excellent case for a standby recovery
database and associated frozen rename target
Mark,
Thank you for the ideas. We are already having discussions with Oracle
(and EMC) about setting up a complete hot backup site. Perhaps the
points you made below will help us gather the funding required as the
price tag still has mgmt reeling. However, the project has been given a
high priority so there is a very good chance we will pursue it later
this year.
I'm going to do a little more research on the delayed block cleanout
issue so I can present the concepts clearly to the less-technically
oriented. If you have any suggestions on materials, I'd appreciate a
pointer.
Thank you again ... this presents some interesting discussion points.
Robyn
On 2/28/06, Mark W. Farnham <mwf@xxxxxxxx> wrote:
> Robyn:
>
> This seems to me like an excellent case for Alexandrian logic; don't
> untie the knot, hack it apart!
>
> One point I've missed in this thread is ensuring that rollbacks are
> complete so that neither infinitesmal chances of failure of roll
> forward nor rollback exist in your cold backup. Since whatever release
> it was when the ability to open the database before all rollbacks are
> complete implemented, the side effect of pending rollbacks in "cold"
> backups has from time to time been ignored.
>
> Another point is delayed block cleanout.
>
> If what your legal department really wants is a cold backup in the
> sense that all the files can be immutably stamped with a date last
> modified and starting the database does not execute changes to the
> files, then a mere cold backup is in fact not enough.
>
> So make the legal department your ally, who will then provide support
> for you to have a standby-failover site. To create your truly frozen
> point in time immutable database you then would:
>
> 1) Cancel recovery on the standby.
> 2) Copy the standby (locally on the standby site, or to yet another
> server if you want to avoid the rename database step).
> 3) (Optional depending on your choice in step 2.) Rename the copy.
> 4) Recover the COPY of the cancelled recovery standby to an exact
> known point in time.
> 5) Comprehensively query all blocks and do whatever else you have to
> do to trigger all delayed block cleanouts (I think that is version
> dependent and I'm not sure I've seen a comprehensive update on that
> issue since the last time I was somewhat confident I thought I knew
> how to do that, references to a comprehensive discussion welcome).
> 6) Shut down the cleaned out copy cold.
> 7) Copy the cleaned out copy to whatever backup media your legal
> department and you can agree on as sufficient.
> 8) Resume remote recovery on the standby. (Actually you can do this
> after step 2 if your choice is a remote machine).
>
> If you have an alternate server, you could also just hot backup to
> there and do the similar function of starting up and completing
> recovery, rollback, and "cleanout" to produce a very clean cold
shutdown.
>
> All this avoids the shutdown on the primary production database
> altogether but still gives you a cold backup which is arguably a
> superior cold backup to the kind that may contain pending rollbacks
and delayed block cleanouts.
>
> Now if I've botched something in this logic, all y'all please be kind,
> but additions/corrections/etc. are certainly most welcome.
>
> Regards,
>
> Mark
>
>
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Robyn
> Sent: Tuesday, February 28, 2006 9:58 AM
> To: m.haddon@xxxxxxxxxxx
> Cc: Oracle-L@Freelists
> Subject: Re: hanging shutdowns
>
>
> Micheal,
>
> I understand your position and when I arrived here, I made all the
> same arguments. I've been told that our legal department insists on a
> cold backup, and the requirement is non-negotiable. We run full test
> recoveries on all our major systems on a regular basis and we use the
> hot backups to do so. None of us doubt that the hot backups are
> adequate for recovery.
>
> So I guess we're not really 24x7, we're 24x7-15 and that 15 minutes is
> a sacred cow that I need to leave alone right now ...
>
> Thanks for the input and if I was calling the shots, I wouldn't do it
> this way. However, I would still need a script that would shut the
> database down quickly, possibly for maintenance or hardware issues, so
> I really appreciate the suggestions provided on this thread.
>
> Robyn
>
>
>
--
Robyn Anderson Sands
email: Robyn.Sands@xxxxxxxxxx
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
