Rman table/view cleanup

[Nugpot]

Hi,

version - 11.1.0.7

I have Rman backups running on a database. Due to some application requirements for testing, the system date was changed to reflect a year ago. So. it's June 2011 now.

My scripts to report backup status etc are not working as expected as they are looking for the max(end_time) from v$rman_status.

Is there any way to cleanup or reinstall the rman dictionary.

Appreciate your help

Regards
Jay

[LKBrwn_DBA]

Yes, take a look at the "CHANGE" command.

[Nugpot]

Hi,

I checked out the link but I'm still not sure what I need to use for my sceanrio.

Here's the output from v$rman_status

select status, start_time, end_time, operation from v$rman_status where operation='BACKUP' order by start_time;

STATUS START_TIM END_TIME OPERATION
----------------------- --------- --------- ---------------------------------
COMPLETED 16-JUN-11 16-JUN-11 BACKUP
COMPLETED 16-JUN-11 16-JUN-11 BACKUP
COMPLETED 16-JUN-11 16-JUN-11 BACKUP
COMPLETED 16-JUN-11 16-JUN-11 BACKUP
COMPLETED 17-JUN-11 17-JUN-11 BACKUP
COMPLETED 17-JUN-11 17-JUN-11 BACKUP
COMPLETED 17-JUN-11 17-JUN-11 BACKUP -- 17th is sysdate on the server today

March 12th was when the system date was changed on the server ..
so the output also has
COMPLETED 11-MAR-12 11-MAR-12 BACKUP
COMPLETED 11-MAR-12 11-MAR-12 BACKUP
COMPLETED 11-MAR-12 11-MAR-12 BACKUP
FAILED 11-MAR-12 11-MAR-12 BACKUP
COMPLETED 11-MAR-12 11-MAR-12 BACKUP
COMPLETED 12-MAR-12 12-MAR-12 BACKUP
COMPLETED 12-MAR-12 12-MAR-12 BACKUP

So, basically I need to get rid of all the entries of 2012.

Thanks again

Jay

[LKBrwn_DBA]

Perhaps DELETE would work:

DELETE BACKUPSET COMPLETED AFTER '17-JUN-11:23:59:59';

[Nugpot]

Hi,

I tried the following .. nothing happened.

RMAN> DELETE BACKUPSET COMPLETED AFTER 'sysdate';

using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4

RMAN>

Regards
Jay

[gandolf989]

You can also unregister and reregister your database. Unregistering will delete the backups in the catalog. If that doesn't work, unregister the database, then use "nid" to change the db id, then reregister the database. this will work.

[LKBrwn_DBA]

Hi,

I tried the following .. nothing happened.

RMAN> DELETE BACKUPSET COMPLETED AFTER 'sysdate';

Don't use quotes:

DELETE BACKUPSET COMPLETED AFTER sysdate;

Or, do as gandolf989 suggests to get "clean" catalog.

[Nugpot]

Finally got the downtime to fix it..

changing the dbid did the trick.

Thanks for all your help!!

Regards
Jay