Skip to Main Content
  • Questions

  • Cannot sync RMAN catalog with control file

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Anya.

Asked: January 04, 2010 - 2:06 pm UTC

Last updated: September 26, 2012 - 11:57 am UTC

Version: 10.2.0.4

Viewed 10K+ times! This question is

You Asked

Tom --

I am on 10.2.0.4 using RMAN + RMAN catalog + Data Guard. I have a weird problem and I cannot find any explanation for why it is happening. Here is my setup: I take full database backup + archived logs using RMAN + catalog on primary) and full database backup using RMAN + catalog on standby every day. Maybe its an overkill, but we rather have more then less. I delete my archived logs from the OS, keeping 4 days worth of logs on disk and then delete expired using RMAN as follows:

#!/bin/ksh -x
# Script to delete archive logs older then 4 days

find /data/archive/PROD/archivelog -mtime +4 -exec rm -r {} \;
. /data/app/oracle/.profile
export ORACLE_SID=PROD
export DATESTAMP="`date +"%y:%m:%d"`"

rman <<EOF
SPOOL MSGLOG TO '/data/app/home/dba/backup_files/archive_logs/PROD_clear_a
rch${DATESTAMP}.log'
@/data/app/home/dba/scripts/connect_nocat.rman
run {
allocate channel d1 device type disk;

crosscheck archivelog all;
delete noprompt expired archivelog all;
release channel d1;
}
EXIT;
EOF


Recently my db crashed with the following:

ORA-27063: number of bytes read/written is incorrect
SVR4 Error: 49: Disc quota exceeded

I checked FRA and apparently it was 100% full archived logs occupying all of the space. So, delete expired did not delete from the FRA (although the files were gone from disk). If I execute this script while connected to the RMAN catalog then FRA does not get cleared up:

SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE                   0                         0               0
ONLINELOG                     0                         0               0
ARCHIVELOG                   99                     90.57            2828
BACKUPPIECE                   0                         0               0
IMAGECOPY                     0                         0               0
FLASHBACKLOG                  0                         0               0

6 rows selected.



If I execute the same script connected to target database only, then FRA gets cleared up:

SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE                   0                         0               0
ONLINELOG                     0                         0               0
ARCHIVELOG                 9.63                      9.63             263
BACKUPPIECE                   0                         0               0
IMAGECOPY                     0                         0               0
FLASHBACKLOG                  0                         0               0

6 rows selected.



Docs state that it is better to do everything with RMAN, rather then using OS method. So I changed my script to the following:

#!/bin/ksh -x
# Script to delete archive logs older then 4 days

. /data/app/oracle/.profile
export ORACLE_SID=PROD
export DATESTAMP="`date +"%y:%m:%d"`"

rman <<EOF
SPOOL MSGLOG TO '/data/app/home/dba/backup_files/archive_logs/PROD_clear_a
rch${DATESTAMP}.log'
@/data/app/home/dba/scripts/connect.rman
run {
allocate channel d1 device type disk;

crosscheck archivelog all;
delete noprompt archivelog until time 'sysdate-4';
release channel d1;
}
EXIT;
EOF


I still have the same problem. If I connect to the catalog, then FRA does not get freed up. If I only connect to target then it does. Resync of catalog does not help. Moreover, if I connect to catalog and target and do *list archivelog all* I will have 4 days worth of logs. If I connect to target only and execute the same command, I will get a month worth of logs. Is there a difference in these commands for the catalog and for control file? Is this a bug or am I just not understanding something essential here? My goal is to keep 4 days worth of logs on disk and delete the rest using RMAN. I would like to have all of the backup info in the catalog. Thank you!

and Tom said...

... (although the files were gone from disk)....

