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 good explanation

"...-- 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?

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

The information is very useful.

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

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

Thanks in advance.

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

tom,

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

please help

Thanks

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

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 , This Kind of anwer I want too....Are u a magician!!!!


Regards,

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 

 

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. 

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

Mr. Mark Thanks a lot !! for valuable information

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

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 ?

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

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.

Hi Tom,

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

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.

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.

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

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?
 

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.

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 :-). 

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

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,

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,

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>

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

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]$

 

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

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

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

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

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

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

Hi ,

This is very useful.
Great stuff.

-Ravi

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?? I TRIED EVERYTHING BUT WAS NOT ABLE TO POST MY QUERY ON THE TOPIC.

PLZ REPLY.

REGARDS,
RAKESH SHARMA

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 

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

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

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?

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

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

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

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

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

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



 

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

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?

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. 

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,

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

"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!

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

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.

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 )

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. 

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 

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.

 

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.  

 

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

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>
 

That's great. Thank you.

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. 

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.
 

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.
 

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. 

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,

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

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?

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

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

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

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

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

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

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  

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

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

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>

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.

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!)

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

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.)

What would be the best way to find the location of a password file in a non-default location in windows platform 10gr2

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

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

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.

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

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

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

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.

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.

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 ?

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>

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