ORA-00257: archiver error. Connect internal only, until freed
数据库出现以上报错,为归档日志满了,需要删除归档日志:
1.查看日志占用空间
…>sqlplus / as sysdba
select * from V$FLASH_RECOVERY_AREA_USAGE;
其他信息查询语句:
select * from v$recovery_file_dest; --查看归档日志的存放地址和实际大小;
select count(*) from v$archived_log where archived='YES' and deleted='NO'; --文件数
2.删除日志
通过rman删除,打开cmd命令窗口执行以下语句:
…>rman target sys/pass
检查一些无用的archivelog
RMAN> crosscheck archivelog all;
删除截止到前7天的所有archivelog
RMAN> delete archivelog until time 'sysdate-7' ;
RMAN> 是否确定要删除以上对象 (输入 YES 或 NO)? yes
删除后再查询占用空间:
3.修改归档日志大小
…>sqlplus / as sysdba
(1)查看归档日志大小:
SQL>show parameter recovery
(2)修改归档日志大小:
SQL>alter system set db_recovery_file_dest_size=32G scope=spfile;
(3)重启数据库,查看大小
SQL> shutdown immediate
SQL> startup
SQL> show parameter recovery
如果要修改目录,执行以下语句:
SQL> alter system set db_recovery_file_dest='/u01/app/oracle/archivelog' scope=spfile;
4.设置定时任务删除归档日志
(1)创建del_archivelog.txt
connect target /
run{
crosscheck archivelog all;
DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-5';
delete expired archivelog all;
}
(2)创建del_archivelog.bat
@echo off
set "filename=del_archivelog_task_log_%date:~0,4%%date:~5,2%%date:~8,2%.txt"
(
echo.
echo ========================= 正在清除过期归档文件,请稍等...... %date% %time% =========================
echo.
rman cmdfile=F:\app\删除归档日志脚本\del_archivelog.txt
echo.
echo ========================= 【结束清理 %date% %time%】 =========================
echo.
)>>F:\app\删除归档日志脚本\%filename% 2>&1<nul
pause
(3)创建定时任务
参考资料: