删除dgbroker

环境:

OS:Centos 7

DB:11.2.0.4

 

------------------------------------------------主库上执行---------------------------------------------

1.查看当前那个机器是主库(该命令在主从库上执行都可以)
DGMGRL> show configuration;

Configuration - slnngktest

Protection Mode: MaxPerformance
Databases:
slnngk - Primary database
slavea - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

2.删除配置
[oracle@dbmaster ~]$ dgmgtl
DGMGRL> connect sys/oracle
Connected.
DGMGRL> remove configuration;
Removed configuration

发现删除配置后,系统会自动修改如下参数(alert.log):

ALTER SYSTEM SET log_archive_dest_2='' SCOPE=BOTH;
Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_config='nodg_config' SCOPE=BOTH;
ALTER SYSTEM SWITCH ALL LOGFILE start (slnngk)

若是遇到如下报错,则需要禁用fast_start

DGMGRL> remove configuration
Error: ORA-16654: fast-start failover is enabled

Failed.

解决办法:
DGMGRL> disable fast_start failover
Disabled.

 

这个时候从库已经没有应用日志了
SQL> select process,status from v$managed_standby;

PROCESS STATUS
--------- ------------
ARCH CLOSING
ARCH CONNECTED
ARCH CONNECTED
ARCH CLOSING
RFS IDLE
RFS IDLE
RFS IDLE
MRP0 WAIT_FOR_LOG

8 rows selected.

 

3.修改dg_broker参数
SQL> connect / as sysdba
Connected.
SQL> alter system set dg_broker_start=false scope=both;

System altered.

 

4.主库上禁止归档到从库
查询如下,若有输出需要禁止
SQL> select dest_id, destination, status
2 from v$archive_dest
3 where target = 'STANDBY';

no rows selected

alter system set log_archive_dest_state_2=defer scope=both;

 

------------------------------------------------从库上执行---------------------------------------------

1.禁用dg_broker_start
SQL> alter system set dg_broker_start=false scope=both;

System altered.

 

 

-------------------主从上删除元数据文件-----------------
主库:
SQL> show parameter dg_broker

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /u01/app/oracle/product/11.2.0.4/db_1/dbs/dr1slnngk.dat
dg_broker_config_file2 string /u01/app/oracle/product/11.2.0.4/db_1/dbs/dr2slnngk.dat
dg_broker_start boolean FALSE

 

rm /u01/app/oracle/product/11.2.0.4/db_1/dbs/dr1slnngk.dat

rm /u01/app/oracle/product/11.2.0.4/db_1/dbs/dr2slnngk.dat

从库:
SQL> show parameter dg_broker;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /u01/app/oracle/product/11.2.0.4/db_1/dbs/dr1slavea.dat
dg_broker_config_file2 string /u01/app/oracle/product/11.2.0.4/db_1/dbs/dr2slavea.dat
dg_broker_start boolean FALSE

 

rm /u01/app/oracle/product/11.2.0.4/db_1/dbs/dr1slavea.dat

rm /u01/app/oracle/product/11.2.0.4/db_1/dbs/dr2slavea.dat

 

---------------检查主从库dg相应的参数-----------------
1.主库
SQL> show parameters log_archive_config;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string nodg_config
SQL>

SQL> show parameters log_archive_dest_2;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string

发现删除dgbroker配置后,主库的这两个参数都已经设置为空

 

2.从库
SQL> show parameters log_archive_config;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string dg_config=(slaveb)

SQL> show parameters log_archive_dest_2;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service=tnsslnngk async valid_
for=(online_logfiles,primary_r
ole) db_unique_name=slnngk
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_28 string
log_archive_dest_29 string
SQL>

发现从库修改了参数log_archive_config,原来从库该参数是dg_config=(slnngk,slaveb)

 

------------------------------------------------再次启用dgbroker后--------------------------------------------------------

后面再次启用dgbroker,dgmgrl添加了从库之后
DGMGRL> add database 'slavea' as connect identifier is 'tnsslavea';
DGMGRL> enable database 'slavea';

 

发现重新配置后,主数据库会自动修改log_archive_config和log_archive_dest_2这两个参数
Data Guard Broker executes SQL [alter system set log_archive_config='dg_config=(slnngk,slavea)']
ALTER SYSTEM SET log_archive_config='dg_config=(slnngk,slavea)' SCOPE=BOTH;
Sun Aug 22 22:36:28 2021
NSA2 started with pid=36, OS id=6301
Sun Aug 22 22:36:28 2021
Thread 1 advanced to log sequence 137 (LGWR switch)
Current log# 1 seq# 137 mem# 0: /u01/app/oracle/oradata/slnngk/redo01.log
Sun Aug 22 22:36:28 2021
Archived Log entry 332 added for thread 1 sequence 136 ID 0xd09347ca dest 1:
Sun Aug 22 22:36:29 2021
ARC0: Standby redo logfile selected for thread 1 sequence 136 for destination LOG_ARCHIVE_DEST_2
Sun Aug 22 22:36:31 2021
ALTER SYSTEM SET log_archive_dest_2='service="tnsslavea"','LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="slavea" net_timeout=30','valid_for=(all_logfiles,primary_role)' SCOPE=BOTH;
ALTER SYSTEM ARCHIVE LOG

从库的应用日志自动也启动了
SQL> select process,status from v$managed_standby;

PROCESS STATUS
--------- ------------
ARCH CLOSING
ARCH CLOSING
ARCH CONNECTED
ARCH CLOSING
RFS IDLE
RFS IDLE
RFS IDLE
RFS IDLE
MRP0 APPLYING_LOG

9 rows selected.