首页 » ORACLE 9i-23c » Troubleshooting Oracle 12c 导出失败expdp ORA-31626 ORA-31637 ORA-12805, exp EXP-00064

Troubleshooting Oracle 12c 导出失败expdp ORA-31626 ORA-31637 ORA-12805, exp EXP-00064

最近在遇到一套Oracle 12c R2 RAC多租户+ADG的环境中,遇到了一些导出数据的问题。导出使用的是expdp命令,但是遇到了ORA-31626、ORA-31637和ORA-12805的错误。尝试改用exp命令时,也出现了一些奇怪的现象,部分表报EXP-00064错误,尽管这些表并不是嵌套表,并且使用CTAS创建一个复制表时可以正常导出,但使用expdp命令导出时仍然报错。这些表的列类型只是number、char和blob。经过反复诊断测试找到问题并还原,简单记录。

 

EXPDP错误

DataPump export (expdp) 或如下错误:

ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_SCHEMA for user SCOTT
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
ORA-06512: at “SYS.KUPV$FT_INT”, line 600
ORA-12805: parallel query server died unexpectedly

MOS中有记录是一个已知原因是 DataPump 安装存在问题。 要么是一开始没安装好,要么是后来出了什么问题。解决方法建议重新reload datapump.方法如下:

On Multitenant Environment, for Oracle versions 12.1, 12.2, 18c, 19c, 21c, and 23c:

-- 1. To rebuild the DataPump packages with the following steps.
Under the ORACLE_HOME, execute:
cd rdbms/admin-- run the dpload.sql in the CDB with all of the PDBs open, as described in "How to execute sql scripts in Multitenant environment (catcon.pl)" NOTE 1932340.1Syntax:
 
$ORACLE_HOME/perl/bin/perl catcon.pl -d /u01/oracle -l <logging directory> -b <prefix for logfile of dpload for each PDB> dpload.sqlWhere:
-l - directory to use for spool log files; for example, /tmp
-b - base name for log and spool file names; for example, dp_rebuild_log_base_nameFor example, at OS promptcd $ORACLE_HOME/rdbms/admin

$ORACLE_HOME/perl/bin/perl catcon.pl -d /u01/oracle -l /tmp -b dp_rebuild_log_base_name dpload.sql

NOTE:

After dpload.sql is run via caton.pl, to recompile any invalid objects, utlrp.sql can be run in all containers vi catcon.pl as well, e.g.

host perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -l /path_for_logs -b name_for_logs $ORACLE_HOME/rdbms/admin/utlrp.sql;

This command can be run several times to compile objects which did not get processed during the first run.
Also, if there are Data Pump components that are still invalid in any one container, utlrp.sql can be run individually in that container, e.g.

connect / as sysdba
alter session set container = <PDB_NAME>
@utlrp.sql

在测试环境dpload正常,虽然不需要重启实例,但还是担心对生产影响,因生产无维护窗口,暂时先不尝试reload,改用exp。(虽然exp已不建议使用)

EXP错误

改用exp导出格式如 exp xxx@xx file=xxx tables=xxx.yy buffers=1000000

但是CTAS一个复制表,就可以正常导出,同时库里有其它表试出不可以导出。

EXP sql trace跟踪
因为部分表失败,对导出失败的表做10046 sql trace跟踪,进入exp交互模式启用10046, 或使用login triiger.或使用event++ 语法:

  alter system set events 'sql_trace {process:pname=cjq|pname=j0}';         -- 11g syntax
  alter system set events 'sql_trace {process:pname=cjq|process:pname=j0}'; -- 12c syntax

alter system set events '10046{process: pname=exp} /* target exp */

这里我们使用交互的方式过去exp进程启用sql trace,再exp导出。

1. 启动exp交互:

# exp username/password

2. 去SQL*Plus查看exp的进程信息

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%'); 

3. 在SQL*Plus启用10046 events 跟踪

SQL> oradebug setospid xxx (xxx = spid from above query)
SQL> oradebug unlimit
SQL> oradebug tracefile_name
SQL> oradebug Event 10046 trace name context forever, level 12

4. exp交互输入,导致Table 模式,输入表名,交互,直到报错
exp trace file

