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

90 阅读 0 评论 60 点赞

我是靠谱客的博主包容板栗，最近开发中收集的这篇文章主要介绍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