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
- Always use the latest version of AutoUpgrade.
- 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.
- Check the database for upgrade readiness:
java -jar autoupgrade.jar -config DB12.cfg -mode analyze
- Downtime starts.
- Run the preupgrade fixups:
java -jar autoupgrade.jar -config DB12.cfg -mode fixups
- 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.
- 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
- 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.
- 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.
- 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.
- 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
- Start the instance in upgrade mode:
sqlplus / as sysdba<<EOF
alter database mount;
alter database open upgrade;
EOF
- Create an AutoUpgrade config file:
upg1.target_home=/u01/app/oracle/product/19
upg1.sid=DB12
- 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.
- 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
- Create keystore directory:
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/wallet
- 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.
Databases Are Fun 