暂无图片
暂无图片
6
暂无图片
暂无图片
暂无图片
首页 / Oracle 19c 每天定时备份方案：RMAN + 数据泵

Oracle 19c 每天定时备份方案：RMAN + 数据泵

原创 JiekeXu All China Database Union 2026-04-27
310

大家好，我是JiekeXu,江湖人称“强哥”，青学会MOP技术社区主席，荣获Oracle ACE Pro称号，OpenTenBase ACE，金仓社区最具价值倡导者KVA，崖山最具价值专家YVP，IvorySQL开源社区专家顾问委员会成员，KWDB社区MVP，墨天轮MVP，墨天轮连续多年度“墨力之星”，拥有Oracle OCP/OCM认证,MySQL 5.7/8.0 OCP认证以及金仓KCA、KCP、KCM、KCSM证书，TiDB PCTA/PCTP证书、PCA、OBCA、OGCA等众多国产数据库认证证书,欢迎关注我的微信公众号“JiekeXu DBA之路”，然后点击右上方三个点“设为星标”置顶，更多干货文章才能第一时间推送，谢谢！

目 录

一、前 言
二、RMAN全备压缩脚本
    1. 创建脚本及内容
    2. 设置脚本权限
    3. 定时任务运行脚本
三、数据库全备与增备脚本
    1. 0 级备份脚本（level0_backup.sh）
    2. 1 级增量备份脚本（level1_backup.sh）
    3. 归档日志备份脚本（arch_backup.sh）
    4. 表空间备份脚本
    5. 设置定时任务
三、数据泵全库导出脚本
    1. 创建脚本及内容
    2. 设置脚本权限
    3. 设置定时任务
四、数据泵导出关键用户
    1. 创建目录及授权
    2. 创建脚本及内容
    3. 设置脚本权限
    4. 设置定时任务
五、备份验证与监控
    1. 检查备份状态
    2. 创建备份报告脚本
六、灾难恢复测试
    1. RMAN恢复测试脚本
    2. 数据泵导入测试脚本
七、备份策略优化建议
八、故障处理指南
    1. 常见错误及解决方案
    2. 紧急恢复流程
九、写在最后
十、官网参考链接

一、前 言

作为有多年经验的 Oracle DBA，现在需要设计一个企业级的数据库备份恢复方案。此方案基于生产环境最佳实践，确保数据安全与快速恢复。之前也分享过几个数据库备份脚本《分享几个数据库备份脚本》以及《使用 Rman 备份恢复 Oracle RAC 到单机文件系统》 、MySQL 备份有效性验证方案《MySQL Xtrabackup 备份数据有效性恢复验证》，本文仅供参考。

二、RMAN全备压缩脚本

1. 创建脚本及内容

vim /u01/scripts/rman_full_backup.sh

#!/bin/bash
# Oracle 19c RMAN Full Compressed Backup Script
# Description: Daily full compressed backup with 3-day retention
# Author: JiekeXu
# Date: 2026-04-24

# Set environment variables
# export ORACLE_SID=orcl
# export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"
source ~/.bash_profile


# Backup Directory setup
BACK_DIR="/u01/backup/rman"
mkdir -p $BACK_DIR
chown -R oracle:oinstall $BACK_DIR
chmod -R 775 $BACK_DIR

# Log file setup
LOG_DIR="/u01/backup/logs"
mkdir -p $LOG_DIR
chown -R oracle:oinstall $LOG_DIR
chmod -R 775 $LOG_DIR
LOG_FILE="$LOG_DIR/rman_backup_$(date +%Y%m%d).log"

# Start backup process
echo "==============================================" >> $LOG_FILE
echo "Starting RMAN full compressed backup at $(date)" >> $LOG_FILE
echo "==============================================" >> $LOG_FILE

