Home » RDBMS Server » Server Administration » Database not Shutting Down
Database not Shutting Down [message #241009] Mon, 28 May 2007 03:40 Go to next message
LaFilipina
Messages: 46
Registered: May 2007
Location: spain
Member
Hi,

I am having problem with one of our databases... it seems like nothing can connect to it. I logged in as sysdba via sqlplus and tried to issue shutdown immediate on the database. After some minutes, it doesn't seem like it is shutting down. I tried the kill the sqlplus process by issuing kill -9 against the process. I believe it made the whole thing even worse Sad ... now I can't connect to the database in any way.. it is just giving me this message:

ERROR:
ORA-01089: immediate shutdown in progress - no operations are permitted

I also noticed that there was a smon trc file generated in the bdump folder... here is what is written in there that i think is worth looking at:

*** SESSION ID:(5.1) 2007-05-19 14:05:32.901
Using 5 slaves for 1 dead transactions
Recovered xid: 0x000b.035.00023189 in 20 milliseconds; nchk=0, size=14

What should I do to restart the database? Is there any serious problem that is causing this whole thing?

I am really looking forward to hearing from your expert opinions.

Thanks!
Re: Database not Shutting Down [message #241024 is a reply to message #241009] Mon, 28 May 2007 03:59 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
did u check shutdown abort?
can u paste last 50 lines from alert log?
RESTART the machine if you dont have other databases running on that.
Re: Database not Shutting Down [message #241026 is a reply to message #241009] Mon, 28 May 2007 04:03 Go to previous messageGo to next message
LaFilipina
Messages: 46
Registered: May 2007
Location: spain
Member
I issued a kill -9 on the smon and the database seemed to shutdown... I tried to restart it again and it started fine. Will there be any bad implication on issuing kill -9 on the smon (that seems to be hanged)?

Thanks.
Re: Database not Shutting Down [message #241029 is a reply to message #241024] Mon, 28 May 2007 04:06 Go to previous messageGo to next message
LaFilipina
Messages: 46
Registered: May 2007
Location: spain
Member
DreamzZ wrote on Mon, 28 May 2007 03:59
did u check shutdown abort?
can u paste last 50 lines from alert log?
RESTART the machine if you dont have other databases running on that.


Hi,

Thanks for the quick response.

I can't try shutdown abort since everytime I try to login in to sqlplus it is telling me that no other connection can be admitted.

I can't restart the machine since there are other databases running on the same server.

I have these from the alert:

Shutting down instance (immediate)
License high water mark = 10
Mon May 28 04:10:06 2007
ALTER DATABASE CLOSE NORMAL
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
Mon May 28 04:10:06 2007
ALTER DATABASE DISMOUNT
Mon May 28 05:00:43 2007
Errors in file <path>bdump/pmon_15124_<sid>.trc:
ORA-00474: SMON process terminated with error
Mon May 28 05:00:43 2007
PMON: terminating instance due to error 474
Instance terminated by PMON, pid = 15124
Mon May 28 05:00:56 2007
Re: Database not Shutting Down [message #241032 is a reply to message #241029] Mon, 28 May 2007 04:08 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Wuts your oracle version and os?

try to kill all process associated with the same isntance

[Updated on: Mon, 28 May 2007 04:10]

Report message to a moderator

Re: Database not Shutting Down [message #241037 is a reply to message #241032] Mon, 28 May 2007 04:17 Go to previous messageGo to next message
LaFilipina
Messages: 46
Registered: May 2007
Location: spain
Member
Oracle version is 8.1.7.4 and OS is Unix HP-UX 64
Re: Database not Shutting Down [message #241040 is a reply to message #241037] Mon, 28 May 2007 04:18 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
when you type
sqlplus sys as sysdba
in da mean while u get this error??
Re: Database not Shutting Down [message #241044 is a reply to message #241040] Mon, 28 May 2007 04:24 Go to previous messageGo to next message
LaFilipina
Messages: 46
Registered: May 2007
Location: spain
Member
DreamzZ wrote on Mon, 28 May 2007 04:18
when you type
sqlplus sys as sysdba
in da mean while u get this error??

I am not sure I understood your question quite well... But here's what transpired:

1. There was no connectivity can be done to the database (OEM, sql plus(gui), etc)
2. I logged in to sqlplus(from the console) and issued a shutdown immediate... the shutdown process was not finishing.
3. I killed the sqlplus proess via unix prompt by issuing kill -9
4. After this, I can't log in to sqlplus (from the unix prompt) anymore.. it is telling me that the shutdown immmediate process is on going and that there is no other connection can be accepted.
5. I killed the smon process using kill -9 via unix
6. This seemed allowed the database to complete the process of shutting down so I am now able to restart the database.

Though I am not sure if what I did wouldn't cause any harm on the database. Now, I have pmon trace file generated on the bdump folder as well. But the db seemed to be running fine. I am also in the process of running a cold backup.
Re: Database not Shutting Down [message #241387 is a reply to message #241044] Tue, 29 May 2007 07:39 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

HI,
Errors in file <path>bdump/pmon_15124_<sid>.trc:
Are you check above trace file ?

issued a shutdown immediate... the shutdown process was not finishing.
That time you should use SHUTDOWN ABORT instead of KILL process.


@DreamzZ
Please Don't use IM SPEAK !!!


regards
Taj
Re: Database not Shutting Down [message #241391 is a reply to message #241387] Tue, 29 May 2007 07:53 Go to previous messageGo to next message
LaFilipina
Messages: 46
Registered: May 2007
Location: spain
Member
Mohammad Taj wrote on Tue, 29 May 2007 07:39
HI,
Errors in file <path>bdump/pmon_15124_<sid>.trc:
Are you check above trace file ?

issued a shutdown immediate... the shutdown process was not finishing.
That time you should use SHUTDOWN ABORT instead of KILL process.


@DreamzZ
Please Don't use IM SPEAK !!!


regards
Taj


Yes, I should have used shutdown abort instead of shutdown immediate... but the moment I issued shutdown immediate and it was hanging right there, I can't log in the database anymore even as sysdba.. it is just telling me that shutdown immediate is in progress and is not allowing any type of connection... I thought I didn't have any other option but to manually kill the smon... right after I issued a kill -9 smon, the pmon trace file was created. I didn't have the pmon trace file when the database is still on a hanged status.
Re: Database not Shutting Down [message #241416 is a reply to message #241391] Tue, 29 May 2007 09:21 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
when you fired shutdown immediate that time any trasaction running on database if yes then shutdown immediate take time.

Keep in mind for next time.
whenever shutdown immediate hang ... you have to connect again with sysdba privs through SQLPLUS COMMAND LINE UTILITY.
like
c:\> sqlplus /nolog
SQL> conn sys/pwd as sysdba


and fire
 shutdown abort 


regards
Taj
Re: Database not Shutting Down [message #241639 is a reply to message #241416] Wed, 30 May 2007 04:32 Go to previous messageGo to next message
LaFilipina
Messages: 46
Registered: May 2007
Location: spain
Member
Hi Mohammad Taj,

When shutdown immediate hangs... it is not allowing me any type of connection.. even from comman line utility like you mentioned... I tried that several times and it is telling me that connection is not allowed because a shutdown immediate is in progress...

Take note that I am working on an 8i environment... so perhaps this is one reason why I am encountering that message after a shutdown immediate hangs.
Re: Database not Shutting Down [message #241680 is a reply to message #241639] Wed, 30 May 2007 07:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64126
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Follow messages in alert.log.
Shutdown immediate does not hang, it wait for end of current calls and rolls back all transactions.
This may take hours if you have long transactions.

If you are really blocked, kill all processes and shared segment and restart the database, wait it finishes to clean all things, then shutdown and restart it.

Regards
Michel

[Updated on: Wed, 30 May 2007 07:03]

Report message to a moderator

Re: Database not Shutting Down [message #241709 is a reply to message #241680] Wed, 30 May 2007 08:11 Go to previous messageGo to next message
LaFilipina
Messages: 46
Registered: May 2007
Location: spain
Member
Michel Cadot wrote on Wed, 30 May 2007 07:02
Follow messages in alert.log.
Shutdown immediate does not hang, it wait for end of current calls and rolls back all transactions.
This may take hours if you have long transactions.

If you are really blocked, kill all processes and shared segment and restart the database, wait it finishes to clean all things, then shutdown and restart it.

Regards
Michel



Right.. It may not have really hanged ... but the status of the database to begin with is already bad. We have a cold backup that runs every saturday.. it failed and for some reason, the database left on a state that connections are being refused.

As I've said, there is no other kind of connection the database is allowing... not from OEM, not from sqlplus issued from the console(in our case unix box), not anywere... not even as sysdba. If the shutdown immediate is not finishing and we wouldn't know how long it'll take (maybe hours and hours) before it finishes... it's kind of risky to wait before the database could be restarted again.

Does anyone know if this behaviour of Oracle (not allowing any sysdba connection via sqlplus issued from console while shutdown immediate is still on going) is just particular to 8i?

Re: Database not Shutting Down [message #241723 is a reply to message #241709] Wed, 30 May 2007 08:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64126
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The solution is:
Quote:
kill all processes and shared segment and restart the database, wait it finishes to clean all things, then shutdown and restart it.

Regards
Michel
Re: Database not Shutting Down [message #242078 is a reply to message #241723] Thu, 31 May 2007 09:37 Go to previous messageGo to next message
krblr
Messages: 24
Registered: April 2007
Location: MD
Junior Member

Agree w/ Michael Cadot, plus others here, kill all of them even remotely related to the SID on that machine. Therefore, 1) At this point, your best opion is to ask your UNIX admin to schedule a reboot (if that is at all possible). 2) If not 1), do something similar to "ps -ef|grep -i <Your_SID>" and kill all that are eligible, even the background procs. Also do a "lsof -n|grep LISTEN" and kill every single one of them listening on ports you have defined, and then try getting logging in as sysdba.

Next time do a shutdown abort. Usually a kill on a pmon will do it, but the bg procs may have got stuck in a real bad state in your case.
Re: Database not Shutting Down [message #242091 is a reply to message #242078] Thu, 31 May 2007 10:19 Go to previous messageGo to next message
LaFilipina
Messages: 46
Registered: May 2007
Location: spain
Member
Thanks, krblr!

Thanks everybody for all your help. I'll definitely take note of this so next time I'd know better what to do in case similar thing will happen with on of our databases.

Thanks again!
Re: Database not Shutting Down [message #242120 is a reply to message #241032] Thu, 31 May 2007 13:19 Go to previous message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
DreamzZ wrote on Mon, 28 May 2007 05:08
Wuts your oracle version and os?

try to kill all process associated with the same isntance


Would you please write in plain english (and not in IM speak) so you don't sound like some illiterate fool.
Previous Topic: ORA-25254: time-out in LISTEN while waiting for a message
Next Topic: please help
Goto Forum:
  


Current Time: Wed Dec 07 06:43:47 CST 2016

Total time taken to generate the page: 0.23505 seconds