EXP导出aud$报错EXP-00008,ORA-00904 解决

158次阅读

没有评论

共计 7613 个字符，预计需要花费 20 分钟才能阅读完成。

主题：EXP 导出 aud$ 报错 EXP-00008,ORA-00904 解决
环境：Oracle 11.2.0.4
问题：在自己的测试环境，导出 sys 用户下的 aud$ 表报错。

1. 故障现场

2. 跟踪处理

3. 匹配 MOS 文章

4. 使用 MOS 解决方案

1. 故障现场

导出 sys 用户下的表，依据经验使用 system 用户导出：

Oracle 如何导出 sys 用户下的系统表 http://www.linuxidc.com/Linux/2018-01/150540.htm
按照这个方法，exp 导出时报错，具体报错信息如下：

[oracle@jyrac1 ~]$ exp system/oracle file=audit.dmp log=audit.log tables=sys.aud$

Export: Release 11.2.0.4.0 – Production on Wed Jan 17 17:16:30 2018

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Tes
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path …
Current user changed to SYS
. . exporting table AUD$
EXP-00008: ORACLE error 904 encountered
ORA-00904: : invalid identifier
Export terminated successfully with warnings.

2. 跟踪处理

使用 oradebug 跟踪对应的 exp 进程：

2.1 exp 交互式

[oracle@jyrac1 ~]$ exp system/oracle

Export: Release 11.2.0.4.0 – Production on Wed Jan 17 17:20:17 2018

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Tes
Enter array fetch buffer size: 4096 >

2.2 定位 exp 进程的 SPID：

SELECT p.spid,
p.addr,
p.pid,
s.sid,
SUBSTR(s.username, 1, 15) “USERNAME”,
SUBSTR(s.program, 1, 15) “PROGRAM”
FROM v$process p, v$session s
WHERE s.paddr = p.addr
AND addr =
(SELECT paddr FROM v$session WHERE UPPER(program) LIKE ‘EXP%’);

结果如下：

SYS@jyzhao1 >SELECT p.spid,
2 p.addr,
3 p.pid,
4 s.sid,
5 SUBSTR(s.username, 1, 15) “USERNAME”,
6 SUBSTR(s.program, 1, 15) “PROGRAM”
7 FROM v$process p, v$session s
8 WHERE s.paddr = p.addr
9 AND addr =
10 (SELECT paddr FROM v$session WHERE UPPER(program) LIKE ‘EXP%’);

