归档日志文件异常

 在生产环境下，oracle数据库如果处于archivelog mode（归档模式），就会不断地产生归档日志，因此有可能会产生ORA-00257错误。原因是档日志文件太多造成磁盘或者设备空间不足。
ORA-00257 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 the 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.

我们可以直接在OS下手工删除归档日志文件，但是由于在controlfile中记录着每一个archivelog的相关信息，当我们在OS下把这些物理文件delete掉或异常变动后，在controlfile中仍然记录着这些archivelog的信息，当我们手工清除archive目录下的文件后，这些记录并没有被我们从controlfile中清除掉，也就是oracle并不知道这些文件已经不存在了!这时候我们要做手工的清除。
        解决方法如下：
1.登录到oracle用户
 [root@localhost 桌面]# su - oracle

2. 进入rman，连接到目标数据库
[oracle@localhost orcl]$ rman
 Recovery Manager: Release 10.2.0.1.0 - Production on ????? 12?? 3 14:15:55 2013
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

RMAN> connect target/
connected to target database: ORCL (DBID=1348206264)

3.列出所有归档日志文件RMAN> list archivelog all;
using target database control file instead of recovery catalog
List of Archived Log Copies
Key     Thrd Seq     S Low Time   Name
------- ---- ------- - ---------- ----
1       1    6       A 16-8?? -13 /opt/oracle10g/db_recovery_file_dest/ORCL/archivelog/2013_09_04/o1_mf_1_6_92fsvkdv_.arc
2       1    7       A 04-9?? -13 /opt/oracle10g/db_recovery_file_dest/ORCL/archivelog/2013_09_04/o1_mf_1_7_92fwtgry_.arc
3       1    8       A 04-9?? -13 /opt/oracle10g/db_recovery_file_dest/ORCL/archivelog/2013_09_04/o1_mf_1_8_92fwv4ss_.arc
4       1    9       A 04-9?? -13 /opt/oracle10g/db_recovery_file_dest/ORCL/archivelog/2013_09_04/o1_mf_1_9_92fwvt94_.arc
5       1    10      A 04-9?? -13 /opt/oracle10g/db_recovery_file_dest/ORCL/archivelog/2013_09_04/o1_mf_1_10_92fww9t3_.arc
6       1    11      A 04-9?? -13 /opt/oracle10g/db_recovery_file_dest/ORCL/archivelog/2013_09_04/o1_mf_1_11_92fwwpph_.arc
7       1    12      A 04-9?? -13 /opt/oracle10g/db_recovery_file_dest/ORCL/archivelog/2013_09_04/o1_mf_1_12_92fwx45b_.arc
8       1    13      A 04-9?? -13 /opt/oracle10g/db_recovery_file_dest/ORCL/archivelog/2013_09_04/o1_mf_1_13_92fwxgkx_.arc
9       1    14      A 04-9?? -13 /opt/oracle10g/db_recovery_file_dest/ORCL/archivelog/2013_09_04/o1_mf_1_14_92fwxrp5_.arc
10      1    15      A 04-9?? -13 /opt/oracle10g/db_recovery_file_dest/ORCL/archivelog/2013_09_04/o1_mf_1_15_92fwy30p_.arc
11      1    16      A 04-9?? -13 /opt/oracle10g/db_recovery_file_dest/ORCL/archivelog/2013_09_04/o1_mf_1_16_92fwydf5_.arc
12      1    17      A 04-9?? -13 /opt/oracle10g/db_recovery_file_dest/ORCL/archivelog/2013_09_04/o1_mf_1_17_92fwypl6_.arc
13      1    18      A 04-9?? -13 /opt/oracle10g/db_recovery_file_dest/ORCL/archivelog/2013_09_04/o1_mf_1_18_92fwz12r_.arc
14      1    19      A 04-9?? -13 /opt/oracle10g/db_recovery_file_dest/ORCL/archivelog/2013_11_14/o1_mf_1_19_988sp988_.arc
RMAN> exit
Recovery Manager complete.

4.进入归档日志目录,删除归档日志文件
[oracle@localhost orcl]$ cd /opt/oracle10g/db_recovery_file_dest/ORCL/archivelog
[oracle@localhost archivelog]$ ls
2013_09_04  2013_11_14
[oracle@localhost archivelog]$ cd 2013_11_14/
[oracle@localhost 2013_11_14]$ ls
o1_mf_1_19_988sp988_.arc
[oracle@localhost 2013_11_14]$ rm o1_mf_1_19_988sp988_.arc

5.检查控制文件和实际物理文件的差别
[oracle@localhost 2013_11_14]$ rman
Recovery Manager: Release 10.2.0.1.0 - Production on ????? 12?? 3 14:18:02 2013
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
RMAN> connect target/