# Run RMAN backup
rman target / <<EOF >> $LOG_FILE 2>&1
RUN {
    # Configure retention policy (7 days)
    CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
    
    # Enable automatic backup of control file and spfile
    CONFIGURE CONTROLFILE AUTOBACKUP ON;
    CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '$BACK_DIR/%F';
    
    # Allocate channels
    ALLOCATE CHANNEL ch1 DEVICE TYPE DISK FORMAT '$BACK_DIR/%d_%T_%U.bkp';
    ALLOCATE CHANNEL ch2 DEVICE TYPE DISK FORMAT '$BACK_DIR/%d_%T_%U.bkp';
    ALLOCATE CHANNEL ch3 DEVICE TYPE DISK FORMAT '$BACK_DIR/%d_%T_%U.bkp';
    ALLOCATE CHANNEL ch4 DEVICE TYPE DISK FORMAT '$BACK_DIR/%d_%T_%U.bkp';
    
    # Backup database with compression
    BACKUP AS COMPRESSED BACKUPSET 
        DATABASE PLUS ARCHIVELOG;
    
    # Backup current control file
    BACKUP CURRENT CONTROLFILE;
    
    # Crosscheck backups
    CROSSCHECK BACKUP;
    CROSSCHECK ARCHIVELOG ALL;
    
    # Delete obsolete backups (older than 7 days)
    DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF 7 DAYS;
    DELETE NOPROMPT EXPIRED BACKUP;
    DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
    
    # Release channels
    RELEASE CHANNEL ch1;
    RELEASE CHANNEL ch2;
    RELEASE CHANNEL ch3;
    RELEASE CHANNEL ch4;
}
EXIT;
EOF

# Check backup result
if [ $? -eq 0 ]; then
    echo "Backup completed successfully at $(date)" >> $LOG_FILE
    echo "Backup files:" >> $LOG_FILE
    ls -lh /u01/backup/rman/*$(date +%Y%m%d)*.bkp >> $LOG_FILE 2>&1
else
    echo "ERROR: Backup failed at $(date)" >> $LOG_FILE
    send_alert "RMAN backup failed for $ORACLE_SID. Check log: $LOG_FILE"
fi

# Clean up old logs (keep last 15 days)
find $LOG_DIR -name "rman_backup_*.log" -mtime +15 -exec rm {} \;

echo "==============================================" >> $LOG_FILE
echo "Backup process completed at $(date)" >> $LOG_FILE
echo "==============================================" >> $LOG_FILE

### 0 8 * * * /u01/scripts/rman_full_backup.sh

2. 设置脚本权限

chown oracle:oinstall /u01/scripts/rman_full_backup.sh
chmod 750 /u01/scripts/rman_full_backup.sh

3. 定时任务运行脚本

crontab -l

# RMAN全备压缩 - 每天8点运行
0 8 * * * /u01/scripts/rman_full_backup.sh

三、数据库全备与增备脚本

1. 0 级备份脚本（level0_backup.sh）

#!/bin/bash
# Oracle 19c RMAN LEVEL 0 Compressed Backup Script
# Description: Daily LEVEL 0 compressed backup with 30-day retention
# Author: JiekeXu
# Date: 2026-04-24

# Set environment variables
export ORACLE_SID=orcl
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"
source ~/.bash_profile

# Backup Directory setup
BACK_DIR="/u01/backup"
mkdir -p $BACK_DIR
chown -R oracle:oinstall $BACK_DIR
chmod -R 775 $BACK_DIR

# Log file setup
LOG_DIR="/u01/backup/logs"
mkdir -p $LOG_DIR
chown -R oracle:oinstall $LOG_DIR
chmod -R 775 $LOG_DIR
LOG_FILE="$LOG_DIR/Rman_Backup_L0_$(date +%Y%m%d).log"

rman target / <<EOF >> $LOG_FILE 2>&1
run {
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
allocate channel ch3 device type disk;
allocate channel ch4 device type disk;
backup as compressed backupset incremental level 0 database format '$BACK_DIR/full_L0_%d_%T_%U' tag='LEVEL0';
backup current controlfile format '$BACK_DIR/controlfile_%d_%T_%U';
backup spfile format '$BACK_DIR/spfile_%d_%T_%U';
release channel ch1;
release channel ch2;
release channel ch3;
release channel ch4;
}
EOF

2. 1 级增量备份脚本（level1_backup.sh）

#!/bin/bash
# Oracle 19c RMAN LEVEL 1 Compressed Backup Script
# Description: Daily LEVEL 1 compressed backup with 30-day retention
# Author: JiekeXu
# Date: 2026-04-24

# Set environment variables
export ORACLE_SID=test
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"
source ~/.bash_profile

# Backup Directory setup
BACK_DIR="/u01/backup"
mkdir -p $BACK_DIR
chown -R oracle:oinstall $BACK_DIR
chmod -R 775 $BACK_DIR

# Log file setup
LOG_DIR="/u01/backup/logs"
mkdir -p $LOG_DIR
chown -R oracle:oinstall $LOG_DIR
chmod -R 775 $LOG_DIR
LOG_FILE="$LOG_DIR/Rman_Backup_L1_$(date +%Y%m%d).log"

rman target / <<EOF >> $LOG_FILE 2>&1
run {
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
allocate channel ch3 device type disk;
allocate channel ch4 device type disk;
backup as compressed backupset incremental level 1 database format '$BACK_DIR/incr_L1_%d_%T_%U' tag='LEVEL1';
backup current controlfile format '$BACK_DIR/controlfile_%d_%T_%U';
backup spfile format '$BACK_DIR/spfile_%d_%T_%U';
release channel ch1;
release channel ch2;
release channel ch3;
release channel ch4;
}
EOF

3. 归档日志备份脚本（arch_backup.sh）

#!/bin/bash
# Oracle 19c RMAN ARCH Compressed Backup Script
# Description: Daily ARCH compressed backup with 30-day retention
# Author: JiekeXu
# Date: 2026-04-24

# Set environment variables
export ORACLE_SID=test
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"
source ~/.bash_profile

# Backup Directory setup
BACK_DIR="/u01/backup"
mkdir -p $BACK_DIR
chown -R oracle:oinstall $BACK_DIR
chmod -R 775 $BACK_DIR

# Log file setup
LOG_DIR="/u01/backup/logs"
mkdir -p $LOG_DIR
chown -R oracle:oinstall $LOG_DIR
chmod -R 775 $LOG_DIR
LOG_FILE="$LOG_DIR/Rman_Backup_L1_$(date +%Y%m%d).log"

rman target / <<EOF >> $LOG_FILE 2>&1
run {
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
backup as compressed backupset archivelog all not backed up 1 times format '$BACK_DIR/arch_%d_%T_%U';
release channel ch1;
release channel ch2;
}
EOF

4. 表空间备份脚本

#!/bin/bash
# 文件名：/home/oracle/scripts/backup/daily_tbs_level0.sh
# 用途：每天执行表空间0级全备
# 执行时间：每天凌晨6:00
# 作者: JiekeXu
# 时间: 2026-04-24

export ORACLE_SID=test
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
source ~/.bash_profile

# 使其支持中文
export LANG=zh_CN.UTF-8

# 根据业务重要性选择备份存放目录
LOG_DIR=/u01/backup/logs
BACKUP_DIR=/u01/backup/data
TODAY=$(date +%Y%m%d_%H%M%S)
LOG_FILE=${LOG_DIR}/level0_tbs_${TODAY}.log

# 根据业务重要性创建备份存放目录
mkdir -p ${BACKUP_DIR}
mkdir -p ${LOG_DIR}

# 需要备份的表空间列表（根据业务重要性调整）
TABLESPACES="CUWB_DATA"

echo "=== 表空间0级全备开始: $(date) ===" > $LOG_FILE

rman target / log $LOG_FILE append << EOF
run {
    # 分配通道
    allocate channel ch1 device type disk format '${BACKUP_DIR}/level0_${TABLESPACES}_%d_%T_%U';
    allocate channel ch2 device type disk format '${BACKUP_DIR}/level0_${TABLESPACES}_%d_%T_%U';
    
    # 备份指定表空间（0级增量）
    backup as compressed backupset 
    incremental level 0 
    tablespace ${TABLESPACES}
    tag 'LEVEL0_${TABLESPACES}_${TODAY}'
    plus archivelog;
    
    # 备份控制文件和SPFILE
    backup current controlfile;
    backup spfile;

    # 交叉检查并删除过期备份
    crosscheck backup;
    delete noprompt expired backup;
    delete noprompt obsolete;
    
    # 释放通道
    release channel ch1; 
    release channel ch2;
}
EOF

# 检查备份结果
if [ $? -eq 0 ]; then
    echo "0级备份成功完成于: $(date)" >> $LOG_FILE
    
    # 生成备份报告
    rman target / << INNER_EOF >> $LOG_FILE
    list backup of tablespace ${TABLESPACES} completed after 'sysdate-1';
    report obsolete;
INNER_EOF
    
    # 发送成功通知（可选）
    # echo "表空间0级备份成功"  >> $LOG_FILE
else
    echo "0级备份失败于: $(date)" >> $LOG_FILE
    exit 1
fi

echo "=== 表空间0级全备结束: $(date) ===" >> $LOG_FILE

5. 设置定时任务

--每周日凌晨2点10分执行0级备份：
10 2 * * 7 /home/oracle/scripts/level0_backup.sh

--每周一到周六凌晨3点20分执行1级备份（在0级备份后的其他时间）：
20 3 * * 1-6 /home/oracle/scripts/level1_backup.sh

--每四个小时备份归档日志：
30 */4 * * * /home/oracle/scripts/arch_backup.sh

