I. 개요
1. 사용 Software
| 제품명 | 버전 | 아키텍쳐 | 배포 사이트 |
|---|---|---|---|
| VirtualBox | 7.0.x | 호스트 환경에 따름 | https://www.virtualbox.org |
| Oracle Enterprise Linux | 8 (8.6 이상 권장) | x86 64bit | |
| Database | 19.7 이상 | x86 64bit | http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html |
Oracle Database 19.3은 http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html 에서 받을 수 있습니다.
2. 구성 계획
A) 서버
| VM | Hostname | CPU | Memory | 구성 방법 | 비고 |
|---|---|---|---|---|---|
| Primary | seoul | 2 | 4GB | 리눅스 설치 | DB 엔진 설치 및 DB 생성 완료 |
| Standby | busan | 2 | 4GB | Primary 복제 | DB 엔진만 설치 (또는 Primary에 엔진 설치 후 복제하여 사용) |
리눅스 기반의 Active-Standby 환경으로 구성합니다.
B) 스토리지
| VM | 용량 | VDI 타입 | 용도 | 비고 |
|---|---|---|---|---|
| Primary | 100GB | Dynamic / Normal | Primary 노드 스토리지 | 디스크 사용량을 최소화하기 위해 Dynamic으로 생성합니다. |
| Standby | 100GB | Dynamic / Normal | Standby 노드 스토리지 | primary의 디스크를 VirtualBox에서 복제하여 사용합니다. |
Primary 노드에 DB 소프트웨어 설치까지 완료한 후 VDI를 복제하여 standby로 사용하면 구축 시간을 절약할 수 있습니다.
복제가 완료된 후에 Primary에만 데이터베이스를 생성하기 바랍니다.
C) 데이터베이스 및 서비스 명
| 호스트명 | DG망 이름 | 서비스IP | DG망 IP | 글로벌 DB명 | 서비스명 | 인스턴스명 | DG 전용 TNS명 | DG 전용 리스너명 | |
|---|---|---|---|---|---|---|---|---|---|
| Primary | seoul | seoul_dg | 10.0.1.71 | 10.0.5.71 | orcl | orcl | orcl | SEOUL_SYN | LISTENER_DG |
| Standby | busan | busan_dg | 10.0.1.72 | 10.0.5.72 | orcl_stby | orcl_stby | orcl | BUSAN_SYN | LISTENER_DG |
II. 네트워크 설정
1. hosts 파일 편집
모든 서버의 /etc/hosts 파일에 IP와 호스트명 정보 입력
[primary IP] [primary 호스트명] [standby IP] [standby 호스트명]
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 10.0.1.71 seoul 10.0.5.71 seoul_dg 10.0.1.72 busan 10.0.5.72 busan_dg
{}
실제 운영 환경에서는 업무에 부담을 줄이기 위해 별도의 DG 전용 망을 사용하는 것을 권장합니다.
2. Primary 네트워크 설정
A) listener.ora 파일 작성
primary 노드 listener.ora 파일에 data guard에서 사용할 리스너 정보 추가
SID_LIST_[리스너명] =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = [primary 글로벌 DB명])
(ORACLE_HOME = [ORACLE_HOME 경로])
(SID_NAME = [인스턴스명])
)
(SID_DESC =
(GLOBAL_DBNAME = [primary 글로벌 DB명]_DGB)
(ORACLE_HOME = [ORACLE_HOME 경로])
(SID_NAME = [인스턴스명])
)
(SID_DESC =
(GLOBAL_DBNAME = [primary 글로벌 DB명]_DGMGRL)
(ORACLE_HOME = [ORACLE_HOME 경로])
(SID_NAME = [인스턴스명])
)
)
[리스너명] =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = [primary 호스트명 또는 IP])(PORT = [리스너 포트 번호]))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC[리스너 포트 번호]))
)
)
ADR_BASE_[리스너명] = [리스너 로그 경로]
# listener.ora Network Configuration File: /u01/app/oracle/product/19c/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
ADR_BASE_LISTENER_DG = /u01/app/oracle
ADR_BASE_LISTENER_ORCL = /u01/app/oracle
LISTENER_DG =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = seoul_dg)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER_DG =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/19c/db_1)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl_DGB)
(ORACLE_HOME = /u01/app/oracle/product/19c/db_1)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/19c/db_1)
(SID_NAME = orcl)
)
)
LISTENER_ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = seoul)(PORT = 1525))
)
SID_LIST_LISTENER_ORCL =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/19c/db_1)
(SID_NAME = orcl)
)
)
Primary 노드에 Static 리스너 설정을 추가합니다.
B) tnsnames.ora 파일 작성
primary 노드 tnsnames.ora 파일에 data guard에서 사용할 접속 정보 추가
[primary 네트워크 서비스명] =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = [primary 호스트명 또는 IP])(PORT = [리스너 포트 번호]))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = [primary 서비스명]))
)
)
[standby 네트워크 서비스명] =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = [standby 호스트명 또는 IP])(PORT = [리스너 포트 번호]))
)
(CONNECT_DATA =
(SERVICE_NAME = [standby 서비스명])
)
)
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19c/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
SEOUL_SYN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = seoul_dg)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
BUSAN_SYN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = busan_dg)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl_stby)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = seoul)(PORT = 1525))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
DB 접속을 위한 TNS 정보를 추가합니다.
C) Primary 리스너 기동 및 체크
리스너 기동 및 체크
lsnrctl start [리스너명] tnsping [primary 네트워크 서비스명]
[oracle@orcl ~]$ lsnrctl start LISTENER_DG LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 13-FEB-2024 14:10:59 Copyright (c) 1991, 2021, Oracle. All rights reserved. Starting /u01/app/oracle/product/19c/db_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 19.0.0.0.0 - Production System parameter file is /u01/app/oracle/product/19c/db_1/network/admin/listener.ora Log messages written to /u01/app/oracle/diag/tnslsnr/seoul/listener_dg/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=seoul_dg)(PORT=1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=seoul_dg)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER_DG Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 13-FEB-2024 14:10:59 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/19c/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/seoul/listener_dg/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=seoul_dg)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "orcl" has 1 instance(s). Instance "orcl", status UNKNOWN, has 1 handler(s) for this service... Service "orcl_DGB" has 1 instance(s). Instance "orcl", status UNKNOWN, has 1 handler(s) for this service... Service "orcl_DGMGRL" has 1 instance(s). Instance "orcl", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully [oracle@orcl ~]$ lsnrctl start LISTENER_ORCL LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 13-FEB-2024 14:11:16 Copyright (c) 1991, 2021, Oracle. All rights reserved. Starting /u01/app/oracle/product/19c/db_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 19.0.0.0.0 - Production System parameter file is /u01/app/oracle/product/19c/db_1/network/admin/listener.ora Log messages written to /u01/app/oracle/diag/tnslsnr/seoul/listener_orcl/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=seoul)(PORT=1525))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=seoul)(PORT=1525))) STATUS of the LISTENER ------------------------ Alias LISTENER_ORCL Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 13-FEB-2024 14:11:16 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/19c/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/seoul/listener_orcl/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=seoul)(PORT=1525))) Services Summary... Service "orcl" has 1 instance(s). Instance "orcl", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully
[oracle@orcl ~]$ ps -ef | grep tns root 27 2 0 14:06 ? 00:00:00 [netns] oracle 1306 1 0 14:10 ? 00:00:00 /u01/app/oracle/product/19c/db_1/bin/tnslsnr LISTENER_DG -inherit oracle 1311 1 0 14:11 ? 00:00:00 /u01/app/oracle/product/19c/db_1/bin/tnslsnr LISTENER_ORCL -inherit oracle 1314 1273 0 14:11 pts/0 00:00:00 grep --color=auto tns [oracle@orcl ~]$ tnsping SEOUL_SYN TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 13-FEB-2024 14:12:52 Copyright (c) 1997, 2021, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = seoul_dg)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl))) OK (0 msec)
3. Standby 네트워크 설정
A) listener.ora 파일 작성
standby 노드 listener.ora 파일에 data guard에서 사용할 리스너 정보 추가
SID_LIST_[리스너명] =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = [standby 글로벌 DB명])
(ORACLE_HOME = [ORACLE_HOME 경로])
(SID_NAME = [인스턴스명])
)
(SID_DESC =
(GLOBAL_DBNAME = [standby 글로벌 DB명]_DGB)
(ORACLE_HOME = [ORACLE_HOME 경로])
(SID_NAME = [인스턴스명])
)
(SID_DESC =
(GLOBAL_DBNAME = [standby 글로벌 DB명]_DGMGRL)
(ORACLE_HOME = [ORACLE_HOME 경로])
(SID_NAME = [인스턴스명])
)
)
[리스너명] =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = [standby 호스트명 또는 IP])(PORT = [리스너 포트 번호]))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC[리스너 포트 번호]))
)
)
ADR_BASE_[리스너명] = [리스너 로그 경로]
# listener.ora Network Configuration File: /u01/app/oracle/product/19c/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
ADR_BASE_LISTENER_DG = /u01/app/oracle
ADR_BASE_LISTENER_ORCL = /u01/app/oracle
LISTENER_DG =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = busan_dg)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER_DG =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl_stby)
(ORACLE_HOME = /u01/app/oracle/product/19c/db_1)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl_stby_DGB)
(ORACLE_HOME = /u01/app/oracle/product/19c/db_1)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl_stby_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/19c/db_1)
(SID_NAME = orcl)
)
)
LISTENER_ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = busan)(PORT = 1525))
)
SID_LIST_LISTENER_ORCL =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl_stby)
(ORACLE_HOME = /u01/app/oracle/product/19c/db_1)
(SID_NAME = orcl)
)
)
B) tnsnames.ora 파일 작성
standby 노드 tnsnames.ora 파일에 data guard에서 사용할 접속 정보 추가
[standby 네트워크 서비스명] =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = [standby 호스트명 또는 IP])(PORT = [리스너 포트 번호]))
)
(CONNECT_DATA =
(SERVICE_NAME = [standby 서비스명])
)
)
[primary 네트워크 서비스명] =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = [primary 호스트명 또는 IP])(PORT = [리스너 포트 번호]))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = [primary 서비스명]))
)
)
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19c/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
BUSAN_SYN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = busan_dg)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl_stby)
)
)
SEOUL_SYN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = seoul_dg)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCL_STBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = busan)(PORT = 1525))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl_stby)
)
)
C) Standby 리스너 기동 및 체크
리스너 기동 및 체크
lsnrctl start [리스너명] tnsping [primary 네트워크 서비스명]
[oracle@orcl ~]$ lsnrctl start LISTENER_DG LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 13-FEB-2024 14:12:19 Copyright (c) 1991, 2021, Oracle. All rights reserved. Starting /u01/app/oracle/product/19c/db_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 19.0.0.0.0 - Production System parameter file is /u01/app/oracle/product/19c/db_1/network/admin/listener.ora Log messages written to /u01/app/oracle/diag/tnslsnr/busan/listener_dg/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=busan_dg)(PORT=1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=busan_dg)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER_DG Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 13-FEB-2024 14:12:19 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/19c/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/busan/listener_dg/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=busan_dg)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "orcl_stby" has 1 instance(s). Instance "orcl", status UNKNOWN, has 1 handler(s) for this service... Service "orcl_stby_DGB" has 1 instance(s). Instance "orcl", status UNKNOWN, has 1 handler(s) for this service... Service "orcl_stby_DGMGRL" has 1 instance(s). Instance "orcl", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully [oracle@orcl ~]$ lsnrctl start LISTENER_ORCL LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 13-FEB-2024 14:12:25 Copyright (c) 1991, 2021, Oracle. All rights reserved. Starting /u01/app/oracle/product/19c/db_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 19.0.0.0.0 - Production System parameter file is /u01/app/oracle/product/19c/db_1/network/admin/listener.ora Log messages written to /u01/app/oracle/diag/tnslsnr/busan/listener_orcl/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=busan)(PORT=1525))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=busan)(PORT=1525))) STATUS of the LISTENER ------------------------ Alias LISTENER_ORCL Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 13-FEB-2024 14:12:25 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/19c/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/busan/listener_orcl/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=busan)(PORT=1525))) Services Summary... Service "orcl" has 1 instance(s). Instance "orcl", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully
[oracle@orcl ~]$ ps -ef | grep tns root 27 2 0 14:07 ? 00:00:00 [netns] oracle 1309 1 0 14:12 ? 00:00:00 /u01/app/oracle/product/19c/db_1/bin/tnslsnr LISTENER_DG -inherit oracle 1313 1 0 14:12 ? 00:00:00 /u01/app/oracle/product/19c/db_1/bin/tnslsnr LISTENER_ORCL -inherit oracle 1316 1276 0 14:12 pts/0 00:00:00 grep --color=auto tns [oracle@orcl ~]$ tnsping SEOUL_SYN TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 13-FEB-2024 14:12:58 Copyright (c) 1997, 2021, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = seoul_dg)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl))) OK (10 msec)
III. 사전 환경 구성
1. 시간 동기화 설정
A) 시간 정보를 참조할 서버 정보 등록
root 계정으로 vi /etc/chrony.conf 하여 설정 수정
server [동기화 서버의 IP] iburst
B) 시간 동기화 서비스 기동 및 활성화
시간 동기화 서비스 기동 및 활성화
systemctl start chronyd systemctl enable chronyd
C) 서비스 기동 결과 확인
시간동기화 서비스 확인
chronyc sources chronyc sourcestats chronyc sources -v chronyc tracking
{}
2. 아카이브 로그 환경 설정
A) Data Guard를 위한 아카이브 로그 파라미터 설정
sys 유저로 primary에서 수행
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=([primary 글로벌 DB명],[standby 글로벌 DB명])'; -- ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
[oracle@orcl ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 13 14:14:20 2024 Version 19.11.0.0.0 Copyright (c) 1982, 2020, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.11.0.0.0 SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcl_stby)'; System altered. SQL> show parameter LOG_ARCHIVE_CONFIG NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ log_archive_config string DG_CONFIG=(orcl,orcl_stby)
{}
Data Guard 운영에 필요한 아카이브 관련 파라미터를 설정합니다.
- LOG_ARCHIVE_CONFIG : 리두 로그를 원격 대상으로 보내고 원격 리두 로그를 수신하며, Data Guard 구성에서 각 데이터베이스의 DB_UNIQUE_NAME을 지정합니다.
- LOG_ARCHIVE_FORMAT : 아카이브 로그 파일명의 포맷을 지정합니다.
B) 아카이브 로그 모드 활성화
sys 유저로 primary에서 수행
ARCHIVE LOG LIST SHUTDOWN IMMEDIATE STARTUP MOUNT ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN; SELECT log_mode FROM v$database;
SQL> ARCHIVE LOG LIST Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 10 Next log sequence to archive 12 Current log sequence 12 SQL> SELECT log_mode FROM v$database; LOG_MODE ------------------------------------ ARCHIVELOG
Data Guard 구축하기 위해서는 반드시 archive log 모드로 운영이 되어야 합니다.
로그 모드를 조회한 후 noarchive 모드이면 반드시 archive 모드로 변경합니다.
C) 강제 로그 적용 모드 활성
sys 유저로 primary에서 수행
ALTER DATABASE FORCE LOGGING; ALTER SYSTEM SWITCH LOGFILE; SELECT FORCE_LOGGING FROM V$DATABASE;
SQL> ALTER DATABASE FORCE LOGGING; Database altered. SQL> ALTER SYSTEM SWITCH LOGFILE; System altered. SQL> SELECT FORCE_LOGGING FROM V$DATABASE; FORCE_LOGGING -------------------------------------------------------------------------------- YES
추가적으로 force logging 모드로 전환하여, 모든 작업이 강제적으로 로그로 기록될 수 있도록 합니다.
3. 파라미터 설정
A) DB명 관련 파라미터 조회
DB name 파라미터 값 조회
show parameter db_name show parameter db_unique_name SELECT db_unique_name FROM v$database;
SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ db_name string orcl SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ db_unique_name string orcl SQL> SELECT db_unique_name FROM v$database; DB_UNIQUE_NAME ------------------------------------------------------------------------------------------ orcl
db_unique_name은 Primary와 Standby가 달라야 합니다.
B) Data Guard 관련 파라미터 (Fetch Archive Log, Standby File Management) 설정
sys 유저로 primary에서 수행
ALTER SYSTEM SET FAL_CLIENT=[primary 글로벌 DB명]; ALTER SYSTEM SET FAL_SERVER=[standby 글로벌 DB명]; ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
SQL> ALTER SYSTEM SET FAL_CLIENT=orcl; System altered. SQL> ALTER SYSTEM SET FAL_SERVER=orcl_stby; System altered. SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO; System altered. SQL> show parameter fal_client NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ fal_client string ORCL SQL> show parameter fal_server NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ fal_server string ORCL_STBY SQL> show parameter standby_file_management NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ standby_file_management string AUTO
Data Guard 환경 구성을 위한 파라미터를 설정합니다.
- FAL_CLIENT : FAL(fetch archive log) 클라이언트를 참조하기 위해 FAL_SERVER 초기화 매개 변수를 통해 구성된 FAL 클라이언트 이름을 지정합니다.
- FAL_SERVER : Standby 데이터베이스에 대한 FAL(fetch archive log) 서버를 지정합니다.
STANDBY_FILE_MANAGEMENT : Physical Standby에 automatic standby file management를 활성화 또는 비활성화합니다. 자동 대기 파일 관리를 사용하면 primary 데이터베이스의 파일 추가 및 삭제 작업이 standby 데이터베이스에도 적용됩니다.
C) Standby Redo 로그 추가
Standby Redo 그룹 생성 (primary와 동일한 갯수와 사이즈로 생성)
ALTER DATABASE ADD STANDBY LOGFILE GROUP [그룹 번호] ('[로그 파일 경로 및 파일 이름]') SIZE [로그 파일 크기];
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 101 ('/oradata/ORCL/standby_redo01.log') SIZE 200M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 102 ('/oradata/ORCL/standby_redo02.log') SIZE 200M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 103 ('/oradata/ORCL/standby_redo03.log') SIZE 200M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 104 ('/oradata/ORCL/standby_redo04.log') SIZE 200M;
Database altered.
SQL> select group#, member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
1 /oradata/ORCL/redo01.log
2 /oradata/ORCL/redo02.log
3 /oradata/ORCL/redo03.log
101 /oradata/ORCL/standby_redo01.log
102 /oradata/ORCL/standby_redo02.log
103 /oradata/ORCL/standby_redo03.log
104 /oradata/ORCL/standby_redo04.log
7 rows selected.
Standby Redo Log를 생성합니다. 기존의 그룹 수와 같거나 많게 추가합니다. (그룹 1개 추가 권장)
4. Standby 환경 설정
A) 디렉토리 생성
oracle 유저로 standby 서버에서 수행
mkdir -p [데이터 파일 저장 경로] mkdir -p [FRA 할당 경로] mkdir -p [adump 파라미터 경로]
[root@busan ~]# mkdir -p /oradata/ORCL [root@busan ~]# mkdir -p /oradata/fast_recovery_area/ORCL [root@busan ~]# mkdir -p /u01/app/oracle/admin/orcl/adump [root@busan ~]# chown -R oracle:dba /oradata [root@busan ~]# chown -R oracle:dba /u01/app/oracle/admin
Standby 노드에 필요한 디렉토리를 생성합니다.
B) 패스워드 파일 복제
oracle 유저로 primary에서 standby로 전송 (또는 standby에서 생성)
scp $ORACLE_HOME/dbs/[패스워드 파일] [oracle 유저]@[standby 호스트명]:[$ORACLE_HOME/dbs 경로] --orapwd file=$ORACLE_HOME/dbs/orapw[DB명] password=[sys 계정 암호] entries=10 format=12
[oracle@orcl ~]$ scp $ORACLE_HOME/dbs/orapworcl oracle@busan_dg:/u01/app/oracle/product/19c/db_1/dbs The authenticity of host 'busan_dg (10.0.5.72)' can't be established. ECDSA key fingerprint is SHA256:OlbI4iHerMUO4ApsJ9mAUg/47+LTgX8fEVdJlAOctF0. Are you sure you want to continue connecting (yes/no/[fingerprint])? yes Warning: Permanently added 'busan_dg,10.0.5.72' (ECDSA) to the list of known hosts. oracle@busan_dg's password: orapworcl 100% 2048 827.0KB/s 00:00
Primary 와 동일한 암호로 설정되어야 하며, 가급적 새로 생성하는 것보다 primary로부터 복제해와서 사용하는 것을 권장합니다.
C) 임시 파라미터 파일 생성
standby 서버에서 oracle 계정으로 홈 경로(또는 적절한 경로)에 생성
cat > ~oracle/[standby 글로벌 DB명].ora *.db_name='[DB명]'
[oracle@orcl ~]$ cat > ~oracle/orcl_stby.ora *.db_name='orcl' ^C
Standby 데이터베이스에서 임시로 사용할 파라미터 파일을 생성합니다.
간단하게 db_name만 지정해도 충분합니다.
IV. Standby DB 복제
1. Standby DB Instance 기동
A) nomount 모드로 DB 시작
Standby 인스턴스 기동
STARTUP NOMOUNT PFILE='~oracle/[standby 글로벌 DB명].ora'
[oracle@orcl ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 13 14:18:29 2024 Version 19.11.0.0.0 Copyright (c) 1982, 2020, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount pfile='/home/oracle/orcl_stby.ora'; ORACLE instance started. Total System Global Area 268434272 bytes Fixed Size 8895328 bytes Variable Size 201326592 bytes Database Buffers 50331648 bytes Redo Buffers 7880704 bytes
{}
2. RMAN을 이용한 DB 복제
A) RMAN 접속
RMAN 으로 Primary와 Standby 동시 접속
rman TARGET sys/[sys 계정 암호]@[primary 네트워크 서비스명] AUXILIARY sys/[sys 계정 암호]@[standby 네트워크 서비스명]
[oracle@orcl ~]$ export NLS_DATE_FORMAT="yyyy/mm/dd hh24:mi:ss" [oracle@orcl ~]$ rman TARGET sys/oracle@seoul_syn AUXILIARY sys/oracle@busan_syn Recovery Manager: Release 19.0.0.0.0 - Production on Tue Feb 13 14:19:15 2024 Version 19.11.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1688108027) connected to auxiliary database: ORCL (not mounted)
B) Duplicate 명령으로 DB 복제
운영 중인 DB를 RMAN으로 standby 서버에 복제
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
-- PASSWORD FILE
SPFILE
SET db_unique_name='[standby 글로벌 DB명]' COMMENT 'Is standby'
-- SET cluster_database='FALSE'
SET FAL_SERVER='[primary 글로벌 DB명]' COMMENT 'Is primary'
NOFILENAMECHECK;
RMAN> DUPLICATE TARGET DATABASE
2> FOR STANDBY
3> FROM ACTIVE DATABASE
4> DORECOVER
5> SPFILE
6> SET db_unique_name='orcl_stby' COMMENT 'Is standby'
7> SET FAL_SERVER='orcl' COMMENT 'Is primary'
8> NOFILENAMECHECK;
Starting Duplicate Db at 2024/02/13 14:21:26
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=182 device type=DISK
current log archived
contents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format '/u01/app/oracle/product/19c/db_1/dbs/orapworcl' ;
restore clone from service 'seoul_syn' spfile to
'/u01/app/oracle/product/19c/db_1/dbs/spfileorcl.ora';
sql clone "alter system set spfile= ''/u01/app/oracle/product/19c/db_1/dbs/spfileorcl.ora''";
}
executing Memory Script
Starting backup at 2024/02/13 14:21:27
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=291 device type=DISK
Finished backup at 2024/02/13 14:21:30
Starting restore at 2024/02/13 14:21:30
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service seoul_syn
channel ORA_AUX_DISK_1: restoring SPFILE
output file name=/u01/app/oracle/product/19c/db_1/dbs/spfileorcl.ora
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2024/02/13 14:21:33
sql statement: alter system set spfile= ''/u01/app/oracle/product/19c/db_1/dbs/spfileorcl.ora''
contents of Memory Script:
{
sql clone "alter system set db_unique_name =
''orcl_stby'' comment=
''Is standby'' scope=spfile";
sql clone "alter system set FAL_SERVER =
''orcl'' comment=
''Is primary'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set db_unique_name = ''orcl_stby'' comment= ''Is standby'' scope=spfile
sql statement: alter system set FAL_SERVER = ''orcl'' comment= ''Is primary'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1258290744 bytes
Fixed Size 8896056 bytes
Variable Size 318767104 bytes
Database Buffers 922746880 bytes
Redo Buffers 7880704 bytes
duplicating Online logs to Oracle Managed File (OMF) location
contents of Memory Script:
{
restore clone from service 'seoul_syn' standby controlfile;
}
executing Memory Script
Starting restore at 2024/02/13 14:22:00
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service seoul_syn
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oradata/ORCL/control01.ctl
output file name=/oradata/fast_recovery_area/ORCL/control02.ctl
Finished restore at 2024/02/13 14:22:04
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
RMAN-05538: warning: implicitly using DB_FILE_NAME_CONVERT
RMAN-05158: WARNING: auxiliary (datafile) file name /oradata/ORCL/system01.dbf conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name /oradata/ORCL/sysaux01.dbf conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name /oradata/ORCL/undotbs01.dbf conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name /oradata/ORCL/users01.dbf conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (tempfile) file name /oradata/ORCL/temp01.dbf conflicts with a file used by the target database
contents of Memory Script:
{
set newname for tempfile 1 to
"/oradata/ORCL/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/oradata/ORCL/system01.dbf";
set newname for datafile 2 to
"/oradata/ORCL/sysaux01.dbf";
set newname for datafile 3 to
"/oradata/ORCL/undotbs01.dbf";
set newname for datafile 4 to
"/oradata/ORCL/users01.dbf";
restore
from nonsparse from service
'seoul_syn' clone database
;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /oradata/ORCL/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 2024/02/13 14:22:09
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service seoul_syn
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /oradata/ORCL/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service seoul_syn
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00002 to /oradata/ORCL/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service seoul_syn
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /oradata/ORCL/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service seoul_syn
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /oradata/ORCL/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2024/02/13 14:22:48
sql statement: alter system archive log current
current log archived
contents of Memory Script:
{
restore clone force from service 'seoul_syn'
archivelog from scn 742232;
switch clone datafile all;
}
executing Memory Script
Starting restore at 2024/02/13 14:22:48
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service seoul_syn
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=14
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service seoul_syn
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=15
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2024/02/13 14:22:50
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=1160835769 file name=/oradata/ORCL/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=1160835769 file name=/oradata/ORCL/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=1160835769 file name=/oradata/ORCL/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=1160835769 file name=/oradata/ORCL/users01.dbf
contents of Memory Script:
{
set until scn 742523;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 2024/02/13 14:22:50
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 14 is already on disk as file /oradata/fast_recovery_area/ORCL_STBY/archivelog/2024_02_13/o1_mf_1_14_lwoz57hg_.arc
archived log for thread 1 with sequence 15 is already on disk as file /oradata/fast_recovery_area/ORCL_STBY/archivelog/2024_02_13/o1_mf_1_15_lwoz58kc_.arc
archived log file name=/oradata/fast_recovery_area/ORCL_STBY/archivelog/2024_02_13/o1_mf_1_14_lwoz57hg_.arc thread=1 sequence=14
archived log file name=/oradata/fast_recovery_area/ORCL_STBY/archivelog/2024_02_13/o1_mf_1_15_lwoz58kc_.arc thread=1 sequence=15
media recovery complete, elapsed time: 00:00:01
Finished recover at 2024/02/13 14:22:53
contents of Memory Script:
{
delete clone force archivelog all;
}
executing Memory Script
released channel: ORA_DISK_1
released channel: ORA_AUX_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=291 device type=DISK
deleted archived log
archived log file name=/oradata/fast_recovery_area/ORCL_STBY/archivelog/2024_02_13/o1_mf_1_14_lwoz57hg_.arc RECID=1 STAMP=1160835767
deleted archived log
archived log file name=/oradata/fast_recovery_area/ORCL_STBY/archivelog/2024_02_13/o1_mf_1_15_lwoz58kc_.arc RECID=2 STAMP=1160835768
Deleted 2 objects
Finished Duplicate Db at 2024/02/13 14:22:59
Standby 복제 DB 확인
[oracle@orcl ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 13 14:24:17 2024 Version 19.11.0.0.0 Copyright (c) 1982, 2020, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.11.0.0.0 SQL> select status from v$instance; STATUS ------------------------------------ MOUNTED SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ db_name string orcl SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ db_unique_name string orcl_stby SQL> SELECT db_unique_name FROM v$database; DB_UNIQUE_NAME ------------------------------------------------------------------------------------------ orcl_stby
V. Data Guard Broke 설정
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='/oradata/dr1orcl.dat' SCOPE=both SID='*'; System altered. SQL> ALTER SYSTEM SET dg_broker_config_file2='/oradata/dr2orcl.dat' SCOPE=both SID='*'; System altered. SQL> SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME LIKE 'dg_broker_config_file%'; NAME VALUE ------------------------------ ---------------------------------------------------------------------- dg_broker_config_file1 /oradata/dr1orcl.dat dg_broker_config_file2 /oradata/dr2orcl.dat
클러스터에 Data Guard를 구성할 경우 모든 노드에서 액세스할 수 있는 공유 스토리지에 broker 설정 파일을 저장합니다.
B) db_broker_start 파라미터 설정
sys 유저로 primary와 standby에서 모두 수행
ALTER SYSTEM SET dg_broker_start=true;
Primary
SQL> ALTER SYSTEM SET dg_broker_start=true; System altered. SQL> show parameter dg_broker_start NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ dg_broker_start boolean TRUE
Standby
SQL> ALTER SYSTEM SET dg_broker_start=true; System altered. SQL> show parameter dg_broker_start NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ dg_broker_start boolean TRUE
{}
Primary와 Standby 데이터베이스의 Data Guard Broker 시작을 활성화합니다.
반드시 양쪽 모두에서 수행해야 합니다.
2. Data Guard Broker 환경 구성
A) Oracle Data Guard command-line interface(이하 DGMGRL) 접속
sysdba 권한으로 접속
dgmgrl sys/<sys 계정 암호>@[primary 네트워크 서비스명]
[oracle@orcl ~]$ dgmgrl sys/oracle@seoul_syn DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Feb 13 14:27:06 2024 Version 19.11.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected to "orcl" Connected as SYSDBA.
원격 접속이 구성된 환경에서 DGMGRL을 통해 데이터베이스에 접속할 수 있습니다.
원격 접속에서 password file 파일을 사용할 수 있도록 암호를 입력하는 방식으로 접속합니다.
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_orcl AS PRIMARY DATABASE IS orcl CONNECT IDENTIFIER IS seoul_syn;
Configuration "dg_orcl" created with primary database "orcl"
DGMGRL> ADD DATABASE orcl_stby AS CONNECT IDENTIFIER IS busan_syn MAINTAINED AS PHYSICAL;
Database "orcl_stby" added
DGMGRL> SHOW CONFIGURATION;
Configuration - dg_orcl
Protection Mode: MaxPerformance
Members:
orcl - Primary database
orcl_stby - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
DISABLED
Primary 데이터베이스로 Data Guard Broker를 구성하고, standby 데이터베이스를 Data Guard Broker에 추가합니다.
C) Data Guard Broker 설정 활성화
Data Guard Broker 활성화
ENABLE CONFIGURATION;
DGMGRL> ENABLE CONFIGURATION; Enabled.
등록된 설정값을 갖고 Data Guard Broker를 활성화합니다.
D) Data Guard Broker 등록 정보 조회
Broker에 등록된 정보 조회
SHOW CONFIGURATION; SHOW DATABASE [primary 글로벌 DB명]; SHOW DATABASE [standby 글로벌 DB명]; -- VALIDATE DATABASE VERBOSE [standby 글로벌 DB명];
DGMGRL> SHOW CONFIGURATION;
Configuration - dg_orcl
Protection Mode: MaxPerformance
Members:
orcl - Primary database
orcl_stby - Physical standby database
Warning: ORA-16854: apply lag could not be determined
Fast-Start Failover: Disabled
Configuration Status:
WARNING (status updated 2 seconds ago)
... << 로그 적용을 위해 잠시 대기 >> ...
DGMGRL> SHOW CONFIGURATION;
Configuration - dg_orcl
Protection Mode: MaxPerformance
Members:
orcl - Primary database
orcl_stby - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 12 seconds ago)
DGMGRL> SHOW DATABASE orcl;
Database - orcl
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
orcl
Database Status:
SUCCESS
DGMGRL> SHOW DATABASE orcl_stby;
Database - orcl_stby
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 68.00 KByte/s
Real Time Query: OFF
Instance(s):
orcl
Database Status:
SUCCESS
Data Guard Broker에 등록된 내용을 SHOW 명령어로 조회할 수 있습니다.
E) 정적 연결 식별자 설정
DB별 접속 정보 확인 및 변경
SHOW DATABASE [primary 글로벌 DB명] StaticConnectIdentifier; EDIT DATABASE [primary 글로벌 DB명] SET PROPERTY StaticConnectIdentifier = '[primary 접속 정보]'; SHOW DATABASE [standby 글로벌 DB명] StaticConnectIdentifier; EDIT DATABASE [standby 글로벌 DB명] SET PROPERTY StaticConnectIdentifier = '[standby 접속 정보]';
DGMGRL> show database orcl StaticConnectIdentifier StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=seoul)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))' DGMGRL> edit database orcl set property StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=seoul_dg)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))'; Property "staticconnectidentifier" updated DGMGRL> show database orcl StaticConnectIdentifier StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=seoul)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))'
DGMGRL> show database orcl_stby StaticConnectIdentifier StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=busan)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl_stby_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))' DGMGRL> edit database orcl_stby set property StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=busan_dg)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl_stby_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))'; Property "staticconnectidentifier" updated DGMGRL> show database orcl_stby StaticConnectIdentifier StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=busan_dg)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl_stby_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))'
기본 포트(1521)이 아닌 포트 번호를 사용하거나, 서비스에 사용되는 네트워크와 다른 설정을 사용할 경우에 Data Guard Broker 내의 접속 설정을 변경해야 할 수 있습니다.
F) Data Guard Broker 설정 백업
DGMGRL 에서 수행
EXPORT CONFIGURATION TO dg_config.xml
DGMGRL> EXPORT CONFIGURATION TO dg_config.xml Succeeded.
Data Guard Broker 구성을 백업 받습니다. 백업은 XML 파일로 DB trace 경로에 생성됩니다.
3. Data Guard 구성 결과 확인 및 추가 작업
A) 데이터베이스 상태 확인
Primary DB와 Standby DB에서 수행
SELECT db_unique_name, open_mode, database_role, switchover_status, dataguard_broker FROM v$database;
Primary
SQL> set lines 200 SQL> col db_unique_name form a20 SQL> col open_mode form a20 SQL> col database_role form a20 SQL> col switchover_status form a20 SQL> col dataguard_broker form a20 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 -------------------- -------------------- -------------------- -------------------- -------------------- orcl READ WRITE PRIMARY TO STANDBY ENABLED
Standby
SQL> set lines 200 SQL> col db_unique_name form a20 SQL> col open_mode form a20 SQL> col database_role form a20 SQL> col switchover_status form a20 SQL> col dataguard_broker form a20 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 -------------------- -------------------- -------------------- -------------------- -------------------- orcl_stby MOUNTED PHYSICAL STANDBY NOT ALLOWED ENABLED
각 데이터베이스에서 Data Guard Broker에 등록된 정보를 확인할 수 있습니다.
B) Local listener 설정
서버별 TNS 설정에 맞게 적용
alter system set local_listener='<서비스 접속 정보>,<DG 전용 접속 정보>' scope=both; --alter system set local_listener='<서비스 tns명>,<DG 전용 tns명>' scope=both;
Primary 설정
SQL> alter system set local_listener='ORCL,SEOUL_SYN' scope=both; System altered. SQL> ALTER SYSTEM REGISTER; System altered. SQL> show parameter local_listener NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ local_listener string ORCL,SEOUL_SYN
Standby 설정
SQL> alter system set local_listener='ORCL_STBY,BUSAN_SYN' scope=both; System altered. SQL> ALTER SYSTEM REGISTER; System altered. SQL> show parameter local_listener NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ local_listener string ORCL_STBY,BUSAN_SYN
기존 설정된 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; new RMAN configuration parameters: CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY; new RMAN configuration parameters are successfully stored RMAN> SHOW ALL; RMAN configuration parameters for database with db_unique_name ORCL are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP ON; # default CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY; CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/19c/db_1/dbs/snapcf_orcl.f'; # default
Standby에 적용되지 않은 아카이브 로그가 삭제되어 데이터 손실이 발생하지 않도록 아카이브 삭제 정책을 수정합니다.
Primary와 Standby에서 각각 수행합니다.
VI. Switchover
1. Standby로 switchover
A) Primary에 DGMGRL로 접속
DGMGRL로 primary DB에 접속
dgmgrl sys/[sys 계정 암호]@[primary 네트워크 서비스명]
[oracle@orcl ~]$ export NLS_DATE_FORMAT="yyyy/mm/dd hh24:mi:ss" [oracle@orcl ~]$ dgmgrl sys/oracle@SEOUL_SYN DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Feb 13 15:09:01 2024 Version 19.11.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected to "orcl" Connected as SYSDBA.
{}
Primary에서 standby로 switchover하기 위해 DGMGRL로 접속합니다.
B) Standby로 switchover
Standby로 switchover 수행
SWITCHOVER TO [standby 글로벌 DB명];
DGMGRL> SWITCHOVER TO orcl_stby; Performing switchover NOW, please wait... Operation requires a connection to database "orcl_stby" Connecting ... Connected to "orcl_stby" Connected as SYSDBA. New primary database "orcl_stby" is opening... Operation requires start up of instance "orcl" on database "orcl" Starting instance "orcl"... Connected to an idle instance. ORACLE instance started. Connected to "orcl" Database mounted. Connected to "orcl" Switchover succeeded, new primary is "orcl_stby"
Switchover 명령어를 사용하여 standby로 데이터베이스를 switchover 합니다.
C) Switchover 결과 확인
Switchover 결과 확인
SHOW CONFIGURATION; SHOW DATABASE [primary 글로벌 DB명]; SHOW DATABASE [standby 글로벌 DB명];
DGMGRL> SHOW CONFIGURATION;
Configuration - dg_orcl
Protection Mode: MaxPerformance
Members:
orcl_stby - Primary database
orcl - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 50 seconds ago)
DGMGRL> SHOW DATABASE orcl;
Database - orcl
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: 3.02 MByte/s
Real Time Query: OFF
Instance(s):
orcl
Database Status:
SUCCESS
DGMGRL> SHOW DATABASE orcl_stby;
Database - orcl_stby
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
orcl
Database Status:
SUCCESS
Show 명령을 통해 DGMGRL에서 switchover 상태를 확인할 수 있습니다.
D) Switchover 후 데이터베이스 상태 확인
Primary DB와 Standby DB에서 수행
SELECT db_unique_name, open_mode, database_role, switchover_status FROM v$database;
SQL> set lines 200 SQL> col db_unique_name form a20 SQL> col open_mode form a20 SQL> col database_role form a20 SQL> col switchover_status form a20 SQL> SELECT db_unique_name, open_mode, database_role, switchover_status FROM v$database; DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS -------------------- -------------------- -------------------- -------------------- orcl_stby READ WRITE PRIMARY TO STANDBY
SQL> set lines 200 SQL> col db_unique_name form a20 SQL> col open_mode form a20 SQL> col database_role form a20 SQL> col switchover_status form a20 SQL> SELECT db_unique_name, open_mode, database_role, switchover_status FROM v$database; DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS -------------------- -------------------- -------------------- -------------------- orcl MOUNTED PHYSICAL STANDBY NOT ALLOWED
Primary와 standby 데이터베이스의 상태도 변경된 것을 v$database 뷰에서 확인할 수 있습니다.
2. Primary로 복원
A) Standby에 DGMGRL로 접속
Standby에 DGMGRL로 접속
dgmgrl sys/[sys 계정 암호]@[standby 네트워크 서비스명]
[oracle@orcl ~]$ dgmgrl sys/oracle@BUSAN_SYN DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Feb 13 15:27:12 2024 Version 19.11.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected to "orcl_stby" Connected as SYSDBA.
Standby에서 primary로 복원하기 위해 DGMGRL로 접속합니다.
B) Primary로 switchover
Primary로 switchover 수행
SWITCHOVER TO [primary 글로벌 DB명];
DGMGRL> SWITCHOVER TO orcl; Performing switchover NOW, please wait... Operation requires a connection to database "orcl" Connecting ... Connected to "orcl" Connected as SYSDBA. New primary database "orcl" is opening... Operation requires start up of instance "orcl" on database "orcl_stby" Starting instance "orcl"... Connected to an idle instance. ORACLE instance started. Connected to "orcl_stby" Database mounted. Connected to "orcl_stby" Switchover succeeded, new primary is "orcl"
Switchover 명령어를 사용하여 primary로 데이터베이스를 switchover 합니다.
C) 복원 결과 확인
복원 결과 확인
SHOW CONFIGURATION; SHOW DATABASE [primary 글로벌 DB명]; SHOW DATABASE [standby 글로벌 DB명];
DGMGRL> SHOW CONFIGURATION;
Configuration - dg_orcl
Protection Mode: MaxPerformance
Members:
orcl - Primary database
orcl_stby - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 52 seconds ago)
DGMGRL> SHOW DATABASE orcl;
Database - orcl
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
orcl
Database Status:
SUCCESS
DGMGRL> SHOW DATABASE orcl_stby;
Database - orcl_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: 3.91 MByte/s
Real Time Query: OFF
Instance(s):
orcl
Database Status:
SUCCESS
DGMGRL에서 Show 명령어로 복원된 결과를 확인할 수 있습니다.
D) 복원 후 데이터베이스 상태 확인
Primary DB와 Standby DB에서 수행
SELECT db_unique_name, open_mode, database_role, switchover_status FROM v$database;
SQL> set lines 200 SQL> col db_unique_name form a20 SQL> col open_mode form a20 SQL> col database_role form a20 SQL> col switchover_status form a20 SQL> SELECT db_unique_name, open_mode, database_role, switchover_status FROM v$database; DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS -------------------- -------------------- -------------------- -------------------- orcl READ WRITE PRIMARY TO STANDBY
SQL> set lines 200 SQL> col db_unique_name form a20 SQL> col open_mode form a20 SQL> col database_role form a20 SQL> col switchover_status form a20 SQL> SELECT db_unique_name, open_mode, database_role, switchover_status FROM v$database; DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS -------------------- -------------------- -------------------- -------------------- orcl_stby MOUNTED PHYSICAL STANDBY NOT ALLOWED
데이터베이스의 상태도 다시 원래대로 복원된 것을 v$database 뷰로 확인할 수 있습니다.
VII. Failover
1. Failover 수행
A) 플래시백 활성화
sys 유저로 primary와 standby에서 수행
ALTER DATABASE FLASHBACK ON; ALTER SYSTEM SWITCH LOGFILE; SELECT FLASHBACK_ON FROM V$DATABASE; -- ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=720 SCOPE=BOTH; -- SHOW PARAMETER DB_FLASHBACK_RETENTION_TARGET -- SELECT FLASHBACK_SIZE, ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;
SQL> ALTER DATABASE FLASHBACK ON;
Database altered.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> SELECT FLASHBACK_ON FROM V$DATABASE;
FLASHBACK_ON
------------------------------------------------------
YES
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=720 SCOPE=BOTH;
System altered.
SQL> SHOW PARAMETER DB_FLASHBACK_RETENTION_TARGET
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
db_flashback_retention_target integer 720
SQL> SELECT FLASHBACK_SIZE, ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;
FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------- ------------------------
629145600 130424832
{}
Failover 테스트를 수행한 후 원복을 수월하게 하기 위해서 flashback을 활성화시킵니다.
B) DGMGRL로 standby DB 접속
DGMGRL로 standby DB에 접속
dgmgrl sys/[sys 계정 암호]@[standby 네트워크 서비스명]
[oracle@orcl ~]$ dgmgrl sys/oracle@BUSAN_SYN DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Feb 14 09:38:31 2024 Version 19.11.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected to "orcl_stby" Connected as SYSDBA.
Primary에서 standby로 switchover하기 위해 DGMGRL로 접속합니다.
C) Standby로 failover 수행
Standby로 failover 수행
FAILOVER TO [standby 글로벌 DB명]; -- 또는 강제로 Primary 정지
DGMGRL> FAILOVER TO orcl_stby; Performing failover NOW, please wait... Failover succeeded, new primary is "orcl_stby"
Failover 명령으로 standby로 데이터베이스가 failover 되도록 작업을 수행합니다.
D) Failover 결과 확인
Failover 후 상태 확인
SHOW CONFIGURATION; SHOW DATABASE [primary 글로벌 DB명]; SHOW DATABASE [standby 글로벌 DB명];
DGMGRL> SHOW CONFIGURATION;
Configuration - dg_orcl
Protection Mode: MaxPerformance
Members:
orcl_stby - Primary database
orcl - Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 26 seconds ago)
DGMGRL> SHOW DATABASE orcl;
Database - orcl
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: (unknown)
Apply Lag: (unknown)
Average Apply Rate: (unknown)
Real Time Query: OFF
Instance(s):
orcl
Database Status:
DISABLED - ORA-16661: the standby database needs to be reinstated
DGMGRL> SHOW DATABASE orcl_stby;
Database - orcl_stby
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
orcl
Database Status:
SUCCESS
Failover가 정상적으로 완료되었는지 확인합니다.
기존의 Primary는 disabled로 복구가 필요한 상태입니다.
E) Failover 직후 데이터베이스 상태 확인
Primary DB와 Standby DB에서 수행
SELECT db_unique_name, open_mode, database_role, switchover_status FROM v$database;
[oracle@orcl ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 14 09:40:30 2024 Version 19.11.0.0.0 Copyright (c) 1982, 2020, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.11.0.0.0 SQL> set lines 200 SQL> col db_unique_name form a20 SQL> col open_mode form a20 SQL> col database_role form a20 SQL> col switchover_status form a20 SQL> SELECT db_unique_name, open_mode, database_role, switchover_status FROM v$database; DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS -------------------- -------------------- -------------------- -------------------- orcl READ WRITE PRIMARY NOT ALLOWED
[oracle@orcl ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 14 09:41:04 2024 Version 19.11.0.0.0 Copyright (c) 1982, 2020, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.11.0.0.0 SQL> set lines 200 SQL> col db_unique_name form a20 SQL> col open_mode form a20 SQL> col database_role form a20 SQL> col switchover_status form a20 SQL> SELECT db_unique_name, open_mode, database_role, switchover_status FROM v$database; DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS -------------------- -------------------- -------------------- -------------------- orcl_stby READ WRITE PRIMARY NOT ALLOWED
각 데이터베이스의 상태를 살펴보면 모두 primary로 되어 있는 것을 확인할 수 있습니다.
Failover 이전의 primary는 현재 비정상적이므로 standby에서의 switchover는 now allowed 상태로 되어 있습니다.
2. Primary 데이터베이스 reinstate 수행
A) Reinstate 수행 및 결과 확인
Primary DB의 Reinstate 수행 및 결과 확인
REINSTATE DATABASE [primary 글로벌 DB명]; SHOW CONFIGURATION; SHOW DATABASE [primary 글로벌 DB명];
DGMGRL> REINSTATE DATABASE orcl;
Reinstating database "orcl", please wait...
Operation requires shut down of instance "orcl" on database "orcl"
Shutting down instance "orcl"...
Connected to "orcl"
ORACLE instance shut down.
Operation requires start up of instance "orcl" on database "orcl"
Starting instance "orcl"...
Connected to an idle instance.
ORACLE instance started.
Connected to "orcl"
Database mounted.
Connected to "orcl"
Continuing to reinstate database "orcl" ...
Reinstatement of database "orcl" succeeded
DGMGRL> SHOW CONFIGURATION;
Configuration - dg_orcl
Protection Mode: MaxPerformance
Members:
orcl_stby - Primary database
orcl - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 26 seconds ago)
DGMGRL> SHOW DATABASE orcl;
Database - orcl
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: 751.00 KByte/s
Real Time Query: OFF
Instance(s):
orcl
Database Status:
SUCCESS
Flashback Database가 활성화 되어 있었다면, reinstate 명령어로 간단하게 복구할 수 있습니다.
B) Reinstate 후 데이터베이스 상태 확인
Primary DB와 Standby DB에서 수행
SELECT db_unique_name, open_mode, database_role, switchover_status FROM v$database;
[oracle@orcl ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 14 09:45:02 2024 Version 19.11.0.0.0 Copyright (c) 1982, 2020, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.11.0.0.0 SQL> set lines 200 SQL> col db_unique_name form a20 SQL> col open_mode form a20 SQL> col database_role form a20 SQL> col switchover_status form a20 SQL> SELECT db_unique_name, open_mode, database_role, switchover_status FROM v$database; DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS -------------------- -------------------- -------------------- -------------------- orcl MOUNTED PHYSICAL STANDBY NOT ALLOWED
SQL> set lines 200 SQL> col db_unique_name form a20 SQL> col open_mode form a20 SQL> col database_role form a20 SQL> col switchover_status form a20 SQL> SELECT db_unique_name, open_mode, database_role, switchover_status FROM v$database; DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS -------------------- -------------------- -------------------- -------------------- orcl_stby READ WRITE PRIMARY TO STANDBY
데이터베이스의 상태를 살펴보면 primary와 standby의 역할이 정상으로 되어 있는 것을 확인할 수 있습니다.
3. Primary로 복원
A) Primary로 switchover
Primary로 switchover 수행
SWITCHOVER TO [primary 글로벌 DB명];
DGMGRL> SWITCHOVER TO orcl; Performing switchover NOW, please wait... Operation requires a connection to database "orcl" Connecting ... Connected to "orcl" Connected as SYSDBA. New primary database "orcl" is opening... Operation requires start up of instance "orcl" on database "orcl_stby" Starting instance "orcl"... Connected to an idle instance. ORACLE instance started. Connected to "orcl_stby" Database mounted. Connected to "orcl_stby" Switchover succeeded, new primary is "orcl"
복구가 된 primary로 switchover를 수행할 수 있습니다.
B) 복원 결과 확인
복원 결과 확인
SHOW CONFIGURATION; SHOW DATABASE [primary 글로벌 DB명]; SHOW DATABASE [standby 글로벌 DB명];
DGMGRL> SHOW CONFIGURATION;
Configuration - dg_orcl
Protection Mode: MaxPerformance
Members:
orcl - Primary database
orcl_stby - Physical standby database
Error: ORA-16786: unable to access Oracle Data Guard broker configuration files
Fast-Start Failover: Disabled
Configuration Status:
ERROR (status updated 32 seconds ago)
DGMGRL> SHOW CONFIGURATION;
Configuration - dg_orcl
Protection Mode: MaxPerformance
Members:
orcl - Primary database
orcl_stby - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 6 seconds ago)
DGMGRL> SHOW DATABASE orcl;
Database - orcl
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
orcl
Database Status:
SUCCESS
DGMGRL> SHOW DATABASE orcl_stby;
Database - orcl_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: 159.00 KByte/s
Real Time Query: OFF
Instance(s):
orcl
Database Status:
SUCCESS
정상적으로 switchover 되었는지 확인할 수 있습니다.
C) 복원 후 데이터베이스 상태 확인
Primary DB와 Standby DB에서 수행
SELECT db_unique_name, open_mode, database_role, switchover_status FROM v$database;
[oracle@orcl ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 14 09:49:43 2024 Version 19.11.0.0.0 Copyright (c) 1982, 2020, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.11.0.0.0 SQL> set lines 200 SQL> col db_unique_name form a20 SQL> col open_mode form a20 SQL> col database_role form a20 SQL> col switchover_status form a20 SQL> SELECT db_unique_name, open_mode, database_role, switchover_status FROM v$database; DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS -------------------- -------------------- -------------------- -------------------- orcl READ WRITE PRIMARY TO STANDBY
[oracle@orcl ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 14 09:49:53 2024 Version 19.11.0.0.0 Copyright (c) 1982, 2020, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.11.0.0.0 SQL> set lines 200 SQL> col db_unique_name form a20 SQL> col open_mode form a20 SQL> col database_role form a20 SQL> col switchover_status form a20 SQL> SELECT db_unique_name, open_mode, database_role, switchover_status FROM v$database; DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS -------------------- -------------------- -------------------- -------------------- orcl_stby MOUNTED PHYSICAL STANDBY NOT ALLOWED
원래의 상태로 복구된 것을 확인할 수 있습니다.
VIII. Data Guard 기동 및 정지
1. Data Guard 정지
A) Maximum Performance 적용 (옵션)
기본 보호 모드인 Maximum Performance 적용 (옵션)
EDIT CONFIGURATION SET PROTECTION MODE AS MAXPERFORMANCE; SHOW CONFIGURATION;
DGMGRL> SHOW CONFIGURATION;
Configuration - dg_orcl
Protection Mode: MaxPerformance
Members:
orcl - Primary database
orcl_stby - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 29 seconds ago)
{}
B) Data Guard Broker 중지
Redo 이동 및 적용을 중지하고 Broker 중지
EDIT DATABASE [primary 글로벌 DB명] SET STATE=transport-off; EDIT DATABASE [standby 글로벌 DB명] SET STATE=apply-off; DISABLE CONFIGURATION
DGMGRL> EDIT DATABASE orcl SET STATE=transport-off;
Succeeded.
DGMGRL> EDIT DATABASE orcl_stby SET STATE=apply-off;
Succeeded.
DGMGRL> DISABLE CONFIGURATION
Disabled.
DGMGRL> SHOW CONFIGURATION;
Configuration - dg_orcl
Protection Mode: MaxPerformance
Members:
orcl - Primary database
orcl_stby - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
DISABLED
DGMGRL> SHOW DATABASE orcl;
Database - orcl
Role: PRIMARY
Intended State: TRANSPORT-OFF
Instance(s):
orcl
Database Status:
DISABLED
DGMGRL> SHOW DATABASE orcl_stby;
Database - orcl_stby
Role: PHYSICAL STANDBY
Intended State: APPLY-OFF
Transport Lag: (unknown)
Apply Lag: (unknown)
Average Apply Rate: (unknown)
Real Time Query: OFF
Instance(s):
orcl
Database Status:
DISABLED
C) 데이터베이스 중지
Primary 또는 Standby 중지 (모두 중지 가능)
SHUTDOWN IMMEDIATE
SQL> SHUTDOWN IMMEDIATE ORA-01109: database not open Database dismounted. ORACLE instance shut down.
SQL> SHUTDOWN IMMEDIATE Database closed. Database dismounted. ORACLE instance shut down.
먼저 standby database부터 중지합니다.
2. Data Guard 기동
A) Primary 데이터베이스 기동
Primary 데이터베이스 기동
STARTUP
[oracle@orcl ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 14 09:25:57 2024 Version 19.11.0.0.0 Copyright (c) 1982, 2020, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 1258290744 bytes Fixed Size 8896056 bytes Variable Size 318767104 bytes Database Buffers 922746880 bytes Redo Buffers 7880704 bytes Database mounted. Database opened.
B) Standby 데이터베이스 기동
Standby 데이터베이스 기동 (ADG의 경우 read only open)
STARTUP MOUNT -- ALTER DATABASE OPEN READ ONLY;
[oracle@orcl ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 14 09:26:35 2024 Version 19.11.0.0.0 Copyright (c) 1982, 2020, Oracle. All rights reserved. Connected to an idle instance. SQL> startup mount ORACLE instance started. Total System Global Area 1258290744 bytes Fixed Size 8896056 bytes Variable Size 318767104 bytes Database Buffers 922746880 bytes Redo Buffers 7880704 bytes Database mounted.
C) Data Guard Broker 기동
Broker 및 Redo 적용, 이전 활성화
ENABLE CONFIGURATION EDIT DATABASE [standby 글로벌 DB명] SET STATE=apply-on; EDIT DATABASE [primary 글로벌 DB명] SET STATE=transport-on;
[oracle@orcl ~]$ dgmgrl / as sysdba
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Feb 14 09:27:37 2024
Version 19.11.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "orcl"
Connected as SYSDBA.
DGMGRL> ENABLE CONFIGURATION
Enabled.
DGMGRL> SHOW CONFIGURATION
Configuration - dg_orcl
Protection Mode: MaxPerformance
Members:
orcl - Primary database
orcl_stby - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 7 seconds ago)
DGMGRL> EDIT DATABASE orcl_stby SET STATE=apply-on;
Succeeded.
DGMGRL> EDIT DATABASE orcl SET STATE=transport-on;
Succeeded.
DGMGRL> SHOW DATABASE orcl
Database - orcl
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
orcl
Database Status:
SUCCESS
DGMGRL> SHOW DATABASE orcl_stby
Database - orcl_stby
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 384.00 KByte/s
Real Time Query: OFF
Instance(s):
orcl
Database Status:
SUCCESS
IX. Active Data Guard 활성화
1. Standby를 Open 모드로 전환
A) Primary의 current redo를 archive (옵션)
Primary에서 아카이브 수행
ALTER SYSTEM ARCHIVE LOG CURRENT;
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT; System altered.
{}
B) 로그 적용 중지
DGMGRL 에서 standby 로그 적용 중지
EDIT DATABASE [standby 글로벌 DB명] SET STATE=apply-off;
DGMGRL> EDIT DATABASE orcl_stby SET STATE=apply-off;
Succeeded.
DGMGRL> SHOW DATABASE orcl_stby;
Database - orcl_stby
Role: PHYSICAL STANDBY
Intended State: APPLY-OFF
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: (unknown)
Real Time Query: OFF
Instance(s):
orcl
Database Status:
SUCCESS
SQL*Plus 에서 수행할 경우 아래 구문으로 수행합니다.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
C) Standby 데이터베이스를 open
standby 데이터베이스를 open
ALTER DATABASE OPEN;
SQL> set lines 200 SQL> col db_unique_name form a20 SQL> col open_mode form a20 SQL> col database_role form a20 SQL> col switchover_status form a20 SQL> SELECT db_unique_name, open_mode, database_role, switchover_status FROM v$database; DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS -------------------- -------------------- -------------------- -------------------- orcl_stby MOUNTED PHYSICAL STANDBY NOT ALLOWED SQL> ALTER DATABASE OPEN; Database altered. SQL> SELECT db_unique_name, open_mode, database_role, switchover_status FROM v$database; DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS -------------------- -------------------- -------------------- -------------------- orcl_stby READ ONLY PHYSICAL STANDBY NOT ALLOWED
D) 로그 적용 활성화
DGMGRL 에서 standby 로그 적용 활성화
EDIT DATABASE [standby 글로벌 DB명] SET STATE=apply-on;
DGMGRL> EDIT DATABASE orcl_stby SET STATE=apply-on;
Succeeded.
DGMGRL> SHOW DATABASE orcl_stby;
Database - orcl_stby
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 18.00 KByte/s
Real Time Query: OFF
Instance(s):
orcl
Database Status:
SUCCESS
SQL*Plus 에서 수행할 경우 아래 구문으로 수행합니다.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
E) 결과 확인
Standby의 Open Mode 변경 확인
SHOW CONFIGURATION;
DGMGRL> SHOW CONFIGURATION;
Configuration - dg_orcl
Protection Mode: MaxPerformance
Members:
orcl - Primary database
orcl_stby - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 38 seconds ago)
X. Snapshot Standby Database
1. Snapshot Standby Database로 전환
A) Standby DB를 snapshot standby로 전환
Convert database 명령으로 역할 전환
CONVERT DATABASE [standby 글로벌 DB명] TO SNAPSHOT STANDBY;
DGMGRL> SHOW CONFIGURATION;
Configuration - dg_orcl
Protection Mode: MaxPerformance
Members:
orcl - Primary database
orcl_stby - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 8 seconds ago)
DGMGRL> CONVERT DATABASE 'orcl_stby' TO SNAPSHOT STANDBY;
Converting database "orcl_stby" to a Snapshot Standby database, please wait...
Database "orcl_stby" converted successfully
DGMGRL> SHOW CONFIGURATION;
Configuration - dg_orcl
Protection Mode: MaxPerformance
Members:
orcl - Primary database
orcl_stby - Snapshot standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 50 seconds ago)
Standby database가 snapshot standby로 롤이 변경된 것을 확인할 수 있습니다.
SQL> SELECT db_unique_name, instance_name, status, open_mode, database_role, switchover_status FROM v$database d, v$instance i; DB_UNIQUE_NAME INSTANCE_NAME STATUS OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS -------------------- -------------------- --------------- -------------------- -------------------- -------------------- orcl_stby orcl OPEN READ WRITE SNAPSHOT STANDBY NOT ALLOWED
{}
2. Standby Database 테스트
A) 기존 데이터 존재 여부 확인
Primary DB에 테스트 유저 존재 여부 확인
select username from dba_users where username = 'SNAPSHOT';
SQL> SELECT db_unique_name, instance_name, status, open_mode, database_role, switchover_status FROM v$database d, v$instance i; DB_UNIQUE_NAME INSTANCE_NAME STATUS OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS -------------------- -------------------- --------------- -------------------- -------------------- -------------------- orcl orcl OPEN READ WRITE PRIMARY FAILED DESTINATION SQL> select username from dba_users where username = 'SNAPSHOT'; no rows selected
Snapshot Standby Database 기능을 테스트하기 위한 계정은 standby 데이터베이스에만 존재하면 됩니다.
B) Standby DB에서 테스트 데이터 생성
Standby DB에 유저 및 테이블 생성 후 테스트 데이터 입력
create user snapshot identified by snapshot;
GRANT dba TO snapshot;
CREATE TABLE SNAPSHOT.SNAP_TEST (id varchar2(5), name varchar2(20));
INSERT INTO SNAPSHOT.SNAP_TEST VALUES ('001','scott');
COMMIT:
select * from SNAPSHOT.SNAP_TEST;
SQL> SELECT db_unique_name, instance_name, status, open_mode, database_role, switchover_status FROM v$database d, v$instance i;
DB_UNIQUE_NAME INSTANCE_NAME STATUS OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- -------------------- --------------- -------------------- -------------------- --------------------
orcl_stby orcl OPEN READ WRITE SNAPSHOT STANDBY NOT ALLOWED
SQL> CREATE USER snapshot IDENTIFIED BY snapshot;
User created.
SQL> GRANT dba TO snapshot;
Grant succeeded.
SQL> CREATE TABLE snapshot.snap_test (id VARCHAR2(5), name VARCHAR2(20));
Table created.
SQL> INSERT INTO snapshot.snap_test VALUES ('001','scott');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from SNAPSHOT.SNAP_TEST;
ID NAME
----- --------------------
001 scott
Standby 데이터베이스에 테스트를 위한 계정을 만들고 권한을 부여한 후에 테스트를 위한 테이블과 데이터를 입력합니다.
이 테이블은 Standby 데이터에서만 조회되어야 합니다.
C) Primady DB에서 조회 불가 확인
Primary DB에서 테스트 테이블 조회 여부 확인
select * from SNAPSHOT.SNAP_TEST;
SQL> SELECT db_unique_name, instance_name, status, open_mode, database_role, switchover_status FROM v$database d, v$instance i;
DB_UNIQUE_NAME INSTANCE_NAME STATUS OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- -------------------- --------------- -------------------- -------------------- --------------------
orcl orcl OPEN READ WRITE PRIMARY TO STANDBY
SQL> select * from SNAPSHOT.SNAP_TEST;
select * from SNAPSHOT.SNAP_TEST
*
ERROR at line 1:
ORA-00942: table or view does not exist
Snapshot Standby Database에만 생성된 테이블이므로 Primary DB에서는 테스트 데이터를 조회할 수 없습니다.
3. Physical Standby Database로 복원
A) Standby DB를 Physical Standby로 복원
Convert database 명령으로 역할 전환
CONVERT DATABASE [standby 글로벌 DB명] TO PHYSICAL STANDBY;
DGMGRL> CONVERT DATABASE 'orcl_stby' TO PHYSICAL STANDBY;
Converting database "orcl_stby" to a Physical Standby database, please wait...
Operation requires shut down of instance "orcl" on database "orcl_stby"
Shutting down instance "orcl"...
Connected to "orcl_stby"
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires start up of instance "orcl" on database "orcl_stby"
Starting instance "orcl"...
Connected to an idle instance.
ORACLE instance started.
Connected to "orcl_stby"
Database mounted.
Connected to "orcl_stby"
Continuing to convert database "orcl_stby" ...
Database "orcl_stby" converted successfully
DGMGRL> SHOW CONFIGURATION
Configuration - dg_orcl
Protection Mode: MaxPerformance
Members:
orcl - Primary database
orcl_stby - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 23 seconds ago)
Physical Standby로 전환하면서 Snapshot Standby 모드에서 생성된 테스트 테이블과 유저가 제거된 것을 확인할 수 있습니다.
SQL> SELECT db_unique_name, instance_name, status, open_mode, database_role, switchover_status FROM v$database d, v$instance i;
DB_UNIQUE_NAME INSTANCE_NAME STATUS OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- -------------------- --------------- -------------------- -------------------- --------------------
orcl_stby orcl OPEN READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED
SQL> select * from SNAPSHOT.SNAP_TEST;
select * from SNAPSHOT.SNAP_TEST
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select username from dba_users where username = 'SNAPSHOT';
no rows selected
XI. Fast-Start Failover
1. FSFO 사전 준비
A) Wallet 설정
Primary에서 wallet 생성 및 접속 정보 추가
mkstore -wrl [wallet 파일 경로] -create mkstore -wrl [wallet 파일 경로] -createCredential '[primary 네트워크 서비스명]' sys [sys 계정 암호] mkstore -wrl [wallet 파일 경로] -createCredential '[standby 네트워크 서비스명]' sys [sys 계정 암호]
[oracle@seoul:~] $ mkstore -wrl /u01/app/oracle/wallet/ -create Oracle Secret Store Tool Release 19.0.0.0.0 - Production Version 19.4.0.0.0 Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved. Enter password: [wallet 암호 입력 예 : oracle_4U] Enter password again: [wallet 암호 입력 예 : oracle_4U] [oracle@seoul:~] $ mkstore -wrl /u01/app/oracle/wallet/ -createCredential 'seoul_syn' sys oracle Oracle Secret Store Tool Release 19.0.0.0.0 - Production Version 19.4.0.0.0 Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved. Enter wallet password: [wallet 암호 입력 예 : oracle_4U] [oracle@seoul:~] $ mkstore -wrl /u01/app/oracle/wallet/ -createCredential 'busan_syn' sys oracle Oracle Secret Store Tool Release 19.0.0.0.0 - Production Version 19.4.0.0.0 Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved. Enter wallet password: [wallet 암호 입력 예 : oracle_4U]
Fast-Start Failover(이하 FSFO) 모드를 활성화하려면 observer 기동이 필요하며, 이를 백그라운드에서 실행하기 위해 wallet을 설정해야 합니다. (또는 nohup을 이용해 실행)
Wallet 파일 Standby로 전송 복제
scp -r [wallet 파일 경로] oracle@[standby 호스트명 또는 IP]:[wallet 파일 경로]
[oracle@seoul:~] $ scp -r /u01/app/oracle/wallet/ oracle@busan_dg:/u01/app/oracle/ oracle@busan_dg's password: ewallet.p12.lck 100% 0 0.0KB/s 00:00 ewallet.p12 100% 904 642.5KB/s 00:00 cwallet.sso.lck 100% 0 0.0KB/s 00:00 cwallet.sso 100% 949 709.6KB/s 00:00
Paimary에서 생성된 wallet 파일을 standby로 전송합니다.
B) sqlnet.ora 설정
Primary와 Standby에 sqlnet.ora 생성 또는 추가
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME,EZCONNECT) WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=[wallet 파일 경로]))) SQLNET.WALLET_OVERRIDE=TRUE
Primary 서버 파일
[oracle@seoul:~]$ cat > $ORACLE_HOME/network/admin/sqlnet.ora NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME,EZCONNECT) WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/wallet/))) SQLNET.WALLET_OVERRIDE=TRUE ^C [oracle@seoul:~]$ ls $ORACLE_HOME/network/admin/ listener.ora samples shrept.lst sqlnet.ora tnsnames.ora
Standby 서버 파일
[oracle@busan:~]$ cat > $ORACLE_HOME/network/admin/sqlnet.ora NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME,EZCONNECT) WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/wallet/))) SQLNET.WALLET_OVERRIDE=TRUE ^C [oracle@busan:~]$ ls $ORACLE_HOME/network/admin/ listener.ora samples shrept.lst sqlnet.ora tnsnames.ora
기존에 sqlnet.ora 파일이 있을 경우에는 변경된 부분은 수정하고, 없는 부분은 추가합니다.
C) 리스너 재기동
모든 리스너 재기동
lsnrctl reload [리스너명]
Primary 서버의 리스너
[oracle@seoul:~] $ lsnrctl reload LISTENER_DG LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 21-FEB-2024 17:16:26 Copyright (c) 1991, 2021, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=seoul_dg)(PORT=1521))) The command completed successfully [oracle@seoul:~] $ lsnrctl reload LISTENER_ORCL LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 21-FEB-2024 17:16:35 Copyright (c) 1991, 2021, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=seoul)(PORT=1525))) The command completed successfully
Standby 서버의 리스너
[oracle@busan:~] $ lsnrctl reload LISTENER_DG LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 21-FEB-2024 17:20:32 Copyright (c) 1991, 2021, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=busan_dg)(PORT=1521))) The command completed successfully [oracle@busan:~] $ lsnrctl reload LISTENER_ORCL LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 21-FEB-2024 17:20:37 Copyright (c) 1991, 2021, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=busan)(PORT=1525))) The command completed successfully
2. FSFO 활성화
A) Observer 기동
Observer 백그라운드 모드로 기동 및 확인
START OBSERVER [observer명] IN BACKGROUND LOGFILE IS '[로그 파일 경로 및 이름]' CONNECT IDENTIFIER IS [네트워크 서비스명]; SHOW OBSERVER
Primary observer 실행
[oracle@seoul:~]$ dgmgrl sys/oracle@seoul_syn DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Feb 22 14:19:34 2024 Version 19.11.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected to "orcl" Connected as SYSDBA. DGMGRL> START OBSERVER ob_pri IN BACKGROUND LOGFILE IS '/u01/observer.log' CONNECT IDENTIFIER IS seoul_syn; Connected to "orcl" Submitted command "START OBSERVER" using connect identifier "seoul_syn"
Standby observer 실행
[oracle@busan:~]$ dgmgrl sys/oracle@busan_syn DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Feb 22 14:20:35 2024 Version 19.11.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected to "orcl_stby" Connected as SYSDBA. DGMGRL> START OBSERVER ob_std IN BACKGROUND LOGFILE IS '/u01/observer.log' CONNECT IDENTIFIER IS busan_syn; Connected to "orcl_stby" Submitted command "START OBSERVER" using connect identifier "busan_syn" DGMGRL> SHOW OBSERVER Configuration - dg_orcl Fast-Start Failover: DISABLED Observer "ob_pri" Host Name: seoul Last Ping to Primary: 8 seconds ago Observer "ob_std" Host Name: busan Last Ping to Primary: 6 seconds ago
백그라운드 모드로 observer를 실행하며, 로그가 별도로 지정된 경로에 쌓이도록 설정합니다.
B) FSFO 관련 설정 적용
Fast-Start Failover 파라미터 설정 및 확인
SHOW FAST_START FAILOVER EDIT DATABASE [primary 글로벌 DB명] SET PROPERTY FastStartFailoverTarget = '[standby 글로벌 DB명]'; SHOW DATABASE [primary 글로벌 DB명] FastStartFailoverTarget EDIT DATABASE [standby 글로벌 DB명] SET PROPERTY FastStartFailoverTarget = '[primary 글로벌 DB명]'; SHOW DATABASE [standby 글로벌 DB명] FastStartFailoverTarget EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold = 30; SHOW CONFIGURATION FastStartFailoverThreshold EDIT CONFIGURATION SET PROPERTY FastStartFailoverLagLimit = 30; SHOW CONFIGURATION FastStartFailoverLagLimit
DGMGRL> SHOW FAST_START FAILOVER
Fast-Start Failover: Disabled
Protection Mode: MaxPerformance
Lag Limit: 30 seconds
Threshold: 30 seconds
Active Target: (none)
Potential Targets: (none)
Observers: ob_pri
ob_std
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)
DGMGRL> SHOW DATABASE orcl FastStartFailoverTarget
FastStartFailoverTarget = ''
DGMGRL> EDIT DATABASE orcl SET PROPERTY FastStartFailoverTarget = 'orcl_stby';
Property "faststartfailovertarget" updated
DGMGRL> SHOW DATABASE orcl FastStartFailoverTarget
FastStartFailoverTarget = 'orcl_stby'
DGMGRL> SHOW DATABASE orcl_stby FastStartFailoverTarget
FastStartFailoverTarget = ''
DGMGRL> EDIT DATABASE orcl_stby SET PROPERTY FastStartFailoverTarget = 'orcl';
Property "faststartfailovertarget" updated
DGMGRL> SHOW DATABASE orcl_stby FastStartFailoverTarget
FastStartFailoverTarget = 'orcl'
DGMGRL> SHOW CONFIGURATION FastStartFailoverThreshold
FastStartFailoverThreshold = '30'
DGMGRL> SHOW CONFIGURATION FastStartFailoverLagLimit
FastStartFailoverLagLimit = '30'
DGMGRL> SHOW FAST_START FAILOVER
Fast-Start Failover: Disabled
Protection Mode: MaxPerformance
Lag Limit: 30 seconds
Threshold: 30 seconds
Active Target: (none)
Potential Targets: "orcl_stby"
orcl_stby valid
Observers: ob_pri
ob_std
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)
FSFO의 target을 설정합니다.
C) Max Performance 모드에서 FSFO 활성화
기본 Max Performance 모드에서 Fast-Start Failover 활성화
ENABLE FAST_START FAILOVER; SHOW FAST_START FAILOVER SHOW OBSERVER
DGMGRL> ENABLE FAST_START FAILOVER;
Enabled in Potential Data Loss Mode.
DGMGRL> SHOW FAST_START FAILOVER
Fast-Start Failover: Enabled in Potential Data Loss Mode
Protection Mode: MaxPerformance
Lag Limit: 30 seconds
Threshold: 30 seconds
Active Target: orcl_stby
Potential Targets: "orcl_stby"
orcl_stby valid
Observers: (*) ob_pri
ob_std
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)
DGMGRL> SHOW OBSERVER
Configuration - dg_orcl
Primary: orcl
Active Target: orcl_stby
Observer "ob_pri" - Master
Host Name: seoul
Last Ping to Primary: 3 seconds ago
Last Ping to Target: 2 seconds ago
Observer "ob_std" - Backup
Host Name: busan
Last Ping to Primary: 3 seconds ago
Last Ping to Target: 2 seconds ago
DGMGRL> SHOW CONFIGURATION
Configuration - dg_orcl
Protection Mode: MaxPerformance
Members:
orcl - Primary database
orcl_stby - (*) Physical standby database
Fast-Start Failover: Enabled in Potential Data Loss Mode
Configuration Status:
SUCCESS (status updated 59 seconds ago)
기본 Max Performance 모드에서 FSFO를 활성화하면 Potentila Data Loss Mode로 활성화 됩니다. 로그 전송 지연이 발생할 경우, 데이터의 손실이 발생할 수도 있습니다.
D) Max Availability 모드로 전환
Max Avaliability 모드로 Fast-Start Failover 활성화
DISABLE FAST_START FAILOVER; EDIT DATABASE [primary 글로벌 DB명] SET PROPERTY 'LogXptMode'='SYNC'; SHOW DATABASE [primary 글로벌 DB명] LogXptMode; EDIT DATABASE [standby 글로벌 DB명] SET PROPERTY 'LogXptMode'='SYNC'; SHOW DATABASE [standby 글로벌 DB명] LogXptMode; EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability; ENABLE FAST_START FAILOVER; SHOW CONFIGURATION
기본 설정에서 바로 MaxAvailability 모드로 변경할 수 없습니다.
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability; Error: ORA-16654: fast-start failover is enabled Failed.
로그 동기화 설정을 변경(ASYNC => SYNC)한 후에 모드 변경이 가능합니다.
DGMGRL> DISABLE FAST_START FAILOVER;
Disabled.
DGMGRL> SHOW DATABASE orcl LogXptMode;
LogXptMode = 'ASYNC'
DGMGRL> SHOW DATABASE orcl_stby LogXptMode;
LogXptMode = 'ASYNC'
DGMGRL> EDIT DATABASE orcl SET PROPERTY 'LogXptMode'='SYNC';
Property "LogXptMode" updated
DGMGRL> EDIT DATABASE orcl_stby SET PROPERTY 'LogXptMode'='SYNC';
Property "LogXptMode" updated
DGMGRL> SHOW DATABASE orcl LogXptMode;
LogXptMode = 'SYNC'
DGMGRL> SHOW DATABASE orcl_stby LogXptMode;
LogXptMode = 'SYNC'
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;
Succeeded.
DGMGRL> ENABLE FAST_START FAILOVER;
Enabled in Zero Data Loss Mode.
DGMGRL> SHOW CONFIGURATION
Configuration - dg_orcl
Protection Mode: MaxAvailability
Members:
orcl - Primary database
orcl_stby - (*) Physical standby database
Fast-Start Failover: Enabled in Zero Data Loss Mode
Configuration Status:
SUCCESS (status updated 38 seconds ago)
데이터 손실을 최소화하기 위해 Max Avaliability 모드로 전환하려면 Log 전송 모드를 ASYNC에서 SYNC로 바꿔야 합니다.
이후에 FSFO를 활성화하면 Zero Data Lose 모드로 시작됩니다.
3. FSFO 끄기
A) FSFO 비활성화 및 기본 Max Performance 모드로 원복
Fast-Start Failover 비활성화 및 Max Performance 모드로 원복
DISABLE FAST_START FAILOVER; EDIT CONFIGURATION SET PROTECTION MODE AS MaxPerformance; EDIT DATABASE [primary 글로벌 DB명] SET PROPERTY 'LogXptMode'='ASYNC'; SHOW DATABASE [primary 글로벌 DB명] LogXptMode EDIT DATABASE [standby 글로벌 DB명] SET PROPERTY 'LogXptMode'='ASYNC'; SHOW DATABASE [standby 글로벌 DB명] LogXptMode STOP OBSERVER ALL;
DGMGRL> DISABLE FAST_START FAILOVER;
Disabled.
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxPerformance;
Succeeded.
DGMGRL> EDIT DATABASE orcl SET PROPERTY 'LogXptMode'='ASYNC';
Property "LogXptMode" updated
DGMGRL> SHOW DATABASE orcl LogXptMode
LogXptMode = 'ASYNC'
DGMGRL> EDIT DATABASE orcl_stby SET PROPERTY 'LogXptMode'='ASYNC';
Property "LogXptMode" updated
DGMGRL> SHOW DATABASE orcl_stby LogXptMode
LogXptMode = 'ASYNC'
DGMGRL> STOP OBSERVER ALL;
Observer stopped.
DGMGRL> SHOW OBSERVER
Configuration - dg_orcl
Fast-Start Failover: DISABLED
No observers.
DGMGRL> SHOW CONFIGURATION
Configuration - dg_orcl
Protection Mode: MaxPerformance
Members:
orcl - Primary database
orcl_stby - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 20 seconds ago)
{}
{}
