Re: hang/wait problem

Ruth, I think the problem that LInda is having is

1. It may take a while for the session to go away even though you killed that session. Oracle may need to rollback and clean up that session. It may take up to hours to do that.
2. I hope she is not in archivelog mode! How can she "stop" the archivelog mode by "ALTER SYSTEM ARCHIVE LOG STOP"??!?!? That will stop that archiver! Not change the database to noarchivelog mode!

The fact taht in the alert.log file showing that all online redolog files are waiting for archive is a clear point!

If you want to turn the database into noarchivelog mode. You need to mount the database. and then issue ALTER DATABASE NOARCHIVELOG.

Winnie

"Ruth Gramolini" <rgramolini_at_tax.state.vt.us> on 06/12/2000 07:36:59 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)

Subject: Re: hang/wait problem

Try looking at v$lock and get the SID of the user who is causing the trouble from this and check v$session for the serial# or this user using the SID. Then you can issue this sql:
alter system kill session 'SID,SEREAL#';Ruth B. Gramolini ORACLE & DB2 DBA
VT Dept. of Taxes
ph# 802.828.5708
fax# 802.828..3754
rgramolini_at_tax.state.vt.us
to kill the user's session.

HTH

• Original Message -----

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Friday, June 09, 2000 4:06 PM

> Hello,
>
> I'm having a hang/wait problem with one of my systems, and this is the
> information I've gleaned. I want to alter a table nologging:
>
> SVRMGR> alter table bvadmin.MR_purchased_items nologging ;
>
> alter table bvadmin.MR_purchased_items nologging
>
> *
>
> ORA-00054: resource busy and acquire with NOWAIT specified
>
>
> I'm trying to kill the user I believe has the table:
> 11 rows selected.
>
> SVRMGR> select * from v$session ;
>
> SADDR SID SERIAL# AUDSID PADDR USER# USERNAME
>
> -------- ---------- ---------- ---------- -------- ----------
> ---------------
> AB1C6328 1 1 0 AB1A1A04 0
>
> AB1C6B54 2 1 0 AB1A1CF4 0
>
> AB1C7380 3 1 0 AB1A1FE4 0
>
> AB1C7BAC 4 1 0 AB1A22D4 0
>
> AB1C83D8 5 1 0 AB1A25C4 0
>
> AB1C8C04 6 1 0 AB1A28B4 0
>
> AB1C9430 7 68 0 AB1A2BA4 0
>
> AB1CA488 9 348 1943952 AB1A3474 41 BVADMIN
> <-------
> AB1CACB4 10 15 0 AB1A2E94 0
>
> AB1CC538 13 8768 0 AB1A3764 0 SYS
>
> 10 rows selected.
>
>
> Oracle says it's marked to kill, and has been for 15+ minutes (won't go
> away). I'm also seeing this message in the alert log:
>
> Wed Jun 7 12:11:55 2000
>
> ORACLE Instance PETS - Can not allocate log, archival required
>
> Wed Jun 7 12:11:55 2000
>
> ARCH: Connecting to console port...
>
> Thread 1 cannot allocate new log, sequence 6
>
> All online logs needed archiving
>
> Current log# 5 seq# 5 mem# 0:
> /opt/oracle/app/oracle/oradata/PETS/log1/redoPEg
> Current log# 5 seq# 5 mem# 1:
> /opt/oracle/app/oracle/oradata/PETS/log2/redoPEg
> Fri Jun 9 10:02:58 2000
>
>
> Archiving is turned off (SVRMGR> ALTER SYSTEM ARCHIVE LOG STOP;ORA-0250:
> archiver not started .
>
> Is this sufficient information to tell if these messages are related, and
> can you recommend a course of action to 1) kill the user, and 2) add
another
> log or turn on archiving, which would aleiviate this problem?
>
> I tried to shutdown immediate, but it hung for 20+ minutes before I
> cancelled the shutdown.
>
> Any information is appreciated.
>
> Best regards, and thanks for the help.
>
> Linda
>
> --
> Author: Linda Hagedorn
> INET: Linda_at_pets.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: Ruth Gramolini
  INET: rgramolini_at_tax.state.vt.us

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