一、背景描述

當前 xxx 項目?Oracle 11g RAC 庫缺少 DG，并且日常沒有備份，存在服務器或存儲損壞，數據或表結構存在丟失風險，在和項目組同步后，項目組反饋可對該數據庫定期備份相關結構信息，如存在數據丟失，只要備份的用戶表結構等信息還在，可通過其它方式導入。

本次通過shell腳本來備份實現，將數據備份到本地，并scp 到遠程目標端。

二、操作過程

1）Oracle 數據庫創建備份用戶

• create user xxxback identified by xxxx;

2）創建備份目錄

• create or replace directory BACKUP_DIR as '/home/oracle/backups/xxx' ;

3）授予備份用戶權限

• grant read,write on directory BACKUP_DIR to xxxback;

4）創建免密登錄遠程服務器

5）編寫腳本

6）定時備份??

# 定期從?xxx-primary-db1 服務器備份，root 用戶執行定期備份至 192.168.xxx.xxx?/database/xxback/xxx 目錄
0 3 * * * su - oracle -c "/home/oracle/backups/xxx/xxx_metadata_backup.sh" >> /dev/null 2>&1
0 4 * * * su - oracle -c "/home/oracle/backups/xxx/xxx_metadata_backup.sh" > /dev/null 2>&1

三、備份腳本

3.1 xxx 備份腳本 (xxx_metadata_backup.sh)

