새소식

인기 검색어

Oracle Database

[ ORACLE ] ORA-00257 archiver error

  • -
반응형

 

얼마 전에 고객사에 ORA-00257 에러가 발생하였다. 해당 고객사는 Crontab으로 7일 이후의 Archive Log는 삭제하는 스크립트를 걸어놨음에도 250G의  +RECO DIskgroup이 Full이 차는 일이 발생하였다.  

우서 장애처리를 위해서 Archive log를 5일 치 남기고 삭제하여 해결하기는 하였으니 이후 왜 Archive log가 Full이 찾는지 원인을 찾기 시작하였다.

 

■ 장애 발생

- RECO DISKGROUP 의 Free_MB가 236이다.

- RECO DISKGROUP에서 ARCHIVE LOG가 249G를 차지하고 있다.

State    Type    Rebal  Sector  Logical_Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512             512   4096  4194304    262144   87992                 0           87992              0             Y  DATA/
MOUNTED  EXTERN  N         512             512   4096  4194304    262144   236                   0              23              0             N  RECO/


SQL> select type,sum(bytes/1024/1024/1024) GB from v$asm_file where group_number=2 and type='ARCHIVELOG' group by type;

TYPE         GB
---------- ----------
ARCHIVELOG 249.672129

 

■ 장애 처리

- 5일 치만 남기고 Archive log 삭제

[oracle@tt ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Tue Dec 14 13:38:13 2021
Version 19.12.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DB1202 (DBID=2164395504)

RMAN>  DELETE noprompt ARCHIVELOG ALL COMPLETED BEFORE 'sysdate -5';
(생략)

RMAN> CROSSCHECK ARCHIVELOG ALL;
(생략)

RMAN > DELETE EXPIRED ARCHIVELOG ALL;

 

■ 장애 원인 분석

- ArchiveLog가 몇 개씩 쌓였다 확인

- 12/09, 12/12, 12/13을 보면 평상시보다 Archive Log가 과도하게 많이 떨어지는 것을 볼 수 있다.

select to_date(first_time,'YYYY/MM/DD') ,count(*)  from v$ARCHIVED_LOG  where first_time > to_date('2021/12/01','yyyy/mm/dd') group by to_date(first_time,'YYYY/MM/DD') order by 1 desc;

##12월
TO_DATE(FIRST_TIME   COUNT(*)
------------------ ----------
21-DEC-13           79
21-DEC-12           78
21-DEC-11           48
21-DEC-10           42
21-DEC-09           105
21-DEC-08           47
21-DEC-07           39
21-DEC-06           39
21-DEC-05           25
21-DEC-04           27
21-DEC-03           25
21-DEC-02           49
21-DEC-01           28

 

■ Logminer 사용

-고객사에서 해당 날짜에 작업이 없다 하였고 DML이 발생헀던것을 확인할 수 있냐는 요청이 와서 Logminer를 이용해서 Insert, Update, Delete Count를 조회해 보았다.

## supplemental_logging 확인 -> NO여야됨
select supplemental_log_data_min from v$database;

## 13일중에 LOG가 많이 쌓였던 시간대 조회 -> 09시 
alter session set nls_date_format='yyyy/mm/dd hh24';

select to_date(first_time,'YYYY/MM/DD HH24') as "TIME",count(*) from v$ARCHIVED_LOG  where first_time > to_date('2021/12/13','yyyy/mm/dd') group by to_date(first_time,'YYYY/MM/DD HH24') order by 1 desc; 
TIME		COUNT(*)
------------- ----------
2021/12/13 19	       4
2021/12/13 18	       3
2021/12/13 17	       2
2021/12/13 16	       4
2021/12/13 15	       8
2021/12/13 14	       1
2021/12/13 13	       1
2021/12/13 12	       1
2021/12/13 11	       1
2021/12/13 10	      13
2021/12/13 09	      18
2021/12/13 08	       5
2021/12/13 07	       1
2021/12/13 06	       1
2021/12/13 05	       3
2021/12/13 04	       1
2021/12/13 03	      11
2021/12/13 02	       2
2021/12/13 01	       2
2021/12/13 00	       2

##13일 09시의 Archive log name 조회
SQL> select name, to_date(first_time,'YYYY/MM/DD HH24') as "TIME" from v$ARCHIVED_LOG  where first_time > to_date('2021/12/13 09','yyyy/mm/dd hh24') order by 2 desc;

NAME						   TIME
-------------------------------------------------- -------------
(생략)
+RECO/archive/1_5434_1035234538.dbf		   2021/12/13 09
+RECO/archive/1_5433_1035234538.dbf		   2021/12/13 09
+RECO/archive/1_5432_1035234538.dbf		   2021/12/13 09
+RECO/archive/1_5431_1035234538.dbf		   2021/12/13 09
+RECO/archive/1_5430_1035234538.dbf		   2021/12/13 09
+RECO/archive/1_5429_1035234538.dbf		   2021/12/13 09
+RECO/archive/1_5428_1035234538.dbf		   2021/12/13 09
+RECO/archive/1_5427_1035234538.dbf		   2021/12/13 09
+RECO/archive/1_5426_1035234538.dbf		   2021/12/13 09
+RECO/archive/1_5425_1035234538.dbf		   2021/12/13 09
+RECO/archive/1_5424_1035234538.dbf		   2021/12/13 09
+RECO/archive/1_5423_1035234538.dbf		   2021/12/13 09
+RECO/archive/1_5422_1035234538.dbf		   2021/12/13 09
+RECO/archive/1_5421_1035234538.dbf		   2021/12/13 09
+RECO/archive/1_5420_1035234538.dbf		   2021/12/13 09
+RECO/archive/1_5419_1035234538.dbf		   2021/12/13 09
+RECO/archive/1_5417_1035234538.dbf		   2021/12/13 09
+RECO/archive/1_5418_1035234538.dbf		   2021/12/13 09


##Log Miner 시작
SQL> @?/rdbms/admin/dbmslm.sql

Session altered.

Package created.

Grant succeeded.

Synonym created.

Session altered.

##Logminer에 ArchiveLog 파일 추가
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+RECO/archive/1_5434_1035234538.dbf', OPTIONS => DBMS_LOGMNR.new);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+RECO/archive/1_5433_1035234538.dbf', OPTIONS => DBMS_LOGMNR.ADDFILE);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+RECO/archive/1_5432_1035234538.dbf', OPTIONS => DBMS_LOGMNR.ADDFILE);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+RECO/archive/1_5431_1035234538.dbf', OPTIONS => DBMS_LOGMNR.ADDFILE);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+RECO/archive/1_5430_1035234538.dbf', OPTIONS => DBMS_LOGMNR.ADDFILE);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+RECO/archive/1_5429_1035234538.dbf', OPTIONS => DBMS_LOGMNR.ADDFILE);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+RECO/archive/1_5428_1035234538.dbf', OPTIONS => DBMS_LOGMNR.ADDFILE);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+RECO/archive/1_5427_1035234538.dbf', OPTIONS => DBMS_LOGMNR.ADDFILE);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+RECO/archive/1_5426_1035234538.dbf', OPTIONS => DBMS_LOGMNR.ADDFILE);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+RECO/archive/1_5425_1035234538.dbf', OPTIONS => DBMS_LOGMNR.ADDFILE);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+RECO/archive/1_5424_1035234538.dbf', OPTIONS => DBMS_LOGMNR.ADDFILE);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+RECO/archive/1_5423_1035234538.dbf', OPTIONS => DBMS_LOGMNR.ADDFILE);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+RECO/archive/1_5422_1035234538.dbf', OPTIONS => DBMS_LOGMNR.ADDFILE);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+RECO/archive/1_5421_1035234538.dbf', OPTIONS => DBMS_LOGMNR.ADDFILE);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+RECO/archive/1_5420_1035234538.dbf', OPTIONS => DBMS_LOGMNR.ADDFILE);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+RECO/archive/1_5419_1035234538.dbf', OPTIONS => DBMS_LOGMNR.ADDFILE);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+RECO/archive/1_5418_1035234538.dbf', OPTIONS => DBMS_LOGMNR.ADDFILE);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+RECO/archive/1_5417_1035234538.dbf', OPTIONS => DBMS_LOGMNR.ADDFILE);


##Logminer Start
begin
      dbms_logmnr.start_logmnr
      (options => dbms_logmnr.dict_from_online_catalog);
end;
/


##DML Count 출력  --> 4시간 걸림...
SQL> select operation,count(*) from  v$logmnr_contents  where operation in ('INSERT','UPDATE','DELETE') group by operation;
OPERATION                        COUNT(*)
-------------------------------- ----------
INSERT                           404797
DELETE                           20883374
UPDATE                           16670


##Logminer Stop
EXECUTE DBMS_LOGMNR.END_LOGMNR();

 

■ DML 횟수 비교

- 평상시 시간당 DML 수와 12/13일 09시의 일어난 DML 수를 비교하였다.

- 12/13일 09시에 과도한 DELETE가 발생하였다. 

 

반응형
저작자표시

'Oracle Database' 카테고리의 다른 글

[ Oracle ] Shrink Space 경험  (0) 2021.11.24
[ Oracle ] 필수 Background Process  (1) 2021.09.11
[ORACLE] Full Table Scan 이해하기  (0) 2021.09.10
[ Oracle ] Select절 Function 튜닝  (0) 2021.09.09
[ORACLE] KILL SESSION  (0) 2021.08.27
Contents

당신이 좋아할만한 콘텐츠

댓글 0

포스팅 주소를 복사했습니다

이 글이 도움이 되었다면 공감 부탁드립니다.

SHARE

DARK

MENU

티스토리툴바