Oracle归档日志 ORA-00257:archiver error解决办法清理Oracle归档日志

朱雀 2022-05-11 04:50 244阅读 0赞

# ORA-00257:archiver error解决办法 #

出现ORA-00257错误（空间不足错误），通过查找资料，绝大部分说这是由于归档日志太多，占用了全部的硬盘剩余空间导致的，通过简单删除日志或加大存储空间就能够解决。

[![ORA-00257:archiver error解决办法][ORA-00257_archiver error]][ORA-00257_archiver error_ORA-00257_archiver error]

## 工具/原料 ##

*  数据库Oracle 10.2.0.1.0
 *  SecureCRT 任意ssh连接工具都可以

## 方法/步骤 ##

1.  SecureCRT登录服务器，切换用户oracle,连接oracle
    
    \[root@userbeta~\]\# su - oracle
    
    \[oracle@userbeta~\]$ sqlplus /nolog
    
    SQL> connect /as sysdba
    
    [![ORA-00257:archiver error解决办法][ORA-00257_archiver error 1]][ORA-00257_archiver error_ORA-00257_archiver error 1]
2.  检查flash recovery area的使用情况,可以看见archivelog已经很大了，达到99.94
    
    SQL> select \* from V$FLASH\_RECOVERY\_AREA\_USAGE;
    
    [![ORA-00257:archiver error解决办法][ORA-00257_archiver error 2]][ORA-00257_archiver error_ORA-00257_archiver error 2]
3.  计算flash recovery area已经占用的空间
    
    SQL> select sum(percent\_space\_used)\*3/100 from v$flash\_recovery\_area\_usage;
    
    [![ORA-00257:archiver error解决办法][ORA-00257_archiver error 3]][ORA-00257_archiver error_ORA-00257_archiver error 3]
4.  修改FLASH\_RECOVERY\_AREA的空间修改为6GB，修改前确认磁盘有足够空间
    
    SQL> ALTER SYSTEM SET DB\_RECOVERY\_FILE\_DEST\_SIZE=8g;
    
    [![ORA-00257:archiver error解决办法][ORA-00257_archiver error 4]][ORA-00257_archiver error_ORA-00257_archiver error 4]
5.  现在来清理一下archivelog归档日志，生产环境建议备份
    
    查询日志目录位置
    
    show parameter recover;
    
    删除归档日志,USERDB是数据库实例名
    
    cd /u01/app/oracle/flash\_recovery\_area/USERDB/archivelog
    
    **使用root帐户删除该目录下的文件或者备份其它地方**
    
    [![ORA-00257:archiver error解决办法][ORA-00257_archiver error 5]][ORA-00257_archiver error_ORA-00257_archiver error 5]
6.  使用rman 操作,内容太多，只做部分截图
    
    \[oracle@userbeta archivelog\]$ rman
    
    RMAN> connect target sys/sys\_passwd
    
    crosscheck backup; 
    
    delete obsolete; 
    
    delete expired backup; 
    
    crosscheck archivelog all; 
    
    delete expired archivelog all; 
    
    //此步会有提示，输入 YES 回车
    
    host;   //退出rman
    
    [![ORA-00257:archiver error解决办法][ORA-00257_archiver error 6]][ORA-00257_archiver error_ORA-00257_archiver error 6]
7.  确认是否操作成功
    
    \#  sqlplus /nolog
    
    SQL>  connect /as sysdba
    
    SQL> select \* from V$FLASH\_RECOVERY\_AREA\_USAGE;
    
    [![ORA-00257:archiver error解决办法][ORA-00257_archiver error 7]][ORA-00257_archiver error_ORA-00257_archiver error 7]
8.  删除完成，重新连接数据库
    
    [![ORA-00257:archiver error解决办法][ORA-00257_archiver error 8]][ORA-00257_archiver error_ORA-00257_archiver error 8]

[https://jingyan.baidu.com/article/3065b3b6e202ccbecef8a411.html][https_jingyan.baidu.com_article_3065b3b6e202ccbecef8a411.html]

[ORA-00257_archiver error]: https://imgsa.baidu.com/exp/w=500/sign=eca7af56004f78f0800b9af349310a83/e824b899a9014c08794c19a7097b02087bf4f4b8.jpg
[ORA-00257_archiver error_ORA-00257_archiver error]: /images/20220511/d9eefd1d92764da6971fac63ced11bd8.png
[ORA-00257_archiver error 1]: https://imgsa.baidu.com/exp/w=500/sign=5e19822fd11b0ef46ce8985eedc551a1/78310a55b319ebc44d9ebc988126cffc1f1716c9.jpg
[ORA-00257_archiver error_ORA-00257_archiver error 1]: /images/20220511/473cd1007d5245949dc5307552d4813b.png
[ORA-00257_archiver error 2]: https://imgsa.baidu.com/exp/w=500/sign=b210b32e8401a18bf0eb124fae2e0761/4e4a20a4462309f7a9969810710e0cf3d7cad632.jpg
[ORA-00257_archiver error_ORA-00257_archiver error 2]: /images/20220511/f1b4916ab6a1450cb882d6a3a49d8c90.png
[ORA-00257_archiver error 3]: https://imgsa.baidu.com/exp/w=500/sign=90aed53536fae6cd0cb4ab613fb20f9e/3812b31bb051f819819d3748d9b44aed2e73e773.jpg
[ORA-00257_archiver error_ORA-00257_archiver error 3]: /images/20220511/b7a137cd2d1a4c06b6230feaffbdacb3.png
[ORA-00257_archiver error 4]: https://imgsa.baidu.com/exp/w=500/sign=41e7110a5043fbf2c52ca623807fca1e/e4dde71190ef76c62054cf299e16fdfaaf516729.jpg
[ORA-00257_archiver error_ORA-00257_archiver error 4]: /images/20220511/2bd82f2998c7447bb16843587a412d7f.png
[ORA-00257_archiver error 5]: https://imgsa.baidu.com/exp/w=500/sign=0e9cb0c940166d223877159476220945/3b87e950352ac65c4b5f5171f8f2b21193138a5d.jpg
[ORA-00257_archiver error_ORA-00257_archiver error 5]: /images/20220511/2941ce22a7bc4bfa81aa1a10690cf889.png
[ORA-00257_archiver error 6]: https://imgsa.baidu.com/exp/w=500/sign=9ad1329fc8ef76093c0b999f1edda301/9825bc315c6034a8c8fa8e83c813495409237693.jpg
[ORA-00257_archiver error_ORA-00257_archiver error 6]: /images/20220511/5657d8711bf041819b9d5f800f893afb.png
[ORA-00257_archiver error 7]: https://imgsa.baidu.com/exp/w=500/sign=f71b18e7ae6eddc426e7b4fb09dab6a2/eac4b74543a9822636131d258982b9014b90ebe6.jpg
[ORA-00257_archiver error_ORA-00257_archiver error 7]: /images/20220511/225a5f4cb5e84c7b9ec7c86a21e86a45.png
[ORA-00257_archiver error 8]: https://imgsa.baidu.com/exp/w=500/sign=87008133a28b87d65042ab1f37082860/21a4462309f79052ac3e743c0ff3d7ca7bcbd58a.jpg
[ORA-00257_archiver error_ORA-00257_archiver error 8]: /images/20220511/e87d1a64238d4953b83f14db985a6003.png
[https_jingyan.baidu.com_article_3065b3b6e202ccbecef8a411.html]: https://jingyan.baidu.com/article/3065b3b6e202ccbecef8a411.html