Oracle Datapump in AWS using Network Link

Oracle DataPump can be complicated when using the Amazons RDS offering

Using Oracle Datapump when using an Oracle RDS offering from Amazon Web Services can leave you tearing your hair out in frustration.

When using Oracle RDS in Amazon you do not have access to the data dump directory and as such can not use impdp/expdp. If you relay on moving databases around via dmp files as I do then this can cause you a serious problem..

One solution is to fall back on the good old fashioned IMP/EXP commands however there are limitations here such as not being easily able to export selective tables and not being allowed to re-map schemas when importing using IMP. There is also the need to allocate extent on any tables with zero rows to allow them to extract. Its all workable but can cause more work than its worth. For those interested in doing it this way (and it may be the only way you can due to infrastructure limitations) see the post here. Oracle EXP IMP on AWS

For those who want the full availability of datapump and be able to extract the dmp files back to a server of their choice than I will explain how in the below guide.

In this example we will use a windows 2012 instance inside the same VPC as the RDS instance in order to preform the Oracle datapump. This does not have to be the case. You could just as easily use an Oracle 11g install on a server of your choice. The main thing is that it must have access to the foreign database (the one you want to extract from).  If this server is inside the same VPC as the database then it can be easily be made accessible with a security group and stay relatively secure.

To use network_link you follow these simple steps:

1 Create a TNS entry for the foreign database in your tnsnames.ora file

2 Test with tnsping

3 Create a database link to the foreign database

4 Specify the database link as network_link in your expdp or impdp syntax

Step 1 Create TNS entry for foreign database

FOREIGN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxxxxxxxxxxxxxxxxxxx.rds.amazonaws.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

.

Step 2 Use the tnsping command line utility to test connection

From the windows cmd line confirm there is connectivity with the foreign DB by running the tnsping utility.

C:\Users\Administrator>tnsping FOREIGN
TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 21-JAN-2016 17:07:54

Copyright (c) 1997, 2010, Oracle. All rights reserved.

Used parameter files:
D:\app\Administrator\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxxxxxxxxxxxx.rds.amazonaws.com)(PORT = 1521)) (CONNECT_DATA = (SERVER
= DEDICATED) (SERVICE_NAME = orcl)))
OK (30 msec)

Step 3  Create a database link to the foreign database

Log onto the DB as system user

SQL> create database link foreign connect to scott identified by scott using 'foreign';

>Database link created.

SQL> select * from tab@foreign;

TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------

test1 TABLE

Step 4  Specify the database link as network_link in your expdp or impdp syntax

Now its time to start the export using oracle datapump. Here you should be in familiar territory as far as the standard expdp arguments that you use.

C:\Users\Administrator> expdp scott/scott directory=network dumpfile=scott.dmp logfile=scott.log network_link=foreign 

This may be a little slower as it is transferring the data across a network all the more reason to put your extracting DB server in the same VPC as the RDS instance.
Just as a final note, it is also possible to simply datapump the DB between the foreign and the target databases directly without the use of a dump file.

C:\Users\Administrator>impdp scott/scott directory=network logfile=scott.log network_link=foreign

There are many tasks that as an Oracle DBA you will find are done slightly differently when using Amazons RDS Oracle offering. Please see links below for details on common tasks.
Tasks

• SystemEnabling and disabling Restricted SessionFlushing the Shared PoolFlushing the Buffer CacheDisconnecting a Session (for version 11.2.0.3.v1 and later)Killing a SessionRenaming the Global Name (for version 11.2.0.3.v1 and later)Granting Privileges to Non-Master UsersModifying DBMS_SCHEDULER Jobs
• LogsSwitching Online Log filesAdding, Dropping and Resizing Online Redo LogsSetting Force Logging (for version 11.2.0.3.v1 and later)Retaining Archived Redo Logs (for version 11.2.0.2.v7 and later)Setting Supplemental Logging (for version 11.2.0.3.v1 and later)
• DatabasesCreating and Resizing Tablespaces and Data FilesSetting Default TablespaceSetting Default Temporary TablespaceCheckpointing the DatabaseSetting Distributed Recovery (for version 11.2.0.3.v1 and later)Granting SELECT or EXECUTE privileges to SYS Objects (for version 11.2.0.3.v1 and later)Setting the Database Time ZoneWorking with Automatic Workload Repository (AWR)Adjusting Database Links for Use with DB Instances in a VPCCreating New Directories in the Main Data Storage Space (for version 11.2.0.4.v1 and later)Listing and Reading Files in a DB Instance Directory (for version 11.2.0.3.v1 and later)

Source: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.html