버전 비교

이전 버전 3

changes.mady.by.user 윤병훈

에 저장

비교대상

새 버전 현재

changes.mady.by.user 윤병훈

에 저장

키

  • 이 줄이 추가되었습니다.
  • 이 줄이 삭제되었습니다.
  • 서식이 변경되었습니다.

Data Guard Broker 활성화

Data Guard Broker 설정 위치 변경

코드 블럭
titleDG Broker 설정 파일 경로 변경 (클러스터 환경)
linenumberstrue
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 파라미터 설정

코드 블럭
titlesys 유저로 primary와 standby에서 모두 수행
linenumberstrue
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) 접속

코드 블럭
titlesysdba 권한으로 접속
linenumberstrue
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.


데이터베이스 등록

코드 블럭
titleData Guard 대상 데이터베이스 등록
linenumberstrue
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 설정 활성화

코드 블럭
titleData Guard Broker 활성화
linenumberstrue
ENABLE CONFIGURATION;
펼치기
서식 미적용
DGMGRL> ENABLE CONFIGURATION;
Enabled.


Data Guard Broker 등록 정보 조회

코드 블럭
titleBroker에 등록된 정보 조회
linenumberstrue
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


정적 연결 식별자 설정

코드 블럭
titleDB별 접속 정보 확인 및 변경
linenumberstrue
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.185105)(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 설정 백업

코드 블럭
titleDGMGRL 에서 수행
linenumberstrue
EXPORT CONFIGURATION TO dg_config.xml
펼치기
서식 미적용
DGMGRL> EXPORT CONFIGURATION TO dg_config.xml
Succeeded.

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

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

코드 블럭
IMPORT CONFIGURATION FROM dg_config.xml

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

데이터베이스 상태 확인

코드 블럭
titlePrimary DB와 Standby DB에서 수행
linenumberstrue
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 설정

코드 블럭
title서버별 TNS 설정에 맞게 적용
linenumberstrue
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 전용 리스너를 추가합니다.

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

코드 블럭
titlePrimary와 Standby의 RMAN 설정 변경
linenumberstrue
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에서 각각 수행합니다.