This blog has moved here.

Sunday, November 29, 2009

Strange RMAN snapshot controlfile issue

A strange thing happen today. I executed a delete obsolete command on my RMAN prompt and it reported the snapshot controlfile as obsolete. I don't know under which circumstances this problem occurs and I couldn't find any relevant information on forums or metalink (oh! sorry "my oracle support") about this.

Below is the output of the DELETE OBSOLETE command:
RMAN> delete obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
using channel ORA_DISK_1
using channel ORA_DISK_2
Deleting the following obsolete backups and copies:
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Control File Copy 36 29-11-2009 12:35:33 /u01/app/oracle/product/11.2.0/
dbhome_1/dbs/snapcf_tetris.f

Do you really want to delete the above objects (enter YES or NO)? y
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of delete command on ORA_DISK_2 channel at 11/29/2009 21:11:16
ORA-19606: Cannot copy or restore to snapshot control file


Indeed, this is the default configured snapshot controlfile:
RMAN> show snapshot controlfile name;               

RMAN configuration parameters for database with db_unique_name TETRIS are:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/
dbhome_1/dbs/snapcf_tetris.f';

It seems I'm in a kind of deadlock here. The snapshot controlfile is reported as obsolete but it can't be deleted as it is used by RMAN. The only solution I found was to change the RMAN configuration to use another snapshot controlfile, to remove then the reported obsolete one and to switch back to the default. However, the question remains: why the snapshot controlfile is reported as obsolete?

PS: This happend on a 11gR2 database installed under a Linux x86 platform.

Update: Apparently this is encountered after executing a DUPLICATE database from ACTIVE DATABASE. Furthermore, the snapshot controlfile is reported as a "datafile copy" when a CROSSCHECK is suggested. See below:
RMAN> delete obsolete;                                                                                                                                                                                           

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
using channel ORA_DISK_1
using channel ORA_DISK_2
Deleting the following obsolete backups and copies:
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Control File Copy 40 30-11-2009 18:41:15 /u01/app/oracle/product/11.2.0/dbhome_1
/dbs/snapcf_tetris.f

Do you really want to delete the above objects (enter YES or NO)? y

RMAN-06207: WARNING: 1 objects could not be deleted for DISK channel(s) due
RMAN-06208: to mismatched status. Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212: Object Type Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Datafile Copy /u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_tetris.f

Obviously, that can't be a datafile copy. So, let's try a crosscheck as suggested:
RMAN> crosscheck datafilecopy '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_tetris.f';                                                                                                                     

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=148 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=140 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of crosscheck command at 11/30/2009 19:09:43
RMAN-20230: datafile copy not found in the repository
RMAN-06015: error while looking up datafile copy name: /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/snapcf_tetris.f

Okey, this was expected as I don't have any datafilecopy with that name despite of what RMAN says. So, let's try a crosscheck for the controlfile copy:
RMAN> crosscheck controlfilecopy '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_tetris.f';                                                                                                                  

released channel: ORA_DISK_1
released channel: ORA_DISK_2
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=148 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=140 device type=DISK
validation failed for control file copy
control file copy file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_tetris.f
RECID=40 STAMP=704313675
Crosschecked 1 objects

As it can be seen the validation fails, although the file exists on that location:
$ ls -al /u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_tetris.f
-rw-r----- 1 oracle oinstall 10436608 Nov 30 18:57 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_tetris.f

I don't know if this is documented somewhere but it looks to me like a bug. No idea why the snapshot control file is messed up after a DUPLICATE TARGET DATABASE ... FROM ACTIVE DATABASE.

9 comments:

jobmiller said...

The way to clean it up is to change the configured location of the snapshot controlfile, delete obsolete, and than change it back.

see note 972539.1

Alexandru Tica said...

Thanks man for this info. Is good to know that, finally there are some references about this on metalink. At the time I was writing this post I couldn't find anything. However, the note doesn't mention why this happens, it just says how to fix the problem.

Sachin said...

I was also facing same issue on 11gR2 after database activation.

This post helped me to resolve it.

I still do not know, why this issue was there. No detail on Metalink.

Anyway, thanks a lot for your post.

Anonymous said...

Has anyone found out if this is a bug? I am also having this issue now. It is sporatic.

Oracle-Weekly said...

I used

RMAN> crosscheck copy of controlfile;

and then

RMAN> delete obsolete;


RMAN> delete obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 5 days
using channel ORA_DISK_1
Deleting the following obsolete backups and copies:
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Control File Copy 1 17-DEC-12 /mnt/oracle_backup/oracle/ORCL/rman/sncf_orcl

Do you really want to delete the above objects (enter YES or NO)? YES

RMAN-06207: WARNING: 1 objects could not be deleted for DISK channel(s) due
RMAN-06208: to mismatched status. Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212: Object Type Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Datafile Copy /mnt/oracle_backup/oracle/ORCL/rman/sncf_orcl

RMAN> list copy of controlfile;

List of Control File Copies
===========================

Key S Completion Time Ckp SCN Ckp Time
------- - --------------- ---------- ---------------
1 A 17-DEC-12 43120250 17-DEC-12
Name: /mnt/oracle_backup/oracle/ORCL/rman/sncf_orcl
Tag: TAG20121217T000142


RMAN> crosscheck copy of controlfile;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=501 device type=DISK
validation failed for control file copy
control file copy file name=/mnt/oracle_backup/oracle/ORCL/rman/sncf_orcl RECID=1 STAMP=802276973
Crosschecked 1 objects


RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 5 days
Report of obsolete backups and copies
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Control File Copy 1 17-DEC-12 /mnt/oracle_backup/oracle/ORCL/rman/sncf_orcl

RMAN> delete obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 5 days
using channel ORA_DISK_1
Deleting the following obsolete backups and copies:
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Control File Copy 1 17-DEC-12 /mnt/oracle_backup/oracle/ORCL/rman/sncf_orcl

Do you really want to delete the above objects (enter YES or NO)? YES
deleted control file copy
control file copy file name=/mnt/oracle_backup/oracle/ORCL/rman/sncf_orcl RECID=1 STAMP=802276973
Deleted 1 objects



DR Wrangler said...

fwiw http://dbwrangler.blogspot.co.uk/search?updated-min=2012-01-01T00:00:00-08:00&updated-max=2013-01-01T00:00:00-08:00&max-results=2

Though I have to say this worked under 11.2.0.2 and now fails under 11.2.0.4

Alexandru Tică said...

DR Wrangler,

Thanks! Good to know it's actually a nasty bug.

dvega said...

Where did you find the relationship with this bug and the DUPLICATE DATABASE .. FROM ACTIVE DATABASE?

Alexandru Tică said...

@dvega: it was just an assumption. Please see the comments section.