metalink上 找到对应的文档 (文档 ID 1903510.1) ：
EXPDP/IMPDP - ORA-04063 (view "SYS.KU$_RADM_FPTM_VIEW" Has Errors) on Full Export / Import (文档 ID 1903510.1)
In this Document
Symptoms
Changes
Cause
Solution
References
Applies to:
Enterprise Manager for Oracle Database - Version 12.1.0.1.0 to 13.2.2.0.0 [Release 12.1 to 13.2]
Oracle Database - Enterprise Edition - Version 12.1.0.1 and later
Oracle Database - Standard Edition - Version 12.1.0.1 and later
Oracle Database - Personal Edition - Version 12.1.0.1 and later
Information in this document applies to any platform.
Symptoms
1. With the Database Configuration Assistant (dbca) you have created a multitenant configuration with a Container Database (CDB) and one of more Pluggable Databases (PDB):
connect / as sysdba
select dbid,name,cdb,open_mode,con_id from v$database;
DBID NAME CDB OPEN_MODE CON_ID
------------ --------- --- -------------------- ------------
3200536659 M12101WU YES READ WRITE 0
alter session set container = cdb$root;
show con_name
CON_NAME
------------------------------
CDB$ROOT
col pdb_name for a30
select pdb_id, pdb_name, status from dba_pdbs order by 1;
PDB_ID PDB_NAME STATUS
------------ ------------------------------ ---------
2 PDB$SEED NORMAL
3 M12101WUPDB1 NORMAL
4 M12101WUPDB2 NORMAL
2. While going through the steps in DBCA, you have specified UTF8 for the national characaterset instead of the default AL16UTF16 national characaterset:
col parameter for a35
col value for a40
select * from nls_database_parameters where parameter like '%SET' order by 1;
PARAMETER VALUE
----------------------------------- ----------------------------------------
NLS_CHARACTERSET WE8MSWIN1252
NLS_NCHAR_CHARACTERSET UTF8
3. After installation, you try to run a full database Export Data Pump job, but the expdp job fails with:
expdp system/manager@m12101wupdb1 directory=my_dir dumpfile=expdp_tc.dmp logfile=expdp_tc.log reuse_dumpfiles=y full=y
Export: Release 12.1.0.1.0 - Production on Mon Jun 30 11:46:36 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/********@m12101wupdb1 directory=my_dir dumpfile=expdp_tc.dmp logfile=expdp_tc.log reuse_dumpfiles=y full=y
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.515 MB
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/ROLE
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.FETCH_XML_OBJECTS [ROLE:"APEX_GRANTS_FOR_NEW_USERS_ROLE"]
ORA-04063: view "SYS.KU$_RADM_FPTM_VIEW" has errors
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 11014
----- PL/SQL Call Stack -----
object line object
handle number name
000007FF347FE160 26217 package body SYS.KUPW$WORKER
000007FF347FE160 11041 package body SYS.KUPW$WORKER
000007FF347FE160 13189 package body SYS.KUPW$WORKER
000007FF347FE160 3128 package body SYS.KUPW$WORKER
000007FF347FE160 11737 package body SYS.KUPW$WORKER
000007FF347FE160 2059 package body SYS.KUPW$WORKER
000007FF357A4200 2 anonymous block
KUPF$FILE.WRITE_LOB
KUPF$FILE.WRITE_LOB
MD FilePieces Count: 1
FORALL
FORALL
DBMS_LOB.TRIM
DBMS_LOB.TRIM
DBMS_METADATA.FETCH_XML_CLOB
DBMS_METADATA.FETCH_XML_CLOB
In procedure DETERMINE_FATAL_ERROR
Job "SYSTEM"."SYS_EXPORT_FULL_01" stopped due to fatal error at Mon Jun 30 11:47:10 2014 elapsed 0 00:00:29
NOTE:
The same error can be hit during a full DataPump import when the view SYS.KU$_RADM_FPTM_VIEW is in an inconsistent status.
Changes
Cause
1. The view SYS.KU$_RADM_FPTM_VIEW is invalid in the CDB and the PDB's, an it stays invalid even if you run the script utlrp.sql to recompile the invalid objects:
alter session set container = cdb$root;
?\rdbms\admin\utlrp.sql
-- check for invalid objects
set lines 140
col status for a9
col object_type for a20;
col owner.object for a50
select status, object_id, object_type, owner||'.'||object_name "OWNER.OBJECT"
from dba_objects
where status != 'VALID' and object_name not like 'BIN$%' order by 4,1;
STATUS OBJECT_ID OBJECT_TYPE OWNER.OBJECT
--------- ------------ -------------------- --------------------------------------------------
INVALID 12255 VIEW SYS.KU$_RADM_FPTM_VIEW
-- check view SYS.KU$_RADM_FPTM_VIEW across the PDB's:
select con_id, status, object_id, object_type,
owner||'.'||object_name "OWNER.OBJECT"
from cdb_objects where object_name like '%KU$_RADM_FPTM_VIEW%' order by 4,1;
CON_ID STATUS OBJECT_ID OBJECT_TYPE OWNER.OBJECT
------------ --------- ------------ -------------------- --------------------------------------------------
1 INVALID 12255 VIEW SYS.KU$_RADM_FPTM_VIEW
2 INVALID 12253 VIEW SYS.KU$_RADM_FPTM_VIEW
3 INVALID 12253 VIEW SYS.KU$_RADM_FPTM_VIEW
4 INVALID 12253 VIEW SYS.KU$_RADM_FPTM_VIEW
alter view SYS.KU$_RADM_FPTM_VIEW compile;
Warning: View altered with compilation errors.
show errors view SYS.KU$_RADM_FPTM_VIEW
Errors for VIEW SYS.KU$_RADM_FPTM_VIEW:
LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 ORA-00932: inconsistent datatypes: expected NCHAR got NCHAR
This is caused by a mismatch between the character set in which the table radm_fptm$ is created and in which the type ku$_radm_fptm$_t is created. This leads to the ORA-932 error when trying to recompile the view SYS.KU$_RADM_FPTM_VIEW.
2. You found the following document:
Note 1641033.1 - Failed To Export MGMTDB Due To Invalid Object KU$_RADM_FPTM_VIEW in 12c
which was based on following defect:
Bug 16498596 - LNX64-12.1-CHM: FAILED TO EXPORT MGMTDB DUE TO INVALID OBJECT KU$_RADM_FPTM_VIEW (not a public bug), fixed in 12.1.0.2 and 12.2.0.1
Based on this document, you tried the workaround to recompile the view. The view became valid in the CDB, but it remains invalid in the PDB's. It also cannot be recompiled in the PDB's (which is actually expected behavior):
alter session set container = cdb$root;
alter type ku$_radm_fptm_t compile reuse settings;
alter view SYS.KU$_RADM_FPTM_VIEW compile;
View altered.
-- check view SYS.KU$_RADM_FPTM_VIEW across the PDB's:
set lines 140
col status for a9
col object_type for a20;
col owner.object for a50
select con_id, status, object_id, object_type,
owner||'.'||object_name "OWNER.OBJECT"
from cdb_objects where object_name like '%KU$_RADM_FPTM_VIEW%' order by 4,1;
CON_ID STATUS OBJECT_ID OBJECT_TYPE OWNER.OBJECT
------------ --------- ------------ -------------------- --------------------------------------------------
1 VALID 12255 VIEW SYS.KU$_RADM_FPTM_VIEW
2 INVALID 12253 VIEW SYS.KU$_RADM_FPTM_VIEW
3 INVALID 12253 VIEW SYS.KU$_RADM_FPTM_VIEW
4 INVALID 12253 VIEW SYS.KU$_RADM_FPTM_VIEW
-- try to recompile in the PDB:
alter session set container = m12101wupdb1;
-- recompile invalid objects:
alter session set container = cdb$root;
@?\rdbms\admin\utlrp.sql
-- check for invalid objects:
set lines 140
col status for a9
col object_type for a20;
col owner.object for a50
select status, object_id, object_type, owner||'.'||object_name "OWNER.OBJECT"
from dba_objects
where status != 'VALID' and object_name not like 'BIN$%' order by 4,1;
STATUS OBJECT_ID OBJECT_TYPE OWNER.OBJECT
--------- ------------ -------------------- --------------------------------------------------
INVALID 12253 VIEW SYS.KU$_RADM_FPTM_VIEW
alter type ku$_radm_fptm_t compile reuse settings
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database
3. The problem in this multitenant configuration is related to following unpublished defects:
Bug 14409012 - NLS:DATAPUMP: WHEN NLS_NCHAR_CHARACTERSET IS UTF8 EXPDP FAILS IN FULL MODE (not a public bug), closed as a duplicate of Bug 13995404
Bug 17847697 - 12102_131122: INVALID OBJECT AND COMPONENT IN MANAGEMENT DB (not a public bug), closed as a duplicate of Bug 13995404
The base defect for both is unpublished:
Bug 13995404 - 12.1_120420: INVALID OBJECT 'KU$_RADM_FPTM_VIEW' WITH MGMTDB (not a public bug)
closed as duplicate of:
Unpublished Bug 24719799 : RACOPC: KU$_RADM_FPTM_VIEW INVALID ON NATIONAL CHARACTERSET CONVERSION.
The fix of Bug 24719799 supplies a method to invalidate the types within ADNCS so that utlrp.sql will pick up and recompile the types impacted by character set change.
The fix is to update the national character set id stored in the type images to the new national character set id during the alter database national character set command.
Solution
Re-create the view and its underlying type in the CDB, e.g.:
-- connect to the CDB:
connect / as sysdba
alter session set container = cdb$root;
-- obtain the DDL for the view SYS.KU$_RADM_FPTM_VIEW:
set lines 100 numwidth 12 pages 10000 long 2000000000
col ddl for a100
select dbms_metadata.get_ddl('VIEW','KU$_RADM_FPTM_VIEW','SYS') "DDL" from dual;
DDL
----------------------------------------------------------------------------------------------------
CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "SYS"."KU$_RADM_FPTM_VIEW" OF "SYS"."KU$_RADM_FPTM_T"
WITH OBJECT IDENTIFIER (fpver) AS
select '1','0',
numbercol, binfloatcol, bindoublecol,
charcol, varcharcol, ncharcol, nvarcharcol,
datecol,ts_col,tswtz_col, fpver
from sys.radm_fptm$
where fpver=1
-- obtain the DDL for the type SYS.KU$_RADM_FPTM_T:
select dbms_metadata.get_ddl('TYPE','KU$_RADM_FPTM_T','SYS') "DDL" from dual;
DDL
----------------------------------------------------------------------------------------------------
CREATE OR REPLACE NONEDITIONABLE TYPE "SYS"."KU$_RADM_FPTM_T" as object
(
vers_major char(1), /* UDT major version # */
vers_minor char(1), /* UDT minor version # */
numbercol number, /* number */
binfloatcol binary_float, /* binary float */
bindoublecol binary_double, /* binary double */
charcol char(1), /* fixed-size character */
varcharcol varchar2(1), /* variable-size character */
ncharcol nchar(1), /* fixed-size national character */
nvarcharcol nvarchar2(1), /* variable-size national character */
datecol date, /* date */
ts_col timestamp, /* timestamp */
tswtz_col timestamp with time zone, /* timestamp with time zone */
fpver number /* version of default fixed point values */
)
-- based on the previous output, re-create the type and the view in the CDB:
alter session set container = cdb$root;
connect / as sysdba
CREATE OR REPLACE NONEDITIONABLE TYPE "SYS"."KU$_RADM_FPTM_T" as object
(
vers_major char(1), /* UDT major version # */
vers_minor char(1), /* UDT minor version # */
numbercol number, /* number */
binfloatcol binary_float, /* binary float */
bindoublecol binary_double, /* binary double */
charcol char(1), /* fixed-size character */
varcharcol varchar2(1), /* variable-size character */
ncharcol nchar(1), /* fixed-size national character */
nvarcharcol nvarchar2(1), /* variable-size national character */
datecol date, /* date */
ts_col timestamp, /* timestamp */
tswtz_col timestamp with time zone, /* timestamp with time zone */
fpver number /* version of default fixed point values */
)
/
CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "SYS"."KU$_RADM_FPTM_VIEW" OF "SYS"."KU$_RADM_FPTM_T"
WITH OBJECT IDENTIFIER (fpver) AS
select '1','0',
numbercol, binfloatcol, bindoublecol,
charcol, varcharcol, ncharcol, nvarcharcol,
datecol,ts_col,tswtz_col, fpver
from sys.radm_fptm$
where fpver=1
/
-- now recompile the invalid objects in the PDB:
alter session set container = m12101wupdb1;
@?\rdbms\admin\utlrp.sql
set lines 140
col status for a9
col object_type for a20;
col owner.object for a50
select status, object_id, object_type, owner||'.'||object_name "OWNER.OBJECT"
from dba_objects
where status != 'VALID' and object_name not like 'BIN$%' order by 4,1;
no rows selected
-- check view SYS.KU$_RADM_FPTM_VIEW across the PDB's:
alter session set container = cdb$root;
select con_id, status, object_id, object_type,
owner||'.'||object_name "OWNER.OBJECT"
from cdb_objects where object_name like '%KU$_RADM_FPTM_VIEW%' order by 4,1;
CON_ID STATUS OBJECT_ID OBJECT_TYPE OWNER.OBJECT
------------ --------- ------------ -------------------- --------------------------------------------------
1 VALID 12255 VIEW SYS.KU$_RADM_FPTM_VIEW
2 INVALID 12253 VIEW SYS.KU$_RADM_FPTM_VIEW
3 VALID 12253 VIEW SYS.KU$_RADM_FPTM_VIEW
4 INVALID 12253 VIEW SYS.KU$_RADM_FPTM_VIEW
-- do the same for the other PDB's: recompile the invalid objects
Then re-run the Export Data Pump job, e.g.:
expdp system/manager@m12101wupdb1 directory=my_dir dumpfile=expdp_tc.dmp logfile=expdp_tc.log reuse_dumpfiles=y full=y
Export: Release 12.1.0.1.0 - Production on Mon Jun 30 11:53:46 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_02": system/********@m12101wupdb1 directory=my_dir dumpfile=expdp_tc.dmp logfile=expdp_tc.log reuse_dumpfiles=y full=y
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
...
Master table "SYSTEM"."SYS_EXPORT_FULL_02" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_02 is:
D:\WORK\EXPDP_TC.DMP
Job "SYSTEM"."SYS_EXPORT_FULL_02" successfully completed at Mon Jun 30 11:56:34 2014 elapsed 0 00:02:44
NOTE:
The issue and the associated solution are not multitenant specific. Hence the same solution works in a non-multitenant environment as well.
References
NOTE:1641033.1 - Failed To Export MGMTDB Due To Invalid Object KU$_RADM_FPTM_VIEW in 12c |
|手机版|小黑屋|重庆思庄Oracle、Redhat认证学习论坛
( 渝ICP备12004239号-4 )
发表于 2018-7-9 11:32:09
QQ好友和群
QQ空间
腾讯微博
腾讯朋友
收藏
支持
反对
楼主