Oracle DataGuard環境failover後通過舊備份建立物理Standby

ORACLE DATAGUARD環境中角色切換有兩種：switchover和failover，分別可以理解為計劃內切換和計劃外切換。執行switchover只是不同節點身份的變化，對於資料和dataguard環境並無影響，而failover一旦執行，則dataguard環境即被破壞，就是說failover切換後，該組dataguard只就僅餘執行failover那一臺primary了。

基於此，一般執行failover後，首要工作是為其建立Standby，那麼如果能夠在不影響現有業務/服務的前提下快速建立Standby呢，要說我，it depends~~~說完像沒說一樣，不過確實如何，因為dataguard本身就夠靈活的，環境不同，部署方案確實也是選擇很多。列舉一個三思處理過的案例供參考：Dataguard執行failover後藉助舊的備份集建立物理Standby。

背景：某個Oracle 11gR2 Dataguard環境中Primary意外當機，考慮到業務比較重要，因此沒有等待，直接執行了failover，將角色切換到Standby端，切換完之後發現新的Primary端網路卡也有異常，時有丟包，雖不嚴重畢竟也是個故障點，那麼就考慮再建立一個ORACLE資料庫，將業務切換到新的ORACLE中，伺服器是現成的，但資料量較大，直接複製的方式需要停機並且操作時間很長，因此首選仍然是通過Dataguard的角色切換。

要建立Standby，必須要先有一份Primary的完整映像，一般可以通過在Primary端建立備份集，或者是使用RMAN DUPLICATE FROM ACTIVE DATABASE特性兩種方式來建立，不過這兩種方案都會導致Primary端負載升高，考慮到當前Primary正承擔重要業務，負載已然不低，因此上述兩方式均被否決。

檢查伺服器時看到failover之前建立過全備，並且完整備份及之後產生的所有日誌也均在，可否使用這份備份集進行恢復呢？如果是10g之前版本，那麼可以肯定此路不通，不過考慮到10g版本中引入了跨RESETLOGS恢復的特性，原理是將RESETLOGS的操作也記錄到REDOLOG中(之前是重置REDO的方式，導致OPEN RESETLOGS操作前後的REDO不再連續)，物理Standby能否正確識別failover前後生成的日誌並應用呢，考慮到此次切換屬於典型的時間緊任務急，因此上述方案值得嘗試。

建立Standby的準備工作略(含軟體安裝，複製standby控制檔案，生成spfile，建立金鑰檔案，配置監聽、網路服務名等)；

接下來複制舊的RMAN備份集和之後產生的歸檔檔案至Standby端，進入RMAN執行恢復：

RMAN> startup mount;

RMAN> restore database;

恢復雖歷時不短，但操作順利完成，而後進入sqlplus命令列模式下，

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select * from v$managed_standby;

PROCESS PID STATUS CLIENT_P CLIENT_PID CLIENT_DBID

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

GROUP# RESETLOG_ID THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS KNOWN_AGENTS ACTIVE_AGENTS

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

ARCH 15186 CONNECTED ARCH 15186 3423599117

N/A 0 0 0 0 0 0 0 0

ARCH 15188 CONNECTED ARCH 15188 3423599117

N/A 0 0 0 0 0 0 0 0

ARCH 15190 CONNECTED ARCH 15190 3423599117

N/A 0 0 0 0 0 0 0 0

ARCH 15192 CONNECTED ARCH 15192 3423599117

N/A 0 0 0 0 0 0 0 0

MRP0 16800 WAIT_FOR_LOG N/A N/A N/A

N/A 759413453 1 1749 0 0 0 9 9

上述資訊可以看到正在等待第1749號日誌。

RMAN中檢視歸檔，看看是否完整：

RMAN> list backup of archivelog all;

..............

................

BS Key Size Device Type Elapsed Time Completion Time

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

502 1.65G DISK 00:07:13 07-DEC-11

BP Key: 502 Status: AVAILABLE Compressed: YES Tag: TAG20111207T021510

Piece Name: /data/backup/rman/fomtiuhf_1_1-20111207.full

List of Archived Logs in backup set 502

Thrd Seq Low SCN Low Time Next SCN Next Time

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

1 1737 1897848796 01-DEC-11 1898158044 01-DEC-11

