上一篇【第37篇】Oracle角色与PROFILE管理详解下一篇【第39篇】Oracle数据库恢复技术详解摘要数据库备份是DBA最重要的职责之一制定科学完整的备份策略是确保业务连续性的基石。本文系统讲解Oracle备份策略的设计方法包括RPO/RTO目标、备份介质选择、备份窗口规划、RMAN备份的核心命令与策略配置全备、增量备份、差异备份、累积备份、以及数据泵EXPDP/IMPDP逻辑备份的实战使用并提供一套完整的企业级备份方案模板。一、备份策略的核心指标1.1 RPO 与 RTO指标全称含义备份影响RPORecovery Point Objective恢复点目标最多允许丢失多长时间的数据RPO越小备份频率越高RTORecovery Time Objective恢复时间目标最多允许停机多长时间RTO越小需要更快的恢复手段示例金融核心系统RPO 0不允许丢任何数据→ 需要实时日志归档 Data Guard电商订单系统RPO 1小时 → 每小时备份归档日志报表分析系统RPO 24小时 → 每日一次全备即可1.2 备份类型选择完整备份策略备份量最大恢复最快 每天全备 → 适合小型数据库 50GB 增量备份策略最优化方案 周日Level 0 全备基础备份 周一~周六Level 1 差异增量备份只备份Level 0后变化的块 累积备份策略恢复更快 周日Level 0 全备 周一~周六Level 1 累积增量每次都备份Level 0后的全部变化二、RMAN 备份配置2.1 RMAN 基础配置# 连接 RMANrman target /# 查看当前所有配置RMANSHOW ALL;rman target /EOF -- 配置备份保留策略保留7天内可恢复的备份 CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; -- 或保留最近2份全备 -- CONFIGURE RETENTION POLICY TO REDUNDANCY 2; -- 启用控制文件自动备份强烈推荐 CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO /backup/rman/ctlfile/cf_%F; -- 配置默认备份路径和格式 CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT /backup/rman/%d_%T_%U; -- 启用压缩减少备份集大小约50%稍微增加CPU CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET; -- 配置并行通道数提高备份速度 CONFIGURE DEVICE TYPE DISK PARALLELISM 4; EXIT; EOF三、RMAN 全量备份# 全量备份脚本含归档日志rman target /EOF RUN { -- 分配4个并行通道 ALLOCATE CHANNEL c1 DEVICE TYPE DISK FORMAT /backup/rman/full/%d_%T_%U; ALLOCATE CHANNEL c2 DEVICE TYPE DISK FORMAT /backup/rman/full/%d_%T_%U; ALLOCATE CHANNEL c3 DEVICE TYPE DISK FORMAT /backup/rman/full/%d_%T_%U; ALLOCATE CHANNEL c4 DEVICE TYPE DISK FORMAT /backup/rman/full/%d_%T_%U; -- 备份数据库 归档日志删除已备份的归档日志 BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG DELETE INPUT TAG FULL_WEEKLY; -- 删除过期备份 DELETE NOPROMPT OBSOLETE; RELEASE CHANNEL c1; RELEASE CHANNEL c2; RELEASE CHANNEL c3; RELEASE CHANNEL c4; } EXIT; EOF四、RMAN 增量备份4.1 增量备份原理Level 0 备份周日备份所有已写入的数据块 Level 1 差异增量周一-周六只备份自上次Level 1或Level 0以来变化的数据块 Level 1 累积增量周一-周六备份自上次Level 0以来所有变化的数据块4.2 增量备份脚本# level_backup.shBACKUP_LEVEL$1# 参数0 或 1rman target /EOF RUN { ALLOCATE CHANNEL c1 DEVICE TYPE DISK FORMAT /backup/rman/incr/%d_%T_%U; ALLOCATE CHANNEL c2 DEVICE TYPE DISK FORMAT /backup/rman/incr/%d_%T_%U; BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL$BACKUP_LEVELDATABASE TAG INCR_LEVEL${BACKUP_LEVEL}_$(date%Y%m%d); -- 备份归档日志 BACKUP ARCHIVELOG ALL DELETE INPUT; -- 清理过期备份 DELETE NOPROMPT OBSOLETE; RELEASE CHANNEL c1; RELEASE CHANNEL c2; } EXIT; EOF4.3 块变更跟踪Block Change Tracking启用此特性可极大加速增量备份Oracle 维护一个二进制文件记录哪些块被修改避免增量备份时扫描所有数据块。-- 启用块变更跟踪企业版特性ALTERDATABASEENABLEBLOCK CHANGE TRACKINGUSINGFILE/u01/oracle/admin/testdb/change_tracking.f;-- 查看状态SELECTstatus,filenameFROMv$block_change_tracking;-- 禁用ALTERDATABASEDISABLEBLOCK CHANGE TRACKING;五、备份集与归档日志管理5.1 验证备份可用性rman target /EOF -- 扫描并验证备份集标记过期/损坏 CROSSCHECK BACKUP; CROSSCHECK ARCHIVELOG ALL; -- 删除过期备份 DELETE NOPROMPT EXPIRED BACKUP; DELETE NOPROMPT EXPIRED ARCHIVELOG ALL; -- 验证备份集数据完整性读取所有块 VALIDATE BACKUPSET ALL; VALIDATE DATABASE; EXIT; EOF5.2 查看备份信息rman target /EOF -- 列出所有备份 LIST BACKUP SUMMARY; LIST BACKUP OF DATABASE; LIST BACKUP OF ARCHIVELOG ALL; -- 查看需要的归档日志 LIST ARCHIVELOG ALL; EXIT; EOF六、数据泵EXPDP/IMPDP逻辑备份逻辑备份不能替代物理备份但作为补充手段非常有价值选择性导出/导入单个表或Schema跨平台数据迁移数据脱敏后导出6.1 创建数据泵目录CREATEORREPLACEDIRECTORY dp_dirAS/backup/datapump;GRANTREAD,WRITEONDIRECTORY dp_dirTOsystem;6.2 数据泵导出EXPDP# 导出整个数据库每天一次逻辑全备expdp system/password\FULLy\DIRECTORYdp_dir\DUMPFILEfull_$(date%Y%m%d)_%U.dmp\LOGFILEfull_$(date%Y%m%d).log\PARALLEL4\FILESIZE4G\COMPRESSIONALL# 导出指定Schemaexpdp system/password\SCHEMASscott,hr\DIRECTORYdp_dir\DUMPFILEschema_$(date%Y%m%d).dmp\LOGFILEschema_exp.log# 导出指定表expdp system/password\TABLESscott.emp,scott.dept\DIRECTORYdp_dir\DUMPFILEtables_$(date%Y%m%d).dmp\LOGFILEtables_exp.log6.3 数据泵导入IMPDP# 导入整个数据库impdp system/password\FULLy\DIRECTORYdp_dir\DUMPFILEfull_20240115_%U.dmp\LOGFILEfull_imp.log\PARALLEL4# 导入指定Schema重映射到新用户impdp system/password\SCHEMASscott\DIRECTORYdp_dir\DUMPFILEschema_20240115.dmp\REMAP_SCHEMAscott:new_scott\REMAP_TABLESPACEusers_data:new_data_ts\LOGFILEschema_imp.log# 仅导入表定义不导入数据impdp system/password\SCHEMASscott\DIRECTORYdp_dir\DUMPFILEschema_20240115.dmp\CONTENTMETADATA_ONLY\LOGFILEmeta_imp.log七、企业级备份方案模板7.1 备份策略矩阵备份类型频率保留时间目标RMAN Level 0 全备每周日凌晨 2:004周本地远程RMAN Level 1 增量备份每天凌晨 2:007天本地归档日志备份每小时7天本地远程数据泵 Schema 备份每天 22:0030天本地控制文件备份每次备份自动触发7天本地7.2 自动化备份脚本Cron 调度#!/bin/bash# oracle_backup.sh# 用法: oracle_backup.sh [level0|level1|archivelog|expdp]ORACLE_SIDtestdbORACLE_HOME/u01/oracle/product/11.2.0/dbhome_1BACKUP_BASE/backupLOG_DIR/backup/logsTODAY$(date%Y%m%d)case$1inlevel0)# 每周日执行rman target /log$LOG_DIR/rman_level0_$TODAY.logEOF RUN { ALLOCATE CHANNEL c1 DEVICE TYPE DISK FORMAT $BACKUP_BASE/rman/level0/%U; ALLOCATE CHANNEL c2 DEVICE TYPE DISK FORMAT $BACKUP_BASE/rman/level0/%U; BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG DELETE INPUT; DELETE NOPROMPT OBSOLETE; } EXIT; EOF;;level1)# 每天执行rman target /log$LOG_DIR/rman_level1_$TODAY.logEOF RUN { ALLOCATE CHANNEL c1 DEVICE TYPE DISK FORMAT $BACKUP_BASE/rman/level1/%U; BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 1 DATABASE; BACKUP ARCHIVELOG ALL DELETE INPUT; } EXIT; EOF;;expdp)# 逻辑备份expdp system/passwordFULLyDIRECTORYdp_dir\DUMPFILEfull_${TODAY}_%U.dmpLOGFILEfull_${TODAY}.log\PARALLEL4FILESIZE4Gfind$BACKUP_BASE/datapump-mtime30-name*.dmp-delete;;esac八、总结Oracle备份策略的核心要点明确RPO/RTO决定备份频率和恢复手段RMAN 为主Level 0 Level 1 增量备份策略开启归档模式必须是热备和时间点恢复的前提块变更跟踪大型数据库增量备份必备企业版数据泵为辅逻辑备份适合选择性恢复和迁移定期验证备份CROSSCHECK VALIDATE备份不验证等于没有备份多地存储本地备份 异地备份防单点灾难上一篇【第37篇】Oracle角色与PROFILE管理详解下一篇【第39篇】Oracle数据库恢复技术详解参考资料《Oracle 11g数据库管理员指南》— 刘宪军著Oracle官方文档Database Backup and Recovery User’s GuideOracle官方文档Database Utilities - Oracle Data Pump