20160223Oracle 11G Data Guard Failover2
[20160223]Oracle 11G Data Guard Failover-flush redo2.txt
--連結: http://blog.csdn.net/tianlesoftware/article/details/6256542
--昨天測試了使用：
SQL> ALTER SYSTEM FLUSH REDO TO target_db_name;
--測試連結 http://blog.itpub.net/267265/viewspace-1992583/
--在主庫上執行，而且target_db_name名必須加引號：
SYS@test> ALTER SYSTEM FLUSH REDO TO 'testdg';
--昨天的測試不是太嚴謹，因為我們的網路很好，而且主庫沒有任何負載。今天重複測試看看。
1.環境：
SYS@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
DGMGRL> show configuration
Configuration - study
Protection Mode: MaxPerformance
Databases:
test - Primary database
testdg - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
--首先關閉備庫。在主庫做一些dml操作然後執行shutdown abort。然後開啟備庫到mount狀態，看看日誌是否傳送到備庫(mount狀態)。
2.建立測試：
--關閉備庫。
SYS@testdg> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
--在主庫建立表TXX。
SCOTT@test> create table txx (id number,scn number,ins_date date);
Table created.
SCOTT@test> set numw 12
SCOTT@test> @logfile;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME GROUP# STATUS TYPE MEMBER IS_
------ ------- --------- ----------- --------- ------- --- ---------- ------------- ------------------- ------------ ------------------- ------ ---------- ---------- -------------------------------------------------- ---
1 1 4821 52428800 512 1 YES INACTIVE 13226433055 2016-02-23 08:08:06 13226442338 2016-02-23 09:00:53 1 ONLINE /u01/app/oracle11g/oradata/test/redo01.log NO
2 1 4822 52428800 512 1 NO CURRENT 13226442338 2016-02-23 09:00:53 2.814750E+14 2 ONLINE /u01/app/oracle11g/oradata/test/redo02.log NO
3 1 4820 52428800 512 1 YES INACTIVE 13226414075 2016-02-23 05:06:31 13226433055 2016-02-23 08:08:06 3 ONLINE /u01/app/oracle11g/oradata/test/redo03.log NO
insert into txx select 1,current_scn,sysdate from v$database ;
commit ;
alter system archive log current ;
--中間停頓一會。
insert into txx select 2,current_scn,sysdate from v$database ;
commit ;
alter system archive log current ;
insert into txx select 3,current_scn,sysdate from v$database ;
commit ;
alter system archive log current ;
insert into txx select 4,current_scn,sysdate from v$database ;
commit ;
alter system archive log current ;
insert into txx select 5,current_scn,sysdate from v$database ;
commit ;
alter system archive log current ;
SCOTT@test> select rowid,txx.* from txx;
ROWID ID SCN INS_DATE
------------------ ------------ ------------ -------------------
AABQkIAAEAAAdq0AAA 1 13226444257 2016-02-23 09:21:41
AABQkIAAEAAAdq0AAB 2 13226444430 2016-02-23 09:23:44
AABQkIAAEAAAdq0AAC 3 13226444465 2016-02-23 09:24:02
AABQkIAAEAAAdq0AAD 4 13226444490 2016-02-23 09:24:20
AABQkIAAEAAAdq0AAE 5 13226444516 2016-02-23 09:24:36
SCOTT@test> @logfile;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME GROUP# STATUS TYPE MEMBER IS_
------ ------- --------- ----------- --------- ------- --- ---------- ------------- ------------------- ------------ ------------------- ------ ---------- ---------- -------------------------------------------------- ---
1 1 4827 52428800 512 1 NO CURRENT 13226444522 2016-02-23 09:24:37 2.814750E+14 1 ONLINE /u01/app/oracle11g/oradata/test/redo01.log NO
2 1 4825 52428800 512 1 YES ACTIVE 13226444471 2016-02-23 09:24:04 13226444496 2016-02-23 09:24:22 2 ONLINE /u01/app/oracle11g/oradata/test/redo02.log NO
3 1 4826 52428800 512 1 YES ACTIVE 13226444496 2016-02-23 09:24:22 13226444522 2016-02-23 09:24:37 3 ONLINE /u01/app/oracle11g/oradata/test/redo03.log NO
--可以發現前面的redo已經歸檔。
SCOTT@test> @rowid AABQkIAAEAAAdq0AAA
OBJECT FILE BLOCK ROW DBA TEXT
------------ ------------ ------------ ------------ -------------------- -------------------------------------------
329992 4 121524 0 4,121524 alter system dump datafile 4 block 121524
--模擬假設主庫損壞，無法到open狀態。
SYS@test> shutdown abort ;
ORACLE instance shut down.
3.開始測試：
--備庫與主庫開啟到mount狀態：
SYS@testdg> startup mount
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2228784 bytes
Variable Size 905973200 bytes
Database Buffers 687865856 bytes
Redo Buffers 7344128 bytes
Database mounted.
SYS@test> startup mount
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2228784 bytes
Variable Size 973082064 bytes
Database Buffers 620756992 bytes
Redo Buffers 7344128 bytes
Database mounted.
SYS@testdg> select process,pid,status,group#,thread#,sequence#,block#,blocks,delay_mins from v$managed_standby order by thread#,group# ;
PROCESS PID STATUS GROUP# THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------- ------------ -------------------- ---------- ---------- ---------- ---------- ----------
ARCH 10460 CONNECTED N/A 0 0 0 0 0
ARCH 10462 CONNECTED N/A 0 0 0 0 0
ARCH 10464 CONNECTED N/A 0 0 0 0 0
ARCH 10466 CONNECTED N/A 0 0 0 0 0
MRP0 10478 APPLYING_LOG N/A 1 4822 2090 102400 0
--MRP0 在等待日誌SEQUENCE#＝4822.
--在主庫執行:
SYS@test> ALTER SYSTEM FLUSH REDO TO 'testdg';
System altered.
--補充使用大寫看看，依舊不行。
SYS@test> ALTER SYSTEM FLUSH REDO TO TESTDG;
ALTER SYSTEM FLUSH REDO TO TESTDG
*
ERROR at line 1:
ORA-00922: missing or invalid option
--這個時候檢查備庫的alert*.log內容：
Tue Feb 23 09:32:30 2016
RFS[1]: Assigned to RFS process 10566
RFS[1]: Selected log 5 for thread 1 sequence 4822 dbid 2071943378 branch 798551880
Tue Feb 23 09:32:31 2016
Archived Log entry 1318 added for thread 1 sequence 4822 ID 0x806ffa4c dest 1:
RFS[1]: Opened log for thread 1 sequence 4823 dbid 2071943378 branch 798551880
Archived Log entry 1319 added for thread 1 sequence 4823 rlc 798551880 ID 0x806ffa4c dest 2:
Tue Feb 23 09:32:31 2016
Media Recovery Log /u01/app/oracle11g/archivelog/1_4823_798551880.dbf
RFS[1]: Opened log for thread 1 sequence 4824 dbid 2071943378 branch 798551880
Tue Feb 23 09:32:31 2016
RFS[2]: Assigned to RFS process 10568
RFS[2]: Opened log for thread 1 sequence 4825 dbid 2071943378 branch 798551880
Archived Log entry 1320 added for thread 1 sequence 4824 rlc 798551880 ID 0x806ffa4c dest 2:
Tue Feb 23 09:32:31 2016
RFS[3]: Assigned to RFS process 10570
RFS[3]: Opened log for thread 1 sequence 4826 dbid 2071943378 branch 798551880
Archived Log entry 1321 added for thread 1 sequence 4826 rlc 798551880 ID 0x806ffa4c dest 2:
Archived Log entry 1322 added for thread 1 sequence 4825 rlc 798551880 ID 0x806ffa4c dest 2:
Media Recovery Log /u01/app/oracle11g/archivelog/1_4824_798551880.dbf
Media Recovery Log /u01/app/oracle11g/archivelog/1_4825_798551880.dbf
Media Recovery Log /u01/app/oracle11g/archivelog/1_4826_798551880.dbf
Media Recovery Waiting for thread 1 sequence 4827
Tue Feb 23 09:32:33 2016
RFS[4]: Assigned to RFS process 10572
RFS[4]: Selected log 4 for thread 1 sequence 4827 dbid 2071943378 branch 798551880
Tue Feb 23 09:32:33 2016
Archived Log entry 1323 added for thread 1 sequence 4827 ID 0x806ffa4c dest 1:
Tue Feb 23 09:32:34 2016
Standby switchover readiness check: Checking whether recoveryapplied all redo..
Database not available for switchover
End-Of-REDO archived log file has not been recovered
Incomplete recovery SCN:3:341542608 archive SCN:3:341562802
Physical Standby did not apply all the redo from the primary.
Media Recovery Log /u01/app/oracle11g/archivelog/1_4827_798551880.dbf
Identified End-Of-Redo (move redo) for thread 1 sequence 4827 at SCN 0x3.145bd5b2
Resetting standby activation ID 2154822220 (0x806ffa4c)
Media Recovery Waiting for thread 1 sequence 4828
Tue Feb 23 09:32:35 2016
Standby switchover readiness check: Checking whether recoveryapplied all redo..
Physical Standby applied all the redo from the primary.
--可以發現已經歸檔的日誌以及線上日誌都傳輸過來了。
--在備庫查詢:
SYS@testdg> @dgs
PROCESS PID STATUS GROUP# THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------- ------------ -------------------- ---------- ---------- ---------- ---------- ----------
ARCH 10460 CONNECTED N/A 0 0 0 0 0
ARCH 10464 CONNECTED N/A 0 0 0 0 0
RFS 10570 IDLE N/A 0 0 0 0 0
RFS 10568 IDLE N/A 0 0 0 0 0
RFS 10566 IDLE N/A 0 0 0 0 0
ARCH 10462 CLOSING 4 1 4827 1 168 0
ARCH 10466 CLOSING 5 1 4822 2048 368 0
MRP0 10478 WAIT_FOR_LOG N/A 1 4828 0 0 0
8 rows selected.
--可以發現主庫的日誌已經全部傳輸過來。
4.驗證：
--在備庫透過bbed檢查表TXX的內容，驗證日誌應用情況：
BBED> set dba 4,121524
DBA 0x0101dab4 (16898740 4,121524)
BBED> x /*rnnt rowdata --*/n--表示顯示數字 t--表示日期型別。
rowdata[0] @8078
----------
flag@8078: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8079: 0x01
cols@8080: 3
col 0[2] @8081: 5
col 1[7] @8084: 13226444516
col 2[7] @8092: 2016-02-23 09:24:36
rowdata[22] @8100
-----------
flag@8100: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8101: 0x00
cols@8102: 3
col 0[2] @8103: 4
col 1[7] @8106: 13226444490
col 2[7] @8114: 2016-02-23 09:24:20
rowdata[44] @8122
-----------
flag@8122: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8123: 0x00
cols@8124: 3
col 0[2] @8125: 3
col 1[7] @8128: 13226444465
col 2[7] @8136: 2016-02-23 09:24:02
rowdata[66] @8144
-----------
flag@8144: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8145: 0x00
cols@8146: 3
col 0[2] @8147: 2
col 1[7] @8150: 13226444430
col 2[7] @8158: 2016-02-23 09:23:44
rowdata[88] @8166
-----------
flag@8166: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8167: 0x00
cols@8168: 3
col 0[2] @8169: 1
col 1[7] @8172: 13226444257
col 2[7] @8180: 2016-02-23 09:21:41
tailchk @8188
-------
BBED-00210: file not found
--可以發現主庫的dml操作沒有丟失。如果主庫損壞在能mount的狀態下，執行ALTER SYSTEM FLUSH REDO TO 'testdg';
--這樣減少操作丟失，保證業務完整性。
--自己有一個疑問，如果主庫的日誌損壞了，這樣損壞的日誌如果應用過來是否很危險，如何檢查redo的完整性呢？
--看來在應用日誌前開啟flashback 也許能減少一定的風險。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-1992840/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 11g Data GuardOracle
- Oracle 11g Data Guard Enabling Active Data GuardOracle
- 11g data guard 新特性
- oracle 11g data guard維護Oracle
- 總結11g 物理data guard
- ORACLE 11G Data Guard 角色轉換Oracle
- 【DataGuard】11g 新特性：Active Data Guard
- 實戰11g active data guard on rac
- Oracle 11g RAC Data Guard 物理standby 建立Oracle
- 搭建Oracle Data Guard 11g（物理備用）Oracle
- Oracle 11g Data Guard 使用duplicate from active databaseOracleDatabase
- 搭建11g data guard(duplicate from active database方式)Database
- DATA GUARD部署模式——DATA GUARD概念和管理模式
- Oracle 11g Data Guard (physical standby - active dataguard) [final]Oracle
- 介紹ORACLE DATA GUARD——DATA GUARD概念和管理Oracle
- Data guard搭建
- oracle data guard!!Oracle
- Data Guard Broker系列之二：Data Guard Broker配置實戰
- DATA GUARD 簡介
- Data Guard 建立（ASM)ASM
- Oracle Data Guard配置Oracle
- Oracle 11g Data Guard 物理備庫快速配置指南（上)Oracle
- Oracle 11g Data Guard 使用 duplicate from active database 建立 standby databaseOracleDatabase
- 非OMF管理下ORACLE 11G R2 Data Guard配置Oracle
- oracle 11g data guard 中RFS、MRP程式的說明Oracle
- Oracle 11g Data Guard 使用duplicate from active database 建立 standby databaseOracleDatabase
- [Data Guard]Oracle10g Data Guard學習筆記（一）Oracle筆記
- [Data Guard]Oracle10g Data Guard學習筆記（二）Oracle筆記
- [Data Guard]Oracle10g Data Guard學習筆記（三）Oracle筆記
- Windows下ORACLE 11G DATA GUARD搭建（用於實時備份）WindowsOracle
- [20160222]Oracle 11G Data Guard FailoverOracleAI
- Oracle 11g Data guard 物理備庫故障恢復重建例項Oracle
- Oracle 11g Data guard 物理備庫應急切換(failover)流程OracleAI
- Oracle 11g Data guard 物理主備庫正常切換(switchover)流程Oracle
- Oracle Data Guard Broker元件Oracle元件
- Oracle Data Guard簡介Oracle
- 單機搭建Data Guard
- Active Data Guard初探（一）