资讯专栏INFORMATION COLUMN

上云采购季！| 2核2G4M爆款云服务器低至59元/年，更有多台、长期优惠，快来选购！

立即前往
首页/文章专栏/19C DG Broker配置和测试

19C DG Broker配置和测试

IT那活儿 / 3813人阅读
19C DG Broker配置和测试
一. DG Broker配置

1. DG当前测试环境配置

select name, database_role db_role, controlfile_type cf_type, open_mode, protection_mode, dataguard_broker dg_broker, guard_status, force_logging from v$database;

主库：

备库:


2. DG Broker主备库配置

DB_BROKER_CONFIG_FILEn参数用于指定DataGuard配置文件的路径，存在默认路径。

DG_BROKER_START参数设置实例启动的时候是否自动启动Broker，需要开启。

alter system set dg_broker_start=true;

数据库的archive_dest_n 参数会由DG BROKER自动进行管理，无需人工干预。


3. 创建DataGuard Broker配置

▼▼▼
[oracle@db1 /home/oracle]$ dgmgrl sys/oracle@prod
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu May 13 20:16:43 2021
Version 19.6.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected to "prod"
Connected as SYSDBA.
DGMGRL> create configuration dg_prod as primary database is prod connect identifier is prod;
Configuration "dg_prod" created with primary database "prod"
DGMGRL> add database pstdby as connect identifier is pstdby maintained as physical;
Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set

Failed.
配置备库报错，使用dg broker需要手动清除主备库的LOG_ARCHIVE_DEST_n参数配置。重启主库和备库即可添加。
DGMGRL> add database pstdby as connect identifier is pstdby maintained as physical;
Database "pstdby" added

DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration;

Configuration - dg_prod

  Protection Mode: MaxPerformance
  Members:
  pstdby - Primary database
    prod - Physical standby database 

Fast-Start Failover: Disabled

Configuration Status:
SUCCESS (status updated 46 second ago)

4. Listener和Tnsnames 配置

▼▼▼
##主库listener
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = prod)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = prod)
    )
   (SID_DESC =
      (GLOBAL_DBNAME = prod_DGMGRL)
      (ORACLE_HOME = /database/oracle/product/rdbms/19.3.0)
      (SID_NAME = prod)
    )
  )

###备库listener.ora配置
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = pstdby)
      (ORACLE_HOME = /database/oracle/product/rdbms/19.3.0)
      (SID_NAME = pstdby)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = pstdby_DGMGRL)
      (ORACLE_HOME = /database/oracle/product/rdbms/19.3.0)
      (SID_NAME = pstdby)
    )
  )


LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = pstdby)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )


###主备库tnsnames.ora
prod =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.199.71)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prod)
    )
  )
  
pstdby =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.199.72)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pstdby)
    )
  )

二. switchover及failover

1. switchover

DGMGRL> switchover to pstdby
Performing switchover NOW, please wait...
Operation requires a connection to database "pstdby"
Connecting ...
Connected to "pstdby"
Connected as SYSDBA.
New primary database "pstdby" is opening...
Operation requires start up of instance "prod" on database "prod"
Starting instance "prod"...
Connected to an idle instance.
ORACLE instance started.
Connected to "prod"
Database mounted.
Database opened.
Connected to "prod"
Switchover succeeded, new primary is "pstdby"
 DGMGRL> show configuration

Configuration - dg_prod

  Protection Mode: MaxPerformance
  Members:
  pstdby - Primary database
    prod - Physical standby database 

Fast-Start Failover: Disabled

Configuration Status:
SUCCESS (status updated 50 seconds ago)

2. failover

failover命令：
failover to database-name [immediate];

dgmgrl sys/oracle@pstdby
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu May 13 23:12:19 2021
Version 19.6.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected to "pstdby"
Connected as SYSDBA.
DGMGRL>failover to pstdby
Performing failover NOW, please wait...
Failover succeeded, new primary is "pstdby"
DGMGRL>show configuration

Configuration - dg_prod

  Protection Mode: MaxPerformance
  Members:
  pstdby - Primary database
    prod - Physical standby database (disabled)
      ORA-16661: the standby database needs to be reinstated

