FAQ
It was my own fault, but in case it proves even slightly useful....

I was upgrading from 11202 to 11203 Enterprise using the DB Upgrade
Assistant utility. When I said "go do it" it went off, chugged for a
bit, then barfed. Told me that the database wasn't running. I checked,
it was.

Cutting a long story short, I checked the indicated logfile and
discovered that it had connected to the database but then got a couple
of errors telling it that "oracle not available". Hmm.

Turns out that I'd added a couple of SQL statements to glogin.sql:

alter session set nls_date_format = 'dd/mm/yyyy hh24:mi:ss';

Being one of them. This works perfectly while the database(s) are open
but, if a database is shut, and you login as sysdba to start it, you
still get glogin.sql executing, and because the database is down, the
above SQL fails.

DBUA picked up the failure and refused to carry on.

Removing the SQL statement(s) from glogin fixed the problem.

The moral to this tale is simple, don't put SQL in glogin.sql (or
login.sql) if you ever shut down your databases!


Cheers,
Norm.

--
Norman Dunbar
Dunbar IT Consultants Ltd

Registered address:
Thorpe House
61 Richardshaw Lane
Pudsey
West Yorkshire
United Kingdom
LS28 7EL

Company Number: 05132767
--
http://www.freelists.org/webpage/oracle-l

Search Discussions

  • Fergal Taheny at Jun 21, 2012 at 12:34 pm
    Hi Norman,
    I had the very same issue. I have a script that I use for cloning
    databases. Obviously it does a restart. Was surprised when it stopped
    working one day. Login.sql was the culprit so I now unset SQLPATH in the
    script before bouncing the db so it doesn't find login.sql. I guess if
    someone edits glogin.sql it could still catch me out.

    Fergal
    On 21 Jun 2012 09:43, "Norman Dunbar" wrote:

    It was my own fault, but in case it proves even slightly useful....

    I was upgrading from 11202 to 11203 Enterprise using the DB Upgrade
    Assistant utility. When I said "go do it" it went off, chugged for a
    bit, then barfed. Told me that the database wasn't running. I checked,
    it was.

    Cutting a long story short, I checked the indicated logfile and
    discovered that it had connected to the database but then got a couple
    of errors telling it that "oracle not available". Hmm.

    Turns out that I'd added a couple of SQL statements to glogin.sql:

    alter session set nls_date_format = 'dd/mm/yyyy hh24:mi:ss';

    Being one of them. This works perfectly while the database(s) are open
    but, if a database is shut, and you login as sysdba to start it, you
    still get glogin.sql executing, and because the database is down, the
    above SQL fails.

    DBUA picked up the failure and refused to carry on.

    Removing the SQL statement(s) from glogin fixed the problem.

    The moral to this tale is simple, don't put SQL in glogin.sql (or
    login.sql) if you ever shut down your databases!


    Cheers,
    Norm.

    --
    Norman Dunbar
    Dunbar IT Consultants Ltd

    Registered address:
    Thorpe House
    61 Richardshaw Lane
    Pudsey
    West Yorkshire
    United Kingdom
    LS28 7EL

    Company Number: 05132767
    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l
  • Michael Dinh at Jun 21, 2012 at 3:42 pm
    Thanks for sharing nice to know tibbits.

    Michael Dinh
    Disparity Breaks Automation (DBA)

    Confidence comes not from always being right but from not fearing to be wrong - Peter T Mcintyre
    Great minds discuss ideas; average minds discuss events; small minds discuss people - Eleanor Roosevelt
    When any rule or formula becomes a substitute for thought rather than an aid to thinking, it is dangerous and should be discarded -Thomas William Phelps

    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Norman Dunbar
    Sent: Thursday, June 21, 2012 1:44 AM
    To: oracle-l@freelists.org
    Subject: How to screw up DBUA inadvertently.

    It was my own fault, but in case it proves even slightly useful....

    I was upgrading from 11202 to 11203 Enterprise using the DB Upgrade
    Assistant utility. When I said "go do it" it went off, chugged for a
    bit, then barfed. Told me that the database wasn't running. I checked,
    it was.

    Cutting a long story short, I checked the indicated logfile and
    discovered that it had connected to the database but then got a couple
    of errors telling it that "oracle not available". Hmm.

    Turns out that I'd added a couple of SQL statements to glogin.sql:

    alter session set nls_date_format = 'dd/mm/yyyy hh24:mi:ss';

    Being one of them. This works perfectly while the database(s) are open
    but, if a database is shut, and you login as sysdba to start it, you
    still get glogin.sql executing, and because the database is down, the
    above SQL fails.

    DBUA picked up the failure and refused to carry on.

    Removing the SQL statement(s) from glogin fixed the problem.

    The moral to this tale is simple, don't put SQL in glogin.sql (or
    login.sql) if you ever shut down your databases!


    Cheers,
    Norm.

    --
    Norman Dunbar
    Dunbar IT Consultants Ltd

    Registered address:
    Thorpe House
    61 Richardshaw Lane
    Pudsey
    West Yorkshire
    United Kingdom
    LS28 7EL

    Company Number: 05132767
    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l
  • Rich Jesse at Jun 21, 2012 at 5:07 pm
    Norm!
    Turns out that I'd added a couple of SQL statements to glogin.sql:

    alter session set nls_date_format = 'dd/mm/yyyy hh24:mi:ss';

    Being one of them. This works perfectly while the database(s) are open
    but, if a database is shut, and you login as sysdba to start it, you
    still get glogin.sql executing, and because the database is down, the
    above SQL fails.

    DBUA picked up the failure and refused to carry on.
    Funny, but I'm having a similar problem with EM 12c throwing errors on my
    agents during the daily target discovery, supposedly because I have this in
    my glogin.sql:

    SET SQLPROMPT "SQL &_CONNECT_IDENTIFIER> "

    MOS 459545.1 says to query V$DATABASE to set the prompt properly, but that
    solution has tons of holes in it (e,g, DB not up, using "/nolog", connecting
    user doesn't have access to V$DATABASE).

    Hmmmm....


    Having tested DBUA going from 10.1.0.5.0 to 11.2.0.3.0 on AIX, I can say
    that my SQLPROMPT didn't cause it any issues -- that I know of! ;)

    Thanks Norm!

    Rich
  • Norman Dunbar at Jun 22, 2012 at 6:03 am
    Morning Rich,
    On 21/06/12 18:06, Rich Jesse wrote:
    ...
    Having tested DBUA going from 10.1.0.5.0 to 11.2.0.3.0 on AIX, I can say
    that my SQLPROMPT didn't cause it any issues -- that I know of! ;)
    It's fine with SET and SHOW and actual SQL*Plus commands. Just don't
    have anything that needs the database to be up and running to execute -
    so, basically, no SQL!

    Cheers,
    Norm.

    --
    Norman Dunbar
    Dunbar IT Consultants Ltd

    Registered address:
    Thorpe House
    61 Richardshaw Lane
    Pudsey
    West Yorkshire
    United Kingdom
    LS28 7EL

    Company Number: 05132767
    --
    http://www.freelists.org/webpage/oracle-l
  • Jared Still at Jun 27, 2012 at 3:51 pm

    On Thu, Jun 21, 2012 at 1:43 AM, Norman Dunbar wrote:
    The moral to this tale is simple, don't put SQL in glogin.sql (or
    login.sql) if you ever shut down your databases!
    In addition I would say make sure you always unset the SQLPATH environment
    variable before doing any kind of maintenance work.

    SQLPATH may have been set in login profile, or perhaps set by the DBA (you)
    while doing some other preparatory work.

    unset SQLPATH goes in most of my shell scripts that drive SQL.

    If a shell script is using sqlplus -S, and SQLPATH is set, it can be a
    little
    frustrating to see that a script is hanging for no apparent reason.

    Usually this would happen for the mentions you mention - SQL spawned
    by login.sql that doesn't work on a closed database.


    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist
    Oracle Blog: http://jkstill.blogspot.com
    Home Page: http://jaredstill.com

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJun 21, '12 at 8:44a
activeJun 27, '12 at 3:51p
posts6
users5
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase