Is Autoupgrade with TDE only possible for in place upgrade (same server)? Are there any ways to do it for out of place (new db home in a different server) with autoupgrade? It seems like the target_home have to be specified.

A reader asked that question on my blog.

The answer is yes; you can upgrade an Oracle Database and move to a new server. We are considering upgrading a non-CDB or an entire CDB using Transparent Data Encryption (TDE) Tablespace Encryption.

Move to New Server and Transparent Data Encryption

When you upgrade your Oracle Database, you often want to move to new hardware. AutoUpgrade fully supports this use case. Mike Dietrich mentions this in his blog post and video.

When you upgrade an encrypted non-CDB or entire CDB, the database must have an auto-login keystore.

There are no further requirements.

The Instructions

I am using the DB12 database from our hands-on lab. You can provision a lab and try it out yourself (for free). See the appendix for instructions on how to encrypt the DB12 database.

Old Server

1. Always use the latest version of AutoUpgrade.
2. Create a config file:

   upg1.source_home=/u01/app/oracle/product/12.2.0.1
   upg1.sid=DB12
   upg1.target_version=19
   

   • I don’t specify target_home because it does not exist on the old server. Instead, I specify target_version, so AutoUpgrade knows which checks to execute.
3. Check the database for upgrade readiness:

   java -jar autoupgrade.jar -config DB12.cfg -mode analyze
   

4. Downtime starts.
5. Run the preupgrade fixups:

   java -jar autoupgrade.jar -config DB12.cfg -mode fixups
   

6. Perform a clean shutdown of the database

   shutdown immediate
   

New Server

There is only one server in the lab environment, so I can’t physically move to a new server. But by moving the instance manually to the new home, I can simulate the same behavior.

1. Move SPFile and password file to the new Oracle home on the new server. The below instructions work in the hands-on lab only:

   export OLD_ORACLE_HOME=/u01/app/oracle/product/12.2.0.1
   export NEW_ORACLE_HOME=/u01/app/oracle/product/19
   export ORACLE_SID=DB12
   cp $OLD_ORACLE_HOME/dbs/spfile$ORACLE_SID.ora $NEW_ORACLE_HOME/dbs
   cp $OLD_ORACLE_HOME/dbs/orapw$ORACLE_SID $NEW_ORACLE_HOME/dbs
   

   • If you copy over sqlnet.ora, you can remove encryption_wallet_location from it, because you will configure TDE using a newer method.
   • There might be other database configuration files. The blog post on files to move during out-of-place patching gives you can overview of the files to move.
2. Register the instance in /etc/oratab:

   export NEW_ORACLE_HOME=/u01/app/oracle/product/19
   export ORACLE_SID=DB12   
   cp /etc/oratab /tmp/oratab
   sed '/^'"$ORACLE_SID"':/d' /tmp/oratab > /etc/oratab
   echo "$ORACLE_SID:$NEW_ORACLE_HOME:N" >> /etc/oratab
   cat /etc/oratab
   

   • Use srvctl as well if you have Oracle Grid Infrastructure.
3. Move the database files (control files, redo logs, and data and temp files) to the new server.
   • If you need to change any of the paths, see the appendix.
   • Alternatively, unmount the storage from the old server and mount it on the new one.
4. I want to use the new wallet_root parameter to configure TDE. I copy the keystore files to a new location that matches the naming requirements of wallet_root:

   export OLD_KEYSTORE=$ORACLE_BASE/admin/$ORACLE_SID/wallet
   export NEW_KEYSTORE=$ORACLE_BASE/admin/$ORACLE_SID/wallet/tde
   mkdir -p $NEW_KEYSTORE
   cp $OLD_KEYSTORE/cwallet.sso $NEW_KEYSTORE
   cp $OLD_KEYSTORE/ewallet.p12 $NEW_KEYSTORE
   

   • You should consider moving any backup keystore files as well.
5. I start a new instance of the database in the new Oracle home and configure TDE using the new parameters:

   export ORACLE_HOME=/u01/app/oracle/product/19
   export PATH=$ORACLE_HOME/bin:$PATH
   sqlplus / as sysdba<<EOF
      startup nomount
      alter system set wallet_root='$ORACLE_BASE/admin/$ORACLE_SID/wallet' scope=spfile;
      shutdown immediate
      startup nomount
      alter system set tde_configuration='KEYSTORE_CONFIGURATION=FILE' scope=both;
   EOF
   

6. Start the instance in upgrade mode:

   sqlplus / as sysdba<<EOF
      alter database mount;
      alter database open upgrade;
   EOF
   

7. Create an AutoUpgrade config file:

   upg1.target_home=/u01/app/oracle/product/19
   upg1.sid=DB12
   

8. Start AutoUpgrade in upgrade mode:

   java -jar autoupgrade.jar -config DB12.cfg -mode upgrade
   

That’s it! I just upgraded my encrypted Oracle Database and moved it to a new server.

Appendix

Keystore Type

You must have an auto-login database keystore to upgrade it on the new server. Check the keystore type in the source database:

select wrl_type, wallet_type from v$encryption_wallet;

• AUTOLOGIN – You can copy the auto-login keystore file (cwallet.sso) from the old to the new server.
• LOCAL_AUTOLOGIN – The keystore file is bound to the old server. You must create a new auto-login keystore on the new server.

To create a new local auto-login keystore:

startup mount
administer key management create local auto_login keystore ...;
shutdown immediate
startup upgrade

Hands-on Lab

If you want to try the procedure in our hands-on lab, you can use these instructions to encrypt the DB12 database.

1. Add TDE configuration to sqlnet.ora:

echo 'ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=$ORACLE_BASE/admin/$ORACLE_SID/wallet)))' >> $ORACLE_HOME/network/admin/sqlnet.ora

2. Create keystore directory:

mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/wallet

3. Create the keystore and complete the TDE configuration:

sqlplus / as sysdba <<EOF
   --Restart to re-read sqlnet.ora with keystore setting
   shutdown immediate
   startup
   
   --Configure TDE
   ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '$ORACLE_BASE/admin/$ORACLE_SID/wallet' IDENTIFIED BY "<tde-keystore-pwd>";
   ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "<tde-keystore-pwd>";
   ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "<tde-keystore-pwd>" WITH BACKUP;
   ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '$ORACLE_BASE/admin/$ORACLE_SID/wallet' IDENTIFIED BY "<tde-keystore-pwd>";

   --Create data
   create tablespace users2 encryption encrypt;
   grant dba to appuser identified by oracle;  
   create table appuser.t1 tablespace users2 as select * from all_objects;
EOF

Locations

In the instructions, I am using the same paths for the database files. If you need to change the location of the control file or redo logs, then it might be easier to create a PFile on the source and use that instead of the SPFile.

If you need to change the location of data or temp files, it might be easier to re-create the control file. In this case, you need an alter database backup controlfile to '/tmp/ctl.txt' on the source database. Edit the trace file to generate the create controlfile statement.