分类目录归档：Oracle

kill oracle session相关描述

发表于 2012 年 8 月 4 日由惜分飞

近期看到不少朋友都对kill session相关的参数不太熟悉,下面是摘自Oracle® Database SQL Reference

语法参考

DISCONNECT SESSION Clause

Use the DISCONNECT SESSION clause to disconnect the current session by destroying the dedicated server process 
(or virtual circuit if the connection was made by way of a Shared Sever). 
To use this clause, your instance must have the database open. 
You must identify the session with both of the following values from the V$SESSION view:
•	For integer1, specify the value of the SID column.
•	For integer2, specify the value of the SERIAL# column.
If system parameters are appropriately configured, then application failover will take effect.
•	The POST_TRANSACTION setting allows ongoing transactions to complete before the session is disconnected.
        If the session has no ongoing transactions, then this clause has the same effect described for as KILL SESSION.
•	The IMMEDIATE setting disconnects the session and recovers the entire session state immediately, 
        without waiting for ongoing transactions to complete.
o	If you also specify POST_TRANSACTION and the session has ongoing transactions, then the IMMEDIATE keyword is ignored.
o	If you do not specify POST_TRANSACTION, or you specify POST_TRANSACTION but the session has no ongoing transactions, 
        then this clause has the same effect as described for KILL SESSION IMMEDIATE.

KILL SESSION Clause

The KILL SESSION clause lets you mark a session as terminated, roll back ongoing transactions, 
release all session locks, and partially recover session resources. 
To use this clause, your instance must have the database open. 
Your session and the session to be terminated must be on the same instance unless you specify integer3.
You must identify the session with the following values from the V$SESSION view:
•	For integer1, specify the value of the SID column.
•	For integer2, specify the value of the SERIAL# column.
•	For the optional integer3, specify the ID of the instance where the target session to be killed exists.
        You can find the instance ID by querying the GV$ tables.
If the session is performing some activity that must be completed, 
such as waiting for a reply from a remote database or rolling back a transaction, 
then Oracle Database waits for this activity to complete, marks the session as terminated, 
and then returns control to you. 
If the waiting lasts a minute, then Oracle Database marks the session to be terminated 
and returns control to you with a message that the session is marked to be terminated. 
The PMON background process then marks the session as terminated when the activity is complete.
Whether or not the session has an ongoing transaction, Oracle Database does not recover 
the entire session state until the session user issues a request to the session 
and receives a message that the session has been terminated.

IMMEDIATE Specify IMMEDIATE to instruct Oracle Database to roll back ongoing transactions, 
release all session locks, recover the entire session state, and return control to you immediately.

执行案例

--Oracle 级别kill
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;

--Linux/Unix kill
kill -9 spid

--Windows kill
orakill ORACLE_SID spid

发表在 Oracle | 一条评论

TOAD导致ORA-00600[17281]

发表于 2012 年 8 月 2 日由惜分飞

alert日志报ORA-00600[17281]

Thu Aug  2 01:49:39 2012
Errors in file /oracle9/app/admin/acc/udump/acc2_ora_647350.trc:
ORA-00600: internal error code, arguments: [17281], [1001], [0x7000007AB9F0A30], [], [], [], [], []
ORA-01001: invalid cursor
Thu Aug  2 01:49:39 2012
Errors in file /oracle9/app/admin/acc/udump/acc2_ora_647350.trc:
ORA-00600: internal error code, arguments: [17281], [1001], [0x7000007AB9F0A30], [], [], [], [], []
ORA-01001: invalid cursor
ORA-00600: internal error code, arguments: [17281], [1001], [0x7000007AB9F0A30], [], [], [], [], []
ORA-01001: invalid cursor

分析trace文件

Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
ORACLE_HOME = /oracle9/app/product/9.2.0
System name:    AIX
Node name:      zwq_acc2
Release:        3
Version:        5
Machine:        00CFFB554C00
Instance name: acc2
Redo thread mounted by this instance: 2
Oracle process number: 1598
Unix process pid: 647350, image: oracle@zwq_acc2 (TNS V1-V3)

*** SESSION ID:(1643.29233) 2012-08-02 00:01:44.423
*** 2012-08-02 00:01:44.423
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [17281], [1001], [0x7000007AB9F0A30], [], [], [], [], []
ORA-01001: invalid cursor
Current SQL statement for this session:
DECLARE
   CURSOR NlsParamsCursor
   IS
      SELECT * FROM nls_session_parameters;
