Database Upgrade using autoupgrade tool

This time I had to upgrade a SE2 12.2 database running on Windows on Azure Cloud to the version 19.3. I do not have such setup in my lab, so I was a little bit sceptical about it, and it will turn out – with a reason.

So, I read a ton of a articles and blog posts, and checked some MOS Notes like these ones:

Oracle 19c – Complete Checklist for Upgrading to Oracle Database 19c (19.x) using DBUA (Doc ID 2545064.1)
Oracle 19c – Complete checklist for Manual Upgrade for upgrading Oracle 12.x, 18c Container database (CDB) to Oracle 19c (19.x) (Doc ID 2549866.1)

Then I downloaded the autoupgrade tool following to this MOS note:
MOS Note: 2485457.1 – AutoUpgrade Tool

When you download it, run it to create the sample config file:

C:\app\oracle\product\19.3> set ORACLE_HOME=C:\app\oracle\product\19.3
C:\app\oracle\product\19.3> set JAVA_HOME=C:\app\oracle\product\19.3\jdk\bin
C:\app\oracle\product\19.3> set PATH=%ORACLE_HOME%\bin;%JAVA_HOME%;%PATH%
C:\app\oracle\product\19.3> set SQLPATH=.;%SQLPATH%
C:\app\oracle\product\19.3> java -jar autoupgrade.jar -create_sample_file config
Created sample configuration file C:\app\oracle\product\19.3\sample_config.cfg

You can rename it if you want and then adapt the settings to fit your environment, for example:

global.autoupg_log_dir=C:\APP\ORACLE\Upgrade12c_to_19c
#
# Database number 1
#
upg1.dbname=ORACONT
upg1.start_time=NOW
upg1.source_home=C:\app\oracle\product\12.2.0
upg1.target_home=C:\app\oracle\product\19.3
upg1.sid=ORACONT
upg1.log_dir=C:\APP\ORACLE\Upgrade12c_to_19c
upg1.upgrade_node=ORACONTDEV
upg1.target_version=19
upg1.run_utlrp=no
upg1.timezone_upg=yes

Then analyze the setup:

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

Check the logs under C:\APP\ORACLE\Upgrade12c_to_19c\cfgtoollogs\upgrade\auto (of course, under the correct path in your environment). If there is no error, then upgrade the database:

java -jar autoupgrade.jar -config sample_config.cfg -mode deploy

That is the short summary about autoupgrade usage. When you start the upgrade, you could see the output like this one:

upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME|END_TIME| UPDATED| MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
| 101| ORACONT|DBUPGRADE|EXECUTING|RUNNING|20/05/06 02:20| N/A|03:14:59|67%Upgraded |
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
Total jobs 1

upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME|END_TIME| UPDATED| MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
| 101| ORACONT|DBUPGRADE|EXECUTING|RUNNING|20/05/06 02:20| N/A|03:20:42|93%Upgraded |
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
Total jobs 1

Do not forget to take a backup of the database before you start the upgrade!

Troubleshooting

As I already mentioned, that I was sceptical, it turned out, it was with the reason. I faced some issues, which fortunately have been solvable. Here are some of them, worth to mention.

~ SP2-0310: unable to open file “LOGIN.SQL” ~

If the upgrade stops with this error:

Errors in database [ORACONT]
Stage [DBUPGRADE]
Operation [STOPPED]
Status [ERROR]
Info [
Error: UPG-1400
UPGRADE FAILED [ORACONT]
Cause: Database upgrade failed with errors
For further details, see the log file located at C:\APP\ORACLE\Upgrade12c_to_19c\ORACONT\101\autoupgrade_20200506_user.log]

Logs: [C:\APP\ORACLE\Upgrade12c_to_19c\ORACONT\101\autoupgrade_20200506_user.log]

2020-05-06 03:24:48.475 ERROR
DATABASE NAME: ORACONT
CAUSE: ERROR at Line 32537 in [C:\APP\ORACLE\Upgrade12c_to_19c\ORACONT\101\dbupgrade\catupgrd20200506022042ORACONT3.log]
REASON: SP2-0310: unable to open file "LOGIN.SQL"
ACTION: [MANUAL]
DETAILS: - CheckForErrors.checkForErrors
2020-05-06 03:24:50.051 INFO Starting - CheckForErrors.getOracleError
2020-05-06 03:24:50.051 ERROR
DATABASE NAME: ORACONT
CAUSE: ERROR at Line 164803 in [C:\APP\ORACLE\Upgrade12c_to_19c\ORACONT\101\dbupgrade\catupgrd20200506022042ORACONT3.log]
REASON: SP2-0556: Invalid file name.
ACTION: [MANUAL]
DETAILS: - CheckForErrors.checkForErrors

CATCTL_TIMESTAMP

PHASE_TIME___END 44 20-05-06 03:09:05

