Re: import from 8i to 9i /IMP-00003:ORA-02298

Can u check in source database if the constraint is set to enable novalidate . It might have some data integrity problem in old data .

-ak

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, July 08, 2003 2:09 PM

> Yes, I am sure. Just to make sure, I have compared the counts from export
> log file and import log file. Also I compared it with import log file I
did
> in to 8i database.
>
> What I did with two pass import is..first I have imported with
constarints=n
> then I imported with ROWS=N & CONSTARINTS=Y. So I am not doing any
> truncates.
>
> And to make sure again... I have tried troubleshooting one of the failed
> constraints with smallest table:
>
> Table TWESVS is having FK, FK_OF_VS_SYSID on column TWESVS.VS_OFF_SYSID
> referring to TWESOF.OF_OFF_SYSID.
>
> 1) The Error:
>
> IMP-00003: ORACLE error 2298 encountered
> ORA-02298: cannot validate (CMS.FK_OF_VS_SYSID) - parent keys not found
>
> 2) The constraint definition:
>
> ALTER TABLE CMS.TWESVS ADD CONSTRAINT FK_OF_VS_SYSID
> FOREIGN KEY (VS_OFF_SYSID)
> REFERENCES CMS.TWESOF (OF_OFF_SYSID) ON DELETE CASCADE;
>
> 3) The user_constarints entry
> SQL> select constraint_name, status from user_constraints
> where constraint_name='FK_OF_VS_SYSID';
>
> CONSTRAINT_NAME STATUS
> ------------------------------ --------
> FK_OF_VS_SYSID ENABLED
>
> 4) And I tried disabling and then enabling manually, it gave ORA-02298
> error.
> Also I tried to catch the bad data into excetions.
> The data in TWESVS.VS_OFF_SYSID ranges from values 1 to 107 (with some
> values missing)
> and the data in TWESOF.OF_OFF_SYSID ranges from 1 to 109.
>
> And the following query returned no rows.
>
> Please help me what could be the reason.
>
> Thanks,
> Surendra
>
> -----Original Message-----
> Sent: Tuesday, July 08, 2003 3:54 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Are your sure that the parent table data is getting imported fully? Number
> of exported rows match
> the number of imported rows?
> During your subsequent import attempts, are you truncating the table?
>
> - Kirti
>
> --- [EMAIL PROTECTED] wrote:
> > I tried importing data first then enabling constraints, same problem.
> > I have disabled(it is not enabled to begin with but reporting 'ENABLED'
in
> > USER_CONSTRAINTS) one of those constraints and tried to enable it
> manually.
> > Same error.
> > And I have checked the data again. It is having parent keys.
> >
> > Thanks,
> > Surendra
> >
> > -----Original Message-----
> > [mailto:[EMAIL PROTECTED]
> > Sent: Tuesday, July 08, 2003 2:29 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > Hi Kirti,
> >
> > As I have mentioned, my data is clean. I could import this data into an
> > existing 8i database.
> > Do you still think that importing data in two passes would fix it?
> > Anything changed form 8i to 9i regarding iport process?
> >
> > I tried catching the invalid data into excetions and did cross checking
> and
> > observed that
> > I have valid data. I mean to say I could not enable those failed
> constarints
> > manually as well.
> > Surprisingly my STATUS column in USER_CONSTARINTS tables reads 'ENABLED'
> for
> > those failed constarints also.
> >
> > Am I missing something?
> >
> > Thanks,
> > Surendra
> >
> > -----Original Message-----
> > Sent: Tuesday, July 08, 2003 12:44 PM
> > To: [EMAIL PROTECTED]
> > Cc: [EMAIL PROTECTED]
> >
> >
> > I don't think this is related to NLS stuff...
> >
> > 02298, 00000,"cannot validate (%s.%s) - parent keys not found"
> > // *Cause: an alter table validating constraint failed because the table
> has
> > // orphaned child records.
> > // *Action: Obvious
> >
> > I suggest cleaning up target database (drop target tables, indexes,
etc).
> > Re-Create all target tables (empty).
> > Do not enable constraints (disable them if already enabled).
> >
> > Re-import with ignore=y, and then enable constraints.
> >
> > Oracle imports tables in table_name order for the schema. Enabled RI
> > constraints can pose a
> > problem.
> >
> > - Kirti
> >
> > --- [EMAIL PROTECTED] wrote:
> > > Hello All,
> > >
> > > We are working on migrating 8i databases to 9i. As Sun boxes are not
> > > available yet, I have decided to do some experiments on my PC. I am
> trying
> > > to import into 9i database using export dump file of 8i database. I
have
> > my
> > > 8i database created in US7ASCII and I have created 9i database also in
> > > US7ASCII with UTF8, just to make sure that I won't hit any conversion
> > > issues.
> > > When I tried to import the data into this newly created database it is
> > > giving me some strange "ORA-02298: cannot validate
(CMS.FK_OF_VS_SYSID)
> -
> > > parent keys not found" errors.
> > > I have verified that the data in 8i database is valid.
> > >
> > > I have successfully imported into an existing 8i database with no
> problem.
> > I
> > > am wondering if the Character set conversion the import utility is
doing
> > > making any difference.
> > >
> > > The following are the messages I am getting when I invoked import:
> > >
> > > ==============
> > > import done in WE8MSWIN1252 character set and UTF8 NCHAR character set
> > > import server uses US7ASCII character set (possible charset
conversion)
> > > export client uses US7ASCII character set (possible charset
conversion)
> > > export server uses US7ASCII NCHAR character set (possible ncharset
> > > conversion)
> > > ==============
> > >
> > >
> > > I have gone through the globalization support guide and concluded that
I
> > > should not worry about charctersets if I am not using any special
> > datatypes
> > > or NCHAR datatypes.
> > >
> > > Please advise me how to resolve this issue. Is character set any issue
> at
> > > all?
> > > Do I have to influence the import utility to use the characterset of
my
> > > interest?
> > > I don't want to do this as I will have to do this each time I import.
> > >
> > > I am wondering if any bug is associated with this.
> > > Thanks for your help.
> > >
> > > Surendra
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > --
> > > Author:
> > > INET: [EMAIL PROTECTED]
> > >
>
>
> __________________________________
> Do you Yahoo!?
> SBC Yahoo! DSL - Now only $29.95 per month!
> http://sbc.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Kirtikumar Deshpande
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author:
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: AK
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).