23ai, DBCA and TDE

Leave a reply

With the 23ai it is possible to create a database protected with TDE from the beginning. In a previous post, for OCI Cloud and ExaCC, was already described what are the options and what can be used. Here, let’s discuss other options, and troubleshoot some other details.

OCI Cloud and ExaCC

To contextualize. The OCI, by default, requires that databases are encrypted. When calling the dbca for 23ai, it detects that it is executing in the cloud, and forces it to use TDE. And since ExaCC is considered cloud, it is needed there as well. The previous post already described this in detail.

23ai + DBCA + TDE

When running the 23ai on-prem more options are available. The examples below were made using the Free Edition of 23ai, but when the full release of 23ai will be available, the options will be the same.

There are a lot of options for dbca in silent mode at 23ai:

[oracle@exxc05db01-]$ dbca -silent -createDatabase -help
        -createDatabase - Command to Create a database.
                -responseFile | (-gdbName -templateName)
                -responseFile - <Fully qualified path for a response file>
                -gdbName <Global database name>
                -templateName <Specify an existing template in default location or the complete template path for DB Creation or provide a new template name for template creation>
                [-adminManaged | -managementPolicy]
                [-adminManaged <Admin managed database, this is default option>]
                [-managementPolicy <AUTOMATIC | RANK> Database management Policy, default value is AUTOMATIC]
                [-characterSet <Character set for the database>]
                [-configureTDE <true | false> Specify true to configure TDE wallet]
                        [-encryptPDBTablespaces <Specify ALL to encrypt all Tablespaces or A comma separated list of name:value pairs with tablespace encryption to true/false. For example SYSTEM:true,SYSAUX:false>]
                        [-encryptTablespaces <Specify ALL to encrypt all Tablespaces or A comma separated list of name:value pairs with tablespace encryption to true/false. For example SYSTEM:true,SYSAUX:false>]
                        [-pdbTDEPassword <Specify password for PDB TDE wallet>]
                        [-primaryDBTdeWallet <Specify the location for TDE wallet of primary database>]
                        [-sourcePdbTDEPassword <Specify password for source PDB TDE wallet and it is used only in creation of PDB from existing PDB which has TDE wallet>]
                        [-sourceTdeWalletPassword <Specify password for source database TDE wallet>]
                        [-tdeAlgorithm <Specify the TDE Algorithm Type.>]
                        [-tdeWalletLoginType <Specify the TDE Wallet Login Type, PASSWORD | AUTO_LOGIN  | LOCAL_AUTO_LOGIN. Default is PASSWORD for SI and AUTO_LOGIN is default for RAC]
                        [-tdeWalletModeForPDB <Type of keystore, either UNITED or ISOLATED. Default is UNITED>]
                        [-tdeWalletPassword <Specify password for TDE wallet>]
                        [-tdeWalletPathInTarFile <value>]
                        [-tdeWalletRoot <Specify the location for TDE wallet root init parameter>]
                [-createListener <Create a new listener to register your database. Specify in format, LISTENER_NAME:PORT>]
…
…

The options are linked to the encryption algorithm to be used, the TDE password, the TDE type, and what is desired to be encrypted.  The most common to be used will be:

  • configureTDE: To use or not TDE.
  • tdeWalletRoot: Path where the wallet will be created (for RAC, needs to be accessible for all instances).
  • tdeWalletLoginType: Type of wallet (described in details below).
  • tdeWalletPassword: Wallet password.
  • encryptPDBTablespaces: To define whether PDB will be encrypted or not.
  • encryptTablespaces: If encrypt all tablespaces or not (including SYSTEM/SYSAUX/UNDO).

Whatever the wallet type to be used, the first thing is to create the directory where the wallet will be saved. For RAC, this path needs to be shared for all instances of the cluster:

[oracle@o23fdc21 ~]$ mkdir /opt/oracle/admin/FREE/wallet -p
[oracle@o23fdc21 ~]$

AUTO_LOGIN

The most used option is the AUTO_LOGIN type, and dbca can be called like this:

[oracle@o23fdc21 ~]$ dbca -silent -createDatabase -templateName FREE_Database.dbc -gdbName FREE -adminManaged -sid FREE -sysPassword oracle23ai -systemPassword oracle23ai -createAsContainerDatabase TRUE -useLocalUndoForPDBs TRUE -characterSet AL32UTF8 -emConfiguration NONE -storageType FS -datafileDestination /opt/oracle/oradata/ -databaseConfigType SINGLE -configureTDE TRUE -tdeWalletRoot /opt/oracle/admin/FREE/wallet -tdeWalletLoginType AUTO_LOGIN -TdeWalletPassword 'fdertyhj4567890h32aghte2457AAABB'  -encryptPDBTablespaces ALL -encryptTablespaces ALL
[WARNING] [DBT-06208] The 'SYS' password entered does not conform to the Oracle recommended standards.
   CAUSE:
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
   ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
