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;
- Configurasyon Dosyaları
- Backgroun Processleri ve
- 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.
