Re: Index missing after exp with TRANSPORT_TABLESPACE=Y

Comments embedded.
steven_nospam at Yahoo! Canada wrote:
> RDBMS: Oracle 9i Release 9.2.0.6.0
> O/S: AIX 5.2 (64-bit)
>
> PROBLEM:
> Certain indexes/primary keys go missing after an exp/imp using
> transportable tablespace. They still exist, but a SELECT command cannot
> locate them and the index is not used for any reports that are
> generated which use that table.
>

Interesting. With 9.2.0.6 on Solaris and using transportable tablespaces I have never experienced such a problem.

> BACKGROUND:
> We have a monthly procedure that exports the database contents from a
> production server and then imports that data into a historical database
> environment so we can perform monthly sales reporting and analysis
> without impacting the production server.

This must involve large data volumes to make this a regular operation, especially since using transportable tablespaces requires the source tablespaces to be placed in read-only mode.

>
> Twice in the last 5-6 data transfers, we have run into a problem where
> the performance on some of the reports was extremely poor. Reports that
> took seconds or minutes to complete would suddenly be taking hours.
>

You've checked the import logs for errors, I presume.

> An analysis showed that one particular table (APTPYH) was supposed to
> have an unnamed primary key (SYS_C00139087) and two unique indexes
> (PYH_KEY1, PYH_KEY2). In the live environment, these keys existed fine.
> However, after the export and import using TRANSPORT_TABLESPACE=Y, the
> primary key and the first index appeared to have been missing when you
> issue a SELECT statement, as below:
>
> SQL> select index_name from all_indexes where table_name='APTPYH';
>
> INDEX_NAME
> ------------------------------
> PYH_KEY2
>
> When we issue a SQL command to recreate the missing indexes, we get one
> of these messages depending on if it was the primary key or the index
> we are trying to create:
>
> 1) ORA-02260: table can have only one primary key
> 2) ORA-00955: name is already used by an existing object
>

Again, you have checked the import logs for errors?

> What we end up having to do is drop the indexes first (and any
> constraints), then recreate anything that was deleted so that it shows
> properly. Once they are recreated, we see the following:
>
> SQL> select index_name from all_indexes where table_name='APTPYH';
>
> INDEX_NAME
> ------------------------------
> SYS_C00139087
> PYH_KEY1
> PYH_KEY2
>
> In both cases where the indexes went missing, it was the same table
> that was affected. In one case, both primary key and index PYH_KEY1 was
> missing. In the second occurrence, only the primary key was missing.
>

And it's only this one table affected? I'll repeat this: check the import logs. Since the metadata is the only data imported for transportable tablespaces I expect you have an error you've missed on these occasions when this problem arises.

> I will be reporting this to Oracle on Metalink shortly, but their site
> appeared to be down this morning, so figured I'd post it here to see if
> anyone else has run into this same scenario. Would like to know
> anyone's thoughts on this.
>
> Thx.
>
> Steve

David Fitzjarrell Received on Tue Oct 10 2006 - 10:09:39 CDT