Oracle Database

Ensure that the HSM is set up, initialized, provisioned, and ready for deployment. For more information, refer to Luna HSM documentation.

Create a partition that will be later on used by Oracle Key Vault.

Create and exchange certificate between the Luna Network HSM and client system. Register client and assign partition to create an NTLS connection.

Initialize Crypto Officer and Crypto User roles for the registered partition.

Run the following command to verify that the partition has been successfully registered and configured:

/usr/safenet/lunaclient/bin/lunacm 

Upon successful execution, you should observe an output similar to the example provided below:

LunaCM v7.2.0-220. Copyright (c) 2006-2017 SafeNet. 
Available HSMs:
Slot Id ->                           0
Label ->                             Oracle
Serial Number ->                     1238712343066
Model ->                             LunaSA 7.2.0
Firmware Version ->                  7.2.0
Configuration ->                     Luna User Partition With SO (PED) Key Export With Cloning Mode
Slot Description ->                  Net Token Slot

Refer to Luna HSM documentation for detailed steps on creating NTLS connection, initializing the partitions, and assigning various user roles.

Ensure that you possess sudo privileges on the client workstation.

Add a user to the hsmusers group using the command:

sudo gpasswd --add <username> hsmusers

Replace username with the actual username you want to include in the hsmusers group.

Confirm that you hold sudo privileges on the client workstation.

Eliminate a user from the hsmusers group using the command:

sudo gpasswd --add <username> hsmusers

Replace username with the specific username you want to exclude from the hsmusers group. To observe the changes, you will need to log in again.

Any user you remove will retain access to the HSM device until the client workstation is rebooted.

Transfer the downloaded .zip file to your client workstation using pscp, scp, or other secure means

This integration has been certified on the RHEL platform.

Extract the .zip file into a directory on your client workstation.

Extract or untar the appropriate client package for your operating system. Do not extract to a new subdirectory; place the files in the client install directory.

tar -xvf cvclient-min.tar

Run the setenv script to create a new configuration file containing information required by the Luna Cloud HSM service.

source ./setenv

To add the configuration to an already installed UC client, use the –addcloudhsm option when running the setenv script.

Run the LunaCM utility and verify that the Cloud HSM service is listed.

If your organization requires non-FIPS algorithms for your operations, ensure that the Allow non-FIPS approved algorithms check box is checked. For more information, refer to Supported Mechanisms.

Set up Luna HSM client for TDE

Configure a master encryption key for HSM-based encryption

Work with pluggable databases

Store Oracle Database Secrets in a hardware keystore

Copy the Luna HSM PKCS#11 library.

• Linux/Solaris/AIX: Copy the Luna HSM PKCS#11 library to this specific directory: /opt/oracle/extapi/[32,64]/hsm/{Vendor}/{Version}/libXX.ext. For example, if you're on 64-bit RHEL, the path could be like this: /opt/oracle/extapi/64/hsm/safenet/7.x.x/libCryptoki2_64.so.

• Windows: On Windows, just drop the Luna HSM PKCS#11 library into this directory: %SYSTEMDRIVE%\oracle\extapi\[32,64]\hsm\{Vendor}\{Version}\libXX.ext. For instance, the resulting path on a Windows system could resemble the following: C:\oracle\extapi\64\hsm\safenet\7.x.x\libCryptoki2_64.dll.

• Parameters:

  [32,64]: Specify whether the binary is 32-bit or 64-bit.

  {Vendor}: Enter the vendor's name that supplies the library.

  {Version}: Provide the version number of the library (in the format [number.number.number]).

  libXX.ext: Swap this with the actual extension of the library file.

Grant read/write permissions to the Oracle user and export these variables:

export ORACLE_SID=orcl
export ORACLE_BASE=/u01/app/oracle  # (Replace with wherever your Oracle is installed)
export ORACLE_HOME=$ORACLE_BASE/product/<OracleDB_Version>/dbhome_1
export PATH=$PATH:$ORACLE_HOME/bin
export TNS_ADMIN=$ORACLE_HOME/network/admin

Remember, only one PKCS#11 library is supported at a time.

Generate master encryption key directly on the HSM

Migrate master encryption key from software keystore to hardware keystore

Configure auto-login for hardware keystore

Configure auto-reconnect for hardware keystore

Refer to Appendix B and complete the hardware keystore configuration based on your installed Oracle Database version.

• Configure hardware keystore for Oracle Database 12c

• Configure hardware keystore for Oracle Database 18c and 19c

Start the database using the following command:

$ sqlplus / as sysdba

If the database is not yet started, initiate it with:

SQL> startup;

Grant the ADMINISTER KEY MANAGEMENT or SYSKM privilege to both the SYSTEM user and any user you intend to use.

SQL> GRANT ADMINISTER KEY MANAGEMENT TO SYSTEM;
SQL> COMMIT;

