Thursday, February 3, 2011

TSPITR Fails with ORA-29308: view TS_PITR_CHECK failure

Trying to do automated tablespace point in time recovery(TSPITR) containing an IOT with a LOB column and fails to the Export Face of the recovered tablespace with the following :

Export: Release - Production on Thu Feb 3 15:18:34 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Export done in US7ASCII character set and UTF8 NCHAR character set
server uses UTF8 character set (possible charset conversion)
Note: table data (rows) will not be exported

About to export Tablespace Point-in-time Recovery objects...
EXP-00008: ORACLE error 29308 encountered
ORA-29308: view TS_PITR_CHECK failure
ORA-06512: at "SYS.DBMS_PITR", line 889
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully
host command complete

Removing automatic instance
shutting down automatic instance
Oracle instance shut down
Automatic instance removed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/03/2011 15:18:38
RMAN-03015: error occurred in stored script Memory Script
RMAN-06135: error executing host command: Additional information: 256
Additional information: 11

Bug 6620517 ORA-29308 from TSPITR with IOT with LOB column
Fixed in

Bug 6620517 is not only to IOT's with LOB's but more issue with IOT's and TSPITR.

This an except from an Oracle SR i opened :

So the option for and, when IOT's are involved, is to do the Tablespace Point In Time Recovery manually.

- Transportable Tablespace export of the related tablespace on the duplicate database
- Transportable Tablespace import of the related tablespace into the source database.


Indeed Bug 6620517 is not resolving the 'IOT with NO LOB 'issue in
That is fixed in 11g.

And you can use the same workaround for Bug:7016765 as here:
RMAN Tablespace Point-in-Time Recovery and ORA-19602

The bug case can be cheked with the following procedure:

1) the recovery-set of tablespaces (eg 'MYTBS') fully contains all the storage objects of an Index-Organized Table (IOT) with a LOB column.
owner||'.'||segment_name "OWNER.OBJECT"
tablespace_name = 'MYTBS'
order by

2) Query the TS_PITR_CHECK view shows the IOT-with-LOB consists of storage objects in the recovery set of tablespaces and the IOT also has a storage object in the SYSTEM tablespace, for example if the following query returns at least one row:

select * from
where ( ts1_name in ('MYTBS')
and ts2_name not in ('MYTBS') )
or ( ts1_name not in ('MYTBS')
and ts2_name in ('MYTBS') );

with one object is reported as being a TABLE in the SYSTEM tablespace and the other object is reported as being an INDEX in one of the tablespaces in the recovery-set and the reason is: "Tables and associated indexes not fully contained in the recovery set"

3) Assure that the table is an IOT:
select iot_type from dba_tables where table_name='[Paste the table_name from the above query]';
It must return IOT
Verify the INDEX is a LOB index:
select index_type from dba_indexes where index_name='Paste the table_name from the above query';
It must return LOB
The (bogus) violation reported above is not reported by the following method (correctly):
execute dbms_tts.transport_set_check('MYTBS',TRUE,TRUE);
select * from transport_set_violations;

This method is also (also) used to check if a set of tablespaces are fully contained and for that reason you can export the recovered tablespace from the auxiliary instance as the workround mentioned previous in the article.

No comments:

Post a Comment