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
