On some days you just want want to perform a simple task, one you have done several times before and know that it will not take too much time to complete. But sometimes you will face a new challenge and spend a whole day to fix an issue that can’t be fixed. Such a day was today. What happened? I wanted to clone a PDB via DB link from a source CDB to a target CDB. So far so easy, isn’t it?

SQL> create pluggable database tgtpdb from srcpdb@srcpdb keystore identified by "xxx" including shared key parallel 8;

Error starting at line : 1 in command -
create pluggable database tgtpdb from srcpdb@srcpdb keystore identified by "xxx" including shared key parallel 8
Error report -
ORA-46659: master keys for the given PDB not found
1.     00000 - "master keys for the given PDB not found"
*Cause: The master keys for the specified pluggable database (PDB) could not
be retrieved.
*Action: Check if the master keys were generated for the specified PDB and
retry the command.

Oh well. I have done this in the past with the same target and source PDBs. OK, let’s check if the source has master encryption keys available (for con_id 5) and if so, let’s try to export the encryption key manually to transfer it.

SQL> select inst_id, con_id, key_id from v$encryption_keys order by 1,2;

   INST_ID     CON_ID KEY_ID
---------- ---------- ------------------------------------------------------------------------------
         1          1 XXXYYYYCCCCDDDDD5BFCE131CE76F23F2
         1          3 XXXYYYYCCCCDDDDDABFCF6C44EF9F325E
         1          4 XXXYYYYCCCCDDDDD7BF285FC4E7CA8678
         1          5 XXXYYYYCCCCDDDDDCBFAEB078D7CF4EB6

SQL> administer key management export keys with secret "MyDirtyLittleSecret" to '/tmp/srcpdbmek.exp' force keystore identified by "MyOtherLittleSecret" with identifier in 'XXXYYYYCCCCDDDDDCBFAEB078D7CF4EB6';

Error starting at line : 1 in command -
administer key management export keys with secret "MyDirtyLittleSecret" to '/tmp/srcpdbmek.exp' force keystore identified by "MyOtherLittleSecret" with identifier in 'XXXYYYYCCCCDDDDDCBFAEB078D7CF4EB6'
Error report -
ORA-46650: cannot retrieve information for a master key identifier
46650. 00000 - "cannot retrieve information for a master key identifier"
*Cause: The master key or the master key information associated with a
specified master key identifier did not exist.
*Action: Check the contents of the trace file to identify the faulty master
key identifier. Either fix or drop the faulty master key, then
retry the command.

As you may see above, it didn’t work either. So I created a service request at Oracle Support (which did not help in any way yet) and started do fiddle around with this. It was obvious that there’s something wrong when accessing the key. Since last time of successful clone operation we have changed the keystore from a local TDE wallet at the Exadata Cloud Service machines to the OCI Vault using keys in a HSM module. The customer wanted to have secured key management. And oh boy was it secure now. We couldn’t even clone a PDB to another container that is using another encryption key.

After some research in the Oracle documentation I recognized that all works as designed, since the key material is not supposed be exported from the HSM in the Vault Service. So I tried to work around the issue by switching back to a local wallet. Time was running and we needed the cloned PDB soon. So I went to the cloud console and chose to manage the encryption key from the UI.

But we can’t! Once you have converted your “Oracle-managed” key (which is not entirely true) to a “customer-managed” key (which isn’t true either) you cannot switch back. At least from the UI. So I logged into one of the Exadata hosts and had a look at what dbaascli could do. Studying the command help I found the following nice little command.

% dbaascli tde hsmToFile
DBAAS CLI version 23.3.2.0.0
Executing command tde hsmToFile

    tde hsmToFile - convert HSM(KMS/OKV) based TDE to FILE based TDE.

      Usage: dbaascli tde hsmToFile --dbname <value>
        {
            [--prepareStandbyBlob <value> [--blobLocation <value>]]
            | [--standbyBlobFromPrimary <value>]
        }
        [--skipPatchCheck <value>] [--executePrereqs] [--primarySuc <value>]
        {
            [--resume [--sessionID <value>]]
            | [--revert [--sessionID <value>]]
        }
        [--waitForCompletion <value>]

So I invoked the command and after 15 Minutes the DB is using a local file based keystore again. Be aware that the command will restart the database several times without notification beforehand, so you better ask for a planned downtime. Also don’t forget to run the same command on the Standby database with the tar file provided when the run on the Primary database was completed successfully. Also make sure that the tar/blob is readable by the oracle user.

After the change of the key management in the source and target CDB I was able to perform the initially intented clone operation. But now I need to figure out how to do “cusotmer-managed” key management properly that is able to clone PDBs between CDBs.

UPDATE: There is a solution to the issue without using a workaround like above, which I describe in Part II.