Data Guard Broker 활성화

Data Guard Broker 설정 위치 변경

ALTER SYSTEM SET dg_broker_config_file1='[설정 파일 저장 경로 및 파일명1].dat' SCOPE=both SID='*';
ALTER SYSTEM SET dg_broker_config_file2='[설정 파일 저장 경로 및 파일명2].dat' SCOPE=both SID='*';
SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME LIKE 'dg_broker_config_file%';
SQL> ALTER SYSTEM SET dg_broker_config_file1='+DATA/ORCLDB/dr1orcldb.dat' SCOPE=both SID='*';

System altered.

SQL> ALTER SYSTEM SET dg_broker_config_file2='+FRA/ORCLDB/dr2orcldb.dat' SCOPE=both SID='*';

System altered.
SQL> ALTER SYSTEM SET dg_broker_config_file1='+DATA/ORCLDB_STBY/dr1orcldb.dat' SCOPE=both;

System altered.

SQL> ALTER SYSTEM SET dg_broker_config_file2='+FRA/ORCLDB_STBY/dr2orcldb.dat' SCOPE=both;

System altered.

클러스터에 설정할 경우 모든 노드에서 액세스할 수 있는 공유 스토리지에 설정 파일을 저장합니다.

db_broker_start 파라미터 설정

ALTER SYSTEM SET dg_broker_start=true;
SQL> ALTER SYSTEM SET dg_broker_start=true;

System altered.
SQL> ALTER SYSTEM SET dg_broker_start=true;

System altered.


Data Guard Broker 환경 구성

Oracle Data Guard command-line interface(이하 DGMGRL) 접속

dgmgrl sys/<sys 계정 암호>@[primary 네트워크 서비스명]

[ORCLDB2@seoul1:~]$ dgmgrl sys/oracle@SEOUL_SYN
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Apr 4 15:21:15 2024
Version 19.22.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected to "ORCLDB"
Connected as SYSDBA.


데이터베이스 등록

CREATE CONFIGURATION [Data Guard 구성 이름] AS PRIMARY DATABASE IS [primary 글로벌 DB명] CONNECT IDENTIFIER IS [primary 네트워크 서비스명];
ADD DATABASE [standby 글로벌 DB명] AS CONNECT IDENTIFIER IS [standby 네트워크 서비스명] MAINTAINED AS PHYSICAL;
DGMGRL> CREATE CONFIGURATION dg_orcldb AS PRIMARY DATABASE IS orcldb CONNECT IDENTIFIER IS seoul_syn;
Configuration "dg_orcldb" created with primary database "orcldb"

DGMGRL> ADD DATABASE orcldb_stby AS CONNECT IDENTIFIER IS busan_syn MAINTAINED AS PHYSICAL;
Database "orcldb_stby" added

DGMGRL> SHOW CONFIGURATION

Configuration - dg_orcldb

  Protection Mode: MaxPerformance
  Members:
  orcldb      - Primary database
    orcldb_stby - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
DISABLED


Data Guard Broker 설정 활성화

ENABLE CONFIGURATION;
DGMGRL> ENABLE CONFIGURATION;
Enabled.


Data Guard Broker 등록 정보 조회

SHOW CONFIGURATION
SHOW DATABASE VERBOSE [primary 글로벌 DB명]
SHOW DATABASE VERBOSE [standby 글로벌 DB명]
VALIDATE DATABASE VERBOSE [primary 글로벌 DB명]
VALIDATE DATABASE VERBOSE [standby 글로벌 DB명]
DGMGRL> SHOW CONFIGURATION

Configuration - dg_orcldb

  Protection Mode: MaxPerformance
  Members:
  orcldb      - Primary database
    orcldb_stby - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 50 seconds ago)

DGMGRL> SHOW DATABASE VERBOSE orcldb

Database - orcldb

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

  Properties:
    DGConnectIdentifier             = 'seoul_syn'
    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

DGMGRL> SHOW DATABASE VERBOSE orcldb_stby