Elapsed: 00:00:00.00
03:09:05 SQL> 03:09:05 SQL> 
========== Process Terminated by catcon ==========
03:09:07 SQL> Disconnected from Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 6 03:09:09 2020
Version 19.3.0.0.0

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

SP2-0310: unable to open file "LOGIN.SQL"
SQL> Connected.
SQL> 2
Session altered.

SQL> 2
Session altered.

SQL>
ALTER SYSTEM KILL SESSION '272,48470' force timeout 0 -- process 3712
/

CATCTL_TIMESTAMP

PHASE_TIME___START 46 20-05-06 03:09:09

then you faced the issue with the search path of login.sql. The solution is to set the environment variable SQLPATH as marked with red color above. To clarify, use SQLPATH if you are on Windows, and use ORACLE_PATH if you are on UNIX. Here are some useful articles you can read about it:

https://blog.dbi-services.com/oracle-12cr2-changes-for-login-sql/

SP2-0310: Unable To Open File “LOGIN.SQL” (Doc ID 2277751.1)
SQLPlus 12.2.0.1.0 and 11.2.0.4 PSU Change in Behavior for Search Path of Login.sql (SQLPlus User Profile Script) (Doc ID 2241021.1)
Sqlplus 12.2.0.1 no Longer Running [g]login.sql on Windows OS (Doc ID 2274608.1)

Bug 29869909 : LOGIN.SQL NOT READ FOR 18.X SQLPLUS ON WINDOW EVEN WITH SQLPATH SET
Bug 25804573 : SQL PLUS 12.2 NOT OBSERVING SQLPATH IN REGISTRY OR ENV VARIABLE FOR LOGIN.SQL

~ Triggers ON DATABASE ~

Take care if you are using some ON DATABASE triggers. If you have such one, then upgrade could stop with the following error:

2020-05-06 04:46:22.714 ERROR
DATABASE NAME: ORACONT
CAUSE: ERROR at Line 225 in [C:\APP\ORACLE\Upgrade12c_to_19c\ORACONT\101\dbupgrade\ORACONT_autocompile20200506043037ORACONT0.log]
REASON: ORA-04045: errors during recompilation/revalidation of SYS.UMF_SCHEMA_XMLTYPE
ACTION: [MANUAL]
DETAILS:
2020-05-06 04:46:22.714 ERROR
DATABASE NAME: ORACONT
CAUSE: ERROR at Line 226 in [C:\APP\ORACLE\Upgrade12c_to_19c\ORACONT\101\dbupgrade\ORACONT_autocompile20200506043037ORACONT0.log]
REASON: ORA-00600: internal error code, arguments: [16624], [0x7FFA32614058], [], [],
ACTION: [MANUAL]
DETAILS:
2020-05-06 04:46:22.714 INFO End Compiling Invalid Objects on Database [ORACONT]
2020-05-06 04:46:22.730 ERROR COMPILED FAILED [ORACONT]
2020-05-06 04:46:22.730 ERROR Exception Error in Database Compiling, Invalid Objects [UPG-1416#COMPILED FAILED [ORACONT]]
2020-05-06 04:46:25.361 ERROR UPGRADE FAILED [ORACONT]

• snippet from [C:\APP\ORACLE\Upgrade12c_to_19c\ORACONT\101\dbupgrade\ORACONT_autocompile20200506043037ORACONT0.log]:

04:35:51 SQL> DECLARE
04:35:51 2 threads pls_integer := &&1;
04:35:51 3 BEGIN
04:35:51 4 utl_recomp.recomp_parallel(threads);
04:35:51 5 END;
04:35:51 6 /
DECLARE
*
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of SYS.UMF_SCHEMA_XMLTYPE
ORA-00600: internal error code, arguments: [16624], [0x7FFA32614058], [], [],
[], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_UTILITY", line 1329
ORA-06512: at line 1

*** 2020-05-06T05:13:01.325808+02:00
ORA-12012: error on auto execute of job "SYS"."UTL_RECOMP_SLAVE_6"
at 0x0000004CADBF93C8 placed jslv.c@1685
ORA-04045: errors during recompilation/revalidation of SYS.UMF_SCHEMA_XMLTYPE
ORA-00600: internal error code, arguments: [16624], [0x7FFA391EB5C0], [], [], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_UTILITY", line 1329
ORA-06512: at line 1

Therefore, the lesson learned is – check if there is any ON DATABASE trigger and disable it before you start with the upgrade!

~ Other bugs ~

We hit also this Bug 30282501 – Memory leak on Windows using an external table using a UNC file path (Doc ID 30282501.8), and we have to request a backport patch for Windows, but we didn’t hit (yet) this one Bug 30208416 : AFTER INSTALLING PATCH 29859191 FOR WIN X64 RMAN REPORTS PL/SQL PACKAGE SYS.DBMS_BACKUP_RESTORE VERSION 19.03.00.00 IN TARGET DATABASE IS NOT CURRENT .

Hope it helps you!