반응형
OS환경 : Oracle Linux 8.4 (64bit)
DB 환경 : Oracle Database 19.12.0.0 ADG
OS 및 사전 설정은 아래 게시물 참조
Oracle Linux 8.4에 Oracle 19c ADG 설치 가이드_Part 1(https://positivemh.tistory.com/819)
방법 : Oracle Linux 8.4에 Oracle 19c ADG(Active Data Guard) 설치 가이드_Part 2
1번 노드(Active) hostname : oel19adg1, db_name : ORAADG, db_unique_name : ORAADG
2번 노드(Standby) hostname : oel19adg2, db_name : ORAADG, db_unique_name : ORAADGDR
*본문에 2번노드 표시가 없으면 모두 1번노드에서 작업
설치파일은 아래 3가지 파일을 이용함(RAC 구성 할때 받아놓은 패치파일 중 grid 패치파일만 빼고 사용함)
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 소프트웨어(엔진) 설치(1번, 2번 노드 모두 진행)
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.
|
패치파일 압축해제
|
1
2
|
$ cd /oracle/media
$ unzip p32900083_190000_Linux-x86-64.zip
|
runinstaller 실행(1번노드 모두 설치 후 2번노드 진행)
|
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가 로딩되기까지 시간이 조금 걸림
gui 로딩중
Set Up Software Only 선택
Single instance database installation 선택
Enterprise Edition 선택
oracle base 지정
oraInventroy 경로 지정
group 는 모두 dba 로 지정
설치 중 root 권한으로 스크립트 실행하는 부분에서 자동로 스크립트 실행할지 여부 지정 root 패스워드 입력
사전 요구사항 체크중
response 파일이 필요하다면 따로 저장 후 Install
db 엔진 설치중
root 계정으로 스크립트 실행할 지 물어보는 메세지 Yes
Close
db 엔진 설치 완료
OJVM 패치
|
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
dd
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
|
$ cd /oracle/media/32900083/32876380/
$ $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-03-28_06-44-54PM_1.log
Verifying environment and performing prerequisite checks...
OPatch continues with these patches: 32876380
Do you want to proceed? [y|n]
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
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 component oracle.javavm.client, 19.0.0.0.0...
Patch 32876380 successfully applied.
Log file location: /oracle/app/oracle/product/19c/cfgtoollogs/opatch/opatch2022-03-28_06-44-54PM_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.
|
리스너 생성(1번, 2번 노드 모두 진행)
netca 진행
|
1
|
$ netca
|
Listener configuration 선택
Add 선택
리스너 이름 지정
프로토콜 지정
포트 지정
No 선택
Next 선택
Finish 선택
DB 생성(1번 노드만 진행)
dbca 진행
|
1
|
$ dbca
|
gui 로딩중
Create a database 선택
Advanced configuration 선택
Oracle Single Instance database 선택 후 Custom Database 선택
SID 입력
데이터 저장영역 선택
FRA만 선택, 아카이브 사용하지 않음(나중에 설정)
리스너 선택
componets 필요시 선택 후 Next
메모리 설정
Character sets - Choose from the list of chracter sets - KO16MSWIN949 선택
Connection mode - Ddedicated server mode 선택 Next
체크 해제 후 Next
패스워드 입력
Yes
Create database 선택 후 Next
Finish
db 생성중
DB 확인
|
1
2
3
4
5
|
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
ORAADG OPEN
|
정상적으로 생성됨
pfile 생성 후 db 종료(1번 노드), spfile 이름변경
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL> create pfile from spfile;
File created.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
$ cd $ORACLE_HOME/dbs/
$ mv spfileORAADG.ora spfileORAADG.ora.bak
|
데이터 가드 구성 설정
아카이브 경로 생성(1번, 2번 노드)
|
1
2
3
4
5
|
1번 노드
$ mkdir -p /oracle/app/oracle/arch/adg1
2번 노드
$ mkdir -p /oracle/app/oracle/arch/adg2
|
adump, oradata, fra 경로 생성(2번 노드)
|
1
2
3
|
$ mkdir -p /oracle/app/oracle/admin/ORAADGDR/adump
$ mkdir -p /oracle/app/oracle/oradata/ORAADGDR
$ mkdir -p /oracle/app/oracle/fast_recovery_area/ORAADGDR
|
pfile 확인(1번 노드)
|
1
2
3
|
$ cd $ORACLE_HOME/dbs/
$ ls
hc_ORAADG.dat init.ora initORAADG.ora lkORAADG orapwORAADG spfileORAADG.ora.bak
|
pfile 내용 확인(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
|
$ vi initORAADG.ora
ORAADG.__data_transfer_cache_size=0
ORAADG.__db_cache_size=1828716544
ORAADG.__inmemory_ext_roarea=0
ORAADG.__inmemory_ext_rwarea=0
ORAADG.__java_pool_size=0
ORAADG.__large_pool_size=16777216
ORAADG.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment
ORAADG.__pga_aggregate_target=838860800
ORAADG.__sga_target=2516582400
ORAADG.__shared_io_pool_size=134217728
ORAADG.__shared_pool_size=520093696
*.audit_file_dest='/oracle/app/oracle/admin/ORAADG/adump'
*.audit_trail='none'
*.compatible='19.0.0'
*.control_files='/oracle/app/oracle/oradata/ORAADG/control01.ctl','/oracle/app/oracle/oradata/ORAADG/control02.ctl'
*.db_block_size=8192
*.db_name='ORAADG'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORAADGXDB)'
*.local_listener='LISTENER_ORAADG'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=800m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2400m
*.undo_tablespace='UNDOTBS1'
*.db_recovery_file_dest_size=7851m *.db_recovery_file_dest='/oracle/app/oracle/fast_recovery_area/' |
pfile 수정(1번 노드)
수정(23~34번째 줄 추가)
|
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
|
$ vi initORAADG.ora
*.audit_file_dest='/oracle/app/oracle/admin/ORAADG/adump'
*.audit_trail='none'
*.compatible='19.0.0'
*.control_files='/oracle/app/oracle/oradata/ORAADG/control01.ctl','/oracle/app/oracle/oradata/ORAADG/control02.ctl'
*.db_block_size=8192
*.db_name='ORAADG'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORAADGXDB)'
*.local_listener='LISTENER_ORAADG'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=800m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2400m
*.undo_tablespace='UNDOTBS1'
*.db_recovery_file_dest_size=7851m
*.db_recovery_file_dest='/oracle/app/oracle/fast_recovery_area/'
*.db_unique_name='ORAADG'
*.dg_broker_start=TRUE
*.fal_client='ORAADG'
*.fal_server='ORAADGDR'
*.standby_file_management='auto'
*.log_archive_config='DG_CONFIG=(ORAADG,ORAADGDR)'
*.log_archive_dest_1='LOCATION=/oracle/app/oracle/arch/adg1 valid_for=(all_logfiles, all_roles) db_unique_name=ORAADG'
#*.log_archive_dest_2='SERVICE=ORAADGDR ASYNC valid_for=(online_logfiles, primary_role) db_unique_name=ORAADGDR'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=4
|
*log_archive_dest_2는 broker 설정시 값이 들어가있으면 에러가 나기때문에 주석처리함
리스너 종료(1번 노드)
|
1
|
$ lsnrctl stop
|
리스너 파일 수정(1번 노드)
기존
|
1
2
3
4
5
6
7
8
9
10
11
|
$ vi listener.ora
# listener.ora Network Configuration File: /oracle/app/oracle/product/19c/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oel19adg1)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
|
수정(13~20번째 줄 추가)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
$ vi listener.ora
# listener.ora Network Configuration File: /oracle/app/oracle/product/19c/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oel19adg1)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORAADG)
(ORACLE_HOME = /oracle/app/oracle/product/19c)
(SID_NAME = ORAADG)
)
)
|
tnsnames.ora 파일 생성(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
|
$ cd $ORACLE_HOME/network/admin
$ vi tnsnames.ora
LISTENER_ORAADG =
(ADDRESS = (PROTOCOL = TCP)(HOST = oel19adg1)(PORT = 1521))
ORAADG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oel19adg1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORAADG)
)
)
ORAADGDR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oel19adg2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORAADGDR)
)
)
|
리스너 기동(1번 노드)
|
1
|
$ lsnrctl start
|
db 기동(1번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SQL> startup mount
ORACLE instance started.
Total System Global Area 2516581456 bytes
Fixed Size 8899664 bytes
Variable Size 536870912 bytes
Database Buffers 1962934272 bytes
Redo Buffers 7876608 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
|
기동 후 force logging 모드 실행(강제 로깅 기능)(1번 노드)
|
1
2
3
|
SQL> alter database force logging;
Database altered.
|
1번 노드 pfile을 2번 노드로 복사(1번 노드)
|
1
2
3
4
5
6
7
|
$ scp initORAADG.ora oel19adg2:/oracle/app/oracle/product/19c/dbs/initORAADGDR.ora
The authenticity of host 'oel19adg2 (192.168.137.20)' 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.20' (ECDSA) to the list of known hosts.
oracle@oel19adg2's password:
initORAADG.ora 100% 1641 1.6MB/s 00:00
|
pfile 수정(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
|
$ cd $ORACLE_HOME/dbs
$ vi initORAADGDR.ora
*.audit_file_dest='/oracle/app/oracle/admin/ORAADGDR/adump'
*.audit_trail='none'
*.compatible='19.0.0'
*.control_files='/oracle/app/oracle/oradata/ORAADGDR/control01.ctl','/oracle/app/oracle/oradata/ORAADGDR/control02.ctl'
*.db_block_size=8192
*.db_name='ORAADG'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORAADGDRXDB)'
*.local_listener='LISTENER_ORAADGDR'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=800m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2400m
*.undo_tablespace='UNDOTBS1'
*.db_recovery_file_dest_size=7851m
*.db_recovery_file_dest='/oracle/app/oracle/fast_recovery_area/'
*.db_unique_name='ORAADGDR'
*.db_file_name_convert = '/oracle/app/oracle/oradata/ORAADG/','/oracle/app/oracle/oradata/ORAADGDR/'
*.log_file_name_convert = '/oracle/app/oracle/oradata/ORAADG/','/oracle/app/oracle/oradata/ORAADGDR/'
*.dg_broker_start=TRUE
*.fal_client='ORAADGDR'
*.fal_server='ORAADG'
*.standby_file_management='auto'
*.log_archive_config='DG_CONFIG=(ORAADG,ORAADGDR)'
*.log_archive_dest_1='LOCATION=/oracle/app/oracle/arch/adg2 valid_for=(all_logfiles, all_roles) db_unique_name=ORAADGDR'
#*.log_archive_dest_2='SERVICE=ORAADG ASYNC valid_for=(online_logfiles, primary_role) db_unique_name=ORAADG'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=4
|
*log_archive_dest_2는 broker 설정시 값이 들어가있으면 에러가 나기때문에 주석처리함
패스워드 파일 복제(1번 노드)
|
1
|
$ scp orapwORAADG oel19adg2:/oracle/app/oracle/product/19c/dbs/orapwORAADGDR
|
Standby Log File 생성(1번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL> alter database add standby logfile '/oracle/app/oracle/oradata/ORAADG/standby_redo01.log' size 200m;
Database altered.
SQL> alter database add standby logfile '/oracle/app/oracle/oradata/ORAADG/standby_redo02.log' size 200m;
Database altered.
SQL> alter database add standby logfile '/oracle/app/oracle/oradata/ORAADG/standby_redo03.log' size 200m;
Database altered.
|
redo로그 생성 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL>
set lines 200 pages 1000
col member form a70
select group#, type, member from v$logfile;
GROUP# TYPE MEMBER
---------- ------- ----------------------------------------------------------------------
1 ONLINE /oracle/app/oracle/oradata/ORAADG/redo01.log
2 ONLINE /oracle/app/oracle/oradata/ORAADG/redo02.log
3 ONLINE /oracle/app/oracle/oradata/ORAADG/redo03.log
4 STANDBY /oracle/app/oracle/oradata/ORAADG/standby_redo01.log
5 STANDBY /oracle/app/oracle/oradata/ORAADG/standby_redo02.log
6 STANDBY /oracle/app/oracle/oradata/ORAADG/standby_redo03.log
6 rows selected.
|
flashback database 활성화(선택)(fra가 설정되어있고 primary db에서만 설정 가능함)(1번 노드)
|
1
2
3
|
SQL> alter database flashback on;
Database altered.
|
리스너 종료(2번 노드)
|
1
|
$ lsnrctl stop
|
리스너 파일 수정(2번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
$ cd $ORACLE_HOME/network/admin
$ vi listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oel19adg2)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORAADGDR)
(ORACLE_HOME = /oracle/app/oracle/product/19c)
(SID_NAME = ORAADGDR)
)
)
|
tnsnames.ora 파일 생성(2번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
$ cd $ORACLE_HOME/network/admin
$ vi tnsnames.ora
LISTENER_ORAADGDR =
(ADDRESS = (PROTOCOL = TCP)(HOST = oel19adg2)(PORT = 1521))
ORAADG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oel19adg1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORAADG)
)
)
ORAADGDR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oel19adg2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORAADGDR)
)
)
|
리스너 기동(2번 노드)
|
1
|
$ lsnrctl start
|
nomount로 기동(2번 노드)
|
1
2
3
4
5
6
7
8
|
SQL> startup nomount pfile='/oracle/app/oracle/product/19c/dbs/initORAADGDR.ora';
ORACLE instance started.
Total System Global Area 2516581456 bytes
Fixed Size 8899664 bytes
Variable Size 536870912 bytes
Database Buffers 1962934272 bytes
Redo Buffers 7876608 bytes
|
접속 테스트(1번 노드에서 2번 노드로, 2번 노드에서 1번 노드로)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
1번 노드
$ sqlplus sys/oracle@ORAADGDR as sysdba
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
ORAADGDR STARTED
2번 노드
$ sqlplus sys/oracle@ORAADG as sysdba
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
ORAADG OPEN
|
rman 백업 경로 생성(1번, 2번 노드)
|
1
|
$ mkdir -p /oracle/app/oracle/rman
|
rman 환경설정(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 원격 접속
|
1
2
3
4
5
6
7
8
9
10
11
|
$ rman target sys/oracle@ORAADG auxiliary sys/oracle@ORAADGDR
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Mar 29 06:38:38 2022
Version 19.12.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORAADG (DBID=2271272191)
connected to auxiliary database: ORAADG (not mounted)
RMAN>
|
duplicate 명령으로 복제
|
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
|
RMAN> duplicate target database for standby from active database;
--로그--
Starting Duplicate Db at 29-MAR-22
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=379 device type=DISK
contents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format '/oracle/app/oracle/product/19c/dbs/orapwORAADGDR' ;
}
executing Memory Script
Starting backup at 29-MAR-22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=269 device type=DISK
Finished backup at 29-MAR-22
contents of Memory Script:
{
restore clone from service 'ORAADG' standby controlfile;
}
executing Memory Script
Starting restore at 29-MAR-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 ORAADG
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oracle/app/oracle/oradata/ORAADGDR/control01.ctl
output file name=/oracle/app/oracle/oradata/ORAADGDR/control02.ctl
Finished restore at 29-MAR-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 tempfile 1 to
"/oracle/app/oracle/oradata/ORAADGDR/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/oracle/app/oracle/oradata/ORAADGDR/system01.dbf";
set newname for datafile 2 to
"/oracle/app/oracle/oradata/ORAADGDR/sysaux01.dbf";
set newname for datafile 3 to
"/oracle/app/oracle/oradata/ORAADGDR/undotbs01.dbf";
set newname for datafile 4 to
"/oracle/app/oracle/oradata/ORAADGDR/users01.dbf";
restore
from nonsparse from service
'ORAADG' clone database
;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /oracle/app/oracle/oradata/ORAADGDR/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 29-MAR-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 ORAADG
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /oracle/app/oracle/oradata/ORAADGDR/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ORAADG
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00002 to /oracle/app/oracle/oradata/ORAADGDR/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ORAADG
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /oracle/app/oracle/oradata/ORAADGDR/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ORAADG
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /oracle/app/oracle/oradata/ORAADGDR/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 29-MAR-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=1 STAMP=1100589126 file name=/oracle/app/oracle/oradata/ORAADGDR/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=1100589126 file name=/oracle/app/oracle/oradata/ORAADGDR/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=1100589126 file name=/oracle/app/oracle/oradata/ORAADGDR/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=1100589126 file name=/oracle/app/oracle/oradata/ORAADGDR/users01.dbf
Finished Duplicate Db at 29-MAR-22
--로그--
|
db 상태 확인(2번 노드)
|
1
2
3
4
5
|
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
ORAADGDR MOUNTED
|
duplicate 완료 후에는 2번 노드가 mount 상태가됨
db role 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
1번 노드
SQL> select database_role, open_mode from v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PRIMARY READ WRITE
2번 노드
SQL> select database_role, open_mode from v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY MOUNTED
|
파라미터 파일 변경
현재 파라미터 파일 확인(1, 2번 노드)
|
1
2
3
4
5
|
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
|
spfile에 아무런 값이 없으므로 pfile 을 사용중인것
spfile 생성(1, 2번 노드)
|
1
2
3
4
5
6
7
8
|
1번 노드
SQL> create spfile from pfile;
File created.
2번 노드
SQL> create spfile from pfile;
File created.
|
양쪽 노드 모두 재기동(1, 2번 노드)
|
1
2
3
4
5
6
7
|
1번 노드
SQL> shutdown immediate
SQL> startup
2번 노드
SQL> shutdown immediate
SQL> startup mount
|
파라미터 파일 확인(1, 2번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
1번 노드
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /oracle/app/oracle/product/19c/dbs/spfileORAADG.ora
2번 노드
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /oracle/app/oracle/product/19c/dbs/spfileORAADGDR.ora
|
정상적으로 spfile 사용중임
configuration 설정
1번 노드 configuration 생성(1번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
|
$ dgmgrl sys/oracle
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Mar 29 07:20:59 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 "ORAADG"
Connected as SYSDBA.
DGMGRL> create configuration 'DR_ORAADG' as primary database is 'ORAADG' connect identifier is 'ORAADG';
Configuration "DR_ORAADG" created with primary database "ORAADG"
|
2번 노드 추가(1번 노드)
|
1
2
|
DGMGRL> add database 'ORAADGDR' as connect identifier is 'ORAADGDR';
Database "ORAADGDR" added
|
configuration 확인(1번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
DGMGRL> show configuration
Configuration - DR_ORAADG
Protection Mode: MaxPerformance
Members:
ORAADG - Primary database
ORAADGDR - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
DISABLED
|
아직 Configuration Status가 DISABLED 상태임
configuration 활성화(1번 노드)
|
1
2
|
DGMGRL> enable configuration
Enabled.
|
enable 후 바로 조회 했을 때는 에러가 발생했지만 잠시후 시도하니 에러가 사라짐
에러가 사라지지 않을 경우 노드2(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
31
|
DGMGRL> show configuration
Configuration - DR_ORAADG
Protection Mode: MaxPerformance
Members:
ORAADG - Primary database
Warning: ORA-16905: The member was not enabled yet.
ORAADGDR - Physical standby database
Error: ORA-16810: multiple errors or warnings detected for the member
Fast-Start Failover: Disabled
Configuration Status:
ERROR (status updated 51 seconds ago)
잠시후 다시 조회(1분 미만)
DGMGRL> show configuration
Configuration - DR_ORAADG
Protection Mode: MaxPerformance
Members:
ORAADG - Primary database
ORAADGDR - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 55 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
|
DGMGRL> SHOW DATABASE 'ORAADG';
Database - ORAADG
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
ORAADG
Database Status:
SUCCESS
DGMGRL> SHOW DATABASE 'ORAADGDR';
Database - ORAADGDR
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: 41.00 KByte/s
Real Time Query: OFF
Instance(s):
ORAADG
Database Status:
SUCCESS
|
모두 정상
해당 시점에 1번노드 alert log 확인 시 처음에 에러가 발생하다가 잠시후 log_archive_dest_2, log_archive_dest_state_2, fal_server 파라미터를 자동으로 설정(변경)해줌
|
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
|
$ cd /oracle/app/oracle/diag/rdbms/oraadg/ORAADG/trace
$ vi alert_ORAADG.log
***********************************************************************
Fatal NI connect error 12521, connecting to:
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oel19adg2)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ORAADGDR)(INSTANCE_NAME=ORAADG)(CID=(PROGRAM=oracle)(HOST=oel19adg1)(USER=oracle))))
VERSION INFORMATION:
TNS for Linux: Version 19.0.0.0.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
Version 19.12.0.0.0
Time: 29-MAR-2022 08:34:31
Tracing not turned on.
Tns error struct:
ns main err code: 12564
TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
2022-03-29T08:34:31.887056+09:00
Starting background process RSM0
2022-03-29T08:34:31.893649+09:00
RSM0 started with pid=71, OS id=5248
2022-03-29T08:34:36.082773+09:00
RSM0 (PID:5248): Using STANDBY_ARCHIVE_DEST parameter default value as /oracle/app/oracle/arch/adg1 [krsd.c:18206]
2022-03-29T08:34:36.087193+09:00
ALTER SYSTEM SET log_archive_dest_2='service="ORAADGDR"','ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="ORAADGDR" net_timeout=30','valid_for=(online_logfile,all_roles)' SCOPE=BOTH;
2022-03-29T08:34:36.101786+09:00
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
2022-03-29T08:34:36.118017+09:00
ALTER SYSTEM SET fal_server='' SCOPE=BOTH;
2022-03-29T08:34:37.439623+09:00
Thread 1 advanced to log sequence 20 (LGWR switch), current SCN: 828000
Current log# 2 seq# 20 mem# 0: /oracle/app/oracle/oradata/ORAADG/redo02.log
2022-03-29T08:34:37.528132+09:00
ARC2 (PID:4926): Archived Log entry 11 added for T-1.S-19 ID 0x876117ff LAD:1
2022-03-29T08:36:33.851107+09:00
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
2022-03-29T08:36:33.859152+09:00
ALTER SYSTEM SET fal_server='' SCOPE=BOTH;
|
로그스위치 확인
1번, 2번 노드 모두 alert log tail 로 확인
|
1
2
3
4
5
|
1번 노드
$ tail -f /oracle/app/oracle/diag/rdbms/oraadg/ORAADG/trace/alert_ORAADG.log
2번 노드
$ tail -f /oracle/app/oracle/diag/rdbms/oraadgdr/ORAADGDR/trace/alert_ORAADGDR.log
|
1번 노드에서 로그 스위치 실행(1번 노드)
|
1
2
3
|
SQL> alter system switch logfile;
System altered.
|
1번, 2번 노드 모두 alert log tail 로 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
1번 노드
2022-03-29T08:50:51.953505+09:00
Thread 1 advanced to log sequence 21 (LGWR switch), current SCN: 830399
Current log# 3 seq# 21 mem# 0: /oracle/app/oracle/oradata/ORAADG/redo03.log
2022-03-29T08:50:51.988815+09:00
ARC0 (PID:4898): Archived Log entry 13 added for T-1.S-20 ID 0x876117ff LAD:1
2번 노드
2022-03-29T08:50:52.108070+09:00
rfs (PID:4476): Selected LNO:4 for T-1.S-21 dbid 2271272191 branch 1100546751
2022-03-29T08:50:52.134717+09:00
PR00 (PID:4499): Media Recovery Waiting for T-1.S-21 (in transit)
2022-03-29T08:50:52.134960+09:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 21 Reading mem 0
Mem# 0: /oracle/app/oracle/oradata/ORAADGDR/standby_redo01.log
2022-03-29T08:50:52.141276+09:00
ARC0 (PID:4363): Archived Log entry 3 added for T-1.S-20 ID 0x876117ff LAD:1
|
db 상태 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
1번 노드
set lines 200 pages 1000
select db_unique_name, open_mode, database_role, switchover_status, dataguard_broker from v$database;
DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR
------------------------------ -------------------- ---------------- -------------------- --------
ORAADG READ WRITE PRIMARY TO STANDBY ENABLED
2번 노드
set lines 200 pages 1000
select db_unique_name, open_mode, database_role, switchover_status, dataguard_broker from v$database;
DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR
------------------------------ -------------------- ---------------- -------------------- --------
ORAADGDR MOUNTED PHYSICAL STANDBY NOT ALLOWED ENABLED
|
Oracle Data Guard 구성 완료(Standby db가 mount 상태)
Active Data Guard로 전환(Standby db를 open 상태로 전환)
Managed Recovery Process 일시 중지(2번 노드)
|
1
2
3
|
SQL> alter database recover managed standby database cancel;
Database altered.
|
Standby 데이터베이스를 open(2번 노드)
|
1
2
3
|
SQL> alter database open;
Database altered.
|
Managed Recovery Process를 다시 기동(2번 노드)
|
1
2
3
|
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
|
db 상태 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
1번 노드
set lines 200 pages 1000
select db_unique_name, open_mode, database_role, switchover_status, dataguard_broker from v$database;
DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR
------------------------------ -------------------- ---------------- -------------------- --------
ORAADG READ WRITE PRIMARY TO STANDBY ENABLED
2번 노드
set lines 200 pages 1000
select db_unique_name, open_mode, database_role, switchover_status, dataguard_broker from v$database;
DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR
------------------------------ -------------------- ---------------- -------------------- --------
ORAADGDR READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED ENABLED
|
active db 상태는 그대로지만 standby db는 open_mode가 read only로 변경됨
에러 방지를 위한 추가 설정
기존 StaticConnectIdentifierd 확인(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
|
$ dgmgrl sys/oracle
DGMGRL> SHOW instance verbose 'ORAADG' on database 'ORAADG';
Instance 'ORAADG' of database 'ORAADG'
PFILE:
Properties:
HostName = 'oel19adg1'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel19adg1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORAADG_DGMGRL)(INSTANCE_NAME=ORAADG)(SERVER=DEDICATED)))'
TopWaitEvents = '(monitor)'
SidName = '(monitor)'
Log file locations:
Alert log : /oracle/app/oracle/diag/rdbms/oraadg/ORAADG/trace/alert_ORAADG.log
Data Guard Broker log : /oracle/app/oracle/diag/rdbms/oraadg/ORAADG/trace/drcORAADG.log
Instance Status:
SUCCESS
DGMGRL> SHOW instance verbose 'ORAADG' on database 'ORAADGDR';
Instance 'ORAADG' of database 'ORAADGDR'
PFILE:
Properties:
HostName = 'oel19adg2'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel19adg2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORAADGDR_DGMGRL)(INSTANCE_NAME=ORAADGDR)(SERVER=DEDICATED)))'
TopWaitEvents = '(monitor)'
SidName = '(monitor)'
Log file locations:
Alert log : /oracle/app/oracle/diag/rdbms/oraadgdr/ORAADGDR/trace/alert_ORAADGDR.log
Data Guard Broker log : /oracle/app/oracle/diag/rdbms/oraadgdr/ORAADGDR/trace/drcORAADGDR.log
Instance Status:
SUCCESS
|
현재 기존 StaticConnectIdentifierd 값으로는 switchover시 제대로 통신이 되지않아 에러가 발생함(SERVICE_NAME이 각각 ORAADG_DGMGRL, ORAADGDR_DGMGRL로 되어있음)
$ORACLE_HOME/network/admin/tnstnames.ora에 있는 tns 별칭을 넣어줘야함
현재 등록된 identifier가 사용가능한(통신가능한) 상태인지 확인(1번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
DGMGRL> validate static connect identifier for 'ORAADG';
Oracle Clusterware is not configured on database "ORAADG".
Connecting to database "ORAADG" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel19adg1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORAADG_DGMGRL)(INSTANCE_NAME=ORAADG)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))" ...
Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel19adg1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORAADG_DGMGRL)(INSTANCE_NAME=ORAADG)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Failed.
DGMGRL> validate static connect identifier for 'ORAADGDR';
Oracle Clusterware is not configured on database "ORAADGDR".
Connecting to database "ORAADGDR" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel19adg2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORAADGDR_DGMGRL)(INSTANCE_NAME=ORAADGDR)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))" ...
Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel19adg2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORAADGDR_DGMGRL)(INSTANCE_NAME=ORAADGDR)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Failed.
|
ORAADG, ORAADGDR 모두 사용 불가함(Failed.)
StaticConnectIdentifierd 변경($ORACLE_HOME/network/admin/tnstnames.ora에 있는 tns 별칭으로 변경)(1번 노드)
|
1
2
3
4
|
DGMGRL> edit database 'ORAADG' set property StaticConnectIdentifier = 'ORAADG';
Property "staticconnectidentifier" updated
DGMGRL> edit database 'ORAADGDR' set property StaticConnectIdentifier = 'ORAADGDR';
Property "staticconnectidentifier" updated
|
정상적으로 변경됨
현재 등록된 identifier가 사용가능한(통신가능한) 상태인지 재확인(1번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
|
DGMGRL> validate static connect identifier for 'ORAADG';
Oracle Clusterware is not configured on database "ORAADG".
Connecting to database "ORAADG" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel19adg1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORAADG)(CID=(PROGRAM=dgmgrl)(HOST=oel19adg1)(USER=oracle))(STATIC_SERVICE=TRUE)))" ...
Succeeded.
The static connect identifier allows for a connection to database "ORAADG".
DGMGRL> validate static connect identifier for 'ORAADGDR';
Oracle Clusterware is not configured on database "ORAADGDR".
Connecting to database "ORAADGDR" using static connect identifier "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oel19adg2)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ORAADGDR)(CID=(PROGRAM=dgmgrl)(HOST=oel19adg1)(USER=oracle))(STATIC_SERVICE=TRUE)))" ...
Succeeded.
The static connect identifier allows for a connection to database "ORAADGDR".
|
ORAADG, ORAADGDR 모두 정상적으로 사용 가능함(Succeeded.)
StaticConnectIdentifierd 재확인(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
|
DGMGRL> SHOW instance verbose 'ORAADG' on database 'ORAADG';
Instance 'ORAADG' of database 'ORAADG'
PFILE:
Properties:
HostName = 'oel19adg1'
StaticConnectIdentifier = 'ORAADG'
TopWaitEvents = '(monitor)'
SidName = '(monitor)'
Log file locations:
Alert log : /oracle/app/oracle/diag/rdbms/oraadg/ORAADG/trace/alert_ORAADG.log
Data Guard Broker log : /oracle/app/oracle/diag/rdbms/oraadg/ORAADG/trace/drcORAADG.log
Instance Status:
SUCCESS
DGMGRL> SHOW instance verbose 'ORAADG' on database 'ORAADGDR';
Instance 'ORAADG' of database 'ORAADGDR'
PFILE:
Properties:
HostName = 'oel19adg2'
StaticConnectIdentifier = 'ORAADGDR'
TopWaitEvents = '(monitor)'
SidName = '(monitor)'
Log file locations:
Alert log : /oracle/app/oracle/diag/rdbms/oraadgdr/ORAADGDR/trace/alert_ORAADGDR.log
Data Guard Broker log : /oracle/app/oracle/diag/rdbms/oraadgdr/ORAADGDR/trace/drcORAADGDR.log
Instance Status:
SUCCESS
|
정상적으로 변경됨
flashback database 활성화(선택)(fra가 설정되어있고 primary db에서만 설정 가능함)(2번 노드)
기존 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 ORAADGDR
Performing switchover NOW, please wait...
Operation requires a connection to database "ORAADGDR"
Connecting ...
Connected to "ORAADGDR"
Connected as SYSDBA.
New primary database "ORAADGDR" is opening...
Operation requires start up of instance "ORAADG" on database "ORAADG"
Starting instance "ORAADG"...
Connected to an idle instance.
ORACLE instance started.
Connected to "ORAADG"
Database mounted.
Database opened.
Connected to "ORAADG"
Switchover succeeded, new primary is "oraadgdr"
|
정상적으로 switchover됨
configuration 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
$ dgmgrl sys/oracle
DGMGRL> show configuration
Configuration - DR_ORAADG
Protection Mode: MaxPerformance
Members:
ORAADGDR - Primary database
ORAADG - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 13 seconds ago)
|
정상적으로 switchover됨
flashback database 활성화(2번 노드)
|
1
2
3
|
SQL> alter database flashback on;
Database altered.
|
flashback database 확인(1번, 2번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
1번 노드
SQL> select db_unique_name, flashback_on from v$database;
DB_UNIQUE_NAME FLASHBACK_ON
------------------------------ ------------------
ORAADG YES
2번 노드
SQL> select db_unique_name, flashback_on from v$database;
DB_UNIQUE_NAME FLASHBACK_ON
------------------------------ ------------------
ORAADGDR YES
|
다시 switchover 해서 primary, standby 원복
|
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> 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...
Operation requires start up of instance "ORAADGDR" on database "ORAADGDR"
Starting instance "ORAADGDR"...
Connected to an idle instance.
ORACLE instance started.
Connected to "ORAADGDR"
Database mounted.
Database opened.
Connected to "ORAADGDR"
Switchover succeeded, new primary is "oraadg"
DGMGRL> show configuration
Configuration - DR_ORAADG
Protection Mode: MaxPerformance
Members:
ORAADG - Primary database
ORAADGDR - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 49 seconds ago)
|
Oracle Active Data Guard 구성 완료
추가 확인
현재 recovery mode 확인(1번, 2번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
1번 노드
SQL>
set lines 200 pages 1000
select dest_id, status, database_mode, recovery_mode, db_unique_name, gap_status
from v$archive_dest_status where dest_id <=2;
DEST_ID STATUS DATABASE_MODE RECOVERY_MODE DB_UNIQUE_NAME GAP_STATUS
---------- --------- --------------- ---------------------------------- -------------------- ------------------------
1 VALID OPEN IDLE ORAADG
2 VALID OPEN_READ-ONLY MANAGED REAL TIME APPLY WITH QUERY ORAADGDR NO GAP
2번 노드
SQL>
set lines 200 pages 1000
select dest_id, status, database_mode, recovery_mode, db_unique_name, gap_status
from v$archive_dest_status where dest_id <=2;
DEST_ID STATUS DATABASE_MODE RECOVERY_MODE DB_UNIQUE_NAME GAP_STATUS
---------- --------- --------------- ---------------------------------- ------------------------------ ------------------------
1 VALID OPEN_READ-ONLY MANAGED REAL TIME APPLY ORAADGDR
2 INACTIVE UNKNOWN IDLE NONE
|
1번 노드에서 조회했을때는 1번(본인노드)의 database mode와 recovery mode가 각각 OPEN에 IDLE로 나오고
2번 노드(standby)는 OPEN_READ-ONLY에 MANAGED REAL TIME APPLY WITH QUERY로 나옴
2번 노드에서 조회했을때는 1번(본인노드)의 database mode와 recovery mode가 각각 OPEN_READ-ONLY에 MANAGED REAL TIME APPLY로 나오고
2번 노드는 UNKNOWN에 IDLE로 나옴
각 노드에서 log_archive_dest1, 2를 확인해보면 2번노드는 log_archive_dest_2가 비어있음(standby db이기때문에 redo log(아카이브로그)를 보낼 필요가 없어서 그런듯함)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
1번 노드
SQL> show parameter log_archive_dest_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string LOCATION=/oracle/app/oracle/arch/adg1 valid_for=(all_logfiles, all_roles) db_unique_name=ORAADG
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service="ORAADGDR", ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="ORAADGDR"
net_timeout=30, valid_for=(online_logfile,all_roles)
2번 노드
SQL> show parameter log_archive_dest_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string LOCATION=/oracle/app/oracle/arch/adg2 valid_for=(all_logfiles, all_roles) db_unique_name=ORAADGDR
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string
|
출처 : Oracle Linux 8.4에 Oracle 19c ADG 설치 가이드_Part 2 :: 내맘대로긍정 (tistory.com)
반응형
'오라클 > 참고' 카테고리의 다른 글
| 오라클 19c rman catalog 및 catalog db 생성 (0) | 2023.01.10 |
|---|---|
| 오라클 19c ADG 설치3 (0) | 2023.01.05 |
| 오라클 19c ADG 설치1 (0) | 2023.01.05 |
| 오라클 RAC 설치 간단 정리 (2) | 2023.01.04 |
| 오라클 ADG 설치 3 (0) | 2023.01.04 |