Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: Import

  1. #1
    Join Date
    Sep 2003
    Posts
    218

    Unanswered: Import

    I am using Oracle 9.x.x on Win2k.
    I have a full export oracle db dump (80 GB), & I try to bring in the full import into a existing db.
    There is only one schema owner and it is for this user, I am trying to import data. I'm getting a lot of ORA-2291 parent key not found errors.

    I tried using rows=y constraints=n indexes=n in a parameter file but i still get ORA-2291 errors on the screen. Besides, it nearly takes two days to complete a 80 GB db dump import.

    What's the best way of successfully doing this?

    Many thanks.

  2. #2
    Join Date
    Jan 2004
    Posts
    370
    You must have constraints on the existing tables.

  3. #3
    Join Date
    Nov 2002
    Posts
    833
    imp is doning loading tables in alphabetical order, but to me it sounds you are going to imp into a schema which is not empty ...

    assign a redo/undo segment as large as you can
    at least use a buffer of at least 16000000 and separate loading data from index

    use commit=n

  4. #4
    Join Date
    Sep 2003
    Posts
    218
    Thanks... to you both for quick response.

    Firstly, If constraints are enabled then "constraints=n" is supposed to take care of it right?

    Secondly, if import is happening alphabetically - then do I have any control on the list of tables to be imported?

  5. #5
    Join Date
    Nov 2002
    Posts
    833
    Originally posted by dr_suresh20
    Thanks... to you both for quick response.

    Firstly, If constraints are enabled then "constraints=n" is supposed to take care of it right?

    Secondly, if import is happening alphabetically - then do I have any control on the list of tables to be imported?
    no normally - and that is my experience - the constraints are loaded at the end of the import process and file
    then all constaints are being enabled ...

    you can only control the list of tables if you work with the parfile parameter

    ensure that the schema to load is empty that no problems should occur

  6. #6
    Join Date
    Jan 2004
    Posts
    370
    Firstly, If constraints are enabled then "constraints=n" is supposed to take care of it right?

    constraints=n means that no constraints will be imported.

    Any enabled constraints on existing tables remain enabled.

  7. #7
    Join Date
    Sep 2003
    Posts
    218
    that is exactly my point is:

    If I have given "constraints=n" in the first place meaning no constraints should get imported in the first place then there is no question of constraints getting enabled right?

    I am still wondering as to how these constraints got thro' in the schema?

  8. #8
    Join Date
    Jan 2004
    Posts
    370
    ensure that the schema to load is empty that no problems should occur

  9. #9
    Join Date
    Sep 2003
    Posts
    218
    Our import process is as follows:

    - There is a schema definition file which contains all objects definition with constraints, indexes, stats etc.
    - There is data file dump file1 for all fact tables minus one big fact table
    - There is another data file dump file2 for this single fact table alone.

    So, how do I proceed further?

  10. #10
    Join Date
    Jan 2004
    Posts
    370
    Check in user_objects and user_constraints for any existing objects/constraints which affect your imports.

    If no objects or constraints exist, import the schema with constraints=n
    There should be no constraints imported - check user_constraints

    Then import your tables.

    As a matter of interest, do you see these errors occur during the import of your first or second dump file?

  11. #11
    Join Date
    Jul 2003
    Posts
    2,296
    It sounds like objects already exist where you are importing.

    If no obejcts exist, then import with constraints=N, indexes=N, rows=Y
    Sometimes unique indexes will give you similar errors as constraints would.

    If objects already exists then:
    First, disable all constraints in your target schema:
    This can be done by logging in as the owner and running:
    PHP Code:
    set echo off
    set heading off
    set feedback off
    set linesize 130

    prompt
    prompt Alter All Foreign Keys Disabled 
    for Connected User
    prompt

    set term off
    spool fk_off
    .tmp

    select 
    'set echo on' from dual;
    select 'set feedback on' from dual;

    select 'alter table '||owner||'.'||table_name||' disable constraint '||constraint_name||';'
      
    from user_constraints
      where constraint_type 
    'R'
      
    and status 'ENABLED';

    spool off
    set term on

    @fk_off.tmp

    quit 
    then you import with ignore=Y constraints=N indexes=N

    once all the data is in you can enable constraints, import indexes-constraints whatever.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  12. #12
    Join Date
    Sep 2003
    Posts
    218
    Thank you all for your response.

    Now, the import process is failing in last step i.e while importing data for a fact table.

    IMP-00058: ORACLE error 12571 encountered
    ORA-12571: TNSacket writer failure
    IMP-00058: ORACLE error 3114 encountered
    ORA-03114: not connected to ORACLE
    IMP-00000: Import terminated unsuccessfully

    Any ideas?

  13. #13
    Join Date
    Nov 2002
    Posts
    833
    Originally posted by dr_suresh20
    Thank you all for your response.

    Now, the import process is failing in last step i.e while importing data for a fact table.

    IMP-00058: ORACLE error 12571 encountered
    ORA-12571: TNSacket writer failure
    IMP-00058: ORACLE error 3114 encountered
    ORA-03114: not connected to ORACLE
    IMP-00000: Import terminated unsuccessfully

    Any ideas?
    what is your alert log showing; has someone shut down either listener or instance or both?

  14. #14
    Join Date
    Sep 2003
    Posts
    218
    Trace of Alert Log file:
    KCF: write/open error block=0xff465 online=1
    file=6 J:\ORACLE\ORADATA\DB1\USERS01.DBF
    error=27069 txt: 'OSD-04026: Invalid parameter passed. (OS 1045605)'
    Thu Mar 18 01:53:38 2004
    Errors in file j:\oracle\admin\db1\bdump\db1_dbw0_3352.trc:
    ORA-01242: data file suffered media failure: database in NOARCHIVELOG mode
    ORA-01114: IO error writing block to file 6 (block # 1045605)
    ORA-01110: data file 6: 'J:\ORACLE\ORADATA\db1\USERS01.DBF'
    ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
    OSD-04026: Invalid parameter passed. (OS 1045605)

    Trace of .trc file:
    *** 2004-03-18 01:53:38.000
    *** SESSION ID2.1) 2004-03-18 01:53:38.000
    KCF: write/open error block=0xff465 online=1
    file=6 J:\ORACLE\ORADATA\db1\USERS01.DBF
    error=27069 txt: 'OSD-04026: Invalid parameter passed. (OS 1045605)'
    error 1242 detected in background process
    ORA-01242: data file suffered media failure: database in NOARCHIVELOG mode
    ORA-01114: IO error writing block to file 6 (block # 1045605)
    ORA-01110: data file 6: 'J:\ORACLE\ORADATA\db1\USERS01.DBF'
    ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
    OSD-04026: Invalid parameter passed. (OS 1045605)

  15. #15
    Join Date
    Jan 2004
    Posts
    370
    ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file

    This error is bad news - Oracle thinks the file is larger than it is.
    Check the bytes field in v$datafile and compare it with the actual file size on the disk.

    It could be corruption in the data dictionary, or it could be something has happened to the datafile.

    I think you'll need to rebuild the tablespace at the very least, but unless you know that something has happened to the datafile I wouldn't have much confidence in this database.

    You should really contact Oracle Support.

Posting Permissions

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