How to create oracle password file

create password file

 

SYS Password Management with RAC and Data Guard (Doc ID 1267828.1)

create Oracle password file 

su - oracle

-bash-3.2$ id
uid=501(oracle) gid=501(oinstall) groups=501(oinstall),502(dba),503(oper),504(asmadmin)

-bash-3.2$ $ORACLE_HOME/bin/orapwd file=/u01/oracle/product/11.2.0/db_1/dbs/orapwvihaan password=vihaan entries=5

[oracle@wcp12cr2 dbs]$ $ORACLE_HOME/bin/orapwd help=y
Usage: orapwd file=<fname> entries=<users> force=<y/n> asm=<y/n>
       dbuniquename=<dbname> format=<legacy/12> sysbackup=<y/n> sysdg=<y/n>
       syskm=<y/n> delete=<y/n> input_file=<input-fname>

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,
    entries - maximum number of distinct DBA (optional),
    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, etc.
             If not specified, format=12 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),
    sysbackup - create SYSBACKUP entry (optional and requires the
                12 format). Ignored, if input_file is specified,
    sysdg - create SYSDG entry (optional and requires the 12 format),
            Ignored, if input_file is specified,
    syskm - create SYSKM entry (optional and requires the 12 format),
            Ignored, if input_file is specified,
    input_file - name of input password file, from where old user
                 entries will be migrated (optional),
    describe - describes the properties of specified password file
               (required).


  There must be no spaces around the equal-to (=) character.

[FORMAT = {12.2|12}]    old format

On filesystem SID 

$ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle format=12

$ORACLE_HOME/bin/orapwd describe file=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapwrac1
Password file Description : format=12.2

another example 

[oracle@wcp12cr2 ~]$ $ORACLE_HOME/bin/orapwd describe file=/oracle/db/ohome/dbs/orapworcl
Password file Description : format=12 ignorecase=N 

Now test the connection ....

-bash-3.2$ sqlplus 'sys/vihaan@prim as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Sat Apr 14 11:27:45 2012

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

prim =

prim =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.129)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SID = vihaan)
 )
 )

===============================================================

SYS Password Management with RAC and Data Guard (Doc ID 1267828.1)

===

Then ORA-1031 error will be reported in primary alert log:

Wed Nov 17 15:33:48 2010
Error 1031 received logging on to the standby
Errors in file /u01/diag/rdbms/tl/<PRIM_INST_NAME1>/trace/<PRIM_INST_NAME1>_arc3_24629.trc:
ORA-01031: insufficient privileges
FAL[server, ARC3]: FAL archive failed, see trace file.
Errors in file /u01/diag/rdbms/tl/<PRIM_INST_NAME1>/trace/<PRIM_INST_NAME1>_arc3_24629.trc:


Solution:
1. For password stored on the file system:
a. Simply copy (scp) the orapw<SID> file from any of the primary instance to all standby instances:

$ scp $ORACLE_HOME/dbs/<PRIM_PWD_INST_NAME1> oracle@<STBY_HOSTNAME1>:<$ORACLE_HOME>/dbs/
$ scp $ORACLE_HOME/dbs/<PRIM_PWD_INST_NAME1> oracle@<STBY_HOSTNAME2>:<$ORACLE_HOME>/dbs/

b. Rename the password file on all standby nodes to match the standby SID.

on <STBY_HOSTNAME1>:
mv $ORACLE_HOME/dbs/<PRIM_PWD_INST_NAME1> $ORACLE_HOME/dbs/<STBY_PWD_INST_NAME1>

on <STBY_HOSTNAME2>:
mv $ORACLE_HOME/dbs/<PRIM_PWD_INST_NAME1> $ORACLE_HOME/dbs/<STBY_PWD_INST_NAME2>
2. For password stored on the ASM diskgroup:
a. On primary, run srvctl config database -d <db_unique_name> to locate the password file
$ srvctl config database -d <DB_NAME>

Password file: +<DG_NAME>/<DB_NAME>/PASSWORD/<PWD_DB_NAME>.400.940505217
b. using asmcmd to copy the file to file system
asmcmd> pwcopy +<DG_NAME>/<DB_NAME>/PASSWORD/<PWD_DB_NAME>.400.940505217 /tmp
c. scp the password file to standby hosts
$ scp /tmp/<PWD_DB_NAME>.400.940505217 oracle@<standbyhost>:/tmp
d. restore the password file to ASM diskgroup on standby
asmcmd> pwcopy /tmp/<PWD_DB_NAME>.400.940505217 +<DG_NAME>/<DB_NAME>DG/PASSWORD/<PWD_DB_NAME>DG
 e. modify the password location for standby
$ srvctl modify database -d <db_unique_name> -pwfile +<DG_NAME>/<DB_NAME>DG/PASSWORD/<PWD_DB_NAME>DG
 
3. It is not required to shutdown/restart any of the primary or standby instances.

4. Log shipping will resume automatically after this.
If not, alter parameter log_archive_dest_state_<n> to defer, then enable.

SQL> alter system set log_archive_dest_state_2=defer scope=both sid='*';

SQL> alter system set log_archive_dest_state_2=enable scope=both sid='*';



Grid ASM 
ASMCMD [+DATA/IBRAC/PWDFILE] > pwcreate --dbuniquename ibrac +DATA/IBRAC/PWDFILE/pwdibrac Very_Complex!1ASMCMD 
ASMCMD-9465: WARNING: passing password on command line is deprecated


