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.
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)
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.