본문 바로가기
DBMS/ORACLE

ARCHIVE(아카이브) HANG 조치, ORA-16020

by 드바 2022. 3. 28.

DBMS: oracle 11.2.0.1


운영 중 갑작스런 archive 영역 사용 증가로 redo log archive에 실패하여 DB가 HANG 상황에 빠지는 경우가 있다.
아카이브 영역을 정리 후 'archive log all'을 시도 했지만 ORA-16020 에러 발생하며 진행이 안될 때 해결방법
HANG 발생 후 조치하였지만 reopen 값에의해(default 300초) 바로 풀리지 않고 기다리고 있는 경우에도 사용하여 바로 진행 할 수 있다(관련 값 V$ARCHIVE_DEST 에서 확인 가능).

 

요약:
1. 아카이브 경로 FULL로 HANG 발생
2. 해당 경로 정리 후 ARCHIVE LOG ALL 실행(ORA-16020 발생하며 실패)
3. alter system set log_archive_dest_state_1=enable
4. ARCHIVE LOG ALL 성공


아래 형태의 메시지 발생하며(아카이브 경로 FULL 상황) archive hang 발생

ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance oradb - Archival Error
ORA-16014: log 2 sequence# 350 not archived, no available destinations
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/oradb/redo02.log'
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/oradb/redo02b.log'

 

ORA-16020 발생

-- 수동 아카이브 실행 실패
SQL> ALTER SYSTEM ARCHIVE LOG ALL;
ORA-16020: fewer destinations available than specified by LOG_ARCHIVE_MIN_SUCCEED_DEST

 

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=/data/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			       oradb_%t_%s_%r.arc
log_archive_local_first 	     boolean			       TRUE
log_archive_max_processes	     integer			       4
log_archive_min_succeed_dest	     integer			       1
log_archive_start		     boolean			       FALSE
log_archive_trace		     integer			       0
standby_archive_dest		     string			       ?/dbs/arch
반응형

해결방법

-- 해당 디렉토리 정리 후
-- log_archive_dest_state_1=enable (아카이브 경로) 실행
SQL> alter system set log_archive_dest_state_1=enable ;

System altered.

-- STATUS 확인
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.

-- 아카이브 실행
SQL>  ARCHIVE LOG ALL;
2 logs archived.
SQL>

'DBMS > ORACLE' 카테고리의 다른 글

시퀀스(sequence) last_number 의미와 alter sequence 시 변화  (0) 2022.05.11
ASM DISK 사이즈 다른 경우 DATAFILE 추가 테스트  (0) 2022.05.10
redo log 사이즈 변경 RAC ASM  (0) 2022.03.25
srvctl stop/start database 명령은 어떤 노드부터 동작할까?  (0) 2022.03.25
db_files 파라미터 변경 RAC  (0) 2022.03.23

관련글

댓글

티스토리툴바