DataGuard+Broker配置方案


前言

   今天为客户部署Oracle Dataguard双机主从热备环境,由于客户需求比较严格,需要实现系统故障自动转移,我方最终选择了,DG+Broker的配置方案,这种高可用架构,可以实现数据库层面的主动切换,当主库服务器宕机或实例挂起后,备库可自动升级为PRIMARY主库角色,结合应用程序判断数据库的角色来决定连接哪个主库节点,这样系统可以实现自动化故障处理,从而提高客户的业务稳定需求。


一、    DataGuard Broker体系架构图

 

 

二、    Dataguard配置步骤

l  基础信息

操作系统:windows2008

数据库:11.2.0.4

 

配置信息:

说明

系统

IP地址

数据库版本

sid

DB_UNIQUE_NAME

Service_name

主库

Win2008

10.72.0.114

Oracle 11.2.0.4

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

 

归档模式的设置(如已是归档模式可省略)

1shutdown abort

2alter database mount;

3alter database archive log

4alter 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是指databasedb_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是指databasedb_unique_name,AS CONNECT IDENTIFIER IS orclbak 这里的orclbak是指tnsname.ora连接到standby databasenet 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开启一下,就好了,另外需要注意,windowsbroker CMD监控窗口需一直打开(别关闭)。

 

6.  最后确认一下DG BROKER的同步状态:

以上信息表示启用了快速故障转移功能,主备同步状态成功,至此, dataguard+broker配置完成!