93-Oracle 23ai free RMAN-实操

原创已于 2025-06-27 19:38:24 修改 · 475 阅读

6 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库 #database #sql #大数据

于 2025-06-27 19:20:08 首次发布

使用现在环境中的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）加速备份

93-Oracle 23ai free RMAN-实操

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

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

​1. 预检查与配置​

2. RMAN配置脚本

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

4. 验证备份有效性​

三、恢复场景模拟​

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

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

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

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

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

五、关键验证点与报告​

六、最佳实践总结​