deletearchivelogall无法清除归档日志解决方法

最近在因归档日志暴增，使用delete archivelog all貌似无法清除所有的归档日志,究竟是什么原因呢？本文将为您解答，需要的朋友可以参考下

1、演示环境
代码如下:

SQL> select * from v$version where rownum<2; 
BANNER 
---------------------------------------------------------------- 
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production 
SQL> select inst_id,instance_name from gv$instance; -->两节点RAC 
INST_ID INSTANCE_NAME 
---------- ---------------- 
1 GOBO4A 
2 GOBO4B 
SQL> show parameter db_recovery -->+REV,使用了ASM 存储方式 
NAME TYPE VALUE 
------------------------------------ ----------- ------------- 
db_recovery_file_dest string +REV 
db_recovery_file_dest_size big integer 1G 
SQL> select flashback_on from v$database; -->数据库未开启闪回特性，也就是说尽管指定了闪回区，未启用闪回特性 
-->相应的，归档日志充满整个闪回区时，闪回区空间并不会被重用 
FLASHBACK_ON 
------------------ 
NO 
 
2、查看及清除现有的归档日志文件 
 代码如下:

oracle@bo2dbp:~> export ORACLE_SID=+ASM1 
oracle@bo2dbp:~> asmcmd 
ASMCMD> cd +REV/GOBO4/ARCHIVELOG 
ASMCMD> ls 
2012_10_08/ 
.... 
arch_795194241_1_10.arc 
arch_795194241_1_100.arc 
.... 
oracle@bo2dbp:~> export ORACLE_SID=GOBO4A 
oracle@bo2dbp:~> rman target / 
Recovery Manager: Release 10.2.0.3.0 - Production on Thu Nov 29 16:23:15 2012 
Copyright (c) 1982, 2005, Oracle. All rights reserved. 
connected to target database: GOBO4 (DBID=921286879) 
#下面通过使用rman backup archivelog方式来删除所有的归档日志文件 
RMAN> backup format '/install_source/rman_bak/arch_%d_%U' 
2> archivelog all delete input; 
Starting backup at 29-NOV-12 
current log archived 
using target database control file instead of recovery catalog 
allocated channel: ORA_DISK_1 
channel ORA_DISK_1: sid=1058 instance=GOBO4A devtype=DISK 
channel ORA_DISK_1: starting archive log backupset 
channel ORA_DISK_1: specifying archive log(s) in backup set 
input archive log thread=1 sequence=139 recid=214 stamp=797450261 
input archive log thread=1 sequence=140 recid=215 stamp=797450292 
input archive log thread=1 sequence=141 recid=216 stamp=797450308 
input archive log thread=1 sequence=142 recid=218 stamp=797450347 
input archive log thread=1 sequence=143 recid=219 stamp=797450372 
input archive log thread=1 sequence=144 recid=220 stamp=797450409 
channel ORA_DISK_1: starting piece 1 at 29-NOV-12 
channel ORA_DISK_1: finished piece 1 at 29-NOV-12 
piece handle=/install_source/rman_bak/arch_GOBO4_1dnrhkn4_1_1 tag=TAG20121129T162806 comment=NONE 
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:15 
channel ORA_DISK_1: deleting archive log(s) 
archive log filename=+REV/gobo4/archivelog/arch_795194241_1_139.arc recid=214 stamp=797450261 
archive log filename=+REV/gobo4/archivelog/arch_795194241_1_140.arc recid=215 stamp=797450292 
archive log filename=+REV/gobo4/archivelog/arch_795194241_1_141.arc recid=216 stamp=797450308 
........ 
piece handle=/install_source/rman_bak/arch_GOBO4_1hnrhli2_1_1 tag=TAG20121129T162806 comment=NONE 
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:09 
channel ORA_DISK_1: deleting archive log(s) 
archive log filename=+REV/gobo4/archivelog/arch_795194241_2_141.arc recid=427 stamp=800547491 
archive log filename=+REV/gobo4/archivelog/arch_795194241_2_142.arc recid=429 stamp=800549193 
archive log filename=+REV/gobo4/archivelog/arch_795194241_2_143.arc recid=433 stamp=800578944 
archive log filename=+REV/gobo4/archivelog/arch_795194241_2_144.arc recid=437 stamp=800641679 
Finished backup at 29-NOV-12 
#再次查看依然有很多归档日志文件存在，而且都是10月23日之前的 
ASMCMD> pwd 
+REV/GOBO4/ARCHIVELOG 
ASMCMD> ls 
2012_09_30/ 
2012_10_09/ 
2012_10_10/ 
2012_10_11/ 
2012_10_12/ 
2012_10_13/ 
2012_10_14/ 
2012_10_15/ 
2012_10_16/ 
2012_10_17/ 
2012_10_18/ 
2012_10_22/ 
2012_10_23/ 
arch_795194241_1_100.arc 
arch_795194241_1_101.arc 
arch_795194241_1_102.arc 
............ 
#再次删除日志文件，来个更狠的命令，直接delete所有的archivelog，最近新增的一个archivelog被删除 
RMAN> delete noprompt archivelog all; 
released channel: ORA_DISK_1 
allocated channel: ORA_DISK_1 
channel ORA_DISK_1: sid=1081 instance=GOBO4A devtype=DISK 
List of Archived Log Copies 
Key Thrd Seq S Low Time Name 
------- ---- ------- - --------- ---- 
453 1 294 A 29-NOV-12 +REV/gobo4/archivelog/arch_795194241_1_294.arc 
deleted archive log 
archive log filename=+REV/gobo4/archivelog/arch_795194241_1_294.arc recid=453 stamp=800662185 
Deleted 1 objects 
# 上面

