Ora-15028:asm file '. ' Not dropped; Currently being accessed--reprint

Couple of weeks ago we had a problem with one of our busiest databases.

The FRA was filling quite rapidly and we just could isn't free enough space. What made this problem interesting is the fact that Oracle is telling us more than 40% of the space is marked as Reclai Mable.

The problem:

We have automatic alerts if we are running out of space. When I had a look into the situation this is what I saw:

Sql> select * from V$flash_recovery_area_usage;
File_type percent_space_used percent_space_reclaimable Number_of_files
------------ ------------------ ------------------------- ---------------
Controlfile. 01 0 1
Onlinelog. 89 0 9
ARCHIVELOG 51.29 49.67 1181
Backuppiece 0 0 0
Imagecopy 0 0 0
Flashbacklog 40.43 34.83 1658

My first thought is to remove old files using RMAN, after checking all standby databases were in sync I execute the FO llowing command (We have space enough to store 7 days worth of archivelogs, but I wanted to play safe as we had many Chang Es during the weekend and one change in particular, generated 60+ GB of archivelogs):

Rman
rman> connect target/
rman> delete noprompt archivelog until time ' SYSDATE-3 ';
List of archived Log Copies
Key thrd Seq S low time Name
------------------------------------------
228321 1 71005 a 07.08.2010 14:27:00 +fra/xxx/archivelog/2010_08_07/thread_1_seq_71005.670.726416889
228323 1 71006 A 07.08.2010 14:28:09 +fra/xxx/archivelog/2010_08_07/thread_1_seq_71006.2112.726416957
228330 1 71007 A 07.08.2010 14:29:16 +fra/xxx/archivelog/2010_08_07/thread_1_seq_71007.1210.726417025
.....
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK follows ===============
RMAN-00571: ===========================================================
Rman-03009:failure of Delete command on Ora_disk_1 channel at 08/09/2010 18:19:58
ora-15028:asm file ' +fra/xxx/ar chivelog/2010_08_07/thread_1_seq_71005.670.726416889 ' not dropped; Currently being accessed

Bingo, what should had been a simple operation now had just become more complicated ...

The solution:

Technically we have plenty of space on the FRA, but Oracle is complaining, which a file is being used. This file is an old.

The only-I could fix the problem is by killing the process holding a-lock on the file. This process have to is one of the arc processes.

In this case the solution is quite simple. The steps I followed were (bearing in mind that arc? Processes is being restarted automatically by Oracle):
1.-find the Process ID for arc:
Ps-ef | Grep-i ora_arc*
Oracle 5607 1 1 19:02? 00:00:00 ora_arc9_prod1
2.-Kill the running process:
Kill-9 5607
3.-Check the process is started again before killing more:
Ps-ef | Grep-i ora_arc9_prod1
4.-perform 2 and 3 for all arc? Running for your instance.

Once we have killed all arc? Processes and we are sure they has been re-started by Oracle I did the following:
1.-Perform couple of redo log switches and make sure is being propagated to all standby databases:

sql> alter system switch logfile;

2.-Connect to RMAN and perform a backup of the Archivelog ' being in used ':
Rman
Rman> Connect Target/
rman> backup Archivelog from sequence 71005 until sequence 71005 thread 1;

The output is:

Starting backup at 09.08.2010 18:50:02
using target database control file instead of recovery catalog
Allocated C Hannel:ora_disk_1
Channel ora_disk_1:sid=620 instance=prod1 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=71005 recid=228321 stamp=726416895
Channel ora_disk_1:starting piece 1 at 09.08.2010 18:50:04
Channel Ora_disk_1: Finished piece 1 at 09.08.2010 18:50:11
piece handle=+fra/xxx/backupset/2010_08_09/annnf0_tag20100809t185002_ 0.1097.726605405 tag=tag20100809t185002 Comment=none
Channel ora_disk_1:backup set complete, elapsed time:00:00:08
finished backup at 09.08.2010 18:50:11
starting Control File and SPFILE autobackup at 09.08.2010 18:50:11
Piece Handle=/u04/oradata/prod/rman_disk/auto/prod/c-4255406167-20100809-00_prod comment=none
Finished Control File and SPFILE autobackup at 09.08.2010 18:50:15

Once we have performed the backup, we can delete old archivelogs to release some space:

rman> Delete noprompt archivelog until time ' SYSDATE-3 ';
....
Deleted archive Log
Archive Log filename=+fra/xxx/archivelog/2010_08_07/thread_1_seq_71005.670.726416889 recid=228321 stamp=726416895
Deleted archive Log
Archive Log filename=+fra/xxx/archivelog/2010_08_07/thread_1_seq_71006.2112.726416957 recid=228323 stamp=726416962
....

3.-Monitor The space on the FRA:
Sql> select * from V$flash_recovery_area_usage;
File_type percent_space_used percent_space_reclaimable Number_of_files
-------------------- ------------------ ------------------------- ---------------
Controlfile. 01.00 1
Onlinelog. 89.00 9
ARCHIVELOG 49.09 46.83 1168
Backuppiece. 00.00 0
Imagecopy. 00.00 0
Flashbacklog 40.22 33.91 1648

Couple of times:

Sql> select * from V$flash_recovery_area_usage;
File_type percent_space_used percent_space_reclaimable Number_of_files
-------------------- ------------------ ------------------------- ---------------
Controlfile. 01.00 1
Onlinelog. 89.00 9
ARCHIVELOG 3.10.04 225
Backuppiece. 10.00 1
Imagecopy. 00.00 0
Flashbacklog 39.91 32.95 1633

As always, comments is welcome.

Ora-15028:asm file '. ' Not dropped; Currently being accessed--reprint