9 Replies Latest reply: Sep 24, 2013 12:13 AM by krishna-TD RSS

    Import getting stuck...

    user3636719
      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
          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
            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-Oracle
              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
                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
                  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-Oracle
                    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
                      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-Oracle
                        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

                          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