Oracle Data Guard Broker

Oracle Data Guard Broker, Data Guard için yönetim aracıdır diye basit bir tanım yapabiliriz. Bize bir çok kolaylık sağlayan bir araçtır. Üç ana başlık altına işlevlerini söylersek; Data Guard yönetimini Otomatikleştirmeyi, Data Guard için Merkezi yönetim kolaylığı ve Monitoring işlemlerini yapmamızı, hem de çok kolay bir şekilde yapmamızı sağlayan bir araçtır. En önemli artı yanları otomatik failover özelliği sağlaması ve tek bir komutla switch (primary <—>standby) işlemini gerçekleştirebilmesi gibi kolaylıklar sağlamasıdır.

Data Guard Broker Temel Bileşenleri;

  1. Configurasyon Dosyaları
  2. Backgroun Processleri ve
  3. DGMRL Command Line Interface

Genellikle Configurasyon Primary Databse tarafında yapılır

Bir Data Guard Broker DMON Background process lerin haberleşmesi ile tek bir merkezden, 30 a kadar standby database yönetilip, monitor edilebilir.

Mimarinin kabaca şekline bakarsak

Data Guard Broker başlatıldığına hemen arkada bir background DMON processi oluşturur.Configuration dosyaları bibary dosyalardır ve broker yapılandırılmasındaki her bir vertiabanında 2 adet bulunur, data gurad ile ilgili tüm bilgileri içeren önemli dosyalardır, DMON üzerinden bu yapılandırma bilgileri sürekli check edilir. Bu configurasyon dosyaları nerede bulunur gelede dbs dizini altındadır bakalım;

SQL> show parameter dg

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
adg_account_info_tracking            string      LOCAL
adg_redirect_dml                     boolean     FALSE
cell_offloadgroup_name               string
dg_broker_config_file1               string      /u01/app/oracle/product/19.3.0
                                                 .0/db/dbs/dr1racdb.dat
dg_broker_config_file2               string      /u01/app/oracle/product/19.3.0
                                                 .0/db/dbs/dr2racdb.dat

dg_broker_start                      boolean     FALSE
inmemory_adg_enabled                 boolean     TRUE

Standby tarafındakine de bir bakalım

SQL> show parameter dg

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
adg_account_info_tracking            string      LOCAL
adg_redirect_dml                     boolean     FALSE
cell_offloadgroup_name               string
dg_broker_config_file1               string      /u01/app/oracle/product/19.3.0
                                                 .0/db/dbs/dr1RACDBSTB.dat
dg_broker_config_file2               string      /u01/app/oracle/product/19.3.0
                                                 .0/db/dbs/dr2RACDBSTB.dat

dg_broker_start                      boolean     FALSE
inmemory_adg_enabled                 boolean     TRUE

Broker ile ya Cloud Control Enterprise Manager aracılığı ile yada DGMGRL CLI (Command Line Interface) aracı ile haberleşerek yönetim ve monitoring işlerini yapabiliyoruz. Broker yapılandırmasak dahi DGMGRL mevcuttur bakalım. DGMGRL e bağlanıp bir help diyelim neler gelecek karşımıza bir inceleyelim.

[[email protected] ~]$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Jan 20 10:52:33 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> help

The following commands are available:

@              Execute DGMGRL script file
!              Host operating system command
/              Repeat the last command
--             Comment to be ignored by DGMGRL
add            Adds a member to the broker configuration
connect        Connects to an Oracle database instance
convert        Converts a database from one type to another
create         Creates a broker configuration
disable        Disables a configuration, a member, or fast-start failover
edit           Edits a configuration or a member
enable         Enables a configuration, a member, or fast-start failover
exit           Exits the program
export         Export Data Guard Broker configuration to a file.
failover       Changes a standby database to be the primary database
help           Displays description and syntax for a command
host           Host operating system command
import         Import Data Guard Broker configuration from a file.
migrate        Migrate a pluggable database from one configuration to another.
quit           Exits the program
reinstate      Changes a database marked for reinstatement into a viable standby
rem            Comment to be ignored by DGMGRL
remove         Removes a configuration or a member
set            Set a DGMGRLI CLI property to a specified value
show           Displays information about a configuration or a member
shutdown       Shuts down a currently running Oracle database instance
spool          store input and output of DGMGRL CLI in a file
sql            Executes a SQL statement
start          Starts the fast-start failover observer
startup        Starts an Oracle database instance
stop           Stops the fast-start failover observer
switchover     Switches roles between a primary and standby database
validate       Performs an exhaustive set of validations for a member

