Password File

Default directory for Oracle Password File:

$ORACLE_HOME/dbs/orapw$ORACLE_SID on Unix

%ORACLE_HOME\database\PWD%ORACLE_SID%.ora on Windows.

To use password file to login to server from a remote machine we need to set this parameter:

show parameter remote_login_passwordfile;

The value for above parameter should be:

EXCLUSIVE for a single instance database.

SHARED for a RAC database.

NONE means no password file.  We can't use password file to login to server as admin from a remote machine.  Even the file exists oracle will ignore it.

Listener must be running in the source machine.  Tns entry must be working from remote machine to the server.

orapwd is the exe we use to create password file. Which lies inside bin folder.

To see all available options:

orapwd help=y

When we run below command oracle will ask password for SYS user.  After giving password it will add SYS user to password file.  We are executing orapwd command inside dbs folder.  If we are running this command from outside we must provide file=path.

orapwd file=orapwprod entries=5 force=y ignorecase=y

We can give password for SYS user inside the above command:

orapwd file=orapwprod password=manager entries=5 force=y ignorecase=y

Generally we login to oracle DB with OS authentication like below:

sqlplus / as sysdba

Here '/' means OS credentials.

To login from a remote machine as sysdba we use the below method:

sqlplus sys/manager@toprod as sysdba

To confirm password file is being used or not:

At Source DB:

shutdown immediate;

rename the password file to orapwprod.bak

startup mount;

By keeping the source db in mount mode, now try this from remote machine:

sqlplus sys/manager@toprod as sysdba

this will throw an error : insufficient privileges

At Source DB:

shutdown immediate;

rename the password file to the original name as orapwprod

startup mount;

From Remote machine:

sqlplus sys/manager@toprod as sysdba

We are able to connect in mount mode.

to confirm

select open_mode, name, host_name from v$database,v$instance;

MOUNTED, PROD, m1.dba.com

Users are added to the password file when they are granted the SYSDBA, SYSOPER, SYSASM, SYSKM, SYSDG or SYSBACKUP privilege.  By default, SYS is the only user that has SYSDBA and SYSOPER privileges.

To check the number of users with SYSDBA and SYSOPER privileges:

select * from v$pwfile_users;

To create a new user 'u1' and grant sysdba privilege, and confirm its addition to the password file:

creat user u1 identified by u1;

grant sysdba to u1;

select * from v$pwfile_users;

USERNAME        SYSDB SYSOP SYSAS

------------------------------ ----- ----- -----

SYS                TRUE  TRUE  FALSE

U1                TRUE  FALSE FALSE

We can grant both SYSDBA and SYSOPER roles to a user:

create user u2 identified by u2;

grant sysdba, sysoper to u2;

select * from v$pwfile_users;

USERNAME        SYSDB SYSOP SYSAS

------------------------------ ----- ----- -----

SYS                TRUE  TRUE  FALSE

U1                TRUE  FALSE FALSE

U2                TRUE  TRUE  FALSE

If we delete the orapwprod file all the above 3 records will be gone.  Password file will become empty. We can't login as sysdba from remote machine. But we can still login as a normal user.

In the password creation command if entries=2 then, even though we granted sysdba privilege to 'n' number of users only 2 can login as sysdba at a time.

As the password file is a binary file, to see the contents inside it at OS level:

strings orapwprod

As we added user 'U1' to the password file, we can login to db from a remote machine using U1 credentials as:

sqlplus u1/u1@toprod as sysdba

Now we can startup or shutdown the db using U1 credentials.

Even through we logged in using U1 credentials still it will show as 'SYS' user with below commands:

select user from dual;

show user;