Martins Blog

Trying to explain complex things in simple terms

ORA-02290: check constraint (RMAN.AL_C_STATUS) violated

Posted by Martin Bach on October 30, 2009

I ran into this problem today when I was working on a backup problem. The scenario is as follows:

  • Oracle 10.2.0.4 EE
  • RHEL 5.2 PAE kernel, i.e. 32bit
  • Backup taken on the standby database
  • Using a recovery catalog

All rman operations on the standby database with catalog database connection failed with the following error stack:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 10/30/2009 10:01:57
RMAN-03014: implicit resync of recovery catalog failed
RMAN-03009: failure of partial resync command on default channel at 10/30/2009 10:01:57
ORA-02290: check constraint (RMAN.AL_C_STATUS) violated

Ooops-so no backups, need to fix this quickly now. BTW, the same problem didn’t exist when running the same operation on the primary, but I don’t have space for the (disk) backup over there.

Underlying problem

The problem’s origin was quickly found: the standby database’s archived log destination has filled up, and one of the junior DBAs “moved” or deleted some of the files. The standby database’s control file assumes that a particular archived log is still there (but it isn’t!), which is causing the issue. The primary controlfile is fine. I dig some digging around and found metalink note 740801.1 which explains this as a bug which should have been fixed.

Some analysis on the constraint in the rman catalog shows the following definition of the constraint:

Owner: RMAN (not surprisingly)
Type: C (check constraint)
Table: AL
Condition: “status in (‘A’,’U’,’D’,’X’)”

The metalink note states that the query of V$ARCHIVED_LOG.STATUS (during the resync of the catalog, cf error message) can cause this problem. With the particular archvied log in a bit of a state (status “?”) this query fails, and the constraint is violated. The standby database query returns this result:

SQL> select distinct status from v$archived_log; 

S
-
?
A
D

The primary database doesn’t suffer the same problem:

SQL> select distinct status from v$archived_log; 

S
-
A
D

Now the workaround is to recreate the standby controlfile (which is proven to be good because of the  query earlier). That’s a fairly straight forward operation since the database is using a file system. A simple “ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘/path/to/controlfile.ctl’;  ” will create the job, then transfer it to the standby and put it into the correct place. Also, we use identical locations for primary and standby so no fancy “catalog start with ” action was necessary.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: