To propagate a single encryption key to all of the databases in a distributed database configuration, you must create a master encryption key on the shard catalog, then use wallet export, followed by wallet import onto the shards, and activate the keys.

This procedure assumes that the keystore password and wallet directory path are the same for the shard catalog and all of the shards. If you require different passwords and directory paths, all of the commands should be issued individually on each shard and the shard catalog with shard DDL disabled, using the shard’s own password and path.

These steps should be done before any data encryption is performed.

1. Create an encryption key on the shard catalog.

   With shard DDL enabled, issue the following statements.

   ADMINISTER KEY MANAGEMENT CREATE KEYSTORE wallet_directory_path
    IDENTIFIED BY keystore_password;
   ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY keystore_password;

   The value for keystore_password should be the same if you prefer to issue wallet open and close commands centrally from the shard catalog.

   The wallet directory path should match the WALLET_ROOT in the corresponding initialization parameter file.

2. With shard DDL disabled, issue the following statement to activate the encryption key.

   ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN
    IDENTIFIED BY keystore_password;
   ADMINISTER KEY MANAGEMENT USE KEY master_key_id
    IDENTIFIED BY keystore_password WITH BACKUP;

   All of the shards and the shard catalog database now have the same encryption key activated and ready to use for data encryption. On the shard catalog, you can issue TDE DDLs (with shard DDL enabled), such as:

   • Create encrypted tablespaces and tablespace sets.

   • Create sharded tables using encrypted tablespaces.

   • Create sharded tables containing encrypted columns (with limitations).

3. Validate that the key IDs on all of the shards match the ID on the shard catalog.

   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));

4. ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY keystore_password WITH BACKUP;

   An encryption key is created and activated in the shard catalog database’s wallet.

   If you issue this statement with DDL enabled, it will also create encryption keys in each of the shards’ wallets, which are different keys from that of the shard catalog. For data movement to work, you cannot use different encryption keys on each shard.

5. Get the master key ID from the shard catalog keystore.

   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));

6. With shard DDL disabled, export the catalog wallet containing the encryption key.

   ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET secret_phrase TO
    wallet_export_file IDENTIFIED BY keystore_password;

7. Physically copy the wallet file to each of the shard hosts, into their corresponding wallet export file location, or put the wallet file on a shared disk to which all of the shards have access.

8. With shard DDL disabled, log on to each shard and import the wallet containing the key.

   ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY keystore_password;
   ADMINISTER KEY MANAGEMENT IMPORT ENCRYPTION KEYS WITH SECRET secret_phrase FROM
    wallet_export_file IDENTIFIED BY keystore_password WITH BACKUP;

9. Restart the shard databases.

10. Activate the key on all of the shards on the shard catalog with shard DDL enabled.