Oracle 9.2.0 - insufficient privileges (sysdba)

[Omar Alvi_1]

Hi yogeeraj,

I'm following through, somewhat.

I might have many more queries, but I'll do with a few for now.

1- Where is the password file created?(which directory)

2- is it there by default or am I creating it the first time when using this orapwd command?

3- After using the orapwd command, do I have to restart oracle. "startup" for this to take effect.

change_on_install is the actual password, right?

Lets hope I can write about this in the forums successes for february - unless it needs downtime, then march maybe :)

4- can I follow your example on a working production environment without glitches? We have the Oracle DB for OV and NNM.

-Alvi

[Yogeeraj_1]

hi alvi,

1- Where is the password file created?(which directory)

The default path is: $ORACLE_HOME/dbs

2- is it there by default or am I creating it the first time when using this orapwd command?

better if you create it there itself!


3- After using the orapwd command, do I have to restart oracle. "startup" for this to take effect.

NO need

change_on_install is the actual password, right?

Yes. The default password in 8i. Am not sure about 9i. In 10g, you are allowed to change it during installation.

4- can I follow your example on a working production environment without glitches? We have the Oracle DB for OV and NNM.

This is safe but beware of any other custom scripts. First of all, verify that your sys password.


hope this helps!

kind regards
yogeeraj

[Yogeeraj_1]

hi again,

"In 10g, you are allowed to change it during installation."

sorry i should written:

"In Oracle 10g, you are allowed to specify your own password for system accounts. e.g. system, sys, etc. You can choose to have same password for all or different ones for each"


kind regards
yogeeraj

[Omar Alvi_1]

Hi,

Still unsucessful

Well I created the orapw file under $ORACLE_HOME/dbs as an oracle user.

Then I proceeded
_____________________________
$ 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.
________________________________

Now here, connect internal prompts for a password, doesn't accept any that I know of.

So i connect using my system/manager known successful password and user.

However, still the only user to successfully login is system, and even that without sysdba privileges.

The other sys user can't even get connected - used all password combinations. Don't know what next.

Regards,

-Alvi

[Indira Aramandla]

Hi Omar,

ORA-01031 insufficient privileges error occurs when Connect Internal has been issued with no password. For local connections the error occurs when the user is NOT in the DBA group as compiled into the 'oracle' executable. For remote connections you must always supply a password.

On UNIX the Oracle executable has two group names compiled into it, one for SYSOPER and one for SYSDBA. These are known as the OSOPER and OSDBA groups. Typically these can be set when the Oracle software is installed and are set in a file called either $ORACLE_HOME/rdbms/lib/config.c or config.s. The default group for OSOPER and OSDBA is generally set to 'dba'.

When you issue the command 'CONNECT / AS SYSOPER' Oracle checks if your Unix logon is a member of the 'OSOPER' group and if so allows you to connect. Similarly to connect as SYSDBA your Unix logon should be a member of the Unix 'OSDBA' group.

The OSDBA groups is the same group as has been historically used to allow CONNECT INTERNAL.

To enable remote internal connections set the init.ora parameters thus:
Set REMOTE_LOGIN_PASSWORDFILE to either EXCLUSIVE or SHARED.

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


Now you said that youd created the password file using orapwd. Did you successfully connect after the password file was created and then started the database. If so then you can alter the sys user password from the default change_on_install



IA

[Yogeeraj_1]

hi Alvi,

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.


revert

kind regards
yogeeraj

[Omar Alvi_1]

Hi Indira and Yogeeraj,

Well, I've been trying ...

The statement you asked me to run returns the following.

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

As for the connect internal option, it prompts for password, which then it presumably doesn't like.

SQL> connect internal
Enter password:
ERROR:
ORA-09275: CONNECT INTERNAL is not a valid DBA connection

-Alvi

[Yogeeraj_1]

hi again,

hmm..

the

ORA-09275: CONNECT INTERNAL is not a valid DBA connection

error. This is due to the desupport of the INTERNAL account in Oracle9i. For a while now Oracle have been recommending people stop using INTERNAL and start using the "connect user/password as sysdba" or "as sysoper" syntax. A person connected as "sysdba" has the same capabilities as someone connected as INTERNAL would have. So, connect internal is a feature that was removed in Oracle9i.

hope this clarifies everything.

kind regards
yogeeraj

[Omar Alvi_1]

thanks again to all,

I gather that if any one of my dba group users is able to connect as sysdba, I can grant the sysdba privilege to the others as well.

But getting that first user is proving elusive :(

-Alvi

[Yogeeraj_1]

hi alvi,

1. login as oracle user
2. sqlplus /nolog
3. connect / as sysdba

this part should work!

please confirm

thanks
yogeeraj