Oracle数据库离线增量备份与恢复-伙伴云

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

网友投稿 1514 2022-05-29

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

项目背景：

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

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

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

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

迁移方案：

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

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

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

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

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

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

迁移过程：

# 在客户业务环境，执行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 /<

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

# 备份集通过客户公网，直接上传到华为云；

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

# 上传过程略；

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

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

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

$ rman target /

RMAN> startup nomount;

RMAN> restore spfile from '/rmanbak/full_bak/orcl/full_ORCL_20210311_15';

# 使用新恢复出来的参数文件，重启数据库

RMAN> shutdown immediate;

RMAN> startup nomount;

RMAN> restore controlfile from '/rmanbak/full_bak/orcl/full_ORCL_20210311_15';

RMAN> alter database mount;

RMAN> restore database;

# 注：需要注意，要把全量备份集的所有磁盘文件，都catalog到rman中：

RMAN> catalog start with '/rmanbak/full_bak/orcl/';

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操作；

# 在此处可以简单确认恢复的数据是否可读；

# 不可以对数据库进行resetlogs操作，可以以read only方式打开，做简单查询：

$ sqlplus "/as sysdba"

SQL> alter database open read only;

SQL> select count(*) from xxx.xxxxx;

# 确认业务数据可以正常查询即可

# 基于数据一致性考虑，最后一次增量备份之前，源端环境就不能有新增的数据了；

# 所以需要在源端将业务系统停机；

# Oracle数据库，需要确认已经没有客户连接session在连接，并关闭监听；

$ lsnrctl stop

$ netstat -ntlp | grep 1521

# 在客户业务环境，执行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 /<

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

上传过程此处不再赘述；

# 将数据库启动到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

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

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;

}

$ sqlplus "/as sysdba"

SQL> shutdown immediate

SQL> startup mount;

SQL> alter database open resetlogs;

# 进行简单查询，确认业务数据状态；

数据库增量恢复完成！

Oracle 数据库

在线表格如何导出excel文件，以最简便的方法实现

1514 2022-05-29

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

Excel在线云编辑，锻炼你的数据技能

在线表格如何导出excel文件，以最简便的方法实现

OA无纸化办公公司，数字化时代的创新之举

推荐文章

企业生产管理是什么，企业生产管理软件

进盘点进销存软件排行榜前十名

进销存系统哪个简单好用？进销存系统优点

工厂生产管理（工厂生产管理流程及制度）

生产管理软件，机械制造业生产管理，制造业生产过程管理软件

进销存软件和ERP有什么区别？进销存与erp软件理解

进销存如何进行库存管理

如何利用excel制作销售订单管理系统？

数据库订单管理系统有哪些功能？数据库订单管理系统怎么设计？

什么是数据库管理系统？

最近发表

热评文章

零代码开发是什么？2022低代码平台排行榜

智能进销存库存管理系统（智慧进销存）

在线文档哪家强？8款在线文档编辑软件推荐

WPS2016怎么绘制简单的价格表?

定制订单管理系统（为特定需求定制的订单管理系统）

什么是在线文档？怎么发在线文档

友情链接