1. Data Guard Broker 활성화
A) Data Guard Broker 설정 위치 변경
DG 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.
{}
클러스터에 설정할 경우 모든 노드에서 액세스할 수 있는 공유 스토리지에 설정 파일을 저장합니다.
B) db_broker_start 파라미터 설정
sys 유저로 primary와 standby에서 모두 수행
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.
2. Data Guard Broker 환경 구성
A) Oracle Data Guard command-line interface(이하 DGMGRL) 접속
sysdba 권한으로 접속
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.
B) 데이터베이스 등록
Data Guard 대상 데이터베이스 등록
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
C) Data Guard Broker 설정 활성화
Data Guard Broker 활성화
ENABLE CONFIGURATION;
DGMGRL> ENABLE CONFIGURATION; Enabled.
D) Data Guard Broker 등록 정보 조회
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
E) 정적 연결 식별자 설정
DB별 접속 정보 확인 및 변경
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)))'
F) Data Guard Broker 설정 백업
DGMGRL 에서 수행
EXPORT CONFIGURATION TO dg_config.xml
DGMGRL> EXPORT CONFIGURATION TO dg_config.xml Succeeded.
백업 파일은 XML 형태로 덤프 파일 경로에 저장됩니다.
복구 명령어는 아래와 같습니다.
IMPORT CONFIGURATION FROM dg_config.xml
3. Data Guard 구성 결과 확인 및 추가 작업
A) 데이터베이스 상태 확인
Primary DB와 Standby DB에서 수행
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
B) Local listener 설정
서버별 TNS 설정에 맞게 적용
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 전용 리스너를 추가합니다.
C) 아카이브 로그 삭제 방지 정책 추가
Primary와 Standby의 RMAN 설정 변경
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에서 각각 수행합니다.