if you have a flash recovery area and are using it (I presume that is what you might probably mean by "FRA", when I see "FRA", I immediately think 'Frankfurt' of course, I'll be connecting through there later this month in fact - and do so about once a month - FRA means an airport to me, but I digress), then your rm trick isn't rm'ing anything we care about.

we archive to the flash recovery area. The files were there, we saw them. So what if you deleted some redundant copies we don't care about...\


you would use a retention policy with rman to control the archives in the flash recovery area

http://docs.oracle.com/cd/B19306_01/backup.102/b14192/setup005.htm#sthref257


Rating

  (19 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Flash Recovery

A reader, January 04, 2010 - 4:49 pm UTC

Thank you for the response! Yes, Flash Recovery Area, sorry, I try not to abreviate anything :)

The problem is that Flash Recovery Area gets full even if I have executed delete expired (should remove files which are not on disk anymore from Flash Recovery Area, right?) Moreover, my understanding is that retention for archives should be set to NONE on the site where backup is taken. I take backups on both sites, so I set the retention policy to NONE on both - primary and standby. Am I wrong? And also why does FRA gets cleared up when I do not connect to the catalog and does not clear up when I connect to the catalog? Thank you!

I find it weird that I can clean up Flash Recovery Area via my script
Tom Kyte
January 05, 2010 - 8:22 am UTC

... my understanding is that retention for
archives should be set to NONE on the site where backup is taken....

why - and it depends. but why? why wouldn't you want the archives in the flash recovery area? They would be, could be, should be used to reduce the time to recover in the event of a failure, also, they'll be retained to make it so the backup you have is recoverable.

have you backed up everything using rman - are you using the tool to manage everything. If rman doesn't know that archives exist elsewhere, rman isn't going to remove anything.


... I find it weird that I can clean up Flash Recovery Area via my script ...
again - why? why would that be strange? What do you think scripts should or should not be able to do ???

Flash Recovery

A reader, January 04, 2010 - 4:51 pm UTC

Thank you for the response! Yes, Flash Recovery Area, sorry, I try not to abreviate anything :)

The problem is that Flash Recovery Area gets full even if I have executed delete expired (should remove files which are not on disk anymore from Flash Recovery Area, right?) Moreover, my understanding is that retention for archives should be set to NONE on the site where backup is taken. I take backups on both sites, so I set the retention policy to NONE on both - primary and standby. Am I wrong? And also why does FRA gets cleared up when I do not connect to the catalog and does not clear up when I connect to the catalog? Thank you!

I find it weird that I can clean up Flash Recovery Area via my script

Resolution...

A reader, January 04, 2010 - 10:48 pm UTC


Hi "Reader"

Why not connect to both the catalog and the target db and delete the archives?

As for the diff. in behavior, you can do some research on Metalink or raise a SR with Oracle Support.

Cheers,
From "Another Reader" :)

follwup

A reader, January 05, 2010 - 9:15 am UTC

Tom --

Maybe I was not very clear. We DO store archives in the flash recovery area. When Flash Recovery Area is defined, unless otherwise specified, Oracle places archives there (correct?). However, I do not have enough space to keep archives in flash recovery area forever nor do I have enough space to keep them for a very long time on disk. Our policy is to retain them on disk for 4 days, then move them to tape.

I use RMAN to backup up everything - datafiles, archives, control file (autobackup on). The only thing is that this RMAN backup is stored outside of the FRA on a separate partition (again, due to space constraints we can only keep one backup on disk). RMAN knows about this backup, we use RMAN catalog where all of the related to backup information is stored.

My issue looks like a bug to me (again, I might be doing something totally dumb :)) When I connect to catalog and target in my script to DELETE EXPIRED ARCHIVELOG ALL (after deleting them from OS), logs get deleted from disk, but FRA does not get cleared up. I do not have the same problem when I just connect to target. I run into the same issue when I DELETE ARCHIVELOG UNTIL TIME ‘SYSDATE-4’ (no OS involved, all RMAN here) – Flash Recovery area gets cleared up if I am NOT connected to catalog and does not get cleared up if I am connected to catalog. Since I am using one central catalog for all of my databases, I am just not sure what to do in this situation.

In your opinion, what is the cleanest way to delete archives from disk in my situation?

Last comment about being surprised that files get deleted from Flash Recovery area – I did not copy paste the whole sentence, sorry :) Thank you!

Tom Kyte
January 05, 2010 - 10:43 am UTC

then why haven't you used rman to set up that retention policy??? That is what I'm asking - are you using the tool or are you trying to do it all yourself. It appears to me to be the latter.

what is your retention policy. If the retention policy is configured to NONE, then REPORT OBSOLETE and DELETE OBSOLETE do not consider any backups to be obsolete.

do you use rman regularly to move the data out of the flash recovery area.

