虛擬機上Oracle 10g DataGuard的配置
在Oracle數據庫中，DataGuard最主要的功能就是容災。它可以分為物理STANDBY和邏輯STANDBY兩種。物理STANDBY主要用在主庫的歸檔日志方面；邏輯STANDBY主要應用的是主庫的歸檔日志提取的SQL語句。本文主要論述的是DataGuard在虛擬機上的配置，包括STANDBY參數的文件的相關配置等。
1.環境準備
虛擬機版本：VMware GSX
操作系統 ：redhat linux 4
Primary主機
ip:192.168.111.131
db_name:wellcomm
db_unique_name:wellcomm
ip:192.168.111.131
db_name:wellcomm
db_unique_name:wellcommb
2.設置Primary主機為force logging模式
- alter database force logging;
3.在Primary 上面創建備用日志(為切換而用)
- alter database add standby logfile group 4 ('/u01/oracle/oradata/wellcomm/stdredo01.log') size 50m;
- alter database add standby logfile group 5 ('/u01/oracle/oradata/wellcomm/stdredo02.log') size 50m;
- alter database add standby logfile group 6 ('/u01/oracle/oradata/wellcomm/stdredo03.log') size 50m;
4.修改primary庫的參數
- alter system set db_unique_name='wellcomm' scope=spfile;
- alter system set log_archive_config='DG_CONFIG=(wellcomm,wellcommb)';
- alter system set log_archive_dest_1='LOCATION=/u01/oracle/oradata/wellcomm/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=wellcomm';
- alter system set log_archive_dest_2='SERVICE=wellcommb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=wellcommb';
- alter system set log_archive_dest_state_1=enable;
- alter system set log_archive_dest_state_2=enable;
- alter system set log_archive_max_processes=10;
#p#
5.克隆Primary數據庫
- shutdown immediate
- startup mount
- backup database;
創建standby的控制文件
- alter database create standby controlfile as '/u01/oracle/controlbak.ctl';
創建standby的參數文件并按standby主機的配置修改
- create pfile='/u01/oracle/initwellcommb.ora' from spfile;
6.在standby主機上恢復數據庫(rman方式);
將5步的文件拷備到對應位置(ftp)
startup mount pfile='';
修改參數文件
- db_name='ora10g1'
- db_unique_name='ora10g3'
- log_archive_config='DG_CONFIG=(wellcomm,wellcommb)'
- log_archive_dest_1='LOCATION=/u01/oracle/oradata/wellcommb/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=wellcommb'
- log_archive_dest_2='SERVICE=wellcomm LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=wellcomm'
- log_archive_dest_state_1=enable
- log_archive_dest_state_2=enable
- remote_login_passwordfile='EXCLUSIVE'
- log_archive_max_processes=10
- restore database;
7.監聽配置和tns服務配置
(1)primary 主機上配置
listener.ora文件內容如下：
- SID_LIST_LISTENER =
- (SID_LIST =
- (SID_DESC =
- (GLOBAL_DBNAME = wellcomm )
- (ORACLE_HOME = /u01/oracle/product/10.2.0/db_1)
- (SID_NAME = wellcomm )
- )
- )
- LISTENER =
- (DESCRIPTION_LIST =
- (DESCRIPTION =
- (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.111.131)(PORT = 1521))
- )
- )
tnsnames.ora文件內容如下：
- WELLCOMM =
- (DESCRIPTION =
- (ADDRESS = (PROTOCOL = TCP)(HOST = wangwang)(PORT = 1521))
- (CONNECT_DATA =
- (SERVER = DEDICATED)
- (SERVICE_NAME = wellcomm)
- )
- )
- WELLCOMMB =
- (DESCRIPTION =
- (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.111.132)(PORT = 1521))
- (CONNECT_DATA =
- (SERVER = DEDICATED)
- (SERVICE_NAME = wellcommb)
- )
- )
#p#
(2)在standby主機上配置
listener.ora文件內容如下：
- SID_LIST_LISTENER =
- (SID_LIST =
- (SID_DESC =
- (GLOBAL_DBNAME = wellcommb)
- (ORACLE_HOME = /u01/oracle/product/10.2.0/db_1)
- (SID_NAME = wellcommb)
- )
- )
- LISTENER =
- (DESCRIPTION_LIST =
- (DESCRIPTION =
- (ADDRESS = (PROTOCOL = TCP)(HOST = wangkang)(PORT = 1521))
- )
- )
tnsnames.ora文件內容如下：
- WELLCOMM =
- (DESCRIPTION =
- (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.111.131)(PORT = 1521))
- (CONNECT_DATA =
- (SERVICE = DEDICATED)
- (SERVICE_NAME = wellcomm)
- )
- )
- WELLCOMMB =
- (DESCRIPTION =
- (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.111.132)(PORT = 1521))
- (CONNECT_DATA =
- (SERVER = DEDICATED )
- (SERVICE_NAME = wellcommb)
- )
- )
重啟監聽
lsnrctl stop
lsnrctl start
8.在standby主機上啟動應用redo
alter database recover managed standby database disconnect from session;
(取消：alter database recover managed standby database cancel;)
9.確認從Primary到Standby的Redo傳輸及應用
(1)在Primary主機上執行日志文件切換(***多次)
alter system switch logfile;
(2)查詢Primary的歸檔日志
select sequence#,applied,first_time,next_time from v$archived_log order by sequence#;
(3)查詢Standby的歸檔日志及其應用
select sequence#,applied,first_time,next_time from v$archived_log order by sequence#;
注意applied字段顯示YES則表明該歸檔日志已被standby數據庫應用了。
【編輯推薦】