Fast-Start Failover: Disabled

Configuration Status:
SUCCESS   (status updated 14 seconds ago)

DGMGRL> reinstate database prod
Reinstating database "prod", please wait...
Oracle Clusterware is restarting database "prod" ...
Connected to "prod"
Connected to "prod"
Continuing to reinstate database "prod" ...
Reinstatement of database "prod" succeeded
DGMGRL>enable database prod
Enabled.
DGMGRL> show configuration

Configuration - dg_prod

  Protection Mode: MaxPerformance
  Members:
  pstdby - Primary database
    prod - Physical standby database 

Fast-Start Failover: Disabled

Configuration Status:
WARNING   (status updated 26 seconds ago)

在主库发生故障时需要手动failover，将主库切换到备库。此时原主库需要人为介入进行故障诊断和修复，修复后在DG Broker中执行reinstate database [ db_name] 命令可以重新将原主库转换为备库恢复为新的主备关系，若需要还原为初始环境执行switchover即可。

reinstate database [ db_name] 启用该功能修复，需要数据库开启闪回。

在原主库极端故障无法修复的情况下，需要重新建立DG环境。此时主库是启用了DG broker的，修复后的备库需要执行enable database [ db_name] 加入broker环境。


三. fast_start failover

1. fast_start failover相关配置

  • 保证主从数据库的闪回数据库功能以及强制归档都打开

SQL> select flashback_on,force_logging from v$database;

FLASHBACK_ON                FOR
------------------                        ---
YES                                      YES

  • 开启fast-start failover

DGMGRL>  enable fast_start failover;
Enabled in Potential Data Loss Mode.

  • 观察器（observer）设置

    可以使用后台进程的方式启动observer，配置如下：

1）查看当前的borker配置

▼▼▼
DGMGRL> show database verbose prod;

Database - prod

Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
prod

Properties:
DGConnectIdentifier = prod
ObserverConnectIdentifier =  
此处没有设置值，默认将使用DGConnectIdentifier的值

2）配置wallet

▼▼▼
mkstore -wrl /home/oracle/wallet/ -create  
提示输入wallet密码
mkstore -wrl /home/oracle/wallet/ -createCredential prod sys <password>
mkstore -wrl /home/oracle/wallet/ -createCredential pstdby sys <password>
在主库的sqlnet.ora中添加wallet条目
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME,EZCONNECT)
WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/home/oracle/wallet/)))
SQLNET.WALLET_OVERRIDE=TRUE
将主库的wallet和sqlnet.ora拷贝的备库的相同位置后，重新reload监听

3）创建后台进程observer

▼▼▼
dgmgrl sys/oracle@pstdby
DGMGRL> start observer ob1 in background logfile is /database/oracle/product/diag/rdbms/pstdby/prod/trace/observerlog connect identifier is pstdby;
Connected to "PSTDBY"
Submitted command "START OBSERVER" using connect identifier "pstdby"

dgmgrl sys/oracle@prod
DGMGRL> start observer ob2 in background logfile is /database/oracle/product/diag/rdbms/prod/prod/trace/observer.log connect identifier is prod;
Connected to "prod"
Submitted command "START OBSERVER" using connect identifier "prod"

此时主备节点都存在observer进程


2. 场景一、模拟主库宕机

  • 查看fast-start failover 状态

  • 验证自动切换，主库模拟异常关闭

    prod库执行shutdown abort;

    查看pstdby状态,自动切换为主库。

  • 恢复DG

▼▼▼
prod库启动后执行
DGMGRL> reinstate database prod
Reinstating database "prod", please wait...
Reinstatement of database "prod" succeeded
DGMGRL> enable database prod
Enabled.
DGMGRL> show configuration

Configuration - dg_prod

  Protection Mode: MaxPerformance
  Members:
  pstdby - Primary database
    prod - (*) Physical standby database 

Fast-Start Failover: Enabled in Potential Data Loss Mode

Configuration Status:
SUCCESS (status updated 7 seconds ago)

DGMGRL> show observer

Configuration - dg_prod

  Primary: pstdby
  Active Target:      prod

