delete archivelog all無(wú)法清除歸檔日志解決方法

SQL> select * from v$version where rownum2;
BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
SQL> select inst_id,instance_name from gv$instance; -->兩節(jié)點(diǎn)RAC
INST_ID INSTANCE_NAME
---------- ----------------
1 GOBO4A
2 GOBO4B
SQL> show parameter db_recovery -->+REV,使用了ASM 存儲(chǔ)方式
NAME TYPE VALUE
------------------------------------ ----------- -------------
db_recovery_file_dest string +REV
db_recovery_file_dest_size big integer 1G
SQL> select flashback_on from v$database; -->數(shù)據(jù)庫(kù)未開啟閃回特性，也就是說(shuō)盡管指定了閃回區(qū)，未啟用閃回特性
-->相應(yīng)的，歸檔日志充滿整個(gè)閃回區(qū)時(shí)，閃回區(qū)空間并不會(huì)被重用
FLASHBACK_ON
------------------
NO

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)
#下面通過(guò)使用rman backup archivelog方式來(lái)刪除所有的歸檔日志文件
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
............
#再次刪除日志文件，來(lái)個(gè)更狠的命令，直接delete所有的archivelog，最近新增的一個(gè)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
# 上面輸出的結(jié)果只有一個(gè)歸檔日志被刪除，何以故？
# 這個(gè)我們的分析一下delete noprompt archivelog all以及備份歸檔日志時(shí)使用的 delete input
# 回顧一下Oracle控制文件以及Oracle RMAN的的備份恢復(fù)的原理。
# 我們知道，Oracle 控制文件里邊記錄了數(shù)據(jù)庫(kù)的名字，id,創(chuàng)建的時(shí)間戳....一大堆的信息，當(dāng)然也有不可少的歸檔信息以及備份信息。
# 如果不知道控制文件有什么？ 那就參考:Oracle 控制文件，文章尾部有給出鏈接。
# 其次，Oracle RMAN的備份恢復(fù)的所有信息都依賴于兩個(gè)東東，要么是控制文件，要么是恢復(fù)目錄(catalog)。
# 因?yàn)樗械膫浞菖c恢復(fù)信息都會(huì)依據(jù)備份是的方式存儲(chǔ)到這兩個(gè)位置。
# 理所當(dāng)然的是，對(duì)這兩個(gè)東東里的備份集，鏡像副本，歸檔日志，等等所有能備份的對(duì)象的任意操作，首先會(huì)參考這些對(duì)象的記錄的信息。
# 其次是當(dāng)被記錄的對(duì)象發(fā)生變化時(shí)做相應(yīng)的更新。

#先來(lái)看看gv$archived_log，如果是單實(shí)例使用v$archived_log
#從下面的查詢可知，又有兩個(gè)新的歸檔日志產(chǎn)生，一個(gè)從第一個(gè)instance產(chǎn)生，一個(gè)從第二個(gè)instance產(chǎn)生。
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
# 從上面的查詢可知，當(dāng)前的兩個(gè)節(jié)點(diǎn)其歸檔日志只有2個(gè)，其余的444個(gè)其名字都是NULL值。
# 看看關(guān)于視圖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;).
# 上面的這段話表明當(dāng)前的這些日志文件要么被手動(dòng)清除，要么被rman的delete input選項(xiàng)清除。
# 其次status列的D字段也表明了這些個(gè)名字為空的歸檔日志已經(jīng)被Deleted.也就是說(shuō)有444個(gè)歸檔日志已經(jīng)被刪除了。
# 再次嘗試刪除歸檔日志，尾數(shù)為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視圖，表明所有現(xiàn)有的archivelog都已經(jīng)被刪除
SQL> select name,status,count(*) from gv$archived_log group by name,status;
NAME S COUNT(*)
-------------------------------------------------- - ----------
D 448
# 在asmcmd命令下也無(wú)法找到我們剛剛刪除的歸檔日志文件
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節(jié)點(diǎn)上再次切換一次
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
--上面的查詢可以看到當(dāng)前的一個(gè)歸檔日志arch_795194241_1_296.arc基于Inst_id為1的有1個(gè)，而基于Inst_id為2的也有一個(gè)
--而直接查詢v$archived_log時(shí)只有1個(gè)當(dāng)前的歸檔日志，實(shí)際上arch_795194241_1_296.arc文件是由第一個(gè)instance產(chǎn)生的。
--數(shù)字296之前的1即可以表明為第一個(gè)instance產(chǎn)生的。
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
# 關(guān)于這個(gè)地方個(gè)人認(rèn)為這個(gè)應(yīng)該是用于做恢復(fù)時(shí)用的。
# RAC數(shù)據(jù)庫(kù)在恢復(fù)時(shí)，無(wú)論多個(gè)少節(jié)點(diǎn)，只有所有的歸檔日志的集合才能完成地表述數(shù)據(jù)庫(kù)的變遷。
# 此時(shí)，無(wú)論從哪個(gè)節(jié)點(diǎn)上看，或者說(shuō)做無(wú)論從哪個(gè)節(jié)點(diǎn)恢復(fù)，都可以看到該歸檔日志。
# 而具體是哪個(gè)instance產(chǎn)生則由'%t'重做線程編號(hào)來(lái)判斷。
#下面再來(lái)看看控制文件
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表明為當(dāng)前TYPE分配的可存儲(chǔ)的總數(shù)，在兩個(gè)instance上都為224條
# 從最近一次切換日志的查詢結(jié)果可知，被刪除的有223條，新增的一條為arch_795194241_1_296.arc，總條數(shù)為224條。
# 如果下次日志切換再增加一條往哪里放呢？那些已經(jīng)超出缺省保留期的歸檔日志被覆蓋，即被重用。
# 用戶在控制文件中保存ARCHIVED LOG部分的保留時(shí)間由誰(shuí)來(lái)決定呢，參數(shù)control_file_record_keep_time，缺省為7天
# 這意味著7天前的歸檔日志和備份信息可能在控制文件中已經(jīng)不存在了
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和之前的日志為什么沒有被刪除
# 因?yàn)?0121023 18:04:53之后的歸檔日志已經(jīng)被覆蓋了，所以使用delete archivelog all時(shí)是根本無(wú)法清除之前的日志的，無(wú)能為力阿。
# 對(duì)于rman下的delete archivelog all方式不會(huì)刪除控制文件中對(duì)應(yīng)的歸檔日志信息，但在控制文件中設(shè)置delete狀態(tài)，
# 即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 在等候阿。