一、背景描述

当前 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