Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: June 25, 2009 - 11:16 am UTC

Last updated: July 17, 2020 - 6:35 am UTC

Version: 9i and 11g

Viewed 50K+ times! This question is

You Asked

Hi Tom,

What is the relation between SYS user password and orapwd file ?

What are the various methods to change the SYS user password.

If I change the SYS user password using
from sqlplus use password command or
use alter user sys identified by xyz,
Then does it automatically do the change password in the orapwd file ?

also VICE VERSA

If after DB creation I use orapwd and set password to something else does it get automatically changed in the DB


and Tom said...

it is a loose relation - they can actually be different


http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:670117794561

that is a bit dated but still true.

If you create the password file with a password different than the sys password, that'll be the password you use to connect as sysdba over a network (the password in the password file is used for sysdba connections). The sys password to just connect as sys (something you wouldn't really ever have a need to do EVER) can be different.

But when you alter the SYS password, they sync that up with the password file, as demonstrated.

Since the password file is separate and distinct from the database - it does not attempt to update the database when you update the password file.

Rating

  (10 ratings)

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

Comments

RAC orapw file

A Reader, January 07, 2010 - 4:21 pm UTC

In a RAC environment, there are separate orapwDB files for each Instance, so does it mean that these orapwDB passwords can be dffferent from one instance to another ?

I thought passwords are tied to database, not instances.

RAC password

Jay, February 02, 2012 - 9:42 pm UTC

Hi Tom,
I ran into a similar issue twice. The situation is like this - I forgot the SYS password for our dev RAC nevironment. So I logged in to one of the nodes and connected using sqlplus / as sysdba. I did an alter user SYS identifed BY....Later, when I tried to login to that specific node from another machine, this new passsord was working. But when I tried to login using the SCAN, the new password was not working. I gave up trying because of other priorities. The next day, the new password was working for the individual node as well as SCAN. What might explain this?
Tom Kyte
February 03, 2012 - 9:55 am UTC

... I forgot the SYS
password for our dev RAC nevironment. ..


sys%ORA11GR2> connect sys/manager
ERROR:
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER


Warning: You are no longer connected to ORACLE.
sys%ORA11GR2> !oerr ora 28009
28009, 00000, "connection as SYS should be as SYSDBA or SYSOPER"
// *Cause:    connect SYS/<password> is no longer a valid syntax
// *Action:   Try connect SYS/<password> as SYSDBA or
//            connect SYS/<password> as SYSOPER
//


you don't ever log in as sys anyway, you can forget it.

why are you even using sys???????

SYS

A reader, February 08, 2012 - 5:13 am UTC

Hi,
I logged in using sys as sysdba.
Regards,
Jay
Tom Kyte
February 08, 2012 - 5:34 am UTC

do not do that, use your OWN credentials.


why did you post this before?

sys%ORA11GR2> connect sys/manager
ERROR:
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER



you NEVER need to use sys, never.

either use your own credentials to become sysdba, or use OS authentication and connect / as sysdba using an OS account that is privileged to do that.

SYS

Jay, February 10, 2012 - 3:42 am UTC

Hi Tom,
You asked me "why did you post this before?

sys%ORA11GR2> connect sys/manager
ERROR:
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER"
But I did not post anything like that.
Tom Kyte
February 10, 2012 - 5:08 pm UTC

you said you were logging in as sys with a password?

SYS

Jay, February 15, 2012 - 3:37 am UTC

Hi,
I said I logged in using sys as sysdba. Anyway, let us leave that aside. My doubt was, is there a lag- a delay between nodes in a RAC as far password updates are concerned. The doubt is for the logins using the password file.
Regards,
Jay
Tom Kyte
February 15, 2012 - 8:26 am UTC

there is a single password file, no latency.


sqlplus / as sysdba

DOES NOT USE THE PASSWORD FILE.

the only time the password file would be used is over the network. connect user/ps@remote as sysdba



If you used / as sysdba, no password is ever needed. You could

connect santa/clause as sysdba

it would work as well as

connect boo/hoo as sysdba
connect foo/bar as sysdba
connect anything/you_want as sysdba

the user/password are not relevant. It uses OS authentication.

RAC

Jay, February 16, 2012 - 2:36 am UTC

Hi Tom,
I came across this link
http://prutser.wordpress.com/2011/01/14/clustered-asm-and-rac-password-file-maintenance/
The link says that in a 2-node rac, there wil be 2 password files, and if the password for SYS is changed in one node, it does not get reflected in the other one (for SYS). It seems to explain the issue I faced.

Tom Kyte
February 16, 2012 - 7:18 am UTC

only if you purposely decided to NOT share it. As that article itself states. It is normal to share the password file, to have one. If you decided to have many - it would be up to you to synchronize them, yes.

and again, it would *NEVER* affect:

connect sys/foobar as sysdba

because we DO NOT use a passwordfile for that. Given that you wrote:

So I logged in to one of the nodes and connected using sqlplus / as sysdba. I
did an alter user SYS identifed BY....Later, when I tried to login to that specific node from
another machine, this new passsord was working.



you said you were logging in "/ as sysdba" - the password file doesn't come into play at all. It is all about OS authentication.

I can only respond to what I see written.

Alter user system

Sjsjshs, November 25, 2017 - 10:02 pm UTC

I changes the system password with alter user (by mistake) 2 minutes later I changed it with his old password.

After I had a problem with a archivelog synchronisation. We had to copy the filw with passwords.

Is it because I changed the system password? Event if I rechanged it again 2 minutes later?
Connor McDonald
November 27, 2017 - 1:15 am UTC

"archive log synchronization" ? Are you talking about DataGuard ?

Basically anything that requires SYSDBA access to a *remote* database will be using the password in the orapwd file.

Alter user system

Jsjsj, November 28, 2017 - 8:47 am UTC

Yes but it's about a rac cluster
Connor McDonald
December 01, 2017 - 2:50 am UTC

For a RAC environment, I would have a single password file shared across the instances.

Alter user system

Jsjsj, December 03, 2017 - 7:15 pm UTC

Ok but you didn't answer my question

SQLPLUS connection string anomalies

Gopi Kaddu, July 16, 2020 - 10:14 am UTC

Hi Tom / Team, Thank you guys for the and knowledge sharing and continued help provided to the entire Oracle community towards their queries and issues. I too have a few, to start with..

Can you please explain and help me understand why sqlplus/oracle is behaving differently when logged in -

1) Using SQLPLUS;
[oracle@olx8e admin]$ sqlplus

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jul 16 05:33:24 2020

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

Enter user-name: system as sysdba
Enter password: ********

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production



2) SQLPLUS connection string (with sysdba) as -

[oracle@olx8e admin]$ sqlplus system/********@odbe as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jul 16 05:31:54 2020

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

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


3) Connection string (without sysdba), as -
[oracle@olx8e admin]$ sqlplus system/********@odbe

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jul 16 05:30:31 2020

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

Last Successful login time: Thu Jul 16 2020 05:24:08 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


My ask above besides, whats causing connections in above scenarios to succeed or fail and what i can do to make them consistent so in any of above scenarios the connection to DB is a success?

Things I have tried:
----------------------------------------------------
Recreated the password file with orapwd utility
Altered password using alter user command
am using same password for both above
tnsping gives o.k, both on local / remote server.

4) Lastly, how should one connect to an Idle Database Instance (with just Oracle software installed and no DB created) remotely using TNS connect string?



Connor McDonald
July 17, 2020 - 6:35 am UTC

(1) All local connections are implicitly SYS when connecting as sysdba, eg

C:\>sqlplus blah as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jul 17 14:26:12 2020
Version 19.5.0.0.0

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

Enter password:

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL> sho user
USER is "SYS"



(2) Is SYSTEM granted SYSDBA? Normally it isn't - you use SYS

(3) As above. SYSTEM is a normal user.

(4) orapwd sets the SYS user coming in across the network. And then you do:

connect sys/pass@my_tns as sysdba