This discussion is archived
9 Replies Latest reply: Sep 3, 2013 12:24 AM by User177447 RSS

Unable to import database using impdp via network_link

User177447 Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 . Expert
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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!!!

Legend

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