This discussion is archived
9 Replies Latest reply: Sep 23, 2013 10:13 PM by krishna-TD RSS

Import getting stuck...

user3636719 Newbie
Currently Being Moderated
Hello Everyone,

I am putting following command to take import.
nohup impdp "'/ as sysdba'" REMAP_SCHEMA=SPORTS:SPORTS DIRECTORY=dpump_dir DUMPFILE=expdp_D.SPORTS_02282012.dmp LOGFILE = impdp_D.SPORTS_03192012.log REMAP_TABLESPACE = SPORTS_DATA_TS:SPORTS_DATA_TS , SPORTS_INDEX_TS:SPORTS_INDEX_TS &
It is running till here
/database1/rdbm6/export> tail -200f impdp_D.SPORTS_03192012.log
;;;
Import: Release 11.2.0.2.0 - Production on Mon Mar 19 15:34:10 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_02" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_02":  "/******** AS SYSDBA" REMAP_SCHEMA=SPORTS:SPORTS DIRECTORY=dpump_dir DUMPFILE=expdp_D.SPORTS_02282012.dmp LOGFILE = impdp_D.SPORTS_03192012.log REMAP_TABLESPACE = SPORTS_DATA_TS:SPORTS_DATA_TS , SPORTS_INDEX_TS:SPORTS_INDEX_TS
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SPORTS" already exists
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
And it is getting stuck, It is not moving forward.

I am not sure what to check and where to check. I have see export log, it looks clear.
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for HPUX: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
  • 1. Re: Import getting stuck...
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    You should not use "' / as sysdba'" for running expdp and impdp - see the first "Note" section here

    http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#i1012504

    Try re-running expdp and impdp using the SYSTEM schema

    HTH
    Srini
  • 2. Re: Import getting stuck...
    user3636719 Newbie
    Currently Being Moderated
    You should not use "' / as sysdba'" for running expdp and impdp - see the first "Note" section here
    Thanks for pointing me to that, I agree. But so far doing this way is worked for me. This is the first time it is getting stuck. I am not sure what the reason could be.

    Is there any place where I can check?
  • 3. Re: Import getting stuck...
    Dean Gagne Expert
    Currently Being Moderated
    From the window running the job you can:

    ctl-c
    then you will get to the Import prompt

    Import> status

    If you type status, you will see what each worker process is doing. Type in status a couple of times over a few minutes to see if you can see anything change.

    To get back to you normal output type

    Import> continue

    Dean
  • 4. Re: Import getting stuck...
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    In addition to what Dean stated, see if the trace features of datapump can help

    Export/Import DataPump Parameter TRACE - How to Diagnose Oracle Data Pump [ID 286496.1]     
    How To Use The New 11g Events++ Syntax For Easier SQL Tracing Of Datapump Operations? [ID 813737.1]     


    HTH
    Srini
  • 5. Re: Import getting stuck...
    user3636719 Newbie
    Currently Being Moderated
    From the window running the job you can:
    
    ctl-c
    then you will get to the Import prompt
    
    Import> status
    
    If you type status, you will see what each worker process is doing. Type in status a couple of times over a few minutes to see if you can see anything change.
    
    To get back to you normal output type 
    
    Import> continue
    
    Dean
    Where do you want me to run ctl-c? in command prompt? after login to DB?

    I am starting to think, is there any table or any thing that full? That is why import is getting stuck there?
  • 6. Re: Import getting stuck...
    Dean Gagne Expert
    Currently Being Moderated
    You would type the ctl-c from the same window that is running the import job. ctl-c does not kill the job, it just puts you in interactive mode. From here you can type help and it will give you the commands that are available.

    Dean
  • 7. Re: Import getting stuck...
    user3636719 Newbie
    Currently Being Moderated
    We figured out the impdp is not able to read dumpfile, I checked the log of the dumpfile it looks clean. It's getting stuck after reaching below level.
    Import: Release 11.2.0.2.0 - Production on Mon Mar 19 15:34:10 2012
     
    Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
    ;;;
    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Master table "SYS"."SYS_IMPORT_FULL_02" successfully loaded/unloaded
    Starting "SYS"."SYS_IMPORT_FULL_02":  "/******** AS SYSDBA" REMAP_SCHEMA=SPORTS:SPORTS DIRECTORY=dpump_dir DUMPFILE=expdp_D.SPORTS_02282012.dmp LOGFILE = impdp_D.SPORTS_03192012.log REMAP_TABLESPACE = SPORTS_DATA_TS:SPORTS_DATA_TS , SPORTS_INDEX_TS:SPORTS_INDEX_TS
    Processing object type SCHEMA_EXPORT/USER
    ORA-31684: Object type USER:"SPORTS" already exists
    Processing object type SCHEMA_EXPORT/ROLE_GRANT
    Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
    Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
    Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
    We are still experiencing same issue, We are going to submit ticket to oracle. We are waiting to hear a solution from them.
  • 8. Re: Import getting stuck...
    Dean Gagne Expert
    Currently Being Moderated
    How did you figure this out. What did you see that pointed you to the file not being read? Just curious.

    Thanks

    Dean
  • 9. Re: Import getting stuck...
    krishna-TD Newbie
    Currently Being Moderated

    I also had a similar problem and import was getting stuck as below.

     

    cpcppmdbv01@/oracle_export/WLM:CPPMDEV1>impdp parfile=impdp_129900_WLMSchemas.par

     

     

    Import: Release 11.2.0.2.0 - Production on Mon Sep 23 20:33:11 2013

     

     

    Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

     

     

    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

    With the Partitioning, Automatic Storage Management and Real Application Testing options

    Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

    Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA parfile=impdp_129900_WLMSchemas.par

    Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

    Processing object type SCHEMA_EXPORT/ROLE_GRANT

    Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

    Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA

    Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY

     

     

    Import> kill

    Are you sure you wish to stop this job ([yes]/no): yes


    I used below option in my parfile and - restarted import and it ocmpleted successfully in 5 mins.
    EXCLUDE=PASSWORD_HISTORY


    Reference

    Oracle « The "Vimal Mohan" Blog

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points