Connect to the database as the SYSTEM user, using the password set during Oracle installation (default: temp123#).

SQL> CONNECT SYSTEM/<password>

Execute the following SQL statement to open the hardware keystore:

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY <hsm_partition_password>;

Set the master encryption key in the hardware keystore:

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY <hsm_partition_password>;

Verify the generated keys on the HSM by executing the partition contents in lunacm.

If network latency is observed and the heartbeat check fails with Luna Cloud HSM, refer to Troubleshooting Tips (Problem 4).

Create a CUSTOMERS table:

SQL> CREATE TABLE CUSTOMERS (ID NUMBER(5), NAME VARCHAR(42), CREDIT_LIMIT NUMBER(10));

Enter values in the CUSTOMERS table:

SQL> INSERT INTO CUSTOMERS VALUES (001, 'George Bailey', 10000);
SQL> INSERT INTO CUSTOMERS VALUES (002, 'Denial Vettory', 20000);
SQL> INSERT INTO CUSTOMERS VALUES (003, 'MS Dhoni', 30000);
SQL> INSERT INTO CUSTOMERS VALUES (004, 'Shahid Afridi', 40000);

Encrypt the CREDIT_LIMIT column:

SQL> ALTER TABLE CUSTOMERS MODIFY (CREDIT_LIMIT ENCRYPT);

Retrieve encrypted values (TDE):

SQL> SELECT CREDIT_LIMIT FROM CUSTOMERS;

List encrypted columns in the database:

SQL> SELECT * FROM DBA_ENCRYPTED_COLUMNS;

View information about the hardware keystore:

SQL> SELECT * FROM V$ENCRYPTION_WALLET;

Create an encrypted tablespace:

SQL> CREATE TABLESPACE SECURESPACE DATAFILE '/u01/app/oracle/oradata/orcl/SECURE01.DBF' SIZE 150M ENCRYPTION DEFAULT STORAGE (ENCRYPT);

Create a table in the tablespace:

SQL> CREATE TABLE EMPLOYEE (ID NUMBER(5), NAME VARCHAR(42), SALARY NUMBER(10)) TABLESPACE SECURESPACE;

Insert values in the EMPLOYEE table:

SQL> INSERT INTO EMPLOYEE VALUES (001,'JOHN SMITH',15000);
SQL> INSERT INTO EMPLOYEE VALUES (002,'SCOTT TIGER',25000);
SQL> INSERT INTO EMPLOYEE VALUES (003,'DIANA HAYDEN',35000);

Display the contents of the EMPLOYEE table:

SQL> SELECT * FROM EMPLOYEE;

Close the keystore:

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY <hsm_partition_password>;

Attempt to display contents after keystore closure:

SQL> SELECT * FROM EMPLOYEE;

If the keystore is closed, an error (ORA-28365: wallet is not open) will indicate that you cannot list the contents of the EMPLOYEE table.

Open the keystore and exit:

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY <hsm_partition_password>;
SQL> EXIT

Connect to the database instance as a user with the ADMINISTER KEY MANAGEMENT or SYSKM privilege.

SQL> CONNECT SYSTEM/<password>;

Open the keystore to perform key management operations.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY <hsm_partition_password>;

Create the TDE master encryption key that will be activated later:

SQL> ADMINISTER KEY MANAGEMENT CREATE KEY FORCE KEYSTORE IDENTIFIED BY <hsm_partition_password>;

Retrieve the IDs of all created keys for reference.

SQL> SELECT KEY_ID FROM V$ENCRYPTION_KEYS;

Connect to the database instance using a user with the ADMINISTER KEY MANAGEMENT or SYSKM privilege.

SQL> CONNECT SYSTEM/<password>;

Ensure the keystore is open to perform key management operations.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY <hsm_partition_password>;

Retrieve the key identifier from the V$ENCRYPTION_KEYS view.

SQL> SELECT KEY_ID FROM V$ENCRYPTION_KEYS;

Utilize the key identifier obtained to activate the TDE master encryption key.

SQL> ADMINISTER KEY MANAGEMENT USE KEY 'key_identifier_from_V$ENCRYPTION_KEYS' IDENTIFIED BY <hsm_partition_password>;

Confirm the key currently in use by executing the following command:

SQL> SELECT KEY_ID FROM V$ENCRYPTION_KEYS WHERE ACTIVATION_TIME = (SELECT MAX(ACTIVATION_TIME) FROM V$ENCRYPTION_KEYS WHERE ACTIVATING_DBID = (SELECT DBID FROM V$DATABASE));

Connect to the database instance using a user with the ADMINISTER KEY MANAGEMENT or SYSKM privilege.

SQL> CONNECT SYSTEM/<password>;

Initiate the hardware keystore for key management operations.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY <hsm_partition_password>;

Set the TDE master encryption key after the hardware keystore is open.

SQL> ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY <hsm_partition_password>;

Create a demonstration tablespace and table for demonstration purposes:

SQL> CREATE TABLESPACE TESTTBSPACE DATAFILE '/u01/app/oracle/oradata/CDB1/UNSECURE01.DBF' SIZE 150M;
SQL> CREATE TABLE EMPLOYEE (ID NUMBER(5), NAME VARCHAR(42), SALARY NUMBER(10)) TABLESPACE TESTTBSPACE;
SQL> INSERT INTO EMPLOYEE VALUES (001,'JOHN SMITH',15000);
SQL> INSERT INTO EMPLOYEE VALUES (002,'SCOTT TIGER',25000);
SQL> INSERT INTO EMPLOYEE VALUES (003,'DIANA HAYDEN',35000);

Ensure the COMPATIBLE initialization parameter is correctly set for the database version.

SQL> SHOW PARAMETER COMPATIBLE;

Encrypt the tablespace with online conversion.

SQL> ALTER TABLESPACE TESTTBSPACE ENCRYPTION ONLINE USING 'AES192' ENCRYPT FILE_NAME_CONVERT = ('UNSECURE01.DBF','SECURE01.DBF');

If you are using Oracle Database 12c and 18c, use the FILE_NAME_CONVERT clause for non-Oracle managed files.

As an Oracle Database 19c user, if you omit the FILE_NAME_CONVERT clause, Oracle Database internally assigns an auxiliary file name and later renames it back to the original name.

In an Oracle-managed files configuration, the creation of new data files is automated.

Retrieve data from the encrypted table.

SQL> SELECT * FROM EMPLOYEE;

Close the keystore to secure the encrypted data.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY <hsm_partition_password>;

Try to fetch data after closing the wallet; nothing is displayed as the wallet is closed.

SQL> SELECT * FROM EMPLOYEE;

List all tablespaces that are encrypted.

SQL> SELECT TABLESPACE_NAME, ENCRYPTED FROM DBA_TABLESPACES;

Open the keystore, using a user with the ADMINISTER KEY MANAGEMENT or SYSKM privilege.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY <hsm_partition_password>;

Ensure the COMPATIBLE initialization parameter aligns with the database version.

SQL> SHOW PARAMETER COMPATIBLE;

Execute the ALTER TABLESPACE SQL statement with the DECRYPT clause to decrypt the tablespace with online conversion.

SQL> ALTER TABLESPACE TESTTBSPACE ENCRYPTION ONLINE DECRYPT FILE_NAME_CONVERT = ('SECURE01.DBF','UNSECURE01.DBF');

If you are using Oracle Database 12c or 18c, it is essential to utilize the FILE_NAME_CONVERT clause, especially when dealing with non-Oracle managed files.

For Oracle Database 19c users, omitting the FILE_NAME_CONVERT clause prompts the system to internally assign an auxiliary file name, subsequently renaming it back to the original name.

In an Oracle-managed files configuration, new data files are created automatically.

Confirm that the tablespace is no longer encrypted.

SQL> SELECT TABLESPACE_NAME, ENCRYPTED FROM DBA_TABLESPACES;

Check the current encryption algorithm status for the master encryption key using the following SQL query.

SQL> SELECT * FROM V$ENCRYPTED_TABLESPACES;

Avoid concurrent execution of online tablespace rekey operations and master key operations.

Ensure the COMPATIBLE initialization parameter is correctly set based on the database version.

SQL> SHOW PARAMETER COMPATIBLE;

If the tablespace's key version status is NORMAL, proceed with the rekey operation.

SQL> ALTER TABLESPACE UNSECURESPACE ENCRYPTION USING 'AES192' REKEY FILE_NAME_CONVERT = ('SECURE01.DBF', 'SECURE02.DBF');

If you are using Oracle Database 12c or 18c, it is essential to utilize the FILE_NAME_CONVERT clause, especially when dealing with non-Oracle managed files.

For Oracle Database 19c users, omitting the FILE_NAME_CONVERT clause prompts the system to internally assign an auxiliary file name, subsequently renaming it back to the original name.

In an Oracle-managed files configuration, new data files are created automatically.

Confirm the success of the rekey operation.

SQL> SELECT * FROM V$ENCRYPTED_TABLESPACES;

Log in to the database instance as a user granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.

SQL> CONNECT SYSTEM/<password>;

Open the Hardware Keystore.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY <hsm_partition_password>;

After opening the hardware keystore, set the hardware keystore TDE master encryption key.

SQL> ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY <hsm_partition_password>;

Create a tablespace for demonstration.

SQL> CREATE TABLESPACE TESTTBSPACE DATAFILE '/u01/app/oracle/oradata/CDB1/UNSECURE01.DBF' SIZE 150M;

Create a table.

SQL> CREATE TABLE EMPLOYEE (ID NUMBER(5), NAME VARCHAR(42), SALARY NUMBER(10)) TABLESPACE TESTTBSPACE;

Insert values into the table.

SQL> INSERT INTO EMPLOYEE VALUES (001, 'JOHN SMITH', 15000);
SQL> INSERT INTO EMPLOYEE VALUES (002, 'SCOTT TIGER', 25000);
SQL> INSERT INTO EMPLOYEE VALUES (003, 'DIANA HAYDEN', 35000);

Bring the tablespace offline.

SQL> ALTER TABLESPACE TESTTBSPACE OFFLINE NORMAL;

The offline conversion method doesn't use auxiliary disk space or files; it operates directly in-place to the data files. Therefore, perform a full backup of the user tablespace before converting it offline.

Encrypt the tablespace.

SQL> ALTER TABLESPACE TESTTBSPACE ENCRYPTION OFFLINE ENCRYPT;

To encrypt individual data files within a tablespace, run the following command:

SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/CDB1/UNSECURE01.DBF' ENCRYPT;

Bring the tablespace back online.

SQL> ALTER TABLESPACE TESTTBSPACE ONLINE;

Fetch data from the table.

SQL> SELECT * FROM EMPLOYEE;

Close the wallet.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY <hsm_partition_password>;

Fetch data from the table after closing the wallet; nothing is displayed as the wallet is closed.

SQL> SELECT * FROM EMPLOYEE;

List all tables that are encrypted.

SQL> SELECT TABLESPACE_NAME, ENCRYPTED FROM DBA_TABLESPACES;

Bring the tablespace offline.

SQL> ALTER TABLESPACE TESTTBSPACE OFFLINE NORMAL;

As a user with the ADMINISTER KEY MANAGEMENT or SYSKM privilege, open the keystore.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY <hsm_partition_password>;

Run the ALTER TABLESPACE SQL statement to perform the decryption.

SQL> ALTER TABLESPACE TESTTBSPACE ENCRYPTION OFFLINE DECRYPT;

Bring the tablespace back online.

SQL> ALTER TABLESPACE TESTTBSPACE ONLINE;

Verify software-based wallet functionality

Verify the master encryption key for Oracle Database encryption

Verify HSM configuration for database access

Follow the instructions in Appendix B to configure the software keystore based on the installed Oracle Database version.

• Configure software keystore for Oracle Database 12c

• Configure software keystore for Oracle Database 18c and 19c

Launch the database using the following command:

$ sqlplus / as sysdba

If the database is not yet started, initiate it with:

SQL> startup;

Grant the ADMINISTER KEY MANAGEMENT or SYSKM privilege to the SYSTEM user and any additional users as needed.

SQL> GRANT ADMINISTER KEY MANAGEMENT TO SYSTEM;
SQL> COMMIT;

Connect to the database as the system user, using the specified password.

SQL> CONNECT SYSTEM/<password>

The password for system is set during Oracle installation, and all passwords in this document are set to temp123#.

Execute the following SQL statement to create the software keystore:

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE IDENTIFIED BY <software_keystore_password>;

For Oracle Database 12c, use the following syntax: ADMINISTER KEY MANAGEMENT CREATE KEYSTORE 'keystore_location' IDENTIFIED BY <software_keystore_password>;. Ensure keystore_location is the path to the Oracle wallet directory set in the sqlnet.ora file.

Open the software keystore with the specified password:

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY <software_keystore_password>;

Set the master encryption key within the software keystore:

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY <software_keystore_password> WITH BACKUP USING 'backup_identifier';

The WITH BACKUP parameter ensures a backup of the keystore, essential for password-based keystores. Optionally, provide a brief description using the USING clause, enclosed in single quotation marks.

Create a CUSTOMERS table in the database.

SQL> CREATE TABLE CUSTOMERS (ID NUMBER(5), NAME VARCHAR(42), CREDIT_LIMIT NUMBER(10));

Enter values into the CUSTOMERS table.

SQL> INSERT INTO CUSTOMERS VALUES (001, 'George Bailey', 10000);
SQL> INSERT INTO CUSTOMERS VALUES (002, 'Denial Vettory', 20000);
SQL> INSERT INTO CUSTOMERS VALUES (003, 'MS Dhoni', 30000);
SQL> INSERT INTO CUSTOMERS VALUES (004, 'Shahid Afridi', 40000);

Encrypt the 'CREDIT_LIMIT' column of the 'CUSTOMERS' table.

SQL> ALTER TABLE CUSTOMERS MODIFY (CREDIT_LIMIT ENCRYPT);

Retrieve values from the encrypted column in clear text; Transparent Data Encryption automatically decrypts them.

SQL> SELECT CREDIT_LIMIT FROM CUSTOMERS;

List encrypted columns in your database.

SQL> SELECT * FROM DBA_ENCRYPTED_COLUMNS;

View information about the software keystore.

SQL> SELECT * FROM V$ENCRYPTION_WALLET;

Create an encrypted tablespace.

SQL> CREATE TABLESPACE SECURESPACE DATAFILE '/u01/app/oracle/oradata/orcl/SECURE01.DBF' SIZE 150M ENCRYPTION DEFAULT STORAGE (ENCRYPT);

Create a table in the encrypted tablespace.

SQL> CREATE TABLE EMPLOYEE (ID NUMBER(5), NAME VARCHAR(42), SALARY NUMBER(10)) TABLESPACE SECURESPACE;

Insert values into the EMPLOYEE table.

SQL> INSERT INTO EMPLOYEE VALUES (001,'JOHN SMITH',15000);
SQL> INSERT INTO EMPLOYEE VALUES (002,'SCOTT TIGER',25000);
SQL> INSERT INTO EMPLOYEE VALUES (003,'DIANA HAYDEN',35000);

Display the contents of the EMPLOYEE table using the following command:

SQL> SELECT * FROM EMPLOYEE;

Close the keystore.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY <software_keystore_password>;

After closing the keystore, execute the following command to display the contents again:

SQL> SELECT * FROM EMPLOYEE;

Verify keystore closure. If the keystore is closed and an attempt is made to list the contents of the EMPLOYEE table, you will encounter the following error:

ERROR at line 1: ORA-28365: wallet is not open

Open the keystore:

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY <software_keystore_password>;
SQL> EXIT

Follow the steps in Appendix B to set up the keystore for migration from software to hardware based on your Oracle Database version:

• Configure keystore for migration from software to hardware for Oracle Database 12c

• Configure keystore for migration from software to hardware for Oracle Database 18c and 19c

Establish a connection to the database as the SYSTEM user:

SQL> CONNECT SYSTEM/<password>;

Migrate the wallet onto the HSM device.

SQL> ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY IDENTIFIED BY "hsm_partition_pwd" MIGRATE USING <software_keystore_password> WITH BACKUP USING 'backup_identifier';

hsm_partition_pwd is the password for the HSM partition. The MIGRATE USING software_keystore string re-encrypts the Transparent Data Encryption column keys and tablespace keys with the new HSM-based master key. The <software_keystore_password> is the password of the software wallet in step 1.

In case network latency is observed, and heartbeat check fails with Luna Cloud HSM, refer to Troubleshooting Tips, Problem 4.

Return the values in the encrypted column to clear text; Transparent Data Encryption decrypts them automatically, now using the HSM master key.

SQL> SELECT CREDIT_LIMIT FROM CUSTOMERS;

Change the password of the software keystore to to match the HSM partition password, ensuring the software wallet is open.

SQL> ADMINISTER KEY MANAGEMENT ALTER KEYSTORE PASSWORD IDENTIFIED BY <software_keystore_password> SET hsm_partition_pwd WITH BACKUP USING 'backup_identifier';

Open the keystore, enabling access to both the software-based and HSM-based keystores.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY "hsm_partition_pwd";

Close the keystore.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY "hsm_partition_pwd";

Open the keystore, enabling access to both the HSM and the software keystore.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "hsm_partition_pwd";

Check the wallet information using the following command.

SQL> SELECT * FROM V$ENCRYPTION_WALLET;

Change the password back to the initial password for the software-based wallet (if desired) and create an auto-login keystore for the software keystore using the following syntax.

SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE IDENTIFIED BY <software_keystore_password>;

For Oracle Database 12c, use the command ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE 'keystore_location' IDENTIFIED BY <software_keystore_password>; to create an auto-login keystore from the existing keystore, where keystore_location is the path to the Oracle wallet directory set in the sqlnet.ora file.

To restrict the use of the auto-login wallet only to the local system, include LOCAL AUTO_LOGIN instead of AUTO_LOGIN.

Verify the creation of an auto-open software keystore in the Oracle wallet directory specified in the sqlnet.ora file or wallet_root parameter. Two wallets will be present in this directory: ewallet.p12 and cwallet.sso. The latter is the auto-open wallet.

mv ewallet.p12 ewallet.p24

Restart the database and connect to the database as system. Open the HSM keystore; the software wallet will open automatically.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "hsm_partition_pwd";

Close the hardware keystore if it is currently open.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY <hsm_partition_password>;

Refer to Appendix B and complete the software keystore configuration based on the installed Oracle Database version:

• Configure software keystore for Oracle Database 12c

• Configure software keystore for Oracle Database 18c and 19c

Create the software keystore.

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE IDENTIFIED BY <software_keystore_password>;

For Oracle Database 12c, use the following syntax: ADMINISTER KEY MANAGEMENT CREATE KEYSTORE 'keystore_location' IDENTIFIED BY <software_keystore_password>;. Ensure keystore_location is the path to the Oracle wallet directory set in the sqlnet.ora file.

If a software wallet has already been created, skip this step and remove/rename the cwallet.sso file.

Open the software keystore.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY <software_keystore_password>;

Add the HSM password as a client.

SQL> ADMINISTER KEY MANAGEMENT ADD SECRET 'hsm_partition_password' FOR CLIENT 'HSM_PASSWORD' IDENTIFIED BY <software_keystore_password> WITH BACKUP USING 'backup_identifier';

Close the software keystore.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY <software_keystore_password>;

Create auto-login keystore.

SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE IDENTIFIED BY <software_keystore_password>;

For Oracle Database 12c, use the following syntax: ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE 'keystore_location' IDENTIFIED BY <software_keystore_password>;. Ensure keystore_location is the path to the Oracle wallet directory set in the sqlnet.ora file.

To configure the keystore for migrating from software to hardware, follow the steps outlined in Appendix B, based on your specific Oracle Database version:

• Configure keystore for migration from software to hardware for Oracle Database 12c

• Configure keystore for migration from software to hardware for Oracle Database 18c and 19c

Close and reopen the database. The next time a TDE operation executes, the hardware security module auto-login keystore will open automatically.

Restart the database and connect as a system.

Check the wallet information with the following command.

SQL> SELECT * FROM V$ENCRYPTION_WALLET;

Update the /etc/Chrystoki.conf file to configure the Luna HSM to utilize the SHIM library as outlined below:

Chrystoki2 = {
      LibUNIX64 = /usr/safenet/lunaclient/lib/libshim.so;
}

Shim2 = {
     ExtraOptions = fail_zero_slots;
     LibUNIX64 = /usr/safenet/lunaclient/lib/libCryptoki2_64.so;
}

Misc = {
     .
     .
     ApplicationInstance=TDE;
     .
}

Do not modify existing parameters in the MISC section.

Update the Oracle Database to use the SHIM library instead of the original Cryptoki library by removing and replacing the existing files:

$ rm /opt/oracle/extapi/64/hsm/safenet/10.5.1/libCryptoki2_64.so

$ cp /usr/safenet/lunaclient/lib/libshim.so /opt/oracle/extapi/64/hsm/safenet/10.5.1/ 

Ensure that the SHIM library has the correct ownership and permissions to function correctly.

-rwxrwxr-x. 1 oracle oinstall 4924216 Jul 16 13:07 libshim.so

Connect to the database as the system administrator:

$ sqlplus / as sysdba

If the database is not yet running, initiate startup:

SQL> startup;

Configure the heartbeat parameter with the following commands:

SQL> ALTER SYSTEM SET "_heartbeat_period_multiplier"=10 SCOPE=SPFILE;
SQL> ALTER SYSTEM SET "_heartbeat_config"=AUTOCONNECT SCOPE=SPFILE; 

Verify the heartbeat configuration by executing the following command:

SQL> show parameter heart

Restart the database to apply the changes:

$ sqlplus / as sysdba
SQL> shutdown immediate;
SQL> startup;

Edit the tnsnames.ora file to include a new service entry for the PDB. The tnsnames.ora file is typically located in the ORACLE_HOME/network/admin directory. Alternatively, its location can be specified using the TNS_ADMIN environment variable. Ensure that TNS_ADMIN is accurately configured.

Example:

salespdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = salespdb.localdomain)
)
)

Replace salespdb with the new pluggable database name.

Restart the listener service.

$ lsnrctl stop
$ lsnrctl start

For RAC:

$ srvctl stop listener -listener LISTENER
$ srvctl start listener -listener LISTENER

Consult Appendix B and execute the necessary steps for hardware keystore configuration based on your Oracle Database version.

• Configure hardware keystore for Oracle Database 12c

• Configure hardware keystore for Oracle Database 18c and 19c

Initiate a sqlplus session to connect to the PDB.

$ sqlplus / as sysdba

Log in to the database instance using a user with the ADMINISTER KEY MANAGEMENT or SYSKM privilege.

SQL> connect system/<password>

Open the hardware keystore in the CDB$ROOT container.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "<hsm_partition_password>";

Set the master encryption key in the CDB$ROOT container onto HSM.

Skip this step if the master encryption key is already generated onto HSM.

SQL> ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY <hsm_partition_password>;

Connect as SYSDBA.

SQL> CONNECT / AS SYSDBA;

Open the PDB in read-write mode.

SQL> ALTER PLUGGABLE DATABASE <PDB_NAME> OPEN READ WRITE;

In RAC environment, the pluggable database must be open on all RAC nodes.

Set the container to the PDB using SQL command or a connection string.

Using SQL command:

