내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
다운로드
trending_flat
OS환경 : Oracle Linux 8.4 (64bit)
DB 환경 : Oracle Database 19.12.0.0 RAC ADG
OS 설정 및 GRID 설정은 아래 게시물 참조
Oracle Linux 8.4에 Oracle 19c RAC to RAC ADG 설치 가이드_Part 1(https://positivemh.tistory.com/824)
Oracle Linux 8.4에 Oracle 19c RAC to RAC ADG 설치 가이드_Part 2(https://positivemh.tistory.com/825)
방법 : Oracle Linux 8.4에 Oracle 19c RAC to RAC ADG 설치 가이드_Part 3
기존 RAC
1번 노드 hostname : oel19db1, db_name : ORADB, db_unique_name : ORADB, ip : 192.168.137.10
1번 노드 hostname : oel19db2, db_name : ORADB, db_unique_name : ORADB, ip : 192.168.137.20
ADG 추가 구성
1번 노드(Standby) hostname : oel19adg1, db_name : ORADB, db_unique_name : ORAADG, ip : 192.168.137.50
1번 노드(Standby) hostname : oel19adg2, db_name : ORADB, db_unique_name : ORAADG, ip : 192.168.137.60
설치파일은 아래 4가지 파일을 이용함
GRID : LINUX.X64_193000_grid_home.zip
DB : LINUX.X64_193000_db_home.zip
OPatch : p6880880_190000_Linux-x86-64.zip(12.2.0.1.27)
RU : p32900083_190000_Linux-x86-64.zip
Patch 32900083 - Combo of OJVM Component Release Update 19.12.0.0.210720 + Grid Infrastructure Jul 2021 Release Update 19.12.0.0.210720
ㄴ Patch 32895426 - Database Grid Infrastructure Jul 2021 Release Update 19.12.0.0.210720
ㄴ Patch 32876380 - Oracle JavaVM Component Release Update 19.12.0.0.210720
DB 소프트웨어(엔진) 설치
db 설치 미디어 압축 해제
|
1
2
|
$ cd $ORACLE_HOME
$ unzip /oracle/media/LINUX.X64_193000_db_home.zip
|
OPatch 파일 최신파일로 교체
|
1
2
3
4
5
6
7
|
$ cd $ORACLE_HOME
$ mv OPatch/ OPatchold
$ unzip /oracle/media/p6880880_190000_Linux-x86-64.zip
$ $ORACLE_HOME/OPatch/opatch version -oh $ORACLE_HOME
OPatch Version: 12.2.0.1.27
OPatch succeeded.
|
runinstaller 실행
|
1
2
3
4
5
|
$ cd $ORACLE_HOME
$ ./runInstaller -applyRU /oracle/media/32900083/32895426/
Preparing the home to patch...
Applying the patch /oracle/media/32900083/32895426/...
(자동으로 패치 적용 후 GUI 창이 나타남)
|
gui가 로딩되기까지 시간이 조금 걸림
Set Up Software Only 선택
Oracle Real Application Cluster database installation 선택
1,2 선택(SSH connectivity는 grid 설치시 진행했으므로 하지않음)
Enterprise Edition 선택
oracle base 지정
group 는 모두 dba 로 지정
설치 중 root 권한으로 스크립트 실행하는 부분에서 자동로 스크립트 실행할지 여부 지정 root 패스워드 입력
사전 요구사항 체크중
SCAN 관련메세지는 SCAN IP가 DNS에 등록되어 있지 않아서 발생한 문제 모두 Ignore
response 파일이 필요하다면 따로 저장 후 Install
db 엔진 설치중
root 계정으로 스크립트 실행할 지 물어보는 메세지 Yes
Close
db 엔진 설치 완료
OJVM 패치(1번, 2번 노드 모두)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
|
$ $ORACLE_HOME/OPatch/opatch apply -oh $ORACLE_HOME
Oracle Interim Patch Installer version 12.2.0.1.27
Copyright (c) 2022, Oracle Corporation. All rights reserved.
Oracle Home : /oracle/app/oracle/product/19c
Central Inventory : /oracle/app/oraInventory
from : /oracle/app/oracle/product/19c/oraInst.loc
OPatch version : 12.2.0.1.27
OUI version : 12.2.0.7.0
Log file location : /oracle/app/oracle/product/19c/cfgtoollogs/opatch/opatch2022-04-07_07-02-43AM_1.log
Verifying environment and performing prerequisite checks...
OPatch continues with these patches: 32876380
Do you want to proceed? [y|n]
y <-- y 입력
User Responded with: Y
All checks passed.
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/oracle/app/oracle/product/19c')
Is the local system ready for patching? [y|n]
y <-- y 입력
User Responded with: Y
Backing up files...
Applying interim patch '32876380' to OH '/oracle/app/oracle/product/19c'
Patching component oracle.javavm.server, 19.0.0.0.0...
Patching component oracle.javavm.server.core, 19.0.0.0.0...
Patching component oracle.rdbms.dbscripts, 19.0.0.0.0...
Patching component oracle.rdbms, 19.0.0.0.0...
Patching com=ponent oracle.javavm.client, 19.0.0.0.0...
Patch 32876380 successfully applied.
Log file location: /oracle/app/oracle/product/19c/cfgtoollogs/opatch/opatch2022-04-07_07-02-43AM_1.log
OPatch succeeded.
|
DB 패치정보 확인
|
1
2
3
4
5
6
|
$ $ORACLE_HOME/OPatch/opatch lspatches -oh $ORACLE_HOME
32876380;OJVM RELEASE UPDATE: 19.12.0.0.210720 (32876380)
32916816;OCW RELEASE UPDATE 19.12.0.0.0 (32916816)
32904851;Database Release Update : 19.12.0.0.210720 (32904851)
OPatch succeeded.
|
db에 패치 적용
1 2 | $ cd $ORACLE_HOME/OPatch $ ./datapatch -verbose |
데이터 가드 구성 전 기존 RAC db와 추가 RAC db의 시간대가 맞는지 확인 후 진행
|
1
2
3
|
기존 RAC 1번, 2번 노드, 추가 RAC 1번, 2번
$ date
Fri Apr 8 10:09:18 KST 2022
|
데이터 가드 구성 전 db 메모리 재설정(기존 RAC 1번 노드)(기존 서버, 추가 서버 물리메모리에 맞게끔 설정)
|
1
2
|
기존 RAC 1번 노드
SQL> alter system set memory_target=1500m scope=spfile sid='*';
|
scope=spfile 재기동 적용은 다음단계에서 진행
데이터 가드 구성 설정
아카이브모드 및 FRA 설정(기존 RAC 1번, 2번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SQL>
alter system set db_recovery_file_dest='+DATA' scope=spfile sid='*';
alter system set db_recovery_file_dest_size=8g scope=spfile sid='*';
alter system set log_archive_dest_1='location=+DATA' scope=spfile sid='*';
SQL>
shutdown immediate
startup mount --1번, 2번 노드 모두 종료 후 mount 기동
alter database archivelog;
alter database open;
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +DATA
Oldest online log sequence 22
Next log sequence to archive 23
Current log sequence 23
|
force logging 설정(기존 RAC 1번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
NO
SQL> alter database force logging;
Database altered.
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
YES
|
/etc/hosts 파일 수정(기존 RAC 1번, 2번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
|
# vi /etc/hosts
##ORAADG Public
192.168.137.50 oel19adg1
192.168.137.60 oel19adg2
##ORAADG Virtual
192.168.137.51 oel19adg1-vip
192.168.137.61 oel19adg2-vip
##ORAADG SCAN
192.168.137.71 oel19adg-scan
|
위 내용 추가
/etc/hosts 파일 수정(추가 RAC 1번, 2번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
|
# vi /etc/hosts
##ORADB Public
192.168.137.10 oel19db1
192.168.137.20 oel19db2
##ORADB Virtual
192.168.137.100 oel19db1-vip
192.168.137.200 oel19db2-vip
##ORADB SCAN
192.168.137.150 oel19db-scan
|
위 내용 추가
listener.ora 파일 수정(기존 RAC 1번, 2번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
|
기존 1번 노드
$ vi $GRID_HOME/network/admin/listener.ora
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=OFF # line added by Agent - Disabled by Agent because REMOTE_REGISTRATION_ADDRESS is set
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET # line added by Agent
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORADB)
(ORACLE_HOME = /oracle/app/oracle/product/19c)
(SID_NAME = ORADB1)
)
(SID_DESC =
(GLOBAL_DBNAME = ORADB_DGMGRL)
(ORACLE_HOME = /oracle/app/oracle/product/19c)
(SID_NAME = ORADB1)
)
(SID_DESC =
(GLOBAL_DBNAME = ORADB_DGB)
(ORACLE_HOME = /oracle/app/oracle/product/19c)
(SID_NAME = ORADB1)
)
)
기존 2번 노드
$ vi $GRID_HOME/network/admin/listener.ora
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=OFF # line added by Agent - Disabled by Agent because REMOTE_REGISTRATION_ADDRESS is set
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORADB)
(ORACLE_HOME = /oracle/app/oracle/product/19c)
(SID_NAME = ORADB2)
)
(SID_DESC =
(GLOBAL_DBNAME = ORADB_DGMGRL)
(ORACLE_HOME = /oracle/app/oracle/product/19c)
(SID_NAME = ORADB2)
)
(SID_DESC =
(GLOBAL_DBNAME = ORADB_DGB)
(ORACLE_HOME = /oracle/app/oracle/product/19c)
(SID_NAME = ORADB2)
)
)
|
굵게 표시한 부분 추가
listener.ora 파일 수정 $GRID_HOME (추가 RAC 1번, 2번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
|
추가 1번 노드
$ vi $GRID_HOME/network/admin/listener.ora
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=OFF # line added by Agent - Disabled by Agent because REMOTE_REGISTRATION_ADDRESS is set
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET # line added by Agent
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORAADG)
(ORACLE_HOME = /oracle/app/oracle/product/19c)
(SID_NAME = ORAADG1)
)
(SID_DESC =
(GLOBAL_DBNAME = ORAADG_DGMGRL)
(ORACLE_HOME = /oracle/app/oracle/product/19c)
(SID_NAME = ORAADG1)
)
(SID_DESC =
(GLOBAL_DBNAME = ORAADG_DGB)
(ORACLE_HOME = /oracle/app/oracle/product/19c)
(SID_NAME = ORAADG1)
)
)
추가 2번 노드
$ vi $GRID_HOME/network/admin/listener.ora
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=OFF # line added by Agent - Disabled by Agent because REMOTE_REGISTRATION_ADDRESS is set
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORAADG)
(ORACLE_HOME = /oracle/app/oracle/product/19c)
(SID_NAME = ORAADG2)
)
(SID_DESC =
(GLOBAL_DBNAME = ORAADG_DGMGRL)
(ORACLE_HOME = /oracle/app/oracle/product/19c)
(SID_NAME = ORAADG2)
)
(SID_DESC =
(GLOBAL_DBNAME = ORAADG_DGB)
(ORACLE_HOME = /oracle/app/oracle/product/19c)
(SID_NAME = ORAADG2)
)
)
|
굵게 표시한 부분 추가
tnsnames.ora 파일 수정 $ORACLE_HOME (기존 RAC 1번, 2번 노드, 추가 RAC 1번, 2번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
|
기존 RAC 1번, 2번 노드, 추가 RAC 1번, 2번 노드
$ vi $ORACLE_HOME/network/admin/tnsnames.ora
ORADB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oel19db1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = oel19db2-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORADB)
)
)
ORADB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oel19db1-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = ORADB1)
)
)
ORADB2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oel19db2-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = ORADB2)
)
)
ORAADG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oel19adg1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = oel19adg2-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORAADG)
)
)
ORAADG1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oel19adg1-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = ORAADG1)
)
)
ORAADG2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oel19adg2-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = ORAADG2)
)
)
|
.bash_profile 수정(기존 RAC 1번, 2번 노드, 추가 RAC 1번, 2번 노드)
|
1
2
3
4
|
$ cd /home/oracle
$ vi .bash_profile
export TNS_ADMIN=$GRID_HOME/network/admin;
$ . ./.bash_profile
|
grid에도 TNS_ADMIN 등록(기존 RAC 1번, 2번 노드)
|
1
2
3
4
5
6
7
|
기존 RAC 1번 노드
$ srvctl setenv database -d ORADB -T "TNS_ADMIN=/oracle/app/grid/19c/network/admin"
기존 RAC 1번, 2번 노드 확인
$ srvctl getenv database -d ORADB -t "TNS_ADMIN"
ORADB:
TNS_ADMIN=/oracle/app/grid/19c/network/admin
|
리스너 재기동(기존 RAC 1번 노드, 추가 RAC 1번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
|
기존 1번 노드
$ srvctl stop listener -node oel19db1 -l listener
srvctl stop listener -node oel19db2 -l listener
srvctl start listener -node oel19db1 -l listener
srvctl start listener -node oel19db2 -l listener
추가 1번 노드
$ srvctl stop listener -node oel19adg1 -l listener
srvctl stop listener -node oel19adg2 -l listener
srvctl start listener -node oel19adg1 -l listener
srvctl start listener -node oel19adg2 -l listener
|
리스너 상태 확인(기존 RAC 1번, 2번 노드, 추가 RAC 1번, 2번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
|
기존 RAC 1번 노드
$ lsnrctl status listener
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 11-APR-2022 14:31:45
Copyright (c) 1991, 2021, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 11-APR-2022 14:29:52
Uptime 0 days 0 hr. 1 min. 53 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/grid/19c/network/admin/listener.ora
Listener Log File /oracle/app/oracle/diag/tnslsnr/oel19db1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.137.10)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.137.100)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_OCR_VOTE" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "ORADB" has 2 instance(s).
Instance "ORADB1", status UNKNOWN, has 1 handler(s) for this service...
Instance "ORADB1", status READY, has 1 handler(s) for this service...
Service "ORADBXDB" has 1 instance(s).
Instance "ORADB1", status READY, has 1 handler(s) for this service...
Service "ORADB_DGB" has 1 instance(s).
Instance "ORADB1", status UNKNOWN, has 1 handler(s) for this service...
Service "ORADB_DGMGRL" has 1 instance(s).
Instance "ORADB1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
기존 RAC 2번 노드
$ lsnrctl status listener
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 11-APR-2022 14:31:46
Copyright (c) 1991, 2021, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 11-APR-2022 14:29:54
Uptime 0 days 0 hr. 1 min. 51 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/grid/19c/network/admin/listener.ora
Listener Log File /oracle/app/oracle/diag/tnslsnr/oel19db2/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.137.20)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.137.200)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "+ASM_OCR_VOTE" has 1 instance(s).
Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "ORADB" has 2 instance(s).
Instance "ORADB2", status UNKNOWN, has 1 handler(s) for this service...
Instance "ORADB2", status READY, has 1 handler(s) for this service...
Service "ORADBXDB" has 1 instance(s).
Instance "ORADB2", status READY, has 1 handler(s) for this service...
Service "ORADB_DGB" has 1 instance(s).
Instance "ORADB2", status UNKNOWN, has 1 handler(s) for this service...
Service "ORADB_DGMGRL" has 1 instance(s).
Instance "ORADB2", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
추가 RAC 1번 노드
$ lsnrctl status listener
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 11-APR-2022 14:31:26
Copyright (c) 1991, 2021, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 11-APR-2022 14:29:56
Uptime 0 days 0 hr. 1 min. 29 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/grid/19c/network/admin/listener.ora
Listener Log File /oracle/app/oracle/diag/tnslsnr/oel19adg1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.137.50)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.137.51)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_OCR_VOTE" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "ORAADG" has 1 instance(s).
Instance "ORAADG1", status UNKNOWN, has 1 handler(s) for this service...
Service "ORAADG_DGB" has 1 instance(s).
Instance "ORAADG1", status UNKNOWN, has 1 handler(s) for this service...
Service "ORAADG_DGMGRL" has 1 instance(s).
Instance "ORAADG1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
추가 RAC 2번 노드
$ lsnrctl status listener
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 11-APR-2022 14:31:27
Copyright (c) 1991, 2021, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 11-APR-2022 14:29:58
Uptime 0 days 0 hr. 1 min. 28 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/grid/19c/network/admin/listener.ora
Listener Log File /oracle/app/oracle/diag/tnslsnr/oel19adg2/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.137.60)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.137.61)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "+ASM_OCR_VOTE" has 1 instance(s).
Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "ORAADG" has 1 instance(s).
Instance "ORAADG2", status UNKNOWN, has 1 handler(s) for this service...
Service "ORAADG_DGB" has 1 instance(s).
Instance "ORAADG2", status UNKNOWN, has 1 handler(s) for this service...
Service "ORAADG_DGMGRL" has 1 instance(s).
Instance "ORAADG2", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
|
ASM 서비스는 60초 이내로 자동으로 등록됨
Standby Log File 생성 및 확인(기존 RAC 1번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SQL>
alter database add standby logfile thread 1 '+DATA' size 200m;
alter database add standby logfile thread 1 '+DATA' size 200m;
alter database add standby logfile thread 2 '+DATA' size 200m;
alter database add standby logfile thread 2 '+DATA' size 200m;
SQL>
set lines 200 pages 1000
col member form a70
select group#, type, member from v$logfile;
GROUP# TYPE MEMBER
---------- ------- ----------------------------------------------------------------------
1 ONLINE +DATA/ORADB/ONLINELOG/group_1.278.1101466079
2 ONLINE +DATA/ORADB/ONLINELOG/group_2.328.1101466081
3 ONLINE +DATA/ORADB/ONLINELOG/group_3.323.1101467375
4 ONLINE +DATA/ORADB/ONLINELOG/group_4.275.1101467375
5 STANDBY +DATA/ORADB/ONLINELOG/group_5.321.1101738843
6 STANDBY +DATA/ORADB/ONLINELOG/group_6.267.1101738845
7 STANDBY +DATA/ORADB/ONLINELOG/group_7.320.1101738845
8 STANDBY +DATA/ORADB/ONLINELOG/group_8.319.1101738845
8 rows selected.
|
flashback database 활성화(선택)(fra가 설정되어있고 primary db에서만 설정 가능함)(기존 RAC 1번 노드)
|
1
2
3
4
5
6
7
8
9
|
SQL> alter database flashback on;
Database altered.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
|
adump 경로 생성(추가 RAC 1번, 2번 노드)
|
1
2
|
1번, 2번 노드
$ mkdir -p /oracle/app/oracle/admin/ORAADG/adump
|
파라미터 변경 후 재기동(기존 RAC 1번, 2번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
1번 노드
SQL>
alter system set dg_broker_start=false scope=spfile sid='*';
alter system set fal_client='ORADB1' scope=spfile sid='ORADB1';
alter system set fal_client='ORADB2' scope=spfile sid='ORADB2';
alter system set fal_server='ORAADG1' scope=spfile sid='ORADB1';
alter system set fal_server='ORAADG2' scope=spfile sid='ORADB2';
alter system set standby_file_management='auto' scope=spfile sid='*';
alter system set log_archive_config='DG_CONFIG=(ORADB,ORAADG)' scope=spfile sid='*';
alter system set log_archive_dest_1='LOCATION=+DATA mandatory' scope=spfile sid='*';
alter system set log_archive_dest_2='SERVICE=ORAADG1 LGWR ASYNC valid_for=(online_logfiles, primary_role) db_unique_name=ORAADG' scope=spfile sid='ORADB1';
--alter system set log_archive_dest_2='SERVICE=ORAADG2 LGWR ASYNC valid_for=(online_logfiles, primary_role) db_unique_name=ORAADG' scope=spfile sid='ORADB2';
alter system set log_archive_dest_state_1='ENABLE' scope=spfile sid='*';
alter system set log_archive_dest_state_2='ENABLE' scope=spfile sid='*';
alter system set log_archive_max_processes=4 scope=spfile sid='*';
alter system set db_unique_name='ORADB' scope=spfile sid='*';shutdown immediate
SQL> shutdown immediate
2번 노드
SQL> shutdown immediate
1번, 2번 노드
SQL> startup
|
*log_archive_dest_2는 broker 설정시 값이 들어가있으면 에러가 나기때문에 주석처리함
패스워드 파일 복제(기존 RAC 1번 노드), grid와 db명령은 .bash_profile에 등록해놓은 alias임
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
기존 RAC 1번 노드
$ grid
+ASM1
/oracle/app/grid/19c
$ asmcmd
ASMCMD> cd +DATA/ORADB/PASSWORD
ASMCMD> ls
pwdoradb.330.1101466069
ASMCMD> pwcopy pwdoradb.330.1101466069 /home/oracle/
ASMCMD> exit
$ db
ORADB1
/oracle/app/oracle/product/19c
$ rsync --progress /home/oracle/pwdoradb.330.1101466069 oel19adg1:/oracle/app/oracle/product/19c/dbs/orapwORAADG1
The authenticity of host 'oel19adg1 (192.168.137.50)' can't be established.
ECDSA key fingerprint is SHA256:sjdPi5sB0qgCnhO4Z8Ubftcl47tdoI9pOafXi0H5Na4.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added 'oel19adg1,192.168.137.50' (ECDSA) to the list of known hosts.
oracle@oel19adg1's password:
pwdoradb.330.1101466069
2,048 100% 0.00kB/s 0:00:00 (xfr#1, to-chk=0/1)
$ rsync --progress /home/oracle/pwdoradb.330.1101466069 oel19adg2:/oracle/app/oracle/product/19c/dbs/orapwORAADG2
The authenticity of host 'oel19adg2 (192.168.137.60)' can't be established.
ECDSA key fingerprint is SHA256:sjdPi5sB0qgCnhO4Z8Ubftcl47tdoI9pOafXi0H5Na4.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added 'oel19adg2,192.168.137.60' (ECDSA) to the list of known hosts.
oracle@oel19adg2's password:
pwdoradb.330.1101466069
2,048 100% 0.00kB/s 0:00:00 (xfr#1, to-chk=0/1)
|
파라미터 파일 복제(기존 RAC 1번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL> create pfile from spfile;
File created.
SQL> exit
$ cd $ORACLE_HOME/dbs
$ ls
initORADB1.ora
$ rsync --progress initORADB1.ora oel19adg1:/oracle/app/oracle/product/19c/dbs/initORAADG1.ora
oracle@oel19adg1's password:
initORADB1.ora
2,217 100% 0.00kB/s 0:00:00 (xfr#1, to-chk=0/1)
|
파라미터 파일 수정(추가 RAC 1번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
|
$ vi $ORACLE_HOME/dbs/initORAADG1.ora
*.audit_file_dest='/oracle/app/oracle/admin/ORAADG/adump'
*.audit_trail='none'
*.cluster_database=true
*.compatible='19.0.0'
*.control_files='+DATA/ORAADG/CONTROLFILE/current.329.1101466079'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_name='ORADB'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=8589934592
*.db_unique_name='ORAADG'
*.dg_broker_start=FALSE
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORAADGXDB)'
ORAADG1.fal_client='ORAADG1'
ORAADG2.fal_client='ORAADG2'
ORAADG1.fal_server='ORADB1'
ORAADG2.fal_server='ORADB2'
family:dw_helper.instance_mode='read-only'
ORAADG1.instance_number=1
ORAADG2.instance_number=2
*.local_listener='-oraagent-dummy-'
*.log_archive_config='DG_CONFIG=(ORADB,ORAADG)'
*.log_archive_dest_1='LOCATION=+DATA mandatory'
#*.log_archive_dest_2='SERVICE=ORADB LGWR ASYNC valid_for=(online_logfiles, primary_role) db_unique_name=ORADB'
#ORAADG1.log_archive_dest_2='SERVICE=ORADB1 LGWR ASYNC valid_for=(online_logfiles, primary_role) db_unique_name=ORADB'
#ORAADG2.log_archive_dest_2='SERVICE=ORADB2 LGWR ASYNC valid_for=(online_logfiles, primary_role) db_unique_name=ORADB'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_max_processes=4
*.memory_target=1500m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='exclusive'
*.standby_file_management='auto'
ORAADG2.thread=2
ORAADG1.thread=1
*.undo_tablespace='UNDOTBS1'
ORAADG2.undo_tablespace='UNDOTBS2'
ORAADG1.undo_tablespace='UNDOTBS1'
|
굵게 표시한 부분 수정, log_archive_dest_2는 모두 주석처리
asm 경로 생성(추가 RAC 1번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
추가 RAC 1번 노드
$ grid
+ASM1
/oracle/app/grid/19c
$ asmcmd
ASMCMD> cd +DATA
ASMCMD> ls
ASMCMD> mkdir ORAADG
ASMCMD> cd ORAADG
ASMCMD> mkdir CONTROLFILE
ASMCMD> exit
$ db
ORAADG1
/oracle/app/oracle/product/19c
|
nomount로 기동(추가 RAC 1번 노드)
|
1
2
3
4
5
6
7
8
|
SQL> startup nomount pfile='/oracle/app/oracle/product/19c/dbs/initORAADG1.ora';
ORACLE instance started.
Total System Global Area 1577054664 bytes
Fixed Size 8896968 bytes
Variable Size 1023410176 bytes
Database Buffers 536870912 bytes
Redo Buffers 7876608 bytes
|
접속 테스트(기존 RAC에서 추가 RAC로, 추가 RAC에서 기존 RAC로)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
기존 RAC 1번, 2번 노드
$ sqlplus sys/oracle@ORAADG as sysdba
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
ORAADG1 STARTED
$ sqlplus sys/oracle@ORAADG1 as sysdba
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
ORAADG1 STARTED
추가 RAC 1번, 2번 노드
$ sqlplus sys/oracle@ORADB as sysdba
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
ORADB2 OPEN
$ sqlplus sys/oracle@ORADB1 as sysdba
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
ORADB1 OPEN
$ sqlplus sys/oracle@ORADB2 as sysdba
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
ORADB2 OPEN
|
rman 백업 경로 생성(기존 RAC 1번, 2번 노드, 추가 RAC 1번, 2번 노드)
|
1
|
$ mkdir -p /oracle/app/oracle/rman
|
rman 환경설정(기존 RAC 1번 노드)
|
1
2
3
4
|
$ rman target /
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oracle/app/oracle/rman/db_ctl_%F';
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/oracle/app/oracle/rman/%d_%U_%T.bk';
|
rman 원격 접속(기존 RAC 1번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
|
$ rman target sys/oracle@ORADB1 auxiliary sys/oracle@ORAADG1
Recovery Manager: Release 19.0.0.0.0 - Production on Mon Apr 11 17:07:42 2022
Version 19.12.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORADB (DBID=2854730844)
connected to auxiliary database: ORADB (not mounted)
RMAN>
|
duplicate 명령으로 복제(기존 RAC 1번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
|
RMAN> duplicate target database for standby from active database;
--로그--
Starting Duplicate Db at 11-APR-22
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=1 instance=ORAADG1 device type=DISK
contents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format '/oracle/app/oracle/product/19c/dbs/orapwORAADG1' ;
}
executing Memory Script
Starting backup at 11-APR-22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=111 instance=ORADB1 device type=DISK
Finished backup at 11-APR-22
duplicating Online logs to Oracle Managed File (OMF) location
duplicating Datafiles to Oracle Managed File (OMF) location
contents of Memory Script:
{
sql clone "create spfile from memory";
shutdown clone immediate;
startup clone nomount;
restore clone from service 'ORADB1' standby controlfile;
}
executing Memory Script
sql statement: create spfile from memory
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1577054664 bytes
Fixed Size 8896968 bytes
Variable Size 1023410176 bytes
Database Buffers 536870912 bytes
Redo Buffers 7876608 bytes
Starting restore at 11-APR-22
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=56 instance=ORAADG1 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ORADB1
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output file name=+DATA/ORAADG/CONTROLFILE/current.335.1101748117
Finished restore at 11-APR-22
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 2 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;
restore
from nonsparse from service
'ORADB1' 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 11-APR-22
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 ORADB1
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:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ORADB1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00002 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ORADB1
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:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ORADB1
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 ORADB1
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
Finished restore at 11-APR-22
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=1101748146 file name=+DATA/ORAADG/DATAFILE/system.332.1101748125
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=1101748146 file name=+DATA/ORAADG/DATAFILE/sysaux.328.1101748133
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=1101748147 file name=+DATA/ORAADG/DATAFILE/undotbs1.326.1101748139
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=1101748147 file name=+DATA/ORAADG/DATAFILE/undotbs2.324.1101748143
datafile 5 switched to datafile copy
input datafile copy RECID=10 STAMP=1101748147 file name=+DATA/ORAADG/DATAFILE/users.321.1101748143
Finished Duplicate Db at 11-APR-22
--로그--
|
db 상태 확인(추가 RAC 1번 노드)
|
1
2
3
4
5
|
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
ORAADG1 MOUNTED
|
duplicate 완료 후에는 추가 RAC 1번 노드가 mount 상태가됨
db role 확인(기존 RAC 1번 노드, 추가 RAC 1번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
기존 RAC 1번 노드
SQL> select inst_id, database_role, open_mode from gv$database;
INST_ID DATABASE_ROLE OPEN_MODE
---------- ---------------- --------------------
1 PRIMARY READ WRITE
2 PRIMARY READ WRITE
추가 RAC 1번 노드
SQL> select inst_id, database_role, open_mode from gv$database;
INST_ID DATABASE_ROLE OPEN_MODE
---------- ---------------- --------------------
1 PHYSICAL STANDBY MOUNTED
|
파라미터 파일 변경
현재 파라미터 파일 확인(추가 RAC 1번 노드)
|
1
2
3
4
5
|
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /oracle/app/oracle/product/19c/dbs/spfileORAADG1.ora
|
spfile을 만들지 않았는데 자동으로 spfile을 사용중임
pfile로 만들어서 내용 확인(추가 RAC 1번 노드)
|
1
2
3
|
SQL> create pfile='/oracle/app/oracle/product/19c/dbs/tmp_pfile.ora' from spfile='/oracle/app/oracle/product/19c/dbs/spfileORAADG1.ora';
File created.
|
만들어진 pfile 확인(추가 RAC 1번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
|
$ cat $ORACLE_HOME/dbs/tmp_pfile.ora
ORAADG1.__data_transfer_cache_size=0
*.__db_cache_size=512M
ORAADG1.__db_cache_size=536870912
ORAADG1.__inmemory_ext_roarea=0
ORAADG1.__inmemory_ext_rwarea=0
ORAADG1.__java_pool_size=16777216
ORAADG1.__large_pool_size=16777216
*.__oracle_base='/oracle/app/oracle'# ORACLE_BASE set from environment
ORAADG1.__pga_aggregate_target=637534208
ORAADG1.__sga_target=939524096
ORAADG1.__shared_io_pool_size=0
*.__shared_pool_size=336M
ORAADG1.__shared_pool_size=352321536
ORAADG1.__streams_pool_size=0
ORAADG1.__unified_pga_pool_size=0
*._always_anti_join='CHOOSE'
*._always_semi_join='CHOOSE'
*._b_tree_bitmap_plans=TRUE
*._bloom_serial_filter='ON'
*._complex_view_merging=TRUE
*._compression_compatibility='19.0.0'
*._diag_adr_trace_dest='/oracle/app/oracle/diag/rdbms/oraadg/ORAADG1/trace'
*._ds_xt_split_count=1
*._eliminate_common_subexpr=TRUE
*._fast_full_scan_enabled=TRUE
*._generalized_pruning_enabled=TRUE
*._gs_anti_semi_join_allowed=TRUE
*._improved_outerjoin_card=TRUE
*._improved_row_length_enabled=TRUE
*._index_join_enabled=TRUE
*._key_vector_create_pushdown_threshold=20000
*._ksb_restart_policy_times='0','60','120','240'# internal update to set default
*._left_nested_loops_random=TRUE
*._mv_access_compute_fresh_data='ON'
*._new_initial_join_orders=TRUE
*._new_sort_cost_estimate=TRUE
*._nlj_batching_enabled=1
*._optim_enhance_nnull_detection=TRUE
*._optim_peek_user_binds=TRUE
*._optimizer_ads_use_partial_results=TRUE
*._optimizer_better_inlist_costing='ALL'
*._optimizer_cbqt_or_expansion='ON'
*._optimizer_cluster_by_rowid_control=129
*._optimizer_control_shard_qry_processing=65528
*._optimizer_cost_based_transformation='LINEAR'
*._optimizer_cost_model='CHOOSE'
*._optimizer_extended_cursor_sharing='UDO'
*._optimizer_extended_cursor_sharing_rel='SIMPLE'
*._optimizer_extended_stats_usage_control=192
*._optimizer_join_order_control=3
*._optimizer_max_permutations=2000
*._optimizer_mode_force=TRUE
*._optimizer_native_full_outer_join='FORCE'
*._optimizer_or_expansion='DEPTH'
*._optimizer_proc_rate_level='BASIC'
*._optimizer_system_stats_usage=TRUE
*._optimizer_try_st_before_jppd=TRUE
*._optimizer_use_cbqt_star_transformation=TRUE
*._or_expand_nvl_predicate=TRUE
*._ordered_nested_loop=TRUE
*._parallel_broadcast_enabled=TRUE
*._pivot_implementation_method='CHOOSE'
*._pred_move_around=TRUE
*._push_join_predicate=TRUE
*._push_join_union_view=TRUE
*._push_join_union_view2=TRUE
*._px_dist_agg_partial_rollup_pushdown='ADAPTIVE'
*._px_groupby_pushdown='FORCE'
*._px_partial_rollup_pushdown='ADAPTIVE'
*._px_shared_hash_join=FALSE
*._px_wif_dfo_declumping='CHOOSE'
*._sql_model_unfold_forloops='RUN_TIME'
*._subquery_pruning_mv_enabled=FALSE
*._table_scan_cost_plus_one=TRUE
*._union_rewrite_for_gs='YES_GSET_MVS'
*._unnest_subquery=TRUE
*._use_column_stats_for_function=TRUE
*._xt_sampling_scan_granules='ON'
*.audit_file_dest='/oracle/app/oracle/admin/ORAADG/adump'
*.audit_trail='NONE'
*.cluster_database=TRUE
*.compatible='19.0.0'
*.connection_brokers='((TYPE=DEDICATED)(BROKERS=1))','((TYPE=EMON)(BROKERS=1))'# connection_brokers default value
*.control_files='+DATA/ORAADG/CONTROLFILE/current.335.1101748117'#Restore Controlfile
*.core_dump_dest='/oracle/app/oracle/diag/rdbms/oraadg/ORAADG1/cdump'
*.cpu_count=1
*.cpu_min_count='1'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_name='ORADB'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=8G
*.db_unique_name='ORAADG'
*.dg_broker_start=FALSE
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORAADGXDB)'
*.fal_client='ORAADG1'
*.fal_server='ORADB1'
*.instance_number=1
*.local_listener='-oraagent-dummy-'
*.log_archive_config='DG_CONFIG=(ORADB,ORAADG)'
*.log_archive_dest_1='LOCATION=+DATA mandatory'
#*.log_archive_dest_2='SERVICE=ORADB1 LGWR ASYNC valid_for=(online_logfiles, primary_role) db_unique_name=ORADB'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_max_processes=4
*.log_buffer=7360K# log buffer update
*.memory_target=1504M
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.optimizer_mode='ALL_ROWS'
*.plsql_warnings='DISABLE:ALL'# PL/SQL warnings at init.ora
*.processes=300
*.query_rewrite_enabled='TRUE'
*.remote_login_passwordfile='EXCLUSIVE'
*.result_cache_max_size=3872K
*.standby_file_management='auto'
*.thread=1
*.undo_tablespace='UNDOTBS1'
|
옵티마이저 관련 히든 파라미터와 함께 기본 내용들이 들어있음
하지만 2번 노드 내용이 일부 빠져있음(fal_client 등등)
controlfile 이름도 변경되어 있음(#Restore Controlfile 부분)
기존에 사용한 pfile에 몇가지 파라미터만 추가, 변경(추가 RAC 1번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
|
$ cat $ORACLE_HOME/dbs/tmp_pfile.ora
$ vi $ORACLE_HOME/dbs/initORAADG1.ora
*.audit_file_dest='/oracle/app/oracle/admin/ORAADG/adump'
*.audit_trail='none'
*.cluster_database=true
*.compatible='19.0.0'
*.connection_brokers='((TYPE=DEDICATED)(BROKERS=1))','((TYPE=EMON)(BROKERS=1))'# connection_brokers default value
*.control_files='+DATA/ORAADG/CONTROLFILE/current.335.1101748117'#Restore Controlfile
*.core_dump_dest='/oracle/app/oracle/diag/rdbms/oraadg/ORAADG/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_name='ORADB'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=8589934592
*.db_unique_name='ORAADG'
*.dg_broker_start=FALSE
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORAADGXDB)'
ORAADG1.fal_client='ORAADG1'
ORAADG2.fal_client='ORAADG2'
ORAADG1.fal_server='ORADB1'
ORAADG2.fal_server='ORADB2'
family:dw_helper.instance_mode='read-only'
ORAADG1.instance_number=1
ORAADG2.instance_number=2
*.local_listener='-oraagent-dummy-'
*.log_archive_config='DG_CONFIG=(ORADB,ORAADG)'
*.log_archive_dest_1='LOCATION=+DATA mandatory'
#*.log_archive_dest_2='SERVICE=ORADB LGWR ASYNC valid_for=(online_logfiles, primary_role) db_unique_name=ORADB'
#ORAADG1.log_archive_dest_2='SERVICE=ORADB1 LGWR ASYNC valid_for=(online_logfiles, primary_role) db_unique_name=ORADB'
#ORAADG2.log_archive_dest_2='SERVICE=ORADB2 LGWR ASYNC valid_for=(online_logfiles, primary_role) db_unique_name=ORADB'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_max_processes=4
*.memory_target=1500m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='exclusive'
*.standby_file_management='auto'
ORAADG2.thread=2
ORAADG1.thread=1
*.undo_tablespace='UNDOTBS1'
ORAADG2.undo_tablespace='UNDOTBS2'
ORAADG1.undo_tablespace='UNDOTBS1'
|
굵게 표시한 부분 추가, log_archive_dest_2는 모두 주석처리
기존 spfile 백업(추가 RAC 1번 노드)
|
1
2
|
$ cd $ORACLE_HOME/dbs
$ mv spfileORAADG1.ora spfileORAADG1.orabak
|
pfile로 spfile 생성(추가 RAC 1번 노드)
|
1
2
3
|
SQL> create spfile='+DATA' from pfile;
File created.
|
spfile 파일명 확인(추가 RAC 1번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
|
$ grid
+ASM1
/oracle/app/grid/19c
$ asmcmd
ASMCMD> cd +DATA/ORAADG/PARAMETERFILE
ASMCMD> ls
spfile.318.1101748681
ASMCMD> exit
$ db
ORAADG1
/oracle/app/oracle/product/19c
|
spfile의 파일명은 spfile.318.1101748681 임
임시 파라미터 파일 생성(추가 RAC 1번 노드)
|
1
2
3
4
|
$ cd $ORACLE_HOME/dbs/
$ mv initORAADG1.ora initORAADG1.orabak
$ vi initORAADG1.ora
spfile=+DATA/ORAADG/PARAMETERFILE/spfile.318.1101748681
|
db 재기동(추가 RAC 1번 노드)
|
1
2
|
SQL> shutdown immediate
SQL> startup mount
|
파라미터 파일 확인(추가 RAC 1번 노드)
|
1
2
3
4
5
|
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/ORAADG/PARAMETERFILE/spfile.318.1101748681
|
정상적으로 spfile 사용중임
configuration 설정
브로커 파라미터 공유 볼륨으로 변경(기존 RAC 1번 노드, 추가 RAC 1번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
|
기존 RAC 1번 노드
SQL>
alter system set dg_broker_config_file1='+DATA/ORADB/dr1ORADB.dat' scope=both sid='*';
alter system set dg_broker_config_file2='+DATA/ORADB/dr2ORADB.dat' scope=both sid='*';
alter system set dg_broker_start=true scope=both sid='*';
추가 RAC 1번 노드
SQL>
alter system set dg_broker_config_file1='+DATA/ORAADG/dr1ORAADG.dat' scope=both sid='*';
alter system set dg_broker_config_file2='+DATA/ORAADG/dr2ORAADG.dat' scope=both sid='*';
alter system set dg_broker_start=true scope=both sid='*';
|
기존 RAC configuration 생성 및 추가 RAC 등록(기존 RAC 1번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
$ dgmgrl sys/oracle
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Mon Apr 11 18:02:17 2022
Version 19.12.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "ORADB"
Connected as SYSDBA.
DGMGRL> create configuration 'DR_ORADB' as primary database is 'ORADB' connect identifier is 'ORADB';
Configuration "DR_ORADB" created with primary database "ORADB"
DGMGRL> add database 'ORAADG' as connect identifier is 'ORAADG';
Database "ORAADG" added
|
configuration 확인(기존 RAC 1번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
DGMGRL> show configuration
Configuration - DR_ORADB
Protection Mode: MaxPerformance
Members:
ORADB - Primary database
ORAADG - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
DISABLED
|
configuration 활성화(기존 RAC 1번 노드)
|
1
2
|
DGMGRL> enable configuration
Enabled.
|
enable 후 바로 조회 했을 때는 에러가 발생했지만 잠시후 시도하니 에러가 사라짐
에러가 사라지지 않을 경우 추가 RAC 1번 노드(standby db) shutdown 후 startup mount로 재기동
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
DGMGRL> show configuration
Configuration - DR_ORADB
Protection Mode: MaxPerformance
Members:
ORADB - Primary database
Warning: ORA-16905: The member was not enabled yet.
ORAADG - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
WARNING (status updated 70 seconds ago)
잠시후(1분 미만) 또는 standby 재기동 후 다시 조회
DGMGRL> show configuration
Configuration - DR_ORADB
Protection Mode: MaxPerformance
Members:
ORADB - Primary database
ORAADG - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 59 seconds ago)
|
정상
broker에 등록된 정보 조회(database)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
DGMGRL> SHOW DATABASE 'ORADB'
Database - ORADB
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
ORADB1
ORADB2
Database Status:
SUCCESS
DGMGRL> SHOW DATABASE 'ORAADG'
Database - ORAADG
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: 675.00 KByte/s
Real Time Query: OFF
Instance(s):
ORAADG1
Database Status:
SUCCESS
|
모두 정상
로그스위치 확인
기존 RAC 1번, 2번 노드, 추가 RAC 1번 노드 모두 alert log tail 로 확인
|
1
2
3
4
5
6
7
8
|
기존 RAC 1번 노드
$ tail -f /oracle/app/oracle/diag/rdbms/oradb/ORADB1/trace/alert_ORADB1.log
기존 RAC 2번 노드
$ tail -f /oracle/app/oracle/diag/rdbms/oradb/ORADB2/trace/alert_ORADB2.log
추가 RAC 1번 노드
$ tail -f /oracle/app/oracle/diag/rdbms/oraadg/ORAADG1/trace/alert_ORAADG1.log
|
로그 스위치 실행(기존 RAC 1번 노드)
|
1
2
3
|
SQL> alter system switch logfile;
System altered.
|
alert log 확인(기존 RAC 1번, 2번 노드, 추가 RAC 1번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
기존 RAC 1번 노드
2022-04-11T18:31:03.894508+09:00
Thread 1 advanced to log sequence 18 (LGWR switch), current SCN: 1106656
Current log# 2 seq# 18 mem# 0: +DATA/ORADB/ONLINELOG/group_2.328.1101466081
2022-04-11T18:31:03.935872+09:00
ARC1 (PID:191884): Archived Log entry 14 added for T-1.S-17 ID 0xaa28a65c LAD:1
기존 RAC 2번 노드
x
추가 RAC 1번 노드
2022-04-11T18:30:43.109192+09:00
MRP0 (PID:223464): Media Recovery Waiting for T-1.S-18
2022-04-11T18:30:43.128836+09:00
ARC0 (PID:198396): Archived Log entry 5 added for T-1.S-17 ID 0xaa28a65c LAD:1
2022-04-11T18:30:43.267760+09:00
rfs (PID:224594): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is ASYNC (PID:259396)
rfs (PID:224594): Primary database is in MAXIMUM PERFORMANCE mode
2022-04-11T18:30:43.287604+09:00
rfs (PID:224594): Selected LNO:5 for T-1.S-18 dbid 2854730844 branch 1101466076
2022-04-11T18:30:44.153701+09:00
Recovery of Online Redo Log: Thread 1 Group 5 Seq 18 Reading mem 0
Mem# 0: +DATA/ORAADG/ONLINELOG/group_5.331.1101748153
Mem# 1: +DATA/ORAADG/ONLINELOG/group_5.330.1101748153
|
db 상태 확인(기존 RAC 1번 노드, 추가 RAC 1번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
기존 RAC 1번 노드
SQL> select inst_id, db_unique_name, open_mode, database_role, switchover_status, dataguard_broker from gv$database;
INST_ID DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR
---------- ------------------------------ -------------------- ---------------- -------------------- --------
2 ORADB READ WRITE PRIMARY TO STANDBY ENABLED
1 ORADB READ WRITE PRIMARY TO STANDBY ENABLED
추가 RAC 1번 노드
SQL> select inst_id, db_unique_name, open_mode, database_role, switchover_status, dataguard_broker from gv$database;
INST_ID DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR
---------- ------------------------------ -------------------- ---------------- -------------------- --------
1 ORAADG MOUNTED PHYSICAL STANDBY NOT ALLOWED ENABLED
|
Active Data Guard로 전환(Standby db를 open 상태로 전환)
Managed Recovery Process 일시 중지(추가 RAC 1번 노드)
|
1
2
3
|
SQL> alter database recover managed standby database cancel;
Database altered.
|
Standby 데이터베이스를 open(추가 RAC 1번 노드)
|
1
2
3
|
SQL> alter database open;
Database altered.
|
Managed Recovery Process를 다시 기동(추가 RAC 1번 노드)
|
1
2
3
|
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
|
db 상태 확인(기존 RAC 1번 노드, 추가 RAC 1번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
기존 RAC 1번 노드
SQL> select inst_id, db_unique_name, open_mode, database_role, switchover_status, dataguard_broker from gv$database;
INST_ID DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR
---------- ------------------------------ -------------------- ---------------- -------------------- --------
2 ORADB READ WRITE PRIMARY TO STANDBY ENABLED
1 ORADB READ WRITE PRIMARY TO STANDBY ENABLED
추가 RAC 1번 노드
SQL> select inst_id, db_unique_name, open_mode, database_role, switchover_status, dataguard_broker from gv$database;
INST_ID DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR
---------- ------------------------------ -------------------- ---------------- -------------------- --------
1 ORAADG READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED ENABLED
|
active db 상태는 그대로지만 standby db는 open_mode가 read only로 변경됨
추가 RAC 2번 노드 추가
임시 파라미터 파일 생성(추가 RAC 2번 노드)
|
1
2
3
|
$ cd $ORACLE_HOME/dbs/
$ vi initORAADG2.ora
spfile=+DATA/ORAADG/PARAMETERFILE/spfile.318.1101748681
|
db 기동(추가 RAC 2번 노드)
|
1
2
3
4
5
6
7
8
9
10
|
SQL> startup
ORACLE instance started.
Total System Global Area 1577054664 bytes
Fixed Size 8896968 bytes
Variable Size 1023410176 bytes
Database Buffers 536870912 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
|
db 상태 확인(기존 RAC 1번 노드, 추가 RAC 1번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
기존 RAC 1번 노드
SQL>
set lines 200 pages 1000
select inst_id, db_unique_name, open_mode, database_role, switchover_status, dataguard_broker from gv$database;
INST_ID DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR
---------- ------------------------------ -------------------- ---------------- -------------------- --------
2 ORADB READ WRITE PRIMARY TO STANDBY ENABLED
1 ORADB READ WRITE PRIMARY TO STANDBY ENABLED
추가 RAC 1번 노드
SQL>
set lines 200 pages 1000
select inst_id, db_unique_name, open_mode, database_role, switchover_status, dataguard_broker from gv$database;
INST_ID DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR
---------- ------------------------------ -------------------- ---------------- -------------------- --------
1 ORAADG READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED ENABLED
2 ORAADG READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED ENABLED
|
현재 crs 상태 확인(추가 RAC 1번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
|
$ crsctl stat res -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
ONLINE ONLINE oel19adg1 STABLE
ONLINE ONLINE oel19adg2 STABLE
ora.chad
ONLINE ONLINE oel19adg1 STABLE
ONLINE ONLINE oel19adg2 STABLE
ora.net1.network
ONLINE ONLINE oel19adg1 STABLE
ONLINE ONLINE oel19adg2 STABLE
ora.ons
ONLINE ONLINE oel19adg1 STABLE
ONLINE ONLINE oel19adg2 STABLE
ora.proxy_advm
OFFLINE OFFLINE oel19adg1 STABLE
OFFLINE OFFLINE oel19adg2 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
1 ONLINE ONLINE oel19adg1 STABLE
2 ONLINE ONLINE oel19adg2 STABLE
ora.DATA.dg(ora.asmgroup)
1 ONLINE ONLINE oel19adg1 STABLE
2 ONLINE ONLINE oel19adg2 STABLE
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE oel19adg1 STABLE
ora.OCR_VOTE.dg(ora.asmgroup)
1 ONLINE ONLINE oel19adg1 STABLE
2 ONLINE ONLINE oel19adg2 STABLE
ora.asm(ora.asmgroup)
1 ONLINE ONLINE oel19adg1 Started,STABLE
2 ONLINE ONLINE oel19adg2 Started,STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
1 ONLINE ONLINE oel19adg1 STABLE
2 ONLINE ONLINE oel19adg2 STABLE
ora.cvu
1 ONLINE ONLINE oel19adg1 STABLE
ora.oel19adg1.vip
1 ONLINE ONLINE oel19adg1 STABLE
ora.oel19adg2.vip
1 ONLINE ONLINE oel19adg2 STABLE
ora.qosmserver
1 ONLINE ONLINE oel19adg1 STABLE
ora.scan1.vip
1 ONLINE ONLINE oel19adg1 STABLE
--------------------------------------------------------------------------------
|
db가 등록되어 있지 않음
crs에 db 등록(추가 RAC 1번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
$ srvctl add database -db ORAADG -oraclehome /oracle/app/oracle/product/19c -role physical_standby -startoption open -spfile +DATA/ORAADG/PARAMETERFILE/spfile.318.1101748681
$ srvctl add instance -db ORAADG -instance ORAADG1 -node oel19adg1
$ srvctl add instance -db ORAADG -instance ORAADG2 -node oel19adg2
$ srvctl start database -d ORAADG
$ srvctl config database -d ORAADG
Database unique name: ORAADG
Database name:
Oracle home: /oracle/app/oracle/product/19c
Oracle user: oracle
Spfile: +DATA/ORAADG/PARAMETERFILE/spfile.318.1101748681
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools:
Disk Groups:
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: dba
Database instances: ORAADG1,ORAADG2
Configured nodes: oel19adg1,oel19adg2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed
|
crs 상태 재확인(추가 RAC 1번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
|
$ crsctl stat res -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
ONLINE ONLINE oel19adg1 STABLE
ONLINE ONLINE oel19adg2 STABLE
ora.chad
ONLINE ONLINE oel19adg1 STABLE
ONLINE ONLINE oel19adg2 STABLE
ora.net1.network
ONLINE ONLINE oel19adg1 STABLE
ONLINE ONLINE oel19adg2 STABLE
ora.ons
ONLINE ONLINE oel19adg1 STABLE
ONLINE ONLINE oel19adg2 STABLE
ora.proxy_advm
OFFLINE OFFLINE oel19adg1 STABLE
OFFLINE OFFLINE oel19adg2 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
1 ONLINE ONLINE oel19adg1 STABLE
2 ONLINE ONLINE oel19adg2 STABLE
ora.DATA.dg(ora.asmgroup)
1 ONLINE ONLINE oel19adg1 STABLE
2 ONLINE ONLINE oel19adg2 STABLE
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE oel19adg1 STABLE
ora.OCR_VOTE.dg(ora.asmgroup)
1 ONLINE ONLINE oel19adg1 STABLE
2 ONLINE ONLINE oel19adg2 STABLE
ora.asm(ora.asmgroup)
1 ONLINE ONLINE oel19adg1 Started,STABLE
2 ONLINE ONLINE oel19adg2 Started,STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
1 ONLINE ONLINE oel19adg1 STABLE
2 ONLINE ONLINE oel19adg2 STABLE
ora.cvu
1 ONLINE ONLINE oel19adg1 STABLE
ora.oel19adg1.vip
1 ONLINE ONLINE oel19adg1 STABLE
ora.oel19adg2.vip
1 ONLINE ONLINE oel19adg2 STABLE
ora.oraadg.db
1 ONLINE ONLINE oel19adg1 Open,Readonly,HOME=/
oracle/app/oracle/pr
oduct/19c,STABLE
2 ONLINE ONLINE oel19adg2 Open,Readonly,HOME=/
oracle/app/oracle/pr
oduct/19c,STABLE
ora.qosmserver
1 ONLINE ONLINE oel19adg1 STABLE
ora.scan1.vip
1 ONLINE ONLINE oel19adg1 STABLE
--------------------------------------------------------------------------------
|
db가 등록되고 online으로 나옴
패스워드 파일 asm 영역에 등록(추가 RAC 1번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
$ grid
+ASM1
/oracle/app/grid/19c
$ asmcmd
ASMCMD> mkdir +DATA/ORAADG/PASSWORD/
ASMCMD> pwcopy --dbuniquename ORAADG /oracle/app/oracle/product/19c/dbs/orapwORAADG1 +DATA/ORAADG/PASSWORD/orapwORAADG
copying /oracle/app/oracle/product/19c/dbs/orapwORAADG1 -> +DATA/ORAADG/PASSWORD/orapwORAADG
ASMCMD> pwget --dbuniquename ORAADG
+DATA/ORAADG/PASSWORD/orapwORAADG
ASMCMD> exit
$ db
ORAADG1
/oracle/app/oracle/product/19c
|
정상적으로 asm영역에 패스워드 파일을 등록함
추가 RAC grid에 TNS_ADMIN 등록(추가 RAC 1번, 2번 노드)
|
1
2
3
4
5
6
7
|
추가 RAC 1번 노드
$ srvctl setenv database -d ORAADG -T "TNS_ADMIN=/oracle/app/grid/19c/network/admin"
추가 RAC 1번, 2번 노드 확인
$ srvctl getenv database -d ORAADG -t "TNS_ADMIN"
ORADB:
TNS_ADMIN=/oracle/app/grid/19c/network/admin
|
리스너 재기동(추가 RAC 1번 노드)
|
1
2
3
4
5
|
추가 1번 노드
$ srvctl stop listener -node oel19adg1 -l listener
srvctl stop listener -node oel19adg2 -l listener
srvctl start listener -node oel19adg1 -l listener
srvctl start listener -node oel19adg2 -l listener
|
데이터가드 정상 확인(기존 RAC 1번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
|
$ dgmgrl sys/oracle
DGMGRL> VALIDATE NETWORK CONFIGURATION FOR ALL ;
Connecting to instance "ORADB1" on database "ORADB" ...
Connected to "ORADB"
Checking connectivity from instance "ORADB1" on database "ORADB to instance "ORAADG1" on database "ORAADG"...
Succeeded.
Checking connectivity from instance "ORADB1" on database "ORADB to instance "ORAADG2" on database "ORAADG"...
Succeeded.
Connecting to instance "ORADB2" on database "ORADB" ...
Connected to "ORADB"
Checking connectivity from instance "ORADB2" on database "ORADB to instance "ORAADG1" on database "ORAADG"...
Succeeded.
Checking connectivity from instance "ORADB2" on database "ORADB to instance "ORAADG2" on database "ORAADG"...
Succeeded.
Connecting to instance "ORAADG1" on database "ORAADG" ...
Connected to "ORAADG"
Checking connectivity from instance "ORAADG1" on database "ORAADG to instance "ORADB1" on database "ORADB"...
Succeeded.
Checking connectivity from instance "ORAADG1" on database "ORAADG to instance "ORADB2" on database "ORADB"...
Succeeded.
Connecting to instance "ORAADG2" on database "ORAADG" ...
Connected to "ORAADG"
Checking connectivity from instance "ORAADG2" on database "ORAADG to instance "ORADB1" on database "ORADB"...
Succeeded.
Checking connectivity from instance "ORAADG2" on database "ORAADG to instance "ORADB2" on database "ORADB"...
Succeeded.
Oracle Clusterware on database "ORADB" is available for database restart.
Oracle Clusterware on database "ORAADG" is available for database restart.
DGMGRL> show database verbose ORADB
Database - ORADB
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
ORADB1
ORADB2
Properties:
DGConnectIdentifier = 'ORADB'
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 ORAADG
Database - ORAADG
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: 6.00 KByte/s
Active Apply Rate: 1.63 MByte/s
Maximum Apply Rate: 1.63 MByte/s
Real Time Query: ON
Instance(s):
ORAADG1 (apply instance)
ORAADG2
Properties:
DGConnectIdentifier = 'ORAADG'
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
|
flashback database 활성화(선택)(fra가 설정되어있고 primary db에서만 설정 가능함)(기존 RAC 1번 노드)
기존 standby를 switchover 해서 primary로 변경 후 flashback database 활성화
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
$ dgmgrl sys/oracle
DGMGRL> switchover to ORAADG
Performing switchover NOW, please wait...
Operation requires a connection to database "ORAADG"
Connecting ...
Connected to "ORAADG"
Connected as SYSDBA.
New primary database "ORAADG" is opening...
Oracle Clusterware is restarting database "ORADB" ...
Connected to an idle instance.
Connected to an idle instance.
Connected to an idle instance.
Connected to an idle instance.
Connected to an idle instance.
Connected to "ORADB"
Connected to "ORADB"
Switchover succeeded, new primary is "oraadg"
|
정상적으로 switchover됨
configuration 확인(기존 RAC 1번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
DGMGRL> show configuration
Configuration - DR_ORADB
Protection Mode: MaxPerformance
Members:
ORAADG - Primary database
ORADB - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 57 seconds ago)
|
flashback database 활성화(추가 RAC 1번 노드)
|
1
2
3
4
5
6
7
8
9
10
|
SQL> alter database flashback on;
Database altered.
SQL> select inst_id, db_unique_name, flashback_on from gv$database;
INST_ID DB_UNIQUE_NAME FLASHBACK_ON
---------- ------------------------------ ------------------
2 ORAADG YES
1 ORAADG YES
|
다시 switchover 해서 primary, standby 원복(기존 RAC 1번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
$ dgmgrl sys/oracle
DGMGRL> switchover to ORADB
Performing switchover NOW, please wait...
New primary database "ORADB" is opening...
Oracle Clusterware is restarting database "ORAADG" ...
Connected to an idle instance.
Connected to an idle instance.
Connected to an idle instance.
Connected to an idle instance.
Connected to an idle instance.
Connected to "ORAADG"
Connected to "ORAADG"
Switchover succeeded, new primary is "oradb"
|
Oracle Active Data Guard 구성 완료
다음 게시글로 이동
다음 게시글에서는 real time redo apply 테스트와 switchover, failover를 테스트함
Oracle Linux 8.4에 Oracle 19c RAC to RAC ADG 설치 가이드_Part 4(https://positivemh.tistory.com/827)
참조 :
808783.1, 2329386.1, 1432367.1, 1130523.1, 1367311.1, 362656.1, 2804791.1, 1631552.1
https://docs.oracle.com/en/database/oracle/oracle-database/19/sbydb/LOG_ARCHIVE_DEST_n-parameter-attributes.html#GUID-9ACDEC6C-C2E0-4EDA-B66D-B1F819D3368D
https://docs.oracle.com/en/database/oracle/oracle-database/19/sbydb/troubleshooting-oracle-data-guard.html#GUID-97282862-4EA0-42D9-97EF-34E75A29F87F
https://docs.oracle.com/en/database/oracle/oracle-database/19/sbydb/index.html
https://docs.oracle.com/en/database/oracle/oracle-database/19/dgbkr/index.html
https://community.oracle.com/tech/apps-infra/discussion/2453742/error-12154-received-logging-on-to-the-standby
https://community.oracle.com/mosc/discussion/3986057/ur-a-parameter-in-tnsnames-ora
https://positivemh.tistory.com/674
https://positivemh.tistory.com/761
https://positivemh.tistory.com/765
https://positivemh.tistory.com/819
https://positivemh.tistory.com/820
https://positivemh.tistory.com/821
https://positivemh.tistory.com/822
https://positivemh.tistory.com/823
https://positivemh.tistory.com/825
https://positivemh.tistory.com/826
https://positivemh.tistory.com/827
https://positivemh.tistory.com/828
https://dataforum.io/display/ORCL/Oracle+Database+19c+Data+Guard
https://blog.goodusdata.com/95
https://blog.goodusdata.com/176
https://tawool.tistory.com/213
https://medium.com/@limaemerson/setting-active-data-guard-physical-standby-in-rac-one-nodes-architecture-part-1-3afcf5325eff
https://www.cndba.cn/zhoushao12/article/3674
https://floo.bar/2019/06/18/oracle-data-guard-broker-18-new-validate-network-configuration-command/
https://oracledbwr.com/step-by-step-configuration-of-data-guard-broker-for-rac-database/
https://dba.stackexchange.com/questions/285729/oracle-19c-with-sap-system-ora-12154-tnscould-not-resolve-the-connect-identi
'ORACLE > Install' 카테고리의 다른 글
| Windows 11에 Oracle 21c XE 설치 가이드 (0) | 2023.11.21 |
|---|---|
| Oracle Linux 8.4에 Oracle 19c RAC to RAC ADG 설치 가이드_Part 4 (0) | 2022.04.11 |
| Oracle Linux 8.4에 Oracle 19c RAC to RAC ADG 설치 가이드_Part 2 (0) | 2022.04.05 |
| Oracle Linux 8.4에 Oracle 19c RAC to RAC ADG 설치 가이드_Part 1 (2) | 2022.04.04 |
| Oracle Linux 8.4에 Oracle 19c ADG 설치 가이드_Part 3 (0) | 2022.03.30 |