首页手记运用incremental...

运用incremental backup恢复归档GAP的DG-上篇

标签：

Oracle

经常听到有DBA因为对DATAGUARD监控不到位，导致归档日志和主库没有同步，更悲剧的是主库通常都设置了RMAN备份，

而全库备份结束后通常会删除归档日志。遇到这种情况，发现很多DBA都会着手重新从主库全库备份恢复到从库，

遇到小的数据库还好，对于动辄上百G或T的库往往会很悲剧，因为数据拷贝时间都相当的漫长。

而事实上对于从库SCN和主库差距并不大，也可以理解为归档差距并不多的DG（比如我们的一个库上线1年了，一个月没有同步差距也不算很大），

根本没必要全库回复，这种情况下增量恢复能快速解决问题，以下是笔者的一次具体操作。

故障现象：

因为周六主库要做历史数据迁移，DBA主动停止了和从库的归档同步。悲剧的是周日凌晨主库有个全库备份动作，

备份完成后把所有的归档都给删除了，这就造成了这部分归档的丢失，从库存在GAP。DBA打算重做DG，申请了一个

晚上8小时的时间，因为数据备份和迁移要5小时，实施和测试计划3小时完成。笔者分析丢失的日志很少，及时制止

了这一实施申请，要求DBA采用incremental recover（增量备份恢复）的方法实现主库和从库同步，

先确定备库的current scn，以此在主库上执行incremental backup，将备份传至备库，使用recover noredo方式恢复备库。

事实证明这种方法是完全可行的。

具体实施如下。

一、实施准备（从库关键数据确认）

1、SPFILE

create pfile='/home/oracle/dba/pfile_before_recover_dg.ora' from spfile;

-rw-r--r-- 1 oracle oinstall 1496 11-14 21:36 pfile_before_recover_dg.ora

drwxr-xr-x 2 oracle oinstall 4096 11-14 21:35 spfile

drwxr-xr-x 2 oracle oinstall 4096 06-01 11:17 expdata

drwxr-xr-x 2 oracle oinstall 4096 2011-04-26 script

[oracle@L-DB-163-18 dba]$ more *.ora

MYPORT.__db_cache_size=3741319168

MYPORT.__java_pool_size=16777216

MYPORT.__large_pool_size=16777216

MYPORT.__shared_pool_size=805306368

MYPORT.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/MYPORT/adump'

*.background_dump_dest='/u01/app/oracle/admin/MYPORT/bdump'

*.compatible='10.2.0.3.0'

*.control_files='/u01/datafile/control01.ctl','/u01/datafile/control02.ctl'#Restore Controlfile

*.core_dump_dest='/u01/app/oracle/admin/MYPORT/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=32

*.db_file_name_convert='+DATA/MYPORT/datafile','/oradata/datafile'

*.db_name='MYPORT'

*.db_unique_name='standby'

*.dispatchers=''

*.fal_client='STANDBY'

*.fal_server='MYPORT'

*.job_queue_processes=10

*.log_archive_config='DG_CONFIG=(MYPORT,standby)'

*.log_archive_dest_1='LOCATION=/u01/archive valid_for=(all_logfiles,all_roles) db_unique_name=standby'

*.log_archive_dest_2='service=MYPORT lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=MYPORT'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_max_processes=4

*.log_file_name_convert='+DATA/MYPORT/onlinelog','/u01/datafile'

*.open_cursors=1000

*.pga_aggregate_target=1092957696

*.processes=1000

*.remote_login_passwordfile='EXCLUSIVE'

*.sessions=885

*.sga_max_size=4589934592

*.sga_target=4589934592

*.shared_servers=0

*.standby_file_management='AUTO'

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/u01/app/oracle/admin/MYPORT/udump'

[oracle@L-DB-163-18 dba]$

2、查看在线LOG

cd /u01/datafile

[oracle@L-DB-163-18 dba]$ cd /u01/datafile

[oracle@L-DB-163-18 datafile]$ ls -lt

总计 1474052

