RE: hanging shutdowns

On Monday, February 27, 2006 Edgar Chupit wrote:

> First of all, I would like to mention that I don't like to shutdown
> database without any practical reason (like hardware/OS
> maintenance/upgrades/etc).

That's a great goal. Why cause an unnecessary outage if you don't have to? I have done some mulling over the shutdown issue, and mentioned it in a couple of my papers and presentations, such as this one:

http://www.ora-600.net/articles/stayinalive.pdf

> And still I would like to argue that under normal circumstances
> startup force restrict + shutdown immediate (or shutdown abort,
> startup force, shutdown immediate) will run almost as fast and is as
> dangerous as a single shutdown immediate.

Yes, in fact it should run faster. I was just suggesting forcing a checkpoint first so that the roll forward takes less time. I think in principle we are in agreement. In fact, I'm confused how my post elicited such a strong response on your part.

> After shutting down abort in order to perform cold backup you still
> need to startup database and close it in consistent mode. Database
> startup is not very fast process in it self, because Oracle not only
> needs to recover database into consistent state (rollback uncommitted
> transactions)...

No, rollback takes place in the background after the database is already open. Opening the database is blocked primarily by roll forward (crash recovery).

> ...but also allocate memory structures...

Do you mean allocate shared memory? That is true, but even on instances with very large SGAs, it often takes less time in practice than a single shutdown immediate. It really depends on if you are running a system that takes a really long time to shutdown immediate after a period of normal use. 8i generally takes longer than 9i, and 10g takes less time than both. Now it seems like most people are fairly satisfied with shutdown immediate speed in 10g. The original poster was complaining that shutdown immediate takes a long time!

> ...and prepare itself for a normal work.

?? Is there some specific component of this step that takes significant time?

> And to shutdown database in consistent state you
> still need to issue shutdown immediate.

That's the basic idea.

> One of the popular reasons why shutdown immediate can take a longer
> time to proceed is because Oracle waits for SNP process to wakeup
> (Note: 1018421.102), but this can also happened when the shutdown
> immediate is called second time (after startup force), so even
> checkpointing and using startup force restrict can cause database to
> hang in shutdown immediate mode.

The same goes for deallocation of temp segments in 8i, as well as a variety of other bugs and inconveniences associated with (especially older versions') shutdown immediate. For the job queue process problem (which applies only to multimaster or updatable snapshot replication environments), why not just 'startup mount', 'alter system set job_queue_processes = 0', and 'alter database open', 'shutdown immediate'?

> Also, there is a Note: 46001.1 that suggest to minimize usage of
> shutdown abort on Windows systems, because it can cause "allocation
> problems when Oracle is next started.". Note: 161234.1 that describes
> situation when shutdown abort can hang. Note: 222553.1 that states
> that startup force can be safer than shutdown abort. And plenty of
> other notes that describes different problems that can occur during
> database shutdown. And surely there are many bugs that can occur after
> shutdown abort (but under normal circumstances shutdown abort is very
> safe).

There are bugs and issues in every Oracle feature. The bugs do not necessarily disqualify those features for use in situations where those bugs do not apply. Most of the notes you mention refer to edge-case O/S oddities that don't apply to the vast majority of installations. If someone is running on a version of Oracle with a critical shutdown-related bug, then they should get the patch! As you mention, we agree that in the majority of circumstances 'shutdown abort' is fine.

> Saying all this, I would like to return to thread subject and suggest
> to the original poster to try to convince the management to switch to
> hot backups, and forget about shutting down the databases because of
> backup at all.

The original poster stated that she wanted to stop doing cold backups, but that she had not yet "won that debate."

--
Jeremiah Wilton
ORA-600 Consulting
Recoveries - Seminars - Hiring
http://www.ora-600.net


--
http://www.freelists.org/webpage/oracle-l