Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, M.

Asked: May 18, 2001 - 8:00 pm UTC

Last updated: November 05, 2012 - 9:33 am UTC

Version: 8.1.7

Viewed 100K+ times! This question is

You Asked

Hi there,

I am running Oracle 8.1.7 EE on SUN Solaris. I created two databases
SUGAR and TestDb (I need one to do some testing). Both are set remote_login_passwordfile = exclusive. The following shows how I had trouble to connect as SYSDBA. The big problem is that I cannot connect to TestDb as SYSDBA.

$sqlplus /nolog
SQL> conn sys/change_on_install@testdb
connected
SQL> select * from v$pwfile_users;
username sysdb sysop
------------------------------
internal true true
sys true true
SQL> conn sys/change_on_install@testdb as sysdba
ERROR
ORA-01017: invalid username/password; logon denied
Warning: You are on longer connected to ORACLE
SQL> conn sys/change_on_install@SUGAR
connected
SQL> select * from v$pwfile_users;
username sysdb sysop
------------------------------
internal true true
sys true true
SQL> conn sys/change_on_install@SUGAR as sysdba
ERROR
ORA-01017: invalid username/password; logon denied
Warning: You are on longer connected to ORACLE
SQL> conn sys/change_on_install as sysdba
connected
SQL> select name from v$database;
name
------
SUGAR

Questions:
1. Why "conn sys/change_on_install@testdb/sugar as sysdba" always failed, but "conn sys/change_on_install as sysdba" OK ?

2. Why I cannot connect to testdb as SYSDBA?

Thanks for any help.



and Tom said...

what was the password you used when you created the password file and have you tried that?

the sysdba trick is generally used to allow other NON-SYS users to connect btw, best to use your OWN account that has been granted sysdba!

Anyway, hopeful this will clear up the confusion and show how this works:


$ orapwd file=orapw password=foobar entries=40

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


$ svrmgrl

SVRMGR> connect internal
Connected.

SVRMGR> startup
ORACLE instance started.
Total System Global Area 193073136 bytes
Fixed Size 69616 bytes
Variable Size 141639680 bytes
Database Buffers 45056000 bytes
Redo Buffers 6307840 bytes
Database mounted.
Database opened.

SVRMGR> connect sys/change_on_install@ora816dev
Connected.

that works OK, lets try as sysdba:

SVRMGR> connect sys/change_on_install@ora816dev 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 foobar 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/foobar@ora816dev as sysdba;
Connected.

SVRMGR> grant sysdba to scott;
Statement processed.

now, we'll see this does not hold true for other users:

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

That worked great, now lets just modify sys's password (not really, didn't change it but Oracle won't notice that

SVRMGR> alter user sys identified by change_on_install;
Statement processed.


SVRMGR> connect sys/change_on_install@ora816dev as sysdba;
Connected.
SVRMGR>

Hey, now we can use change_on_install -- that is because altering SYS's password will sync up the password in the password file -- sys is a very very close cousin of INTERNAL. sys's password will be burned into the password file now. the password foobar is obsolete


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




Rating

  (103 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Pauline, May 19, 2001 - 10:44 pm UTC


Tushar Abedin, May 21, 2001 - 1:00 am UTC


Great ins & Outs

Mark Kirk, May 21, 2001 - 1:12 am UTC

Thansk for going through this from several aspects. Most discussions of the sysdba question elsewhere are too brief and directive without being explanatory.

Here you've given theory, script, explanation and added examples of how someone might get twisted up.

Top Flight!

Very Useful

Bj, May 21, 2001 - 6:05 pm UTC


Password

Anil, May 22, 2001 - 4:48 am UTC

Very good explanation

Anil, May 22, 2001 - 4:52 am UTC


Sofie Ye, July 27, 2001 - 12:12 am UTC


Alter user will *not* sync up password in file?

Randy, August 06, 2001 - 4:32 pm UTC

"...-- that is because altering SYS's
password will not sync up the password in the password file..." Did you mean to say it *will* sync up the password file?



But a bit confusing ...at the end ..

Jay, October 07, 2001 - 11:29 pm UTC

start quote //.."-- that is because altering SYS's
password will not sync up the password in the password file "

sys's password will be burned into the password
file now. ....//end quote

A bit of contradiction ...Mr.Tom ??

Tom Kyte
October 08, 2001 - 8:05 am UTC

funny how the addition of a SINGLE word can change the entire meaning ;)

Obviously it was "... altering SYS's password will sync up ..." -- thats what I was demonstrating. I corrected the text, thanks for pointing that out.

Useful Info

Yogesh Bhandarkar, October 09, 2001 - 12:09 am UTC

The information is very useful.

differences?

Benny, October 10, 2001 - 4:24 pm UTC

You mentioned that sys is a very close cousin of internal,
what are the differences?

Tom Kyte
October 10, 2001 - 6:37 pm UTC

some permissions, like the ability to shutdown, startup.

Problem to connect as SYSDBA

Johnson, October 11, 2001 - 5:01 am UTC

The discussion was good. It would be of greater use, if you can tell us more about the role sysdba.

Thanks in advance.

Tom Kyte
October 11, 2001 - 7:08 am UTC

Thanks alot!

Thunderbird, February 01, 2002 - 6:33 am UTC

Your answer was very usefull for me. Thus I solve my trouble.

Junaid Azam, August 20, 2002 - 6:59 pm UTC


password forgotten

reader, April 23, 2003 - 1:48 am UTC

tom,

i dont have the password for sys,system. how to recreate the password or to know the password.

please help

Thanks

Tom Kyte
April 23, 2003 - 7:18 am UTC

have the person who is able to log in

"internal"

"as sysdba"

log in and alter these users. those accounts use OS authentication to id the user and do not require a password.

or, if you have access to any dba account, use that and just alter the users.

Problem to connect as SYSDBA

reader, April 23, 2003 - 9:16 am UTC

tom,
there is no other user with dba privileges. its running on Win2000(9iR2).

to connect as "internal" i need 'svrmgrl' - i dont have that in 9iR2 - right?

for "as sysdba" - i dont have any dba user.

Sorry for my ignorance. please help me

Thanks


Tom Kyte
April 23, 2003 - 7:04 pm UTC

as sysdba -- you don't need a DBA user, you just need an OS account (eg: try the account that installed oracle)

then

sqlplus "/ as sysdba"



trying to change (lost) sys password

A reader, June 30, 2003 - 10:53 am UTC

This is a cut and paste of what I have tried.  Oracle 9.2.0 on Linux

[oracle@db admin]$ whoami
oracle
[oracle@db admin]$ pwd
/opt/oracle/product/9.2.0/network/admin
[oracle@db admin]$ cat sqlnet.ora

NAMES.DEFAULT_DOMAIN = [blah]
NAMES.DIRECTORY_PATH= (TNSNAMES)
SQLNET.AUTHENTICATION_SERVICES=(NTS)

[oracle@db admin]$ sqlplus / as sysdba
Usage: SQLPLUS [ [<option>] [<logon>] [<start>] ]
where <option> ::= -H | -V | [ [-L] [-M <o>] [-R <n>] [-S] ]
     <logon>  ::= <username>[/<password>][@<connect_string>] | / | /NOLOG
     <start>  ::= @<URI>|<filename>[.<ext>] [<parameter> ...]
       "-H" displays the SQL*Plus version banner and usage syntax
       "-V" displays the SQL*Plus version banner
       "-L" attempts log on just once
       "-M <o>" uses HTML markup options <o>
       "-R <n>" uses restricted mode <n>
       "-S" uses silent mode

[oracle@db admin]$ sqlplus /nolog

SQL> connect / as sysdba
ERROR:
ORA-01031: insufficient privileges

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


SQL>


Can you point me in the right direction.

 

Tom Kyte
June 30, 2003 - 11:15 am UTC

lose the windoze

SQLNET.AUTHENTICATION_SERVICES=(NTS)

in the sqlnet.ora



Great!

Moiz Arviwala, June 30, 2003 - 11:20 pm UTC

Tom , This Kind of anwer I want too....Are u a magician!!!!


Regards,

Unable to remote connect as sysdba

Deepak Gupta, September 13, 2003 - 7:28 am UTC

Hi Mr. Tom,

I am trying to connect as remote sysdba from my client PC.  The following error is occuring. why so ? 

SQL> select name,value from V$parameter
  2  where name like 'remote%'
  3  /

NAME                                                             VALUE
---------------------------------------------------------------- ------------------
remote_os_authent                                                FALSE
remote_os_roles                                                  FALSE
remote_login_passwordfile                                        EXCLUSIVE
remote_dependencies_mode                                         TIMESTAMP

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
INTERNAL                       TRUE  TRUE
SYS                            TRUE  TRUE
NS                             TRUE  TRUE




SQL> conn sys@orcl
Enter password: *******
Connected.
SQL> conn sys@orcl as sysdba
Enter password: *******
ERROR: ORA-12154: TNS:could not resolve service name


Warning: You are no longer connected to ORACLE.
SQL> conn internal@orcl
Enter password: ******
Connected.
SQL> conn internal@orcl as sysdba
Enter password: ******
ERROR: ORA-12154: TNS:could not resolve service name


Warning: You are no longer connected to ORACLE.
SQL> 

Regards + Thanks 

 

Tom Kyte
September 13, 2003 - 10:06 am UTC

sigh, no versions...  must be 8i though if you are trying internal

sys@ORA920> connect ops$tkyte@ora920 as sysdba
Enter password:
Connected.

scott@ORA817DEV> connect ops$tkyte@ora817dev as sysdba
Enter password:
Connected.
scott@ORA817DEV>


but I cannot reproduce in any case. 