[WARNING] [DBT-06208] The 'SYSTEM' password entered does not conform to the Oracle recommended standards.
   CAUSE:
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
   ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
Prepare for db operation
10% complete
Copying database files
12% complete
40% complete
Creating and starting Oracle instance
42% complete
46% complete
47% complete
51% complete
55% complete
60% complete
Completing Database Creation
66% complete
69% complete
70% complete
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at:
 /opt/oracle/cfgtoollogs/dbca/FREE.
Database Information:
Global Database Name:FREE
System Identifier(SID):FREE
Look at the log file "/opt/oracle/cfgtoollogs/dbca/FREE/FREE0.log" for further details.
[oracle@o23fdc21 ~]$

Bellow is possible to notice that the wallet was created as requested, and since specified the encryptTablespaces equals ALL, everything is encrypted:

[oracle@o23fdc21 ~]$ sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - Production on Sun Jul 14 19:42:28 2024
Version 23.4.0.24.05

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


Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05

SQL> set linesize 255
SQL> select TABLESPACE_NAME, ENCRYPTED, con_id from cdb_tablespaces order by con_id, TABLESPACE_NAME;

TABLESPACE_NAME                ENC     CON_ID
------------------------------ --- ----------
SYSAUX                         YES          1
SYSTEM                         YES          1
TEMP                           YES          1
UNDOTBS1                       YES          1
USERS                          YES          1

SQL> exit
Disconnected from Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05
[oracle@o23fdc21 ~]$

LOCAL_AUTO_LOGIN

Update 13/Aug: The information below was updated. Peter Wahl, PM from TDE and Oracle Key Vault nicely contacted me to clarify the information.

The next wallet type is the LOCAL_AUTO_LOGIN wallet, which creates a special TDE wallet that can be used only on the computer that created it (more info in the Security Guide doc).

So, the information from DBCA (The specified TDE keystore type LOCAL_AUTO_LOGIN is not supported for SYSTEM tablespaces encryption in root container (CDB$ROOT)) is wrong and is a bug caused by the dbca itself. It will be fixed in a future release of 23ai. It is possible (and documented here) since version 21c. For RAC (since they need to share the same wallet), we cannot use local auto-login wallet.

[oracle@o23fdc21 ~]$ dbca -silent -createDatabase -templateName FREE_Database.dbc -gdbName FREE -adminManaged -sid FREE -sysPassword oracle23ai -systemPassword oracle23ai -createAsContainerDatabase TRUE -useLocalUndoForPDBs TRUE -characterSet AL32UTF8 -emConfiguration NONE -storageType FS -datafileDestination /opt/oracle/oradata/ -databaseConfigType SINGLE -configureTDE TRUE -tdeWalletRoot /opt/oracle/admin/FREE/wallet -tdeWalletLoginType LOCAL_AUTO_LOGIN -TdeWalletPassword 'fdertyhj4567890h32aghte2457AAABB'  -encryptPDBTablespaces ALL -encryptTablespaces ALL
[WARNING] [DBT-06208] The 'SYS' password entered does not conform to the Oracle recommended standards.
   CAUSE:
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
   ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
[WARNING] [DBT-06208] The 'SYSTEM' password entered does not conform to the Oracle recommended standards.
   CAUSE:
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
   ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
[FATAL] [DBT-08117] The specified TDE keystore type LOCAL_AUTO_LOGIN is not supported for SYSTEM tablespaces encryption in root container (CDB$ROOT).
   ACTION: Either specify the auto-login TDE keystore type or choose not to encrypt SYSTEM tablespaces for root container(CDB$ROOT).
[oracle@o23fdc21 ~]$

So, the option (to bypass the dbca bug) to create the database is to remove the encryptTablespaces option:

[oracle@o23fdc21 ~]$ dbca -silent -createDatabase -templateName FREE_Database.dbc -gdbName FREE -adminManaged -sid FREE -sysPassword oracle23ai -systemPassword oracle23ai -createAsContainerDatabase TRUE -useLocalUndoForPDBs TRUE -characterSet AL32UTF8 -emConfiguration NONE -storageType FS -datafileDestination /opt/oracle/oradata/ -databaseConfigType SINGLE -configureTDE TRUE -tdeWalletRoot /opt/oracle/admin/FREE/wallet -tdeWalletLoginType LOCAL_AUTO_LOGIN -TdeWalletPassword 'fdertyhj4567890h32aghte2457AAABB'  -encryptPDBTablespaces ALL
[WARNING] [DBT-06208] The 'SYS' password entered does not conform to the Oracle recommended standards.
   CAUSE:
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
   ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
[WARNING] [DBT-06208] The 'SYSTEM' password entered does not conform to the Oracle recommended standards.
   CAUSE:
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
   ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
Prepare for db operation
10% complete
Copying database files
12% complete
40% complete
Creating and starting Oracle instance
42% complete
46% complete
47% complete
51% complete
55% complete
60% complete
Completing Database Creation
66% complete
69% complete
70% complete
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at:
 /opt/oracle/cfgtoollogs/dbca/FREE.
