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




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)