#!/bin/sh
# 腳本名稱：xxx_metadata_backup.sh
# 功能：Oracle 11g RAC元數據備份及傳輸
# 備份內容：表結構、序列、觸發器、存儲過程等數據庫對象（不包含數據）
# 備份頻率：每天一次# ====== 核心配置 ======
export ORACLE_SID=xxx1
ORACLE_USER="xxxback"
ORACLE_PASS="xxx"
ORACLE_SERVICE="xxxx1"
SCHEMAS_TO_BACKUP=("xxx" "xxx")
ORACLE_DIR="BACKUP_DIR"
LOCAL_BACKUP_DIR="/home/oracle/backups/xxx"  # 已存在的目錄
REMOTE_USER="xxxback"
REMOTE_SERVER="192.168.xxx.xxx"
REMOTE_PATH="/database/xxxback/xxx"
RETENTION_DAYS=7
# =====================# 設置Oracle環境
# export ORACLE_SID=xxx1
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'# 日志文件
CURRENT_DATE=$(date +%Y-%m-%d)
LOG_FILE="${LOCAL_BACKUP_DIR}/backup_${ORACLE_SERVICE}_${CURRENT_DATE}.log"# 1. 驗證目錄權限
echo "===== 備份開始 [${CURRENT_DATE}] =====" | tee "${LOG_FILE}"
echo "節點: $(hostname -s)" | tee -a "${LOG_FILE}"
echo "Oracle服務: ${ORACLE_SERVICE}" | tee -a "${LOG_FILE}"
echo "備份用戶: ${SCHEMAS_TO_BACKUP[*]}" | tee -a "${LOG_FILE}"
echo "Oracle目錄: ${ORACLE_DIR}" | tee -a "${LOG_FILE}"# 驗證本地備份目錄可寫性
echo "[$(date +'%F %T')] 驗證本地備份目錄權限..." | tee -a "${LOG_FILE}"
if [ ! -d "${LOCAL_BACKUP_DIR}" ]; thenecho "[$(date +'%F %T')] [ERROR] 本地備份目錄不存在: ${LOCAL_BACKUP_DIR}" | tee -a "${LOG_FILE}"exit 1
elif [ ! -w "${LOCAL_BACKUP_DIR}" ]; thenecho "[$(date +'%F %T')] [ERROR] 本地備份目錄不可寫: ${LOCAL_BACKUP_DIR}" | tee -a "${LOG_FILE}"exit 1
fi
echo "[$(date +'%F %T')] 本地備份目錄驗證通過: ${LOCAL_BACKUP_DIR}" | tee -a "${LOG_FILE}"# 查詢Oracle目錄路徑
echo "[$(date +'%F %T')] 查詢Oracle目錄路徑..." | tee -a "${LOG_FILE}"
{
sqlplus -s /nolog <<EOF
connect ${ORACLE_USER}/${ORACLE_PASS}@${ORACLE_SERVICE}
SET HEADING OFF FEEDBACK OFF PAGES 0
SELECT directory_path FROM dba_directories WHERE directory_name='${ORACLE_DIR}';
EXIT;
EOF
} > "${LOCAL_BACKUP_DIR}/dir_check.log" 2>&1DIR_PATH=$(grep -v '^$' "${LOCAL_BACKUP_DIR}/dir_check.log" | head -1)
if [ -z "$DIR_PATH" ]; thenecho "[$(date +'%F %T')] [ERROR] Oracle目錄不存在或無權訪問: ${ORACLE_DIR}" | tee -a "${LOG_FILE}"cat "${LOCAL_BACKUP_DIR}/dir_check.log" >> "${LOG_FILE}"exit 1
fi# 標準化路徑格式（確保無結尾斜杠）
DIR_PATH=$(echo "${DIR_PATH}" | sed 's:/*$::')
LOCAL_BACKUP_DIR=$(echo "${LOCAL_BACKUP_DIR}" | sed 's:/*$::')echo "[$(date +'%F %T')] Oracle目錄路徑: ${DIR_PATH}" | tee -a "${LOG_FILE}"# 檢查Oracle目錄是否與本地備份目錄相同
SAME_DIRECTORY=0
if [ "${DIR_PATH}" = "${LOCAL_BACKUP_DIR}" ]; thenecho "[$(date +'%F %T')] Oracle目錄與本地備份目錄相同，跳過移動操作" | tee -a "${LOG_FILE}"SAME_DIRECTORY=1
else# 驗證Oracle目錄可寫性TEST_FILE="${DIR_PATH}/write_test_$(date +%s).tmp"touch "${TEST_FILE}" >/dev/null 2>&1if [ $? -ne 0 ]; thenecho "[$(date +'%F %T')] [ERROR] Oracle進程用戶無法寫入目錄: ${DIR_PATH}" | tee -a "${LOG_FILE}"echo "請檢查目錄權限: ls -ld ${DIR_PATH}" | tee -a "${LOG_FILE}"exit 1elserm -f "${TEST_FILE}"echo "[$(date +'%F %T')] Oracle目錄可寫性驗證通過" | tee -a "${LOG_FILE}"fi
fi# 2. 執行元數據備份
BACKUP_FILE="xxx_metadata_${ORACLE_SERVICE}_${CURRENT_DATE}.dmp"
BACKUP_LOG="expdp_${ORACLE_SERVICE}_${CURRENT_DATE}.log"echo "[$(date +'%F %T')] 開始元數據導出..." | tee -a "${LOG_FILE}"
{
expdp "${ORACLE_USER}"/"${ORACLE_PASS}"@"${ORACLE_SERVICE}" \DIRECTORY="${ORACLE_DIR}" \DUMPFILE="${BACKUP_FILE}" \LOGFILE="${BACKUP_LOG}" \SCHEMAS=$(IFS=,; echo "${SCHEMAS_TO_BACKUP[*]}") \CONTENT=METADATA_ONLY \CLUSTER=NO \EXCLUDE=STATISTICS \PARALLEL=1
} >> "${LOG_FILE}" 2>&1EXPDP_EXIT=$?
if [ $EXPDP_EXIT -ne 0 ]; thenecho "[$(date +'%F %T')] [ERROR] 元數據導出失敗 (錯誤碼: $EXPDP_EXIT)" | tee -a "${LOG_FILE}"# 提取并記錄關鍵錯誤信息grep -iE 'error|failed|invalid|ora-|ude-' "${LOG_FILE}" | tail -10 | tee -a "${LOG_FILE}"exit 1
fi
echo "[$(date +'%F %T')] 元數據導出完成" | tee -a "${LOG_FILE}"# 3. 處理備份文件
if [ $SAME_DIRECTORY -eq 0 ]; thenecho "[$(date +'%F %T')] 移動備份文件到本地目錄..." | tee -a "${LOG_FILE}"mv "${DIR_PATH}/${BACKUP_FILE}" "${LOCAL_BACKUP_DIR}/" || {echo "[$(date +'%F %T')] [ERROR] 移動備份文件失敗" | tee -a "${LOG_FILE}"exit 1}mv "${DIR_PATH}/${BACKUP_LOG}" "${LOCAL_BACKUP_DIR}/" || {echo "[$(date +'%F %T')] [ERROR] 移動日志文件失敗" | tee -a "${LOG_FILE}"exit 1}
elseecho "[$(date +'%F %T')] 備份文件已在本地目錄，無需移動" | tee -a "${LOG_FILE}"
fi# 4. 傳輸備份文件
echo "[$(date +'%F %T')] 開始文件傳輸到遠程服務器..." | tee -a "${LOG_FILE}"# 驗證文件存在
FILES_TO_TRANSFER=("${LOCAL_BACKUP_DIR}/${BACKUP_FILE}""${LOCAL_BACKUP_DIR}/${BACKUP_LOG}""${LOG_FILE}"
)MISSING_FILE=0
for file in "${FILES_TO_TRANSFER[@]}"; doif [ ! -f "$file" ]; thenecho "[$(date +'%F %T')] [WARNING] 文件不存在: $file" | tee -a "${LOG_FILE}"MISSING_FILE=1fi
doneif [ $MISSING_FILE -eq 0 ]; thenscp -o StrictHostKeyChecking=no -o ConnectTimeout=30 \"${FILES_TO_TRANSFER[@]}" \"${REMOTE_USER}@${REMOTE_SERVER}:${REMOTE_PATH}/" >> "${LOG_FILE}" 2>&1SCP_EXIT=$?if [ $SCP_EXIT -ne 0 ]; thenecho "[$(date +'%F %T')] [ERROR] 文件傳輸失敗 (錯誤碼: $SCP_EXIT)" | tee -a "${LOG_FILE}"exit 1fiecho "[$(date +'%F %T')] 文件傳輸完成" | tee -a "${LOG_FILE}"
elseecho "[$(date +'%F %T')] [ERROR] 文件缺失，跳過傳輸" | tee -a "${LOG_FILE}"exit 1
fi# 5. 清理舊備份
echo "[$(date +'%F %T')] 清理過期備份文件..." | tee -a "${LOG_FILE}"# 本地清理
find "${LOCAL_BACKUP_DIR}" \-name "xxx_metadata_${ORACLE_SERVICE}_*.dmp" \-mtime +${RETENTION_DAYS} \-delete -print | tee -a "${LOG_FILE}"find "${LOCAL_BACKUP_DIR}" \-name "expdp_${ORACLE_SERVICE}_*.log" \-mtime +${RETENTION_DAYS} \-delete -print | tee -a "${LOG_FILE}"find "${LOCAL_BACKUP_DIR}" \-name "backup_${ORACLE_SERVICE}_*.log" \-mtime +${RETENTION_DAYS} \-delete -print | tee -a "${LOG_FILE}"# 如果Oracle目錄不同，則清理該目錄
if [ $SAME_DIRECTORY -eq 0 ]; thenfind "${DIR_PATH}" \-name "xxx_metadata_${ORACLE_SERVICE}_*.dmp" \-mtime +${RETENTION_DAYS} \-delete -print | tee -a "${LOG_FILE}"find "${DIR_PATH}" \-name "expdp_${ORACLE_SERVICE}_*.log" \-mtime +${RETENTION_DAYS} \-delete -print | tee -a "${LOG_FILE}"
fi# 遠程清理
ssh -o StrictHostKeyChecking=no -o ConnectTimeout=10 ${REMOTE_USER}@${REMOTE_SERVER} \"find ${REMOTE_PATH} \-name 'xxx_metadata_${ORACLE_SERVICE}_*.dmp' \-mtime +${RETENTION_DAYS} \-delete -print" | tee -a "${LOG_FILE}"ssh -o StrictHostKeyChecking=no -o ConnectTimeout=10 ${REMOTE_USER}@${REMOTE_SERVER} \"find ${REMOTE_PATH} \-name '*_${ORACLE_SERVICE}_*.log' \-mtime +${RETENTION_DAYS} \-delete -print" | tee -a "${LOG_FILE}"echo "[$(date +'%F %T')] 清理完成" | tee -a "${LOG_FILE}"
echo "===== 備份成功結束 [${CURRENT_DATE}] =====" | tee -a "${LOG_FILE}"
exit 0