How do I use the Oracle Instant Client to run a Data Pump import or export for my Amazon RDS for Oracle DB instance?

10 minute read
0

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

Short description

There are several ways to perform an export or import into an Amazon RDS for Oracle DB instance.

After setting up the environment, you can:

  • Import tables from a source Oracle RDS instance to a target Oracle RDS instance.
  • Export data from an Oracle RDS instance and create a dump file locally on an Amazon Elastic Compute Cloud (EC2) instance or remote host.
  • Export data from an Oracle RDS instance and store the dump file on the RDS host.
  • Import a dump file located on an RDS host.
  • Transfer dump files between your RDS for Oracle DB instance and an Amazon Simple Storage Service (Amazon S3) bucket using the S3 Integration option.

Resolution

To deliver a managed service experience, host-level access to make use of the impdp and expdp utilities on the RDS host isn't allowed. An alternate option is to use the Data Pump API (DBMS_DATAPUMP) to perform the imports or exports. However, you can perform this task by using the Data Pump utilities on a remote host.

Oracle Instant Client is a lightweight client that you can install on your computer or on an Amazon EC2 instance. Oracle Instant Client includes the impdp and expdp utilities that you can use to perform the export and import operations from the command line.

Prerequisites

Do the following before using the Oracle Instant Client:

  • Review Doc ID 553337.1 to check whether the binary that you're downloading is compatible with the source and target versions. Exporting from a client with an equal or a later version is usually supported. Importing using a client version that's the 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 19c, then you can install the latest 19c version of the Oracle Instant Client.
  • To use Data Pump, install the Tools package on top of the Basic package. To install the packages, see the Oracle Instant Client documentation.
  • Make 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 get the following error while running the import: ORA-39405. Use the following query to check the current DST version of your instance. To update the DST version to the latest available version in an Oracle RDS instance, use the TIMEZONE_FILE_AUTOUPGRADE option.
SELECT * FROM V$TIMEZONE_FILE;

To test the Data Pump Import or Export from a database link using an 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, Tools (RPM) package, and the SQL*Plus (RPM) package. In this article, the following RPM's are the latest available downloads:

  • oracle-instantclient19.16-basic-19.16.0.0.0-1.x86_64.rpm
  • oracle-instantclient19.16-tools-19.16.0.0.0-1.x86_64.rpm
  • oracle-instantclient19.16-sqlplus-19.16.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 Installing Oracle Instant Client Using RPM. 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.16, then the default binary location for the installation is /usr/lib/oracle/19.16/client64/bin.

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

Example:

sudo yum install oracle-instantclient19.16-sqlplus-19.16.0.0.0-1.x86_64.rpm

6.    Set or update the following environmental variables, as seen in this example:

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

7.    Create your configuration files, such as tnsnames.ora and sqlnet.ora, in the following location: /usr/lib/oracle/ example-client-version/client64/lib/network/admin. In this example, the location will be: /usr/lib/oracle/19.16/client64/lib/network/admin.

Setting up the environment

1.    Add the required TNS entries for the Data Pump Import or Export to the tnsnames.ora file.

Example of an entry in the tnsnames.ora file:

target = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) 
   (HOST = xxxx.rxrxrmwt1x471wi.eu-central-1.rds.amazonaws.com) (PORT = 1521)))(CONNECT_DATA = (SID = orcl)))

For more information, see Configuring SQL*Plus to use SSL with an Oracle DB instance.

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

  2. 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;

Import tables from a source Oracle RDS instance to a target Oracle RDS instance

To import the tables from a source Oracle RDS instance into a target Oracle RDS instance, do the following:

1.    Run a query similar to the example below to create a database link between the source and target databases. This is 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)))';

The database link connecting the target instance to the source instance has inbound rules that allow connections of the target instance.

2.    Complete the prerequisites and setup outlined in this article before running the impdp command.

3.    Log in to the EC2 instance that contains the Oracle instant client.

4.    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

Example output:

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

Export data from an Oracle RDS instance and create a dump file locally on a remote host

To export the data from an Oracle RDS instance and create a dump file locally, do the following:

  • Install an Oracle database on an EC2 instance or remote host. In the following example, Oracle XE is installed on a Windows EC2 instance. For more information on Oracle XE, see Oracle Database XE Quick Start.
  • Update the inbound rules for the security group of the source RDS instances to allow connections from the EC2 instance.

1.    Log in to the XE database with an Oracle Client, such as SQL*Plus. Then, create a directory on the Oracle XE database. This directory will reference the directory where you want to create the dump file on the EC2 instance. Run a query similar to the following:

create directory exp_dir as 'C:\TEMP\';

2.    On the XE database, create a database link to your source RDS database using a command similar to this example:

CREATE DATABASE LINK exp_rds CONNECT TO admin identified by example_password USING '(DESCRIPTION = (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = example-hostname)(PORT=example-port)))(CONNECT_DATA =(SERVICE_NAME = example-service-name)))';

3.    Test the database link similar to the following:

select sysdate from dual@exp_rds;

4.    To create the dump file on the EC2 instance, run a command similar to the following:

expdp system network_link=exp_rds directory=exp_dir dumpfile=table_dump.dmp logfile=expdp_table_dump.log tables=admin.test1,admin.test2,admin.test3

Example output:

Connected to: Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database. Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/********
    network_link=exp_rds directory=exp_dir dumpfile=table_dump.dmp logfile=expdp_table_dump.log tables=admin.test1,admin.test2,admin.test3 Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
    Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE . . exported "ADMIN"."TEST2" 2.713 MB  23814 rows. . exported "ADMIN"."TEST1"  677.1 KB  1814 rows. . exported "ADMIN"."TEST3"  15.98 KB  5 rows Master table "SYSTEM"."SYS_EXPORT_TABLE_01"
    successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:   C:\TEMP\TABLE_DUMP.DMP
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Wed Aug 24 18:15:25 2022 elapsed 0 00:00:18

Export data from an Oracle RDS instance and store the dump file on the RDS host

To export data from an Oracle RDS instance and store the dump file on the RDS host, do the following:

1.    Complete the prerequisites and setup outlined in this article before running the expdp command.

2.    Log in to the EC2 instance that contains the Oracle instant client.

3.    Create a dump file on the RDS instance by running a command similar to the following:

expdp admin@target dumpfile=table_dump.dmp logfile=expdp_table_dump.log tables=admin.test1,admin.test2,admin.test3

Example output:

Export: Release 19.0.0.0.0 - Production on Wed Aug 24 16:18:58 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle and/or its affiliates.  All rights reserved.
Password:
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "ADMIN"."SYS_EXPORT_TABLE_01":  admin/********@target dumpfile=table_dump.dmp logfile=expdp_table_dump.log tables=admin.test1,admin.test2,admin.test3
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "ADMIN"."TEST2"                             2.713 MB   23814 rows
. . exported "ADMIN"."TEST1"                             677.1 KB    1814 rows
. . exported "ADMIN"."TEST3"                             15.98 KB       5 rows
Master table "ADMIN"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ADMIN.SYS_EXPORT_TABLE_01 is:
  /rdsdbdata/datapump/table_dump.dmp
Job "ADMIN"."SYS_EXPORT_TABLE_01" successfully completed at Wed Aug 24 16:19:20 2022 elapsed 0 00:00:15

Import dump file located on the RDS host

To import a dump file that is stored on the RDS host, do the following:

Note: In this example, the data exists in the DATA_PUMP_DIR on the RDS host.

1.    Complete the prerequisites and setup outlined in this article before running the impdp command.

2.    Log in to the EC2 instance that contains the instant client.

3.    Run a command similar to the following on the EC2 instance to import the dump file located on the RDS host.

Note: In this example, the tables are truncated before the data is imported.

impdp admin@target directory=DATA_PUMP_DIR dumpfile=table_dump.dmp logfile=impdp_table_dump.log tables=admin.test1,admin.test2,admin.test3 table_exists_action=truncate

Example output:

import: Release 19.0.0.0.0 - Production on Thu Sep 8 13:24:44 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle and/or its affiliates.  All rights reserved.
Password:
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "ADMIN"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ADMIN"."SYS_IMPORT_TABLE_01":  admin/********@target directory=DATA_PUMP_DIR dumpfile=table_dump.dmp logfile=impdp_table_dump.log tables=admin.test1,admin.test2,admin.test3 table_exists_action=truncate
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "ADMIN"."TEST2" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Table "ADMIN"."TEST3" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Table "ADMIN"."TEST1" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ADMIN"."TEST2"                             2.749 MB   24059 rows
. . imported "ADMIN"."TEST1"                             677.2 KB    1814 rows
. . imported "ADMIN"."TEST3"                             15.98 KB       5 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 Thu Sep 8 13:24:54 2022 elapsed 0 00:00:06

Transferring dump files between your RDS for Oracle DB instance and an Amazon S3 bucket

To transfer dump files between an RDS Oracle DB instance and an Amazon S3 bucket, you can use the S3 Integration option. For more information, see Transferring files between Amazon RDS for Oracle and an Amazon S3 bucket.


Related information

Overview of Oracle Data Pump

DBMS_DATAPUMP

AWS OFFICIAL
AWS OFFICIALUpdated a year ago