EXPエクスポートaud$エラーEXP-00008,ORA-00904解決

9374 ワード

件名:EXPエクスポートaud$エラーEXP-00008、ORA-00904解決環境:Oracle 11.2.0.4問題:独自のテスト環境でsysユーザーの下でaud$エラーをエクスポートします.
  • 1.故障現場
  • .トレース処理
  • 3.MOS文書
  • にマッチする
  • 4.MOSソリューション
  • を用いる
    1.故障現場
    sysユーザーの下の表をエクスポートし、経験に基づいてsystemユーザーを使用してエクスポートします.
  • Oracleがsysユーザーのシステム・テーブル
  • をエクスポートする方法
    この方法に従って、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
    
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
    
    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
    
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
    
    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
    
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

    美化された文書は、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 10927.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
    
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
    
    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の追跡スキルを使用しています.