Slight 11.2.0.2 caveat with TTS and missing XMLDB

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.

Advertisement