Oracle DatabseのData GuardがOCIコンソールで簡単にボタンで作れるということで、やみてみました。
■構成
#■Oracle Database 作成
OCIコンソールで以下画面のようにEnterprise Edition以上のEditionを設定し作成
#■Data Guard構成
##●Security List設定
Data Gurd のデータ同期はOracle*NETの1521ポートで通信しあうため、両方のDBシステムのサブネットにセキュリティ・リストのイングレスおよびエグレス・ルールを適切に構成し、TCPトラフィックが該当するポート間を流れるようにします。
##●Data Guard構成設定
①作成したDatabaseの[Data Guardアソシエーション]画面にある[Data Guardの有効化]ボタンをクリック
②以下画面のようにSutandby Databaseの配置位置を設定して、[有効化]ボタンをクリックして作成
##●Data Guardの設定確認
ここからは、詳しくログ出力させながらコマンドで確認実行していきます。
①dgmgrl接続
[oracle@dg-inst01 ~]$ dgmgrl /
DGMGRL for Linux: Release 18.0.0.0.0 - Production on Thu Mar 28 15:19:36 2019
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "orcl_dg01"
Connected as SYSDG.
②Data Guardの設定確認
・Primary DB(orcl_dg01)
DGMGRL> show database verbose orcl_dg01
Database - orcl_dg01
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
orcl
Properties:
DGConnectIdentifier = 'orcl_dg01'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
RedoRoutes = ''
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '30'
TransportLagThreshold = '30'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '8'
LogArchiveMinSucceedDest = '1'
DataGuardSyncLatency = '0'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
PreferredObserverHosts = ''
HostName = 'dg-inst01'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg-inst01)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl_dg01_DGMGRL.privatesubnet01.vcn1723100.oraclevcn.com)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))'
OnlineArchiveLocation = 'LOCATION=USE_DB_RECOVERY_FILE_DEST MANDATORY'
OnlineAlternateLocation = ''
StandbyArchiveLocation = 'LOCATION=USE_DB_RECOVERY_FILE_DEST MANDATORY'
StandbyAlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
SidName = '(monitor)'
Log file locations:
Alert log : /u01/app/oracle/diag/rdbms/orcl_dg01/orcl/trace/alert_orcl.log
Data Guard Broker log : /u01/app/oracle/diag/rdbms/orcl_dg01/orcl/trace/drcorcl.log
Database Status:
SUCCESS
・Standby DB(orcl_dg02)
DGMGRL> show database verbose orcl_dg02
Database - orcl_dg02
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 13 seconds ago)
Apply Lag: 9 seconds (computed 13 seconds ago)
Average Apply Rate: 1.00 KByte/s
Active Apply Rate: 192.00 KByte/s
Maximum Apply Rate: 211.00 KByte/s
Real Time Query: ON
Instance(s):
orcl
Properties:
DGConnectIdentifier = 'orcl_dg02'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
RedoRoutes = ''
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '30'
TransportLagThreshold = '30'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '8'
LogArchiveMinSucceedDest = '1'
DataGuardSyncLatency = '0'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
PreferredObserverHosts = ''
HostName = 'dg-inst02'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg-inst02)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl_dg02_DGMGRL.privatesubnet01.vcn1723100.oraclevcn.com)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))'
OnlineArchiveLocation = 'LOCATION=USE_DB_RECOVERY_FILE_DEST MANDATORY'
OnlineAlternateLocation = ''
StandbyArchiveLocation = 'LOCATION=USE_DB_RECOVERY_FILE_DEST MANDATORY'
StandbyAlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
SidName = '(monitor)'
Log file locations:
Alert log : /u01/app/oracle/diag/rdbms/orcl_dg02/orcl/trace/alert_orcl.log
Data Guard Broker log : /u01/app/oracle/diag/rdbms/orcl_dg02/orcl/trace/drcorcl.log
Database Status:
SUCCESS
#■スイッチオーバー・テスト
両方向のスイッチオーバーを実行して、Data Guard構成が期待どおりに機能していることを確認
OCI Webコンソールから[Switch Over]をクリックしてスイッチオーバーさせますが、
ここでは勉強のため、詳しくログ出力させながらコマンドで確認実行してみてみます。
##●dgmgrl接続
Data Guardコマンドライン・インタフェース(DGMGRL)を使用すると、Data Guard Broker構成とそのデータベースを、コマンドラインから直接、あるいはバッチ・プログラムやスクリプトから管理できます。ということで、dgmgrlを使用してみてみます
[oracle@fdg-inst01 ~]$ dgmgrl sys/<password>@orcl_dg01
DGMGRL for Linux: Release 18.0.0.0.0 - Production on Thu Mar 28 11:40:11 2019
Version 18.4.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "orcl_dg01"
Connected as SYSDBA.
##●スイッチオーバー
①switchover to standby DB(orcl_dg02)
DGMGRL> switchover to orcl_dg02
Performing switchover NOW, please wait...
Operation requires a connection to database "orcl_dg02"
Connecting ...
Connected to "orcl_dg02"
Connected as SYSDBA.
New primary database "orcl_dg02" is opening...
Oracle Clusterware is restarting database "orcl_dg01" ...
Connected to "orcl_dg01"
Connected to "orcl_dg01"
Switchover succeeded, new primary is "orcl_dg02"
②switchover確認
orcl_dg01がSTANDBY、orcl_dg02がPRIMARYに切り替わっていることを確認
・orcl_dg01
DGMGRL> show database orcl_dg01
Database - orcl_dg01
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 10 seconds ago)
Apply Lag: 0 seconds (computed 10 seconds ago)
Average Apply Rate: 2.00 KByte/s
Real Time Query: ON
Instance(s):
orcl
Database Status:
SUCCESS
・orcl_dg02
DGMGRL> show database orcl_dg02
Database - orcl_dg02
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
orcl
Database Status:
SUCCESS
##●スイッチバック
orcl_dg01をPRIMARYに切り戻す
①switchover to primary DB(orcl_dg01)
DGMGRL> switchover to orcl_dg01
Performing switchover NOW, please wait...
Operation requires a connection to database "orcl_dg01"
Connecting ...
Connected to "orcl_dg01"
Connected as SYSDBA.
New primary database "orcl_dg01" is opening...
Oracle Clusterware is restarting database "orcl_dg02" ...
Connected to "orcl_dg02"
Switchover succeeded, new primary is "orcl_dg01"
②スイッチバック確認
orcl_dg01がPRIMARY、orcl_dg02がSTANDBYに切り替わっていることを確認
・orcl_dg01
DGMGRL> show database orcl_dg01
Database - orcl_dg01
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
orcl
Database Status:
SUCCESS
・orcl_dg02
DGMGRL> show database orcl_dg02
Database - orcl_dg02
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 9 seconds ago)
Apply Lag: 0 seconds (computed 9 seconds ago)
Average Apply Rate: 4.00 KByte/s
Real Time Query: ON
Instance(s):
orcl
Database Status:
SUCCESS
#■フェイルオーバー・テスト
手動でもフェイルオーバー・テストできますが、ファスト・スタート・フェイルオーバーを使用してみてみます。
##●maxavailability保護モード設定
MaxPerformance でもいいのですが手順確認のため、maxavailability保護モードに設定してみてみます
①現在の保護モード確認
DGMGRL> show configuration verbose
Configuration - orcl_dg01_orcl_dg02
Protection Mode: MaxPerformance
・・・
②maxavailability保護モード設定
DGMGRL> edit database orcl_dg02 set property 'logXptMode'='SYNC';
Property "logXptMode" updated
DGMGRL> edit database orcl_dg01 set property 'logXptMode'='SYNC';
Property "logXptMode" updated
DGMGRL> edit configuration set protection mode as maxavailability;
Succeeded.
③maxavailability保護モード設定確認
DGMGRL> show configuration verbose
Configuration - orcl_dg01_orcl_dg02
Protection Mode: MaxAvailability
・・・
##●Observer設定
Brokerからのファスト・スタート・フェイルオーバーを有効にします
※注意：
Observerは、DataGurad構成するDBシステムには設定せず、別のインスタンスで設定する必要がありますが、
ここでは、動作確認してみたいため、Standby DBに設定してみます
実環境ではやらないでください
・設定前確認
DGMGRL> show fast_start failover
Fast-Start Failover: DISABLED
・・・
・ファスト・スタート・フェイルオーバー有効
DGMGRL> Enable fast_start failover
Enabled.
・ファスト・スタート・フェイルオーバー有効確認
DGMGRL> show fast_start failover
Fast-Start Failover: ENABLED
Threshold: 30 seconds
Active Target: orcl_dg02
Potential Targets: "orcl_dg02"
orcl_dg02 valid
Observer: (none)
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 Write Errors YES
Oracle Error Conditions:
(none)
・オブザーバ起動
ここでは、orcl_dg02をオブザーバーにします
[oracle@dg-inst02 ~]$ dgmgrl sys/<password>@orcl_dg02
DGMGRL> start observer
[W000 2019-03-31T12:56:25.468+00:00] FSFO target standby is orcl_dg02
[W000 2019-03-31T12:56:32.709+00:00] Observer trace level is set to USER
[W000 2019-03-31T12:56:32.709+00:00] Try to connect to the primary.
[W000 2019-03-31T12:56:32.709+00:00] Try to connect to the primary orcl_dg01.
[W000 2019-03-31T12:56:32.740+00:00] The standby orcl_dg02 is ready to be a FSFO target
[W000 2019-03-31T12:56:36.755+00:00] Connection to the primary restored!
[W000 2019-03-31T12:56:38.755+00:00] Disconnecting from database orcl_dg01.
##●フェイルオーバー実行
PrimaryとStandby Databaseの名前確認
・事前確認
DGMGRL> show configuration verbose
Configuration - orcl_dg01_orcl_dg02
Protection Mode: MaxAvailability
Members:
orcl_dg01 - Primary database
orcl_dg02 - (*) Physical standby database
・OS停止
[root@dg-inst01 opc]# halt -n -p
Broadcast message from opc@dg-inst01
(/dev/pts/1) at 13:32 ...
The system is going down for power off NOW!
・observer出力ログ確認
[W000 2019-03-31T13:01:11.660+00:00] Primary database cannot be reached.
[W000 2019-03-31T13:01:11.660+00:00] Fast-Start Failover threshold has not exceeded. Retry for the next 30 seconds
[W000 2019-03-31T13:01:12.661+00:00] Try to connect to the primary.
Unable to connect to database using orcl_dg01
ORA-12541: TNS:no listener
・・・
[S002 2019-03-31T13:01:41.884+00:00] Fast-Start Failover started...
2019-03-31T13:01:41.884+00:00
Initiating Fast-Start Failover to database "orcl_dg02"...
[S002 2019-03-31T13:01:41.884+00:00] Initiating Fast-start Failover.
Performing failover NOW, please wait...
Failover succeeded, new primary is "orcl_dg02"
2019-03-31T13:03:00.433+00:00
[S002 2019-03-31T13:03:00.433+00:00] Fast-Start Failover finished...
[W000 2019-03-31T13:03:00.433+00:00] Failover succeeded. Restart pinging.
[W000 2019-03-31T13:03:00.469+00:00] Primary database has changed to orcl_dg02.
・フェイルオーバー確認
orcl_dg02がPrimary databaseに切り替わっていることを確認
[oracle@fdg-inst01 ~]$ dgmgrl /
DGMGRL> show configuration verbose
Configuration - orcl_dg01_orcl_dg02
Protection Mode: MaxAvailability
Members:
orcl_dg02 - Primary database
orcl_dg01 - (*) Physical standby database
(*) Fast-Start Failover target
Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
TraceLevel = 'USER'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
ObserverReconnect = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
ObserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'
ConfigurationWideServiceName = 'orcl_CFG'
Fast-Start Failover: ENABLED
Threshold: 30 seconds
Active Target: orcl_dg01
Potential Targets: "orcl_dg01"
orcl_dg01 valid
Observer: dg-inst02
Lag Limit: 30 seconds (not in use)
Shutdown Primary: TRUE
Auto-reinstate: TRUE
Observer Reconnect: (none)
Observer Override: FALSE
Configuration Status:
SUCCESS