#先来看看gv$archived_log，如果是单实例使用v$archived_log 
#从下面的查询可知，又有两个新的归档日志产生，一个从第一个instance产生，一个从第二个instance产生。 
SQL> select name,status,count(*) from gv$archived_log group by name,status; 
NAME S COUNT(*) 
-------------------------------------------------- - ---------- 
D 444 
+REV/gobo4/archivelog/arch_795194241_1_295.arc A 2 
+REV/gobo4/archivelog/arch_795194241_2_150.arc A 2 
# 从上面的查询可知，当前的两个节点其归档日志只有2个，其余的444个其名字都是NULL值。 
# 看看关于视图v$archived_log中NAME列的解释 
# Archived log file name. If set to NULL, either the log file was cleared before it was archived or an RMAN backup command 
# with the "delete input" option was executed to back up archivelog all (RMAN> backup archivelog all delete input;). 
# 上面的这段话表明当前的这些日志文件要么被手动清除，要么被rman的delete input选项清除。 
# 其次status列的D字段也表明了这些个名字为空的归档日志已经被Deleted.也就是说有444个归档日志已经被删除了。 
# 再次尝试删除归档日志，尾数为295和150的归档日志也被删除 
RMAN> delete noprompt archivelog all; 
released channel: ORA_DISK_1 
allocated channel: ORA_DISK_1 
channel ORA_DISK_1: sid=1081 instance=GOBO4A devtype=DISK 
List of Archived Log Copies 
Key Thrd Seq S Low Time Name 
------- ---- ------- - --------- ---- 
454 1 295 A 29-NOV-12 +REV/gobo4/archivelog/arch_795194241_1_295.arc 
455 2 150 A 29-NOV-12 +REV/gobo4/archivelog/arch_795194241_2_150.arc 
deleted archive log 
archive log filename=+REV/gobo4/archivelog/arch_795194241_1_295.arc recid=454 stamp=800712037 
deleted archive log 
archive log filename=+REV/gobo4/archivelog/arch_795194241_2_150.arc recid=455 stamp=800712038 
Deleted 2 objects 
# 查询gv$archived_log视图，表明所有现有的archivelog都已经被删除 
SQL> select name,status,count(*) from gv$archived_log group by name,status; 
NAME S COUNT(*) 
-------------------------------------------------- - ---------- 
D 448 
# 在asmcmd命令下也无法找到我们刚刚删除的归档日志文件 
ASMCMD> pwd 
+REV/GOBO4/ARCHIVELOG 
ASMCMD> ls -l arch_795194241_1_295.arc 
asmcmd: entry 'arch_795194241_1_295.arc' does not exist in directory '+REV/GOBO4/ARCHIVELOG/' 
ASMCMD> ls -l arch_795194241_2_150.arc 
asmcmd: entry 'arch_795194241_2_150.arc' does not exist in directory '+REV/GOBO4/ARCHIVELOG/' 
# 在A节点上再次切换一次 
SQL> alter system switch logfile; 
System altered. 
SQL> select inst_id,name,count(*) from gv$archived_log group by inst_id,name; 
INST_ID NAME COUNT(*) 
---------- -------------------------------------------------- ---------- 
2 223 
1 +REV/gobo4/archivelog/arch_795194241_1_296.arc 1 
2 +REV/gobo4/archivelog/arch_795194241_1_296.arc 1 
1 223 
--上面的查询可以看到当前的一个归档日志arch_795194241_1_296.arc基于Inst_id为1的有1个，而基于Inst_id为2的也有一个 
--而直接查询v$archived_log时只有1个当前的归档日志，实际上arch_795194241_1_296.arc文件是由第一个instance产生的。 
--数字296之前的1即可以表明为第一个instance产生的。 
SQL> select name from v$archived_log where name='+REV/gobo4/archivelog/arch_795194241_1_296.arc'; 
NAME 
-------------------------------------------------- 
+REV/gobo4/archivelog/arch_795194241_1_296.arc 
# 关于这个地方个人认为这个应该是用于做恢复时用的。 
# RAC数据库在恢复时，无论多个少节点，只有所有的归档日志的集合才能完成地表述数据库的变迁。 
# 此时，无论从哪个节点上看，或者说做无论从哪个节点恢复，都可以看到该归档日志。 
# 而具体是哪个instance产生则由'%t'重做线程编号来判断。 
#下面再来看看控制文件 
SQL> select * from gv$controlfile_record_section where type='ARCHIVED LOG'; 
INST_ID TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID 
---------- ---------------------------- ----------- ------------- ------------ ----------- ---------- ---------- 
1 ARCHIVED LOG 584 224 224 149 148 456 
2 ARCHIVED LOG 584 224 224 149 148 456 
# RECORDS_TOTAL：Number of records allocated for the section 
# 列RECORDS_TOTAL表明为当前TYPE分配的可存储的总数，在两个instance上都为224条 
# 从最近一次切换日志的查询结果可知，被删除的有223条，新增的一条为arch_795194241_1_296.arc，总条数为224条。 
# 如果下次日志切换再增加一条往哪里放呢？那些已经超出缺省保留期的归档日志被覆盖，即被重用。 
# 用户在控制文件中保存ARCHIVED LOG部分的保留时间由谁来决定呢，参数control_file_record_keep_time，缺省为7天 
# 这意味着7天前的归档日志和备份信息可能在控制文件中已经不存在了 
SQL> show parameter control_file_record_keep_time 
NAME TYPE VALUE 
------------------------------------ ----------- ------------------------------ 
control_file_record_keep_time integer 7 
SQL> select count (*) from v$archived_log; 
COUNT(*) 
---------- 
224 
# Author : Robinson 
# Blog : http://blog.csdn.net/robinson_0612 
SQL> alter session set nls_date_format='yyyymmdd hh24:mi:ss'; 
Session altered. 
# 下面的查询正好表明为什么2012_10_23和之前的日志为什么没有被删除 
# 因为20121023 18:04:53之后的归档日志已经被覆盖了，所以使用delete archivelog all时是根本无法清除之前的日志的，无能为力阿。 
# 对于rman下的delete archivelog all方式不会删除控制文件中对应的归档日志信息，但在控制文件中设置delete状态， 
# 即v$archived_log视图的status列为deleted 
SQL> select min (FIRST_TIME), min (COMPLETION_TIME), max (FIRST_TIME), max (COMPLETION_TIME) from 
2 v$archived_log; 
MIN(FIRST_TIME) MIN(COMPLETION_TI MAX(FIRST_TIME) MAX(COMPLETION_TI 
----------------- ----------------- ----------------- ----------------- 
20121023 18:03:12 20121023 18:04:53 20121130 12:00:26 20121130 12:14:51 
SQL> select min (FIRST_TIME), min (COMPLETION_TIME), max (FIRST_TIME), max (COMPLETION_TIME) from 
2 gv$archived_log; 
MIN(FIRST_TIME) MIN(COMPLETION_TI MAX(FIRST_TIME) MAX(COMPLETION_TI 
----------------- ----------------- ----------------- ----------------- 
20121023 18:03:12 20121023 18:04:53 20121130 12:00:26 20121130 12:14:51 
# 既然这般，如何是好啊？ 
# 那就直接在asmcmd命令行下删除吧。一顿狂删 rm -rf 2012_09_30/ 
# 莫急，莫急，一不小心删完了，我晕，ORA-00254/ORA-15173 Archive_log Directory On Asm Being Deleted 在等候阿。 
 
小结 
a、delete archivelog all将会毫无保留的删除所有的归档日志(在控制文件中有相应记录的) 
b、归档日志的信息被记录在控制文件之中，其生存期和可保留的总数也受到控制文件创建初以及参数control_file_record_keep_time限制 
c、对于那些已经在控制文件中被覆盖的归档日志，该方式不起作用，使用backup archivelog all delete input同样不起作用 
d、注意backup archivelog all时delete input与delete all input有些差异，前者删除仅仅被备份过的归档日志，而后者则对于多个归档位置下的所有归档日志全部删除。 
e、视图v$archived_log或gv$archived_log提供了归档日志的相关详细信息 
f、建议备份归档日志后再删除。注，RAC+ASM下切不可使得archivedlog文件夹为空，否则，整个文件夹连同上级空目录会被删除 




声明：本网页内容旨在传播知识，若有侵权等问题请及时与本网联系，我们将在第一时间删除处理。TEL:177 7030 7066 E-MAIL:11247931@qq.com