今天遇到一個奇怪的事情，使用OBSOLETE不刪除歸檔日誌，而且也不刪除過期的歸檔的BACKUP SET
從delete obsolete的概念來看如下：

The REPORT OBSOLETE and DELETE OBSOLETE commands work in two steps:
   For each datafile for which there are full backup, datafile copy, or level 0 incremental backups, RMAN identifies the oldest full or
level 0 backup or copy that is not obsolete under the retention policy being tested. Any full backup, level 0 incremental backup, or
datafile copy of a datafile older than the one identified in this step is considered obsolete.                                                                                                                                                                                                                                                                                                                                                                          Rman Not Deleting Obsolete Archive Logs And Archive Log Backups (文件 ID 282617.1)
   Any archived logs and level 1 incremental backups that are older than the oldest non-obsolete full backup are then obsolete because
there is no full or level 0 backup to which they can be applied.
可以看到所謂的冗餘策略不管是天數還是副本數量，保證的都是你冗餘時間或者副本最早的那一個FULL BACKUP DATABASE（FULL BACK 或者LEVEL 0增量備份）
是能夠恢復的，而早於這個FULL BACKUP以前的增量備份和歸檔日誌或者其他備份都是失效的。簡單的說delete obsolete會刪除已經沒有用的不用於恢復的
歸檔日誌。但是今天遇到的問題是明顯的已經處於無用狀態的歸檔任然保留，delete obsolete不能刪除。

檢視MOS發現問題如下：

once the datafile is identified, the file must be brought up to date with the other files. To do this, execute the following steps:

# If file is Offline:

1. Recover datafile XX:

alter database recover datafile XX

-- recovery will apply archives.

2. Set the Datafile Online

alter database datafile XX online;

or

# If tablespace is in begin backup mode:

1. sql> alter tablespace end backup;

Once file is brought upto date, RMAN will not have the need to retain older archivelog files.

簡單的說就是如果有資料檔案處於OFFLINE 狀態或者HOT BACKUP 狀態，一旦重新ONLINE這個資料檔案或者END BACKUP這個檔案
是需要歸檔來進行恢復，所以是不能刪除的，所以馬上檢視資料檔案狀態如下：

查詢過程和處理過程
SQL> select * from dba_data_files;

FILE_NAME                                                ONLINE_STATUS
-------------------------------------------------------- -------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\BENDI\USERS01.DBF       ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\BENDI\SYSAUX01.DBF      ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\BENDI\UNDOTBS01.DBF     ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\BENDI\SYSTEM01.DBF      SYSTEM
C:\ORACLE\PRODUCT\10.2.0\ORADATA\BENDI\TEST01.DBF        ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\BENDI\UNTDOTBS01.DBF    ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\BENDI\TESTCK.DBF        RECOVER

7 rows selected

SQL> drop tablespace testck;

drop tablespace testck

ORA-01549: tablespace not empty, use INCLUDING CONTENTS option

SQL> drop tablespace testck including contents and datafiles;

Tablespace dropped

SQL> select * from dba_data_files;

FILE_NAME                                                ONLINE_STATUS
-------------------------------------------------------- -------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\BENDI\USERS01.DBF       ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\BENDI\SYSAUX01.DBF      ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\BENDI\UNDOTBS01.DBF     ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\BENDI\SYSTEM01.DBF      SYSTEM
C:\ORACLE\PRODUCT\10.2.0\ORADATA\BENDI\TEST01.DBF        ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\BENDI\UNTDOTBS01.DBF    ONLINE

6 rows selected

我刪除了表空間後，REPORT OBSOLETE可以正常刪除過期的歸檔和歸檔備份集。

當然也有MOS文章說還有一種可能的情況就是BUG。
Bug 12412131 - Archivelog backups are not being deleted by 'DELETE OBSOLETE' (文件 ID 12412131.8)

DELETE OBSOLETE不刪除歸檔日誌以及歸檔的備份集

相關文章