How to Upload files to AWS RDS

Introduction

Although it is not possible to log onto an AWS RDS instance, it is still possible to push files onto the RDS server. This script allows files to be pushed up to the RDS server.

Prerequisite

The following prerequisites are need

  • The script requires a functioning Oracle client running on premise or on an AWS EC2 machine
  • The perl code below will need to placed in the SOURCE directory
  • The DBI Perl module needs to be installed on the Oracle Client machine
  • Perl needs to be installed on the Oracle Client machine
  • Uses colour codes. (Only required for prettiness) See here.
  • Uses clear screen code. (Only required for more readability). See here

Usage

oracle@myserver> ./rds_upload.ksh

This script uploads files from the DOWNLOADS directory to the target RDS instance.

Command line options for

Mandatory Parameters
--------------------
-d <Target Database TNS name>      -- Defines the TNS Entry of the Destination DB.  Typically, this is the RDS SID.
-m <Target Database DBA account>   -- Defines the RDS Destination database master account.  Typically, this will be dbmaster.
-p <Target Database DBA password>  -- Defines the RDS Destination database master account password.

Optional Parameters
--------------------
-l                                 -- List all files in the Download directory
-f                                 -- File to be uploaded.

Misc
----
-x                         -- Switch debug on
-h                         -- Display this help

rds_upload.ksh

#!/bin/ksh

$HOME/.profile

my_dir="/u01/app/oracle/DBA/ORACLE/SCRIPTS"

source "$my_dir/colors.ksh"
source "$my_dir/defaults.ksh"

############################################################################
#
# Create errcheck function
#
############################################################################

function errcheck {
  set +x
  if [[ $1 = 0 ]]
  then return
  fi

  SDS_ERR=$1

  case $SDS_ERR in

  1)        echo "Invalid parameter found."
            exit 10;;
  2)        echo "Invalid parameter found. Parameter missing an argument."
            exit 10;;
  *)        echo "Not set yet"
            exit 99;;
  esac
}

############################################################################
#
# SCRIPT START
#
############################################################################

############################################################################
#
#       Script :        rds_upload.ksh
#
#       Date :          12th Jan 2014
#
#       Author :        Mark Ramsay
#
#       Copyright:      www.markramsay.com 2014
#
#       Description :   Export from AWS RDS
#
#       Parameters :    Run the script with -h for details.
#
# History       Date      Name      Reason
#               ----      ----      ------
#               dd/mm/yy  xxx       yyyyyyy
#
############################################################################
#
# Set environment variables
#

#SDS_APP_BUILD_LOC=/u01/app/oracle/DBA/ORACLE/SCRIPTS
SDS_APP_BUILD_LOC=/u01/MARKR/AWS_EXPORT
SDS_LOG_LOC=${SDS_APP_BUILD_LOC}/LOGS
SDS_DOWNLOAD_LOC=${SDS_APP_BUILD_LOC}/DOWNLOADS
SDS_MODULE_LOC=${SDS_APP_BUILD_LOC}/MODULES
SDS_PERL_LOC=$ORACLE_HOME/perl/bin
SDS_DATE=`date '+%d%b%y_%H%M'`

#
# Perform getopts loops
#

while getopts ":d:m:p:f:lxh" opt;
do
case $opt in
d)  SDS_DBNAME_TARGET=$OPTARG;;
m)  SDS_DBMAST_TARGET=$OPTARG;;
p)  SDS_DBMASTPASS_TARGET=$OPTARG;;
f)  SDS_FILE_NAME=$OPTARG;;
l)  SDS_LIST_DIRECTORY=TRUE;;
x)  set -x;;
h)  echo "This script uploads files from the DOWNLOADS directory to the target RDS instance."
    echo ""
    echo "Command line options for $SDS_scriptname"
    echo ""
    echo "Mandatory Parameters"
    echo "--------------------"
    echo "-d <Target Database TNS name>      -- Defines the TNS Entry of the Destination DB.  Typically, this is the RDS SID."
    echo "-m <Target Database DBA account>   -- Defines the RDS Destination database master account.  Typically, this will be dbmaster."
    echo "-p <Target Database DBA password>  -- Defines the RDS Destination database master account password."
    echo ""
    echo "Optional Parameters"
    echo "--------------------"
    echo "-l                                 -- List all files in the Download directory"
    echo "-f                                 -- File to be uploaded."
    echo ""
    echo "Misc"
    echo "----"
    echo "-x                         -- Switch debug on"
    echo "-h                         -- Display this help"
    echo ""
    echo "Notes"
    echo "====="
    echo "The following files need to exist in the DOWNLOADS directory"
    echo "   - AwsFileDownloader.class"
    echo "   - ojdbc6.jar"
    echo ""
    exit 0;;
\?) errcheck 1;;
:)  errcheck 2;;
esac
done

