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

11 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

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.