Upgrade and profile scripts

In short: When upgrading an Oracle database to any release, I recommend to:

• Remove the glogin.sql file from the target Oracle Home (sqlplus/admin subfolder).
• Remove login.sql from current directory, if present.
• Unset SQLPATH environment variable.

That’s it. Shortest blog post ever…

If you want a little more detail, read on. This blog post started by a tweet I saw the other day. I vaguely remember from my days outside Oracle (in the real world) that the upgrade run book I used had similar steps.

What If

What might happen if you don’t do it? AutoUpgrade might fail with:

-------------------------------------------------
Errors in database [CDB1]
Stage     [DBUPGRADE]
Operation [STOPPED]
Status    [ERROR]
Info    [
Error: UPG-1400
UPGRADE FAILED [CDB1]
Cause: Database upgrade failed with errors
For further details, see the log file located at /home/oracle/upg_logs/CDB1/CDB1/100/autoupgrade_20200804_user.log]

A command line upgrade might fail with:

Unexpected error encountered in catctlMain; Error Stack Below; exiting
Died at /u01/app/oracle/product/19/rdbms/admin/catctl.pl line 9188.
 at /u01/app/oracle/product/19/rdbms/admin/catctl.pl line 9188.
    main::catctlDie("\x{a}Unexpected error encountered in catconInit; exiting\x{a} No chil"...) called at /u01/app/oracle/product/19/rdbms/admin/catctl.pl line 37
27
    main::catctlDBLogon("/u01/app/oracle/product/19/rdbms/admin", "/home/oracle/upg_logs/CDB1/CDB1/100/dbupgrade", "catupgrd20200804112601", "CDB\$ROOT", 0, 4) ca
lled at /u01/app/oracle/product/19/rdbms/admin/catctl.pl line 3782
    main::catctlLogon() called at /u01/app/oracle/product/19/rdbms/admin/catctl.pl line 1422
    main::catctlMain() called at /u01/app/oracle/product/19/rdbms/admin/catctl.pl line 1370
    eval {...} called at /u01/app/oracle/product/19/rdbms/admin/catctl.pl line 1368

------------------------------------------------------
CATCTL FATAL ERROR
------------------------------------------------------

And Database Upgrade Assistant might fail like this:

And when you dig a little further into the logs you will find ORA-04023: Object SYS.STANDARD could not be validated or authorized.

Notice that I write that the error might happen. That is because it all depends on what you put in glogin.sql or login.sql. Some things might work – but some for sure won’t. One thing that is often used is SET SERVEROUT ON and that will for sure break the upgrade.

Why

glogin.sql is officially named site profile script and login.sql is named user profile script. As mentioned in the documentation they are executed each time you start SQL*Plus and can contain anything that you would type in SQL*Plus. A lot of the commands you use in SQL*Plus are using functionality in the database. Take for example SET SERVEROUT ON which uses the database package DBMS_OUTPUT.

When a database is upgraded it is started in UPGRADE mode which changes the behaviour of the database a lot. Further, the upgrade needs to change a lot of functionality in the database. It does so by calling SQL*Plus to execute a script – and it must execute many scripts. Simplified, it is something similar to:

sqlplus / as sysdba @upgrade.sql

If you define a profile script during upgrade you are effectively injecting code to be executed before the upgrade script. And if your profile script is trying to use functionality that doesn’t work for the time being an error occurs. Some scripts are set to exit on SQLERROR which will break the upgrade. Others, ignore the error only to fail later on when the log file is inspected for unexpected error messages.

Imagine what happens when the upgrade tries to replace the package DBMS_OUTPUT and at the same time there is a profile script which sets SET SERVEROUT ON. We are trying to use the same functionality there are being replaced.

How

You can use this little example to avoid the problem:

export TARGET_ORACLE_HOME=/u01/app/oracle/product/19
mv $TARGET_ORACLE_HOME/sqlplus/admin/glogin.sql $TARGET_ORACLE_HOME/sqlplus/admin/glogin.sql.backup
mv login.sql login.sql.backup
export SQLPATH=
# Now start the upgrade using the preferred method
java -jar autoupgrade.jar ....
# Or
cd $TARGET_ORACLE_HOME/bin
dbupgrade
# Or
$TARGET_ORACLE_HOME/bin/dbua

What Now

AutoUpgrade already has a simple sanity check on the glogin.sql but it is not bulletproof. If your glogin.sql is way off you might get an error:

The other options (command line or DBUA) have no checks at all.

We are working on making our documentation clearer on this problem, so expect to see an update coming soon.