I’ve never really liked the idea of REMOTE_LOGIN_PASSWORDFILE=SHARED, probably just because I haven’t seen much use for it. As a result, I’ve never paid any attention to it. If you don’t recall the difference between EXCLUSIVE and SHARED settings for 9i, here is the quote from the documentation:
SHARED
More than one database can use a password file. However, the only user recognized by the password file is SYS.EXCLUSIVE
The password file can be used by only one database and the password file can contain names other than SYS.
As I said, I could never imagine the use case for a shared password file. If you have better ideas of a situation that is a good fit for shared password file, please share.
Today, I was going through a migration strategy with a client, and we were reviewing init.ora parameters. I noticed that they used SHARED settings for their password file and was curious why. Apparently, there was no clear explanation for SHARED setting and it was used more or less as exclusive — one file per instance. Alex Fatkulin seemed to be curious too and did the RTFM part for me (good boy!) and, surprise! the EXCLUSIVE setting is not used in 10g anymore.
It turned out that Oracle merged the SHARED and EXCLUSIVE password file features — now we can used shared password file and store passwords for users other than SYS. REMOTE_LOGIN_PASSWORDFILE=SHARED is used for that. EXCLUSIVE still works for backwards compatibility but now it behaves just like SHARED.
Here is the new reference from the docs:
NONE
Oracle ignores any password file. Therefore, privileged users must be authenticated by the operating system.SHARED
One or more databases can use the password file. The password file can contain SYS as well as non-SYS users.Note:
The value EXCLUSIVE is supported for backward compatibility. It now has the same behavior as the value SHARED.
There are quite a few non-obvious behavior changes that are not well known but are in fact documented, so thorough RTFM-ing seems to be a good idea with every new release. For our part, we will try to post some of them here so stay tuned!
11 Comments. Leave new
Is this new in 10gR2? My exam prep guide (written before R2) does discuss (and quiz) the distinction between SHARED and EXCLUSIVE?
Oracle documentation (Oracle Reference Guide) for 10.1 and 10.2 will surely answer your question. ;-)
Let us know when you find it. You can even go further and set a test case for that if you that curious.
As I know,
1. If REMOTE_LOGIN_PASSWORDFILE is not determined, it is set by default to EXCLUSIVE. How Oracle keep that for only backward compatibility while its default value is EXCLUSIVE.
2. If REMOTE_LOGIN_PASSWORDFILE is set to shared, SYS password can not be changed. (One useful point of using this value).
3. When using SHARED value for this parameter, SYSDBA or SYSOPER can not be granted to any user. This somehow prevents mistaken granting SYSDBA or SYSOPER to some users.
4. User which administrating multiple databases on the same box, need to know only one password if this value is set to SHARED
…..
1. If this parameter is not determined, EXCLUSIVE is set by default. (Doubt to keep it for only backward compatibility).
2. Shared is practical in environment when you do not want to allow anyone to grant SYSDBA or SYSOPER to anybody else. Also for DBA which admin multiple DBs, having shred password file help them to remember only one password for sysdba privileges.
Password file name is instance specific so you will have to mess with links if all your databases are on one node. If databases are scattered over a number of nodes — well, I guess password files has to be on shared storage then.
Have you ever used shared password file successfully? I mean truly shared between several instances? I recall I tried to set it up in 8i days and I couldn’t get it to work so I gave up and, as I mentioned already, couldn’t really see any real benefits of it.
Oracle 10.2 Reference suggests that default value is SHARED. Though, I haven’t tested it yet.
Thanks Alex,
1.Referring to Oracle 10gR2 admin . (Setting REMOTE_LOGIN_PASSWORDFILE section)
https://download-east.oracle.com/docs/cd/B19306_01/server.102/b14231/dba.htm#sthref167
2. I definitely see benefits on using shared for this parameter which prevents further granting SYSDBA to any user in stable environment
The link you gave contradicts the reference guide. There is definitely a documentation bug somewhere. Let me test it.
2. I definitely see benefits on using shared for this parameter which prevents further granting SYSDBA to any user in stable environment.
Can you elaborate?
Kind of a late update to this.
I came across this entry while searching for some info on oracle password files.
Both the 10g and 11g documentation are incorrect.
10g: https://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams179.htm#CHDICDHE
11g: https://download.oracle.com/docs/cd/B28359_01/server.111/b28320/initparams198.htm#REFRN10184
If remote_login_passwordfile=shared, no users can be added to the password file.
Attempting to do so will raise an ORA-1999: “Password file cannot be updated in SHARED mode”
Hi
Is it possible to share a passwd file between 2 databases that have differents DBID ? and how ? any constraints ? (should shared the same home?)
How does oracle know which passwd file it should use ?
Is it designed to automatically look for a standard filename (smth like $ORACLE_HOME/dbs/passDBID) filename or any other standard name ?
In this case i suppose these DB should share the same home or the DBID2 should have its passDBID2 being a link that point to the passDBID1 file …
Then we want make maintenance/modif of the passwd file possible, we should care about user ID and chown/chmod are correctly set up for the shared orapasswd file.
Maybe the SHARED option is mainly designed for distributed or cluster DB.
Maybe having the REMOTE_LOGIN_PASSWORDFILE storing the absolute filename would be better than just EXCLUSIVE/NONE/SHARED values ?
Thanks for your time reading.
Cheers.
if u create a new passwordfile (even in default location),database does not recognize it.
How can we user one password file to multiple databases? can anybody help me to know this by giving steps?