归档目录空间不足ORA-16038、ORA-19502、ORA-00312、ORA-19504

归档目录空间不足的处理

默认归档目录
/dev/mapper/vg_arch-lv_arch 492M 409M 59M 88% /arch_test
单个redo大小
SQL> select bytes/1024/1024 size_mb from v$log;

SIZE_MB
----------
50
50
50
剩余归档空间只能供redo切一次，第二次就该告警，出现故障。


创建测试表
CREATE TABLE SCOTT.t_arch (
id NUMBER,
data VARCHAR2(1000),
create_date DATE
); 2 3 4 5

Table created.


创建存储过程
CREATE OR REPLACE PROCEDURE SCOTT.arch_test1 IS
v_rows NUMBER := 1000; -- 每次插入1000行
v_data VARCHAR2(1000) := RPAD('X', 1000, 'X'); -- 每行1000字节
BEGIN
FOR i IN 1..v_rows LOOP
INSERT INTO SCOTT.t_arch (id, data, create_d 2 3 4 5 6 ate)
VALUES (i, v_data, SYSDATE);
END LOOP;
COMMIT;
END arch_test;
/ 7 8 9 10 11

Procedure created.

创建JOB，每分钟执行一次
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'ARCH_TEST_JOB',
job_type => 'STORED_PROCEDURE',
job_action => 'SCOTT.arch_test',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=MINUTEL 2 3 4 5 6 7 Y;INTERVAL=1',
enabled => TRUE,
comments => 'Job to generate redo logs gradually'
);
END;
/ 8 9 10 11 12
PL/SQL procedure successfully completed.


查看JOB运行情况
select job_name, state, last_start_date, next_run_date
from user_scheduler_jobs
where job_name = 'ARCH_TEST_JOB';
JOB_NAME STATE LAST_START_DATE NEXT_RUN_DATE
----------------------------------------------------------------------------------------------------
ARCH_TEST_JOB SCHEDULED 26-OCT-25 02.15.47.898228 PM -07:00 26-OCT-25 02.16.47.900000 PM -07:00

查看表数据增长
select count(*) from scott.t_arch;
COUNT(*)
----------
1000




--数据库告警日志
Thread 1 advanced to log sequence 214 (LGWR switch)
Current log# 1 seq# 214 mem# 0: /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_mzjobpso_.log
Sun Oct 26 15:11:48 2025
ARC0: Encountered disk I/O error 19502
ARC0: Closing local archive destination LOG_ARCHIVE_DEST_1: '/arch_test/1_213_1198082022.dbf' (error 19502) (cludb1)
ARC0: I/O error 19502 archiving log 3 to '/arch_test/1_213_1198082022.dbf'
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance cludb1 - Archival Error
ORA-16038: 日志 3 sequence# 213 无法归档
ORA-19502: 文件 "", 块编号 (块大小=) 上出现写入错误
ORA-00312: 联机日志 3 线程 1: '/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_mzjobpxw_.log'
Sun Oct 26 15:11:48 2025
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance cludb1 - Archival Error
ORA-16014: 日志 3 sequence# 213 未归档, 没有可用的目的地
ORA-00312: 联机日志 3 线程 1: '/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_mzjobpxw_.log'


--告警码：ORA-16038、ORA-19502、ORA-00312、ORA-19504(failed to create file "")
--故障表现：数据库挂起，DDL、DML、DQL无法执行。

-----------------------------------------------------------------
处理方法：
清理出归档磁盘空间
此时rman中删除归档的规则为备份一次到磁盘中即可删除。
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;
执行一次归档备份后空间将会自动删除已备份过的归档释放出空间；
RMAN> BACKUP ARCHIVELOG ALL DELETE INPUT;
执行完以后会释放出一部分空间

或者使用手动删除语句，指定删除日期
delete archivelog until time 'sysdate - 3';

与此同时可以新增一个归档路径，相当于给归档目录的备份,不至于影响业务
alter system set log_archive_dest_2='LOCATION=/arch_linshi OPTIONAL' scope=spfile;
v$archive_dest该视图记录了所有归档路径的信息
重点关注binding的值，当归档进程尝试将归档写入LOG_ARCHIVE_DEST_1时，如果BINDING值为默认的optional
(可选的,主路径对应的值是 MANDATORY)，那么当归档操作失败时,将会继续尝试其他路径
col dest_name for a20;
col destination for a20;
SELECT DEST_NAME,destination,binding,target,status FROM V$ARCHIVE_DEST;

DEST_NAME DESTINATION BINDING TARGET STATUS
-------------------- -------------------------------------------------------
LOG_ARCHIVE_DEST_1 /arch_test OPTIONAL PRIMARY ERROR
LOG_ARCHIVE_DEST_2 OPTIONAL PRIMARY INACTIVE
LOG_ARCHIVE_DEST_3 OPTIONAL PRIMARY INACTIVE
LOG_ARCHIVE_DEST_4 OPTIONAL PRIMARY INACTIVE
LOG_ARCHIVE_DEST_5 OPTIONAL PRIMARY INACTIVE
LOG_ARCHIVE_DEST_6 OPTIONAL PRIMARY INACTIVE
LOG_ARCHIVE_DEST_7 OPTIONAL PRIMARY INACTIVE
LOG_ARCHIVE_DEST_8 OPTIONAL PRIMARY INACTIVE

新增归档路径(想切回原路径的话需要将LOG_ARCHIVE_DEST_2设置为''后重启)
alter system set LOG_ARCHIVE_DEST_2='LOCATION=/arch_linshi' scope=spfile;

SQL> SELECT DEST_NAME,destination,binding,target,status FROM V$ARCHIVE_DEST;

DEST_NAME DESTINATION BINDING TARGET STATUS
-------------------- -------------------- ------------------ -------------- ------------------
LOG_ARCHIVE_DEST_1 /arch_test OPTIONAL PRIMARY ERROR
LOG_ARCHIVE_DEST_2 /arch_linshi OPTIONAL PRIMARY VALID

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /arch_linshi
Oldest online log sequence 215
Next log sequence to archive 217
Current log sequence 217