Fixing "ORA-02000: missing IDENTIFIED" issue while trying to create TDE key for a 12.1.0.2 PDB database

 

Addressing "ORA-02000: missing IDENTIFIED keyword" in 12.1.0.2 with July 202 BP:

Today I was working on enabling TDE in one of our 12.1.0.2 Multitenant database. when I was trying to create TDE key for the PDB, TLEDPDB database, I was getting "ORA-02000: missing IDENTIFIED keyword".

Looks like we have a bug for this issue and we have to apply one-off patch(27487279) to fix the issue.

My Environment: 

Hostname         :   vmrac-node1 
                       vmrac-node2

CDB database    : SAMSACDB
PDB database    : TLEDPDB
Patch level         : 2020 July BP.

Below is the issue.

[oracle@vmrac-node1 admin]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 18 09:19:17 2021
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL>set lines 200

select * from v$encryption_wallet;

WRL_TYPE            WRL_PARAMETER      STATUS          WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID
------------------- ------------------------- ------------------------ ------------------- --------- --------- ----------
ASM                 +DATAC1/samsacdb/WALLET/  OPEN   AUTOLOGIN  SINGLE    NO                 0

SQL> show pdbs

    CON_ID CON_NAME                  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TLEDPDB                         MOUNTED

SQL> alter pluggable database TLEDPDB open instances=all;
Pluggable database altered.

SQL> alter pluggable database TLEDPDB save state;   
Pluggable database altered.

(FYI....Above command is to save the state so that when we bounce the CDB database, this PDB database will open in READ WRITE mode) 

Connect to the PDB and try to create master KEY for the PDB database.

SQL> alter session set container=TLEDPDB;
Session altered.

SQL>set lines 200
select * from v$encryption_wallet;

WRL_TYPE            WRL_PARAMETER         STATUS         WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID
------------------- ------------------------- ------------------------ ------------------- --------- --------- ----------
ASM     +DATAC1/samsacdb/WALLET/  OPEN_NO_MASTER_KEY  AUTOLOGIN    SINGLE    UNDEFINED      0

SQL> ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY Welcome1 WITH BACKUP;

ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY Welcome1 WITH BACKUP
                                  *

ERROR at line 1:
ORA-02000: missing IDENTIFIED keyword

Tried again to see if there is any syntax error, but everything was good.

SQL> ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY Welcome1 WITH BACKUP;
ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY Welcome1 WITH BACKUP
                                 *
ERROR at line 1:
ORA-02000: missing IDENTIFIED keyword
==================================================
To Fix the issue, we need to apply the below patch and retry to create the master key in PDB. Please see below.

Patch : p27487279_12102200714ProactiveBP_Linux-x86-64

I downloaded and applied this patch in both eh nodes as below.
==================================================

[oracle@vmrac-node1 27487279]$ opatch apply

Oracle Interim Patch Installer version 12.2.0.1.24
Copyright (c) 2021, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/12.1.0.2/db_1
Central Inventory : /u01/app/oraInventory
from           : /u01/app/oracle/product/12.1.0.2/db_1/oraInst.loc
OPatch version    : 12.2.0.1.24
OUI version       : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12.1.0.2/db_1/cfgtoollogs/opatch/opatch2021-03-15_14-32-21PM_1.log

Verifying environment and performing prerequisite checks...

OPatch continues with these patches:   27487279
Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/12.1.0.2/db_1')
Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '27487279' to OH '/u01/app/oracle/product/12.1.0.2/db_1'
Patching component oracle.rdbms, 12.1.0.2.0...
Patch 27487279 successfully applied.
Log file location: /u01/app/oracle/product/12.1.0.2/db_1/cfgtoollogs/opatch/opatch2021-03-15_14-32-21PM_1.log

OPatch succeeded.

[oracle@vmrac-node1 27487279]$ pwd

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

Now If I try to create master key for the PDB, it works :)

[oracle@vmrac-node1 12.1]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 18 09:44:41 2021
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing options

SQL> show pdbs

   CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                     READ ONLY  NO
         3 TLEDPDB                       READ WRITE NO

SQL> alter session set container=TLEDPDB;
Session altered.

SQL> ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY Welcome1 WITH BACKUP;
keystore altered.

SQL>

It worked now without any issues.

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

Thanks
Sambaiah Sammeta