How to take Data Pump Backup from a remote database

Oracle Data Pump is a new and unique feature of Oracle Database 11g Release 2. A new public interface package, DBMS_DATAPUMP, provides a server-side infrastructure for fast data and metadata movement between Oracle databases. It is ideal for large databases and data warehousing environments, where high-performance data movement offers significant time savings to database administrators.

Data Pump automatically manages multiple, parallel streams of unload and load for maximum throughput. The degree of parallelism can be adjusted on-the-fly. There are new and easy-to-use Export and Import utilities (expdp and impdp), as well as a web-based Enterprise Manager export/import interface.

Data Pump Export and Import both support a network mode in which the job’s source is a remote Oracle instance. When you perform an import over the network, there are no dump files involved because the source is another database, not a dump file set.

When you perform an export over the network, the source can be a read-only database on another system. Dump files are written out on the local system just as they are with a local (non-networked) export.

 

In the following example, I am showing you step by step procedure to export(expdp) from a remote database:

 

Here in this example, source database is  Oracle 11g and  running on linux and there is another computer running on windows where you need to take backup of source database. At first install oracle 11g on windows machine. After installation of oracle, add source database’s network information to tnsnames.ora file as follows:

SOURCE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.20)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)
)
)

Then create a folder where backup will be taken. For example D:\BACKUP

 

2. You have to create a user on source database(running on linux) to take export backup. You also have to grant appropriate permission to new user for taking backup as follows:

[oracle@testserver ~]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 22 10:12:51 2016

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

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user dpback identified by dpback;

SQL> grant connect, resource to dpback;

SQL> grant EXP_FULL_DATABASE to dpback;

SQL> GRANT CREATE ANY DIRECTORY TO dpback;

 

3. Login to your database(running on windows) as follows:                                                                                                                                                C:\Users\SUMAN>sqlplus

Enter user-name: sys as sysdba
Enter password:

Create a dblink to source database as follows:

SQL> CREATE PUBLIC DATABASE LINK SOURCE
CONNECT TO dpback
IDENTIFIED BY dpback
USING ‘SOURCE’;

You need to create a user to take expdp backup just like you did at your source database

SQL> create user dpback identified by dpback;

SQL> grant connect, resource to dpback;

SQL> grant EXP_FULL_DATABASE to dpback;

SQL> GRANT CREATE ANY DIRECTORY TO dpback;

 

4. Connect as dpback user

SQL> conn dpback
Enter password:
Connected.
SQL> create directory dp_dir as ‘D:\BACKUP’;

Directory created.

5. Now you can take expdp backup by running following command

C:\Users\SUMAN>expdp dpback/dpback directory=dp_dir full=Y dumpfile=full.dmp logfile=full.log network_link=SOURCE

Here network_link=SOURCE is important. SOURCE is the dblink name that I create at first.

You can add compression, parallel etc parameter for better performance. You can also create a batch file to automate backup as follows:

expdp dpback/dpback directory=dp_dir full=Y dumpfile=full_epayment%date:~-4%_%date:~4,2%_%date:~7,2%_%time:~0,2%_%time:~3,2%.dmp logfile=full_epayment%date:~-4%_%date:~4,2%_%date:~7,2%_%time:~0,2%_%time:~3,2%.log compression=all network_link=SOURCE

 

To import you can use following command:

impdp system/**** directory=dp_dir full=Y dumpfile=FULL_EPAYMENT.DMP logfile=imp_full_epayment.log EXCLUDE=SCHEMA:\”LIKE \’SYS%\’\”

Before executing impdp command, make sure that

1) IMP_FULL_DATABASE is granted to the import user

2) All the tablespaces of source database has been created.

 

 

 

‹ How do you pass username/password to start Oracle WebLogic server in Unix

Oracle 11g : Login problem(ORA-01017 Invalid Username/Password) from older client ›

Posted in Backup and Restore, DBA