删除环境中的数据库和代理配置

原创 CiciLee 2022-08-27

236

在今天的文章中，我将介绍 Data Guard Broker-删除环境中的数据库和代理配置。

我们可能想在代理环境中删除一个、几个或整个代理配置。操作步骤如下。

1.在删除之前，我们分别查询Configuration和Environment中的数据库。

DGMGRL> show configuration

Configuration - Broker _Configuration

  Protection Mode: MaxPerformance
  Databases:
    primary - Primary database
    standby - Physical standby database
    logical - Logical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS


DGMGRL> show database primary

Database - primary

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

Database Status:
SUCCESS


DGMGRL> show database standby

Database - standby

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 0 seconds ago)
  Apply Lag:       0 seconds (computed 0 seconds ago)
  Apply Rate:      640.00 KByte/s
  Real Time Query: ON
  Instance(s):
    primary1 (apply instance)
    primary2

Database Status:
SUCCESS


DGMGRL> show database logical

Database - logical

  Role:            LOGICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 0 seconds ago)
  Apply Lag:       0 seconds (computed 0 seconds ago)
  Apply Rate:      0 Byte/s
  Instance(s):
    primary1
    primary2 (apply instance)

Database Status:
SUCCESS
复制

2.删除配置后，我们做一些检查以了解系统将如何工作。

我们查询 DG_BROKER_START 参数的状态。

[Primary-1] SQL> show parameter dg_broker_start

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     TRUE

复制

我们质疑 DMON 进程的存在。

[oracle@primary1 ~]$ ps -ef | grep ora_dmon | grep -v grep
oracle   14329     1  0 Jan14 ?        00:00:06 ora_dmon_primary1
复制

我们查询配置文件是否存在。

ASMCMD> pwd
+data/primary/BROKER_CONF_FILE
ASMCMD> ls
dr1primary.dat

ASMCMD> pwd
+data/standby/BROKER_CONF_FILE
ASMCMD> ls
dr1standby.dat

ASMCMD> pwd
+data/logical/BROKER_CONF_FILE
ASMCMD> ls
dr1logical.dat

ASMCMD> pwd
+fra/primary/BROKER_CONF_FILE
ASMCMD> ls
dr2primary.dat

ASMCMD> pwd
+fra/standby/BROKER_CONF_FILE
ASMCMD> ls
dr2standby.dat

ASMCMD> pwd
+fra/logical/BROKER_CONF_FILE
ASMCMD> ls
dr2logical.dat
复制

我们正在查询 LOG_ARCHIVE_DEST_n 参数的值。

[Primary-1] SQL> column name format a30
[Primary-1] SQL> column value format a94
[Primary-1] SQL> set linesize 9000
[Primary-1] SQL> select name, value from v$parameter where lower(name) like '%log_archive_dest%' and lower(name) not like '%log_archive_dest_state%' and value is not null;

NAME                           VALUE
------------------------------ ----------------------------------------------------------------------------------------------
log_archive_dest_1             LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary
log_archive_dest_2             service="standby", LGWR ASYNC  delay=0 optional compression=disable max_failure=0 max_
                               connections=1 reopen=300 db_unique_name="standby" net_timeout=30, valid_for=(all_logfiles,prim
                               ary_role)

log_archive_dest_3             service="logical", LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_
                               connections=1 reopen=300 db_unique_name="logical" net_timeout=30, valid_for=(all_logfiles,prim
                               ary_role)

复制

[Physical-1] SQL> set linesize 9000
[Physical-1] SQL> column name format a30
[Physical-1] SQL> column value format a94
[Physical-1] SQL> select name, value from v$parameter where lower(name) like '%log_archive_dest%' and lower(name) not like '%log_archive_dest_state%' and value is not null;

NAME                           VALUE
------------------------------ ----------------------------------------------------------------------------------------------
log_archive_dest_1             LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby
复制

[Logical-1] SQL> column name format a30
[Logical-1] SQL> column value format a94
[Logical-1] SQL> set linesize 9000
[Logical-1] SQL> select name, value from v$parameter where lower(name) like '%log_archive_dest%' and lower(name) not like '%log_archive_dest_state%' and value is not null;

NAME                           VALUE
------------------------------ ----------------------------------------------------------------------------------------------
log_archive_dest_1             LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=logical

复制

我们正在查询 LOG_ARCHIVE_CONFIG 参数的值。

[Primary-1] SQL> show parameter log_archive_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string      dg_config=(primary,standby,logical)

复制

