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 22.214.171.124) 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 126.96.36.199 Updates. With a vanilla 188.8.131.52 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:
In addition check the wallet settings with:
orapki wallet display -wallet /u01/app/oracle/admin/UPGR/wallet Oracle PKI Tool : Version 184.108.40.206.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
You can change the auto login simply with a checkbox – just don’t forget to SAVE afterwards.
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
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.
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.
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.
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.
Appreciate Mike for your quick response , this will definitely help when I will start my upgrade journey along with TDE and expecting to go fine through command line :).
Thanks – and let me know how it goes forward!
Upgrade did failed with same issue as your’s and waiting for the update from Oracle SR update.
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
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.
Use the command line upgrade instead – this works.
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
ERRORS FOUND: During Upgrade
FILENAME: /u01/app/oracle/product/220.127.116.11/dbhome_1/diagnostics/catupgrd0.log AT LINE NUMBER: 1690293
Identifier CATALOG 18-03-16 12:40:28
SCRIPT = [/u01/app/oracle/product/18.104.22.168/dbhome_1/rdbms/admin/c1102000.sql]
ERROR = [ORA-00604: error occurred at recursive SQL level 1 ORA-28365: wallet is not open
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!
I could run my upgrade successfully in 3rd attempt.
I have shared you the SR number over chat.
Thanks for all your help!!
Thanks Malesh – I have the SR number now.
Could you please share the details of the workaround/solution from Oracle support?
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.
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.
Yes, I do agree with Mike.
Add path in SQLNET.ORA and test.
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.
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.
I’m not sure if you can approach this without having the wallet loc in sqlnet.ora.
I’d just recommend that you’d open an SR and check with support.
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.
I upgraded from 22.214.171.124 to 126.96.36.199 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.
then please open an SR. I’m not sure what the DBUA does here.
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.
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?
I know this thread is getting old but it’s indeed prevailing for us.
We cannot use autologin and dbua dows not like that (wallet not open).
Have tried diff workarounds, no one seems to work.
One option in DBUA is to offline user tablespaces i.e our TDE tbs. Even though all “TDE data is offline” the dbs still wants the wallet to be open (tried to relocate the “wallet dir”, no diff)
Is there no way to “disable” TDE during upgrade !? (execpt “remove all TDE data one way or the other)
Last resort manual upgrade !?
manual upgrade isn’t the last resort but the solution. I can’t tell you if or when DBUA fixes this. One of the reasons I run either “autoupgrade” or “preupgrade.jar + dbupgrade”.
I just finished upgrading DB RAC 12c up to 19c using DBCA.
I check not to exist folder wallet and file sqlnet.ora.
Now I copied the wallet from DR into DC but failed.
Please help me fix this issue?
please open an SR – and use AutoUpgrade instead since DBCA does not handle the wallet/keystore topic but AutoUpgrade does.
See my team mate’s blog: https://dohdatabase.com/2022/03/29/autoupgrade-tde/
Autoupgrade prechecks are failing with [TDE_PASSWORDS_REQUIRED] on a database with no TDE.
I do not understand why it has this check and why it treats this database as an encrypted database.
TDE is not used there:
SQL> SELECT * FROM dba_encrypted_columns;
no rows selected
SQL> SELECT tablespace_name, encrypted, status FROM dba_tablespaces where encrypted=’YES’ ;
no rows selected
Autoupgrade Summary Report
[Date] Thu Sep 01 15:18:46 CEST 2022
[Number of Jobs] 1
[Job ID] 107
[DB Name] $DB_NAME
[Version Before Upgrade] 188.8.131.52.0
[Version After Upgrade] 184.108.40.206.0
[Stage Name] PRECHECKS
[Start Time] 2022-09-01 15:10:51
[Log Directory] /xxxx/xxxx/107/prechecks
Check failed for $DB_NAME, manual intervention needed for the below checks
Reason:Database Checks has Failed details in /xxxx/xxxx/107/prechecks
Info:Return status is ERROR
Thanks in advance!
please use a newer version of AU. There was an issue with this check which has been corrected.
Sorry for any inconvenience.
Just a hint: The MOS note to download AU has the previous versions as well. You can always try the previous version to check if this issue happens, too. If it happens as well, then there is a general issue. But in the above case, the previous versions did not give you this error and should have worked fine.