-rw-r----- 1 oracle oinstall 19873792 11-14 21:39 control01.ctl

-rw-r----- 1 oracle oinstall 19873792 11-14 21:39 control02.ctl

-rw-r----- 1 oracle oinstall 104858112 11-14 21:39 stand03.log

-rw-r----- 1 oracle oinstall 104858112 11-14 21:38 stand01.log

-rw-r----- 1 oracle oinstall 104858112 11-14 20:22 stand02.log

-rw-r----- 1 oracle oinstall 104858112 11-14 18:14 stand04.log

-rw-r----- 1 oracle oinstall 104858112 11-11 02:18 stand05.log

-rw-r----- 1 oracle oinstall 104858112 11-11 02:18 stand06.log

-rw-r----- 1 oracle oinstall 20979712 07-14 04:41 temp01.dbf

-rw-r----- 1 oracle oinstall 104858112 07-14 03:44 group_4.270.697238219

-rw-r----- 1 oracle oinstall 104858112 07-14 03:44 group_4.271.697238221

-rw-r----- 1 oracle oinstall 104858112 07-14 03:44 group_3.268.697238217

-rw-r----- 1 oracle oinstall 104858112 07-14 03:44 group_3.269.697238219

-rw-r----- 1 oracle oinstall 104858112 07-14 03:44 group_2.264.697238179

-rw-r----- 1 oracle oinstall 104858112 07-14 03:44 group_2.265.697238179

-rw-r----- 1 oracle oinstall 104858112 07-14 03:44 group_1.262.697238177

-rw-r----- 1 oracle oinstall 104858112 07-14 03:44 group_1.263.697238177

[oracle@L-DB-163-18 datafile]$

3、查看数据文件

[oracle@L-DB-163-18 datafile]$ cd /oradata/datafile

[oracle@L-DB-163-18 datafile]$ ls -lt

总计 235078392

-rw-r----- 1 oracle oinstall 134225920 11-14 09:20 bbcled_data.588.727381165.dbf

-rw-r----- 1 oracle oinstall 16106135552 11-14 09:20 auditbbc.882.710070905.dbf

-rw-r----- 1 oracle oinstall 13631496192 11-14 09:20 eport_data.522.736786031.dbf

-rw-r----- 1 oracle oinstall 268443648 11-14 09:20 eport_index.779.736786171.dbf

-rw-r----- 1 oracle oinstall 26346135552 11-14 09:20 in_bbc_data.291.730331961.dbf

-rw-r----- 1 oracle oinstall 10737426432 11-14 09:20 in_bbc_data.752.758838665

-rw-r----- 1 oracle oinstall 5368717312 11-14 09:20 in_bbc_index.869.729797303.dbf

-rw-r----- 1 oracle oinstall 536879104 11-14 09:20 inman_data.617.718999513.dbf

-rw-r----- 1 oracle oinstall 134225920 11-14 09:20 inman_index.723.718999521.dbf

-rw-r----- 1 oracle oinstall 268443648 11-14 09:20 in_man_index.885.729797313.dbf

-rw-r----- 1 oracle oinstall 34358697984 11-14 09:20 in_hangzhou_data.362.747696959.dbf

-rw-r----- 1 oracle oinstall 10737426432 11-14 09:20 in_hangzhou_data.740.758652903

-rw-r----- 1 oracle oinstall 8388616192 11-14 09:20 in_hangzhou_index.821.729797323.dbf

-rw-r----- 1 oracle oinstall 104865792 11-14 09:20 in_xian_index.663.747696899.dbf

-rw-r----- 1 oracle oinstall 268443648 11-14 09:20 in_xian_index.724.729797333.dbf

-rw-r----- 1 oracle oinstall 34359730176 11-14 09:20 hangzhou.451.709317119.dbf

-rw-r----- 1 oracle oinstall 2147491840 11-14 09:20 sysaux.257.697238119.dbf

-rw-r----- 1 oracle oinstall 5368717312 11-14 09:20 system.256.697238117.dbf

