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!

About the Author

What does it take to be chief technology officer at a company of technology experts? Experience. Imagination. Passion. Alex Gorbachev has all three. He’s played a key role in taking the company global, having set up Pythian’s Asia Pacific operations. Today, the CTO office is an incubator of new services and technologies – a mini-startup inside Pythian. Most recently, Alex built a Big Data Engineering services team and established a Data Science practice. Highly sought after for his deep expertise and interest in emerging trends, Alex routinely speaks at industry events as a member of the OakTable.

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.

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.

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 ?

PYTHIAN®, LOVE YOUR DATA®, and ADMINISCOPE® are trademarks and registered trademarks owned by Pythian in North America and certain other countries, and are valuable assets of our company. Other brands, product and company names on this website may be trademarks or registered trademarks of Pythian or of third parties. Use of trademarks without permission is strictly prohibited.