BEGIN
   SELECT NVL (LENGTHB (CHR (65536)), NVL (LENGTHB (CHR (65536)), 1))
     INTO :CharLength
     FROM DUAL;
   FOR NlsRecord IN NlsParamsCursor
   LOOP
      IF NlsRecord.parameter = 'NLS_DATE_LANGUAGE'
      THEN
         :NlsDateLanguage := NlsRecord.VALUE;
      ELSIF NlsRecord.parameter = 'NLS_DATE_FORMAT'
      THEN
         :NlsDateFormat := NlsRecord.VALUE;
      ELSIF NlsRecord.parameter = 'NLS_NUMERIC_CHARACTERS'
      THEN
         :NlsNumericCharacters := NlsRecord.VALUE;
      ELSIF NlsRecord.parameter = 'NLS_TIMESTAMP_FORMAT'
      THEN
         :NlsTimeStampFormat := NlsRecord.VALUE;
      ELSIF NlsRecord.parameter = 'NLS_TIMESTAMP_TZ_FORMAT'
      THEN
         :NlsTimeStampTZFormat := NlsRecord.VALUE;
      END IF;
   END LOOP;
END;
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedmp+0148          bl       ksedst               1029746FC ?
ksfdmp+0018          bl       01FD4014
kgeriv+0118          bl       _ptrgl
kgeasi+00cc          bl       kgeriv               000000000 ? 110347AE0 ?
                                                   1101A1738 ? 110380110 ?
                                                   110380420 ?
kgicli+0164          bl       kgeasi               110006308 ? 110386088 ?
                                                   438100004381 ? 200000002 ?
                                                   200000002 ? 000000000 ?
                                                   0000003E9 ? 000000002 ?
kgidlt+03a0          bl       kgicli               110287AA0 ? 000000168 ?
kgidel+0018          bl       kgidlt               11003DD08 ? 110062138 ?
                                                   000000069 ? 000000000 ?
                                                   000000000 ?
perabo+00cc          bl       kgidel               000000000 ? 000000000 ?
perdcs+0038          bl       perabo               000000120 ? 000000012 ?
                                                   70000000005C390 ?
peidcs+00ac          bl       perdcs               110002A20 ? 70000000007A450 ?
kkxcls+00bc          bl       peidcs               FFFFFFFFFFFA298 ? 110349708 ?
kxscln+0048          bl       kkxcls               1031628A8 ?
kkscls+0268          bl       kxscln               110349F60 ?
opicca+00f0          bl       kkscls               1009E8034 ? FFFFFFFFFFFA4C0 ?
opiclo+0020          bl       opicca               FFFFFFFFFFFA810 ?
kpoclsa+004c         bl       opiclo               000000002 ?
opiodr+08cc          bl       _ptrgl
ttcpip+0cc4          bl       _ptrgl
opitsk+0d60          bl       ttcpip               11000CF90 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
opiino+0758          bl       opitsk               000000000 ? 000000000 ?
opiodr+08cc          bl       _ptrgl
opidrv+032c          bl       opiodr               3C00000018 ? 4101FAF78 ?
                                                   FFFFFFFFFFFF7A0 ? 0A000F000 ?
sou2o+0028           bl       opidrv               3C0C000000 ? 4A02C3B50 ?
                                                   FFFFFFFFFFFF7A0 ?
main+0138            bl       01FD3A28
__start+0098         bl       main                 000000000 ? 000000000 ?

--------------------- Binary Stack Dump ---------------------

SO: 70000067d802e90, type: 2, owner: 0, flag: INIT/-/-/0x00
  (process) Oracle pid=1598, calls cur/top: 7000006f50c45f0/7000006f50c45f0, flag: (0) -
            int error: 0, call error: 0, sess error: 0, txn error 0
  (post info) last post received: 0 0 0
              last post received-location: No post
              last process to post me: 70000067d5eedf8 2 6
              last post sent: 0 0 0
              last post sent-location: No post
              last process posted by me: none
    (latch info) wait_event=0 bits=0
    Process Group: DEFAULT, pseudo proc: 7000006815fe518
    O/S info: user: oraacc, term: UNKNOWN, ospid: 647350
    OSD pid info: Unix process pid: 647350, image: oracle@zwq_acc2 (TNS V1-V3)
    ----------------------------------------
    SO: 70000068169e7b8, type: 4, owner: 70000067d802e90, flag: INIT/-/-/0x00
    (session) trans: 0, creator: 70000067d802e90, flag: (41) USR/- BSY/-/-/-/-/-
              DID: 0000-0000-00000000, short-term DID: 0000-0000-00000000
              txn branch: 0
              oct: 0, prv: 0, sql: 700000980c32508, psql: 700000980c32508, user: 44/DBRPT
    O/S info: user: wusp9, term: AHMWUSP9, ospid: 2812:1900, machine: AH\AHMWUSP9
              program:
    last wait for 'SQL*Net message from client' blocking sess=0x0 seq=13 wait_time=3647540
                driver id=54435000, #bytes=1, =0
    temporary object counter: 0

