|
一. 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; 主库：
备库:
DB_BROKER_CONFIG_FILEn参数用于指定DataGuard配置文件的路径，存在默认路径。 DG_BROKER_START参数设置实例启动的时候是否自动启动Broker，需要开启。 alter system set dg_broker_start=true; 数据库的archive_dest_n 参数会由DG BROKER自动进行管理，无需人工干预。
[oracle@db1 home/oracle]$ dgmgrl sys/oracle@prodDGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu May 13 20:16:43 2021Version 19.6.0.0.0Copyright (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 setFailed.配置备库报错，使用dg broker需要手动清除主备库的LOG_ARCHIVE_DEST_n参数配置。重启主库和备库即可添加。DGMGRL> add database 'pstdby' as connect identifier is 'pstdby' maintained as physical;Database "pstdby" addedDGMGRL> enable configurationEnabled.DGMGRL> show configuration;Configuration - dg_prod Protection Mode: MaxPerformance Members: pstdby - Primary database prod - Physical standby database Fast-Start Failover: DisabledConfiguration Status:SUCCESS (status updated 46 second ago) 4. Listener和Tnsnames 配置▼▼▼ ##主库listenerLISTENER = (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.oraprod = (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及failover1. switchoverDGMGRL> switchover to pstdbyPerforming 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 configurationConfiguration - dg_prod Protection Mode: MaxPerformance Members: pstdby - Primary database prod - Physical standby database Fast-Start Failover: DisabledConfiguration Status:SUCCESS (status updated 50 seconds ago) 2. failoverfailover命令：failover to database-name [immediate];dgmgrl sys/oracle@pstdbyDGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu May 13 23:12:19 2021Version 19.6.0.0.0Copyright (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 pstdbyPerforming failover NOW, please wait...Failover succeeded, new primary is "pstdby"DGMGRL>show configurationConfiguration - dg_prod Protection Mode: MaxPerformance Members: pstdby - Primary database prod - Physical standby database (disabled) ORA-16661: the standby database needs to be reinstatedFast-Start Failover: DisabledConfiguration Status:SUCCESS (status updated 14 seconds ago)DGMGRL> reinstate database prodReinstating 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" succeededDGMGRL>enable database prodEnabled.DGMGRL> show configurationConfiguration - dg_prod Protection Mode: MaxPerformance Members: pstdby - Primary database prod - Physical standby database Fast-Start Failover: DisabledConfiguration 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 failover1. 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 - prodRole: PRIMARYIntended State: TRANSPORT-ONInstance(s):prodProperties: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@pstdbyDGMGRL> 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@prodDGMGRL> 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 prodReinstating database "prod", please wait...Reinstatement of database "prod" succeededDGMGRL> enable database prodEnabled.DGMGRL> show configurationConfiguration - dg_prod Protection Mode: MaxPerformance Members: pstdby - Primary database prod - (*) Physical standby database Fast-Start Failover: Enabled in Potential Data Loss ModeConfiguration Status:SUCCESS (status updated 7 seconds ago)DGMGRL> show observerConfiguration - dg_prod Primary: pstdby Active Target: prodObserver "ob1" - Master Host Name: pstdby Last Ping to Primary: 1 second ago Last Ping to Target: 1 second agoObserver "ob2" - Backup Host Name: prod Last Ping to Primary: 2 seconds ago Last Ping to Target: 0 seconds ago 当前borker状态
禁用主库1521端口 [root@pstdby onlinelog]# firewall-cmd --zone=public --remove-port=1521/tcp --permanentWarning: NOT_ENABLED: 1521:tcpSuccess[oracle@prod /home/oracle]$ tnsping pstdbyTNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 17-MAY-2021 13:52:01Copyright (c) 1997, 2019, Oracle. All rights reserved.Used parameter files:/database/oracle/product/rdbms/19.3.0/network/admin/sqlnet.oraUsed TNSNAMES adapter to resolve the aliasAttempting 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 prodTNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 17-MAY-2021 13:53:20Copyright (c) 1997, 2019, Oracle. All rights reserved.Used parameter files:/database/oracle/product/rdbms/19.3.0/network/admin/sqlnet.oraUsed TNSNAMES adapter to resolve the aliasAttempting 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 prodTNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 17-MAY-2021 14:02:40Copyright (c) 1997, 2019, Oracle. All rights reserved.Used parameter files:/database/oracle/product/rdbms/19.3.0/network/admin/sqlnet.oraUsed TNSNAMES adapter to resolve the aliasAttempting 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 pstdbyTNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 17-MAY-2021 14:02:09Copyright (c) 1997, 2019, Oracle. All rights reserved.Used parameter files:/database/oracle/product/rdbms/19.3.0/network/admin/sqlnet.oraUsed TNSNAMES adapter to resolve the aliasAttempting 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 pstdbyTNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 17-MAY-2021 14:10:31Copyright (c) 1997, 2019, Oracle. All rights reserved.Used parameter files:/database/oracle/product/rdbms/19.3.0/network/admin/sqlnet.oraUsed TNSNAMES adapter to resolve the aliasAttempting 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 prodTNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 17-MAY-2021 14:11:23Copyright (c) 1997, 2019, Oracle. All rights reserved.Used parameter files:/database/oracle/product/rdbms/19.3.0/network/admin/sqlnet.oraUsed TNSNAMES adapter to resolve the aliasAttempting 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 unreachableDGMGRL> show configurationConfiguration - dg_prod Protection Mode: MaxPerformance Members: pstdby - Primary database prod - (*) Physical standby database Error: ORA-16664: unable to receive the result from a memberFast-Start Failover: Enabled in Potential Data Loss ModeConfiguration Status:ERROR (status updated 55 seconds ago)DGMGRL> show observerConfiguration - dg_prod Primary: pstdby Active Target: prodObserver "ob1" - Master Host Name: pstdby Last Ping to Primary: 1 second ago Last Ping to Target: 1 second agoObserver "ob2" - Backup Host Name: prod Last Ping to Primary: 0 seconds ago Last Ping to Target: 1 second ago结论：在网络故障时，borker可能会产生一些错误的提示，主备库不会发生角色切换。 在网络恢复时，错误提示会自动清除。
|
