How do I use Oracle Instant Client to run Data Pump Import or Export for my Amazon RDS for Oracle DB instance?

Last updated: 2021-11-10

I want to use impdp and expdp clients to perform export and import into my Amazon Relational Database Service (Amazon RDS) for Oracle DB instance.

Resolution

Because Amazon RDS for Oracle is a managed database, you don't have access to the host for using the impdp or expdp clients. If you use the Data Pump API to perform imports or exports, you need the PL/SQL package. Oracle Instant Client is a lightweight client that can be installed either in your computer or an Amazon Elastic Compute Cloud (Amazon EC2) instance. Oracle Instant Client includes the impdp and expdp clients that can be used to perform export and import from the command line.

Do the following before using Oracle Instant Client:

  • Verify Doc ID 553337.1 to check whether the binary that you're downloading supports the export of source and target versions. Export from a client with an equal or a later version is usually supported. Import using the client version that's same as the target Amazon RDS major version is supported. For example, if the version of the source instance is 12.2 and the version of the target instance is 18c, then you can install the latest version of Oracle Instant 18c Client.
  • To use Data Pump, install the Tools package on top of the Basic package. To install the packages, see Oracle documentation for Oracle Instant Client.
  • Be sure that the Daylight Saving Time (DST) version of the target RDS instance is equal to or later than that of the source instance. Otherwise, you receive the ORA-39405 error. Use the following query to check the current DST version of your instance. To update the DST version to the latest available version, use the TIMEZONE_FILE_AUTOUPGRADE option.
SELECT * FROM V$TIMEZONE_FILE;

To test Data Pump Import or Export via a database link using Oracle Instant Client, do the following:

1.    Create a test Amazon EC2 instance using the Amazon Linux 2 operating system.

2.    Download the Basic (RPM) package and Tools (RPM) package. In this article, the following RPM downloads are considered:

        oracle-instantclient19.8-basic-19.8.0.0.0-1.x86_64.rpm

        oracle-instantclient19.8-tools-19.8.0.0.0-1.x86_64.rpm

3.    Transfer the binaries to the EC2 instance. For more information, see Transfer files to Linux instances using an SCP client.

4.    Follow the instructions in the Oracle documentation for Install Oracle Instant Client on Linux from RPMs.
This process installs the binaries in the default location /usr/lib/oracle/example-client-version/client64. For example, if you download the binaries for version 19.8, then the default binary location for the installation is /usr/lib/oracle/19.8/client64.

5.    Install the SQL*Plus Package (RPM) package to test the connectivity between the EC2 instance and RDS instance.

6.    Set or update the following environmental variables:

export PATH=$PATH:/usr/lib/oracle/19.8/client64/bin
export LD_LIBRARY_PATH=/usr/lib/oracle/19.8/client64/lib

7.    Create a custom directory to store tnsnames.ora by running a command similar to the following. Set the TNS_ADMIN environment variable to point to the directory.

mkdir -p /root/tnsentries

Be sure that you have the tnsnames.ora file under this directory.

Run the following command to make sure that the TNS_ADMIN environmental variable points to the directory.

export TNS_ADMIN=/root/tnsentries

8.    Include the required TNS entries for the Data Pump Import in the tnsnames.ora file.

9.    Update the inbound rules for the security group of the source and target RDS instances to allow connections from the EC2 instance.

10.    Create test tables in the source RDS instance to perform the export by running queries similar to the following:

CREATE TABLE TEST1 AS SELECT * FROM DBA_TABLES;
CREATE TABLE TEST2 AS SELECT * FROM DBA_OBJECTS;
CREATE TABLE TEST3 AS SELECT * FROM DBA_DATA_FILES;

11.    Run a query similar to the following to create the database link between the source and target databases to be used with the network_link parameter:

CREATE DATABASE LINK sample_conn CONNECT TO example-username IDENTIFIED BY example-password USING '(DESCRIPTION = (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = example-hostname)(PORT = example-port)))(CONNECT_DATA =(SERVICE_NAME = example-service-name)))';

Because you use the database link to connect from the target instance to the source instance, the inbound rules for the source database is set to allow connections of the target instance.

12.    To import data from the source instance to the target instance, run a command similar to the following:

impdp admin@target directory=data_pump_dir logfile=imp_test_tables_using_nw_link.log tables=admin.test1,admin.test2,admin.test3 network_link=sample_conn

You might get an output similar to the following:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "ADMIN"."SYS_IMPORT_TABLE_01":  admin/********@target directory=data_pump_dir logfile=imp_test_tables_using_nw_link.log tables=admin.test1,admin.test2,admin.test3 network_link=sample_conn
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 3.625 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported "ADMIN"."TEST2"                              20634 rows
. . imported "ADMIN"."TEST1"                               1537 rows
. . imported "ADMIN"."TEST3"                                  6 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "ADMIN"."SYS_IMPORT_TABLE_01" successfully completed at Wed Oct 14 23:57:28 2020 elapsed 0 00:01:06

Oracle documentation for Overview of Oracle Data Pump

Oracle documentation for DBMS_DATAPUMP

Did this article help?


Do you need billing or technical support?