9 Replies Latest reply: Sep 3, 2013 2:24 AM by User177447 RSS

    Unable to import database using impdp via network_link

    User177447

      Dear All,

       

      I am trying to import database from remote location's dump to my local database after creating dblink from local db to remote db.


      At Remote Server:


      - Directory created and granted the privileges. Dir Name: PREMLIVE_EXPDP


      At Local server:


      Username: expuser with dba, imp and exp full database privileges.


      DB Link name: to_premiatest14


      I faced an error of directory invalid when I run the below command in local server to import schema from remote database.


      C:\>impdp expuser/expuser network_link=to_premiatest14 directory=PREMLIVE_EXPDP remap_schema=PREMIATEST:PREMIATEST15 logfile=2013-09-02_premiatest15.log

       

      Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit

      Production

      With the Partitioning, OLAP, Data Mining and Real Application Testing options

      ORA-39002: invalid operation

      ORA-39070: Unable to open the log file.

      ORA-39087: directory name PREMLIVE_EXPDP is invalid

       

      Any help or advice's would be really highly appreciated.

       

      Regards,

      Syed

        • 1. Re: Unable to import database using impdp via network_link
          sb92075

          802557 wrote:

           

          Dear All,

           

          I am trying to import database from remote location's dump to my local database after creating dblink from local db to remote db.


          At Remote Server:


          - Directory created and granted the privileges. Dir Name: PREMLIVE_EXPDP


          At Local server:


          Username: expuser with dba, imp and exp full database privileges.


          DB Link name: to_premiatest14


          I faced an error of directory invalid when I run the below command in local server to import schema from remote database.


          C:\>impdp expuser/expuser network_link=to_premiatest14 directory=PREMLIVE_EXPDP remap_schema=PREMIATEST:PREMIATEST15 logfile=2013-09-02_premiatest15.log

           

          Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit

          Production

          With the Partitioning, OLAP, Data Mining and Real Application Testing options

          ORA-39002: invalid operation

          ORA-39070: Unable to open the log file.

          ORA-39087: directory name PREMLIVE_EXPDP is invalid

           

          Any help or advice's would be really highly appreciated.

           

          Regards,

          Syed

           

          realize that local & remote  are relative & everything you have is remote to us.

           

          For System1 post hostname, IP#, Operating System name & version & Oracle s/w version to 4 decimal places.

          For System2 post hostname, IP#, Operating System name & version & Oracle s/w version to 4 decimal places.

          Clarify which hostname is associated with data source & which hostname is associated with the data target.

          from which hostname is the impdp command initiated?

          • 2. Re: Unable to import database using impdp via network_link
            JohnWatson

            If I read your post correctly, you have not created the directory on your local system. You need to do this.

            • 3. Re: Unable to import database using impdp via network_link
              Richard Harrison .

              Hi,

              This is not possible.

               

              impdp over network_link can only import the objects directly from a remote database - it cannot access remote dumpfiles to do an import.

               

              There is a special case with expdp where a remote dumpfile can be created from a local database but this only works for expdp.

               

              You either need to copy the file to the local server, somehow share it to the local server form the remote or forget about the file and take the remote database as the source directly.

               

              Regards,

              Harry

              • 4. Re: Unable to import database using impdp via network_link
                User177447

                Dear Jhon,

                 

                FYI, i have created the directory at remote side which holds the dump file. I am importing the database being in the local server using the remote side dump file via network_link.

                 

                 

                Dear Harry,

                 

                I cannot copy the file in the local server (a test server) due to some space issues, so am using the network/dblink to import the database in the local server.

                 

                 

                Regards,

                Syed

                • 5. Re: Unable to import database using impdp via network_link
                  User177447

                  Some more details follows.

                   

                  Server 1: Remote Server

                   

                  BANNER

                  ----------------------------------------------------------------

                  Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

                  PL/SQL Release 10.2.0.5.0 - Production

                  CORE    10.2.0.5.0      Production

                  TNS for 64-bit Windows: Version 10.2.0.5.0 - Production

                  NLSRTL Version 10.2.0.5.0 - Production

                   

                  TNS-Entry:

                   

                  PREMIATEST14=

                    (DESCRIPTION =

                      (ADDRESS = (PROTOCOL = TCP)(HOST = XX.XX.XX.14)(PORT = 1521))

                      (CONNECT_DATA =

                        (SERVER = DEDICATED)

                        (SERVICE_NAME = malath)

                      )

                    )

                   

                  DIRECTORY:

                   

                  PREMLIVE_EXPDP AS

                  'C:\Dump\PREMLIVE_EXPDP'; ---->>>> This location/directory holds the dump file.

                   

                   

                  Server 2: Local Server

                   

                  BANNER

                  ----------------------------------------------------------------

                  Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

                  PL/SQL Release 10.2.0.5.0 - Production

                  CORE    10.2.0.5.0      Production

                  TNS for 64-bit Windows: Version 10.2.0.5.0 - Production

                  NLSRTL Version 10.2.0.5.0 - Production

                   

                  TNS-Entry:

                   

                  PREMIATEST15=

                    (DESCRIPTION=

                      (ADDRESS=

                        (PROTOCOL=TCP)

                        (HOST=XX.XX.XX.15)

                        (PORT=1521)

                      )

                      (CONNECT_DATA=

                        (SERVICE_NAME=PREMIA15)

                      )

                    )

                   

                  PREMIATEST14 =

                    (DESCRIPTION =

                      (ADDRESS = (PROTOCOL = TCP)(HOST = XX.XX.XX.14)(PORT = 1521))

                      (CONNECT_DATA =

                        (SERVER = DEDICATED)

                        (SERVICE_NAME = malath)

                      )

                    )

                   

                  Network/DBLink:

                   

                  CREATE PUBLIC DATABASE LINK TO_PREMIATEST14 CONNECT TO EXPUSER IDENTIFIED BY <PWD> USING 'PREMIATEST14';

                   

                  DBLink tested :

                   

                  SQL> select count(*) from premiatest.brkdivion@to_premiatest14;

                   

                   

                    COUNT(*)

                  ----------

                          94

                   

                  Error on importing:

                   

                  C:\>impdp expuser/expuser network_link=to_premiatest14 directory=PREMLIVE_EXPDP

                  remap_schema=PREMIATEST:PREMIATEST15 logfile=2013-09-02_premiatest15.log

                   

                   

                  Import: Release 10.2.0.5.0 - 64bit Production on Tuesday, 03 September, 2013 1:2

                  3:10

                   

                   

                  Copyright (c) 2003, 2007, Oracle.  All rights reserved.

                   

                   

                  Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit

                  Production

                  With the Partitioning, OLAP, Data Mining and Real Application Testing options

                  ORA-39002: invalid operation

                  ORA-39070: Unable to open the log file.

                  ORA-39087: directory name PREMLIVE_EXPDP is invalid

                   

                  I don't know where am i going wrong . Valuable advice's/assistance will be highly appreciated.

                   

                  Thanks & Regards,

                  Syed

                  • 6. Re: Unable to import database using impdp via network_link
                    RodSantander

                    Hi Syed,

                     

                    I think you are doing the oposite about directory stuff.

                    The directory parameter is just to holds the log file. There is no required dumpfile file in IMPDP via dblink operations, it's all cross network.

                    Basically what is missing is the DIRECTORY in the local server.

                    Also don't forget create the related tablespace, and remap if necessary.



                    Regards,

                    Rodrigo






                    • 7. Re: Unable to import database using impdp via network_link
                      User177447

                      Hi Rodrigo,

                       

                      Thanks for the response.

                       

                      I created the directory in the local server and run the command but no proper result.

                       

                      C:\>impdp expuser/expuser network_link=to_premiatest14 directory=PREMLIVE_EXPDP

                      remap_schema=PREMIATEST:PREMIATEST15 remap_tablespace=PREMIATEST:PREMIATEST15 lo

                      gfile=2013-09-02_premiatest15.log

                       

                       

                      Import: Release 10.2.0.5.0 - 64bit Production on Tuesday, 03 September, 2013 8:4

                      2:05

                       

                       

                      Copyright (c) 2003, 2007, Oracle.  All rights reserved.

                       

                       

                      Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit

                      Production

                      With the Partitioning, OLAP, Data Mining and Real Application Testing options

                      Starting "EXPUSER"."SYS_IMPORT_SCHEMA_01":  expuser/******** network_link=to_pre

                      miatest14 directory=PREMLIVE_EXPDP remap_schema=PREMIATEST:PREMIATEST15 remap_ta

                      blespace=PREMIATEST:PREMIATEST15 logfile=2013-09-02_premiatest15.log

                      Estimate in progress using BLOCKS method...

                      Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

                      Total estimation using BLOCKS method: 0 KB

                      Processing object type SCHEMA_EXPORT/USER

                      ORA-31684: Object type USER:"EXPUSER" already exists

                      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/PRE_SCHEMA/PROCACT_SCHEMA

                      Job "EXPUSER"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at 08:42:17

                       

                       

                       

                       

                       

                       

                      Regards

                      • 8. Re: Unable to import database using impdp via network_link
                        JohnWatson

                        If I understand you correctly, now that you have created the directory locally, you no longer get any errors. But you have not told import what it is that you want to import. You need to specify TABLES or SCHEMAS or FULL.

                        • 9. Re: Unable to import database using impdp via network_link
                          User177447

                          Ups!! Sorry for that silly mistake John.

                           

                          Rodrigo & John, thanks a lot for your great assistance and valuable advice's. I run the command again with the appropriate parameters and the import is started now.

                           

                          Thank you all experts!!!