How to delete the old unavailable backup from RMAN catalog

Hi all,

I have the following setup:
9.2.0.4 Catalog
8.1.7.4 Target
8.1.7.4 RMAN used to backup the target

The problem is that I had a set of RMAN backups to disk. These test sets happen to have been deleted on the OS level. So in order to remove them, I first did a crosscheck, then made then unvailable and then tried deleting them. For some reason, the catalog does not seem to delete the entry. When I try doing some validation on the backup sets and archive log, RMAN is looking for some way older archives which no longer exists. I even tried 'delete expired backups'. So my questions are as follows:

1. How do one remove the entry? Checked the RC_BACKUP_SET and notice the entries had been marked with status "O" (Which I presume to be partially unavailable)

2. How to get rid of the old archive log information from the controlfile? ( present controlfile_record_keep_time is at default which is 7)?

Thanx in advance...

Thanx,
Sam

> For some reason, the catalog does not seem to delete the entry.

Any error messages? Can you cut n paste the exercise? I do this all the time on 9i databases (target and catalog on 9i) and it seems to work fine.

> When I try doing some validation on the backup sets and archive log, RMAN is looking for some way older archives which no longer exists.

Again - please paste. It shouldn't be looking "way older" archives if you are connecting using the target controlfile (NOT the catalog) and your controlfile_record_keep_time=7.

> How to get rid of the old archive log information from the controlfile? ( present controlfile_record_keep_time is at default which is 7)?

See above. Are you using the catalog or the target controlfile?

Thanx for the feedbacks.

As you can see on my setup description, I'm using the catalog and not the control file and has a mix environment and not 9i to 9i. There were so much differences and improvement of using 9i RMAN with respect to 8i RMAN.

1. No Errors upon doing a crosscheck or delete expired backup option
2. List backup shows the following :

Quote:

---

List of Backup Sets
Key Recid Stamp LV Set Stamp Set Count Completion Time
------- ---------- ---------- -- ---------- ---------- ----------------------
535 27 526414621 0 526414618 27 17-MAY-2004 18:17:01

List of Backup Pieces
Key Pc# Cp# Status Completion Time Piece Name
------- --- --- ----------- ---------------------- ------------------------
562 1 1 UNAVAILABLE 17-MAY-2004 18:17:01 /backup/WEBTIME_bkup/arch_WEBTIME_t526414618_s27_p1_disk

---

This was what I got after running delete expired backup.

The RMAN validation fails with the following errors:

Quote:

---

replace script validate_restore_db_level_0 {
execute script allocate_2_tapes;
execute script allocate_2_disks;
restore controlfile to '/oracle/log/rman_log/%d_tmp.ctl' VALIDATE;
restore database validate;
restore archivelog all validate;
execute script release_2_disks;
execute script release_2_tapes;
}


Error stack
============
RMAN-03013: command type: IRESTORE
RMAN-06026: some targets not found - aborting restore
RMAN-06025: no backup of log thread 1 seq 2184 scn 3686066 found to restore
RMAN-06025: no backup of log thread 1 seq 2183 scn 3685322 found to restore
RMAN-06025: no backup of log thread 1 seq 2182 scn 3683848 found to restore

---

These archived logs should no longer be looked into for recovery as the Leve 0 is way newer than the archivelogs.

Hope this would shed some light to some, where I couldn't see any light anywhere. :0

Thanx,
Sam

what if you add

run { set until sequence XXX
blah blah
}

?

If you do a "crosscheck backup completed between date1 and date2", the status should change from "unavailable" to "expired". Then, issue a "delete expired backup.." - if that's what you are trying to do.

> "This was what I got after running delete expired backup."

What did you get? It doesn't appear in your post. The next para is simply a restore validate.

Axr2:

Quote:

---

> "This was what I got after running delete expired backup."

What did you get? It doesn't appear in your post. The next para is simply a restore validate.

---

It was in reference to the previous paragraph of list results.

Thanx,
Sam

> It was in reference to the previous paragraph of list results.

I still don't get it. Can you perform the following and paste the results?

1) Allocate channel for maintenance...
2) Crosscheck backup (or the specific piece)
3) list backup piece=...

I expect step 2 to change the status on that piece from "unavailable" to "expired".

4) delete expired backup ...

Error message.

It's quite possible that I may not have a solution at the end of this exercise. I'm only trying to help. In the interim - "help me help you".

With regard to deletion:

Here are some feedbacks to AXR2's suggestion:

Quote:

---

RMAN> allocate channel for maintenance type disk;

RMAN-03022: compiling command: allocate
RMAN-03023: executing command: allocate
RMAN-08030: allocated channel: delete
RMAN-08500: channel delete: sid=48 devtype=DISK

RMAN> crosscheck backup completed before '18-MAY-2004 00:00:00';

RMAN-03022: compiling command: XCHECK
RMAN-03023: executing command: XCHECK

RMAN> list backup device type 'disk';

RMAN-03022: compiling command: list
RMAN-03025: performing implicit partial resync of recovery catalog
RMAN-03023: executing command: partial resync
RMAN-08003: starting partial resync of recovery catalog
RMAN-08005: partial resync complete

List of Backup Sets
Key Recid Stamp LV Set Stamp Set Count Completion Time
------- ---------- ---------- -- ---------- ---------- ----------------------
509 1 526408349 0 526408338 2 17-MAY-2004 16:32:29

