Oracle 11g DataGuard使用總結

Oracle 11g DataGuard使用總結

Oracle 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,一切順利。

在CentOS 6.4下安裝Oracle 11gR2(x64)

Oracle 11gR2 在VMWare虛擬機器中安裝步驟

Debian 下 安裝 Oracle 11g XE R2

Oracle Data Guard 重要配置參數

基於同一主機配置 Oracle 11g Data Guard

探索Oracle之11g DataGuard

Oracle Data Guard （RAC+DG) 歸檔刪除策略及指令碼

Oracle Data Guard 的角色轉換

Oracle Data Guard的日誌FAL gap問題

Oracle 11g Data Guard Error 16143 Heartbeat failed to connect to standby 處理方法