Oracle数据库离线增量备份与恢复

举报
寒六先生 发表于 2021/03/11 18:43:17 2021/03/11
【摘要】 客户数据库版本为Oracle 11G 11.2.0.4版本； 客户Oracle数据库业务需要上云，数据量较大，RMAN备份后，备份集约有1T左右； 客户希望尽可能减少业务停机时间； 客户Oracle数据库在内网，无法直通公网；

Oracle数据库离线增量备份与恢复

项目背景：

  • 客户数据库版本为Oracle 11G 11.2.0.4版本；

  • 客户Oracle数据库业务需要上云，数据量较大，RMAN备份后，备份集约有1T左右；

  • 客户希望尽可能减少业务停机时间；

  • 客户Oracle数据库在内网，无法直通公网；


迁移方案：

  • 因为客户的Oracle数据库无法直连公网，所以只能采用离线迁移方式；

  • 为减少业务停机时间，本方案使用RMAN备份，分全量和增量两个备份集，分别上传到公有云；

  • 全量备份时间较长，定在凌晨 01:00开始，备份完成后通过其它主机上传到华为云；

  • 客户公网带宽为500Mbps，传输速度约50MB左右，完成1TB的备份集数据传输，约需要6个小时；

  • 全量备份执行、全量备份集上传，均无法业务中断，待全量备份集在云上恢复完成后，再在线下停业务、增量备份、上传增量备份集、云上恢复增量备份集；

  • 迁移方案拓扑简图如下所示：

迁移过程：

1. 数据库全量备份

# 在客户业务环境，执行Oracle数据库全量备份；
# 备份脚本如下：
$ more full_orcl.sh 
echo -e '******Start********_'$(date '+%Y%m%d%H%M%S')
export ORACLE_SID=orcl
export ORACLE_BASE=/oracle/app
export ORACLE_HOME=/oracle/app/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
rman target /<<EOF
run{
       allocate channel c1 type disk;
       backup as compressed backupset incremental level 0 tag 'orcl_incr_L0' format
       '/rmanbak/full_bak/orcl/full_%d_%T_%s' database include current controlfile;
       sql "alter system archive log current";
       backup as compressed backupset archivelog all format '/rmanbak/full_bak/orcl/arclog_0_%s_%p_%t_%d' delete input;
       crosscheck backup;
       delete noprompt expired backup;
       delete noprompt obsolete;
       crosscheck archivelog all;
       delete noprompt expired archivelog all;
       release channel c1;
}
exit;
EOF
​
# 执行数据库备份脚本：
$ ./full_orcl.sh

2. 全量备份集上传公有云

# 备份集通过客户公网，直接上传到华为云；
# 通过在客户主机上安装华为云obsutils，直接将备份集上传到华为云OBS桶；
# 上传过程略；

3. 云上进行全量备份集恢复

  • 注：云上数据库环境需要提前部署好；

  • 仅需要安装好Oracle数据库软件即可，无需建库；

  • 通过在源数据库，查看rman备份，确认全量备份集中，哪个备份文件中包含参数文件、控制文件，下面会用到；

3.1. 恢复参数文件：
$ rman target /
RMAN> startup nomount;
RMAN> restore spfile from '/rmanbak/full_bak/orcl/full_ORCL_20210311_15';
3.2. 恢复控制文件：
# 使用新恢复出来的参数文件，重启数据库
RMAN> shutdown immediate;
RMAN> startup nomount;
RMAN> restore controlfile from '/rmanbak/full_bak/orcl/full_ORCL_20210311_15';
3.3. 执行数据库磁盘文件restore：
RMAN> alter database mount;
RMAN> restore database;
# 注：需要注意，要把全量备份集的所有磁盘文件，都catalog到rman中：
RMAN> catalog start with '/rmanbak/full_bak/orcl/';
3.4. 执行数据库recover：
RMAN> recover database;
Starting recover at 11-MAR-21
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=297
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=298
channel ORA_DISK_1: reading from backup piece /rmanbak/full_bak/orcl/arclog_0_16_1_1066925664_ORCL
channel ORA_DISK_1: piece handle=/rmanbak/full_bak/orcl/arclog_0_16_1_1066925664_ORCL tag=TAG20210311T161424
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/oracle/app/fast_recovery_area/ORCL/archivelog/2021_03_11/o1_mf_1_297_j4mobbwl_.arc thread=1 sequence=297
channel default: deleting archived log(s)
archived log file name=/oracle/app/fast_recovery_area/ORCL/archivelog/2021_03_11/o1_mf_1_297_j4mobbwl_.arc RECID=8 STAMP=1066926666
archived log file name=/oracle/app/fast_recovery_area/ORCL/archivelog/2021_03_11/o1_mf_1_298_j4mobbxm_.arc thread=1 sequence=298
channel default: deleting archived log(s)
archived log file name=/oracle/app/fast_recovery_area/ORCL/archivelog/2021_03_11/o1_mf_1_298_j4mobbxm_.arc RECID=7 STAMP=1066926666
unable to find archived log
archived log thread=1 sequence=299
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/11/2021 16:31:08
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 299 and starting SCN of 6324617
# 注：在此处，我们需要将数据库归档日志，恢复到备份时刻的最后一个日志号；
# 根据上面错误提示，我们在recover时，需要指定thread=1 sequence=299；
​
# 再次执行recover：
RMAN> run {
set until sequence 299 thread 1;
recover database;
}
# 执行recover成功；
# 说明：因为后续还需要继续恢复增量备份集，所以此处切不可对数据库进行resetlogs操作；
3.5. 数据验证：
# 在此处可以简单确认恢复的数据是否可读；
# 不可以对数据库进行resetlogs操作，可以以read only方式打开，做简单查询：
$ sqlplus "/as sysdba"
SQL> alter database open read only;
SQL> select count(*) from xxx.xxxxx;
# 确认业务数据可以正常查询即可

