Database Upgrade and TDE – Things to Know

Transparent Data Encryption is an excellent and very useful Oracle database feature. In this blog post I will show and highlight Database Upgrade and TDE – Things to Know.

Enabling Tablespace Encryption with TDE

For a simple example I use our Hands-On Lab and the UPGR database (Oracle 11.2.0.4) and the listener declarations in the 12.2 $ORACLE_HOME.

At first I add the following string to the sqlnet.ora file:

# sqlnet.ora
#
ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = FILE)
                             (METHOD_DATA = (DIRECTORY = /u01/app/oracle/admin/UPGR/wallet))
                             )

Note 2018, Feb 4:
My previous example had $ORACLE_BASE instead of /u01/app/oracle, the real path, in it. With $ORACLE_BASE the command line upgrade works fine but the DBUA upgrade fails. As Hector and Cindy of my team helped with different test scenarios we learned that this must be a change in the most recent 12.2.0.1 Updates. With a vanilla 12.2.0.1 installation everything works fine but as soon as you use (as I did – and Hector verified it) the January 2018 Update (RU) you need to replace $ORACLE_BASE with the real path. The same applies to the orapki call below.

Afterwards I create the directory $ORACLE_BASE/admin/UPGR/wallet. This is the default location. The database will create the encrypted file ewallet.p12 in it containing the Master Key once I execute:

ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "secret01";

Please note that the Master Key is not identical with “secret01“. Changing the Master Key can be done with the same command but will ask for the password of course. The password is case sensitive.

Furthermore from now on it’s essential to recognize that access to encrypted data requires the wallet to be opened, usually upon startup of the database:

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "secret01";

Now I will encrypt my USERS tablespaces of the UPGR database. But as some features such as online tablespace encryption are missing in Oracle Database 11g I will workaround it and encrypt the entire database after upgrade.

alter database default tablespace SYSAUX;
drop tablespace USERS;   -- it's empty in my case - otherwise relocate objects first

CREATE TABLESPACE "USERS" DATAFILE '/u02/oradata/UPGR/users_encrypt01.dbf'
    SIZE 20M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED LOGGING
    EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
    DEFAULT STORAGE ( ENCRYPT ) ENCRYPTION USING 'AES256';

alter database default tablespace USERS;

Finally I will check the tablespaces:

select TABLESPACE_NAME , ENCRYPTED from DBA_TABLESPACES order by 1;

TABLESPACE_NAME 	       ENC
------------------------------ ---
SYSAUX			       NO
SYSTEM			       NO
TEMP			       NO
UNDOTBS1		       NO
USERS			       YES

Furthermore, to make my simple text example with the converted USERS tablespace work (as I adjusted all users to have their default tablespace now on USERS) it will be very helpful to grant those users QUOTA as well:

grant unlimited tablespace to SYS;
grant unlimited tablespace to SYSTEM;
grant unlimited tablespace to OUTLN;
grant unlimited tablespace to LBACSYS;
grant unlimited tablespace to APPQOSSYS;
grant unlimited tablespace to ANONYMOUS;
grant unlimited tablespace to DIP;
grant unlimited tablespace to DBSNMP;
grant unlimited tablespace to WMSYS;
grant unlimited tablespace to XDB;
grant unlimited tablespace to ORACLE_OCM;

On a test or development system I may turn the wallet into an auto-open wallet. For a production system I wouldn’t recommend this. On the command line execute:

orapki wallet create -wallet /u01/app/oracle/admin/UPGR/wallet -auto_login

It will of course ask for the password, in my case secret01. This will create another encrypted file in the location where the wallet is stored: cwallet.sso.

In addition check the wallet settings with:

orapki wallet display -wallet /u01/app/oracle/admin/UPGR/wallet

Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Requested Certificates: 
Subject:        CN=oracle
User Certificates:
Oracle Secret Store entries: 
ORACLE.SECURITY.DB.ENCRYPTION.AT91AjV9CU81...
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
ORACLE.SECURITY.TS.ENCRYPTION.BRR7egpfmPjx...
Trusted Certificates: 

Oracle Wallet Manager

You can execute such tasks of course also with the Oracle Wallet Manager (owm), a GUI tool: Start it with owm:

Database Upgrade and TDE - Things to Know

Oracle Wallet Manager, Oracle Database 11.2.0.4

You can change the auto login simply with a checkbox – just don’t forget to SAVE afterwards.

Database Upgrade and TDE - Things to Know

Oracle Wallet Manager – Changing the AUTO LOGIN – don’t forget to SAVE

Things to Know with Upgrades and TDE?

Is there anything special to mention with database upgrades and TDE? Yes, there is.

As mentioned above you need to use the real path in your sqlnet.ora and your calls to orapki instead of the environment variable $ORACLE_BASE.

Furthermore it is VERY important if you use a release/installation dependent path that you copy your sqlnet.ora and your wallet directory manually as the DBUA won’t do this for you. In case you used the $ORACLE_BASE the DBUA may fail with multiple ORA-28365: wallet is not open errors:

The DBUA logs aren’t very helpful then:

[Thread-140] [ 2018-01-15 20:55:10.318 CET ] [StepErrorHandler.collectNonIgnorableError:517]  Collecting non-ignorable error :[[catupgrd0.log] , ORA-28365: wallet is not open]

But at least from the catupgrd0.log you may see that the issue happens during the XDB upgrade:

20:55:01 SQL> Rem ================================================================
20:55:01 SQL> Rem BEGIN XDB RDBMS Object Upgrade from 11.2.0
20:55:01 SQL> Rem ================================================================
...
Elapsed: 00:00:00.08
20:55:04 SQL>
20:55:04 SQL> create table xdb.xdb$cdbports (
20:55:04   2           pdb         number,
20:55:04   3           service     number,
20:55:04   4           port        number);
create table xdb.xdb$cdbports (
*
ERROR at line 1:
ORA-28365: wallet is not open

The command line upgrade with catctl.pl works flawless and fine.

Online TDE Conversion in Oracle Database 12.2

In Oracle Database 12.2 we included this cool feature called “Online TDE Conversion” which I wanted to try out as well to encrypt now my entire database without the need for any downtime. Franck Pachot has done some interesting stats on performance impact. It’s not a big overhead at all.

ALTER TABLESPACE SYSTEM ENCRYPTION ONLINE
  ENCRYPT FILE_NAME_CONVERT=('/u02/oradata/UPGR/system01.dbf','/u02/oradata/UPGR/system01_encrypted.dbf');
ALTER TABLESPACE SYSAUX ENCRYPTION ONLINE
 ENCRYPT FILE_NAME_CONVERT=('/u02/oradata/UPGR/sysaux01.dbf','/u02/oradata/UPGR/sysaux01_encrypted.dbf');
ALTER TABLESPACE UNDOTBS1 ENCRYPTION ONLINE
 ENCRYPT FILE_NAME_CONVERT=('/u02/oradata/UPGR/undotbs01.dbf','/u02/oradata/UPGR/undotbs01_encrypted.dbf');

If I’d like to encrypt the TEMP tablespace as well I’ll have to recreate it as temporary tablespaces can’t be encrypted online.

Further Information

Please find this very helpful post by Oracle ACE Director Arup Nanda for further information.

In addition MOS Note 1251597.1: “Quick TDE Setup and FAQ” is very helpful to start with as well.

Documentation:

–Mike

23 thoughts on “Database Upgrade and TDE – Things to Know

  1. Mike, looks like auto login wallet created problem to upgrade script so what’s your suggestion, do you suggest to keep auto login while performing upgrade to 12.2.
    Secondly you also mentioned you have to rerun multiple times the upgrade. Pl scripts, any idea what was fixed, before you run to success. Would be great if you provide some more insight.

    • Malesh,

      I actually couldn’t fix the auto login wallet issue with the DBUA upgrade, no matter how often I tried. And I tried with different listener settings.
      At the same time the command line upgrade in all my 3 different attempts completed without any issue.
      I will file a bug against the DBUA and updated the blog post then accordingly.

      Cheers,
      Mike

    • Malesh,

      the workaround should be to use REAL paths such as /u01/app/oracle/admin/MYSID/wallet in the sqlnet.ora instead of $ORACLE_BASE/MYSID/wallet.

      This is the outcome from our internal tests:

      (1) Initial problem:
      The DBUA is unable to deal with the wallet when the paths are specified with $ORACLE_BASE instead of the real path

      (2) Mike-home-made-problem:
      As Hector mentioned I should have granted unlimited tablespace to the Oracle users. Once I made the default tablespace USERS it did change for the XDB user as well. During the upgrade it tried to write objects into the USERS tablespace where it did have quota on. I still don’t understand why this didn’t happen in the command line upgrade case but I won’t investigate further.

      The (2) came in with my test only where I dropped and recreated the tablespace but without granting privs to the users using the USERS tablespace.
      The (1) problem came in secretly with one of the recent patches.

      Simple workaround:
      Use the command line upgrade instead – this works.

      Cheers,
      Mike

  2. Hi Mike,

    Thanks for your response , I did used command line tool to upgrade (./dbupgrade -n 4 -l $ORACLE_HOME/diagnostics).
    But some how it fails with ORA-28365: wallet is not open , The only difference i see is I did not used REAL paths such as /u01/app/oracle/admin/MYSID/wallet in the sqlnet.ora , instead used $ORACLE_BASE/MYSID/wallet.

    Question) While upgrading we generally keep the Listener down , to avoid any client connections traffic , so wondering setting that REAL Path in SQL*NET.ora would really help if listener is down. Correct me otherwise If I am wrong here.

    Anyways I will try again keeping that entry in sqlnet.ora and see if that work out.

    Even Oracle Support is not sure about this strange behavior and engineer mentioned he would try replicating issue.
    But just thinking if you know any of your clients have really done upgrade using TDE and fixed it , would help me as we are hitting road block here, as we have bigger landscape to upgrade with less time.

    Appreciate if you can guide Support team.

    Snip of error :
    ==========
    *** WARNING: ERRORS FOUND DURING UPGRADE ***

    1. Evaluate the errors found in the upgrade logs
    and determine the proper action.
    2. Rerun the upgrade when the problem is resolved

    REASON:
    ERRORS FOUND: During Upgrade
    FILENAME: /u01/app/oracle/product/12.2.0.1/dbhome_1/diagnostics/catupgrd0.log AT LINE NUMBER: 1690293
    ——————————————————
    Identifier CATALOG 18-03-16 12:40:28
    SCRIPT = [/u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin/c1102000.sql]
    ERROR = [ORA-00604: error occurred at recursive SQL level 1 ORA-28365: wallet is not open

    • Malesh,

      please send me the SR number (either via blog, linkedin, Twitter or email, whatever works best for you).
      Then I’ll give the support engineer owning the SR a hint.

      We did try it internally as my team mate in Mexico couldn’t verify it either. But once he patched his database to the same patch level I used the behavior is reproducable. We don’t know which patch or whatever brought in this change. It seems to happen only if you are on Oct17 or Jan18.

      Cheers – and sorry for the inconvenience!
      Mike

  3. Hi Malesh,

    Could you please share the details of the workaround/solution from Oracle support?

    Thanks,
    Sameer

  4. Mike,
    I am now testing upgrade of database with TDE and run into the same problem. In my case, the wallet is located at the default location of /u01/app/oracle/admin//wallet. The wallet is auto-login. There is nothing in SQLNET.ORA to define wallet location. Works fine in 11g. In 18c, the manual upgrade via dbupgrade fails with error:

    DELETE FROM registry$log
    *
    ERROR at line 1:
    ORA-28365: wallet is not open

    Do you recommend explicitly specifying wallet location in SQLNET.ORA with full path? There is no clear guideline on whether the 11g wallet with auto-login will open correctly in 18c or not.

    Thanks,
    Arun

    • Arun,

      to my personal excuse I’ll have to say, I started just working with TDE in 12.2 as it became mandatory in our cloud environments.
      I didn’t touch it before outside of PPT 😉

      And yes, I clearly recommend to add the location into the SQLNET.ORA.
      But I’m not sure if this is mentioned somewhere.

      I will do a quick experiment if time allows.

      Cheers,
      Mike

  5. Here are some observations. I did two things:
    a) Put the entry for ENCRYPTION_WALLET_LOCATION in 18c SQLNET.ORA
    b) After starting DB in upgrade mode, I queried the v$encryption_wallet view and it showed me the wallet was open.
    I proceeded with the upgrade and it worked. No issues.

    Then, I flashed back the DB to the pre-upgrade GRP, removed the ENCRYPTION_WALLET_LOCATION entry from the 18c SQLNET.ORA file and started the DB in upgrade mode again. This time, I did not query the v$encryption_wallet (querying this view causes the wallet to open if the wallet is auto-login). Did the upgrade again and it worked. So, I have no clue why the upgrade failed in the first try and worked the next time.

    I will be doing lot more testing with different databases in next few weeks, so I will provide update if I find something. Your blog is excellent source of knowledge. Saves lot of time and I get excellent, tried and tested information.
    Thanks,
    Arun

  6. Hello,
    I do not use sqlnet.ora to keep the wallet location. If I dont have the wallet entry in old home i.e., 11g, and if I add the real path in the sqlnet.ora file of 12c before Dbua upgrade. Will it be successful?
    During upgrade, it failed due to the above errors and I did not have sqlnet file in new home.

    • When i upgraded from 11203 to 12201 without wallet entry in sqlnet.ora, my upgrade failed with wallet not open.

      But when did the same upgrade again with wallet entry in sqlnet.ora it was successful.

      So try with wallet location in sqlnet.ora, you may also be lucky.

  7. I upgraded from 11.2.0.3 to 12.1.0.2 using DBUA and couldn’t open the wallet after the upgrade. I copied the wallet and SQLite.ora to the Oracle 12 home folders before the upgrade as you suggested. I also used real paths in the sqlnet.ora file.

      • Thanks Mike. Do you suggest using different paths in sqlnet.ora in the 11g home and 12c home. The wallet opens automatically using 11g sqlnet.ora before upgrade and tries to open wallet using 12c sqlnet.ora after upgrade. That’s when I couldn’t open the wallet and had to restore database.

        • Phil,

          I actually would try it without DBUA. As I wrote: “The command line upgrade with catctl.pl works flawless and fine.”
          Or do you see this problem during the command line upgrade?

          Cheers,
          Mike

Leave a Reply

Your email address will not be published. Required fields are marked *

* Checkbox to comply with GDPR is required

*

I agree

This site uses Akismet to reduce spam. Learn how your comment data is processed.