Re: Index missing after exp with TRANSPORT_TABLESPACE=Y

fitzjarrell_at_cox.net wrote:
> > 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.
>

Our routine puts the LIVE databases in read-only, exports the data, copies the data files, then imports the data into an alternate EOM database. It is mostly used for month-end reporting, but also provides us with a monthly frozen image of the data for auditing purposes. I think the procedure was simply ported originally from an ISAM environment, and so there was no planned changes to create information warehouse setups. Data is only about 20-30gb in size.

> 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.

> David Fitzjarrell

Hi Dave,

Thx for the input. It is only one table that we are aware of being affected. I tried to find out if any other indexes had been dropped from about 1600 of them from over 600-700 tables, but could not see anything else missing.

I checked the export and the import The info below from the import was shortened to show the key points. There were some constraint errors (which sheepishly I just noticed!), but nothing related to that table.

Export file created by EXPORT:V09.02.00 via conventional path About to import transportable tablespace(s) metadata... import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing MYDATA's objects into MYDATA

(several imported tables)

. . importing table                   "APTPYH"

(several more imported tables)

IMP-00093: Inconsistency between dumpfile constraint definition for table TPTTRS with columns ("TRS_TRANSM_NO" , "TRS_TAG_CTL_NO" , "TRS_LN_NO")
IMP-00093: Inconsistency between dumpfile constraint definition for table TPTRMD with columns ("RMD_LGN_ID" , "RMD_CM_REF_NO" , "RMD_TAG_CTL_NO")
IMP-00093: Inconsistency between dumpfile constraint definition for table TPTTVC with columns ("TVC_BRH" , "TVC_T_INV_NO" , "TVC_PROCR_TAG_NO" , "TVC_TAG_CTL_NO")
IMP-00093: Inconsistency between dumpfile constraint definition for table TPTPBH with columns ("PBH_BRH" , "PBH_CUS_NO" , "PBH_TOLL_ORD_NO" , "PBH_P_TAG_CTL_NO" , "PBH_PRS_DT" , "PBH_IP_REF_NO") IMP-00093: Inconsistency between dumpfile constraint definition for table SASPSS with columns ("PSS_PRG" , "PSS_SIZE" , "PSS_GRD" , "PSS_BRH" , "PSS_SLS_TYP")
IMP-00093: Inconsistency between dumpfile constraint definition for table BYTRQD with columns ("RQD_BUY_BRH" , "RQD_REQN_NO" , "RQD_DST_BRH")
IMP-00093: Inconsistency between dumpfile constraint definition for table EMXEG2 with columns ("EG2_EGU" , "EG2_EMU") IMP-00093: Inconsistency between dumpfile constraint definition for table EMXEM2 with columns ("EM2_EMU" , "EM2_BX_TYP" , "EM2_EML_ID")

Import terminated successfully with warnings.

The alert logs for the import area did not show any unexpected errors, and there were no trc file from that time.

However, perhaps the "inconsistencies" are affecting the other tables without indicating this. As far as I can see, none of the errors listed here have constraints that point to or from the APTPYH table, but at least I have something more I can check into.

Thx,

Steve Received on Tue Oct 10 2006 - 11:12:32 CDT