Use "help <command>" to see syntax for individual commands

Peki buradaki komutlar ile ilgili bize yardımcı olacak mı? Ona da bir bakalım

DGMGRL> help switchover

Switches roles between a primary and standby database

Syntax:

  SWITCHOVER TO <standby database name> [WAIT [<timeout in seconds>]];

Evet komutların nasıl kullanılacağını da gösteriyor bunlar bizim ileride çok işimize yarayacak :))

Şu ana kadar herhangi bir konfigürasyon yapmadık, sadece broker ile ilgili bilgiler verdik bundan sonra Broker çalıştırma adımlarına başlayalım o halde;

primary ve standby tüm veritabanlarımızda aşağıdaki parametreyi true olarak set edelim

SQL> alter system set dg_broker_start=true scope=both sid='*';


System altered.

Broker a bağlanalım

[[email protected] ~]$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Jan 20 13:37:38 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect /
Connected to "racdb"
Connected as SYSDG.
DGMGRL>

Standby tarafına primary taraftan bağlanalım

DGMGRL> connect [email protected]
Password:
Connected to "RACDBSTB"
Connected as SYSDBA.
DGMGRL>

Broker a bağlanıyoruz fakat broker configure edilmedi henüz bakalım

[[email protected] ~]$ dgmgrl /
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Jan 20 13:39:32 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected to "racdb"
Connected as SYSDG.
DGMGRL> show configuration
ORA-16532: Oracle Data Guard broker configuration does not exist

Configuration details cannot be determined by DGMGRL

Evet configurasyona başlayalım; Öncelikle syntaxına bakalım sonra uygulayalım;

DGMGRL> help create

Creates a broker configuration

Syntax:

  CREATE CONFIGURATION <configuration name> [AS]
    PRIMARY DATABASE IS <database name>
    CONNECT IDENTIFIER IS <connect identifier>;

DGMGRL> create configuration racdbconf as primary database is racdb connect identifier is racdb;

Configuration "racdbconf" created with primary database "racdb"

Şimdi show configuration diyelim bakalım;

DGMGRL> show configuration;


Configuration - racdbconf


  Protection Mode: MaxPerformance

  Members:
  racdb - Primary database


Fast-Start Failover:  Disabled


Configuration Status:
DISABLED

Şimdi standby veritabanını bu konfigürasyona ekleyelim, help add diyelim syntax a baklım ve işlemi yapalım

DGMGRL> help add

Adds a member to the broker configuration

Syntax:

  ADD { RECOVERY_APPLIANCE | DATABASE | FAR_SYNC } <object name>
    [AS CONNECT IDENTIFIER IS <connect identifier>];

DGMGRL> add database RACDBSTB;
Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set

Failed.

Bu hatayı aldıysak LOG_ARCHIVE_DEST_n parametresini tekrar set edelim, Öncelikle broker configurasyonunu silelim

DGMGRL> remove configuration;
Removed configuration

Primary tarafta 1 ve 2. nodda

SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      SERVICE=RACDBSTB LGWR ASYNC VA
                                                 LID_FOR=(ONLINE_LOGFILES,PRIMA
                                                 RY_ROLE) DB_UNIQUE_NAME=RACDBS
                                                 TB

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='' scope=both;


System altered.

SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string

Standby tarafında

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='' scope=both;

System altered.


SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string

Şimdi artık Data Guard Broker Konfigürasyonuna yeniden başlayalım;

DGMGRL> CREATE CONFIGURATION 'RACDBCONF' as primary database is 'racdb' connect identifier is racdb;

Configuration "RACDBCONF" created with primary database "racdb"
DGMGRL> show configuration;

Configuration - RACDBCONF

  Protection Mode: MaxPerformance
  Members:
  racdb - Primary database

Fast-Start Failover:  Disabled

Configuration Status:
DISABLED

DGMGRL> add database 'RACDBSTB' as CONNECT IDENTIFIER IS RACDBSTB MAINTAINED AS PHYSICAL;

Database "RACDBSTB" added


DGMGRL> show configuration

