Kamran Agayev's Oracle Blog

Oracle Certified Master

You get CRITICAL failure each time you use DBCA to create a database

Posted by Kamran Agayev A. on August 28th, 2012

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

7 Responses to “You get CRITICAL failure each time you use DBCA to create a database”

  1. Mahir M. Quluzade Says:

    Kamran, very interesting, thanks for share!

  2. Nikolay Ivankin Says:

    To say the truth, DBCA doesn’t create DB, but restores with the help of RMAN :-)

  3. Kamran Agayev A. Says:

    Exactly Nikolay. With “create database” I mean when you check “Create database” option in dbca :)

  4. Log Buffer #286, A Carnival of the Vanities for DBAs | The Pythian Blog Says:

    [...] Do you know you get database failure each time when you create a new database with DBCA? Kamran asks. [...]

  5. Thiyagu , Gunasekaran Says:

    Hi Kamran;
    I created fresh DB through DBCA. then issued following query .. i got error ‘SP2-0552′ no idea abt that error.
    Could you please something say about that ??

    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;
    SP2-0552: Bind Variable “MI” not declared.

    Thanks
    Thiyagusham. G

  6. suvajit Says:

    please let me know how you define “Where is the error of ALTER DATABASE “MYDB” OPEN RESETLOGS”??

  7. Kamran Agayev A. Says:

    Rewrite the quotation marks and try again. After copy/paste, delete quotation marks ‘dd and ss’ and rewrite it again

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>