How to use Transportable Tablespace with TDE Tablespace Encryption

You can use transportable tablespace to migrate your data between databases. It is a great way of moving your data. How does it work together with TDE Tablespace Encryption?

It depends on which kind of migration you are planning. The endian format of the source and target platforms (or operating system) plays a vital role.

First, you determine the endian format of the source and target database:

SQL> select platform_name, endian_format from v$transportable_platform;
  • If the endian formats are identical, you will perform a same-endian migration.
  • If the endian formats differ, you will perform a cross-endian migration.

Same-endian Migration of TDE Encrypted Tablespaces

It is supported to use transportable tablespace when you migrate to a different platform as long as the endian format does not change.

Oracle Database uses a two-tier key architecture which consists of two encryption keys:

  • Master Encryption Key (MEK)
  • Tablespace Encryption Key (TEK)

In the tablespace, the TEK is stored. When you use transportable tablespace, you copy the data files, and thus, the TEK remains the same. The data in the tablespace continues to be encrypted during the entire migration using the same TEK.

But what about the MEK? It is required to get access to the TEK.

Option 1: Use ENCRYPTION_PASSWORD parameter

  1. During Data Pump transportable tablespace export, you specify an ENCRYPTION_PASSWORD:

    expdp ... encryption_password=<a_strong_and_secure_password>

    The encryption password is not the MEK of the source but a special password you choose for the migration only.

  2. On import, you specify the encryption password:

    impdp ... encryption_password=<a_strong_and_secure_password>

A benefit of this option is that the source and target database is encrypted using a different MEK. You can query the database and verify that no new MEK is added to the target database. The target database continues to use its own MEK:

SQL> select * from v$encryption_keys;

According to the documentation, this is the recommended option.

Option 2: Import Master Encryption Key

  1. You start the Data Pump transportable tablespace export.
  2. In the source database, you export the source database MEK:
    SQL> administer key management export keys 
         with secret "<another_strong_and_secure_password>"
         to '/home/oracle/secure_location/exported-keys'
         force keystore identified by "<source_database_MEK";
  3. You import the source database MEK into the target database:
    SQL> administer key management import keys 
         with secret "<another_strong_and_secure_password>"
         from '/home/oracle/secure_location/exported-keys'
         force keystore identified by "<target_database_MEK>"
         with backup;
  4. You start the Data Pump transportable tablespace import.

By querying v$encryption_keys, you can see that another key has been added to the database.

You can read more about export and import of MEKs in the documentation.

Option 3: Oracle Key Vault

If you are using Oracle Key Vault, it’s very easy to allow the target database to access the source database master encryption key.

When you perform the Data Pump transportable tablespace import in the target database, it will already have access to the encryption keys that protect the tablespaces. Nothing further is needed.

What About Rekeying?

If you make the source database encryption key available to the target database, consider whether you also want to perform a rekey operation. This applies to options 2 and 3.


If you use options 2 or 3, you will receive the below warning during Data Pump transportable tablespace export:

ORA-39396: Warning: exporting encrypted data using transportable option without password

This is expected behavior:

This warning points out that in order to successfully import such a transportable tablespace job, the target database wallet must contain a copy of the same database master key used in the source database when performing the export. Using the ENCRYPTION_PASSWORD parameter during the export and import eliminates this requirement.

Cross-endian Migration of TDE Encrypted Tablespaces

You can’t migrate an encrypted tablespace to a platform with a different endian format using transportable tablespace. It is not supported.

If you have an encrypted tablespace and you want to use transportable tablespace:

  1. Decrypt the tablespace
  2. Migrate the tablespace using transportable tablespace
  3. Re-encrypt the tablespace


Further Reading

Other Blog Posts in This Series

Transparent Data Encryption and Multitenant

In a multitenant environment where you want to use Transparent Data Encryption (TDE), you can do it in two ways:

  • United keystore mode. The default option. The CDB has a keystore, and all PDBs use that keystore. The encryption keys belong to each individual PDB, but the one keystore contains all the encryption keys.
  • Isolated keystore mode. Became available with 19.11.0 and in later versions. The CDB has a keystore that all PDBs can use, but you can configure a PDB to use its own keystore. If a PDB uses TDE in isolated mode, that PDB will physically have its own keystore, where only the TDE encryption keys get stored. PDBs that are not configured to use isolated mode, will put the encryption keys into the keystore of the CDB. Isolated mode is fairly new and is not fully supported yet by AutoUpgrade, OCI tooling, and other tools.

United mode is the easy way of doing things. You configure one keystore and then all PDBs can use that keystore.

