Question: How to create an encrypted tablespace in a 12c pluggable database of multi tenant DB?
To create an encrypted tablespace in a 12c pluggable database requires some additional steps as compared to previous version 11g. The reason is by default the wallet does not hold a tablespace key for the pluggable databases.
1. Set parameter encryption_wallet_location in sqlnet.ora
Example sqlnet.ora:
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
ENCRYPTION_WALLET_LOCATION =
(source =
(method = file)
(method_data =
(directory = /u01/app/oracle/product/12.1.0/dbhome_1/network/admin)))
2. Create the encryption wallet while connected to the container db:
sql> alter system set encryption key identified by "welcome1";
Example wallet contents after initial wallet creation:
$ mkstore -wrl . -list Oracle Secret Store Tool : Version 12.1.0.1 Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved. Enter wallet password: Oracle Secret Store entries: ORACLE.SECURITY.DB.ENCRYPTION.AX/6LjyVSU+Bv0HWal3oaDYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY ORACLE.SECURITY.ID.ENCRYPTION. ORACLE.SECURITY.KB.ENCRYPTION. ORACLE.SECURITY.TS.ENCRYPTION.BSrBWdkVPE/wvzv5Me2Wk4UCAwAAAAAAAAAAAAAAAAAAAAAAAAAA
3. It is required to backup the wallet before attempting any change:
administer key management backup keystore identified by "welcome1";
4. Connect to the pluggable database and first open the wallet:
administer key management set keystore open identified by "welcome1";
If at this point it would be attempted to create an encrypted tablespace from within the pluggable database, it will fail as follows:
sql> create tablespace data datafile * ERROR at line 1: ORA-28374: typed master key not found in wallet
5. Connect to the pluggable database and rekey the master key:
administer key management set key identified by "welcome1";
Example directory listing after creating the backup wallet and the rekey operation:
$ pwd /u01/app/oracle/product/12.1.0/dbhome_1/network/admin
$ ls -ltr total 36 -rw-r--r-- 1 oracle dba 407 Jan 19 2012 shrept.lst drwxr-xr-x 2 oracle dba 4096 Jun 25 09:48 samples -rw-r--r-- 1 oracle dba 349 Jun 25 09:57 listener.ora -rw-r----- 1 oracle dba 552 Jun 25 11:11 tnsnames.ora -rw-r--r-- 1 oracle dba 352 Jun 25 12:13 sqlnet.ora -rw-r--r-- 1 oracle dba 3112 Jun 25 12:15 ewallet.p12_initial -rw------- 1 oracle dba 0 Jun 25 12:35 ewallet.p12.lck -rw------- 1 oracle dba 0 Jun 25 12:35 cwallet.sso.lck -rw-r--r-- 1 oracle dba 3112 Jun 25 12:38 ewallet_2013062510380429.p12 -rw-r--r-- 1 oracle dba 6768 Jun 25 12:38 ewallet.p12
Example wallet contents after rekey operation from pluggable database:
$ mkstore -wrl . -list Oracle Secret Store Tool : Version 12.1.0.1 Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved. Enter wallet password: Oracle Secret Store entries: ORACLE.SECURITY.DB.ENCRYPTION.AahtI+TGak8lv2sItl3/9vsAAAAAAAAAAAAAAAAAAAAAAAAAAAAA ORACLE.SECURITY.DB.ENCRYPTION.AX/6LjyVSU+Bv0HWal3oaDYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY.DFF72E37E69844F4E0430100007F6F6C ORACLE.SECURITY.ID.ENCRYPTION. ORACLE.SECURITY.KB.ENCRYPTION. ORACLE.SECURITY.KM.ENCRYPTION.AahtI+TGak8lv2sItl3/9vsAAAAAAAAAAAAAAAAAAAAAAAAAAAAA ORACLE.SECURITY.KM.ENCRYPTION.AX/6LjyVSU+Bv0HWal3oaDYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA ORACLE.SECURITY.KM.ENCRYPTION.MASTERKEY ORACLE.SECURITY.TS.ENCRYPTION.BSrBWdkVPE/wvzv5Me2Wk4UCAwAAAAAAAAAAAAAAAAAAAAAAAAAA ORACLE.SECURITY.TS.ENCRYPTION.BZg3bagsiU+Zv9n6FAseqV4CAwAAAAAAAAAAAAAAAAAAAAAAAAAA
6. Now the encrypted tablespace can be created:
SQL> @enc_ts Tablespace created. SQL> l 1 create tablespace data datafile 2 '/u01/oradata/v1201/pdb1/data01.dbf' size 100m encryption using 'AES256' 3* default storage(encrypt) SQL>
SQL> connect system/manager1@pdb1 Connected. SQL> select tablespace_name , encrypted from dba_tablespaces; TABLESPACE_NAME ENC ------------------------------ --- SYSTEM NO SYSAUX NO TEMP NO USERS NO EXAMPLE NO DATA YES 6 rows selected.