List of Backup Pieces
Key Pc# Cp# Status Completion Time Piece Name
------- --- --- ----------- ---------------------- ------------------------
536 1 1 UNAVAILABLE 17-MAY-2004 16:32:29 /backup/WEBTIME_bkup/df_WEBTIME_t526408338_s2_p1_lv0_disk

List of Datafiles Included
File Name LV Type Ckp SCN Ckp Time
---- ------------------------------------- -- ---- ---------- -------------
2 /db04/oradata/webtime/rbs.dbf 0 Full 3721716 17-MAY-2004 16:32:18
5 /db01/oradata/webtime/tools.dbf 0 Full 3721716 17-MAY-2004 16:32:18


RMAN> delete expired backup completed before '18-MAY-2004 00:00:00';

RMAN-03022: compiling command: delete expired
RMAN-03023: executing command: delete expired

RMAN> list backup device type 'disk';

RMAN-03022: compiling command: list

List of Backup Sets
Key Recid Stamp LV Set Stamp Set Count Completion Time
------- ---------- ---------- -- ---------- ---------- ----------------------
509 1 526408349 0 526408338 2 17-MAY-2004 16:32:29

List of Backup Pieces
Key Pc# Cp# Status Completion Time Piece Name
------- --- --- ----------- ---------------------- ------------------------
536 1 1 UNAVAILABLE 17-MAY-2004 16:32:29 /backup/WEBTIME_bkup/df_WEBTIME_t526408338_s2_p1_lv0_disk

List of Datafiles Included
File Name LV Type Ckp SCN Ckp Time
---- ------------------------------------- -- ---- ---------- -------------
2 /db04/oradata/webtime/rbs.dbf 0 Full 3721716 17-MAY-2004 16:32:18
5 /db01/oradata/webtime/tools.dbf 0 Full 3721716 17-MAY-2004 16:32:18

---

Coming to pando's thoughts: The reason why I did not put the set until clause was that I want to run this validation after every level 0 backup. The successful completion of this script would give some confidence the backups are at a restorable point without needing any manual intervention on the recovery scripts.

I'm more intersted in know what could be holding those log sequence information. This database gets incrementally backed on a daily basis and the archive backups three times a day.

Something I'm missing in this puzzle to get some sense of of.

Thanx.
Sam

Quote:

---

RMAN> delete expired backup completed before '18-MAY-2004 00:00:00';

---

how about a "change backuppiece '....' delete;"

Does that help get rid of it?

See Note 235973.1 on Metalink about the latter issue. Oracle says "ignore it".

Yes Axr2., I did see the metalink post. The reason I ignored it was that it states that this problem will occour only when you use the controlfile to backup. In my case I'm using the catalog. The backup piece deletion option does not work, because there is no backuppiece in the disk. So it would require you to run the crosscheck and then do delete expired backup. Which I tried and was not successful. At this juncture I have opened a Tar with oracle and lets see what they have to offer.

Thanx,
Sam

Just a thought, but are you familiar with the reset database; command in rman?

> At this juncture I have opened a Tar with oracle and lets see what they have to offer.

It'll be great if you could post their eventual response / solution.

Yes I'll do so

Thanx,
Sam

Hi All,

It is true that when your brains are fried, you cannot think clearly in depth. Here is the solution, for my problem.


1. Since I was lucky, I was able to retrive the disk backups from our usual daily backups on tape. For my luck, we backup the disk backup directory to tape.

2. Issued the following commands on those disk backup sets with the status unavailable on RMAN.

change backupset available;

3. On the OS moved the files to another directory

4. Ran the cross check, this expired the backups
crosscheck backup;
crosscheck backup of archivelog all;

5. Deleted the expired backups from catalog

delete expired backups;

6. Reran the validation. Bingo, its all working.

The validation completed successfully.

So, the solution in short is that when you have the backupset marked unavailable, RMAN seem to include those sets also in its validation process, and ademantly looks for those backups for the validation.

I concern is that its going to be an issue when you go for a longterm backup policy, where you take a full backup once a month and hold them on for a long time. The way Oracle suggest that you can achieve this on 8i is by marking those backupsets unavailable. In this can, it wouldn't work well looks like at this juncture. :)

I'm yet to hear from Oracle, why this is happening?

Anyhow, hope this would help you all.

Thanx.
Sam

Here is the test case.

Sam

Quote:

---

I concern is that its going to be an issue when you go for a longterm backup policy, where you take a full backup once a month and hold them on for a long time. The way Oracle suggest that you can achieve this on 8i is by marking those backupsets unavailable.

---

Where does Oracle suggest this? Is this actually documented somewhere?

This is completely bizzare that you had to restore the bs to mark it expired. I'm soon gonna be backing up an 8i target with 9i catalog with retentions of 7 years. Guess some serious testing needs to be done.

Thanks for the followup. Do post again with Oracle support's official response.

Here is a the extract from chapter 3 on RMAN user guide:

Quote:

---

The unavailable option provides for cases when a backup or copy cannot be found or has migrated offsite. A file that is marked UNAVAILABLE is not used in a restore or recover command. If the file is later found or returns to the main site, then you can mark it available again by using the available operand. Note that you do not need to allocate a channel of type maintenance for this operation.


--------------------------------------------------------------------------------
Note:
You must use a recovery catalog when executing change ... unavailable and change ... available. :D

--------------------------------------------------------------------------------

---

Also, in my past discussion with Oracle, I have heard this from a number of support personals of this solution. But the catch is what I have explained on my attachment earlier.

Thanx,
Sam