Skip to main content
Delphix

Issue with Oracle 12c PDBs multi-tenant using Transparent Data Encryption (TDE)

vpdb_tde.shTHIS ARTICLE IS NOT PUBLIC, PLEASE DO NOT DISTRIBUTE

Issue

If you have an Oracle 12c Container Database (CDB) with a Pluggable Database (PDB) that uses Oracle's Transparent Data Encryption (TDE) there could be issues when provisioning a virtual PDB from this PDB dSource. In this case the provision fails because the vPDB is left in READ WRITE RESTRICTED mode. If you manually re-enable the vPDB, the PDB_PLUG_IN_VIOLATIONS view of the target CDB has a plugin error for the vPDB that refers to missing keys:

SQL> select cause, message from pdb_plug_in_violations
   where name = '<vPDBNAME>' and type = 'ERROR'; 
CAUSE                MESSAGE
-------------------- ----------------------------------------
Wallet Key Needed    PDB needs to import keys from source.

If vPDB recovery touches any encrypted data blocks during recovery then the process can also fail with "ORA-28365: wallet is not open" or "ORA-28374: typed master key not found in wallet", depending the target system's wallet configuration.

Troubleshooting

Normally when the vPDB provision fails the Delphix Engine will Disable the vPDB, which unplugs it from the target CDB and removes all mountpoints. Use the following procedure to re-enable the vPDB and reattach it to the target CDB:

  1. In the Delphix command-line interface, as the delphix_admin user or user with admin privileges, enable the vPDB source with attemptStart=false
    ssh delphix_admin@yourengine
    > cd source
    > ls
    > select "<vPDBNAME>"
    > enable
    > set attemptStart=false
    > commit
  2. On the target system, execute mount-vdb.sh from the vPDB's toolkit folder to remount the NFS shares 

    sh <toolkit_dir>/Delphix_*/databases/oracle/<vPDBNAME>-$ORACLE_SID/$ORACLE_SID/mount-vdb.sh
  3. In the target CDB, plugin the vPDB 

    create pluggable database <vPDBNAME> using
       '<mountpoint>/<vPDBNAME>-<ORACLE_SID>/datafile/delphix_group_writable/<vPDBNAME>.xml'
       NOCOPY TEMPFILE REUSE;
    alter pluggable database <vPDBNAME> open read write;
    show pdbs

When opening the vPDB it is normal to receive a warning that the PDB was altered with errors. The output of SHOW PDBS will indicate that the vPDB is in restricted mode. Once the vPDB is plugged into the CDB you can inspect any plugin errors in the target CDB's PDB_PLUG_IN_VIOLATIONS view.

Resolution

In order to successfully provision vPDBs from a dSource using TDE, the "Wallet Key Needed" plugin violation must be resolved by exporting the encryption keys used by the PDB dSource and importing them an encryption wallet that has been opened by the vPDB. Additionally, source PDB keys must be made available to the temporary recovery CDB so that any encrypted redo can be applied during vPDB recovery.  The procedure outlined below will set up the system so that both tasks succeed during vPDB provision/refresh/rewind/rollback.

This procedure is only supported for Oracle 12.1.0.2. Attempting this procedure with Oracle 12.1.0.1 is likely to fail with ORA-46655 errors. See Additional Details for further information.

  1. On the production source, export the CDB and PDB master keys:
    1. Login to the source CDB with SYSDBA or SYSKM privileges

    2. Check the status of the source CDB's encryption wallet:

      select wrl_parameter, status, wallet_type from v$encryption_wallet;
    3. If the wallet's status is CLOSED, open it with a password:

      administer key management set keystore open
         identified by <source_keystore_password> container=all;
    4. If the wallet's status is OPEN and the wallet type is AUTOLOGIN, close the autologin wallet and reopen it with a password:

      The SQL below creates a brief time window where no wallet is open. Applications or users attempting to access encrypted blocks may receive "ORA-28365: wallet is not open". If in doubt, ask the customer for a brief downtime window.

      alter system set wallet close;
      administer key management set keystore open
         identified by <source_keystore_password> container=all;
    5. From a shell session, create a temporary keystore directory:

      mkdir -p /tmp/tempkeystore
    6. In SQLPlus, create a temporary keystore in the temp directory:

      administer key management create keystore '/tmp/tempkeystore'
         identified by delphix;
    7. Merge all of the source system's keys into the temporary wallet:

      administer key management
         merge keystore '<source_keystore>'
         identified by <source_keystore_password>
         into existing keystore '/tmp/tempkeystore'
         identified by delphix;
    8. For each dSource PDB, export the PDB's master keys:

      host rm -f /tmp/<PDBname>_keys.exp
      alter session set container=<source_PDB_name>;
      administer key management export keys with secret "secret"
         to '/tmp/<PDBname>_keys.exp'
         identified by <source_keystore_password>;
    9. If the source wallet was not originally opened with a password, return it to its original state:

      alter session set container=cdb$root;
      alter system set wallet close identified by <source_keystore_password>;
  2. Confirm that the target systems's wallet meets the following requirements:

    1. $ORACLE_HOME/network/admin/sqlnet.ora must contain a static wallet location.  Variables such as $ORACLE_SID or $ORACLE_UNQNAME will prevent the temporary recovery CDB from finding the target wallet, and vPDB recovery may fail with "ORA-28365: wallet is not open"
    2. For RAC targets, the wallet should be in shared storage that is accessible to all nodes.
    3. The target system's wallet must have a valid master key for the CDB.  Run the following query to list the current valid master keys:

      alter session set container=cdb$root;
      select key_id, activation_time from v$encryption_keys
         where activating_pdbname = 'CDB$ROOT'
         and activating_dbname = (select instance_name from v$instance)
         order by 2 desc;

      If the above query returns no rows, you can try to set the CDB's master key using the following command:

      alter session set container=cdb$root;
      administer key management set key
         identified by <target_keystore_password> with backup;

      The above command can fail with "ORA-28374: typed master key not found in wallet" if the wallet contains CDB$ROOT keys from another database, in which case the wallet will likely need to be rebuilt.  The procedure for a wallet rebuild depends greatly upon the state of the target system and the existence of non-virtual PDBs with encrypted objects, and is beyond the scope of this KB article.

  3. Configure the target system

    1. Copy the temporary wallet directory from step 1e to the target system, e.g.:

      scp -r ora12102@<source_system>:/tmp/tempkeystore /tmp/
    2. Create a permanent filesystem location to hold exported source PDB keys, e.g.:

      mkdir -p /u01/app/ora12102/keyexports
    3. Copy the source PDB key exports from step 1h to the target system, e.g.:

      scp <user>@<prod_system>:/tmp/*_keys.exp /u01/app/ora12102/keyexports/
    4. Check the status of the target CDB's encryption wallet; it should be OPEN with wallet type AUTOLOGIN:

      select wrl_parameter, status, wallet_type from v$encryption_wallet;
    5. Merge the source CDB's keys into the target system's wallet; this merge is required to ensure that vPDB recovery does not fail with "ORA-28374: typed master key not found in wallet":

      administer key management
         merge keystore '/tmp/tempkeystore'
         identified by delphix
         into existing keystore '<target_keystore>'
         identified by '<target_keystore_password>';
    6. Close the autologin wallet:

      alter system set wallet close;
    7. From a shell session, remove any existing cwallet files from the target's wallet:

      rm -f <target_wallet>/cwallet.sso*
    8. Recreate the autologin wallet:

      administer key management create auto_login keystore
         from keystore '<target_keystore>'
         identified by <target_keystore_password>';
    9. Confirm that the target's wallet status is now OPEN with wallet type AUTOLOGIN

      select wrl_parameter, status, wallet_type from v$encryption_wallet;

Once the above setup has been completed, vPDBs can be successfully provisioned and refreshed.  However, the PDB will be left in restricted state with the "PDB needs to import keys from source" plugin violation after each provision and refresh.  When necessary, vPDB keys can be imported manually using the following procedure:

      1. Open the target CDB's wallet with a password:

        alter system set wallet close;
        administer key management set keystore open
           identified by '<target_keystore_password>';
      2. Import the dSource PDB's keys into the vPDB

        alter session set container=<vPDB-name>;
        administer key management import keys with secret "secret"
           from '/u01/app/ora12102/keyexports/<dSource-PDB-name>_keys.exp'
           identified by <target_keystore_password> with backup;
      3. Close and reopen the vPDB

        alter session set container=cdb$root;
        alter pluggable database <vPDB-name> close instances=all;
        alter pluggable database <vPDB-name> open read write instances=all;
      4. Rekey the vPDB to prevent future ORA-46655 errors

        alter session set container=<vPDB-name>;
        administer key management set key
          identified by <target_keystore_password> with backup;
      5. Close the password-based keystore; the keystore should reopen automatically with a wallet state of AUTOLOGIN

        alter system set wallet close identified by <target_keystore_password>;

It would also be possible to automate the key import process using a hook.  A sample hook script is attached.  The script can be added to vPDBs as a "Run Bash Shell Command" operation under Post Start hooks.

Additional Information

Error "ORA-46655: no valid keys in the file from which keys are to be imported" would indicate that all keys you are trying to import are already in the target system's wallet.  This can happen if another vPDB from the same source is actively using the same key ID, as Oracle does not allow duplicate active keys.  Ensure that other vPDBs from the same source have been rekeyed to use a unique key.

In Oracle 12.1.0.1, "ORA-46655: no valid keys in the file from which keys are to be imported" occurs even when importing keys for the first vPDB for a given PDB dSource.  We must put the source keys in the target's wallet to ensure that vPDB discovery does not fail, but under Oracle 12.1.0.1 this prevents us from importing the same keys to resolve the vPDB plugin violation.  This is the reason that Oracle 12.1.0.1 cannot be supported for this procedure.

Encryption keys cannot be exported or imported when the wallet is opened via autologin. Attempting export/import operations on an AUTOLOGIN wallet will result in error "ORA-28417: password-based keystore is not open".  This is an Oracle-imposed limitation.

The sample hook script above would be very susceptible race conditions / contention surrounding the target CDB's keystore. We should strongly discourage running multiple concurrent vPDB operations on the same target CDB when using the hook above.

If you receive error "ORA-28365: wallet is not open" during database recovery, it means that the temporary CDB is not opening the correct wallet.  Confirm that the ENCRYPTED_WALLET_LOCATION specified in sqlnet.ora does not contain any dynamic values (e.g. $ORACLE_SID or $ORACLE_UNQNAME), and that the wallet has AUTOLOGIN enabled (contains a cwallet.sso file).

If you receive error "ORA-28374: typed master key not found in wallet" during database recovery or key import, it likely means that the source system has been rekeyed and the target is missing the new master key.  This query can be run on the source system to see the list of source keys:

select key_id, activating_dbname, activating_pdbname, activation_date from v$encryption_keys;

Compare the source key IDs to the contents of the target system's wallet, which can be inspected using orapki:

orapki wallet display -wallet <target_keystore> -pwd <target_keystore_password>

If the customer has rekeyed their source CDB or PDB then steps 1 and 3 above will need to be repeated to copy fresh keys to the target system(s).

"ORA-28374: typed master key not found in wallet" can also occur when attempting to close the target system's autologin wallet.  In testing this was found to be caused by copying an autologin (cwallet.sso) file from the source system.  The customer may need to remove and recreate the cwallet.sso file to resolve this condition.

There is currently an outstanding issue with rewind, rollback and disable/enable operations.  Oracle will not allow import of keys into VPDBs that have been rewound or disabled and reenabled, as it requires reimporting a key ID that already exists in the target's wallet.  VPDBs that are rewound or disabled/enabled will be left with the "wallet key needed" plugin violation, and attempts to import keys to resolve the plugin violation will fail with ORA-46655.  This issue is still under investigation.

External Links (if applicable)

TDE Wallet Problem in 12c: Cannot do a Set Key operation when an auto-login wallet is present (Doc ID 1944507.1)

 
Script is attached
 
  • Was this article helpful?