cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle operation problem

KY.Chuang
Advisor

Oracle operation problem

I have a Oracle operation problem and my oracle version is 8i.
When I use Oracle Database Administrattion Tool-Oracle DBA Studio to connect and manage Database.
Login Oracle Service with system connect as [SYSDBA]... But It give me a error [ORA-01031 insufficient privileges].
But, when I connect as [SYSOPER] it successed.
Does It have differents in
[SYSDBA] and [SYSOPER]?
And which setting make I can't
connect as [SYSDBA].

Thanks.




Service is King
15 REPLIES
malay boy
Trusted Contributor

Re: Oracle operation problem

This is plug off from metalink.See the attachment.

There are three person in my team-Me ,myself and I.
Massimo Bianchi
Honored Contributor

Re: Oracle operation problem

Hi,
did you do:
grant sysdba to system;

from sqlplus/svrmgrl ?
Massimo
Yogeeraj_1
Honored Contributor

Re: Oracle operation problem

hi,

SYSOPER privilege allows operations such as:
Instance startup, mount & database open ;
Instance shutdown, dismount & database close ;
Alter database BACKUP, ARCHIVE LOG, and RECOVER.

This privilege allows the user to perform basic operational tasks without the ability to look at user data.


SYSDBA privilege includes all SYSOPER privileges plus full system privileges
(with the ADMIN option), plus 'CREATE DATABASE' etc..

This is effectively the same set of privileges available when previously connected INTERNAL.



also note that:
SYS is special account. But sys is not good enough. You need INTERNAL or SYSDBA to do certain operations. I would recommend strongly against using SYS to do things. SYS cannot create triggers, SYS cannot have read only transactions, and so on. SYS should not be used.

System is just an account, nothing special about it. We use it to store various things.

Internal is a super special account. It is a SYSDBA/SYSOPER. It can startup/shutdown the database. You should avoid using it as it will be going away at some point in the future. The correct approach is to "connect user/password AS sysdba" to perform these operations.


hope this helps!

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
T G Manikandan
Honored Contributor

Re: Oracle operation problem

This is because the OS authentication is done here and the OS user from where you are doing a

SQL>connect system/test@test as sysdba

will give a 1031 error.

Add that OS user to the dba group on the system. It should work then.

revert on the same


Thanks
malay boy
Trusted Contributor

Re: Oracle operation problem

What are the minimal privileges/roles required for DBA Studio?
--------------------------------------------------------------
The minimum privileges or roles required for the DBA Studio are as follows
In Oracle version 8.1.7: connect + select_catalog_role
In Oracle version 9.0.1: connect + select any dictionary
There are three person in my team-Me ,myself and I.
T G Manikandan
Honored Contributor

Re: Oracle operation problem

ALso note that again after adding the user to the 'dba' group still 1031 error occurs.

This is because dba should be the primary group of the user.

What you can do is

#newgrp dba
#sqlplus system/@test
sql>connect / as sysdba



Revert
Yogeeraj_1
Honored Contributor

Re: Oracle operation problem

hi again,

Note that in the example above (by TG), the password used is the one when you created the password file.

$ orapwd file=orapw password=yog entries=40

I just recreated my password file with a password yog. My sys password is NOT yog.


$ svrmgrl

SVRMGR> connect internal
Connected.

SVRMGR> connect sys/change_on_install@ora817dev
Connected.

that works OK, lets try as sysdba:

SVRMGR> connect sys/change_on_install@ora817dev as sysdba;
ORA-01017: invalid username/password; logon denied

there I got your error. My SYS password is change_on_install but the password file has yog in it. SYS is special -- sys connecting as sysdba is like internal -- you have to use the password file password!

Lets try that:

SVRMGR> connect sys/yog@ora817dev as sysdba;
Connected.


the sysdba trick is generally used to allow other NON-SYS users to connect. BTW, it is recommended that you use your OWN account that has been granted sysdba!


SVRMGR> grant sysdba to scott;
Statement processed.

SVRMGR> connect scott/tiger@ora817dev as sysdba;
Connected.

Similarly, you can setup your own environment.

hope this helps too

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Andreas D. Skjervold
Honored Contributor

Re: Oracle operation problem

Hi

To use the orapw file for external "internal" logins (as sysdba) you must have the following in your init.ora file:
remote_login_passwordfile = exclusive

rgds
Andreas
Only by ignoring what everyone think is important, can you be aware of what everyone ignores!
Massimo Bianchi
Honored Contributor

Re: Oracle operation problem

If you change the remote_login_passwordfile to exclusize remeber also to create the password file with the orapw, as stated upper !

Otherwise your db won't start..
Massimo
T G Manikandan
Honored Contributor

Re: Oracle operation problem

Andreas,

GReat to see you again after a long time.

Welcome back.
Expecting great answers as usual.


Thanks
Andreas D. Skjervold
Honored Contributor

Re: Oracle operation problem

Hi TG

Nice to have been missed! Been kinda busy lately.

rgds
Andreas
Only by ignoring what everyone think is important, can you be aware of what everyone ignores!
KY.Chuang
Advisor

Re: Oracle operation problem

Dear all,
I create the orapw file with orapw.
And also add
(remote_login_passwordfile = exclusive) to init.ora file.

When I login SYSDBA with sys/, It fail.(ORA-01017 invalid username/passowrd) which I execute Oracle DBA Studio in Remote.
But I login oracle with svrmgrl(connect sys/ as sysdba) in locale, It could.
Why?

Service is King
Yogeeraj_1
Honored Contributor

Re: Oracle operation problem

hi,

quote from above:


$ svrmgrl

SVRMGR> connect internal
Connected.

SVRMGR> connect sys/change_on_install@ora817dev
Connected.

that works OK, lets try as sysdba:

SVRMGR> connect sys/change_on_install@ora817dev as sysdba;
ORA-01017: invalid username/password; logon denied

there I got your error. My SYS password is change_on_install but the password file has yog in it. SYS is special -- sys connecting as sysdba is like internal -- you have to use the password file password!

Lets try that:

SVRMGR> connect sys/yog@ora817dev as sysdba;
Connected.


the sysdba trick is generally used to allow other NON-SYS users to connect. BTW, it is recommended that you use your OWN account that has been granted sysdba!




most probably, you are not connecting as sysdba in the DBA studio, hence the error!

i suggest, you connect using the svrmgrl and change the sys password using:

alter user sys identified by ;

Again, avoid using the sys account and create your own as i mentioned in my previous post.

Best Regards
Yogeeraj

No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Andreas D. Skjervold
Honored Contributor

Re: Oracle operation problem

Hi again Ky

Perhaps silly, but remember to bounce your database after updating the init.ora file.

rgds
Andreas
Only by ignoring what everyone think is important, can you be aware of what everyone ignores!
Massimo Bianchi
Honored Contributor

Re: Oracle operation problem

Hi,
there is also another point:
AFTER you created the password file you have to grant the sysdba and sysoper role to the user.


Yuo define one user in the file (user=) and his/her password (password=), after that you will have to re-grant the permission to that user.

svrmgrl> grant sysoper, sysdba to XXXXX ;
svrmgrl> commit;

HTH,
Massimo