例子：
    # 每月的最后1天
    0 0 L * * *

    说明：
    Linux
    *    *    *    *    *
    -    -    -    -    -
    |    |    |    |    |
    |    |    |    |    +----- day of week (0 - 7) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat
    |    |    |    +---------- month (1 - 12) OR jan,feb,mar,apr ...
    |    |    +--------------- day of month (1 - 31)
    |    +-------------------- hour (0 - 23)
    +------------------------- minute (0 - 59)

三、数据泵全库导出脚本

1. 创建脚本及内容

vim /u01/scripts/expdp_full_export.sh

#!/bin/bash
# Oracle 19c Data Pump Full Export Script
# Description: Daily full compressed export with 3-day retention
# Author: JiekeXu
# Date: 2026-04-24

# Set environment variables
export ORACLE_SID=orcl
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
source ~/.bash_profile

# Log and dump file settings
DUMP_DIR="/u01/backup/dpump"
LOG_DIR="/u01/backup/logs"
mkdir -p $DUMP_DIR
mkdir -p $LOG_DIR
DUMP_FILE="full_expdp_$(date +%Y%m%d).dmp"
LOG_FILE="expdp_$(date +%Y%m%d).log"
FULL_LOG="$LOG_DIR/expdp_full_$(date +%Y%m%d).log"

# Start export process
echo "==============================================" >> $FULL_LOG
echo "Starting Data Pump full export at $(date)" >> $FULL_LOG
echo "==============================================" >> $FULL_LOG

sqlplus  -S "/ as sysdba" <<EOF
-- 创建数据泵目录对象
CREATE OR REPLACE DIRECTORY DPUMP_DIR AS '$DUMP_DIR';
GRANT READ, WRITE ON DIRECTORY DPUMP_DIR TO system;
EOF

# Run Data Pump export
expdp \'/ as sysdba\' \
    directory=DPUMP_DIR \
    dumpfile=$DUMP_FILE \
    logfile=$LOG_FILE \
    full=Y \
    compression=all \
    parallel=4 \
    job_name=full_export_$(date +%H%M%S) \
    exclude=statistics \
    >> $FULL_LOG 2>&1

