Skip to Main Content
  • Questions
  • RECOVERY CATALOG AND EXPIRED ARCHIVELOG INFORMATION

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, vishnudas.

Asked: May 12, 2018 - 11:47 am UTC

Last updated: October 02, 2020 - 2:12 am UTC

Version: 11.2.0.1.0

Viewed 10K+ times! This question is

You Asked

Hi
there...
i have some doubt here.googled it many times but couldn't find anything..

i invoked RMAN and issued the folowing command..


RMAN> list expired archivelog all;

using target database control file instead of recovery catalog
specification does not match any archived log in the repository

so it says that "using target database control file instead of recovery catalog"
1)does this mean
that my database has a recovery catalog???

2)if i want to delete the expired archive log how can i do that??

3)if recovery catalog is created for my database how can i connect it again??


But when i issued CROSSCHECK ARCHIVELOG ALL;

i got listed some files what does this mean??Is it better to use "CROSSCHECK ARCHIVELOG ALL"
INSTEAD OF "LIST EXPIRED ARCHIVELOG ALL"??

And,These are my RMAN setting for the database..


RETENTION POLICY TO RECOVERY WINDOW OF 8 DAYS;

BACKUP OPTIMIZATION OFF; # default

DEFAULT DEVICE TYPE TO DISK; # default

CONTROLFILE AUTOBACKUP ON;

CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:\rman_backup\%d_controlfile_%F';

DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

MAXSETSIZE TO UNLIMITED; # default ENCRYPTION FOR DATABASE OFF; # default

ENCRYPTION ALGORITHM 'AES128'; # default

COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

ARCHIVELOG DELETION POLICY TO NONE; # default

SNAPSHOT CONTROLFILE NAME TO 'D:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\DUMMY.ORA'; # default






and Connor said...

1)does this mean that my database has a recovery catalog???

No. It simply means you did not connect to a catalog (whether it exists or not). We used information in the control file in lieu of a catalog. A catalog does not exist unless you have explicitly created one.

3)if recovery catalog is created for my database how can i connect it again??

If you had created a catalog in the past, you would connect to it when you run RMAN, eg

rman target myuser/mypass catalog mycatuser/mycatpass@mycatdb

2)if i want to delete the expired archive log how can i do that??

Let RMAN do it. You set your retention policy as per your business requirements and then issue:

delete expired archivelog all;

Is it better to use "CROSSCHECK ARCHIVELOG ALL"

No! Crosscheck as the name suggests is a way of working out if RMAN is out of sync with reality. For exmaple, if you deleted some archives via OS commands and hence RMAN did not know. I know in the past people would just, delete from the OS, then run crosscheck to get RMAN in sync.

The problem with this is that if you have a true problem (ie, an archivelog is missing and was not backed up), then once you run crosscheck - you have just told RMAN that this archive no longer exists and if it was never backed up - then you have big recovery dramas. Let RMAN do the deletion and run crosscheck as a means of confirming that RMAN is *always* in sync, rather then *getting* RMAN in sync.

Rating

  (4 ratings)

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

Comments

vis, May 22, 2018 - 6:27 am UTC

Thank you... :) now iam in the right track!!

Kevin, September 29, 2020 - 1:23 pm UTC

The problem with this is that if you have a true problem (ie, an archivelog is missing and was not backed up), then once you run crosscheck - you have just told RMAN that this archive no longer exists and if it was never backed up - then you have big recovery dramas. Let RMAN do the deletion and run crosscheck as a means of confirming that RMAN is *always* in sync, rather then *getting* RMAN in sync.

I currently am in this situation and do not know how to recover?? I am trying to duplicate the production database to the test environment on the same server. it fails looking for the deleted archivelog files.... I have done a "new" backup that includes my current archivelogs, set my backup retention policies to 1 day. I have crosschecked and deleted expired. cataloged... etc.
Connor McDonald
September 30, 2020 - 2:26 am UTC

If you do "duplicate from active database" then the process should take care of getting all the archive logs that are required.

Kevin, September 30, 2020 - 2:46 pm UTC

I have tried duplicate from active database as well and also crosschecked backups and copys... no luck. whits end.
Connor McDonald
October 01, 2020 - 1:06 am UTC

If "duplicate from active database" didn't work, then this is not a missing archive problem because we don't look for old backups - we take a new one.

Shows us the entire output from your duplicate.

Kevin, October 01, 2020 - 8:16 pm UTC

Ok, I started completely from scratch in order to get the log files fresh. new backup, new delete obsolete, tried copying from backups with no luck. then tried "DUPLICATE FROM ACTIVE DATABASE" and it worked. not sure what the difference was. I have bounced and had a snap file issue corrected since the last time I tried it... so unless I made a mistake during my process I can't really tell why it did work this time.

here is the output from the failed duplicate from backup too much to paste, cant upload the file so I added a link to google drives, hopefully that works.
https://drive.google.com/file/d/1iuhBP07Ub0ltEq_BcnR55PRA0Um7dj7g/view?usp=sharing
Connor McDonald
October 02, 2020 - 2:12 am UTC

RMAN duplicate by default always tries to "resume" from a failed one. See in the logs you have this:

Using previous duplicated file E:\APP\ADMINISTRATOR\ORADATA\SAND\SYSTEM01.DBF for datafile 1 with checkpoint SCN of 1554002103
Using previous duplicated file E:\APP\ADMINISTRATOR\ORADATA\SAND\SYSAUX01.DBF for datafile 2 with checkpoint SCN of 1554002103
Using previous duplicated file E:\APP\ADMINISTRATOR\ORADATA\SAND\UNDOTBS01.DBF for datafile 3 with checkpoint SCN of 1554002103
Using previous duplicated file E:\APP\ADMINISTRATOR\ORADATA\SAND\USERS01.DBF for datafile 4 with checkpoint SCN of 1554002103
Using previous duplicated file E:\APP\ADMINISTRATOR\ORADATA\SAND\IMPDATA01.DBF for datafile 5 with checkpoint SCN of 1554002103
Using previous duplicated file E:\APP\ADMINISTRATOR\ORADATA\SAND\IMPINDEX01.DBF for datafile 6 with checkpoint SCN of 1554002103
Using previous duplicated file E:\APP\ADMINISTRATOR\ORADATA\SAND\REPORTRAKTS.DBF for datafile 7 with checkpoint SCN of 1554002103


so even though you ran a fresh duplicate command, we're using old datafiles from a previous duplicate/restore/etc.

Delete those and see how you go

More to Explore

Backup/Recovery

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