SQL> ALTER SESSION SET CONTAINER=<pdb_name>;

Using a connection string:

SQL> CONNECT SYSTEM/<system_password>@Pluggable Database Service name

Example:

SQL> CONNECT SYSTEM/temp123#@salespdb;

Grant the following privileges to the PDB admin.

SQL> GRANT ADMINISTER KEY MANAGEMENT TO salesadm;
SQL> GRANT CREATE SESSION TO salesadm;
SQL> GRANT CONNECT TO salesadm;
SQL> GRANT DBA TO salesadm;
SQL> GRANT CREATE ANY TABLE TO salesadm;
SQL> GRANT UNLIMITED TABLESPACE TO salesadm;
SQL> ALTER USER salesadm PROFILE DEFAULT;
SQL> COMMIT;

Here, salesadm is the administrative username created at the time of creating the PDB.

Connect to the PDB using the PDB username. If the connection is established without any issues, it confirms the successful configuration of the PDB access.

SQL> CONNECT pdbuser/<system_password>@Pluggable Database Service name

Example:

SQL> CONNECT salesadm/temp123#@salespdb;

Initiate a SQLPlus session to connect to the PDB.

$ sqlplus / as sysdba
SQL> CONNECT <pdb_admin>/<pdb_admin_password>@Pluggable Database Service name

Example:

SQL> connect salesadm/temp123#@salespdb

Execute the ADMINISTER KEY MANAGEMENT SQL statement to open the keystore for the PDB. Use the provided syntax:

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "<hsm_partition_password>";

Prior to opening the keystore and generating the PDB Master key, ensure that the keystore for the CDB (root container) is opened, and the Master key for CDB is generated. Do not configure the HSM auto login for CDB until the PDB Master key is generated. This practice applies to all PDBs using TDE. After generating the Master key for all PDBs, you can configure the CDB for auto login, and it will work for all PDBs.

Execute the following SQL statement to create the PDB master key:

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "<hsm_partition_password>";

This action generates a new master key. Any encryption or decryption operations performed within this PDB will utilize this master key.

In case of network latency or a failed heartbeat check with DPoD, refer to Troubleshooting Tips Problem 4.

Create CUSTOMERS table in PDB.

SQL> CREATE TABLE CUSTOMERS (ID NUMBER(5), NAME VARCHAR(42), CREDIT_LIMIT NUMBER(10));

Insert values into CUSTOMERS table.

SQL> INSERT INTO CUSTOMERS VALUES (001, 'George Bailey', 10000);
SQL> INSERT INTO CUSTOMERS VALUES (002, 'Denial Vettory', 20000);

Encrypt CREDIT_LIMIT column.

SQL> ALTER TABLE CUSTOMERS MODIFY (CREDIT_LIMIT ENCRYPT);

List values in encrypted column.

SQL> SELECT CREDIT_LIMIT FROM CUSTOMERS;

TDE automatically decrypts the values, displaying them in clear text.

List encrypted columns in the database.

SQL> SELECT * FROM DBA_ENCRYPTED_COLUMNS;

Create encrypted tablespace.

SQL> CREATE TABLESPACE SECURESPACE DATAFILE '/u01/app/oracle/oradata/orcl/salespdb/SECURE01.DBF' SIZE 150M ENCRYPTION DEFAULT STORAGE (ENCRYPT);

Create table in the encrypted tablespace.

SQL> CREATE TABLE EMPLOYEE (ID NUMBER(5), NAME VARCHAR(42), SALARY NUMBER(10)) TABLESPACE SECURESPACE;

Insert values into EMPLOYEE table.

SQL> INSERT INTO EMPLOYEE VALUES (001,'JOHN SMITH',15000);
SQL> INSERT INTO EMPLOYEE VALUES (002,'SCOTT TIGER',25000);
SQL> INSERT INTO EMPLOYEE VALUES (003,'DIANA HAYDEN',35000);

Display contents of EMPLOYEE table.

SQL> SELECT * FROM EMPLOYEE;

Close the keystore.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY "hsm_partition_password";

Attempt to display contents after closing keystore.

SQL> SELECT * FROM EMPLOYEE;

An error will be encountered, signaling that the EMPLOYEE table contents cannot be listed when the keystore is closed.

ERROR at line 1:
ORA-28365: wallet is not open

Open the keystore.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "hsm_partition_password";

Connect as SYSDBA.

SQL> CONNECT / AS SYSDBA;

Check Unplug Status of the PDB.

SQL> SELECT STATUS, PDB_NAME FROM DBA_PDBS;

Close the PDB Before Unplugging.

SQL> ALTER PLUGGABLE DATABASE PDB1 CLOSE IMMEDIATE;

Unplug the PDB.

SQL> ALTER PLUGGABLE DATABASE PDB1 UNPLUG INTO '/u01/app/oracle/oradata/pdb1.xml';

Verify PDB unplugging status:

SQL> SELECT STATUS FROM DBA_PDBS;

Ensure to move the master encryption keys of the unplugged PDB from the hardware keystore used at the source CDB to the hardware keystore in use at the destination. Refer to the Thales Luna HSM Product Documentation for detailed steps if using a different HSM at the destination.

Ensure that the master key is set and open in the root container of the destination database using the Hardware Keystore.

List the PDBs.

SQL> SHOW PDBS;

Plug the PDB into destination CDB.

SQL> CREATE PLUGGABLE DATABASE PDB1_FROMCDB1 USING '/u01/app/oracle/oradata/pdb1.xml' FILE_NAME_CONVERT=('/u01/app/oracle/oradata/ORCL/pdb1','/u01/app/oracle/oradata/CDB2/pdbfromcdb1') KEYSTORE IDENTIFIED BY <hsm_partition_password>;

Check PDB plugging status by querying the STATUS column of the DBA_PDBS data dictionary view.

SQL> SELECT STATUS FROM DBA_PDBS;

Open the PDB in read-write mode.

SQL> ALTER PLUGGABLE DATABASE PDB1_FROMCDB1 OPEN;

Log in to the plugged PDB as the user who was granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.

Open the master encryption key:

SQL> ALTER SESSION SET CONTAINER = PDB1_FROMCDB1;
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY <hsm_partition_password>;

Import the hardware keystore master encryption key.

SQL> ADMINISTER KEY MANAGEMENT IMPORT ENCRYPTION KEYS WITH SECRET "HSM" FROM 'HSM' IDENTIFIED BY <hsm_partition_password>;

Restart the PDB.

SQL> ALTER PLUGGABLE DATABASE PDB1_FROMCDB1 CLOSE;
SQL> ALTER PLUGGABLE DATABASE PDB1_FROMCDB1 OPEN;

Log in to the database instance as a user with the ADMINISTER KEY MANAGEMENT or SYSKM privilege.

SQL> CONNECT SYSTEM/<password>;

Open the Hardware Keystore.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY <hsm_partition_password>;

Ensure that the master ncryption key is generated on the HSM before creating or updating a secret.

To add a secret:

SQL> ADMINISTER KEY MANAGEMENT ADD SECRET 'mysecret' FOR CLIENT 'test' USING TAG 'testsecret' IDENTIFIED BY temp123#;

To update a secret:

SQL> ADMINISTER KEY MANAGEMENT UPDATE SECRET 'mynewsecret' FOR CLIENT 'test' USING TAG 'testsecret' IDENTIFIED BY temp123#;

To delete a secret:

SQL> ADMINISTER KEY MANAGEMENT DELETE SECRET FOR CLIENT 'test' IDENTIFIED BY temp123#;

Open any RAC instance and execute the following command:

crsctl stat res -t

Verify that the output provides details on the operational status of diverse resources within your Oracle RAC setup. Confirm that all resources are in the ONLINE state and that the servers are designated as STABLE.

