Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: shutdown hang

Re: shutdown hang

From: Stephen J Palmer <spalmer_at_brk.photronics.com>
Date: Wed, 24 May 2000 16:41:20 -0400
Message-Id: <10507.106588@fatcity.com> 





What we do about locks is run this script to determine if it is in fact a
lock...




$ cat locks.sql


select


 l.type,

 rpad(to_char(l.sid)||','||to_char(s.serial#),15) CONN,
 rpad(s.schemaname,12),
 rpad(s.osuser||'-'||s.machine ,20) ID,




 l.ctime TIME,


 l.block BLOCK


from v$session s, v$lock l


where l.sid = s.sid;





And then run the following script to determine the locked table......




$ cat lockinfo.sql


set linesize 130


set pagesize 200


column id_info format a40


column obj_mode format a25


select


 lo.oracle_username||' '||se.osuser||'-'||se.machine||' ('||
 to_char(se.sid)||','||to_char(serial#)||')' id_info,
 ao.object_name||'-'||to_char(lo.locked_mode) obj_mode
from v$locked_object lo,

     all_objects ao,
     v$session se
where lo.object_id = ao.object_id and
      lo.session_id = se.sid;
___________________________________________





Then kill the session ID that has the hanging lock. This seems
to happen to us, if someone crashes out of third party apps...
like SQL/Navigator.



We also have a PERL script that kills hanging locks after a
given amount of time.



HTH


Steve



Winnie_Liu_at_infonet.com wrote:



> Teresa,

>

> I don't think I can answer your question staight. I have seen tables that

> had been locked for minutes, hours to about 1 day.

>

> Any other DBA have similar experience that can shared with Teresa?

>

> Winnie

>

> "Southerland, Teresa" <tsouther_at_indiana.edu> on 05/24/2000 01:15:30 PM

>

> Please respond to ORACLE-L_at_fatcity.com

>

> To:   Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>

> cc:    (bcc: Winnie Liu/HQ/ISC)

>

> Winnie,

>

> I have an off the wall question.  If there was a transaction running that

> executed 120,000 rows and had to rollback without shut down to the

> database,

> what kind of time frame would this take?

>

> Thanks,

>

> Teresa

>

> -----Original Message-----

> Sent: Friday, May 19, 2000 1:16 PM

> To: Multiple recipients of list ORACLE-L

>

> Joan,

>

> The reaon is that in your first startup, Oracle does NOT clean up

> everything yet. It opened the database for you to use, but still cleaning

> up the processes inthe background. Those clean up may take any time from

> minutes to days (from some Oracle guru in Oracle corporation).

>

> If you are looking at the alert.log file after those startup, you should

> see something like

>

> SMON: enabling cache recovery

> SMON: enabling tx recovery

>

> The database is up and running fine, but SMON is doing the recovery at the

> background.

>

> Until you see the words:

>

> SMON: disabling cache recovery

> SMON: disabling tx recovery

>

> The recovery done by SMON is not done yet. This process may take hours and

> days if you are temporary segments is huge and there are a lot of clean up

> need to be done by SMON.

>

> When you do a shutdown immediate, since you are asking Oracle to shutdown

> the database cleanly. SMON had to finish that job before shutting down the

> database. Those hours/days will then add to your shutdown time.

>

> So the best method for me (which I also get from Oracle) is to do a

> shutdown abort and then a startup and wait for the SMON recovery to be

> finished in the background.

>

> There are a few events that you can set to free up SMON from performing

> those daily activities and concentrate on the recovery. If you are

> interested, I will send them to you.

>

> Hope it makes more sense now.

>

> Winnie

>

> Joan Hsieh <jhsieh_at_infonet.tufts.edu> on 05/19/2000 07:28:05 AM

>

> Please respond to ORACLE-L_at_fatcity.com

>

> To:   Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>

> cc:    (bcc: Winnie Liu/HQ/ISC)

>

> Winnie,

>

> I did shutdown abort and startup (took 20 min.) What concerns me is

> after startup, I did shutdown immediate again. It hangs (more than one

> hour and didn't comeback) NOBODY on the system. My question is the first

> startup should clean up everything, why shutdown immediate hangs after?

>

> Thanks,

>

> Joan

>

> ORACLE-L_at_fatcity.com wrote:

> >

> > It is perfectly normal. But if I were you, I will shutdown abort and

> > startup again (oracle is actually cleaning up the processes in the

> > background, they are doing the same job as if you issue the shutdown

> > immediate). At least the database is up and running while Oracle did the

> > cleaning.

> >

> > My worst nightmare is to wait for 14 hours for it to get cleaned up.

> >

> > Winnie

> >

> > Joan Hsieh <jhsieh_at_infonet.tufts.edu> on 05/18/2000 02:19:39 PM

> >

> > Please respond to ORACLE-L_at_fatcity.com

> >

> > To:   Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>

> > cc:    (bcc: Winnie Liu/HQ/ISC)

> >

> > Hi List,

> >

> > One of the developers deleted more than 120,000 rows on a  small

> > database. In the middle of the session, he killed the process.(because

> > hang) I did shutdown abort and startup again (waited a while whitout

> > problem) and want to shutdown immediate and startup over again. This

> > time shutdown immediate hangs. Is this normal (waiting rollback)? I've

> > been hang here for over one hours now.

> >

> > Thanks in advance,

> >

> > Joan

> > --

> > Author: Joan Hsieh

> >   INET: jhsieh_at_infonet.tufts.edu

> >

> > Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

> > San Diego, California        -- Public Internet access / Mailing Lists

> > --------------------------------------------------------------------

> > To REMOVE yourself from this mailing list, send an E-Mail message

> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in

> > the message BODY, include a line containing: UNSUB ORACLE-L

> > (or the name of mailing list you want to be removed from).  You may

> > also send the HELP command for other information (like subscribing).

> >

> > --

> > Author:

> >   INET: Winnie_Liu_at_infonet.com

> >

> > Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

> > San Diego, California        -- Public Internet access / Mailing Lists

> > --------------------------------------------------------------------

> > To REMOVE yourself from this mailing list, send an E-Mail message

> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in

> > the message BODY, include a line containing: UNSUB ORACLE-L

> > (or the name of mailing list you want to be removed from).  You may

> > also send the HELP command for other information (like subscribing).

> --

> Author: Joan Hsieh

>   INET: jhsieh_at_infonet.tufts.edu

>

> Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

> San Diego, California        -- Public Internet access / Mailing Lists

> --------------------------------------------------------------------

> To REMOVE yourself from this mailing list, send an E-Mail message

> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in

> the message BODY, include a line containing: UNSUB ORACLE-L

> (or the name of mailing list you want to be removed from).  You may

> also send the HELP command for other information (like subscribing).

>

> --

> Author:

>   INET: Winnie_Liu_at_infonet.com

>

> Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

> San Diego, California        -- Public Internet access / Mailing Lists

> --------------------------------------------------------------------

> To REMOVE yourself from this mailing list, send an E-Mail message

> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in

> the message BODY, include a line containing: UNSUB ORACLE-L

> (or the name of mailing list you want to be removed from).  You may

> also send the HELP command for other information (like subscribing).

> --

> Author: Southerland, Teresa

>   INET: tsouther_at_indiana.edu

>

> Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

> San Diego, California        -- Public Internet access / Mailing Lists

> --------------------------------------------------------------------

> To REMOVE yourself from this mailing list, send an E-Mail message

> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in

> the message BODY, include a line containing: UNSUB ORACLE-L

> (or the name of mailing list you want to be removed from).  You may

> also send the HELP command for other information (like subscribing).

>

> --

> Author:

>   INET: Winnie_Liu_at_infonet.com

>

> Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

> San Diego, California        -- Public Internet access / Mailing Lists

> --------------------------------------------------------------------

> To REMOVE yourself from this mailing list, send an E-Mail message

> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in

> the message BODY, include a line containing: UNSUB ORACLE-L

> (or the name of mailing list you want to be removed from).  You may

> also send the HELP command for other information (like subscribing).




Stephen Palmer


Junior Database Administrator
Received on Wed May 24 2000 - 15:41:20 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US