Configuration - RACDBCONF

  Protection Mode: MaxPerformance
  Members:
  racdb    - Primary database
    RACDBSTB - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
DISABLED

Şimdi Parametrelerimiz tekrar eski haline getirelim;

Primary tarafta bütün nodlarda

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(RACDB,RACDBSTB)' scope=both sid='*';

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=RACDBSTB LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RACDBSTB' scope=both sid='*';

Standby tarafında

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(RACDB,RACDBSTB)' scope=both;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=RACDB LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RACDB' scope=both;

System altered.

Şimdi artık devam edebiliriz.

[[email protected] ~]$ dgmgrl /
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Jan 20 14:55:29 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected to "racdb"
Connected as SYSDG.
DGMGRL> enable configuration
Enabled.


Şimdi standby db deki data guard parametrelerimize ve durumuna bir bakalım,

DGMGRL> show database verbose racdbstb


Database - RACDBSTB

  Role:               PHYSICAL STANDBY

  Intended State:     APPLY-ON

  Transport Lag:      0 seconds (computed 1 second ago)

  Apply Lag:          0 seconds (computed 1 second ago)

  Average Apply Rate: 2.00 KByte/s
  Active Apply Rate:  4.48 MByte/s
  Maximum Apply Rate: 7.86 MByte/s
  Real Time Query:    OFF

  Instance(s):
    racdb

  Properties:
    DGConnectIdentifier             = 'racdbstb'
    ObserverConnectIdentifier       = ''
    FastStartFailoverTarget         = ''
    PreferredObserverHosts          = ''
    LogShipping                     = 'ON'
    RedoRoutes                      = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyLagThreshold               = '30'
    TransportLagThreshold           = '30'
    TransportDisconnectedThreshold  = '30'
    ApplyParallel                   = 'AUTO'
    ApplyInstances                  = '0'
    StandbyFileManagement           = ''
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '0'
    LogArchiveMinSucceedDest        = '0'
    DataGuardSyncLatency            = '0'
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = ''
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    ArchiveLocation                 = ''
    AlternateLocation               = ''
    StandbyArchiveLocation          = ''
    StandbyAlternateLocation        = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    LogXptStatus                    = '(monitor)'
    SendQEntries                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'standbydb1.ttnet.com'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=standbydb1.ttnet.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=RACDBSTB_DGMGRL)(INSTANCE_NAME=racdb)(SERVER=DEDICATED)))'
    TopWaitEvents                   = '(monitor)'
    SidName                         = '(monitor)'

  Log file locations:
    Alert log               : /u01/app/oracle/diag/rdbms/racdbstb/racdb/trace/alert_racdb.log
    Data Guard Broker log   : /u01/app/oracle/diag/rdbms/racdbstb/racdb/trace/drcracdb.log


Database Status:
SUCCESS

Primary taraf için verbose edelim ve bakalım tüm data guard parametrelerini configuration file dan alarak bize suncak, bakalım ne var ne yok :))

DGMGRL> show database verbose racdb


Database - racdb

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON

  Instance(s):
    racdb1
    racdb2


  Properties:
    DGConnectIdentifier             = 'racdb'
    ObserverConnectIdentifier       = ''
    FastStartFailoverTarget         = ''
    PreferredObserverHosts          = ''
    LogShipping                     = 'ON'
    RedoRoutes                      = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyLagThreshold               = '30'
    TransportLagThreshold           = '30'
    TransportDisconnectedThreshold  = '30'
    ApplyParallel                   = 'AUTO'
    ApplyInstances                  = '0'
    StandbyFileManagement           = ''
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '0'
    LogArchiveMinSucceedDest        = '0'
    DataGuardSyncLatency            = '0'
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = ''
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    ArchiveLocation                 = ''
    AlternateLocation               = ''
    StandbyArchiveLocation          = ''
    StandbyAlternateLocation        = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    LogXptStatus                    = '(monitor)'
    SendQEntries                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName(*)
    StaticConnectIdentifier(*)
    TopWaitEvents(*)
    SidName(*)
    (*) - Please check specific instance for the property value

  Log file locations(*):
    (*) - Check specific instance for log file locations.

Database Status:
SUCCESS


Bu yazı oldukça uzun oldu o yüzden, Data Guard Broker ile ilgili diğer özellikler switch ower vb. başka bir yazıda değineceğim. Umarım faydalı olmuştur.