RE: Cleaning up my RMAN catalog

From: Dunbar, Norman <norman.dunbar_at_environment-agency.gov.uk>
Date: Wed, 2 Jun 2010 08:16:00 +0100
Message-ID: <919FC80F27E0C6428106496EDF92A7520D52ABBD_at_EXCCLUS05.PRODDS.NTNL>

Morning Janine,

>> Is there some way to completely reset things so that it
>> looks like I've never run a single backup of any kind? I
>> have Googled and searched the Backup & Recovery doc and all
>> I can find is dropping the catalog database, which doesn't
>> apply in my case.

On a 9i system we use here, the old backups are trashed by the server housekeeping processes when they are "of an age so as to be no longer usable". Our daily backup (backup & duplicate actually) does this in order to sync the catalog with the backups (still) left on disc.

I admit this isn't the ideal way, but we only use rman in this way to create a daily reporting database from the production one. The reporting team have, in the past, run some "interesting" queries!

Anyway, we do this:

rman target= / catalog=rman/longines_at_dba9ipr << EOF

	allocate channel for maintenance type disk;
	crosscheck backup;
	crosscheck archivelog all;
	delete noprompt expired backup;
	delete noprompt obsolete;
	release channel;
	list backup;
	exit;

EOF That gets rid of the "missing" backups from the catalogue. I'm pretty certain (read about 60%!) that the same should be true for your set up where you use the controlfile. However, I have not checked. And anyway, if I'm wrong, a lot of people will tell me - so I get to learn something too! ;-)

Looking at the 11g RMAN Recipes book (Kuhn, Alapati & Nanda) there is a change .. uncatalog command which is, apparently, for situations whereby the OS utilities have been used to delete a backup and now you need to tell rman about it. Again, it's catalog based but might work with control files because it states that the command "updates a deleted backup record's status to deleted in the controlfile repository" - which sounds just what you need.

It's as simple (!) as "change XXXX 'full name to file' uncatalog;" where XXXX is controlfilecopy or datafilecopy etc and the full path is the full path to the file that was backed up, not (unfortunately) the name of an rman backup.

It's possible that you might have to build a script with a lot of these change commands to get all your archivelog backups "deleted".

Good luck.

Cheers,
Norman.

PS. Good books:

Robert Freeman - Oracle xx RMAN Backup & Recovery. (xx being 9i, 10g or 11g)
Kuhn, Alapati & Nanda - RMAN Recipes for Oracle Database 11g.

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jun 02 2010 - 02:16:00 CDT