-rw-r----- 1 oracle oinstall 1056768 11-14 09:20 tbs_catalog.791.752426905.dbf

-rw-r----- 1 oracle oinstall 1073750016 11-14 09:20 tbs_catalog.891.710434097.dbf

-rw-r----- 1 oracle oinstall 209723392 11-14 09:20 ts_test.348.704715437.dbf

-rw-r----- 1 oracle oinstall 7225745408 11-14 09:20 undotbs1.258.697238119.dbf

-rw-r----- 1 oracle oinstall 4294975488 11-14 09:20 undotbs2.267.697238205.dbf

-rw-r----- 1 oracle oinstall 34304827392 11-14 09:20 users.259.697238119.dbf

-rw-r----- 1 oracle oinstall 23571996672 11-14 09:20 users.604.752426949.dbf

-rw-r----- 1 oracle oinstall 536879104 11-14 09:20 xian.880.711538313.dbf

二、实施（从库关键数据确认）

具体步骤为：

1、先确定备库的current scn，以此在主库上执行incremental backup

SQL> select to_char(current_scn) from v$database;

TO_CHAR(CURRENT_SCN)

----------------------------------------

1508381043

SQL> SELECT * FROM V$ARCHIVE_GAP;

THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#

---------- ------------- --------------

1 12907 13164

2 10915 11003

2、在主库执行incremental备份

run

{

allocate channel d1 type disk;

allocate channel d2 type disk;

allocate channel d3 type disk;

allocate channel d4 type disk;

backup as compressed backupset incremental from SCN 1508381043 database format '/u01/rmanbak/for18dg/standby_%d_%T_%U.bak'

include current controlfile for standby filesperset=5 tag 'FOR STANDBY';

release channel d1;

release channel d2;

release channel d3;

release channel d4;

}

--------------------------------------

[oradba@oracle2 for18dg]$ rman target/

Recovery Manager: Release 10.2.0.4.0 - Production on 星期一 11月 14 21:44:27 2011

connected to target database: MYPORT (DBID=2649744285)

RMAN> run

{

allocate channel d1 type disk;

allocate channel d2 type disk;

allocate channel d3 type disk;

allocate channel d4 type disk;

backup as compressed backupset incremental from SCN 1508381043 database format '/u01/rmanbak/for18dg/standby_%d_%T_%U.bak'

include current controlfile for standby filesperset=5 tag 'FOR STANDBY';

release channel d1;

release channel d2;

release channel d3;

release channel d4;

}2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13>

using target database control file instead of recovery catalog

allocated channel: d1

channel d1: sid=909 instance=MYPORT2 devtype=DISK

allocated channel: d2

channel d2: sid=993 instance=MYPORT2 devtype=DISK

allocated channel: d3

channel d3: sid=884 instance=MYPORT2 devtype=DISK

allocated channel: d4

channel d4: sid=886 instance=MYPORT2 devtype=DISK

Starting backup at 2011-11-14 21:44:34

channel d1: starting compressed full datafile backupset

channel d1: specifying datafile(s) in backupset

input datafile fno=00004 name=+DATA/MYPORT/datafile/users.259.697238119

input datafile fno=00001 name=+DATA/MYPORT/datafile/system.256.697238117

input datafile fno=00002 name=+DATA/MYPORT/datafile/undotbs1.258.697238119

channel d1: starting piece 1 at 2011-11-14 21:44:34

channel d2: starting compressed full datafile backupset

channel d2: specifying datafile(s) in backupset

input datafile fno=00024 name=+DATA/MYPORT/datafile/users.604.752426949

input datafile fno=00025 name=+DATA/MYPORT/datafile/in_hangzhou_data.740.758652903

input datafile fno=00014 name=+DATA/MYPORT/datafile/in_bbc_index.869.729797303

channel d2: starting piece 1 at 2011-11-14 21:44:34

channel d3: starting compressed full datafile backupset

channel d3: specifying datafile(s) in backupset

