Thursday, March 17, 2011

Delete Applied Archivelogs on Standby Database using PL/SQL

After I spent a lot of time trying to find a script to delete logs applied to the standby database, I decided to write my own.
Script, or PL / SQL block run over DBMS_BACKUP_RESTORE package and run as the job on the operating system. In this particular case, logs that was applied and older than 7 days will be deleted.

declare
cursor c_delete_logs is
select recid, stamp, name, thread#, sequence#, resetlogs_change#, first_change#, block_size
from v$archived_log
where
deleted = 'NO'
and applied='YES'
and completion_time < sysdate-7
order by recid desc;

r_delete_logs c_delete_logs%rowtype;
res binary_integer;
begin
open c_delete_logs;
loop
fetch c_delete_logs into r_delete_logs;
exit when c_delete_logs%notfound;

res := DBMS_BACKUP_RESTORE.validatearchivedlog
(
recid => r_delete_logs.recid,
stamp => r_delete_logs.stamp,
fname => r_delete_logs.NAME,
THREAD => r_delete_logs.thread#,
SEQUENCE => r_delete_logs.sequence#,
resetlogs_change => r_delete_logs.resetlogs_change#,
first_change => r_delete_logs.first_change#,
blksize => r_delete_logs.block_size
);
-- dbms_output.put_line(res);

if res=0 then
DBMS_BACKUP_RESTORE.deletearchivedlog
(
recid => r_delete_logs.recid,
stamp => r_delete_logs.stamp,
fname => r_delete_logs.NAME,
THREAD => r_delete_logs.thread#,
SEQUENCE => r_delete_logs.sequence#,
resetlogs_change => r_delete_logs.resetlogs_change#,
first_change => r_delete_logs.first_change#,
blksize => r_delete_logs.block_size
);
end if;

end loop;
commit;
end;
/