9 Replies Latest reply on Nov 27, 2010 1:41 AM by Levi Pereira

    expdp from remote database

    748245
      Hi Group,

      I have two servers :

      SERVER1 (PROD) (10G)
      SERVER2 (TEST) (11G)

      I need to execute expdp and generate an export file in SERVER2 with data from SERVER1, I've tried at first to execute like this:

      expdp system@server1 schemas=EULDNPP directory=TEST dumpfile=expdp_EULDNPP.dmp logfile=expdp_EULDNPP.log

      but on server1 there is no directory TEST, even it exist
      the file would be generate on server1 which I can not access.

      ORA-39002: invalid operation
      ORA-39070: Unable to open the log file.
      ORA-39087: directory name TEST is invalid

      How could I generate the dump file on server2 where I'm executing expdp.

      Thanks a lot.

      Edited by: user12003066 on 26-nov-2010 10:44
        • 1. Re: expdp from remote database
          Levi Pereira
          Hi,

          Try Use Network Link Parameter

          http://www.remote-dba.net/oracle_10g_new_features/network_link.htm

          http://download.oracle.com/docs/cd/B28359_01/server.111/b28319/dp_export.htm#i1011008

          NETWORK_LINK - You can initiate an export job from your server and have Data Pump export data from a remote database to dump files located on the instance from which you initiate the Data Pump export job.

          Regards,
          Levi Pereira
          http://levipereira.wordpress.com
          • 2. Re: expdp from remote database
            448778
            user12003066 wrote:
            Hi Group,

            I have two servers :

            SERVER1 (PROD) (10G)
            SERVER2 (TEST) (11G)

            I need to execute expdp and generate an export file in SERVER2 with data from SERVER1, I've tried at first to execute like this:

            expdp system@server1 schemas=EULDNPP directory=TEST dumpfile=expdp_EULDNPP.dmp logfile=expdp_EULDNPP.log

            but on server1 there is no directory TEST, even it exist
            the file would be generate on server1 which I can not access.

            ORA-39002: invalid operation
            ORA-39070: Unable to open the log file.
            ORA-39087: directory name TEST is invalid

            How could I generate the dump file on server2 where I'm executing expdp.

            Thanks a lot.

            Edited by: user12003066 on 26-nov-2010 10:44
            You need to create a database link between these 2 databases and then use "NETWORK_LINK" parameter in the EXPDP and specify the name of the databaselink you will create.
            • 3. Re: expdp from remote database
              729036
              [edit] Start using datapump export
              $ expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp
              ORA-39002: invalid operation
              ORA-39070: Unable to open the log file.
              ORA-39087: directory name DMPDIR is invalid


              we need to create a directory first!

              [edit] Create database directories
              Execute the following commands to create a database directory. This directory must point to a valid directory on the same server as the database:

              SQL> CREATE DIRECTORY dmpdir AS '/opt/oracle';
              Directory created.
              SQL> GRANT read, write ON DIRECTORY dmpdir TO scott;
              Grant succeeded.
              PS: Oracle introduced a default directory from 10g R2, called DATA_PUMP_DIR, that can be used:

              SQL> SELECT directory_path FROM dba_directories WHERE directory_name = 'DATA_PUMP_DIR';

              try now hope it helps
              • 4. Re: expdp from remote database
                448778
                shaiffy wrote:
                [edit] Start using datapump export
                $ expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp
                ORA-39002: invalid operation
                ORA-39070: Unable to open the log file.
                ORA-39087: directory name DMPDIR is invalid


                we need to create a directory first!

                [edit] Create database directories
                Execute the following commands to create a database directory. This directory must point to a valid directory on the same server as the database:

                SQL> CREATE DIRECTORY dmpdir AS '/opt/oracle';
                Directory created.
                SQL> GRANT read, write ON DIRECTORY dmpdir TO scott;
                Grant succeeded.
                PS: Oracle introduced a default directory from 10g R2, called DATA_PUMP_DIR, that can be used:

                SQL> SELECT directory_path FROM dba_directories WHERE directory_name = 'DATA_PUMP_DIR';

                try now hope it helps
                If you don't use NETWORK_LINK parameter , EXPDP created the dump file on the database host and you will have to copy this file to remote server and create a directory and place the dump file in that directory, before running IMPDP, you can avoid all these using NETWORK_LINK parameter which allows expdp to export data from the remote database.
                • 5. Re: expdp from remote database
                  729036
                  sorry but i know the network link but was just trying to solve his error thats say directory invalid etc ??
                  hope u understood
                  regards
                  • 6. Re: expdp from remote database
                    748245
                    OK I implemented NETWORK_LINK to execute the export and it was generated on server2 as it was required but it only worked after trying times the same sentence, a kind of errors were showed:

                    expdp system schemas=DESA directory=TEST dumpfile=expdp_desa.dmp logfile=expdp_desa.log network_link=DBL_TEST

                    1st try:

                    Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
                    With the Partitioning, OLAP, Data Mining and Real Application Testing options
                    ORA-39006: internal error
                    ORA-39113: Unable to determine database version
                    ORA-04062: timestamp of package "SYS.DBMS_UTILITY" has been changed

                    2 try:

                    Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
                    With the Partitioning, OLAP, Data Mining and Real Application Testing options
                    ORA-39097: Data Pump job encountered unexpected error -4062
                    ORA-39065: unexpected master process exception in DISPATCH
                    ORA-04062: timestamp of package "SYS.DBMS_DATAPUMP" has been changed

                    3 try:

                    Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
                    With the Partitioning, OLAP, Data Mining and Real Application Testing options
                    ORA-39004: invalid state
                    ORA-39091: unable to determine logical standby and streams status
                    ORA-04062: timestamp of package "SYS.DBMS_STREAMS_PUB_RPC" has been changed

                    4 try:

                    Estimate in progress using BLOCKS method...
                    Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
                    ORA-39126: Worker unexpected fatal error in KUPW$WORKER.CONFIGURE_METADATA_UNLOAD [ESTIMATE_PHASE]
                    ORA-04062: timestamp of package "SYS.DBMS_METADATA" has been changed

                    ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
                    ORA-06512: at "SYS.KUPW$WORKER", line 7839

                    ----- PL/SQL Call Stack -----
                    object line object
                    handle number name
                    7000000519757e8 18237 package body SYS.KUPW$WORKER
                    7000000519757e8 7866 package body SYS.KUPW$WORKER
                    7000000519757e8 6262 package body SYS.KUPW$WORKER
                    7000000519757e8 11899 package body SYS.KUPW$WORKER
                    7000000519757e8 2312 package body SYS.KUPW$WORKER
                    7000000519757e8 8504 package body SYS.KUPW$WORKER
                    7000000519757e8 1545 package body SYS.KUPW$WORKER
                    700000051976138 2 anonymous block

                    Estimate in progress using BLOCKS method...
                    Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
                    Total estimation using BLOCKS method: 819.3 MB
                    Processing object type SCHEMA_EXPORT/USER
                    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
                    Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
                    Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
                    Processing object type SCHEMA_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
                    Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
                    Processing object type SCHEMA_EXPORT/TABLE/TABLE
                    Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
                    Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
                    Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
                    Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
                    .
                    .
                    and it finished:
                    .
                    .
                    Master table "SYSTEM"."SYS_EXPORT_SCHEMA_04" successfully loaded/unloaded
                    ******************************************************************************
                    Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_04 is:
                    /bk_exports/test/expdp_desa.dmp
                    Job "SYSTEM"."SYS_EXPORT_SCHEMA_04" completed with 3 error(s) at 16:23:51


                    Could be the version of databases?
                    SERVER1 (10.2.0.4)
                    SERVER2 (11.1.0.7)
                    • 7. Re: expdp from remote database
                      Levi Pereira
                      Hi,

                      Try this...

                      Set parameter REMOTE_DEPENDENCIES_MODE to SIGNATURE on both databases, remote and source.

                      If this does not work try to see if you have:
                      Restrictions
                      When the NETWORK_LINK parameter is used in conjunction with the TABLES parameter, only whole tables can be exported (not partitions of tables).
                      The only types of database links supported by Data Pump Export are: public, fixed-user, and connected-user. Current-user database links are not supported.

                      Regards,
                      Levi Pereira
                      http://levipereira.wordpress.com
                      • 8. Re: expdp from remote database
                        748245
                        Good, I changed the parameter value to SIGNATURE on the local database, (I have no access to remote db) and execute the exportdp again and no more messages appeared. Now is it recommended to keep the parameter REMOTE_DEPENDENCIES_MODE value on SIGNATURE? or better switch to the default value TIMESTAMP?

                        Thanks a lot.
                        • 9. Re: expdp from remote database
                          Levi Pereira
                          Hi,

                          I'm glad you managed to solve your problem.

                          I do not know your environment, so I can not tell which parameter is better (TIMESTAMP or SIGNATURE).

                          My advice is this "If it's not broke, do not fix it"

                          I have a link with a great article. What helped you decide which is the best choice for your environment.

                          http://oracletoday.blogspot.com/2009/02/remote-dependencies.html

                          Regards,
                          Levi Pereira
                          http://levipereira.wordpress.com