Oracle Cloud:Data Guard構成してみてみた

Oracle DatabseのData GuardがOCIコンソールで簡単にボタンで作れるということで、やみてみました。

■構成
<img width="651" alt="構成.png" src="https://qiita-image-store.s3.amazonaws.com/0/109087/dfc7bf3f-b88b-7304-f97d-7a57ae0f4e7b.png">


#■Oracle Database 作成
OCIコンソールで以下画面のようにEnterprise Edition以上のEditionを設定し作成

![1_DB作成01.png](https://qiita-image-store.s3.amazonaws.com/0/109087/b2396ed8-7c5b-47ff-b49e-fa4aacf76b6c.png)

![1_DB作成02.png](https://qiita-image-store.s3.amazonaws.com/0/109087/3b0964b7-2a99-a02d-3e4f-7dd29e7d995b.png)




#■Data Guard構成

##●Security List設定
Data Gurd のデータ同期はOracle*NETの1521ポートで通信しあうため、両方のDBシステムのサブネットにセキュリティ・リストのイングレスおよびエグレス・ルールを適切に構成し、TCPトラフィックが該当するポート間を流れるようにします。

<img width="870" alt="10_SecurityList01.png" src="https://qiita-image-store.s3.amazonaws.com/0/109087/2d4252ea-5536-b369-4656-c635a81580fd.png">


##●Data Guard構成設定
①作成したDatabaseの[Data Guardアソシエーション]画面にある[Data Guardの有効化]ボタンをクリック

![11_DGアソシエーション01.png](https://qiita-image-store.s3.amazonaws.com/0/109087/c03aad02-c0e8-57fb-2486-42b6829b4d48.png)


②以下画面のようにSutandby Databaseの配置位置を設定して、[有効化]ボタンをクリックして作成
![11_DGアソシエーション03.png](https://qiita-image-store.s3.amazonaws.com/0/109087/b725fc9e-08ce-f21d-c08c-94e25e3cdffd.png)




##●Data Guardの設定確認
ここからは、詳しくログ出力させながらコマンドで確認実行していきます。

①dgmgrl接続

```shell-session
[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)

```shell-session
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)

```shell-session
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を使用してみてみます

```shell-session
[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)

```shell-session
	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

```shell-session
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

```shell-session
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)

```shell-session
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

```shell-session
DGMGRL> show database orcl_dg01
	Database - orcl_dg01

	  Role:               PRIMARY
	  Intended State:     TRANSPORT-ON
	  Instance(s):
	    orcl

	Database Status:
	SUCCESS
```

・orcl_dg02

```shell-session
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保護モードに設定してみてみます

①現在の保護モード確認

```shell-session
DGMGRL> show configuration verbose

	Configuration - orcl_dg01_orcl_dg02

	  Protection Mode: MaxPerformance
	  ・・・
```

②maxavailability保護モード設定

```shell-session
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保護モード設定確認

```shell-session
DGMGRL> show configuration verbose

	Configuration - orcl_dg01_orcl_dg02

	  Protection Mode: MaxAvailability
	・・・				 
```

##●Observer設定
Brokerからのファスト・スタート・フェイルオーバーを有効にします

※注意：
Observerは、DataGurad構成するDBシステムには設定せず、別のインスタンスで設定する必要がありますが、
ここでは、動作確認してみたいため、Standby DBに設定してみます
実環境ではやらないでください

・設定前確認

```shell-session
DGMGRL> show fast_start failover

	Fast-Start Failover: DISABLED
	・・・
```

・ファスト・スタート・フェイルオーバー有効

```shell-session
DGMGRL> Enable fast_start failover
	Enabled.
```

・ファスト・スタート・フェイルオーバー有効確認

```shell-session
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をオブザーバーにします

```shell-session
[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の名前確認

・事前確認

```shell-session
DGMGRL> show configuration verbose

	Configuration - orcl_dg01_orcl_dg02

	  Protection Mode: MaxAvailability
	  Members:
	  orcl_dg01 - Primary database
	    orcl_dg02 - (*) Physical standby database
```

・OS停止

```shell-session
[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出力ログ確認

```shell-session
		[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に切り替わっていることを確認

```shell-session
[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
```