?
Solved

Shutdown immediate hangs when used in backup scripts

Posted on 2005-05-09
9
Medium Priority
?
1,886 Views
Last Modified: 2013-12-11
Occasionally our weekly cold backup script fails due to receiving a 'shutdown: waiting for active calls to complete' message in the database's alert log (this situation occurs across a number of databases at various times, never any one specific database). I've researched this issue here, at Metalink and around the internet in general but have not seen a definative answer to the problem.  Ask Tom website gives as resolution of "do not perform cold backups" which does not seem like an appropriate answer. I've heard that an approach to this problem might be to add code into the existing backup script to locate and 'kill' any client connections to the database(s), specifically looking for SID processes that contain (LOCAL=NO). Is there a better approach to this problem (other than scripting) and if not, is there anyone here that might have a script (or portions of their backup script) that would enable such process checking/termination as part of their current weekly cold backup script?  Our platform is Solaris 2.6/2.8 and Oracle version 8.1.7.4/9.2.0.4-6 and 10.1.0.4 (note: no such shutdown issues have been noted in 10.1.0.4). This problem has been a long time issue, it would be great to finally work around it (or even better, resolve it). Thanks for your assistance.
0
Comment
Question by:tmike2005
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 6

Expert Comment

by:jwittenm
ID: 13960192
In the past, on Oracle 8i and 9i systems, we have either written the types of scripts that you are talking about, or done the following:
Shutdown abort
startup restrict
shutdown immediate
0
 
LVL 21

Expert Comment

by:oleggold
ID: 13970022
I think that "Shutdown abort" can be very dangerous because any current transaction are killed.It's much better to identify currently running user sessions and kill them when possible if done manually.
For authomatic handling the best option is to define a default profile and limit its connect and definetly idle time.
Also it's possible to perform a forcible log switch before a shutdown to resolve any archiving issues if exist.
Hope it helps
0
 
LVL 48

Expert Comment

by:schwertner
ID: 13976317
Use
SHUTDOWN IMMEDIATE
You can even use dbshut.sql script via dbora, passing the needed parameter there.

SHUTDOWN IMMEDIATE works. Obviously you use SHUTDOWN and this is the cause to wait for closing of opened connections.

SHUTDOWN ABORT is not recommended for normal use.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Expert Comment

by:jwittenm
ID: 13978799
Shutdown immediate waits for current transactions to commit or rollback.
Shutdown abort is no more dangerous than a hot backup.  People used to be afraid of those, too.
0
 

Author Comment

by:tmike2005
ID: 13986885
Appreciate the responses so far -- I was hoping this issue had been a problem for someone here at one point or another and if so, what was their approach to the problem (i.e., was there an actual resolution whether it was a script written to seek out and 'kill', a specific setting in sqlnet, etc.).

It's appearing that the option might be to simply perform hot backups for our weekly saves (is what Oracle's stand is beyond usnig shutdown abort, startup restrict, shutdown in sequence. This is fine but I'd personally rather have at least one cold backup each week to have a fall back on any particular db that was in a solid state of consistency versus potentially loosing transactions at the point of the shutdown abort operation. Log switching is fine to lessen the potential data loss but if there are active transactions going on at the time of the actual shutdown abort, then what?  

Oleggold -- your suggestion regarding defining a default profile and managing connection timeouts would help if the db server was strictly client-server architecture. However, many of the databases (nearly all) are accessed through web applications. As you might suspect, web applications are not all that easy to control when it comes to connection management.

I am going to apply sqlnet_timeout.expire=1 to sqlnet.ora  to see if that will help take out any potential dead connection. Anybody else have a comment regarding this potential angle of attack to the problem?

tmike
0
 
LVL 6

Accepted Solution