do your commands appear to rman to be wiping out data it thinks it needs (sure sounds like it, the catalog seems to think "we need these archives - else our backups become useless")


retention

A reader, January 05, 2010 - 11:49 am UTC

Because of the following 10.2 documentation:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/rman.htm

When backups of archived redo log files are taken on the primary database (that is where I take them):

1. Issue the following on the standby database:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

2. Issue the following command on the primary database:
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE

I take full database backup on primary + archivelogs every day. I also take full database backup (no archivelogs) on standby. I have configured deletion policy to NONE on primary. Also, I followed “Oracle Data Guard Best Practices”, which states to configure ARCHIVELOG DELETION POLICY TO NONE on the database where backups are taken (I take backups on both – primary and standby)

http://www.oracle.com/technology/deploy/availability/pdf/oracle-openworld-2008/298772.pdf

Hence, I configured ARCHIVELOG deletion policy to NONE on both standby and primary, whereby I delete archived logs via a script. This is where I ran into trouble – whether the script involves OS or not (as I describe above). Is my set up (in terms of configuring RMAN policy/ deleting archived logs) incorrect? Is my understanding of the retention policy configuration within Data Guard incorrect?

Yes, I use RMAN script every day to backup my database + archived logs. I also use RMAN script every day to delete archived logs from disk and from FRA. However, we do not yet use RMAN to move RMAN backups to tape. So as far as the catalog is concerned we have disk backups. Please advise :( Thank you!

Tom Kyte
January 05, 2010 - 12:26 pm UTC

that is not an rman retention policy. what is your rman retention policy.


retention

A reader, January 05, 2010 - 12:56 pm UTC

Just checked and RMAN retention policy is set to NONE. Have no idea why, I think the idea was that we will eventually go to tape directly (we will use Networker as our third party tool). And Networker policies cannot be automatically be in sync with RMAN policies. Can that be a problem, because Oracle thinks that it needs all of the logs since backup retention policy is set to NONE?
Tom Kyte
January 06, 2010 - 6:52 am UTC

quote:

If the retention policy is configured to NONE, then REPORT OBSOLETE and DELETE OBSOLETE do not consider any backups to be obsolete.


Retention

A reader, January 06, 2010 - 11:14 am UTC

I understand that. But I should still be able to do DELETE EXPIRED (if I delete from OS level first) or DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-2' (if I do it all in RMAN) while connected to the catalog. Why does FRA get cleared when I am not connected to catalog and does not clear when I am connected. Maybe my syntax is wrong? How do I make sure that I a) Have 2 days worth of logs on disk and b) when I delete logs, catalog, FRA and control file stay in sync?
Tom Kyte
January 06, 2010 - 1:19 pm UTC

you are deleting from the flash recovery area? I don't think so - so the OS copies we care about - they are *still there* - I've said that. The stuff you are erasing - we don't care about, we don't manage that stuff. It isn't in the flash recovery area after all.

please read about setting up the retention policy in RMAN, so that these appear to be things we should be deleting as per the catalog.

retention

A reader, January 07, 2010 - 8:42 am UTC

Tom, I have set up a retention period for window of 60 days. I have one more question related to this. When I run LIST ARCHIVELOG ALL command while connected to the RMAN catalog I get different results then when I run the same command while connected to target only. Even if I resync catalog, the results are different? Is it ok for catalog and control file to be out of sync when it comes to archived logs? Thank you!
Tom Kyte
January 11, 2010 - 8:15 pm UTC

catalog is 'infinite', control files are not.

you don't tell us how they differ, so, I cannot really say anything more. It would be useful to understand what "differences" you are seeing.

Alexander, July 19, 2010 - 10:16 am UTC

Tom,

Is there anyway to catalog backups on remote storage? For some reason we always have old backups that never get cleaned up by RMAN retention policies and our storage people yell at us for going past SLAs. The only way we have to get rid of them right now is very hokey. We use Sybase sqlbacktrack commands to view and delete them.
Tom Kyte
July 19, 2010 - 2:10 pm UTC

define "remote storage" for me - I don't know what you mean by that.

Alexander, July 19, 2010 - 2:26 pm UTC

Know what TDP is? It's a disk based virtual tape library.
Tom Kyte
July 23, 2010 - 6:32 am UTC

so, how does it differ from DISK as far as an application is concerned. If it is a "disk based virtual tape library", then it should act like disk right - and therefore would not be "remote".

explain the difficulty you are having.

Alexander, July 23, 2010 - 8:06 am UTC

It's off on another sever. All the syntax I can find is like

catalog start with '/mybackup_dir/';

Which is looking locally on your machine.

I don't know if it's relevant but we are not using a recovery catalog.
Tom Kyte
July 23, 2010 - 9:49 am UTC

the file system would have to be available to the database server which writes to it.

there is no such thing as a "remote file system", there are

a) file systems you have access to
b) file systems you don't have access to