| Name                       | Target | State  | Server | State Details                      |
|----------------------------|--------|--------|--------|------------------------------------|
| Local Resources            |        |        |        |                                    |
| ora.DATA.dg                | ONLINE | ONLINE | rac1   | STABLE                             |
|                            | ONLINE | ONLINE | rac2   | STABLE                             |
|                            | ONLINE | ONLINE | rac3   | STABLE                             |
| ora.LISTENER.lsnr          | ONLINE | ONLINE | rac1   | STABLE                             |
|                            | ONLINE | ONLINE | rac2   | STABLE                             |
|                            | ONLINE | ONLINE | rac3   | STABLE                             |
| ora.asm                    | ONLINE | ONLINE | rac1   | Started,STABLE                     |
|                            | ONLINE | ONLINE | rac2   | Started,STABLE                     |
|                            | ONLINE | ONLINE | rac3   | Started,STABLE                     |
| ora.net1.network           | ONLINE | ONLINE | rac1   | STABLE                             |
|                            | ONLINE | ONLINE | rac2   | STABLE                             |
|                            | ONLINE | ONLINE | rac3   | STABLE                             |
| ora.ons                    | ONLINE | ONLINE | rac1   | STABLE                             |
|                            | ONLINE | ONLINE | rac2   | STABLE                             |
|                            | ONLINE | ONLINE | rac3   | STABLE                             |
| Cluster Resources          |        |        |        |                                    |
| ora.LISTENER_SCAN1.lsnr    | 1      | ONLINE | rac2   | STABLE                             |
| ora.LISTENER_SCAN2.lsnr    | 1      | ONLINE | rac1   | STABLE                             |
| ora.LISTENER_SCAN3.lsnr    | 1      | ONLINE | rac3   | STABLE                             |
| ora.MGMTLSNR               | 1      | ONLINE | rac2   | 169.254.110.224 192.168.1.102,STABLE |
| ora.cvu                    | 1      | ONLINE | rac3   | STABLE                             |
| ora.mgmtdb                 | 1      | ONLINE | rac2   | Open,STABLE                        |
| ora.oc4j                   | 1      | ONLINE | rac3   | STABLE                             |
| ora.orcl1rac.db            | 1      | ONLINE | rac1   | Open,STABLE                        |
|                            | 2      | ONLINE | rac2   | Open,STABLE                        |
|                            | 3      | ONLINE | rac3   | Open,STABLE                        |
| ora.orcl2rac.db            | 1      | ONLINE | rac1   | Open,STABLE                        |
|                            | 2      | ONLINE | rac2   | Open,STABLE                        |
|                            | 3      | ONLINE | rac3   | Open,STABLE                        |
| ora.orcl3rac.db            | 1      | ONLINE | rac1   | Open,STABLE                        |
|                            | 2      | ONLINE | rac2   | Open,STABLE                        |
|                            | 3      | ONLINE | rac3   | Open,STABLE                        |
| ora.rac1.vip               | 1      | ONLINE | rac1   | STABLE                             |
| ora.rac2.vip               | 1      | ONLINE | rac2   | STABLE                             |
| ora.rac3.vip               | 1      | ONLINE | rac3   | STABLE                             |
| ora.scan1.vip              | 1      | ONLINE | rac2   | STABLE                             |
| ora.scan2.vip              | 1      | ONLINE | rac1   | STABLE                             |
| ora.scan3.vip              | 1      | ONLINE | rac3   | STABLE                             |

Execute the following commands to view detailed setup information for the three databases and validate that the Oracle RAC 3x3 node setup is working as expected:

$ srvctl config database -d ORCL1RAC
$ srvctl status database -d ORCL1RAC

$ srvctl config database -d ORCL2RAC
$ srvctl status database -d ORCL2RAC

$ srvctl config database -d ORCL3RAC
$ srvctl status database -d ORCL3RAC

DATABASE ORCL1RAC

[oracle@rac1 ~]$ srvctl config database -d ORCL1RAC
- Database unique name: orcl1rac
- Database name: orcl1rac
- Oracle home: /u01/app/oracle/product/12.1.0.2/db_1
- Oracle user: oracle
- Spfile: +DATA/ORCL1RAC/PARAMETERFILE/spfile.301.910221979
- Password file: +DATA/ORCL1RAC/PASSWORD/pwdorcl1rac.276.910221645
- Start options: open
- Stop options: immediate
- Database role: PRIMARY
- Management policy: AUTOMATIC
- Server pools:
- Disk Groups: DATA
- Configured nodes: rac1, rac2, rac3
- Database instances: orcl1rac1, orcl1rac2, orcl1rac3
- Database is administrator managed

[oracle@rac1 ~]$ srvctl status database -d ORCL1RAC
- Instance orcl1rac1 is running on node rac1
- Instance orcl1rac2 is running on node rac2
- Instance orcl1rac3 is running on node rac3

DATABASE ORCL2RAC

[oracle@rac2 ~]$ srvctl config database -d ORCL2RAC
- Database unique name: orcl2rac
- Database name: orcl2rac
- Oracle home: /u01/app/oracle/product/12.1.0.2/db_2
- Oracle user: oracle
- Spfile: +DATA/ORCL2RAC/PARAMETERFILE/spfile.331.910223671
- Password file: +DATA/ORCL2RAC/PASSWORD/pwdorcl2rac.306.910223319
- Start options: open
- Stop options: immediate
- Database role: PRIMARY
- Management policy: AUTOMATIC
- Server pools:
- Disk Groups: DATA
- Configured nodes: rac1, rac2, rac3
- Database instances: orcl2rac1, orcl2rac2, orcl2rac3
- Database is administrator managed

[oracle@rac2 ~]$ srvctl status database -d ORCL2RAC
- Instance orcl2rac1 is running on node rac1
- Instance orcl2rac2 is running on node rac2
- Instance orcl2rac3 is running on node rac3

DATABASE ORCL3RAC

[oracle@rac3 ~]$ srvctl config database -d ORCL3RAC
- Database unique name: orcl3rac
- Database name: orcl3rac
- Oracle home: /u01/app/oracle/product/12.1.0.2/db_3
- Oracle user: oracle
- Spfile: +DATA/ORCL3RAC/PARAMETERFILE/spfile.361.910224445
- Password file: +DATA/ORCL3RAC/PASSWORD/pwdorcl3rac.336.910224083
- Start options: open
- Stop options: immediate
- Database role: PRIMARY
- Management policy: AUTOMATIC
- Server pools:
- Disk Groups: DATA
- Configured nodes: rac1, rac2, rac3
- Database instances: orcl3rac1, orcl3rac2, orcl3rac3
- Database is administrator managed

[oracle@rac3 ~]$ srvctl status database -d ORCL3RAC
- Instance orcl3rac1 is running on node rac1
- Instance orcl3rac2 is running on node rac2
- Instance orcl3rac3 is running on node rac3

V$ACTIVE_INSTANCES View

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Apr 27 20:31:35 2016

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> SELECT inst_name FROM v$active_instances;

INST_NAME
------------------------------------------------------------
rac1.localdomain:orcl1rac1
rac2.localdomain:orcl1rac2
rac3.localdomain:orcl1rac3
------------------------------------------------------------

Configure PKCS#11 provider

Migrate master encryption key to HSM

Copy the Luna HSM PKCS#11 library to the recommended directory structure based on your operating system.

• Linux/Solaris/AIX:

"/opt/oracle/extapi/[32,64]/hsm/{Vendor}/{Version}/libapiname.ext"

• Windows:

"%SYSTEMDRIVE%\oracle\extapi\[32,64]\hsm\{Vendor}\{Version}\libapiname.ext"

Replace the placeholders:

• [32,64]: Specify whether the binary is 32-bit or 64-bit.

• {Vendor}: Name of the vendor supplying the library.

• {Version}: Version number of the library (format: [number.number.number]).

• libapiname.ext: The API name with the XX prefixed with the word lib and the extension of the library file.

Example path for UNIX systems: "/opt/oracle/extapi/64/hsm/safenet/7.x.x/libCryptoki2_64.so"

Run the following commands to create the directory and set up the SafeNet library:

mkdir -p /opt/oracle/extapi/64/hsm/safenet/7.x.x
cp /usr/safenet/lunaclient/lib/libCryptoki2_64.so /opt/oracle/extapi/64/hsm/safenet/7.x.x/
chown -R oracle:oinstall /opt/oracle/
chmod -R 775 /opt/oracle/

Ensure that the Oracle user has read/write permission for the specified directory.

The provided directory structures cater to the example setup with three Oracle Homes, each demonstrating a different scenario.

Only one PKCS#11 library is supported at a time.

Create directories

Verify software-based wallet

Verify master encryption key

Test the database

Create the HSM auto wallet

Refer to Appendix B for instructions on completing the software keystore configuration on all nodes, tailored to the installed Oracle Database version.

• Configure software keystore for Oracle Database 12c

• Configure software keystore for Oracle Database 18c and 19c

Start the database.

$ sqlplus / as sysdba

Restart the database, in case it is not already started.

SQL> startup;

Grant the ADMINISTER KEY MANAGEMENT or SYSKM privilege to the SYSTEM user and any other user intended for use.

SQL> GRANT ADMINISTER KEY MANAGEMENT TO SYSTEM;
SQL> COMMIT;

Connect to the database as the SYSTEM user.

SQL> CONNECT SYSTEM/<password>

The password for the SYSTEM user may have been set during the Oracle installation. Throughout this document, all database passwords are assumed to be Temp1234.

Execute the following ADMINISTER KEY MANAGEMENT SQL statement to create the keystore:

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE IDENTIFIED BY <software_keystore_password>;

For Oracle Database 12c, use the following syntax: SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE 'keystore_location' IDENTIFIED BY <software_keystore_password>;. Ensure that keystore_location corresponds to the path set in the sqlnet.ora file for the Oracle wallet directory.

Execute the following ADMINISTER KEY MANAGEMENT SQL statement to open the keystore:

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY <software_keystore_password>;

Repeat steps 1-7 on all instances (RAC1, RAC2, and RAC3). If you are storing the wallet on a shared disk, only repeat steps 1-5.

Set the master encryption key in the software keystore:

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY <software_keystore_password> WITH BACKUP USING 'backup_identifier';

When using password-based keystores, it's crucial to include the WITH BACKUP option, as it creates a backup of the keystore. Additionally, you can use the USING clause to add a short description of the backup, enclosed in single quotation marks. The specified identifier is then appended to the named keystore file. For instance, in the example ewallet_time_stamp_emp_key_backup.p12, the identifier is set as emp_key_backup.

