How to delete archive log files using PL/SQL

Although RMAN is always the first choice for most DBAs to delete archive log files, PL/SQL could be easier for this scenario,

User wants to switch an existing database from NOARCHIVELOG mode to ARCHIVELOG mode in order to increase the database availability because the database backup job has to shut down database when running in NOARCHIVELOG mode.

However, the system generates too much archive log data, which do not need to be backed up, in ARCHIVELOG mode. Therefore, it needs

1. A light archive-log-deleting job which can be easily scheduled as database job or OS crontab job.
2. The job will not delete archive log files while other RMAN backup job is running, because it could delete files which are being backed up by RMAN job and fail the RMAN job.
  
PL/SQL is pretty easy to do that,

declare
  f_count number ;
begin
  select count(*) into f_count from v$rman_status where STATUS like 'RUNNING%';
  if f_count>0 then
     dbms_output.put_line('RMAN backup job is running, archive log files cannot be deleted');
     return;
  end if;
  f_count:=0;
  for c1 in (
     select RECID,STAMP,NAME,THREAD#,SEQUENCE#,RESETLOGS_CHANGE#,FIRST_CHANGE#,BLOCK_SIZE
       from v$archived_log
      where STANDBY_DEST='NO' and deleted='NO'
  )
  loop
     begin
       dbms_backup_restore.deleteArchivedLog(c1.RECID,c1.STAMP,c1.NAME,c1.THREAD#,c1.SEQUENCE#,c1.RESETLOGS_CHANGE#,c1.FIRST_CHANGE#,c1.BLOCK_SIZE);
       dbms_output.put_line(c1.name||' is deleted.');
       f_count:=f_count+1;
     exception
       when others then
          null;
     end;
  end loop;
  if f_count=0 then
     dbms_output.put_line('No archive log files deleted.');
  end if;
end;
/

The PL/SQL block can be enclosed in database scheduler job definition or Linux/Unix shell script running as crontab job. Here is a simple bash shell example,

#!/bin/bash
# Redirect all STDOUT & STDERR messages to log file
exec &>>/u01/log/backup.log   
# Set Oracle environment
export ORAENV_ASK=NO
export ORACLE_SID=<Database SID>
. oraenv 1>/dev/null
# Run PL/SQL block in SQLPLUS
sqlplus -S /nolog <<EOF | grep -v 'PL/SQL procedure successfully completed.'
connect / as sysdba
set serveroutput on
declare
  f_count number ;
begin
  /* If there is RMAN job running, f_count=0 -> no job running*/
  select count(*) into f_count from v\$rman_status where STATUS like 'RUNNING%';
  if f_count>0 then
     dbms_output.put_line('RMAN job is running, archivelog log files cannot be deleted while RMAN job is running');
     return;
  end if;
  f_count:=0;
  for c1 in (
     select RECID,STAMP,NAME,THREAD#,SEQUENCE#,RESETLOGS_CHANGE#,FIRST_CHANGE#,BLOCK_SIZE
       from v\$archived_log
      where STANDBY_DEST='NO' and deleted='NO'
  )
  loop
     begin
       dbms_backup_restore.deleteArchivedLog(c1.RECID,c1.STAMP,c1.NAME,c1.THREAD#,c1.SEQUENCE#,c1.RESETLOGS_CHANGE#,c1.FIRST_CHANGE#,c1.BLOCK_SIZE);
       dbms_output.put_line(c1.name||' is deleted.');
       f_count:=f_count+1;
     exception
       when others then
          null;
     end;
  end loop;
  if f_count=0 then
     dbms_output.put_line('No archivelog files deleted.');
  end if;
end;
/
EOF