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!
Thanks a lot ninja!
The missing login.sql file made me made.
However, the next Problem comes up:
I need to installed the Patch 29869909 for an Upgrade from 18.3 to 19.3, right?
But where to install it?
If I try to install it in the 18 home, opatch tells me, there is no need to patch.
Well, the Doc tells me to install in 19.3 home, which runs fine with
./opatch apply.
But at 97% of autoupgrade it crashes:
Current state of interim SQL patches:
No interim patches found
Current state of release update SQL patches:
Binary registry:
No release update patches installed
SQL registry:
No release update patches installed
Error: prereq checks failed!
patch 29869909: XML descriptor does not exist in either the file system or SQL registry
Prereq check failed, exiting without installing any patches.
Any ideas?
Christian
Hi Cristian,
unfortunately, I had 12.2 Version to upgrade, and not the 18.3. 🙁
Also, from your output, I cannot see what could be the possible error/issue. Did you check your opatch log?
BR
Dejan
Hi Dejan,
well autoupgrade fails an requests to check catupgrade.logs.
And in the catupgrade.logs this comes up:
No interim patches found
Current state of release update SQL patches:
Binary registry:
No release update patches installed
SQL registry:
No release update patches installed
Error: prereq checks failed!
patch 29869909: XML descriptor does not exist in either the file system or SQL registry
Prereq check failed, exiting without installing any patches.
Therefore, I guess there is something wrong how I pplied the patches.
Where did You install the patch 25804573 – In the rdbms 19, right?
Regards
Christian
Thanks a lot, ninja! The same error “unable to open file “LOGIN.SQL” ” was driving me nuts. Glad you found and shared it with the community. Keep rocking!