connected to target database: ORCL (DBID=1348206264)

RMAN> crosscheck archivelog all;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 devtype=DISK
validation succeeded for archived log
archive log filename=/opt/oracle10g/db_recovery_file_dest/ORCL/archivelog/2013_09_04/o1_mf_1_6_92fsvkdv_.arc recid=1 stamp=825263731
validation succeeded for archived log
archive log filename=/opt/oracle10g/db_recovery_file_dest/ORCL/archivelog/2013_09_04/o1_mf_1_7_92fwtgry_.arc recid=2 stamp=825266773
validation succeeded for archived log
archive log filename=/opt/oracle10g/db_recovery_file_dest/ORCL/archivelog/2013_09_04/o1_mf_1_8_92fwv4ss_.arc recid=3 stamp=825266796
validation succeeded for archived log
archive log filename=/opt/oracle10g/db_recovery_file_dest/ORCL/archivelog/2013_09_04/o1_mf_1_9_92fwvt94_.arc recid=4 stamp=825266816
validation succeeded for archived log
archive log filename=/opt/oracle10g/db_recovery_file_dest/ORCL/archivelog/2013_09_04/o1_mf_1_10_92fww9t3_.arc recid=5 stamp=825266827
validation succeeded for archived log
archive log filename=/opt/oracle10g/db_recovery_file_dest/ORCL/archivelog/2013_09_04/o1_mf_1_11_92fwwpph_.arc recid=6 stamp=825266841
validation succeeded for archived log
archive log filename=/opt/oracle10g/db_recovery_file_dest/ORCL/archivelog/2013_09_04/o1_mf_1_12_92fwx45b_.arc recid=7 stamp=825266854
validation succeeded for archived log
archive log filename=/opt/oracle10g/db_recovery_file_dest/ORCL/archivelog/2013_09_04/o1_mf_1_13_92fwxgkx_.arc recid=8 stamp=825266864
validation succeeded for archived log
archive log filename=/opt/oracle10g/db_recovery_file_dest/ORCL/archivelog/2013_09_04/o1_mf_1_14_92fwxrp5_.arc recid=9 stamp=825266875
validation succeeded for archived log
archive log filename=/opt/oracle10g/db_recovery_file_dest/ORCL/archivelog/2013_09_04/o1_mf_1_15_92fwy30p_.arc recid=10 stamp=825266884
validation succeeded for archived log
archive log filename=/opt/oracle10g/db_recovery_file_dest/ORCL/archivelog/2013_09_04/o1_mf_1_16_92fwydf5_.arc recid=11 stamp=825266894
validation succeeded for archived log
archive log filename=/opt/oracle10g/db_recovery_file_dest/ORCL/archivelog/2013_09_04/o1_mf_1_17_92fwypl6_.arc recid=12 stamp=825266905
validation succeeded for archived log
archive log filename=/opt/oracle10g/db_recovery_file_dest/ORCL/archivelog/2013_09_04/o1_mf_1_18_92fwz12r_.arc recid=13 stamp=825266916
validation failed for archived log
archive log filename=/opt/oracle10g/db_recovery_file_dest/ORCL/archivelog/2013_11_14/o1_mf_1_19_988sp988_.arc recid=14 stamp=831477586
Crosschecked 14 objects

从上面标红的字中可以看到我们上一步手工删除的归档日志文件被标识为异常了。

6.同步控制文件的信息和实际物理文件的信息
RMAN> delete expired archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 devtype=DISK

List of Archived Log Copies
Key     Thrd Seq     S Low Time   Name
------- ---- ------- - ---------- ----
14      1    19      X 04-9?? -13 /opt/oracle10g/db_recovery_file_dest/ORCL/archivelog/2013_11_14/o1_mf_1_19_988sp988_.arc

Do you really want to delete the above objects (enter YES or NO)?

Do you really want to delete the above objects (enter YES or NO)? yes
deleted archive log
archive log filename=/opt/oracle10g/db_recovery_file_dest/ORCL/archivelog/2013_11_14/o1_mf_1_19_988sp988_.arc recid=14 stamp=831477586
Deleted 1 EXPIRED objects

 
经过这样操作以后，就不会再在OEM中看到那些日志信息了。但是，更好的操作应该是：

         RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';--即删除7天前的归档日志

最好在删除日志的时候，做好相应的日志或者数据库备份。此外，可以使用report obsolete命令报告过期备份。

• ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK;
  run
  {
  report obsolete;
  CROSSCHECK BACKUP;
  CROSSCHECK COPY;
  DELETE EXPIRED BACKUP;
  DELETE EXPIRED COPY;
  delete obsolete;
  }
  release channel;
  

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29257832/viewspace-1062033/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29257832/viewspace-1062033/