oracle 11g dataguard 使用dgbroker管理主备切换以及快速failover

概述:dataguard的管理可以是sqlplus、dgbroker和EM等工具进行管理,本文配置dg broker来管理dg的switch over 和 fast failover;官方文档:https://docs.oracle.com/cd/E11882_01/server.112/e40771/cli.htm#DGBKR575


实验:

主备库开启dg broker 特性功能

SQL> show parameter dg
NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
cell_offloadgroup_name               string
dg_broker_config_file1               string                 /u01/app/oracle/product/11.2.0
                                                            /db_1/dbs/dr1CUBE.dat
dg_broker_config_file2               string                 /u01/app/oracle/product/11.2.0
                                                            /db_1/dbs/dr2CUBE.dat
dg_broker_start                      boolean                FALSE
SQL> alter system set dg_broker_start=true;
System altered.

dgbroker配置:

[oracle@cube ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.    

DGMGRL> connect sys
Password:
Connected.
DGMGRL> CREATE CONFIGURATION 'DRSolution' AS PRIMARY DATABASE IS 'CUBE' CONNECT IDENTIFIER IS CUBE;
Configuration "DRSolution" created with primary database "CUBE"
DGMGRL>  SHOW CONFIGURATION;

Configuration - DRSolution

  Protection Mode: MaxPerformance
  Databases:
    CUBE - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> ADD DATABASE 'JAKKI' as  CONNECT IDENTIFIER IS JAKKI;
Database "JAKKI" added
DGMGRL> SHOW CONFIGURATION;

Configuration - DRSolution

  Protection Mode: MaxPerformance
  Databases:
    CUBE  - Primary database
    JAKKI - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> SHOW DATABASE VERBOSE 'CUBE';

Database - CUBE

  Role:            PRIMARY
  Intended State:  OFFLINE
  Instance(s):
    CUBE


  Properties:
    DGConnectIdentifier             = 'cube'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '/u01/app/oracle/oradata/JAKKI/, /u01/app/oracle/oradata/CUBE/'
    LogFileNameConvert              = '/u01/app/oracle/oradata/JAKKI/, /u01/app/oracle/oradata/CUBE/'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    ApplyLagThreshold               = '0'
    TransportLagThreshold           = '0'
    TransportDisconnectedThreshold  = '30'
    SidName                         = 'CUBE'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=cube)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=CUBE_DGMGRL)(INSTANCE_NAME=CUBE)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '/u01/app/oracle/fast_recovery_area'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
DISABLED

DGMGRL> SHOW DATABASE VERBOSE  'JAKKI';

Database - JAKKI

  Role:            PHYSICAL STANDBY
  Intended State:  OFFLINE
  Transport Lag:   (unknown)
  Apply Lag:       (unknown)
  Apply Rate:      (unknown)
  Real Time Query: OFF
  Instance(s):
    JAKKI

  Properties:
    DGConnectIdentifier             = 'jakki'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '/u01/app/oracle/oradata/CUBE/, /u01/app/oracle/oradata/JAKKI/'
    LogFileNameConvert              = '/u01/app/oracle/oradata/CUBE/, /u01/app/oracle/oradata/JAKKI/'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    ApplyLagThreshold               = '0'
    TransportLagThreshold           = '0'
    TransportDisconnectedThreshold  = '30'
    SidName                         = 'JAKKI'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jakki)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=JAKKI_DGMGRL)(INSTANCE_NAME=JAKKI)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '/u01/app/oracle/fast_recovery_area'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'
Database Status:
DISABLED


DGMGRL> ENABLE CONFIGURATION;
Enabled.

DGMGRL> SHOW CONFIGURATION;

Configuration - DRSolution

  Protection Mode: MaxPerformance
  Databases:
    CUBE  - Primary database
    JAKKI - Physical standby database

Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

DGMGRL> enable database 'JAKKI';
Enabled.

DGMGRL> show database 'JAKKI';
Database - JAKKI

Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       0 seconds (computed 1 second ago)
  Apply Rate:      0 Byte/s
  Real Time Query: ON
  Instance(s):
    JAKKI
Database Status:
SUCCESS

DGMGRL> EDIT DATABASE 'JAKKI' SET PROPERTY 'LogXptMode'='SYNC';
Property "LogXptMode" updated
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
Succeeded.
DGMGRL> SHOW CONFIGURATION;
Configuration - DRSolution
  Protection Mode: MaxAvailability
  Databases:
    CUBE  - Primary database
    JAKKI - Physical standby database


DGMGRL> EDIT DATABASE 'CUBE' SET PROPERTY 'LogXptMode'='SYNC';
Property "LogXptMode" updated
DGMGRL> EDIT DATABASE 'JAKKI' SET PROPERTY 'LogXptMode'='SYNC';
Property "LogXptMode" updated
DGMGRL> EDIT DATABASE 'CUBE' SET PROPERTY FastStartFailoverTarget='JAKKI';
Property "faststartfailovertarget" updated
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
Succeeded.
Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

