Oracle: Prepare database for upgrade using autoupgrade.jar
In my job, I often have to upgrade or patch Oracle databases. Furtunately, most of all the databases in production are now on 19c, and the rest is mainly on 12.2.0.1.
The best tool for upgrading is autoupgrade.jar, which in future will be the only upgrade tool for Oracle databases.
Preparation
First, download the latest (and always check that!) version of autoupgrade.jar from Oracle and copy it to the source system.
Then execute autoupgrade.jar like that:
# java -jar autoupgrade.jar -config_values "sid=$ORACLE_SID,source_home=$ORACLE_HOME,target_version=19.25" -mode analyzeBe sure to use Java version 1.8. And if you like use rlwrap so you don't have to re-type all your commands (on Windows, it works out of the box).
But sometimes, I also find an old 11g database (fortunately 11.2 = 11gR2). When they are on the latest minor release, which means 11.2.0.4, I don't have any problems to upgade them to 12.2.0.1 or 19c.
If your source version is something that's not supported to upgrade to Oracle 19c (like 12.1.0.1, or lower than 11.2.0.4), you will get this error:
AutoUpgrade 24.8.241119 launched with default internal options
Processing config file ...
There were conditions found preventing AutoUpgrade from successfully running
*Unsupported Upgrade
<database name> Source version [11.2.0.3.0] cannot be upgraded to the specified target version [19.25]Analyze
When the versions are supported, it will show simething like this:
AutoUpgrade 24.8.241119 launched with default internal options
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 Non-CDB(s) will be analyzed
Type 'help' to list console commandsWhen Autoupgrade is running, these are the most important commands:
lsj: lists the jobs running (only one job per database, so it's quite important when you're updating many databases simultaneously)status -job <number>: show the status of the job
upg> lsj
+----+-------+---------+---------+-------+----------+-------+----------------------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE|
+----+-------+---------+---------+-------+----------+-------+----------------------------+
| 103| <dbname> |PRECHECKS|EXECUTING|RUNNING| 13:14:11| 0s ago|Loading database information|
+----+-------+---------+---------+-------+----------+-------+----------------------------+
Total jobs 1After the job has finished, this is the output:
upg> Job 103 completed
------------------- Final Summary --------------------
Number of databases [ 1 ]
Jobs finished [1]
Jobs failed [0]
Please check the summary report at:
/tmp/autoupgrade/cfgtoollogs/upgrade/auto/status/status.html
/tmp/autoupgrade/cfgtoollogs/upgrade/auto/status/status.logWhen you then have a look at the file status.log, you should see this or similar information:
==========================================
Autoupgrade Summary Report
==========================================
[Date] Tue Jan 21 13:14:17 CET 2025
[Number of Jobs] 1
==========================================
[Job ID] 103
==========================================
[DB Name] <dbname>
[Version Before Upgrade] 11.2.0.4.0
[Version After Upgrade] 19.25
------------------------------------------
[Stage Name] PRECHECKS
[Status] FAILURE
[Start Time] 2025-01-21 13:14:11
[Duration] 0:00:05
[Log Directory] /tmp/autoupgrade/<dbname>/103/prechecks
[Detail] /tmp/autoupgrade/<dbname>/103/prechecks/<dbname>_preupgrade.log
Check failed for <dbname>, manual intervention needed for the below checks
[FLASH_RECOVERY_AREA_SETUP]
Cause:The following checks have ERROR severity and no auto fixup is available or
the fixup failed to resolve the issue. Fix them before continuing:
<dbname> FLASH_RECOVERY_AREA_SETUP
Reason:Database Checks has Failed details in /tmp/autoupgrade/<dbname>/103/prechecks
Action:[MANUAL]
Info:Return status is ERROR
ExecutionError:No
Error Message:The following checks have ERROR severity and no auto fixup is available or
the fixup failed to resolve the issue. Fix them before continuing:
<dbname> FLASH_RECOVERY_AREA_SETUP
------------------------------------------Here you can already see if there's something to prepare when the upgrades has to be done.
In this case, the database parameters
db_recovery_file_destand- `db_recovery_file_dest_size
have to be defined to allow an upgrade.
Additionally, the file /tmp/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log will show much more information about the upgrade.
This is the header:
Report generated by AutoUpgrade 24.8.241119 (#b404cf007) on 2025-01-21 13:14:17
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: <dbname>
Container Name: Not Applicable in Pre-12.1 database
Container ID: Not Applicable in Pre-12.1 database
Version: 11.2.0.4.0
DB Patch Level: UNKNOWN
Compatible: 11.2.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone File: 14
Database log mode: ARCHIVELOG
Readonly: false
Edition: EE
Oracle Component Upgrade Action Current Status
---------------- -------------- --------------
Oracle Server [to be upgraded] VALID
Expression Filter [to be removed] VALID
Oracle Workspace Manager [to be upgraded] VALID
Rule Manager [to be removed] VALID
Oracle XML Database [to be upgraded] VALID
*
* ALL Components in This Database Registry:
*
Component Current Current Original Previous Component
CID Version Status Version Version Schema
---------- ----------- ----------- ----------- ----------- ------------
CATALOG 11.2.0.4.0 VALID SYS
CATPROC 11.2.0.4.0 VALID SYS
EXF 11.2.0.4.0 VALID EXFSYS
OWM 11.2.0.4.0 VALID WMSYS
RUL 11.2.0.4.0 VALID EXFSYS
XDB 11.2.0.4.0 VALID XDB
==============
BEFORE UPGRADE
==============Let's go to the Required Actions:
REQUIRED ACTIONS
================1. (AUTOFIXUP) Set a value for the compatible parameter in pfile/spfile.
2. Configure Fast Recovery Area in the database by issuing the following commands:
Recommended Actions
3. Data Pump Advanced Queuing
3. (AUTOFIXUP) Connect to the database as SYS to drop all Data Pump Advanced
Queuing (AQ) tables prior to upgrading. Check MOS note 2789059.1 for
details.
The database needs to be free of Data Pump Advanced Queuing (AQ) tables
in order for Data Pump AQ message types to be re-created during the
database upgrade.
There exists at least one Data Pump Advanced Queuing (AQ) table in the
SYS schema which might prevent Data Pump AQ message types from getting
re-created.4. RESOURCE_LIMIT
(AUTOFIXUP) Explicitly set RESOURCE_LIMIT to FALSE in the pfile/spfile to
retain the previous behavior.
The RESOURCE_LIMIT initialization parameter default value changed from
FALSE to TRUE in 12.1.0.2 onwards. Without an explicit setting, the
upgrade may introduce unintented RESOURCE_LIMIT enforcement.
the RESOURCE_LIMIT initialization parameter is not explicitly set in the
pfile/spfile.5. Perform one of the following:
Perform one of the following:
1) Expire user accounts that use only the old 10G password version and
follow the procedure recommended in Oracle Database Upgrade Guide under
the section entitled, "Checking for Accounts Using Case-Insensitive
Password Version".
2) Explicitly set SQLNET.ALLOWED_LOGON_VERSION_SERVER in the 19
SQLNET.ORA to a non-Exclusive Mode value, such as "11". (This is a short
term approach and is not recommended because it will retain known
security risks associated with the 10G password version.)
Starting with Oracle Database release 12.2.0.1, Exclusive Mode is the new
default password-based authentication mode. All Exclusive Mode
login/authentication attempts will fail for preexisting user accounts
which only have the 10G password version and neither the 11G or 12C
password version (see DBA_USERS.PASSWORD_VERSIONS.) For more information,
refer to "Understanding Password Case Sensitivity and Upgrades" in the
Oracle Database Upgrade Guide.
Your database system has at least one account with only the 10G password
version (see the PASSWORD_VERSIONS column of DBA_USERS).- Recompile
6. (AUTOFIXUP) Run 11.2.0.4.0 $ORACLE_HOME/rdbms/admin/utlrp.sql to
recompile. Parameter job_queue_processes is set to 10. The script to
recompile invalid objects will run parallel.
There should be no INVALID objects in Oracle-maintained schemas before
database upgrade.
1 objects are INVALID.- JAVA_POOL_SIZE
7. (AUTOFIXUP) Update NUMERIC INITIALIZATION PARAMETERS to meet estimated
minimums. This action may be done now or when starting the database in
upgrade mode using the 19 ORACLE HOME.
The database upgrade process requires certain initialization parameters
to meet minimum values. The Oracle upgrade process itself has minimum
values which may be higher and are marked with an asterisk. After
upgrading, those asterisked parameter values may be reset if needed.
Parameter Currently 19 minimum
--------- --------- ------------------
*java_pool_size 33554432 117440512- Remove STREAMS
8. Remove Streams setup. For detailed steps, refer to the section "Removing
an Oracle Streams Configuration" in the Oracle Streams Concepts and
Administration Guide specific for the Oracle release from which you are
removing. For versions pre-12.1.0.2, the procedure
dbms_streams_adm.remove_streams_configuration must not be used as may
lead to unwanted results. Instead, use the other procedures
(dbms_capture_adm.drop_capture, dbms_apply_adm.drop_apply,
dbms_streams_adm.remove_queue, etc). For 12.1.0.2 and higher, procedure
dbms_streams_adm.remove_streams_configuration can be safely used.
Starting with Oracle Database 19, Oracle Streams is desupported. It is
strongly advised to remove any streams configuration manually.
Oracle Streams feature is configured in the database.- SPFile: memory parameters
9. Review below list of parameters set in memory only and for the ones that
are intended to be permanent:
1. Save these settings in respective SPFILE.
2. Run AutoUpgrade in ANALYZE mode so that parameters can get reflected
in interim pfiles created by the tool.
The parameters with values that are in memory only are:
Instance Parameter Memory Value Parameter File Value
---------- ------------ ----------------- --------------------
<dbname> spfile /data_<dbname>/dbs/sp NONE
file<dbname>.ora
<dbname> db_cache_siz 1241513984 1258291200
e
<dbname> shared_pool_ 822083584 805306368
size
<dbname> sga_max_size 2952790016 2147483648
For database initialization parameter values that are not in the
database's initialization parameter file (pfile/spfile), note that the
values in memory only will be lost on database shutdown in the current
Oracle home prior to upgrading. Hence, these values will not be used in
the database upgrade unless they are recorded in the parameter file.
Found at least one parameter with a value in memory that is not in the
database's initialization parameter file.- Statistics
10. (AUTOFIXUP) Gather stale data dictionary statistics prior to database
upgrade in off-peak time using:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
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 11.2.0.4
Oracle Database Performance Tuning Guide.
Dictionary statistics do not exist or are stale (not up-to-date).- Statistics for fixed objects
11. (AUTOFIXUP) Gather statistics on fixed objects prior to the upgrade using
the command:
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
Gathering statistics on fixed objects, if none have been gathered yet, is
recommended prior to upgrading.
For information on managing optimizer statistics, refer to the 11.2.0.4
Oracle Database Performance Tuning Guide.
None of the fixed object tables have had stats collected.Information only
- Remove EXF and RUL
12. (AUTOFIXUP) Run $ORACLE_HOME/rdbms/admin/catnoexf.sql located in the new
Oracle Database Oracle home to remove both EXF and RUL.
Starting with Oracle Database release 12.1, the Expression Filter (EXF)
and Database Rules Manager (RUL) features are desupported, and are
removed during the upgrade process. This step can be manually performed
before the upgrade to reduce downtime.
Expression Filter (EXF) or Rules Manager (RUL) exist in the database.- SPFile changes
13. (AUTOFIXUP) Mandatory changes are applied automatically in the
during_upgrade_pfile_dbname.ora file. Some of these changes maybe present
in the after_upgrade_pfile_dbname.ora file. The
during_upgrade_pfile_dbname.ora is used to start the database in upgrade
mode. The after_upgrade_pfile_dbname.ora is used to start the database
once the upgrade has completed successfully.
Mandatory changes are required to perform the upgrade. These changes are
implemented in the during_ and after_upgrade_pfile_dbname.ora files.
Parameter
---------
local_listener=remove
cluster_database=FALSE - RMAN Catalog
14. Check the Oracle Backup and Recovery User's Guide for information on how
to manage an RMAN recovery catalog schema.
It is good practice to have the catalog schema the same or higher version
than the RMAN client version you are using.
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.- SYSTEM tablespace
15. To help you keep track of your tablespace allocations, the following
AUTOEXTEND tablespaces are expected to successfully EXTEND during the
upgrade process.
Minimum tablespace sizes for upgrade are estimates.
Min Size
Tablespace Size For Upgrade
---------- ---------- -----------
SYSTEM 380 MB 664 MB- Database registry
16. Here are ALL the components in this database registry:
Review the information before upgrading.
Component Current Current Original Previous Component
CID Version Status Version Version Schema
--------- ----------- ----------- ----------- ----------- -----------
CATALOG 11.2.0.4.0 VALID SYS
CATPROC 11.2.0.4.0 VALID SYS
EXF 11.2.0.4.0 VALID EXFSYS
OWM 11.2.0.4.0 VALID WMSYS
RUL 11.2.0.4.0 VALID EXFSYS
XDB 11.2.0.4.0 VALID XDB - Invalid objects
17. Here is a count of invalid objects by users:
Review the information before upgrading.
User Name Number of INVALID Objects
--------------------------- -------------------------
<username> 1 After Upgrade
Required Actions
REQUIRED ACTIONS
================
None
Recommended actions
- Timezone upgrade
18. (AUTOFIXUP) Upgrade the database time zone file using the DBMS_DST
package.
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.
The database is using time zone file version 14 and the target 19
$ORACLE_HOME/oracore/zoneinfo directory contains time zone file version
32.- Database directories
19. 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.
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.
Found 2 user directory objects to be checked: DUMP, EXPIMP.- Recompile objects with timezone mismatch
20. (AUTOFIXUP) Recompile the objects with timestamp mismatch. Refer to MOS
note 781959.1 for more details.
Timestamp of dependent objects must coincide with the timestamp of parent
objects.
There are objects whose timestamp are mismatched with its parent objects.- Dictionary statistics
21. (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
command:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
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.
Oracle recommends gathering dictionary statistics after upgrade.- Statistics for fixed objects
22. 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;
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 11.2.0.4
Oracle Database Performance Tuning Guide.
Oracle recommends gathering fixed object statistics after upgrade. This
recommendation is given for all preupgrade runs.- Post upgrade recompile
23. (AUTOFIXUP) On post upgrade, AutoUpgrade recompiles invalid database
objects with $ORACLE_HOME/rdbms/admin/utlprpom.sql
Invalid database objects need to be recompiled after a database is
upgraded. Note that starting with Release 12.2.0.1 and later, AutoUpgrade
recompiles only invalid objects in Oracle-maintained schemas and defers
recompilation of invalid application objects post upgrade to users.
There are 0 invalid objects in Oracle-maintained schemas and 0 invalid
objects in application schemas after upgrade.Conclusion
Autoupgrade is a great tool for Oracle database upgrades, and you can also use it for upgrade preparations. Fix each finding mady by Autoupgrade, and your upgrade will be fine.
Finally, follow Mike's and Daniel's blogs. They know everything about Oracle upgrades.