ASMCMD [+DATA/IBRAC/PWDFILE] > ls -s
Block_Size  Blocks  Bytes  Space  Name
       512      12   6144      0  pwdibrac => +DATA/IBRAC/PASSWORD/pwdibrac.3840.1145270461
ASMCMD [+DATA/IBRAC/PWDFILE] >




??
ASMCMD [+DATA/IBRAC/PWDFILE] > pwget --dbuniquename ibrac
PRCD-1229 : An attempt to access configuration of database ibrac was rejected because its version 12.2.0.1.0 differs from the program version 19.0.0.0.0. Instead run the program from /u01/app/oracle/product/12.2.0/dbhome_1.
A

+DATA/IBRAC/PASSWORD/pwdibrac.3840.1145270461


from database 
srvctl modify database -d ibrac -pwfile +DATA/IBRAC/PASSWORD/pwdibrac.3840.1145270461

srvctl config database -d ibrac|grep -i password
Password file: +DATA/IBRAC/PASSWORD/pwdibrac.3840.1145270461


now you can change any password !!!

SQL> alter user sys identified by sys ;    :)

User altered.



sqlplus sys/sys@'ibrac2' as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Aug 19 10:49:34 2023

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Sat Aug 19 2023 10:49:29 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


another example 

for password file 


[oracle@oragrid ~]$ srvctl config database -d v19mpc -v
Database unique name: v19mpc
Database name: v19mpc
Oracle home: /u01/app/oracle/product/19.0.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/V19MPC/PARAMETERFILE/spfile.273.1236175303
Password file:   <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<  no file 
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA
Services:
OSDBA group:
OSOPER group:
Database instance: v19mpc





[oracle@oragrid ~]$ cd $ORACLE_HOME
[oracle@oragrid dbhome_1]$ cd dbs
[oracle@oragrid dbs]$ ls -ltr
total 16
-rw-r--r--. 1 oracle oinstall 3079 May 14  2015 init.ora
-rw-r-----. 1 oracle asmadmin   24 Jun 17 13:07 lkV19MPC
-rw-r-----. 1 oracle oinstall 2048 Jun 17 13:10 orapwv19mpc
-rw-rw----. 1 oracle asmadmin 1544 Jun 25 09:14 hc_v19mpc.dat
[oracle@oragrid dbs]$ pwd

/u01/app/oracle/product/19.0.0/dbhome_1/dbs
[oracle@oragrid dbs]$ id
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),3003(asmadmin),3004(asmdba),54322(dba) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023


as a grid user !!!

[oracle@oragrid dbs]$ logout
[root@oragrid ~]# su - grid
[grid@oragrid ~]$ asmcmd -p



[grid@oragrid ~]$ cat /etc/oratab
#Backup file is  /u01/app/grid/crsdata/oragrid/output/oratab.bak.oragrid.grid line added by Agent
#



# This file is used by ORACLE utilities.  It is created by root.sh
# and updated by either Database Configuration Assistant while creating
# a database or ASM Configuration Assistant while creating ASM instance.

# A colon, ':', is used as the field terminator.  A new line terminates
# the entry.  Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
#   $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third field indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
#+ASM:/u01/app/19.0.0/grid:N            # line added by Agent
+ASM:/u01/app/19.31.0/grid:N            # line added by Agent
v19mpc:/u01/app/oracle/product/19.0.0/dbhome_1:N                # line added by Agent

[grid@oragrid ~]$ . oraenv
ORACLE_SID = [grid] ? +ASM
The Oracle base has been set to /u01/app/grid

[grid@oragrid ~]$ asmcmd -p
ASMCMD [+] > pwd
+
ASMCMD [+] > pwcopy --dbuniquename v19mpc '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwv19mpc' '+DATA'
copying /u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwv19mpc -> +DATA/orapwv19mpc
ASMCMD [+] >



ASMCMD [+] > pwcopy --dbuniquename v19mpc '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwv19mpc' '+DATA'
copying /u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwv19mpc -> +DATA/orapwv19mpc

ASMCMD [+] >  pwget --dbuniquename v19mpc
+DATA/orapwv19mpc

ASMCMD [+] >




srvctl modify database -d v19mpc -pwfile +DATA/orapwv19mpc


 su - oracle
 orcl  /oracle/db/ohome




[oracle@oragrid ~]$ setsid
 1-       +ASM
 2-     v19mpc

Select the Oracle SID with given number [1]:
2
Your profile configured for v19mpc with information below:

The Oracle base remains unchanged with value /u01/app/oracle






[oracle@oragrid ~]$ srvctl modify database -d v19mpc -pwfile +DATA/orapwv19mpc
[oracle@oragrid ~]$ srvctl config database -d v19mpc -v
Database unique name: v19mpc
Database name: v19mpc
Oracle home: /u01/app/oracle/product/19.0.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/V19MPC/PARAMETERFILE/spfile.273.1236175303
Password file: +DATA/orapwv19mpc
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA
Services:
OSDBA group:
OSOPER group:
Database instance: v19mpc
[oracle@oragrid ~]$






orapwd describe file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID}

Password file Description : format=12.2
Data Guard Physical Standby Changing the SYS password when a broker configuration exists (Doc ID 1199943.1)