oracle 11g dataguard 使用dgbroker管理主备切换以及快速failover方法讲解
概述：dataguard的管理可以是sqlplus、dgbroker和em等工具进行管理，本文配置dg broker来管理dg的switch over 和 fast failover；
实验：
主备库开启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使得更加的方便；