What to do if a logon trigger get invalidated ?
Hans Erik Busk
trigger can create a single point of failure that can bring down the
whole database for good.
If the trigger becomes invalidated (a table it relies on becomes
corrupted for instance), it is not possible for anyone to log on, not
even sys as sysdba !
In effect this prevents any rescue operations.
Or have I overlooked something ?
Oracle 9.2 on W2K
p.s. Not an acute problem, it was a test database
Hans Erik Busk
Pete Sharman
You should ALWAYS be able to log on as SYSDBA to fix this. Can you post the
results of trying to connect as SYSDBA with an invalid logon trigger?
HTH. Additions and corrections welcome.
Pete
SELECT standard_disclaimer, witty_remark FROM company_requirements;
Anton Buijs
1. On the server itself do connect internal (in 9.2: sqlplus / as sysdba).
Disable the trigger.
2. Set the (Oracle internal so undocumented) init.ora parameter
_system_trig_enabled = FALSE, stop/start the database and then disable the
trigger. Restart again with the parameter removed.
Hans Erik Busk <t...@cn.stam.dk> schreef in berichtnieuws
hg32su8adhreu8tq0...@4ax.com...
srivenu
regards
Srivenu
Steve Ashmore
granted
to it , the trigger will not fire . The DBA role has it. (8.1.7 and
probably 9i)
Stephen C. Ashmore
Brainbench MVP for Oracle Administration
http://www.brainbench.com
Author of: 'So You Want to be an Oracle DBA?'
"Anton Buijs" <aamm...@xs4all.nl> wrote in message
news:3dc17a59$0$46617$e4fe...@news.xs4all.nl...
Mark D Powell
Pete is right as I have had this happen. I unset TWO_TASK and logged
on to sqlplus using /nolog then connected / as sysdba. I then issued
"alter trigger owner.trigger_name disable;" command to allow
connections. After disabling it I recompiled the trigger: "alter
trigger owner.trigger_name compile;" and followed this with an "alter
trigger owner.trigger_name enable;" and everything was fine.
There should be no need to bounce the db as another user suggested.
HTH -- Mark D Powell --
Anton Buijs
because the first must work.
Anton Buijs <aamm...@xs4all.nl> schreef in berichtnieuws
3dc17a59$0$46617$e4fe...@news.xs4all.nl...
Hans Erik Busk
wrote:
>You should ALWAYS be able to log on as SYSDBA to fix this. Can you post the
>results of trying to connect as SYSDBA with an invalid logon trigger?
>
I might have a problem as the "connect / as sysdba" does not work,
butwithout the logon trigger I can "connect sys/password as sysdba"
and also startup and shutdown the database
I have the "remote_login_passwordfile=SHARED" and
is a member of the ORA_DBA group
C:\WINNT>sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Sat Nov 2 18:29:36 2002
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect / as sysdba
ERROR:
ORA-01031: insufficient privileges
SQL> connect sys/password as sysdba
Connected to an idle instance.
SQL> startup nomount pfile="d:\oracle\ADMIN\Mads9\pfile\init.ora";
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown
ORA-24324: service handle not initialized
ORA-24323: value not allowed
ORA-03113: end-of-file on communication channel
SQL> startup nomount pfile="d:\oracle\ADMIN\Mads9\pfile\init.ora";
ORA-01031: insufficient privileges
Hans Erik Busk
Hans Erik Busk
<aamm...@xs4all.nl> wrote:
>Next works in V8.1.7 (Unix), can't say it for V9.2.
>1. On the server itself do connect internal (in 9.2: sqlplus / as sysdba).
>Disable the trigger.
>2. Set the (Oracle internal so undocumented) init.ora parameter
>_system_trig_enabled = FALSE, stop/start the database and then disable the
>trigger. Restart again with the parameter removed.
>
1. does not work, (see earlier post)
2. saves the database.
Thank you
Hans Erik Busk
Pete Sharman
invalid logon trigger. The connect / as sysdba command fails because
REMOTE_LOGIN_PASSWORDFILE is SHARED. That means (and I quote from the doc)
"More than one database can use a password file. However, the only user
recognized by the password file is SYS." IN other words, you can only connect
as SYS using SYSDBA. Reset the parameter to EXCLUSIVE and the connection should
work.
The connect sys/password as sysdba is running into a different problem. THe
database's knickers seem to be in a complete twist, resulting in a lost
connection (ORA-03113) so the next startup command also fails because you're no
longer connected to Oracle.
I'm pretty sure that changing the REMOTW_LOGIN_PASSWORDFILE parameter to
EXCLUSIVE would allow you to login with the invalid logon trigger, but the whole
point's moot anyway since you seemed to get in from another posting with the
necessary underscore parameter. This is not something I would recommend (using
underscore parameters to get around problems there are more supported ways of
fixing, I mean).
Anton Buijs
dramatic: you also need to set it when patching a database from 8.1.7.0 to
8.1.7.3 according to the patch installation instructions of Oracle...
I normally would not have adviced such a parameter.
Pete Sharman <peter....@oracle.com> schreef in berichtnieuws
aq46b...@drn.newsguy.com...
Hans Erik Busk
wrote:
>What you have here is a combination of errors that have nothing to do with an
>invalid logon trigger. The connect / as sysdba command fails because
>REMOTE_LOGIN_PASSWORDFILE is SHARED. That means (and I quote from the doc)
>"More than one database can use a password file. However, the only user
>recognized by the password file is SYS." IN other words, you can only connect
>as SYS using SYSDBA. Reset the parameter to EXCLUSIVE and the connection should
>work.
Thank you for your response, I have however a few clarifying comments:
I have also tried "REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE" with the same
result (i.e. insufficient priv, when logging in as / as sydba).
The interesting question is if there is any difference in privileges
between the two:
"connect / as sysdba" or
"connect sys/password as sysdba"
Oh the good old times with svrmgrl and connect internal ! ;-)
>The connect sys/password as sysdba is running into a different problem. THe
>database's knickers seem to be in a complete twist, resulting in a lost
>connection (ORA-03113) so the next startup command also fails because you're no
>longer connected to Oracle.
What i didn't point out was the odd message i get when
"connect sys/pw as sydba"
"Connected to an idle instance"
because the instance is certainly not idle, another user logged on
before the trigger became invalid is still connected and able among
other things to disable the trigger.
>necessary underscore parameter. This is not something I would recommend (using
>underscore parameters to get around problems there are more supported ways of
>fixing, I mean).
I agree completely, but at least this could be a way of salvage in the
rather unlikely event of the trigger becoming invalid.
Hans Erik Busk
Pete Sharman
>
>On 3 Nov 2002 14:01:24 -0800, Pete Sharman <peter....@oracle.com>
>wrote:
>
>>What you have here is a combination of errors that have nothing to do with an
>>invalid logon trigger. The connect / as sysdba command fails because
>>REMOTE_LOGIN_PASSWORDFILE is SHARED. That means (and I quote from the doc)
>>"More than one database can use a password file. However, the only user
>>recognized by the password file is SYS." IN other words, you can only connect
>>as SYS using SYSDBA. Reset the parameter to EXCLUSIVE and the connection should
>>work.
>
>Thank you for your response, I have however a few clarifying comments:
>
>I have also tried "REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE" with the same
>result (i.e. insufficient priv, when logging in as / as sydba).
This is probably pointing to the fact that you're using OS authentication
without the necessary OS privileges. For example, on Unix you need to be in the
DBA group (by default, anyway - that can be changed) to have connect / as sysdba
work. On Windows, you need to be either in the ORA_DBA or ORA_<sid>_DBA group.
>
>The interesting question is if there is any difference in privileges
>between the two:
>"connect / as sysdba" or
>"connect sys/password as sysdba"
If they're set up so they work, about the only difference is that one requires
more typing. :)
>
>Oh the good old times with svrmgrl and connect internal ! ;-)
No, the good old days were SQL*DBA, or even better the ior command! ;)
>
>>The connect sys/password as sysdba is running into a different problem. THe
>>database's knickers seem to be in a complete twist, resulting in a lost
>>connection (ORA-03113) so the next startup command also fails because you're no
>>longer connected to Oracle.
>
>What i didn't point out was the odd message i get when
>
>"connect sys/pw as sydba"
> "Connected to an idle instance"
>
>because the instance is certainly not idle, another user logged on
>before the trigger became invalid is still connected and able among
>other things to disable the trigger.
>
Yup, saw that but passed over it because some of the earlier issues we were
discussing could have caused that.
>
>>necessary underscore parameter. This is not something I would recommend (using
>>underscore parameters to get around problems there are more supported ways of
>>fixing, I mean).
>
>I agree completely, but at least this could be a way of salvage in the
>rather unlikely event of the trigger becoming invalid.
True!
Pete
>
>Hans Erik Busk
Pete Sharman
>
>In this particular case, the underscore parameter mentioned is not so
>dramatic: you also need to set it when patching a database from 8.1.7.0 to
>8.1.7.3 according to the patch installation instructions of Oracle...
>I normally would not have adviced such a parameter.
Agreed, but it can be quite difficult to determine someone's level of knowledge
from a ng post at times, so I always remind people (basically so they can cover
their own you know what).
Pete
Hans Erik Busk
wrote:
>>I have also tried "REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE" with the same
>>result (i.e. insufficient priv, when logging in as / as sydba).
>
>This is probably pointing to the fact that you're using OS authentication
>without the necessary OS privileges. For example, on Unix you need to be in the
>DBA group (by default, anyway - that can be changed) to have connect / as sysdba
>work. On Windows, you need to be either in the ORA_DBA or ORA_<sid>_DBA group.
Oh - but I am member of ORA_DBA group.
Yesterday I tried with "REMOTE_LOGIN_PASSWORDFILE=NONE", but when
logging on (sitting at the server of course) I got
"insufficient privileges" error
Haven't had that problem with 8.1.6 or earlier versions.
>No, the good old days were SQL*DBA, or even better the ior command! ;)
Well I have only been along since 7.3
I guess I can live with this, but it is annoying when things doesn't
react the way I thought they should.
I will probably do some testing on a "clean" computer to figure out
what is happening - when (if) i get time for it.
Hans Erik Busk