Re: RMAN catalog maintenance

From: Jeremy Schneider <jeremy.schneider_at_ardentperf.com>
Date: Fri, 27 Jun 2014 09:28:07 -0500
Message-ID: <CA+fnDAa4dSeXUto_pDNPo1SqspFA9eo-q38e73Y47Zrfz10-xA_at_mail.gmail.com>



One extra tip I'd add to the mix - this isn't directly related to your original question around catalog consolidation, but maybe useful and not immediately obvious to newer DBAs: I'd be careful how your backup script connects to the catalog.

In some cases, a connectivity issue with the catalog can prevent the backup from running. This is not ideal. My preferred setup is to run the backups without a catalog and then either have a automatic resync right after each backup (part of backup script) or have automatic resyncs at some interval automatically (x times per day).

-J

--
http://about.me/jeremy_schneider


On Fri, Jun 27, 2014 at 8:52 AM, Garry Chen <gc92_at_cornell.edu> wrote:


> Since your Oracle version crossed several released, one thing you might
> need to look out for is the RMAN binary that you used to do the backup. It
> is better to use same release of RMAN binary to backup the same database
> release. In my current environment, only one RMAN catalog for all
> databases but we do use difference RMAN binary for each release of
> database. The only performance derogation we see is on the storage device
> not the RMAN itself. Since we are using TSM for the backup storage, a RMAN
> test environment is used for testing TSM.
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Ruel, Chris
> *Sent:* Friday, June 27, 2014 9:23 AM
> *To:* sbecker6925_at_gmail.com; oracle-l
> *Subject:* RE: RMAN catalog maintenance
>
>
>
> I think you are on the right track. Many of the environments I have been
> in use a catalog to support hundreds of databases with no issues. I know
> in versions past, sometimes there were rman catalog performance issues but
> you would usually be able to deal with that by hitting up MOS and making
> some index adjustments. Most recently though, I have not experienced
> that. As far as I am concerned, splitting dev/prod is your call. I
> usually just have one catalog with everything.
>
>
>
> So, do you know what you retention is for your backups? From the code
> examples you gave, it also looks like you keep all backups on disk. Are
> the backups then deleted via RMAN or deleted via OS commands? If at all
> possible, just do all backup deletes through RMAN and use the DELETE
> OBSOLETE command as part of your backup script...or a separate script that
> runs daily. This will just delete anything older than your retention
> policy. The CROSSCHECK command is only really needed if you are removing
> backups manually and the catalog/controlfile are not aware.
>
>
>
> I am not sure why your predecessors used multiple CROSSCHECK commands to
> delete/crosscheck the backups in stages...maybe someone else can think of a
> reason but I have never needed to do that.
>
>
>
>
>
>
>
> Chris Ruel * Oracle Database Administrator
>
> cruel_at_lfg.com * Desk:317.759.2172 * Cell 317.523.8482
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [
> mailto:oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org>] *On
> Behalf Of *Sandra Becker
> *Sent:* Thursday, June 26, 2014 6:12 PM
> *To:* oracle-l
> *Subject:* RMAN catalog maintenance
>
>
>
> After our DBA team was cut in half during recent layoffs, I was tasked
> with simplifying our current backup strategy. We have a mixture of 10gr2
> through 11gR2. Every database had its own catalog in production but lower
> environments had catalogs based on the database version. When I questioned
> this strategy last fall (shortly after I started), I was told this was
> necessary because a single catalog was too slow. Never got any details on
> what exactly was slow though. The multiple catalogs led to yet another
> schema, tables, scripts to collect all the backup information in one place
> for the daily report.
>
> So I was tasked with consolidating this multitude of catalogs into
> two--production and lower environments. Consolidation has gone well so
> far. Backups are not taking any longer, I created an Apex report to pull
> the data from both catalogs into a single report (rather than the three we
> previously had.)
>
> We have a catalog maintenance script that runs for each database. It
> crosshchecks and deletes expired backups. The databases don't run the
> script at the same time so there's less contention on the catalog.
>
> Now my question:
>
> I would like to ensure this script is as efficient as possible. I see the
> following and was wondering if this is really an efficient script:
>
> crosscheck backup completed between 'sysdate - 90' and 'sysdate - 35';
> delete force noprompt expired backup completed between 'sysdate - 90' and
> 'sysdate - 35';
> crosscheck backup completed between 'sysdate - 180' and 'sysdate - 91';
> delete force noprompt expired backup completed between 'sysdate - 180' and
> 'sysdate - 91';
> crosscheck backup completed between 'sysdate - 270' and 'sysdate - 181';
> delete force noprompt expired backup completed between 'sysdate - 270' and
> 'sysdate - 181';
> crosscheck backup completed between 'sysdate - 365' and 'sysdate - 271';
> delete force noprompt expired backup completed between 'sysdate - 365' and
> 'sysdate - 271';
>
> I'm double-checking, but to the best of my knowledge, we don't have any
> backups older than 90 days. Still, it seems inefficient to me to
> repeatedly do the crosschecks and deletes. What am I not understanding
> here?
>
> Thanks for help/suggestions.
>
>
>
>
> --
> Sandy
> Transzap, Inc.
>
> Notice of Confidentiality: **This E-mail and any of its attachments may
> contain
> Lincoln National Corporation proprietary information, which is privileged,
> confidential,
> or subject to copyright belonging to the Lincoln National Corporation
> family of
> companies. This E-mail is intended solely for the use of the individual or
> entity to
> which it is addressed. If you are not the intended recipient of this
> E-mail, you are
> hereby notified that any dissemination, distribution, copying, or action
> taken in
> relation to the contents of and attachments to this E-mail is strictly
> prohibited
> and may be unlawful. If you have received this E-mail in error, please
> notify the
> sender immediately and permanently delete the original and any copy of
> this E-mail
> and any printout. Thank You.**
>
-- http://www.freelists.org/webpage/oracle-l
Received on Fri Jun 27 2014 - 16:28:07 CEST

Original text of this message