ORACLE_07445[_kill()+48][SIGIOT]错误_附嵌套表研究

用户环境: hp-ux B.11.23 + oracle9206 rac

1)错误的发现

今天在一个客户这里巡检，发现日志文件有以下错误：

Thu Jan 24 02:12:36 2008

Errors in file /opt/app/oracle/admin/gzgov/udump/gzgov1_ora_8417.trc:

ORA-07445: exception encountered: core dump [_kill()+48] [SIGIOT] [unknown code] [0x0000020E1] [] []

相应的trc文件中，有一些信息：

*** 2008-01-24 02:12:36.394

*** SESSION ID:(20.39311) 2008-01-24 02:12:36.355

Exception signal: 6 (SIGIOT), code: -1 (unknown code), addr: 0x20e1, exception issued by pid: 8417, uid: 200, PC: [0xc00000000031a89

0, _kill()+48]

r1: 9fffffffbf470128 r20: 0 br5: 0

r2: 0 r21: 0 br6: 0

r3: 0 r22: 0 br7: 0

r4: c00000006bad1598 r23: 0 ip: c00000000031a890

r5: 9fffffffffff669c r24: 0 iipa: 0

r6: e r25: 0 cfm: 2

r7: 9fffffffbf166590 r26: 0 um: 1a

r8: 25 r27: 0 rsc: 1f

r9: 2 r28: 0 bsp: 9fffffffbf800d50

r10: 20 r29: 0 bspstore: 9fffffffbf800d50

r11: c000000000000185 r30: 0 rnat: 0

r12: 9fffffffffff21b0 r31: 0 ccv: 0

r13: 9fffffffbf7ed420 NaTs: ffffc00c unat: 0

r14: 0 PRs: 29 fpsr: 9804c8277433f

r15: 0 br0: c00000000023f1d0 pfs: c000000000000002

r16: 0 br1: 4000000001205520 lc: 0

r17: 0 br2: 0 ec: 0

r18: 0 br3: 0 isr: 9fffffffbf800d50

r19: 0 br4: 0 ifa: 0

Reason code: 0000

*** 2008-01-24 02:12:36.421

ksedmp: internal or fatal error

ORA-07445: exception encountered: core dump [_kill()+48] [SIGIOT] [unknown code] [0x0000020E1] [] []

Current SQL statement for this session:

SELECT /*+NESTED_TABLE_GET_REFS+*/ "GZGOVII"."GZGOV_NEWSCLICK".* FROM "GZGOVII"."GZGOV_NEWSCLICK"

………..

LIBRARY OBJECT LOCK: lock=c00000006d17d8a0 handle=c000000073a51cd0 mode=N

2)分析sql

trc文件中的

SELECT /*+NESTED_TABLE_GET_REFS+*/ "GZGOVII"."GZGOV_NEWSCLICK".* FROM "GZGOVII"."GZGOV_NEWSCLICK"

语句，有NESTED_TABLE_GET_REFS提示，后来发现，这个提示是oracle处理嵌套表时需要的，有时候，在exp,imp.load数据时，oracle会自动加上这个hint.

This hint specifies that nested tables should be retrieved as refs.It is used for exporting, importing and loading. It should never be issued by user queries, and in fact is unlikely to make a difference if tried.

This is also consistent with seeing large amounts of queries with this hint, and that those queries are not reused. If this is a problem for you, try to export at another time of day. If the exports are really long-running or the system load is constant, try to find out what you are using exports for and suggest alternatives (eg. backup and recovery purposes, etc). If this is a DSS system, you probably have a smaller shared pool and use import/export heavily. If so, it might be a good idea to use a bigger shared pool when doing this activity.

3)分析用户的逻辑备份

经过查看用户的逻辑备份日志，最近的逻辑备份都是失败的，日志最后如下：

. . exporting table GZGOV_MESSAGE 11789 rows exported . . exporting table GZGOV_NEWSCLICK EXP-00056: ORACLE error 3113 encountered ORA-03113: end-of-file on communication channel EXP-00056: ORACLE error 24324 encountered ORA-24324: service handle not initialized EXP-00056: ORACLE error 24324 encountered ORA-24324: service handle not initialized EXP-00056: ORACLE error 24324 encountered ORA-24324: service handle not initialized EXP-00056: ORACLE error 24324 encountered ORA-24324: service handle not initialized EXP-00000: Export terminated unsuccessfully ORA-24324: service handle not initialized EXP-00000: Export terminated unsuccessfully $

4)第一步结论