Skip this step if the wallet is created on a shared location. Copy the ewallet.p12 file and the backup file created in the directory /etc/oracle/wallet/ORCL1RAC from RAC1 to RAC2 and RAC3 in the same directory as on RAC1

$ scp /etc/oracle/wallet/ORCL1RAC/ewallet* oracle@rac2.localdomain:/etc/oracle/wallet/ORCL1RAC/
$ scp /etc/oracle/wallet/ORCL1RAC/ewallet* oracle@rac3.localdomain:/etc/oracle/wallet/ORCL1RAC/

Create a CUSTOMERS table.

SQL> CREATE TABLE CUSTOMERS (ID NUMBER(5), NAME VARCHAR(42), CREDIT_LIMIT NUMBER(10));

Insert some values into the CUSTOMERS table.

SQL> INSERT INTO CUSTOMERS VALUES (001, 'George Bailey', 10000);
SQL> INSERT INTO CUSTOMERS VALUES (002, 'Denial Vettory', 20000);
SQL> INSERT INTO CUSTOMERS VALUES (003, 'MS Dhoni', 30000);
SQL> INSERT INTO CUSTOMERS VALUES (004, 'Shahid Afridi', 40000);

Encrypt the CREDIT_LIMIT column of the CUSTOMERS table.

SQL> ALTER TABLE CUSTOMERS MODIFY (CREDIT_LIMIT ENCRYPT);

Return values in clear text from the encrypted column.

SQL> SELECT CREDIT_LIMIT FROM CUSTOMERS;

List encrypted columns in your database.

SQL> SELECT * FROM DBA_ENCRYPTED_COLUMNS;

View information about the software keystore.

SQL> SELECT * FROM GV$ENCRYPTION_WALLET;

Create an encrypted tablespace.

SQL> CREATE TABLESPACE SECURESPACE DATAFILE '+DATA' SIZE 150M ENCRYPTION DEFAULT STORAGE (ENCRYPT);

Create a table in the tablespace.

SQL> CREATE TABLE EMPLOYEE (ID NUMBER(5), NAME VARCHAR(42), SALARY NUMBER(10)) TABLESPACE SECURESPACE;

Insert values into the EMPLOYEE table.

SQL> INSERT INTO EMPLOYEE VALUES (001, 'JOHN SMITH', 15000);
SQL> INSERT INTO EMPLOYEE VALUES (002, 'SCOTT TIGER', 25000);
SQL> INSERT INTO EMPLOYEE VALUES (003, 'DIANA HAYDEN', 35000);

Display the contents of the EMPLOYEE table.

SQL> SELECT * FROM EMPLOYEE;

Close the keystore.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY <software_keystore_password>;

After closing the keystore, attempt to display contents again.

SQL> SELECT * FROM EMPLOYEE;

If the keystore is closed, you will receive the following error indicating that you cannot list the contents of the EMPLOYEE table:

ERROR at line 1:
ORA-28365: wallet is not open

Connect to RAC2 and RAC3 machines and execute the following commands after opening the wallet.

• Open the keystore.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY <software_keystore_password>;

• Access the contents of the EMPLOYEE table.

SQL> SELECT * FROM EMPLOYEE;
SQL> exit

Each time you start the database, you need to open the wallet to view the encrypted data. You can set the wallet to auto-login so that it automatically opens when the database starts.

The above commands work on all three nodes — RAC1, RAC2, and RAC3 — after copying the wallet to all instances.

Ensure the keystore configuration is complete by referring to Appendix B and following the steps in the relevant section corresponding to your Oracle Database version.

• Configure keystore for migration from software to hardware for Oracle Database 12c

• Configure keystore for migration from software to hardware for Oracle Database 18c and 19c

Connect to SQL*Plus.

$ sqlplus / as sysdba

Connect as system.

SQL> connect system/<password>

Migrate wallet to HSM.

SQL> ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY IDENTIFIED BY "hsm_partition_pwd" MIGRATE USING <software_keystore_password> WITH BACKUP USING 'backup_identifier';

Replace "hsm_partition_pwd" with the HSM partition password. The MIGRATE USING software_keystore string re-encrypts the TDE column keys and tablespace keys with the new HSM-based master key. <software_keystore_password> is the password of the software wallet.

Copy wallet files (if not using shared location).

$ scp /etc/oracle/wallet/ORCL1RAC/ewallet* oracle@rac2.localdomain:/etc/oracle/wallet/ORCL1RAC/
$ scp /etc/oracle/wallet/ORCL1RAC/ewallet* oracle@rac3.localdomain:/etc/oracle/wallet/ORCL1RAC/

Change software keystore password.

SQL> ADMINISTER KEY MANAGEMENT ALTER KEYSTORE PASSWORD IDENTIFIED BY <software_keystore_password> SET hsm_partition_pwd WITH BACKUP USING 'backup_identifier';

Copy wallet files (again, if not using shared location):

Copy the ewallet.p12 and backup files created in the directory "/etc/oracle/wallet/ORCL1RAC" from RAC1 to RAC2 and RAC3 in the same directory as RAC1.

Open dual keystore. From now on, when you open the keystore, it will open both the software-based keystore and the HSM-based keystore.

Restart the database.

$ srvctl stop database -d ORCL1RAC
$ srvctl start database -d ORCL1RAC

Open a terminal and connect to SQLPlus as the sysdba user.

$ sqlplus / as sysdba

Connect to the database as the system user.

SQL> connect system/<password>

Open the keystore using the HSM partition password.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "hsm_partition_pwd";

Execute the next command to see the values in the encrypted column in clear text. TDE now decrypts them automatically using the HSM master key.

SQL> SELECT * FROM EMPLOYEE;

Check the wallet information with the following command.

SQL> SELECT * FROM GV$ENCRYPTION_WALLET;

Copy the wallet from RAC1 to others after changing the wallet password.

Create an auto-login wallet on all instances using the initial password for the software-based wallet (or a new one if desired). Use the following syntax:

SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE IDENTIFIED BY <software_keystore_password>;

For Oracle Database 12c, use the path to the Oracle wallet directory specified in the sqlnet.ora file.

To use the auto-login wallet only on the local system, include LOCAL AUTO_LOGIN instead of AUTO_LOGIN.

Verify that an auto-open software keystore has been created in the Oracle wallet directory specified in the sqlnet.ora file. You will find two wallets in this directory: ewallet.p12 and cwallet.sso; the latter is the auto-open wallet. Rename ewallet.p12 to ewallet.p24 so that Oracle picks only cwallet.sso when auto-login is enabled.

Copy the auto-login wallet and encryption wallet from one RAC instance to others after creating the wallet. It is not required if you use shared storage for the wallet. Renaming the wallet is required for v12.2.0.1 only, but it is recommended to rename the encryption wallet when the auto-login wallet has been created.

Restart the database.

$ srvctl stop database -d ORCL1RAC
$ srvctl start database -d ORCL1RAC

Connect to the database as the system and open the HSM keystore. The software wallet will open automatically.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "hsm_partition_pwd";

Refer to Appendix B to configure the Software Keystore based on your Oracle Database version. Use the relevant section for Oracle Database 12c or Oracle Database 18c and 19c.

• Configure software keystore for Oracle Database 12c

• Configure software keystore for Oracle Database 18c and 19c

Rename the cwallet.sso file on all nodes.

Ensure that the encryption wallet ewallet.p12 is present on all nodes.

After restarting the database, connect as a system user, and open the software keystore using the following SQL command:

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY <software_keystore_password>;

Add the HSM secret as a client in the software keystore using the following SQL command:

SQL> ADMINISTER KEY MANAGEMENT ADD SECRET 'hsm_partition_password' FOR CLIENT 'HSM_PASSWORD' IDENTIFIED BY <software_keystore_password> WITH BACKUP USING 'backup_identifier';

This command adds the password of the HSM partition as a secret for a client named HSM_PASSWORD in the software keystore. The client name HSM_PASSWORD is an identifier used by Oracle to represent this specific HSM password as a secret within the software keystore. Additionally, a backup of this secret is created for security and recovery purposes.

Close the software keystore using the following SQL command:

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY <software_keystore_password>;

Create (or recreate) auto-login keystore.

SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE IDENTIFIED BY <software_keystore_password>;

For Oracle Database 12c, use the following command instead, adjusting the keystore_location to the path set in the sqlnet.ora file: SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE 'keystore_location' IDENTIFIED BY <software_keystore_password>;

Rename the ewallet.p12 to ewallet.p24 to enable auto login.

This step is necessary for Oracle Database v12.2.0.1, and it is recommended to rename the encryption wallet when an auto-login wallet has been created.

Copy the auto-login wallet and encryption wallet from one RAC instance to others after creating the wallet. This step is not required if shared storage is used for the wallet.

Refer to Appendix B and complete the Keystore for Migration from Software to Hardware configuration based on the installed Oracle Database version:

• Configure keystore for migration from software to hardware for Oracle Database 12c

• Configure keystore for migration from software to hardware for Oracle Database 18c and 19c

Restart the database and connect as a system.

Check the wallet information with the following command:

SQL> SELECT * FROM GV$ENCRYPTION_WALLET;

Refer to Appendix B and complete the hardware keystore configuration based on the installed Oracle Database version.

