Oracle DG Broker做切换Switchover

2026-01-27 09:59 潇湘隐者阅读(0) 评论(0) 收藏举报

使用Oracle DG Broker做Switchover是非常简单的一件事情,但是流程必须规范,事前做足检查工作.避免没有检查到位,做Switchover时出现异常.

切换前检查

DGMGRL>show configuration;

DGMGRL>show database <primary_db_name>;

DGMGRL>show database <standby_db_name>;

DGMGRL>show database verbose <primary_db_name>;

DGMGRL>show database verbose <standby_db_name>;

DGMGRL>show database <db_name> logxptstatus;

DGMGRL>show database <db_name> 'inconsistentlogxptprops';

注意: 用具体的主库名和备库名替换上面的<primary_db_name>和<standby_db_name>或<db_name>

主要检查下面这些内容:

DG中所有数据库都处于正常状态.无任何错误或警告信息。
主数据库上已配置备用重做日志文件。
主数据库与备用数据库的状态分别为日志传输开启（TRANSPORT-ON）和日志应用开启（APPLY-ON）。

DGMGRL>  validate database gsp;        

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Flashback Database Status:
    gsp:  Off

  Managed by Clusterware:
    gsp:  NO             
    Validating static connect identifier for the primary database gsp...
    The static connect identifier allows for a connection to database "gsp".

DGMGRL> validate database gspro

  Database Role:     Physical standby database
  Primary Database:  gsp

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:
    gsp  :  Off
    gspro:  Off

  Managed by Clusterware:
    gsp  :  NO             
    gspro:  NO             
    Validating static connect identifier for the primary database gsp...
    The static connect identifier allows for a connection to database "gsp".

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status       
              (gsp)                   (gspro)                              
    1         7                       3                       Insufficient SRLs

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status       
              (gspro)                 (gsp)                                
    1         7                       3                       Insufficient SRLs

DGMGRL>

主库信息:

Ready for Switchover: Yes 表示可以准备切换(Switchover)

备库信息:

Ready for Switchover: Yes 表示切换操作已准备就绪

如果需要进一步诊断或troubleshoot,可以使用下面命令查看更多详细信息

validate database verbose gsp;
validate database verbose gspro;

例子:

DGMGRL> validate database verbose gsp;

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Flashback Database Status:
    gsp:  On

  Capacity Information:
    Database  Instances        Threads        
    gsp       1                1              

  Managed by Clusterware:
    gsp:  NO             
    Validating static connect identifier for the primary database gsp...
    The static connect identifier allows for a connection to database "gsp".

  Temporary Tablespace File Information:
    gsp TEMP Files:  3

  Data file Online Move in Progress:
    gsp:  No

  Transport-Related Information:
    Transport On:  Yes

  Log Files Cleared:
    gsp Standby Redo Log Files:  Cleared

DGMGRL> validate database verbose gspro;

  Database Role:     Physical standby database
  Primary Database:  gsp

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:
    gsp  :  On
    gspro:  Off

  Capacity Information:
    Database  Instances        Threads        
    gsp       1                1              
    gspro     1                1              

  Managed by Clusterware:
    gsp  :  NO             
    gspro:  NO             
    Validating static connect identifier for the primary database gsp...
    The static connect identifier allows for a connection to database "gsp".

  Temporary Tablespace File Information:
    gsp TEMP Files:    3
    gspro TEMP Files:  3

  Data file Online Move in Progress:
    gsp:    No
    gspro:  No

  Standby Apply-Related Information:
    Apply State:      Running
    Apply Lag:        0 seconds (computed 1 second ago)
    Apply Delay:      0 minutes

  Transport-Related Information:
    Transport On:  Yes
    Gap Status:    No Gap
    Transport Lag:  0 seconds (computed 1 second ago)
    Transport Status:  Success

  Log Files Cleared:
    gsp Standby Redo Log Files:    Cleared
    gspro Online Redo Log Files:   Cleared
    gspro Standby Redo Log Files:  Available

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status       
              (gsp)                   (gspro)                              
    1         3                       4                       Sufficient SRLs

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status       
              (gspro)                 (gsp)                                
    1         3                       4                       Sufficient SRLs

  Current Configuration Log File Sizes:
    Thread #   Smallest Online Redo      Smallest Standby Redo    
               Log File Size             Log File Size            
               (gsp)                     (gspro)                  
    1          200 MBytes                200 MBytes               

  Future Configuration Log File Sizes:
    Thread #   Smallest Online Redo      Smallest Standby Redo    
               Log File Size             Log File Size            
               (gspro)                   (gsp)                    
    1          200 MBytes                200 MBytes               

  Apply-Related Property Settings:
    Property                        gsp Value                gspro Value
    DelayMins                       0                        0
    ApplyParallel                   AUTO                     AUTO
    ApplyInstances                  0                        0

  Transport-Related Property Settings:
    Property                        gsp Value                gspro Value
    LogShipping                     ON                       ON
    LogXptMode                      ASYNC                    ASYNC
    Dependency                      <empty>                  <empty>
    DelayMins                       0                        0
    Binding                         optional                 OPTIONAL
    MaxFailure                      0                        0
    ReopenSecs                      300                      300
    NetTimeout                      30                       30
    RedoCompression                 DISABLE                  DISABLE