1 1738 1898158044 01-DEC-11 1898277748 01-DEC-11

1 1739 1898277748 01-DEC-11 1898524562 02-DEC-11

1 1740 1898524562 02-DEC-11 1898736322 02-DEC-11

1 1741 1898736322 02-DEC-11 1899050433 02-DEC-11

1 1742 1899050433 02-DEC-11 1899363421 02-DEC-11

1 1743 1899363421 02-DEC-11 1899691461 02-DEC-11

1 1744 1899691461 02-DEC-11 1900008107 02-DEC-11

1 1745 1900008107 02-DEC-11 1900311751 02-DEC-11

1 1746 1900311751 02-DEC-11 1900470992 02-DEC-11

1 1747 1900470992 02-DEC-11 1900730459 03-DEC-11

1 1748 1900730459 03-DEC-11 1900978002 03-DEC-11

1 1749 1900978002 03-DEC-11 1901054322 03-DEC-11

1 1750 1901054322 03-DEC-11 1901098575 03-DEC-11

1 1751 1901098575 03-DEC-11 1901390021 03-DEC-11

1 1752 1901390021 03-DEC-11 1901717349 03-DEC-11

1 1753 1901717349 03-DEC-11 1902040375 03-DEC-11

1 1754 1902040375 03-DEC-11 1902343084 03-DEC-11

..................

經驗證是有的，那麼先恢復出來吧，rman命令列中執行restore archivelog命令如下：

RMAN> restore archivelog from sequence 1749;

Starting restore at 07-DEC-11

using channel ORA_DISK_1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 12/07/2011 21:13:46

RMAN-20242: specification does not match any archived log in the repository

............................

...........................

居然報錯說未能找到，這就奇怪了，再嘗試檢視更細粒度的歸檔呢：

RMAN> list backup of archivelog sequence 1749;

specification does not match any backup in the repository

居然也沒有找到。

分析應該是由於，FAILOVER(相當於OPEN RESETLOGS)後，resetlog_id發生了變化，執行的list或restore都是恢復當前resetlog_id的資訊，沒有找到匹配的記錄。

再次執行list archivelog檢視：

BS Key Size Device Type Elapsed Time Completion Time

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

504 1.34G DISK 00:04:28 07-DEC-11

BP Key: 504 Status: AVAILABLE Compressed: YES Tag: TAG20111207T021510

Piece Name: /data/backup/rman/fqmtiuu6_1_1-20111207.full

List of Archived Logs in backup set 504

Thrd Seq Low SCN Low Time Next SCN Next Time

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

1 1772 1906185973 05-DEC-11 1906407667 05-DEC-11

1 1773 1906407667 05-DEC-11 1906414068 05-DEC-11

1 1774 1906414068 05-DEC-11 1906415997 05-DEC-11

1 1775 1906415997 05-DEC-11 1906467127 05-DEC-11

1 1776 1906467127 05-DEC-11 1906707111 06-DEC-11

1 1777 1906707111 06-DEC-11 1906946742 06-DEC-11

1 1778 1906946742 06-DEC-11 1907268990 06-DEC-11

1 1779 1907268990 06-DEC-11 1907582378 06-DEC-11

1 1780 1907582378 06-DEC-11 1907906222 06-DEC-11

1 1781 1907906222 06-DEC-11 1908211804 06-DEC-11

1 1782 1908211804 06-DEC-11 1908532669 06-DEC-11

1 1783 1908532669 06-DEC-11 1908846355 06-DEC-11

1 1784 1908846355 06-DEC-11 1908994400 06-DEC-11

1 1785 1908994400 06-DEC-11 1909129826 06-DEC-11

1 1786 1909129826 06-DEC-11 1909129833 06-DEC-11

1 1787 1909129833 06-DEC-11 1909248075 06-DEC-11 (TERMINAL)

BS Key Size Device Type Elapsed Time Completion Time

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

503 158.83M DISK 00:00:30 07-DEC-11

BP Key: 503 Status: AVAILABLE Compressed: YES Tag: TAG20111207T021510

Piece Name: /data/backup/rman/frmtiuu6_1_1-20111207.full

List of Archived Logs in backup set 503

Thrd Seq Low SCN Low Time Next SCN Next Time

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

1 1 1909248074 06-DEC-11 1909248078 06-DEC-11

1 2 1909248078 06-DEC-11 1909541467 07-DEC-11

1 3 1909541467 07-DEC-11 1909684157 07-DEC-11

可以看到，1787號日誌之後標記了terminal，再之後的日誌檔案序號自動重置，注意1787號日誌檔案和1號日誌檔案的LOW SCN和NEXT SCN，可以看到SCN是連續的。

這最起碼確定了日誌中的操作是連續的，接下來就是要想辦法將之前的歸檔恢復出來。

先是嘗試從指定的備份集中恢復，執行命令如下：

RMAN> restore archivelog all from '/data/backup/rman/fomtiuhf_1_1-20111207.full';

Starting restore at 07-DEC-11

using channel ORA_DISK_1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 12/07/2011 21:19:22

RMAN-06509: only SPFILE or control file can be restored from AUTOBACKUP

看起來這種命令不支援恢復歸檔，分析備份片段的資訊，發現tag是相同的，繼續嘗試通過tag恢復，執行命令如下：

RMAN> restore archivelog all from tag "TAG20111207T021510";

Starting restore at 07-DEC-11

using channel ORA_DISK_1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 12/07/2011 21:26:54

RMAN-06026: some targets not found - aborting restore

...................

RMAN-06025: no backup of archived log for thread 1 with sequence 1708 and starting SCN of 1893487782 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 1707 and starting SCN of 1893344684 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 1706 and starting SCN of 1893135723 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 1705 and starting SCN of 1892813124 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 1704 and starting SCN of 1892478885 found to restore

............................

丟擲錯誤，原因是備份資訊不全。這些檔案的備份集確實沒有，雖然操作失敗了，但是卻帶來了新的希望，是否是由於這些備份不存在所以才失敗，雖然已經找不到這些檔案的備份集了，但是可以換一個角度思考，如果讓RMAN認為這些檔案不需要恢復，是否就能夠成功執行了呢？

移動備份集到其它路徑下，而後執行RMAN命令檢查備份集：

RMAN> crosscheck backup of archivelog all;

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1153 device type=DISK

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/data/backup/rman/fpmtiuhg_1_1-20111207.full RECID=500 STAMP=769227312

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/data/backup/rman/fnmtiuhf_1_1-20111207.full RECID=501 STAMP=769227311

..........................

刪除這些備份資訊：
RMAN> delete noprompt expired backup;

然後再將備份集恢復至原始路徑，執行catalog命令，將需要的歸檔檔案備份重新註冊到控制檔案中：

RMAN> catalog backuppiece '/data/backup/rman/fpmtiuhg_1_1-20111207.full';

cataloged backup piece

backup piece handle=/data/backup/rman/fpmtiuhg_1_1-20111207.full RECID=500 STAMP=769277444

RMAN> catalog backuppiece '/data/backup/rman/fomtiuhf_1_1-20111207.full';

cataloged backup piece

backup piece handle=/data/backup/rman/fpmtiuhf_1_1-20111207.full RECID=501 STAMP=769277444

...........................

............................

注意僅註冊那些包含恢復用到的歸檔檔案備份集即可，執行完成後再次檢視備份的歸檔檔案：

RMAN> list backup of archivelog all;

確認無誤後執行命令將其恢復出來：

RMAN> restore archivelog all;

......................

歸檔檔案恢復成功後切換至sqlplus命令列模式下，檢視日誌應用的情況：

SQL> select * from v$managed_standby;

PROCESS PID STATUS CLIENT_P CLIENT_PID CLIENT_DBID

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

GROUP# RESETLOG_ID THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS KNOWN_AGENTS ACTIVE_AGENTS

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

ARCH 15186 CONNECTED ARCH 15186 3423599117

N/A 0 0 0 0 0 0 0 0

ARCH 15188 CONNECTED ARCH 15188 3423599117

N/A 0 0 0 0 0 0 0 0

ARCH 15190 CONNECTED ARCH 15190 3423599117

N/A 0 0 0 0 0 0 0 0

ARCH 15192 CONNECTED ARCH 15192 3423599117

N/A 0 0 0 0 0 0 0 0

MRP0 16800 APPLYING_LOG N/A N/A N/A

