On this post we are going to upgrade Oracle 12c to 19c on an ACTIVE/PASSIVE setup on Windows Failover Cluster Manager:
| Server Name | DBNAME | 12c Oracle Home | 19c Oracle Home |
| win-rac1 | DBPROD | C:\oracle\app\product\12.1.0\dbhome_1 | C:\oracle\app\product\19c\dbhome_1 |
| win-rac2 | DBPROD | C:\oracle\app\product\12.1.0\dbhome_1 | C:\oracle\app\product\19c\dbhome_1 |
This is the current patch level of both 12c and 19c homes (on both nodes!):
C:\oracle\app\product\12.1.0\dbhome_1\OPatch\34185253>opatch lspatches
34185253;WINDOWS ORACLE JAVAVM COMPONENT BUNDLE PATCH 12.1.0.2.220719 (64bit): 34185253
33883271;Windows Database Bundle Patch : 12.1.0.2.220719 (33883271)
C:\oracle\app\product\12.1.0\dbhome_1\OPatch\34185253>
C:\oracle\app\product\19c\dbhome_1\OPatch>opatch lspatches
34411846;OJVM RELEASE UPDATE: 19.17.0.0.221018 (34411846)
34468114;Windows Database Bundle Patch : 19.17.0.0.221018 (34468114)
C:\oracle\app\product\19c\dbhome_1\OPatch>Its worth to mention I have been facing many errors like:
***************** Upgrading Java and non-Java *******************************
Serial Phase #:53 [DBPROD] Files:2
Unexpected error encountered in catctlMain; Error Stack Below; exiting
Died at C:\oracle\app\product\19c\dbhome_1\rdbms\admin\catctl.pl line 9261.
at C:\oracle\app\product\19c\dbhome_1\rdbms\admin\catctl.pl line 9261.
main::catctlDie(“\x{a}Unexpected error encountered in catconExec; exiting\x{a} No such”…) called at C:\oracle\app\product\19c\dbhome_1\rdbms\admin\catctl.pl line 5716
main::catctlExecutePhaseFiles(53, 2, undef, undef, undef) called at C:\oracle\app\product\19c\dbhome_1\rdbms\admin\catctl.pl line 2088
main::catctlRunPhase(53, 2, undef, undef, undef) called at C:\oracle\app\product\19c\dbhome_1\rdbms\admin\catctl.pl line 2252
main::catctlRunPhases(0, 108, 108, undef, undef, undef) called at C:\oracle\app\product\19c\dbhome_1\rdbms\admin\catctl.pl line 2871
main::catctlRunMainPhases() called at C:\oracle\app\product\19c\dbhome_1\rdbms\admin\catctl.pl line 1500
main::catctlMain() called at C:\oracle\app\product\19c\dbhome_1\rdbms\admin\catctl.pl line 1407
eval {…} called at C:\oracle\app\product\19c\dbhome_1\rdbms\admin\catctl.pl line 1405
Or
2022-12-04T21:09:32.114879+00:00
joxcsys: begin CREATE JAVA SYSTEM in pid 952
…..
2022-12-04T21:11:29.193358+00:00
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0x2BF200C52C] [PC:0x7FF71444ABC4, kqlidp0_int()+23028]
Errors in file C:\APP\ORACLE\diag\rdbms\dbtest\dbtest\trace\dbtest_ora_2184.trc (incident=33817):
ORA-07445: exception encountered: core dump [kqlidp0_int()+23028] [ACCESS_VIOLATION] [ADDR:0x2BF200C52C] [PC:0x7FF71444ABC4] [UNABLE_TO_READ] []
Incident details in: C:\APP\ORACLE\diag\rdbms\dbtest\dbtest\incident\incdir_33817\dbtest_ora_2184_i33817.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2022-12-04T21:11:30.849981+00:00
Errors in file C:\APP\ORACLE\diag\rdbms\dbtest\dbtest\incident\incdir_33817\dbtest_ora_2184_i33817.trc:
ORA-00602: internal programming exception: [PC:0x7FFB8AE4215A] [ADDR:0x7FFB62975760]
ORA-07445: exception encountered: core dump [kqlidp0_int()+23028] [ACCESS_VIOLATION] [ADDR:0x2BF200C52C] [PC:0x7FF71444ABC4] [UNABLE_TO_READ] []
Most of them related to the Java System. As a workaround I followed this DocID, which seems to make the upgade more consistent:
Database Upgrade To 18c/19c Failing With CatJava As Invalid While Processing "Rdbms/Jlib/Aqapi.jar" (Doc ID 2616365.1)
Symptoms
Database upgrade to 18c/19c failing with CATAJAVA as Invalid while processing "RDBMS/JLIB/AQAPI.JAR" as a part database upgrade internally which is resulting in "ORA-29532" Error:
As per upgrade log, upgrade script is failing while executing "rdbms/jlib/aqapi.jar" with below error due to which CATAJAVA resulting in Invalid state.
ERRORS FOUND: During Upgrade
------------------------------------------------------
Identifier CATJAVA 19-04-03 05:53:52
SCRIPT = [/u01/app/oracle/product/18.4.0/dbhome_1/rdbms/admin/initjms.sql]
ERROR = [ORA-29532: Java call terminated by uncaught Java exception:]
STATEMENT = [call sys.dbms_java.loadjava('-v -f -r -s -g PUBLIC rdbms/jlib/aqapi.jar')]
Solution
This issue is fixed in Oracle database version 20.1.0.1.0. If patch is needed for lower database versions, please contact Oracle support.
Patch must be applied on Target home and you need to rollback the upgrade and perform it again.
And if patch is not readily available for your Target home, you can use the below workaround. But in either case the upgrade must be rolled back to fix the issue.
A workaround (preupgrade) would be to run
@?/xdk/admin/initxml.sql
to install XDK in Source Oracle home (the XML component in dba_registry) before upgrading.STEPS:
1:- Install Oracle 19c Software in both nodes. Steps HERE
2:- Download preupgrade scripts from Oracle Support:
How to Download and Run Oracle’s Database Pre-Upgrade Utility (Doc ID 884522.1)
copy them on 19c ORACLE_HOME/rdbms/admin
3:- Run PreUpgrade:
**On the node DB is running!
set ORACLE_HOME=C:\oracle\app\product\12.1.0\dbhome_1
set ORACLE_SID=DBPROD
set PATH=%ORACLE_HOME%\bin;%PATH%
set TNS_ADMIN=%ORACLE_HOME%\NETWORK\ADMIN\tnsnames.ora
cd C:\oracle\app\product\12.1.0\dbhome_1\BIN
java -jar C:\oracle\app\product\19c\dbhome_1\rdbms\admin\preupgrade.jar TEXT TERMINAL
If you get this error, you might need to install JAVA from https://www.java.com/en/download/manual.jsp:
C:\oracle\app\product\12.1.0\dbhome_1\BIN>java -jar C:\oracle\app\product\19c\dbhome_1\rdbms\admin\preupgrade.jar TEXT TERMINAL
'java' is not recognized as an internal or external command,
operable program or batch file.
C:\oracle\app\product\12.1.0\dbhome_1\BIN>Output:
C:\oracle\app\product\12.1.0\dbhome_1\BIN>java -jar C:\oracle\app\product\19c\dbhome_1\rdbms\admin\preupgrade.jar TEXT T
ERMINAL
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 13 on 2022-12-09T21:43:05
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: DBPROD
Container Name: DBPROD
Container ID: 0
Version: 12.1.0.2.0
DB Patch Level: WINDOWS DB BUNDLE PATCH 12.1.0.2.220719(64bit):33883271
Compatible: 12.1.0.2.0
Blocksize: 8192
Platform: Microsoft Windows x86 64-bit
Timezone File: 18
Database log mode: ARCHIVELOG
Readonly: FALSE
Edition: EE
Oracle Component Upgrade Action Current Status
---------------- -------------- --------------
Oracle Server [to be upgraded] VALID
JServer JAVA Virtual Machine [to be upgraded] VALID
Oracle XDK for Java [to be upgraded] VALID
Real Application Clusters [to be upgraded] OPTION OFF
Oracle Workspace Manager [to be upgraded] VALID
OLAP Analytic Workspace [to be upgraded] VALID
Oracle Label Security [to be upgraded] VALID
Oracle Database Vault [to be upgraded] VALID
Oracle Text [to be upgraded] VALID
Oracle XML Database [to be upgraded] VALID
Oracle Java Packages [to be upgraded] VALID
Oracle Multimedia [to be upgraded] VALID
Oracle Spatial [to be upgraded] VALID
Oracle OLAP API [to be upgraded] VALID
==============
BEFORE UPGRADE
==============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
1. (AUTOFIXUP) Gather stale data dictionary statistics prior to database
upgrade in off-peak time using:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Dictionary statistics do not exist or are stale (not up-to-date).
Dictionary statistics help the Oracle optimizer find efficient SQL
execution plans and are essential for proper upgrade timing. Oracle
recommends gathering dictionary statistics in the last 24 hours before
database upgrade.
For information on managing optimizer statistics, refer to the 12.1.0.2
Oracle Database SQL Tuning Guide.
INFORMATION ONLY
================
2. Here are ALL the components in this database registry:
Component Current Current Original Previous Component
CID Version Status Version Version Schema
--------- ----------- ----------- ----------- ----------- -----------
APS 12.1.0.2.0 VALID SYS
CATALOG 12.1.0.2.0 VALID SYS
CATJAVA 12.1.0.2.0 VALID SYS
CATPROC 12.1.0.2.0 VALID SYS
CONTEXT 12.1.0.2.0 VALID CTXSYS
DV 12.1.0.2.0 VALID DVSYS
JAVAVM 12.1.0.2.0 VALID SYS
OLS 12.1.0.2.0 VALID 12.1.0.2.0 12.1.0.2.0 LBACSYS
ORDIM 12.1.0.2.0 VALID 12.1.0.2.0 12.1.0.2.0 ORDSYS
OWM 12.1.0.2.0 VALID WMSYS
RAC 12.1.0.2.0 OPTION OFF SYS
SDO 12.1.0.2.0 VALID MDSYS
XDB 12.1.0.2.0 VALID XDB
XML 12.1.0.2.0 VALID SYS
XOQ 12.1.0.2.0 VALID 12.1.0.2.0 12.1.0.2.0 OLAPSYS
Review the information before upgrading.
3. To help you keep track of your tablespace allocations, the following
AUTOEXTEND tablespaces are expected to successfully EXTEND during the
upgrade process.
Min Size
Tablespace Size For Upgrade
---------- ---------- -----------
SYSTEM 840 MB 1186 MB
Minimum tablespace sizes for upgrade are estimates.
4. Check the Oracle Backup and Recovery User's Guide for information on how
to manage an RMAN recovery catalog schema.
If you are using a version of the recovery catalog schema that is older
than that required by the RMAN client version, then you must upgrade the
catalog schema.
It is good practice to have the catalog schema the same or higher version
than the RMAN client version you are using.
5. Here is a count of invalid objects by Oracle-maintained users:
Oracle-Maintained User Name Number of INVALID Objects
--------------------------- -------------------------
None None
Review the information before upgrading.
6. Here is a count of invalid objects by Application users:
Application User Name Number of INVALID Objects
--------------------------- -------------------------
None None
Review the information before upgrading.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database DBPROD
which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
SQL>@C:\oracle\app\cfgtoollogs\DBPROD\preupgrade/preupgrade_fixups.sql
=============
AFTER UPGRADE
=============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
7. Upgrade the database time zone file using the DBMS_DST package.
The database is using time zone file version 18 and the target 19 release
ships with time zone file version 32.
Oracle recommends upgrading to the desired (latest) version of the time
zone file. For more information, refer to "Upgrading the Time Zone File
and Timestamp with Time Zone Data" in the 19 Oracle Database
Globalization Support Guide.
8. Recreate directory objects to remove any symbolic links from directory
paths. To identify paths that contain symbolic links before upgrading,
use OS commands like UNIX file or WINDOWS dir. After upgrading, run
$ORACLE_HOME/rdbms/admin/utldirsymlink.sql to identify directory objects
with symbolic links in the path.
Found 1 user directory objects to be checked: ORACLECLRDIR.
Starting in Release 18c, symbolic links are not allowed in directory
object paths used with BFILE data types, the UTL_FILE package, or
external tables.
9. (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
command:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Oracle recommends gathering dictionary statistics after upgrade.
Dictionary statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. After a database
upgrade, statistics need to be re-gathered as there can now be tables
that have significantly changed during the upgrade or new tables that do
not have statistics gathered yet.
10. Gather statistics on fixed objects after the upgrade and when there is a
representative workload on the system using the command:
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
This recommendation is given for all preupgrade runs.
Fixed object statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. Those
statistics are specific to the Oracle Database release that generates
them, and can be stale upon database upgrade.
For information on managing optimizer statistics, refer to the 12.1.0.2
Oracle Database SQL Tuning Guide.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database DBPROD
which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
SQL>@C:\oracle\app\cfgtoollogs\DBPROD\preupgrade/postupgrade_fixups.sql
==================
PREUPGRADE SUMMARY
==================
C:\oracle\app\cfgtoollogs\DBPROD\preupgrade\preupgrade.log
C:\oracle\app\cfgtoollogs\DBPROD\preupgrade\preupgrade_fixups.sql
C:\oracle\app\cfgtoollogs\DBPROD\preupgrade\postupgrade_fixups.sql
Execute fixup scripts as indicated below:
Before upgrade:
Log into the database and execute the preupgrade fixups
@C:\oracle\app\cfgtoollogs\DBPROD\preupgrade\preupgrade_fixups.sql
After the upgrade:
Log into the database and execute the postupgrade fixups
@C:\oracle\app\cfgtoollogs\DBPROD\preupgrade\postupgrade_fixups.sql
Preupgrade complete: 2022-12-09T21:43:05
C:\oracle\app\product\12.1.0\dbhome_1\BIN>4:- Run PreUpgrade Fixups:
SQL> @C:\oracle\app\cfgtoollogs\DBPROD\preupgrade\preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 13
Generated on: 2022-12-09 21:43:00
For Source Database: DBPROD
Source Database Version: 12.1.0.2.0
For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
1. dictionary_stats YES None.
2. component_info NO Informational only.
Further action is optional.
3. tablespaces_info NO Informational only.
Further action is optional.
4. rman_recovery_version NO Informational only.
Further action is optional.
5. invalid_ora_obj_info NO Informational only.
Further action is optional.
6. invalid_app_obj_info NO Informational only.
Further action is optional.
The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database is not ready
for upgrade. To resolve the outstanding issues, start by reviewing
the preupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.
PL/SQL procedure successfully completed.
SQL>**Once prerequisites/fixes are met, we can continue
*** BEFORE running the upgrade, we need to remove DB and Listener from Failover Cluster Manager (FCM). This is because FCM will see resources going down and will try to start on other node, which we don’t want.
5:- Remove DB (save first Dependencies):
6:- Remove Listener (save first Dependencies):
7:- Delete 12c Listener (from both nodes):
8:- Create 19c Listener (on both nodes):
9:- Copy listener.ora / sqlnet.ora / tnsnames from 12c to 19c homes (on both nodes)
from C:\oracle\app\product\12.1.0\dbhome_1\NETWORK\ADMIN
to C:\oracle\app\product\19c\dbhome_1\network\admin10:- Start Listener / DB resources on node where DISK is online (WIN-RAC1)
11:- Run Database Upgrade Assistant (from 19c home) on server DB is running
Now, this is very important: Once Upgrade starts and DB starts in UPGRADE Mode:
check alert log for this:
2022-12-07T22:32:04.797395+00:00
CJQ0 started with pid=43, OS id=3456
Completed: ALTER DATABASE OPEN MIGRATE
2022-12-07T22:32:18.344128+00:00
ALTER SYSTEM SET _enable_cdb_upgrade_capture=TRUE SCOPE=MEMORY;
alter pluggable database application APP$CDB$CATALOG begin install '12.1.0.2.0'
Completed: alter pluggable database application APP$CDB$CATALOG begin install '12.1.0.2.0'
alter pluggable database application APP$CDB$CATALOG end install '12.1.0.2.0'
Completed: alter pluggable database application APP$CDB$CATALOG end install '12.1.0.2.0'
alter pluggable database application APP$CDB$CATALOG begin upgrade
'12.1.0.2.0' to '19.0.0.0.0.partial'
Completed: alter pluggable database application APP$CDB$CATALOG begin upgrade
'12.1.0.2.0' to '19.0.0.0.0.partial'
2022-12-07T22:32:22.422303+00:00
This parameter “_enable_cdb_upgrade_capture” is wrong, it should not be here because we are not a CDB db, so connect to the DB and run:
set ORACLE_SID=DBPROD
set ORACLE_HOME=C:\oracle\app\product\19c\dbhome_1
sqlplus / as sysdba
SQL> alter system set "_enable_cdb_upgrade_capture"=FALSE scope=MEMORY;
System altered.
SQL>This should fix the Upgrade Steps, but might still fail on the Post Upgrade steps while compiling objects. If it does, connect to DB and recompile all objects “@?/rdbms/admin/utlrp.sql” and Retry the upgrade
If all ok, you will see something like this:
12:- Once DB has been upgraded, we need to match configuration across both nodes:
Copy files from C:\oracle\app\product\19c\dbhome_1\database (from win-rac1 to win-rac2)
13:- Delete Database Service on node 2 (WIN-RAC2) and recreate as 19c:
**No listed here as not running:
C:\Users\Administrator.TESTLAB>sc query type= service | find /i "DBPROD"
SERVICE_NAME: OracleVssWriterDBPROD
DISPLAY_NAME: OracleVssWriterDBPROD
SERVICE_NAME: OracleOraDB19Home1TNSListenerDBPROD
DISPLAY_NAME: OracleOraDB19Home1TNSListenerDBPROD
C:\Users\Administrator.TESTLAB>oradim -delete -sid DBPROD
Instance deleted.
C:\Users\Administrator.TESTLAB>
set ORACLE_HOME=C:\oracle\app\product\19c\dbhome_1
SET PATH=%ORACLE_HOME%\BIN;%PATH%
SET ORACLE_SID=DBPROD
C:\Users\Administrator.TESTLAB>C:\oracle\app\product\19c\dbhome_1\bin\oradim -new -sid DBPROD -syspwd Pas5w0rd -startmod
e auto -spfile
Enter password for Oracle service user:
OPW-00029: Password complexity failed for SYS user : Password must contain at least 1 special character.
Instance created.
C:\Users\Administrator.TESTLAB>Open Services to confirm:
14:- Add Listener 19c to Failover Cluster Manager:
15: -Add Dependencies:
16:- Add DATABASE 19c to Failover Cluster Manager:
17: -Add Dependencies:
18: – Start Both Resources:
19:- Move Resource to second node to make sure all OK:
20:- Connect to DB and confirm all ok (You should do this right after Upgrade completed ok):
set pages 1000 lines 120
col instance_name for a20
col host_name for a20
alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
select instance_name, host_name, startup_time from gv$instance order by 1;
SQL> select instance_name, host_name, startup_time from gv$instance order by 1;
INSTANCE_NAME HOST_NAME STARTUP_TIME
-------------------- -------------------- --------------------
dbprod WIN-RAC2 11-dec-2022 14:39:16
col comp_name format a50
col STATUS format a20
set linesize 190
set pagesize 99
select comp_name, version, status from dba_registry order by 1;
COMP_NAME VERSION STATUS
-------------------------------------------------- ------------------------------ --------------------
JServer JAVA Virtual Machine 19.0.0.0.0 VALID
OLAP Analytic Workspace 19.0.0.0.0 VALID
Oracle Database Catalog Views 19.0.0.0.0 VALID
Oracle Database Java Packages 19.0.0.0.0 VALID
Oracle Database Packages and Types 19.0.0.0.0 VALID
Oracle Database Vault 19.0.0.0.0 VALID
Oracle Label Security 19.0.0.0.0 VALID
Oracle Multimedia 19.0.0.0.0 VALID
Oracle OLAP API 19.0.0.0.0 VALID
Oracle Real Application Clusters 19.0.0.0.0 OPTION OFF
Oracle Text 19.0.0.0.0 VALID
Oracle Workspace Manager 19.0.0.0.0 VALID
Oracle XDK 19.0.0.0.0 VALID
Oracle XML Database 19.0.0.0.0 VALID
Spatial 19.0.0.0.0 VALID
15 rows selected.
SQL>That’s it! Database upgrade from 12c to 19c on Failover Cluster Manager done.
Comments