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;
/
9 comments:
Hello,
Not sure if you're still monitoring your blog or not (no post for a long time), but I found this and would like to implement at my work. Does this get run on primary or standby database? Just curious. Please let me know!
Thank you!!
Jeremy
Hello,
This script running on standby.
Dragoslav
Hi,
wouldn't be simpler to use RMAN?
run {
allocate channel for maintenance device type disk;
delete archivelog until time 'sysdate -7';
}
Hello, Dejan!
Nice to see you, again.
Yes, it's simpler.
In addition, you can configure archivelog deletion policy:
configure archivelog deletion policy to applied on standby;
and start using rman script similar to your.
However, the script that I posted here is very flexible and with a little of imagination can be used in many different ways. For example, in one version, I use this script to delete the oldest 40MB of archivelogs that are applied, archived, with the status available, and that are backed up at least once, all on condition that the total size of archivelog files on the disk is larger than 120GB (on that server I have a total of 160GB of storage available for archivelog files).
thanks for sharing the script. It was worth applying to Standby using a cronjob.
thanks for sharing the script. It was worth applying to Standby using a cronjob.
how to make this above script as windows schedule job? Would you plz provide as code?
Many years after your posting this, it's exactly what I need. Thank you!! (12.1.0.2)
I'm using exactly the same script until today. :D
Post a Comment