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
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 220.127.116.11 it was the first TSPITR demo on 18.104.22.168. 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:
After a while RMAN stop’s with the following error:
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 22.214.171.124. Metalink Note 1273450.1 mention a bug (10185688) introduced with 126.96.36.199 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.
Oracle Workspace Manager
Oracle DATABASE Catalog Views
Oracle DATABASE Packages AND Types
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 188.8.131.52 and Oracle 12.1.
References and links
A few links mentioned and used in this post: