閱讀820 返回首頁    go 阿裏雲 go 技術社區[雲棲]

Oracle DataGuard：單節點到RAC集群的主備環境搭建

需求
隨著業務增長，數據量業務複雜度越來越高，數據量越來越大，對數據庫和服務器的性能、高可用、容災等要求也越來越高。以當前的數據庫環境為例，Windows 2008 r2 服務器+NAS存儲+Oracle11.2.0.1+12T+300GARCH/天的規模已經變得非常臃腫，不再適合快速發展的業務場景。
因此，根據業務場景、數據規模和客戶需求，提出數據庫遷移至Linux+OracleRAC環境，同步完成之後再做主備切換；滿足數據庫高可用、容災需求。

實施步驟
DBCA創建集群測試數據庫(orcl)；
主備數據庫參數設置；
刪除集群數據庫控製文件和數據文件、日誌文件；
隻啟動RAC節點1的實例做數據庫同步；
歸檔追完之後，啟動節點2；
監控數據庫同步狀態；
監控集群狀態。

主備數據庫環境
image

網絡規劃
image
主備環境在同一個機房內，並且為了不影響生產環境，專門拉了一條直連的網線，配置局域網內環境。主機配置192.168.1.1，備機RAC節點1配置192.168.1.2。
切記DNS不要自動生成，以免在兩個機器內部產生環路，影響與核心交換機的網路路由。

參考文檔
How to Create a RAC Standby Database
https://www.oracle.com/technetwork/cn/database/dataguardoverview-091578-zhs.html
https://www.oracle.com/technetwork/database/features/availability/twp-dataguard-11gr2-1-131981.pdf

主庫環境檢查和參數設置

# 是否開啟 force logging
SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
YES
#   如果NO，則強製開啟
SQL> alter database force logging;
Database altered.

# 是否開啟歸檔
SQL> archive log list

Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           G:\arch
Oldest online log sequence     100
Next log sequence to archive   102
Current log sequence           102
#    如果未開啟，則重啟數據庫至mount狀態，開啟歸檔
SQL> startup mount
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2926472 bytes
Variable Size 1392511096 bytes
Database Buffers 738197504 bytes
Redo Buffers 13848576 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.

# (oracle)tnsnames.ora  服務配置
# 主備數據庫的都需要配置，配置完成後將文件拷貝至RAC雙節點
ORCL_PRI =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1523))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

test_rac =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
      (UR = A)
    )
  )

# (oracle)密碼文件拷貝至備庫各節點(本次采取直接在備庫生成密碼文件)
cd $ORACLE_HOME/dbs
orapwd file=orapwdorcl1 entries=5 ignorecase=y password=xxxxxx

# (oracle)注冊靜態監聽 listener.ora
LISTENER3 = 
  (DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT= 1523))
  )

SID_LIST_LISTENER3 =
  (SID_LIST = 
    (SID_DESC = 
      (GLOBAL_NAME = orcl)
      (ORACLE_HOME =D:\app\Administrator\product\11.2.0\dbhome_1)
      (SID_NAME = orcl)
        (ENVS = “EXTPROC_DLLS=ONLY:D:\app\Administrator\product\11.2.0\dbhome_1\bin\oracle11.dll”)
    )
  )

lsnrctl start LISTENER3

lsnrctl status LISTENER3
…………
Service "orcl" has 1 instance(s).
  Instance "orcl", status UNKNOW, has 1 handler(s) for this service…

# (oracle)備庫上測試監聽是否可以正常連接
tnsping  orcl_pri
sqlplus xxx/yyy@orcl_pri

# (oracle)數據文件路徑
SQL> select name from v$datafile;
………………

# (oracle)日誌文件路徑

SQL> select member from v$logfile;
…………

# (oracle)初始化參數配置
# 因為主庫已有一個單節點的備庫，所以部分參數不做修改
# 在線修改
alter system set fal_server = 'orcl_dr,testrac';    
alter system set log_archive_dest_3='SERVICE=test_rac LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testrac';

備庫環境準備和參數設置(節點1)

# (oracle)是否開啟 force logging
SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
YES
#   如果NO，則強製開啟
SQL> alter database force logging;
Database altered.

# (oracle)是否開啟歸檔
SQL> archive log list
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           +ORAARCH/orcl/archivelog 
Oldest online log sequence     100
Next log sequence to archive   102
Current log sequence           102
#     如果未開啟，則重啟數據庫至mount狀態，開啟歸檔
SQL> startup mount
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2926472 bytes
Variable Size 1392511096 bytes
Database Buffers 738197504 bytes
Redo Buffers 13848576 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.

# (oracle)tnsnames.ora  服務配置
# 主備數據庫的都需要配置，配置完成後將文件拷貝至RAC雙節點
ORCL_PRI =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1523))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

test_rac =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
      (UR = A)
    )
  )

# (oracle)注冊靜態監聽 listener.ora
# 主備數據庫的都需要配置，配置完成後將文件拷貝至RAC雙節點，修改SID_NAME和HOST信息
LISTENER = 
  (DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = testrac1)(PORT= 1521))
  )

SID_LIST_LISTENER =
  (SID_LIST = 
    (SID_DESC = 
      (GLOBAL_NAME = orcl)
      (ORACLE_HOME = /opt/oracle/database/product/11.2.0)
      (SID_NAME = orcl1)
    )
  )

ADR_BASE_LISTENER = /opt/oracle/database

lsnrctl start LISTENER

lsnrctl status
…………
Service "LISTENER" has 1 instance(s).
  Instance "orcl1", status UNKNOW, has 1 handler(s) for this service…

# (oracle)主庫上測試監聽是否可以正常連接
tnsping  test_rac
sqlplus xxx/yyy@test_rac

# (oracle)數據文件路徑
SQL> select name from v$datafile;
………………

# (oracle)日誌文件路徑
SQL> select member from v$logfile;
……………

# (oracle)停庫，oracle用戶下
srvctl stop database -d orcl
srvctl status database -d orcl

# (oracle)去掉在OCR中的注冊信息
srvctl remove instance -d orcl -i orcl1
srvctl remove instance -d orcl -i orcl2
srvctl remove database -d orcl 

# (grid)刪除數據、日誌文件、控製文件
asmcmd

# (oracle)初始化參數配置，啟動數據庫至nomount狀態
# 在線修改
alter system set db_files=2000 scope=spfile;

alter system set db_unique_name=testrac scope=spfile;
alter system set service_names=orcl;

alter system set control_files=‘+ORADATA/orcl/controlfile/controlfile01.ora’,’+ORADATA/orcl/controlfile/controlfile02.ora','+ORAFRA/orcl/controlfile/controlfile03.ora' scope=spfile;

alter system set log_archive_config=‘dg_config=(orcl_pri,test_rac)’; 

alter system set log_archive_dest_1='LOCATION=+ORAARCH VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=testrac';

alter system set log_archive_dest_2='SERVICE=orcl_pri LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl';

alter system set fal_client=test_rac;

alter system set fal_server=orcl_pri;

alter system set db_file_name_convert='D:\APP\ADMINISTRATOR\ORADATA\ORCL','+ORADATA/orcl/datafile','G:\DATA','+ORADATA/orcl/datafile','F:\DATA','+ORADATA/orcl/datafile','E:\DATA','+ORADATA/orcl/datafile','C:\ORADATA','+ORADATA/orcl/datafile' scope=spfile;

alter system set log_file_name_convert='G:\arch\ORCL','+ORADATA/orcl/onlinelog' scope=spfile;

# (oracle)注冊testrac 信息至grid集群
srvctl add database -d testrac -o /opt/oracle/database/product/11.2.0 -p +ORADATA/orcl/spfileorcl.ora -r PRIMARY -s open  -a “ORADATA,ORAARCH,ORAFRA"

