AutoUpgrade and the COMPATIBLE parameter

Several times I’ve got asked by customers if AutoUpgrade does change the COMPATIBLE parameter. So let me explain what happens to COMPATIBLE when you upgrade. And give you also some hints about AutoUpgrade and the COMPATIBLE parameter.

Photo by Ivana Cajina on Unsplash

Does AutoUpgrade change COMPATIBLE?

No. Clearly, AutoUpgrade does not change the COMPATIBLE parameter for several reasons. At first, we recommend to not change the COMPATIBLE parameter right after the upgrade if you can afford another downtime soon after. By keeping COMPATIBLE on the previous (before upgrade) value, you have the choice to revert back to the previous state. Either via flashing back to a Guaranteed Restore Point (GRP) right after the upgrade, or days after the upgrade with the database downgrade.

Furthermore, by default AutoUpgrade sets a GRP – and allows you to flashback to it. But having a GRP since Oracle Database 12.2.0.1 blocks the change of COMPATIBLE as long as the GRP is in place.

Actually these are enough reasons to have the AutoUpgrade not change COMPATIBLE. And still, we listen to your feedback and discussed whether AutoUpgrade may get a parameter to enforce compatibility after a successful upgrade. And with the change to the CDB-only architecture with Oracle 20c this may even play less of a role as there is no seamless simple fallback anyways anymore when you migrate to the CDB-architecture.

Can AutoUpgrade change COMPATIBLE?

Yes it can. Or actually you can convince it to do so. And here is how.

AutoUpgrade has these powerful options to execute a shell script before or after upgrade:
before_action and after_action.

I take screenshots from the Oracle Database 19c Upgrade Guide here:

This way I can pass a script to AutoUpgrade changing COMPATIBLE after the database upgrade.

But wait one second please. Didn’t I write above that you can’t change COMPATIBLE when a GRP is in place – which is the standard behavior of the tool? Yes, I did. And I will assume that you will change COMPATIBLE only in cases where you are either 100% certain that the upgrade will succeed and you won’t fall back to a GRP. Or in cases where you have another backup/restore strategy in place already, and don’t want to rely on a GRP.

Hence, I will use the parameter:

• upg1.restoration=NO

in my command file to have no GRP created.

And even if you disagree with my statement above, there is still an option. You can advice AutoUpgrade to drop the GRP automatically once the upgrade has been completed successfully:

• upg1.drop_grp_after_upgrade=YES

will drop the GRP.

A tiny example

With this config file I will upgrade my DB12 database in the Hands-On Lab:

global.autoupg_log_dir=/home/oracle/logs
upg1.dbname=DB12
upg1.start_time=NOW
upg1.source_home=/u01/app/oracle/product/12.2.0.1
upg1.target_home=/u01/app/oracle/product/19
upg1.sid=DB12
upg1.log_dir=/home/oracle/logs
upg1.upgrade_node=localhost
upg1.target_version=19
upg1.restoration=no
upg1.after_action=/home/oracle/scripts/change_compatible.sh

The key parameters are marked in RED.

If you’d like to use the GRP standard option, this would be the config file then instead:

global.autoupg_log_dir=/home/oracle/logs
upg1.dbname=DB12
upg1.start_time=NOW
upg1.source_home=/u01/app/oracle/product/12.2.0.1
upg1.target_home=/u01/app/oracle/product/19
upg1.sid=DB12
upg1.log_dir=/home/oracle/logs
upg1.upgrade_node=localhost
upg1.target_version=19
upg1.drop_grp_after_upgrade=YES
upg1.after_action=/home/oracle/scripts/change_compatible.sh

I’m executing a simple shell script after upgrade:

#!/bin/bash
# This is my shell script change_compatible.sh #
. db19
sqlplus /nolog @/home/oracle/scripts/change_compatible.sql

And just in case you are wondering (several people have asked) how I set my environment. I do set the environment with my script db19. This is owned by root and has execute rights. It is placed in /usr/local/bin, and hence can be executed by my oracle user. This is how the environment script looks like:

#!/bin/sh
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/19
OH19=$ORACLE_BASE/product/19

if [ "$1" = "" ] ; then
  ORACLE_SID=DB12
else
  ORACLE_SID=$1
fi

ORACLE_HOSTNAME=hol.localdomain
TNS_ADMIN=/u01/app/oracle/product/19/network/admin
NLS_LANG=AMERICAN_AMERICA.AL32UTF8
PATH=$ORACLE_HOME/bin:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
CLASSPATH=.:$ORACLE_HOME/jdbc/lib/classes12.jar:$ORACLE_HOME/jdbc/lib/nls_charset12.jar:$ORACLE_HOME/rdbms/jlib/xdb.jar:$ORACLE_HOME/lib/xmlparserv2.jar:$ORACLE_HOME/sqlj/lib/utl_dbws.jar
export ORACLE_BASE ORACLE_HOME ORACLE_SID OH19 NLS_LANG CLASSPATH PATH LD_LIBRARY_PATH TNS_ADMIN

export PS1="[$ORACLE_SID] \\u@\\h:\\w\n$ "

umask 022

Don’t forget to set:

chmod u+x /home/oracle/scripts/change_compatible.sh

And this shell script calls a SQL file:

connect / as sysdba
alter system set compatible='19.0.0' scope=spfile sid='*';
shutdown immediate
startup
exit

After the upgrade:

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Mar 17 23:12:57 2020
Version 19.6.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

SQL> show parameter compatible

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
compatible			     string	 19.0.0
noncdb_compatible		     boolean	 FALSE

Pretty nice – if you need and want this.

One important thing you should recognize:
You will find the logfile in <autoupg_log_dir>/<SID>/<jobnumber>/postupgrade – its name is action_stdout.log.

More Information and Links

• AutoUpgrade Command Line Parameters before_action and after_action (scroll down a bit)
• When and how should you change COMPATIBLE?
• Should you change COMPATIBLE when you apply an RU?
• Oracle Multitenant: Be aware of the silent COMPATIBLE change
• Database Upgrade to 19c – Hands-On Lab

–Mike