RE: sysdba

Mark A. Williams, September 13, 2003 - 11:31 pm UTC

Are you possibly using 8.0.4 to attempt to connect as sysdba?

From a Metalink posting I found:

"Connection as SYSDBA can be made only using oracle8i - i.e ver 8.1.5 or above on all platforms. However, there is problem with the error message displayed. 8.0.4 gives ORA-12154: TNS: could not resolve service name which is misleading."

Not sure if that is your situation, but it seems close anyway.

- Mark

Thanks a lot!!

Deepak Gupta, September 15, 2003 - 1:11 am UTC

Mr. Mark Thanks a lot !! for valuable information

deletion of password file ,

Deepak Gupta, September 15, 2003 - 1:12 am UTC

Hi Mr. Tom,

I am using Oracle8 Release 8.0.4.0.0 - Production.

after deleted my password file , I am not able to connect as internal ( Invalid username/password )

NAME VALUE
------------------------------------ ---------------------
remote_login_passwordfile EXCLUSIVE

I create again password file to get connect internal to starup my db ( internal take same password what had put in password file )

why so ?

Is there any default password for internal ?

if I do not want use password file then what to do ( to connect internal )?

Thanks + Regards



Tom Kyte
September 15, 2003 - 9:39 am UTC


if you log into the proper OS account on the same machine, you can do connect internal (or "as sysdba") without it.

on unix, the DBA group (or whatever group you specified) for example can connect internal locally without a password.

It's not giving error for me.Why ?

Ashiq Shamsudeen A, September 15, 2003 - 10:04 am UTC

Hi Tom,

db--> oracle817 and OS --> W2k professional

As you mentioned I've created password file and I got connected as sys with SYSDBA .It haven't given me a error.See below

scott@ASHIQ> host orapwd file=orapw password=foobar entries=10

scott@ASHIQ> conn sys/change_on_install@ashiq
Connected.
scott@ASHIQ>
scott@ASHIQ>
scott@ASHIQ> conn sys/change_on_install@ashiq as sysdba
Connected

Why its not giving error ?


Tom Kyte
September 15, 2003 - 2:26 pm UTC

the password file isn't just orapw

</code> http://docs.oracle.com/docs/cd/A87860_01/doc/win.817/a73008/ch6.htm#1028152 <code>

describes the name and where this file goes.

Not able to connect as internal without password file!!!

Deepak Gupta, September 18, 2003 - 6:28 am UTC

Hi Mr. Tom,

Database --->Oracle8 Release 8.0.4.0.0 -
O/S ---> Windows NT 4.0

I connected as Administrator ( build-in NT account, it all privilege) on NT Oracle server machine (locally) and at command propmt
(if I removed password file )

M:\orant>svrmgr30
svrmgr> connect internal

I got following error

"invalid username/password : login denied "


Thanks + regards






Tom Kyte
September 18, 2003 - 10:21 am UTC



sorry, don't have 8.0 anywhere.
don't have windows on any computers...

but it seems easy to solve -- create the password file?

log in as the guy who installed oracle - they should be in the right "group"

any user can connect as sysdba!

A reader, November 17, 2003 - 3:46 pm UTC

Hi Tom,

First, I worship you. Thank you for this website and being so cool. I have all your books, read your website and pray that some day I can meet you and say many thanks. Now, on to my question.

I have read and read and read your explanations on internal, os authentication, sysdba, sysoper priviledges. I think I understand them.

On my Oracle 8.1.7 environment, I am able to login as sysdba when I have not been granted the sysdba priviledge!

e.g. connect foo@someREMOTEmachine as sysdba, asks for the password and logs me in as sysdba. once in, I can see any table owned by sys! Yikes!

I have checked that I am not in the password file (V$passwd_users). I am also NOT logging into "someREMOTEmachine" locally. Can you please explain why this would be so.

I have a feeling, that this is a REALLY stupid question. If so, apologies in advance.

Tom Kyte
November 21, 2003 - 7:29 am UTC

go talk to your OS admin, they've put everyone in the "power user group"

also, whats in your sqlnet.ora

any user can connect as sysdba

A reader, November 17, 2003 - 4:04 pm UTC

Hi Tom,

Sorry, but in the previous post I forgot to mention that the OS is Windows 2K. Waiting for your reply. Thanks.




any user can connect as sysdba

A reader, November 18, 2003 - 11:47 am UTC

Hi Tom,

Sorry to keep posting on your site. But I think I have figured out the answer to the question I posted (anyone can login as sysdba).

Ofcourse, only the people who have the sysdba priviledge should be able to login as sysdba. I verfied this by looking at the password file contents v$pwfile_users to see only internal and sys.

Also checked the session_privs table to find only a handful of priviledges (not the 100+ for sysdba).

Just wanted to keep you and anyone else who gets confused like I did, posted. Thanks and God bless.

Randall Richardson, March 01, 2004 - 12:47 pm UTC

Thanks Tom..

One follow-up question.

We are in a Windows cluster env and I have the spfile along with all admin\ORACLE_SID\ type stuff on the cluster drive. The ORACLE_HOME has to be on a local drive.

Can I change the location where the password file from ORACLE_HOME\database to a different directory like I do with the spfile?


Thanks again.




Tom Kyte
March 01, 2004 - 1:42 pm UTC

not that I'm aware of -- each instance needs it's own.

how are you sharing an spfile amongst instances?

Randall RIchardson, March 01, 2004 - 2:25 pm UTC

Its a Failsafe cluster, Database is Active/Passive

Each node has a text file "init<SID>.ora" in the Local "ORACLE_HOME\database" directory that point to the shared drive init file.

------ initPROD.ora ------------
spfile=G:\oracle\admin\PROD\spfile\PROD.ORA
--------------------------------

I just wanted to try and make sys password changes on the cluster a little more stream lined. Oh well..

Thanks..

Connect as sysdba doesn't work

Michael, March 16, 2004 - 6:59 am UTC

Hi Tom!

I have the following problem with the "connect as sysdba":

Only service and listener on NT is started (with: oradim -startup -sid ora81db -starttype srvc).

Then in SQL*Plus:

E:\Apps\oracle\ora81\bin>set ORACLE_SID=ora81db

E:\Apps\oracle\ora81\bin>set ORACLE_HOME=E:\Apps\oracle\ora81

E:\Apps\oracle\ora81\bin>sqlplus /nolog

SQL*Plus: Release 8.1.7.0.0 - Production on Di Mõr 16 12:49:32 2004

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

SQL> conn sys as sysdba
Kennwort eingeben: (The correct password from the orapwd tool)
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

SQL> conn internal
Bei einer nicht hochgefahrenen Instanz angemeldet.

SQL> startup
ORACLE-Instanz hochgefahren.

Total System Global Area   99227676 bytes
Fixed Size                    75804 bytes
Variable Size              82296832 bytes
Database Buffers           16777216 bytes
Redo Buffers                  77824 bytes
Datenbank mit MOUNT angeschlossen.
Datenbank ge÷ffnet.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
INTERNAL                       TRUE  TRUE
SYS                            TRUE  TRUE

SQL> conn sys as sysdba
Kennwort eingeben: (Same password as above)
Connect durchgef³hrt.
SQL> shutdown immediate
ORA-01031: Unzureichende Berechtigungen
SQL> conn sys as sysoper
Kennwort eingeben:
Connect durchgef³hrt.
SQL> shutdown immediate
ORA-01031: Unzureichende Berechtigungen
SQL> conn internal
Connect durchgef³hrt.
SQL> show user
USER ist "SYS"
SQL> shutdown immediate
Datenbank geschlossen.
Datenbank abgehõngt.
ORACLE-Instanz heruntergefahren.

I don't know what's wrong. Any idea?
 

Tom Kyte
March 16, 2004 - 9:29 am UTC

the

connect sys as sysdba

isn't "working" right -- it is just connecting as "sys", not "sys as sysdba", the "as sysdba" is getting lost by sqlplus


you can use "connect / as sysdba" in sqlplus or use svrmgrl to

connect sys as sydba





iSQLPlus

Sikandar Hayat, March 16, 2004 - 11:37 am UTC

I am unable to connect as sysdba from the isqlplus window. I am using Oracle 9i R2 on Windows box. Please guide me how to configure it to login as sysdba.

Tom Kyte
March 16, 2004 - 1:40 pm UTC

did you read and follow all of the needed setup in the sqlplus guide?

(i would not recommend using sysdba like this, sysdba is pretty darn powerful, I'd pretty much limit it to real DBA tools -- if even there (i use it in sqlplus command line and no where else myself)

Interesting ...

Michael, March 18, 2004 - 2:15 am UTC

Hi Tom!

With the SVRMGR the "connect as sysdba" worked immediatly. Thanks!

Then i tried in SQL*Plus the following:
(The user MIKE has the password "mike" and is sysdba)

E:\Apps\oracle\ora81\bin>sqlplus /nolog

SQL*Plus: Release 8.1.7.0.0 - Production on Do Mõr 18 08:07:29 2

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

SQL> conn sys as sysdba
Kennwort eingeben:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist


SQL> conn sys as sysdba;
Kennwort eingeben:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist


SQL> conn sys/sys as sysdba
Bei einer nicht hochgefahrenen Instanz angemeldet.
SQL> conn mike as sysdba
Kennwort eingeben: (i entered "mike")
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist


Achtung: Sie sind nicht mehr mit ORACLE verbunden.
SQL> conn mike/mike as sysdba
Bei einer nicht hochgefahrenen Instanz angemeldet.
SQL> startup
ORACLE-Instanz hochgefahren.

Total System Global Area   99227676 bytes
Fixed Size                    75804 bytes
Variable Size              82296832 bytes
Database Buffers           16777216 bytes
Redo Buffers                  77824 bytes
Datenbank mit MOUNT angeschlossen.
Datenbank ge÷ffnet.
SQL> conn mike as sysdba
Kennwort eingeben: (i entered "mike")
Connect durchgef³hrt.
SQL> shutdown immediate
ORA-01031: Unzureichende Berechtigungen
SQL> conn mike/mike as sysdba
Connect durchgef³hrt.
SQL> shutdown immediate
Datenbank geschlossen.
Datenbank abgehõngt.
ORACLE-Instanz heruntergefahren.

It looks like, if you use the syntax user/password, then SQL*Plus pays attention to the "as sysdba". Otherwise (only username) it is ignored.

IMHO, i would call this behaviour of SQL*Plus a bug. (It seems to be fixed in 9i :-). 

Great

S Devarshi, June 04, 2004 - 6:41 am UTC

Hello Tom
I have a tablw called weekly,with 350 columns.
I add around 500 rows every week to this table. After I returned from a holiday I am getting the error Ora-00600
,argument [ktsiseg-info1] . Is there a way to get over this?
All other table in the tablespace are working fine.
I Use 8.0.4.0.0 on unix platform(sco7.1).

Devarshi

Tom Kyte
June 04, 2004 - 8:35 am UTC

ora-600 == please contact support. insufficient data to even begin to look at this (like, what causes the error, a query, all queries, creating an index, dropping an index, an insert, an update, all inserts, some deletes, etc)

support will collect the information it needs to make a diagnoses.

similar problem - but your solution doesn't work for me

Stefan, July 05, 2004 - 1:43 pm UTC

Hi Tom,

I seem to have a similar problem (but I am using 9i R2): I can

connect sys/*** as sysdba

where: env | grep ORA
ORACLE_SID=mvartp01
ORACLE_BASE=/opt/app/oracle
ORACLE_HOME=/opt/app/oracle/product/9.2.0.5

but:

connect sys/***@mvartp01 as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied

I can connect as a normal user either way:

connect sms/***
connect sms/***@mvartp01

both work.

I tried your suggestion:

orapwd file=orapw password=foobar entries=40

Now I can do

connect sys/foobar as sysdba

but

connect sys/foobar@mvartp01 as sysdba

still fails. I am not sure whether the orapwd command worked - I just found, I can also connect with:

connect sys/invalid_pw as sysdba

I tried to set a new password:

alter user sys identified by new_pw;

but still get:
connect sys/new_pw@mvartp01 as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied


I found references to remote_login_passwordfile and checked:

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

NAME
----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
remote_login_passwordfile
SHARED

Now - that is strange, because:
cd /opt/app/oracle

find . -name init\*ora -exec grep remote_login_passwordfile {} \; -print
remote_login_passwordfile = EXCLUSIVE
./product/9.2.0.5/dbs/initmvartp01.ora
remote_login_passwordfile=EXCLUSIVE
./admin/mvartp01/pfile/init.ora

Might that be the problem? And - how do I fix it?

Thanks,
Stefan 

Tom Kyte
July 05, 2004 - 2:02 pm UTC

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

that and mvartp01 could be *anything* in your tnsnames.ora file, I'm not really sure what database it might be pointing to at all.

Great Resource

David Wang, July 20, 2004 - 12:03 am UTC

Tom,

I got a question about connect as sysdba. I created password files in both 9.2.0.3 and 10.1.0.2 databases with entries=1. I was able to add more than 1 users as sysdba. It was verified from v$pwfile_users and test connect sys@service_name as sysdba command. Could you tell me why it happen? Thanks,



Tom Kyte
July 20, 2004 - 8:48 am UTC

entries are made in multiples of 4 if I recall correctly, i tested it once before.

entries=1 or 2 or 3 or 4 are "the same" as are 5,6,7,8 and so on.

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:2318668671089 <code>



RE: orapwd file and entries

Mark A. Williams, July 20, 2004 - 10:58 am UTC

The documentation has this to say:

[quote]
ENTRIES
This parameter specifies the number of entries that you require the password file to accept. This number corresponds to the number of distinct users allowed to connect to the database as SYSDBA or SYSOPER. The actual number of allowable entries can be higher than the number of users, because the ORAPWD utility continues to assign password entries until an operating system block is filled. For example, if your operating system block size is 512 bytes, it holds four password entries. The number of password entries allocated is always a multiple of four.
[end quote]

So it makes sense... This was taken from the "Overview of Administering an Oracle Database" currently located here: </code> http://download-west.oracle.com/docs/cd/B13789_01/server.101/b10739/dba.htm#sthref104

- Mark

=======================================
Mark A. Williams
Oracle DBA
Author, Professional .NET Oracle Programming
http://www.apress.com/book/bookDisplay.html?bID=378 <code>

Using "as sysdba" within a Unix shell script

Marc Paquin, July 20, 2004 - 1:10 pm UTC

Hello Tom,

Within a Unix shell script I start a sqlplus session to a remote server using the following syntax:

#-----------------------------------------------------------
#!/usr/bin/ksh

sqlplus -s 'user/password@connect_string as sysdba' <<EOF
select user from dual;
EOF
#-----------------------------------------------------------

This works fine

Now if I try to use shell variables as follow
#-----------------------------------------------------------
#!/usr/bin/ksh
PWD=password
USER_ID=user
CONNECT=connect_string

sqlplus -s ${USER}/${PWD}@${CONNECT} as sysdba <<EOF
select user from dual;
EOF
#-----------------------------------------------------------

The sqlplus session will fail and display the Usage
The problem seems to be with the missing forward tics
I tried the same test with:

sqlplus -s '${USER}/${PWD}@${CONNECT} as sysdba'
Now the unix variable are not substituted

I tried escaping the forward tics
sqlplus -s \'${USER}/${PWD}@${CONNECT} as sysdba\'
I get an invalid character message

I tried putting the complete connect string (with tics)within one variable
#-----------------------------------------------------------
#!/usr/bin/ksh

FULL_CONNECT_STRING="'use/password@connect_string as sysdba'"

sqlplus -s $FULL_CONNECT_STRING <<EOF
select user from dual;
EOF
#-----------------------------------------------------------

This will also fail and display sqlplus usage

Tom,

Question:
How would you resolve this issue?

Thanks for your help



Tom Kyte
July 20, 2004 - 8:39 pm UTC

by no means a "ksh" expert -- but this works:



#!/usr/bin/ksh -vx
PWD=oracle
USER_ID=sys
CONNECT=ora9ir2

sqlplus -s /nolog <<EOF
connect ${USER_ID}/${PWD}@${CONNECT} as sysdba
select user from dual;
EOF


Trying to create DB : ORA-12547: TNS:lost contact

Sami, August 08, 2004 - 7:47 pm UTC

Dear Tom,

I amusing Redhat AS 3.0 and Oracle 10.1.0

What is wrong here. I amtrying to create DB in command line. It is not connect to idle instance.


[oracle@linux1 dbs]$ set |grep ORA
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/10.1.0/db_1
ORACLE_SID=ORCL1
ORACLE_TERM=xterm
ORA_NLS33=/u01/app/oracle/product/10.1.0/db_1/ocommon/nls/admin/data

[oracle@linux1 dbs]$ id
uid=700(oracle) gid=701(oinstall) groups=701(oinstall),700(dba)
[oracle@linux1 dbs]$ sqlplus /nolog
 
SQL*Plus: Release 10.1.0.2.0 - Production on Sun Aug 8 19:33:05 2004
 
Copyright (c) 1982, 2004, Oracle.  All rights reserved.
 
SQL> connect / as sysdba
ERROR:
ORA-12547: TNS:lost contact
 
 
SQL> exit
[oracle@linux1 dbs]$ uname -r
2.4.21-15.ELorafw1
[oracle@linux1 dbs]$

 

Tom Kyte
August 09, 2004 - 7:22 am UTC

does this install "work at all" -- meaning, did the sample databases work?

looks like the install may have failed? that the oracle binary isn't "right". if you just run $ORACLE_HOME/bin/oracle -- do you see any error messages? did the logs from the install look clean?

Oracle Advanced Security

Robert Hanrahan, August 10, 2004 - 11:15 am UTC

hi All,

Oracle 8.1.7.4.0 - Tru64 UNIX 5.1a

After configuring sqlnet.ora for OAS this way:

Server side sqlnet.ora:

sqlnet.crypto_checksum_types_server= (md5)
sqlnet.authentication_services= (kerberos5)
sqlnet.crypto_checksum_server = accepted
sqlnet.encryption_types_server = (DES)
sqlnet.crypto_seed = "4fhfguweotcadsfdsafjkdsfqp5f201p45mxskdlfdasf"
sqlnet.encryption_server = accepted

Client side sqlnet.ora:

# - Oracle Advanced Security Network Security -------------------------

sqlnet.authentication_services = kerberos5
sqlnet.crypto_checksum_client = required

# Data checksumming using the MD5 algorithm
sqlnet.crypto_checksum_types_client = (MD5,SHA1)

# Data Encryption Standard - algorithm used for encryption
sqlnet.encryption_types_client = (DES)
sqlnet.crypto_seed = "i5rrruweotcadsfdsafjkdsfqp5f201p45mxskdlfdasf"
sqlnet.encryption_client = required

# to start automatic tracing to see if encryption works
# trace_level_client=16

-------------------------------------------------------

At this point encryption worked, but I couldn't do
sqlplus internal anymore

I had to change this parameter on the server side sqlnet.ora:

sqlnet.authentication_services= (beq, tcps, kerberos5, nts)

and I got no more errors with "sqlplus internal"
and svrmgrl ( with connect internal )

cheers

rjh

sqlnet.ora

Robert James Hanrahan, August 20, 2004 - 10:04 am UTC

hi all,

I solve the problem...

I edited the sqlnet.ora this way:

-----------------------------------------------------------------------

sqlnet.crypto_checksum_types_server= (md5)
sqlnet.authentication_services= (beq, tcps, kerberos5, nts)
sqlnet.crypto_checksum_server = accepted
sqlnet.encryption_types_server = (DES)
sqlnet.crypto_seed = "4fhfguweotcadsfdsafjkdsfqp5f201p45mxskdlfdasf"
sqlnet.encryption_server = accepted

-----------------------------------------------------------------------

The problem arised because of the setting of the parameter to just kerberos.

sqlnet.authentication_services=kerberos5

bye

Nelson Alvarez, September 21, 2004 - 9:31 pm UTC


Oracle Consultant

Dawar, November 01, 2004 - 3:36 pm UTC

Tom,

More than two months ago I have installed Oracle 10.1.0.2.0. on my linux server.
It is running ok.

Recently I have installed html_db on the same box and got some configuation errors and not able to run ./opmnctl startall.

while I am trying to fix that problem I realised.

I do not have following directory
"/ocommon" under (existing DB) $ORACLE_HOME

But in my /etc/profile.d/oracle.sh
I have following environment variable set.

ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data

Please note: In Html_DB directory /ocommom is exiting under $ORACLE_HOME.

Any thought.

Regards,
Dawar



Tom Kyte
November 02, 2004 - 6:36 am UTC

contact support for installation/configuration issues.

solution of "as sysdba" problem found satisfactory

Rishikesh Madake, November 20, 2004 - 1:39 am UTC

I found the conversations (problem solving by tom) really useful . I thank you for that.

solution of "as sysdba" very useful indeed

Rishikesh Madake, November 20, 2004 - 1:42 am UTC

I read the solution given by TOM by writing the scripts and appropriate error messages. I really liked the way it is written. It exposes the problem and its solution exactly. Thank you for that

EXCEL TO ORACLE

Rishikesh, November 27, 2004 - 7:01 am UTC

Tom please tell me how to transfer the data from Excel spreadsheet into oracle.
the procedure i followed is as follows , igot the following errors:

I have a table called "stud" with fields
1.rollno number(9)
2.name char(10)

PROCEDURE:

1. Excel file(with data in it ..3 records) saved as .csv file
2. control file ldr.ctl contains this :-

LOAD DATA
infile 'c:\stud.csv'
replace into table stud
fields terminated by ','
optionally enclosed by '"'
TRAILING NULLCOLS
(rollno , name )

3. from command prompt I gave following command

C:\>sqlldr scott/tiger control=c:\ldr.ctl

SQL*Loader: Release 9.2.0.1.0 - Production on Sat Nov 27 17:24:00 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Commit point reached - logical record count 13

4. BUT THE DATA WAS NOT ENTERED IN THE TABLE WHEN CHECKED

5. ldr.log showed the following
SQL*Loader: Release 9.2.0.1.0 - Production on Sat Nov 27 17:26:25 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Control File: c:\ldr.ctl
Data File: c:\stud.csv
Bad File: c:\stud.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table STUD, loaded from every logical record.
Insert option in effect for this table: REPLACE
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------ --------- ---- -----------------
ROLLNO FIRST * , O(") CHARACTER
NAME NEXT * , O(") CHARACTER

Record 1: Rejected - Error on table STUD, column ROLLNO.
Field in data file exceeds maximum length
Record 3: Rejected - Error on table STUD, column ROLLNO.
Field in data file exceeds maximum length
Record 11: Rejected - Error on table STUD, column ROLLNO.
Field in data file exceeds maximum length
Record 12: Rejected - Error on table STUD, column ROLLNO.
Field in data file exceeds maximum length
Record 13: Rejected - Error on table STUD, column ROLLNO.
Field in data file exceeds maximum length
Record 2: Rejected - Error on table STUD, column ROLLNO.
ORA-01722: invalid number

Record 4: Rejected - Error on table STUD, column ROLLNO.
ORA-01722: invalid number

Record 5: Rejected - Error on table STUD, column ROLLNO.
ORA-01722: invalid number

Record 6: Rejected - Error on table STUD, column ROLLNO.
ORA-01722: invalid number

Record 7: Rejected - Error on table STUD, column ROLLNO.
ORA-01722: invalid number

Record 8: Rejected - Error on table STUD, column ROLLNO.
ORA-01722: invalid number

Record 9: Rejected - Error on table STUD, column ROLLNO.
ORA-01722: invalid number

Record 10: Rejected - Error on table STUD, column ROLLNO.
ORA-01722: invalid number

Record 14: Rejected - Error on table STUD, column ROLLNO.
Field in data file exceeds maximum length

Table STUD:
0 Rows successfully loaded.
14 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.


Space allocated for bind array: 33024 bytes(64 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 14
Total logical records rejected: 14
Total logical records discarded: 0

Run began on Sat Nov 27 17:26:25 2004
Run ended on Sat Nov 27 17:26:26 2004

Elapsed time was: 00:00:00.83
CPU time was: 00:00:00.07

PLEASE GUIDE ME AS WHERE IT WENT WRONG ?????

Regrads
Rishikesh,India

Tom Kyte
November 27, 2004 - 9:29 am UTC

you could have shared the create tables and actual csv file with us couldn't you?

it would be very useful.

SYS password

Ravi Raja, December 03, 2004 - 1:38 am UTC

Hi ,

This is very useful.
Great stuff.

-Ravi

Solving the issue: "ORA-1031 when connect / as sysdba"

Sven Bleckwedel, December 09, 2004 - 7:29 am UTC

Hi Tom,

Helping a business in maintaining their instance, I encountered some problems with ORA-01031:

- fact: SQL*Plus 9.2.0.1
- fact: Oracle9i Enterprise Edition Release 9.2.0.1.0
- fact: Windows 2000 SP4
- fact: Oracle user (and admin) is in ORA_DBA group
- fact: SQLNET.AUTHENTICATION_SERVICES=(NTS), in sqlnet.ora
- symptom: CONNECT sys AS SYSDBA
- symptom: ORA-01031: "insufficient privileges"
- cause: CONNECT sys AS SYSDBA without password

Looking at Metalink, a document said that this problem is due to <Bug:1664348>, fixed in 9.0.1 when connecting to SQL*PLUS with a privileged user without password in the connect string and the "fix-workaround" was to "connect sys/password as sysdba to SQL*PLUS".  This wasn't my problem and I wouldn't use workarounds:

SQL> connect / as sysdba
ERROR:
ORA-01031: insufficient privileges

SQL> connect sys/passwd as sysdba
Conectado.

BUT, what REALLY solved this problem was these steps, mentioned below:

"
O/S Authentication on Windows 2000: 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 

1. Start the "Oracle Administration Assistant for Windows NT" by going to: 
   Start/Programs/Oracle -> O_H/Database Administration/Oracle ->
   Administration Assistant for Windows NT

2. Drill down to Oracle Managed Objects/Computers/<host-name>/Databases. 

3. Right click on the name of the database to connect to. 
   Click on: Connect Database, if requested type username and password 
   of Oracle User 

4. To add a Privileged database user, go to step 5. 
   To add a Non-Privileged user, go to Step 9. 

5. To add a Privileged database user, right click on the container named 
   OS Database Administrators and click on Action>Add/Remove 

6. Select the appropriate NT Domain that you wish to select the User/Group from 

7. Find and select the user/group and click Add.  Click OK. 
   The user/group will be added to the container. 

8. Test the user added by logging onto the OS as the user.
   Start a SQL*Plus session.  In the Log On box, type "/ as sysdba"
   in the User Name box.  Click OK or press Return 
   You should be connected without entering a password...
...
"

HTH other users with these "windoze issues"...

Rgds,
Sven
 

HOW DO I POST MY QUESTION??

RAKESH SHARMA, January 20, 2005 - 2:28 am UTC

HOW DO I POST MY QUESTION?? I TRIED EVERYTHING BUT WAS NOT ABLE TO POST MY QUERY ON THE TOPIC.

PLZ REPLY.

REGARDS,
RAKESH SHARMA

Tom Kyte
January 20, 2005 - 10:27 am UTC

your caps lock key is stuck.

Unable to login from remote machine as sysdba

Rakesh Sharma, January 20, 2005 - 3:44 am UTC

Dear Mr. Tom,

I'm a first time visitor to this website. Kindly ignore my last mail.

I read through the entire forum of "Problem to connect as SYSDBA", however it hasn't solved my problem.
In my case, all the users can connect to the database as sysdba even though I haven't given them sysdba privilege. This is when they login from the machine where oracle db is installed (server).

SQL> conn scott/tiger@train as sysdba
Connected.

However, from client machine no user (including sys) can login "as sysdba".

SQL> conn scott/tiger@train as sysdba
ERROR:
ORA-01031: insufficient privileges

SQL> conn sys/secret@train as sysdba
ERROR:
ORA-01031: insufficient privileges

My questions are:
1 Why all the users are allowed to connect to the db as sysdba w/o having sysdba privilage?
Qn.2 Why from client machine I’m not able login as sysdba? (As suggested by you in one reply I've checked the "power user group" - there's no user in it. )

Please help.

Thanking you in anticipation.

Rakesh Sharma 

Tom Kyte
January 20, 2005 - 10:28 am UTC

stop allowing them to telnet into your server as the Oracle software owner or get them out of the "dba" group.


ANYONE in the dba group (whatever group you used upon install) is permited to connect locally "as sysdba"

Unable to login from Client as sysdba

Rakesh Sharma, January 21, 2005 - 1:29 am UTC

Dear Mr. Tom,

Thanks for your prompt & excellent reply! I'm now able to solve my 1st problem. On oracle server, its only me (member of ORA_DBA) who can connect as sysdba, other users can't. Earlier I allowed other users to use the server machine with my O.S. ID.

However, my second problem still persists. i.e. I'm still not able to login to the db "as sysdba" through any machine other than the server machine. I'm part of group ORA_DBA, ADMINISTRATOR of the server where db is installed. When I connect from client SQL*Plus it gives me ERROR: ORA-01031: insufficient privileges. I've logged in the client machine with my domain id.

Please note the Oracle client version is 8.0.4. & My Oracle DB Server is Oracle 9i (9.2.0.1.0).

Thanks & Regards,
Rakesh Sharma



Tom Kyte
January 21, 2005 - 7:51 am UTC

You'll have issues with that ancient client (sqlplus is far too out of date) and doing this.

have you set up your password file for remote administration? You will be using "connect your_name/your_password@remote_site as sysdba" -- but the 8.0 sqlplus isn't going to like that.

Orapwd and Multiple Database

Deepak Haldiya, January 24, 2005 - 11:52 am UTC

Hi Tom,

I would like to know how oracle differentiates between multiple password files for different database?

Case:

orapwd file=$ORACLE_HOME/dbs/orapwSID1.pwd password=pwd1
orapwd file=$ORACLE_HOME/dbs/orapwSID2.pwd password=pwd2

sqlplus sys/pwd2@SID2 as sysbda
sql>

How does Oracle knows to look into the file orapwSID2.pwd and not in orapwSID1.pwd when I am trying to connect to SID2?

Thanks
Deepak



Tom Kyte
January 24, 2005 - 2:02 pm UTC

well, trick question -- because sid2 is a tns connect string that could be connecting to a database using the sid sid1 :)

the connection gets you to a database instance, the instance name is part of the tns connect entry. You'd have to look at the definition of SID2 in your tnsnames.ora file to see what database instance it would attempt to connect to.

Orapwd files and multiple databases

Deepak Haldiya, January 24, 2005 - 2:49 pm UTC

But my question is related to orapwd files and not tnsnames.ora connection string.

let's say I create 2 oracle password files orapwd1.pwd and orapwd2.pwd by using the following command:

orapwd orapwd1.pwd pwd_xyz, and
orapwd orapwd2.pwd pwd_abc

I have two database, namely SID1 and SID2

How will oracle will figure out which password file to use with Database SID1 and which one to use with SID2?



Tom Kyte
January 24, 2005 - 3:42 pm UTC

yes it is related to the tnsnames.ora!!! given your example:



sqlplus sys/pwd2@SID2 as sysbda
^^^^

sid2 in that connect is a tns connect string, it'll be translated into something that contains the oracle sid, the oracle sid it contains (which could be foobar for all we know) is what is used to construct the name of the password file.

if you just:

connect a/b as sysdba;

the oracle_sid is picked up from your environment.

if you connect a/b@TNS_ENTRY as sysdba;

the oracle_sid is picked up from the tns entry.

Thanks Tom !!!

Deepak Haldiya, January 24, 2005 - 6:31 pm UTC

Thanks Tom for this clarification.

This implies that the passowrd file has to follow a strict naming standard and directory layout so that it can be read by oracle.

Regards
Deepak

Tom Kyte
January 24, 2005 - 7:21 pm UTC

yup

Sys password unknown - very helpful !!

Dunc, February 21, 2005 - 4:55 am UTC

Inherited some Oracle 9i DBs and wasn't told the sys/system passwords. This helped to resolve my problem. Very useful site !!

Oracle 9i OAS Requirements

Prashant Mahajan, June 13, 2005 - 9:04 pm UTC

Hi Tom,

I have a few questions regarding Oracle 9i OAS. We want to transfer the data between Oracle Database server and the client using Oracle Net Services with SSL over TCPS connection.

I have following questions,
(1) Are there any hardware changes required in the network configuration to achieve this? E.g. another network card, port.

(2) What Oracle products need to be installed on client side?

(3) What Oracle products need to be installed on server side?

(4) Are there any changes required in the application if we want to use SSL?

(5) Do we need to purchase certificate from Verisign or are there any recommendation or requirements specific to Oracle that we get the certificate from?

(6) Will there be any effect response time from server to client due to SSL encryption?

Thanks in advance.

Prashant

Tom Kyte
June 13, 2005 - 9:09 pm UTC

if you want to transfer data between a client and database server over SQLNet, what does the application server have to do with anything? you are client server???



Oracle 9i OAS Requirements

Prashant Mahajan, June 14, 2005 - 3:58 pm UTC

Sorry Tom. What I mean is that we want encrypted data transfer between Database Server and Web Server.

Tom Kyte
June 14, 2005 - 4:35 pm UTC

you need to install and configure ASO, the advanced security option (separately licensed if you do not have it already)

And the documentation to do so exists.

altha, June 21, 2005 - 10:56 am UTC

how to chanage the SYS password?
what is a defragmentation the tablespace and how to do this?

Tom Kyte
June 21, 2005 - 5:10 pm UTC

alter user sys identified by foobar;


</code> http://dictionary.reference.com/search?q=defragmentation <code>

perhaps you meant "do tablespaces become fragmented and how do I detect that"?

if so, search around, lots of discussion on that on this site.

connect as sysdba

Olaf, June 22, 2005 - 7:35 am UTC

Very good job, Tom. But how could you explain the follows (on Linux:

> export ORACLE_SID=PDB
> sqlplus /nolog

SQL*Plus: Release 9.2.0.6.0 - Production on Wed Jun 22 15:18:10 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> connect / as sysdba
Connected.

SQL> disconnect
Disconnected from Oracle9i Release 9.2.0.6.0 - Production
JServer Release 9.2.0.6.0 - Production
SQL> connect /@PDB as sysdba
ERROR:
ORA-01031: insufficient privileges

I get the same error if I try to connect as:

connect sys/mypassword@PDB as sysdba

ERROR:
ORA-01031: insufficient privileges

If I type:

connect t/t as sysdba

I am connected....



 

Tom Kyte
June 22, 2005 - 1:52 pm UTC

you are in the OS group that permits local authorization, but your account is not permitted to connect as sysdba remotely.

read about about the init.ora settings, getting the passwords synced up and so on.

ORA-01017

James Young, June 23, 2005 - 11:38 pm UTC

it happened at 10g EM:
The Database Preferred Credentials page appears. Enter the credentials in the form of username and password for normal, SYSDBA and host connections. Click Test

What is the Host username/Host password should be ?
I always have the error message: ORA-01017

Tom Kyte
June 24, 2005 - 6:31 am UTC

is remote SYSDBA authentication set up (see same sort of followup right above this one)

SYSDBA Privilege for Windows

Kim, August 01, 2005 - 8:43 pm UTC

I have few questions...

1) In Oracle 9i for windows,
without SYSDBA or SYSOPER privilege how to perform
Instance startup, mount & database open,
Instance shutdown, dismount & database close,
Alter database BACKUP, ARCHIVE LOG, and RECOVER etc.

2)
As there is no INTERNAL in Oracle 9i, how to set SYSDBA,SYSOPER in Windows?

3)
Can we set SYSDBA,SYSOPER privilege in WINDOWS XP Home Edition?



Tom Kyte
August 01, 2005 - 9:35 pm UTC

1) find a person with sysdba or sysoper

or pull the plug on the server and then plug it ... (nix that, that was a joke)

2) connect / as sysdba

3) that is not really a supported OS, if you can install on it however,

connect / as sysdba

This was close for me... Yet still no cigar!

Chris Wrigley, August 08, 2005 - 7:26 am UTC

You said:
"SVRMGR> connect scott/tiger@ora816dev as sysdba;
Connected.

That worked great, now lets just modify sys's password (not really, didn't change it but Oracle won't notice that

SVRMGR> alter user sys identified by change_on_install;
Statement processed.

SVRMGR> connect sys/change_on_install@ora816dev as sysdba;
Connected.
SVRMGR> 

Hey, now we can use change_on_install -- that is because altering SYS's password will sync up the password in the password file -- sys is a very very close cousin of INTERNAL.  sys's password will be burned into the password file now.  the password foobar is obsolete"
-----

Tom,

I know my SYS and SYSTEM passwords. SYS -apparently- does have SYSDBA system privilege. (Also note that my remote_login_passwordfile parameter is set to "SHARED")

However, your instructions above have not worked for me. I have inherited 8.1.7 DBs and am unable to connect as sysdba. The problem is, I do not know what password the Oracle password file was set up with.

I tried syncing up my SYS password with the one in the password file connected as SYS as you suggested (without the SYSDBA priv as "scott" had in your example above obviously) using:

SQL> alter user sys identified by mySYSpasswd;
Statement processed.

Success here (seemingly), but yet it still does not allow me to connect SYS as SYSDBA. (again, insufficient privs).

Hope you can help. 

Tom Kyte
August 08, 2005 - 8:32 am UTC

because you are using shared. shared doesn't do that.

You should be able to log into the OS directly.
connect "/ as sysdba" (needs no password file, you are in the DBA group).
shutdown
recreate password file
startup



Sorry Tom, cannot connect that way!

Chris, August 08, 2005 - 10:09 am UTC

Your information about "shared" helped to understand I could not approach it that way.

However I am still unable to connect through the OS as sysdba. It's on Windows 2000, btw. From the root machine:

SVRMGRL> connect "/ as sysdba"
Password: <left Null>
ORA-01017: invalid username/password; logon denied

I've tried pretty much all variations. It wants a password. Does the same even if I connect as sys first.

Regards,


Tom Kyte
August 08, 2005 - 7:52 pm UTC

connect internal


Dave, August 08, 2005 - 4:23 pm UTC

if you sing svrmgrl just do connect internal

if you are the correct os user with the correct setup it wont ask you for a password

That's just the point!

Chris, August 09, 2005 - 4:55 am UTC

"connect internal" was one of the first things I tried on the box before writing here. It's obviously a permissions issue of some kind.

I connect to the production box via Proxy Master (similar to VNC), my user login is a member of the administrator's group. I also made it a member of ORA_DBA group just to make sure. I make sure it logs into the machine and not the domain.

Even with the above in place, I still have no luck with connect internal, via SQLPlus or SVMGRL. (It still prompts me for a password)

How frustrating!


Tom Kyte
August 09, 2005 - 9:56 am UTC

can you get to the machine and log into it as the person that did the install in the first place.


You should be able to use the services to shut it down (windows), recreate the password file.

Strange Finding

Deepak, September 06, 2005 - 6:41 am UTC

Hi Tom,

I used the following set of commands on my DB server.

C:\>sqlplus /nolog

SQL*Plus: Release 9.2.0.7.0 - Production on Tue Sep 6 15:25:27 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> conn sys/change_on_install as sysdba
Connected.
SQL> conn sys/change_on_install123 as sysdba
Connected.
SQL> conn sys/change_on_install123456789 as sysdba
Connected.
SQL> conn sys/change_on_inasdastall123456789 as sysdba
Connected.
SQL>

I would like to know why does Oracle allow me to connect using any random passowrd for SYS user?

We have remote_login_passwordfile=EXCLUSIVE set in the pfile.

Please help me... 

Tom Kyte
September 06, 2005 - 8:32 am UTC

when you connect locally - we use OS authentication, use any name/password you like, they all work.

BECAUSE it is the fact you are logged into the OS as an authorized user that counts here.

That password file is for remote authentication over the network.

User login problem

Alay, September 16, 2005 - 10:31 am UTC

Hi Tom,
I have been ask these question in interview.
(1) One day when one user is try to login into the database and press 'enter' after entering username and password his session got hanged. What could be the problem? How you solve it?

(2) One day when users try to login into the database some can login successfully but some got OCI error. What could be the problem? How you solve it?

Can you give the proper answer for that.

alt, September 21, 2005 - 10:21 am UTC

I installed ORACLE 9i on windows operating system.for only SYS and system
user witout giving the password also we can login to the database,how to restiction this.(REMOTE LOGIN=EXCLUSIVE in init.ora file )



Tom Kyte
September 21, 2005 - 7:35 pm UTC

that isn't right -- "as sysdba" you can log in if you are logged into the os as the right account (and hence, you limit access to that account)

but 

SQL> connect system
SQL> connect sys

will demand a valid password. 

ora-1031

Parag J Patankar, December 20, 2005 - 9:40 am UTC

Hi Tom,

I am asking this question to you because I am new to Windows environment. I have remote_login_passwordfile='exclusive' and created passwordfile with password xxxxx. As administrator to Windows 2000 when I am trying to connect as sysdba I am getting following error

JServer Release 9.2.0.1.0 - Production

20:06:54 SQL> connect system/xxxxx as sysdba
ERROR:
ORA-01031: insufficient privileges

Will you pl tell me what I have missed out ? 

regards & thanks
pjp 

Tom Kyte
December 20, 2005 - 9:58 am UTC

the password file is only used for REMOTE (over the network) authentications.

a local sysdba connection like that always uses OS Authentication.

This just means "your OS user is not in the OSDBA group and hence is not allowed to connect as sysdba"

you would have to connect @database - using SQL net to get the password file to be used (on windows, on linux, on unix, on whatever)

Sync password file

A reader, January 09, 2006 - 10:44 am UTC

Hi Tom.

After changing the password of a user with SYSDBA using the VALUES clause of an alter user command the password file is not syncronised.  Is there a way to syncronise it?

-----------------------------------------------------------
SQL> grant connect, sysdba to X identified by X;

Grant succeeded.

SQL> select password from dba_users where username = 'X';

PASSWORD
------------------------------
8E9A9A5413F0B5EE

SQL> alter user X identified by Y;

User altered.

SQL> alter user X identified by values '8E9A9A5413F0B5EE';

User altered.

SQL> connect X/X
Connected.

SQL> connect X/X as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.

SQL> connect X/Y as sysdba
Connected.
-----------------------------------------------------------

Many thanks.

 

Tom Kyte
January 09, 2006 - 10:55 am UTC

ops$tkyte@ORA10GR2> connect no_one/by_that_name as sysdba;
Connected.


You don't say the sqlplus version here - but  you don't need a valid username or password to connect LOCALLY (not over sqlnet) - the password file isn't used, it does OS AUTHENTICATION.  

 

Sync password file

A reader, January 09, 2006 - 11:09 am UTC

Wow! That's a quick response! Thank you.

I'm not generally concerned about where I'm connecting from or how, or whether I need authentication or not.

I wanted to know if it's possible to syncronise the password file with the database. To login as someone else, I would say:

alter user X identified by Y;

which syncronises the password file so connecting as sysdba will also be with password Y. If I don't know X's password but only the encrypted version then saying:

alter user X identied by values '....';

will not syncronise the password file. If that user has sysdba is my only option to revoke and regrant sysdba to sync. the file?

Incidentally I am using 10gR2 sqlplus


Regards


Tom Kyte
January 09, 2006 - 11:27 am UTC

it is not really "encrypted", that is a one way message digest (a hash).  

but you are correct, that if you use the undocumented "values" clause of the alter user command, it does NOT syncronize with the password file.


sys@ORA10GR2> !strings -a orapwora10gr2
]\[Z
ORACLE Remote Password file
INTERNAL
AB27B53EDC5FEF41
8A8F025737A9097A
OPS$TKYTE
7B115C56E551E7CC

sys@ORA10GR2> alter user ops$tkyte identified by values 'CF0015EA0385D263';

User altered.

sys@ORA10GR2> !strings -a orapwora10gr2
]\[Z
ORACLE Remote Password file
INTERNAL
AB27B53EDC5FEF41
8A8F025737A9097A
OPS$TKYTE
7B115C56E551E7CC

sys@ORA10GR2> alter user ops$tkyte identified by foobar;

User altered.

sys@ORA10GR2> !strings -a orapwora10gr2
]\[Z
ORACLE Remote Password file
INTERNAL
AB27B53EDC5FEF41
8A8F025737A9097A
OPS$TKYTE
CF0015EA0385D263    <<<==== back to "normal"


<b>your workaround of revoke+grant seems to work nicely, and is the only thing I can think of to get it to sync up</b>
 

Sync password file

A reader, January 09, 2006 - 11:48 am UTC

That's great. Thank you.

Sync password file

Scott, January 09, 2006 - 5:53 pm UTC

Hi again, Tom.

I have just tried this scenario on another machine (which has Oracle 10.1.0.2.0) and got a different result!:

-----------------------------------------------
SQL> grant connect, sysdba to x1 identified by x1;

Grant succeeded.

SQL> select password from dba_users where username = 'X1';

PASSWORD
------------------------------
4FEEDB77EE7609F2

SQL> alter user x1 identified by y1;

User altered.

SQL> alter user x1 identified by values '4FEEDB77EE7609F2';

User altered.

SQL> connect x1/x1
Connected.

SQL> connect x1/x1 as sysdba
Connected.
-----------------------------------------------

This suggests that the password file is updated when using the "values" clause of an alter user command (I'm using Windows and don't have "strings -a ...", but can see from the timestamp that the file has indeed been updated).

Is there a difference in this version of Oracle and R2; is there a parameter, or perhaps some other configurable setting; or is it that, being undocumented, the behaviour of using values is unreliable or unpredictable?

Kind regards,

Scott. 

Tom Kyte
January 09, 2006 - 6:24 pm UTC

when using connect as sysdba AND NOT USING THE NETWORK - the password file isn't used.

remember:

ops$tkyte@ORA10GR2> connect no_one/by_that_name as sysdba;
Connected.


you can do the same!!! we use OS AUTHENTICATION...

but yes, it looks like 10gr1 did update the password file (i just tested - it did).  10gr2 does not.  Not sure that it matters since "by values" is "not documented" so I don't know what support would say about that one.

Your approach of the grant/revoke seems to be the safest approach.
 

Sync password file

Scott, January 09, 2006 - 7:03 pm UTC

OS authentication is used only when there is a group ora_dba which you're a) a member of; and b) logged in as, otherwise the pw file is in play, is it not?

After all, if I remove the ORA_DBA group (equivalent to removing yourself from the DBA group in UNIX), and delete the password file, as I have just done, and try to connect sys (identified by sys) as sysdba...


C:\>sqlplus /nolog

SQL*Plus: Release 10.1.0.2.0 - Production on Tue Jan 10 00:37:32 2006

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

SQL> connect / as sysdba
ERROR:
ORA-01031: insufficient privileges


SQL> connect sys/sys as sysdba
ERROR:
ORA-01031: insufficient privileges


Bring back the PW file and...

C:\oracle\product\10.1.0\Db_1\database>move PWDemerald.ora.pw.no.more PWDemerald.ora

C:\oracle\product\10.1.0\Db_1\database>sqlplus /nolog

SQL*Plus: Release 10.1.0.2.0 - Production on Tue Jan 10 00:38:22 2006

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

SQL> connect sys/sys as sysdba
Connected.


I am connecting locally am I not (i.e. not over the network).  And without the password file I cannot connect as sysdba.
 

Tom Kyte
January 09, 2006 - 9:29 pm UTC

what I mean is - if the person connecting is in the "special group", the password file is not meaningful unless you go over the network.  so the x1/x1 test is not "conclusive evidence" in that case - because:

ops$tkyte@ORA10GR2> connect no_one/by_that_name as sysdba;
Connected.

works..  If you are logged in as someone in that special group.

But thanks for the clarification (more information = better information), you are absolutely correct. 

while cloning..i am facing the error ora-01031

romit, January 26, 2006 - 9:35 am UTC

I am performing database clone. I ahve done the steps like copying the files, properly setting the environment.
After logging with teh new user and firing the statement

sqlplus '/as sysdba'...i get Ora-01031 error.

I have followed DOC ID 212346.1 for this clone.
OS is RHAS3.0, DB is 9iR2


Tom Kyte
January 26, 2006 - 10:23 am UTC

you are not in the dba group then.

while cloning..i am facing the error ora-01031

romit, January 26, 2006 - 10:31 am UTC

Tom,

primary group is oinstall and sec is dba.....
I hae also folowed every step ( applicable) as per doc id 212346.1

but still facing this problem .......


Tom Kyte
January 26, 2006 - 10:48 am UTC

sorry, you are not in the group that is permitted to connect as sysdba on that machine. given your oracle_home+oracle_sid - your OS USER is not allowed to connect as sysdba.

please help

A reader, March 03, 2006 - 9:18 am UTC

on my local machine i'm logged on into WIN XP (SP2) as the user who had installed 9iR2. this user is a member of groups "ORA_DBA" and "Administrator". file SQLNET.ORA within %ORACLE_HOME%\network\admin only contains entries:

SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

after starting SQL*PLUS (with /nolog option) i'm still getting prompted for password on every attempt to connect "/ as sysdba" or connect internal.

what did i miss?


Amazing

Babar Ali, April 29, 2006 - 3:32 am UTC

aoa all ....
i realy appriciate this site and specially tom who helped people alot .
with best Regards


connect / as sysdba@

V, August 16, 2006 - 12:32 pm UTC

IS there anyway to sqlplus "/@TEST as sysdba" without using a password like sqlplus "/ as sysdba"?

Tom Kyte
August 16, 2006 - 3:45 pm UTC

using OS authentication - but in a unix environment that could be pretty "shaky".

</code> http://docs.oracle.com/docs/cd/B19306_01/win.102/b14304/authen.htm#i1006045 <code>

ora-01990

sudatta, September 07, 2006 - 2:42 am UTC

i have created orapwd file .
and set the following in init.ora file

remote_login_passwordfile = EXCLUSIVE
However when i am trying to open the database i get the following error.

ORA-01990: error opening password file '/oracle/oracle8i/app/oracle/product/8.1.7/dbs/
orapw'

what do i need to check before i open my database.moreover when i select from v$pwfile_users i get no rows selected.


sudatta

Tom Kyte
September 07, 2006 - 7:11 am UTC

prove to us using cut and paste the file exists there using "ls -l" on it.

orapw

Sudatta, September 08, 2006 - 4:21 am UTC

oops , i had created that file in another directory,merely copying it(no recreation) solved it.
Thanks


dba login

yogesh singh, September 14, 2006 - 5:14 am UTC

i have fress install oracle9i and putting username ->sys
and password->change_on_install and connect as sysdba
but that is again asking username n password(becuase i,m just started oracle9i fundamental -1)
i,m unable to enter inside pls help me

thanking u
yogesh

Tom Kyte
September 14, 2006 - 9:11 am UTC

you set the passwords upon install.

what password did YOU set.

REMOTE_LOGIN_PASSWORDFILE

Syed Ahmad, December 02, 2006 - 3:58 pm UTC

Ton,

Could you please explain what is the purpsoe of REMOTE_LOGIN_PASSWORDFILE parameter in init.ora. When should one set this parameter to EXCLUSIVE or NONE. If I am not using OEM then should we set this paramter to EXCLUSIVE?

Is it a good idea to set this to EXCLUSIVE in production environment?
Thanks

Syed

Tom Kyte
December 02, 2006 - 8:02 pm UTC

well
</code> http://docs.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams178.htm#sthref734 <code>

would you like to

a) login over the network without OS authentication? better have one
b) not login without using OS authentication? better not have one.


it is neither a good nor bad idea - you choose, what do YOU need to support?

Conn with EXTERNALLY AUTHENTICATION but with PASSWORD

Hector Gabriel Ulloa Ligarius, December 05, 2006 - 10:58 am UTC

Hi Team Tom..

 See the next example : 

 Exist one user with externally authentication 

$ su - example
Password:*******

$sqlplus /nolog

SQL> conn _/ as sysdba
Enter password: <--- I press any letter..
Connected.      <--- Why?

Is a bug??

Database : 9.2.0.6
O.S.     : SunOS adonis 5.8 Generic_108528-15 sun4u sparc SUNW,Sun-Fire-280R

Regards
Hector Gabriel Ulloa Ligarius
San Juan ,Puerto Rico  

Tom Kyte
December 05, 2006 - 10:12 pm UTC

look at the other place I put the answer to this SAME question

and ask, well, in one place?

orapwd file backup

sudatta, January 29, 2007 - 6:02 am UTC

1. Is it necessary to backup the orapwd file in cold backup.
2. If the answer to this is yes, then what happens to a backup taken without this file , is it useless.

Sudatta



Tom Kyte
January 31, 2007 - 1:29 pm UTC

well, read the answer to the immediately prior question and the same applies :)

connect internal

vikram, May 18, 2007 - 9:57 am UTC

Hi tom,
i am right now using oracle 8...when ever i connect
using svrmgrl with connect internal options it is not asking the password ..i want oracle to ask password ..please tell what to do..this is my first question ..please reply to me..
Tom Kyte
May 18, 2007 - 4:13 pm UTC

stop using accounts that are

a) local to the machine the server is on
b) have been granted sysdba

OS authentication is always one, just revoke sysdba from OS accounts that should not have it, lock down access to those that do

I'm confused

Invisible, May 24, 2007 - 5:05 am UTC

OK, can somebody please explain to me in really tiny words how I can make it so I can log in to Oracle?

I was under the impression that being a member of the ORA_DBA group means I can log in without a password... and yet svrmgrl keeps demanding a password, and won't accept anything I type. I don't understand what I'm doing wrong.

I want to confirm that if our database server gets hit by a small meteorite, I can actually install Oracle on another machine and recover the database. So I found a spare box with Windoze XP and installed Oracle 8i server on it. I used ORADIM to create an instance and tried to log in - but I just kept getting TNS errors. So I created a listener (which wasn't easy) and then that went away. But I still cannot log in to Oracle...

C:\>oradim -new -sid WATP -startmode manual -pfile C:\Database\admin\pfile\init.ora

C:\>set ORACLE_SID=WATP

C:\>svrmgrl

Oracle Server Manager Release 3.1.6.0.0 - Production

Copyright (c) 1997, 1999, Oracle Corporation.  All Rights Reserved.

Oracle8i Release 8.1.6.0.0 - Production
JServer Release 8.1.6.0.0 - Production

SVRMGR> connect internal
Password:
Password:
ORA-01031: insufficient privileges
SVRMGR>

Tom Kyte
May 26, 2007 - 11:02 am UTC

so, where is the evidence you are actually in the ORA_DBA group.

8.1.6???!?!? here is a tiny word: upgrade

what is in your sqlnet.ora?

Well...

Invisible, May 29, 2007 - 6:55 am UTC

Upgrade? Sure! Are you going to cough up £8,000 for a new copy of Oracle? No, I didn't think so... ;-)

Anyway, I was logged in as the machine administrator when I installed Oracle, and I'm still logged in under that account now. I have also manually checked in the Windows user management tool, and the administrator account is definitely in ORA_DBA.

SQLNET.ORA does not exist. I doubt it helps, but I can give you the contents of TNSNAMES.ORA and LISTENER.ORA anyway...

# TNSNAMES.ORA Network Configuration File: C:\Oracle\Ora81\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

WATP.BAS =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = watp.bas)
    )
  )


# LISTENER.ORA Network Configuration File: C:\Oracle\Ora81\network\admin\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = basxp03)(PORT = 1521))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\Oracle\Ora81)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = watp.bas)
      (ORACLE_HOME = C:\Oracle\Ora81)
      (SID_NAME = watp)
    )
  )


This is a fresh install of Oracle, so I'm not really sure what the problem is. According to the manual, "you are in ORA_DBA == you do not need a password", so...

I did manage to actually log in once, but I've tried so many different things now I can't remember the exact combination of things I did that time. I wrote it down, but it doesn't seem to work now, so something must be different.

Tom Kyte
May 30, 2007 - 10:44 am UTC

upgrades "come with" - surely you know that?

So, I really hope you are using a 10 year old OS as well - right? I mean, surely you didn't upgrade that!


you want to setup your sqlnet.ora to allow for NTS authentication.

SQLNET.AUTHENTICATION_SERVICES=(NTS)


sorry, if the presumption that running a business on supported software was "deemed unreasonable"

Yays!

Invisible, May 30, 2007 - 11:05 am UTC

Eureka!

Now finally I can get on with testing the recovery procedure. :-)

Thanks for that Tom. (I am so writing this stuff down...)



As for the rest... Upgrades "come with" what? I don't understand that sentence.

And the OS - well, when did Windows NT 4.0 come out? I don't actually know personally. I know it was a while ago now...

I understand what you're saying about using supported software. I was just pointing out that there are some pretty steep numbers involved. (And when the company you work for is in financial crisis, upgrading software that isn't actually "broken" isn't a huge priority for the people holding the chequebook - even if it is a big priority for me!)

Tom Kyte
May 30, 2007 - 3:51 pm UTC

Upgrades come with the product. You do not buy future releases. It "comes with" for supported customers.


If you are installing a new database, you must be doing ongoing development...

reader

A reader, May 31, 2007 - 5:19 pm UTC

In windows 10g 10.2.0.2
the parameter remote_login_paswordfile takes onle 2 values
SHARED and NONE as per oracle manual SHARED is the value by default

I was looking in regedit for ORA_SID_PWFILE . no entries
OH\database directory does not have any pwd<sid>.ora
How to determine if there is any passwordfile created by default creation of database in e-bus (11i) application. v$pw_file has no entries either
Tom Kyte
June 01, 2007 - 8:26 am UTC

if it isn't there, it isn't there - seems you already determined it?

Not quite

Invisible, June 01, 2007 - 5:49 am UTC

We don't have support [any more], so I don't think that statement applies.

(Surely Oracle corp isn't going to give free copies of all their new software to everyone who's ever bought an Oracle product. They're going to give them to the people who pay the support fees and have a valid support contract...)

As I said, I'm installing a new copy of the database so we can test the database recovery procedure. Nothing more. In a day or two the software will be uninstalled again.

(No "development" work happening here - we just use the DB to run a 3rd party application. 8i is what they recommend using with the product.)

reader

A reader, June 02, 2007 - 8:57 am UTC

What would be the best way to find the location of a password file in a non-default location in windows platform 10gr2
Tom Kyte
June 03, 2007 - 5:38 pm UTC

it will be in the default location.

Password file sync in Dataguard

Satheesh Shanmugam, March 25, 2010 - 11:39 am UTC

When you change the sys password it will get syncd with password file. But how does it work in dataguard (either physical or logical). Is it same behaviour.?

I have a Dataguard setup, version is 10.2.0.4.

When i change the sys password in primary db using "Alter user" command,
Will it get syncd up with password file in primary?
We know that password will get into DR. What will happen to the password file in DR? Will it get syncd up too?

Whenever i do sys password change in primary do i need to copy the password file from primary to standby or do i need to recreate the password file in DR every time?

Appreciate you insight on this.

Regards,
Satheesh Shanmugam

Tom Kyte
March 26, 2010 - 2:41 pm UTC

documentation on "Primary Database Changes That Require Manual Intervention at a Physical Standby" says....

http://docs.oracle.com/docs/cd/E11882_01/server.112/e10700/manage_ps.htm#SBYDB4793

Thank you. Couple of question more

Satheesh Shanmugam, March 29, 2010 - 10:05 pm UTC

Thanks for pointing out the doc
1. That piece of document refers remote_login_passwordfile=shared/exclusive. Can i set that value to none in standby and avoid this manual refresh? Or is there any way to avoid this manual refresh. I am worried about human error in my environment.
2. That note refers physical standby, how is the behaviour in logical standby. Do i need to refresh password file there too?


Regards,
Satheesh Shanmugam
Tom Kyte
April 05, 2010 - 10:21 am UTC

1) If you set it to none, then you won't be using the password file, you would ONLY be using OS authentication.

So, either you want the password to be refreshed or not, you decide. I don't care what setting you have to remote_login_passwordfile - the fact is there will be ONE on the production machine and ANOTHER ONE on the standby. Do you want them to be the same or NOT?

2) see
http://docs.oracle.com/docs/cd/E11882_01/server.112/e10700/log_transport.htm#SBYDB00523
if you are using the password file, it would have to be synchronized

Varidh Bhargava, June 15, 2010 - 2:05 am UTC

Tom, I must say your post really helps me a lot
I was stuck with this issue for whole one day. Came across your link and problem got solved in a fraction of second !!
Kudos to you

In RAC, it is little different!

Riyaj Shamsudeen, January 20, 2011 - 4:33 pm UTC

Tom
One small note though.
In RAC instances, altering sys password only modifies local password file. It doesn't propagate password files to other instances.
We manually propagate to other instances to keep password files in sync when we change sys password.
Apologies, if you have covered this point in another note.



connecting as sysdba (with and without NTS)

A reader, February 09, 2012 - 1:37 pm UTC

hi tom,

i am connecting to my database in server over a tnsname.ora, inside my tnsname is a hostname that loopback to my computer public ip address.

SQLNET.AUTHENTICATION_SERVICES = (NTS)

C:\Documents and Settings\Administrator>orapwd file=C:\oracle\product\10.2.0\db_
1\database\ora10pwd password=foobar

szejie =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracleszejie.dlinkddns.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ora10)
        (SERVER = DEDICATED)
    )
  )

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) 
(HOST = oracleszejie.dlinkddns.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ora10) (SERVER = DEDICATED))) OK (240 msec)

----

SQL> conn sys/abc123@szejie as sysdba
Connected.
SQL> conn sys/1232323232@szejie as sysdba
Connected.
SQL> conn sys/12ccc@szejie as sysdba
Connected.

Q1) all password works ? why`? is it because of the 
SQLNET.AUTHENTICATION_SERVICES = (NTS) ?

---------------------------------

Therefore i change to
SQLNET.AUTHENTICATION_SERVICES = (NONE)


now i cannot connect  at all

SQL> conn sys/foobar as sysdba
ERROR:
ORA-01031: insufficient privileges

SQL> conn sys/foobar@szejie as sysdba
ERROR:
ORA-01031: insufficient privileges

q2) Any idea whats wrong ?

Regards,
Noob

Tom Kyte
February 09, 2012 - 7:40 pm UTC

stop using SYS, stop it right now. Use your OWN ACCOUNTS, have your own sysdba account with your own credentials.


then we can talk.

you were using OS authentication initially. passwords and in fact user names are not relevant. You could have connected using santa/clause as sysdba and it would have worked.

connecting as sysdba (with and without NTS)

A reader, February 09, 2012 - 2:08 pm UTC

hi tom,

ignore my 2nd question, i didn't set my password file properly.

---------------

however, i still puzzled over why am i able to connect as sysdba with any password. 

does SQLNET.AUTHENTICATION_SERVICES = (NTS) overwrite the fact i am connecting to a hostname that loopback to my computer public ip address. 

how does ORACLE knows i am connecting internally (using OS_AUTHENTICATION) ?


SQL> conn sys/aw1@szejie as sysdba
Connected.
SQL> conn sys/1234 as sysdba
Connected.
SQL> conn sys/1234@szejie as sysdba
Connected.
SQL> conn sys@szejie as sysdba
Enter password:
Connected.
SQL>

Regards,
Noob

Tom Kyte
February 09, 2012 - 7:42 pm UTC

connecting as sysdba (with and without NTS)

A reader, February 10, 2012 - 1:01 am UTC

hi tom,

thanks for the explanation.
with sqlplus, that is the behaviour.

but any idea why when using SQL developer on the same machine, it would be always using the password file authentication method ?

Regards,
Noob

conn sys/pwd as sysdba fails

Michael Buck, February 24, 2012 - 8:54 am UTC

Dear Uncle Tom,
This thread solved the following problem for me.
WIn 2008 Srv/Oracle Express 11G peach install as Win user =>gefe<=. Connection to sys works for that user. Now we switch to user ADMINISTRATOR on that machine and the same command fails on invalid username/password. Seemed totally irrational. Then using the advice in this article, we simply changed the password of sys to exactly the same one we used before. ADMINISTRATOR can now connect sys/pwd as sysdba. Thanks for the tip. I would never have guessed at the solution.

How user is authenticated ?

Ankit, November 04, 2012 - 7:55 am UTC

Hi Tom

Thanks a lot for your guidance.
I just need to understand how this authentication is working.

1. Login to Unix with user U having membership in DBA group.
2. sudo su - oracle
password ******
3. set instance and then sqlplus /nolog
4. SQL> connect / as sysdba
......successfully connected .....

I understand that OS authentication is in action, but I also read that the user having SYSDBA or SYSOPER privilege is present in v$PWFILE_USESR. But querying it gave only SYS user and oracle user (to which sudo was done above in step 2) didn't appear in query result.

Just want to understand how it's working and what am I missing to understand.

Thanks a lot.

Tom Kyte
November 05, 2012 - 9:33 am UTC

that listed everyone in the password file, not everyone need be in the password file.

you got in because you were in the DBA group, anyone in the DBA group can connect as sysdba.

How can I connect as sysdba?

Mehmet, November 18, 2013 - 9:11 am UTC

I installed oracle 11g and I can loggin in sysman but cant create user because of dont have privilege,
I think I should login as sysdba but dont know how can I do this ?

Dawn Peter, May 15, 2015 - 10:07 pm UTC

Hi Tom,

What could be the reason for the '/ as sysdba' login behaving differently as below. Could be a bug ?

[oracle@servername ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri May 15 14:29:25 2015

Copyright (c) 1982, 2013, Oracle. All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name: ^C
[oracle@servername ~]$ id oracle
uid=60011(oracle) gid=60013(oinstall) groups=60011(dba),60013(oinstall)
[oracle@servername ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Fri May 15 14:39:41 2015

Copyright (c) 1982, 2013, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and OLAP options

SYS@DBNAME>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and OLAP options
[oracle@servername ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri May 15 15:01:21 2015

Copyright (c) 1982, 2013, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and OLAP options

SYS@DBNAME>

A reader, May 18, 2015 - 6:41 pm UTC

Hi Tom,

Just to be more clear.. The above log is from the same server session within a span of few minutes..

Regards,
Dawn Peter