Using Data Pump Export(expdp) Over Network

Data Pump Export can be used over Networks. Normally when data pump export utility(expdp) executes, it creates a dump file to the server where the database is installed. You can DIRECTORY parameters in order to specify operating system file path.
Suppose that you have 2 databases(srv1db and srv2db) in 2 different servers(srv1 and srv2). If you run expdp on srv2db, dump file shall be created in srv2 server. You can also run expdp on srv1db and dump file shall be created in srv2 server, with NETWORK_LINK expdp parameter. See demostration below:


--srv1db
SQL> select DIRECTORY_PATH from all_directories where directory_name = 'DATA_PUMP_DIR';

DIRECTORY_PATH
--------------------------------------------------------------------------------
/usr/users/oracle/DP

SQL>


--srv2db
SQL> select DIRECTORY_PATH from all_directories where directory_name = 'DATA_PUMP_DIR';

DIRECTORY_PATH
--------------------------------------------------------------------------------
/home/oracle/DP

SQL>

---srv1
uccs01 oracle@srv1:/usr/users/oracle/DP> ls -ltr


--srv2
bash-3.00$ ls -ltr /home/oracle/DP




--expdp on srv2
bash-3.00$ expdp mte/mte@srv1db tables=t directory=DATA_PUMP_DIR dumpfile=mte_t.dmp logfile=mte_t.log

