OS환경 : AIX 6 1 (64bit)
DB 환경 : Oracle Database 11.2.0.3
에러 : ORA-04063: package body "SYS.DBMS_CUBE_EXP" has error
full export를 받으려고 expdp문을 실행하고 바로 발생한 에러
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | $ expdp system/oracle directory=mig2019 dumpfile=exptest_%U.dmp logfile=exp_test.log full=y parallel=8 Export: Release 11.2.0.3.0 - Production on Tue Nov 28 20:50:17 2019 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=mig2019 dumpfile=exptest_%U.dmp logfile=exp_test.log full=y parallel=8 Estimate in progress using BLOCKS method... ORA-39126: Worker unexpected fatal error in KUPW$WORKER.FETCH_XML_OBJECTS [] ORA-31642: the following SQL statement fails: BEGIN "SYS"."DBMS_CUBE_EXP".SCHEMA_CALLOUT(:1,0,1,'11.02.00.00.00'); END; ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "SYS.DBMS_METADATA", line 1749 ORA-04063: package body "SYS.DBMS_CUBE_EXP" has errors ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_CUBE_EXP" ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "SYS.KUPW$WORKER", line 8996 ----- PL/SQL Call Stack ----- object line object handle number name 700002d5bf94c08 20462 package body SYS.KUPW$WORKER 700002d5bf94c08 9028 package body SYS.KUPW$WORKER 700002d5bf94c08 10935 package body SYS.KUPW$WORKER 700002d5bf94c08 13698 package body SYS.KUPW$WORKER 700002d5bf94c08 2654 package body SYS.KUPW$WORKER 700002d5bf94c08 9697 package body SYS.KUPW$WORKER 700002d5bf94c08 1775 package body SYS.KUPW$WORKER 700002d7ea7d830 2 anonymous block . . |
해결 방법 : 패키지 컴파일, 패키지 재생성, 해당 패키지 삭제
해당 패키지 조회
1 2 3 4 5 6 7 8 9 10 11 12 | SQL> col object_name for a20 select object_name, object_type, status from dba_objects where object_name = 'DBMS_CUBE_EXP' / OBJECT_NAME OBJECT_TYPE STATUS -------------------- ------------------- ------- DBMS_CUBE_EXP PACKAGE INVAILD DBMS_CUBE_EXP PACKAGE BODY INVALID DBMS_CUBE_EXP SYNONYM VALID |
INVAILD 상태임
해당 패키지 컴파일 시도
1 2 3 | SQL> alter package DBMS_CUBE_EXP compile body; Warning: Package Body altered with compilation errors. |
컴파일 에러 발생
패키지 재생성 스크립트 실행
1 2 3 4 | SQL>conn / as sysdba SQL>spool fixolap.log SQL>@?/olap/admin/olapiboo.plb; SQl>spool off |
다시 해당 패키지 조회
1 2 3 4 5 6 7 8 9 10 11 12 | SQL> col object_name for a20 select object_name, object_type, status from dba_objects where object_name = 'DBMS_CUBE_EXP' / OBJECT_NAME OBJECT_TYPE STATUS -------------------- ------------------- ------- DBMS_CUBE_EXP PACKAGE INVAILD DBMS_CUBE_EXP PACKAGE BODY INVALID DBMS_CUBE_EXP SYNONYM VALID |
여전히 INVAILD 상태임
sys.exppkgact$에 있는 DBMS_CUBE_EXP 패키지 확인
1 2 3 4 5 6 7 8 9 10 11 | SQL> set lines 100 select * from sys.exppkgact$ where package='DBMS_CUBE_EXP' order by 1,2; PACKAGE SCHEMA CLASS LEVEL# ------------------------------ ------------------------------ ------------ ------------ DBMS_CUBE_EXP SYS 2 1050 DBMS_CUBE_EXP SYS 4 1050 DBMS_CUBE_EXP SYS 6 1050 |
sys.exppkgact$ 테이블 백업
1 2 3 | SQL> create table sys.exppkgact$_backup as select * from sys.exppkgact$; Table created. |
해당 패키지 삭제
1 2 3 4 5 6 7 | SQL> delete from sys.exppkgact$ where package='DBMS_CUBE_EXP' and schema='SYS'; 3 rows deleted. SQL> commit; Commit complete. |
expdp 재실행
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | $ expdp system/oracle directory=mig2019 dumpfile=exptest_%U.dmp logfile=exp_test.log full=y parallel=8 Export: Release 11.2.0.4.0 - Production on Thu Nov 29 08:56:05 2019 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. ;;; Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** directory=mig2019 dumpfile=exptest_%U.dmp logfile=exp_test.log full=y parallel=8 Estimate in progress using BLOCKS method... Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA Total estimation using BLOCKS method: 76.12 MB . . exported "ORDDATA"."ORDDCM_STD_ATTRS" 188.8 KB 2415 rows . . exported "APEX_030200"."WWV_FLOW_LIST_ITEMS" 590.3 KB 3048 rows . . exported "APEX_030200"."WWV_FLOW_REGION_REPORT_COLUMN" 1.199 MB 7903 rows . . exported "SYSMAN"."MGMT_HC_OS_COMPONENTS" 159.4 KB 1502 rows . . exported "APEX_030200"."WWV_FLOW_STEP_VALIDATIONS" 611.4 KB 1990 rows . . Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCACT_INSTANCE Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCDEPOBJ Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA Processing object type DATABASE_EXPORT/AUDIT Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is: /home/oracle/exptest_01.dmp /home/oracle/exptest_02.dmp /home/oracle/exptest_03.dmp /home/oracle/exptest_04.dmp /home/oracle/exptest_05.dmp /home/oracle/exptest_06.dmp /home/oracle/exptest_07.dmp /home/oracle/exptest_08.dmp Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Thu Nov 29 08:59:05 2019 elapsed 0 00:02:58 |
정상적으로 실행됨
원인 : DBMS_CUBE_EXP 패키지가 invalid 상태여서 발생한 문제
참조 : (Doc ID 2017988.1), (Doc ID 1998072.1), (Doc ID 1328829.1)