REMOTE_LOGIN_PASSWORDFILE: is the property that specifies whether oracle uses password file for user authentication and also controls how many databases can use the password file.
Know the REMOTE_LOGIN_PASSWORDFILE property status:
SQL> show parameter remote_login_passwordfile
NAME TYPE VALUE
———————————— ——- ——————————
remote_login_passwordfile string EXCLUSIVE
SQL>
OR
SQL> show parameter password
NAME TYPE VALUE
———————————— ——- ——————————
remote_login_passwordfile string EXCLUSIVE
SQL>
By Default Oracle sets the property to EXCLUSIVE value that means the password file is being used exclusively only by one database. With this default value, if you attempt to bring up a different database in current instance, then you’ll end up with below error message:
SQL> STARTUP PFILE=/opt/oracle/product/8.1.5/dbfiles/initinst03.ora
ORACLE instance started.
Total System Global Area 87859360 bytes
Fixed Size 73888 bytes
Variable Size 56893440 bytes
Database Buffers 30720000 bytes
Redo Buffers 172032 bytes
ORA-01991: invalid password file ‘/opt/oracle/product/8.1.5/dbfiles/orapwprodins01’
SQL>
In order to bring up other database, you first need to have REMOTE_LOGIN_PASSWORDFILE set to SHARED so that same password file will be used for authenticating to any of the Database in the given instance.
To set/update Oracle DB properties you can use : ALTER SYSTEM SET command:
SQL> alter system set remote_login_passwordfile=shared;
alter system set remote_login_passwordfile=shared
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL>
Check if the updating to property is allowed from the DB:
SQL> select name, issys_modifiable from v$parameter where upper(name) like ‘REMOTE_LOGIN%’;
NAME ISSYS_MOD
—————————————————————- ———
remote_login_passwordfile FALSE
SQL>
If issys_modifiable is FALSE then the REMOTE_LOGIN_PASSWORDFILE property can’t be modified from the SQL interface. The alternate you have is to configure the property to be updatable from the SPFILE by running below command:
SQL> ALTER SYSTEM SET remote_login_passwordfile=shared scope=SPFILE;
ALTER SYSTEM SET remote_login_passwordfile=shared scope=SPFILE
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL>
Ignore the error output from the command, but it essentially configured Scope parameter to use the SPFILE for the property changes.
Now, open the DB SPFILE (init<instancename>.ora) and modify the remote_login_passwordfile parameter to Shared as shown below:
Before
[root@dev ~]# grep -i remote_login_passwordfile /opt/oracle/product/8.1.5/dbfiles/initinst03.ora
remote_login_passwordfile = exclusive
[root@dev ~]#
After:
[root@dev ~]# grep -i remote_login_passwordfile /opt/oracle/product/8.1.5/dbfiles/initinst03.ora
remote_login_passwordfile = shared
[root@dev ~]#
Now Bounce the oracle instances:
SQL> SHUTDOWN NORMAL
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> STARTUP PFILE=/opt/oracle/product/8.1.5/dbfiles/initinst03.ora
ORACLE instance started.
Total System Global Area 87859360 bytes
Fixed Size 73888 bytes
Variable Size 56893440 bytes
Database Buffers 30720000 bytes
Redo Buffers 172032 bytes
Database mounted.
Database opened.
SQL>
Now Database will be Opened without any issues and you can verify the change of property value as below:
SQL> show parameter password
NAME TYPE VALUE
———————————— ——- ——————————
remote_login_passwordfile string SHARED
SQL>
