RE: RMAN catalog maintenance
- From: "Ruel, Chris" <Chris.Ruel@xxxxxxx>
- To: "sbecker6925@xxxxxxxxx" <sbecker6925@xxxxxxxxx>, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 27 Jun 2014 13:22:34 +0000
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@xxxxxxx<mailto:cruel@xxxxxxx> * Desk:317.759.2172 * Cell 317.523.8482
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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.**
Other related posts:
