expdp 导出报错处理（ORA-39014 &ORA-39029 &ORA-31671）

expdp 导出报错处理（ORA-39014 &ORA-39029 &ORA-31671）
 
 

环境描述：19.7 CDB架构
 

问题描述： 每日dmp备份计划任务中，其中一个pdb 执行expdp导出报错。

问题回顾：
 

1）执行expdp报错信息，如下

[oracle@xxx dump]$ expdp system/xxx@orclpdb  directory=expdp_dir dumpfile=orclpdb%U_%T.dmp full=y logfile=orclpdb.log

Export: Release 19.0.0.0.0 - Production on Mon Aug 8 13:59:55 2022
Version 19.7.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
Starting "SYSTEM"."SYS_EXPORT_FULL_04":  system/********@orclpdb directory=expdp_dir dumpfile=orclpdb%U_%T.dmp full=y logfile=orclpdb.log
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name "DW00" prematurely terminated
ORA-31671: Worker process DW00 had an unhandled exception.
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 2 with process name "DW00" prematurely terminated
ORA-31671: Worker process DW00 had an unhandled exception.
Job "SYSTEM"."SYS_EXPORT_FULL_04" stopped due to fatal error at Mon Aug 8 14:01:26 2022 elapsed 0 00:01:30

2）查看alert 日志

– 检查alert日志内容，发现执行expdp 报错后， alert 日志中也会同时抛出ORA-00600错误。

2022-08-08T14:02:39.013116+08:00
orclpdb(4):Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2022-08-08T14:02:39.013188+08:00
orclpdb(4):
orclpdb(4):DW00 terminating with fatal err=600, pid=887, wid=1, job SYSTEM.SYS_EXPORT_SCHEMA_01
2022-08-08T14:02:55.006989+08:00
Thread 1 advanced to log sequence 93741 (LGWR switch)
  Current log# 8 seq# 93741 mem# 0: /oradata/orclpdb/redo08.log
2022-08-08T14:02:55.122548+08:00
ARC1 (PID:28290): Archived Log entry 95260 added for T-1.S-93740 ID 0xb54518f2 LAD:1
2022-08-08T14:03:08.755475+08:00
orclpdb(4):
orclpdb(4):DW00 started with pid=235, OS id=39398, wid=2, job SYSTEM.SYS_EXPORT_SCHEMA_01
2022-08-08T14:03:12.024573+08:00
Errors in file /disk1/oracle/diag/rdbms/orclpdb/orclpdb/trace/orclpdb_dw00_39398.trc  (incident=247656) (PDBNAME=orclpdb):
ORA-00600: internal error code, arguments: [qosdExpStatRead: expcnt mismatch], [], [], [], [], [], [], [], [], [], [], []
orclpdb(4):Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /disk1/oracle/diag/rdbms/orclpdb/orclpdb/trace/orclpdb_dw00_39398.trc  (incident=247657) (PDBNAME=orclpdb):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [qosdExpStatRead: expcnt mismatch], [], [], [], [], [], [], [], [], [], [], []
orclpdb(4):Incident details in: /disk1/oracle/diag/rdbms/orclpdb/orclpdb/incident/incdir_247657/orclpdb_dw00_39398_i247657.trc
2022-08-08T14:03:14.850910+08:00
orclpdb(4):Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /disk1/oracle/diag/rdbms/orclpdb/orclpdb/trace/orclpdb_dw00_39398.trc  (incident=247658) (PDBNAME=orclpdb):
ORA-00600: internal error code, arguments: [qosdExpStatRead: expcnt mismatch], [], [], [], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_STATS", line 2141
ORA-06512: at "SYS.DBMS_STATS", line 57585
ORA-06512: at "SYS.DBMS_STATS", line 57818
ORA-06512: at "SYS.KUPW$WORKER", line 30029
ORA-06512: at "SYS.KUPW$WORKER", line 3503
ORA-06512: at "SYS.KUPW$WORKER", line 13746
ORA-06512: at "SYS.KUPW$WORKER", line 2439
ORA-06512: at line 2
orclpdb(4):Incident details in: /disk1/oracle/diag/rdbms/orclpdb/orclpdb/incident/incdir_247658/orclpdb_dw00_39398_i247658.trc
2022-08-08T14:03:15.699352+08:00
Dumping diagnostic data in directory=[cdmp_20220808140315], requested by (instance=1, osid=39398 (DW00)), summary=[incident=247657].

3）查询mos资料

– 和我们这个现象相关联的Bug有两个

Bug 28681153 - ORA-600: [qosdexpstatread: expcnt mismatch] (Doc ID 28681153.8 )
Bug 31143146 - ORA-600: [qosdexpstatread: expcnt mismatch] even after applying 28681153 (Doc ID 31143146.8 )

Doc ID 31143146.8 文档描述：

4）检查数据库当前补丁情况

[oracle@xxxx ~]$ $ORACLE_HOME/OPatch/opatch lsinv|grep 28681153
     28672457, 28673945, 28681153, 28692103, 28692275, 28694639, 28694872

我们当前数据库已经安装补丁28681153，但是仍然发生ORA-600: [qosdexpstatread: expcnt mismatch] 错误，和文档Doc ID 31143146.8描述内容一致。

5）临时解决办法

-- 连接到pdb中
SQL> alter session set container=orcl;

Session altered.

SQL> With b as (select count(*) cnt,objn,snapshot_id from sys.exp_stat$ es group by objn,snapshot_id)select * from sys.exp_obj$ a, b 
  2  where a.objn=b.objn and a.snapshot_id=b.snapshot_id and a.EXP_CNT<>b.CNT;

      OBJN SNAPSHOT_ID    EXP_CNT        CNT       OBJN SNAPSHOT_ID
---------- ----------- ---------- ---------- ---------- -----------
     96444           1        652      66188      96444           1

-- manually update exp_stat$, exp_obj$

SQL> delete from sys.exp_stat$ b where b.snapshot_id = 1 and b.objn = 96444 ;

66188 rows deleted.

SQL> With b as (
  2  select count(*) cnt,objn,snapshot_id from sys.exp_stat$ es group by
  3  objn,snapshot_id)
  4  select * from sys.exp_obj$ a, b where a.objn=b.objn and
  5  a.snapshot_id=b.snapshot_id
  6  and a.EXP_CNT<>b.CNT;

no rows selected

SQL> update sys.exp_obj$ a set a.EXP_CNT=0 where a.SNAPSHOT_ID= 1 and a.objn = 96444;

1 row updated.

SQL> commit;

Commit complete.

SQL> With b as (
  2  select count(*) cnt,objn,snapshot_id from sys.exp_stat$ es group by
  3  objn,snapshot_id)
  4  select * from sys.exp_obj$ a, b where a.objn=b.objn and
  5  a.snapshot_id=b.snapshot_id
  6  and a.EXP_CNT<>b.CNT;

no rows selected

6）再次执行expdp任务

 执行expdp不再报错，恢复正常。 

7）后续补丁

– 我们需要利用停机维护时间，安装补丁31143146

1. 下载并打上one-off patch 31143146 

2. 使 bug 31143146 的修正有效 
ALTER SYSTEM SET "_FIX_CONTROL"='31143146:ON';