Operating System - HP-UX
1748058 Members
5062 Online
108758 Solutions
New Discussion юеВ

Re: Oracle 9.2.0 - insufficient privileges (sysdba)

 
Omar Alvi_1
Super Advisor

Oracle 9.2.0 - insufficient privileges (sysdba)

Hi,

My environment is as follows

HP-UX 11.00 2-node cluster. Oracle 9.2.0 installed in the cluster.

Firstly, I must say, I'm a novice to dbs, as I only interact with them when the frontend has issues.

The front end here is data protector, and I'm integrating Oracle to take the backup - had issues there with the connect string - went to the OS and DB to investigate.

Apparently there's no svrmgrl now - I connect to sqlplus. But I cannot connect as sysdba using any of the users.

The following basically explains the scenario
________________________________________
#sqlplus

SQL*Plus: Release 9.2.0.2.0 - Production on Mon Feb 20 13:45:48 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter user-name: system
Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.2.0 - Production

SQL> connect system/manager@ov_remote
Connected.
SQL> connect system/manager@ov_remote as sysdba
ERROR:
ORA-01031: insufficient privileges


Warning: You are no longer connected to ORACLE.
SQL>
___________________________________________

So I'm unable to connect as sysdba. Thats what Data Protector wants to do as well. I have an oracle user, a sys user and this system user all with dba as their primary groups - but none of them connects with full privileges - why?

How can I change that?

On investigation I found people talking about the following

set the database parameter remote_login_passwordfile to
either "exclusive" or "shared"

Now, I don't have this parameter in my init.ora file. Only thing close to this is "remote_os_authent = true". Don't know whether the above parameter applies in my case or not.

Really appreciate any assistance.

Thanks and Regards,

-Alvi
42 REPLIES 42
paolo barila
Valued Contributor

Re: Oracle 9.2.0 - insufficient privileges (sysdba)

Hi,
try

in init.ora file

remote_login_passwordfile=EXCLUSIVE


then

# sqlplus /nolog

SQL> connect sys/@ov_remote as sysdba

share share share
Yogeeraj_1
Honored Contributor

Re: Oracle 9.2.0 - insufficient privileges (sysdba)

hi Alvi,

what was the password you used when you created the password file and have you tried that?

revert

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Omar Alvi_1
Super Advisor

Re: Oracle 9.2.0 - insufficient privileges (sysdba)

Hi yogee and paolo,

As for the variable, I would be willing to add once I know its effects, and whether it needs the DB to be reinitiated or what. Production environment this one is.

The password file you mean /etc/passwd - don't remember the first passwords when creating the user, but I suspect at least for system that "manager" is the default passwd.

Appreciate your swift responses.

-Alvi
Yogeeraj_1
Honored Contributor

Re: Oracle 9.2.0 - insufficient privileges (sysdba)

hi again Alvi,

the password file is created during the installation of the Oracle software.

you will need to alter the sys password so that they become in sync...

hope this helps!

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
paolo barila
Valued Contributor

Re: Oracle 9.2.0 - insufficient privileges (sysdba)

We mean the password specified with:

# orapwd file=orapw password=
share share share
Yogeeraj_1
Honored Contributor

Re: Oracle 9.2.0 - insufficient privileges (sysdba)

hi again,

i had a similar problem long time back. Let me see if i can provide you with a solution with an example.

i have to get back to my notes...

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: Oracle 9.2.0 - insufficient privileges (sysdba)

hi again,

this is how you create your password file:
$ orapwd file=orapw password=foobar entries=40

and when you alter the sys password this file is modified accordingly.... Most probably it has never been done at your site...

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Omar Alvi_1
Super Advisor

Re: Oracle 9.2.0 - insufficient privileges (sysdba)

Hi - paolo and yogeeraj again,

orapwd, never touched such a file - went in with the defaults. Actually even for scripts never needed to use the as sysdba privilege. Its just that the DP integration works with full privileges.

btw, I see that the other two users -members of dba-, oracle and sys can't connect to sql with even non sysdba priviledges
__________________________________
SQL> connect sys/manager@ov_remote
ERROR:
ORA-01017: invalid username/password; logon denied


SQL> connect oracle/oracle@ov_remote
ERROR:
ORA-01017: invalid username/password; logon denied


SQL> connect system/manager@ov_remote
Connected.
SQL>
_____________________________________

Again, really appreciate the assistance.

-Alvi
Yogeeraj_1
Honored Contributor

Re: Oracle 9.2.0 - insufficient privileges (sysdba)

Hi again,

Note that the sysdba trick is generally used to allow other NON-SYS users to connect. By the way, it is best to use your OWN account that has been granted sysdba!

Hope this is not confusing to you, anyway, this is how this works:

$ orapwd file=orapw password=foobar entries=40

Here, a password file with a password foobar has been created. Note that my sys password is NOT foobar.
=======================================
$ sqlplus /nolog

SQL> connect internal
Connected.

SQL> startup
ORACLE instance started.
Total System Global Area 1395673576 bytes
Fixed Size 104936 bytes
Variable Size 574263296 bytes
Database Buffers 819200000 bytes
Redo Buffers 2105344 bytes
Database mounted.
Database opened.

SQL> connect sys/change_on_install@ora817
Connected.
=======================================
that works OK.

Now, let us try as sysdba:

=======================================
SQL> connect sys/change_on_install@ora817 as sysdba;
ORA-01017: invalid username/password; logon denied

=======================================

This is the same error that you are getting! My SYS password is change_on_install but the password file has foobar in it. SYS is special -- sys connecting as sysdba is like internal -- you have to use the password file password!

Now, if we try this:
==========================================
SQL> connect sys/foobar@ora817 as sysdba;
Connected.

SQL> grant sysdba to scott;

Grant succeeded.

==========================================

You will observe that this does not hold true for other users:
==========================================
SQL> connect scott/tiger@ora817 as sysdba;
Connected.
==========================================

That worked great, and now if we just modify sys's password (not really, didn't change it but Oracle won't notice that)

==========================================
SQL> alter user sys identified by change_on_install;

User altered.


SQL> connect sys/change_on_install@ora817 as sysdba;
Connected.
SQL>
==========================================

You will observe now that we can use change_on_install -- that is because altering SYS's password will sync up the password in the password file -- sys is a very very close cousin of INTERNAL. sys's password will be burned into the password file now. The password foobar is now obsolete and no longer applicable.

==========================================
SQL> connect sys/foobar@ora817 as sysdba;
ORA-01017: invalid username/password; logon denied
SQL>

==========================================

hope this helps!

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)