Observer "ob1" - Master

  Host Name: pstdby
  Last Ping to Primary: 1 second ago
  Last Ping to Target:          1 second ago

Observer "ob2" - Backup

  Host Name: prod
  Last Ping to Primary: 2 seconds ago
  Last Ping to Target:          0 seconds ago


3. 场景二、模拟网络故障

  • 当前borker状态

  • 禁用主库1521端口

[root@pstdby onlinelog]# firewall-cmd --zone=public --remove-port=1521/tcp --permanent
Warning: NOT_ENABLED: 1521:tcp
Success

[oracle@prod /home/oracle]$ tnsping pstdby

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 17-MAY-2021 13:52:01

Copyright (c) 1997, 2019, Oracle. All rights reserved.

Used parameter files:
/database/oracle/product/rdbms/19.3.0/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.199.72)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pstdby)))
TNS-12543: TNS:destination host unreachable
[oracle@pstdby /home/oracle]$ tnsping prod

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 17-MAY-2021 13:53:20

Copyright (c) 1997, 2019, Oracle. All rights reserved.

Used parameter files:
/database/oracle/product/rdbms/19.3.0/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.199.71)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prod)))
OK (20 msec)

  • 此时borker状态未发生变化

prod库的Observer.log中有告警提示

  • 打开pstdb的1521端口，禁用prod的1521端口

[oracle@pstdby /home/oracle]$ tnsping prod

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 17-MAY-2021 14:02:40

Copyright (c) 1997, 2019, Oracle. All rights reserved.

Used parameter files:
/database/oracle/product/rdbms/19.3.0/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.199.71)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prod)))
TNS-12543: TNS:destination host unreachable

[oracle@prod /home/oracle]$ tnsping pstdby

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 17-MAY-2021 14:02:09

Copyright (c) 1997, 2019, Oracle. All rights reserved.

Used parameter files:
/database/oracle/product/rdbms/19.3.0/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.199.72)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pstdby)))
OK (10 msec)

此时borker状态未发生变化

  • 同时禁用主备库的1521端口

[oracle@prod /home/oracle]$ tnsping pstdby

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 17-MAY-2021 14:10:31

Copyright (c) 1997, 2019, Oracle. All rights reserved.

Used parameter files:
/database/oracle/product/rdbms/19.3.0/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.199.72)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pstdby)))
TNS-12543: TNS:destination host unreachable
[oracle@pstdby /home/oracle]$ tnsping prod

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 17-MAY-2021 14:11:23

Copyright (c) 1997, 2019, Oracle. All rights reserved.

Used parameter files:
/database/oracle/product/rdbms/19.3.0/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.199.71)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prod)))
TNS-12543: TNS:destination host unreachable

DGMGRL> show configuration

Configuration - dg_prod

  Protection Mode: MaxPerformance
  Members:
  pstdby - Primary database
    prod - (*) Physical standby database 
      Error: ORA-16664: unable to receive the result from a member

Fast-Start Failover: Enabled in Potential Data Loss Mode

Configuration Status:
ERROR (status updated 55 seconds ago)

DGMGRL> show observer

Configuration - dg_prod

  Primary: pstdby
  Active Target:      prod

Observer "ob1" - Master

  Host Name: pstdby
  Last Ping to Primary: 1 second ago
  Last Ping to Target:          1 second ago

Observer "ob2" - Backup

  Host Name: prod
  Last Ping to Primary: 0 seconds ago
  Last Ping to Target:          1 second ago


结论：在网络故障时，borker可能会产生一些错误的提示，主备库不会发生角色切换。

在网络恢复时，错误提示会自动清除。


END


更多精彩干货分享

点击下方名片关注

IT那活儿

GPU云服务器 云服务器 Broker broker mqtt DG dg 修改服务器时间

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/129826.html

相关文章

  • DG备库读写测试方案

    DG备库读写测试方案 img{ display:block; margin:0 auto !important; width:100%; } body{ width:75%; margin:0...

    IT那活儿 评论0 收藏856

发表评论

登陆后可评论

0条评论

IT那活儿

男|高级讲师
我要私信

TA的文章

阅读更多
最新活动
阅读需要支付1元查看
<