基於data guard 增量scn的rman備份重新同步rolling forward物理備庫
前提：
自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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- rman 增量備份恢復
- 使用Data Guard Broker進行Data Guard物理備用庫配置(Oracle 19c)Oracle
- 【ASK_ORACLE】Oracle Data Guard（二）物理備庫的概念和優勢Oracle
- oracle10g RMAN增量備份策略Oracle
- oracle資料庫備份之exp增量備份Oracle資料庫
- MySQL備份與恢復——基於Xtrabackup物理備份恢復MySql
- 【DG】Data Guard主備庫Failove切換AI
- 【DG】Data Guard主備庫Switchover切換
- 使用RMAN備份資料庫資料庫
- postgresql物理備份工具pg_rman的使用詳解SQL
- 【RMAN】RMAN的備份保留策略
- dg丟失歸檔，使用rman增量備份恢復
- RMAN的備份原理
- EXP增量備份
- Xtrabackup增量備份
- 【RMAN】RMAN備份至ASMASM
- RMAN備份概述
- 利用RMAN備份重建資料庫資料庫
- 【RMAN】在備庫執行rman備份時報錯RMAN-06820 ORA-17629
- [20221028]rman使用tape與增量備份測試2.txt
- MySQL備份與恢復——基於OUTFILE /LOAD DATA 邏輯備份恢復MySql
- oracle基於SCN增量恢復Oracle
- RMAN備份恢復典型案例——RMAN備份&系統變慢
- ORACLE DG從庫 Rman備份恢復Oracle
- RMAN備份進度
- rman 備份指令碼指令碼
- [20221020]奇怪的增量備份.txt
- 基於percona xtrabackup 2.4.14的增量備份恢復還原mysql 5.6MySql
- 使用RMAN增量備份處理Dataguard因歸檔丟失造成的gap
- Mysql備份與恢復(1)---物理備份MySql
- RMAN 備份相關的概念
- rman如何在備庫執行一致性備份
- oracle資料庫使用rman備份指令碼Oracle資料庫指令碼
- MySQL 定時增量備份MySql
- Oracle 12c 使用RMAN搭建物理備庫(RAC to RAC)Oracle
- Oracle RMAN備份實戰Oracle
- Oracle OCP(60)：RMAN 備份Oracle
- RMAN備份恢復技巧
- 【rman備份策略】實驗