Data Guard Broker kurulumu, ayarlarmansi, monitorlenmesi ve silinmesi

1.AMAÇ VE KAPSAM

Oracle Data Guard broker yazılımı oracle database yazılımıyla beraber kuruluyor, programın bin dosyasına $ORACLE_HOME/bin/dgmgrl üstünden erişebilirisiniz. Dgmgrl sayesinde primary/standby database i üstünde gerçekleştirecğeiniz operasyonları kısa sürede yapabiliriz özellikle swichover operasyonu tek komutla gerçekleşmekte.

2. ÖNEMLİ

Broker kurulumunu yaptıktan sonra Data Guard parameterleri üstünde yapacağınız bütün değişiklikleri bu tool üstünden yapmanız gerekiyor (veya bu toolu kullanarak em console/grid console üstünden). Eğer SQL*Plus üstünden yapmak isterseniz ‘REMOVE CONFIGURATION PRESERVE DESTINATIONS’  komutunu brokerda çalıştırıp bütün broker ayarlarını silmeniz ve broker’a eklenmiş olan bütün databaselerin DG_BROKER_START parametresini FALSE çekmeniz gerekmekte.

2.UYGULAMA

Gerekli bilgiler

Primary Database SID: PRIM

Primary Database Domain/IP: ABSO

Standby Database SID: PRIM_SBY

Standby Database Domain/IP: CROO

2.1. KURULUM

2.1.1  Database’lerinizin listerı üstünde  GLOBAL_DBNAME = ORACLE_SID parametresi bulunmuyorsa lütfen ayarlayın.

Primary

LSNR_PRIM =
 (DESCRIPTION_LIST =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = IPC)(KEY = PRIM))
 (ADDRESS = (PROTOCOL = TCP)(HOST = ABSO)(PORT = 1521))
 )
 )
SID_LIST_LSNR_PRIM =
 (SID_LIST =
 (SID_DESC =
 (GLOBAL_DBNAME = PRIM)
 (SID_NAME = PRIM)
 (ORACLE_HOME = /oracle/11.2.0.3)
 )
 )

Standby

LSNR_PRIM_SBY =
 (DESCRIPTION_LIST =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = IPC)(KEY = PRIM_SBY))
 (ADDRESS = (PROTOCOL = TCP)(HOST = CROO)(PORT = 1521))
 )
 )
SID_LIST_LSNR_PRIM_SBY =
 (SID_LIST =
 (SID_DESC =
 (GLOBAL_DBNAME = PRIM_SBY)
 (SID_NAME = PRIM_SBY)
 (ORACLE_HOME = /oracle/11.2.0.3)
 )
 )

2.2. dgmgrl calıstırıp primary database baglaniyoruz. Baglanma esnasinda sys/şifre şeklinde baglanmanızı öneririm aksi taktirde switchover tarzı işlemlerde “ORA-01031: insufficient privileges”  hatasını alabilirsiniz.

