当ORACLE归档日志满后怎么正确删除归档日志
当ORACLE归档日志满后如何正确删除归档日志
放假过来，发现测试库的归档日志满了！！！以前是dba搞呀，这次人家不在啊，只有自己上了！！
windows 平台：
将del_arc_orcl.bat 添加到计划任务即可.
今天一早，同事说Oracle数据库连接不上。用Sqlplus测试一下，报如下错：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.
通常，在正式的也就是生产环境下，Oracle是启用归档模式的（SQL>alter database archivelog;）。因此，Oracle系统会不断的产生归档日志。可以先查看一下已有的归档日志：
1.telnet 到主机：telnet 10.10.2.41
AIX Version 5
(C) Copyrights by IBM and by others 1982, 2005.
login: root
root's Password:
*******************************************************************************
* *
* *
* Welcome to AIX Version 5.2! *
* *
* *
* Please see the README file in /usr/lpp/bos for information pertinent to *
* this release of the AIX Operating System. *
* *
* *
*******************************************************************************
Last unsuccessful login: Thu Jul 30 00:06:26 BEIST 2009 on /dev/pts/0 from smnpcbackup
Last login: Sat Aug 8 10:37:11 BEIST 2009 on /dev/pts/0 from 10.10.10.211
2.切换用户到oracle
root@p630_1#:/> su - oracle
3.进入rman
p630_1#oracle> rman
Recovery Manager: Release 9.2.0.8.0 - 64bit Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
4.连接到目标数据库
RMAN> connect target /
connected to target database: RAC9I (DBID=654968050)
5.查看所有日志情况
RMAN> list archivelog all;
using target database controlfile instead of recovery catalog
List of Archived Log Copies
Key Thrd Seq S Low Time Name
------- ---- ------- - --------- ----
960 1 522 A 01-AUG-09 /archlog1/Arch1_522.arc
962 1 523 A 02-AUG-09 /archlog1/Arch1_523.arc
964 1 524 A 04-AUG-09 /archlog1/Arch1_524.arc
972 1 525 A 05-AUG-09 /archlog1/Arch1_525.arc
967 1 526 A 06-AUG-09 /archlog1/Arch1_526.arc
973 1 527 A 06-AUG-09 /archlog1/Arch1_527.arc
974 1 528 A 07-AUG-09 /archlog1/Arch1_528.arc
简单地，我们可以到OS下把这些物理文件delete掉。但是，我们都知道在controlfile中记录着每一个archivelog的相关信息，当我们在OS中删除这些文件后，我们的controlfile中仍然记录着这些archivelog的信息，因此在Oracle的OEM管理器中还会存在这些日志。因为当我们手工清除 archive目录下的文件后，这些记录并没有被我们从controlfile中清除掉，也就是oracle并不知道这些文件已经不存在了。如果我们已经这样做了，那么可以通过如下步骤来更新Oracle的controlfile中关于日志的信息：
1.进入rman
p630_1#oracle> rman
2.连接到目标数据库
RMAN> connect target /;
3.检测归档日志
RMAN> crosscheck archivelog all; --命令的作用是将磁盘或者磁带上不存在的日志标记为expired
4.删除过期的归档日志
RMAN> delete expired archivelog all;
经过这样操作以后，就不会再在OEM中看到那些日志信息了。但是，正常的操作应该是：
RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';--即删除7天前的归档日志
最好在删除日志的时候，做好相应的日志或者数据库备份。此外，可以使用report obsolete命令报告过期备份。
RMAN> report obsolete;--报告过期备份
RMAN> allocate channel for maintenance type disk;--分配维护通道
allocated channel: ORA_MAINT_DISK_1channel
ORA_MAINT_DISK_1: sid=16 devtype=DISK
RMAN> delete obsolete;--删除过期备份
RMAN> release channel;--释放通道
或者整体一起执行命令：
ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK;
run
{
report obsolete;
CROSSCHECK BACKUP;
CROSSCHECK COPY;
DELETE EXPIRED BACKUP;
DELETE EXPIRED COPY;
delete obsolete;
}
release channel;
当然，也可以为系统增加备份空间：
alter database add logfile 'absolute_path/newredofilename.log' size 20M;
alter system switch logfile;
alter database drop logfile absolute_path/oldredofilename.log';
原文地址http://blog.sina.com.cn/s/blog_3f2ef1180100epqi.html
放假过来，发现测试库的归档日志满了！！！以前是dba搞呀，这次人家不在啊，只有自己上了！！
windows 平台：
del_arc_orcl.bat forfiles /p d:/arc_orcl /m *.DBF /d -3 /c "cmd /c del @file"
将del_arc_orcl.bat 添加到计划任务即可.
今天一早，同事说Oracle数据库连接不上。用Sqlplus测试一下，报如下错：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.
通常，在正式的也就是生产环境下，Oracle是启用归档模式的（SQL>alter database archivelog;）。因此，Oracle系统会不断的产生归档日志。可以先查看一下已有的归档日志：
1.telnet 到主机：telnet 10.10.2.41
AIX Version 5
(C) Copyrights by IBM and by others 1982, 2005.
login: root
root's Password:
*******************************************************************************
* *
* *
* Welcome to AIX Version 5.2! *
* *
* *
* Please see the README file in /usr/lpp/bos for information pertinent to *
* this release of the AIX Operating System. *
* *
* *
*******************************************************************************
Last unsuccessful login: Thu Jul 30 00:06:26 BEIST 2009 on /dev/pts/0 from smnpcbackup
Last login: Sat Aug 8 10:37:11 BEIST 2009 on /dev/pts/0 from 10.10.10.211
2.切换用户到oracle
root@p630_1#:/> su - oracle
3.进入rman
p630_1#oracle> rman
Recovery Manager: Release 9.2.0.8.0 - 64bit Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
4.连接到目标数据库
RMAN> connect target /
connected to target database: RAC9I (DBID=654968050)
5.查看所有日志情况
RMAN> list archivelog all;
using target database controlfile instead of recovery catalog
List of Archived Log Copies
Key Thrd Seq S Low Time Name
------- ---- ------- - --------- ----
960 1 522 A 01-AUG-09 /archlog1/Arch1_522.arc
962 1 523 A 02-AUG-09 /archlog1/Arch1_523.arc
964 1 524 A 04-AUG-09 /archlog1/Arch1_524.arc
972 1 525 A 05-AUG-09 /archlog1/Arch1_525.arc
967 1 526 A 06-AUG-09 /archlog1/Arch1_526.arc
973 1 527 A 06-AUG-09 /archlog1/Arch1_527.arc
974 1 528 A 07-AUG-09 /archlog1/Arch1_528.arc
简单地，我们可以到OS下把这些物理文件delete掉。但是，我们都知道在controlfile中记录着每一个archivelog的相关信息，当我们在OS中删除这些文件后，我们的controlfile中仍然记录着这些archivelog的信息，因此在Oracle的OEM管理器中还会存在这些日志。因为当我们手工清除 archive目录下的文件后，这些记录并没有被我们从controlfile中清除掉，也就是oracle并不知道这些文件已经不存在了。如果我们已经这样做了，那么可以通过如下步骤来更新Oracle的controlfile中关于日志的信息：
1.进入rman
p630_1#oracle> rman
2.连接到目标数据库
RMAN> connect target /;
3.检测归档日志
RMAN> crosscheck archivelog all; --命令的作用是将磁盘或者磁带上不存在的日志标记为expired
4.删除过期的归档日志
RMAN> delete expired archivelog all;
经过这样操作以后，就不会再在OEM中看到那些日志信息了。但是，正常的操作应该是：
RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';--即删除7天前的归档日志
最好在删除日志的时候，做好相应的日志或者数据库备份。此外，可以使用report obsolete命令报告过期备份。
RMAN> report obsolete;--报告过期备份
RMAN> allocate channel for maintenance type disk;--分配维护通道
allocated channel: ORA_MAINT_DISK_1channel
ORA_MAINT_DISK_1: sid=16 devtype=DISK
RMAN> delete obsolete;--删除过期备份
RMAN> release channel;--释放通道
或者整体一起执行命令：
ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK;
run
{
report obsolete;
CROSSCHECK BACKUP;
CROSSCHECK COPY;
DELETE EXPIRED BACKUP;
DELETE EXPIRED COPY;
delete obsolete;
}
release channel;
当然，也可以为系统增加备份空间：
alter database add logfile 'absolute_path/newredofilename.log' size 20M;
alter system switch logfile;
alter database drop logfile absolute_path/oldredofilename.log';
原文地址http://blog.sina.com.cn/s/blog_3f2ef1180100epqi.html