Export: Release 10.2.0.2.0 - 64bit Production on Wednesday, 13 August, 2008 10:15:53

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "MTE"."SYS_EXPORT_TABLE_01": mte/********@srv1db tables=t directory=DATA_PUMP_DIR dumpfile=mte_t.dmp logfile=mte_t.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "MTE"."T" 5.125 KB 31 rows
Master table "MTE"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MTE.SYS_EXPORT_TABLE_01 is:
/usr/users/oracle/DP/mte_t.dmp
Job "MTE"."SYS_EXPORT_TABLE_01" successfully completed at 10:12:11



---srv1
uccs01 oracle@srv1:/usr/users/oracle/DP> ls -ltr
total 2455
-rw-r----- 1 oracle dba 69632 Aug 13 10:12 mte_t.dmp
-rw-r--r-- 1 oracle dba 981 Aug 13 10:12 mte_t.log
uccs01 oracle@srv1:/usr/users/oracle/DP>


--srv2
bash-3.00$ ls -ltr /home/oracle/DP




--srv2db, create a dblink for srv1db
SQL>create public database link srv1db_LINK.ORC
connect to USER1
using 'srv1db.ORC';


--expdp on srv2
bash-3.00$ expdp USER1/USER1@srv2db tables=t directory=DATA_PUMP_DIR dumpfile=mte_t2.dmp logfile=mte_t2.log network_link=srv1db_link

Export: Release 10.2.0.2.0 - 64bit Production on Wednesday, 13 August, 2008 10:22:09

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "USER1"."SYS_EXPORT_TABLE_01": USER1/********@srv2db tables=t directory=DATA_PUMP_DIR dumpfile=mte_t2.dmp logfile=mte_t2.log network_link=srv1db_link
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "USER1"."T" 5.351 KB 16 rows
Master table "USER1"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for USER1.SYS_EXPORT_TABLE_01 is:
/home/oracle/DP/mte_t2.dmp
Job "USER1"."SYS_EXPORT_TABLE_01" successfully completed at 10:22:45

bash-3.00$



---srv1
uccs01 oracle@srv1:/usr/users/oracle/DP> ls -ltr
total 2455
-rw-r----- 1 oracle dba 69632 Aug 13 10:12 mte_t.dmp
-rw-r--r-- 1 oracle dba 981 Aug 13 10:12 mte_t.log
uccs01 oracle@srv1:/usr/users/oracle/DP>


--srv2
bash-3.00$ ls -ltr /home/oracle/DP
-rw-r--r-- 1 oracle oinstall 0 Aug 13 10:22 mte_t2.log
-rw-r----- 1 oracle oinstall 4096 Aug 13 10:22 mte_t2.dmp
bash-3.00$

Workaround for ORA-39014, ORA-12801, ORA-29913

There in an Oracle Bug(5472417 , Note:438608.1) about parallelism mechanism of data pump export. Workaround fort his may be removing parallelism parameter from export(running as a single process)

bash-3.00$ expdp MT/MT full=y directory=DATA_PUMP_DIR dumpfile=orcl_full_imp_p%u.dmp logfile=orcl_full_imp.log parallel=4 network_link=orcl_link

Export: Release 10.2.0.2.0 - 64bit Production on Wednesday, 13 August, 2008 10:49:49

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "MT"."SYS_EXPORT_FULL_01": MT/******** full=y directory=DATA_PUMP_DIR dumpfile=orcl_full_imp_p%u.dmp logfile=orcl_full_imp.log parallel=4 network_link=orcl_link
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 13.06 GB
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PASSWORD_VERIFY_FUNCTION
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/ROLE
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/WITH_GRANT_OPTION/OBJECT_GRANT
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/CROSS_SCHEMA/OBJECT_GRANT
Processing object type DATABASE_EXPORT/CONTEXT
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/PRE_TABLE_ACTION
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/CROSS_SCHEMA/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/RLS_POLICY
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/FUNCTION
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/CROSS_SCHEMA/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_BODY
Processing object type DATABASE_EXPORT/SCHEMA/JAVA_CLASS/JAVA_CLASS
Processing object type DATABASE_EXPORT/SCHEMA/JAVA_RESOURCE/JAVA_RESOURCE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_TABLE_ACTION
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/TRIGGER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCACT_INSTANCE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCDEPOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 4 with process name "DW04" prematurely terminated
ORA-31671: Worker process DW04 had an unhandled exception.
ORA-12801: error signaled in parallel query server
ORA-29913: error in executing ODCIEXTTABLEPOPULATE callout
ORA-29400: data cartridge error
KUP-04038: internal error: kupax-meta1
ORA-06512: at "SYS.ORACLE_DATAPUMP", line 78
ORA-06512: at "SYS.KUPW$WORKER", line 1345
ORA-06512: at line 2

Job "MT"."SYS_EXPORT_FULL_01" stopped due to fatal error at 11:13:47

Solution of ORA-39002, ORA-39070, ORA-39087

The errors that i mentioned in the subject, can be occured during data pump export/import(expdp/impdp executables). If the directory object does not have necessary rigths, will fire an exception. Giving rights(read - write) will be a solution.

bash-3.00$ expdp mte/mte@test02 tables=t directory=DATA_PUMP_DIR dumpfile=mte_t.dmp logfile=mte_t.log

Export: Release 10.2.0.2.0 - 64bit Production on Wednesday, 13 August, 2008 10:09:48

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name DATA_PUMP_DIR is invalid




>sqlplus
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.2.0
Connected as HR

SQL> conn mte/mte@test02;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.2.0
Connected as mte

SQL> select directory_name from all_directories;

DIRECTORY_NAME
------------------------------
HLR_DIR

SQL> conn HR/HR@test02;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.2.0
Connected as HR

SQL> grant read, write on directory DATA_PUMP_DIR to mte;

Grant succeeded

SQL> conn mte/mte@test02;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.2.0
Connected as mte

SQL> select directory_name from all_directories;

DIRECTORY_NAME
------------------------------
DATA_PUMP_DIR
HLR_DIR

SQL>



bash-3.00$ expdp mte/mte@test02 tables=t directory=DATA_PUMP_DIR dumpfile=mte_t.dmp logfile=mte_t.log

Export: Release 10.2.0.2.0 - 64bit Production on Wednesday, 13 August, 2008 10:15:53

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "MTE"."SYS_EXPORT_TABLE_01": mte/********@test02 tables=t directory=DATA_PUMP_DIR dumpfile=mte_t.dmp logfile=mte_t.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "MTE"."T" 5.125 KB 31 rows
Master table "MTE"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MTE.SYS_EXPORT_TABLE_01 is:
/usr/users/oracle/DP/mte_t.dmp
Job "MTE"."SYS_EXPORT_TABLE_01" successfully completed at 10:12:11

An Interesting PL/SQL Bug about References

I have struggled a PL/SQL problem a few days ago. The problem was, as Pl/SQL Compiler says, "PLS-00225: subprogram or cursor 'string' reference is out of scope". It was not as easy as Compiler mentions.
Let me show it by an example:

Suppose that you have 2 packages, PACK1 and PACK2. In PACK2, you have a procedure in PACK1 named PACK1 and another procedure that named PACK1 in PACK2 package. When the namings are established like that you will get "PLS-00225: subprogram or cursor 'string' reference is out of scope" error.

Example below CMP.COMPARE and P_TEST.COMPARE exists. In P_TEST, there is a reference for COMPARE package. It Works fine.

SQL> CREATE OR REPLACE PACKAGE CMP IS
2 TYPE t_HashTable IS TABLE OF VARCHAR2(512) INDEX BY VARCHAR2(32);
3
4 TYPE t_SQL IS RECORD(
5 SQL_STATEMENT VARCHAR2(4000),
6 BIND_LIST t_HashTable
7 );
8
9 END CMP;
10 /

Package created

SQL>
SQL> CREATE OR REPLACE PACKAGE BODY CMP IS
2
3 PROCEDURE Compare IS
4 BEGIN
5 NULL;
6 END Compare;
7
8 END CMP;
9 /

Package body created

SQL>
SQL> CREATE OR REPLACE PACKAGE p_test IS END p_test;
2 /

Package created

SQL> CREATE OR REPLACE PACKAGE BODY p_test IS
2
3 TYPE t_SQLList IS TABLE OF CMP.t_SQL INDEX BY VARCHAR2(16);
4
5 PROCEDURE Compare IS
6 BEGIN
7 NULL;
8 END Compare;
9
10 END p_test;
11 /

Package body created

SQL>



Example2 below COMPARE.COMPARE and P_TEST.COMPARE exists. In P_TEST, there is a reference for COMPARE package. Altough there won't seem any errors, PL/SQL Compiler is angry with code.


SQL> CREATE OR REPLACE PACKAGE COMPARE IS
2 TYPE t_HashTable IS TABLE OF VARCHAR2(512) INDEX BY VARCHAR2(32);
3
4 TYPE t_SQL IS RECORD(
5 SQL_STATEMENT VARCHAR2(4000),
6 BIND_LIST t_HashTable
7 );
8
9 END COMPARE;
10 /

Package created

SQL> CREATE OR REPLACE PACKAGE BODY COMPARE IS
2
3 PROCEDURE Compare IS
4 BEGIN
5 NULL;
6 END Compare;
7
8 END COMPARE;
9 /

Package body created

SQL> CREATE OR REPLACE PACKAGE p_test IS END p_test;
2 /

Package created

SQL> CREATE OR REPLACE PACKAGE BODY p_test IS
2
3 TYPE t_SQLList IS TABLE OF COMPARE.t_SQL INDEX BY VARCHAR2(16);
4
5 PROCEDURE Compare IS
6 BEGIN
7 NULL;
8 END Compare;
9
10 END p_test;
11 /

Warning: Package body created with compilation errors

SQL> show errors;
Errors for PACKAGE BODY SYSADM.P_TEST:

LINE/COL ERROR
-------- -------------------------------------------------------------------
3/30 PLS-00225: subprogram or cursor 'COMPARE' reference is out of scope
3/3 PL/SQL: Item ignored

SQL>



Example3 below CMP.COMPARE and P_TEST.COMPARE2 exists.(i changed the name of COMPARE to COMPARE2 in P_TEST) In P_TEST, there is a reference for COMPARE package. It Works fine.

SQL> CREATE OR REPLACE PACKAGE COMPARE IS
2 TYPE t_HashTable IS TABLE OF VARCHAR2(512) INDEX BY VARCHAR2(32);
3
4 TYPE t_SQL IS RECORD(
5 SQL_STATEMENT VARCHAR2(4000),
6 BIND_LIST t_HashTable
7 );
8
9 END COMPARE;
10 /

Package created

SQL> CREATE OR REPLACE PACKAGE BODY COMPARE IS
2
3 PROCEDURE Compare IS
4 BEGIN
5 NULL;
6 END Compare;
7
8 END COMPARE;
9 /

Package body created

SQL> CREATE OR REPLACE PACKAGE p_test IS END p_test;
2 /

Package created

SQL> CREATE OR REPLACE PACKAGE BODY p_test IS
2
3 TYPE t_SQLList IS TABLE OF COMPARE.t_SQL INDEX BY VARCHAR2(16);
4
5 PROCEDURE Compare2 IS
6 BEGIN
7 NULL;
8 END Compare2;
9
10 END p_test;
11 /

Package body created

SQ

Solution for ORA-01092, ORA-00704, ORA-39700

A few days ago i have created an Oracle database manually(once i have crated an automated DB creation tamplate, it has a bug L). Somehow, some errors occured during creation. Then, when i started open the db, i got some errors. I explained below what the work around is.

orcl oracle@srv:/data06/app/oracle/admin/orcl/scripts> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on Tue Aug 5 23:50:26 2008

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 104857600 bytes
Fixed Size 2046864 bytes
Variable Size 75498608 bytes
Database Buffers 20971520 bytes
Redo Buffers 6340608 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced


SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options


Check the alert log and trace

orcl oracle@srv:/data06/app/oracle/admin/orcl/bdump> more alert_orcl.log
......
.....
Errors in file /data06/app/oracle/admin/orcl/udump/orcl_ora_151225.trc:
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Tue Aug 5 23:45:33 2008
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 151225
ORA-1092 signalled during: alter database open...



Then solution

orcl oracle@srv:/data06/app/oracle/admin/orcl/scripts> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on Tue Aug 5 23:51:59 2008

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 104857600 bytes
Fixed Size 2046864 bytes
Variable Size 75498608 bytes
Database Buffers 20971520 bytes
Redo Buffers 6340608 bytes
Database mounted.
Database opened.
SQL> @/data06/app/oracle/product/10.2/rdbms/admin/catalog.sql;
.....
SQL> @/data06/app/oracle/product/10.2/rdbms/admin/catproc.sql;