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

程序员文章站 2023-12-12 09:26:34

概述：dataguard的管理可以是sqlplus、dgbroker和em等工具进行管理，本文配置dg broker来管理dg的switch over 和 fast 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

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

------------------------------------

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

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

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

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使得更加的方便；