#
# Parameters validation
#
#
# Check mandatory parameter SDS_RELEASE_DATE is set and set to a valid value
#

: ${SDS_DBNAME_TARGET:?"$(echo -ne ${LGREEN})Mandatory parameter -d not specified. Try -h for help$(echo -e ${NORM})"}
: ${SDS_DBMAST_TARGET:?"$(echo -ne ${LGREEN})Mandatory parameter -m not specified. Try -h for help$(echo -e ${NORM})"}
: ${SDS_DBMASTPASS_TARGET:?"$(echo -ne ${LGREEN})Mandatory parameter -p not specified. Try -h for help$(echo -e ${NORM})"}

#
# If -l set, list download directory contents and exit
#

if [ ${SDS_LIST_DIRECTORY} ]
then

echo "The following files exist for uploading"
echo -e ""${CYAN}

#
#Ignore the download code
#

ls ${SDS_DOWNLOAD_LOC} |egrep -v "ojdbc6.jar|AwsFileDownloader.class"
echo -e ""${NORM}
exit

fi

#
# Upload file
#

if [ ${SDS_FILE_NAME} ]
then

#
# Get RDS Endpoint
#

SDS_DBNAME_TARGET_LC=$(echo ${SDS_DBNAME_TARGET} |tr '[:upper:]' '[:lower:]')
SDS_RDS_ENDPOINT=$(grep ${SDS_DBNAME_TARGET_LC} $TNS_ADMIN/tnsnames.ora |grep rds.amazonaws.com |awk -F\= '{print $4}' |awk -F\) '{print $1}' |sed -e 's/ //')

#
# Start download
#

echo "Staring Upload of File ${SDS_FILE_NAME} to ${SDS_DBNAME_TARGET}"
echo "*******************************************************************************************************************"
echo ""
echo "Running command..."
echo ""
echo "perl ${SDS_MODULE_LOC}/aws_move_files_to_rds.pl ${SDS_RDS_ENDPOINT} ${SDS_DBMAST_TARGET} ${SDS_DBMASTPASS_TARGET} ${SDS_DBNAME_TARGET} ${SDS_DOWNLOAD_LOC}/${SDS_FILE_NAME}"
echo ""
echo "You have 10 seconds to exit"
echo ""
sleep 10
${SDS_PERL_LOC}/perl ${SDS_MODULE_LOC}/aws_move_files_to_rds.pl ${SDS_RDS_ENDPOINT} ${SDS_DBMAST_TARGET} ${SDS_DBMASTPASS_TARGET} ${SDS_DBNAME_TARGET} ${SDS_DOWNLOAD_LOC}/${SDS_FILE_NAME}
errcheck $?
echo "Upload Complete"

fi

exit

Perl Code

use DBI;
use warnings;
use strict;

# RDS instance info
my $RDS_PORT=1521;
my $RDS_HOST="$ARGV[0]";
my $RDS_LOGIN="$ARGV[1]/$ARGV[2]";
my $RDS_SID="$ARGV[3]";

#The $ARGV[0] is a parameter you pass into the script
my $dirname = "DATA_PUMP_DIR";
my $fname = $ARGV[4];

my $data = "dummy";
my $chunk = 8192;

my $sql_open = "BEGIN perl_global.fh := utl_file.fopen(:dirname, :fname, 'wb', :chunk); END;";
my $sql_write = "BEGIN utl_file.put_raw(perl_global.fh, :data, true); END;";
my $sql_close = "BEGIN utl_file.fclose(perl_global.fh); END;";
my $sql_global = "create or replace package perl_global as fh utl_file.file_type; end;";

my $conn = DBI->connect('dbi:Oracle:host='.$RDS_HOST.';sid='.$RDS_SID.';port='.$RDS_PORT,$RDS_LOGIN, '') || die ( $DBI::errstr . "\n");

my $updated=$conn->do($sql_global);
my $stmt = $conn->prepare ($sql_open);
$stmt->bind_param_inout(":dirname", \$dirname, 12);
$stmt->bind_param_inout(":fname", \$fname, 12);
$stmt->bind_param_inout(":chunk", \$chunk, 4);
$stmt->execute() || die ( $DBI::errstr . "\n");

open (INF, $fname) || die "\nCan't open $fname for reading: $!\n";
binmode(INF);
$stmt = $conn->prepare ($sql_write);
my %attrib = ('ora_type','24');
my $val=1;
while ($val> 0) {
  $val = read (INF, $data, $chunk);
  $stmt->bind_param(":data", $data , \%attrib);
  $stmt->execute() || die ( $DBI::errstr . "\n") ; };
die "Problem copying: $!\n" if $!;
close INF || die "Can't close $fname: $!\n";
  $stmt = $conn->prepare ($sql_close);
$stmt->execute() || die ( $DBI::errstr . "\n") ;
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License