Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

RMAN script to delete archive file

SOLVED
Go to solution
Ratzie
Super Advisor

RMAN script to delete archive file

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?
4 REPLIES
Yogeeraj_1
Honored Contributor

Re: RMAN script to delete archive file

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Eric Antunes
Honored Contributor
Solution

Re: RMAN script to delete archive file

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
Each and every day is a good day to learn.
Ratzie
Super Advisor

Re: RMAN script to delete archive file

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
Honored Contributor

Re: RMAN script to delete archive file

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
Each and every day is a good day to learn.