How to Create Password File on ASM diskgroup

1) Make sure compatible.asm at least 12.1.

SQL> SELECT NAME,VALUE,GROUP_NUMBER FROM V$ASM_ATTRIBUTE where name like '%compatible%'

NAME                                     VALUE                                                                  GROUP_NUMBER
---------------------------------------- ---------------------------------------------------------------------- ------------
compatible.asm                           19.0.0.0.0                                                                        1
compatible.rdbms                         11.2.0.4                                                                          1
compatible.advm                          19.0.0.0                                                                          1
compatible.asm                           19.0.0.0.0                                                                        2
compatible.rdbms                         11.2.0.4.0                                                                        2
compatible.advm                          19.0.0.0.0                                                                        2

6 rows selected.

2) Create password by using ‘pwcreate’.

ASMCMD> pwcreate --dbuniquename TESTDB '+datac1/TESTDB/password/pwdtestdb'
Enter password: *********
ASMCMD>

3) Verify the password by :

$ crsctl stat res ora.testdb.db -f | grep PWFILE
PWFILE=+DATAC1/TESTDB/PASSWORD/pwdtestdb

For 11.2, there is no support for this feature.

$ crsctl stat res ora.testdb.db -f | grep PWFILE
PWFILE=

“ORA-28367: wallet does not exist” When Opening TDE Wallet

ORA-28367 error appears when opening the TDE wallet/keystore.

In 11c:

SQL>ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY "software_wallet_password"; 
ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY "software_wallet_password"
*
ERROR at line 1:
ORA-28367: wallet does not exist

in 12c:

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "software_keystore_password";
 ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "software_keystore_password"
*
ERROR at line 1:
ORA-28367: wallet does not exist

CAUSE

1) Please check sqlnet.ora in place.

2) Check the format of “ENCRYPTION_WALLET_LOCATION” in sqlnet.ora right as the following:

ENCRYPTION_WALLET_LOCATION=
    (SOURCE=
      (METHOD=FILE)
      (METHOD_DATA=(DIRECTORY=/var/opt/oracle/dbaas_acfs/TESTDB/tde_wallet)))

ORA-00392 from ALTER DATABASE OPEN RESETLOGS

After restoring the database, the following errors appear when opening resetlogs:

SQL>  alter database open resetlogs;
 alter database open resetlogs
*
ERROR at line 1:
ORA-00392: log 2 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 2 thread 1: '+DATAC1/TESTDB/ONLINELOG/redo02.log'
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
         1          1          0   52428800        512          1 YES CLEARING               1682491 26-JUL-21      1682509 26-JUL-21          0
         3          1          0   52428800        512          1 YES CLEARING               1682488 26-JUL-21      1682491 26-JUL-21          0
         2          1          0   52428800        512          1 NO  CLEARING_CURRENT       1682509 26-JUL-21   2.8147E+14                    0

CAUSE

For some reason, “alter database open resetlogs” is abnormally abrupted leaving the redo log status as CLEARING/CLEARING_CURRENT in control file.

SOLUTION

SQL> alter database clear unarchived logfile group 1 ;
Database altered.

SQL>alter database clear unarchived logfile group 2 ;
Database altered.

SQL>alter database clear unarchived logfile group 3 ;
Database altered.
SQL>alter database open resetlogs;
Database altered.

If the above method is not working , then need recreate the control file with RESETLOGSĀ option.

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/tmp/control_file.sql' resetlogs ;

Check the above control_file.sql to make sure all online redo logs directories exist.

SQL> STARTUP FORCE NOMOUNT
SQL> @/tmp/control_file.sql

controlfile created
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL ;

Type <CANCEL> when prompted

Finally open database resetlogs successfully.

SQL> ALTER DATABASE OPEN RESETLOGS ;
Database altered.