93-Oracle 23ai free RMAN-实操

使用现在环境中的Oracle 23ai free，其中实例为FREE实例（CDB）与FREEPDB1（PDB）的RMAN备份恢复全流程实操记录。11g和19c的在其他的安装和备份记录中。

由于是free版本，show all，会提示2个warning

RMAN-06908: warning: operation will not run in parallel on the allocated channels
RMAN-06909: warning: parallelism require Enterprise Edition

一、备份恢复最佳实践框架​

环境要求​

• 数据库处于ARCHIVELOG模式​（支持在线备份与时间点恢复）
• 启用控制文件自动备份​（避免控制文件丢失导致恢复失败）
• 使用Fast Recovery Area (FRA)​​ 集中管理备份文件
• 定期验证备份有效性​

备份策略设计​

备份类型	频率	保留策略	命令示例
全量备份	每周日	保留30天	BACKUP DATABASE PLUS ARCHIVELOG
增量备份	每日	基于恢复窗口	BACKUP INCREMENTAL LEVEL 1
归档日志备份	每小时	备份后自动删除	BACKUP ARCHIVELOG ALL DELETE INPUT

二、全流程备份操作步骤​

​1. 预检查与配置​

-- 检查归档模式
SELECT log_mode FROM v$database; -- 需返回 ARCHIVELOG
--
LOG_MODE
------------
ARCHIVELOG
-- 若未启用归档模式：
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

-- 配置FRA（Fast Recovery Area）
SQL> ALTER SYSTEM SET db_recovery_file_dest_size=50G;
SQL> ALTER SYSTEM SET db_recovery_file_dest='/opt/oracle/DB_FRA';
--
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_instance_recovery_bloom_filter_size integer     1048576
db_recovery_auto_rekey               string      ON
db_recovery_file_dest                string      /opt/oracle/DB_FRA
db_recovery_file_dest_size           big integer 50G
recovery_parallelism                 integer     0
remote_recovery_file_dest            string
transaction_recovery                 string      ENABLED

2. RMAN配置脚本 

-- 连接RMAN
rman target / 

-- 关键配置
CONFIGURE CONTROLFILE AUTOBACKUP ON; 
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS; 
CONFIGURE DEVICE TYPE DISK PARALLELISM 4; 
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;
--
[oracle@OL97 ~]$ rman target /

Recovery Manager: Release 23.0.0.0.0 - Production on Fri Jun 27 18:46:07 2025
Version 23.8.0.25.04
Copyright (c) 1982, 2025, Oracle and/or its affiliates.  All rights reserved.
connected to target database: FREE (DBID=1475650002)
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
…………
new RMAN configuration parameters are successfully stored

3. 执行全量备份（CDB+PDB）​​

RUN块语法,脚本整体包含在RUN { ... }块中，这是RMAN执行多命令的标准结构

可以保存为 /home/oracle/rman_backup_cdb.rman

rman target / @/home/oracle/rman_backup_cdb.rman log=/logs/rman_full_$(date +%Y%m%d).log

RUN {
    -- 手动分配4个通道（并行度=CPU核心数/2）
    ALLOCATE CHANNEL ch1 DEVICE TYPE DISK MAXPIECESIZE 10G;
    ALLOCATE CHANNEL ch2 DEVICE TYPE DISK MAXPIECESIZE 10G;
    ALLOCATE CHANNEL ch3 DEVICE TYPE DISK MAXPIECESIZE 10G;
    ALLOCATE CHANNEL ch4 DEVICE TYPE DISK MAXPIECESIZE 10G;
    
    -- 强制日志切换确保事务一致性（包含所有未归档日志
    SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
    
    -- 备份CDB及所有PDB（含压缩和归档日志，自动删除已备份的归档）
    BACKUP AS COMPRESSED BACKUPSET 
        DATABASE 
        PLUS ARCHIVELOG 
        DELETE ALL INPUT  -- 备份后自动删除已处理的归档日志
        TAG 'CDB_FULL_BACKUP'
        FORMAT '/orabackup/rman/%d_CDB_FULL_%T_%U.bkpset';  -- %d=库名, %T=时间, %U=唯一ID
    
    -- 单独备份PDB FREEPDB1（独立备份集便于快速恢复）
    BACKUP AS COMPRESSED BACKUPSET 
        PLUGGABLE DATABASE FREEPDB1 
        TAG 'PDB_FULL_BACKUP'
        FORMAT '/orabackup/rman/%d_FREEPDB1_%T_%U.bkpset';
    
    -- 独立备份控制文件与SPFILE（避免依赖主备份集）
    BACKUP CURRENT CONTROLFILE 
        TAG 'CTRL_BACKUP' 
        FORMAT '/orabackup/rman/control_%d_%T.ctl';
    BACKUP SPFILE 
        TAG 'SPFILE_BACKUP' 
        FORMAT '/orabackup/rman/spfile_%d_%T.bak';
    
    -- 备份后维护操作
    CROSSCHECK BACKUP;      -- 验证备份文件在磁盘上的有效性
    DELETE NOPROMPT EXPIRED BACKUP;  -- 清理失效备份（RMAN元数据与物理文件不一致）
    DELETE NOPROMPT OBSOLETE;        -- 按保留策略删除过期备份
    
    -- 释放所有通道
    RELEASE CHANNEL ch1;
    RELEASE CHANNEL ch2;
    RELEASE CHANNEL ch3;
    RELEASE CHANNEL ch4;
}
---以下为日志
input archived log thread=1 sequence=82 RECID=78 STAMP=1204557958
channel ORA_DISK_1: starting piece 1 at 27-JUN-25
channel ORA_DISK_1: finished piece 1 at 27-JUN-25
piece handle=/opt/oracle/DB_FRA/FREE/backupset/2025_06_27/o1_mf_annnn_FULL_BACKUP_n5wxx0pg_.bkp tag=FULL_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 27-JUN-25
--
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/opt/oracle/oradata/FREE/sysaux01.dbf
input datafile file number=00001 name=/opt/oracle/oradata/FREE/system01.dbf
input datafile file number=00011 name=/opt/oracle/oradata/FREE/undotbs01.dbf
input datafile file number=00007 name=/opt/oracle/oradata/FREE/users01.dbf
channel ORA_DISK_1: starting piece 1 at 27-JUN-25

 4. 验证备份有效性​