by:
jwittenm earned 1500 total points
ID: 13988128
I've used this approach on systems ranging from the busiest web site in the world to a terabyte plus data warehouse.  If you want to wait for all transactions to finish before you shutdown, then 'immediate' is the proper way to do it, but you have no way of knowing when it will actually shut down.  Again, 'immediate' waits for transactions to commit or rollback.  Your problem with 'immediate' is not with 'dead connections' (which is what the sqlnet.expire_time takes care of).  Beware of the increased network load that the dead connection detection will impose.  Especially at a setting of 1.

Abort uses Oracle's standard, proven (you and everyone who uses the database use it every day.  hit <control-c> in the middle of an update in sqlplus and it does the same thing) functionality via redo and rollback.  Shutdown abort kills the transactions and shuts down the instance 'now'.  Startup restrict allows the instance to do normal instance recovery (using redo and rollback) without outside interference.  When the database opens, issue shutdown immediate to get a clean state.  The only transactions I would expect you to have would be from 'outside' if you have an outward facing site, since those people won't know when you are taking it down.  Internal apps should be aware of the normal down time and not be running anyway.  Any transactions that get killed will be rerun (if the app developer had any sense at all) when the database comes back up.

With all that said, there is no reason not to rely on hot backups.  I'm working on a multi-terabyte RAC cluster now that will NEVER have a cold backup taken, post implementation.  Take the hot backups.  Test the hot backups (easy with RMAN).  Rely on the hot backups.  Was this always the case?  Absolutely not.  But things have changed a lot in the last 5 or 6 years.  RMAN is rock-solid now.  It does not cost extra.
0
 
LVL 6

Expert Comment

by:jwittenm
ID: 13988229
Whew.  with all that said, they are just suggestions.  i have no vested interest :)
you have to decide what is right for you.
0
 

Author Comment

by:tmike2005
ID: 14013292
It's hard to debate the issue of only using hot backups versus cold -- jwittenm's point (along with his explanation) is well taken. However, I am in a situation (work) that requires once a week cold backups as part of corporate procedure. There are many servers all running Solaris 2.6, some with 2.8 and all with 8.1.7.4.  The hung database issues always seem to appear on one specific server which makes one wonder what the differences between the hung server versus all the others. A close inspection of each shows they are setup identical other than small variations in shared memory settings to accomodate the db count, SGA sizes, etc. The idea of trying to terminate dead connections as I mentioned earlier is simply an untried option. I implemented the sqlnet_expire.time to see how or of that would have any impact on the situation when bringing the databases down each Sunday evening for the cold backups. Although too early to say, this past weekend all databases on the suspect  server shutdown nicely. More investigation has to occur regarding what was happening at the time but it was the first time these databases shutdown cleanly in many weeks.

It seems to me that other than a long batch job happening at the time of the shutdown immediate request, that the database should come down fairly quickly (as has been the normal experience) versus taking hours to shutdown (and in our case, going beyond the time when the databases need to be back up some 4 plus hours later on early Monday morning). And the hung condition occurs randomly amongst the databases on the server -- and most of them are either not active at all during the weekend or minimal activity.

Unless there is somebody else out here that has a different opinion on how best to handle this problem, it seems that the only answer is to avoid cold backups (on at least this suspect server) and opt for hot backups. Corporate needs of a cold backup aside, if the only alternative is to wait each week to see if there is a database still in a state of shutdown which causes more pain than it's worth (loss of backing up the database to off-line storage, customers not being able to access their applications which use the database, etc.) then all I can do is to present those alternatives. Obviously I was hoping to find a work-around to handle the cold backups and avoid the possible issue with the database taking forever (so it seems) to shutdown (without abort).

Anyone care to comment further?  I appreciate any and all feedback.

 
0
 
LVL 48

Expert Comment

by:schwertner
ID: 14016484
Oracle software is as every other software. It is not perfect.
If you are able and have resorces to do this, reinstall (Export, reinstall, Import) the database.
You understand - sometimes we clean our houses.
This helps, especially if you upgrade the software version.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month17 days, 8 hours left to enroll

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question