How to Download Files from AWS RDS
|
Table of Contents
|
Introduction
Although it is not possible to log onto an AWS RDS instance, it is still possible to download files from the RDS server. This script allows files to be download from 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 java code below will need to compiled and placed in the DOWNLOAD directory
- The OJDBC driver will need to placed in the DOWNLOAD directory
- Uses colour codes. (Only required for prettiness) See here.
- Uses clear screen code. (Only required for more readability). See here
Usage
oracle@myserver> ./rds_download.ksh
This script downloads files from an AWS RDS Instance.
Command line options for
Mandatory Parameters
--------------------
-d <Source Database TNS name> -- Defines the TNS Entry of the Destination DB. Typically, this is the RDS SID.
-m <Source Database DBA account> -- Defines the RDS Destination database master account. Typically, this will be dbmaster.
-p <Source Database DBA password> -- Defines the RDS Destination database master account password.
Optional Parameters
--------------------
-l -- List all files in DATA_PUMP_DIR.
-f -- File to be downloaded.
-z <Oracle Directory Name> -- Defines the Oracle directory to look in. The default is DATA_PUMP_DIR.
Misc
----
-x -- Switch debug on
-h -- Display this help
Notes
=====
The following files need to exist in the DOWNLOADS directory
- AwsFileDownloader.class
- ojdbc6.jar
rds_download.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_download.ksh
#
# Date : 12th Jan 2014
#
# Author : Mark Ramsay
#
# Copyright: www.markramsay.com 2014
#
# Description : Download files 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_DATE=`date '+%d%b%y_%H%M'`
SDS_DIRECTORY_NAME=DATA_PUMP_DIR
#
# Perform getopts loops
#
while getopts ":d:m:p:f:z:lxh" opt;
do
case $opt in
d) SDS_DBNAME_SOURCE=$OPTARG;;
m) SDS_DBMAST_SOURCE=$OPTARG;;
p) SDS_DBMASTPASS_SOURCE=$OPTARG;;
f) SDS_FILE_NAME=$OPTARG;;
l) SDS_LIST_DIRECTORY=TRUE;;
z) SDS_DIRECTORY_NAME=$OPTARG;;
x) set -x;;
h) echo "This script downloads files from an AWS RDS Instance."
echo ""
echo "Command line options for $SDS_scriptname"
echo ""
echo "Mandatory Parameters"
echo "--------------------"
echo "-d <Source Database TNS name> -- Defines the TNS Entry of the Destination DB. Typically, this is the RDS SID."
echo "-m <Source Database DBA account> -- Defines the RDS Destination database master account. Typically, this will be dbmaster."
echo "-p <Source Database DBA password> -- Defines the RDS Destination database master account password."
echo ""
echo "Optional Parameters"
echo "--------------------"
echo "-l -- List all files in DATA_PUMP_DIR."
echo "-f -- File to be downloaded."
echo "-z <Oracle Directory Name> -- Defines the Oracle directory to look in. The default is DATA_PUMP_DIR."
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_SOURCE:?"$(echo -ne ${LGREEN})Mandatory parameter -d not specified. Try -h for help$(echo -e ${NORM})"}
: ${SDS_DBMAST_SOURCE:?"$(echo -ne ${LGREEN})Mandatory parameter -m not specified. Try -h for help$(echo -e ${NORM})"}
: ${SDS_DBMASTPASS_SOURCE:?"$(echo -ne ${LGREEN})Mandatory parameter -p not specified. Try -h for help$(echo -e ${NORM})"}
#
# If -l set, list directory contents and exit
#
if [ ${SDS_LIST_DIRECTORY} ]
then
sqlplus -S ${SDS_DBMAST_SOURCE}/${SDS_DBMASTPASS_SOURCE}@${SDS_DBNAME_SOURCE} <<EOF
set lines 180
col FILENAME for A50
col TYPE for A20
select * from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('${SDS_DIRECTORY_NAME}')) where type != 'directory' order by mtime
/
EOF
exit
fi
#
# Download file
#
if [ ${SDS_FILE_NAME} ]
then
#
# Get RDS Endpoint
#
SDS_DBNAME_SOURCE_LC=$(echo ${SDS_DBNAME_SOURCE} |tr '[:upper:]' '[:lower:]')
SDS_RDS_ENDPOINT=$(grep ${SDS_DBNAME_SOURCE_LC} $TNS_ADMIN/tnsnames.ora |grep rds.amazonaws.com |awk -F\= '{print $4}' |awk -F\) '{print $1}' |sed -e 's/ //')
#
# Start download
#
echo "Staring Download of File ${SDS_FILE_NAME} from ${SDS_DBNAME_SOURCE}"
echo "*******************************************************************************************************************"
echo ""
echo "Running command java -cp .:ojdbc6.jar AwsFileDownloader ${SDS_RDS_ENDPOINT} 1521 ${SDS_DBNAME_SOURCE} ${SDS_DBMAST_SOURCE} ${SDS_DBMASTPASS_SOURCE} ${SDS_FILE_NAME} ${SDS_DIRECTORY_NAME}"
echo ""
echo "You have 10 seconds to exit"
echo ""
sleep 10
cd ${SDS_DOWNLOAD_LOC}
java -cp .:ojdbc6.jar AwsFileDownloader ${SDS_RDS_ENDPOINT} 1521 ${SDS_DBNAME_SOURCE} ${SDS_DBMAST_SOURCE} ${SDS_DBMASTPASS_SOURCE} ${SDS_FILE_NAME} ${SDS_DIRECTORY_NAME}
#java -cp .:ojdbc6.jar AwsFileDownloader ${SDS_RDS_ENDPOINT} 1521 ${SDS_DBNAME_SOURCE} ${SDS_DBMAST_SOURCE} ${SDS_DBMASTPASS_SOURCE} ${SDS_FILE_NAME}
fi
exit
Java Code
// Downloads the named file from RDS DATA_PUMP_DIR to create a local copy
// Usage: AwsFileDownloader host port service username password filename
// In RDS, the "service" is also known as "DB Name"
import java.io.*;
import java.sql.*;
import java.util.*;
import oracle.jdbc.*;
import oracle.sql.*;
public class AwsFileDownloader
{
protected Connection gConn;
private void doDownload(String pFileName, String pDirName)
throws Exception
{
byte[] buf = new byte[1048576]; // Download in 1Mb chunks
int iLen=0;
oracle.sql.BFILE b = null;
OracleStatement stmt = (OracleStatement) (gConn.createStatement());
OracleResultSet orSet = (OracleResultSet) (stmt.executeQuery ("SELECT BFILENAME('" + pDirName + "','" + pFileName + "') FROM dual"));
if(orSet.next())
{
b = orSet.getBFILE(1);
}
orSet.close();
stmt.close();
if(null != b)
{
if(b.fileExists())
{
long l = b.length();
System.out.printf("BFILE exists, size %d bytes%n", l);
BufferedOutputStream bfOut = new BufferedOutputStream(new FileOutputStream(pFileName));
b.openFile();
InputStream isIn = b.getBinaryStream();
BufferedInputStream bfIn = new BufferedInputStream(isIn);
while(-1 != (iLen = bfIn.read(buf)))
{
System.out.printf("Read %d bytes%n", iLen);
if(iLen > 0)
{
bfOut.write(buf, 0, iLen);
}
}
bfIn.close();
isIn.close();
bfOut.close();
b.closeFile();
}
else
{
throw new Exception("File does not exist");
}
}
else
{
throw new Exception("Unable to create BFILE");
}
}
public AwsFileDownloader(String pHost, String pPort, String pService, String pUsername, String pPassword, String pFileName, String pDirName)
throws Exception
{
Class.forName("oracle.jdbc.driver.OracleDriver");
gConn = DriverManager.getConnection(
String.format("jdbc:oracle:thin:@%s:%s:%s", pHost, pPort, pService),
pUsername,
pPassword);
System.out.println("Connected to RDS");
doDownload(pFileName, pDirName);
gConn.close();
System.out.println("Download complete");
}
// Usage: AwsFileDownloader host port service username password filename
public static void main(String[] argv)
throws Exception
{
if(7 == argv.length)
{
new AwsFileDownloader(argv[0], argv[1], argv[2], argv[3], argv[4], argv[5], argv[6]);
}
else
{
System.err.println("Usage: AwsFileDownloader host port service username password filename dirname");
System.err.println("E.g.");
System.err.println("AwsFileDownloader abc.def.ap-southeast-2.rds.amazonaws.com 1521 THEDB scott tiger file.dmp data_pump_dir");
}
}
}
How to Compile the Java
oracle@myserver> cd SOURCE
oracle@myserver> /u01/app/oracle/product/12.1.0/client_1/jdk/bin/javac -cp /u01/app/oracle/product/12.1.0/client_1/instantclient/ojdbc6.jar AwsFileDownloader.java
oracle@myserver> cd ../DOWNLOAD
oracle@myserver> cp ../SOURCE/AwsFileDownloader.class .
oracle@myserver> cp /u01/app/oracle/product/12.1.0/client_1/instantclient/ojdbc6.jar .
