expdp시 발생한 ORA-39006: internal error ,ORA-39213: Metadata processing is not available 해결

OS환경 : Oracle Linux5.8(64bit)

DB 환경 : Oracle Database 10.2.0.5

에러 : ORA-39006: internal error ,ORA-39213: Metadata processing is not available

expdp 시 발생한 ORA-39006, ORA-39213 에러 트러블슈팅

# expdp 실행

[oracle@oracle ~]$ expdp system/oracle directory=TESTDATA dumpfile=testts.dmp schemas=test 
 
Export: Release 10.2.0.5.0 - 64bit Production on Wednesday, 28 February, 2018 10:29:59
 
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39006: internal error
ORA-39213: Metadata processing is not available

# dbms_metadata_util.load_stylesheets 실행

SQL> execute dbms_metadata_util.load_stylesheets
BEGIN dbms_metadata_util.load_stylesheets; END;
 
*
ERROR at line 1:
ORA-25153: Temporary Tablespace is Empty
ORA-06512: at "SYS.DBMS_METADATA_UTIL", line 1988
ORA-06512: at line 1

해결 방법 : 

# ORA-25153: Temporary Tablespace is Empty 에러 해결

# temp ts가 있지만 default temporary tablespace로 지정 안되있어서 나는 오류

SQL>alter database default temporary tablespace temp;

#dbms_metadata_util.load_stylesheets 재실행

SYS@orcl> exec dbms_metadata_util.load_stylesheets
 
PL/SQL procedure successfully completed.

# expdp 재실행

[oracle@oracle ~]$ expdp system/oracle directory=TESTDATA dumpfile=testts.dmp schemas=test 
 
Export: Release 10.2.0.5.0 - 64bit Production on Wednesday, 28 February, 2018 10:44:26
 
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39006: internal error
ORA-39065: unexpected master process exception in DISPATCH
ORA-04063: package body "SYS.KUPW$WORKER" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.KUPW$WORKER"
 
ORA-39097: Data Pump job encountered unexpected error -6508

# INVALID 오브젝트 확인

SYS@orcl> SET LINES 200
COL OBJECT_NAME FOR A35
COL OBJECT_TYPE FOR A25
COL Owner FOR A25
Select owner,object_name,object_type,status,created,LAST_DDL_TIME from dba_objects where status='INVALID' and owner='SYS' order by object_name;
 
OWNER                     OBJECT_NAME                         OBJECT_TYPE               STATUS  CREATED      LAST_DDL_TIM
------------------------- ----------------------------------- ------------------------- ------- ------------ ------------
SYS                       KUPW$WORKER                         PACKAGE BODY              INVALID 22-DEC-17    28-FEB-18
 
1 row selected.

# KUPW$WORKER 재컴파일

SYS@orcl> alter package KUPW$WORKER compile BODY;
 
Warning: Package Body altered with compilation errors.

# compile 안되는 문제 해결1

# utlrp 실행

SYS@orcl> @?/rdbms/admin/utlrp.sql
 
TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2018-02-28 11:07:33
 
1 row selected.
 
Elapsed: 00:00:00.01
DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.73
 
TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END  2018-02-28 11:07:34
 
1 row selected.
 
Elapsed: 00:00:00.00
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.00
DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
 
OBJECTS WITH ERRORS
-------------------
                  1
 
1 row selected.
 
Elapsed: 00:00:00.01
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
 
ERRORS DURING RECOMPILATION
---------------------------
                          0
 
1 row selected.
 
Elapsed: 00:00:00.00
 
PL/SQL procedure successfully completed.

# INVALID 오브젝트 확인

SYS@orcl> SET LINES 200
COL OBJECT_NAME FOR A35
COL OBJECT_TYPE FOR A25
COL Owner FOR A25
Select owner,object_name,object_type,status,created,LAST_DDL_TIME from dba_objects where status='INVALID' and owner='SYS' order by object_name;
 
OWNER                     OBJECT_NAME                         OBJECT_TYPE               STATUS  CREATED      LAST_DDL_TIM
------------------------- ----------------------------------- ------------------------- ------- ------------ ------------
SYS                       KUPW$WORKER                         PACKAGE BODY              INVALID 22-DEC-17    28-FEB-18
 
1 row selected.

# 계속 INVALID 상태

# compile 안되는 문제 해결2

SYS@orcl> CREATE GLOBAL TEMPORARY TABLE sys.ku$noexp_tab ON COMMIT PRESERVE ROWS  AS SELECT * FROM sys.ku_noexp_view;
 
Table created.
 
SYS@orcl> GRANT SELECT ON sys.ku$noexp_tab TO PUBLIC;
 
Grant succeeded.
 
SYS@orcl> GRANT INSERT ON sys.ku$noexp_tab TO PUBLIC;
 
Grant succeeded.
 
SYS@orcl> alter package KUPW$WORKER compile BODY;
 
Package body altered.

# INVALID 오브젝트 재확인

SYS@orcl> SET LINES 200
COL OBJECT_NAME FOR A35
COL OBJECT_TYPE FOR A25
COL Owner FOR A25
Select owner,object_name,object_type,status,created,LAST_DDL_TIME from dba_objects where status='INVALID' and owner='SYS' order by object_name;
 
no rows selected

INVALID 오브젝트가 없음을 확인

# expdp 재실행

[oracle@oracle ~]$ expdp system/oracle directory=TESTDATA dumpfile=testts.dmp schemas=test 
 
Export: Release 10.2.0.5.0 - 64bit Production on Wednesday, 28 February, 2018 11:10:23
 
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** directory=TESTDATA dumpfile=testts.dmp schemas=test 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6.25 MB
Processing object type SCHEMA_EXPORT/USER
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/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "test"."EMP3"                                4.546 MB  114689 rows
. . exported "test"."DEPT"                                5.656 KB       4 rows
. . exported "test"."DUMMY"                               4.914 KB       1 rows
. . exported "test"."EMP"                                 7.820 KB      14 rows
. . exported "test"."SALGRADE"                            5.585 KB       5 rows
. . exported "test"."BONUS"                                   0 KB       0 rows
. . exported "test"."PLAN_TABLE"                              0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/test/testts.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:10:36

# 정상 EXPDP 완료

원인 : INVALID 오브젝트가 존재해서 안됬던것

참조 : 

https://oracleracdba1.wordpress.com/2013/05/16/ora-04063-package-body-sys-kupwworker-has-errors/

http://gunnm.tistory.com/86

https://boomslaang.wordpress.com/2013/11/08/datapump-error-expdp-ora-39065-ora-01403/

http://www.dba-oracle.com/t_ora_39213_metadata_processing.htm

http://stepintooracledba.blogspot.kr/2015/02/ora-39065-unexpected-master-process.html