exp & expdp: Export file to remote server

Data Pump

In this article I will show you how to perform an Export but creating the file in a remote server. Sometimes we have to deal with these kind of “challenges” since we don’t have always a free access to databases and servers. Sometimes we have to reach the database from we have to extract the export only through a tns entry because we don’t have access to that server, our account is not allowed to Login to the server or any other reason. I will show you 2 methods to accomplish this challenge.

In This example I will use the following servers:

Source Server: orcl12c.oraworld.com/192.168.8.120/orcl12c
Target Server: orcl11g.oraworld.com/192.168.8.113/orcl11g

In the Source Database (db12c) I have the Schema “DGOMEZ” created and that schema has the table “MYTABLE”. We will work with this schema.

[[email protected] ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Tue Feb 10 22:24:18 2015

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> select table_name from dba_tables where owner=’DGOMEZ’;

TABLE_NAME

MYTABLE

SQL>

Prerequisite:
The most important prerequisite is to have a tns entry in the Target Server (db11g) pointing to the database from we will extract the export , in other words from the Source Server (db12c).

The following tns entry is in my Target Server (db11g):

[[email protected] ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora

tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2/db_1//network/admin/tnsnames.ora

Generated by Oracle configuration tools.

ORCL12C =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = db12c)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl2)
)
)

Method 1: Using exp

Using this method the advantage is that we need only the tns entry and that’s all. This method is the easiest one.

Currently the directory where we will create the file is empty (in Target Server):

[[email protected] target]$ pwd
/home/oracle/target
[[email protected] target]$ ls -ltr
total 0
[[email protected] target]$

Let’s create the export:

[[email protected] target]$ exp [email protected] file=/home/oracle/target/dgomez.dmp log=/home/oracle/target/dgomez.log owner=dgomez

Export: Release 11.2.0.4.0 – Production on Thu Feb 12 23:40:57 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Password:

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)

About to export specified users …
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user DGOMEZ
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user DGOMEZ
About to export DGOMEZ’s objects …
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export DGOMEZ’s tables via Conventional Path …
. . exporting table MYTABLE 0 rows exported
EXP-00091: Exporting questionable statistics.
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.
[[email protected] target]$

Verification: You can see that the directory in Target file now has the dump file and its log.

[[email protected] target]$ pwd
/home/oracle/target
[[email protected] target]$ ls -ltr
total 20
-rw-r–r– 1 oracle oinstall 1416 Feb 12 23:47 dgomez.log
-rw-r–r– 1 oracle oinstall 16384 Feb 12 23:47 dgomez.dmp
[[email protected] target]$

Method 2: Using expdp

This method has the advantage that it uses data pump which is better than exp regarding performance and other things. The disadvantage is that we must have one database running in Target Database because we must have one Oracle Directory.

In my Target database I will use the following Instance in order to create the Oracle Directory:

[[email protected] target]$ ps -ef |grep pmon
oracle 2523 2265 0 23:49 pts/1 00:00:00 grep pmon
grid 3448 1 0 Feb11 ? 00:00:16 asm_pmon_+ASM
oracle 3770 1 0 Feb11 ? 00:00:19 ora_pmon_orcl
[[email protected] target]$

Creating the Oracle Directory:

SQL> create directory dumptarget as ‘/home/oracle/target’;

Directory created.

SQL> grant read,write on directory dumptarget to system;

Grant succeeded.

SQL>

We also need a database link created in the database running in the Target Server:

SQL> create public database link dumptarget connect to system identified by manager1 using ‘ORCL12C’;

Database link created.

Let’s see the directory in Target Server that we will use to store the dump files:

[[email protected] target]$ pwd
/home/oracle/target
[[email protected] target]$ ls -ltr
total 0
[[email protected] target]$

As you can see it is empty.

Now, let’s do the export:

Network_link: The database link.
Directory: The Oracle Directory.

[[email protected] target]$ expdp system/manager1 directory=dumptarget dumpfile=dgomez.dmp logfile=dgomez.log network_link=dumptarget schemas=dgomez

Export: Release 11.2.0.4.0 – Production on Fri Feb 13 00:08:49 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting “SYSTEM”.”SYS_EXPORT_SCHEMA_01″: system/ directory=dumptarget dumpfile=dgomez.dmp logfile=dgomez.log network_link=dumptarget schemas=dgomez
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
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported “DGOMEZ”.”MYTABLE” 0 KB 0 rows
Master table “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded


Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/target/dgomez.dmp
Job “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully completed at Fri Feb 13 00:10:36 2015 elapsed 0 00:01:46

Verification: You can see now that the files were created in the Target Server.

[[email protected] target]$ pwd
/home/oracle/target
[[email protected] target]$ ls -ltr
total 180
-rw-r–r– 1 oracle asmadmin 1400 Feb 13 00:10 dgomez.log
-rw-r—– 1 oracle asmadmin 176128 Feb 13 00:10 dgomez.dmp
[[email protected] target]$