Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Posts
    23

    Unanswered: 8i Full Import Hanging

    I am doing a full import into a newly created 8i instance and the import appears to be hanging. It is hanging on a "CREATE FORCE VIEW" statement, during the importation of the view objects. It appears to have imported all the tables successfully, but is hanging in the middle of these views (it made it up to the "L" views alphabetically).

    I did mistakenly have ARCHIVELOG mode on when the import was done, but deleted down the unnecessary archive logs as they were created. There is plenty of space on all disks. The process has stopped writing to the import log file over an hour ago, and there is apparently no system activity when checking task manager (on Win 2k server).

    Here is my import script:

    set ORACLE_HOME=c:\oracle\ora81
    set ORACLE_SID=UKPROD

    %ORACLE_HOME%\bin\imp system/manager@UKPROD FULL=Y BUFFER=2000000 COMMIT=Y INDEXES=Y GRANTS=Y CONSTRAINTS=Y ROWS=Y IGNORE=Y FILE=F:\KERNEL\exp_kernel.dmp LOG=F:\KERNEL\exp_kernel.log

    Any ideas why it might be hanging? Should I cancel and scrap this import and recreate a new instance (without ARCHIVELOG mode on) and retry? Or is there a way to cancel and restart the import?

    Thanks!

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    could it be a space issue?

    what tablespace are the views being created on?

    full imports can be tricky since you need everything set up identical.

    views are some of the last to be imported so you could always re-create all the views manually.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Aug 2003
    Posts
    23
    Originally posted by The_Duck
    could it be a space issue?

    what tablespace are the views being created on?

    full imports can be tricky since you need everything set up identical.

    views are some of the last to be imported so you could always re-create all the views manually.
    The new instance is defined with all the tablespaces larger than the old instance, so space is not the problem. same tablespace names too. I've also disabled ARCHIVELOG mode and it still hangs at the same spot. We've recopied and reextracted (zip) the dump file and same thing.

  4. #4
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Re: 8i Full Import Hanging

    My suggestions:

    A) Rebuild the srvc and inst
    B) Make sure it is not in ArchiveLog mode
    C) You have sufficient rollback segments online.
    D) Make sure you have run
    @<%oraclepath%>\RDBMS\ADMIN\CATALOG.SQL;
    @<%oraclepath%>\RDBMS\ADMIN\CATPROC.SQL;
    @<%oraclepath%>\RDBMS\ADMIN\CATHS.SQL;
    E) Shutdown as many other activity/databases as possible to give more memory.
    F) Before starting the import, completely shutdown and restart the service and instance.
    G) Instead of running all your parameters into the command line, create a parameter file and feed it in similar to this.
    <%oraclepath%>\BIN\IMP SYSTEM/******* PARFILE=D:\BILDPROD\IMPORT.PAR
    I've always had problems trying to feed the parameters from a command line on WinNT or W2K. I've attached a copy of my parameter file.

    The buffer command is one you might want to obseve also.

    Good luck. Hope this isn't a disaster recovery situation.

  5. #5
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    4

    Question

    Some ideas

    0. Recreate database. Set no archivelog mode

    1. Always capture what is inside import file with SHOW=Y parameter. In this case no actual import occurs but all sql statements ans data is logged into log file( say imp_sql.log).

    2. From the imp_sql.log log file, create all table spaces manually by carefully checking datafile paths without reuse option.

    3. import with FULL=Y TABLES=Y ROWS=N INDEXES=N CONTSRAINTS=N IGNORE=Y

    4. Views should be created by this time. If it hangs, study particular sql staement from imp_sql.log.

    5. ipmort ROWS=Y ROWS=Y INDEXES=N CONSTRAINTS=N INGNORE=Y

    6. import ROWS=N INDEXFILE=imp_index.log CONSTRAINTS=Y INGNORE=N

    7. EDit the imp_index.log file manually run the file.

    Good luck !!

  6. #6
    Join Date
    Aug 2003
    Posts
    23
    Thanks, everyone. I followed the above steps and it works fine. Seems that the problem was the script hanging, not something on the oracle side. I rewrote the import to use a parameter file and it works fine now.

    Thanks!

Posting Permissions

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