I have been recently able to make several migration tests from On-Premises to Exadata, and during some of my tests I faced a PDB_PLUG_IN_VIOLATIONS with cause been “Wallet Key Needed” when converting a migrated Non-CDB to PDB.

Read more: Migration from Non-CDB to Multitenant : Wallet Key Needed

Explanation

This violation comes from the fact that the source Non-CDB database migrated on the Exadata from On-Premises was encrypted. The message is clear, the source wallet needs to be exported and imported in the new PDB after having run noncdb_to_pdb.sql script.

SQL> select name, cause, type, message, status from PDB_PLUG_IN_VIOLATIONS where type = 'ERROR' and status  'RESOLVED';

NAME                 CAUSE                                                            TYPE      MESSAGE                                                                STATUS
-------------------- ---------------------------------------------------------------- --------- ---------------------------------------------------------------------- ---------
PDB_NAME       Wallet Key Needed                                                ERROR     PDB needs to import keys from source.                                  PENDING

Export wallet from the Non-CDB

Connecting to the Non-CDB source database, I can check the wallet configuration.

SQL> select WRL_PARAMETER, WRL_TYPE,WALLET_TYPE, status from V$ENCRYPTION_WALLET;

WRL_PARAMETER                                                WRL_TYPE             WALLET_TYPE          STATUS
------------------------------------------------------------ -------------------- -------------------- ------------------------------
/var/opt/oracle/dbaas_acfs/DB_NAME/wallet_root/tde/             FILE                 AUTOLOGIN            OPEN

So I tried to export my wallet.

SQL> administer key management
  2  export encryption keys with secret ""
  3  to '/var/opt/oracle/dbaas_acfs/DB_NAME/DB_NAME_to_PDB_wallet.p12'
  4  identified by "************"
  5  /
administer key management
*
ERROR at line 1:
ORA-28417: password-based keystore is not open

But I faced following error:

ORA-28417: password-based keystore is not open

This is because my wallet is opened in autologin and not as password file.

So let’s close it and open it as password file.

To do so it should be sufficient to run a keystore close before running a keystore open.

SQL> administer key management set keystore close;

keystore altered.

I was also trying moving the autologin cwallet.sso file and see how that works.

I first closed my Source Non-CDB database.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Then I renamed the cwallet.sso file.

oracle@ExaCC-cl01n1:/var/opt/oracle/dbaas_acfs/DB_NAME/wallet_root/tde/ [PDB_NAME (CDB$ROOT)] mv cwallet.sso cwallet.sso.no_auto

I started again the database.

SQL> startup
ORACLE instance started.

Total System Global Area 3.7572E+10 bytes
Fixed Size                 13653168 bytes
Variable Size            4697620480 bytes
Database Buffers         3.2749E+10 bytes
Redo Buffers              111153152 bytes
Database mounted.
Database opened.

I checked the wallet type and its status.

SQL> select WRL_PARAMETER, WRL_TYPE,WALLET_TYPE, status from V$ENCRYPTION_WALLET;

WRL_PARAMETER                                                WRL_TYPE             WALLET_TYPE          STATUS
------------------------------------------------------------ -------------------- -------------------- ------------------------------
/var/opt/oracle/dbaas_acfs/DB_NAME/wallet_root/tde/             FILE                 UNKNOWN              CLOSED

As expected it was closed. I opened it.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "*****************";

keystore altered.

Checked the wallet status again.

SQL> select WRL_PARAMETER, WRL_TYPE,WALLET_TYPE, status from V$ENCRYPTION_WALLET;

WRL_PARAMETER                                                WRL_TYPE             WALLET_TYPE          STATUS
------------------------------------------------------------ -------------------- -------------------- ------------------------------
/var/opt/oracle/dbaas_acfs/DB_NAME/wallet_root/tde/             FILE                 PASSWORD             OPEN

Now the wallet is opened with password as needed. And I tried to export the wallet again.

SQL> administer key management
  2  export encryption keys with secret ""
  3  to '/var/opt/oracle/dbaas_acfs/DB_NAME/bak_DB_NAME_to_PDB_wallet.p12'
  4  identified by "************"
  5  /
