RE: shutdown hang

According to the internals class I am taking (and the instructor, Scott Gossett, is one of the best), rollback takes about as long as it took from the start of the transaction to when you started the rollback.

The reason is:

to make the original changes to the blocks, Oracle builds change vectors which get applied to the blocks (rollback segment header block, undo block and data block). When you rollback, you build similar change vectors to change things back...... Why you ask? Well, Oracle is building entries in the rollback segment to be able to rollback the rollback if a problem happens in the middle. Yes, I know that sounds strange, but when have we ever understood what Oracle is doing and why? <G>

So you are doing as much work to rollback as you did to get to that point.

This class rocks. And is making my head hurt... there is SO much to learn!

Rachel

>From: Ari D Kaplan <akaplan_at_interaccess.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: RE: shutdown hang
>Date: Wed, 24 May 2000 13:46:26 -0800
>
>It depends on how large the 120,000 rows are. If there is a LONG column or
>200 columns, etc. it could take an hour or so. If it is a "typical" table,
>say 15 columns, then 120,000 rows should take about a minute.
>
>With other tables, as Winnie has said, can take hours or more.
>
>-Ari Kaplan
>Independent Oracle DBA Consultant
>
><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
><-> For 370+ Oracle tips, visit: <->
><-> <->
><-> www.arikaplan.com <->
><-> <->
><-> email: akaplan_at_interaccess.com <->
><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
>
>
>On Wed, 24 May 2000 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).
> >
>
>--
>Author: Ari D Kaplan
> INET: akaplan_at_interaccess.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
Received on Wed May 24 2000 - 19:47:25 CDT