4. 数据库增量备份

4.1.停业务
# 基于数据一致性考虑，最后一次增量备份之前，源端环境就不能有新增的数据了；
# 所以需要在源端将业务系统停机；
# Oracle数据库，需要确认已经没有客户连接session在连接，并关闭监听；
$ lsnrctl stop
$ netstat -ntlp | grep 1521
4.2.增量备份
# 在客户业务环境，执行Oracle数据库增量备份；
# 备份脚本如下：
$ more incr_orcl.sh 
echo -e '******Start********_'$(date '+%Y%m%d%H%M%S')
export ORACLE_SID=orcl
export ORACLE_BASE=/oracle/app
export ORACLE_HOME=/oracle/app/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
rman target /<<EOF
run{
       allocate channel c1 type disk;
       backup as compressed backupset incremental level 1 tag 'orcl_incr_L1' format
       '/rmanbak/incr_bak/orcl/incr_%d_%T_%s' database include current controlfile;
       sql "alter system archive log current";
       backup as compressed backupset archivelog all format '/rmanbak/incr_bak/orcl/arclog_1_%s_%p_%t_%d' delete input;
       crosscheck backup;
       delete noprompt expired backup;
       delete noprompt obsolete;
       crosscheck archivelog all;
       delete noprompt expired archivelog all;
       release channel c1;
}
exit;
EOF
​
# 执行数据库备份脚本：
$ ./incr_orcl.sh
4.3.增量备份集上传

上传过程此处不再赘述；

4.4.增量备份集恢复
# 将数据库启动到mount状态：
$ rman target /
RMAN> shutdown immediate
RMAN> startup mount
​
# 将增量备份集catalog到rman：
RMAN> catalog start with '/rmanbak/incr_bak/orcl/';
​
# 通过以下命令，确认备份集的最后一个归档日志：
RMAN> recover database;
Starting recover at 11-MAR-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /oracle/app/oradata/orcl/system01.dbf
destination for restore of datafile 00002: /oracle/app/oradata/orcl/sysaux01.dbf
destination for restore of datafile 00003: /oracle/app/oradata/orcl/undotbs01.dbf
destination for restore of datafile 00004: /oracle/app/oradata/orcl/users01.dbf
destination for restore of datafile 00005: /oracle/app/oradata/orcl/TABLESPACE.dbf
destination for restore of datafile 00006: /oracle/app/oradata/orcl/TBS_SHMSA01.dbf
destination for restore of datafile 00007: /oracle/app/oradata/orcl/SSDD_TBS01.dbf
destination for restore of datafile 00008: /oracle/app/oradata/orcl/SYNC_XUANCHUAN_TBS01.dbf
destination for restore of datafile 00009: /oracle/app/oradata/orcl/MarShareL201.dbf
destination for restore of datafile 00010: /oracle/app/oradata/orcl/MSAPUB_DATA01.dbf
destination for restore of datafile 00011: /oracle/app/oradata/orcl/MLP_SHCORPUS01.dbf
channel ORA_DISK_1: reading from backup piece /rmanbak/incr_bak/orcl/incr_ORCL_20210311_17
channel ORA_DISK_1: piece handle=/rmanbak/incr_bak/orcl/incr_ORCL_20210311_17 tag=OADB_INCR_L1
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
​
starting media recovery
​
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=299
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=300
channel ORA_DISK_1: reading from backup piece /rmanbak/incr_bak/orcl/arclog_1_19_1_1066926876_ORCL
channel ORA_DISK_1: piece handle=/rmanbak/incr_bak/orcl/arclog_1_19_1_1066926876_ORCL tag=TAG20210311T163436
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/oracle/app/fast_recovery_area/ORCL/archivelog/2021_03_11/o1_mf_1_299_j4mpshyt_.arc thread=1 sequence=299
channel default: deleting archived log(s)
archived log file name=/oracle/app/fast_recovery_area/ORCL/archivelog/2021_03_11/o1_mf_1_299_j4mpshyt_.arc RECID=9 STAMP=1066928175
archived log file name=/oracle/app/fast_recovery_area/ORCL/archivelog/2021_03_11/o1_mf_1_300_j4mpshz2_.arc thread=1 sequence=300
channel default: deleting archived log(s)
archived log file name=/oracle/app/fast_recovery_area/ORCL/archivelog/2021_03_11/o1_mf_1_300_j4mpshz2_.arc RECID=10 STAMP=1066928175
unable to find archived log
archived log thread=1 sequence=301
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/11/2021 16:56:17
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 301 and starting SCN of 6325298
​
# 再次执行recover恢复增量备份集：
RMAN> run {
set until sequence 301 thread 1;
recover database;
}
4.5.数据库启动&验证
$ sqlplus "/as sysdba"
SQL> shutdown immediate
SQL> startup mount;
SQL> alter database open resetlogs;
​
# 进行简单查询，确认业务数据状态；

数据库增量恢复完成！

【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
Oracle 数据库
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入