Auto Refresh of Oracle DB across VPCs

#!/bin/ksh
#
# Shell script for Snapshot of Databases
# Nazim Merchant
# 26 Jul 2018
#
# Usage script.ksh instance-name source_db_name target_db_name e.g. oradbdev oradbuat
#
#======================================================================================
set -x

. /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh

MAIL_LIST="nazim@nazimcricket.com"


DT=`date "+%Y%m%d"`
MDIR=/home/oracle/bin

# Discover Instance Name and Env Profile Name

if echo $1 | grep -q dev; then
  vSourceDB=full-db-name-$1
  vSourceEnv=dev
elif echo $1 | grep -q uat; then
  vSourceDB=full-db-name-$1
  vSourceEnv=uat
elif echo $1 | grep -q prd; then
  vSourceDB=full-db-name-$1
  vSourceEnv=prd
fi

if echo $2 | grep -q dev; then
  vTargetDB=full-db-name-$2
  vTargetEnv=dev
  vTargetAcc=999999999999999
  vTargetKey=99999-9999-999-999-99999
  vTargetS3=s3-bucket-path
elif echo $2 | grep -q uat; then
  vTargetDB=full-db-name-$2
  vTargetEnv=uat
  vTargetAcc=99999999999999
  vTargetKey=99999-9999-9999-9999-9999
  vTargetS3=s3-bucket-path
elif echo $2 | grep -q prd; then
  vTargetDB=full-db-name-$2
  vTargetEnv=prd
  vTargetAcc=9999999999999
  vTargetKey=99999999-9999-9999-9999999
  vTargetS3=s3-bucket-path
fi

echo Snapshot from $vSourceDB on Profile $vSourceEnv to $vTargetDB on Profile $vTargetEnv


# CREATE SNAPSHOT
aws rds create-db-snapshot --db-snapshot-identifier snapshot-$1-$DT --db-instance-identifier $vSourceDB --profile $vSourceEnv


# CHECK SNAPSHOT STATUS
vStatus=`aws rds describe-db-snapshots --db-snapshot-identifier snapshot-$1-$DT --profile $vSourceEnv | grep Status | tr -d '",: ' | sed "s/\Status//g"`


while [ $vStatus != available ] ;
do
 sleep 30
 vStatus=`aws rds describe-db-snapshots --db-snapshot-identifier snapshot-$1-$DT --profile $vSourceEnv | grep Status | tr -d '",: ' | sed "s/\Status//g"`
done


# MODIFY SNAPSHOT TO SHARE
aws rds modify-db-snapshot-attribute --db-snapshot-identifier snapshot-$1-$DT --attribute-name restore --values-to-add "$vTargetAcc" --profile $vSourceEnv

# CAPTURE ARN OF SNAPSHOT
arn=`aws rds describe-db-snapshots --db-snapshot-identifier snapshot-$1-$DT --profile $vSourceEnv | grep DBSnapshotArn | tr -d '", ' | sed "s/\DBSnapshotArn://g"`


# COPY THE SNAPSHOT IN UAT PROFILE
aws rds --profile $vTargetEnv copy-db-snapshot --source-db-snapshot-identifier $arn --target-db-snapshot-identifier snapshotcpy-$2-$DT --kms-key-id $vTargetKey


# CHECK UAT SNAPSHOT STATUS
vStatus2=`aws rds describe-db-snapshots --db-snapshot-identifier snapshotcpy-$2-$DT --profile $vTargetEnv | grep Status | tr -d '",: ' | sed "s/\Status//g"`

while [ $vStatus2 != available ] ;
do
 sleep 30
 vStatus2=`aws rds describe-db-snapshots --db-snapshot-identifier snapshotcpy-$2-$DT --profile $vTargetEnv | grep Status | tr -d '",: ' | sed "s/\Status//g"`
done

# PREPARE THE TARGET ENVIRONMENT FOR REPLACEMENT
vPrep=`aws rds --profile $vTargetEnv describe-db-instances --db-instance-identifier $vTargetDB | egrep -w "DBName|DBSubnetGroupName|OptionGroupName|AvailabilityZone"| tr -d '",: ' | sed "s/\DBName/--db-name /g" | sed "s/\DBSubnetGroupName/--db-subnet-group-name /g" | sed "s/\OptionGroupName/--option-group-name /g" | sed "s/\AvailabilityZone/--availability-zone /g"`

vPostPrep=`aws rds --profile $vTargetEnv describe-db-instances --db-instance-identifier $vTargetDB |  egrep -w "VpcSecurityGroupId|DBParameterGroupName" | tr -d '",: ' | sed "s/\VpcSecurityGroupId/--vpc-security-group-ids /g" | sed "s/\DBParameterGroupName/--db-parameter-group-name /g"`

