How do you shutdown Oracle 11g running on windows?

[aking]

Hi
I have 5 running Oracle servers, currently we are upgrading from 10g => 11g. I am searching for the definitive answer to the question: Does Oracle shut down automatically when Windows is shut down? Or does it need to be shutdown manually first?

Our original Oracle consultant (who had a unix background) advised me to always shut the database down at the command prompt by 'shutdown immediate'. I have been doing this since 2005.
Our new Oracle consultant (more of a windows background) insists that this is not necessary and that all i need to do is shutdown windows and Oracle will automatically shutdown.

i have noticed this before with Oracle - different opinions and the difficulty in finding out a validated answer. It is very time consuming... is it some kind of sales strategy?
I have looked in the documentation - nothing definitive in there that i could see.

I have compared the Alert log of when i shutdown the database manually before shutting down windows, compared to when i just shut windows down, they are different. I think the Alert Log entries are worse in the case where windows was shut down without shutting oracle down first.
e.g. 'Beginning crash recovery of 1 thread' or 'Completed redo application of 1.52MB'.
But i am not sure, i could post the complete alert log for manual and automatic shutdowns but it would be quite long....

Does anyone know the answer? I have sent the complete alert log to the our new Oracle consultant and so far (1 week) there has just been a deafening silence. Is this really such a difficult question?

 

[SantaMufasa]

AKing,

Oracle has the mechanisms in place to perform a recovery (via on-line redo logs) whenever an abnormal termination occurs to the database. Abnormal terminations take place when the Oracle database instance goes down without a graceful "shutdown immediate". A "shutdown abort" or a power failure are examples of abnormal terminations. Another example of an abnormal Oracle termination is an o/s shutdown (without a "shutdwon immediate").

Therefore, Oracle "takes care of business" via crash recovery.

Shutting down Windows without gracefully shutting down Oracle first is like shutting down Windows via a "power off" without performing an orderly "Shutdown".

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[Turkbear]

Hi,
When I managed several Oracle servers, I alwys shut down the instance first, then shut down the Windows services - While the Windows shutdown is supposed to issue a 'shutdown immediate'
(There is a Registry entry that can alter this)
before stopping, I preferred not to leave it to Windows . If your data is important ( and if not, why keep it) then why not be sure?

Here are a couple of articles ( some older) about that issue:

http://www.sap-img.com/oracle-database/shutting-down-oracle-on-windows.htm

http://download.oracle.com/docs/cd/B28359_01/server.111/b28301/instance002.htm#CHDEFDDI

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[KenCunningham]

I concur with Santa and Turkbear. It's not much of an overhead to do the greaceful thing if it ensures the integrity of your database.

The internet - allowing those who don't know what they're talking about to have their say.

 

[aking]

Thanks, that seems pretty conclusive.
I take this to mean that while Windows supposedly tells Oracle to shut down first no-one is really sure if this really works and all experts shutdown their databases manually.
Oracle and Windows not talking to each other has definitely been the worst part of looking after Windows Oracle servers, so this makes perfect sense!

The other thing i should have mentioned (the actual reason this question came up) is that my new Oracle consultant doesn't like me to do 'shutdown immediate' - he says it's bad, and equivalent to crashing the database.
But when i do 'shutdown' the database will sit there all day, then it will say it can't close as there are still active processes. (ORA-01013).
We use Oracle for one major application. I can close that application and stop all the associated servcies - it makes no difference. Oracle will only shutdown with the immediate switch. Which is why i was hoping to forget about it all together and just shut down windows.
Now i guess i will investigate why my database won't shut down normally. Which involves talking to metalink - so might have an answer by xmas. ;-)
But i bet i know the answer already, i bet when you do 'shutdown' then Oracle waits for some signal from the O/S that never arrives when it is running on windows. Which comes back to square one - Oracle and Windows don't talk to each other. Which has been the root of every single problem i've had with this database.

 

[KenCunningham]

shutdown with the immediate option is what I always use, since as you say shutdown alone waits for ever.

The immediate option does a graceful job as far as I know. Perhaps your consultant is confusing immediate with abort, which is rather more of a blunt tool and only used when there is no other option.

The internet - allowing those who don't know what they're talking about to have their say.

 

[Turkbear]

Hi,
A 'standard' shutdown will wait until all pending transactions have committed or rolled back ( I believe) and that can be a long time if any users have left their connection on and not committed their work.

A shutdown immediate does a 'clean' shutdown by rolling back any uncommitted transactions before the shutdown, a much faster process.

