ORA-16038: log 3 sequence# 231 cannot be archived

OS환경 : Oracle Linux6.8(64bit)

DB 환경 : Oracle Database 11.2.0.4

에러 : ORA-16038: log 3 sequence# 231 cannot be archived

아카이브로그 삭제 시 에러 메세지

아카이브 로그 위치 확인

SQL> archive log list
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           /oracle/app/11.2.0/db/dbs/arch
Oldest online log sequence     154
Next log sequence to archive   156
Current log sequence           156

샘플 테이블 및 데이터 생성

SQL> DROP TABLE ARCHTEST PURGE;
SQL> CREATE TABLE ARCHTEST(COLA VARCHAR2(20), COLB NUMBER, COLC NUMBER, 
COLD VARCHAR2(30), COLE VARCHAR2(30), COLF VARCHAR2(30), 
COLG NUMBER, COLH VARCHAR2(30), COLI VARCHAR2(30));
CREATE INDEX IDX_COLB ON HWMTEST1(COLB); 

샘플 데이터 삽입(500만)

SQL> DECLARE
TYPE tbl_ins IS TABLE OF ARCHTEST%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..1000000 LOOP 
   w_ins(i).COLA :=i;
   w_ins(i).COLB :=300000;
   w_ins(i).COLC :=99;
   w_ins(i).COLD :='ABC'||dbms_random.string('x',10);
   w_ins(i).COLE :='EEEEEEEEEEEEEEEE';
   w_ins(i).COLF :='FFFFFFFFFFFFFFFF';
   w_ins(i).COLG :=9999999;
   w_ins(i).COLH :='HHHHHHHHHHHHHHHHHHHHHHHHHH';
   w_ins(i).COLI :='IIIIIIIIIIIIIIIIIIIIIIIIII';
END LOOP;
   FORALL i in 1..1000000 INSERT INTO ARCHTEST VALUES w_ins(i);
   COMMIT;
   FORALL i in 1..1000000 INSERT INTO ARCHTEST VALUES w_ins(i);
   COMMIT;
   FORALL i in 1..1000000 INSERT INTO ARCHTEST VALUES w_ins(i);
   COMMIT;
   FORALL i in 1..1000000 INSERT INTO ARCHTEST VALUES w_ins(i);
   COMMIT;
   FORALL i in 1..1000000 INSERT INTO ARCHTEST VALUES w_ins(i);
   COMMIT;
END;
/
 
PL/SQL procedure successfully completed.

대량 DELETE 작업 진행

SQL> 
BEGIN
FOR I IN 1 .. 8 LOOP
DELETE ARCHTEST WHERE ROWNUM<=500000;
COMMIT;
END LOOP;
END;
/

세션2

아카이브 로그 위치로 이동 후 현재 쌓이고 있는 아카이브 로그 모두 삭제

$ cd /oracle/app/11.2.0/db/dbs/arch
$ ls -al
total 407580
drwxr-xr-x  2 oracle dba     12288 Dec 26 15:59 .
drwxr-xr-x 74 oracle dba      4096 Nov 27 10:13 ..
-rw-r-----  1 oracle dba 208665088 Dec 26 15:59 arch2_156_991691191.dbf
-rw-r-----  1 oracle dba 208665088 Dec 26 15:59 arch2_157_991691191.dbf
-rw-rw----  1 oracle dba      1544 Dec 26 11:28 hc_ORCLDB2.dat
-rw-r--r--  1 oracle dba      2851 Nov  8 19:19 init.ora
-rw-r--r--  1 oracle dba        47 Nov  8 21:58 initORCLDB2.ora
$ rm arch*

alert log 확인

Wed Dec 26 11:02:38 2018
ARC1: Error 19510 Closing archive log file '/oracle/app/11.2.0/db/dbs/arch/arch1_231_991691191.dbf'
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance SJEDB1 - Archival Error
ORA-16038: log 3 sequence# 231 cannot be archived
ORA-19510: failed to set size of  blocks for file "" (block size=)
ORA-00312: online log 3 thread 1: '/dev/raw/raw14'
Wed Dec 26 11:02:41 2018
Archived Log entry 317 added for thread 1 sequence 231 ID 0x2712c937 dest 1:
Archiver process freed from errors. No longer stopped
Wed Dec 26 11:02:49 2018
Thread 1 advanced to log sequence 233 (LGWR switch)
  Current log# 2 seq# 233 mem# 0: /dev/raw/raw13
Wed Dec 26 11:02:53 2018
Archived Log entry 318 added for thread 1 sequence 232 ID 0x2712c937 dest 1:
Wed Dec 26 11:03:03 2018
Thread 1 advanced to log sequence 234 (LGWR switch)
  Current log# 3 seq# 234 mem# 0: /dev/raw/raw14

에러 발생

해결 방법 : log switch나 아카이브 로그가 발생중일 때 아카이브로그를 지우지 않아야함

원인 : 아카이브로그가 쌓이던 도중 아카이브 파일이 삭제되어 발생한 문제

참조 :