input datafile fno=00009 name=+DATA/MYPORT/datafile/auditbbc.882.710070905

input datafile fno=00019 name=+DATA/MYPORT/datafile/eport_data.522.736786031

input datafile fno=00016 name=+DATA/MYPORT/datafile/in_hangzhou_index.821.729797323

input datafile fno=00021 name=+DATA/MYPORT/datafile/in_xian_index.663.747696899

input datafile fno=00023 name=+DATA/MYPORT/datafile/tbs_catalog.791.752426905

channel d3: starting piece 1 at 2011-11-14 21:44:34

channel d4: starting compressed full datafile backupset

channel d4: specifying datafile(s) in backupset

input datafile fno=00022 name=+DATA/MYPORT/datafile/in_hangzhou_data.362.747696959

input datafile fno=00005 name=+DATA/MYPORT/datafile/undotbs2.267.697238205

input datafile fno=00006 name=+DATA/MYPORT/datafile/ts_test.348.704715437

input datafile fno=00012 name=+DATA/MYPORT/datafile/inman_index.723.718999521

input datafile fno=00013 name=+DATA/MYPORT/datafile/bbcled_data.588.727381165

channel d4: starting piece 1 at 2011-11-14 21:44:34

channel d4: finished piece 1 at 2011-11-14 21:51:10

piece handle=/u01/rmanbak/for18dg/standby_MYPORT_20111114_pamrlq22_1_1.bak tag=FOR STANDBY comment=NONE

channel d4: backup set complete, elapsed time: 00:06:36

channel d4: starting compressed full datafile backupset

channel d4: specifying datafile(s) in backupset

input datafile fno=00007 name=+DATA/MYPORT/datafile/hangzhou.451.709317119

input datafile fno=00003 name=+DATA/MYPORT/datafile/sysaux.257.697238119

input datafile fno=00010 name=+DATA/MYPORT/datafile/tbs_catalog.891.710434097

input datafile fno=00011 name=+DATA/MYPORT/datafile/xian.880.711538313

input datafile fno=00020 name=+DATA/MYPORT/datafile/eport_index.779.736786171

channel d4: starting piece 1 at 2011-11-14 21:51:13

channel d1: finished piece 1 at 2011-11-14 21:53:08

piece handle=/u01/rmanbak/for18dg/standby_MYPORT_20111114_p7mrlq22_1_1.bak tag=FOR STANDBY comment=NONE

channel d1: backup set complete, elapsed time: 00:08:34

channel d1: starting compressed full datafile backupset

channel d1: specifying datafile(s) in backupset

input datafile fno=00018 name=+DATA/MYPORT/datafile/in_bbc_data.291.730331961

input datafile fno=00026 name=+DATA/MYPORT/datafile/in_bbc_data.752.758838665

input datafile fno=00008 name=+DATA/MYPORT/datafile/inman_data.617.718999513

input datafile fno=00015 name=+DATA/MYPORT/datafile/in_man_index.885.729797313

input datafile fno=00017 name=+DATA/MYPORT/datafile/in_xian_index.724.729797333

channel d1: starting piece 1 at 2011-11-14 21:53:10

channel d2: finished piece 1 at 2011-11-14 21:53:45

piece handle=/u01/rmanbak/for18dg/standby_MYPORT_20111114_p8mrlq22_1_1.bak tag=FOR STANDBY comment=NONE

channel d2: backup set complete, elapsed time: 00:09:11

channel d2: starting compressed full datafile backupset

channel d2: specifying datafile(s) in backupset

including standby control file in backupset

channel d2: starting piece 1 at 2011-11-14 21:53:47

channel d2: finished piece 1 at 2011-11-14 21:53:48

piece handle=/u01/rmanbak/for18dg/standby_MYPORT_20111114_pdmrlqj9_1_1.bak tag=FOR STANDBY comment=NONE

channel d2: backup set complete, elapsed time: 00:00:03

channel d4: finished piece 1 at 2011-11-14 21:56:53