检查开启fast failover的前提条件:

ALTER SYSTEM SET UNDO_RETENTION=3600 SCOPE=SPFILE;
ALTER SYSTEM SET UNDO_MANAGEMENT='AUTO' SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
SHOW PARAMETER UNDO;
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320 SCOPE=BOTH;
ALTER DATABASE ARCHIVELOG;
ALTER SYSTEM SET db_recovery_file_dest_size=<size>;
ALTER SYSTEM SET db_recovery_file_dest=<directory-specification>;
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;
SQL> select FLASHBACK_ON from v$database;
FLASHBACK_ON
------------------------------------
NO

SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database flashback on
Database altered.
SQL> recover managed standby database using current logfile disconnect;

Media recovery complete.

fast failover 开启:

DGMGRL> START OBSERVER;
Observer started

使用nohup后台开启observer:

[oracle@cube ~]$ nohup dgmgrl sys/password@cube "start observer" &
[1] 2263
[oracle@cube ~]$ nohup: ignoring input and appending output to `nohup.out'
[1]+  Exit 255                nohup dgmgrl sys/password@cube "start observer"

[oracle@cube ~]$ dgmgrl 

DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys
Password:
Connected.
DGMGRL> ENABLE FAST_START FAILOVER;
Enabled.
DGMGRL> SHOW FAST_START FAILOVER;
Fast-Start Failover: ENABLED
  Threshold:          30 seconds
  Target:             JAKKI
  Observer:           cube
  Lag Limit:          30 seconds (not in use)
  Shutdown Primary:   TRUE
  Auto-reinstate:     TRUE
  Observer Reconnect: (none)
  Observer Override:  FALSE
Configurable Failover Conditions
  Health Conditions:
    Corrupted Controlfile          YES
    Corrupted Dictionary           YES
    Inaccessible Logfile            NO
    Stuck Archiver                  NO
    Datafile Offline               YES
  Oracle Error Conditions:
    (none)

switch over 演示:

DGMGRL> switchover to 'JAKKI';
Performing switchover NOW, please wait...
Operation requires a connection to instance "JAKKI" on database "JAKKI"
Connecting to instance "JAKKI"...
Connected.
New primary database "JAKKI" is opening...
Operation requires startup of instance "CUBE" on database "CUBE"
Starting instance "CUBE"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "JAKKI"
DGMGRL> show configuration;


Configuration - DRSolution


  Protection Mode: MaxAvailability
  Databases:
    JAKKI - Primary database
    CUBE  - Physical standby database


Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

failover 测试:

DGMGRL> connect sys/password@jakki;
Connected.
DGMGRL> failover to 'JAKKI';
Performing failover NOW, please wait...
Failover succeeded, new primary is "JAKKI"
DGMGRL> show configuration;
Configuration - DRSolution
  Protection Mode: MaxAvailability
  Databases:
    JAKKI - Primary database
      Warning: ORA-16817: unsynchronized fast-start failover configuration


    CUBE  - (*) Physical standby database (disabled)
      ORA-16661: the standby database needs to be reinstated
Fast-Start Failover: ENABLED
Configuration Status:
WARNING
原主库重新启动至mount状态:

[oracle@cube ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 4 15:56:39 2018
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area  839282688 bytes
Fixed Size                  2257880 bytes
Variable Size             545262632 bytes
Database Buffers          289406976 bytes
Redo Buffers                2355200 bytes
Database mounted.

重新将原主库添加至dg broker配置文件

[oracle@cube ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/windows@jakki
Connected.
DGMGRL> show configuration;
Configuration - DRSolution
  Protection Mode: MaxAvailability
  Databases:
    JAKKI - Primary database
      Warning: ORA-16817: unsynchronized fast-start failover configuration
    CUBE  - (*) Physical standby database (disabled)
      ORA-16661: the standby database needs to be reinstated
Fast-Start Failover: ENABLED
Configuration Status:
WARNING

DGMGRL> reinstate database 'CUBE';
Reinstating database "CUBE", please wait...
Reinstatement of database "CUBE" succeeded
DGMGRL>  show configuration;
Configuration - DRSolution
  Protection Mode: MaxAvailability
  Databases:
    JAKKI - Primary database
    CUBE  - (*) Physical standby database
Fast-Start Failover: ENABLED
Configuration Status:
SUCCESS
DGMGRL> show database 'JAKKI';
Database - JAKKI
  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    JAKKI
Database Status:
SUCCESS
DGMGRL> show database 'CUBE';
Database - CUBE
  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:      0 Byte/s
  Real Time Query: ON
  Instance(s):
    CUBE
Database Status:
SUCCESS

至此使用dg broker 管理dataguard配置,测试switchover 和failover 都已经完成;使用dg broker管理dataguard使得更加的方便;

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值