administer key management
*
ERROR at line 1:
ORA-46644: creation or open of file to store the exported keys failed

I got a new error:

ORA-46644: creation or open of file to store the exported keys failed

This might be due to user permissions to write in the folder. Let’s save it in /tmp.

SQL> administer key management
  2  export encryption keys with secret ""
  3  to '/tmp/DB_NAME.p12'
  4  identified by "************"
  5  /

keystore altered.

This time it worked.

I shutdown the database again.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Moved the autologin wallet file back.

oracle@ExaCC-cl01n1:/var/opt/oracle/dbaas_acfs/DB_NAME/wallet_root/tde/ [PDB_NAME (CDB$ROOT)] mv cwallet.sso.no_auto cwallet.sso

Started the database again.

SQL> startup
ORACLE instance started.

Total System Global Area 3.7572E+10 bytes
Fixed Size                 13653168 bytes
Variable Size            4697620480 bytes
Database Buffers         3.2749E+10 bytes
Redo Buffers              111153152 bytes
Database mounted.
Database opened.

And checked that my wallet has been automatically opened again.

SQL> select WRL_PARAMETER, WRL_TYPE,WALLET_TYPE, status from V$ENCRYPTION_WALLET;

WRL_PARAMETER                                                WRL_TYPE             WALLET_TYPE          STATUS
------------------------------------------------------------ -------------------- -------------------- ------------------------------
/var/opt/oracle/dbaas_acfs/DB_NAME/wallet_root/tde/             FILE                 AUTOLOGIN            OPEN

Import the wallet in the PDB

Now we need to import the wallet in the PDB

I connected to the PDB.

SQL> alter session set container=PDB_NAME;

Session altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         4 PDB_NAME                 READ WRITE YES

Check the wallet status.

SQL> select WRL_PARAMETER, WRL_TYPE,WALLET_TYPE, status from V$ENCRYPTION_WALLET;

WRL_PARAMETER                                                WRL_TYPE             WALLET_TYPE          STATUS
------------------------------------------------------------ -------------------- -------------------- ------------------------------
                                                             FILE                 AUTOLOGIN            OPEN_NO_MASTER_KEY

I tried to import the wallet, expected it to fail as the wallet is with a autologin type.

SQL> administer key management
  2  import encryption keys with secret ""
  3  from '/tmp/DB_NAME.p12'
  4  identified by "**************"
  5  with backup USING 'pre-import-PDB_NAME'
  6  /
administer key management
*
ERROR at line 1:
ORA-28417: password-based keystore is not open

So I need to connect to the CDB$ROOT to open the wallet with the password. I checked and could see that the wallet is opened automatically for the CDB$ROOT and all PDB. The CDB$ROOT wallet is shared with the PDB.

SQL> alter session set container=cdb$root;

Session altered.

SQL> select CON_ID, WRL_PARAMETER, WRL_TYPE,WALLET_TYPE, status from V$ENCRYPTION_WALLET;

    CON_ID WRL_PARAMETER                                                WRL_TYPE             WALLET_TYPE          STATUS
---------- ------------------------------------------------------------ -------------------- -------------------- ------------------------------
         1 /var/opt/oracle/dbaas_acfs/CDB_NAME/wallet_root/tde/         FILE                 AUTOLOGIN            OPEN
         2                                                              FILE                 AUTOLOGIN            OPEN
         3                                                              FILE                 AUTOLOGIN            OPEN
         4                                                              FILE                 AUTOLOGIN            OPEN_NO_MASTER_KEY

So I closed the wallet, opened it with password and check its status.

SQL> administer key management set keystore close;

keystore altered.

SQL> administer key management set keystore open identified by "*************";

keystore altered.

SQL> select CON_ID, WRL_PARAMETER, WRL_TYPE,WALLET_TYPE, status from V$ENCRYPTION_WALLET;

    CON_ID WRL_PARAMETER                                                WRL_TYPE             WALLET_TYPE          STATUS
---------- ------------------------------------------------------------ -------------------- -------------------- ------------------------------
         1 /var/opt/oracle/dbaas_acfs/CDB_NAME/wallet_root/tde/         FILE                 PASSWORD             OPEN
         2                                                              FILE                 UNKNOWN              CLOSED
         3                                                              FILE                 UNKNOWN              CLOSED
         4                                                              FILE                 UNKNOWN              CLOSED

