Oracle 9.2.0 - insufficient privileges (sysdba)

[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

[Omar Alvi_1]

Hi yogeeraj,

Which particular parameters are you asking for.


From my remote system, as oracle user I'm getting as I mentioned

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

As for your query.
select name,value from V$parameter where name like 'remote_login%';

NAME
----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
remote_login_passwordfile
NONE

-Alvi

[Yogeeraj_1]

hi alvi,

this is incorrect, change it from NONE to EXCLUSIVE

kind regards
yogeeraj

[Yogeeraj_1]

fyi.

NONE means "no remote sysdba's"
and you are trying to be a remote sysdba.

[Omar Alvi_1]

Thanks again,

But how do I change this parameter? New to SQL, Oracle and all

And btw, in my init.ora file I have the following entry.

remote_os_authent = true

Are they related?

-Alvi

[Yogeeraj_1]

hi again,

this is from the doc (old one i must confess)

3.2) Setting up the Init.Ora file
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To enable remote internal connections set the init.ora parameters thus:

7.1.x
~~~~~
Set REMOTE_LOGIN_PASSWORDFILE to either EXCLUSIVE or SHARED. EXCLUSIVE
forces the password file to be tied exclusively to a single instance. To
disable remote internal connections set REMOTE_LOGIN_PASSWORDFILE to NONE.

7.0.x
~~~~~
No init.ora changes are required for 7.0.x releases.


Note: The setting of REMOTE_OS_AUTHENT does NOT affect the ability to
connect internal from a remote machine.



Of course you must make changes to your spfile or accordingly

kind regards
yogeeraj

[Yogeeraj_1]

hi again,

more from the docs about the 3 different modes:


REMOTE_LOGIN_PASSWORDFILE specifies whether Oracle checks for a password file
and how many databases can use the password file.

NONE: Oracle ignores any password file. Therefore, privileged users must be
authenticated by the operating system.

EXCLUSIVE: The password file can be used by only one database and the password
file can contain names other than SYS and

INTERNAL. This setting is required for Oracle Parallel Server.

SHARED: More than one database can use a password file. However, the only users
recognized by the password file are SYS and INTERNAL.



The remote login password file allows remote "connect internals" to take place.
Since we cannot use OS authentication over the network to see of the person on
the other end of the connection is in the correct group -- we can use this to
password protect internal and have internal get prompted for a password.


kind regards
yogeeraj

[Yogeeraj_1]

hi again,

to change, depends on whether you are using the init.ora or the spfile.

for spfile change:
alter system set remote_login_passwordfile=EXCLUSIVE scope=spfile;

for pfile change:

just edit the file and add:
remote_login_passwordfile=EXCLUSIVE

hope this helps!

kind regards
yogeeraj

[Omar Alvi_1]

Hi yogeeraj,

I have pfile, but thats a directory.

So I should be editing /opt/oracle/admin/openview/pfile/init.ora

right?

I've edited this file but yet to successfully login from remote system

connect sys/manager@ov_remote as sysdba

Or do I need to restart oracle as well then?

-Alvi

[Yogeeraj_1]

hi again,

If you want OS authenticated users over a network, you must set "remote_os_authent=true".

This is a security hole in Unix.

We do NOT use them over the network in Unix as they are based on "trust" (just like the r* commands rsh, rexec)

In fact, the database assumes the authentication is verified by the parameters in the initSID.ora:
REMOTE_OS_AUTHENT and REMOTE_LOGIN_PASSWORDFILE.

hope this helps!

kind regards
yogeeraj

[Yogeeraj_1]

hi,

you will need to restart otherwise it will not work!

also see attached note.

kind regards
yogeeraj

[Yogeeraj_1]

note attached.

[Omar Alvi_1]

That means downtime

- Appreciate all your assistance, persistence, and patience.

Thnka you very much

Thread still open, may need fiurther assistance - many more points to assign even.

Thanks and regards,

-Alvi

[Xiaogang Zheng]

Alvi,

The following is your message

--- Begin ------------------------------------------------------------------------------------------
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

--- End ------------------------------------------------------------------------------------------

Please follow my troubleshooting steps:
Your situation mostly happens at a environment that has more than one instance. And your connected instance is not you want to connect.

On local machine;
1. SQL> connect system/manager as sysdba
Connected
2. SQL> select instance_name, host_name from v$instance;
3. SQL> select * from v$pwfile_users;

On remote machine;
1. tnsping ov_remote
2. Please verify the host and sid is the same as you get from local.


Please post your result after you follow my steps.

Xiaogang