版权声明:转载时请以超链接形式标明文章原始出处和作者信息及本声明
http://blog.csdn.net/wenshuangzhu/article/details/44059809
【问题描述】
性能测试时出现持续的数据库连接失败,报ora-00257错误:
ora-00257:archiver error. Connect internal only, until freed.
【问题定位】
根据下面的错误描述信息,可以知道问题很明显是由于归档错误导致。
> oerr ora 00257
00257, 00000, "archiver error. Connect internal only, until freed."
// *Cause: The archiver process received an error while trying to archive
// a redo log. If the problem is not resolved soon, the database
// will stop executing transactions. The most likely cause of this
// message is the destination device is out of space to store the
// redo log file.
// *Action: Check archiver trace file for a detailed description
// of the problem. Also verify that the
// device specified in the initialization parameter
// ARCHIVE_LOG_DEST is set up properly for archiving.
查询归档日志存放位置:
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +DG_ARCH
Oldest online log sequence 87
Next log sequence to archive 90
Current log sequence 91
可以看到归档日志保存在asm磁盘组+DG_ARCH。
查询asm磁盘组使用情况:
SQL> select name, total_mb, free_mb from v$asm_diskgroup;
NAME TOTAL_MB FREE_MB
------------------------------ ---------- ----------
DG_ARCH 102400 587
DG_DATA 204800 151431
DG_DBFILE 102400 34787
DG_INDEX 102400 81824
可以看到+DG_ARCH几乎已经没有空闲的存储空间了。
定位原因为性能测试数据库开启了数据库归档,但是没有任何策略去定期清理归档空间(例如定期备份并删除无效的归档日志),导致归档日志长期累积耗尽磁盘存储空间,并最终导致后续的归档操作均失败。
【问题解决】
通过rman手动删除归档日志,释放归档空间:
> rman target /
RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7'; ---删除7天前的所有归档日志
RMAN> CROSCHECK ARCHIVELOG ALL;
RMAN> DELETE EXPIRED ARCHIVELOG ALL;