Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: What to do if a logon trigger get invalidated ?
In article <nlr9su8ef1fre0j8v1mnhpb5rtj2kaiajv_at_4ax.com>, Hans says...
>
>On 31 Oct 2002 10:08:53 -0800, Pete Sharman <peter.sharman_at_oracle.com>
>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
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.
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).
HTH. Additions and corrections welcome.
Pete
SELECT standard_disclaimer, witty_remark FROM company_requirements; Received on Sun Nov 03 2002 - 16:01:24 CST