If we need to create a TDE column or a tablespace, first requirement is to create a keystore to save the encryption key (secret).
Provide the location (ENCRYPTION_WALLET_LOCATION) where the wallet or keystore will be stored in sqlnet.ora. This can also be provided as database parameter WALLET_ROOT if you don't want to use the sqlnet.ora method.
[oracle@linux75-2 ~]$ cat /u01/orcl/122/network/admin/sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/orcl/122/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES)
ENCRYPTION_WALLET_LOCATION =
(SOURCE =(METHOD = FILE)(METHOD_DATA =
(DIRECTORY = /u01/orcl/122/network/wallet/)))
SQLNET.EXPIRE_TIME=2
[oracle@linux75-2 ~]$
Create a keystore
SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/orcl/122/network/wallet/' IDENTIFIED BY oracle; keystore altered. SQL>Now, keystore ewallet.p12 file will be created in the location mentioned. NEVER delete, lose or tamper this file.
We can open and close the keystore as needed. The issue is that every time we bounce the database, we have to explicitly open the keystore.
SQL> select * from v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID -------------------- -------------------------------------------------- ------------------------------ -------------------- --------- --------- ---------- FILE /u01/orcl/122/network/wallet/ CLOSED UNKNOWN SINGLE NO 0 SQL> administer key management set keystore open identified by oracle; keystore altered. SQL> select * from v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID -------------------- -------------------------------------------------- ------------------------------ -------------------- --------- --------- ---------- FILE /u01/orcl/122/network/wallet/ OPEN PASSWORD SINGLE NO 0 SQL> exit
When I tried to upgrade the database to 19c from the current 12.2 version using Autoupgrade utility, the analyze phase failed due to non availability of Auto-login keystore even though the keystore is open when the analyze phase is initiated.
So how do we create an auto login keystore?
Auto-login keystore is nothing but we don't have to manually open the keystore after a restart of the database. The creation is simple by the below command.
SQL> ADMINISTER KEY MANAGEMENT CREATE LOCAL AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u01/orcl/122/network/wallet/' IDENTIFIED BY oracle; keystore altered. SQL> ! [oracle@linux75-2 ~]$ ls -lrt /u01/orcl/122/network/wallet/ total 12 -rw-------. 1 oracle oinstall 2408 Jun 12 23:41 ewallet_2021061218110265.p12 -rw-------. 1 oracle oinstall 3848 Jun 12 23:41 ewallet.p12 -rw-------. 1 oracle oinstall 3901 Sep 26 00:21 cwallet.sso [oracle@linux75-2 ~]$ exit exit
The LOCAL keyword specifies the keystore can only be opened from the local machine.
You can see a new file by name cwallet.sso is created which is the auto login keystore file. NEVER delete, lose or tamper this file as this will be an irreversible damage to the database.
Once the auto login keystore is created, the password keystore will no longer work and the autologin keystore will automatically precedence and login and open the keystore from the next restart.
SQL> select * from v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID ---------- -------------------------------------------------- ------------------------------ -------------------- --------- --------- ---------- FILE /u01/orcl/122/network/wallet/ OPEN PASSWORD SINGLE NO 0 SQL> administer key management set keystore close; administer key management set keystore close * ERROR at line 1: ORA-28389: cannot close auto login wallet SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 1073741824 bytes Fixed Size 8628936 bytes Variable Size 595592504 bytes Database Buffers 461373440 bytes Redo Buffers 8146944 bytes Database mounted. Database opened. SQL> set lines 200 SQL> col wrl_parameter for a50 SQL> select * from v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID ---------- -------------------------------------------------- ------------------------------ -------------------- --------- --------- ---------- FILE /u01/orcl/122/network/wallet/ OPEN LOCAL_AUTOLOGIN SINGLE NO 0 SQL> exitYou can see that the WALLET_TYPE is changed from PASSWORD to LOCAL_AUTOLOGIN and is also the STATUS is OPEN upon the restart of the database.
Now, my analyze phase of the Autoupgrade also completed successfully without any errors.
Happy Keystoring...!!!
