Oracle DG-Broker主备高可用一键切换

操作系统: 

[oracle@node139 ~]$ uname -a

Linux node139 4.1.12-61.1.28.el6uek.x86_64 #2 SMP Thu Feb23 20:03:53 PST 2017 x86_64 x86_64 x86_64 GNU/Linux

[oracle@node139 ~]$ cat /etc/redhat-release

Red Hat Enterprise Linux Server release 6.9 (Santiago)

数据库版本:
Oracle:    Oracle 11.2.0.4.0


DataGuard 配置前题条件:
          两台服务器已配置完成一主一备的DG环境,在配置DG broker之前需要确保Dataguard配置正常且主库和备库均使用spfile.
          两台服务器的保护模式一定要一样

主库ORA11G_PRIMARY:

02:21:10SYS@ DB-Primary>select name,dbid,database_role,protection_mode fromv$database;

NAME               DBID DATABASE_ROLE    PROTECTION_MODE

--------- ---------- ------------------------------------

ORCL          1518610607PRIMARY              MAXIMUM AVAILABILITY

备库ORA11G_STANDBY:

02:21:18SYS@ ORA11G_STANDBY>select name,dbid,database_role,protection_mode fromv$database;

NAME               DBID DATABASE_ROLE    PROTECTION_MODE

--------- ---------- ---------------- --------------------

ORCL          1518610607PHYSICAL STANDBY MAXIMUM AVAILABILITY

主库开启flashback database:

02:22:53 SYS@ DB-Primary>select name,flashback_on fromv$database;

NAME      FLASHBACK_ON

--------- ------------------

DB-Primary  NO

02:23:12 SYS@ DB-Primary>show parameter recovery

NAME                                TYPE       VALUE

------------------------------------ -----------------------------------------

db_recovery_file_dest               string      /u01/app/oracle/fast_recovery_ area

db_recovery_file_dest_size          big integer 4122M

recovery_parallelism                integer     0

02:23:44 SYS@ DB-Primary>alter database flashback on;

Database altered.

Elapsed: 00:00:01.60

02:24:03 SYS@ DB-Primary>select name,flashback_on fromv$database;

NAME      FLASHBACK_ON

--------- ------------------

DB-Primary  YES

Elapsed: 00:00:00.00

备库也要开启flashbackdatabase:

SQL> select flashback_on fromV$database;

FLASHBACK_ON

------------------

NO

 

SQL> alter database recover managedstandby database cancel;

SQL> shutdown immediate

SQL> startup nomount

SQL> alter database mount standbydatabase;

SQL> alter database flashback on;

SQL> shutdown immediate

SQL> startup nomount

SQL> alter database mount standbydatabase;

Database altered.

SQL> alter database recover managedstandby database using current logfile disconnect from session;

Database altered.

SQL> select flashback_on,name fromv$database;

FLASHBACK_ON          NAME

------------------ ---------

YES                  ORCL

 

DGBroker 配置:

·        1.主库设置

·        2.备库设置

·        3.创建DataGuard Broker配置

·        4.添加standby database到配置

·        5.开启配置

·        6.验证配置和switch over

DG配置环境:

                      

 

Database   Name

 

 

DB-Primary

 

 

DB-Standby

 

 

Database   Unqie Name

 

 

ORA11G_PRIMARY

 

 

ORA11G_STANDBY

 

 

Net   Service Name

 

 

ORA11G_PRIMARY

 

 

ORA11G_STANDBY

 

 

Version

 

 

11.2.0.4   Linux for x86_64

 

 

11.2.0.4  Linux for x86_64

 

1.主库设置

DB_BROKER_CONFIG_FILEn参数用于指定DataGuard配置文件的路径,
DG_BROKER_START参数设置实例启动的时候是否自动启动Broken.

SQL>alter system setdg_broker_config_file1='/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/dr1ORA11G_PRIMARY.dat' scope=both sid='*';

System altered.

SQL>alter system setdg_broker_config_file2='/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/dr2ORA11G_PRIMARY.dat'scope=both sid='*';

System altered.

SQL>alter system set DG_BROKER_START=TRUE scope=both sid='*';

Systemaltered.

设置完上面的参数后,我们还需要修改监听listener.ora文件.我们必须添加一个静态注册的GLOBAL_DBNAME为db_unique_name_DGMGRL.db_domain,这个GLOBAL_DBNAME会在DGMGRL重启数据库的时候用到.通过DGMGRL重启数据库时DMON进程会先将数据库关闭,然后DGMGRL在通过静态监听中的service_name连接到数据库,发送启动的命令.如果不这么做的话,在做switchover的时候我们容易遇到TNS-12514错误

[oracle@node139 admin]$ cat listener.ora

# listener.ora Network Configuration File:/u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME=ora11g_primary_DGMGRL)

      (SERVICE_NAME = ora11g_primary)

      (ORACLE_HOME =/u01/app/oracle/product/11.2.0.4/dbhome_1)

      (SID_NAME = orcl)

     )

)

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST =node139)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY =EXTPROC1521))

    )

  )

 

ADR_BASE_LISTENER = /u01/app/oracle

这里需要说明的是GLOBAL_DBNAME=<db_unique_name>_DGMGRL,<db_domain>.
SERVICE_NAME=<db_unique_name>,<db_domain>.
SID_NAME=echo $ORACLE_SID.
ORACLE_HOME=echo $ORACLE_HOME

tnsnames配置:
主库

[oracle@node139 admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File:/u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

ORA11G_PRIMARY =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST =node139)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ora11g_primary)

    )

  )

 

 

ORA11G_STANDBY =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST =node140)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ora11g_standby)

    )

  )


2.备库设置

和主库设置一样,同样需要设置DB_BROKER_CONFIG_FILEn参数和DG_BROKER_START参数.还有静态监听.

SQL>alter system setdg_broker_config_file1='/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/dr1ORA11G_STANDBY.dat' scope=both sid='*';

Systemaltered.

SQL>alter system setdg_broker_config_file2='/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/dr2ORA11G_STANDBY.dat'scope=both sid='*';

Systemaltered.

SQL>alter system set DG_BROKER_START=TRUE scope=both sid='*';

Systemaltered.

备库监听器配置:

[oracle@node140 admin]$ cat listener.ora

# listener.ora Network Configuration File:/u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME= ora11g_standby_orcl)

      (SERVICE_NAME=ora11g_standby)

      (ORACLE_HOME =/u01/app/oracle/product/11.2.0.4/dbhome_1)

      (SID_NAME = orcl)

     )

    (SID_DESC =

      (GLOBAL_DBNAME=ora11g_standby_DGMGRL)

      (SERVICE_NAME=ora11g_standby)

      (ORACLE_HOME =/u01/app/oracle/product/11.2.0.4/dbhome_1)

      (SID_NAME = orcl)

     )

)

 

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST =node140)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY =EXTPROC1521))

    )

  )

 

ADR_BASE_LISTENER = /u01/app/oracle

备库tnsnames 配置:

[oracle@node140 admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File:/u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

 

ORA11G_PRIMARY =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST =node139)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ora11g_primary)

    )

  )

 

 

ORA11G_STANDBY =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST =node140)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ora11g_standby)

    )

  )

 

3.创建DataGuard Broker配置

在主库上使用dgmgrl连接到数据库.创建配置.
添加别名记录:可以使用上下键翻页:
[oracle@node139 ~]$ cat /home/oracle/.bash_profile 

alias dgmgrl=&apos;rlwrapdgmgrl&apos;

[oracle@dg1 admin]$ dgmgrl

DGMGRL for Linux: Version 11.2.0.1.0 - 64bit ProductionCopyright (c) 2000,2009, Oracle. All rights reserved.Welcome to DGMGRL, type "help" forinformation.

DGMGRL> connect sys/oracleConnected.

DGMGRL>create configuration 'ORA11G_PRIMARYcfg' as primary databaseis 'ORA11G_PRIMARY' connect identifier is 'ORA11G_PRIMARY';

Configuration"ORA11G_PRIMARYcfg" created with primary database"ORA11G_PRIMARY"

DGMGRL>

这里的参数要说明一下.ORA11G_PRIMARYcfg是配置的名称,这里可以随便填.
PRIMARY DATABASE IS ‘ORA11G_PRIMARY′ ,这儿的ORA11G_PRIMARY是指database的db_unique_name,
而connect identifier is ‘ORA11G_PRIMARY′这里的ORA11G_PRIMARY是指tnsname.ora连接到主库的net service name.

我们可以使用show confiruration查看配置信息.

DGMGRL>show configuration

Configuration- ORA11G_PRIMARYcfg

 ProtectionMode: MaxAvailability

 Databases:

  ORA11G_PRIMARY - Primary database

Fast-StartFailover: DISABLED

ConfigurationStatus:

DISABLED

 

4.添加standby database到配置

DGMGRL>add database 'ORA11G_STANDBY' as connect identifier is ORA11G_STANDBY maintained as physical;

Database "ORA11G_STANDBY" added

