Unable to shutdown the database

[zigz...@yahoo.com]

I am on Oracle 11.1.0.7 on HP UNIX 11i. I have been using following procedure to shutdown database for a long
time:

1. shutdown abort
2. startup
3. shutdown immediate

Today (after Sandy hurricane where my datacenter lost network connectivity), I noticed:

In the last step, database does not close, I have been waiting for 30 minutes and gave up. My database is only 50GB.
Any idea, why?

When I could not do step 3, I did again following steps:

1. shutdown abort
2. startup

Database comes up and I do not see any errors. I can access all my data. Can anyon eexplain, database will not shutdown without abort(why I have to wait forever, how I can troubleshoot).

[Robert Klemme]

On 02.11.2012 08:46, zigz...@yahoo.com wrote:
> I am on Oracle 11.1.0.7 on HP UNIX 11i. I have been using following procedure to shutdown database for a long
> time:
>
> 1. shutdown abort
> 2. startup
> 3. shutdown immediate

Why are you doing that? Why are you shutting down your database, then
starting it and shutting down again when you only want to shut down?
And why are you using "shutdown abort"?

"If possible, perform this type of shutdown only in the following
situations:

The database or one of its applications is functioning irregularly and
none of the other types of shutdown works.

You must shut down the database instantaneously (for example, if
you know a power shutdown is going to occur in one minute).

You experience problems when starting a database instance.
...
The next startup of the database will require automatic instance
recovery procedures."

http://docs.oracle.com/cd/E11882_01/server.112/e25494/start003.htm#i1006611

Cheers

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

[epok...@gmail.com]

The only difference between a Shutdown Immediate and a Shutdown Normal is that the first one (Immediate) does NOT "wait for users to exit". It replaces that portion with "canceling active transactions and rollback".

Perhaps it was "stuck" doing the "canceling, etc." for some odd reason. 30 minutes for a shutdown is not normal. Makes even less sense since it appears your startup was successful.

[joel garry]

Are those 3 steps a leftover from older versions where the agents
would keep attaching? For some cold backup?

Your symptoms sound like some client attaches automatically (or
perhaps a persistent user kept trying and happened to hit it just
right), keeping you from shutting down. Check all your crons,
including remote ones, and all scheduled jobs, and any oms. Didn't
you have problems shutting dbconsole at one time? Those java
processes can be maddening.

When you shutdown, you might want to have another sysdba session open,
to check for what transactions are hanging it up. Sometimes an
existing dbconsole session can help too.

Startup force does a shutdown abort and then startup. Both abort and
force should only be used in unusual situations. I seem to be in the
minority thinking an abort is dangerous, though. The general problem
is, if you have any problems in your redo stream, you'll not be able
to startup. I'm perhaps over sensitive about it, from the long ago
days when standbys in recovery were delicate.

jg
--
@home.com is bogus.
"Second nature, backed by decades of experience. Some things can’t be
bottled. " http://www.utsandiego.com/news/2012/nov/02/tp-beer-ambassador-dr-bill-leaves-few-brews/

[Robert Klemme]

On 02.11.2012 15:53, epok...@gmail.com wrote:
> Perhaps it was "stuck" doing the "canceling, etc." for some odd
> reason. 30 minutes for a shutdown is not normal. Makes even less
> sense since it appears your startup was successful.

Couldn't it also be the instance recovery triggered by the "startup"
after "shutdown abort" not having been finished?

[Kai Rode]

>> 1. shutdown abort
>> 2. startup
>> 3. shutdown immediate

>Why are you doing that? Why are you shutting down your database, then
>starting it and shutting down again when you only want to shut down?
>And why are you using "shutdown abort"?

Actually this is not completely nonsensical. If you "shutdown immediate",
pending transactions will do a normal rollback. If you do it the way the
original poster describes, the database will do a fast parallel rollback in
step 2 so it *could* be faster.

Plus I remember a conversation with oracle support I once had while working
on a priority 1 ticket: Them: "Now do a shutdown abort". Me: "Do you mean
shutdown immediate?". Them: "If you have that much time..." :-)

[joel garry]

On Nov 2, 10:13 am, Robert Klemme <shortcut...@googlemail.com> wrote:

That does make perfect, obvious sense. Also, someone answered this in
the forums with a pointer to MOS 1194229.1, which notes a memory
configuration shortage can do this. I've seen memory leaks in
dbconsole java processes on hp-ux that seem to add up over a couple to
6 months (and in older versions, memory fragmentation from the i/o
usage by rman). dbconsole alerts me to excessive memory usage when
OMS decides to do stuff _after_ unusually heavy usage of the db is
over, D'Oh!