SPID ADDR PID SID USERNAME PROGRAM
———————— —————- ———- ———- —————————— ——————————
15552 000000007F4CA268 33 141 SYSTEM exp@jyrac1 (TNS

2.3 oradebug 跟踪 spid：

SQL> oradebug setospid 15552
SQL> oradebug unlimit
SQL> oradebug tracefile_name
SQL> oradebug Event 10046 trace name context forever, level 12

实际过程记录：

SYS@jyzhao1 >oradebug setospid 15552
Oracle pid: 33, Unix process pid: 15552, image: oracle@jyrac1 (TNS V1-V3)
SYS@jyzhao1 >oradebug unlimit
Statement processed.
SYS@jyzhao1 >oradebug tracefile_name
/opt/app/oracle/diag/rdbms/jyzhao/jyzhao1/trace/jyzhao1_ora_15552.trc
SYS@jyzhao1 >oradebug Event 10046 trace name context forever, level 12
Statement processed.

2.4 查看 10046 的跟踪文件：

我搜索 904 这个错误，可以匹配到 err=904 的部分

=====================
PARSE ERROR #139705015595160:len=301 dep=0 uid=5 oct=3 lid=5 tim=1516181414835913 err=904
SELECT SYNNAM, DBMS_JAVA.LONGNAME(SYNNAM), DBMS_JAVA.LONGNAME(SYNTAB), TABOWN, TABNODE, PUBLIC$, SYNOWN, SYNOWNID, TABOWNID, SYNOBJNO FROM SYS.EXU9PTS WHERE SYNOBJNO IN (SELECT SYNOBJNO FROM SYS.EXU9TYPT WHERE TABOBJNO = :1) ORDER BY SYNTIME
WAIT #139705015595160: nam=’SQL*Net message to client’ ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=18 tim=1516181414836191
WAIT #139705015595160: nam=’SQL*Net message from client’ ela= 127 driver id=1650815232 #bytes=1 p3=0 obj#=18 tim=1516181414836355
WAIT #0: nam=’SQL*Net message to client’ ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=18 tim=1516181414836399

*** 2018-01-17 17:30:19.545
WAIT #0: nam=’SQL*Net message from client’ ela= 4709518 driver id=1650815232 #bytes=1 p3=0 obj#=18 tim=1516181419545928
WAIT #0: nam=’SQL*Net message to client’ ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=18 tim=1516181419546119
WAIT #0: nam=’SQL*Net message from client’ ela= 65 driver id=1650815232 #bytes=1 p3=0 obj#=18 tim=1516181419546230
PARSE #139705014733696:c=0,e=17,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1516181419547202
BINDS #139705014733696:
Bind#0
oacdty=11 mxl=16(16) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=00 csi=00 siz=16 off=0
kxsbbbfp=7f0f9bca8510 bln=16 avl=16 flg=05
value=00002451.0000.0001
EXEC #139705014733696:c=0,e=98,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1516181419547371
FETCH #139705014733696:c=0,e=59,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3684871272,tim=1516181419547468
CLOSE #139705014733696:c=0,e=29,dep=1,type=3,tim=1516181419547548
PARSE #139705014733696:c=0,e=26,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1516181419548823
BINDS #139705014733696:
Bind#0
oacdty=11 mxl=16(16) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=00 csi=00 siz=16 off=0
kxsbbbfp=7f0f9bca8510 bln=16 avl=16 flg=05
value=0000243C.0007.0001
EXEC #139705014733696:c=0,e=112,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1516181419549015
FETCH #139705014733696:c=1000,e=116,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3684871272,tim=1516181419549155
CLOSE #139705014733696:c=0,e=9,dep=1,type=3,tim=1516181419549188
=====================

也可以选择 tkprof 美化 trc 文件：

[root@jyrac1 ~]# tkprof /opt/app/oracle/diag/rdbms/jyzhao/jyzhao1/trace/jyzhao1_ora_15552.trc /tmp/jyzhao1_ora_15552.out waits=y sort=exeela

TKPROF: Release 11.2.0.4.0 – Development on Wed Jan 17 17:33:44 2018

美化后的文档可以更清晰的看到 904 错误的原因：

********************************************************************************

The following statement encountered a error during parse:

SELECT SYNNAM, DBMS_JAVA.LONGNAME(SYNNAM), DBMS_JAVA.LONGNAME(SYNTAB), TABOWN, TABNODE, PUBLIC$, SYNOWN, SYNOWNID, TABOWNID, SYNOBJNO FROM SYS.EXU9PTS WHERE SYNOBJNO IN (SELECT SYNOBJNO FROM SYS.EXU9TYPT WHERE TABOBJNO = :1) ORDER BY SYNTIME

Error encountered: ORA-00904
********************************************************************************

3. 匹配 MOS 文章

根据报错和 trc 文件的内容，匹配到 MOS 文档：Errors EXP-8 ORA-904 During Export (文档 ID 1091927.1)

APPLIES TO:
Oracle Database – Enterprise Edition – Version 10.2.0.4 and later
Information in this document applies to any platform.
Checked for relevance on 16-Dec-2014
SYMPTOMS
You are attempting to perform an export as the SYSTEM user or a DBA. During export, the following errors occurred:

EXP-00008: ORACLE error 904 encountered
ORA-00904: : invalid identifier

When performing a trace on the export, we see that the ORA-904 error is caused by the following statement:

PARSE ERROR #9:len=302 dep=0 uid=5 oct=3 lid=5 tim=778463694169 err=904
SELECT SYNNAM, DBMS_JAVA.LONGNAME(SYNNAM), DBMS_JAVA.LONGNAME(SYNTAB), TABOWN, TABNODE, PUBLIC$, SYNOWN, SYNOWNID, TABOWNID, SYNOBJNO FROM SYS.EXU9PTS WHERE SYNOBJNO IN (SELECT SYNOBJNO FROM SYS.EXU9TYPT WHERE TABOBJNO = :1) ORDER BY SYNTIME
CAUSE
The PUBLIC privilege has been revoked from the DBMS_JAVA package.

Privileges to the DBMS_JAVA package are needed for the export to succeed. However, as there is concern about security issues due to privileges on the DBMS_JAVA many have revoked the execute privilege to this package to keep the database secure.

SOLUTION
Explicitly grant EXECUTE on the DBMS_JAVA package to the DBA role:

SQL> grant execute on DBMS_JAVA to dba;

This will give members of the DBA role the rights to perform the export while keeping the DBMS_JAVA package out of the PUBLIC role.

4. 使用 MOS 解决方案

MOS 解决方案赋权 DBMS_JAVA 给 DBA 角色：

SYS@jyzhao1 >grant execute on DBMS_JAVA to dba;

Grant succeeded.

再次尝试 exp 导出成功。

[oracle@jyrac1 ~]$ exp system/oracle file=audit.dmp log=audit.log tables=sys.aud$

Export: Release 11.2.0.4.0 – Production on Wed Jan 17 17:39:07 2018

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Tes
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path …
Current user changed to SYS
. . exporting table AUD$ 25 rows exported
Export terminated successfully without warnings.

至此，问题完美解决，该案例的定位，主要用到了 oradebug 的跟踪技能。

更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址：http://www.linuxidc.com/Linux/2018-01/150539.htm

正文完

星哥说事-微信公众号