We never shutdown the databases (unless something required it) while any users will still connected but we still used the immediate option.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[karluk]

You are getting good advice in this thread, but I think someone needs to explicitly address the terrible advice you are getting from your new Oracle consultant. I don't wish to be too harsh, since we are only getting second hand what he is telling you, but based on what you're telling us I would tend to question how much he really knows about Oracle. For some reason he doesn't like "shutdown immediate", so his solution is to instead simply shut down Windows while the database is still running. But, as you have noticed from your examination of the database alert log, all this does is to cause the instance to crash and force Oracle to perform a crash recovery when the instance is restarted. If you have clearly communicated your findings to your consultant and he still prefers bypassing an orderly database shutdown via "shutdown immediate" in favor of intentionally crashing the database, then I would say you badly need to dump your consultant and find someone who understands Oracle better.

 

[aking]

Thanks guys. So basically I don't have to worry about using 'shutdown immediate' - that's reassuring.
Why couldn't Metalink just tell me that? For 2 weeks i have been asking them! and i have been posting alert logs and trace files....
But yeah, maybe it's time for a new consultant - hmmmm.
Anyway thanks for all advice.

 

[SantaMufasa]

AKing,

All of the above guidance from our Tek-Tips Oracle friends is absolutely correct advice (including the questioning of the qualifications of the "Oracle consultant").

Here then is a briefing of the different behaviours of the various Oracle shutdown options (from most benign to most abrupt/"lethal"):

[ul][li]SHUTDOWN (which equals a SHUTDOWN NORMAL):[/li]
[ul][li]The database waits for all users to disconnect on their own (which is why it seems to take forever for an instance to go down),[/li]
[li]prohibits new connections,[/li]
[li]closes and dismounts the database,[/li]
[li]then shuts down the instance.[/li][/ul]

[li]SHUTDOWN IMMEDIATE.[/li]
[ul][li]Cancels current database calls,[/li]
[li]Rollsback "in-flight" transactions (which is why it takes at least a brief amount of time for this option)[/li]
[li]Closes and dismounts the database,[/li][li]then shuts down the instance.[/li][/ul]Note: PMON (the Process MONitor) gracefully shuts down and rollsback the user processes. No instance recovery is required upon startup.

[li]SHUTDOWN ABORT.[/LI]
[ul][li]This doesn’t wait for anything. It shuts the database down now (as though someone shut off the electricity to the server). Instance recovery will take place upon startup. You should use this option only after failed attempts at a SHUTDOWN IMMEDIATE.[/li][/ul][/ul]

I hope this helps to explain things a bit.



Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[Beilstwh]

You will also find since Oracle 8i that shutdown abort is totally safe. A number of high availability companies use shutdown abort to get the database down fast since the recovery is done on startup while the database is open and available. For a fairly large example, AMAZON has always used shutdown abort to minimize downtime.

Bill
Lead Application Developer
New York State, USA

 

[stefanhei]

I would not say that shutdown abort is totally save. It is more or less the equivalent of killing the database processes on os-level. Amazon may have its application built in a way that it can ensure integrity in case of ungraceful shutdowns.
Unless forced to by something disastrous like os-corruption or hardware malfunction I would not use that option.

 

[aking]

My conclusions:
Oracle recovery no matter how you shut it down is very solid - i.e. it always seems to come back!
'shutdown immediate' is what works and what i will continue to use and what causes the least amount of erros in the alert log.
'shutdown' doesn't work and there is no point in ever using this command (for me), the database will sit just there, eventually it will report the ORA0103 error in the alert log, in the mean time it just carries on running.

I got an answer out of metalink yesterday (2 open calls and 1 complaint got me a link to this article):
Shutdown is Cancelled With ORA-1013 After Waiting for an Hour [ID 305666.1].
I don't think this is a final answer. It gives me one piece of information that i didn't know and could have been imparted in 5 minutes rather than taking 3 weeks(!). i.e. that Oracle waits for 1 hour for all processes to terminate and if they don't then it cancels the shutdown request.

Q: Why can't I shut my Oracle database down with the command 'shutdown'.
A: Because the Oracle database waits for an hour for all active logins to complete.

