运维首页 > 数据运维 > 一次impdp ORA39006 的填坑之旅

一次impdp ORA39006 的填坑之旅

2024年 1月 10日 46.9k 0

故事背景

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

三掌柜
数据运维
0 0

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF，云和恩墨技术通讯（2024年7月刊）
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论

三掌柜

这个人很懒，什么都没有留下～

文章8286 评论0