RDS for Oracleのダンプ取得〜S3アップロードをシェルスクリプトで実装してみた

  OracleOptionGroup:
    Type: AWS::RDS::OptionGroup
    Properties:
      EngineName: oracle-se2
      MajorEngineVersion: '19'
      OptionConfigurations:
        - OptionName: S3_INTEGRATION # S3_INTEGRATION を付与
          OptionVersion: '1.0'

  OracleDb:
    Type: AWS::RDS::DBInstance
    Properties:
      # ...（中略）
      OptionGroupName: !Ref OracleOptionGroup
      ManageMasterUserPassword: true   # パスワードをRDSが生成→Secrets Managerで管理
      AssociatedRoles:
        - FeatureName: S3_INTEGRATION
          RoleArn: !GetAtt 
.Arn

  # プライベートのRDSが インターネット経由せずに S3(ダンプアップロード)へ到達するための経路
  S3Endpoint:
    Type: AWS::EC2::VPCEndpoint
    Properties:
      VpcId: !Ref Vpc
      ServiceName: !Sub "com.amazonaws.${AWS::Region}.s3"
      VpcEndpointType: Gateway
      RouteTableIds:
        - !Ref PrivateRouteTable

# jq
sudo dnf install -y jq
# Oracle Instant Client（basic + sqlplus）を配置し PATH / LD_LIBRARY_PATH を通す
#   ※ここでは /opt/oracle 配下に展開した想定
echo 'export PATH=/opt/oracle/instantclient:$PATH'                       | sudo tee /etc/profile.d/instantclient.sh
echo 'export LD_LIBRARY_PATH=/opt/oracle/instantclient:$LD_LIBRARY_PATH' | sudo tee -a /etc/profile.d/instantclient.sh
source /etc/profile.d/instantclient.sh
sqlplus -v   # 通ればOK

bash run_dump_verify.sh <SecretArn> <RdsEndpoint> <Bucket> [Schema] [DBName]

#!/bin/bash
# =============================================================================
# RDS(Oracle) dump operation verification script
#   [1] get DB credentials (Secrets Manager)
#   [2] export dump (Data Pump)
#   [3] upload to S3  (+ verify object exists in S3 = hard gate)
#   [4] remove temp dump on RDS
#
# Usage (on EC2):
#   bash run_dump_verify.sh <SecretArn> <RdsEndpoint> <Bucket> [Schema] [DBName]
#   PURGE_LEFTOVERS=1  ... also delete all export_* leftovers at step [4]
#
# Exit codes (Job monitoring watches these):
#   0=OK / 10=preflight / 20=secret / 30=dump / 40=upload-start
#   50=upload-wait-or-verify / 60=cleanup
# =============================================================================
set -o pipefail
export LD_LIBRARY_PATH="${LD_LIBRARY_PATH:-}"   # profile側の $LD_LIBRARY_PATH 参照を安全に

# ---- args ----
ARN="${1:?usage: bash run_dump_verify.sh <SecretArn> <RdsEndpoint> <Bucket> [Schema] [DBName]}"
RDS_EP="${2:?RdsEndpoint is required}"
BUCKET="${3:?Bucket is required}"
SCHEMA="${4:-ADMIN}"
DB_NAME="${5:-ORCL}"
REGION="ap-northeast-1"
PURGE_LEFTOVERS="${PURGE_LEFTOVERS:-0}"

# ---- logging (screen + file) ----
TS=$(date +%Y%m%d_%H%M%S)
LOGFILE="/tmp/run_dump_verify_${TS}.log"
log(){ echo "[$(date '+%F %T')] $*" | tee -a "$LOGFILE"; }
STEP="init"
die(){ local rc="$1"; shift; log "[ERROR] step=${STEP} failed: $* (rc=${rc})"; log "Process End.  [RESULT] FAILED step=${STEP} rc=${rc} log=${LOGFILE}"; exit "$rc"; }
# sqlplusを実行し、生出力を$OUT/戻り値を$RCに入れつつログファイルへ追記
OUT=""; RC=0
run_sql(){ OUT=$(sqlplus -S "$CONN" "$@" 2>&1); RC=$?; printf '%s\n' "$OUT" >> "$LOGFILE"; }
# 出力からエラー理由らしき行を抽出（ログの[ERROR]行に載せる用）
reason_of(){ printf '%s\n' "$1" | grep -iE 'ORA-|PLS-|SP2-|error|denied|not *found' | head -3 | tr '\n' ' '; }