[Physical-1] SQL> show parameter log_archive_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string      dg_config=(standby,primary,logical)
复制

[Logical-1] SQL> show parameter log_archive_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string      dg_config=(logical,primary,standby)
复制

我们正在质疑主数据库和备用数据库的 SYNC 状态。

[Primary-1] SQL> select max(sequence#),thread# from v$archived_log group by thread#;

MAX(SEQUENCE#)    THREAD#
-------------- ----------
           555          1
           313          2
复制

[Physical-1] SQL> select max(sequence#),thread# from v$archived_log group by thread#;

MAX(SEQUENCE#)    THREAD#
-------------- ----------
           555          1
           313          2
复制

[Logical-1] SQL> SELECT max(sequence#),thread#,applied FROM dba_logstdby_log group by thread#, applied;

MAX(SEQUENCE#)    THREAD# APPLIED
-------------- ---------- --------
           555          1 YES
           313          2 YES
复制

我们从逻辑备用数据库代理配置中删除。

DGMGRL> remove database "logical"
Removed database "logical" from the configuration

DGMGRL> show configuration

Configuration - Broker _Configuration

  Protection Mode: MaxPerformance
  Databases:
    primary - Primary database
    standby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS
复制

我们正在查询 LOG_ARCHIVE_CONFIG 参数的值。

[Primary-1] SQL> show parameter log_archive_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string      dg_config=(primary,standby)

复制

[Physical-1] SQL> show parameter log_archive_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string      dg_config=(standby,primary)

复制

[Logical-1] SQL> show parameter log_archive_config

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

复制

我们正在查询 LOG_ARCHIVE_DEST_n 参数的值。

[Primary-1] SQL> column name format a30
[Primary-1] SQL> column value format a94
[Primary-1] SQL> set linesize 9000
[Primary-1] SQL> select name, value from v$parameter where lower(name) like '%log_archive_dest%' and lower(name) not like '%log_archive_dest_state%' and value is not null;

NAME                           VALUE
------------------------------ ----------------------------------------------------------------------------------------------
log_archive_dest_1             LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary
log_archive_dest_2             service="standby", LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_
                               connections=1 reopen=300 db_unique_name="standby" net_timeout=30, valid_for=(all_logfiles,prim
                               ary_role)

复制

[Physical-1] SQL> column name format a30
[Physical-1] SQL> column value format a94
[Physical-1] SQL> set linesize 9000
[Physical-1] SQL> select name, value from v$parameter where lower(name) like '%log_archive_dest%' and lower(name) not like '%log_archive_dest_state%' and value is not null;

NAME                           VALUE
------------------------------ ----------------------------------------------------------------------------------------------
log_archive_dest_1             LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby
复制

[Logical-1] SQL> column name format a30
[Logical-1] SQL> column value format a94
[Logical-1] SQL> set linesize 9000
[Logical-1] SQL> select name, value from v$parameter where lower(name) like '%log_archive_dest%' and lower(name) not like '%log_archive_dest_state%' and value is not null;

NAME                           VALUE
------------------------------ ----------------------------------------------------------------------------------------------
log_archive_dest_1             LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=logical

复制

我们质疑 DMON 流程的价值。

[oracle@logical1 ~]$ ps -ef |grep ora_dmon |grep -v grep
oracle    4812     1  0 Jan14 ?        00:00:03 ora_dmon_primary1
复制

我们查询 DG_BROKER_START 参数的值。

[Logical-1] SQL> show parameter dg_broker_start

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     TRUE

复制

结论

REMOVE 数据库从 LOG_ARCHIVE_CONFIG 参数中删除。

为 REMOVE 数据库提供重做传输的 LOG_ARCHIVE_DEST_n 参数中的值被删除。

DG_BROKER_START 参数保持为 TRUE。

DMON 进程继续运行。

REMOVE 数据库中的配置文件继续存在于相关目录中。

我们使用物理备用数据库 PRESERVE DESTINATIONS 语法从代理配置中删除。

DGMGRL> remove database "standby" preserve destinations;
Removed database "standby" from the configuration

复制

我们查询代理配置。

DGMGRL> show configuration

Configuration - Broker _Configuration

  Protection Mode: MaxPerformance
  Databases:
    primary - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

复制

我们正在查询 LOG_ARCHIVE_DEST_n 参数的值。

[Primary-1] SQL> column name format a30
[Primary-1] SQL> column value format a94
[Primary-1] SQL> set linesize 9000
[Primary-1] SQL> select name, value from v$parameter where lower(name) like '%log_archive_dest%' and lower(name) not like '%log_archive_dest_state%' and value is not null;

NAME                           VALUE
------------------------------ ----------------------------------------------------------------------------------------------
log_archive_dest_1             LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary
log_archive_dest_2             service="standby", LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_
                               connections=1 reopen=300 db_unique_name="standby" net_timeout=30, valid_for=(all_logfiles,prim
                               ary_role)

复制

我们正在查询 LOG_ARCHIVE_CONFIG 参数的值。

[Primary-1] SQL> show parameter log_archive_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string      dg_config=(primary,standby)
复制

[Physical-1] SQL> show parameter log_archive_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string      dg_config=(standby,primary)

复制

结论

REMOVE 数据库不会从 LOG_ARCHIVE_CONFIG 参数中删除。

为 REMOVE 数据库提供重做传输的 LOG_ARCHIVE_DEST_n 参数中的值不会被删除。

DG_BROKER_START 参数保持为 TRUE。

DMON 进程继续运行。

REMOVE 数据库中的配置文件继续存在于相关目录中。

具有来自步骤 1 和步骤 2 的所有值的代理配置是 REMOVE 而没有 PRESERVE DESTINATIONS 系统税。

DGMGRL> remove configuration
Removed configuration
DGMGRL> show configuration
ORA-16532: Data Guard broker configuration does not exist

复制

DGMGRL 无法确定配置详细信息

我们查询 LOG_ARCHIVE_DEST_n 参数的值。

[Primary-1] SQL> column name format a30
[Primary-1] SQL> column value format a94
[Primary-1] SQL> set linesize 9000
[Primary-1] SQL> select name, value from v$parameter where lower(name) like '%log_archive_dest%' and lower(name) not like '%log_archive_dest_state%' and value is not null;
NAME                           VALUE
------------------------------ ----------------------------------------------------------------------------------------------
log_archive_dest_1             LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary

复制

查询LOG_ARCHIVE_CONFIG 参数的值。

[Primary-1] SQL> show parameter log_archive_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string      nodg_config

复制

我们查询 DMON 进程的状态。

[oracle@primary1 ~]$ ps -ef | grep ora_dmon |grep -v grep
oracle   29544     1  0 21:02 ?        00:00:00 ora_dmon_primary1

复制

我们查询 DG_BROKER_START 参数的值。

[Primary-1] SQL> show parameter dg_broker

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      +data/PRIMARY/BROKER_CONF_FILE/dr1primary.dat
dg_broker_config_file2               string      +FRA/PRIMARY/BROKER_CONF_FILE/dr2primary.dat
dg_broker_start                      boolean     TRUE

复制

质疑 Broker 配置文件是否在相关位置。

[grid@primary1 ~]$ asmcmd
ASMCMD> ls
DATA/
FRA/
OCR_VOTE/
ASMCMD> cd +data
ASMCMD> ls
PRIMARY/
ASMCMD> cd primary
ASMCMD> ls
BROKER_CONF_FILE/
CHANGETRACKING/
CONTROLFILE/
DATAFILE/
DATAGUARDCONFIG/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
spfileprimary.ora
ASMCMD> cd BROKER_CONF_FILE
ASMCMD> ls
dr1primary.dat

复制

结论

可以看出LOG_ARCHIVE_CONFIG参数中没有data guard conf。

提供重做传输的 LOG_ARCHIVE_DEST_n 参数为空白。

DG_BROKER_START 参数保持为 TRUE。

DMON 进程继续运行。

REMOVE 数据库中的配置文件继续存在于相关目录中。

BROKER Configuration 为 REMOVE 后的操作。

我们将 DG_BROKER_START 参数设置为 FALSE。

[Primary-1] SQL> alter system set dg_broker_start=FALSE scope=both sid='*';

System altered.

[Primary-1] SQL> show parameter dg_broker

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      +data/PRIMARY/BROKER_CONF_FILE
                                                 /dr1primary.dat
dg_broker_config_file2               string      +FRA/PRIMARY/BROKER_CONF_FILE/
                                                 dr2primary.dat
dg_broker_start                      boolean     FALSE

复制

配置文件将从各自的目录中删除。

原文标题：Removing Database And Broker Configuration In Environment
原文作者：Onur ARDAHANLI
原文地址：https://dbtut.com/index.php/2022/05/26/removing-database-and-broker-configuration-in-environment/

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

删除环境中的数据库和代理配置

评论

相关阅读