RMAN - advisable maintenance?

[KenEwald]

After deleting a few years of backup pieces, I have stretched out catalog tables.

Has anyone had success rebuiding (ex: move) the larger and now much more fluffy tables, like rc_backup_piece?

[LKBrwn_DBA]

RC_BACKUP_PIECE is a view, but you can move the underlying tables ( db, bs, bp) as with any regular table:

ALTER TABLE BP MOVE TABLESPACE New_Tablespace;

[KenEwald]

You're absolutely right on that view... Thanks.

Thanks for your input. I suppose if I take the move route, I'll need to rebuild indexes.

[BV1963]

What is an area of concern? Backup performance? Space management? I wish I had this sort of problem .

[KenEwald]

good questions. Both.

The system the repository database lives on is small and doesn't have much space to grow into.

Performance is always a concern and a broad brush approach of first cleaning out the dead wood seemed the right thing to do in this case.

Upon further review, it looks like there's a couple Metalink notes on resync performance issues in a 10.2.0.3 catalog that are fixed in 10.2.0.4. Changing the default output log retention from 30 to 7 days was probably the biggest one.

Cheers

[BV1963]

Ok, space is not an issue.
so, performance must be a problem.
How much does it slow down your backup? No offense, it just does not seem like a very critical issue to me. Besides, you can problaby run a backup without a catalog and resync soon after backup was done.

[KenEwald]

Ok, space is not an issue.

Not normally, but It is an issue on this system.

so, performance must be a problem.
How much does it slow down your backup? No offense, it just does not seem like a very critical issue to me.

No offense taken. Thank you for being candid. Critical is relative to condition. In this case, catalog performance is critical.

Besides, you can problaby run a backup without a catalog and resync soon after backup was done.

Perhaps in most environments. I'm manually sync'ing a standby via the catalog and prompt knowledge of backup piece names is critical.


This is perhaps part of the slow resync problem...

See Metalink note 378234.1 Rman Catalog Resync Operation is Very slow at 10G
This is more of an issue when you take frequent backups.

[BV1963]

Will definitely look into the note. One thing that I do and I found to be usefull, to keep separate catalog owners for different databases. In this case not wanted catalog can be dropped at any time. Same can be done (meaninig creating different user id) for the same database on the periodical basis. Let's say create new catalog every six month, keep an old one for a while and later drop it.

[KenEwald]

Applied the workaround for that bug, and it reduced the number of rows by about a million in ROUT. I'm currently moving the HWM back on that table and am hoping for much faster resync's soon.

[BV1963]

Just curios, how bad backup performance was before and after the patch and cleanup effort.If you could, please share some benchmarking.