DataGuard+Broker配置方案
今天为客户部署Oracle Dataguard双机主从热备环境，由于客户需求比较严格，需要实现系统故障自动转移，我方最终选择了，DG+Broker的配置方案，这种高可用架构，可以实现数据库层面的主动切换，当主库服务器宕机或实例挂起后，备库可自动升级为PRIMARY主库角色，结合应用程序判断数据库的角色来决定连接哪个主库节点，这样系统可以实现自动化故障处理，从而提高客户的业务稳定需求。
一、 DataGuard Broker体系架构图
二、 Dataguard配置步骤
l 基础信息
操作系统：windows2008
数据库：11.2.0.4
配置信息：
|
说明 |
系统 |
IP地址 |
数据库版本 |
sid |
DB_UNIQUE_NAME |
Service_name |
|
主库 |
10.72.0.114 |
orcl |
orcl |
orcl |
||
|
备库 |
Win2008 |
10.73.64.253 |
Oracle 11.2.0.4 |
orcl |
orclbak |
orclbak |
l 准备工作
按照要求创建相应的主数据库和备用数据库，并删除备用数据库的数据文件、控制文件，两个数据库的所有路径需要一致；
1.搭建数据库
主库的操作：
1.确认主库参数
SQL> select
name,open_mode,database_role,log_mode,force_logging from v$database;
NAME OPEN_MODE DATABASE_ROLE LOG_MODE
FOR
---------
-------------------- ---------------- ------------ ---
ORCL READ WRITE PRIMARY ARCHIVELOG YES
SQL> show
parameter name
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
db_file_name_convert string
db_name string orcl
db_unique_name string orcl
global_names boolean FALSE
instance_name string orcl
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string orcl
2.设置数据库的归档模式
SQL> archive
log list;
数据库日志模式
存档模式
自动存档
启用
存档终点
D:\app\Administrator\fast_recovery_area\orcl
最早的联机日志序列
833
下一个存档日志序列
835
当前日志序列
835
归档模式的设置(如已是归档模式可省略)：
1、shutdown abort；
2、alter database mount;
3、alter database archive log；
4、alter database open；
3.开启force logging
SQL> alter
database force logging;
SQL> select
name,DB_UNIQUE_NAME,database_role,log_mode,force_logging from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE LOG_MODE
FOR
---------
------------------------------ ---------------- ------------ ---
ORCL orcl PRIMARY ARCHIVELOG YES
4.主库配置监听
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
ORA-Main-114)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY =
EXTPROC1521))
)
)
SID_LIST_LISTENER
=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME =
D:\app\Administrator\product\11.2.0\dbhome_1)
(SID_NAME = orcl)
(SERVICE_NAME = orcl)
)
)
ADR_BASE_LISTENER
= D:\app\Administrator
5.配置主库的TNSNAMES.ORA并和密码文件一起传到备库
# Generated by
Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
10.72.0.114)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
(UR=A)
)
)
ORCLBAK =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
10.73.64.253)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclbak)
(UR=A)
)
)
传输监听文件到备库：
6.修改主库pfile参数
ALTER SYSTEM SET
LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orclbak)';
ALTER SYSTEM SET
LOG_ARCHIVE_DEST_1=
'LOCATION=D:\app\Administrator\fast_recovery_area\orcl\ARCHIVELOG
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl';
ALTER SYSTEM SET
LOG_ARCHIVE_DEST_2='SERVICE=orclbak LGWR
ASYNC affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclbak' ;
ALTER SYSTEM SET
LOG_ARCHIVE_DEST_STATE_1='ENABLE';
ALTER SYSTEM SET
LOG_ARCHIVE_DEST_STATE_2='DEFER'; --这里先设置关闭
ALTER SYSTEM SET
STANDBY_FILE_MANAGEMENT='AUTO';
ALTER SYSTEM SET
FAL_SERVER=orclbak;
ALTER SYSTEM SET
FAL_CLIENT=orcl;
7.全备主库,并传输备份集到备库的相同位置
run
{
allocate channel c0 device type disk;
allocate channel c1 device type disk;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE
TYPE DISK TO 'e:\orabak\rman/%F';
backup
database format 'e:\orabak\ora11g_full_db_%d_%T_%u.bak';
BACKUP ARCHIVELOG ALL FORMAT
'e:\orabak\ora11g_arc_%s_%p_%t.bak';
}
8.修改备库参数并启动至nomount状态
DB_UNIQUE_NAME=orclbak
LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orclbak)'
LOG_ARCHIVE_DEST_1=
'LOCATION=D:\app\Administrator\fast_recovery_area\orcl\ARCHIVELOG
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orclbak'
LOG_ARCHIVE_DEST_2=
'SERVICE=orcl LGWR ASYNC affirm
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=orcl
FAL_CLIENT=orclbak
STANDBY_FILE_MANAGEMENT=AUTO
9.修改并启动备库监听
# listener.ora
Network Configuration File: D:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by
Oracle configuration tools.
SID_LIST_LISTENER
=
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME =
D:\app\Administrator\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS =
"EXTPROC_DLLS=ONLY:D:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
WIN-4GH7UKQEEOC)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY =
EXTPROC1521))
)
)
SID_LIST_LISTENER
=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME =
D:\app\Administrator\product\11.2.0\dbhome_1)
(SID_NAME = orcl)
)
)
ADR_BASE_LISTENER
= D:\app\Administrator
10.进行备库的恢复
C:\Users\Administrator>rman
target /
恢复管理器: Release 11.2.0.4.0 - Production on 星期五 2月 26 17:35:52 2016
Copyright (c)
1982, 2011, Oracle and/or its affiliates.
All rights reserved.
已连接到目标数据库: ORCL (DBID=1389423916)
RMAN> connect
auxiliary sys/******@orclbak
已连接到辅助数据库: ORCL (未装载)
RMAN> duplicate
target database for standby nofilenamecheck;
启动 Duplicate Db 于 26-2月 -16
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_AUX_DISK_1
通道 ORA_AUX_DISK_1: SID=2091 设备类型=DISK
内存脚本的内容:
{
restore clone standby controlfile;
}
正在执行内存脚本
启动 restore 于 26-2月 -16
使用通道 ORA_AUX_DISK_1
……恢复日志过程省略
完成 Duplicate Db 于 26-2月 -16
RMAN>
12.将主库的LOG_ARCHIVE_DEST_STATE_2设置为ENABLE
ALTER SYSTEM SET
LOG_ARCHIVE_DEST_STATE_2='ENABLE';
13.添加standby redo log --如本身主库已建立standby redo log，此步骤可省去
SQL> select
member from v$logfile;
#主库增加 STANDBY LOGFILE
ALTER DATABASE ADD
STANDBY LOGFILE group 4('D:\app\Administrator\oradata\standby_log\slog1.rdo')
SIZE 200M;
ALTER DATABASE ADD
STANDBY LOGFILE group 5('D:\app\Administrator\oradata\standby_log\slog2.rdo')
SIZE 200M;
ALTER DATABASE ADD
STANDBY LOGFILE group 6('D:\app\Administrator\oradata\standby_log\slog3.rdo')
SIZE 200M;
ALTER DATABASE ADD
STANDBY LOGFILE group 7('D:\app\Administrator\oradata\standby_log\slog4.rdo') SIZE
200M;
#备库增加STANDBY LOGFILE
SQL> select
member from v$logfile;
ALTER DATABASE ADD
STANDBY LOGFILE group 4('D:\app\Administrator\oradata\standby_log\slog1.rdo')
SIZE 200M;
ALTER DATABASE ADD
STANDBY LOGFILE group 5('D:\app\Administrator\oradata\standby_log\slog2.rdo')
SIZE 200M;
ALTER DATABASE ADD
STANDBY LOGFILE group 6('D:\app\Administrator\oradata\standby_log\slog3.rdo')
SIZE 200M;
ALTER DATABASE ADD
STANDBY LOGFILE group 7('D:\app\Administrator\oradata\standby_log\slog4.rdo')
SIZE 200M;
14.最后在备库开启active dataguard
SQL> ALTER
DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM
SESSION;
SQL> ALTER
DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER
DATABASE OPEN;
SQL> ALTER
DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM
SESSION;
至此，普通的dataguard热备同步配置完成，下面开始部署dg
broker故障转移自动切换。
三、 DG BROKER配置
1. 配置broker前提
主从数据库都需要打开flashback database
select flashback_on from v$database; 在主从都看 查看是否开启
shut immediate
startup mount
alter database flashback
on;
//开启闪回
主从数据库必须置为最大可用状态，如果不是也不要着急，一会用dgmgrl来切成最大可用。
select protection_mode,database_role,protection_level
from v$database
另外，主从数据库都要使用spfile：
show parameter spfile
还有，主从数据库都要启用broker:
dg_broker_start=true
alter system set dg_broker_start=true;
2. 开始配置broker
在主库上使用dgmgrl连接到数据库.创建配置.
[oracle@dg1 admin]$ dgmgrl
DGMGRL> connect sys/oracle
DGMGRL> create configuration 'orcl_broker'
as primary database is 'orcl' connect identifier is 'orcl'; Configuration
" orcl_broker
" created with primary database "orcl"
DGMGRL>
这里的参数要说明一下. orcl_broker是配置的名称,这里可以随便填.PRIMARY DATABASE IS ‘orcl′ ,这儿的orcl是指database的db_unique_name,而connect identifier is ‘orcl′这里的orcl是指tnsname.ora连接到主库的net service name.
小知识：删除配置用DGMGRL>
remove configuration;
3. 添加standby database到配置
还是在主库上
DGMGRL> add database 'orclbak' as
connect identifier is orclbak maintained as physical;
Database "orclbak" added
这里的参数要说明一下.add database
‘orclbak′ ,这儿的orclbak是指database的db_unique_name,而AS CONNECT IDENTIFIER IS orclbak 这里的orclbak是指tnsname.ora连接到standby database的net service name.
DGMGRL> show configuration
Configuration - orclpricfg
Protection Mode:
MaxAvailability
Databases:
orcl - Primary database
orclbak - Physical
standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
4. 修改DG broker 参数
还是在主库上执行：
DGMGRL> edit database 'orcl' set property
'logxptmode'='sync';
Property "logxptmode" updated
DGMGRL> edit database 'orclbak' set property
'logxptmode'='sync';
Property "logxptmode" updated
5. 启动快速故障转移:
DGMGRL> enable fast_start failover;
Enabled.
注意：两个数据库必须置为最大可用模式，如果不是，可用用如下命令来操作：
DGMGRL> edit configuration set protection mode as
MAXAvailability;
Succeeded.
再启动
DGMGRL> enable fast_start failover;
注意：有小伙伴在执行上面的命令后，报：
ORA-16569-Data Guard configuration
is not enabled
解决办法：执行enable
configuration; 后再执行 enable fast_start
failover;
DGMGRL> show configuration;
Configuration - orclpricfg
Protection Mode: MaxAvailability
Databases:
orcl - Primary database
Warning:
ORA-16819: fast-start failover observer not started
orclbak - (*) Physical standby database
Warning:
ORA-16819: fast-start failover observer not started
Fast-Start Failover: ENABLED
Configuration Status:
WARNING
DGMGRL>
DGMGRL> start observer;
Observer started
注：上面的报错说明没有开启broker监控，下面用start observer开启一下，就好了,另外需要注意，windows的broker
CMD监控窗口需一直打开（别关闭）。
6. 最后确认一下DG BROKER的同步状态：
以上信息表示启用了快速故障转移功能，主备同步状态成功，至此，
dataguard+broker配置完成！
发表评论 取消回复