topic Re: Oracle operation problem in Operating System - HP-UX

Oracle operation problem

KY.Chuang — Mon, 26 May 2003 04:47:27 GMT

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.

Re: Oracle operation problem

malay boy — Mon, 26 May 2003 04:59:17 GMT

This is plug off from metalink.See the attachment.

Re: Oracle operation problem

Massimo Bianchi — Mon, 26 May 2003 05:43:44 GMT

Hi,
did you do:
grant sysdba to system;

from sqlplus/svrmgrl ?
Massimo

Re: Oracle operation problem

Yogeeraj_1 — Mon, 26 May 2003 05:46:11 GMT

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

Re: Oracle operation problem

T G Manikandan — Mon, 26 May 2003 05:47:13 GMT

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

Re: Oracle operation problem

malay boy — Mon, 26 May 2003 05:47:37 GMT

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

Re: Oracle operation problem

T G Manikandan — Mon, 26 May 2003 05:55:15 GMT

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

Re: Oracle operation problem

Yogeeraj_1 — Mon, 26 May 2003 06:11:55 GMT

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

Re: Oracle operation problem

Andreas D. Skjervold — Mon, 26 May 2003 09:10:52 GMT

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

Re: Oracle operation problem

Massimo Bianchi — Mon, 26 May 2003 09:17:34 GMT

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

Re: Oracle operation problem

T G Manikandan — Mon, 26 May 2003 09:18:54 GMT

Andreas,

GReat to see you again after a long time.

Welcome back.
Expecting great answers as usual.

Thanks

Re: Oracle operation problem

Andreas D. Skjervold — Mon, 26 May 2003 11:32:41 GMT

Hi TG

Nice to have been missed! Been kinda busy lately.

rgds
Andreas

Re: Oracle operation problem

KY.Chuang — Tue, 27 May 2003 00:49:22 GMT

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?

Re: Oracle operation problem

Yogeeraj_1 — Tue, 27 May 2003 03:28:21 GMT

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

Re: Oracle operation problem

Andreas D. Skjervold — Tue, 27 May 2003 06:11:26 GMT

Hi again Ky

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

rgds
Andreas

Re: Oracle operation problem

Massimo Bianchi — Tue, 27 May 2003 06:40:20 GMT

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