broker 处理办法

转载

mb5ffd7010f3b33 2021-08-20 11:01:00

文章标签 hive ide 2d oracle sql 文章分类 代码人生

环境信息：

DGMGRL>  SHOW CONFIGURATION VERBOSE

Configuration - dg_config

  Protection Mode: MaxPerformance
  Members:
  orcl    - Primary database
    orclfs  - Far sync instance 
      orcl2dg - Physical standby database 
      orcl0dg - Physical standby database 
    orcl2dg - Physical standby database (alternate of orclfs)
    orcl0dg - Physical standby database (alternate of orclfs)
    orclldg - Physical standby database 
    orcldg  - Physical standby database 

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    TraceLevel                      = 'USER'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'
    ExternalDestination1            = ''
    ExternalDestination2            = ''
    PrimaryLostWriteAction          = 'CONTINUE'
    ConfigurationWideServiceName    = 'orcl_CFG'

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS

设置环境变量和实例ID

## set oracle sid and dbsnmp passsowrd
export ORACLE_SID=
export dbsnmp=dbsnmppassword

诊断代码

##get info for dg 
vsids=$(echo "
set heading off
set echo off
set linesize 200
col DATABASE for a20
col CONNECT_IDENTIFIER for a30
select CONNECT_IDENTIFIER from  v\$dg_broker_config order by DATAGUARD_ROLE ;"|sqlplus  -s / as sysdba|grep -v selected)
for vsid in $vsids
do
    echo ""
    echo ""$vsid" *****---"
    echo "
set linesize 200 pagesize 1000
col STATUS for a20
col DEST_NAME for a20
col RECOVERY_MODE for a25
col PROTECTION_MODE for a25
col ERROR for a40
    select STATUS,DEST_NAME,RECOVERY_MODE,PROTECTION_MODE,ERROR from v\$archive_dest_status where STATUS<>'INACTIVE';"|sqlplus -s dbsnmp/dbsnmppassword
done;

输出显示：

prdg19/orclfs *****---

STATUS               DEST_NAME            RECOVERY_MODE             PROTECTION_MODE           ERROR
-------------------- -------------------- ------------------------- ------------------------- ----------------------------------------
VALID                LOG_ARCHIVE_DEST_1   MANAGED REAL TIME APPLY   MAXIMUM PERFORMANCE
VALID                STANDBY_ARCHIVE_DEST IDLE                      MAXIMUM PERFORMANCE


prdg19/orcl0dg *****---

STATUS               DEST_NAME            RECOVERY_MODE             PROTECTION_MODE           ERROR
-------------------- -------------------- ------------------------- ------------------------- ----------------------------------------
VALID                LOG_ARCHIVE_DEST_1   MANAGED REAL TIME APPLY   MAXIMUM PERFORMANCE
VALID                STANDBY_ARCHIVE_DEST IDLE                      MAXIMUM PERFORMANCE


prdg19/orcl2dg *****---

STATUS               DEST_NAME            RECOVERY_MODE             PROTECTION_MODE           ERROR
-------------------- -------------------- ------------------------- ------------------------- ----------------------------------------
VALID                LOG_ARCHIVE_DEST_1   MANAGED REAL TIME APPLY   MAXIMUM PERFORMANCE
VALID                STANDBY_ARCHIVE_DEST IDLE                      MAXIMUM PERFORMANCE


prdb19/orclldg *****---

STATUS               DEST_NAME            RECOVERY_MODE             PROTECTION_MODE           ERROR
-------------------- -------------------- ------------------------- ------------------------- ----------------------------------------
VALID                LOG_ARCHIVE_DEST_1   MANAGED REAL TIME APPLY   MAXIMUM PERFORMANCE
VALID                STANDBY_ARCHIVE_DEST IDLE                      MAXIMUM PERFORMANCE


prdg19/orcldg *****---

STATUS               DEST_NAME            RECOVERY_MODE             PROTECTION_MODE           ERROR
-------------------- -------------------- ------------------------- ------------------------- ----------------------------------------
VALID                LOG_ARCHIVE_DEST_1   MANAGED REAL TIME APPLY   MAXIMUM PERFORMANCE

re transport 代码

## re transport

vsids=$(echo "
set heading off
set echo off
set linesize 200
col DATABASE for a20
col CONNECT_IDENTIFIER for a30
select DATABASE from  v\$dg_broker_config where DATAGUARD_ROLE='PRIMARY' ;"|sqlplus  -s / as sysdba|grep -v selected)
echo ""
echo ""
echo "dgmgrl orders:------------"
for vsids in $vsids
do
   echo ""
   echo "dgmgrl /"
   echo "edit database "$vsids "set STATE='transport-off;'"
   echo "edit database "$vsids "set STATE='transport-on;'"
   echo "exit;"
done

输出显示：

[oracle@prdg19 rdbms]$ for vsids in $vsids
> do
>    echo ""
>    echo "dgmgrl /"
>    echo "edit database "$vsids "set STATE='transport-off;'"
>    echo "edit database "$vsids "set STATE='transport-on;'"
>    echo "exit;"
> done

dgmgrl /
edit database orcl set STATE='transport-off;'
edit database orcl set STATE='transport-on;'
exit;

re apply

## re apply
vsids=$(echo "
set heading off
set echo off
set linesize 200
col DATABASE for a20
col CONNECT_IDENTIFIER for a30
select DATABASE from  v\$dg_broker_config where DATAGUARD_ROLE='PHYSICAL STANDBY' ;"|sqlplus  -s / as sysdba|grep -v selected)
echo ""
echo ""
echo "dgmgrl orders:------------"
for vsids in $vsids
do
   echo ""
   echo "dgmgrl /"
   echo "edit database "$vsids "set STATE='apply-off';"
   echo "edit database "$vsids "set STATE='apply-on';"
   echo "exit;"
done

输出显示：

[oracle@prdg19 rdbms]$ for vsids in $vsids
> do
>    echo "dgmgrl /"
>    echo "edit database "$vsids "set STATE='apply-off';"
>    echo "edit database "$vsids "set STATE='apply-on';"
>    echo "exit;"
> done
dgmgrl /
edit database orcl2dg set STATE='apply-off';
edit database orcl2dg set STATE='apply-on';
exit;
dgmgrl /
edit database orclldg set STATE='apply-off';
edit database orclldg set STATE='apply-on';
exit;
dgmgrl /
edit database orcldg set STATE='apply-off';
edit database orcldg set STATE='apply-on';
exit;
dgmgrl /
edit database orcl0dg set STATE='apply-off';
edit database orcl0dg set STATE='apply-on';
exit;