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;
/

9 comments:

Jeremy said...

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

Dragoslav Gnjatovic said...

Hello,

This script running on standby.

Dragoslav

Dejan Topalovic said...

Hi,

wouldn't be simpler to use RMAN?

run {
allocate channel for maintenance device type disk;
delete archivelog until time 'sysdate -7';
}

Dragoslav Gnjatovic said...

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).

dbacas said...

thanks for sharing the script. It was worth applying to Standby using a cronjob.

dbacas said...

thanks for sharing the script. It was worth applying to Standby using a cronjob.

ESKAMOD SOLUTION said...

how to make this above script as windows schedule job? Would you plz provide as code?

DOUG CHASE said...

Many years after your posting this, it's exactly what I need. Thank you!! (12.1.0.2)

Dragoslav Gnjatovic said...

I'm using exactly the same script until today. :D