Slight 11.2.0.2 caveat with TTS and missing XMLDB
Posted by Martin Bach on May 10, 2011
A small post about a problem I encountered a little while ago, quite interesting nevertheless.
The background story is that-for security reasons-my customer’s databases are all created with “Oracle Database Catalog Views” and “Oracle Database Packages and Types” only. Anything else will have to be requested by the application team. I like the idea in principle, and it is certainly a lot better than all those databases with UltraSearch, Spatial and Text installed which open the door to vulnerabilities and exploits. Have a look at the problems fixed by Critical Patch Updates and you will know what I am talking about.
As part of a project I have been asked to transport a tablespace from such a database to another host. Everything is fine until you try to export the tablespace metadata with expdp. As you know, anyone using traditional exp is a bit backward ;)
[oracle@node1 ~] $ expdp / dumpfile=tts_metadata.dmp directory=data_pump_dir logfile=tts_metadata.log \ > transport_tablespaces='TTS_TEST' Export: Release 11.2.0.2.0 - Production on Tue May 10 16:05:49 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Starting "ORACLE"."SYS_EXPORT_TRANSPORTABLE_01": /******** dumpfile=tts_metadata.dmp logfile=tts_metadata.log directory=data_pump_dir transport_tablespaces=TTS_TEST Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UNLOAD_METADATA [PLUGTS_BLK] ORA-00904: "Q"."TABOBJ_NUM": invalid identifier ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95 ORA-06512: at "SYS.KUPW$WORKER", line 8358 ----- PL/SQL Call Stack ----- object line object handle number name 0x15e53a900 19208 package body SYS.KUPW$WORKER 0x15e53a900 8385 package body SYS.KUPW$WORKER 0x15e53a900 2935 package body SYS.KUPW$WORKER 0x15e53a900 9054 package body SYS.KUPW$WORKER 0x15e53a900 1688 package body SYS.KUPW$WORKER 0x155a4e638 2 anonymous block Job "ORACLE"."SYS_EXPORT_TRANSPORTABLE_01" stopped due to fatal error at 16:05:57 [oracle@node1 ~]
This is due to bug 10185688, which is fixed in 12.1, 11.2.0.3.0, or in one-off patch 10185688. Oracle 12.1 and 11.2.0.3 are not available at the time of this writing.
You will only encounter this problem if your database doesn’t have XML and dependent options compiled in. I wonder how many databases out there have options in DBA_SERVER_REGISTRY which aren’t used. When I was working at IGEFI we only introduced XMLDB to Multifonds after customers requested the ability to load XML files, prior to the move from 9i to 10g ( in 2006 that was). I still don’t understand why you’d want to prefer XML input files rather than CSV for external tables but that wasn’t my call either.
By the way, the old backward exp command doesn’t have this restriction!
[oracle@node1 ~] $ exp file=tts.dmp log=tts.log TRANSPORT_TABLESPACE=y TABLESPACES=TTS_TEST Export: Release 11.2.0.2.0 - Production on Tue May 10 16:20:56 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Tes Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses AL32UTF8 character set (possible charset conversion) Note: table data (rows) will not be exported About to export transportable tablespace metadata... For tablespace TTS_TEST ... . exporting cluster definitions . exporting table definitions . . exporting table TEST . exporting referential integrity constraints . exporting triggers . end transportable tablespace metadata export Export terminated successfully without warnings.
Not a problem then, but quite interesting.
This entry was posted on May 10, 2011 at 15:49 and is filed under 11g Release 2. Tagged: "Q"."TABOBJ_NUM", KUPW$WORKER.UNLOAD_METADATA, tts, [PLUGTS_BLK]. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
venkat said
You can validate by giving
execute dbms_registry.valid(‘XDB’);