这里可以看出当前执行的sql是一种框架中发生,但是从PROCESS STATE未找到登录客户端名称,但是从machine中大概可以估算出来是windows机器.通过查询V$sql找出MODULE

SQL> select module,last_load_time  from V$sql where sql_text like 'declare  cursor NlsParamsCursor is%';

MODULE          LAST_LOAD_TIME
--------------- ----------------------------------
Toad.exe        2012-08-02/00:01:44

从这里可以确定,是因为TOAD登录数据库出现该错误,查询MOS发现相关内容ORA-600 [17281] using TOAD [ID 329531.1]

解决方法

 You can also overcome this by patching the Oracle Client software as follows.:  

1. Fix unpublished Bug 4359111, if the Oracle software is 10.1.0.4 or below. 
   Patches for this bug can be found on My Oracle Support under Patch:4359111.

2. Fix unpublished bug 5910901 by applying Patch:5910901 if the Oracle software is 10.1.0.5 or above,

3. Apply the 10.2.0.4 patch set or above on the client.

4. You can change the non-Oracle client software being used, 

5. You can just ignore the error as it is non-corruptive.

6. Run the queries directly through SQLPLUS instead of using TOAD.

**Ensure that your version of TOAD is compatible with the version of the database as well.  
If the errors continue when using TOAD, but not with SQL*Plus, contact TOAD support to request a fix.**

发表在 ORA-xxxxx | 标签为 ORA-600 17281 | 2 条评论

ORA-00600[kgscLogOff-notempty]

发表于 2012 年 8 月 1 日由惜分飞

在查看一网友alert日志时发现很多ORA-00600[kgscLogOff-notempty]记录,通过查询MOS分析记录结果如下:
alert日志出现ORA-00600[kgscLogOff-notempty]

Wed Aug  1 10:28:35 2012
Errors in file /vgdata/oracle/admin/orcl1/udump/orcl1_ora_18430.trc:
ORA-00600: internal error code, arguments: [kgscLogOff-notempty], [1], [], [], [], [], [], []
Wed Aug  1 10:28:38 2012
Errors in file /vgdata/oracle/admin/orcl1/udump/orcl1_ora_18508.trc:
ORA-00600: internal error code, arguments: [kgscLogOff-notempty], [3], [], [], [], [], [], []

trace结合MOS分析原因

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /vgdata/oracle/product
System name:	HP-UX
Node name:	hpdc1
Release:	B.11.31
Version:	U
Machine:	ia64
Instance name: orcl1
Redo thread mounted by this instance: 1
Oracle process number: 230
Unix process pid: 18430, image: oracleorcl1@hpdc1

*** SERVICE NAME:(orcl1) 2012-08-01 10:28:35.937
*** SESSION ID:(191.141) 2012-08-01 10:28:35.937
*** 2012-08-01 10:28:35.937
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [kgscLogOff-notempty], [1], [], [], [], [], [], []
----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
ksedst()+64          call     _etext_f()+23058430  000000000 ? 000000001 ?
                              09017233648          
ksedmp()+1680        call     _etext_f()+23058430  000000000 ?
                              09017233648          C000000000000D20 ?
                                                   40000000052C8C90 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ?
ksfdmp()+48          call     _etext_f()+23058430  000000003 ?
                              09017233648          
kgerinv()+400        call     _etext_f()+23058430  40000000093F4370 ?
                              09017233648          000000003 ?
                                                   C000000000000612 ?
                                                   000008F07 ? 000000000 ?
                                                   000000000 ?
kgeasnmierr()+144    call     _etext_f()+23058430  6000000000015190 ?
                              09017233648          6000000000016248 ?
                                                   6000000000013770 ?
                                                   600000000009C808 ?
                                                   60000000000165B0 ?
$cold_kgscLogOff()+  call     _etext_f()+23058430  6000000000015190 ?
144                           09017233648          9FFFFFFFBF562160 ?
                                                   9FFFFFFFBF562170 ?
                                                   60000000000165C0 ?
                                                   000000000 ? 000000001 ?
kkslof()+320         call     _etext_f()+23058430  6000000000015190 ?
                              09017233648          
opifcs()+592         call     _etext_f()+23058430  C000000360365908 ?
                              09017233648          C000000000000E21 ?
                                                   4000000002E1BF20 ?
                                                   000000000 ? 000000000 ?
ksuxds()+1568        call     _etext_f()+23058430  C000000360365908 ?
                              09017233648          4000000002EBA7A0 ?
                                                   000008F9F ? 000004810 ?
                                                   9FFFFFFFBF56BEF6 ?
                                                   C000000360365908 ?
                                                   4000000001354140 ?
                                                   00000003F ?
