11g dataguard使用總結

11g的dataguard相比於10g來說，最優越的特性應該算就是active dataguard了，這一點改進在很大意義上促使使用者需要把資料庫從10g升級到11g,讀寫分離在這個時候得到了昇華，而且在後臺會根據需要進行資料的同步，相比於使用10g，想讀資料的時候把資料庫啟動到read only 階段，但這個時候不接受日誌同步資料，如果需要同步資料還需要把資料庫再啟動到mount階段，感覺還是比較繁瑣的。
11g的active dataurad功能很強大，同時搭建的時候使用rman 的duplicate選項在11g也得到了很大的改進，我們不需要專門去做一個備份，就可以直接在備庫上透過duplicate把資料檔案從主庫傳送到備庫，同時做恢復，整個過程更加簡潔。
當然了一般的系統是一主一備，關鍵的系統甚至要一主兩備，如果系統多了，手工管理很容易搞糊塗，這個時候還是用加強版的dg broker來做吧，以前在10g的時候感覺還是手工管理，手工switchover，failover比較踏實，因為也在10g的過程中switchover,failover出現過一些問題，算是留下了一些陰影，到了11g的時候，工作中大量使用，就嘗試著自己試了試，發現確實很好，而且搭建備庫步驟比手工搭建要輕鬆的多。所以還是需要擁抱變化，勇敢接受新事物，一方面可以給自己療傷，一方面恢復使用的信心，至少在災難發生的時候回臨危不亂。
我們來簡單看一個例子，怎麼結合dg broker來搭建dataguard,步驟相比手工要簡化很多，而且可靠性要高很多，因為人為去做的檢查點，在dg broker的檢查中都會去做。
出了設定主庫為歸檔模式，force logging之外。
我們所要做的就是配置網路監聽。當然這個也不是難點，listener.ora tnsnames.ora主備是很相似的。
比如我們使用1523的埠在primary和standby之間互聯
                    listener.ora  主庫和備庫的host部分不一樣
LISTENER_1523 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 10.xxxxx.45)(PORT = 1523))
      )
    )
  )

SID_LIST_LISTENER_1523 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = test11g)
      (ORACLE_HOME = /DATA/app/oracle/product/11.2.0.4)
      (SID_NAME = test11g)
 )
 )  

tnsnames.ora 主庫備庫一致
TEST11G =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.xxxxx.45)(PORT = 1523))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test11g)
    )
  )

STEST11G =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.xxxxxx.46)(PORT = 1523))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test11g)
    )
)

然後就是使用tnsping開始檢查，檢查防火牆，等等
新增備庫日誌
主庫檢視日誌情況，可以使用下面的方法檢查一下。
SQL> select member,group#,status from v$logfile
MEMBER                                                 GROUP# STATUS
-------------------------------------------------- ---------- -------
/DATA/app/oracle/oradata/test11g/redo03.log                 3
/DATA/app/oracle/oradata/test11g/redo02.log                 2
/DATA/app/oracle/oradata/test11g/redo01.log                 1
SQL> select group#,status,bytes,blocksize,status from v$log;
    GROUP# STATUS                BYTES  BLOCKSIZE STATUS
---------- ---------------- ---------- ---------- ----------------
         1 INACTIVE           52428800        512 INACTIVE
         2 CURRENT            52428800        512 CURRENT
         3 INACTIVE           52428800        512 INACTIVE
新增備庫日誌的語句類似：
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/DATA/app/oracle/oradata/test11g/redo04.log' SIZE 524288000;
修改下面的資料庫引數，
alter system set standby_file_management=auto scope=both;
alter system set dg_broker_start=TRUE scope=both;
alter system set local_listener=TEST11G scope=both;

檢查dmon的情況
SQL> !ps -ef|grep dmon_test11g
oracle    8129     1  0 11:25 ?        00:00:00 ora_dmon_test11g
oracle    8138  8108  0 11:25 pts/0    00:00:00 /bin/bash -c ps -ef|grep dmon_test11g
oracle    8140  8138  0 11:25 pts/0    00:00:00 grep dmon_test11g

檢視dg broker的日誌，開始的時候沒有配置檔案，會重新建立。
DMON: cannot open configuration file "/DATA/app/oracle/product/11.2.0.4/dbs/dr2test11g.dat"
  ORA-27037: unable to obtain file status
  Linux-x86_64 Error: 2: No such file or directory
  Additional information: 3
DMON: cannot open configuration file "/DATA/app/oracle/product/11.2.0.4/dbs/dr2test11g.dat"
  ORA-27037: unable to obtain file status
  Linux-x86_64 Error: 2: No such file or directory
  Additional information: 3
Configuration does not exist, Data Guard broker ready

資料庫alert日誌：
Mon Jul 27 11:25:15 2015
DMON started with pid=26, OS id=8129 
Starting Data Guard Broker (DMON)
Mon Jul 27 11:25:23 2015

接著複製密碼檔案，引數檔案(spfile)到備庫

在備庫生成pfile,然後修改下面的引數
        修改db_unique_name為stest11g
        修改local_listener為stest11g
        配置 db_file_name_convert
             log_file_name_convert
   db_file_name_convert='/DATA/app/oracle/oradata/test11g','/DATA/app/oracle/oradata/test11g','/DATA/app/oracle/fast_recovery_area/test11g','/DATA/app/oracle/fast_recovery_area/test11g'              log_file_name_convert='/DATA/app/oracle/oradata/test11g','/DATA/app/oracle/oradata/test11g','/DATA/app/oracle/fast_recovery_area/test11g','/DATA/app/oracle/fast_recovery_area/test11g' 
檢查db_recovery_file_dest 和其它的檔案路徑是否有效(adump,oradata)
             fal_server
             fal_client
生成spfile
這個時候被庫的工作就基本完成了，剩下的就是使用rman來恢復，dg broker來配置了。

資料複製恢復
啟動備庫到nomount階段
SQL> create spfile from pfile;
File created.
SQL> startup nomount
SQL> !ps -ef|grep dmon_test11g
oracle   17773     1  0 11:44 ?        00:00:00 ora_dmon_test11g
oracle   17779 17733  0 11:44 pts/0    00:00:00 /bin/bash -c ps -ef|grep dmon_test11g
oracle   17781 17779  0 11:44 pts/0    00:00:00 grep dmon_test11g

檢查引數是否生效
備庫透過rman複製資料檔案
$  rman target sys/xxxx@test11g auxiliary sys/xxxxx@stest11g nocatalog
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Jul 27 11:46:23 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: TEST11G (DBID=1038061657)
using target database control file instead of recovery catalog
connected to auxiliary database: TEST11G (not mounted)
RMAN> duplicate target database for standby from active database nofilenamecheck;

配置完成，資料複製工作就告一段落。
開始配置dg broker

主庫執行：
$ dgmgrl /
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> create configuration dg_test11g as
> primary database is test11g
> connect identifier is test11g;
Configuration "dg_test11g" created with primary database "test11g"
DGMGRL> add database stest11g as
> connect identifier is stest11g
> maintained as physical;
Database "stest11g" added
這個時候配置還沒有生效
DGMGRL> show configuration;
Configuration - dg_test11g
  Protection Mode: MaxPerformance
  Databases:
    test11g  - Primary database
    stest11g - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
啟用配置
DGMGRL> enable  configuration;
然後再次檢視，如果配置沒有問題，就會生效。
DGMGRL> show configuration;
Configuration - dg_test11g
  Protection Mode: MaxPerformance
  Databases:
    test11g  - Primary database
    stest11g - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
接著進行再次驗證
DGMGRL> enable database test11g;
Enabled.
DGMGRL> enable database stest11g;
Enabled.
DGMGRL> 
DGMGRL> show configuration;
Configuration - dg_test11g
  Protection Mode: MaxPerformance
  Databases:
    test11g  - Primary database
    stest11g - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

dataguard的搭建就完成了，可以在主庫切換日誌檢視是否在備庫資料庫日誌中會應用日誌
Media Recovery Waiting for thread 1 sequence 12 (in transit)
Recovery of Online Redo Log: Thread 1 Group 4 Seq 12 Reading mem 0
  Mem# 0: /DATA/app/oracle/oradata/test11g/redo04.log

11g備庫使用active dataguard特性。
SQL> alter database open;
Database altered.

SQL>  recover managed standby database using current logfile disconnect from session;
Media recovery complete.

switchover的問題答疑
如果出現下面的錯誤，很可能當前作業系統使用者下安裝了多個資料庫例項，取消預設的ORACLE_SID即可。
或者直接使用dgmgrl sys/oracle@test11g 這種方式連線
DGMGRL> switchover to 'stest11g';
Performing switchover NOW, please wait...
Operation requires a connection to instance "test11g" on database "stest11g"
Connecting to instance "test11g"...
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
        connect to instance "test11g" of database "stest11g"

切換正常，但是需要手工啟動備庫，切換其實是正常的，需要手工啟動一下主庫
主要原因就是在listener.ora中需要配置一個xxxx_DGMGRL的global_name

DGMGRL> switchover to stest11g
Performing switchover NOW, please wait...
Operation requires a connection to instance "test11g" on database "stest11g"
Connecting to instance "test11g"...
Connected.
New primary database "stest11g" is opening...
Operation requires startup of instance "test11g" on database "test11g"
Starting instance "test11g"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Failed.
Warning: You are no longer connected to ORACLE.
Please complete the following steps to finish switchover:
        start up instance "test11g" of database "test11g"

如果配置完全正確，正常切換的日誌就會如下：
DGMGRL> switchover to stest11g;
Performing switchover NOW, please wait...
Operation requires a connection to instance "test11g" on database "stest11g"
Connecting to instance "test11g"...
Connected.
New primary database "stest11g" is opening...
Operation requires startup of instance "test11g" on database "test11g"
Starting instance "test11g"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "stest11g"
DGMGRL> 
最後還是希望大家能夠合理使用dataguard,一切順利。