这里的参数要说明一下.add database ‘ORA11G_STANDBY′ ,这儿的ORA11G_STANDBY是指database的db_unique_name,
而AS CONNECT IDENTIFIER IS ORA11G_STANDBY 这里的ORA11G_STANDBY是指tnsname.ora连接到standby database的net service name.

DGMGRL>show configuration

Configuration- ORA11G_PRIMARYcfg

 ProtectionMode: MaxAvailability

 Databases:

  ORA11G_PRIMARY - Primary database

  ORA11G_STANDBY - Physical standby database

Fast-StartFailover: DISABLED

ConfigurationStatus:

DISABLED

5.开启配置

DGMGRL> enable Configuration
DGMGRL> Enabled.

DGMGRL>show configuration;

Configuration- ORA11G_PRIMARYcfg

 ProtectionMode: MaxAvailability

 Databases:

  ORA11G_PRIMARY - Primary database

  ORA11G_STANDBY - Physical standby database

Fast-StartFailover: DISABLED

ConfigurationStatus:

SUCCESS

修改DG broker 参数:

编辑数据库属性

LogXptMode
默认情况下,Broker 将主数据库设置为使用异步日志传输。针对最高可用性环境时,需要将此设置更改为同步。

NetTimeout
NetTimeout 属性指定在考虑连接丢失前 LGWR 将阻塞对同步模式中来自备用数据库的确认的等待秒数(对应于log_archive_dest_n 的 NET_TIMEOUT 选项)。默认值为 30 秒。使用最高可用性模式时,考虑降低该值以减少备用数据库不可用时的提交阻塞时间。选择一个足够高的值,避免由间歇性网络问题引起的假性断开。本示例使用 10 秒钟。

ObserverConnectIdentifier(11g 及更高版本)
Oracle数据库 11g 将 ObserverConnectIdentifier 数据库属性添加到 Broker 配置,使您可以为观察器指定一个连接标识符,用于监视主数据库和故障切换目标。默认情况下,观察器和 Data Guard 使用相同的连接标识符在主数据库和备用数据库间进行重做传输和信息交换(Oracle 数据库 11g 中为DGConnectIdentifier,Oracle 数据库 10g 中为InitialConnectIdentifier)。ObserverConnectIdentifier 使您可以指定观察器使用不同的连接标识符。例如,您可以用此参数使观察器使用与客户端应用程序相同的连接标识符监视数据库。

在本指南中,我们将在保留其他属性的默认值,但您应熟悉所有 Broker 配置和数据库属性。Data Guard Broker 文档(10g 和 11g)第 9 章中包含了每个属性的描述。其中一些属性已经在这两个版本中有所改动。

注:Broker 的许多数据库属性与数据库 spfile 参数相对应。Broker 在角色转换、数据库启动/关闭以及其他事件期间,通过执行相应的 ALTER SYSTEM 命令来维护这些参数。如果这些参数在 Broker 外部进行了修改,将出现警告。要查看特定参数,使用“show database ... StatusReport”命令。

editdatabase db1_a set property LogXptMode=&apos;SYNC&apos;;
edit database db1_a set property NetTimeout=10;
edit database db1_b set property NetTimeout=10;

其中最大性能要对应异步
MaxPerformance/async
只有最大可用才能对应同步
MaxAvailability/sync

在这里一下要以SYNC的方式来配置,如果通过ASYNC不能配置成功

DGMGRL> edit configuration set protection modeas  maxavailability;
 #配置主备库使用的保护模式

DGMGRL> edit database 'ORA11G_PRIMARY' set property' logxptmode'='sync';

Property"logxptmode" updated

DGMGRL> edit database 'ORA11G_STANDBY' set property 'logxptmode' ='sync';

Property"logxptmode" updated

DGMGRL>

DGMGRL> enable fast_start failover;

Enabled.

DGMGRL>show configuration;

Configuration- ORA11G_PRIMARYcfg

 ProtectionMode: MaxAvailability

 Databases:

  ORA11G_PRIMARY - Primary database

    Warning: ORA-16819: fast-start failover observer not started

  ORA11G_STANDBY - (*) Physical standby database

    Warning: ORA-16819: fast-start failover observer not started

Fast-StartFailover: ENABLED

ConfigurationStatus:

WARNING

DGMGRL>

DGMGRL> start observer;

Observerstarted

在备库上查看:

[oracle@shsrv~]$dgmgrl

DGMGRLfor Linux: Version 11.2.0.3.0 - 64bit Production

Copyright(c) 2000, 2009, Oracle. All rights reserved.

Welcometo DGMGRL, type "help" for information.

DGMGRL>show configuration;

notlogged on

