RMAN - removing old catalog entries

I want to remove RMAN catalog entries older than 30 days. The backup pieces have long since been moved off the system.

I understand I can use the RMAN CHANGE command to remove just the catalog records but I can't find the syntax anywhere. Can anyone help me?

Here's my best guess at piecing it together:
CHANGE BACKUP COMPLETED BEFORE 'SYSDATE-30' UNCATALOG;

Ken

Is this what you are looking for (CHANGE)?

Quote:

---

Originally Posted by ebrian

Is this what you are looking for (CHANGE)?

---

Yea, I read the docs. They're just not clear on the syntax for the example above.

Cheers

Hi

Does this work

CHANGE BACKUPSETS COMPLETED BEFORE 'SYSDATE-30' UNCATALOG;

if that doesnt work one other way of doing that would be

http://www.orafaq.com/scripts/unix/purgecat.txt

Just change the line
echo "CHANGE BACKUPSET $key DELETE;" >>$CMDFILE

in that script to
echo "CHANGE BACKUPSET $key UNCATALOG;" >>$CMDFILE

and also this line
list backupset of database
from time 'SYSDATE-3000' until time 'SYSDATE-$DAYSTOKEEP'

Hmm Frank Nuade has done some smart MetaProgramming i beleive ;-)

regards
Hrishy

Exactly what I was looking for. Thank you Hrishy!

RMAN> change backupset completed before 'sysdate-30' uncatalog;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of uncatalog command at 07/02/2008 12:55:34
RMAN-06122: CHANGE .. UNCATALOG not supported for BACKUPSET

Found this...

Oracle Error : RMAN-06122: CHANGE .. UNCATALOG not supported for BACKUPSET
Cause: The CHANGE BACKUPSET .. UNCATALOG command was entered. The UNCATALOG operation is not supported with backup set.
Action: Use CHANGE BACKUPSET .. DELETE instead.

Then did this:
RMAN> change backupset completed before 'sysdate-30' delete;
...
Do you really want to delete the above objects (enter YES or NO)? YES

But when I "list backupset completed before 'sysdate-30';" they're all still there... urgh

Any ideas?

Hi

Can you try by deleting the backupset with the keys
by using the following script
http://www.orafaq.com/scripts/unix/purgecat.txt

regards
Hrishy

Have you tried:

Code:

---

RMAN> crosscheck backup completed before 'sysdate-30';
RMAN> delete expired backup completed before 'sysdate-30';

---

Thanks. I've just about re-engineered the purgecat.ksh script for 10.2 catalog. The commands and output are a little different.

I'll give this a check and see if that doesn't do the trick and report back. Thanks for your assistance.

Quote:

---

Originally Posted by ebrian

Have you tried:

Code:

---

RMAN> crosscheck backup completed before 'sysdate-30';
RMAN> delete expired backup completed before 'sysdate-30';

---

---

This works perfectly. Thank you!

Is there a way to feed into the script the "YES" response?

Hi

Try

Code:

---

RMAN> delete noprompt expired backup completed before 'sysdate-30';

---

noprompt worked like a charm. Thanks.

For the benefit of posterity...

run {
crosscheck backup completed before 'sysdate-30';
delete noprompt expired backup completed before 'sysdate-30';
}
exit;

Logging the output was a little screwey. The command line LOG didn't work at all, "spool log to" worked in the script, but .. and here's the weird part .. only AFTER the "exit;". If "spool log off" was before the "exit;", it created a 0 byte file.

Thanks for posting back your results.

Not exactly sure how you are executing the RMAN script, but two examples to get logging to occur are as follows:

If you have a file called rman.cmd as:

Code:

---

run {
crosscheck backup completed before 'sysdate-30';
delete noprompt expired backup completed before 'sysdate-30';
}

---

You can run the command with proper logging using:

Code:

---

rman target / log=/tmp/rman.log @/tmp/rman.cmd

---

For the spooling option, you can create a rman.cmd file as (notice NO 'spool log off'):

Code:

---

spool log to /tmp/rman.log;
run {
crosscheck backup completed before 'sysdate-30';
delete noprompt expired backup completed before 'sysdate-30';
}

---

And run it as follows:

Code:

---

rman target / @/tmp/rman.cmd

---

Right. I tried it both ways, but in **this case only** spooling inside the script and with the exit prior to "spool log off" is the only way I can get it to spool the output.

This is not typical behavior. Must be something about the contents of the script .. like noprompt or something.

I have other rman scripts that behave as expected whether the logging is in the script or on the command line.