Suddenly our support guys found some archivelog backups were not deleted even they were not needed by the backup policy ( recover window of 7 days).
At the beginning I thought maybe it was a similar issue with the one I found already that the end time of the full backups were different so the backups were not removed just after the full backup while they would be removed on the next day. I asked them to do such check and forgot to follow up it until our customer talked this issue with me directly.
I checked and found it was a different one as the database backups were deleted as plan while the archivelog backups were kept there so the RMAN thought it should not remove such backups. It was really weird as I could not find any related error from the backup logs and also when I run 'report obsolete' I just got nothing.
From the oracle support website I found one possible reason was that some data files maybe in recover status and the database believed these archivelog backups were needed for recover so could not delete them. I run 'select file#,status,name from v$datafile' and did find one file 24 was marked as 'RECOVER', so I tried to recover it:
SQL> alter database recover datafile 24; alter database recover datafile 24 * ERROR at line 1: ORA-00279: change 5675308317 generated at 10/09/2019 11:37:34 needed for thread 2 ORA-00289: suggestion : +BACKUPDATA ORA-00280: change 5675308317 for thread 2 is in sequence #265269
So the reason was clear that one file was in RECOVER status and some very old archive logs were needed to bring it online, while such archive logs were deleted already!
It was lucky that there was a standby database and I found the corresponding datafile on it was in ONLINE status, so I did below thing to recover the file on primary database:
1. On standby database RMAN> backup datafile 24 format '/tmp/file24.bak'; 2. Scp the backup to the primary database in the same folder 3. On primary database RMAN> catalog backuppiece '/tmp/file24.bak'; RMAN> list backup of datafile 24; Make sure the new backup was recognized 4. On primary database RMAN> sql 'alter database datafile 24 offline'; RMAN> restore datafile 24; RMAN> recover datafile 24; RMAN> sql 'alter database datafile 24 online'; 5. Done
The I made a full backup of the datafile 24 then run 'crosscheck backup' and 'report obsolete' but still got nothing. Why?
Next day I realized making a full backup was not enough to obsolete the old archivelog backups as there was no old enough full database backup to meet the backup policy ( recover window of 7 days) so these old backups will be removed after about 7 days.