log "==== Dump verification started (log=${LOGFILE}) ===="

# ---- [0] preflight ----
STEP="0-preflight"
command -v sqlplus >/dev/null 2>&1 || source /etc/profile.d/instantclient.sh 2>/dev/null || true
command -v sqlplus >/dev/null 2>&1 || die 10 "sqlplus not found (check Instant Client PATH)"
command -v jq      >/dev/null 2>&1 || die 10 "jq not found (sudo dnf install -y jq)"

# ---- [1] credentials ----
STEP="1-secret"
log "[1/4] Getting DB credentials from Secrets Manager ..."
CREDS=$(aws secretsmanager get-secret-value --secret-id "$ARN" --query SecretString --output text --region "$REGION" 2>>"$LOGFILE") \
  || die 20 "Secrets Manager get-secret-value (check ARN/permission/region)"
DB_USER=$(echo "$CREDS" | jq -r .username)
DB_PASS=$(echo "$CREDS" | jq -r .password)
CONN="${DB_USER}/${DB_PASS}@//${RDS_EP}:1521/${DB_NAME}"
log "  -> OK (user=${DB_USER}, db=${DB_NAME}, schema=${SCHEMA})"

DUMP="export_${TS}.dmp"; DPLOG="export_${TS}.log"; JOB="EXP_${TS}"

# ---- generate temp SQL (quoted heredoc protects KU$ constants) ----

cat > /tmp/_exp.sql <<'SQL'
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET SERVEROUTPUT ON VERIFY OFF
DECLARE
  h1 NUMBER; st VARCHAR2(30);
BEGIN
  h1 := DBMS_DATAPUMP.OPEN('EXPORT','SCHEMA',NULL,'&1');
  DBMS_DATAPUMP.ADD_FILE(h1,'&2','DATA_PUMP_DIR',NULL,DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
  DBMS_DATAPUMP.ADD_FILE(h1,'&3','DATA_PUMP_DIR',NULL,DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
  DBMS_DATAPUMP.METADATA_FILTER(h1,'SCHEMA_EXPR','IN (''&4'')');
  DBMS_DATAPUMP.START_JOB(h1);
  DBMS_DATAPUMP.WAIT_FOR_JOB(h1, st);
  IF st != 'COMPLETED' THEN
    RAISE_APPLICATION_ERROR(-20001, 'Data Pump export state = '||st);
  END IF;
END;
/
EXIT
SQL

cat > /tmp/_size.sql <<'SQL'
SET HEADING OFF PAGESIZE 0 FEEDBACK OFF VERIFY OFF
SELECT TO_CHAR(ROUND(filesize/1024/1024,2))
FROM   TABLE(rdsadmin.rds_file_util.listdir('DATA_PUMP_DIR')) WHERE filename='&1';
EXIT
SQL

cat > /tmp/_upload.sql <<'SQL'
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET HEADING OFF PAGESIZE 0 FEEDBACK OFF VERIFY OFF
SELECT rdsadmin.rdsadmin_s3_tasks.upload_to_s3(
         p_bucket_name=>'&1', p_prefix=>'&2', p_s3_prefix=>'',
         p_directory_name=>'DATA_PUMP_DIR') FROM dual;
EXIT
SQL

cat > /tmp/_readlog.sql <<'SQL'
SET HEADING OFF PAGESIZE 0 FEEDBACK OFF VERIFY OFF LINESIZE 4000 LONG 1000000
SELECT text FROM TABLE(rdsadmin.rds_file_util.read_text_file('BDUMP','dbtask-&1..log'));
EXIT
SQL

cat > /tmp/_cleanup.sql <<'SQL'
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET SERVEROUTPUT ON VERIFY OFF
BEGIN
  UTL_FILE.FREMOVE('DATA_PUMP_DIR','&1');
  UTL_FILE.FREMOVE('DATA_PUMP_DIR','&2');
END;
/
EXIT
SQL

cat > /tmp/_purge.sql <<'SQL'
SET SERVEROUTPUT ON VERIFY OFF
BEGIN
  FOR f IN (SELECT filename FROM TABLE(rdsadmin.rds_file_util.listdir('DATA_PUMP_DIR'))
            WHERE filename LIKE 'export\_%' ESCAPE '\') LOOP
    BEGIN
      UTL_FILE.FREMOVE('DATA_PUMP_DIR', f.filename);
      DBMS_OUTPUT.PUT_LINE('purged '||f.filename);
    EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('skip '||f.filename||': '||SQLERRM);
    END;
  END LOOP;
END;
/
EXIT
SQL

cat > /tmp/_countleft.sql <<'SQL'
SET HEADING OFF PAGESIZE 0 FEEDBACK OFF VERIFY OFF
SELECT COUNT(*) FROM TABLE(rdsadmin.rds_file_util.listdir('DATA_PUMP_DIR'))
WHERE filename LIKE 'export\_%' ESCAPE '\';
EXIT
SQL

# ---- [2] export dump ----
STEP="2-dump"
log "[2/4] Exporting dump (schema=${SCHEMA}) ..."
run_sql @/tmp/_exp.sql "$JOB" "$DUMP" "$DPLOG" "$SCHEMA"
[ "$RC" -eq 0 ] || die 30 "Data Pump export | reason: $(reason_of "$OUT")"
DUMP_MB=$(sqlplus -S "$CONN" @/tmp/_size.sql "$DUMP" 2>>"$LOGFILE" | tr -d '[:space:]')
log "  -> Dump finished. File: ${DUMP}  Size: ${DUMP_MB} MB"

# ---- [3] upload to S3 ----
STEP="3-upload"
log "[3/4] Uploading to S3 (bucket=${BUCKET}) ..."
run_sql @/tmp/_upload.sql "$BUCKET" "$DUMP"
TASK_ID=$(printf '%s' "$OUT" | tr -d '[:space:]')
{ [ "$RC" -eq 0 ] && [ -n "$TASK_ID" ]; } || die 40 "S3 upload start | reason: $(reason_of "$OUT")"
log "  -> upload task started (task_id=${TASK_ID}), waiting for completion (max 15min, poll 10s) ..."

STEP="3-upload-wait"
DONE=0
for i in $(seq 1 90); do
  sleep 10   # タスクログ(dbtask-*.log)はRDSが非同期生成。生成前に読むとORA-29283になるので先に待つ
  OUT=$(sqlplus -S "$CONN" @/tmp/_readlog.sql "$TASK_ID" 2>/dev/null || true)
  # ログ未生成(ORA-29283=ファイル未存在)は「まだ完了待ち」であって失敗ではない → 次のポーリングへ
  if echo "$OUT" | grep -qi "ORA-29283"; then continue; fi
  # 【暫定】完了判定を "finished successfully" の文字列で拾っている（後述の「今後の課題」参照）
  if echo "$OUT" | grep -qi "finished successfully"; then DONE=1; break; fi
  # ここまで来て失敗マーカー/実ORAエラーが出ていれば本物のアップロード失敗
  if echo "$OUT" | grep -qiE "task failed|ORA-[0-9]"; then
    printf '%s\n' "$OUT" >> "$LOGFILE"
    die 50 "S3 upload task error | reason: $(reason_of "$OUT")"
  fi
done
[ "$DONE" -eq 1 ] || die 50 "S3 upload wait timeout after 15min (task log: dbtask-${TASK_ID}.log)"

# ---- verify the object actually exists in S3 (hard gate) ----
STEP="3-verify-s3"
S3LS=$(aws s3 ls "s3://${BUCKET}/${DUMP}" --region "$REGION" 2>>"$LOGFILE"); S3RC=$?
printf '%s\n' "$S3LS" >> "$LOGFILE"
{ [ "$S3RC" -eq 0 ] && [ -n "$S3LS" ]; } || die 50 "uploaded object NOT found in S3: s3://${BUCKET}/${DUMP}"
log "  -> S3 Upload finished. Location: s3://${BUCKET}/${DUMP}  (verified present in S3)"

# ---- [4] remove temp dump on RDS ----
STEP="4-cleanup"
log "[4/4] Removing temp dump from RDS (DATA_PUMP_DIR) ..."
run_sql @/tmp/_cleanup.sql "$DUMP" "$DPLOG"
[ "$RC" -eq 0 ] || die 60 "temp dump removal | reason: $(reason_of "$OUT")"
if [ "$ｓ" = "1" ]; then
  log "  あ=1: purging all export_* leftovers ..."
  sqlplus -S "$CONN" @/tmp/_purge.sql >>"$LOGFILE" 2>&1
fi
LEFT=$(sqlplus -S "$CONN" @/tmp/_countleft.sql 2>>"$LOGFILE" | tr -d '[:space:]')
log "  -> Dump remove finished. (remaining export_* files: ${LEFT})"

# ---- done ----
rm -f /tmp/_exp.sql /tmp/_size.sql /tmp/_upload.sql /tmp/_readlog.sql /tmp/_cleanup.sql /tmp/_purge.sql /tmp/_countleft.sql
STEP="done"
log "Process End.  [RESULT] SUCCESS  dump=${DUMP} size=${DUMP_MB}MB bucket=${BUCKET} log=${LOGFILE}"
exit 0

SQL> SELECT * FROM TABLE(rdsadmin.rds_file_util.listdir('DATA_PUMP_DIR'));

FILENAME              TYPE         FILESIZE MTIME
--------------------- ---------- ---------- ---------
datapump/             directory        4096 16-JUL-26

bash run_dump_verify.sh \
  "arn:aws:secretsmanager:ap-northeast-1:<ACCOUNT_ID>:secret:rds!db-xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx-XXXXXX" \
  "<rds-endpoint>.ap-northeast-1.rds.amazonaws.com" \
  "<dump-bucket-name>"

-- ダンプ取得直後
SQL> SELECT * FROM TABLE(rdsadmin.rds_file_util.listdir('DATA_PUMP_DIR'));

FILENAME                        TYPE         FILESIZE MTIME
------------------------------- ---------- ---------- ---------
datapump/                       directory        4096 24-JUL-26
export_20260724_110756.dmp      file           380928 24-JUL-26
export_20260724_110756.log      file             1203 24-JUL-26

-- 後処理後（.dmp/.log が消えている）
SQL> SELECT * FROM TABLE(rdsadmin.rds_file_util.listdir('DATA_PUMP_DIR'));

FILENAME                        TYPE         FILESIZE MTIME
------------------------------- ---------- ---------- ---------
datapump/                       directory        4096 24-JUL-26

[2026-07-24 11:07:56] ==== Dump verification started (log=/tmp/run_dump_verify_20260724_110756.log) ====
[2026-07-24 11:07:56] [1/4] Getting DB credentials from Secrets Manager ...
[2026-07-24 11:07:56]   -> OK (user=****, db=ORCL, schema=ADMIN)
[2026-07-24 11:07:56] [2/4] Exporting dump (schema=ADMIN) ...
[2026-07-24 11:08:39]   -> Dump finished. File: export_20260724_110756.dmp  Size: .36 MB
[2026-07-24 11:08:39] [3/4] Uploading to S3 (bucket=<dump-bucket-name>) ...
[2026-07-24 11:08:39]   -> upload task started (task_id=xxxxxxxxxxxxx-44), waiting for completion (max 15min, poll 10s) ...
[2026-07-24 11:08:50]   -> S3 Upload finished. Location: s3://<dump-bucket-name>/export_20260724_110756.dmp  (verified present in S3)
[2026-07-24 11:08:50] [4/4] Removing temp dump from RDS (DATA_PUMP_DIR) ...
[2026-07-24 11:08:50]   -> Dump remove finished. (remaining export_* files: 0)
[2026-07-24 11:08:50] Process End.  [RESULT] SUCCESS  dump=export_20260724_110756.dmp size=.36MB bucket=<dump-bucket-name> log=...