-- 检查备份集
LIST BACKUP TAG 'FULL_BACKUP';
-- 模拟恢复测试
VALIDATE BACKUPSET <备份集ID>; 
RESTORE DATABASE VALIDATE;  -- 不实际恢复，仅验证
--
RESTORE DATABASE VALIDATE;
Starting restore at 27-JUN-25
using channel ORA_DISK_1

channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /opt/oracle/DB_FRA/FREE/backupset/2025_06_27/o1_mf_nnndf_TAG20250627T185158_n5wy0lk8_.bkp

三、恢复场景模拟​

​场景1：整库完全恢复（CDB级）

RUN {
    STARTUP MOUNT;
    RESTORE DATABASE;
    RECOVER DATABASE;
    ALTER DATABASE OPEN RESETLOGS; 
}

 场景2：PDB级恢复（FREEPDB1数据文件损坏）​​

-- 步骤1：离线PDB
ALTER PLUGGABLE DATABASE FREEPDB1 CLOSE IMMEDIATE;

-- 步骤2：RMAN恢复PDB
RUN {
    RESTORE PLUGGABLE DATABASE FREEPDB1;
    RECOVER PLUGGABLE DATABASE FREEPDB1;
    ALTER PLUGGABLE DATABASE FREEPDB1 OPEN;
}

 场景3：时间点恢复（误删表数据）​​

RUN {
    SET UNTIL TIME "TO_DATE('2025-06-27 14:00:00', 'YYYY-MM-DD HH24:MI:SS')";
    RESTORE DATABASE;
    RECOVER DATABASE;
    ALTER DATABASE OPEN RESETLOGS; 
}

 场景4：控制文件丢失恢复​

STARTUP NOMOUNT;
RESTORE CONTROLFILE FROM AUTOBACKUP;  -- 自动定位最新控制文件备份
ALTER DATABASE MOUNT;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;

 四、自动化备份脚本示例​

#!/bin/bash
export ORACLE_SID=FREE
export ORACLE_HOME=/opt/oracle/product/23ai/dbhomeFree
export PATH=$ORACLE_HOME/bin:$PATH

rman target / <<EOF
RUN {
    BACKUP INCREMENTAL LEVEL 1 
        FOR RECOVER OF COPY WITH TAG 'DAILY_INCR' DATABASE;
    BACKUP ARCHIVELOG ALL NOT BACKED UP 1 TIMES;
    DELETE NOPROMPT OBSOLETE;  -- 自动清理过期备份
}
EXIT;
EOF

 cron定时任务配置​（每日凌晨1点执行）

--cron定时任务
0 1 * * * /home/oracle/rman_backup.sh >> /logs/rman_backup.log 2>&1

五、关键验证点与报告​

备份完整性检查

CROSSCHECK BACKUP;  -- 验证备份文件是否可用
REPORT OBSOLETE;    -- 列出过期备份

恢复后验证​

• 检查PDB状态：SELECT name, open_mode FROM v$pdbs;
• 验证业务表数据一致性

日志分析​

• 检查RMAN日志：/logs/rman_backup.log
• 查看告警日志：$ORACLE_BASE/diag/rdbms/free/FREE/trace/alert_FREE.log

六、最佳实践总结​

多租户环境特别建议​

• CDB级备份保障容器元数据安全
• PDB级备份支持快速单库恢复

灾难防护措施​

• 备份文件异地存储（如SCP传输至远程服务器）
• 每月全库恢复演练

性能优化​

• 使用COMPRESSED BACKUPSET减少磁盘占用
• 并行通道（PARALLELISM）加速备份