DBMS: oracle 19.3 |
아카이브 모드 환경에서
아카이브 진행 중인 즉 redo log -> archive log 가 시작되었지만 아직 완료되지 않은
아카이브 로그 파일을 삭제했을 경우 발생하는 에러 및 해결방법입니다
[목차여기]
- 아카이브 진행 중인 로그 삭제 시 아카이브 HANG 발생하여 DB 모든 트랜잭션 중지됨
- 백업 솔루션 등에서 아카이브 백업 후 로그 삭제 시 최근 얼마간의 로그는 남기고 삭제하는 것을 권장
참고
ARCHIVE(아카이브) HANG 조치, ORA-16020
아카이브 진행 중인 로그 삭제
아카이브가 진행 중인 로그파일을 OS에서 rm 명령으로 삭제했을 경우(5180번 로그 아카이브 중 삭제)
alert.log에 ora-19508, ora-16038 등 에러 발생하며 아카이브 작업이 실패했고 더 이상 아카이브가 진행되지 않아
사용할 수 있는 online log가 없어진 시점에 DB는 HANG 상태에 빠져 버렸습니다
2024-03-14T22:34:51.361653+09:00
Thread 1 advanced to log sequence 5181 (LGWR switch)
Current log# 4 seq# 5181 mem# 0: /ORA19/app/oracle/oradata/ORA19C/redo04.log
2024-03-14T22:34:52.747491+09:00
Errors in file /ORA19/app/oracle/diag/rdbms/ora19c/ora19c/trace/ora19c_arc1_23219.trc:
ORA-19508: failed to delete file "/home/oracle/arch/1_5180_1077743123.dbf"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 11
2024-03-14T22:34:52.747727+09:00
ARC1 (PID:23219): Error 19508 Closing archive log file '/home/oracle/arch/1_5180_1077743123.dbf'
ARC1 (PID:23219): Stuck archiver: insufficient local LADs
ARC1 (PID:23219): Stuck archiver condition declared
2024-03-14T22:34:53.060251+09:00
Errors in file /ORA19/app/oracle/diag/rdbms/ora19c/ora19c/trace/ora19c_arc1_23219.trc:
ORA-16038: log 6 sequence# 5180 cannot be archived
ORA-19508: failed to delete file ""
ORA-00312: online log 6 thread 1: '/ORA19/app/oracle/oradata/ORA19C/redo06.log'
ARC1 (PID:23219): Archival error occurred on a closed thread, archiver continuing
2024-03-14T22:34:53.060326+09:00
ORACLE Instance ora19c, archival error, archiver continuing
2024-03-14T22:35:18.386160+09:00
Thread 1 advanced to log sequence 5182 (LGWR switch)
Current log# 5 seq# 5182 mem# 0: /ORA19/app/oracle/oradata/ORA19C/redo05.log
2024-03-14T22:35:45.400378+09:00
ORACLE Instance ora19c - Cannot allocate log, archival required
2024-03-14T22:35:45.403878+09:00
Thread 1 cannot allocate new log, sequence 5183
All online logs need archiving
Examine archive trace files for archiving errors
Current log# 5 seq# 5182 mem# 0: /ORA19/app/oracle/oradata/ORA19C/redo05.log
수동 로그 스위치
현재 redo 확인 및 수동으로 로그스위치 시도 시 실패하였습니다(ora-16014)
SQL> select a.group#,
THREAD#,
SEQUENCE#,
a.member, b.bytes/1024/1024 MB, b.archived, b.status
from v$logfile a, v$log b
where a.group# = b.group#
order by 2, 3
;
GROUP# THREAD# SEQUENCE# MEMBER MB ARC STATUS
---------- ---------- ---------- --------------------------------------------------- ---------- --- ----------------
6 1 5180 /ORA19/app/oracle/oradata/ORA19C/redo06.log 1024 NO INACTIVE
4 1 5181 /ORA19/app/oracle/oradata/ORA19C/redo04.log 1024 NO ACTIVE
5 1 5182 /ORA19/app/oracle/oradata/ORA19C/redo05.log 1024 NO CURRENT
SQL>
SQL>
SQL> alter system archive log current;
alter system archive log current
*
ERROR at line 1:
ORA-16014: log 6 sequence# 5180 not archived, no available destinations
ORA-00312: online log 6 thread 1: '/ORA19/app/oracle/oradata/ORA19C/redo06.log'
SQL> select a.group#,
THREAD#,
SEQUENCE#,
a.member, b.bytes/1024/1024 MB, b.archived, b.status
from v$logfile a, v$log b
where a.group# = b.group#
order by 2, 3
;
GROUP# THREAD# SEQUENCE# MEMBER MB ARC STATUS
---------- ---------- ---------- --------------------------------------------------- ---------- --- ----------------
6 1 5180 /ORA19/app/oracle/oradata/ORA19C/redo06.log 1024 NO INACTIVE
4 1 5181 /ORA19/app/oracle/oradata/ORA19C/redo04.log 1024 NO ACTIVE
5 1 5182 /ORA19/app/oracle/oradata/ORA19C/redo05.log 1024 NO CURRENT
반응형
해결방법
v$archive_dest를 조회하여 현재 아카이브 경로의 상태를 확인합니다
STATUS가 ERROR입니다
SQL> select dest_name, status, valid_now from v$archive_dest;
DEST_NAME STATUS VALID_NOW
-------------------------------------------------- --------- ----------------
LOG_ARCHIVE_DEST_1 ERROR INACTIVE
LOG_ARCHIVE_DEST_2 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_3 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_4 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_5 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_6 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_7 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_8 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_9 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_10 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_11 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_12 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_13 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_14 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_15 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_16 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_17 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_18 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_19 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_20 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_21 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_22 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_23 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_24 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_25 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_26 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_27 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_28 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_29 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_30 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_31 INACTIVE UNKNOWN
31 rows selected.
SQL> show parameter archive
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target integer 0
log_archive_config string
log_archive_dest string
log_archive_dest_1 string location=/home/oracle/arch
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
log_archive_dest_18 string
log_archive_dest_19 string
log_archive_dest_2 string
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
log_archive_dest_3 string
log_archive_dest_30 string
log_archive_dest_31 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string ENABLE
log_archive_dest_state_10 string enable
log_archive_dest_state_11 string enable
log_archive_dest_state_12 string enable
log_archive_dest_state_13 string enable
log_archive_dest_state_14 string enable
log_archive_dest_state_15 string enable
log_archive_dest_state_16 string enable
log_archive_dest_state_17 string enable
log_archive_dest_state_18 string enable
log_archive_dest_state_19 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_20 string enable
log_archive_dest_state_21 string enable
log_archive_dest_state_22 string enable
log_archive_dest_state_23 string enable
log_archive_dest_state_24 string enable
log_archive_dest_state_25 string enable
log_archive_dest_state_26 string enable
log_archive_dest_state_27 string enable
log_archive_dest_state_28 string enable
log_archive_dest_state_29 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_30 string enable
log_archive_dest_state_31 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
log_archive_duplex_dest string
log_archive_format string %t_%s_%r.dbf
log_archive_max_processes integer 4
log_archive_min_succeed_dest integer 1
log_archive_start boolean FALSE
log_archive_trace integer 0
해당 경로의 상태를 enable로 변경합니다
alter system set log_archive_dest_state_1=enable;
명령어 실행 후 STATUS가 VALID로 변경되었습니다
SQL> alter system set log_archive_dest_state_1=enable ;
System altered.
SQL> select dest_name, status, valid_now from v$archive_dest;
DEST_NAME STATUS VALID_NOW
------------------------------------------------------------------ --------- ----------------
LOG_ARCHIVE_DEST_1 VALID YES
LOG_ARCHIVE_DEST_2 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_3 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_4 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_5 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_6 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_7 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_8 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_9 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_10 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_11 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_12 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_13 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_14 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_15 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_16 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_17 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_18 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_19 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_20 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_21 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_22 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_23 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_24 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_25 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_26 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_27 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_28 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_29 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_30 INACTIVE UNKNOWN
LOG_ARCHIVE_DEST_31 INACTIVE UNKNOWN
31 rows selected.
멈춰있던 아카이브가 자동으로 진행되면서 문제 해결되었습니다
2024-03-14T22:37:43.835993+09:00
ALTER SYSTEM ARCHIVE LOG
2024-03-14T22:39:30.211328+09:00
NET (PID:26558): Stuck archiver condition cleared
2024-03-14T22:39:30.293468+09:00
ALTER SYSTEM SET log_archive_dest_state_1='ENABLE' SCOPE=BOTH;
2024-03-14T22:39:31.671946+09:00
ARC0 (PID:23210): Archived Log entry 5487 added for T-1.S-5180 ID 0x4368ce8e LAD:1
2024-03-14T22:39:31.747030+09:00
Thread 1 advanced to log sequence 5183 (LGWR switch)
Current log# 6 seq# 5183 mem# 0: /ORA19/app/oracle/oradata/ORA19C/redo06.log
2024-03-14T22:39:36.944884+09:00
ARC2 (PID:23221): Archived Log entry 5488 added for T-1.S-5182 ID 0x4368ce8e LAD:1
2024-03-14T22:39:37.093972+09:00
ARC1 (PID:23219): Archived Log entry 5489 added for T-1.S-5181 ID 0x4368ce8e LAD:1
'DBMS > ORACLE' 카테고리의 다른 글
| [oracle] data pump 작업 시 trigger 주의 (0) | 2024.04.02 |
|---|---|
| [oracle] index unusable DML 테스트 (0) | 2024.03.17 |
| [oracle] 스케줄러(dbms_scheduler) 실행 시간대(time zone) (0) | 2024.02.22 |
| [oracle] 리스너 timezone 설정 (0) | 2024.02.20 |
| [oracle] alert.log 시간대(time zone) 기준은 무엇인가 (0) | 2024.02.18 |
댓글