How to remove Files from AWS RDS

Introduction

On RDS you do not have access to the server. Therefore, it is not possible to log on and remove files. For example, removing export files. The following script can be used to remove such files.

Prerequisite

The following prerequisites are need

  • The script requires a functioning Oracle client running on premise or on an AWS EC2 machine
  • Uses colour codes. (Only required for prettiness) See here.
  • Uses clear screen code. (Only required for more readability). See here

Script Usage

oracle@myserver> ./rds_remove_files.ksh -h

This script removes files stored in DATA_PUMP_DIR in RDS.

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 <file1>,<file2>,<etc>           -- Remove the files specified.
-a                                 -- Remove all files from DATA_PUMP_DIR.

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

rds_remove_files.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_remove_files.ksh
#
#       Date :          12th Jan 2014
#
#       Author :        Mark Ramsay
#
#       Copyright:      www.markramsay.com 2014
#
#       Description :   Remove files from 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_PARAMETER_LOC=${SDS_APP_BUILD_LOC}/PARAMETERS
SDS_USED_PARAMETER_LOC=${SDS_APP_BUILD_LOC}/USED_PARAMS
SDS_LOG_LOC=${SDS_APP_BUILD_LOC}/LOGS
SDS_PARAMETER_TMPL=${SDS_APP_BUILD_LOC}/TEMPLATES
SDS_VALID_ENVS='DEV[0-9][0-9]|TST[0-9][0-9]|INT[0-9][0-9]|SUP[0-9][0-9]|PRF][0-9][0-9]|PFL[0-9][0-9]|PEF[0-9][0-9]|PRD[0-9][0-9]'
SDS_DATE=`date '+%d%b%y_%H%M'`

#
# Perform getopts loops
#

while getopts ":d:m:p:f:laxh" opt;
do
case $opt in
d)  SDS_DBNAME_SOURCE=$OPTARG;;
m)  SDS_DBMAST_SOURCE=$OPTARG;;
p)  SDS_DBMASTPASS_SOURCE=$OPTARG;;
l)  SDS_LIST_DIRECTORY=TRUE;;
f)  SDS_FILE_LIST=$OPTARG;;
a)  SDS_REMOVE_ALL=TRUE;;
x)  set -x;;
h)  echo "This script removes files stored in DATA_PUMP_DIR in RDS."
    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 <file1>,<file2>,<etc>           -- Remove the files specified."
    echo "-a                                 -- Remove all files from DATA_PUMP_DIR."
    echo ""
    echo "Misc"
    echo "----"
    echo "-x                         -- Switch debug on"
    echo "-h                         -- Display this help"
    echo ""
    exit 0;;
\?) errcheck 1;;
:)  errcheck 2;;
esac
done

#
# 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
echo "Getting a list of files.  Please wait.."
sqlplus -S ${SDS_DBMAST_SOURCE}/${SDS_DBMASTPASS_SOURCE}@${SDS_DBNAME_SOURCE} <<EOF
set lines 180
set feedback off
col FILENAME for A50
col TYPE for A20
select * from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR')) where type != 'directory' order by mtime
/
EOF
exit
fi

#
# If -f set, remove the files specified and exit
#

if [ ${SDS_FILE_LIST} ]
then

#
# List files to be removed
#

echo "Staring Removal of Files for Database ${SDS_DBNAME_SOURCE} from DATA_PUMP_DIR"
echo "*****************************************************************************"
echo ""
echo "The following files will be removed.."
echo ""
for n in $(echo ${SDS_FILE_LIST} |sed -e 's/,/ /')
do
echo -e ${RED}${n}${NORM}
done
echo ""
echo "You have 10 seconds to exit"
echo ""
sleep 10

#
# Remove Files
#

for f in $(echo ${SDS_FILE_LIST} |sed -e 's/,/ /')
do

sqlplus -S ${SDS_DBMAST_SOURCE}/${SDS_DBMASTPASS_SOURCE}@${SDS_DBNAME_SOURCE} <<EOF
set feedback off
exec utl_file.fremove('DATA_PUMP_DIR','${f}')
EOF
echo -e ${CYAN}"File $f Removed."${NORM}
done
echo ""
echo "Files removed. Exiting..."
exit
fi

#
# If -a set, remove all files found
#

if [ ${SDS_REMOVE_ALL} ]
then

#
# Get a full list of files
#

echo "Getting a full list of files to delete.  Please wait..."
echo ""
SDS_FULL_FILE_LIST=$(sqlplus -S ${SDS_DBMAST_SOURCE}/${SDS_DBMASTPASS_SOURCE}@${SDS_DBNAME_SOURCE} <<EOF
set hea off
select filename from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR')) where type != 'directory' order by mtime
/
EOF)

if [[ $(echo ${SDS_FULL_FILE_LIST}) = "no rows selected" ]]
then
echo -e ${CYAN}"No files found to delete."${NORM}
echo ""
echo "Exiting..."
echo ""
exit
fi

#
# List files to be removed
#

echo "Staring Removal All Files for Database ${SDS_DBNAME_SOURCE} from DATA_PUMP_DIR"
echo "*****************************************************************************"
echo ""
echo "The following files will be removed.."
echo ""
for n in ${SDS_FULL_FILE_LIST}
do
echo -e ${RED}${n}${NORM}
done
echo ""
echo "You have 10 seconds to exit"
echo ""
sleep 10

#
# Remove files
#

for f in ${SDS_FULL_FILE_LIST}
do

sqlplus -S ${SDS_DBMAST_SOURCE}/${SDS_DBMASTPASS_SOURCE}@${SDS_DBNAME_SOURCE} <<EOF
set feedback off
exec utl_file.fremove('DATA_PUMP_DIR','${f}')
EOF
echo -e ${CYAN}"File $f Removed."${NORM}
done
echo ""
echo "All files removed. Exiting..."
exit
fi

echo ${SDS_FULL_FILE_LIST}
fi
exit
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License