Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: What to do if a logon trigger get invalidated ?

Re: What to do if a logon trigger get invalidated ?

From: Pete Sharman <peter.sharman_at_oracle.com>
Date: 3 Nov 2002 14:01:24 -0800
Message-ID: <aq46bk080l@drn.newsguy.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US