Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Location
    california
    Posts
    37

    Unhappy Unanswered: Slow oracle import help !

    I am trying to recover a crashed database. The files I am importing have a few million rows each. I started the import below 12 hrs ago and I see no progress. The disks are spinning but thats about it. The screen still says the same thing it did when I launced it this morning. "Export file created by EXPORT v08 via conventional path"

    When I go into sqlplus I see no tables created. I do not know if I should kill it. There are no other users on this box and this is a new database. I have lots of disk space, about 300 gig and 512k ram. Anything you could suggest would be appriciated.

    Thanks
    Peter



    Before I ran I set :

    alter database noarchivelog
    db_block_boffers=99999
    log_buffer=163840
    shared_pool_size=5242880
    large_pool_size=61440
    log_checkpoint_interval=10000
    log_checkpoint_timeout
    processes=150
    db_block_size=8192

    My shell import script

    #!/bin/ksh
    ### THIS SCRIPT WILL DO A FULL SYSTEM import OF ALL
    ### ORACLE FILES TO DISK FILE to compressed format file.
    ###
    ### setup environment variables and other job variables
    ################################################## ####################
    signon=`cat dbapass.sql`
    file=fullexport_CLRP_05-28-01_2200
    cat /p01h/ora_exports/CLRP/${file}_*.dmp.Z | uncompress > /p01h/ora_exports/CLRP/uncompress_pipe &
    sleep 2
    $ORACLE_HOME/bin/imp fromuser=lawson touser=lawson indexes=n commit=y ignore=y analyze = n buffer=900000 tables=customer,employee,payment, file=/p01h/ora_exports/CLRP/uncompress_pipe log=/p01l/app/oracle/admin/CLRP/log/imp2_$file.log <<EOF
    $signon
    EOF

  2. #2
    Join Date
    Jan 2004
    Location
    Hyderabad, India
    Posts
    37
    Hi

    I found one extra comma after table 'PAYMENT'. I am not sure because of this its giving the problem.

    Check your use_async_io parameter in ur init.ora file. make it false and try again..
    Regards
    Suneel

  3. #3
    Join Date
    Jan 2004
    Posts
    84

    Re: Slow oracle import help !

    There is no 'slow import'.. Infact there is no import going on!!!
    Because of some syntax error it has got stuck. Try again after removing the comma as suggested. Also try the following:
    mknode skpipe1 p
    mknode skpipe2 p
    uncompress < skpipe1 > skpipe2 &
    cat exp.dmp.Z* > skpipe1 &
    sleep 5
    and in imp statement give file=skpipe2

    Pls note exp.dmp.Z* corresponds to compressed dump filename for your case.

  4. #4
    Join Date
    Sep 2003
    Location
    Columbia, MD USA
    Posts
    95
    you should also set your db_block_bUffers to something like 6000-10000 with the amount of memory you have in your machine.... 99999 makes that value higher than the physical amount of memory in your machine
    drew
    ------------------
    http://wjtechnologies.com

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Also, create a LARGE rollback segment and take all smaller ones offline

    Gregg

  6. #6
    Join Date
    Jan 2004
    Location
    california
    Posts
    37
    Greg ,

    How do I create a large rollback seg. I am the only user on the server, and the database is currently empty. Would the code below do ?. Are the settings correct. I have lots of disk space. How could I find out what my other rollback segments are. Do I need to remove the old ones or just take them offline ? I have some large tables.

    CREATE ROLLBACK SEGMENT a_rollback_segment_name
    TABLESPACE a_tablespace_name
    STORAGE
    (
    INITIAL 2M
    NEXT 2M
    MINEXTENTS 20
    MAXEXTENTS UNLIMITED
    )


    Thanks
    Peter

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    If you want the initial data and tables to be loaded faster then I would set CONSTRAINTS=N

    After the initial import is done, then run again and set ROWS=N IGNORE=Y CONSTRAINTS=Y INDEXES=Y
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Just take the smaller rollbacks offline.

    CREATE ROLLBACK SEGMENT a_rollback_segment_name
    TABLESPACE a_tablespace_name
    STORAGE
    (
    INITIAL 200M
    NEXT 25M
    MINEXTENTS 2
    MAXEXTENTS UNLIMITED
    )

    Also do as Duck says if you have constraining issues... It also might
    be that you are paging/swapping like crazy.... 512 memory...
    Whats the total size of the SGA??

    Gregg

Posting Permissions

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