正式由于在执行exp的过程中，当开始导出GZGOV_NEWSCLICK到一定时间后，数据库就报了上面的07445的错误。

经过手工执行用户的逻辑备份命令：

exp gzgovii/db110g file=`date +%Y%m%d`.dmp buffer=204800 compress=y grants=y indexes=yes constraints=yes log=exp.log &

确实发现GZGOV_NEWSCLICK到一定时间后,exp就异常退出了，且数据库日志里报了以上的07445的错误。

5)嵌套表？

根据官方文档的说明，这个oracle自动加上的hints跟嵌套表有关，于是研究了一下嵌套表，过程如下：

CREATE TABLE dept
(deptno NUMBER(2) PRIMARY KEY,
dname VARCHAR2(14),
loc VARCHAR2(13)
);

CREATE TABLE emp
(empno NUMBER(4) PRIMARY KEY,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4) REFERENCES emp,
hiredate DATE,
sal NUMBER(7, 2),
comm NUMBER(7, 2),
deptno NUMBER(2) REFERENCES dept
);

INSERT INTO dept SELECT * FROM scott.dept;
INSERT INTO emp SELECT * FROM scott.emp;
commit;

CREATE OR REPLACE TYPE emp_type
AS OBJECT
(empno NUMBER(4),
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7, 2),
comm NUMBER(7, 2)
);
/

CREATE OR REPLACE TYPE emp_tab_type
AS TABLE OF emp_type;

使用嵌套表：

CREATE TABLE dept_and_emp
(deptno NUMBER(2) PRIMARY KEY,
dname VARCHAR2(14),
loc VARCHAR2(13),
emps emp_tab_type
)
NESTED TABLE emps STORE AS emps_nt;

可以在嵌套表上增加约束：

ALTER TABLE emps_nt ADD CONSTRAINT emps_empno_unique
UNIQUE(empno) ;

嵌套表不支持参照完整性约束，不能参考任何其他表甚至自己：

ALTER TABLE emps_nt ADD CONSTRAINT mgr_fk
FOREIGN KEY(mgr) REFERENCES emps_nt(empno);

会产生错误ORA-30730。

INSERT INTO dept_and_emp
SELECT dept.*,
CAST( MULTISET( SELECT empno, ename, job, mgr, hiredate, sal, comm
FROM emp
WHERE emp.deptno = dept.deptno ) AS emp_tab_type )
FROM dept;
commit;
MULTISET用来告诉Oracle子查询返回不止一行，CAST用来告诉Oracle将返回设置为一个集合类型。

查询时，嵌套表中的数据将在同一列中：

SELECT deptno, dname, loc, d.emps AS employees
FROM dept_and_emp d
WHERE deptno = 10;

Oracle同样提供方法去掉集合的嵌套，像关系型表一样处理（能够将EMPS列当作一个表，并自然连接且不需要连接条件）：

SELECT d.deptno, d.dname, emp.* FROM dept_and_emp d, TABLE(d.emps) emp;

按照“每行实际是一张表”的思想来更新：

UPDATE
TABLE( SELECT emps
FROM dept_and_emp
WHERE deptno = 10
)
SET comm = 100;

但如果返回SELECT emps FROM dept_and_emp WHERE deptno = 10少于一行，更新将失败（普通情况下更新0行是许可的），并返回ORA-22908错误——如同更新语句没有写表名一样；如果返回多于一行，更新也会失败，返回ORA-01427错误。这说明Oracle在使用了嵌套表后认为每一行指向另一个表，而不是如同关系型模型那样认为是另一个行集。

插入与删除的语法：

INSERT INTO TABLE
(SELECT emps FROM dept_and_emps WHERE deptno=10)
VALUES
(1234,'NewEmp','Clerk',7782,SYSDATE,1200,NULL);

DELETE FROM TABLE
(SELECT emps FROM dept_and_emps WHERE deptno=20)
WHERE ename='SCOTT';

一般而言，必须总是连接，而不能单独查询嵌套表（如EMPS）中的数据，但是如果确实需要，是可以的。提示NESTED_TABLE_GET_REFS被用于EXP和IMP处理嵌套表。

SELECT /*+NESTED_TABLE_GET_REFS+*/
NESTED_TABLE_ID, SYS_NC_ROWINFO$
FROM "EMPS_NT";

而我们察看EMPS_NT的表结构是看不到NESTED_TABLE_ID,SYS_NC_ROWINFO$两列的。对父表DEPT_AND_EMP来说NESTED_TABLE_ID是一个外键。