DGMGRL>connect sys/oracle@ORA11G_PRIMARY

Connected.

DGMGRL>show configuration;

Configuration- ORA11G_PRIMARYcfg

 ProtectionMode: MaxAvailability

 Databases:

  ORA11G_PRIMARY - Primary database

  ORA11G_STANDBY - (*) Physical standby database

 

Fast-StartFailover: ENABLED

ConfigurationStatus:

SUCCESS

验证FFS:

主库:

SQL>col FS_FAILOVER_OBSERVER_HOST for a30

SQL> selectfs_failover_observer_present,fs_failover_observer_host,fs_failover_threshold from v$database;

 

FS_FAIL FS_FAILOVER_OBSERVER_HOST     FS_FAILOVER_THRESHOLD

------- ---------------------------------------------------

YES       node139                 30

备库:

SQL> col FS_FAILOVER_OBSERVER_HOST fora30

SQL> selectfs_failover_observer_present,fs_failover_observer_host,fs_failover_threshold from v$database;

 

FS_FAIL FS_FAILOVER_OBSERVER_HOST     FS_FAILOVER_THRESHOLD

------- ---------------------------------------------------

YES       node139                 30

 查看主库配置信息:

DGMGRL> show database verboseora11g_primary;
查看备库配置信息:

DGMGRL> show database verboseORA11G_STANDBY;


6.验证swictover

DGMGRL>switchover to &apos;ORA11G_STANDBY&apos;;

Performingswitchover NOW, please wait...

Newprimary database "ORA11G_STANDBY" is opening...

Operationrequires shutdown of instance "ORA11G_PRIMARY" on database

"ORA11G_PRIMARY"Shuttingdown instance "ORA11G_PRIMARY"..

.ORACLEinstance shut down.Operation requires startup of instance"ORA11G_PRIMARY" on database

"ORA11G_PRIMARY"Startinginstance "ORA11G_PRIMARY"...

ORACLEinstance started.

Databasemounted.Switchover succeeded,

newprimary is "ORA11G_STANDBY"

ORA11G_PRIMARY

SQL>select database_role,switchover_status from v$database;

DATABASE_ROLE   SWITCHOVER_STATUS

------------------------------------

PHYSICALSTANDBY NOT ALLOWED

ORA11G_STANDBY

SQL>select database_role,switchover_status from v$database;

DATABASE_ROLE   SWITCHOVER_STATUS

------------------------------------

PRIMARY         TO STANDBY

查看切换后的配置文件:

DGMGRL> show configuration;

Configuration - primarycfg

  Protection Mode: MaxAvailability

  Databases:

    ora11g_standby - Primary database

    ora11g_primary - (*) Physical standbydatabase

Fast-Start Failover: ENABLED

Configuration Status:

SUCCESS

7、failover 验证:

在主库上做shutdown abort的操作,备库会主动的切换为主库!

中止主数据库。

shutdown abort

观察器日志:

Initiating Fast-Start Failover to database

"ORA11G_PRIMARY"...Performing failover NOW,please wait...

Failover succeeded, new primary is"ORA11G_STANDBY"

通过登录到新主数据库上的 dgmgrl 查看 Broker 配置。您会看到之前的主数据库现在已禁用。

dgmgrl sys/oracle@ORA11G_STANDBY configuration

Configuration  Name:               

FSF  Enabled: YES

Protection Mode:     MaxAvailability

Databases:

              ORA11G_STANDBY - Primary database

              ORA11G_PRIMARY - Physical standby database (disabled)         -

Fast-Start Failover target

Fast-Start Failover: ENABLED

Current status for "FSF":

Warning: ORA-16608: one or more databases have warnings

查看测试数据

登录到新的主数据库并验证更改与之前主数据库一致。

select count(*) from x;

COUNT(*)

----------

68855

将之前中止的主数据库恢复为备用数据库

通过安装数据库启动恢复。注意,数据库此时不会打开。仅当观察器验证主数据库仍为主数据库后,主数据库才会打开。如果观察器发现该数据库不再是主数据库,会尝试将其恢复为故障切换的目标备用数据库。

恢复的第一步是将数据库闪回到备用数据库变为主数据库的 SCN 处(新主数据库上的v$database.standby_became_primary_scn)。如闪回数据库部分中所述,闪回数据库将分成两个阶段进行:恢复阶段和介质恢复阶段。在恢复阶段,闪回数据库使用闪回数据库日志中的前映像块将数据库恢复到standby_became_primary_scn 之前的一点。在介

分割线
打赏
YWSOS.COM 平台代运维解决方案
 评论
 发表评论
姓   名:

Powered by AKCMS