and (b) isn't going to get you anywhere.

Alexander, July 27, 2010 - 3:18 pm UTC

So the backups are going into a 3rd party proprietary database, not a filesystem. Which leads me to believe I can't do this.

Alexander, July 29, 2010 - 9:01 am UTC

Scratch that last post. This is very doable. I don't think you care Tom I'm just posting this in case someone else hits this.

If you are you TDP for Oracle, can need to clean up old backup files that Oracle has somehow lost track of, you can use the tdposync utility. It provides a slick interface to the backup files in your media management layer.

(x384pdc:oracle)> tdposync syncdb -tdpo_optfile=/database/oracle/ctl/OCP31S.opt
 
IBM Tivoli Storage Manager for Databases:
Data Protection for Oracle
Version 5, Release 3, Level 3.0
(C) Copyright IBM Corporation 1997, 2006. All rights reserved.
 
 
Catalog 1 User Name: oracle          <== this is the oracle db id
 
Catalog 1 Password: abcd1234    <== this is the oracle db password
 
Catalog 1 Connect String: OCP31S  <== this is the target database
 
 
From Date (1990-01-01):     <== this is the default from date
 
To Date (2010-07-28): 2010-07-09    <== this is the day before the oldest RMAN date 



SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jul 28 14:51:58 2010
 
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
 
 
Gathering Catalog information...Please wait
IBM Tivoli Storage Manager for Databases
Synchronize utility PICK Window
 Node Name:            X384PDC_ADSM
 Owner Name:           oracle
  
               Backup Date            Size        Backup Name
        ---------------------------------------------------------------------------------------
x    1. |  2010-07-09  08:03:11    2,969.00MB     /ocp31s//backup_OCP31S_1nliba1t_1_1
x    2. |  2010-07-08  08:03:11    3,198.00MB     /ocp31s//backup_OCP31S_1mli8llu_1_1
x    3. |  2010-07-08  02:53:11    2,966.00MB     /ocp31s//backup_OCP31S_1lli83gl_1_1
x    4. |  2010-07-07  05:53:10    2,985.00MB     /ocp31s//backup_OCP31S_1kli5pm4_1_1
x    5. |  2010-07-06  07:23:13    3,102.00MB     /ocp31s//backup_OCP31S_1jli3aiv_1_1
x    6. |  2010-07-05  07:43:12    3,081.00MB     /ocp31s//backup_OCP31S_1ili0ncf_1_1
x    7. |  2010-07-04  11:13:06    1,024.00KB     /ocp31s//backup_OCP31S_1hlhufa1_1_1
x    8. |  2010-07-04  11:05:21    3,682.00MB     /ocp31s//backup_OCP31S_1glhuerg_1_1
x    9. |  2010-07-04  11:05:17       13.00MB     /ocp31s//backup_OCP31S_1flhuerb_1_1
x   10. |  2010-07-04  11:02:21    3,806.00MB     /ocp31s//backup_OCP31S_1elhuels_1_1
x   11. |  2010-07-04  10:57:35       31.25GB     /ocp31s//backup_OCP31S_1dlhuecv_1_1
x   12. |  2010-07-04  10:43:40       31.25GB     /ocp31s//backup_OCP31S_1clhudir_1_1
x   13. |  2010-07-04  10:22:44       31.25GB     /ocp31s//backup_OCP31S_1blhucbk_1_1
x   14. |  2010-07-04  09:50:18       31.25GB     /ocp31s//backup_OCP31S_1alhuaeq_1_1
x   15. |  2010-07-04  09:15:13       31.25GB     /ocp31s//backup_OCP31S_19lhu8d0_1_1
x   16. |  2010-07-04  08:38:17       31.25GB     /ocp31s//backup_OCP31S_18lhu67o_1_1
x   17. |  2010-07-04  07:57:11       31.25GB     /ocp31s//backup_OCP31S_17lhu3qm_1_1
x   18. |  2010-07-04  07:11:55       31.25GB     /ocp31s//backup_OCP31S_16lhu15q_1_1
x   19. |  2010-07-04  06:22:58       31.25GB     /ocp31s//backup_OCP31S_15lhtua1_1_1
x   20. |  2010-07-04  05:19:02       31.25GB     /ocp31s//backup_OCP31S_14lhtqi5_1_1
x   21. |  2010-07-04  04:06:14       31.25GB     /ocp31s//backup_OCP31S_13lhtm9l_1_1
x   22. |  2010-07-04  02:49:35       31.25GB     /ocp31s//backup_OCP31S_12lhthpu_1_1
x   23. |  2010-07-04  01:30:56       31.25GB     /ocp31s//backup_OCP31S_11lhtd6g_1_1
x   24. |  2010-07-03  22:11:15       74.22GB     /ocp31s//backup_OCP31S_10lht1g1_1_1
x   25. |  2010-07-03  22:00:09    2,668.00MB     /ocp31s//backup_OCP31S_0vlht0r7_1_1
x   26. |  2010-07-03  05:53:11    2,994.00MB     /ocp31s//backup_OCP31S_0ulhr865_1_1
x   27. |  2010-07-02  07:03:10    3,035.00MB     /ocp31s//backup_OCP31S_0tlhontc_1_1
x   28. |  2010-07-01  07:43:10    3,084.00MB     /ocp31s//backup_OCP31S_0slhm5sc_1_1
        |
        |
        0---------10--------20--------30--------40--------50--------60--------70-----

<U>=Up  <D>=Down  <T>=Top  <B>=Bottom  <R#>=Right  <L#>=Left
<G#>=Goto Line #  <#>=Toggle Entry  <+>=Select All  <->=Deselect All
<#:#+>=Select A Range <#:#->=Deselect A Range  <O>=Ok  <C>=Cancel
 pick>


Alexander, October 06, 2010 - 9:25 am UTC

Hello,

Tom what can we do if we are not using a recovery catalog and our control_file_keep_time wasn't set correctly? If we need to access backups that RMAN no longer knows about (and they are on sbt_tape)? You can catalog disk backups, but not stuff stored in a 3rd party storage database).
Tom Kyte
October 07, 2010 - 1:41 am UTC

restore tape to disk as needed, and then catalog them.

Using rman without the recovery catalog is like having a telephone book that is not sorted - it is still a telephone book but a bit of a pain to use.

Alexander, October 07, 2010 - 9:02 am UTC

How do we restore something RMAN doesn't know exists? You mean have the storage people retrieve the files needs and put them on disk somewhere I can access?
Tom Kyte
October 11, 2010 - 10:20 am UTC

yes

put the files back where rman put them in the first place.

Alexander, August 31, 2011 - 12:18 pm UTC

Hi Tom,

We've been struggling at my place of work for years with backups not getting cleaned up and the storage people yelling at us for being out of SLAs. The following is an example and I just have no idea why RMAN is not automatically removing these:

/database/oracle/ctl
(pa0bpdc:)> rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Aug 31 13:11:38 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: OWWF1P (DBID=3081887940)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 42 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' FORMAT   'backup_%d_%U' PARMS  'ENV=(TDPO_OPTFILE=/database/oracle/ctl/OWWF1P.opt)';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/product/102x/dbs/snapcf_OWWF1P.f'; # default

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 42 days
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           23572  14-JUL-11
  Backup Piece       23572  14-JUL-11          backup_OWWF1P_krmhca2o_1_1
Backup Set           23575  14-JUL-11
  Backup Piece       23575  14-JUL-11          backup_OWWF1P_ksmhca2o_1_1
Backup Set           23574  14-JUL-11
  Backup Piece       23574  14-JUL-11          backup_OWWF1P_kumhca2p_1_1
Backup Set           23589  14-JUL-11
  Backup Piece       23589  14-JUL-11          c-3081887940-20110714-03