I connected to the PDB and tried to import the wallet.

SQL> alter session set container=PDB_NAME;

Session altered.

SQL> administer key management
  2  import encryption keys with secret ""
  3  from '/tmp/DB_NAME.p12'
  4  identified by "**************"
  5  with backup USING 'pre-import-PDB_NAME'
  6  /
administer key management
*
ERROR at line 1:
ORA-46658: keystore not open in the container

Which was not working as expected as the wallet is closed for the PDB. So I opened it with password.

SQL> administer key management set keystore open identified by "***************";

keystore altered.

SQL> select CON_ID, WRL_PARAMETER, WRL_TYPE,WALLET_TYPE, status from V$ENCRYPTION_WALLET;

    CON_ID WRL_PARAMETER                                                WRL_TYPE             WALLET_TYPE          STATUS
---------- ------------------------------------------------------------ -------------------- -------------------- ------------------------------
         4                                                              FILE                 PASSWORD             OPEN_NO_MASTER_KEY

And could now successfully import it.

SQL> administer key management
  2  import encryption keys with secret ""
  3  from '/tmp/DB_NAME.p12'
  4  identified by "******************"
  5  with backup USING 'pre-import-PDB_NAME'
  6  /

keystore altered.

I checked the wallet status, and all good, it changed from OPEN_NO_MASTER_KEY to OPEN.

SQL> select CON_ID, WRL_PARAMETER, WRL_TYPE,WALLET_TYPE, status from V$ENCRYPTION_WALLET;

    CON_ID WRL_PARAMETER                                                WRL_TYPE             WALLET_TYPE          STATUS
---------- ------------------------------------------------------------ -------------------- -------------------- ------------------------------
         4                                                              FILE                 PASSWORD             OPEN

I connected to the CDB$ROOT again. I checked the wallet status and could see it is open with password for the CDB$ROOT and new PDB and closed for the others, which might impact them. Not a problem in my case, as all here, is only used for tests for migration.

SQL> alter session set container=cdb$root;

Session altered.

SQL> select CON_ID, WRL_PARAMETER, WRL_TYPE,WALLET_TYPE, status from V$ENCRYPTION_WALLET;

    CON_ID WRL_PARAMETER                                                WRL_TYPE             WALLET_TYPE          STATUS
---------- ------------------------------------------------------------ -------------------- -------------------- ------------------------------
         1 /var/opt/oracle/dbaas_acfs/CDB_NAME/wallet_root/tde/         FILE                 PASSWORD             OPEN
         2                                                              FILE                 UNKNOWN              CLOSED
         3                                                              FILE                 UNKNOWN              CLOSED
         4                                                              FILE                 PASSWORD             OPEN

I close the wallet.

SQL> administer key management set keystore close identified by "******************";

keystore altered.

And could check that the wallet has been automatically opened again for the CDB$ROOT and all PDB.

SQL> select CON_ID, WRL_PARAMETER, WRL_TYPE,WALLET_TYPE, status from V$ENCRYPTION_WALLET;

    CON_ID WRL_PARAMETER                                                WRL_TYPE             WALLET_TYPE          STATUS
---------- ------------------------------------------------------------ -------------------- -------------------- ------------------------------
         1 /var/opt/oracle/dbaas_acfs/CDB_NAME/wallet_root/tde/         FILE                 AUTOLOGIN            OPEN
         2                                                              FILE                 AUTOLOGIN            OPEN
         3                                                              FILE                 AUTOLOGIN            OPEN
         4                                                              FILE                 AUTOLOGIN            OPEN

I restarted the PDBs and could see that the wallet key needed pdb violations has been resolved.

To wrap up

Converting an encrypted Non-CDB to PDB will require the wallet, containing the master key needed to encrypt/decrypt, to be exported from the source database and imported to the new PDB.

Last but not least, for me it was a lab, so no risk, but it is important to mention than we need to work carefully with the wallet and have good backup because loosing a wallet can be dramatical and can result in no way to access your data any more. Pay attention.

Post Views: 1,554
Thumbnail [60x60]
by
Marc Wagner