故事背景
本故事根据真实事件改编，写下处理的心路历程，某年某月某日，一个月黑风高的晚上，
项目组使用expdp和impdp迁移数据时，在impdp导入时报ORA-39006: internal error，并且说在测试环境测试的没问题
生产上这个库一个月前新搭建的，今天上线，我心想，测试都没问题，生产怎么会有问题，是不是项目组干了啥导致的
项目组给了个impdp的输出，真的只有ORA-39006: internal error ，问咋办，凉拌，没有更多的信息，首先怀疑dmp包在使用ftp传输的时候有问题，让项目组重新传了一份进行导入，还是报相同的错误
impdp前台日志
[oracle@cesdb3 ~]$ impdp '"/ as sysdba"' directory=my_dir dumpfile=testexp.dmp logfile=testexp.log.`date +%Y%m%d%H%M%S` schemas=testexp
Import: Release 19.0.0.0.0 - Production on Mon Jan 8 15:32:41 2024
Version 19.13.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
ORA-39006: internal error
问题分析
既然第一板斧没用，那就登录数据库服务器看看吧，先看看alert日志
alert日志
2024-01-08T15:32:45.464640+08:00 DM00 started with pid=61, OS id=75742, job SYS.SYS_IMPORT_SCHEMA_01 2024-01-08T15:32:48.279538+08:00 DW00 started with pid=62, OS id=75755, wid=1, job SYS.SYS_IMPORT_SCHEMA_01 2024-01-08T15:32:49.965293+08:00 DW00 terminating with fatal err=4063, pid=62, wid=1, job SYS.SYS_IMPORT_SCHEMA_01
有点收获，DW00 terminating with fatal err=4063，先查查这个，找到了mos (Doc ID 2060450.1)
里面介绍到了objects obj$ and dba_segments 不一致，expdp导出时会报DW00 terminating with fatal err=4063，而现在是impdp 导入时报，不太符合
alert 日志线索也断了，还能看啥呢，抱着试试的心态看看impdp的日志
impdp后台日志
[oracle@cesdb3 ~]$ cat testexp.log.20240108153241
;;;
Import: Release 19.0.0.0.0 - Production on Mon Jan 8 15:32:41 2024
Version 19.13.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
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-04063: package body "SYS.KUPW$WORKER" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.KUPW$WORKER"
ORA-06512: at line 2
哦哦哦，有情况，居然和前台的输出不一样，看看KUPW$WORKER 是个什么状态
1* select owner,object_name,object_type,status from dba_objects where object_name = 'KUPW$WORKER'
OWNER OBJECT_NAME OBJECT_TYPE STATUS
---------- -------------------- -------------------- -------
SYS KUPW$WORKER PACKAGE VALID
SYS KUPW$WORKER PACKAGE BODY INVALID
居然是失效的，再排查下sys和system下有没有其它对象也是失效的
SQL> select owner,object_name,object_type,status from dba_objects where owner in ('SYS','SYSTEM') and status = 'INVALID';
OWNER OBJECT_NAME OBJECT_TYPE STATUS
---------- -------------------- -------------------- -------
SYS KUPW$WORKER PACKAGE BODY INVALID
看来目前就这一个对象失效了，手工编译下试试
SQL> alter package KUPW$WORKER compile body;
Warning: Package Body altered with compilation errors.
SQL> show errors;
Errors for PACKAGE BODY KUPW$WORKER:
LINE/COL ERROR
-------- -----------------------------------------------------------------
34051/5 PL/SQL: SQL Statement ignored
34051/34 PL/SQL: ORA-00942: table or view does not exist
看来还是不太行，气血上涌，第二板斧，既然datapump有问题，那我就重建datapump呗, mos (Doc ID 430221.1)
@?/rdbms/admin/dpload.sql
@?/rdbms/admin/utlrp.sql
1* select owner,name,type,text from dba_errors
OWNER NAME TYPE TEXT
---------- -------------------- ------------------- ------------------------------------------------------------
SYS KUPW$WORKER PACKAGE BODY PL/SQL: ORA-00942: table or view does not exist
SYS KUPW$WORKER PACKAGE BODY PL/SQL: SQL Statement ignored
苦苦等待了20分钟，期间回想是不是做错了，不应该重建的，结果告诉我，第二板斧砍歪了，再寻mos，看看KUPW$WORKER 是个什么，找到了mos (Doc ID 2691905.1)，重建KUPW$WORKER 包
@?/rdbms/admin/prvthpui.plb
@?/rdbms/admin/prvtbpui.plb
alter package KUPW$WORKER compile body;
Warning: Package Body altered with compilation errors.
1* select owner,name,type,text from dba_errors
OWNER NAME TYPE TEXT
---------- -------------------- ------------------- ------------------------------------------------------------
SYS KUPW$WORKER PACKAGE BODY PL/SQL: ORA-00942: table or view does not exist
SYS KUPW$WORKER PACKAGE BODY PL/SQL: SQL Statement ignored
还是不行呢，再探mos，找到了mos (Doc ID 2668886.1)，我感觉已经接近答案了
alter session set events '10046 trace name context forever,level 12';
alter session set events '942 trace name errorstack level 3';
oradebug setmypid
oradebug tracefile_name
@?/rdbms/admin/prvtbpw.plb
show error
exit
--trace 中有以下信息
----- Data Guard Broker Runtime State -----
about to signal 942
Name: KU$NOEXP_TAB
--对象 `KU$NOEXP_TAB` 确实不存在
1* select owner,object_name,object_type,status from dba_objects where object_name = 'KU$NOEXP_TAB'
no rows selected
--按照mos开始操作
create global temporary table sys.ku$noexp_tab on commit preserve rows as select * from sys.ku_noexp_view;
grant select on sys.ku$noexp_tab to public;
grant insert on sys.ku$noexp_tab to public;
SQL> create global temporary table sys.ku$noexp_tab on commit preserve rows as select * from sys.ku_noexp_view;
create global temporary table sys.ku$noexp_tab on commit preserve rows as select * from sys.ku_noexp_view
*
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'TEMP'
当这个报错出来，我的第一想法是，mos 居然提供了一个错误的操作，再报再探，mos (Doc ID 1579215.1)
1* select segment_name, status,tablespace_name from dba_rollback_segs
SEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
SYSTEM ONLINE SYSTEM
_SYSSMU1_1261223759$ OFFLINE UNDOTBS1
_SYSSMU2_27624015$ OFFLINE UNDOTBS1
_SYSSMU3_2421748942$ OFFLINE UNDOTBS1
_SYSSMU4_625702278$ OFFLINE UNDOTBS1
_SYSSMU5_2101348960$ OFFLINE UNDOTBS1
_SYSSMU6_813816332$ OFFLINE UNDOTBS1
_SYSSMU7_2329891355$ OFFLINE UNDOTBS1
_SYSSMU8_399776867$ OFFLINE UNDOTBS1
_SYSSMU9_1692468413$ OFFLINE UNDOTBS1
_SYSSMU10_930580995$ OFFLINE UNDOTBS1
11 rows selected.
-- 脑子转不动了，cpu被烧了，它怎么能够是 offline 呢
SQL> show parameter undo_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
-- 啊，这怎么能够是 manual 呢，先不管，先改成 auto，把 ku$noexp_tab 建出来再说
alter system set undo_management=auto scope=spfile;
shu immediate
startup
create global temporary table sys.ku$noexp_tab on commit preserve rows as select * from sys.ku_noexp_view;
grant select on sys.ku$noexp_tab to public;
grant insert on sys.ku$noexp_tab to public;
alter package KUPW$WORKER compile body;
1* select owner,name,type,text from dba_errors
no rows selected
终于搞定，再试试impdp
[oracle@cesdb3 ~]$ impdp '"/ as sysdba"' directory=my_dir dumpfile=testexp.dmp logfile=testexp.log.`date +%Y%m%d%H%M%S` schemas=testexp
Import: Release 19.0.0.0.0 - Production on Mon Jan 8 21:08:54 2024
Version 19.13.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
Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01": "/******** AS SYSDBA" directory=my_dir dumpfile=testexp.dmp logfile=testexp.log.20240108210854 schemas=testexp
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"TESTEXP" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TESTEXP"."T1" 9.388 MB 73678 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Mon Jan 8 21:10:40 2024 elapsed 0 00:01:39
完美，没有任何问题，是不可能的
1* select COMP_ID,COMP_NAME,VERSION_FULL,STATUS from dba_registry where status='INVALID'
COMP_ID COMP_NAME VERSION_FULL STATUS
------------------------------ ---------------------------------------- ------------------------------ --------------------------------------------
CATPROC Oracle Database Packages and Types 19.13.0.0.0 INVALID
CATPROC 组件显示失效，看看有什么失效的对象
set serveroutput on;
declare
start_time date;
end_time date;
object_name varchar(100);
object_id char(10);
begin
SELECT date_loading, date_loaded into start_time, end_time FROM registry$
WHERE cid = 'CATPROC';
SELECT obj#,name into object_id,object_name
FROM obj$
WHERE status > 1 AND
(ctime BETWEEN start_time AND end_time OR
mtime BETWEEN start_time AND end_time OR
stime BETWEEN start_time AND end_time) AND
ROWNUM <=1;
dbms_output.put_line('Please compile Invalid object '||object_name||'
Object_id '||object_id );
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('CATPROC can be validated now' );
end;
/
CATPROC can be validated now
PL/SQL procedure successfully completed.
-- 没输出失效对象，看来是个显示问题，手工处理失效对象不会进行更新dba_registry，手工注册下
execute DBMS_REGISTRY_SYS.VALIDATE_CATPROC;
SQL> select COMP_ID,COMP_NAME,VERSION_FULL,STATUS from dba_registry where status='INVALID';
no rows selected
问题总结
1.后面查看alert日志的时候，发现建库第一次启动的时候，undo_management就等于manual 2.因为建库是通过 java 解析 excel(excel里有建库需要的信息) 生成建库脚本自动建库的，excel最后从服务器下载下来发现损坏打不开了，找不到原因了 3.回顾这个问题，只有解析excel生成的建库脚本把undo_management参数被设置为了manual，导致`KUPW$WORKER`失效 4.上面的处理步骤绕了很大的一圈才发现最终的问题，首先应该通过10046和errorstack发现报错更多的信息，这样可以通过mos进行高精度的搜索，做到精准打击，谋定而后动
参考
Expdp Terminated with "DW00 terminating with fatal err=4063" (Doc ID 2060450.1)
How To Reload Datapump Utility EXPDP/IMPDP (Doc ID 430221.1)
KUPW$WORKER or KUPU$UTILITIES_INT Invalid after Upgrade (Doc ID 2691905.1)
SYS.KUPW$WORKER Invalid and ORA-00942 when Executing PRVTBPW.PLB (Doc ID 2668886.1)
Troubleshooting Guide (TSG) - ORA-01552: Cannot Use System Rollback Segment for Non-System Tablespace (Doc ID 1579215.1)
Catalog and Catproc - How to find what Objects are keeping them Invalid in the Registry (dba_registry) (Doc ID 578841.1)
CATPROC Component Is Invalid and Will Not Validate (Doc ID 759635.1)
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
