Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2002
    Posts
    5

    Red face Unanswered: data import problems

    hi..

    i'm try to sync up a development oracle database (which already exists... and i added about 2G of extra data files to this dev db before import) w/ a production database. the file systems on the two macine are very different in structure. they are both running 8.1.5 on Linux. i ran the following import command..

    imp system/pass@askitsys full=y ignore=y commit=y \
    buffer=1000000 \
    file=/usr/expaned-prod-export/batgirl_db_exp_2002_09_10_23_00.dmp \
    log=first.log

    and received some interesting messages:

    1 & 2)

    Export file created by EXPORT:V08.01.05 via conventional path

    Warning: the objects were exported by ASKITDBA, not by you

    import done in US7ASCII character set and WE8ISO8859P1 NCHAR character set
    import server uses WE8ISO8859P1 character set (possible charset conversion)
    IMP-00017: following statement failed with ORACLE error 1119:
    "CREATE TABLESPACE "OEM_REPOSITORY" DATAFILE '/home/oracle8i//u01/app/oracl"
    "e/oradata/batgirl/oemrep01.dbf' SIZE 5242880 AUTOEXTEND ON NEXT 26214"
    "400 MAXSIZE 83886080 DEFAULT STORAGE(INITIAL 131072 NEXT 131072 MINEXTEN"
    "TS 1 MAXEXTENTS 2147483645 PCTINCREASE 0) ONLINE PERMANENT "
    IMP-00003: ORACLE error 1119 encountered

    3) this error i feel is the worst.. as i was hoping ignore would just ignore the rest of the issues...

    IMP-00019: row rejected due to ORACLE error 1
    IMP-00003: ORACLE error 1 encountered
    ORA-00001: unique constraint (ASKIT_OWNER.XPKASKIT_EVENT) violated
    Column 1 7
    Column 2 Submit Info Prompted
    Column 3 n/a
    Column 4 1 0 rows imported
    . . importing table "ASKIT_LOG"
    IMP-00058: ORACLE error 904 encountered
    ORA-00904: invalid column name
    . . importing table "ASKIT_LOG_ARCHIVE"q



    the IMP-00058 didn't get written to the log file... so i believe the process stopped here.

    so where should i go from here. do i need to run this script as the user i exported the db as? should i remove the old data some how (and how??).

    i hate being clueless about some of the finer points of database.. but i've kind of just had this problem thrown at me.

    thanks in advance

    mike

  2. #2
    Join Date
    Dec 2002
    Posts
    5

    Re: data import problems

    well this morning i awoke and found the following in my log file...

    Column 18 1
    Column 19
    Column 20 17-OCT-2001:17:36:07
    Column 21 0
    Column 22
    Column 23
    Column 24 ADJUST NUMB STANDARD DEVIATION US BOLLING BAND
    IMP-00009: abnormal end of export file
    IMP-00018: partial import of previous table completed: 78713 rows imported
    Import terminated successfully with warnings.


    so did this work? should i be on the lookout for missing data?

    thanks again..

    mike

  3. #3
    Join Date
    Dec 2002
    Posts
    5

    Re: data import problems

    i'm now running the following on the import file:

    > imp system/passwd@askitsys full=y \
    indexfile=second-fteset.sql \
    buffer=10000000 log=second.log \
    data=/usr/expaned-prod-export/batgirl_db_exp_2002_09_10_23_00.dmp

    to test the integrity of the data file....

    well this doesn't look good:


    . . skipping table "INCOMING_QUESTION_QUEUE"

    . . skipping table "ITEM"
    IMP-00009: abnormal end of export file
    Import terminated successfully with warnings.


    i have another data file to try to import.. i might now perform this integrity test on this other file...

    mike

  4. #4
    Join Date
    Sep 2002
    Location
    ITALY
    Posts
    53

    Re: data import problems

    Hi,
    it's likely that you have more than a issue.

    First of all, you have some NLS problem because you are importing with a wrong charset (wich NLS_LANG was used during the import ?) and so all the high codes (8th bit not used by us7ascii) may be converted, check the data loaded by imp to verify they are as you expect them.

    secondly:
    ORA-1119 sounds like "no space left on device"

    third:
    ORA-00001 is a PK violation, and means that you have restarted the import without cleaning up the target database, or that the PK is not enabled in the source database

    fourth
    ORA-00904 can be a problem with the catalogs of your target database, but the information you have given us is too much incomplete for us to diagnose comfortably the issue you have.

    Hoping that this stuff can help you: good luck.



    [QUOTE][SIZE=1]Originally posted by hanulec
    hi..

    import done in US7ASCII character set and WE8ISO8859P1 NCHAR character set
    import server uses WE8ISO8859P1 character set (possible charset conversion)

    IMP-00017: following statement failed with ORACLE error 1119:
    "CREATE TABLESPACE "OEM_REPOSITORY" DATAFILE '/home/oracle8i//u01/app/oracl"
    "e/oradata/batgirl/oemrep01.dbf' SIZE 5242880 AUTOEXTEND ON NEXT 26214"
    "400 MAXSIZE 83886080 DEFAULT STORAGE(INITIAL 131072 NEXT 131072 MINEXTEN"
    "TS 1 MAXEXTENTS 2147483645 PCTINCREASE 0) ONLINE PERMANENT "
    IMP-00003: ORACLE error 1119 encountered

    3) this error i feel is the worst.. as i was hoping ignore would just ignore the rest of the issues...

    IMP-00019: row rejected due to ORACLE error 1
    IMP-00003: ORACLE error 1 encountered
    ORA-00001: unique constraint (ASKIT_OWNER.XPKASKIT_EVENT) violated
    Column 1 7
    Column 2 Submit Info Prompted
    Column 3 n/a
    Column 4 1 0 rows imported
    . . importing table "ASKIT_LOG"
    IMP-00058: ORACLE error 904 encountered
    ORA-00904: invalid column name
    . . importing table "ASKIT_LOG_ARCHIVE"q
    Franco Ceotto
    SIPTI srl
    OnSite Services
    9i OCP DBA, Performance Engineer

  5. #5
    Join Date
    Dec 2002
    Posts
    5
    thanks for the reply frank. i found that this export is basically garbage as the export exceeded the 2G file limit. right now i'm testing the import integrity of a multipart export.

    as for the other things you noted..

    1. not sure about the NLS_LANG issue. once i get anything into the db i'll check that out

    2. "ORA-1119 sounds like "no space left on device" - the import was trying to write to a partition it doesn't have the ability to. how do i resolve this? i was hoping the import would use the data files already defined in the current db.. i guess i'm wrong.

    3. not sure the best way to clean up the target database

  6. #6
    Join Date
    Dec 2002
    Posts
    5
    my multipart import test finished but w/ warnings at only the start...

    imp system/passwd@SID full=y \ indexfile=ftest-onebyone.sql \
    buffer=10000000 \
    file=PIPE \
    log=onebyone.log

    where I had to keep on specifying new named PIPEs as a gunzip of all of my four 1G db exports didn't work correctly.

    and the output was...

    Connected to: Oracle8i Enterprise Edition Release 8.1.5.0.2 - Production
    With the Partitioning and Java options
    PL/SQL Release 8.1.5.0.0 - Production

    Export file created by EXPORT:V08.01.05 via conventional path

    Warning: the objects were exported by ASKITDBA, not by you

    import done in US7ASCII character set and WE8ISO8859P1 NCHAR character set
    import server uses WE8ISO8859P1 character set (possible charset conversion)
    export client uses WE8ISO8859P1 character set (possible charset conversion)
    IMP-00046: using FILESIZE value from export file of 1073741824
    IMP-00021: operating system error - error code (dec 2, hex 0x2)
    . . skipping table "DEF$_AQCALL"

    . . skipping table "DEF$_AQERROR"

    . . skipping table "DEF$_CALLDEST"

    ............

    . . skipping table "TRACKING_CODE"

    Import terminated successfully with warnings.


    i imagine the warnings are due to the gunzip sent thru the pipe.


    my remaining questions now are:

    a. is my previous 2G file system limited backup retreiveable at all. can i get any data out of it

    b. what should be the steps to shoe-horn this multipart import into an existing database on another server which doesn't have the same filesystem layout??

  7. #7
    Join Date
    Sep 2002
    Location
    ITALY
    Posts
    53
    Originally posted by hanulec
    thanks for the reply frank. i found that this export is basically garbage as the export exceeded the 2G file limit. right now i'm testing the import integrity of a multipart export.

    as for the other things you noted..

    1. not sure about the NLS_LANG issue. once i get anything into the db i'll check that out

    2. "ORA-1119 sounds like "no space left on device" - the import was trying to write to a partition it doesn't have the ability to. how do i resolve this? i was hoping the import would use the data files already defined in the current db.. i guess i'm wrong.

    3. not sure the best way to clean up the target database
    No way out if image bigger than 2G.
    But ... try this:

    rm -f /tmp/exp.p
    mknod /tmp/exp.p p
    compress < /tmp/exp.p > myexpfile.dmp.Z &
    exp system/xxx file=/tmp/exp.p ... &


    importing as :

    rm -f /tmp/imp.p
    mknod /tmp/imp.p p
    uncompress < myexpfile.dmp.Z >/tmp/imp.p &
    imp system/xxx file=/tmp/imp.p ..... &


    good luck
    Franco Ceotto
    SIPTI srl
    OnSite Services
    9i OCP DBA, Performance Engineer

  8. #8
    Join Date
    Sep 2002
    Location
    ITALY
    Posts
    53
    a. is my previous 2G file system limited backup retreiveable at all. can i get any data out of it

    b. what should be the steps to shoe-horn this multipart import into an existing database on another server which doesn't have the same filesystem layout??


    the big question: Can you retake the export ??

    gzip is not the right tool,
    compress steam-oriented works fine (i tested it in 100s Gb exp/imp).


    you can import to the remote db from the unix-one, trying:

    imp a/b@remote-tns file=/tmp/pipe ...
    Franco Ceotto
    SIPTI srl
    OnSite Services
    9i OCP DBA, Performance Engineer

Posting Permissions

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