ksudel()+128         call     _etext_f()+23058430  60000000000A7B54 ?
                              09016643752          60000000000A7CF8 ?
                                                   9FFFFFFFFFFF6C20 ?
                                                   600000000009D8A0 ?
opilof()+3664        call     _etext_f()+23058430  60000000000A7B54 ?
                              09016643752          60000000000A7CF8 ?
                                                   4000000003D681E0 ?
                                                   C00000000000122A ?
                                                   00000814F ?
                                                   60000000000A7CE0 ?
opiodr()+2016        call     opilof()+3664        60000000002BE7B0 ?
                                                   60000000002BE7B8 ?
                                                   000000001 ?
                                                   9FFFFFFFFFFF71B0 ?
                                                   600000000009D8A0 ?
                                                   60000000002BE7E0 ?
ttcpip()+1824        call     _etext_f()+23058430  60000000000AA0BC ?
                              09017243504          000000001 ?
                                                   6000000000015310 ?
                                                   6000000000015310 ?
                                                   9FFFFFFFFFFF8250 ?
                                                   600000000009D8A0 ?
                                                   9FFFFFFFFFFF8250 ?
                                                   6000000000020D88 ?
opitsk()+2224        call     _etext_f()+23058430  6000000000020D80 ?
                              09017192128          9FFFFFFFFFFFA3D4 ?
                                                   6000000000020EE8 ?
                                                   000000001 ?
                                                   9FFFFFFFFFFFAA90 ?
                                                   9FFFFFFFFFFFA884 ?
                                                   6000000000020E88 ?
                                                   000000000 ?
opiino()+2144        call     _etext_f()+23058430  000000000 ? 000000000 ?
                              09016681384          600000000009D8A0 ?
                                                   4000000002AE7E10 ?
                                                   000008001 ?
                                                   9FFFFFFFFFFFA874 ?
opiodr()+2016        call     _etext_f()+23058430  00000003C ?
                              09016681384          9FFFFFFFFFFFF260 ?
                                                   9FFFFFFFFFFFF250 ?
                                                   9FFFFFFFFFFFBF90 ?
                                                   0000000B0 ?
                                                   600000000008F560 ?
opidrv()+1136        call     _etext_f()+23058430  60000000000AA0BC ?
                              09017243504          000000001 ?
                                                   6000000000015310 ?
                                                   6000000000015310 ?
                                                   9FFFFFFFFFFFCAE0 ?
                                                   600000000009D8A0 ?
sou2o()+240          call     9fffffffffffcae0     00000003C ? 000000004 ?
                                                   9FFFFFFFFFFFF250 ?
opimai_real()+480    call     9fffffffffffcae0     9FFFFFFFFFFFF270 ?
                                                   00000003C ? 000000004 ?
                                                   9FFFFFFFFFFFF250 ?
main()+352           call     9fffffffffffcae0     000000000 ?
                                                   9FFFFFFFFFFFF2A0 ?
main_opd_entry()+80  call     9fffffffffffcae0     000000002 ?
                                                   9FFFFFFFFFFFF750 ?
                                                   C000000000033910 ?
                                                   000000000 ?
--------------------- Binary Stack Dump ---------------------

通过查询MOS发现ORA-600 [kgscLogOff-notempty] On Session Logoff [ID 413120.1]中记录有

The following error may occur on session logoff

ORA-600 [kgscLogOff-notempty], [1]
or ORA-600 [kgscLogOff-notempty], [2]

with a Call Stack like:

... kgscLogOff  kkslof opifcs ksuxds ksudel ...

和该数据库中的Stack Dump记录一致,数据库版本也在该bug范围内,从而可以确定是unpublished Bug 4887675

解决方案
该错误是在logoff时产生,对数据库影响不大,如果偶尔出现可以忽略,出现频率过高,建议按照下列方法解决

There is No Data Corruption (or) Data Loss due to this Error.
This error occurs during the logoff operation, hence the impact should be negligible.

This is fixed in 10.2.0.4 and above releases.

One-off patches may be available depending on your current release and operating system.
To obtain a patch from MetaLink:
1) Click on Patches.
2) Click on Simple Search
3) Enter patch number: 4887675
4) Select your O/S
5) Click Go.

As workaround, avoid  Killing/Terminating the Session Abnormally.

发表在 ORA-xxxxx | 标签为 kgscLogOff-notempty, ORA-600 kgscLogOff-notempty | 一条评论

17813235971

QQ 咨询

分类目录归档：Oracle

kill oracle session相关描述

TOAD导致ORA-00600[17281]

ORA-00600[kgscLogOff-notempty]

文章分类

最近发表

分类目录归档：Oracle

kill oracle session相关描述

TOAD导致ORA-00600[17281]

ORA-00600[kgscLogOff-notempty]

标签云

文章分类

最近发表