Database - orcldb_stby

  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: 14.00 KByte/s
  Active Apply Rate:  229.00 KByte/s
  Maximum Apply Rate: 229.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    ORCLDB

  Properties:
    DGConnectIdentifier             = 'busan_syn'
    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                        = 'busan'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=busan-dg)(PORT=1625))(CONNECT_DATA=(SERVICE_NAME=ORCLDB_STBY_DGMGRL)(INSTANCE_NAME=ORCLDB)(SERVER=DEDICATED)))'
    TopWaitEvents                   = '(monitor)'
    SidName                         = '(monitor)'

  Log file locations:
    Alert log               : /u01/app/oracle/db/diag/rdbms/orcldb_stby/ORCLDB/trace/alert_ORCLDB.log
    Data Guard Broker log   : /u01/app/oracle/db/diag/rdbms/orcldb_stby/ORCLDB/trace/drcORCLDB.log

Database Status:
SUCCESS

DGMGRL> VALIDATE DATABASE VERBOSE orcldb

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Flashback Database Status:
    orcldb:  On

  Capacity Information:
    Database  Instances        Threads
    orcldb    2                2

  Managed by Clusterware:
    orcldb:  YES

  Temporary Tablespace File Information:
    orcldb TEMP Files:  1

  Data file Online Move in Progress:
    orcldb:  No

  Transport-Related Information:
    Transport On:  Yes

  Log Files Cleared:
    orcldb Standby Redo Log Files:  Cleared

DGMGRL> VALIDATE DATABASE VERBOSE orcldb_stby

  Database Role:     Physical standby database
  Primary Database:  orcldb

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:
    orcldb     :  On
    orcldb_stby:  Off

  Capacity Information:
    Database     Instances        Threads
    orcldb       2                2
    orcldb_stby  1                2
    Warning: the target standby has fewer instances than the
    primary database, this may impact application performance

  Managed by Clusterware:
    orcldb     :  YES
    orcldb_stby:  YES

  Temporary Tablespace File Information:
    orcldb TEMP Files:       1
    orcldb_stby TEMP Files:  1

  Data file Online Move in Progress:
    orcldb:       No
    orcldb_stby:  No

  Standby Apply-Related Information:
    Apply State:      Running
    Apply Lag:        0 seconds (computed 1 second ago)
    Apply Delay:      0 minutes

  Transport-Related Information:
    Transport On:  Yes
    Gap Status:    No Gap
    Transport Lag:  0 seconds (computed 1 second ago)
    Transport Status:  Success

  Log Files Cleared:
    orcldb Standby Redo Log Files:       Cleared
    orcldb_stby Online Redo Log Files:   Cleared
    orcldb_stby Standby Redo Log Files:  Available

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (orcldb)                (orcldb_stby)
    1         3                       4                       Sufficient SRLs
    2         3                       4                       Sufficient SRLs

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (orcldb_stby)           (orcldb)
    1         3                       4                       Sufficient SRLs
    2         3                       4                       Sufficient SRLs

  Current Configuration Log File Sizes:
    Thread #   Smallest Online Redo      Smallest Standby Redo
               Log File Size             Log File Size
               (orcldb)                  (orcldb_stby)
    1          200 MBytes                200 MBytes
    2          200 MBytes                200 MBytes

  Future Configuration Log File Sizes:
    Thread #   Smallest Online Redo      Smallest Standby Redo
               Log File Size             Log File Size
               (orcldb_stby)             (orcldb)
    1          200 MBytes                200 MBytes
    2          200 MBytes                200 MBytes

  Apply-Related Property Settings:
    Property                        orcldb Value             orcldb_stby Value
    DelayMins                       0                        0
    ApplyParallel                   AUTO                     AUTO
    ApplyInstances                  0                        0

  Transport-Related Property Settings:
    Property                        orcldb Value             orcldb_stby Value
    LogShipping                     ON                       ON
    LogXptMode                      ASYNC                    ASYNC
    Dependency                      <empty>                  <empty>
    DelayMins                       0                        0
    Binding                         optional                 optional
    MaxFailure                      0                        0
    ReopenSecs                      300                      300
    NetTimeout                      30                       30
    RedoCompression                 DISABLE                  DISABLE