Isolated mode is suitable when you want to completely isolate the PDBs and even keep the encryption keys separate. Moreover, you can have different passwords protecting the keystores. Isolated mode strengthens security but adds maintenance overhead; more keystores to backup and protect). Additionally, in isolated mode, each PDB can use a different kind of keystore. The CDB can use a software keystore (a file in the OS), PDB1 can use its own software keystore (another file in the OS), and PDB2 can store its encryption keys in Oracle Key Vault. More security and more flexibility.

Regardless of which keystore mode you plan to use, you always start by configuring TDE in united mode in the CDB. Afterward you can enable isolated mode in individual PDBs, if you want that.

How To Configure TDE

This procedure enables TDE in united mode. I will use a software keystore (a file in the OS):

  1. Create a directory where I will place the keystore. You can change $ORA_KEYBASE to another location.

    export ORA_KEYBASE=$ORACLE_BASE/admin/$ORACLE_SID/wallet
    #Don't change ORA_KEYSTORE
    mkdir -p $ORA_KEYSTORE
  2. Configure WALLET_ROOT to tell the database where I want to create the keystore files, and TDE_CONFIGURATION to tell the database to use a software keystore:

    alter session set container=cdb$root;
    alter system set wallet_root='$ORA_KEYBASE' scope=spfile;
    shutdown immediate
    alter system set tde_configuration='KEYSTORE_CONFIGURATION=FILE' scope=both;
  3. Now create the keystore and a TDE encryption key for CDB$ROOT. My TDE keystore password is oracle_4U; you should pick a better password:

    administer key management create keystore '$ORA_KEYSTORE' identified by "oracle_4U";
    administer key management set keystore open force keystore identified by "oracle_4U";
    administer key management set key identified by "oracle_4U" with backup;

    You can optionally use the CONTAINERS=ALL clause to set a TDE encryption key in all PDBs. Don’t do this if you plan on using isolated keystore later on:

    administer key management create keystore '$ORA_KEYSTORE' identified by "oracle_4U";
    administer key management set keystore open force keystore identified by "oracle_4U" container=all;
    administer key management set key identified by "oracle_4U" with backup container=all;
  4. Optionally, create an auto-login keystore. If you don’t, you must manually input the TDE keystore password every time the database starts.

    administer key management create local auto_login keystore from keystore '$ORA_KEYSTORE' identified by "oracle_4U";

That’s it. You can now start to create encrypted tablespaces:

create tablespace ... encryption encrypt;

I have now created the root keystore in the location defined by WALLET_ROOT. The database automatically adds a subfolder called tde. In that folder you find ewallet.p12 which is the actual software keystore of the CDB, and cwallet.sso which is the auto-login keystore:

$ pwd
$ ll
total 8
-rw-------. 1 oracle dba 4040 May 16 09:35 cwallet.sso
-rw-------. 1 oracle dba 3995 May 16 09:35 ewallet.p12

Configure Isolated Keystore

You can enable isolated mode in a PDB after you configure the CDB for united mode (the above procedure). The following assumes that TDE has not been configured yet in PDB1:

  1. Switch to the PDB and configure TDE_CONFIGURATION:
  2. Create the keystore and a TDE encryption key for the PDB. Notice I am giving my PDB keystore a different password:
  3. Optionally, create an auto-login keystore of the PDB keystore. If not, you need to manually input the TDE keystore password in the PDB every time it starts:

The PDB keystore is now placed in a subfolder of WALLET_ROOT matching the PDB GUID (D6A29777EC214B6FE055000000000001). You find similar files, ewallet.p12 and cwallet.sso in the dedicated folder for the isloated PDB keystore:

$ pwd
$ ll
total 8
-rw-------. 1 oracle dba 2120 May 16 09:37 cwallet.sso
-rw-------. 1 oracle dba 2059 May 16 09:35 ewallet.p12

To get the GUID of a PDB:

select name, guid from v$containers;

The database will automatically create the directories needed for the PDB keystore.

Migrating Between Keystore Modes

If you need to migrate between the two keystore modes, there are two commands you can use. oracle_4U is the keystore password of the root keystore; oracle_4U2 is the keystore password of the PDB keystore.

To migrate a PDB from united to isolated mode, i.e., to isolate a keystore:

alter session set container=PDB1;
administer key management
   force isolate keystore identified by "oracle_4U2" 
   from root keystore force keystore identified by "oracle_4U"
   with backup;

To migrate a PDB from isolated to united mode, i.e., to unite a PDB keystore into a root keystore:

