运用incremental backup恢复归档GAP的DG-上篇-原创手记-慕课网

经常听到有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数据库