N/A 769214827 1 1753 334080 0 0 9 9

從上述資訊可以看出，已經開始應用了！！好訊息啊，接下來就看是否能夠成功邁過resetlogs那個操作了。

檢視Alert日誌中的資訊：

Media Recovery Waiting for thread 1 sequence 1749 branch(resetlogs_id) 759413453

Wed Dec 07 21:46:35 2011

Fetching gap sequence in thread 1 branch(resetlogs_id) 759413453, gap seq 1749-1778

Wed Dec 07 21:47:16 2011

Fetching gap sequence in thread 1 branch(resetlogs_id) 759413453, gap seq 1749-1777

Wed Dec 07 21:47:47 2011

Fetching gap sequence in thread 1 branch(resetlogs_id) 759413453, gap seq 1749-1777

Wed Dec 07 21:48:18 2011

Fetching gap sequence in thread 1 branch(resetlogs_id) 759413453, gap seq 1749-1777

Wed Dec 07 21:48:34 2011

Media Recovery Log /data/ora11g/oradata/oracle9i/archive/1_1749_759413453.dbf

Wed Dec 07 21:49:19 2011

Media Recovery Log /data/ora11g/oradata/oracle9i/archive/1_1750_759413453.dbf

Wed Dec 07 21:49:45 2011

Media Recovery Log /data/ora11g/oradata/oracle9i/archive/1_1751_759413453.dbf

Wed Dec 07 21:50:50 2011

Sqlplus端持續監測v$managed_standby檢視中的資訊，注意到當應用至1787號日誌時MPR程式就消失了，檢視alert日誌：

Wed Dec 07 22:07:23 2011

Media Recovery Log /data/ora11g/oradata/oracle9i/archive/1_1786_759413453.dbf

Media Recovery Log /data/ora11g/oradata/oracle9i/archive/1_1787_759413453.dbf

Identified End-Of-Redo for thread 1 sequence 1787

Wed Dec 07 22:07:34 2011

Media Recovery End-Of-Redo indicator encountered

Media Recovery Applied until change 1909248073

Signalling error 1152 for datafile 1!

Errors in file /data/ora11g/diag/rdbms/oradb2/oracle9i/trace/oracle9i_pr00_16802.trc:

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01110: data file 1: '/data/ora11g/oradata/oracle9i/system01.dbf'

Slave exiting with ORA-1547 exception

Errors in file /data/ora11g/diag/rdbms/oradb2/oracle9i/trace/oracle9i_pr00_16802.trc:

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01110: data file 1: '/data/ora11g/oradata/oracle9i/system01.dbf'

Wed Dec 07 22:07:35 2011

Recovery Slave PR00 previously exited with exception 1547

Errors in file /data/ora11g/diag/rdbms/oradb2/oracle9i/trace/oracle9i_mrp0_16800.trc:

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01110: data file 1: '/data/ora11g/oradata/oracle9i/system01.dbf'

MRP0: Background Media Recovery process shutdown (oracle9i)

日誌端看出，應用程式由於遇到錯誤，自動停止了，ORA-01547和ORA-01152屬於一般錯誤，常見於執行恢復過程中REDO資料異常。

兩項錯誤的官方解釋如下：

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

Cause: Media recovery with one of the incomplete recovery options ended without error. However, if the ALTER DATABASE OPEN RESETLOGS command were attempted now, it would fail with the specified error. The most likely cause of this error is forgetting to restore one or more datafiles from a sufficiently old backup before executing the incomplete recovery.

Action: Rerun the incomplete media recovery using different datafile backups, a different control file, or different stop criteria.

ORA-01152: file string was not restored from a sufficiently old backup

Cause: An incomplete recovery session was started, but an insufficient number of logs were applied to make the database consistent. This file is still in the future of the last log applied. The most likely cause of this error is forgetting to restore the file from a backup before doing incomplete recovery.

Action: Either apply more logs until the database is consistent or restore the database file from an older backup and repeat recovery.

錯誤資訊描述的對啊，確實是日誌序號不足，因為resetlog後不是遞增，而是重置成1了，沒啥好說的，繼續應用唄，再次嘗試重新啟動歸檔應用，

SQL> alter database recover managed standby database disconnect from session;

Database altered.

