I was quite busy in the past week with customer projects, lecturing backup & recovery lessons and enjoying the summer holidays. Therefore is also quite a lot of time passed since my last blog post. The days are getting shorter and so more time remains for a couple of posts 🙂

Tablespace point in time revocery

Tablespace point in time recovery is used when the data within a tablespace is screwed up and needs to be restored in a point back in time. Rather than restoring the complete database just a part of it is restored. This database part is called auxiliary set and must contain the SYSTEM, SYSAUX, UNDO and affected tablespaces. As soon as the auxiliary set has been restored / recoverd the data can be moved back to the target database. The following image shows the schematic flow.

TSPITR can be executed with the following three options:

• Full automatic TSPITR: RMAN cares about “almost” everything
• Customized TSPITR with automatic auxiliary instance: A bit something between full automatic and manual 🙂
• Manual TSPITR: Still using RMAN but you have to build your own auxiliary database and care about the data move

The issue

In one of my demos, I show a full automatic TSPITR. So far this demo went smoothly and easily through. Since the test database recently has been updated to 11.2.0.2 it was the first TSPITR demo on 11.2.0.2. In the demo I just create a table in the tablespace DATA01, fill the table with data, update the table with some wrong values and start the TSPITR. It is unnecessary to mention that the DB is running in archivelog mode and associated backups are available. For TSPITR I run the following RMAN command:

recover tablespace DATA01 until sequence=10 auxiliary destination '/u00/app/oracle/admin/TDB01/aux';

After a while RMAN stop’s with the following error:

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

Looking a bit deeper into the RMAN output show’s that the error arise, when RMAN start’s to export the tablespace DATA01. My research in MOS yielded a expdp problem in 11.2.0.2. Metalink Note 1273450.1 mention a bug (10185688) introduced with 11.2.0.2 when exporting transportable tablespaces in databases without XDB installed. My test database for RMAN demo’s is a tiny small database with only the most essential components installed.

select comp_name "Component" from dba_registry;

Component
--------------------------------------------------------------------------------
Oracle Workspace Manager
Oracle Database Catalog Views
Oracle Database Packages and Types

Solution

At the end it the issue is not really an RMAN TSPITR issue but more an expdp problem. In my case the solution was fairly easy. I’ve just installed XDB and re-run the demo again. Alternatively there exists a generic patch and plans for fixing this in 11.2.0.3 and Oracle 12.1.

References and links

A few links mentioned and used in this post:

• Metalink Note: 1273450.1 Data Pump TTS Export Fails With ORA-39126 & ORA-904
• Bug: 10185688 TTS export using EXPDP fails if XDB not installed
• Metalink Note: 1292089.1 Master Note for Oracle XML Database (XDB) Install / Deinstall