alter session set container=PDB1;
administer key management
   unite keystore identified by "oracle_4U2" 
   with root keystore force keystore identified by "oracle_4U" 
   with backup;

To determine which keystore mode is in use:

select con_id, wrl_parameter, keystore_mode 
   from v$encryption_wallet;

Final Notes

If you want to use isolated keystore mode in 19.11, 19.12 or 19.13 you need to apply patch 32235513 as well. From 19.14 and onwards this is not needed.

Isolated mode used to be a cloud-only feature. But since 19.11 it has been made available to everyone.

Further Reading

Future-proof Your Encrypted Database During Upgrade

In Oracle Database 19c, use of sqlnet.ora to define the keystore (or wallet) location has been deprecated. Instead you should use the database parameter WALLET_ROOT. If you upgrade to Oracle Database 19c with AutoUpgrade, it has become a lot easier. Let AutoUpgrade do the work for you.

How To

If you instruct AutoUpgrade to use the new encryption parameters it will not only add the parameters to the SPFile but also copy the keystore file to the location defined. This is what you have to do:

  1. Create a text file which contains the definition of WALLET_ROOT and TDE_CONFIGURATION. I call it /tmp/au-pfile-tde. Optionally, change the location of the keystore to fit your organization.
  1. Instruct AutoUpgrade to add those parameter during and after upgrade. Add the following to your AutoUpgrade config file:

That’s it! AutoUpgrade will detect that you are changing the keystore location, and it will copy the keystore files to the new location at the appropriate time.


  • When you use WALLET_ROOT the keystore files should always be stored in a subfolder called tde. This means that the keystore files will end up in /etc/oracle/keystores/$ORACLE_SID/tde. You should not add /tde manually to WALLET_ROOT. The database will do that automatically when it looks up the keystore.
  • If you want to move the keystore into ASM, there is a special procedure that you must follow. AutoUpgrade can’t do that for you. Check How To Migrate TDE Wallet From Local OS File System to ASM ? (Doc ID 2369614.1).

Now What

Since you have moved the keystore files to a new location there are some things that you should take care of:

  • You can remove the sqlnet.ora parameter ENCRYPTION_WALLET_LOCATION. It is not used anymore.
  • The keystore files that were stored in the old location (that defined by ENCRYPTION_WALLET_LOCATION) can be moved manually to a backup location. I would never recommend that you delete keystore files – NEVER! Instead move the old files to a backup location and keep them there.
  • The keystore files are to be considered critical and contain sensitive information, so ensure that the new location has the same security measures as the old one – like:
    • Restricted file permissions
    • Auditing
    • Backup


Traditionally, if you have an encrypted database, you need to define the keystore location in sqlnet.ora using the parameter ENCRYPTION_WALLET_LOCATION. You would set it to something like this:


For many reasons, sqlnet.ora was not a good location for this parameter, and especially with the introduction of isolated keystore mode, a new method was needed.

In came WALLET_ROOT and TDE_CONFIGURATION database initialization parameters. The former, WALLET_ROOT, defines the location of the keystore. The latter, TDE_CONFIGURATION, defines which kind of keystore is in use. Typically, it is set to FILE – that’s when you use a software keystore (a file in the OS). But it could also be OKV if you are Oracle Key Vault. For a software keystore you would set it to something like:


Now, the database finds the keystore location using the WALLET_ROOT parameter which is much more smooth.

As of Oracle Database 19c, configuration the keystore using sqlnet.ora has been deprecated, and as with any other deprecated functionality, you should move to a fully supported alternative.

Further Reading

Other Blog Posts in This Series

How to Stop Hardcoding Your TDE Keystore Password

I was helping a customer the other day together with Mike. They were upgrading from 18c to 19c and had to convert the database to a PDB as well. At first glance, it seemed pretty straightforward, but things got complicated because:

  • They have standby databases and want the Data Guard setup to survive the plug-in operation.
  • They are using ASM.
  • They are using TDE Tablespace Encryption and have also encrypted their SYSTEM and SYSAUX tablespace.
  • The DBA that will carry out the upgrade and plug-in is not allowed to have the TDE Keystore password. They have separation of duties, so only the security admins have the keystore password.

Can you do that? Yes, you can! Let me tell you how.


First, upgrade the database. You can easily maintain the Data Guard setup during an upgrade. I wrote a blog post about a little while ago. In addition, to upgrade a database with encrypted tablespaces you don’t need the keystore password. You must configure the database to use an auto login keystore, and that’s it. If you are concerned about the use of an auto-login keystore, you can simply remove it again after the upgrade.

