I. 네트워크 설정
1. 네트워크 정보 수정
A) hosts 파일 수정
root 계정으로 vi /etc/hosts 수행
[primary 1번 DG 전용 IP] [primary 1번 DG 전용 호스트명] [primary 2번 DG 전용 IP] [primary 2번 DG 전용 호스트명] [primary 1번 DG 전용 VIP] [primary 1번 DG 전용 VIP명] [primary 2번 DG 전용 VIP] [primary 2번 DG 전용 VIP명] [standby DG 전용 IP] [standby DG 전용 호스트명]
### Public 10.0.1.101 seoul1 seoul1.localdomain 10.0.1.102 seoul2 seoul2.localdomain ### Private 10.0.5.101 seoul1-priv seoul1-priv.localdomain 10.0.5.102 seoul2-priv seoul2-priv.localdomain ### Virtual 10.0.1.201 seoul1-vip seoul1-vip.localdomain 10.0.1.202 seoul2-vip seoul2-vip.localdomain ### SCAN 10.0.1.180 seoul-scan seoul-scan.localdomain ### STANDBY 10.0.1.105 busan ### DG 10.0.7.101 seoul1-dg seoul1-dg.localdomain 10.0.7.102 seoul2-dg seoul2-dg.localdomain 10.0.7.105 busan-dg ### DG Virtual 10.0.7.201 seoul1-dgv seoul1-dgv.localdomain 10.0.7.202 seoul2-dgv seoul2-dgv.localdomain
2. 클러스터 리소스 추가
A) RAC(Primary)에 Data Guard 전용 network 객체 추가
클러스터에 Data Guard 전용 네트워크 객체 추가
srvctl add network -k [네트워크 번호] -S [DG 전용 IP C클래스 정보]/255.255.255.0/[DG 전용 NIC명] srvctl config network -netnum [네트워크 번호]
[root@seoul1 ~]# srvctl add network -k 2 -S 10.0.7.0/255.255.255.0/ens38 [root@seoul1 ~]# srvctl config network -netnum 2 2 네트워크가 존재합니다. 서브넷 IPv4: 10.0.7.0/255.255.255.0/ens38, static 서브넷 IPv6: 핑 대상: 네트워크가 사용으로 설정되었습니다. 네트워크가 노드에서 개별적으로 사용으로 설정됨: 네트워크가 노드에서 개별적으로 사용 안함으로 설정됨:
[root@seoul1 ~]# crsctl stat res -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
ONLINE ONLINE seoul1 STABLE
ONLINE ONLINE seoul2 STABLE
ora.chad
ONLINE ONLINE seoul1 STABLE
ONLINE ONLINE seoul2 STABLE
ora.net1.network
ONLINE ONLINE seoul1 STABLE
ONLINE ONLINE seoul2 STABLE
ora.net2.network
OFFLINE OFFLINE seoul1 STABLE
OFFLINE OFFLINE seoul2 STABLE
ora.ons
ONLINE ONLINE seoul1 STABLE
ONLINE ONLINE seoul2 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
1 ONLINE ONLINE seoul1 STABLE
2 ONLINE ONLINE seoul2 STABLE
ora.CRS.dg(ora.asmgroup)
1 ONLINE ONLINE seoul1 STABLE
2 ONLINE ONLINE seoul2 STABLE
ora.DATA.dg(ora.asmgroup)
1 ONLINE ONLINE seoul1 STABLE
2 ONLINE ONLINE seoul2 STABLE
ora.FRA.dg(ora.asmgroup)
1 ONLINE ONLINE seoul1 STABLE
2 ONLINE ONLINE seoul2 STABLE
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE seoul1 STABLE
ora.asm(ora.asmgroup)
1 ONLINE ONLINE seoul1 Started,STABLE
2 ONLINE ONLINE seoul2 Started,STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
1 ONLINE ONLINE seoul1 STABLE
2 ONLINE ONLINE seoul2 STABLE
ora.cvu
1 ONLINE ONLINE seoul1 STABLE
ora.qosmserver
1 ONLINE ONLINE seoul1 STABLE
ora.scan1.vip
1 ONLINE ONLINE seoul1 STABLE
ora.seoul1.vip
1 ONLINE ONLINE seoul1 STABLE
ora.seoul2.vip
1 ONLINE ONLINE seoul2 STABLE
ora.testdb.db
1 ONLINE ONLINE seoul1 Open,HOME=/u01/app/o
racle/db/19c,STABLE
2 ONLINE ONLINE seoul2 Open,HOME=/u01/app/o
racle/db/19c,STABLE
--------------------------------------------------------------------------------
B) RAC(Primary)에 Data Guard 전용 VIP 추가
클러스터에 DG 전용 VIP 추가
srvctl add vip -n [1번 호스트명] -k [네트워크 번호] -A [primary 1번 DG VIP명]/255.255.255.0/[DG 전용 NIC명] srvctl add vip -n [2번 호스트명] -k [네트워크 번호] -A [primary 2번 DG VIP명]/255.255.255.0/[DG 전용 NIC명] srvctl start vip -vip [1번 노드 DG 전용 VIP명] srvctl start vip -vip [2번 노드 DG 전용 VIP명] crsctl stat res -t | grep -E 'net|vip'
[root@seoul1 ~]# srvctl add vip -n seoul1 -k 2 -A seoul1-dgv/255.255.255.0/ens38 [root@seoul1 ~]# srvctl add vip -n seoul2 -k 2 -A seoul2-dgv/255.255.255.0/ens38 [root@seoul1 ~]# srvctl start vip -vip seoul1_2 [root@seoul1 ~]# srvctl start vip -vip seoul2_2 [root@seoul1 ~]# crsctl stat res -t | grep -E 'net|vip' ora.net1.network ora.net2.network ora.asmnet1.asmnetwork(ora.asmgroup) ora.scan1.vip ora.seoul1.vip ora.seoul1_2.vip ora.seoul2.vip ora.seoul2_2.vip
[root@seoul1 ~]# crsctl stat res -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
ONLINE ONLINE seoul1 STABLE
ONLINE ONLINE seoul2 STABLE
ora.chad
ONLINE ONLINE seoul1 STABLE
ONLINE ONLINE seoul2 STABLE
ora.net1.network
ONLINE ONLINE seoul1 STABLE
ONLINE ONLINE seoul2 STABLE
ora.net2.network
ONLINE ONLINE seoul1 STABLE
ONLINE ONLINE seoul2 STABLE
ora.ons
ONLINE ONLINE seoul1 STABLE
ONLINE ONLINE seoul2 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
1 ONLINE ONLINE seoul1 STABLE
2 ONLINE ONLINE seoul2 STABLE
ora.CRS.dg(ora.asmgroup)
1 ONLINE ONLINE seoul1 STABLE
2 ONLINE ONLINE seoul2 STABLE
ora.DATA.dg(ora.asmgroup)
1 ONLINE ONLINE seoul1 STABLE
2 ONLINE ONLINE seoul2 STABLE
ora.FRA.dg(ora.asmgroup)
1 ONLINE ONLINE seoul1 STABLE
2 ONLINE ONLINE seoul2 STABLE
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE seoul1 STABLE
ora.asm(ora.asmgroup)
1 ONLINE ONLINE seoul1 Started,STABLE
2 ONLINE ONLINE seoul2 Started,STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
1 ONLINE ONLINE seoul1 STABLE
2 ONLINE ONLINE seoul2 STABLE
ora.cvu
1 ONLINE ONLINE seoul1 STABLE
ora.qosmserver
1 ONLINE ONLINE seoul1 STABLE
ora.scan1.vip
1 ONLINE ONLINE seoul1 STABLE
ora.seoul1.vip
1 ONLINE ONLINE seoul1 STABLE
ora.seoul1_2.vip
1 ONLINE ONLINE seoul1 STABLE
ora.seoul2.vip
1 ONLINE ONLINE seoul2 STABLE
ora.seoul2_2.vip
1 ONLINE ONLINE seoul2 STABLE
ora.testdb.db
1 ONLINE ONLINE seoul1 Open,HOME=/u01/app/o
racle/db/19c,STABLE
2 ONLINE ONLINE seoul2 Open,HOME=/u01/app/o
racle/db/19c,STABLE
--------------------------------------------------------------------------------
C) RAC(Primary)에 Data Guard 전용 listener 추가
클러스터에 Data Guard 전용 리스너 추가
srvctl add listener -listener LISTENER_DG -netnum [네트워크 번호] -endpoints "TCP:[DG 전용 리스너 포트 번호]" -oraclehome $GI_HOME srvctl start listener -listener LISTENER_DG lsnrctl stat LISTENER_DG
[+ASM1@seoul1:~]$ srvctl add listener -listener LISTENER_DG -netnum 2 -endpoints "TCP:1625" -oraclehome $GI_HOME [+ASM1@seoul1:~]$ srvctl start listener -listener LISTENER_DG
[+ASM1@seoul1:~]$ lsnrctl stat LISTENER_DG LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 04-APR-2024 13:43:02 Copyright (c) 1991, 2023, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_DG))) STATUS of the LISTENER ------------------------ Alias LISTENER_DG Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 04-APR-2024 13:42:54 Uptime 0 days 0 hr. 0 min. 7 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/gi/19c/network/admin/listener.ora Listener Log File /u01/app/oracle/db/diag/tnslsnr/seoul1/listener_dg/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_DG))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.7.201)(PORT=1625))) The listener supports no services The command completed successfully
[+ASM2@seoul2:~]$ lsnrctl stat LISTENER_DG LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 04-APR-2024 13:43:45 Copyright (c) 1991, 2023, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_DG))) STATUS of the LISTENER ------------------------ Alias LISTENER_DG Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 04-APR-2024 13:42:54 Uptime 0 days 0 hr. 0 min. 50 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/gi/19c/network/admin/listener.ora Listener Log File /u01/app/oracle/db/diag/tnslsnr/seoul2/listener_dg/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_DG))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.7.202)(PORT=1625))) The listener supports no services The command completed successfully
D) Local Listener 추가
DG 전용 리스너를 local listener로 추가
alter system set LOCAL_LISTENER="(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=[인스턴스별 기존 VIP명 또는 IP])(PORT=[기존 리스너 포트]))(ADDRESS=(PROTOCOL=TCP)(HOST=[인스턴스별 DG 전용 VIP 명 또는 IP])(PORT=[DG 전용 리스너 포트 번호])))" sid='[인스턴스명]';
SQL> alter system set LOCAL_LISTENER="(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=seoul1-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=seoul1-dgv)(PORT=1625)))" sid='TESTDB1'; System altered. SQL> alter system register; System altered.
SQL> alter system set LOCAL_LISTENER="(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=seoul2-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=seoul2-dgv)(PORT=1625)))" sid='TESTDB2'; System altered. SQL> alter system register; System altered.
3. 복제용 임시 리스너 생성
A) Standby에 임시 listener 생성
$DB_HOME/network/admin/listener.ora 파일 생성
LISTENER_TEMP =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = [standby DG 전용 호스트명 또는 IP])(PORT = [리스너 포트 번호]))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC[리스너 포트 번호]))
)
)
SID_LIST_LISTENER_TEMP =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = [standby 글로벌 DB명])
(ORACLE_HOME = [DB 홈 경로])
(SID_NAME = [standby 인스턴스명])
)
)
[TESTDB@busan:~]$ cat >> $DB_HOME/network/admin/listener.ora
LISTENER_TEMP =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = busan-dg)(PORT = 1625))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1625))
)
)
SID_LIST_LISTENER_TEMP =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = TESTDB_STBY)
(ORACLE_HOME = /u01/app/oracle/db/19c)
(SID_NAME = TESTDB)
)
)
^C
B) 임시 listener 기동
DB 복제용 임시 리스너 기동
lsnrctl start LISTENER_TEMP
[TESTDB@busan:~]$ lsnrctl start LISTENER_TEMP LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 04-APR-2024 14:01:53 Copyright (c) 1991, 2023, Oracle. All rights reserved. Starting /u01/app/oracle/db/19c/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 19.0.0.0.0 - Production System parameter file is /u01/app/oracle/db/19c/network/admin/listener.ora Log messages written to /u01/app/oracle/db/diag/tnslsnr/busan/listener_temp/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.7.105)(PORT=1625))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1625))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=busan-dg)(PORT=1625))) STATUS of the LISTENER ------------------------ Alias LISTENER_TEMP Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 04-APR-2024 14:01:53 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/db/19c/network/admin/listener.ora Listener Log File /u01/app/oracle/db/diag/tnslsnr/busan/listener_temp/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.7.105)(PORT=1625))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1625))) Services Summary... Service "TESTDB_STBY" has 1 instance(s). Instance "TESTDB", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully
4. 접속 정보 설정
A) Primary와 Standby에 tnsnames.ora 설정
$DB_HOME/network/admin/tnsnames.ora 파일 수정
### primary site ###
[primary 호스트명]_SYN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = [primary 1번 DG 전용 VIP명])(PORT = [리스너 포트]))
(ADDRESS = (PROTOCOL = TCP)(HOST = [primary 2번 DG 전용 VIP명])(PORT = [리스너 포트]))
(LOAD_BALANCE = yes)
(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = [primary 글로벌 DB명]))
)
[primary 1번 인스턴스명]_SYN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = [primary 1번 DG 전용 VIP명])(PORT = [리스너 포트]))
(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = [primary 글로벌 DB명])(INSTANCE_NAME = [primary 1번 인스턴스명]))
)
[primary 2번 인스턴스명]_SYN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = [primary 1번 DG 전용 VIP명])(PORT = [리스너 포트]))
(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = [primary 글로벌 DB명])(INSTANCE_NAME = [primary 2번 인스턴스명]))
)
### standby site ###
[standby 호스트명]_SYN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = [standby DG 전용 호스트명])(PORT = [리스너 포트]))
(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = [standby 글로벌 DB명]))
)
### primary site ###
SEOUL_SYN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = seoul1-dgv)(PORT = 1625))
(ADDRESS = (PROTOCOL = TCP)(HOST = seoul2-dgv)(PORT = 1625))
(LOAD_BALANCE = yes)
(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = TESTDB))
)
TESTDB1_SYN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = seoul1-dgv)(PORT = 1625))
(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = TESTDB)(INSTANCE_NAME = TESTDB1))
)
TESTDB2_SYN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = seoul2-dgv)(PORT = 1625))
(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = TESTDB)(INSTANCE_NAME = TESTDB2))
)
### standby site ###
BUSAN_SYN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = busan-dg)(PORT = 1625))
(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = TESTDB_STBY))
)
{}
II. 사전 환경 구성
1. 아카이브 로그 환경 설정
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;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCLDB,ORCLDB_STBY)'; System altered.
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 14 Next log sequence to archive 16 Current log sequence 16
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> SELECT FORCE_LOGGING FROM V$DATABASE; FORCE_LOGGING -------------------------------------------------------------------------------- YES
2. 파라미터 설정
A) DB명 관련 파라미터 조회
DB name 파라미터 값 조회
SELECT name, db_unique_name FROM v$database;
SQL> SELECT name, db_unique_name FROM v$database; NAME DB_UNIQUE_NAME --------------------------- -------------------- ORCLDB ORCLDB
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=ORCLDB; System altered. SQL> ALTER SYSTEM SET FAL_SERVER=ORCLDB_STBY; System altered. SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO; System altered.
C) Standby Redo 로그 추가
Standby Redo 그룹 생성 (primary와 동일한 갯수와 사이즈로 생성)
ALTER DATABASE ADD STANDBY LOGFILE THREAD [THREAD 번호] GROUP [그룹 번호] ('[로그 파일 경로 및 파일 이름]') SIZE [로그 파일 크기];
SQL> select THREAD#, f.GROUP#, TYPE, BYTES/1024/1024 mb, MEMBER from v$logfile f, v$log l where l.GROUP#(+) = f.GROUP# order by 1, 2;
THREAD# GROUP# TYPE MB MEMBER
---------- ---------- --------------------- ---------- --------------------------------------------------------------------------------
1 1 ONLINE 200 +DATA/ORCLDB/ONLINELOG/group_1.262.1165255269
1 1 ONLINE 200 +FRA/ORCLDB/ONLINELOG/group_1.257.1165255269
1 2 ONLINE 200 +DATA/ORCLDB/ONLINELOG/group_2.263.1165255269
1 2 ONLINE 200 +FRA/ORCLDB/ONLINELOG/group_2.259.1165255269
1 3 ONLINE 200 +DATA/ORCLDB/ONLINELOG/group_3.264.1165255269
1 3 ONLINE 200 +FRA/ORCLDB/ONLINELOG/group_3.258.1165255269
2 4 ONLINE 200 +DATA/ORCLDB/ONLINELOG/group_4.267.1165255725
2 4 ONLINE 200 +FRA/ORCLDB/ONLINELOG/group_4.260.1165255725
2 5 ONLINE 200 +DATA/ORCLDB/ONLINELOG/group_5.268.1165255725
2 5 ONLINE 200 +FRA/ORCLDB/ONLINELOG/group_5.261.1165255725
2 6 ONLINE 200 +DATA/ORCLDB/ONLINELOG/group_6.269.1165255725
2 6 ONLINE 200 +FRA/ORCLDB/ONLINELOG/group_6.262.1165255727
12 rows selected.
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1
2 GROUP 11 ('+FRA','+DATA') size 200M,
3 GROUP 12 ('+FRA','+DATA') size 200M,
4 GROUP 13 ('+FRA','+DATA') size 200M,
5 GROUP 14 ('+FRA','+DATA') size 200M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2
2 GROUP 15 ('+FRA','+DATA') size 200M,
3 GROUP 16 ('+FRA','+DATA') size 200M,
4 GROUP 17 ('+FRA','+DATA') size 200M,
5 GROUP 18 ('+FRA','+DATA') size 200M;
SQL> select THREAD#, f.GROUP#, TYPE, BYTES/1024/1024 mb, MEMBER from v$logfile f, v$log l where l.GROUP#(+) = f.GROUP# order by 1, 2;
THREAD# GROUP# TYPE MB MEMBER
---------- ---------- --------------------- ---------- --------------------------------------------------------------------------------
1 1 ONLINE 200 +DATA/ORCLDB/ONLINELOG/group_1.262.1165255269
1 1 ONLINE 200 +FRA/ORCLDB/ONLINELOG/group_1.257.1165255269
1 2 ONLINE 200 +DATA/ORCLDB/ONLINELOG/group_2.263.1165255269
1 2 ONLINE 200 +FRA/ORCLDB/ONLINELOG/group_2.259.1165255269
1 3 ONLINE 200 +DATA/ORCLDB/ONLINELOG/group_3.264.1165255269
1 3 ONLINE 200 +FRA/ORCLDB/ONLINELOG/group_3.258.1165255269
2 4 ONLINE 200 +DATA/ORCLDB/ONLINELOG/group_4.267.1165255725
2 4 ONLINE 200 +FRA/ORCLDB/ONLINELOG/group_4.260.1165255725
2 5 ONLINE 200 +DATA/ORCLDB/ONLINELOG/group_5.268.1165255725
2 5 ONLINE 200 +FRA/ORCLDB/ONLINELOG/group_5.261.1165255725
2 6 ONLINE 200 +DATA/ORCLDB/ONLINELOG/group_6.269.1165255725
2 6 ONLINE 200 +FRA/ORCLDB/ONLINELOG/group_6.262.1165255727
11 STANDBY +FRA/ORCLDB/ONLINELOG/group_11.276.1165414987
11 STANDBY +DATA/ORCLDB/ONLINELOG/group_11.271.1165414987
12 STANDBY +FRA/ORCLDB/ONLINELOG/group_12.277.1165414987
12 STANDBY +DATA/ORCLDB/ONLINELOG/group_12.272.1165414987
13 STANDBY +DATA/ORCLDB/ONLINELOG/group_13.273.1165414989
13 STANDBY +FRA/ORCLDB/ONLINELOG/group_13.278.1165414987
14 STANDBY +FRA/ORCLDB/ONLINELOG/group_14.279.1165414989
14 STANDBY +DATA/ORCLDB/ONLINELOG/group_14.274.1165414989
15 STANDBY +FRA/ORCLDB/ONLINELOG/group_15.280.1165415003
15 STANDBY +DATA/ORCLDB/ONLINELOG/group_15.275.1165415003
16 STANDBY +DATA/ORCLDB/ONLINELOG/group_16.276.1165415005
16 STANDBY +FRA/ORCLDB/ONLINELOG/group_16.281.1165415003
17 STANDBY +FRA/ORCLDB/ONLINELOG/group_17.282.1165415005
17 STANDBY +DATA/ORCLDB/ONLINELOG/group_17.277.1165415005
18 STANDBY +FRA/ORCLDB/ONLINELOG/group_18.283.1165415005
18 STANDBY +DATA/ORCLDB/ONLINELOG/group_18.278.1165415005
28 rows selected.
3. Standby 환경 설정
A) 디렉토리 생성
oracle 유저로 standby 서버에서 수행
mkdir -p [데이터 파일 저장 경로] mkdir -p [FRA 할당 경로] mkdir -p [adump 파라미터 경로]
[ORCLDB@busan:~]$ mkdir -p $ORACLE_BASE/admin/ORCLDB/adump
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
[+ASM1@seoul1:~]$ srvctl config database -d ORCLDB
데이터베이스 고유 이름: ORCLDB
데이터베이스 이름: ORCLDB
Oracle 홈: /u01/app/oracle/db/19c
Oracle 사용자: oracle
Spfile: +DATA/ORCLDB/PARAMETERFILE/spfile.270.1165255727
비밀번호 파일: +DATA/ORCLDB/PASSWORD/pwdorcldb.256.1165255157
도메인:
시작 옵션: open
정지 옵션: immediate
데이터베이스 롤: PRIMARY
관리 정책: AUTOMATIC
서버 풀:
디스크 그룹: FRA,DATA
마운트 위치 경로:
서비스:
유형: RAC
시작 동시성:
정지 동시성:
OSDBA 그룹: dba
OSOPER 그룹: dba
데이터베이스 인스턴스: ORCLDB1,ORCLDB2
구성된 노드: seoul1,seoul2
CSS 중요: no
CPU 개수: 0
메모리 대상: 0
최대 메모리: 0
데이터베이스 서비스에 대한 기본 네트워크 번호:
관리자 관리 데이터베이스입니다.
[+ASM1@seoul1:~]$ asmcmd
ASMCMD> pwcopy +DATA/ORCLDB/PASSWORD/pwdorcldb.256.1165255157 /home/oracle/orapwORCLDB
ASMCMD> exit
[+ASM1@seoul1:~] $ scp /home/oracle/orapwORCLDB oracle@busan-dg:/u01/app/oracle/db/19c/dbs
The authenticity of host 'busan-dg (10.0.7.105)' can't be established.
ED25519 key fingerprint is SHA256:vlcfe27dSJBhwko+AgNB9+1GBneaMYImhftmv4axGIg.
This host key is known by the following other names/addresses:
~/.ssh/known_hosts:9: 10.0.1.105
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added 'busan-dg' (ED25519) to the list of known hosts.
oracle@busan-dg's password:
orapwORCLDB 100% 2048 4.8MB/s 00:00
C) 임시 파라미터 파일 생성
standby 서버에서 oracle 계정으로 홈 경로(또는 적절한 경로)에 생성
cat > ~oracle/[standby 글로벌 DB명].ora *.db_name='[DB명]'
[ORCLDB@busan:~]$ cat > ~oracle/ORCLDB_STBY.ora *.db_name='ORCLDB' ^C
{}
III. Standby 데이터베이스 생성
{}
1. Standby DB Instance 기동
A) nomount 모드로 DB 시작
Standby 인스턴스 기동
STARTUP NOMOUNT PFILE='~oracle/[standby 글로벌 DB명].ora'
[ORCLDB@busan:~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 4 14:28:58 2024 Version 19.22.0.0.0 Copyright (c) 1982, 2023, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount pfile='/home/oracle/ORCLDB_STBY.ora'; ORACLE instance started. Total System Global Area 343930632 bytes Fixed Size 8939272 bytes Variable Size 260046848 bytes Database Buffers 67108864 bytes Redo Buffers 7835648 bytes SQL>
{}
2. RMAN을 이용한 DB 복제
A) RMAN 접속
RMAN 으로 Primary와 Standby 동시 접속
rman TARGET sys/[sys 계정 암호]@[primary 네트워크 서비스명] AUXILIARY sys/[sys 계정 암호]@[standby 네트워크 서비스명]
[ORCLDB1@seoul1:~]$ export NLS_DATE_FORMAT="yyyy/mm/dd hh24:mi:ss" [ORCLDB1@seoul1:~]$ rman TARGET sys/oracle@SEOUL_SYN auxiliary sys/oracle@BUSAN_SYN Recovery Manager: Release 19.0.0.0.0 - Production on Thu Apr 4 15:08:47 2024 Version 19.22.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCLDB (DBID=2948583395) connected to auxiliary database: ORCLDB (not mounted) RMAN>
B) Duplicate 명령으로 DB 복제
운영 중인 DB를 RMAN으로 standby 서버에 복제
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE -- 운영 primary로부터 복제
-- BACKUP LOCATION '[백업 저장 경로 (디스크)]' -- 백업으로부터 복제 (디스크 저장)
-- FROM FILE '[백업 메타 정보 XML 파일]' -- 백업으로부터 복제 (테이프 장치)
DORECOVER
PASSWORD FILE
SPFILE
SET cluster_database='FALSE'
SET db_unique_name='[standby 글로벌 DB명]' COMMENT 'Is standby'
SET FAL_SERVER='[primary 글로벌 DB명]' COMMENT 'Is primary'
NOFILENAMECHECK;
RMAN> DUPLICATE TARGET DATABASE
2> FOR STANDBY
3> FROM ACTIVE DATABASE
4> DORECOVER
5> PASSWORD FILE
6> SPFILE
7> SET cluster_database='FALSE'
8> SET db_unique_name='orcldb_stby' COMMENT 'Is standby'
9> SET FAL_SERVER='orcldb' COMMENT 'Is primary'
10> NOFILENAMECHECK;
Starting Duplicate Db at 2024/04/04 15:11:24
PASSWORD clause is not needed when duplicating for standby database from active database
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=433 device type=DISK
current log archived
contents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format '/u01/app/oracle/db/19c/dbs/orapwORCLDB' ;
restore clone from service 'SEOUL_SYN' spfile to
'/u01/app/oracle/db/19c/dbs/spfileORCLDB.ora';
sql clone "alter system set spfile= ''/u01/app/oracle/db/19c/dbs/spfileORCLDB.ora''";
}
executing Memory Script
Starting backup at 2024/04/04 15:11:26
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=394 instance=ORCLDB2 device type=DISK
Finished backup at 2024/04/04 15:11:29
Starting restore at 2024/04/04 15:11:29
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/db/19c/dbs/spfileORCLDB.ora
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 2024/04/04 15:11:33
sql statement: alter system set spfile= ''/u01/app/oracle/db/19c/dbs/spfileORCLDB.ora''
contents of Memory Script:
{
sql clone "alter system set cluster_database =
FALSE comment=
'''' scope=spfile";
sql clone "alter system set db_unique_name =
''ORCLDB_STBY'' comment=
''Is standby'' scope=spfile";
sql clone "alter system set FAL_SERVER =
''ORCLDB'' comment=
''Is primary'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set cluster_database = FALSE comment= '''' scope=spfile
sql statement: alter system set db_unique_name = ''ORCLDB_STBY'' comment= ''Is standby'' scope=spfile
sql statement: alter system set FAL_SERVER = ''ORCLDB'' comment= ''Is primary'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 3758092920 bytes
Fixed Size 8946296 bytes
Variable Size 721420288 bytes
Database Buffers 3019898880 bytes
Redo Buffers 7827456 bytes
duplicating Online logs to Oracle Managed File (OMF) location
duplicating Datafiles to Oracle Managed File (OMF) location
contents of Memory Script:
{
sql clone "alter system set control_files =
''+DATA/ORCLDB_STBY/CONTROLFILE/current.257.1165417921'', ''+FRA/ORCLDB_STBY/CONTROLFILE/current.256.1165417921'' comment=
''Set by RMAN'' scope=spfile";
restore clone from service 'SEOUL_SYN' standby controlfile;
}
executing Memory Script
sql statement: alter system set control_files = ''+DATA/ORCLDB_STBY/CONTROLFILE/current.257.1165417921'', ''+FRA/ORCLDB_STBY/CONTROLFILE/current.256.1165417921'' comment= ''Set by RMAN'' scope=spfile
Starting restore at 2024/04/04 15:12:01
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=197 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=+DATA/ORCLDB_STBY/CONTROLFILE/current.259.1165417925
output file name=+FRA/ORCLDB_STBY/CONTROLFILE/current.258.1165417925
Finished restore at 2024/04/04 15:12:05
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
set newname for clone datafile 1 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 7 to new;
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 +DATA in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 2024/04/04 15:12: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 +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
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 +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
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 +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
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 00005 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
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 00007 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2024/04/04 15:12:15
sql statement: alter system archive log current
current log archived
contents of Memory Script:
{
restore clone force from service 'SEOUL_SYN'
archivelog from scn 2595025;
switch clone datafile all;
}
executing Memory Script
Starting restore at 2024/04/04 15:12:20
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=16
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=17
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=18
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=2 sequence=8
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=2 sequence=9
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2024/04/04 15:12:25
datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=1165417945 file name=+DATA/ORCLDB_STBY/DATAFILE/system.260.1165417931
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=1165417945 file name=+DATA/ORCLDB_STBY/DATAFILE/sysaux.261.1165417931
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=1165417946 file name=+DATA/ORCLDB_STBY/DATAFILE/undotbs1.262.1165417933
datafile 5 switched to datafile copy
input datafile copy RECID=9 STAMP=1165417946 file name=+DATA/ORCLDB_STBY/DATAFILE/undotbs2.263.1165417933
datafile 7 switched to datafile copy
input datafile copy RECID=10 STAMP=1165417946 file name=+DATA/ORCLDB_STBY/DATAFILE/users.264.1165417935
contents of Memory Script:
{
set until scn 2595679;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 2024/04/04 15:12:26
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 17 is already on disk as file +FRA/ORCLDB_STBY/ARCHIVELOG/2024_04_04/thread_1_seq_17.260.1165417941
archived log for thread 1 with sequence 18 is already on disk as file +FRA/ORCLDB_STBY/ARCHIVELOG/2024_04_04/thread_1_seq_18.261.1165417943
archived log for thread 2 with sequence 8 is already on disk as file +FRA/ORCLDB_STBY/ARCHIVELOG/2024_04_04/thread_2_seq_8.262.1165417943
archived log for thread 2 with sequence 9 is already on disk as file +FRA/ORCLDB_STBY/ARCHIVELOG/2024_04_04/thread_2_seq_9.263.1165417945
archived log file name=+FRA/ORCLDB_STBY/ARCHIVELOG/2024_04_04/thread_1_seq_17.260.1165417941 thread=1 sequence=17
archived log file name=+FRA/ORCLDB_STBY/ARCHIVELOG/2024_04_04/thread_2_seq_8.262.1165417943 thread=2 sequence=8
archived log file name=+FRA/ORCLDB_STBY/ARCHIVELOG/2024_04_04/thread_1_seq_18.261.1165417943 thread=1 sequence=18
archived log file name=+FRA/ORCLDB_STBY/ARCHIVELOG/2024_04_04/thread_2_seq_9.263.1165417945 thread=2 sequence=9
media recovery complete, elapsed time: 00:00:01
Finished recover at 2024/04/04 15:12:27
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=394 instance=ORCLDB2 device type=DISK
deleted archived log
archived log file name=+FRA/ORCLDB_STBY/ARCHIVELOG/2024_04_04/thread_1_seq_16.259.1165417941 RECID=1 STAMP=1165417940
deleted archived log
archived log file name=+FRA/ORCLDB_STBY/ARCHIVELOG/2024_04_04/thread_1_seq_17.260.1165417941 RECID=2 STAMP=1165417941
deleted archived log
archived log file name=+FRA/ORCLDB_STBY/ARCHIVELOG/2024_04_04/thread_1_seq_18.261.1165417943 RECID=3 STAMP=1165417942
deleted archived log
archived log file name=+FRA/ORCLDB_STBY/ARCHIVELOG/2024_04_04/thread_2_seq_8.262.1165417943 RECID=4 STAMP=1165417943
deleted archived log
archived log file name=+FRA/ORCLDB_STBY/ARCHIVELOG/2024_04_04/thread_2_seq_9.263.1165417945 RECID=5 STAMP=1165417944
Deleted 5 objects
Finished Duplicate Db at 2024/04/04 15:12:29
[ORCLDB@busan:~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 4 15:13:10 2024 Version 19.22.0.0.0 Copyright (c) 1982, 2023, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.22.0.0.0 SQL> select status from v$instance; STATUS ------------------------------------ MOUNTED
3. Standby 리소스 추가
A) Restart 리스너 추가
데이터베이스 복제용 임시 리스너 중지
lsnrctl stop LISTENER_TEMP
[ORCLDB@busan:~]$ lsnrctl stop LISTENER_TEMP LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 04-APR-2024 15:13:33 Copyright (c) 1991, 2023, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=busan-dg)(PORT=1625))) The command completed successfully
Standby 쪽에 vi $GI_HOME/network/admin/listener.ora 로 글로벌 DB 접속 정보 추가
LISTENER_DG =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = [standby DG 전용 호스트명 또는 IP])(PORT = [리스너 포트]))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC[리스너 포트]))
)
)
SID_LIST_LISTENER_DG =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = [standby 글로벌 DB명])
(ORACLE_HOME = [DB 홈 경로])
(SID_NAME = [standby 인스턴스명])
)
)
LISTENER_DG =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = busan-dg)(PORT = 1625))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1625))
)
)
SID_LIST_LISTENER_DG =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCLDB_STBY)
(ORACLE_HOME = /u01/app/oracle/db/19c)
(SID_NAME = ORCLDB)
)
)
Restart에 리스너 추가
srvctl add listener -listener LISTENER_DG -endpoints "IPC:EXTPROC[리스너 포트]" -oraclehome $GI_HOME srvctl start listener -listener LISTENER_DG
[ORCLDB@busan:~]$ srvctl add listener -listener LISTENER_DG -endpoints "IPC:EXTPROC1625" -oraclehome $GI_HOME [ORCLDB@busan:~]$ srvctl start listener -listener LISTENER_DG [ORCLDB@busan:~]$ srvctl config listener -l LISTENER_DG 이름: LISTENER_DG 유형: Database Listener 홈: /u01/app/oracle/gi/19c 끝점: IPC:EXTPROC1625 리스너가 사용으로 설정되었습니다.
B) Restart에 데이터베이스 추가
Restart에 데이터베이스 추가
srvctl add database -db "[standby 글로벌 DB명]" -dbname "[DB명]" -instance "[standby 인스턴스명]" -oraclehome [DB 홈 경로] -role PHYSICAL_STANDBY -startoption "READ ONLY" -stopoption IMMEDIATE -policy AUTOMATIC -diskgroup [ASM 디스크 그룹1],[ASM 디스크 그룹2],... -spfile [sp파일 경로 및 이름] -pwfile [패스워드 파일 경로 및 이름] srvctl enable database -db "[standby 글로벌 DB명]"
[ORCLDB@busan:~]$ srvctl add database -db "ORCLDB_STBY" -dbname "ORCLDB" -instance "ORCLDB" -oraclehome /u01/app/oracle/db/19c -role PHYSICAL_STANDBY -startoption "READ ONLY" -stopoption IMMEDIATE -policy AUTOMATIC -diskgroup FRA,DATA -spfile /u01/app/oracle/db/19c/dbs/spfileORCLDB.ora -pwfile /u01/app/oracle/db/19c/dbs/orapwORCLDB
[ORCLDB@busan:~]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE busan STABLE
ora.FRA.dg
ONLINE ONLINE busan STABLE
ora.LISTENER.lsnr
ONLINE ONLINE busan STABLE
ora.LISTENER_DG.lsnr
ONLINE ONLINE busan STABLE
ora.asm
ONLINE ONLINE busan Started,STABLE
ora.ons
OFFLINE OFFLINE busan STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
1 ONLINE ONLINE busan STABLE
ora.diskmon
1 OFFLINE OFFLINE STABLE
ora.evmd
1 ONLINE ONLINE busan STABLE
ora.orcldb_stby.db
1 OFFLINE OFFLINE STABLE
--------------------------------------------------------------------------------
IV. Data Guard Broker 설정
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에서 각각 수행합니다.