# FETCH PASSWORD
vPwd=`aws secretsmanager --profile $vTargetEnv list-secrets | egrep Name | egrep -i $vTargetDB | tr -d '",: ' | sed "s/\Name//g"`
vPwdV=`aws secretsmanager --profile $vTargetEnv get-secret-value --secret-id $vPwd | grep SecretString | tr -d '",: ' | sed "s/\SecretString//g"`


# DELETE THE TARGET DATABASE
aws rds delete-db-instance --profile $vTargetEnv --db-instance-identifier $vTargetDB --skip-final-snapshot

vStatus3=`aws rds --profile $vTargetEnv describe-db-instances --db-instance-identifier $vTargetDB | grep DBInstanceStatus | tr -d '",: ' | sed "s/\DBInstanceStatus//g"`

while [ $vStatus3 -eq deleting ] ;
do
sleep 30
vStatus3=`aws rds --profile $vTargetEnv describe-db-instances --db-instance-identifier $vTargetDB | grep DBInstanceStatus | tr -d '",: ' | sed "s/\DBInstanceStatus//g"`
done


# RESTORE THE DATABASE FROM THE SNAPSHOT
vRest=`aws rds restore-db-instance-from-db-snapshot --db-snapshot-identifier snapshotcpy-$2-$DT --profile $vTargetEnv --db-instance-identifier $vTargetDB --copy-tags-to-snapshot $vPrep`

vStatus4=`aws rds --profile $vTargetEnv describe-db-instances --db-instance-identifier $vTargetDB | grep DBInstanceStatus | tr -d '",: ' | sed "s/\DBInstanceStatus//g"`

while [ $vStatus4 != available ] ;
do
sleep 30
vStatus4=`aws rds --profile $vTargetEnv describe-db-instances --db-instance-identifier $vTargetDB | grep DBInstanceStatus | tr -d '",: ' | sed "s/\DBInstanceStatus//g"`
done


# PERFORM POST RESTORE TASKS
vRest2=`aws rds --profile $vTargetEnv modify-db-instance --db-instance-identifier $vTargetDB $vPostPrep`

vStatus5=`aws rds --profile $vTargetEnv describe-db-instances --db-instance-identifier $vTargetDB | grep DBInstanceStatus | tr -d '",: ' | sed "s/\DBInstanceStatus//g"`

while [ $vStatus5 != available ] ;
do
sleep 30
vStatus5=`aws rds --profile $vTargetEnv describe-db-instances --db-instance-identifier $vTargetDB | grep DBInstanceStatus | tr -d '",: ' | sed "s/\DBInstanceStatus//g"`
done


# REBOOT THE DATABASE
aws rds reboot-db-instance --profile $vTargetEnv --db-instance-identifier $vTargetDB

vStatus6=`aws rds --profile $vTargetEnv describe-db-instances --db-instance-identifier $vTargetDB | grep DBInstanceStatus | tr -d '",: ' | sed "s/\DBInstanceStatus//g"`

while [ $vStatus6 != available ] ;
do
sleep 30
vStatus6=`aws rds --profile $vTargetEnv describe-db-instances --db-instance-identifier $vTargetDB | grep DBInstanceStatus | tr -d '",: ' | sed "s/\DBInstanceStatus//g"`
done


# RESET MASTER PASSWORD
aws rds modify-db-instance --profile $vTargetEnv --db-instance-identifier $vTargetDB --master-user-password $vPwdV



# FINAL REBOOT - SEEMS TO BE NEEDED
aws rds reboot-db-instance --profile $vTargetEnv --db-instance-identifier $vTargetDB

vStatus7=`aws rds --profile $vTargetEnv describe-db-instances --db-instance-identifier $vTargetDB | grep DBInstanceStatus | tr -d '",: ' | sed "s/\DBInstanceStatus//g"`

while [ $vStatus7 != available ] ;
do
sleep 30
vStatus7=`aws rds --profile $vTargetEnv describe-db-instances --db-instance-identifier $vTargetDB | grep DBInstanceStatus | tr -d '",: ' | sed "s/\DBInstanceStatus//g"`
done






vStatus8=`aws rds --profile $vTargetEnv describe-db-instances --db-instance-identifier $vTargetDB | grep DBInstanceStatus | tr -d '",: ' | sed "s/\DBInstanceStatus//g"`

if [ $vStatus8 -eq available ] ; then

   vMsg="SUCCESS Refresh of $2 from $1"

else

   vMsg="ERROR Refresh of $2 from $1"

fi



# Send Mail


v_body=`aws rds describe-db-instances --profile uat --db-instance-identifier rds-testing-oracle-oradbuat | egrep -w "DBInstanceIdentifier|DBName|InstanceCreateTime|DBParameterGroupName|OptionGroupName|VpcSecurityGroupId|DBInstanceArn|DBInstanceStatus" | sort | tr -d " "`


/usr/sbin/sendmail -oi -t <<EOF
To: $MAIL_LIST
Subject: $vMsg

$v_body

EOF