ABSO:oracle(PRIM):/home/oracle > dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/****password****
Connected.

2.1.2.  Primary database broker ekliyoruz.

DGMGRL> CREATE CONFIGURATION 'PRIM_DGB' AS 
PRIMARY DATABASE IS 'PRIM'
CONNECT IDENTIFIER IS 'PRIM'
;> > > >
Configuration "PRIM_DGB" created with primary database "PRIM"

2.1.3.  Standby database ekliyoruz.

DGMGRL> ADD DATABASE 'PRIM_SBY' AS
CONNECT IDENTIFIER IS 'PRIM_SBY' ;
>
Database "PRIM_SBY" added

2.1.4. Ayarları kontrol edelim.

DGMGRL> SHOW CONFIGURATION
Configuration - PRIM_DGB
Protection Mode: MaxPerformance
 Databases:
 PRIM - Primary database
 PRIM_SBY - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
Monitoring Data Guard Broker

Standby ve Prımary veritabanları düzgün bir şekilde brokera eklendi.

2.2 DATA GUARD BROKER’IN AYARLANMASI
2.2.1  TNS network error (ORA-12514: TNS:listener does not currently know of service requested in connect descriptor) hatası almamak için StaticConnectIdentifier ORACLE_SID olarak ayarlayın.

DGMGRL> edit database "PRIM" set property staticConnectidentifier='PRIM';
Property "staticconnectidentifier" updated
DGMGRL> edit database "PRIM_SBY" set property staticConnectidentifier='PRIM_SBY';
Property "staticconnectidentifier" updated

2.2 2. Ayarları aktif edelim.

DGMGRL> ENABLE CONFIGURATION
Enabled.
DGMGRL> SHOW CONFIGURATION
Configuration - PRIM_DGB
Protection Mode: MaxPerformance
 Databases:
 PRIM - Primary database
 PRIM_SBY - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

2.2.3 Standby üstünde Redo ların işlenmesini durdurma (Primary  archive logları göndericek ama standby bunları işlemiyecek.)

DGMGRL> EDIT DATABASE 'PRIM_SBY' SET STATE='APPLY-OFF';
Succeeded.

Standby alert log

Wed Mar 26 08:53:17 2014
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Wed Mar 26 08:53:17 2014
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /oracle/diag/rdbms/PRIM_sby/PRIM_SBY/trace/PRIM_SBY_pr00_13497.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 2004941
Wed Mar 26 08:53:17 2014
MRP0: Background Media Recovery process shutdown (PRIM_SBY)
Managed Standby Recovery Canceled (PRIM_SBY)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

2.2.4 Standby üstünde Redo’ların işlenmesini aktif hale getirelim.

DGMGRL> EDIT DATABASE 'PRIM_SBY' SET STATE='APPLY-ON';
Succeeded.

Standby alert log

Wed Mar 26 08:56:59 2014
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE
Attempt to start background Managed Standby Recovery process (PRIM_SBY)
Wed Mar 26 08:56:59 2014
MRP0 started with pid=37, OS id=18469
MRP0: Background Managed Standby Recovery process started (PRIM_SBY)
 started logmerger process
Wed Mar 26 08:57:04 2014
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 24 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log /oradata/PRIM_SBY/fra/PRIM_SBY/archivelog/2014_03_26/o1_mf_1_174_9m51zdgd_.arc
Media Recovery Waiting for thread 1 sequence 175 (in transit)
Recovery of Online Redo Log: Thread 1 Group 12 Seq 175 Reading mem 0
 Mem# 0: /oradata/PRIM_SBY/redo_pri/sbyredo02pri.log
 Mem# 1: /oradata/PRIM_SBY/redo_sec/sbyredo02sec.log
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE

2.2.5 Primary database’in standby archivelog gönderme işlemini durdurur.

DGMGRL> edit database 'PRIM' set state = 'LOG-TRANSPORT-OFF';
Succeeded.

Primary alert log

Wed Mar 26 09:02:33 2014
ALTER SYSTEM SET log_archive_dest_state_2='RESET' SCOPE=BOTH;

2.2.6 Primary database’in standby archivelogları gönderme işlemini başlatır.

Primary alert log

DGMGRL> edit database 'PRIM' set state = 'ONLINE';
Succeeded.
Primary alert log
Wed Mar 26 09:10:13 2014
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
ALTER SYSTEM ARCHIVE LOG
Wed Mar 26 09:10:13 2014
Thread 1 cannot allocate new log, sequence 176
Private strand flush not complete
 Current log# 5 seq# 175 mem# 0: /oradata/PRIM/redo_pri/redo05pri.log
 Current log# 5 seq# 175 mem# 1: /oradata/PRIM/redo_sec/redo05sec.log
Thread 1 advanced to log sequence 176 (LGWR switch)
 Current log# 6 seq# 176 mem# 0: /oradata/PRIM/redo_pri/redo06pri.log
 Current log# 6 seq# 176 mem# 1: /oradata/PRIM/redo_sec/redo06sec.log
Wed Mar 26 09:10:13 2014
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
Archived Log entry 245 added for thread 1 sequence 175 ID 0x4bd2839 dest 1:
LNS: Standby redo logfile selected for thread 1 sequence 176 for destination LOG_ARCHIVE_DEST_2

2.3 DATA GUARD BROKER MONITORLENMESI
2.3.1.  “Show database” komutuyla replikasyonun durumunu görebiliriz, replikasyonla ilgili bir ORA-XXXXX hatası oldugunda aşağıda SUCCESS yazan kısımda göreceğiz.

DGMGRL> show database "PRIM"
Database - PRIM
 Role: PRIMARY
 Intended State: TRANSPORT-ON
 Instance(s):
 PRIM
Database Status:
SUCCESS
DGMGRL> show database "PRIM_SBY"
Database - PRIM_SBY
Role: PHYSICAL STANDBY
 Intended State: APPLY-ON
 Transport Lag: 0 seconds
 Apply Lag: 0 seconds
 Real Time Query: ON
 Instance(s):
 PRIM_SBY
Database Status:
SUCCESS

2.3.2. Replikasyonun durumu ve ayarları.

DGMGRL> SHOW DATABASE VERBOSE "PRIM"
Database - PRIM
Role: PRIMARY
 Intended State: TRANSPORT-ON
 Instance(s):
 PRIM
Properties:
 DGConnectIdentifier = 'PRIM'
 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 = '10'
 LogArchiveMinSucceedDest = '1'
 DbFileNameConvert = '/oradata/PRIM_SBY/, /oradata/PRIM/'
 LogFileNameConvert = '/oradata/PRIM_SBY/, /oradata/PRIM/'
 FastStartFailoverTarget = ''
 InconsistentProperties = '(monitor)'
 InconsistentLogXptProps = '(monitor)'
 SendQEntries = '(monitor)'
 LogXptStatus = '(monitor)'
 RecvQEntries = '(monitor)'
 SidName = 'PRIM'
 StaticConnectIdentifier = 'PRIM'
 StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
 AlternateLocation = ''
 LogArchiveTrace = '0'
 LogArchiveFormat = '%t_%s_%r.dbf'
 TopWaitEvents = '(monitor)'
Database Status:
SUCCESS
DGMGRL> SHOW DATABASE VERBOSE "PRIM_SBY"
Database - PRIM_SBY
Role: PHYSICAL STANDBY
 Intended State: APPLY-ON
 Transport Lag: 0 seconds
 Apply Lag: 0 seconds
 Real Time Query: ON
 Instance(s):
 PRIM_SBY
Properties:
 DGConnectIdentifier = 'PRIM_SBY'
 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 = '10'
 LogArchiveMinSucceedDest = '1'
 DbFileNameConvert = 'PRIM, PRIM_SBY'
 LogFileNameConvert = 'PRIM, PRIM_SBY'
 FastStartFailoverTarget = ''
 InconsistentProperties = '(monitor)'
 InconsistentLogXptProps = '(monitor)'
 SendQEntries = '(monitor)'
 LogXptStatus = '(monitor)'
 RecvQEntries = '(monitor)'
 SidName = 'PRIM_SBY'
 StaticConnectIdentifier = 'PRIM_SBY'
 StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
 AlternateLocation = ''
 LogArchiveTrace = '0'
 LogArchiveFormat = '%t_%s_%r.dbf'
 TopWaitEvents = '(monitor)'
Database Status:
SUCCESS

2.3.3. Database statusunde hata gördüğünüz zaman statusreport komutunu kullanabilirsiniz.

DGMGRL> show database "PRIM" statusreport
STATUS REPORT
 INSTANCE_NAME SEVERITY ERROR_TEXT
 PRIM ERROR ORA-16737: the redo transport service for 
standby "PRIM_SBY" has an error
 PRIM WARNING ORA-16714: the value of property 
LogArchiveTrace is inconsistent with the database setting
 PRIM WARNING ORA-16715: redo transport-related property 
ReopenSecs of standby database "PRIM_SBY " is inconsistent

2.3.4. Log transport hatalarında(ORA-16737),  LogXptStatus komutunu kullanabilirsiniz.

DGMGRL> SHOW DATABASE "PRIM" 'LogXptStatus'
LOG TRANSPORT STATUS
PRIMARY_INSTANCE_NAME STANDBY_DATABASE_NAME STATUS
 PRIM PRIM_SBY ORA-12541: TNS:no listener

2.3.5. Spfile ve broker configuration arasinda parametre farklılıklarında  InconsistentProperties komutunu kullanabilirsiniz.

DGMGRL> SHOW DATABASE "PRIM" 'InconsistentProperties'
INCONSISTENT PROPERTIES
INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE
PRIM LogArchiveTrace 255 0 0

2.4. DATABASELER ARASINDA SWITCHOVER YAPMA ISLEMI
2.4.1.  Hangi veritabanimiz primarymis kontrol edelim.

DGMGRL> SHOW CONFIGURATION
Configuration - PRIM_DGB
Protection Mode: MaxPerformance
 Databases:
 PRIM - Primary database
 PRIM_SBY - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
Monitoring Data Guard Broker

2.4.2. PRIM database’i primarymiş,  PRIM_SBY standbyına switch işlemini yapalım.

DGMGRL> switchover to "PRIM_SBY"
Performing switchover NOW, please wait...
New primary database "PRIM_SBY" is opening...
Operation requires shutdown of instance "PRIM" on database "PRIM"
Shutting down instance "PRIM"...
ORACLE instance shut down.
Operation requires startup of instance "PRIM" on database "PRIM"
Starting instance "PRIM"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "PRIM_SBY"

2.4.3. Şimdide PRIM_SBY primary tekrardan PRIM database’ini primary yapıp 2 veritabanında durumuna bakalım.

DGMGRL> switchover to "PRIM"
Performing switchover NOW, please wait...
New primary database "PRIM" is opening...
Operation requires shutdown of instance "PRIM_SBY" on database "PRIM_SBY"
Shutting down instance "PRIM_SBY"...
ORACLE instance shut down.
Operation requires startup of instance "PRIM_SBY" on database "PRIM_SBY"
Starting instance "PRIM_SBY"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "PRIM"
DGMGRL> show configuration
Configuration - PRIM_DGB
Protection Mode: MaxPerformance
 Databases:
 PRIM_SBY - Primary database
 PRIM - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
- Switch back to PRIM

DGMGRL> show database "PRIM"
Database - PRIM
 Role: PRIMARY
 Intended State: TRANSPORT-ON
 Instance(s):
 PRIM
Database Status:
SUCCESS
DGMGRL> show database "PRIM_SBY"
Database - PRIM_SBY
 Role: PHYSICAL STANDBY
 Intended State: APPLY-ON
 Transport Lag: 0 seconds
 Apply Lag: 0 seconds
 Real Time Query: ON
 Instance(s):
 PRIM_SBY
Database Status:
SUCCESS

2.5 ORACLE DATA GUARD BROKER SILINME ISLEMI
2.5.1.  Disable configuration: Bu komut broker programını dataguard üstündeki yönetimini sonlandırır.

DGMGRL> disable configuration;
Disabled.
DGMGRL> show configuration;
Configuration - PRIM_DGB
Protection Mode: MaxPerformance
 Databases:
 PRIM - Primary database
 PRIM_SBY - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED

2.5.2. Disable  standby database: Standby database’in yönetimini veya monitor lenmesini istemediğimizde bu komutu çalıştırıyoruz.

DGMGRL> DISABLE DATABASE "PRIM_SBY";
Disabled.

2.5.3. Veritabanını broker ayarlarından silmek.

DGMGRL> remove database "PRIM_SBY"
Removed database "PRIM_SBY" from the configuration
DGMGRL> DGMGRL> SHOW CONFIGURATION;
Configuration - PRIM_DGB
Protection Mode: MaxPerformance
 Databases:
 PRIM - Primary database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

2.5.4. Bütün ayarları silmek.

DGMGRL> REMOVE CONFIGURATION;
Removed configuration
DGMGRL> SHOW CONFIGURATION;
ORA-16532: Data Guard broker configuration does not exist
Configuration details cannot be determined by DGMGRL

2.5.5. Dg_broker_start parametresini primary ve standby database’lerde FALSE çevirerek brokeri tamamen etkisiz hale getirirz.

Primary

PUBLIC@ABSO:PRIM: >ALTER SYSTEM SET DG_BROKER_START=FALSE SCOPE=BOTH;
System altered.

Standby

SYS@CROO:PRIM_SBY: >ALTER SYSTEM SET DG_BROKER_START=FALSE SCOPE=BOTH;
System altered.