同時修改Primary的log_archive_dest引數，啟動傳送歸檔到當前這個standby端：

SQL> alter system set log_archive_dest_state_2='enable';

注意觀察從端的alert日誌：

Wed Dec 07 22:18:32 2011

alter database recover managed standby database disconnect from session

Attempt to start background Managed Standby Recovery process (oracle9i)

Wed Dec 07 22:18:32 2011

MRP0 started with pid=26, OS id=17498

MRP0: Background Managed Standby Recovery process started (oracle9i)

started logmerger process

Wed Dec 07 22:18:37 2011

Managed Standby Recovery not using Real Time Apply

Parallel Media Recovery started with 8 slaves

Waiting for all non-current ORLs to be archived...

All non-current ORLs have been archived.

Media Recovery Log /data/ora11g/oradata/oracle9i/archive/1_1_769214827.dbf

Media Recovery Log /data/ora11g/oradata/oracle9i/archive/1_2_769214827.dbf

Completed: alter database recover managed standby database disconnect from session

Wed Dec 07 22:19:54 2011

Media Recovery Log /data/ora11g/oradata/oracle9i/archive/1_3_769214827.dbf

Wed Dec 07 22:20:28 2011

Media Recovery Waiting for thread 1 sequence 4

日誌成功繼承到新的序列了，太好啦，成功啦！！

後面，隨著primary端的日誌不斷髮送至standby端，standby的日誌接收和應用也在繼續：

Wed Dec 07 22:21:57 2011

RFS[1]: Assigned to RFS process 17537

RFS[1]: Identified database type as 'physical standby': Client is ARCH pid 23229

RFS[1]: Opened log for thread 1 sequence 9 dbid -871368179 branch 769214827

Wed Dec 07 22:21:57 2011

RFS[2]: Assigned to RFS process 17539

RFS[2]: Identified database type as 'physical standby': Client is ARCH pid 23225

RFS[2]: Opened log for thread 1 sequence 8 dbid -871368179 branch 769214827

Wed Dec 07 22:21:57 2011

RFS[3]: Assigned to RFS process 17541

RFS[3]: Identified database type as 'physical standby': Client is ARCH pid 23233

RFS[3]: Opened log for thread 1 sequence 7 dbid -871368179 branch 769214827

Wed Dec 07 22:21:58 2011

Fetching gap sequence in thread 1, gap sequence 4-9

Wed Dec 07 22:22:02 2011

RFS[4]: Assigned to RFS process 17543

RFS[4]: Identified database type as 'physical standby': Client is LGWR ASYNC pid 17618

Primary database is in MAXIMUM PERFORMANCE mode

RFS[4]: Selected log 4 for thread 1 sequence 11 dbid -871368179 branch 769214827

Wed Dec 07 22:22:09 2011

Archived Log entry 73 added for thread 1 sequence 9 rlc 769214827 ID 0xcc9aa297 dest 2:

RFS[1]: Opened log for thread 1 sequence 4 dbid -871368179 branch 769214827

Wed Dec 07 22:22:12 2011

Archived Log entry 74 added for thread 1 sequence 7 rlc 769214827 ID 0xcc9aa297 dest 2:

RFS[3]: Opened log for thread 1 sequence 5 dbid -871368179 branch 769214827

Wed Dec 07 22:22:14 2011

Archived Log entry 75 added for thread 1 sequence 8 rlc 769214827 ID 0xcc9aa297 dest 2:

RFS[2]: Opened log for thread 1 sequence 6 dbid -871368179 branch 769214827

Wed Dec 07 22:22:23 2011

Archived Log entry 76 added for thread 1 sequence 4 rlc 769214827 ID 0xcc9aa297 dest 2:

Wed Dec 07 22:22:23 2011

Media Recovery Log /data/ora11g/oradata/oracle9i/archive/1_4_769214827.dbf

RFS[1]: Opened log for thread 1 sequence 10 dbid -871368179 branch 769214827

Wed Dec 07 22:22:25 2011

至此，Dataguard環境部署成功。然後，找一個合適的時間點，執行計劃內的switchover即可。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7607759/viewspace-712959/，如需轉載，請註明出處，否則將追究法律責任。

Oracle DataGuard環境failover後通過舊備份建立物理Standby

相關文章