srvctl add instance -d testrac -i orcl1 -n testrac1
srvctl add instance -d testrac -i orcl2 -n testrac2

# (oracle)重啟，並手動生成 pfile 拷貝至備庫
srvctl start database -d testrac -o nomount
srvctl status database -d testrac
create file=$ORACLE_HOME/pfile from spfile=‘+ORADATA/orcl/spfileorcl.ora’;

# (oracle)關閉集群
srvctl stop database -d testrac

ADG同步

# (oracle)啟動節點1的數據庫實例至 nomount 狀態
srvctl start database -d testrac -n testrac1 -o no mount

# (oracle)開始同步
參考附錄1

standby 和redo 日誌文件處理

# (oracle)創建standby log file，比主庫在線日誌組多一個
alter database add standby logfile group 11 '+ORADATA/orcl/standbylog/standby11a.dbf' size 1G;
………………
alter database add standby logfile group 17 '+ORADATA/orcl/standbylog/standby17a.dbf' size 1G;

# (oracle)開啟實時日誌恢複
alter database recover managed standby database disconnect from session using current logfile;

# (oracle)主庫切換係統日誌(多次)
alter system switch logfile;

# (oracle)打開備庫
alter database recover managed standby database cancel;
alter database open;

# (oracle)重建備庫redo log group
# 查看當前日誌狀態
select GROUP#,STATUS from v$log;

# 關閉standby 文件自動管理
alter system set standby_file_management = ‘MANUAL';

# 清理並刪除CLEARING 狀態的日誌組和文件，並重建
alter database clear logfile group 1;
alter database drop logfile group 1;
alter database add logfile group 1 '+ORADATA/orcl/ONLINELOG/online01.ora' size 1G;
………………
# 清理並刪除CLEARING_CURRENT狀態的日誌組和文件，並重建
# 主庫切換日誌
alter system switch logfile;

# 備庫重建日誌
alter database clear logfile group 4;
alter database drop logfile group 4;
alter database add logfile group 4 '+ORADATA/orcl/ONLINELOG/online04.ora' size 1G;

# (oracle)開啟數據庫實時同步進程（MRP）
alter system set standby_file_management = ‘AUTO';
alter database recover managed standby database disconnect from session using current logfile;

同步校驗

# (oracle)連接備庫校驗
SQL> select sequence#,archived,applied from v$archived_log;
SEQUENCE# ARC APPLIED

99 YES YES
   100 YES YES
   101 YES IN-MEMORY

SQL> select to_number(substr(t.value,5,2)) h, to_number(substr(value,8,2)) s from v$dataguard_stats t where name = 'apply lag';
h s

0 0

# (oracle)主庫

SQL> create table sync_test(id number, name varchar2(20));
SQL> insert into sync_test values(1,’haha’);
SQL> commit;

(oracle)備庫

SQL> select * from sync_test;

    ID         NAME
    -- ----------
     1         haha

集群處理

# 拉起RAC集群
# (oracle)當前集群狀態
srvctl status database -d testrac
Instance orcl1 is running on node testrac1
Instance orcl2 is not running on node testrac2

# (oracle)拉起節點2實例
srvctl start database -d testrac  -n testrac2

# (oracle)查看集群狀態
srvctl status database -d testrac
    Instance orcl1 is running on node testrac1
    Instance orcl2 is running on node testrac2

SQL> select inst_id,instance_name,status from gv$instance;
INST_ID INSTANCE_NAME STATUS

     1 orcl1            OPEN
     2 orcl2            OPEN
更多集群狀態查詢，參考附錄2

最後更新：2017-07-24 12:02:43

  上一篇：go  Vim技能修煉教程(17) - 編譯自己的Vim
  下一篇：go  亞太市場網絡安全調查報告：中國安全預算投入排第二