=====================
PARSING IN CURSOR #140036662172504 len=643 dep=0 uid=9 oct=3 lid=9 tim=133633457005340 hv=4183964855 ad='4c2b909488' sqlid='7vh54fmwq4g5r'
SELECT OBJID, NAME, OWNER, OWNERID, TABLESPACE, TSNO, FILENO, BLOCKNO,             AUDIT$, COMMENT$, CLUSTERFLAG, PCTFREE$, PCTUSED$, INITRANS,           MAXTRANS, DEGREE, INSTANCES, CACHE, PROPERTY, DEFLOG, TSDEFLOG,           
		ROID, ROWCNT, BLKCNT, AVGRLEN, TFLAGS, TRIGFLAG, OBJSTATUS,             XDBOOL, DOBJID     
		FROM   SYS.EXU10TABS      
		WHERE  NAME = :1 AND             SECONDARYOBJ = :2 AND             OWNERID = :3 AND             
		NOT EXISTS (              
		SELECT NAME                
		FROM   SYS.EXU8NXPN$               
		WHERE  N$.OWNERID = :3 AND     N$.NAME = :1 AND       N$.TYPE = 2)
END OF STMT

BINDS #140036662172504:
 Bind#0
  oacdty=01 mxl=128(38) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000000 frm=01 csi=852 siz=176 off=0
  kxsbbbfp=7f5cd38108d8  bln=128  avl=19  flg=05
  value="Nxxxxxx_BLOB"
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000000 frm=00 csi=00 siz=0 off=128
  kxsbbbfp=7f5cd3810958  bln=22  avl=01  flg=01
  value=0
 Bind#2
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000000 frm=00 csi=00 siz=0 off=152
  kxsbbbfp=7f5cd3810970  bln=22  avl=03  flg=01
  value=110
 Bind#3
  No oacdef for this bind.
 Bind#4
  No oacdef for this bind.

— catexp.sql 中两个view, 用于exp
SYS.EXU10TABS — 导出表
SYS.EXU8NXP –排除表

对比了不能导出的表和能正常导出表以上查询记录,仅PROPERTY关键列不同,查询EXU10TABS的定义,来自TAB$。

PROPERTY 值

-- 不正常表
SQL> @dec 1180591620717948438528
                                DEC                  HEX
----------------------------------- --------------------
      1180591620717948438528.000000   400000000020040800

-- 正常表
SQL> @dec 537135104
                                DEC                  HEX
----------------------------------- --------------------
                   537135104.000000             20040800

-- dcore.bsq
  property      number not null,            /* table properties (bit flags): */
                              /* 0x01 = typed table, 0x02 = has ADT columns, */
                 /* 0x04 = has nested-TABLE columns, 0x08 = has REF columns, */
                      /* 0x10 = has array columns, 0x20 = partitioned table, */
               /* 0x40 = index-only table (IOT), 0x80 = IOT w/ row OVerflow, */
             /* 0x100 = IOT w/ row CLustering, 0x200 = IOT OVeRflow segment, */
               /* 0x400 = clustered table, 0x800 = has internal LOB columns, */
        /* 0x1000 = has primary key-based OID$ column, 0x2000 = nested table */
                    /* 0x4000 = View is Read Only, 0x8000 = has FILE columns */
       /* 0x10000 = obj view's OID is system-gen, 0x20000 = used as AQ table */
                                   /* 0x40000 = has user-defined lob columns */
                               /* 0x00080000 = table contains unused columns */
                            /* 0x100000 = has an on-commit materialized view */
                             /* 0x200000 = has system-generated column names */
                                      /* 0x00400000 = global temporary table */
                            /* 0x00800000 = session-specific temporary table */
                                        /* 0x08000000 = table is a sub table */
                                        /*   0x20000000 = pdml itl invariant */
                                          /* 0x80000000 = table is external  */
                          /* PFLAGS2: 0x400000000 = delayed segment creation */
  /* PFLAGS2: 0x20000000000 = result cache mode FORCE enabled on this table  */
  /* PFLAGS2: 0x40000000000 = result cache mode MANUAL enabled on this table */
  /* PFLAGS2: 0x80000000000 = result cache mode AUTO enabled on this table   */
  /* PFLAGS2: 0x400000000000000 = has identity column                        */

0x400000000000000000没有找到说明。有可能是个错误的值。

在测试环境创建一个表,PROPERTY 增加0x400000000000000000尝试

-- 测试
SQL> @o t209

owner                     object_name                    subname                        object_type          status           OID      D_OID CREATED             LAST_DDL_TIME
------------------------- ------------------------------ ------------------------------ -------------------- --------- ---------- ---------- ------------------- -------------------
ANBOB                     T209                                                          TABLE                VALID          80562      80562 2024-01-19 13:56:15 2024-01-19 14:01:05