Backup Set           23594  14-JUL-11
  Backup Piece       23594  14-JUL-11          c-3081887940-20110714-04
Backup Set           23599  15-JUL-11
  Backup Piece       23599  15-JUL-11          c-3081887940-20110715-00
Backup Set           23604  15-JUL-11
  Backup Piece       23604  15-JUL-11          c-3081887940-20110715-01
Backup Set           23617  16-JUL-11
  Backup Piece       23617  16-JUL-11          c-3081887940-20110716-00
Backup Set           23622  16-JUL-11
  Backup Piece       23622  16-JUL-11          c-3081887940-20110716-01
Backup Set           23627  16-JUL-11
  Backup Piece       23627  16-JUL-11          c-3081887940-20110716-02
Backup Set           23632  16-JUL-11
  Backup Piece       23632  16-JUL-11          c-3081887940-20110716-03
Backup Set           23637  17-JUL-11
  Backup Piece       23637  17-JUL-11          c-3081887940-20110717-00
Backup Set           23642  17-JUL-11
  Backup Piece       23642  17-JUL-11          c-3081887940-20110717-01
Backup Set           23647  18-JUL-11
  Backup Piece       23647  18-JUL-11          c-3081887940-20110718-00
Backup Set           23651  18-JUL-11
  Backup Piece       23651  18-JUL-11          c-3081887940-20110718-01
Backup Set           23656  18-JUL-11
  Backup Piece       23656  18-JUL-11          c-3081887940-20110718-02
Backup Set           23657  18-JUL-11
  Backup Piece       23657  18-JUL-11          /oracle/product/102x/dbs/c-3081887940-20110718-03
Backup Set           23658  18-JUL-11
  Backup Piece       23658  18-JUL-11          /oracle/product/102x/dbs/c-3081887940-20110718-04
Backup Set           23659  18-JUL-11
  Backup Piece       23659  18-JUL-11          /oracle/product/102x/dbs/c-3081887940-20110718-05
Backup Set           23660  18-JUL-11
  Backup Piece       23660  18-JUL-11          /oracle/product/102x/dbs/c-3081887940-20110718-06
Backup Set           23661  18-JUL-11
  Backup Piece       23661  18-JUL-11          /oracle/product/102x/dbs/c-3081887940-20110718-07
Backup Set           23662  18-JUL-11
  Backup Piece       23662  18-JUL-11          /oracle/product/102x/dbs/c-3081887940-20110718-08
Backup Set           23663  18-JUL-11
  Backup Piece       23663  18-JUL-11          /oracle/product/102x/dbs/c-3081887940-20110718-09
Backup Set           23664  18-JUL-11
  Backup Piece       23664  18-JUL-11          /oracle/product/102x/dbs/c-3081887940-20110718-0a
Backup Set           23665  18-JUL-11
  Backup Piece       23665  18-JUL-11          /oracle/product/102x/dbs/c-3081887940-20110718-0b
Backup Set           23666  18-JUL-11
  Backup Piece       23666  18-JUL-11          /oracle/product/102x/dbs/c-3081887940-20110718-0c
Backup Set           23667  18-JUL-11
  Backup Piece       23667  18-JUL-11          /oracle/product/102x/dbs/c-3081887940-20110718-0d
Backup Set           23672  18-JUL-11
  Backup Piece       23672  18-JUL-11          c-3081887940-20110718-0e
Backup Set           23677  19-JUL-11
  Backup Piece       23677  19-JUL-11          c-3081887940-20110719-00
Backup Set           23682  19-JUL-11
  Backup Piece       23682  19-JUL-11          c-3081887940-20110719-01
Backup Set           23687  19-JUL-11
  Backup Piece       23687  19-JUL-11          c-3081887940-20110719-02
Backup Set           23692  19-JUL-11
  Backup Piece       23692  19-JUL-11          c-3081887940-20110719-03
Backup Set           23697  19-JUL-11
  Backup Piece       23697  19-JUL-11          c-3081887940-20110719-04
Backup Set           23702  20-JUL-11
  Backup Piece       23702  20-JUL-11          c-3081887940-20110720-00
Backup Set           23707  20-JUL-11
  Backup Piece       23707  20-JUL-11          c-3081887940-20110720-01

RMAN> exit


The cleanup portion of the backup script looks like this:

