복사되었습니다!
OS 환경 : Oracle Linux 9.6 (64bit)
DB 환경 : Oracle Database 19.28.0.0
방법 : 오라클 19c ADG Gap 발생시 rman 증분백업이용 복구 시나리오
현재 오라클 19c Restart(ASM) to 싱글(FS) ADG가 구축되어 있는 상태임
참고 : 오라클 19c Restart(ASM) to 싱글(FS) ADG 구성 가이드 ( https://positivemh.tistory.com/1391 )
현재 상태에서 상태에서 네트워크 장애를 발생시키고 강제로 아카이브를 유실시켜 gap을 만들고
이 장애를 해결하기 위해 rman 증분백업을 이용해 복구하는 시나리오를 테스트해봄
테스트
테스트 전 레벨 0 백업 생성
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
$ rman target /
RMAN> backup incremental level 0 database format '/app/oracle/rman/level0_%T_%U';
backup incremental level 0 database format '/app/oracle/rman/level0_%T_%U';
Starting backup at 2026-06-07 21:28:50
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=274 device type=DISK
channel ORA_DISK_1: starting incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/ORACLE19/DATAFILE/system.261.1220456359
input datafile file number=00002 name=+DATA/ORACLE19/DATAFILE/sysaux.262.1220456361
input datafile file number=00003 name=+DATA/ORACLE19/DATAFILE/undotbs1.263.1220456361
input datafile file number=00004 name=+DATA/ORACLE19/DATAFILE/users.265.1220456367
channel ORA_DISK_1: starting piece 1 at 2026-06-07 21:28:52
channel ORA_DISK_1: finished piece 1 at 2026-06-07 21:28:59
piece handle=/app/oracle/rman/level0_20260607_0t4q3gsk_29_1_1 tag=TAG20260607T212852 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 2026-06-07 21:28:59
Starting Control File and SPFILE Autobackup at 2026-06-07 21:28:59
piece handle=/app/oracle/product/19c/dbs/c-3400652514-20260607-03 comment=NONE
Finished Control File and SPFILE Autobackup at 2026-06-07 21:29:00
|
아카이브 갭 등 정상여부 확인
|
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
|
현재 adg는 정상적인 상태임
Standby db의 네트워크를 잠시 내려놓음(장애 발생 상황 가정)
|
1
2
3
4
5
6
7
8
9
10
|
# ifconfig
ens160: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.137.60 netmask 255.255.255.0 broadcast 192.168.137.255
ether 00:50:56:a8:f1:2c txqueuelen 1000 (Ethernet)
RX packets 474857 bytes 8816967143 (8.2 GiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 162866 bytes 37564406 (35.8 MiB)
TX errors 0 dropped 801 overruns 0 carrier 0 collisions 0
# nmcli connection down ens160
|
Primary에서 로그 스위치 수행
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
|
3회 수행함
Primary alert log에 아래와 같이 에러가 남음
|
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
|
$ tail -300f /app/oracle/diag/rdbms/oracle19/oracle19/trace/alert_oracle19.log
2026-06-07T21:29:38.684175+09:00
Thread 1 advanced to log sequence 82 (LGWR switch), current SCN: 1340496
Current log# 1 seq# 82 mem# 0: +DATA/ORACLE19/ONLINELOG/group_1.258.1235315217
2026-06-07T21:29:38.707115+09:00
ARC3 (PID:121247): Archived Log entry 124 added for B-1220456354.T-1.S-81 LOS:0x000000000014744d NXS:0x0000000000147450 NAB:2 ID 0xcb94dc22 LAD:1
..
2026-06-07T21:30:47.322247+09:00
ARC2 (PID:121245): Error 12543 received logging on to the standby
2026-06-07T21:30:47.366516+09:00
Errors in file /app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_arc2_121245.trc:
ORA-12543: TNS:destination host unreachable
ARC2 (PID:121245): FAL: Error 12543 creating remote archived redo log file 'oracle19stb'
ARC2 (PID:121245): FAL archive failed with error 12543 (see trace for details)
ARC2 (PID:121245): FAL archive failed, archiver continuing
2026-06-07T21:30:47.380851+09:00
ORACLE Instance oracle19, archival error, archiver continuing
2026-06-07T21:30:47.380925+09:00
ORA-16055: FAL request rejected
2026-06-07T21:30:47.381109+09:00
Errors in file /app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_arc2_121245.trc:
ORA-16055: FAL request rejected
..
2026-06-07T21:33:50.892878+09:00
TMON (PID:121142): Process (PID:121237) hung on an I/O to LAD:2 after 260 seconds with threshold of 240 at [krsu.c:10195]
TMON (PID:121142): WARN: Terminating process hung on an operation (PID:121237)
2026-06-07T21:33:52.974584+09:00
TMON (PID:121142): Process (PID:121243) hung on an I/O to LAD:2 after 254 seconds with threshold of 240 at [krsu.c:10195]
TMON (PID:121142): WARN: Terminating process hung on an operation (PID:121243)
2026-06-07T21:33:57.087569+09:00
TMON (PID:121142): Killing 1 processes (PIDS:121237) (Process by index) in order to remove hung processes. Requested by OS process 121142
2026-06-07T21:33:57.088623+09:00
Process termination requested for pid 121237 [source = rdbms], [info = 2] [request issued by pid: 121142, uid: 54321]
2026-06-07T21:34:01.113679+09:00
TMON (PID:121142): Killing 1 processes (PIDS:121243) (Process by index) in order to remove hung processes. Requested by OS process 121142
2026-06-07T21:34:01.124937+09:00
Process termination requested for pid 121243 [source = rdbms], [info = 2] [request issued by pid: 121142, uid: 54321]
..
2026-06-07T21:34:03.142751+09:00
TMON (PID:121142): Detected ARCH process failure
TMON (PID:121142): STARTING ARCH PROCESSES
Starting background process ARC1
2026-06-07T21:34:03.162404+09:00
ARC1 started with pid=43, OS id=125682
TMON (PID:121142): ARC1: Archival started
TMON (PID:121142): STARTING ARCH PROCESSES COMPLETE
2026-06-07T21:34:03.180846+09:00
ARC1 (PID:125682): FAIL: Reclaiming FAL entry from dead process (PID:121243)
ARC1 (PID:125682): FAL archive failed with error 12609 (see trace for details)
ARC1 (PID:125682): FAL archive failed, archiver continuing
2026-06-07T21:34:03.236481+09:00
ORACLE Instance oracle19, archival error, archiver continuing
2026-06-07T21:34:03.236596+09:00
ORA-16055: FAL request rejected
2026-06-07T21:34:03.236825+09:00
Errors in file /app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_arc1_125682.trc:
ORA-16055: FAL request rejected
2026-06-07T21:34:21.194499+09:00
TT00 (PID:125789): Gap Manager starting
|
시퀀스 82까지 생성되었지만 네트워크 문제로 전송실패함
그리고 arch 프로세스도 행걸려서 자동으로 종료되었다가 다시 시작됨
Standby로 넘어가지 못한 아카이브 로그 제거 시도
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
RMAN> delete noprompt archivelog all;
delete noprompt archivelog all;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 device type=DISK
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=+DATA/ORACLE19/ARCHIVELOG/2026_06_07/thread_1_seq_79.307.1235338177 thread=1 sequence=79
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=+DATA/ORACLE19/ARCHIVELOG/2026_06_07/thread_1_seq_80.303.1235338179 thread=1 sequence=80
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=+DATA/ORACLE19/ARCHIVELOG/2026_06_07/thread_1_seq_81.308.1235338179 thread=1 sequence=81
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=+DATA/ORACLE19/ARCHIVELOG/2026_06_07/thread_1_seq_82.300.1235338343 thread=1 sequence=82
|
rman에서 제거하려니 실패함(오라클이 실수를 막아줌)
asmcmd에서 확인해도 아카이브로그가 안지워졌음
|
1
2
3
4
5
6
7
8
9
|
$ . oraenv
ORACLE_SID = [oracle19] ? +ASM
The Oracle base remains unchanged with value /app/oracle
ASMCMD [+data/oracle19/archivelog/2026_06_07] > ls
thread_1_seq_79.307.1235338177 <<-- !! 제거 예정
thread_1_seq_80.303.1235338179 <<-- !! 제거 예정
thread_1_seq_81.308.1235338179
thread_1_seq_82.300.1235338343
|
테스트를 위해 시퀀스 66 아카이브 로그 수동으로 삭제
|
1
2
3
4
5
|
ASMCMD [+data/oracle19/archivelog/2026_06_07] > rm thread_1_seq_79.307.1235338177
ASMCMD [+data/oracle19/archivelog/2026_06_07] > rm thread_1_seq_80.303.1235338179
ASMCMD [+data/oracle19/archivelog/2026_06_07] > ls
thread_1_seq_81.308.1235338179
thread_1_seq_82.300.1235338343
|
시퀀스 79,80을 지움
현재 상황
Primary에서 아카이브로그 시퀀스 79부터 82까지가 생성되었고
여기서 시퀀스 79,80을 지워버린 상태
Standby 네트워크를 정상화 시켜도 아카이브로그는 순차적으로 적용해야하기 때문에
시퀀스 79,80이 없어서 시퀀스 81,82를 Standby에 적용할수가 없음
Standby 네트워크 정상화
|
1
2
3
4
5
6
7
8
9
10
|
# ifconfig
ens160: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.137.60 netmask 255.255.255.0 broadcast 192.168.137.255
ether 00:50:56:a8:f1:2c txqueuelen 1000 (Ethernet)
RX packets 474857 bytes 8816967143 (8.2 GiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 162866 bytes 37564406 (35.8 MiB)
TX errors 0 dropped 801 overruns 0 carrier 0 collisions 0
# nmcli connection up ens160
|
Standby mrp 프로세스 재시작
|
1
2
3
4
5
6
7
|
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
|
아카이브 갭 확인
|
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 ERROR OPEN_READ-ONLY MANAGED REAL TIME APPLY WITH QUERY oracle19stb RESOLVABLE 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 db 상태를 보면 처음에 ERROR임 잠시 뒤 VALID로 변경됨
하지만 GAP_STATUS는 UNRESOLVABLE GAP임
Standby에서 아카이브 갭 시퀀스 확인
|
1
2
3
4
5
|
SQL> select * from v$archive_gap;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE# CON_ID
---------- ------------- -------------- ----------
1 79 80 1
|
시퀀스 79(low),80(high)이 필요한 상태임
Standby에서 마지막으로 적용한 아카이브로그 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
SQL>
select thread#, sequence#, first_change#, next_change#, applied
from v$archived_log
where applied = 'YES'
order by sequence# desc;
THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# APPLIED
---------- ---------- ------------- ------------ ---------
1 78 1339005 1340309 YES
1 77 1337012 1339005 YES
1 76 1336774 1337012 YES
1 75 1336765 1336774 YES
1 74 1336762 1336765 YES
1 73 1336480 1336762 YES
1 72 1336475 1336480 YES
1 71 1336465 1336475 YES
1 70 1336439 1336465 YES
1 69 1334609 1336439 YES
10 rows selected.
|
78번까지 적용되어 있음
Primary alert log에 아래와 같이 에러가 남음
|
1
2
3
4
5
6
|
$ tail -300f /app/oracle/diag/rdbms/oracle19/oracle19/trace/alert_oracle19.log
2026-06-07T21:40:23.169886+09:00
Errors in file /app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_tt00_125789.trc:
ORA-00308: cannot open archived log '+DATA/ORACLE19/ARCHIVELOG/2026_06_07/thread_1_seq_79.307.1235338177'
ORA-17503: ksfdopn:2 Failed to open file +DATA/ORACLE19/ARCHIVELOG/2026_06_07/thread_1_seq_79.307.1235338177
ORA-15012: ASM file '+DATA/ORACLE19/ARCHIVELOG/2026_06_07/thread_1_seq_79.307.1235338177' does not exist
|
해결
아카이브로그 백업이 있다면 해당 아카이브를 적용하면 아래 명령으로 적용하면되지만
|
1
|
SQL> alter database register logfile '/tmp/arch_stb/아카이브로그파일명.arc';
|
현재는 아카이브로그 백업이 없기 때문에 rman 증분백업으로 복구를 수행해봄
이전 게시글에선 rman duplicate로 새로 adg를 구축했었음
참고 : 오라클 19c ADG Gap 발생시 rman duplicate 재수행 시나리오 ( https://positivemh.tistory.com/1392 )
Standby 서버에서 현재 SCN 확인
|
1
2
3
4
5
|
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1340462
|
Primary 서버에서 해당 SCN 이후의 변경분 백업 생성
|
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
|
$ rman target /
RMAN> backup incremental from scn 1340462 database format '/app/oracle/rman/stb_inc_%u' tag 'for_stb_gap';
backup incremental from scn 1340462 database format '/app/oracle/rman/stb_inc_%u' tag 'for_stb_gap';
Starting backup at 2026-06-07 21:41:38
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=280 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/ORACLE19/DATAFILE/system.261.1220456359
input datafile file number=00002 name=+DATA/ORACLE19/DATAFILE/sysaux.262.1220456361
input datafile file number=00003 name=+DATA/ORACLE19/DATAFILE/undotbs1.263.1220456361
input datafile file number=00004 name=+DATA/ORACLE19/DATAFILE/users.265.1220456367
channel ORA_DISK_1: starting piece 1 at 2026-06-07 21:41:39
channel ORA_DISK_1: finished piece 1 at 2026-06-07 21:41:42
piece handle=/app/oracle/rman/stb_inc_0v4q3hkj tag=FOR_STB_GAP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 2026-06-07 21:41:43
channel ORA_DISK_1: finished piece 1 at 2026-06-07 21:41:44
piece handle=/app/oracle/rman/stb_inc_104q3hkm tag=FOR_STB_GAP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2026-06-07 21:41:44
|
백업이 완료됨
스탠바이용 컨트롤파일도 백업
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
RMAN> backup current controlfile for standby format '/app/oracle/rman/stb_ctl.bck';
backup current controlfile for standby format '/app/oracle/rman/stb_ctl.bck';
Starting backup at 2026-06-07 21:42:04
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
channel ORA_DISK_1: starting piece 1 at 2026-06-07 21:42:05
channel ORA_DISK_1: finished piece 1 at 2026-06-07 21:42:06
piece handle=/app/oracle/rman/stb_ctl.bck tag=TAG20260607T214204 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2026-06-07 21:42:06
Starting Control File and SPFILE Autobackup at 2026-06-07 21:42:06
piece handle=/app/oracle/product/19c/dbs/c-3400652514-20260607-04 comment=NONE
Finished Control File and SPFILE Autobackup at 2026-06-07 21:42:07
|
백업이 완료됨
생성된 증분백업 파일들과 컨트롤파일 백업파일을 Standby 서버로 전송
|
1
2
3
4
5
6
|
$ cd /app/oracle/rman
$ scp stb_* 192.168.137.60:/app/oracle/rman2/
oracle@192.168.137.60's password:
stb_ctl.bck 100% 10MB 250.7MB/s 00:00
stb_inc_0v4q3hkj 100% 896KB 243.5MB/s 00:00
stb_inc_104q3hkm 100% 10MB 288.5MB/s 00:00
|
Standby 서버에서 MRP 중지
|
1
2
3
|
SQL> alter database recover managed standby database cancel;
Database altered.
|
Standby DB 중지 후 nomount 기동
|
1
2
|
SQL> shutdown immediate
SQL> startup nomount
|
Standby 서버에서 컨트롤파일 먼저 복구
|
1
2
3
4
5
6
7
8
9
10
11
|
RMAN> restore standby controlfile from '/app/oracle/rman2/stb_ctl.bck';
restore standby controlfile from '/app/oracle/rman2/stb_ctl.bck';
Starting restore at 2026-06-07 21:17:02
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/app/oracle/oradata/oracle19stb/control01.ctl
Finished restore at 2026-06-07 21:17:04
|
mount로 변경
|
1
|
RMAN> alter database mount;
|
Standby 서버에서 백업파일 카탈로깅(등록)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
RMAN> catalog start with '/app/oracle/rman2';
catalog start with '/app/oracle/rman2';
using target database control file instead of recovery catalog
searching for all files that match the pattern /app/oracle/rman2
List of Files Unknown to the Database
=====================================
File Name: /app/oracle/rman2/stb_ctl.bck
File Name: /app/oracle/rman2/stb_inc_0v4q3hkj
File Name: /app/oracle/rman2/stb_inc_104q3hkm
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /app/oracle/rman2/stb_ctl.bck
File Name: /app/oracle/rman2/stb_inc_0v4q3hkj
File Name: /app/oracle/rman2/stb_inc_104q3hkm
|
Standby 서버에서 증분 백업 적용
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
RMAN> recover database noredo;
recover database noredo;
Starting recover at 2026-06-07 21:18:47
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /app/oracle/oradata/oracle19stb/system.261.1220456359
destination for restore of datafile 00002: /app/oracle/oradata/oracle19stb/sysaux.262.1220456361
destination for restore of datafile 00003: /app/oracle/oradata/oracle19stb/undotbs1.263.1220456361
destination for restore of datafile 00004: /app/oracle/oradata/oracle19stb/users.265.1220456367
channel ORA_DISK_1: reading from backup piece /app/oracle/rman/stb_inc_0v4q3hkj
channel ORA_DISK_1: errors found reading piece handle=/app/oracle/rman/stb_inc_0v4q3hkj
channel ORA_DISK_1: failover to piece handle=/app/oracle/rman2/stb_inc_0v4q3hkj tag=FOR_STB_GAP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished recover at 2026-06-07 21:18:49
|
정상적으로 복구됨
alter log 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
$ tail -300f /app/oracle/diag/rdbms/oracle19stb/oracle19stb/trace/alert_oracle19stb.log
2026-06-07T21:18:48.559761+09:00
Restore from backup piece /app/oracle/rman/stb_inc_0v4q3hkj
Restore from backup piece /app/oracle/rman2/stb_inc_0v4q3hkj
Cannot read block 1 from /app/oracle/rman/stb_inc_0v4q3hkj - restore failover to read from /app/oracle/rman2/stb_inc_0v4q3hkj
ORA-19505: failed to identify file "/app/oracle/rman/stb_inc_0v4q3hkj"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
Incremental restore complete of datafile 4 /app/oracle/oradata/oracle19stb/users.265.1220456367
checkpoint is 1341720
last deallocation scn is 3
Incremental restore complete of datafile 3 /app/oracle/oradata/oracle19stb/undotbs1.263.1220456361
checkpoint is 1341720
last deallocation scn is 1316171
Incremental restore complete of datafile 2 /app/oracle/oradata/oracle19stb/sysaux.262.1220456361
checkpoint is 1341720
last deallocation scn is 868405
Incremental restore complete of datafile 1 /app/oracle/oradata/oracle19stb/system.261.1220456359
checkpoint is 1341720
last deallocation scn is 1340053
|
정상적으로 checkpoint 1341720 까지 복구됨(참고로 증분백업 적용전 scn은 1340462 였음)
db open 상태로 변경
|
1
2
3
|
SQL> alter database open;
Database altered.
|
mrp 시작
|
1
2
3
|
SQL> alter database recover managed standby database disconnect from session;
Database altered.
|
mrp 기동됨
아카이브 갭 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
#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>
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
|
GAP_STATUS가 UNRESOLVABLE GAP에서 NO GAP로 변경됨
Standby에서 아카이브 갭 시퀀스 확인
|
1
2
3
|
SQL> select * from v$archive_gap;
no rows selected
|
no rows로 표시됨
Standby에서 마지막으로 적용한 아카이브로그 확인
|
1
2
3
4
5
6
7
8
9
|
SQL>
select thread#, sequence#, first_change#, next_change#, applied
from v$archived_log
where applied = 'YES'
order by sequence# desc;
THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# APPLIED
---------- ---------- ------------- ------------ ---------
1 86 1343778 1343783 YES
|
정상적으로 86번 시퀀스까지 아카이브를 적용함
87번 시퀀스는 현재 Standby에서 ACTIVE 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$standby_log l, v$logfile f
where f.group# = l.group#
order by 1, 2;
THREAD# GROUP# MEMBER ARC STATUS MB SEQUENCE#
---------- ---------- ------------------------------------------------------------ --- ---------- ---------- ----------
0 5 /app/oracle/oradata/oracle19stb/group_5.285.1235315381 YES UNASSIGNED 200 0
1 4 /app/oracle/oradata/oracle19stb/group_4.284.1235315377 YES ACTIVE 200 87
1 6 /app/oracle/oradata/oracle19stb/group_6.286.1235315381 NO UNASSIGNED 200 0
|
추가
Standby alert을 보니 아래 에러가 발생했음
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
$ tail -300f /app/oracle/diag/rdbms/oracle19stb/oracle19stb/trace/alert_oracle19stb.log
2026-06-07T21:50:19.632484+09:00
rfs (PID:70868): SRL LNO:5 needs clearing because log has not been created
2026-06-07T21:50:19.632605+09:00
Errors in file /app/oracle/diag/rdbms/oracle19stb/oracle19stb/trace/oracle19stb_rfs_70868.trc:
ORA-00367: checksum error in log file header
ORA-00315: log 5 of thread 0, wrong thread # 1 in header
ORA-00312: online log 5 thread 0: '/app/oracle/oradata/oracle19stb/group_5.285.1235315381'
2026-06-07T21:50:19.640985+09:00
ARC1 (PID:67420): Archived Log entry 4 added for B-1220456354.T-1.S-87 LOS:0x0000000000148127 NXS:0x0000000000149199 NAB:10648 ID 0xcb94dc22 LAD:1
2026-06-07T21:50:19.641153+09:00
rfs (PID:70868): Selected LNO:6 for T-1.S-88 dbid 3400652514 branch 1220456354
2026-06-07T21:50:19.697485+09:00
PR00 (PID:71329): Media Recovery Waiting for T-1.S-88 (in transit)
2026-06-07T21:50:19.698260+09:00
Recovery of Online Redo Log: Thread 1 Group 6 Seq 88 Reading mem 0
Mem# 0: /app/oracle/oradata/oracle19stb/group_6.286.1235315381
|
그룹 5번 redo 헤더에 문제가 있는듯함
추후 참고용 trc 파일
|
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
|
$ cat /app/oracle/diag/rdbms/oracle19stb/oracle19stb/trace/oracle19stb_rfs_70868.trc
Trace file /app/oracle/diag/rdbms/oracle19stb/oracle19stb/trace/oracle19stb_rfs_70868.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0
Build label: RDBMS_19.28.0.0.0DBRU_LINUX.X64_250704
ORACLE_HOME: /app/oracle/product/19c
System name: Linux
Node name: ora19stb
Release: 6.12.0-1.23.3.2.el9uek.x86_64
Version: #1 SMP PREEMPT_DYNAMIC Tue May 13 17:24:00 PDT 2025
Machine: x86_64
CLID: P
Instance name: oracle19stb
Instance number: 1
Database name: oracle19
Database unique name: oracle19stb
Database id: 3400652514
Database role: PHYSICAL STANDBY
Redo thread mounted by this instance: 1
Oracle process number: 46
Unix process pid: 70868, image: oracle@ora19stb
*** 2026-06-07T21:25:46.590398+09:00
*** SESSION ID:(26.40508) 2026-06-07T21:25:46.590416+09:00
*** CLIENT ID:() 2026-06-07T21:25:46.590421+09:00
*** SERVICE NAME:(oracle19stb) 2026-06-07T21:25:46.590424+09:00
*** MODULE NAME:(oracle@ora19 (TNS V1-V3)) 2026-06-07T21:25:46.590428+09:00
*** ACTION NAME:() 2026-06-07T21:25:46.590431+09:00
*** CLIENT DRIVER:() 2026-06-07T21:25:46.590446+09:00
*** 2026-06-07 21:25:46.590061 [krsh.c:6437]
krsr_rfs_atc: Identified database type as 'PHYSICAL': Client is ASYNC (PID:121251)
*** 2026-06-07 21:25:46.598325 [krsr.c:17303]
krsr_dump_alert_msg: Header received for LNO:4 T-1.S-85 dbid 3400652514 branch 1220456354
*** 2026-06-07T21:25:47.007061+09:00
*** 2026-06-07 21:25:47.007050 [krsr.c:17315]
krsr_dump_alert_msg: Archival completed for LNO:4 T-1.S-85 dbid 3400652514 branch 1220456354
DDE rules only execution for: ORA 312
----- START Event Driven Actions Dump ----
---- END Event Driven Actions Dump ----
----- START DDE Actions Dump -----
Executing SYNC actions
----- START DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) -----
Successfully dispatched
----- END DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (SUCCESS, 0 csec) -----
Executing ASYNC actions
----- END DDE Actions Dump (total 0 csec) -----
krsk_rlf_need_clearing: SRL LNO:5 needs clearing because log has not been created
<error barrier> at 0x7ffc85c73090 placed krsk.c@11442
ORA-00367: checksum error in log file header
ORA-00315: log 5 of thread 0, wrong thread # 1 in header
ORA-00312: online log 5 thread 0: '/app/oracle/oradata/oracle19stb/group_5.285.1235315381'
*** 2026-06-07 21:25:47.025225 [krsr.c:17303]
krsr_dump_alert_msg: Header received for LNO:6 T-1.S-86 dbid 3400652514 branch 1220456354
*** 2026-06-07T21:25:48.761255+09:00
*** 2026-06-07 21:25:48.761236 [krsr.c:17315]
krsr_dump_alert_msg: Archival completed for LNO:6 T-1.S-86 dbid 3400652514 branch 1220456354
*** 2026-06-07 21:25:48.777459 [krsr.c:17303]
krsr_dump_alert_msg: Header received for LNO:4 T-1.S-87 dbid 3400652514 branch 1220456354
*** 2026-06-07T21:50:19.622578+09:00
*** 2026-06-07 21:50:19.622558 [krsr.c:17315]
krsr_dump_alert_msg: Archival completed for LNO:4 T-1.S-87 dbid 3400652514 branch 1220456354
DDE rules only execution for: ORA 312
----- START Event Driven Actions Dump ----
---- END Event Driven Actions Dump ----
----- START DDE Actions Dump -----
Executing SYNC actions
----- START DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) -----
Successfully dispatched
----- END DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (SUCCESS, 0 csec) -----
Executing ASYNC actions
----- END DDE Actions Dump (total 0 csec) -----
krsk_rlf_need_clearing: SRL LNO:5 needs clearing because log has not been created
<error barrier> at 0x7ffc85c73090 placed krsk.c@11442
ORA-00367: checksum error in log file header
ORA-00315: log 5 of thread 0, wrong thread # 1 in header
ORA-00312: online log 5 thread 0: '/app/oracle/oradata/oracle19stb/group_5.285.1235315381'
*** 2026-06-07 21:50:19.645413 [krsr.c:17303]
krsr_dump_alert_msg: Header received for LNO:6 T-1.S-88 dbid 3400652514 branch 1220456354
|
Standby쪽 redo이기 때문에 지금 clear 해주어도됨
MRP 중지
|
1
2
3
4
|
XGUBUNX
SQL> alter database recover managed standby database cancel;
Database altered.
|
그룹 5번 redo 로그 clear 수행
|
1
2
3
|
SQL> alter database clear logfile group 5;
Database altered.
|
alert log 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
|
$ tail -300f /app/oracle/diag/rdbms/oracle19stb/oracle19stb/trace/alert_oracle19stb.log
2026-06-07T21:53:51.445616+09:00
Errors in file /app/oracle/diag/rdbms/oracle19stb/oracle19stb/trace/oracle19stb_ora_69849.trc:
ORA-00367: checksum error in log file header
ORA-00315: log 5 of thread 0, wrong thread # 1 in header
ORA-00312: online log 5 thread 0: '/app/oracle/oradata/oracle19stb/group_5.285.1235315381'
Completed: alter database clear logfile group 5
2026-06-07T21:54:09.065466+09:00
alter database clear logfile group 5
2026-06-07T21:54:09.065747+09:00
Clearing online log 5 of thread 0 sequence number 0
Completed: alter database clear logfile group 5
|
한번 수행했을때 alert에 에러가 나는것 같아서 한번더 수행해봤음, clear가 잘 된듯함
MRP 시작
|
1
2
3
|
SQL> alter database recover managed standby database disconnect from session;
Database altered.
|
Primary에서 데이터 생성 및 로그 스위치시 정상적으로 Standby alert에 5번 그룹 리두를 사용하는게 확인됨
|
1
2
3
4
5
6
7
8
9
10
|
$ tail -300f /app/oracle/diag/rdbms/oracle19stb/oracle19stb/trace/alert_oracle19stb.log
2026-06-07T21:58:20.234438+09:00
rfs (PID:70868): Selected LNO:5 for T-1.S-93 dbid 3400652514 branch 1220456354
2026-06-07T21:58:20.266740+09:00
PR00 (PID:81814): Media Recovery Waiting for T-1.S-93 (in transit)
2026-06-07T21:58:20.267881+09:00
Recovery of Online Redo Log: Thread 1 Group 5 Seq 93 Reading mem 0 <<--!!
Mem# 0: /app/oracle/oradata/oracle19stb/group_5.285.1235315381 <<--!!
2026-06-07T21:58:20.808471+09:00
ARC0 (PID:67414): Archived Log entry 9 added for B-1220456354.T-1.S-92 LOS:0x000000000014947d NXS:0x00000000001496ec NAB:389116 ID 0xcb94dc22 LAD:1
|
결론 :
adg 환경에서 gap이 너무 많이 생기거나 아카이브로그가 유실된 경우 본문과 같은 방식으로
증분백업을 이용하여 정상화시킬 수 있음
참조 :
오라클 19c Restart(ASM) to 싱글(FS) ADG 구성 가이드 ( https://positivemh.tistory.com/1391 )
오라클 19c ADG Gap 발생시 rman duplicate 재수행 복구 시나리오 ( https://positivemh.tistory.com/1392 )
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 > Admin' 카테고리의 다른 글
| 오라클 19c ADG 구성 중 db_file_name_convert, log_file_name_convert 파라미터 테스트 (0) | 2026.06.12 |
|---|---|
| 오라클 19c ADG Gap 발생시 rman duplicate 재수행 복구 시나리오 (0) | 2026.06.08 |
| 오라클 19c ACFS 경로 변경 테스트 (0) | 2026.06.06 |
| 오라클 19c ACFS 구성 테스트 (0) | 2026.06.06 |
| 오라클 19c datapump 상세 로그 확인 METRICS, LOGTIME 옵션 (0) | 2026.06.01 |