• Configure hardware keystore for Oracle Database 12c

• Configure hardware keystore for Oracle Database 18c and 19c

Restart the database if not already started.

$ srvctl stop database -d ORCL2RAC
$ srvctl start database -d ORCL2RAC

Verify the status of the database once the start command has been executed.

$ srvctl status database -d ORCL2RAC
--------------------------------------------------------------
Instance orcl2rac1 is running on node rac1
Instance orcl2rac2 is running on node rac2
Instance orcl2rac3 is running on node rac3
--------------------------------------------------------------

Start the database.

$ sqlplus / as sysdba

Grant necessary privileges.

SQL> GRANT ADMINISTER KEY MANAGEMENT TO SYSTEM;
SQL> COMMIT;

Connect to the database as SYSTEM.

SQL> CONNECT SYSTEM/<password>

The password for SYSTEM can be set during Oracle installation. All dbapasswords throughout this document have been set to Temp1234.

Open the HSM keystore.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "hsm_partition_password";

Configure the master encryption key in the software keystore.

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "hsm_partition_password";

Verify the generated keys on the HSM partition to ensure the master encryption key is successfully set. Below is an example snapshot of the HSM partition contents:

Partition Name:  ORCL2
Partition SN:    152042028
Storage (Bytes): Total=102701, Used=1848, Free=100853
Number objects:  5
Object Label:    ORACLE.TDE.HSM.MK.0661286A8C71864F2ABF7891D044154D9A
Object Type:     Symmetric Key
Object Label:    DATA_OBJECT_SUPPORTED_IDEN
Object Type:     Data
Object Label:    ORACLE.SECURITY.KM.ENCRYPTION.30363631323836413843373138363446324142463738393144303434313534443941
Object Type:     Data
Object Label:    DATA_OBJECT_SUPPORTED_IDEN
Object Type:     Data
Object Label:    ORACLE.TSE.HSM.MK.072AC159D9153C4FF0BF3BF931ED9693850203
Object Type:     Symmetric Key

Create a CUSTOMERS table in the database.

SQL> CREATE TABLE CUSTOMERS (ID NUMBER(5), NAME VARCHAR(42), CREDIT_LIMIT NUMBER(10));

Enter some values in the CUSTOMERS table.

SQL> INSERT INTO CUSTOMERS VALUES (001, 'George Bailey', 10000);
SQL> INSERT INTO CUSTOMERS VALUES (002, 'Denial Vettory', 20000);
SQL> INSERT INTO CUSTOMERS VALUES (003, 'MS Dhoni', 30000);
SQL> INSERT INTO CUSTOMERS VALUES (004, 'Shahid Afridi', 40000);

Encrypt the 'CREDIT_LIMIT' column of the 'CUSTOMERS' table.

SQL> ALTER TABLE CUSTOMERS MODIFY (CREDIT_LIMIT ENCRYPT);

Retrieve values listed in the encrypted column in clear text. Transparent Data Encryption decrypts them automatically.

SQL> SELECT CREDIT_LIMIT FROM CUSTOMERS;

List encrypted columns in your database.

SQL> SELECT * FROM DBA_ENCRYPTED_COLUMNS;

View information about the software keystore.

SQL> SELECT * FROM GV$ENCRYPTION_WALLET;

Create an encrypted tablespace.

SQL> CREATE TABLESPACE SECURESPACE DATAFILE '+DATA' SIZE 150M ENCRYPTION DEFAULT STORAGE (ENCRYPT);

Create a table in the tablespace.

SQL> CREATE TABLE EMPLOYEE (ID NUMBER(5), NAME VARCHAR(42), SALARY NUMBER(10)) TABLESPACE SECURESPACE;

Insert some values in the EMPLOYEE table.

SQL> INSERT INTO EMPLOYEE VALUES (001, 'JOHN SMITH', 15000);
SQL> INSERT INTO EMPLOYEE VALUES (002, 'SCOTT TIGER', 25000);
SQL> INSERT INTO EMPLOYEE VALUES (003, 'DIANA HAYDEN', 35000);

Display the contents of the EMPLOYEE table.

SQL> SELECT * FROM EMPLOYEE;

Close the keystore.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY "hsm_partition_password";

Display the contents again.

SQL> SELECT * FROM EMPLOYEE;

If the keystore is closed, you will encounter the following error, indicating that you cannot list the contents of the EMPLOYEE table.
ERROR at line 1:
ORA-28365: wallet is not open`

Open the wallet on the RAC2 and RAC3 machines.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "hsm_partition_password";

Try to access the contents of the EMPLOYEE table.

SQL> SELECT * FROM EMPLOYEE;

Close the HSM.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY "hsm_partition_password";

Refer to Appendix B and complete the software keystore configuration based on the installed Oracle Database version:

• Configure hardware keystore for Oracle Database 12c

• Configure hardware keystore for Oracle Database 18c and 19c

Create the software keystore:

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE IDENTIFIED BY <software_keystore_password>;

For Oracle Database 12c, use:
SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE 'keystore_location' IDENTIFIED BY <software_keystore_password>;. Replace keystore_location with the path to the Oracle wallet directory set in the sqlnet.ora file.

If a software wallet has already been created, skip this step and remove/rename the cwallet.sso file.

Open the software keystore.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY <software_keystore_password>;

Add the HSM secret as a client.

SQL> ADMINISTER KEY MANAGEMENT ADD SECRET 'hsm_partition_password' FOR CLIENT 'HSM_PASSWORD' IDENTIFIED BY <software_keystore_password> WITH BACKUP USING 'backup_identifier';

Close the software keystore.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY <software_keystore_password>;

Create auto-login keystore.

SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE IDENTIFIED BY <software_keystore_password>;

For Oracle Database 12c, use:
SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE 'keystore_location' IDENTIFIED BY <software_keystore_password>;. Replace keystore_location with the path to the Oracle wallet directory set in the sqlnet.ora file.

To enhance auto-login functionality, consider renaming the ewallet.p12 file to ewallet.p24 so that Oracle prioritizes cwallet.sso when enabled. Note that this is an optional step.

Follow the instructions in Appendix B to migrate the keystore from software to hardware, based on your Oracle Database version:

• Configure keystore for migration from software to hardware for Oracle Database 12c

• Configure keystore for migration from software to hardware for Oracle Database 18c and 19c

Copy both ewallet.p12 and cwallet.sso files from the directory /etc/oracle/wallet/ORCL2RAC on RAC1 to RAC2 and RAC3 in the same directory, if you are not using shared storage.

$ scp /etc/oracle/wallet/ORCL2RAC/* oracle@rac2.localdomain:/etc/oracle/wallet/ORCL2RAC/
$ scp /etc/oracle/wallet/ORCL2RAC/* oracle@rac3.localdomain:/etc/oracle/wallet/ORCL2RAC/

Close the wallet and open it one more time. The hardware security module auto-login keystore will automatically open the next time a TDE operation is executed.

Restart the database and connect as a system.

SQL> SELECT * FROM GV$ENCRYPTION_WALLET;

Confirm the sqlnet.ora file has the following entry:

ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = <path to the oracle wallet directory>)))

Create and encrypt a test tablespace.

SQL> CREATE TABLESPACE securespace DATAFILE '/home/oracle/app/oracle/oradata/orcl/secure01.dbf' SIZE 10M ENCRYPTION DEFAULT STORAGE (ENCRYPT);

Create a table in the tablespace.

SQL> CREATE TABLE employee (id NUMBER(5), name VARCHAR(42), salary NUMBER(10)) TABLESPACE securespace;

Insert values into the employee table.

SQL> INSERT INTO employee VALUES (001, 'JOHN SMITH', 10000);
SQL> INSERT INTO employee VALUES (002, 'SCOTT TIGER', 20000);
SQL> INSERT INTO employee VALUES (003, 'DIANA HAYDEN', 50000);

Modify the sqlnet.ora file with the following settings:

ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = HSM) (METHOD_DATA = (DIRECTORY = <path to the oracle wallet directory>)))

Connect to the database as system and execute the following command.

SQL> ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY IDENTIFIED BY "hsm_partition_pwd" MIGRATE USING <software_keystore_password> WITH BACKUP USING 'backup_identifier';

hsm_partition_pwd is the HSM partition password, and the MIGRATE USING <software_keystore_password> re-encrypts TDE column keys and tablespace keys with the new HSM-based master key.

Verify that queries on encrypted tablespace work.

SQL> SELECT * FROM EMPLOYEE;

Update sqlnet.ora configuration for auto-open wallet.

ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /etc/oracle/wallet)))

Connect as system and open the software keystore.

SQL> connect system/<password>
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY <software_keystore_password>;

If an error (ORA-28354) occurs, restart the database and retry.

Add the HSM secret as a client.

SQL> ADMINISTER KEY MANAGEMENT ADD SECRET 'hsm_partition_password' FOR CLIENT 'HSM_PASSWORD' IDENTIFIED BY <software_keystore_password> WITH BACKUP USING 'backup_identifier';

Close the software keystore.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY <software_keystore_password>;

In case you encounter the ORA-28374 error, you may safely disregard it and continue with the subsequent steps.

Create (or recreate) the auto-login keystore.

SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE 'keystore_location' IDENTIFIED BY <software_keystore_password>;

Update sqlnet.ora file to use the HSM.

ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = HSM) (METHOD_DATA = (DIRECTORY = /etc/oracle/wallet)))

Restart the database and connect as system. Check the wallet information.

SQL> SELECT * FROM V$ENCRYPTION_WALLET;

Ensure the wallet directory on the standby database mirrors that of the primary database. Copy all contents from the primary's wallet directory to the corresponding directory on the standby. For example:

scp -r oracle@<hostname or IP of Primary>:/etc/oracle/wallet/* /etc/oracle/wallet

The assumption is made that /etc/oracle/wallet is the designated directory for the encryption wallet.

Copy the sqlnet.ora file from the Primary Database to the Standby Database's $ORACLE_HOME/network/admin/ location.

scp oracle@<hostname or IP of Primary>:$ORACLE_HOME/network/admin/sqlnet.ora $ORACLE_HOME/network/admin/sqlnet.ora

Ensure that the content of the sqlnet.ora file on the standby database aligns with the following configuration:

ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = HSM) (METHOD_DATA = (DIRECTORY = <Path to oracle wallet>)))

Replace <Path to oracle wallet> with the actual path to the Oracle wallet directory.

Start the standby database and resume managed recovery.

$ sqlplus / as sysdba

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

This ensures that all necessary information about the HSM wallet is provided to the standby database. Once standby database recovery is completed, it can be opened in read-only mode, allowing query access.

Switch the Standby database into read-only mode with the following commands:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE OPEN READ ONLY;

Connect to the standby database as system and execute a query on the encrypted tablespace. For example, display the contents of the EMPLOYEE table.

SQL> SELECT * FROM EMPLOYEE;

The result will include the data from the encrypted table:

ID   NAME           SALARY
---- -------------- -------
1    JOHN SMITH     10000
2    SCOTT TIGER    20000
3    DIANA HAYDEN   50000

If the HSM Auto Wallet is not configured on the Primary database after migrating the key from the software wallet to the HSM, open the wallet before executing the select command on encrypted tables or tablespaces on the standby database:

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "hsm_partition_pwd";

Ensure that the sqlnet.ora file includes the following entry:

ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = HSM))

Execute the following commands to create a test tablespace and encrypt it:

SQL> CREATE TABLESPACE securespace DATAFILE '/home/oracle/app/oracle/oradata/orcl/secure01.dbf' SIZE 10M ENCRYPTION DEFAULT STORAGE (ENCRYPT);

Create a table named employee within the newly created tablespace:

SQL> CREATE TABLE employee (id NUMBER(5), name VARCHAR(42), salary NUMBER(10)) TABLESPACE securespace;

Populate the employee table with sample values:

SQL> INSERT INTO employee VALUES (001, 'JOHN SMITH', 10000);
SQL> INSERT INTO employee VALUES (002, 'SCOTT TIGER', 20000);
SQL> INSERT INTO employee VALUES (003, 'DIANA HAYDEN', 50000);

Check the status of the encryption wallet:

SQL> SELECT * FROM v$encryption_wallet;

Copy sqlnet.ora to standby database, if you do not plan to use the HSM auto wallet. Place the file at the following location:

$ORACLE_HOME/network/admin/sqlnet.ora

Please note that this step is optional and ensures consistent configuration between the primary and standby databases.

Ensure the HSM is closed if currently open. Execute the following command:

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY "hsm_partition_password";

Edit the sqlnet.ora file to include the following configuration:

ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = <Path to Oracle wallet>)))

For example:

ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /etc/oracle/wallet)))

Establish the software keystore in the designated location (e.g., "/etc/oracle/wallet"):

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/etc/oracle/wallet' IDENTIFIED BY <software_keystore_password>;

Open the software keystore to enable key management.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY <software_keystore_password>;

Add the HSM password as a client to the software keystore:

SQL> ADMINISTER KEY MANAGEMENT ADD SECRET 'hsm_partition_password' FOR CLIENT 'HSM_PASSWORD' IDENTIFIED BY <software_keystore_password> WITH BACKUP USING 'backup_identifier';

The secret corresponds to the HSM password, and HSM_PASSWORD is an Oracle-defined client name representing the HSM password as a secret in the software keystore.

Close the software keystore using the following command:

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY <software_keystore_password>;

If the error ORA-28374: typed master key not found in wallet is returned, ignore it and proceed to the next step to create the Auto-Login keystore.

Create the auto-login keystore using the existing keystore as a base:

SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/etc/oracle/wallet' IDENTIFIED BY <software_keystore_password>;

Modify the sqlnet.ora file to specify the HSM for encryption:

ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = HSM) (METHOD_DATA = (DIRECTORY = /etc/oracle/wallet)))

Restart the database and connect as the system user.

Verify the wallet information with the following command:

SQL> SELECT * FROM V$ENCRYPTION_WALLET;

Ensure that you have fulfilled all the prerequisites outlined in the Prerequisites section.

If an auto wallet was created on the primary database, replicate the primary database wallet directory on the standby. Copy all contents from the primary database wallet directory to the corresponding directory on the standby.

Copy the sqlnet.ora file from the primary database to the standby database, placing it at the following location:

$ORACLE_HOME/network/admin/sqlnet.ora

Adjust the content based on your configuration:

• For HSM-only wallet (No auto login):

ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = HSM))

• For HSM with auto wallet:

ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = HSM) (METHOD_DATA = (DIRECTORY = <Path to Oracle wallet>)))

Connect to the standby database as sysdba:

$ sqlplus / as sysdba

Execute the following commands to resume managed recovery:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Once standby database recovery is complete, open it in read-only mode to allow query access.

Switch the standby database to read-only mode with the following commands:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE OPEN READ ONLY;

Execute a test query on the standby database to ensure it can access the encrypted tablespace:

SQL> SELECT * FROM EMPLOYEE;

This query should return the expected results.

If the HSM auto wallet is not configured on the primary database, open the wallet before running the select command on encrypted tables or tablespaces on the standby.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "hsm_partition_pwd";

Ensure that the library path is correctly set to:

"/opt/oracle/extapi/[32/64]/HSM/[x.x.x]/libcryptoki2_64.so"

Confirm that the directory mentioned above (/opt/oracle/extapi/[32/64]/HSM/[x.x.x]/) is owned by oracle:oinstall and has read/write permissions.

Ensure that the 64-bit Java Virtual Machine (JVM) is running on the machine. If you are using a 64-bit client, a 64-bit JVM is necessary. Using a 32-bit JVM might result in the inability to use the 64-bit library.

Confirm that the provided HSM password is accurate. A mismatch may occur if the password entered does not align with the expected credentials.

Ensure that neither HSM Auto_Login nor Local_Auto_Login is enabled for the CDB (Container Database). If enabled, consider temporarily disabling it for the operation.

If Auto_Login or Local_Auto_Login is enabled for the CDB, switch to using the encryption wallet instead of the auto wallet in the CDB. Perform the necessary operation in the PDB. Once the Master Key is generated for the PDB, Auto_Login can be re-enabled in the CDB, and it will seamlessly apply to all PDBs.

Open your Oracle environment for system modifications.

Execute the following command to adjust the event settings, allowing for a 30-second wait before closing the wallet:

ALTER SYSTEM SET EVENT='28420 trace name context forever, level 10' COMMENT='HSM heartbeat timeout attempt' SCOPE=SPFILE;

You have the flexibility to customize the trace level by changing the value from 10 to your preferred alternative.

Save the changes, ensuring they are applied globally.

Open your $ORACLE_HOME/network/admin/sqlnet.ora file.

Add the following line to specify the location of the Oracle wallet directory:

ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = <path to the oracle wallet directory>)))

Open your $ORACLE_HOME/network/admin/sqlnet.ora file.

Insert the following line to indicate the hardware keystore method:

ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = HSM))

Open your $ORACLE_HOME/network/admin/sqlnet.ora file.

Include the following line to set up the hardware keystore and specify the directory:

ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = HSM) (METHOD_DATA =(DIRECTORY = <path to the oracle wallet directory>)))

Create a wallet directory named wallet in the $ORACLE_BASE/admin/db_unique_name directory or any other location.

Log in to the database instance as a user with SYSDBA administrative privilege:

Connect / as sysdba

Set the WALLET_ROOT parameter:

alter system set wallet_root='<path to the oracle wallet directory>' scope=spfile;

Shut down and start up the database:

shutdown immediate;
startup;

Set the TDE_CONFIGURATION parameter:

alter system set TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" SCOPE=both;

Create a wallet directory named wallet in the $ORACLE_BASE/admin/db_unique_name directory or any other location.

Log in to the database instance as a user with SYSDBA administrative privilege:

Connect / as sysdba

Set the WALLET_ROOT parameter:

alter system set wallet_root='<path to the oracle wallet directory>' scope=spfile;

Shutdown and startup the database:

shutdown immediate;
startup;

Set the TDE_CONFIGURATION parameter:

alter system set TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=HSM" SCOPE=both;

Create a wallet directory named wallet in the $ORACLE_BASE/admin/db_unique_name directory or any other location.

Log in to the database instance as a user with SYSDBA administrative privilege.

Connect / as sysdba

Set the WALLET_ROOT parameter.

alter system set wallet_root='<path to the oracle wallet directory>' scope=spfile;

Shut down and start up the database.

shutdown immediate;
startup;

Set the TDE_CONFIGURATION parameter.

alter system set TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=HSM|FILE" SCOPE=both;