TheBonsai's Blog

About the days and nights of TheBonsai

Five hours, ORACLE, dbca, ASM and a customized SQL*Plus prompt…

January 31st, 2009 by TheBonsai

Yesterday was a bad day. I wanted to completely rebuild a database with a corrupt data dictionary. My plan:

  • Backup/exports
  • Shutdown DB, remove userspace files
  • Cleanup files inside ASM
  • DBCA to make new one (no scripts available)

You see, nothing unusual. So far so good, everything fine except the last step. The scripts I had from the first installation didn’t run through, so I just wanted to click something together. But DBCA spit out:

DBCA could not startup the ASM instance configured on this node. To proceed with database creation using ASM you need the aSM instance to be up and running. Do you want to recreate the ASM instance on this node?

Er.. what? ASM instance is up and running and some minutes before the old database ran fine with it! So what? Metalink, Google: Environment variables, Listener configuration etc etc etc. Nothing applied. The only thing I really knew 100% was that the ASM setup was rocksolid.

DBCA failed in the following cases in my experiments (I removed and re-added ASM instance):

  • it detected offline ASM and failed trying to start it
  • it detected online ASM and failed
  • it detected no ASM, successfully created one, and failed to add disks etc..

Long story short: Every combination of DBCA and ASM actions failed.

After several hours we got the solution. I was about to give up and manually rewrote another DB installation script to use it for this DB environment. The last hours we turned around nearly every bit of that installation, we nearly did everything except reinstall the software. We even rebooted the server ;). My collegue was hopeless, too. But we both were fanatic to find the “why” – it was one of these things where workarounds may work, but don’t satisfy the admins.

I hacked a glogin.sql startup file several weeks ago, that displays a nice prompt with instance and username and some timing statistics when you use SQL*Plus. This is something fine and useful for me (and in general).

But we (infact my collegue) found out:

DBCA seems to communicate with the ASM instance using SQL*Plus. My changed output (infact only the prompt and the time statistics) made the SQL*Plus text parsers of DBCA going crazy. Without one word in the right direction in any log of DBCA. DBCA log always said it found the ASM disks and listed them (in the log). So, it was a simple text parsing issue! The developers of DBCA or the JAVA-classes that manage the communication seem to assume that nobody personaziles his SQL*Plus. Only bad software makes such assumptions 😉

Now I know why ORACLE recommends to use different ORACLE_HOMEs. I didn’t try it, but I can imagine that the SQL*Plus of the ORACLE_HOME from the ASM instance is used when different.

The evening in numbers:

  • applies to ORACLE software 10.2.0.4, most likely others, too
  • happened on SLES10 SP2
  • may be documented somewhere, but I didn’t find it on min. 200 pages I read on the Internet
  • gave us min. 500 million new grey hairs
  • took us 5 hours

I really begin to love ORACLE :) At least life isn’t boring…

This entry was posted on Saturday, January 31st, 2009 at 13:30 and is filed under english, Linux, Oracle. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

2 responses about “Five hours, ORACLE, dbca, ASM and a customized SQL*Plus prompt…”

  1. Usn said:

    Hi Jan, could you share your settings for glogin.sql/login.sql that caused the error, please? Would be interesting to test.

    Thanks
    Usn

  2. TheBonsai said:

    It should be something like this (modified glogin.sql from distribution):

    prompt Reading glogin.sql startupfile…



    set linesize 1000;
    set pagesize 100;
    set timing on;
    set time on;
    set sqlprompt “&&_USER@&&_CONNECT_IDENTIFIER SQL> “

Leave a Reply