Exporting a Keystore from ASM to a Target Host for Oracle TDE Provisioning ( KBA8286 )

orapki wallet create -wallet /tmp/wallet -auto_login -pwd mypassword123
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.Got tag 3 instead of 16.
PKI-02003: Unable to load the wallet at: /tmp/wallet

Prerequisites

It is expected that the source database already has a keystore configured and has encrypted tables, however the steps for configuring this are provided below as an example only. These steps should only be performed in a lab environment or after consultation with your DBA team or Oracle.

 

1. Configure sqlnet.ora, this must be done on all nodes in the case of RAC.

[oracle@mw19rac1 trace]$ cat $ORACLE_HOME/network/admin/sqlnet.ora
SQLNET.ALLOWED_LOGON_VERSION_SERVER=11
ENCRYPTION_WALLET_LOCATION=
    (SOURCE=
    (METHOD=FILE)
    (METHOD_DATA=
    (DIRECTORY=+DATA/DBOMSRE7B249)))

2. Verify Oracle is detecting the correct ENCRYPTION_WALLET_LOCATION using sqlplus.

SQL> select WRL_PARAMETER,STATUS from v$encryption_wallet;

WRL_PARAMETER                 STATUS
----------------------------- ------------------------------
+DATA/DBOMSRE7B249/           CLOSED

3. Create the keystore using sqlplus.

SQL> ADMINISTER KEY MANAGEMENT create keystore '+DATA/DBOMSRE7B249' identified by abc123;

4. Open the keystore and verify its status using sqlplus.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY abc123;

keystore altered.

SQL> select WRL_PARAMETER,STATUS from v$encryption_wallet;

WRL_PARAMETER                   STATUS
------------------------------- ------------------------------
+DATA/DBOMSRE7B249/             OPEN_NO_MASTER_KEY

5. Set the key using sqlplus.

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY abc123 WITH BACKUP;

SQL> select WRL_PARAMETER,STATUS from v$encryption_wallet;

WRL_PARAMETER                  STATUS
------------------------------ ------------------------------
+DATA/DBOMSRE7B249/            OPEN

A backup will now be created in the keystore location, along with the existing keystore.

ASMCMD> ls ewallet*
ewallet.p12
ewallet_2021102504065896.p12

6. Now that the keystore and key are configured, TDE tablespaces can be created.

create tablespace tde encryption using 'AES256' default storage(encrypt);
alter user dlpxdbora quota unlimited on tde;
create table dlpxdbora.test ( id number, name varchar2(255)) tablespace tde;
SQL> insert into dlpxdbora.test values ( 1, 'encrypted');

1 row created.
SQL> commit;

Commit complete.

7. With the tablespace encrypted, the database can now be linked to Delphix or a new snapshot taken to capture the encrypted tablespace.

 

To complete exporting a keystore to target host

Once a source database contains the encryption keystore on ASM, this will need to be exported and copied to the target provisioning host. 

1. Create an empty keystore on source filesystem to merge source keys into using sqlplus

This will not change the source keystore in any way.

SQL> !mkdir /tmp/wallet

SQL> administer key management create keystore '/tmp/wallet' identified by welcome123;

keystore altered.

SQL> !ls /tmp/wallet
ewallet.p12

SQL>

2. Merge the source keystore on ASM into the file based keystore on disk using sqlplus

SQL> administer key management merge keystore '+DATA/DBOMSRE7B249' identified by abc123 into existing keystore '/tmp/wallet' identified by welcome123 with backup;

keystore altered.

SQL> !ls /tmp/wallet
ewallet.p12  ewallet_2021102504203973.p12

The orapki tool can be used to inspect the new keystore.

[oracle@mw19rac1 trace]$ orapki wallet display -wallet /tmp/wallet
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
Requested Certificates:
Subject:        CN=oracle
User Certificates:
Oracle Secret Store entries:
ORACLE.SECURITY.DB.ENCRYPTION.AWmSDN32h08ovyehfgfV73IAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
ORACLE.SECURITY.ID.ENCRYPTION.
ORACLE.SECURITY.KB.ENCRYPTION.
ORACLE.SECURITY.KM.ENCRYPTION.AWmSDN32h08ovyehfgfV73IAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
Trusted Certificates:
[oracle@mw19rac1 trace]$

3. Copy the file based keystore to the target host. In this example it is copied to a temporary location, however it could be copied directly to the final destination show later in this document.

[oracle@mw19rac1 wallet]$ scp /tmp/wallet/ewallet.p12 oracle@tde-tgt.dcol2.delphix.com:/tmp/wallet
oracle@tde-tgt.dcol2.delphix.com's password:
ewallet.p12                                                                                                                                                                                                                                                                                                    100% 3995   470.0KB/s   00:00
[oracle@mw19rac1 wallet]$

At this point, no further action is required on the source host/database. All further steps are performed on the target host.

4. Create a minimum init file to allow an instance to be started to manage the keystore.

cat $ORACLE_HOME/dbs/initTDE.ora
db_name=TDE
[oracle@tde-tgt dbs]$ export ORACLE_SID=TDE

5. Set the wallet to auto login.

The ".sso" file indicates that the keystore is autologin.

[oracle@tde-tgt dbs]$ sqlplus "/as sysdba"

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Oct 25 01:11:01 2021
Version 19.11.0.0.0

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

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  268434272 bytes
Fixed Size            8895328 bytes
Variable Size          201326592 bytes
Database Buffers       50331648 bytes
Redo Buffers            7880704 bytes
SQL> ADMINISTER KEY MANAGEMENT CREATE LOCAL AUTO_LOGIN KEYSTORE FROM KEYSTORE '/tmp/wallet' identified by welcome123;

keystore altered.

SQL> shutdown abort;
ORACLE instance shut down.
SQL>

[oracle@tde-tgt dbs]$ ls -la /tmp/wallet/
total 8
drwxr-xr-x.  2 oracle oinstall   44 Oct 25 01:11 .
drwxrwxrwt. 11 root   root      245 Oct 25 01:06 ..
-rw-------.  1 oracle oinstall 4040 Oct 25 01:11 cwallet.sso
-rw-------.  1 oracle oinstall 3995 Oct 25 01:06 ewallet.p12

6. Configure $ORACLE_HOME/network/admin/sqlnet.ora on target host.
   The DIRECTORY should using the $ORACLE_SID replacement variable to allow multiple VDBs to use the same sqlnet.ora file.

[oracle@tde-tgt oracle]$ cat $ORACLE_HOME/network/admin/sqlnet.ora
SQLNET.ALLOWED_LOGON_VERSION_SERVER=11
ENCRYPTION_WALLET_LOCATION=
    (SOURCE=
    (METHOD=FILE)
    (METHOD_DATA=
    (DIRECTORY=/u01/app/oracle/wallet/$ORACLE_SID)))

7. Copy the created wallet/sso file to the target wallet location. in this case, we will be provisioning a VDB called "MYVDB".
   This step will need to be performed before provisioning for any VDB being provisioned to this host.

[oracle@tde-tgt oracle]$ mkdir -p /u01/app/oracle/wallet/MYVDB
[oracle@tde-tgt oracle]$ cp /tmp/wallet/* /u01/app/oracle/wallet/MYVDB
[oracle@tde-tgt oracle]$ ls -la /u01/app/oracle/wallet/MYVDB
total 8
drwxr-xr-x. 2 oracle oinstall   44 Oct 25 01:17 .
drwxr-xr-x. 3 oracle oinstall   19 Oct 25 01:17 ..
-rw-------. 1 oracle oinstall 4040 Oct 25 01:17 cwallet.sso
-rw-------. 1 oracle oinstall 3995 Oct 25 01:17 ewallet.p12
[oracle@tde-tgt oracle]$

 

Provisioning should now be successful. Once the provision is complete, you can confirm by logging into the target VDB and checking v$encryption_wallet and v$encryption_key.

Which should be as follows..

QL> select WRL_PARAMETER,status,wallet_type from v$encryption_wallet;

WRL_PARAMETER               STATUS                  WALLET_TYPE
------------------------------ ------------------------------ --------------------
/u01/app/oracle/wallet/MYVDB/  OPEN                  LOCAL_AUTOLOGIN

SQL>
SQL> select key_id from v$encryption_keys;

KEY_ID
------------------------------------------------------------------------------
AWmSDN32h08ovyehfgfV73IAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

The key_id shown here should match the key_id from the source database.

 

Related Articles

The following articles may provide more information or related information to this article:

• Delphix Documentation - Provisioning a VDB from an Encrypted Oracle Database