欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

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

上一篇:

下一篇: