How to Transfer file from AWS RDS for Oracle to Other Oracle Server

I have shown here how to transfer file from Oracle RDS database to Non RDS Oracle server to access the file. In that example we will transfer file from Source (Oracle RDS) to Target (Non RDS Oracle server).


Steps:

Target (Non RDS Oracle Server):

        1.      Create users in target (If you already don’t have users to connect from source)

        2.      Create directory if don’t exist (Here I have used existing directory  'DATA_PUMP_DIR')

        3.      Grant the user write privilege on the directory for transferring file from source

Source (Oracle RDS):

        4.      Create database link to target

        5.      Transfer the file using UTL_FILE package 

 

Target (Non RDS Oracle Server):

# sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Feb 19 15:15:53 2019

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

Connected to:

Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

SQL> create user transferfile identified by tr;

User created.

 

SQL> grant create session,resource to transferfile;

Grant succeeded.

 

SQL> select DIRECTORY_PATH from all_directories where DIRECTORY_NAME='DATA_PUMP_DIR';

DIRECTORY_PATH

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

/oracle/TARGET/oradata/data/data_pump_dir

 

SQL> grant read,write on directory DATA_PUMP_DIR to transferfile;

Grant succeeded.

 

Source (Oracle RDS):

select * from table(rdsadmin.rds_file_util.listdir('DATA_PUMP_DIR')) where filename='Export_full.csv';



create database link to_transfer

  connect to transferfile identified by tr

  using '(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = XXX.XX.XXXX.XXX)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = TARGET.US.oracle.com)))';

 

 

declare

  lfh utl_file.file_type;

  rfh utl_file.file_type@to_transfer;

  ldata raw(32767);

begin

  -- open file handles

  lfh := utl_file.fopen(location=>'DATA_PUMP_DIR', filename=>'Export_full.csv', open_mode=>'rb');

  rfh := utl_file.fopen@to_transfer(location=>'DATA_PUMP_DIR', filename=>'Export_full.csv', open_mode=>'wb');

  -- iterate local file and write it to remote

  begin

    loop

      begin

        utl_file.get_raw(lfh, ldata, 32767);

        utl_file.put_raw@to_transfer(rfh, ldata, true);

      exception

        when no_data_found then

          exit;

      end;

    end loop;

  end;

  -- close file handles

  utl_file.fclose(lfh);

  utl_file.fclose@to_transfer(rfh); 

exception

  -- exception handling   

when others then

    utl_file.fclose(lfh);

    utl_file.fclose@to_transfer(rfh);

    raise;

end;

/

 

Now check the file in Target:

# pwd

/oracle/TARGET/oradata/data/data_pump_dir

# ls -lrt

-rw-rw-r--. 1 target target 3588901688 Feb 19 16:58 Export_full.csv


Comments