Database Information:
Global Database Name:FREE
System Identifier(SID):FREE
Look at the log file "/opt/oracle/cfgtoollogs/dbca/FREE/FREE1.log" for further details.
[oracle@o23fdc21 ~]$

PASSWORD

The last TDE wallet type is the traditional PASSWORD. As before, can’t be used together with the option encryptTablespaces equal ALL:

adminManaged -sid FREE -sysPassword oracle23ai -systemPassword oracle23ai -createAsContainerDatabase TRUE -useLocalUndoForPDBs TRUE -characterSet AL32UTF8 -emConfiguration NONE -storageType FS -datafileDestination /opt/oracle/oradata/ -databaseConfigType SINGLE -configureTDE TRUE -tdeWalletRoot /opt/oracle/admin/FREE/wallet -tdeWalletLoginType PASSWORD -TdeWalletPassword 'fdertyhj4567890h32aghte2457AAABB'  -encryptPDBTablespaces ALL -encryptTablespaces ALL
[WARNING] [DBT-06208] The 'SYS' password entered does not conform to the Oracle recommended standards.
   CAUSE:
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
   ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
[WARNING] [DBT-06208] The 'SYSTEM' password entered does not conform to the Oracle recommended standards.
   CAUSE:
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
   ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
[FATAL] [DBT-08117] The specified TDE keystore type PASSWORD is not supported for SYSTEM tablespaces encryption in root container (CDB$ROOT).
   ACTION: Either specify the auto-login TDE keystore type or choose not to encrypt SYSTEM tablespaces for root container(CDB$ROOT).
[oracle@o23fdc21 ~]$

So, the option is to remove the parameter and call the dbca:

[oracle@o23fdc21 ~]$ dbca -silent -createDatabase -templateName FREE_Database.dbc -gdbName FREE -adminManaged -sid FREE -sysPassword oracle23ai -systemPassword oracle23ai -createAsContainerDatabase TRUE -useLocalUndoForPDBs TRUE -characterSet AL32UTF8 -emConfiguration NONE -storageType FS -datafileDestination /opt/oracle/oradata/ -databaseConfigType SINGLE -configureTDE TRUE -tdeWalletRoot /opt/oracle/admin/FREE/wallet -tdeWalletLoginType PASSWORD -TdeWalletPassword 'fdertyhj4567890h32aghte2457AAABB'  -encryptPDBTablespaces ALL
[WARNING] [DBT-06208] The 'SYS' password entered does not conform to the Oracle recommended standards.
   CAUSE:
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
   ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
[WARNING] [DBT-06208] The 'SYSTEM' password entered does not conform to the Oracle recommended standards.
   CAUSE:
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
   ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
Prepare for db operation
10% complete
Copying database files
12% complete
40% complete
Creating and starting Oracle instance
42% complete
46% complete
47% complete
51% complete
55% complete
60% complete
Completing Database Creation
66% complete
69% complete
70% complete
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at:
 /opt/oracle/cfgtoollogs/dbca/FREE.
Database Information:
Global Database Name:FREE
System Identifier(SID):FREE
Look at the log file "/opt/oracle/cfgtoollogs/dbca/FREE/FREE2.log" for further details.
[oracle@o23fdc21 ~]$

And is possible to notice the TDE was created correctly:

[oracle@o23fdc21 ~]$ sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - Production on Sun Jul 14 23:09:56 2024
Version 23.4.0.24.05

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


Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05

SQL> set linesize 255
SQL> select con_id, wallet_type, status from v$encryption_wallet;

    CON_ID WALLET_TYPE          STATUS
---------- -------------------- ------------------------------
         1 PASSWORD             OPEN
         2 PASSWORD             OPEN

SQL> select TABLESPACE_NAME, ENCRYPTED, con_id from cdb_tablespaces order by con_id, TABLESPACE_NAME;

TABLESPACE_NAME                ENC     CON_ID
------------------------------ --- ----------
SYSAUX                         NO           1
SYSTEM                         NO           1
TEMP                           NO           1
UNDOTBS1                       NO           1
USERS                          YES          1

SQL> exit
Disconnected from Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05
[oracle@o23fdc21 ~]$

Summary

Now with 23ai is it possible to create the database, since the beginning, using TDE to encrypt it. All the wallet types are supported but some details are important to be cleared. Cloud databases are more restricted because it forced to use TDE. But on-prem it is not a requirement. In the end, it is important to know how to use the dbca properly for TDE.

 

Disclaimer: “The postings on this site are my own and don’t necessarily represent my actual employer positions, strategies, or opinions. The information here was edited to be useful for general purposes, and specific data and identifications were removed to allow reach the generic audience and to be useful for the community. Post protected by copyright.”

 

Leave a Reply

Your email address will not be published. Required fields are marked *