DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: exp 8i imp 9i import fails when export was successful

  1. #1
    Join Date
    Aug 2005
    Posts
    69

    exp 8i imp 9i import fails when export was successful

    hi,
    ran an export from 8i db, all worked ok terminated successfully without warnings. Dump file si about the right size as expected.

    However, when trying to import it I got the following snippet ...

    . . importing table "TMP_SELECTION_RESTRICTS" 2 rows imported
    . . importing table "TMP_SELECT_RESTRICTS_HISTORY" 68 rows imported
    . . importing table "TRANREC_REP" 0 rows imported
    . . importing table "TRANSACTIONS"
    IMP-00009: abnormal end of export file
    IMP-00028: partial import of previous table rolled back: 8386018 rows rolled back
    IMP-00034: Warning: FromUser "DTITRANS" not found in export file
    IMP-00034: Warning: FromUser "FQV" not found in export file
    IMP-00034: Warning: FromUser "TAYO1" not found in export file
    Import terminated successfully with warnings.


    It seems to have failed on the TRANSACTIONS table (which has 14 million rows exported) and then skipped the rest on the imports for that user and gone on to the next.


    This process normally works fine, so am not sure what to do about it.
    What can I do if the exprot is so say ok, but the import reckons the export has abnormal termination?

  2. #2
    Join Date
    Sep 2005
    Location
    Delhi
    Posts
    78
    Quote Originally Posted by davo
    hi,
    ran an export from 8i db, all worked ok terminated successfully without warnings. Dump file si about the right size as expected.

    However, when trying to import it I got the following snippet ...

    . . importing table "TMP_SELECTION_RESTRICTS" 2 rows imported
    . . importing table "TMP_SELECT_RESTRICTS_HISTORY" 68 rows imported
    . . importing table "TRANREC_REP" 0 rows imported
    . . importing table "TRANSACTIONS"
    IMP-00009: abnormal end of export file
    IMP-00028: partial import of previous table rolled back: 8386018 rows rolled back
    IMP-00034: Warning: FromUser "DTITRANS" not found in export file
    IMP-00034: Warning: FromUser "FQV" not found in export file
    IMP-00034: Warning: FromUser "TAYO1" not found in export file
    Import terminated successfully with warnings.


    It seems to have failed on the TRANSACTIONS table (which has 14 million rows exported) and then skipped the rest on the imports for that user and gone on to the next.


    This process normally works fine, so am not sure what to do about it.
    What can I do if the exprot is so say ok, but the import reckons the export has abnormal termination?
    can u write exp and imp command here...

  3. #3
    Join Date
    Aug 2005
    Posts
    69
    hi,
    noticed that the import seems to do FSC user then HOUSING then FSC again?

    Although the table it fails on is definitely whilst importing into HOUSING

    export server uses US7ASCII NCHAR character set (possible ncharset conversion)
    . importing SHA1's objects into SHA1
    . importing FSC's objects into FSC
    . importing HOUSING's objects into HOUSING
    . importing FSC's objects into FSC

    Here is the beginning of the export log file.......

    Connected to: Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
    JServer Release 8.1.7.2.0 - Production
    Export done in US7ASCII character set and US7ASCII NCHAR character set
    Note: grants on tables/views/sequences/roles will not be exported

    About to export the entire database ...
    . exporting tablespace definitions
    . exporting profiles
    . exporting user definitions
    . exporting roles
    . exporting resource costs
    . exporting rollback segment definitions
    . exporting database links
    . exporting sequence numbers
    . exporting directory aliases
    . exporting context namespaces
    . exporting foreign function library names
    . exporting object type definitions
    . exporting system procedural objects and actions
    . exporting pre-schema procedural objects and actions
    . exporting cluster definitions
    . about to export SYSTEM's tables via Conventional Path ...
    . . exporting table AQ$_QUEUE_UPGRADE_TMP 4 rows



    here is the end of it ... (cant get full output in as too large for the mail)

    ...
    . about to export SAMWH's tables via Conventional Path ...
    . exporting referential integrity constraints
    . exporting synonyms
    . exporting views
    . exporting stored procedures
    . exporting operators
    . exporting indextypes
    . exporting bitmap, functional and extensible indexes
    . exporting posttables actions
    . exporting triggers
    . exporting snapshots
    . exporting snapshot logs
    . exporting job queues
    . exporting refresh groups and children
    . exporting dimensions
    . exporting post-schema procedural objects and actions
    . exporting user history table
    . exporting default and system auditing options
    . exporting statistics
    Export terminated successfully without warnings.





    Here is the import dos batch file ....

    @ECHO ON
    REM~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    REM import database
    REM~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    for /F "tokens=1-3 delims=/" %%i in ('date/t') do (
    set Day=%%i
    set Month=%%j
    set Year=%%k
    set Date=%%k%%j%%i
    )

    SET LOGPATH=I:\dimexport\actionlogs
    SET LOGFILE=%LOGPATH%\%Day%%Month%imphoulive.log

    FOR /F "tokens=1" %%i in ('DATE /T') DO SET VDATE=%%i
    FOR /F "tokens=1" %%i in ('TIME /T') DO SET VTIME=%%i
    echo Import Database Commenced at %VDATE% :- %VTIME% >%LOGFILE%

    set oracle_sid=HOUWHSE
    imp omsjobs/**** parfile=I:\dimexport\scripts\houfulimp.dat

    FOR /F "tokens=1" %%i in ('DATE /T') DO SET VDATE=%%i
    FOR /F "tokens=1" %%i in ('TIME /T') DO SET VTIME=%%i
    echo Import Database Completed at %VDATE% :- %VTIME% >>%LOGFILE%
    copy %LOGFILE% %LOGPATH%\3_imphoulive.log


    here is the content of the param file ....

    FILE=J:\houfull.dmp
    LOG=I:\DimExport\actionlogs\IMPHOUDAT.LOG
    FROMUSER=DTITRANS,FQV,FSC,HOUSING,SHA1,TAYO1
    TOUSER=DTITRANS,FQV,FSC,HOUSING,SHA1,TAYO1
    GRANTS=n
    INDEXES=n
    ROWS=y
    CONSTRAINTS=n
    IGNORE=y
    SKIP_UNUSABLE_INDEXES=y
    BUFFER=262140
    ANALYZE=n
    DESTROY=Y



    We are set up on the database for auotallocate, user has unlimited tablespace. We have loads of disk space ... dunno ?

  4. #4
    Join Date
    Apr 2003
    Location
    Gourock, Scotland
    Posts
    102
    Sorry, don't know the answer to your problem, but can't Sx3/Northgate help you?
    If I have to choose between two evils, I always like to choose the one I haven't tried yet.

  5. #5
    Join Date
    Aug 2005
    Posts
    69
    spooky!!
    how do you know it's SX3?

    are you familiar with their tables or something?

    It seems to have worked from their point of view - the export was done there, we are trying to import it and that's when it fails

  6. #6
    Join Date
    Aug 2005
    Posts
    69
    found the answer ...
    export was still running when the import started, and it seems to have overtaken it, so the tables were not all there.

    thanks anyway

  7. #7
    Join Date
    Apr 2003
    Location
    Gourock, Scotland
    Posts
    102
    We have the same software here - it was the FSC and FQV users that clinched it. Glad you found your answer.
    If I have to choose between two evils, I always like to choose the one I haven't tried yet.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width