REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE in Oracle 10g
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!
June 18th, 2007 at 3:22 pm
Is this new in 10gR2? My exam prep guide (written before R2) does discuss (and quiz) the distinction between SHARED and EXCLUSIVE?
June 18th, 2007 at 4:54 pm
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.
June 18th, 2007 at 10:07 pm
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
…..
June 19th, 2007 at 10:30 pm
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.
June 20th, 2007 at 2:46 am
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.
June 21st, 2007 at 2:20 pm
Thanks Alex,
1.Referring to Oracle 10gR2 admin . (Setting REMOTE_LOGIN_PASSWORDFILE section)
http://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
June 21st, 2007 at 5:10 pm
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?