jg
--
@home.com is bogus.

Republicans + databases = http://www.utsandiego.com/news/2012/oct/31/mailer-accusing-voters-upsets-some/

[Robert Klemme]

On 11/02/2012 06:17 PM, Kai Rode wrote:
>>> 1. shutdown abort
>>> 2. startup
>>> 3. shutdown immediate
>
>> Why are you doing that? Why are you shutting down your database, then
>> starting it and shutting down again when you only want to shut down?
>> And why are you using "shutdown abort"?
>
> Actually this is not completely nonsensical. If you "shutdown immediate",
> pending transactions will do a normal rollback. If you do it the way the
> original poster describes, the database will do a fast parallel rollback in
> step 2 so it *could* be faster.

But that would be done on next startup anyway. Why do it here? For a
cold backup? Also, "faster" is not the only goal. Especially with
databases reliability is high on the list as well. So I'd probably
rather pick the _possibly_ slower operation and stick to the regular
procedure.

> Plus I remember a conversation with oracle support I once had while working
> on a priority 1 ticket: Them: "Now do a shutdown abort". Me: "Do you mean
> shutdown immediate?". Them: "If you have that much time..." :-)

Well, I have also seen support personnel of a telco provider to suggest
switching off the Windows firewall (which resulted in a virus infection
of a friend's PC) so I usually take advice from support with a grain of
salt - especially when it seems to contradict the documentation so
drastically.

Kind regards

robert

[zigz...@yahoo.com]

As I said earlier, because of Sandy storm, my site where database and app servers are installed lost wide area network connectivity. As a result I could not remotely logon to database server. Then inside the site, someone managed to connect using UNIX server console. Then the person was trying to shutdown oracle and restart it. We have scripts which were developed to be called from UNIx’s /sbin/rcx.d …when server reboots. Even though we were not rebooting the server, we were using those scripts to recycle oracle and shutdown was getting hung.
Today, our WAN connectivity was restored, so I could logon to DB server remotely and wow I could do all the three steps in 2-3 minutes and shutdown immediate completed in a minute instead of “hanging” even after 30 minutes.
May be, UNIX server was somehow trying to connect through wide area network while shutdown immediate was going on.. I do not know why it will need to go through WAN when Oracle is being shutdown…; but restore of WAN made all the difference. All my disks on db server are “local”; i.e., we do not use SAN in this site.
Thanks a lot for your insight on different ways of shutting down. I have read at few places, there is nothing wrong with shutdown abort (other than users uncommited work will be interrupted) and then doing startup and then shutdown immediate . You will not risk your database in anyway by doing 3 steps. Instead of just one step shutdown immediate.

[zigz...@yahoo.com]

See below link from Don Burleson exapling the differences lucidly:

http://dba-oracle.com/t_oracle_shutdown_immediate_abort.htm

[Noons]

zigz...@yahoo.com wrote,on my timestamp of 3/11/2012 3:04 PM:


> “local”; i.e., we do not use SAN in this site. Thanks a lot for your insight
> on different ways of shutting down. I have read at few places, there is
> nothing wrong with shutdown abort (other than users uncommited work will be
> interrupted) and then doing startup and then shutdown immediate . You will
> not risk your database in anyway by doing 3 steps. Instead of just one
> step shutdown immediate.
>

Shutdown normal will almost always not result in a speedy shutdown. Mostly
because Oracle has to wait for all pending transactions to finish. Shutdown
abort will not wait for anything and just crash the instance immediately. It's
good for some of the situations I mention ahead.

Shutdown immediate will most of the time do the right thing in usable time. The
only two situations I know of where it'll be slow (aside from bugs - yes, they
happen in the best families...) are:
- when there are lots of connected sessions and they all need to be closed and
rolled back
- when large portions of memory have been swapped out, including portions of the
SGA.
The above two are not exclusive by any stretch of the imagination.
As well, it is a good practice to shutdown the listener before shutting down the
db: in very busy connect/disconnect applications the listener may indeed affect
how fast the db can come down.
One thing I've done is to use largepages for the SGA of most of my databases.
It ensures the SGA is never paged out and the only thing I have to worry about
on a shutdown is getting all the sessions closed. It takes around half a minute
for one of our dbs with 700+ connected sessions. Sometimes a bit longer if a lot
of idle sessions have been swapped out and need to be brought back in.

[Robert Klemme]

On 03.11.2012 06:03, zigz...@yahoo.com wrote:

> See below link from Don Burleson exapling the differences lucidly:
>
> http://dba-oracle.com/t_oracle_shutdown_immediate_abort.htm

Frankly, I prefer Oracle documentation over his site. I never saw him
argue his point convincingly. Many will also remember his quarrel with
Richard Foote. He may be a good Oracle DBA or consultant it's just that
I never saw that and find presentations of others much more convincing.
Hence I take his advice with a much bigger grain of salt than others.

[John Hurley]

zigzag:

Personally I cannot remember having to do more than a couple of
shutdown aborts in the last 10 years.

Very handy to use in extreme circumstances. In normal circumstances
avoid ... in my opinion a lazy habit.

Block cleanout and various kinds of oracle housekeeping can occur
after a database restart.

[Mladen Gogala]

On Sat, 03 Nov 2012 10:56:15 -0700, John Hurley wrote:


> Block cleanout and various kinds of oracle housekeeping can occur after
> a database restart.

Block cleanout doesn't occur on the instance startup, it occurs when the
blocks that need the old lock and session information removed are
referenced. That means that the block cleanout will happen during a query,
not during the instance start.

The practice of doing abort/start/shutdown is, of course, wrong. The most
likely reason for the "hanging" is, of course, recovery. Zigzagdna is an
old customer here who seems to be opposed to reading literature and using
the accepted methods of testing stuff. This is rather typical example of
the self inflicted problems that he usually comes up with. I tend to
ignore his posts.



--

[John Hurley]

I said can occur "after" ... not because of ...

[zigz...@yahoo.com]

On Friday, November 2, 2012 3:46:22 AM UTC-4, zigz...@yahoo.com wrote:

I can give you 10 different links which say there is nothing wrong with shutdown abort. I agree that one should try shutdown immediate first; but sometimes it takes forever!!!

[Mladen Gogala]

On Mon, 05 Nov 2012 20:41:00 -0800, zigzagdna wrote:


> I can give you 10 different links which say there is nothing wrong with
> shutdown abort. I agree that one should try shutdown immediate first;
> but sometimes it takes forever!!!

It doesn't matter what your "10 different links" say. It matters what the
manual says. And the manual says to use "shutdown immediate". If it takes
forever, it's probably an old version or a bug of some kind and you should
work with Oracle support to resolve it. If shutdown immediate is taking
forever, there is a problem. Your "10 different links" will not resolve
it. Oracle support might.



--
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com

[ddf]

I'll throw in my 2 cents here; to my knowledge it's not 'bad' to use shutdown abort, it's just preferable to NOT use it. Yes, Oracle will perform recovery on the subsequent startup but it won't likely recover/rollback everything. Since 7.3 (I believe) Oracle has implemented 'deferred transaction recovery' which allows the database to open before all of the uncommitted transactions have rolled back; as Oracle won't let uncommitted transactions be seen these uncommitted transactions will be rolled back when the blocks affected are accessed either by a select, an update or a delete. So recovery at startup only brings the database back to the state it was in when the 'shutdown abort' was executed. This can cause transactions to consume more time since the old changes may need to be rolled back before the new changes can take place. This leads to row lock contention as SMON rolls back the uncommitted transactions left from the 'shutdown abort' and the session performing the new changes ALSO attempts to rollback those same changes.

Unless it's a dire emergency I prefer to use 'shutdown immediate' and wait, so I don't have a situation where Oracle is fighting against itself to rollback uncommitted work after a 'shutdown abort'.


David Fitzjarrell

[joel garry]

On Nov 2, 8:04 pm, zigzag...@yahoo.com wrote:
> On Friday, November 2, 2012 3:46:22 AM UTC-4, zigz...@yahoo.com wrote:
> > I am on Oracle 11.1.0.7 on HP UNIX 11i. I have been using following procedure to shutdown database for a long
>
> > time:
>
> > 1. shutdown abort
>
> > 2. startup
>
> > 3. shutdown immediate
>
> > Today (after Sandy hurricane where my datacenter lost network connectivity), I noticed:
>
> > In the last step, database does not close, I have been waiting for 30 minutes and gave up. My database is only 50GB.
>
> > Any idea, why?
>
> > When I could not do step 3, I did again following steps:
>
> > 1. shutdown abort
>
> > 2. startup
>
> > Database comes up and I do not see any errors. I can access all my data. Can anyon eexplain, database will not shutdown without abort(why I have to wait forever, how I can troubleshoot).
>
> As I said earlier, because of Sandy storm, my site  where database and app servers are installed   lost wide area network connectivity. As a result I could not remotely logon to database server. Then inside the site, someone managed to connect using UNIX server console. Then the person was trying to shutdown oracle and restart it. We have scripts which were developed to be called from UNIx’s /sbin/rcx.d …when server reboots. Even though we  were not rebooting the server, we were using those scripts to recycle oracle and shutdown was getting hung.
> Today, our WAN connectivity was restored, so I could logon to DB server remotely and wow I could do all the three steps in 2-3 minutes and shutdown immediate completed in a minute instead of “hanging”  even after  30 minutes.
> May be, UNIX server was somehow trying to connect through wide area network while shutdown immediate was going on.. I do not know why it will need to go through  WAN when Oracle is being shutdown…; but restore of WAN made all the difference. All my disks on db server are “local”; i.e., we do not use SAN in this site.

If you have an rman backup going from somewhere else, it may be
possible for it to spawn parallel processes, which continue to try to
communicate with the parent that is no longer reachable. These oracle
processes would ignore a kill session command, requiring death from
the OS. I know I've seen this locally, when the parent is
accidentally killed, I'm speculating about WAN. But you could try it
if you have a suitable test setup, and it could possibly apply to any
parallelization operation.

> Thanks a lot for your insight on different ways of shutting down. I have read at few places, there is nothing wrong with  shutdown abort (other than users uncommited work will be interrupted) and  then doing  startup and then shutdown  immediate . You will not   risk  your database in anyway by doing 3 steps. Instead of just one step shutdown immediate.

Remember, most of those are simply quoting each other, some are "he-
man" implying you are a scaredy-cat to be afraid of shutdown abort and
that it can't be a problem if they've never seen a problem, and some
simply don't acknowledge bugs or extreme situations for educational
purposes. The one you've pointed at is certainly out of date.

jg
--
@home.com is bogus.

https://plus.google.com/photos/106910392693696947072/albums/5374880216169839969/5781744200831793442

[Jonathan Lewis]

Oracle Corporation has claimed for years that you can pull the plug on the
server and if the machine restarts the database will recover quite happily.
This is why a "shutdown abort" should be perfectly safe. (For the speed
readers, go back and look at the word SHOULD).

The fact that very few DBAs will deliberately do frequent shutdown aborts
means that there are code paths that there are probably circumstances where
a recovery after instance failure has not been tested - and that's why you
should avoid frequency shutdown aborts, especially if you are using some of
the exciting newer features of Oracle.

Nevertheless there may be cases where the application code makes it
impossible to do a clean shutdown within the required time frame and you
need to do an abort. To minimise the frequency with which you need to abort
you could use your favourite scripting language to do as I did many years
ago:
a) Check sum(used_urec) from v$transaction - if the result is "very large"
(interpretation is system dependent) then shutdown abort
b) Spawn a watching process that sleeps for N minutes
c) Initiate a shutdown immediate

If the watching process wakes up and finds the database still up, then it
issues a shutdown abort. Either way it terminates itself
The driving process will either get a "succesful shutdown", or "instance
died" - so it can tell how the database went down.

If you really feel it's necessary, you can always startup restrict /
shutdown immediate after an abort.


By the way - since a startup SHOULD (note that word again) be successful
after any type of instance failure, then there SHOULD be no need to startup
and shutdown after an abort - it's the database is going to survive it's
going to startup whether you start it 10 seconds or three hours later. The
only reason why you might want to get to a "clean shutdown" state is if you
follow up with a backup strategy that doesn't copy the online redo logs.


Bottom line - shutdown abort SHOULD always be safe, but for statistical
reasons DBAs don't want to assume that the instance recovery code-path is
perfect in every way: so we only do aborts if we really have to (which
means the recovery code-path doesn't get very much field-testing).

--

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings

[Mladen Gogala]

On Wed, 07 Nov 2012 10:03:48 +0000, Jonathan Lewis wrote:

> The only reason why you might want to get to a "clean shutdown" state is
> if you follow up with a backup strategy that doesn't copy the online
> redo logs.

Not quite true. I have once lost a database when I tried to re-create
control file after shutdown abort. The idea was to make the database OPS.
I needed to increase "maxinstances" parameter in the control file. It was
an 8i database. Fortunately, I was able to restore it from the backup and
it was a development database, but I have ruined my weekend. By the way,
the same method of "racification" (what an ugly word, Mogens would be
delighted) works today:

1) Re-create control file
2) Add the needed number of log threads
3) Add the needed number of UNDO tablespaces.

The only step that wasn't there in OPS is adding the database and its
instances to OCR using srvctl.