복사되었습니다!
OS 환경 : Oracle Linux 9.6 (64bit)
DB 환경 : Oracle Database 19.28.0.0
방법 : 오라클 19c Restart(ASM) to 싱글(FS) ADG 구성 가이드
본문에서는 이미 Restart(ASM)로 구성된 서버와 19c 엔진만 설치된 Filesystem 서버를 준비해두고, 이 환경에서 ADG를 구성해봄
동일 환경으로 ADG 구성하는것과의 차이점은 db_file_name_convert, log_file_name_convert에 ASM 경로 -> FS 경로를 넣어주는 부분만 다름
싱글(FS) to 싱글(FS) ADG 구성 가이드는 아래 게시물을 참고하면됨
참고 : Oracle Linux 8.4에 Oracle 19c ADG 설치 가이드_Part 1 ( https://positivemh.tistory.com/819 )
RAC(ASM) to RAC(ASM) ADG 구성 가이드는 아래 게시물을 참고하면됨
참고 : Oracle Linux 8.4에 Oracle 19c RAC to RAC ADG 설치 가이드_Part 1 ( https://positivemh.tistory.com/824 )
Primary(Active) hostname : ora19, db_name : oracle19, db_unique_name : oracle19
Standby(Standby) hostname : ora19stb, db_name : oracle19, db_unique_name : oracle19stb
테스트
|
1
2
3
4
5
6
|
# cat /etc/hosts
#Dataguard Primary
192.168.137.50 ora19
#Dataguard Standby
192.168.137.60 ora19stb
|
Primary 리스너 정보(정적리스너 설정 필요)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
$ cat /app/grid/product/19c/network/admin/listener.ora
#Backup file is /app/oracle/crsdata/ora19/output/listener.ora.bak.ora19.oracle line added by Agent
# listener.ora Network Configuration File: /app/grid/product/19c/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora19)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = oracle19)
(ORACLE_HOME = /app/oracle/product/19c)
(SID_NAME = oracle19)
)
)
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=ON # line added by Agent
|
Standby 리스너 정보(정적리스너 설정 필요)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
$ cat /app/oracle/product/19c/network/admin/listener.ora
# listener.ora Network Configuration File: /app/oracle/product/19c/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora19stb)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = oracle19stb)
(ORACLE_HOME = /app/oracle/product/19c)
(SID_NAME = oracle19stb)
)
)
|
Primary tns 정보
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
$ cat /app/oracle/product/19c/network/admin/tnsnames.ora
ORACLE19 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora19)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oracle19)
)
)
ORACLE19STB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora19stb)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oracle19stb)
)
)
|
Standby tns 정보
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
$ cat /app/oracle/product/19c/network/admin/tnsnames.ora
ORACLE19 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora19)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oracle19)
)
)
ORACLE19STB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora19stb)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oracle19stb)
)
)
|
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
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
|
$ lsnrctl start
#Primary 리스너
$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 07-JUN-2026 16:11:50
Copyright (c) 1991, 2025, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 07-JUN-2026 16:11:43
Uptime 0 days 0 hr. 0 min. 6 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /app/grid/product/19c/network/admin/listener.ora
Listener Log File /app/oracle/diag/tnslsnr/ora19/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora19)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "oracle19" has 1 instance(s).
Instance "oracle19", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
#Standby 리스너
$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 07-JUN-2026 15:40:52
Copyright (c) 1991, 2025, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora19stb)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 07-JUN-2026 15:33:55
Uptime 0 days 0 hr. 6 min. 57 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /app/oracle/product/19c/network/admin/listener.ora
Listener Log File /app/oracle/diag/tnslsnr/ora19stb/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora19stb)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "oracle19stb" has 2 instance(s).
Instance "oracle19stb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
|
현재 db는 아카이브로그 모드 상태임
|
1
2
3
4
5
6
7
|
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +DATA
Oldest online log sequence 5
Next log sequence to archive 7
Current log sequence 7
|
Force Logging 설정
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL>
set lines 200 pages 1000
select name, force_logging from v$database;
NAME FORCE_LOGGING
--------- ---------------------------------------
ORACLE19 NO
SQL> alter database force logging;
Database altered.
SQL> select name, force_logging from v$database;
NAME FORCE_LOGGING
--------- ---------------------------------------
ORACLE19 YES
|
현재 데이터파일, 템프파일 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL>
set lines 200 pages 1000
col tablespace_name for a20
col file_name for a70
select tablespace_name, file_id, file_name, round(bytes/1024/1024/1024, 2) gb,
round(maxbytes/1024/1024/1024, 2) max_gb, autoextensible, status, online_status from dba_data_files
union all
select tablespace_name, file_id, file_name, round(bytes/1024/1024/1024, 2) gb,
round(maxbytes/1024/1024/1024, 2) max_gb, autoextensible, status, null from dba_temp_files order by 2;
TABLESPACE_NAME FILE_ID FILE_NAME GB MAX_GB AUT STATUS ONLINE_
-------------------- ---------- ---------------------------------------------------------------------- ---------- ---------- --- --------- -------
SYSTEM 1 +DATA/ORACLE19/DATAFILE/system.261.1220456359 .98 32 YES AVAILABLE SYSTEM
TEMP 1 +DATA/ORACLE19/TEMPFILE/temp.264.1220456361 .49 32 YES ONLINE
SYSAUX 2 +DATA/ORACLE19/DATAFILE/sysaux.262.1220456361 .98 32 YES AVAILABLE ONLINE
UNDOTBS1 3 +DATA/ORACLE19/DATAFILE/undotbs1.263.1220456361 .62 32 YES AVAILABLE ONLINE
USERS 4 +DATA/ORACLE19/DATAFILE/users.265.1220456367 0 32 YES AVAILABLE ONLINE
|
+DATA/ORACLE19/DATAFILE, +DATA/ORACLE19/TEMPFILE에 존재함
ADG 관련 파라미터 설정
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
alter system set log_archive_config='DG_CONFIG=(oracle19,oracle19stb)' scope=both;
alter system set log_archive_dest_1='location=+data valid_for=(all_logfiles,all_roles) db_unique_name=oracle19' scope=both;
alter system set log_archive_dest_2='service=oracle19stb lgwr async noaffirm valid_for=(online_logfiles,primary_role) db_unique_name=oracle19stb' scope=both;
alter system set log_archive_max_processes=4 scope=both;
alter system set fal_client=oracle19 scope=both;
alter system set fal_server=oracle19stb scope=both;
alter system set db_file_name_convert='+DATA/ORACLE19/DATAFILE','/app/oracle/oradata/oracle19stb','+DATA/ORACLE19/TEMPFILE','/app/oracle/oradata/oracle19stb' scope=spfile;
alter system set log_file_name_convert='+DATA/ORACLE19/ONLINELOG','/app/oracle/oradata/oracle19stb' scope=spfile;
alter system set standby_file_management=auto scope=both;
|
db 재기동
|
1
2
3
|
SQL>
shutdown immediate
startup
|
적용된 파라미터 확인
|
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
|
SQL>
set lines 200 pages 1000
col value for a120
col name for a50
select name, value
from v$parameter
where name in ('db_name','db_unique_name','log_archive_config', 'log_archive_dest_1','log_archive_dest_2',
'log_archive_dest_state_1','log_archive_dest_state_2', 'remote_login_passwordfile',
'log_archive_format','log_archive_max_processes','fal_client','fal_server','db_file_name_convert',
'log_file_name_convert', 'standby_file_management', 'dg_broker_start');
NAME VALUE
-------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------
db_file_name_convert +DATA/ORACLE19/DATAFILE, /app/oracle/oradata/oracle19stb, +DATA/ORACLE19/TEMPFILE, /app/oracle/oradata/oracle19stb
log_file_name_convert +DATA/ORACLE19/ONLINELOG, /app/oracle/oradata/oracle19stb
log_archive_dest_1 location=+data valid_for=(all_logfiles,all_roles) db_unique_name=oracle19
log_archive_dest_2 service=oracle19stb lgwr async noaffirm valid_for=(online_logfiles,primary_role) db_unique_name=oracle19stb
log_archive_dest_state_1 enable
log_archive_dest_state_2 enable
fal_client ORACLE19
fal_server ORACLE19STB
log_archive_config DG_CONFIG=(oracle19,oracle19stb)
log_archive_format %t_%s_%r.arc
log_archive_max_processes 4
standby_file_management AUTO
remote_login_passwordfile EXCLUSIVE
db_name oracle19
db_unique_name oracle19
dg_broker_start FALSE
16 rows selected.
|
파라미터가 잘 적용됨
현재 redo 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL>
set lines 200
set pages 1000
col member for a60
select thread#, l.group#, member, archived, l.status, (bytes/1024/1024) MB, sequence#
from v$log l, v$logfile f
where f.group# = l.group#
order by 1, 2;
THREAD# GROUP# MEMBER ARC STATUS MB SEQUENCE#
---------- ---------- ------------------------------------------------------------ --- ---------------- ---------- ----------
1 1 +DATA/ORACLE19/ONLINELOG/group_1.258.1235315217 YES INACTIVE 200 22
1 2 +DATA/ORACLE19/ONLINELOG/group_2.275.1235315197 NO CURRENT 200 23
1 3 +DATA/ORACLE19/ONLINELOG/group_3.260.1235315267 YES INACTIVE 200 21
|
3개 존재함
Standby용 redo 생성
|
1
2
3
4
|
SQL>
alter database add standby logfile '+DATA' size 200M;
alter database add standby logfile '+DATA' size 200M;
alter database add standby logfile '+DATA' size 200M;
|
현재 redo 재확인
|
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
|
SQL> select group#, type, member from v$logfile order by 1;
GROUP# TYPE MEMBER
---------- ------- ------------------------------------------------------------
1 ONLINE +DATA/ORACLE19/ONLINELOG/group_1.258.1235315217
2 ONLINE +DATA/ORACLE19/ONLINELOG/group_2.275.1235315197
3 ONLINE +DATA/ORACLE19/ONLINELOG/group_3.260.1235315267
4 STANDBY +DATA/ORACLE19/ONLINELOG/group_4.284.1235315377
5 STANDBY +DATA/ORACLE19/ONLINELOG/group_5.285.1235315381
6 STANDBY +DATA/ORACLE19/ONLINELOG/group_6.286.1235315381
SQL>
set lines 200
set pages 1000
col member for a60
select l.thread#, l.group#, type, member, l.archived, l.status, (l.bytes/1024/1024) MB, l.sequence#
from v$standby_log l, v$logfile f
where 1=1
and f.group# = l.group#
order by 1, 2;
THREAD# GROUP# TYPE MEMBER ARC STATUS MB SEQUENCE#
---------- ---------- ------- ------------------------------------------------------------ --- ---------- ---------- ----------
0 4 STANDBY +DATA/ORACLE19/ONLINELOG/group_4.284.1235315377 YES UNASSIGNED 200 0
0 5 STANDBY +DATA/ORACLE19/ONLINELOG/group_5.285.1235315381 YES UNASSIGNED 200 0
0 6 STANDBY +DATA/ORACLE19/ONLINELOG/group_6.286.1235315381 YES UNASSIGNED 200 0
|
standby redo log가 추가됨
Standby용 pfile 생성 및 Standby로 전송
|
1
2
3
4
5
6
7
8
|
SQL> create pfile from spfile;
File created.
$ cd /app/oracle/product/19c/dbs
$ scp initoracle19.ora ora19stb:/app/oracle/product/19c/dbs/initoracle19stb
oracle@ora19stb's password:
initoracle19stb.ora 100% 1632 3.6MB/s 00:00
|
패스워드 파일 확인
|
1
2
|
$ srvctl config database -d oracle19 | grep -i "Password file"
Password file: +DATA/ORACLE19/PASSWORD/pwdoracle19.287.1235316209
|
ASM에 존재함
pwcopy 명령으로 os로 복제
|
1
|
$ asmcmd pwcopy '+DATA/ORACLE19/PASSWORD/pwdoracle19.287.1235316209' '/home/oracle/orapworacle19' //orapw[SID]
|
패스워드 파일 Standby로 전송
|
1
2
3
|
$ scp /home/oracle/orapworacle19 ora19stb:/app/oracle/product/19c/dbs/orapworacle19stb
oracle@ora19stb's password:
orapworacle19 100% 2048 2.6MB/s 00:00
|
Standby 서버에서 pfile 수정(굵게 표시한 부분 수정)
|
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
|
$ cd /app/oracle/product/19c/dbs
$ vi initoracle19stb.ora
*.audit_file_dest='/app/oracle/admin/oracle19stb/adump' <<-- !!
*.audit_trail='none'
*.compatible='19.0.0'
*.control_files='/app/oracle/oradata/oracle19stb/control01.ctl' <<-- !!
*.db_block_size=8192
*.db_create_file_dest='/app/oracle/oradata/oracle19stb' <<-- !!
*.db_file_name_convert='+DATA/ORACLE19/DATAFILE','/app/oracle/oradata/oracle19stb','+DATA/ORACLE19/TEMPFILE','/app/oracle/oradata/oracle19stb'
*.db_unique_name='oracle19stb' <<-- !!
*.db_name='oracle19'
*.diagnostic_dest='/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=oracle19stbXDB)' <<-- !!
*.fal_client='ORACLE19STB' <<-- !!
*.fal_server='ORACLE19' <<-- !!
*.log_archive_config='DG_CONFIG=(oracle19,oracle19stb)'
*.log_archive_dest_1='location=/app/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=oracle19stb' <<-- !!
*.log_archive_dest_2='service=oracle19 lgwr async noaffirm valid_for=(online_logfiles,primary_role) db_unique_name=oracle19' <<-- !!
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=4
*.log_file_name_convert='+DATA/ORACLE19/ONLINELOG','/app/oracle/oradata/oracle19stb'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=522m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1564m
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
|
Standby 서버에서 adump 경로 생성
|
1
|
$ mkdir -p /app/oracle/admin/oracle19stb/adump
|
Standby 서버의 .bash_profile 파일 수정(ORACLE_SID, DB_UNIQUE_NAME 수정)
|
1
2
3
4
5
6
7
8
|
$ cat /home/oracle/.bash_profile
export ORACLE_BASE=/app/oracle;
export ORACLE_HOME=$ORACLE_BASE/product/19c;
export ORACLE_SID=oracle19stb;
export DB_UNIQUE_NAME=oracle19stb;
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib;
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;
|
Standby DB nomount 기동
|
1
2
3
4
5
6
7
8
|
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1644165832 bytes
Fixed Size 8940232 bytes
Variable Size 402653184 bytes
Database Buffers 1224736768 bytes
Redo Buffers 7835648 bytes
|
양방향 sqlplus 접속 테스트
|
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
|
#Primary에서 테스트
$ sqlplus sys/oracle@oracle19 as sysdba
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
oracle19 OPEN
$ sqlplus sys/oracle@oracle19stb as sysdba
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
oracle19stb STARTED
#Standby에서 테스트
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
oracle19 OPEN
$ sqlplus sys/oracle@oracle19stb as sysdba
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
oracle19stb STARTED
|
양방향 잘 접속됨(접속이 안된다면 정적리스너 설정 다시 확인 필요함)
Primary 서버에서 rman 접속
|
1
2
3
4
5
6
7
8
9
10
11
|
$ rman target sys/oracle@oracle19 auxiliary sys/oracle@oracle19stb
Recovery Manager: Release 19.0.0.0.0 - Production on Sun Jun 7 16:20:36 2026
Version 19.28.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORACLE19 (DBID=3400652514)
connected to auxiliary database: ORACLE19 (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
|
RMAN> duplicate target database for standby from active database dorecover;
duplicate target database for standby from active database;
Starting Duplicate Db at 2026-06-07 16:47:16
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
contents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format '/app/oracle/product/19c/dbs/orapworacle19stb' ;
}
executing Memory Script
Starting backup at 2026-06-07 16:47:18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=281 device type=DISK
Finished backup at 2026-06-07 16:47:21
contents of Memory Script:
{
restore clone from service 'oracle19' standby controlfile;
}
executing Memory Script
Starting restore at 2026-06-07 16:47: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 oracle19
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/app/oracle/oradata/oracle19stb/control01.ctl
Finished restore at 2026-06-07 16:47:26
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
"/app/oracle/oradata/oracle19stb/temp.264.1220456361";
switch clone tempfile all;
set newname for datafile 1 to
"/app/oracle/oradata/oracle19stb/system.261.1220456359";
set newname for datafile 2 to
"/app/oracle/oradata/oracle19stb/sysaux.262.1220456361";
set newname for datafile 3 to
"/app/oracle/oradata/oracle19stb/undotbs1.263.1220456361";
set newname for datafile 4 to
"/app/oracle/oradata/oracle19stb/users.265.1220456367";
restore
from nonsparse from service
'oracle19' clone database
;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /app/oracle/oradata/oracle19stb/temp.264.1220456361 in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 2026-06-07 16:47:31
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 oracle19
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /app/oracle/oradata/oracle19stb/system.261.1220456359
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 oracle19
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00002 to /app/oracle/oradata/oracle19stb/sysaux.262.1220456361
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 oracle19
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /app/oracle/oradata/oracle19stb/undotbs1.263.1220456361
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 oracle19
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /app/oracle/oradata/oracle19stb/users.265.1220456367
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2026-06-07 16:47:40
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=1235319319 file name=/app/oracle/oradata/oracle19stb/system.261.1220456359
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=1235319319 file name=/app/oracle/oradata/oracle19stb/sysaux.262.1220456361
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=1235319319 file name=/app/oracle/oradata/oracle19stb/undotbs1.263.1220456361
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=1235319319 file name=/app/oracle/oradata/oracle19stb/users.265.1220456367
Finished Duplicate Db at 2026-06-07 16:47:42
|
복제됨
커맨드에 dorecover를 붙여주면 duplicate가 오래 걸려도 그사이 생긴 아카이브로그도 Standby에 적용해줌
참고로 병렬 채널 할당도 가능함
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
RMAN>
RUN {
# 1. Target(Primary) 채널 4개 할당 (ASM 디스크 Read 담당)
ALLOCATE CHANNEL p1 DEVICE TYPE DISK;
ALLOCATE CHANNEL p2 DEVICE TYPE DISK;
ALLOCATE CHANNEL p3 DEVICE TYPE DISK;
ALLOCATE CHANNEL p4 DEVICE TYPE DISK;
# 2. Auxiliary(Standby) 채널 4개 할당 (FS 디스크 Write 담당)
ALLOCATE AUXILIARY CHANNEL s1 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL s2 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL s3 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL s4 DEVICE TYPE DISK;
# 3. Duplicate 명령 수행 (DORECOVER 포함)
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE
DORECOVER;
}
|
duplicate 이후에는 Standby DB가 mount 상태가 됨
|
1
2
3
4
5
|
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
oracle19stb MOUNTED
|
db role 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
#Primary
SQL> select database_role, open_mode from v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PRIMARY READ WRITE
#Standby
SQL> select database_role, open_mode from v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY MOUNTED
|
Standby DB spfile 생성 및 mount 모드로 재기동
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL> create spfile from pfile;
File created.
SQL> shutdown immediate
SQL> startup mount
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /app/oracle/product/19c/dbs/sp
fileoracle19stb.ora
|
spfile 사용중임
Standby db read only로 기동
|
1
2
3
|
SQL> alter database open read only;
Database altered.
|
Standby에서 MRP 프로세스(Managed Recovery Process)를 기동
|
1
2
3
|
SQL> alter database recover managed standby database disconnect from session;
Database altered.
|
기동됨
참고로 아래 구문은 deprecated 된 예전 명령임
|
1
|
alter database recover managed standby database using current logfile disconnect;
|
프로세스 확인
|
1
2
3
|
$ ps -ef|grep mrp
oracle 28785 1 0 16:21 ? 00:00:00 ora_mrp0_oracle19stb
oracle 28993 7353 0 16:21 pts/0 00:00:00 grep --color=auto mrp
|
mrp 기동됨
db 상태 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
#Primary
SQL>
set lines 200 pages 1000
select db_unique_name, open_mode, database_role, switchover_status, dataguard_broker broker from v$database;
DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS BROKER
------------------------------ -------------------- ---------------- -------------------- --------
oracle19 READ WRITE PRIMARY TO STANDBY DISABLED
#Standby
SQL>
set lines 200 pages 1000
select db_unique_name, open_mode, database_role, switchover_status, dataguard_broker broker from v$database;
DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS BROKER
------------------------------ -------------------- ---------------- -------------------- --------
oracle19stb READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED DISABLED
|
정상적으로 엑티브 데이터 가드(ADG)가 구성됨
아카이브 갭 등 정상여부 확인
|
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
|
#Primary
SQL>
set lines 200 pages 1000
select dest_id, db_unique_name, status, database_mode, recovery_mode, db_unique_name, gap_status
from v$archive_dest_status
where dest_id <=2
order by 1,2;
DEST_ID DB_UNIQUE_NAME STATUS DATABASE_MODE RECOVERY_MODE DB_UNIQUE_NAME GAP_STATUS
---------- ------------------------------ --------- --------------- ---------------------------------- ------------------------------ ------------------------
1 oracle19 VALID OPEN IDLE oracle19
2 oracle19stb VALID OPEN_READ-ONLY MANAGED REAL TIME APPLY WITH QUERY oracle19stb NO GAP
#Standby
SQL>
set lines 200 pages 1000
select dest_id, db_unique_name, status, database_mode, recovery_mode, db_unique_name, gap_status
from v$archive_dest_status
where dest_id <=2
order by 1,2;
DEST_ID DB_UNIQUE_NAME STATUS DATABASE_MODE RECOVERY_MODE DB_UNIQUE_NAME GAP_STATUS
---------- ------------------------------ --------- --------------- ---------------------------------- ------------------------------ ------------------------
1 oracle19stb VALID OPEN_READ-ONLY MANAGED REAL TIME APPLY oracle19stb
2 oracle19 VALID UNKNOWN IDLE oracle19
|
정상임
Primary에서 테이블, 데이터 생성 및 Standby에서 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
#Primary
SQL>
create table test as select * from dba_objects;
select count(*) from test;
COUNT(*)
----------
64234
#Standby
SQL> select count(*) from test;
COUNT(*)
----------
64234
|
잘 보임
Switch Over 테스트
Primary에서 verify 명령 먼저 수행
|
1
2
3
|
SQL> alter database switchover to oracle19stb verify;
Database altered.
|
verify 명령 수행시 문제가 없으면 Database altered.가 표시되고 switchover에 문제가 있다면 에러가 발생함
에러 조치 후 switchover 가능함
Primary에서 switchover 수행
|
1
2
3
|
SQL> alter database switchover to oracle19stb;
Database altered.
|
참고로 아래 구문은 예전에(11g 이전) 사용하던 구문임
|
1
|
alter database commit to switchover to standby;
|
전환 후 db role 확인
|
1
2
3
4
5
6
7
8
|
#Primary(구 Standby)
SQL>
set lines 200 pages 1000
select db_unique_name, open_mode, database_role, switchover_status, dataguard_broker broker from v$database;
DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS BROKER
------------------------------ -------------------- ---------------- -------------------- --------
oracle19stb MOUNTED PRIMARY NOT ALLOWED DISABLED
|
switchover후 구 Standby가 Primary가 되면서 mount 상태가 됨
db open 후 재확인
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL> alter database open;
Database altered.
SQL>
set lines 200 pages 1000
select db_unique_name, open_mode, database_role, switchover_status, dataguard_broker broker from v$database;
DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS BROKER
------------------------------ -------------------- ---------------- -------------------- --------
oracle19stb READ WRITE PRIMARY FAILED DESTINATION DISABLED
|
read write, primary가 됨
Standby DB(구 Primary)는 db가 종료되었기 때문에 다시 기동 후 확인
|
1
2
3
4
5
6
7
8
9
|
#Standby(구 Primary)
SQL> startup
SQL>
set lines 200 pages 1000
select db_unique_name, open_mode, database_role, switchover_status, dataguard_broker broker from v$database;
DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS BROKER
------------------------------ -------------------- ---------------- -------------------- --------
oracle19 READ ONLY PHYSICAL STANDBY RECOVERY NEEDED DISABLED
|
startup 시 자동으로 read only로 기동됨
Standby(구 Primary)에서 MRP 프로세스(Managed Recovery Process)를 기동
|
1
2
3
|
SQL> alter database recover managed standby database disconnect from session;
Database altered.
|
기동됨
프로세스 확인
|
1
2
3
|
$ ps -ef|grep mrp
oracle 46223 1 0 17:22 ? 00:00:00 ora_mrp0_oracle19
oracle 46559 32185 0 17:22 pts/2 00:00:00 grep --color=auto mrp
|
mrp 기동됨
Primary(구 Standby)에서 테이블, 데이터 생성 및 Standby(구 Primary)에서 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
#Primary(구 Standby)
SQL>
create table test2 as select * from dba_objects;
select count(*) from test2;
COUNT(*)
----------
64235
#Standby(구 Primary)
SQL> select count(*) from test2;
COUNT(*)
----------
64235
|
잘 보임
다음 테스트를 위해 Switch Over 재수행해서 원복
|
1
2
3
4
5
6
7
8
|
#Primary에서 switchover 수행
SQL> alter database switchover to oracle19;
#Primary open
SQL> alter database open;
#Standby 기동
SQL> startup
#Standby MRP 기동
SQL> alter database recover managed standby database disconnect from session;
|
failover는 본 게시글에서 테스트하지 않음
공식가이드 참고
참고 : https://docs.oracle.com/en/database/oracle/oracle-database/19/sbydb/managing-oracle-data-guard-role-transitions.html#GUID-1496944D-3089-4A56-A518-5F9FBF82D2C6
참고1. mrp 프로세스 중지 및 시작 명령
|
1
2
3
4
5
6
7
8
9
|
#중지
SQL> alter database recover managed standby database cancel;
Database altered.
#시작
SQL> alter database recover managed standby database disconnect from session;
Database altered.
|
참고2. gap 확인
|
1
2
3
|
SQL> select * from v$archive_gap;
no rows selected
|
참고3. 데이터가드 상태 확인
|
1
2
3
4
5
6
7
8
|
SQL> select name, value from v$dataguard_stats;
NAME VALUE
-------------------------------- ----------------------------------------------------------------
transport lag +00 00:00:00
apply lag +00 00:00:00
apply finish time
estimated startup time 16
|
결론 :
Restart 환경(ASM) 싱글(Filesystem) 환경으로 ADG를 구성해보았음
요약하면 정적 리스너를 양쪽 db서버에 등록한뒤
adg 파라미터를 설정한 뒤 rman duplicate 명령을 이용해 간편하게 db를 복제하고
standby db에서 mrp를 기동하는 절차로 진행하였음
문제가 발생해서 gap(적용할 아카이브가 많이 생기는것을 gap이라 부름)이 너무 많이 발생하게 되는 경우
standby에 redo를 적용하는 시간과 duplicate를 다시 하는 시간을 계산하여
빠른 방식으로 재구성하면됨
참조 :
Oracle Linux 8.4에 Oracle 19c ADG 설치 가이드_Part 1 ( https://positivemh.tistory.com/819 )
Oracle Linux 8.4에 Oracle 19c RAC to RAC ADG 설치 가이드_Part 1 ( https://positivemh.tistory.com/824 )
https://docs.oracle.com/en/database/oracle/oracle-database/19/sbydb/managing-oracle-data-guard-role-transitions.html#GUID-AAD70601-D248-4309-B8DD-F461EE31A5FF
https://docs.oracle.com/en/database/oracle/oracle-database/19/spmss/switchover-to-a-physical-db.html#GUID-AAD70601-D248-4309-B8DD-F461EE31A5FF
'ORACLE > Install' 카테고리의 다른 글
| 오라클 19c RAC 환경 opatchauto 이용 19.31 RU 패치 가이드 (0) | 2026.06.07 |
|---|---|
| 오라클 19c DB RU(Release Update) 패치중 세션 종료 테스트 (0) | 2026.05.26 |
| Oracle Linux 7.6에 Oracle 11gR2 Silent 모드 설치 가이드 (0) | 2026.05.14 |
| 오라클 26ai AutoUpgrade를 이용해 엔진 설치 방법 (0) | 2026.05.12 |
| Oracle 26ai Single DB RU(Release Update) 패치 가이드 (0) | 2026.05.01 |