SQL> select property,obj#,dataobj# from sys.tab$ where obj#=80562;

       PROPERTY            OBJ#        DATAOBJ#
--------------- --------------- ---------------
    17717004288           80562           80562

SQL> @dec 17717004288

                                DEC                  HEX
----------------------------------- --------------------
                 17717004288.000000            420040800

SQL> @hex 400000000420040800

                                DEC                  HEX
----------------------------------- --------------------
      1180591620735128307712.000000   400000000420040800

SQL> update sys.tab$ set property=1180591620735128307712 where obj#=80562;

1 row updated.

SQL> commit;

Commit complete.

[oracle@oel7db1 admin]$ exp anbob/oracle@cdb1pdb1 tables=t209 file=test209.dmp

Export: Release 19.0.0.0.0 - Production on Fri Jan 19 14:09:11 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
EXP-00064: T209 is an inner nested table and cannot be exported.
Export terminated successfully with warnings.

SQL> update sys.tab$ set property=17717004288  where obj#=80562;
1 row updated.

SQL> commit;
Commit complete.

[oracle@oel7db1 admin]$ exp anbob/oracle@cdb1pdb1 tables=t209 file=test209.dmp

Export: Release 19.0.0.0.0 - Production on Fri Jan 19 14:12:09 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                           T209          0 rows exported
Export terminated successfully without warnings.
[oracle@oel7db1 admin]$

Note:
还原了该问题EXP-00064 。手动update tab$恢复原值,exp正常。 注 不要在对未知操作更新dict. 我们前期已跑过hcheck.sql 无dict 逻辑错误,但对于值的错误是hcheck无法检查的。注意上面的操作我并未flush shared pool,对于exp是实时生效,所以也可以尝试在exp后update会原值。

alter table move online会不会修正该值?

SQL> alter table  anbob.t209 move online;
Table altered.

SQL> @o t209
no rows selected

SQL> @o anbob.t209

owner                     object_name                    subname                        object_type          status                OID           D_OID CREATED             LAST_DDL_TIME
------------------------- ------------------------------ ------------------------------ -------------------- --------- --------------- --------------- ------------------- -------------------
ANBOB                     T209                                                          TABLE                VALID               80562           80565 2024-01-19 13:56:15 2024-01-19 14:19:21

SQL> select property,obj#,dataobj# from sys.tab$ where obj#=80562
  2  ;

       PROPERTY            OBJ#        DATAOBJ#
--------------- --------------- ---------------
    17717004288           80562           80565

SQL> update sys.tab$ set property=1180591620735128307712 where obj#=80562;
1 row updated.

SQL> commit;
Commit complete.

SQL> alter table  anbob.t209 move online;
Table altered.

SQL> select property,obj#,dataobj# from sys.tab$ where obj#=80562;
       PROPERTY            OBJ#        DATAOBJ#
--------------- --------------- ---------------
    17717004288           80562           80566

SQL> update sys.tab$ set property=1180591620735128307712 where obj#=80562;
1 row updated.

SQL> commit;
Commit complete.

SQL> alter system flush shared_pool;
System altered.

SQL> select property,obj#,dataobj# from sys.tab$ where obj#=80562;
       PROPERTY            OBJ#        DATAOBJ#
--------------- --------------- ---------------
1.180591621E+21           80562           80566

SQL> alter table  anbob.t209 move online;
Table altered.
SQL> select property,obj#,dataobj# from sys.tab$ where obj#=80562;

       PROPERTY            OBJ#        DATAOBJ#
--------------- --------------- ---------------
1.180591621E+21           80562           80567

Note:
alter table move online并不会修正PROPERTY值,而是复用内存中的值,而不是update后未flush shared pool的值。

如果update后有没有办法不flush shared pool而让内存中的对象生效呢? 恐怕要做一个rename或修改表结构的DDL, 这里我们可以考虑增删一个隐藏列。

SQL> alter table anbob.t209 add id3  int invisible default 0  not null ;

SQL> alter table  anbob.t209 drop column id3;

Summary:
12c的数据库因为之前安装的datapump问题,导致expdp无法使用,需要datapump reload,但无法确认其影响,改用exp时发现部分表导出错误的提示exp-64诊断是嵌套表,但实际并不是,从standby database导出一样的报错,后分析为tab$.property值错误(不确认原因), 经测试导出报错的表均是该问题,手动修改后可以解决。

— over —

打赏

, ,

目前这篇文章还没有评论(Rss)

我要评论