Oracle 9.2.0 - insufficient privileges (sysdba)

[Raju Joseph (PDI)]

Hi Omar,

Login as Oracle software owner( ex.oracle)

/*
Do NOT use @ov_remote for this test
*/

$export ORACLE_SID=
$sqlplus /nolog
SQL>connect sys as sysdba
Enter password:

You should get :
Connected.
SQL>

Let me know whether you are getting SQL> prompt or another error.

Thanks
Raju

[Xiaogang Zheng]

Hi Alvi,

The oracle password file is located on $ORACLE_HOME/dbs as you know. The file can be deleted and recreated. The file contains all users that has sysdba and/or sysoper system role.

The important thing before you recreate the file is that you forget the password and no one remember the password for sysdba and sysoper.

From lots of previous articles, you can connect internal.
Please follow the statements.

SQL> connect internal
Connected.

SQL> show user
â ¦..
SQL> select * from v$pwfile_users;

You will see a user list that has sysdba or/and sysoper. If the userid system does not in the list. Grant the privilege to it.

SQL> grant sysdba to system.
â ¦.

Then select again. You will see system has the sysdba.

SQL> connect system/manager@connectstring as sysdba
Connected.

SQL> show user
Sys


I think it will help
Xiaogang

[TwoProc]

I see one problem, though it is a bit different than your question.

To connect to your remote open view database for rman backup - you should be connecting as the backup admin account that you created on that database, not as sys, system, etc. Ditto for the local database that you want to backup, you should be connecting as the installed backup user you created, not as sys, etc.

Since you are "hung" in trying to get "sysdba" access to a database that you don't need to get access for, your problem will go away.

Also, just as an fyi - the real user for the old "connect internal" (svrmgrl) is "sys as sysdba" - not system.

Also, another issue that you should be able to resolve: HP in their wisdom, has not elected to catch up to the rest of the world using Oracle - so their program is going to call "svrmgrl" for connection (regardless) - even though it doesn't exist. So, you'll have to create a symbolic link to "sqlplus" for it to work.

[Indira Aramandla]

Hi Omar,

You could query from v$parameter. Whome did you login here are to select from v4parameter.

Did you try to login as Oracle user and then set the ORACLE_SID. As Yogeeraj mentioned, do not use the @ symbol as you have set the ORACLE_SID, and then try to login and please let us know what is the outcome.


Indira A

[Omar Alvi_1]

Hi All,

Well, the login as sysdba without the @ option was successful. But what does that mean?

Here are the details.

______________________

SQL> connect system/manager as sysdba
Connected.
SQL> connect oracle/oracle as sysdba
Connected.
SQL> connect / as sysdba
Connected.
SQL> connect sys/manager as sysdba
Connected.
______________________
However, wiht @ov_remote there are two different messages for the two users

______________________

SQL> connect sys/manager@ov_remote as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied

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

And also tried the v$pwfile_user query

SQL> select * from v$pwfile_users;

no rows selected

Seem to be a multitude of steps closer to the final resolution - hopefully.

This thread is in for some big point aggregate.

Again, appreciate all the assistance

-Alvi

[Indira Aramandla]

Hi Omar,

When you set the variabl ORACLE_SID to point to the instance that you want to connect, then when sqlplus you need not specify the @SID.

If there is only one instance then set this as ORACLE_SID in the user (oracle's) .profile.

if there are multiple instances then set your ORACLE_SID appropriately and then logon.

If it is a remote login then you have to provide the connect string with the @SID.


Indira A

[Indira Aramandla]

Hi again,


You do not need to specify the username/password when connecting as SYSBDA or SYSOPER.

SQL> connect sys/manager@ov_remote as sysdba

SQL> connect system/manager@ov_remote as sysdba

All you need is to be loged on as an OS user belonging to the DBA group and set the ORACLE_SID to poin to the instance and login as

sqlplus /nolog
SQL>connect / as sysdba
SQL>connected

Now you are connected as SYSDBA. There is no requirement for system/manager or sys/manager username and password to be specified.


Indira A

[Yogeeraj_1]

hi,

Indira has explained you everything that should be. Can you confirm if you are able to connect from a remote client using:

connect sys/manager@ov_remote as sysdba

thanks
yogeeraj

[Omar Alvi_1]

Hi,

well, I'm treading on water here basically, with my knowledge of Oracle.

$ORACLE_SID=openview
and ov_remote is what I connect to usually

ov_remote is the one I can successfully tnsping.

Thats just a clarification in case I'm erring in something basic.

locally I can connect without using the @ov_remote as sysdba options.

Remotely I get the same errors as follows.

SQL> connect sys/manager@ov_remote as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied


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

and this is my tnsping remotely

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS_LIST= (ADDRESS = (PROTOCOL = IPC)(KEY =openview)) (ADDRESS = (COMMUNITY=OPENVIEW_COMMUNITY) (PROTOCOL=TCP) (HOST=nnmdw01) (PORT=1521))) (CONNECT_DATA= (SID=openview)))
OK (50 msec)

-Alvi

[Yogeeraj_1]

hi alvi,

what about the initialisation parameters?

You did not reply on the above query about the remote_login.. parameter!


can you post the output of the following statement:

select name,value from V$parameter where name like 'remote_login%' ;

9ir2 by default uses a stored parameter file (spfile). Perhaps you are not looking at the correct parameter file.

thanks
kind regards
yogeeraj