Password file in the Oracle database is must to login remotely for sysdba privileges. Oracle creates password file automatically while creating database using DBCA by asking us what password to set for sys and system users.
Here we will see how we can recover from the situation when we lost our password file. It can be lost if the file is deleted physically.
Here I tried to connect sqlplus using tns entry of database
[oracle@prod-18c ~]$ sqlplus sys@orcl1 as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Fri May 24 23:07:42 2019
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Enter password:
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
[oracle@prod-18c ~]$ We can see above , it is giving an error that username or password is invalid . Now , let us check in the directory if the password file physically exists. Generally it resides in $ORACLE_HOME/dbs directory.
[oracle@prod-18c ~]$ ls -l $ORACLE_HOME/dbs/orapw*
-rw-r----- 1 oracle oinstall 3584 Mar 3 14:26 /u01/app/oracle/product/12.2.0.1/db_1/dbs/orapwjdb
-rw-r----- 1 oracle oinstall 3584 May 22 23:32 /u01/app/oracle/product/12.2.0.1/db_1/dbs/orapwjdb1
[oracle@prod-18c ~]$ Here we can see no password file for orcl1 database exists ,there are two password files available which belongs to jdb and jdb1 database respectively.
Now to recover from this situation we need to create new password file using orapwd utility.
You can check all supported parameters of orapwd utility by just giving orapwd command :
[oracle@prod-18c ~]$ orapwd
Usage: orapwd file=<fname> force=<y/n> asm=<y/n>
dbuniquename=<dbname> format=<12/12.2>
delete=<y/n> input_file=<input-fname>
sys=<y/password/external(<sys-external-name>)>
sysbackup=<y/password/external(<sysbackup-external-name>)>
sysdg=<y/password/external(<sysdg-external-name>)>
syskm=<y/password/external(<syskm-external-name>)>
Usage: orapwd describe file=<fname>
where
file - name of password file (required),
password - password for SYS will be prompted
if not specified at command line.
Ignored, if input_file is specified,
force - whether to overwrite existing file (optional),
asm - indicates that the password to be stored in
Automatic Storage Management (ASM) disk group
is an ASM password. (optional),
dbuniquename - unique database name used to identify database
password files residing in ASM diskgroup only.
Ignored when asm option is specified (optional),
format - use format=12 for new 12c features like SYSBACKUP, SYSDG and
SYSKM support, longer identifiers, SHA2 Verifiers etc.
use format=12.2 for 12.2 features like enforcing user
profile (password limits and password complexity) and
account status for administrative users.
If not specified, format=12.2 is default (optional),
delete - drops a password file. Must specify 'asm',
'dbuniquename' or 'file'. If 'file' is specified,
the file must be located on an ASM diskgroup (optional),
input_file - name of input password file, from where old user
entries will be migrated (optional),
sys - specifies if SYS user is password or externally authenticated.
For external SYS, also specifies external name.
SYS={y/password} specifies if SYS user password needs
to be changed when used with input_file,
sysbackup - creates SYSBACKUP entry (optional).
Specifies if SYSBACKUP user is password or externally
authenticated. For external SYSBACKUP, also specifies
external name. Ignored, if input_file is specified,
sysdg - creates SYSDG entry (optional).
Specifies if SYSDG user is password or externally authenticated.
For external SYSDG, also specifies external name.
Ignored, if input_file is specified,
syskm - creates SYSKM entry (optional).
Specifies if SYSKM user is password or externally authenticated.
For external SYSKM, also specifies external name.
Ignored, if input_file is specified,
describe - describes the properties of specified password file
(required).
There must be no spaces around the equal-to (=) character.
[oracle@prod-18c ~]$ Now , I added password file using FILE and ENTRIES parameter and hit enter. It will ask you to enter password for sys.
[oracle@prod-18c ~]$ orapwd FILE=$ORACLE_HOME/dbs/orapworcl1 ENTRIES=15
Enter password for SYS:
OPW-00029: Password complexity failed for SYS user : Password must contain at least 1 special character.
[oracle@prod-18c ~]$ orapwd FILE=$ORACLE_HOME/dbs/orapworcl1 ENTRIES=15
Enter password for SYS: //Or$cle12
[oracle@prod-18c ~]$
We can see that first time it failed and gave error that given password was not complex. So again I tried and gave complex password and it succeeded .
After adding password file try same sys connection again.
[oracle@prod-18c dbs]$ sqlplus sys@orcl1 as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Fri May 24 23:15:16 2019
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> We are able to connect now. To check more about it lets check v$pwfile_users
SQL> select username ,sysdba,authentication_type from v$pwfile_users;
USERNAME SYSDB AUTHENTI
-------------------------------------------------------------------------------------------------------------------------------- ----- --------
SYS TRUE PASSWORD
SQL> We can see authentication type is PASSWORD for sys. So create password file easily if your existing password is deleted.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Telegram Channel: https://t.me/helporacle
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/
Joel Perez’s LinkedIn: Joel Perez’s Profile
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