External Store for a Keystore Password

The plug-in operation will require the keystore password. But the DBA doesn’t know it – so we need to find a solution for that. The solution is to store the keystore password in an external store. I also wrote a blog post about that a while ago. When you have it configured you can exchange the commands that require a keystore password, like:


With this:


The database will get the keystore password from an external store, which is basically a file in the file system which is encrypted with a password that only the database know.

The security admins would need to do this in the CDB that will receive the non-CDB database. They can do it in advance, so they can relax while the DBA carries out the operation in a maintenance window. If the TDE keystore is already configured using the WALLET_ROOT parameter, you can use the feature right away. Otherwise, you need a database restart to configure it.

Like with the auto-login keystore, if you are concerned about the security, you can simply disable it again after the operation.

Plug In

Now things get complicated. When you plug in your non-CDB database the manifest file contains information on where the data files are located – but only on the primary database. This is an extract of a manifest file (the one you create with DBMS_PDB.DESCRIBE):


After plug-in, the CDB can start to use the data files right away. It reads from the manifest files where the data files are located. But there is no information on where files are located on the standby database. To overcome this you must create aliases in the ASM instance on the standby host. The aliases will point back to the original data files (used by the standby database). So, when the plug-in happens and redo start to flow to the standby database, it will know which data files to recover. If you are storing data files in a regular file system, you could use soft links to serve the same purpose.

The procedure is already very well described:

I won’t repeat the procedure as the above articles are really good. But these articles don’t consider the situation where your SYSTEM and/or SYSAUX tablespace is encrypted.

If that is the case, you must import your encryption keys into CDB$ROOT before you execute the CREATE PLUGGABLE DATABASE command. In Reusing the Source Standby Database Files When Plugging a non-CDB as a PDB into the Primary Database of a Data Guard Configuration (Doc ID 2273304.1) it should happen right before step 17.2.2:

SQL> alter session set container=CDB$ROOT;
SQL> administer key management import keys ... keystore identified by external store ... ;
SQL> --Continue with step 17.2.2
SQL> create pluggable database .... ;

Dots and Underscores

When you follow the MOS notes you might wonder why the dots in the ASM aliases are replaced with underscores. At first glance, I had no idea, but it worked. I later learned the following:

The format for an ASM filename is [filetype|tablespacename].[ASM file number].[file incarnation], but basically it is three pieces of name separated by periods. We can’t create any filename or alias that mimics that format. So the scripts change those periods to underscores (‘_’). That is allowed.

ORA-15032 and ORA-15046

Most likely you get this error because there are already existing aliases on the ASM file. Only one alias is allowed per file.

  1. You can verify that by using the ls command in ASMCMD. If it is an alias the Name column will look similar to this alias1 => +DATA/......
  2. Ensure the database is not using the alias. If it does, rename the file in the database.
  3. Remove the alias from ASM. It is strongly recommended to use rmalias. Although also possible with rm I consider it much safer to use rmalias.


You can upgrade and convert your database to a PDB without comprising your standby database. In addition to that, you can configure your database in such a way that you don’t even need to type in the TDE keystore password.

TDE from a Non-Security Guy

TDE Tablespace Encryption is a must-have in the cloud and something that most of us have to learn. I recently gave a presentation on the basics and what you will need to learn to survive in the cloud.

If you are interested in the recording, it is uploaded to our YouTube channel. It is 43 minutes and no one will probably see it from start to end. But you should really have a look at the Secure External Password Store. It is a really awesome feature. There is a demo right here.

Danish Oracle User Group

The presentation was organized together with the Danish Oracle User Group and I have also published the slides.

I made a quick poll during the presentation to ask how many years the attendees had worked with databases without worrying about encryption. The answers ranged from eight to 35 (or even 42 – but was that a joke?). The majority was above 20. But with the evolution of the cloud this is about to change. You must learn about encryption these days.

I also mentioned some indications that AES256 is not necessarily more secure than AES128 (the latter being the default encryption algorithm in Oracle Database 19c). I can’t tell you why – that’s way beyond my knowledge – but here are the references:

Also, there was a comment from Asger stating that an encrypted database is hard to compress by your storage system. That is true – normally encryption is bad for your compression ratio. Unless you use compression options that are built into the database. It will compress the data before it is encrypted. You can have your cake – and eat it!

And just a few final words of advice:

  • Always back up your keystore
  • Never delete a keystore
  • Never forget your keystore password

I had much fun talking about TDE. The more I explore, the more I like it. Thanks to those that attended.