DGMGRL>

切换步骤

主库切换(Switchover)

switchover to <standby_db>

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

注意: 在切换过程,最好用下面命令实时观察主库&备库的错误日志与dg broker的日志输出信息.

主库:

$ tail -60f drcgsp.log
$ tail -60f alert_gsp.log

备库:

$ tail -60f alert_gsp.log 
$ tail -60f drcgsp.log

注意事项:

如果切换的目标是物理standby，那么Brocker 在切换过程中会关闭主库。

如果切换环境是MAX_PROTECTION模式并且主库只有一个备库，Brocker在切换过程中将同时关闭主备库；如果主库有多个备库，那么Brocker仅关闭新主库。

检查验证

DGMGRL> show configuration;

Configuration - dg_gsp

  Protection Mode: MaxPerformance
  Members:
  gspro - Primary database
    gsp   - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 67 seconds ago)

DGMGRL>

疑问问题

DG Broker做switchover会改变DG的保护模式吗?

DG Broker will not downgrade the PROTECTION mode during the process of Switchover.
Configure suitable REDO transport mode on current standby that becomes Primary after switchover.
Configure SRL (Standby Redo Logs) and local archiving destination on current Primary.
Broker switchover command verifies the availability of SRL on current Primary Database and suitable Redo transport method on current Standby.

DG Broker在主备切换(Switchover)过程中不会降级数据库的保护模式。
在当前备库(切换后的主库)上配置合适的重做日志传输模式(redo transport mode)
在当前主库上配置备库重做日志(SRL, Standby Redo Logs)与本地归档目标位置
Broker 主备切换命令会校验当前主库上备用重做日志（SRL）的可用性，以及当前备库上适配的重做日志传输方式。

配置了DG Broker后,还能使用SQL手工切换吗? 手工切换会破坏DG Broker吗?

测试验证如下:

SQL> ALTER DATABASE SWITCHOVER TO gspro VERIFY;
ALTER DATABASE SWITCHOVER TO gspro VERIFY
*
ERROR at line 1:
ORA-16475: succeeded with warnings, check alert log for more details

检查告警日志如下所示:

SWITCHOVER VERIFY: Send VERIFY request to switchover target GSPRO
SWITCHOVER VERIFY WARNING: switchover target has no standby database defined in LOG_ARCHIVE_DEST_n parameter. If the switchover target is converted to a primary database, the new primary database will not be protected.
ORA-16475 signalled during: ALTER DATABASE SWITCHOVER TO gspro VERIFY...

检查备库的log_archive_dest_2,发现备库上参数log_archive_dest_1/log_archive_dest_2都为空

SQL> show parameter 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
SQL>

此时需要修改相关参数才能满足DG切换条件,但是可能会破坏DG Broker的数据. 所以结论是不建议手工做切换,这样会破坏DB Broker的元数据.

alter system set log_archive_dest_1='location=/db19clog/gspro_arc_ valid_for=(all_logfiles,all_roles) db_unique_name=gspro' scope=both;
alter system set log_archive_dest_2='service=gsp, SYNC AFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name=gsp net_timeout=30 valid_for=(online_logfile,primary_role)' scope=both;


SQL> alter system set log_archive_dest_1='location=/db19clog/gspro_arc_ valid_for=(all_logfiles,all_roles) db_unique_name=gspro' scope=both;

System altered.


SQL> alter system set log_archive_dest_2='service=gsp, SYNC AFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name=gsp net_timeout=30 valid_for=(online_logfile,primary_role)' scope=both;

System altered.

SQL>

DG Broker切换可以在任意数据库保护模式下?

可以.

参考资料

12c Dataguard Switchover Best Practices using DGMGRL(Dataguard Broker Command Prompt)
https://docs.oracle.com/en/database/oracle/oracle-database/19/dgbkr/using-data-guard-broker-to-manage-switchovers-failovers.html#GUID-89BF9FC5-1E3F-4C0B-90CB-AF4B39B5245E

刷新页面返回顶部

潇湘隐者