Search code examples
oracleimpdpexpdp

Downloading oracle RDS table data to a local database

I have an Amazon Oracle RDS database. I would like to export the RDS table and import it to my local database. The kicker is that it includes a NCLOB column. The local system is Win10 running Cygwin.

I run expdp to capture the data:

$ expdp xlat@int_rds/*****tables=TEXT_POOL_XLAT file=int_TEXT_POOL_XLAT.expdp

Export: Release 11.2.0.1.0 - Production on Mon Feb 18 11:37:30 2019

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

Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "file=int_TEXT_POOL_XLAT.expdp" Location: Command Line, Replaced with: "dumpfile=int_TEXT_POOL_XLAT.expdp"
Legacy Mode has set reuse_dumpfiles=true parameter.
Legacy Mode has set nologfile=true parameter.
Starting "XLAT"."SYS_EXPORT_TABLE_01":  xlat/********@int_rds tables=TEXT_POOL_XLAT dumpfile=int_TEXT_POOL_XLAT.expdp reuse_dumpfiles=true nologfile=true
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 51.68 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "XLAT"."TEXT_POOL_XLAT"                     32.50 MB  137850 rows
Master table "XLAT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for XLAT.SYS_EXPORT_TABLE_01 is:
  /rdsdbdata/datapump/int_TEXT_POOL_XLAT.expdp
Job "XLAT"."SYS_EXPORT_TABLE_01" successfully completed at Mon Feb 18 17:37:29 2019 elapsed 0 00:00:04

So far so good. expdp dumps the file into the Oracle DATA_PUMP_DIR and I use a script to download the data with the sqlplus commands:

sqlplus -s $DBusername/$DBpassword@$database >/dev/null <<EOF 
set colsep ,
set pagesize 0
set trimspool on
set headsep off
set linesize 8000
set termout off
SET FEEDBACK OFF
spool $filename $append
select * from table (rdsadmin.rds_file_util.read_text_file(p_directory => 'DATA_PUMP_DIR', p_filename  => '$DPfilename'));
quit
EOF

Data gets downloaded. But when I run impdp I get:

$ impdp xlat/f0nature1931@local tables=TEXT_POOL_XLAT dumpfile=int_TEXT_POOL_XLAT.expdp directory='DATA_PUMP_DIR'

Import: Release 11.2.0.1.0 - Production on Mon Feb 18 11:58:32 2019

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

Connected to: Personal Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31619: invalid dump file "C:\app\waynej/admin/orcl/dpdump/int_TEXT_POOL_XLAT.expdp"

Am I missing something? I don't think it's a CR/LF issue since the script downloads without any translations.

Thanks in advance.

Solution

  • RDS procedure rdsadmin.rds_file_util.read_text_file to read ONLY a text file. EXPDP DUMP file is not text file, it is binary file.

    File access for the RDS instance is forbidden. Access to the DATA_PUMP_DIR directory only through the db_link and use DBMS_FILE_TRANSFER package.

    1. Create database link on RDS instance between RDS DB and your local Oracle.
    2. Copy the dump files from RDS instance to local Oracle DB uses the DBMS_FILE_TRANSFER.PUT_FILE via database link
    3. Import dump files into local database impdp xlat/f0nature1931@local tables=TEXT_POOL_XLAT dumpfile=int_TEXT_POOL_XLAT.expdp directory='DATA_PUMP_DIR'

    If you do not have the opportunity to make a link between your local database and the RDS Oracle, you can export data in two more ways.

    1 You can do the export of data using the old exp utility on the local PC, this utility also creates export files .dmp, but for a different format. The format is not compatible with impdp expdp. The exp imp utility can connect over the SQL*NET network to the target database as client-server. This utility is obsolete and has less performance. The dmp file is not created on the server, as when running the utility expdp. The dmp file is written on the side where the utility exp is run (server or client)

    $ORACLE_HOME/bin/exp parfile=parfile_exp_full FILE=export.dmp LOG=export.log

    And then do the data import using the imp to local Oracle instance.

    $ORACLE_HOME/bin/imp parfile=parfile_imp_full FILE=export.dmp LOG=import.log

    2

    You can export the data to an CSV file using the sqlplus 

    $ORACLE_HOME/bin/sqlplus -s user/pass@rds_amazon  @csv2.sql.

more csv2.sql 

set heading off
set termout OFF
SET FEEDBACK OFF
SET TAB OFF
set pause off
set verify off
SET UNDERLINE OFF
set trimspool on
set echo off
set linesize 1000
set pagesize 0
set wrap off
spool test2.csv
select code||','||name||','||code_rail from alexs.all_station;
spool off
exit;

    And then make the data import to Local Oracle instance using the utility sqlldr or SQL Developer.

    SQL Developer for importing from Excel