利用閃回資料庫（flashback）修復Failover後的DG環境

1.1 BLOG文件結構圖

1.2 前言部分

1.2.1 導讀和注意事項

各位技術愛好者，看完本文後，你可以掌握如下的技能，也可以學到一些其它你所不知道的知識，~O(∩_∩)O~：

① Failover後DG環境的恢復方法（重點）

② DG的基本維護操作

③ GC客戶端軟體的安裝

④ 利用GC快速搭建一套DG環境

⑤ Failover和Switchover的區別

⑥ 其它維護操作

Tips：

① 本文在itpub（http://blog.itpub.net/26736162）、部落格園(http://www.cnblogs.com/lhrbest)和微信公眾號（xiaomaimiaolhr）上有同步更新。

② 文章中用到的所有程式碼、相關軟體、相關資料及本文的pdf版本都請前往小麥苗的雲盤下載，小麥苗的雲盤地址見：http://blog.itpub.net/26736162/viewspace-1624453/。

③ 若網頁文章程式碼格式有錯亂，請下載pdf格式的文件來閱讀。

④ 在本篇BLOG中，程式碼輸出部分一般放在一行一列的表格中。

⑤ 本文適合於初中級人員閱讀，資料庫大師請略過本文。

⑥ 不喜勿噴。

本文若有錯誤或不完善的地方請大家多多指正，您的批評指正是我寫作的最大動力。

1.3 本文簡介

10月23和24日考完了OCM，感覺過關的法則就是“真題+多練”，練習過10來遍，基本就可以考過了。OCM的考試內容除了GC這塊小麥苗沒有接觸過，其它內容基本都算熟。基本命令熟記於心，不熟的命令可以立馬找到官方文件，善用OEM和SQL Developer工具。所以，想快速透過OCM考試的朋友可以私下聯絡小麥苗，小麥苗會把自己的經驗全都教給大家。

好了，廢話不多說了。最近小麥苗的DBA寶典微信群裡，有朋會友問到了Failover操作後，如何恢復到最初的DG環境。這個問題，小麥苗大概知道利用閃回可以實現，只是沒有做過實驗，或者曾經做過實驗，只是沒有記錄文件，反正就是年紀大了，想不起來了。好吧，最近就抽個時間把這個實驗做一遍。有不對的地方，依然請大家指出。

1.4 相關知識點掃盲

① 物理DG的Switchover切換：http://blog.itpub.net/26736162/viewspace-1753111/

② 物理DG的Failover切換：http://blog.itpub.net/26736162/viewspace-1753130/

③ 利用閃回資料庫（flashback）修復Failover後的DG環境：http://blog.itpub.net/26736162/viewspace-2146883/

④ Switchover和Failover的區別：http://blog.itpub.net/26736162/viewspace-2141207/

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

第二章實驗準備

2.1 實驗環境介紹

實驗環境為練習OCM的虛擬機器環境：

專案	Source DB	Target DB
DB 型別	單機	單機
DB VERSION	11.2.0.3.0	11.2.0.3.0
DB 儲存	FS	FS
OS版本及kernel版本	OEL linux 5.4 32	OEL linux 5.4 32
DB_NAME	PROD1	PROD1
ORACLE_SID	PROD1	SBDB1
ORACLE_HOME	/u01/app/oracle/product/11.2.0/dbhome_1	/u01/app/oracle/product/11.2.0/db_1
hosts檔案	10.190.104.111 edsir4p1.us.oracle.com edsir4p1 10.190.104.28 edsir1p8.us.oracle.com edsir1p8

2.2 實驗目標

備庫執行FAILOVER後，透過閃回資料庫技術重新恢復DG環境，而不用重新搭建DG。

2.3 實驗過程

2.4 利用GC快速搭建DG環境

小麥苗手頭的DG環境是在一個主機上，測試多有不便，剛好，最近練習OCM的環境還在，就用練習OCM的環境來做這個實驗吧。若已經有DG環境的朋友可以略過該小節內容。

2.4.1 安裝GC客戶端軟體

1、起動GC伺服器，首先確保EMREP資料庫處於OPEN狀態，監聽也已經啟動，GC伺服器啟動日誌為：/u01/app/gc_inst/em/EMGC_OMS1/sysman/log/emctl.log

cd /u01/app/oracle/Middleware/oms11g/bin

./emctl start oms

			
					[oracle@edsir1p8- ~]$ ps -ef|grep pmon 
				
					oracle    4763     1  0 00:53 ?        00:00:00 ora_pmon_EMREP 
				
					oracle   11802 11633  0 01:39 pts/2    00:00:00 grep pmon 
				
					[oracle@edsir1p8- ~]$ cd /u01/app/ 
				
					gc_inst/      Middleware/   oracle/       oraInventory/  
				
					[oracle@edsir1p8- ~]$ cd /u01/app/Middleware/oms11g/bin 
				
					[oracle@edsir1p8- bin]$ ./emctl start oms 
				
					Oracle Enterprise Manager 11g Release 1 Grid Control   
				
					Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved. 
				
					Starting WebTier... 
				
					WebTier Successfully Started 
				
					Starting Oracle Management Server... 
				
					Oracle Management Server Successfully Started 
				
					AdminServer Could Not Be Started 
				
					Oracle Management Server is Up 
				
					[oracle@edsir1p8- bin]$ more /etc/hosts 
				
					# Do not remove the following line, or various programs 
				
					# that require network functionality will fail. 
				
					127.0.0.1       localhost.localdomain   localhost 
				
					10.190.104.28   edsir1p8.us.oracle.com  edsir1p8 
				
					10.190.104.111 edsir4p1.us.oracle.com edsir4p1 
				
					[oracle@edsir1p8- bin]$ ./emctl status oms -details 
				
					Oracle Enterprise Manager 11g Release 1 Grid Control   
				
					Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved. 
				
					Enter Enterprise Manager Root (SYSMAN) Password :  
				
					Console Server Host : edsir1p8.us.oracle.com 
				
					HTTP Console Port   : 7788 
				
					HTTPS Console Port  : 7799 
				
					HTTP Upload Port    : 4889 
				
					HTTPS Upload Port   : 4900 
				
					OMS is not configured with SLB or virtual hostname 
				
					Agent Upload is locked. 
				
					OMS Console is locked. 
				
					Active CA ID: 1

2、安裝agent

從這裡下載

右鍵儲存到桌面。

a、在需要安裝agent的機器上mkdir /u01/app/agentbase 建立目錄，並將agentDownload.linux檔案cp到/u01/app/agentbase目錄下，並且賦予可執行許可權。

b、在服務端OMS啟動的情況下，在客戶端執行：

./agentDownload.linux -b /u01/app/agentbase -m edsir1p8.us.oracle.com -r 7799 -y

安裝過程中要輸入偶數機上OMS的密碼

c、安裝完成要用root執行：

[root@edsir4p1 ~]# sudo /u01/app/agentbase/agent11g/root.sh

沒有root密碼要使用sudo執行，注意：一定要執行該指令碼，它會設定一些檔案的許可權（該指令碼會把$AGENT_HOME/bin/nm*的幾個檔案的所有者修改為root。）。如果不執行，那麼搭建DG可能會報錯：“ERROR: NMO not setuid-root (Unix-only)”

d、進入/u01/app/agentbase/agent11g/bin

./emctl status agent 檢查同步狀態

./emctl upload agent 上傳同步

./emctl secure agent 重新註冊agent，用於安裝時密碼輸錯

過程如下所示：

			
					[oracle@edsir4p1-PROD2 ~]$ mkdir -p /u01/app/agentbase 
				
					[oracle@edsir4p1-PROD2 ~]$ cd /u01/app/agentbase 
				
					[oracle@edsir4p1-PROD2 agentbase]$ cp /home/oracle/Desktop/agentDownload.linux . 
				
					[oracle@edsir4p1-PROD2 agentbase]$ chmod +x agentDownload.linux  
				
					[oracle@edsir4p1-PROD2 agentbase]$ ll 
				
					total 40 
				
					-rwxr-xr-x 1 oracle oinstall 38525 Nov  6 01:46 agentDownload.linux 
				
					[oracle@edsir4p1-PROD2 agentbase]$ ./agentDownload.linux  
				
					agentDownload.linux invoked on Mon Nov  6 01:46:55 UTC 2017 with Arguments "" 
				
					agentDownload.linux: Invalid Invocation 
				
					Usage: agentDownload.linux -b[cdhimnoprtuvxyNR] 
				
					   b - Base installation location for Agent Oracle home 
				
					   d - Do NOT initiate automatic target discovery 
				
					   h - Usage (this message) 
				
					   i - Inventory pointer location file 
				
					   l - To specify as local host (pass -local to runInstaller) 
				
					   m - Management Service host name for downloading the Management Agent software 
				
					   n - Cluster name 
				
					   o - Old Oracle Home location during Upgrade 
				
					   p - Static port list file 
				
					   r - Port for connecting to the Management Service host 
				
					   t - Do NOT start the Agent 
				
					   u - Upgrade 
				
					   v - Inventory directory location 
				
					   x - Debug output 
				
					   c - CLUSTER_NODES 
				
					   N - Do NOT prompt for Agent Registration Password 
				
					   R - To use virtual hostname(ORACLE_HOSTNAME) for this installation. If this is being used along with more than one cluster nodes through -c option, then -l option also needs to be passed. 
				
					   y - Decline Security Updates. 
				
					[oracle@edsir4p1-PROD2 agentbase]$ ./agentDownload.linux -b /u01/app/agentbase -m edsir1p8.us.oracle.com -r 7799 -y 
				
					agentDownload.linux invoked on Mon Nov  6 01:49:01 UTC 2017 with Arguments "-b /u01/app/agentbase -m edsir1p8.us.oracle.com -r 7799 -y" 
				
					Platform=Linux.i686, OS=linux 
				
					GetPlatform:returned=0, and os is set to: linux, platform=Linux.i686 
				
					Creating /u01/app/agentbase/agentDownload11.1.0.1.0Oui ... 
				
					LogFile for this Download can be found at: "/u01/app/agentbase/agentDownload11.1.0.1.0Oui/agentDownload.linux110617014901.log" 
				
					Running on Selected Platform: Linux.i686 
				
					Installer location: /u01/app/agentbase/agentDownload11.1.0.1.0Oui 
				
					Downloading Agent install response file ... 
				
					Downloading Agent install response file ... 
				
					Executing wget_get_file   
				
					。。。。。。。。。。省略部分。。。。。。 
				
					Finished Downloading agent_download.rsp with Status=0 
				
					Response file check Complete - Success 
				
					Checking the writable permission for baseDir - passed 
				
					Provide the Agent Registration password so that the Management Agent can communicate with Secure Management Service. 
				
					Note: You may proceed with the installation without supplying the password; however, Management Agent can be secured manually after the installation. 
				
					If Oracle Management Service is not secured, agent will not be secured, so continue by pressing Enter Key. 
				
					Enter Agent Registration Password:      <<<<=輸入密碼 
				
					Downloading Oracle Installer ... 
				
					Executing wget_get_file   
				
					using the url  to access OMS 
				
					。。。。。。。。。。省略部分。。。。。。 
				
					Configuration assistant "Agent Configuration Assistant" Succeeded 
				
					AgentPlugIn:agent configuration finished with status = true 
				
					Running Configuration assistant "Agent Add-on Plug-in" 
				
					Configuration assistant "Agent Add-on Plug-in" Succeeded 
				
					Querying Agent status: Agent is running 
				
					Removing the copied stuff..... 
				
					Removed: /u01/app/agentbase/agentDownload11.1.0.1.0Oui/oui_linux.jar 
				
					Removed: /u01/app/agentbase/agentDownload11.1.0.1.0Oui/agent_download.rsp 
				
					Removed:/u01/app/agentbase/agentDownload11.1.0.1.0Oui/Disk1 
				
					Log name of installation can be found at: "/u01/app/agentbase/agentDownload.linux110617014901.log" 
				
					/u01/app/agentbase/agent11g/root.sh needs to be executed by root to complete this installation. 
				
					[oracle@edsir4p1-PROD2 agentbase]$ sudo /u01/app/agentbase/agent11g/root.sh 
				
					[oracle@edsir4p1-PROD2 agentbase]$ ll 
				
					total 80 
				
					drwxr-xr-x 40 oracle oinstall  4096 Nov  6 01:53 agent11g 
				
					drwxr-xr-x  2 oracle oinstall  4096 Nov  6 01:53 agentDownload11.1.0.1.0Oui 
				
					-rwxr-xr-x  1 oracle oinstall 38525 Nov  6 01:46 agentDownload.linux 
				
					-rw-r--r--  1 oracle oinstall    78 Nov  6 01:46 agentDownload.linux110617014655.log 
				
					-rw-r--r--  1 oracle oinstall 24908 Nov  6 01:53 agentDownload.linux110617014901.log 
				
					[oracle@edsir4p1-PROD2 agentbase]$ cd agent11g/bin/ 
				
					[oracle@edsir4p1-PROD2 bin]$ ./emctl status agent 
				
					Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0  
				
					Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved. 
				
					--------------------------------------------------------------- 
				
					Agent Version     : 11.1.0.1.0 
				
					OMS Version       : 11.1.0.1.0 
				
					Protocol Version  : 11.1.0.0.0 
				
					Agent Home        : /u01/app/agentbase/agent11g 
				
					Agent binaries    : /u01/app/agentbase/agent11g 
				
					Agent Process ID  : 26954 
				
					Parent Process ID : 26914 
				
					Agent URL         :  
				
					Repository URL    :  
				
					Started at        : 2017-11-06 01:53:15 
				
					Started by user   : oracle 
				
					Last Reload       : 2017-11-06 01:53:15 
				
					Last successful upload                       : 2017-11-06 01:55:13 
				
					Total Megabytes of XML files uploaded so far :    17.86 
				
					Number of XML files pending upload           :        0 
				
					Size of XML files pending upload(MB)         :     0.00 
				
					Available disk space on upload filesystem    :    83.54% 
				
					Last successful heartbeat to OMS             : 2017-11-06 01:57:20 
				
					--------------------------------------------------------------- 
				
					Agent is Running and Ready 
				
					[oracle@edsir4p1-PROD2 bin]$

2.4.2 使用GC快速搭建物理備庫

從瀏覽器開啟，使用sysman使用者進行登入。

使用sys使用者登入PROD1資料庫。

等待大約10分鐘即可自動完成DG的搭建和配置工作。期間，可以檢視主庫和備庫的告警日誌以及資料資料夾的大小來預估搭建完成時間。

建立完成後：

2.4.3 啟用實時應用

2.5 開啟主備庫閃回

主庫：

			
					SQL> select name, open_mode, database_role, flashback_on from v$database; 
				
					NAME      OPEN_MODE            DATABASE_ROLE    FLASHBACK_ON 
				
					--------- -------------------- ---------------- ------------------ 
				
					PROD1     READ WRITE           PRIMARY          NO 
				
					SQL>  select INSTANCE_NAME,INSTANCE_ROLE from v$instance; 
				
					INSTANCE_NAME    INSTANCE_ROLE 
				
					---------------- ------------------ 
				
					PROD1            PRIMARY_INSTANCE

備庫：

			
					SQL> select name, open_mode, database_role, flashback_on from v$database; 
				
					NAME      OPEN_MODE            DATABASE_ROLE    FLASHBACK_ON 
				
					--------- -------------------- ---------------- ------------------ 
				
					PROD1     READ ONLY WITH APPLY PHYSICAL STANDBY NO 
				
					SQL> select INSTANCE_NAME,INSTANCE_ROLE from v$instance; 
				
					INSTANCE_NAME    INSTANCE_ROLE 
				
					---------------- ------------------ 
				
					SBDB1            PRIMARY_INSTANCE

主庫開啟閃回：

			
					SQL> alter database flashback on; 
				
					Database altered. 
				
					SQL>  select name, open_mode, database_role, flashback_on from v$database; 
				
					NAME      OPEN_MODE            DATABASE_ROLE    FLASHBACK_ON 
				
					--------- -------------------- ---------------- ------------------ 
				
					PROD1     READ WRITE           PRIMARY          YES 
				
					SQL> select oldest_flashback_scn, to_char(oldest_flashback_time,'yyyy-mm-dd HH24:mi:ss') oldest_flashback_time from v$flashback_database_log; 
				
					OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI 
				
					-------------------- ------------------- 
				
					              867005 2017-11-06 02:55:59 
				
					SQL> show parameter flashback 
				
					NAME                                 TYPE        VALUE 
				
					------------------------------------ ----------- ------------------------------ 
				
					db_flashback_retention_target        integer     1440 
				
					====>引數db_flashback_retention_target控制閃回時間範圍，數字單位是分鐘，預設為1天。這個數字決定了閃回的時間範圍，如果設定更長的時間，對應的閃回日誌檔案大小就會比較大一些。

告警日誌：

			
					Mon Nov 06 02:55:55 2017 
				
					alter database flashback on 
				
					Starting background process RVWR 
				
					Mon Nov 06 02:55:56 2017 
				
					RVWR started with pid=37, OS id=1096  
				
					Allocated 3981204 bytes in shared pool for flashback generation buffer 
				
					Flashback Database Enabled at SCN 867006 
				
					Completed: alter database flashback on

此時，備庫並沒有開啟閃回，需要在備庫上手動開啟閃回：

			
					SQL> alter database flashback on; 
				
					alter database flashback on 
				
					* 
				
					ERROR at line 1: 
				
					ORA-01153: an incompatible media recovery is active 
				
					SQL> recover managed standby database cancel; 
				
					Media recovery complete. 
				
					SQL>  alter database flashback on; 
				
					Database altered. 
				
					SQL> select name, open_mode, database_role, flashback_on from v$database; 
				
					NAME      OPEN_MODE            DATABASE_ROLE    FLASHBACK_ON 
				
					--------- -------------------- ---------------- ------------------ 
				
					PROD1     READ ONLY            PHYSICAL STANDBY YES 
				
					SQL> alter database recover managed standby database using current logfile disconnect from session; 
				
					Database altered.

第三章實驗1

實驗1、PROD1意外當機，SBDB1執行Failover操作變為主庫；然後將PROD1利用閃回資料庫功能閃回到SBDB1變為主庫的SCN時間點，然後將PROD1轉換為備庫，最後利用switchover轉換為最初的環境。<===PROD1需要開啟閃回

3.1 Failover操作

切換之前確保監聽使用靜態監聽、fal_client、fal_server、log_archive_dest_1和log_archive_dest_2引數已正確配置。

主庫操作：

			
					SYS@PROD1> select dbid,name,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database; 
				
					      DBID NAME       CURRENT_SCN PROTECTION_MODE      PROTECTION_LEVEL     DATABASE_ROLE    FOR OPEN_MODE            SWITCHOVER_STATUS 
				
					---------- ---------- ----------- -------------------- -------------------- ---------------- --- -------------------- -------------------- 
				
					2177200393 PROD1           868787 MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  PRIMARY          YES READ WRITE           TO STANDBY 
				
					SYS@PROD1> create table test_bylhr as select * from dba_objects; 
				
					Table created. 
				
					SYS@PROD1> insert into test_bylhr select * from test_bylhr; 
				
					72459 rows created. 
				
					SYS@PROD1> commit; 
				
					Commit complete. 
				
					SYS@PROD1> select count(1) from test_bylhr; 
				
					  COUNT(1) 
				
					---------- 
				
					    144918 
				
					SYS@PROD1> shutdown abort 
				
					ORACLE instance shut down.

備庫操作：

			
					SYS@SBDB1> select count(1) from test_bylhr; 
				
					  COUNT(1) 
				
					---------- 
				
					144918 
				
					SYS@SBDB1> alter database recover managed standby database cancel; 
				
					Database altered. 
				
					SYS@SBDB1> alter database recover managed standby database finish; 
				
					Database altered. 
				
					SYS@SBDB1> set line 9999 
				
					SYS@SBDB1> select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name from v$database; 
				
					NAME      LOG_MODE     OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS    DB_UNIQUE_NAME 
				
					--------- ------------ -------------------- ---------------- -------------------- ------------------------------ 
				
					PROD1     ARCHIVELOG   READ ONLY            PHYSICAL STANDBY SESSIONS ACTIVE      SBDB1 
				
					SYS@SBDB1>  alter database commit to switchover to primary with session shutdown; 
				
					Database altered. 
				
					SYS@SBDB1> select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name from v$database; 
				
					NAME      LOG_MODE     OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS    DB_UNIQUE_NAME 
				
					--------- ------------ -------------------- ---------------- -------------------- ------------------------------ 
				
					PROD1     ARCHIVELOG   MOUNTED              PRIMARY          NOT ALLOWED          SBDB1 
				
					SYS@SBDB1> alter database open; 
				
					Database altered.

3.2 Primary重新加入

Failover後的Primary資料庫，實際上已經失去了和DG的關聯，如果Primary故障嚴重，是難以保障對應的歸檔資料可以順利傳輸的。如果希望Primary重新回到DG環境，關鍵就是恢復的時間點。要求Primary回到Standby切換角色的那個時間點，理論上就可以“延續”操作。

3.2.1 查詢原備庫變為新主庫的SCN

在原備庫端，檢視v$database檢視，可以看到這個庫成為primary的具體時間。

			
					SYS@SBDB1> select STANDBY_BECAME_PRIMARY_SCN from v$database; 
				
					STANDBY_BECAME_PRIMARY_SCN 
				
					-------------------------- 
				
					                    869428 
				
					SYS@SBDB1> create table test_bylhr2 as select * from dba_objects where rownum<=1000; 
				
					Table created. 
				
					SYS@SBDB1> alter system switch logfile; 
				
					System altered. 
				
					SYS@SBDB1> select count(1) from test_bylhr2; 
				
					  COUNT(1) 
				
					---------- 
				
					      1000 
				
					SYS@SBDB1> select dbms_flashback.get_system_change_number from dual; 
				
					GET_SYSTEM_CHANGE_NUMBER 
				
					------------------------ 
				
					                  869833

3.2.2 原主庫執行閃回操作

			
					SYS@PROD1> startup mount 
				
					ORACLE instance started. 
				
					Total System Global Area  313860096 bytes 
				
					Fixed Size                  1336232 bytes 
				
					Variable Size             247467096 bytes 
				
					Database Buffers           58720256 bytes 
				
					Redo Buffers                6336512 bytes 
				
					Database mounted. 
				
					SYS@PROD1> flashback database to scn 869428; 
				
					Flashback complete. 
				
					SYS@PROD1> select dbid,name,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database; 
				
					      DBID NAME       CURRENT_SCN PROTECTION_MODE      PROTECTION_LEVEL     DATABASE_ROLE    FOR OPEN_MODE            SWITCHOVER_STATUS 
				
					---------- ---------- ----------- -------------------- -------------------- ---------------- --- -------------------- -------------------- 
				
					2177200393 PROD1                0 MAXIMUM PERFORMANCE  UNPROTECTED          PRIMARY          YES MOUNTED              NOT ALLOWED

注意：重新加入的原Primary是不能恢復角色的，而是隻能先成為Standby角色。應用後續的日誌達到同步。

			
					SYS@PROD1> alter database convert to physical standby; 
				
					Database altered. 
				
					SYS@PROD1> startup force;          
				
					ORACLE instance started. 
				
					Total System Global Area  313860096 bytes 
				
					Fixed Size                  1336232 bytes 
				
					Variable Size             247467096 bytes 
				
					Database Buffers           58720256 bytes 
				
					Redo Buffers                6336512 bytes 
				
					Database mounted. 
				
					Database opened. 
				
					SYS@PROD1> select dbid,name,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database; 
				
					      DBID NAME       CURRENT_SCN PROTECTION_MODE      PROTECTION_LEVEL     DATABASE_ROLE    FOR OPEN_MODE            SWITCHOVER_STATUS 
				
					---------- ---------- ----------- -------------------- -------------------- ---------------- --- -------------------- -------------------- 
				
					2177200393 PROD1           869428 MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES READ ONLY            TO PRIMARY 
				
					SYS@PROD1> alter database recover managed standby database using current logfile disconnect from session; 
				
					Database altered. 
				
					SYS@PROD1> select count(1) from test_bylhr2; 
				
					  COUNT(1) 
				
					---------- 
				
					      1000

Oracle DG在發生Failover之後，當主庫解決問題，是不可以直接回到DG環境的。這個過程往往需要一些輔助組建的配合。如RMAN、Flashback，都可以簡化重回DG的過程時間。

注意：如果原主庫查詢不到test_bylhr2表的資料，則需要仔細檢查監聽使用靜態監聽、fal_client、fal_server、log_archive_dest_1和log_archive_dest_2引數已正確配置。

3.3 執行switchover切換成初始環境

新主庫：

			
					SYS@SBDB1> select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name from v$database; 
				
					NAME      LOG_MODE     OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS    DB_UNIQUE_NAME 
				
					--------- ------------ -------------------- ---------------- -------------------- ------------------------------ 
				
					PROD1     ARCHIVELOG   READ WRITE           PRIMARY          TO STANDBY           SBDB1 
				
					SYS@SBDB1> alter database commit to switchover to physical standby with session shutdown; 
				
					Database altered. 
				
					SYS@SBDB1> shutdown immediate 
				
					ORA-01507: database not mounted 
				
					ORACLE instance shut down. 
				
					SYS@SBDB1> startup mount 
				
					ORACLE instance started. 
				
					Total System Global Area  313860096 bytes 
				
					Fixed Size                  1336232 bytes 
				
					Variable Size             239078488 bytes 
				
					Database Buffers           67108864 bytes 
				
					Redo Buffers                6336512 bytes 
				
					Database mounted. 
				
					SYS@SBDB1>  select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name from v$database; 
				
					NAME      LOG_MODE     OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS    DB_UNIQUE_NAME 
				
					--------- ------------ -------------------- ---------------- -------------------- ------------------------------ 
				
					PROD1     ARCHIVELOG   MOUNTED              PHYSICAL STANDBY TO PRIMARY           SBDB1

新備庫執行：

			
					SYS@PROD1> select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name from v$database; 
				
					NAME   LOG_MODE     OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS    DB_UNIQUE_NAME 
				
					------ ------------ -------------------- ---------------- -------------------- ------------------------------ 
				
					PROD1  ARCHIVELOG   READ ONLY            PHYSICAL STANDBY TO PRIMARY           PROD1 
				
					SYS@PROD1> alter database commit to switchover to primary with session shutdown; 
				
					Database altered. 
				
					SYS@PROD1> select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name from v$database; 
				
					NAME   LOG_MODE     OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS    DB_UNIQUE_NAME 
				
					------ ------------ -------------------- ---------------- -------------------- ------------------------------ 
				
					PROD1  ARCHIVELOG   MOUNTED              PRIMARY          NOT ALLOWED          PROD1 
				
					SYS@PROD1> alter database open; 
				
					Database altered. 
				
					SYS@PROD1>  select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name from v$database; 
				
					NAME   LOG_MODE     OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS    DB_UNIQUE_NAME 
				
					------ ------------ -------------------- ---------------- -------------------- ------------------------------ 
				
					PROD1  ARCHIVELOG   READ WRITE           PRIMARY          RESOLVABLE GAP       PROD1 
				
					SYS@PROD1> create table test_bylhr3 as select * from dual; 
				
					Table created.

備庫查詢：

			
					SYS@SBDB1> select * from test_bylhr3; 
				
					select * from test_bylhr3 
				
					              * 
				
					ERROR at line 1: 
				
					ORA-01219: database not open: queries allowed on fixed tables/views only 
				
					SYS@SBDB1> alter database open; 
				
					Database altered. 
				
					SYS@SBDB1> select * from test_bylhr3; 
				
					D 
				
					- 
				
					X

至此，該實驗完畢。即主備庫執行Failover後，透過閃回重新恢復最初的DG環境。

第四章實驗2

實驗2、PROD1意外當機，SBDB1執行Failover操作變為主庫；然後將SBDB1利用閃回資料庫功能閃回到SBDB1變為主庫的SCN時間點，此時SBDB1仍然是主庫的角色，然後將SBDB1轉換為備庫。<===SBDB1需要開啟閃回，會丟失部分資料。

4.1 Failover操作

主庫操作：

			
					SYS@PROD1>  select dbid,name,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database; 
				
					      DBID NAME   CURRENT_SCN PROTECTION_MODE      PROTECTION_LEVEL     DATABASE_ROLE    FOR OPEN_MODE            SWITCHOVER_STATUS 
				
					---------- ------ ----------- -------------------- -------------------- ---------------- --- -------------------- -------------------- 
				
					2177200393 PROD1       901700 MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  PRIMARY          YES READ WRITE           TO STANDBY 
				
					SYS@PROD1> create table test_bylhr4 as select * from dual; 
				
					Table created. 
				
					SYS@PROD1> select * from test_bylhr4; 
				
					D 
				
					- 
				
					X 
				
					SYS@PROD1> shutdown abort 
				
					ORACLE instance shut down.

備庫操作：

			
					SYS@SBDB1> select * from test_bylhr4; 
				
					D 
				
					- 
				
					X 
				
					SYS@SBDB1> alter database recover managed standby database finish force; 
				
					Database altered. 
				
					SYS@SBDB1> select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name from v$database; 
				
					NAME      LOG_MODE     OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS    DB_UNIQUE_NAME 
				
					--------- ------------ -------------------- ---------------- -------------------- ------------------------------ 
				
					PROD1     ARCHIVELOG   MOUNTED              PHYSICAL STANDBY TO PRIMARY           SBDB1 
				
					SYS@SBDB1>  alter database commit to switchover to primary with session shutdown; 
				
					Database altered. 
				
					SYS@SBDB1> select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name from v$database; 
				
					NAME      LOG_MODE     OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS    DB_UNIQUE_NAME 
				
					--------- ------------ -------------------- ---------------- -------------------- ------------------------------ 
				
					PROD1     ARCHIVELOG   MOUNTED              PRIMARY          NOT ALLOWED          SBDB1 
				
					SYS@SBDB1>  select * from test_bylhr4; 
				
					 select * from test_bylhr4 
				
					               * 
				
					ERROR at line 1: 
				
					ORA-01219: database not open: queries allowed on fixed tables/views only 
				
					SYS@SBDB1> alter database open; 
				
					Database altered. 
				
					SYS@SBDB1> select * from test_bylhr4; 
				
					D 
				
					- 
				
					X 
				
					SYS@SBDB1>  create table test_bylhr5 as select * from dual; 
				
					Table created. 
				
					SYS@SBDB1>  select * from test_bylhr5; 
				
					D 
				
					- 
				
					X

4.2 新主庫閃回

			
					SYS@SBDB1> shutdown immediate 
				
					Database closed. 
				
					Database dismounted. 
				
					ORACLE instance shut down. 
				
					SYS@SBDB1> startup mount 
				
					ORACLE instance started. 
				
					Total System Global Area  313860096 bytes 
				
					Fixed Size                  1336232 bytes 
				
					Variable Size             243272792 bytes 
				
					Database Buffers           62914560 bytes 
				
					Redo Buffers                6336512 bytes 
				
					Database mounted. 
				
					SYS@SBDB1>  select STANDBY_BECAME_PRIMARY_SCN from v$database; 
				
					STANDBY_BECAME_PRIMARY_SCN 
				
					-------------------------- 
				
					                    901723 
				
					SYS@SBDB1>  flashback database to scn 901723; 
				
					Flashback complete. 
				
					SYS@SBDB1> select * from test_bylhr5; 
				
					select * from test_bylhr5 
				
					              * 
				
					ERROR at line 1: 
				
					ORA-01219: database not open: queries allowed on fixed tables/views only 
				
					SYS@SBDB1> select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name from v$database; 
				
					NAME      LOG_MODE     OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS    DB_UNIQUE_NAME 
				
					--------- ------------ -------------------- ---------------- -------------------- ------------------------------ 
				
					PROD1     ARCHIVELOG   MOUNTED              PRIMARY          NOT ALLOWED          SBDB1 
				
					SYS@SBDB1>  alter database convert to physical standby; 
				
					Database altered. 
				
					SYS@SBDB1> shutdown immediate 
				
					ORA-01507: database not mounted 
				
					ORACLE instance shut down. 
				
					SYS@SBDB1> startup mount 
				
					ORACLE instance started. 
				
					Total System Global Area  313860096 bytes 
				
					Fixed Size                  1336232 bytes 
				
					Variable Size             243272792 bytes 
				
					Database Buffers           62914560 bytes 
				
					Redo Buffers                6336512 bytes 
				
					Database mounted. 
				
					SYS@SBDB1> select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name from v$database; 
				
					NAME      LOG_MODE     OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS    DB_UNIQUE_NAME 
				
					--------- ------------ -------------------- ---------------- -------------------- ------------------------------ 
				
					PROD1     ARCHIVELOG   MOUNTED              PHYSICAL STANDBY TO PRIMARY           SBDB1 
				
					SYS@SBDB1> alter database open; 
				
					Database altered. 
				
					SYS@SBDB1> select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name from v$database; 
				
					NAME      LOG_MODE     OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS    DB_UNIQUE_NAME 
				
					--------- ------------ -------------------- ---------------- -------------------- ------------------------------ 
				
					PROD1     ARCHIVELOG   READ ONLY            PHYSICAL STANDBY TO PRIMARY           SBDB1 
				
					SYS@SBDB1>  
				
					SYS@SBDB1> alter database recover managed standby database using current logfile disconnect from session; 
				
					Database altered. 
				
					SYS@SBDB1>  select * from test_bylhr5; 
				
					 select * from test_bylhr5 
				
					               * 
				
					ERROR at line 1: 
				
					ORA-00942: table or view does not exist 
				
					SYS@SBDB1> select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name from v$database; 
				
					NAME      LOG_MODE     OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS    DB_UNIQUE_NAME 
				
					--------- ------------ -------------------- ---------------- -------------------- ------------------------------ 
				
					PROD1     ARCHIVELOG   READ ONLY WITH APPLY PHYSICAL STANDBY SESSIONS ACTIVE      SBDB1

4.3 原主庫開啟

			
					SYS@PROD1> startup force 
				
					ORACLE instance started. 
				
					Total System Global Area  313860096 bytes 
				
					Fixed Size                  1336232 bytes 
				
					Variable Size             251661400 bytes 
				
					Database Buffers           54525952 bytes 
				
					Redo Buffers                6336512 bytes 
				
					Database mounted. 
				
					Database opened. 
				
					SYS@PROD1> create table test_bylhr6 as select * from dual; 
				
					Table created. 
				
					SYS@PROD1> select * from test_bylhr6; 
				
					D 
				
					- 
				
					X

備庫查詢：

			
					SYS@SBDB1> select * from test_bylhr6; 
				
					D 
				
					- 
				
					X

DGMGRL中檢視配置：

			
					DGMGRL> show  CONFIGURATION VERBOSE; 
				
					Configuration - PROD1.us.oracle.com 
				
					  Protection Mode: MaxPerformance 
				
					  Databases: 
				
					    PROD1 - Primary database 
				
					    SBDB1 - Physical standby database 
				
					Fast-Start Failover: DISABLED 
				
					Configuration Status: 
				
					SUCCESS

說明直接在原備庫上執行閃回操作也可以讓Failover後的環境重新恢復最初的環境。不過這也意味著部分資料的丟失。

第五章實驗總結

1、主庫在開啟閃回資料庫功能後，備庫並不會開啟閃回資料庫的功能。

2、PROD1為主庫，SBDB1為備庫；若PROD1意外當機，則SBDB1執行Failover操作變為主庫；此時若想恢復DG環境，則有3種處理辦法：

① 將PROD1利用閃回資料庫功能閃回到SBDB1變為主庫的SCN時間點，然後將PROD1轉換為備庫，最後利用switchover轉換為最初的環境。<===PROD1需要開啟閃回

② 將SBDB1利用閃回資料庫功能閃回到SBDB1變為主庫的SCN時間點，此時SBDB1仍然是主庫的角色，然後將SBDB1轉換為備庫。<===SBDB1需要開啟閃回，會丟失部分資料。

③ 利用RMAN重新搭建DG環境。詳情請參考：http://blog.itpub.net/26736162/viewspace-1753130/

第六章參考文章

6.1 MOS或官網

This chapter describes scenarios you might encounter while administering your Data Guard configuration. Each scenario can be adapted to your specific environment.聽Table 13-1lists the scenarios presented in this chapter.

Table 13-1 Data Guard Scenarios

Reference	Scenario

這8個實驗，建議有興趣的朋友都做一遍。

http://blog.itpub.net/24500180/viewspace-1068495/

http://blog.itpub.net/24500180/viewspace-1068141/

http://blog.itpub.net/24500180/viewspace-1069602/

13.2 Converting a Failed Primary Into a Standby Database Using Flashback Database

After a failover occurs, the original primary database can no longer participate in the Data Guard configuration until it is repaired and established as a standby database in the new configuration. To do this, you can use the Flashback Database feature to recover the failed primary database to a point in time before the failover occurred, and then convert it into a physical or logical standby database in the new configuration. The following sections describe:

Note:
You must have already enabled Flashback Database on the original primary database before the failover. See for more information.
See Also:
for automatic reinstatement of the failed primary database as a new standby database (as an alternative to using Flashback Database)

13.2.1 Flashing Back a Failed Primary Database into a Physical Standby Database

The following steps assume that a failover has been performed to a physical standby database and that Flashback Database was enabled on the old primary database at the time of the failover. This procedure brings the old primary database back into the Data Guard configuration as a physical standby database.

Step 1 Determine the SCN at which the old standby database became the primary database.

On the new primary database, issue the following query to determine the SCN at which the old standby database became the new primary database:

SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;

Step 2 Flash back the failed primary database.