使用这个提示就可以直接操作嵌套表了：

UPDATE /*+NESTED_TABLE_GET_REFS+*/ emps_nt
SET ename=INITCAP(ename);

6)进一步分析

查询该表的定义：

-- Create table create table GZGOVII.GZGOV_NEWSCLICK ( CONTENT_ID NUMBER, CLICK_DATE DATE, CLICK_COUNT NUMBER ) tablespace FSDATA pctfree 10 pctused 40 initrans 1 maxtrans 255 storage ( initial 200K minextents 1 maxextents unlimited );

这个表根本不是嵌套表，oracle为什么会加上这个hint?呢，不得而知。

于是开始怀疑exp的参数有问题，因为这个表的记录非常多，但buffer=204800似乎太小了，

所以修改命令，进行单独对这个表导出的测试：

$ exp gzgovii/db110g file=exptest.dmp buffer=4096000 tables=GZGOV_NEWSCLICK log=exptest.log feedback=5000

导出过程中，监控v$session_longops:

SID TARGET MESSAGE START_TIME LAST_UPDATE_TIME
--- ------------------------ -------------------------------------------------------------------- ----------- ----------------
138 GZGOVII.GZGOV_NEWSCLICK Table Scan: GZGOVII.GZGOV_NEWSCLICK: 76410 out of 76410 Blocks done 2008-1-23 0 2008-1-23 2:26:2
138 GZGOVII.GZGOV_NEWSCLICK Table Scan: GZGOVII.GZGOV_NEWSCLICK: 73843 out of 77529 Blocks done 2008-1-29 1 2008-1-29 15:29:

发现当前正在的导出的操作记录还在，还有23号的导出成功的记录还在，23号后面的记录都没有了，说明都不正常执行完毕就退出了。

继续监控导出过程，最后又报错：

Assertion failed: bd->seqno_skgxprbd == bid->seqno_skgxpbid, file skgxpu.c, line 2859

EXP-00056: ORACLE error 3113 encountered
ORA-03113: end-of-file on communication channel
EXP-00056: ORACLE error 24324 encountered
ORA-24324: service handle not initialized
EXP-00056: ORACLE error 24324 encountered
ORA-24324: service handle not initialized
EXP-00056: ORACLE error 24324 encountered
ORA-24324: service handle not initialized
EXP-00056: ORACLE error 24324 encountered
ORA-24324: service handle not initialized
EXP-00000: Export terminated unsuccessfully
$

此时v$session_ops显示，其实已经快导出完了：75943 out of 77529 Blocks done

同事分析，该表可能有不合法记录，或者数据文件有坏块，于是检查数据库文件：

$ dbv file=/dev/vgfsdata/rfsdata1 blocksize=8192

DBVERIFY: Release 9.2.0.6.0 - Production on Tue Jan 29 15:56:32 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

DBVERIFY - Verification starting : FILE = /dev/vgfsdata/rfsdata1

DBVERIFY - Verification complete

Total Pages Examined : 254720
Total Pages Processed (Data) : 76530
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 26362
Total Pages Failing (Index): 0
Total Pages Processed (Other): 177
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 151651
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 109856332 (0.109856332)

$ dbv file=/dev/vgfsdata/rfsdata2 blocksize=8192

DBVERIFY: Release 9.2.0.6.0 - Production on Tue Jan 29 16:00:43 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

DBVERIFY - Verification starting : FILE = /dev/vgfsdata/rfsdata2

DBVERIFY - Verification complete

Total Pages Examined : 254720
Total Pages Processed (Data) : 78363
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 28169
Total Pages Failing (Index): 0
Total Pages Processed (Other): 414
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 147774
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 109857157 (0.109857157)
$

可能是有不合法记录，只能改天对表做游标一步一步的挪移了????

7)第二天，继续检查

发现该天的备份居然又成功了。

用bdf命令，发现exp的dump文件所存目录为nfs:

root@dbsrv1:/root/db_exp#bdf

172.16.1.165:/data/gzgov_db_bak
85728728 66324200 15049712 82% /root/db_exp

登陆到172.16.1.165机器，发现该机器是linux，且用df -m命令进程会挂掉，并且查看

chkconfig --list nfs，发现nfs服务显示是关闭的（其实显示不正常的）。

重启了一下nfs服务

service nfs stop

service nfs start

最后建议用户重启该机器，用户决定暂时不重启，等适当时候重启该nfs服务器。

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8861952/viewspace-1034694/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8861952/viewspace-1034694/