故事背景
本故事根据真实事件改编,写下处理的心路历程,某年某月某日,一个月黑风高的晚上,
项目组使用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 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)