piece handle=/u01/rmanbak/for18dg/standby_MYPORT_20111114_pbmrlqee_1_1.bak tag=FOR STANDBY comment=NONE

channel d4: backup set complete, elapsed time: 00:05:43

channel d3: finished piece 1 at 2011-11-14 21:57:08

piece handle=/u01/rmanbak/for18dg/standby_MYPORT_20111114_p9mrlq22_1_1.bak tag=FOR STANDBY comment=NONE

channel d3: backup set complete, elapsed time: 00:12:34

channel d1: finished piece 1 at 2011-11-14 21:59:23

piece handle=/u01/rmanbak/for18dg/standby_MYPORT_20111114_pcmrlqi5_1_1.bak tag=FOR STANDBY comment=NONE

channel d1: backup set complete, elapsed time: 00:06:14

Finished backup at 2011-11-14 21:59:23

released channel: d1

released channel: d2

released channel: d3

released channel: d4

RMAN>

--------------------------------------

3、将备份集传输到备库的/data/oracle/backup/rman目录

scp -r root@172.26.163.14:/u01/rmanbak/for18dg/ /bak/from14/

[root@L-DB-163-18 bak]# scp -r root@172.26.163.14:/u01/rmanbak/for18dg/ /bak/from14/

root@172.26.163.14's password:

standby_MYPORT_20111114_pcmrlqi5_1_1.bak 100% 341MB 11.4MB/s 00:30

standby_MYPORT_20111114_p9mrlq22_1_1.bak 100% 190MB 11.2MB/s 00:17

standby_MYPORT_20111114_pamrlq22_1_1.bak 100% 94MB 11.7MB/s 00:08

standby_MYPORT_20111114_p8mrlq22_1_1.bak 100% 159MB 11.4MB/s 00:14

standby_MYPORT_20111114_pbmrlqee_1_1.bak 100% 49MB 9.7MB/s 00:05

standby_MYPORT_20111114_p7mrlq22_1_1.bak 100% 305MB 11.3MB/s 00:27

standby_MYPORT_20111114_pdmrlqj9_1_1.bak 100% 1520KB 1.5MB/s 00:00

[root@L-DB-163-18 bak]#

职场休闲incremental dataguarORACLE数据库

点击查看更多内容

为 TA 点赞

若觉得本文不错，就分享一下吧！

评论

评论

共同学习，写下你的评论

评论加载中...

展开查看更多评论

作者其他优质文章

正在加载中

PIPIONE

手记
篇

粉丝

147

获赞与收藏

702

关注作者，订阅最新文章

相关文章推荐

RMAN优缺点及RMAN备份及恢复步骤

Oracle Rman备份恢复和管理

oracle备份恢复之跨越归档进行恢复

RAC环境下的备份与恢复（一）

Oracle RAC与DG

阅读免费教程

后端通用面试教程

41个小节 30273 342

网络编程入门教程

20个小节 12461 235

Pandas 入门教程

25个小节 18362 330

推荐

评论

收藏

共同学习，写下你的评论



感谢您的支持，我会继续努力的～

扫码打赏，你说多少就多少

赞赏金额会直接到老师账户

支付方式

打开微信扫一扫，即可进行扫码打赏哦

今天注册有机会得

100积分直接送

付费专栏免费学

大额优惠券免费领

立即参与放弃机会

点击
抽奖

慕课手记新用户专享福利

恭喜你，你的运气太好了，居然抽中了 100个积分！

恭喜你，抽中了价值元的专栏！

太棒了，直接落到你账户里！

积分商城里的罗技鼠标、机械键盘、
Kindle 阅读器、小米平衡车
Apple iPad （10.2英寸）、大额优惠券
在等着你去兑换了噢

作者：

免费赠送

兑换码：1111222211 复制

优惠券可用于购买实战课、体系课
无门槛使用

先去看看，有什么好东西马上兑换我爱学习，选课去


热搜

最近搜索清空

运用incremental backup恢复归档GAP的DG-上篇

相关文章推荐

阅读免费教程