LoginSignup
8
6

More than 3 years have passed since last update.

@shirok(Shiro Kobayashi)in日本オラクル株式会社

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

Last updated at Posted at 2019-03-31

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

■構成
構成.png

■Oracle Database 作成

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

1_DB作成01.png

1_DB作成02.png

■Data Guard構成

●Security List設定

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

10_SecurityList01.png

●Data Guard構成設定

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

11_DGアソシエーション01.png

②以下画面のようにSutandby Databaseの配置位置を設定して、[有効化]ボタンをクリックして作成
11_DGアソシエーション03.png

●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
8
6
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
8
6