# Check export result
if [ $? -eq 0 ]; then
    echo "Export completed successfully at $(date)" >> $FULL_LOG
    
    # Compress dump file
    gzip $DUMP_DIR/$DUMP_FILE
    if [ $? -eq 0 ]; then
        echo "Dump file compressed successfully: $DUMP_DIR/${DUMP_FILE}.gz" >> $FULL_LOG
    else
        echo "WARNING: Failed to compress dump file" >> $FULL_LOG
    fi
    
    # List generated files
    echo "Generated files:" >> $FULL_LOG
    ls -lh $DUMP_DIR/*$(date +%Y%m%d)* >> $FULL_LOG 2>&1
else
    echo "ERROR: Export failed at $(date)" >> $FULL_LOG
    send_alert "Data Pump export failed for $ORACLE_SID. Check log: $FULL_LOG"
fi

# Clean up old exports (keep last 3 days)
find $DUMP_DIR -name "full_expdp_*.dmp.gz" -mtime +3 -exec rm -f {} \;
find $DUMP_DIR -name "expdp_*.log" -mtime +3 -exec rm -f {} \;
find $LOG_DIR -name "expdp_full_*.log" -mtime +7 -exec rm -f {} \;

echo "==============================================" >> $FULL_LOG
echo "Export process completed at $(date)" >> $FULL_LOG
echo "==============================================" >> $FULL_LOG

2. 设置脚本权限

chown oracle:oinstall /u01/scripts/expdp_full_export.sh
chmod 750 /u01/scripts/expdp_full_export.sh

3. 设置定时任务

su - oracle
crontab -e
# 数据泵全库导出 - 每天12点运行
0 12 * * * /u01/scripts/expdp_full_export.sh

crontab -l

四、数据泵导出关键用户

1. 创建目录及授权

-- 查看数据库用户
sqlplus / as sysdba
set line  240 
col profile for a20 
set pages 999 
col username for a25 
col LAST_LOGIN for a55 
col ACCOUNT_STATUS for a18 
select USERNAME,ACCOUNT_STATUS,CREATED,to_char(LAST_LOGIN,'yyyy-mm-dd HH24:MI:SS'),LOCK_DATE,PROFILE,PASSWORD_VERSIONS,DEFAULT_TABLESPACE from dba_users where account_status='OPEN' order by CREATED asc; 

-- 查看数据泵导出目录
col OWNER for a25
col DIRECTORY_NAME for a35 
col DIRECTORY_PATH for a65
Select * from dba_directories where DIRECTORY_PATH not like '%dbhome%';

-- 创建数据泵目录对象
CREATE OR REPLACE DIRECTORY DPUMP_DIR AS '${DUMP_DIR}';
GRANT READ, WRITE ON DIRECTORY DPUMP_DIR TO system;

2. 创建脚本及内容

vim /u01/scripts/Oracle_Schema_Dump.sh

#!/bin/bash
#28 19 * * * sh /nfs/backup/script/oracledump.sh
#echo "password">>/nfs/backup/script/rsyncd.pass
source /home/oracle/.bash_profile

source_dir=/data/ora-share/jiekedb
rmpath=$source_dir
mkdir -p ${source_dir}
bak_time=`date '+%Y%m%d%H%M'`

full(){

expdp \'/ as sysdba\' directory=DUMP_DIR SCHEMAS=${1} exclude=STATISTICS LOGFILE=expdp_${1}_${bak_time}.log dumpfile=expdp_${1}_${bak_time}_%U.dmp COMPRESSION=ALL CLUSTER=N PARALLEL=4

if [ $? == 0 ]
    then
      expdplist=`ls ${source_dir}/expdp_${1}_${bak_time}_*.dmp`
      for list in ${expdplist}
        do
          newlist=$(echo ${list}|sed "s/${bak_time}_//g"|awk -F '/' '{print $NF}')
        done
    find $rmpath -type f -name "expdp_${1}_*" -mtime +8 -exec rm -rf {} \;
else
    message="oracle expdp is fail on dbname ${2} "
fi
}

backup(){
    usernames=`sqlplus  -S "/ as sysdba" <<EOF
set echo off
set feedback off
set newpage none
set verify off
set pagesize 0
set termout off
set trimspool off
set trims on
set linesize 6000
set heading off
set timing off
set numwidth 38
select username from dba_users a where a.account_status='OPEN' and a.username  in ('OPS','CUB','CCM','CCZ');
EOF
`
    for username in ${usernames}
    do
    full ${username}
    done
}

main(){
   backup
}

main

3. 设置脚本权限

chown oracle:oinstall /u01/scripts/Oracle_Schema_Dump.sh
chmod 750 /u01/scripts/Oracle_Schema_Dump.sh

4. 设置定时任务

su - oracle
crontab -e
# 数据泵按用户导出 - 每天6点运行
0 6 * * * /u01/scripts/Oracle_Schema_Dump.sh

crontab -l

五、备份验证与监控

1. 检查备份状态

-- 检查RMAN备份状态
set line 234
SELECT 
    session_key, input_type, status, start_time, end_time
FROM v$rman_backup_job_details
ORDER BY start_time DESC
FETCH FIRST 10 ROWS ONLY;

-- 检查数据泵作业状态
set line 234
col JOB_NAME for a35
col STATE for a35
SELECT 
    job_name, state, degree, attached_sessions, datapump_sessions
FROM dba_datapump_jobs
ORDER BY job_name;

2. 创建备份报告脚本

vi /u01/scripts/backup_report.sh

#!/bin/bash
# Backup Report Script
REPORT_DIR="/u01/backup/reports"
mkdir -p $REPORT_DIR
REPORT_FILE="$REPORT_DIR/backup_report_$(date +%Y%m%d).txt"

{
echo "=============================================="
echo " Oracle Backup Report for $(date)"
echo "=============================================="
echo ""
echo "RMAN Backups (Last 3 Days):"
echo "----------------------------------------------"
ls -lh /u01/backup/rman/*$(date -d '2 days ago' +%Y%m%d)*.bkp 2>/dev/null
ls -lh /u01/backup/rman/*$(date -d '1 day ago' +%Y%m%d)*.bkp 2>/dev/null
ls -lh /u01/backup/rman/*$(date +%Y%m%d)*.bkp 2>/dev/null
echo ""
echo "Data Pump Exports (Last 3 Days):"
echo "----------------------------------------------"
ls -lh /u01/backup/dpump/full_expdp_*.dmp.gz 2>/dev/null | grep -E "$(date -d '2 days ago' +%Y%m%d)|$(date -d '1 day ago' +%Y%m%d)|$(date +%Y%m%d)"
} > $REPORT_FILE

六、灾难恢复测试

1. RMAN恢复测试脚本

vi /u01/scripts/rman_restore_test.sh

#!/bin/bash
# RMAN Restore Test Script
export ORACLE_SID=orcl
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
source /home/oracle/.bash_profile

# Create test database
rman target / <<EOF
STARTUP NOMOUNT
## RESTORE SPFILE TO '$ORACLE_HOME/dbs/spfile_tmp.ora' FROM '/u01/backup/rman/c-3799879052-20260427-02';
RESTORE CONTROLFILE FROM '/u01/backup/rman/latest_autobackup.bkp';
ALTER DATABASE MOUNT;
RUN{
ALLOCATE CHANNEL ch1 DEVICE TYPE DISK;
ALLOCATE CHANNEL ch2 DEVICE TYPE DISK;
ALLOCATE CHANNEL ch3 DEVICE TYPE DISK;
ALLOCATE CHANNEL ch4 DEVICE TYPE DISK;
ALLOCATE CHANNEL ch5 DEVICE TYPE DISK;
ALLOCATE CHANNEL ch6 DEVICE TYPE DISK;
ALLOCATE CHANNEL ch7 DEVICE TYPE DISK;
ALLOCATE CHANNEL ch8 DEVICE TYPE DISK;
RESTORE DATABASE;
RECOVER DATABASE;
RELEASE CHANNEL ch1;
RELEASE CHANNEL ch2;
RELEASE CHANNEL ch3;
RELEASE CHANNEL ch4;
}
ALTER DATABASE OPEN RESETLOGS;
SHUTDOWN IMMEDIATE;
EXIT;
EOF

图片.png

2. 数据泵导入测试脚本

vi /u01/scripts/impdp_test.sh

#!/bin/bash
# Data Pump Import Test Script
export ORACLE_SID=testdb
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
source /home/oracle/.bash_profile

# Create test directory
mkdir -p /u01/backup/test_import

# Import latest export
impdp \'/ as sysdba\' \
    directory=DPUMP_DIR \
    dumpfile=full_expdp_$(date -d '1 day ago' +%Y%m%d).dmp.gz \
    remap_schema=prod_user:test_user \
    remap_tablespace=prod_ts:test_ts \
    transform=segment_attributes:n \
    logfile=impdp_test_$(date +%Y%m%d).log

七、备份策略优化建议

  1. 增量备份策略：

    -- 配置每周日0级备份，其他日1级备份
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION ON;

  2. 多路复用控制文件：

    shu immediate
!cp /u01/oradata/control01.ctl /u02/oradata/control02.ctl
startup nomount
ALTER SYSTEM SET control_files='/u01/oradata/control01.ctl','/u02/oradata/control02.ctl' SCOPE=SPFILE;
alter database mount;
alter database open;

  3. 块更改跟踪（加速增量备份）：

    ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/u01/oradata/changetracking.dbf';

  4. 定期验证备份有效性：

定期进行备份恢复演练。

对所需文件运行 BACKUP VALIDATE… 命令。例如，输入以下命令来验证所有数据库文件和归档重做日志文件是否存在物理和逻辑损坏：

BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;

--还可以使用 VALIDATE 命令检查单个数据块，如下例所示：
VALIDATE DATAFILE 4 BLOCK 10 TO 13;

--还可以验证备份集，如下例所示：
VALIDATE BACKUPSET 3;

LIST BACKUP OF DATABASE;
REPORT NEED BACKUP;

八、故障处理指南

1. 常见错误及解决方案

错误代码 问题描述 解决方案
ORA-19809 备份空间不足 清理旧备份，增加存储
ORA-19566 数据文件损坏 从备份恢复，使用RMAN块恢复
ORA-27038 文件已存在 使用REPLACE参数或删除旧文件
EXP-00091 字符集问题 设置NLS_LANG环境变量

2. 紧急恢复流程

  1. 停止应用服务
  2. 使用最新RMAN备份恢复数据库
  3. 应用归档日志到故障时间点
  4. 打开数据库并验证数据
  5. 启动应用服务

九、写在最后

本文提供了完整的 Oracle 19c 备份解决方案，包含全备和增备脚本，以及表空间备份脚本，又有数据泵全库导出和分用户导出的方案，对于中小企业小数据库使用来说完全没有问题，但大型和超大型的数据库此方案仅供参考，当然大型数据库一般也都会配置专业的 DBA 来管理数据库。通过此方案，仅可确保 Oracle 19c 数据库的数据安全性和可恢复性，满足中小企业级备份要求。

十、官网参考链接

https://docs.oracle.com/en/database/oracle/oracle-database/19/bradv/part-overview-backup-recovery.html

全文完，希望可以帮到正在阅读的你，如果觉得有帮助，可以分享给你身边的朋友，同事，你关心谁就分享给谁，一起学习共同进步~~~

欢迎关注我的公众号【JiekeXu DBA之路】，一起学习新知识！
——————————————————————————
公众号：JiekeXu DBA之路
墨天轮：https://www.modb.pro/u/4347
CSDN ：https://blog.csdn.net/JiekeXu
ITPUB：https://blog.itpub.net/69968215
腾讯云：https://cloud.tencent.com/developer/user/5645107
——————————————————————————

facebook_pro_light_1920 × 1080 副本.png

墨力计划oraclermanoracle 数据泵备份恢复
最后修改时间：2026-04-28 09:45:46
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

文章被以下合辑收录

JiekeXu DBA之路（共249篇）
JiekeXu：来自西北，90 后，获得 11g OCP、11g OCM 认证，墨天轮 MVP，墨天轮 2021 十大突出贡献人物之一，此专栏记录一个菜鸟 DBA 学习成长之路，欢迎关注公众号 JiekeXu DBA之路，一起学习进步。

评论