truncate表的时候异常中断，所有该表操作挂死

jimmyhe1981 · 发表于 2008-10-30 10:21

oracle 9206 三节点rac。
hp 11.11

以下操作均在节点1（gdrac1）上操作。

在一次truncate一个大表的过程中：

SQL> select sum(bytes)/1024/1024/1024 size_g from user_segments where
segment_name='ALERTBUFF';

SIZE_G
----------
329.8125

1* select tablespace_name,PARTITIONED from user_tables where table_name='ALERTBUFF'
SQL> /

TABLESPACE_NAME PAR
------------------------------ ---
DATA_DYNAMIC NO

SQL> select EXTENT_MANAGEMENT,SEGMENT_SPACE_MANAGEMENT from user_tablespaces where
tablespace_name='DATA_DYNAMIC';

EXTENT_MAN SEGMEN
---------- ------
LOCAL MANUAL

SQL> select index_name,tablespace_name,PARTITIONED from user_indexes where table_name='ALERTBUFF';

INDEX_NAME TABLESPACE_NAME PAR
------------------------------ ------------------------------ ---
IDX_ALERTBUFF_ALERTTIME DATA_DYNAMIC NO
PK_ALERTBUFF DATA_DYNAMIC NO

我们执行了truncate table xxx reuse storage，希望通过分步回缩extent的方式在truncate该表。

但是在truncate table ALERTBUFF reuse storage的时候，网络中断了。再次登录db的时候，发现该session还在，找到其sid，serial#，spid，kill掉session和os中的process，希望重新跑。

检查起状态已经变成killed，但是其dba dml lock中仍然有其锁存在：
SQL> select * from dba_dml_locks;

SESSION_ID OWNER NAME MODE_HELD
MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS
---------- ------------------------------ ------------------------------ -------------
------------- ------------ ----------------------------------------
64 MISC ALERTBUFF Exclusive
None 1643 Global
64 SYS MLOG$ Row-X (SX) None 1643
Global

SQL> l
1* select * from v$session_wait where sid=64
SQL> /

SID SEQ# EVENT
P1TEXTP1 P1RAW P2TEXT
P2 P2RAW P3TEXT P3 P3RAW WAIT_TIME
SECONDS_IN_WAIT STATE
---------- ---------- ----------------------------------------------------------------
---------------------------------------------------------------- ----------
----------------
---------------------------------------------------------------- ----------
----------------
---------------------------------------------------------------- ----------
---------------- ---------- --------------- -------------------
64 3540 DFS lock handle
type|mode1128857605 0000000043490005 id1
18 0000000000000012 id2 2 0000000000000002
0 39340 WAITING

SQL>

在alertlog中有告警信息:
ARC1: Beginning to archive log 5 thread 1 sequence 53670
Creating archive destination LOG_ARCHIVE_DEST_1: '/arch/gdrac_1_53670.arc'
ARC1: Completed archiving  log 5 thread 1 sequence 53670

Wed Oct 29 23:12:28 2008
PMON failed to delete process, see PMON trace file
Wed Oct 29 23:14:40 2008
Thread 1 advanced to log sequence 53672
  Current log# 1 seq# 53672 mem# 0: /ora_redo/redo11
  Current log# 1 seq# 53672 mem# 1: /ora_redo/redo12
Wed Oct 29 23:14:40 2008

相关的pmon trace file见附件。

目前关于这个表的任何操作都无法进行！即使是select也会有library cache lock。

请问如何在不重启instance、重启database的情况下，解决该问题！

谢谢！

[ 本帖最后由 jimmyhe1981 于 2008-10-30 10:27 编辑 ]

hcl · 发表于 2008-10-30 10:28

找到这个sid的pid,kill -9 pid

jimmyhe1981 · 发表于 2008-10-30 10:52

原帖由 hcl 于 2008-10-30 10:28 发表
找到这个sid的pid,kill -9 pid

昨晚就已经kill了的。

昨晚的log：
昨晚的log：
SQL> select spid from v$session a,v$process b where a.paddr=b.addr and a.sid=64;

SPID
------------
6821

SQL> !
oracle@gd_rac01:/oracle$ ps -ef |grep 6821
  oracle  6821  6820  0 22:28:40 ?       0:00 oraclegdrac1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
  oracle 15152 15150  1 22:54:02 pts/4    0:00 grep 6821
oracle@gd_rac01:/oracle$
oracle@gd_rac01:/oracle$
oracle@gd_rac01:/oracle$
oracle@gd_rac01:/oracle$ kill -9 6821

今天的检查：
SQL> select spid from v$session a,v$process b where a.paddr=b.addr and a.sid=64;

SPID
------------
6821

SQL> !
oracle@gd_rac01:/oracle$ ps -ef |grep 6821
  oracle 16357 16324  0 10:34:40 pts/0    0:00 grep 6821
oracle@gd_rac01:/oracle$

可以发现那个进程昨晚就被kill掉了的。

zuohao_lu · 发表于 2008-10-30 10:53

没遇见过.等高手.

blue_prince · 发表于 2008-10-30 10:57

alter system kill session一把试试

jieyancai · 发表于 2008-10-30 10:57

你再看看这：
http://www.eygle.com/faq/Kill_Session.htm

d.c.b.a · 发表于 2008-10-30 11:12

实在不行就重起一下库了，或者将这个表Rename一下

xlpxlpxlp · 发表于 2008-10-30 11:12

Alert.log
[PHP]
Wed Oct 29 23:12:28 2008
PMON failed to delete process, see PMON trace file
[/PHP]

PMON Trace
[PHP]
*** 2008-10-29 23:12:14.056
found process c0000004435e0a00 pid=28 serial=82 dead
[46;1H[K[7mgdrac1_pmon_7976.trc (2%)[m[46;1H[46;1H[Kksb_wait_for_cic_completion: ksiget(KSBCIPAR) returned 33
ksb_wait_for_cic_completion: ksiget(KSBCIPAR) returned 33
ksb_wait_for_cic_completion: ksiget(KSBCIPAR) returned 33
[/PHP]

根据serial=82在v$process, v$session找找。

shiyihai · 发表于 2008-10-30 11:27

“found process c0000004435e0a00 pid=28 serial=82 dead”，可看到状态已经dead了。
v$session_wait中event是DFS lock handle，应该一直在等待CIC(the Cross Instance Calls)，ksb_wait_for_cic_completion: ksiget(KSBCIQUE) returned 33
有关'DFS LOCK HANDLE'导致hang着的bug在metalink上有，可上去看看有无解决方案。

jimmyhe1981 · 发表于 2008-10-30 11:47

原帖由 blue_prince 于 2008-10-30 10:57 发表
alter system kill session一把试试

SQL> set timing on
SQL> set time on
10:37:39 SQL> set feedback on
10:37:44 SQL> set echo on
10:37:49 SQL>
10:37:50 SQL> select sid,serial#,status from v$session where sid=64;

   SID SERIAL# STATUS
---------- ---------- --------
      64    58703 KILLED

1 row selected.

Elapsed: 00:00:00.01
10:38:31 SQL>
10:38:32 SQL> alter system kill session '64,58703';
alter system kill session '64,58703'
*
ERROR at line 1:
ORA-00031: session marked for kill

Elapsed: 00:01:00.60
10:39:37 SQL>
10:39:58 SQL> select sid,serial#,status from v$session where sid=64;

   SID SERIAL# STATUS
---------- ---------- --------
      64    58703 KILLED

1 row selected.

Elapsed: 00:00:00.00
10:40:02 SQL>