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.
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 18.104.22.168 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:
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:
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:
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/22.214.171.124 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/126.96.36.199 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 188.8.131.52.0 - Production on Tue Mar 17 23:12:57 2020 Version 184.108.40.206.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 220.127.116.11.0 - Production Version 18.104.22.168.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
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