Do you know you get database failure each time when you create a new database with DBCA? 

Today, while testing troubleshooting Data Recovery Advisor (DRA) I saw an interesting issue. When I queried GV$IR_FAILURE view on the newly created database, I got some rows … This view is used and updated by DRA and Oracle background processes. When Oracle encounter an error such as data bock corruption, missing datafile or controlfile and etc. , it automatically updates the mentioned view. But how it’s possible to get an error on newly created database when no operation is performed.

Here’s the output from this view:

SQL> set linesize 150

SQL> col description format a35

SQL> col impacts format a30

SQL> select failure_id, to_char(time_detected,’dd.mm.yyyy hh24:mi:ss’) time_detected, modified, description, impacts, priority, status from GV$IR_FAILURE;

FAILURE_ID TIME_DETECTED       MODIFIED  DESCRIPTION                         IMPACTS                        PRIORITY STATUS

———- ——————- ——— ———————————– —————————— ——– ————

2 28.08.2012 14:44:57           Control file needs media recovery   Database cannot be opened      CRITICAL OPEN

And in one case I got fou 4 failures (one was child failure)

Just want to mention that if you want to know the source of the view “GV$IR_FAILURE”, query v$fixed_view_definition view:

SQL> col view_definition format a70

SQL>  select * from v$fixed_view_definition

where view_name=’GV$IR_FAILURE’;

VIEW_NAME                      VIEW_DEFINITION

—————————— ———————————————————————-

GV$IR_FAILURE                  select inst_id,       id,       pid,       cid_count,       clsname,

cast(ctime as date),       cast(mtime as date),       fdg_msg,

damage_msg,       decode(priority, 0, ‘CRITICAL’,

1, ‘HIGH’,                         2, ‘LOW’,

‘UNKNOWN’),       decode(status, 0, ‘OPEN’,

1, ‘CLOSED’,                       2, ‘UNDER-REPAIR’,

‘UNKNOWN’) from x$dbkfdg    where type = 1 and clsnameid = 2

and bitand(FLAGS,3) = 0

So the original table is – x$dbkfdg

Now let’s go back to our issue. After getting this error on GV$IR_FAILURE view, I logged in to RMAN and run LIST FAILURE command to get the list of failures. Although the failure is logged as “CRITICAL” and the status is “OPEN”, the command returned no result:

RMAN> list backup;

using target database control file instead of recovery catalog

specification does not match any backup in the repository

RMAN>

Then I checked detected time of the failure from the view – GV$IR_FAILURE:

SQL> select to_char(time_detected,’dd.mm.yyyy hh24:mi:ss’) time_detected from GV$IR_FAILURE;

TIME_DETECTED

——————-

28.08.2012 14:44:57

So this means that at the mentioned time we got a critical problem with control file. Then I checked the log file of the database creation for the same time period:

cd /u01/home/oracle/cfgtoollogs/dbca/testdb

vi trace.log

[Thread-55] [ 2012-08-28 14:44:10.243 AZST ] [CloneDBCreationStep.executeImpl:448]  createCTLSql=Create controlfile reuse set database “mydb”

[Thread-55] [ 2012-08-28 14:44:20.299 AZST ] [CloneDBCreationStep.executeImpl:470]  Shutdown database

[Thread-55] [ 2012-08-28 14:44:20.307 AZST ] [CloneDBCreationStep.executeImpl:492]  Startup ……nomount……

[Thread-55] [ 2012-08-28 14:44:49.702 AZST ] [CloneDBCreationStep.executeImpl:511]  Enabling restricted session.

[Thread-55] [ 2012-08-28 14:44:52.062 AZST ] [CloneDBCreationStep.executeImpl:513]  alter database “mydb” open resetlogs;

[Thread-55] [ 2012-08-28 14:45:07.405 AZST ] [CloneDBCreationStep.executeImpl:521]  Removing existing services from sourcedb seeddata

[Thread-55] [ 2012-08-28 14:45:07.733 AZST ] [CloneDBCreationStep.executeImpl:526]  Renaming globale_name

So the error came from “ALTER DATABASE “MYDB” OPEN RESETLOGS” command.

After running ADVISE FAILURE command and the querying the view again, you’ll see that the failure is “solved”, status is CLOSED and MODIFIED columns is updated to the current time:

SQL> select failure_id, to_char(time_detected,’dd.mm.yyyy hh24:mi:ss’) time_detected, to_char(modified,’dd.mm.yyyy hh24:mi:ss’) modified, description, impacts, priority, status from GV$IR_FAILURE;

FAILURE_ID TIME_DETECTED       MODIFIED            DESCRIPTION                         IMPACTS                        PRIORITY STATUS

———- ——————- ——————- ———————————– —————————— ——– ————

         2 28.08.2012 14:44:57 28.08.2012 16:27:06 Control file needs media recovery   Database cannot be opened      CRITICAL CLOSED