Shut down the old primary database (if necessary), mount it, and flash it back to the value for STANDBY_BECAME_PRIMARY_SCN that was determined in .

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> FLASHBACK DATABASE TO SCN standby_became_primary_scn;

Step 3 Convert the database to a physical standby database.

Perform the following steps on the old primary database:

Issue the following statement on the old primary database:
```
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
```
This statement will dismount the database after successfully converting the control file to a standby control file.

Shut down and restart the database:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

Step 4 Start transporting redo to the new physical standby database.

Perform the following steps on the new primary database:

Issue the following query to see the current state of the archive destinations:

SQL> SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION, -
> ERROR,SRL FROM V$ARCHIVE_DEST_STATUS;

If necessary, enable the destination:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_n=ENABLE;

Perform a log switch to ensure the standby database begins receiving redo data from the new primary database, and verify it was sent successfully. Issue the following SQL statements on the new primary database:
```
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION,- 
> ERROR,SRL FROM V$ARCHIVE_DEST_STATUS;
```
On the new standby database, you may also need to change the LOG_ARCHIVE_DEST_n initialization parameters so that redo transport services do not transmit redo data to other databases.

Step 5 Start Redo Apply on the new physical standby database.

Issue the following SQL statement on the new physical standby database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE -
> USING CURRENT LOGFILE DISCONNECT;

Redo Apply automatically stops each time it encounters a redo record that is generated as the result of a role transition, so Redo Apply will need to be restarted one or more times until it has applied beyond the SCN at which the new primary database became the primary database. Once the failed primary database is restored and is running in the standby role, you can optionally perform a switchover to transition the databases to their original (pre-failure) roles. See for more information.

6.2 部落格

Data Guard高階玩法：透過閃回恢復failover備庫：http://blog.itpub.net/23718752/viewspace-2124234/

使用Flashback讓Failover資料庫重新加入DG環境：http://blog.itpub.net/17203031/viewspace-1184082

http://blog.itpub.net/24500180/viewspace-1068495/

http://blog.itpub.net/24500180/viewspace-1068141/

http://blog.itpub.net/24500180/viewspace-1069602/

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

About Me

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

● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除

● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/viewspace-2146883/

● 本文部落格園地址：http://www.cnblogs.com/lhrbest/p/7793934.html

● 本文pdf版、個人簡介及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● DBA寶典今日頭條號地址：

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

● QQ群號：230161599（滿）、618766405

● 微信群：可加我微信，我拉大家進群，非誠勿擾

● 聯絡我請加QQ好友（646634621），註明新增緣由

● 於 2017-11-01 09:00 ~ 2017-11-30 22:00 在魔都完成

● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

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

● 小麥苗的微店：

● 小麥苗出版的資料庫類叢書：http://blog.itpub.net/26736162/viewspace-2142121/

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

使用微信客戶端掃描下面的二維碼來關注小麥苗的微信公眾號（xiaomaimiaolhr）及QQ群（DBA寶典），學習最實用的資料庫技術。

小麥苗的微信公眾號小麥苗的DBA寶典QQ群2 《DBA筆試面寶典》讀者群小麥苗的微店

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

【DG】利用閃回資料庫（flashback）修復Failover後的DG環境

1.1 BLOG文件結構圖

1.2 前言部分

1.2.1 導讀和注意事項

1.3 本文簡介

1.4 相關知識點掃盲

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

第二章實驗準備

2.1 實驗環境介紹

2.2 實驗目標

2.3 實驗過程

2.4 利用GC快速搭建DG環境

2.4.1 安裝GC客戶端軟體

2.4.2 使用GC快速搭建物理備庫

2.4.3 啟用實時應用

2.5 開啟主備庫閃回

第三章實驗1

3.1 Failover操作

3.2 Primary重新加入

3.2.1 查詢原備庫變為新主庫的SCN

3.2.2 原主庫執行閃回操作

3.3 執行switchover切換成初始環境

第四章實驗2

4.1 Failover操作

4.2 新主庫閃回

4.3 原主庫開啟

第五章實驗總結

第六章參考文章

6.1 MOS或官網

13.2 Converting a Failed Primary Into a Standby Database Using Flashback Database

13.2.1 Flashing Back a Failed Primary Database into a Physical Standby Database

6.2 部落格

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

相關文章

【DG】利用閃回資料庫（flashback）修復Failover後的DG環境

1.1 BLOG文件結構圖

1.2 前言部分

1.2.1 導讀和注意事項

1.3 本文簡介

1.4 相關知識點掃盲

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

第二章 實驗準備

2.1 實驗環境介紹

2.2 實驗目標

2.3 實驗過程

2.4 利用GC快速搭建DG環境

2.4.1 安裝GC客戶端軟體

2.4.2 使用GC快速搭建物理備庫

2.4.3 啟用實時應用

2.5 開啟主備庫閃回

第三章 實驗1

3.1 Failover操作

3.2 Primary重新加入

3.2.1 查詢原備庫變為新主庫的SCN

3.2.2 原主庫執行閃回操作

3.3 執行switchover切換成初始環境

第四章 實驗2

4.1 Failover操作

4.2 新主庫閃回

4.3 原主庫開啟

第五章 實驗總結

第六章 參考文章

6.1 MOS或官網

13.2 Converting a Failed Primary Into a Standby Database Using Flashback Database

13.2.1 Flashing Back a Failed Primary Database into a Physical Standby Database

6.2 部落格

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

相關文章

第二章實驗準備

第三章實驗1

第四章實驗2

第五章實驗總結

第六章參考文章