No, that is not a definitive answer.
What are the active processes which are running?, why are they not being shutdown?, how to shut them down?, what would be the implications? etc.
These are the (obvious) questions implied by my original question. But 3 weeks is long enough to bang your head against a brick wall. Oracle support is a big challenge, every support call turns into a basic english lesson. Oracle consultants seem to be variable ;-). My last one has vanished since he came in to do the 11g upgrade - it has to be said that the 2 databases he created are working fine and the only question is this shutdown question - to which he has stopped answering emails (it's all very wierd).
The best place for Oracle answers seems to be forums like these.
So a storm in a teacup really, the system works and i haven't really learned much new, the stressful bit is trying to get a definitive answer to anything, but otherwise the database just works (and faster than anything else) so i have to put up with it.
After saying more than once that my question hadn't been tackled and rephrasing it more and more simply and saying that i wasn't satisfied metalinks final response was:
Would you like to recommend the analyst who assisted you for an exceptional service nomination?
Sums it up really...

Thanks for the advice guys, i should learn to just leave this database alone - would save me time.!

 

[KenCunningham]

I think you hit the nail of the head. It's sometimes dangerous for these people to give 'definitive' answers because they could come back and be used against them id anything untoward happens, so you're often better off going with the advice of people at the sharp end in the real world, as long as they're trustworthy like the Tek-Tips contibutors of course ;-)

The internet - allowing those who don't know what they're talking about to have their say.

 

[stefanhei]

aking,
you can find the active logins by querying v$session before issuing shutdown normal:

select username,program,machine
  from v$session
 where type != 'BACKGROUND';

If there is any other session displayed than yours the database will not cancel it but will wait for it to exit on its own (as Santa already pointed out).

 

[SantaMufasa]

I should learn to just leave this database alone...

That is one method, but what happens if "something bad happens to good people" (where you and your organisation are the "good people")?


There is no question that the animal they call "Oracle" is a very complex creature. If your job depends upon your Oracle database's continued successful running, then I recommend your getting training that causes an increase to both your comfort level and your proficiency level with Oracle.

You needn't take expensive instructor-led training (ILT), although some people prefer ILT. You can become proficient with a variety of high-quality texts and with computer-based tutorials.

Let us know if you would like testimonials on good books, ILT, or computer-based tutorials, specifically tailored for learning Database Administration.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[aking]

Yes the business depends on Oracle, but I don't ever want to think that my job depends on this database.... And if Oracle really exploded we would turn to SQLServer. The reason we don't is because Oracle runs significantly faster, I found this out the hard way by switching to SQLServer and then having to switch back, I think it is pretty obvious that i am not an oracle fan, but i do appreciate the performance, and i also appreciate how good oracle is at recovering - in 6 years we have only had one really bad incident, and that does beat windows by a long way i must admit

The bottom line is that i don't agree with the support model, support is either atrocious, or very expensive or - as you suggest - you have to learn it yourself, and that is not quick.
I look after vmware, exchange, windows servers on a few domains and all that that entails, I have enough on.
I'll probably never create a database or need to know oracle that deeply from that angle. I went on the course and I stopped after the first module - it was trying to teach me the wrong things, things i would never use. I was the only guy on the course who had ever installed Oracle, and the only guy who didn't know SQL - i knew straight away i was in the wrong place....

When i started I needed to know: how to install it, how to configure it, how to back it up, how to check if it is broken - the admin stuff....

i didn't need: database creation, managing storage structures, managing schema objects, managing data and concurrency, managing undo data etc etc ad infinitum....
The app guys who built the pharmaceutical regulatory software that we use that runs on top of Oracle handle all that.

If there is a course for unlucky windows idiots like me who have strayed by mistake into Oracle-land and need to know how to administer it then that is the course for me. But i don't think it exists.? Anyway I could teach half this course myself by now ;-).

I was trained in a really different approach, much more learn-as-you-go and much more interactive with support.
Private Oracle support in England ranges from £800 - £1500 a day which i think is prohibitive. Whereas official metalink support probably works out as pennies per day - but isn't even worth that(!), I think it must only survive because you need it for patch downloads.

If there exists computer based tutorials strictly for database administration for running Oracle on Windows (specifically windows and not linux) please point me to them, I couldn't find any when i first started. I estimate 50% of my time on Oracle has been finding out the little glitches between oracle and windows that are undocumented. Like having to create certain environmental variables in 9i to stop Enterprise Manager crashing periodically. Knowing things like that to keep oracle working on my network day to day are a higher priority than knowingthe ins and outs of the v$ schema.

Forgive me posting at such length but i wanted to give the windows sysadmin perspective on Oracle. I don't want to learn it back to front i just want to drive it. You can drive a car without knowing the deep physics of a combustion engine.....