# ------------------------------------------------------------------
# TDP cleanup routines (if you're using TDP)
# ------------------------------------------------------------------
DELETE NOPROMPT OBSOLETE DEVICE TYPE SBT_TAPE ;
CROSSCHECK COPY DEVICE TYPE SBT_TAPE ;
DELETE NOPROMPT EXPIRED BACKUP DEVICE TYPE SBT_TAPE ;
CROSSCHECK BACKUP DEVICE TYPE SBT_TAPE ;
DELETE NOPROMPT EXPIRED BACKUP DEVICE TYPE SBT_TAPE ;
CROSSCHECK BACKUPSET DEVICE TYPE SBT_TAPE ;
DELETE NOPROMPT EXPIRED BACKUPSET DEVICE TYPE SBT_TAPE ;
CROSSCHECK ARCHIVELOG ALL DEVICE TYPE SBT_TAPE ;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL DEVICE TYPE SBT_TAPE ;


So I realize that because the way that is right now, the most RECENT obsolete backups wont be identified that week because there is no crosscheck before the delete obsolete. But that only explains why there would be one week old ones there, not from July.

I have hit this many times before, and I can just manually run the delete obsolete command, but I really need to understand why this is been happening, it's been plaguing us for years.
Tom Kyte
August 31, 2011 - 2:17 pm UTC

you have a 42 day retention policy. We'll keep everything we need to go back AT LEAST 42 days.


I don't know what are in these backup pieces - are they all full complete backups? Or - is there a good chance that not all files are backed up all of the time and hence we have to keep some old copies (and the archives) around in order to be able to put them back the way they were on jul 20th (42 days ago). That is - in the 18-jul backup pieces is there some data that might not be in the jul 20th/jul 21st... backup pieces. If so, the only way to recover the database to jul 20th (which you've asked for) would be to have that older data around.

How to delete backups but not the repository entries?

Shekar, May 24, 2012 - 12:09 am UTC

Hi Tom,

We delete day old backups everyday using rman. After deleting the backups their corresponding entries are deleted from the rman repository too. Is there any way to delete the bqackups but keep the repository entries for future reference? The backups are written to ASM disks, so cannot use operating system commands to delete them.
Thank you.
Tom Kyte
May 24, 2012 - 8:58 am UTC

why would you want them 'for reference'? The recovery catalog is to reflect what rman has available - rman would be a bit messed up during recovery unless you sync'ed up the catalog again.

I might suggest you copy out of the repository into your own history tables what you want to keep and then use rman to delete it. that way you'll have what you want and rman won't be "confused"

Old tape backups to be restored

NP, September 10, 2012 - 2:22 pm UTC

A follow up question to the above. Until few months back we used to take backups on filesystems. In those days, before each full backup we would just delete the last backup using OS commands and then run rman cross check. It would make the old backups obsolete, but the entries wouldn't be deleted from control file or catalog. In case we had a need to restore back to few weeks old backups, we would just restore the backup pieces from tape to the filesystem, catalog those backup pieces and run the restore. Because of business requirement we would keep the retention policy to recovery window of 60 days. Thus it was guaranteed that you would find information of 60 days old backup in the catalog. It worked well.

Fast forward to today, all our datafiles and backups are on ASM. Backups go to the FRA (on ASM) first and then we back up FRA to tape using rman media manager. We cannot delete the old backups using OS commands anymore, since they are on ASM. So we delete them using rman delete command. And as a result all the information about the old backups gets wiped out from the control file as well as from the catalog. We have retention policy set to REDUNDANCY 1 because with our earlier policy of 60 days recovery window rman would try to keep 60 days backups on FRA, which was not practical. But the business requirement of reverting back to 60 days still remains. What can be done in the current situation to meet that requirement?

The reason why we don't backup to tape directly is that restore run from FRA disks is faster than tape restore.

Tom Kyte
September 26, 2012 - 11:57 am UTC

you should have to do nothing, if you back up to tape using RMAN, we'll remove them when we need space


http://www.oracle.com/technetwork/issue-archive/2007/07-jan/o17recovery-087778.html


Request your comment

NP, September 20, 2012 - 11:24 am UTC

Hi Tom,
I would really appreciate if you could comment on my above question.
Thank you.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.