Aujourd'hui, je vous propose de voir comment répondre à la problématique suivante :
Comment supprimer à intervalles réguliers les archivelogs d'une base de données sur un système sur lequel le planificateur Unix (cron) n'est pas activé pour le compte oracle ?

De plus, on ne connait pas le mot de passe de ce dernier (accès en "sudo su" uniquement).

Pour cela, une première possibilité que l'on trouve rapidement après une petite recherche sur internet et dans la doc Oracle est d'utiliser le scheduler pour planifier un job de type "external script" qui appellerait donc un script shell .
La définition du job serait comme celle-ci :

dbms_scheduler.create_job(job_name => 'purge_al',
job_type => 'EXTERNAL_SCRIPT',
job_action => 'purge_archivelog.sh',
credential_name => 'oracle_cred',
enabled => true,
auto_drop => false);

Ce script purge_archivelog.sh exécuterait lui-même un script RMAN contenant simplement le bloc suivant par exemple, pour :

run {
  delete archivelog all completed before 'sysdate-3/24';
}

Ce job devant être exécuté avec le compte système oracle, il faut cependant définir un "credential" associé au compte Oracle et pour cela il faudrait connaitre son mot de passe ce qui n'est pas notre cas (sic).

begin
dbms_credential.create_credential(
credential_name => 'oracle_cred',
username => 'oracle',
password => 'oracle password');
end;
/

Il reste donc une alternative dans ce cas et c'est l'objet de cet article : exécuter la suppression des archivelogs via une procédure PL/SQL.

En effet, celle-ci peut être exécutée sans avoir à créer de credentials ni connaitre de mot de passe, du moment que l'on a accès à l'utilisateur SYS (en ayant accès accès au compte Oracle sur le serveur de la base de données).

Au lieu de créer un job du scheduler Oracle de type "External script", on le crée donc de type "PL/SQL BLOCK".

Pour la suppression des archivelogs, le code fait appel à la procédure deleteArchivedLog du package dbms_backup_restore.

Il suffit de sélectionner les archivelogs à supprimer, en particulier en fonction d'un critère de date via le champ first_time et de les passer à la procédure.

La procédure dont voici le code complet ci-dessous est définie pour s'exécuter toutes les 4 heures à heure fixe (0,4,8,12,16,20) et supprime les archivelogs dont la date de 1ère séquence a plus de 3 heures. La procédure est à cataloguer avec le user SYS.

BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'delete_archivelog_job',
job_type => 'PLSQL_BLOCK', 
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=hourly; byhour=0,4,8,12,16,20; byminute=0; bysecond=0;',
enabled => TRUE,
job_action => 'DECLARE f_count integer;
BEGIN 
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'' and FIRST_TIME < sysdate-3/24
)
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);
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.'');
else
dbms_output.put_line(f_count || '' archive log files deleted.'');
end if;
END; '
);
END;
/

C'est tout, ça fonctionne directement !

Et pour terminer, voici quelques procédures pour piloter ce job :

Pour exécuter la procédure manuellement :

set serveroutput on
execute dbms_scheduler.run_job('DELETE_ARCHIVELOG_JOB');

Pour suspendre ou réactiver la planification de la procédure :

exec dbms_scheduler.disable('DELETE_ARCHIVELOG_JOB');
exec dbms_scheduler.enable('DELETE_ARCHIVELOG_JOB'

Pour le suivi d'exécution :

select log_date, job_name, status, run_duration,error#,output
from dba_scheduler_job_run_details where job_name='DELETE_ARCHIVELOG_JOB' ;