정적 연결 식별자 설정

SHOW DATABASE [standby 글로벌 DB명] StaticConnectIdentifier
EDIT DATABASE [standby 글로벌 DB명] SET PROPERTY StaticConnectIdentifier = '[standby 접속 정보]';
DGMGRL> SHOW DATABASE orcldb_stby StaticConnectIdentifier
  StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.1.105)(PORT=1523))(CONNECT_DATA=(SERVICE_NAME=ORCLDB_STBY_DGMGRL)(INSTANCE_NAME=ORCLDB)(SERVER=DEDICATED)))'

DGMGRL> EDIT DATABASE orcldb_stby SET PROPERTY StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=busan-dg)(PORT=1625))(CONNECT_DATA=(SERVICE_NAME=ORCLDB_STBY_DGMGRL)(INSTANCE_NAME=ORCLDB)(SERVER=DEDICATED)))';
Property "staticconnectidentifier" updated

DGMGRL> SHOW DATABASE orcldb_stby StaticConnectIdentifier
  StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=busan-dg)(PORT=1625))(CONNECT_DATA=(SERVICE_NAME=ORCLDB_STBY_DGMGRL)(INSTANCE_NAME=ORCLDB)(SERVER=DEDICATED)))'


Data Guard Broker 설정 백업

EXPORT CONFIGURATION TO dg_config.xml
DGMGRL> EXPORT CONFIGURATION TO dg_config.xml
Succeeded.

백업 파일은 XML 형태로 덤프 파일 경로에 저장됩니다.

복구 명령어는 아래와 같습니다.

IMPORT CONFIGURATION FROM dg_config.xml

Data Guard 구성 결과 확인 및 추가 작업

데이터베이스 상태 확인

SELECT name, db_unique_name FROM v$database;
SELECT db_unique_name, open_mode, database_role, switchover_status, dataguard_broker FROM v$database;
SQL> SELECT name, db_unique_name FROM v$database;

NAME                        DB_UNIQUE_NAME
--------------------------- --------------------
ORCLDB                      ORCLDB

SQL> SELECT db_unique_name, open_mode, database_role, switchover_status, dataguard_broker FROM v$database;

DB_UNIQUE_NAME       OPEN_MODE            DATABASE_ROLE        SWITCHOVER_STATUS    DATAGUARD_BROKER
-------------------- -------------------- -------------------- -------------------- ------------------------
ORCLDB               READ WRITE           PRIMARY              TO STANDBY           ENABLED
SQL> SELECT name, db_unique_name FROM v$database;

NAME                        DB_UNIQUE_NAME
--------------------------- --------------------
ORCLDB                      ORCLDB_STBY

SQL> SELECT db_unique_name, open_mode, database_role, switchover_status, dataguard_broker FROM v$database;

DB_UNIQUE_NAME       OPEN_MODE            DATABASE_ROLE        SWITCHOVER_STATUS    DATAGUARD_BROKER
-------------------- -------------------- -------------------- -------------------- ------------------------
ORCLDB_STBY          READ ONLY WITH APPLY PHYSICAL STANDBY     NOT ALLOWED          ENABLED


Local listener 설정

alter system set local_listener='<서비스 네트워크 접속 정보>,<DG 전용 네트워크 접속 정보>' scope=both;
SQL> ALTER SYSTEM SET local_listener="(ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=busan)(PORT=1523)) (ADDRESS=(PROTOCOL=TCP)(HOST=busan-dg)(PORT=1625)))";

System altered.

SQL> alter system register;

System altered.

기존 설정된 local listener 파라미터에 DG 전용 리스너를 추가합니다.

아카이브 로그 삭제 방지 정책 추가

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
new RMAN configuration parameters are successfully stored
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
new RMAN configuration parameters are successfully stored

Standby에 적용되지 않은 아카이브 로그가 삭제되어 데이터 손실이 발생하지 않도록 아카이브 삭제 정책을 수정합니다.

Primary와 Standby에서 각각 수행합니다.