RMAN script to delete archive file

[Ratzie]

So we are running streams with 10.2.0.2.
In my RMAN backup script I backup the database, and archivelogs, I would then like to run a script that will run a query on dba_logmnr_log
Where KEEP =NO, and then delete the archive log.
Can I run a loop from within RMAN.
So, query dba_logmnr_log. Where KEEP=NO
Store sequence number, then run a loop to delete the archive log.


--RMAN> connect target /
--RMAN> crosscheck archivelog all;
--RMAN> delete archivelog sequence=###;
--RMAN> crosscheck archivelog all;

Has anyone done this?

[Yogeeraj_1]

hi,

The only way to do this is to dynamically generate the rman script and run it.

e.g Use SQL and spool command to generate the scripts.

hope this helps!
kind regards
yogeeraj

[Eric Antunes · Accepted solution]

Hi Ratzie,

I don't have streams neither 10g here but why don't you backup and delete the archivelogs in your rman backup script:

run{
...
backup
archivelog all delete input;
}

Best Regards,

Eric Antunes

[Ratzie]

Again, because of streams we can not do that, streams may still be using it, hence the query for sequence number that streams does no longer needs. KEEP=NO

[Eric Antunes]

Hi,

As Yogeeraj already said, the only way to do this is to dynamically generate the script and execute it after.

It would be something like this:

select
'run { allocate channel ''dev_0'' type ''sbt_tape'' backup archivelog from sequence = '
||min(al.sequence#)||' until sequence = '||max(al.sequence#)||' delete input;'
||'}'
from v$archived_log al

This works for me for v$archived_log: just change v$archived_log to dba_logmnr_log and, if sequence# doesn't exists, the approriate column. Finally, add it the "where KEEP=NO" clause:

select
'run { allocate channel ''dev_0'' type ''sbt_tape'' backup archivelog from sequence = '
||min(dll.sequence#)||' until sequence = '||max(dll.sequence#)||' delete input;'
||'}'
from dba_logmnr_log dll
where KEEP=NO


Best Regards,

Eric Antunes