前提：
自ORACLE11G开始，如果DATA GUARD物理备库存在ARCHIVE GAP，并且这个GAP缺口过大，可能FAL_SERVER及FAL_CLIENT不能自动自主库把MISS归档文件传递到物理备库，在ORACLE10G情况，可能要重建部署物理备库，消耗时间就比较久了，所以ORACLE11G产生了基于SCN的RMAN增量备份，重新同步物理备库，避免重布物理备库，提升工作效能。
数据库版本：
oracle 11.2.0.4
测试流程：
1，物理备库目前应用到653主库归档文件，存在归档GAP
自ORACLE11G开始，如果DATA GUARD物理备库存在ARCHIVE GAP，并且这个GAP缺口过大，可能FAL_SERVER及FAL_CLIENT不能自动自主库把MISS归档文件传递到物理备库，在ORACLE10G情况，可能要重建部署物理备库，消耗时间就比较久了，所以ORACLE11G产生了基于SCN的RMAN增量备份，重新同步物理备库，避免重布物理备库，提升工作效能。
数据库版本：
oracle 11.2.0.4
测试流程：
1，物理备库目前应用到653主库归档文件，存在归档GAP
SQL> select thread#, sequence#, status from v$managed_standby where process='MRP0';
THREAD# SEQUENCE# STATUS
---------- ---------- ------------
1 653 WAIT_FOR_GAP
2,物理备库发现归档GAP 653-656
Error 12541 received logging on to the standby
Check whether the listener is up and running.
FAL[client, MRP0]: Error 12541 connecting to langfang for fetching gap sequence
Mon Feb 02 21:27:35 2015
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 653-656
DBID 2014868718 branch 853777039
FAL[client]: All defined FAL servers have been attempted.
------------------------------------------------------------
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that's sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.
3，物理备库可以接受GAP之后的源自主库的归档文件，但不应用
SQL> select SEQUENCE#,name from v$archived_log where SEQUENCE#>=653
2 ;
SEQUENCE# NAME
---------- --------------------------------------------------
657 /oracle/from_xinao_arch/1_657_853777039.dbf
658 /oracle/from_xinao_arch/1_658_853777039.dbf
659 /oracle/from_xinao_arch/1_659_853777039.dbf
660 /oracle/from_xinao_arch/1_660_853777039.dbf
661 /oracle/from_xinao_arch/1_661_853777039.dbf
662 /oracle/from_xinao_arch/1_662_853777039.dbf
663 /oracle/from_xinao_arch/1_663_853777039.dbf
664 /oracle/from_xinao_arch/1_664_853777039.dbf
665 /oracle/from_xinao_arch/1_665_853777039.dbf
666 /oracle/from_xinao_arch/1_666_853777039.dbf
667 /oracle/from_xinao_arch/1_667_853777039.dbf
SEQUENCE# NAME
---------- --------------------------------------------------
668 /oracle/from_xinao_arch/1_668_853777039.dbf
669 /oracle/from_xinao_arch/1_669_853777039.dbf
670 /oracle/from_xinao_arch/1_670_853777039.dbf
671 /oracle/from_xinao_arch/1_671_853777039.dbf
672 /oracle/from_xinao_arch/1_672_853777039.dbf
673 /oracle/from_xinao_arch/1_673_853777039.dbf
676 /oracle/from_xinao_arch/1_676_853777039.dbf
677 /oracle/from_xinao_arch/1_677_853777039.dbf
678 /oracle/from_xinao_arch/1_678_853777039.dbf
20 rows selected.
4，准备采用基于SCN方式增量RMAN备份重新自主库同步物理备库
5,停止物理备库日志应用
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
6，查询目前物理备库数据库SCN
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
1622402
7，基于上述备库查询的SCN在主库进行一个增量RMAN备份
RMAN> run
2> {
3> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;
4> allocate channel d1 type disk;
5> allocate channel d2 type disk;
6> allocate channel d3 type disk;
7> allocate channel d4 type disk;
8> BACKUP INCREMENTAL FROM SCN 1622402 database format '/home/oracle/chuli_standby_gap/forstandby_%d_%T_%s_%p.bkp';
9> release channel d1;
10> release channel d2;
release channel d3;
11> 12> release channel d4;
13> }
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%U_%F';
RMAN configuration parameters are successfully reset to default value
released channel: ORA_DISK_1
allocated channel: d1
channel d1: SID=1 device type=DISK
allocated channel: d2
channel d2: SID=31 device type=DISK
allocated channel: d3
channel d3: SID=30 device type=DISK
allocated channel: d4
channel d4: SID=34 device type=DISK
Starting backup at 02-FEB-15
channel d1: starting full datafile backup set
channel d1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oracle/langfang/system01.dbf
input datafile file number=00004 name=/oracle/langfang/users01.dbf
channel d1: starting piece 1 at 02-FEB-15
channel d2: starting full datafile backup set
channel d2: specifying datafile(s) in backup set
input datafile file number=00003 name=/oracle/langfang/undotbs01.dbf
input datafile file number=00006 name=/oracle/langfang/testuser1.dbf
channel d2: starting piece 1 at 02-FEB-15
channel d3: starting full datafile backup set
channel d3: specifying datafile(s) in backup set
input datafile file number=00002 name=/oracle/langfang/sysaux01.dbf
input datafile file number=00005 name=/oracle/langfang/tbs_zxy_new.dbf
channel d3: starting piece 1 at 02-FEB-15
channel d4: starting full datafile backup set
channel d4: specifying datafile(s) in backup set
input datafile file number=00007 name=/oracle/langfang/tbs_32k.dbf
channel d4: starting piece 1 at 02-FEB-15
channel d4: finished piece 1 at 02-FEB-15
piece handle=/home/oracle/chuli_standby_gap/forstandby_LANGFANG_20150202_85_1.bkp tag=TAG20150202T214936 comment=NONE
channel d4: backup set complete, elapsed time: 00:00:47
channel d4: starting full datafile backup set
channel d4: specifying datafile(s) in backup set
including current control file in backup set
channel d4: starting piece 1 at 02-FEB-15
channel d4: finished piece 1 at 02-FEB-15
piece handle=/home/oracle/chuli_standby_gap/forstandby_LANGFANG_20150202_86_1.bkp tag=TAG20150202T214936 comment=NONE
channel d4: backup set complete, elapsed time: 00:00:01
channel d4: starting full datafile backup set
channel d4: specifying datafile(s) in backup set
channel d3: finished piece 1 at 02-FEB-15
piece handle=/home/oracle/chuli_standby_gap/forstandby_LANGFANG_20150202_84_1.bkp tag=TAG20150202T214936 comment=NONE
channel d3: backup set complete, elapsed time: 00:01:04
including current control file in backup set
channel d4: starting piece 1 at 02-FEB-15
channel d1: finished piece 1 at 02-FEB-15
piece handle=/home/oracle/chuli_standby_gap/forstandby_LANGFANG_20150202_82_1.bkp tag=TAG20150202T214936 comment=NONE
channel d1: backup set complete, elapsed time: 00:01:39
channel d4: finished piece 1 at 02-FEB-15
piece handle=/home/oracle/chuli_standby_gap/forstandby_LANGFANG_20150202_87_1.bkp tag=TAG20150202T214936 comment=NONE
channel d4: backup set complete, elapsed time: 00:00:35
channel d2: finished piece 1 at 02-FEB-15
piece handle=/home/oracle/chuli_standby_gap/forstandby_LANGFANG_20150202_83_1.bkp tag=TAG20150202T214936 comment=NONE
channel d2: backup set complete, elapsed time: 00:01:49
Finished backup at 02-FEB-15
released channel: d1
released channel: d2
released channel: d3
released channel: d4
RMAN>
8,在主库创建基于物理备库的控制文件
SQL> alter database create standby controlfile as '/home/oracle/chuli_standby_gap/standby.ctl';
Database altered.
Database altered.
9，在主库通过SCP上述RMAN备份至备库
[oracle@langfang chuli_standby_gap]$ pwd
/home/oracle/chuli_standby_gap
[oracle@langfang chuli_standby_gap]$ ls -l
total 883484
-rw-r-----. 1 oracle oinstall 68632576 Feb 2 21:50 forstandby_LANGFANG_20150202_82_1.bkp
-rw-r-----. 1 oracle oinstall 737124352 Feb 2 21:51 forstandby_LANGFANG_20150202_83_1.bkp
-rw-r-----. 1 oracle oinstall 37298176 Feb 2 21:50 forstandby_LANGFANG_20150202_84_1.bkp
-rw-r-----. 1 oracle oinstall 22544384 Feb 2 21:50 forstandby_LANGFANG_20150202_85_1.bkp
-rw-r-----. 1 oracle oinstall 13041664 Feb 2 21:50 forstandby_LANGFANG_20150202_86_1.bkp
-rw-r-----. 1 oracle oinstall 13041664 Feb 2 21:50 forstandby_LANGFANG_20150202_87_1.bkp
-rw-r-----. 1 oracle oinstall 12992512 Feb 2 22:03 standby.ctl
[oracle@langfang chuli_standby_gap]$ scp * oracle@10.0.0.4:/home/oracle/from_scn
oracle@10.0.0.4's password:
forstandby_LANGFANG_20150202_82_1.bkp 100% 65MB 32.7MB/s 00:02
forstandby_LANGFANG_20150202_83_1.bkp 100% 703MB 29.3MB/s 00:24
forstandby_LANGFANG_20150202_84_1.bkp 100% 36MB 35.6MB/s 00:01
forstandby_LANGFANG_20150202_85_1.bkp 100% 22MB 21.5MB/s 00:00
forstandby_LANGFANG_20150202_86_1.bkp 100% 12MB 12.4MB/s 00:01
forstandby_LANGFANG_20150202_87_1.bkp 100% 12MB 12.4MB/s 00:00
standby.ctl 100% 12MB 12.4MB/s 00:00
[oracle@langfang chuli_standby_gap]$
10,关闭物理备库
[oracle@xinao from_scn]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 2 22:10:12 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
11，启动物理备库到nomount，恢复物理备库控制文件
[oracle@xinao from_scn]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Feb 2 22:10:36 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup nomount
Oracle instance started
Total System Global Area 588746752 bytes
Fixed Size 2255472 bytes
Variable Size 201328016 bytes
Database Buffers 381681664 bytes
Redo Buffers 3481600 bytes
RMAN> restore standby controlfile from '/home/oracle/from_scn/standby.ctl';
Starting restore at 02-FEB-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=/oracle/xinao/control01.ctl
output file name=/oracle/xinao/control02.ctl
Finished restore at 02-FEB-15
12，mount物理备库
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
13，确认物理备库的数据文件路径正确
SQL> col name for a50
SQL> set linesize 300
SQL> select file#,name from v$datafile;
FILE# NAME
---------- --------------------------------------------------
1 /oracle/xinao/system01.dbf
2 /oracle/xinao/sysaux01.dbf
3 /oracle/xinao/undotbs01.dbf
4 /oracle/xinao/users01.dbf
5 /oracle/xinao/tbs_zxy_new.dbf
6 /oracle/xinao/testuser1.dbf
7 /oracle/xinao/tbs_32k.dbf
7 rows selected.
14，物理备库注册自主库传递过来的RMAN增量备份集
RMAN> catalog start with '/home/oracle/from_scn' noprompt;
using target database control file instead of recovery catalog
searching for all files that match the pattern /home/oracle/from_scn
List of Files Unknown to the Database
=====================================
File Name: /home/oracle/from_scn/forstandby_LANGFANG_20150202_84_1.bkp
File Name: /home/oracle/from_scn/forstandby_LANGFANG_20150202_87_1.bkp
File Name: /home/oracle/from_scn/standby.ctl
File Name: /home/oracle/from_scn/forstandby_LANGFANG_20150202_82_1.bkp
File Name: /home/oracle/from_scn/forstandby_LANGFANG_20150202_83_1.bkp
File Name: /home/oracle/from_scn/forstandby_LANGFANG_20150202_86_1.bkp
File Name: /home/oracle/from_scn/forstandby_LANGFANG_20150202_85_1.bkp
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /home/oracle/from_scn/forstandby_LANGFANG_20150202_84_1.bkp
File Name: /home/oracle/from_scn/forstandby_LANGFANG_20150202_87_1.bkp
File Name: /home/oracle/from_scn/standby.ctl
File Name: /home/oracle/from_scn/forstandby_LANGFANG_20150202_82_1.bkp
File Name: /home/oracle/from_scn/forstandby_LANGFANG_20150202_83_1.bkp
File Name: /home/oracle/from_scn/forstandby_LANGFANG_20150202_86_1.bkp
File Name: /home/oracle/from_scn/forstandby_LANGFANG_20150202_85_1.bkp
15，恢复物理备库
RMAN> recover database noredo;
Starting recover at 02-FEB-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 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/xinao/system01.dbf
destination for restore of datafile 00004: /oracle/xinao/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/from_scn/forstandby_LANGFANG_20150202_82_1.bkp
channel ORA_DISK_1: piece handle=/home/oracle/from_scn/forstandby_LANGFANG_20150202_82_1.bkp tag=TAG20150202T214936
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
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 00002: /oracle/xinao/sysaux01.dbf
destination for restore of datafile 00005: /oracle/xinao/tbs_zxy_new.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/from_scn/forstandby_LANGFANG_20150202_84_1.bkp
channel ORA_DISK_1: piece handle=/home/oracle/from_scn/forstandby_LANGFANG_20150202_84_1.bkp tag=TAG20150202T214936
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
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 00003: /oracle/xinao/undotbs01.dbf
destination for restore of datafile 00006: /oracle/xinao/testuser1.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/from_scn/forstandby_LANGFANG_20150202_83_1.bkp
channel ORA_DISK_1: piece handle=/home/oracle/from_scn/forstandby_LANGFANG_20150202_83_1.bkp tag=TAG20150202T214936
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
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 00007: /oracle/xinao/tbs_32k.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/from_scn/forstandby_LANGFANG_20150202_85_1.bkp
channel ORA_DISK_1: piece handle=/home/oracle/from_scn/forstandby_LANGFANG_20150202_85_1.bkp tag=TAG20150202T214936
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished recover at 02-FEB-15
16，物理备库启用日志应用
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.
17，确认主备库数据是否同步
主库
SQL> select group#,sequence#,status from v$log where status='CURRENT';
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
2 680 CURRENT
物理备库
SQL> select process,status,sequence# from v$managed_standby where process='MRP0';
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
MRP0 APPLYING_LOG 680
18,手工在主库切换日志确认是否可以同步到备库
SQL> alter system switch logfile;
System altered.
Mon Feb 02 22:18:06 2015
Media Recovery Waiting for thread 1 sequence 681
Mon Feb 02 22:18:06 2015
Archived Log entry 2 added for thread 1 sequence 680 ID 0x78f2cae5 dest 1:
Mon Feb 02 22:18:06 2015
RFS[1]: Selected log 5 for thread 1 sequence 681 dbid 2014868718 branch 853777039
Recovery of Online Redo Log: Thread 1 Group 5 Seq 681 Reading mem 0
Mem# 0: /oracle/xinao/standby_redo05.log
19，关闭物理备库日志应用
SQL> alter database recover managed standby database cancel;
Database altered.
20，打开物理备库
SQL> alter database open;
Database altered.
21,打开物理备库日志应用
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.
项目经验:
个人简介
8年oracle从业经验，具备丰富的oracle技能，目前在国内北京某专业oracle服务公司从事高级技术顾问。
服务过的客户：
中国电信
中国移动
中国联通
中国电通
国家电网
四川达州商业银行
湖南老百姓大药房
山西省公安厅
中国邮政
北京302医院
河北廊坊新奥集团公司
项目经验:
中国电信3G项目AAA系统数据库部署及优化
中国联通CRM数据库性能优化
中国移动10086电商平台数据库部署及优化
湖南老百姓大药房ERR数据库sql优化项目
四川达州商业银行TCBS核心业务系统数据库模型设计和RAC部署及优化
四川达州商业银行TCBS核心业务系统后端批处理存储过程功能模块编写及优化
北京高铁信号监控系统RAC数据库部署及优化
河南宇通客车数据库性能优化
中国电信电商平台核心采购模块表模型设计及优化
中国邮政储蓄系统数据库性能优化及sql优化
北京302医院数据库迁移实施
河北廊坊新奥data guard部署及优化
山西公安厅身份证审计数据库系统故障评估
国家电网上海灾备项目4 node rac+adg
联系方式：
手机：18201115468
qq : 305076427
qq微博: wisdomone1
新浪微博：wisdomone9
qq群：275813900
itpub博客名称：wisdomone1 http://blog.itpub.net/9240380/
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-1424363/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-1424363/
447
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
