We're seeing a lot of clients finally deciding to upgrade to V19 after years of nagging from us. I've done several now, so I've put together a guide to go through the different methods available.
I recommend setting up a VM, install V11 (you can still get the V11 software from edelivery.oracle.com) and then go through the steps. I would also recommend backing up the VM once you have installed V11 so that you can restore it for when you need to start again unless you want to practice re-installing V11 a lot.
This document assumes -
V11 is 11.2.0.4
V19 zip file has been uploaded to the host
cd
cd Downloads
ls
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/19.0.0/dbhome_1
export ORA_INVENTORY=/u01/app/oraInventory
mkdir -p ${ORACLE_HOME}
cd $ORACLE_HOME
pwd
/u01/app/oracle/product/19.0.0/dbhome_1
unzip -oq ~/Downloads/LINUX.X64_193000_db_home.zip
Takes a couple of minutes
Once complete, run this – change EE to SE if it’s Standard Edition to be installed
./runInstaller -ignorePrereq -waitforcompletion -silent \
-responseFile ${ORACLE_HOME}/install/response/db_install.rsp \
oracle.install.option=INSTALL_DB_SWONLY \
ORACLE_HOSTNAME=${ORACLE_HOSTNAME} \
UNIX_GROUP_NAME=oinstall \
INVENTORY_LOCATION=${ORA_INVENTORY} \
SELECTED_LANGUAGES=en,en_GB \
ORACLE_HOME=${ORACLE_HOME} \
ORACLE_BASE=${ORACLE_BASE} \
oracle.install.db.InstallEdition=EE \
oracle.install.db.OSDBA_GROUP=dba \
oracle.install.db.OSBACKUPDBA_GROUP=dba \
oracle.install.db.OSDGDBA_GROUP=dba \
oracle.install.db.OSKMDBA_GROUP=dba \
oracle.install.db.OSRACDBA_GROUP=dba \
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false \
DECLINE_SECURITY_UPDATES=true
If you get a message saying the ORACLE_HOME is already registered, run this:
$ORACLE_HOME/oui/bin/runInstaller -silent -detachHome ORACLE_HOME="/u01/app/oracle/product/19.0.0/dbhome_1"
Or just ./runInstaller and use the GUI.
You may see “successfully setup software with warnings” – check the log
Once done
As a root user, execute the following script(s):
/u01/app/oracle/product/19.0.0/dbhome_1/root.sh
Optionally patch to the latest PSU now, or after the database has been upgraded
Using the Auto upgrade method
This uses the auto upgrade script as explained here
Download the latest version of the tool - AutoUpgrade Tool (Doc ID 2485457.1) and copy it to $ORACLE_HOME/rdbms/admin of 19C
[oracle@orapri ]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin
Create the sample config file with this command
[oracle@orapri admin]$ java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/autoupgrade.jar -create_sample_file config
Edit the config file to reflect the upgrade info – the SID, ORACLE_HOMEs, temp dir and hostname
#
# sample config file
#
# build version 19.9.1
# build date 2020/07/10 11:20:49
#
#
# Global configurations
#
# This directory will include the following
# (1) AutoUpgrade's global directory
# (2) Any logs, not directly tied to a job
# (3) Config files
# (4) progress.json and status.json
global.autoupg_log_dir=/tmp/upgrade_log
#
# Database number 1
#
upg1.log_dir=/tmp/upgrade_log
upg1.sid=ORCL
upg1.source_home=/u01/app/oracle/product/11.2.0/dbhome_1
upg1.target_home=/u01/app/oracle/product/19/0.0/dbhome_1
upg1.start_time=NOW
upg1.upgrade_node=orapri.localdomain # run ‘hostname’ command
#upg1.run_utlrp=[yes|no] # Optional. Whether or not to run utlrp after upgrade
#upg1.timezone_upg=[yes|no] # Optional. Whether or not to run the timezone upgrade
#upg1.target_version=[12.2|18|19|21] # Oracle version of the target ORACLE_HOME. Only required when the target Oracle database version is 12.2
create the log directory
[oracle@orapri admin]$ mkdir /tmp/upgrade_log
run the script in analyze mode (database needs to be open)
[oracle@orapri admin]$ java -jar autoupgrade.jar -config sample_config.cfg -mode analyze
It will start a process and open a console – you can let it run or enter some commands – lsj and tasks
AutoUpgrade 23.4.230921 launched with default internal options
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 Non-CDB(s) will be analyzed
Type 'help' to list console commands
upg> lsj
+----+-------+---------+---------+-------+----------+-------+----------------------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE|
+----+-------+---------+---------+-------+----------+-------+----------------------------+
| 100| ORCL|PRECHECKS|EXECUTING|RUNNING| 10:33:51| 0s ago|Loading database information|
+----+-------+---------+---------+-------+----------+-------+----------------------------+
Total jobs 1
upg> Job 100 completed
------------------- Final Summary --------------------
Number of databases [ 1 ]
Jobs finished [1]
Jobs failed [0]
Please check the summary report at:
/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.html
/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log
Once complete, go to the log directory and open the html file with a browser (ctrl-o)
/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.html
Click on the ‘Checks Report’ for details
If ‘FixUp Available’ is ‘YES’, the tool will perform it automatically.
Any ERRORS (red) that have ‘FixUp Available’ as ‘NO’ need to be done manually before the upgrade can start.
Any with ‘WARNING’ or ‘INFO’ and ‘NO’ can be left, they are optional fixes.
The config file can be edited to prevent any of the fixes from being applied if you want to do them manually.
If you do any manual fixes, run the analyze again.
If you want the job to do the fixes, run the tool in ‘fixups’ mode.
Note that this makes changes to the database, so make sure you have a backup.
This might take a while – check the status with ‘lsj’
[oracle@orapri admin]$ java -jar autoupgrade.jar -config sample_config.cfg -mode fixups
AutoUpgrade 23.4.230921 launched with default internal options
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 Non-CDB(s) will be processed
Type 'help' to list console commands
upg> lsj
+----+-------+---------+---------+-------+----------+-------+-------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED|MESSAGE|
+----+-------+---------+---------+-------+----------+-------+-------+
| 101| ORCL|PREFIXUPS|EXECUTING|RUNNING| 10:43:51| 0s ago| |
+----+-------+---------+---------+-------+----------+-------+-------+
Total jobs 1
upg> tasks
+---+--------------+-------------+
| ID| NAME| Job#|
+---+--------------+-------------+
| 1| main| WAITING|
| 42| event_loop|TIMED_WAITING|
| 43| console| RUNNABLE|
| 44| queue_reader| WAITING|
| 46| cmd-0| WAITING|
| 47| job_manager-0| WAITING|
|221|orcl-puifxl0-0| WAITING|
|222|orcl-puifxl0-1| WAITING|
|223|orcl-puifxl0-2| WAITING|
|224|orcl-puifxl0-3| WAITING|
|231| exec_loop| WAITING|
|240| sql-7E0BFF| RUNNABLE|
+---+--------------+-------------+
It will take a few minutes to run, but should return this
upg> Job 101 completed
------------------- Final Summary --------------------
Number of databases [ 1 ]
Jobs finished [1]
Jobs failed [0]
Please check the summary report at:
/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.html
/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log
Open the status.html file in a browser, and select the ‘fixup report’
If there are errors, fix them manually
If all OK, run the command with the ‘deploy’ mode – this will perform all the operations and upgrade the database, so make sure apps are aware and are shutdown first.
You can track the progress with ‘lsj’, or by opening this html file in a browser (ctrl-o)
/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/state.html
The page auto refreshes
This will take a while – it took 3.5 hours in a test VM,
[oracle@orapri admin]$ java -jar autoupgrade.jar -config sample_config.cfg -mode deploy
AutoUpgrade 23.4.230921 launched with default internal options
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 Non-CDB(s) will be processed
Type 'help' to list console commands
upg> lsj
+----+-------+---------+---------+-------+----------+-------+----------------------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE|
+----+-------+---------+---------+-------+----------+-------+----------------------------+
| 102| ORCL|PRECHECKS|EXECUTING|RUNNING| 10:58:54| 1s ago|Loading database information|
+----+-------+---------+---------+-------+----------+-------+----------------------------+
Total jobs 1
upg> tasks
+---+-------------+-------------+
| ID| NAME| Job#|
+---+-------------+-------------+
| 1| main| WAITING|
| 43| event_loop|TIMED_WAITING|
| 44| console| RUNNABLE|
| 45| queue_reader| WAITING|
| 47| cmd-0| WAITING|
| 48|job_manager-0| WAITING|
|118| orcl-puic-0| WAITING|
|119| orcl-puic-1| WAITING|
|120| orcl-puic-2| WAITING|
|121| orcl-puic-3| WAITING|
|122| orcl-puic-4| WAITING|
|123| orcl-puic-5| WAITING|
|124| orcl-puic-6| WAITING|
|125| orcl-puic-7| WAITING|
|143| sql-3C0AFD| RUNNABLE|
|148| sql-B5AC35| RUNNABLE|
|151| sql-41ADBC| RUNNABLE|
|152| sql-BB8D11| RUNNABLE|
|153| sql-B7E20B| RUNNABLE|
|154| sql-AF4981| RUNNABLE|
|155| sql-B9CCEE| RUNNABLE|
|156| sql-EF6528| RUNNABLE|
+---+-------------+-------------+
You can also run ‘status’
upg> status
Config
User configuration file [/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/sample_config.cfg]
General logs location [/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto]
Mode [DEPLOY]
Jobs Summary
Total databases in configuration file [1]
Total Non-CDB being processed [1]
Total Containers being processed [0]
Jobs finished successfully [0]
Jobs finished/stopped [0]
Jobs in progress [1]
Progress
+---+---------------------------------------------------------+
|Job| Progress|
+---+---------------------------------------------------------+
|102|[|||||||||||||||||||||||||| ] 50 %|
+---+---------------------------------------------------------+
‘help’ shows all available commands:
upg> help
exit // To close and exit
help // Displays help
lsj [<option>] [-a <number>] // list jobs by status up to n elements.
-f Filter by finished jobs.
-r Filter by running jobs.
-e Filter by jobs with errors.
-p Filter by jobs being prepared.
-n <number> Display up to n jobs.
-a <number> Repeats the command (in <number> seconds).
lsr // Displays the restoration queue
lsa // Displays the stop queue
tasks // Displays the tasks running
clear // Clears the terminal
resume -job <number> [-ignore_errors=<ORA-#####,ORA-#####>] // Restarts a job with option to ignore errors
status [<option>] [-a <number>] // Summary of current execution
-config Show Config Information
-job <number> Summary of a given job
-job <number> -c <dbname> Show details of container
-a [<number>] Repeats the command (in <number> seconds).
restore -job <number> // Restores the database to its state prior to the upgrade
restore all_failed // Restores all failed jobs to their previous states prior to the upgrade
logs // Displays all the log locations
stop -job <number> // Stops the specified job
h[ist] // Displays the command line history
/[<number>] // Executes the command specified from the history. The default is the last command
meta // Displays Internal latch count
hwinfo // Displays additional information
fxlist -job <number> [<option>] // FixUps summary
-c <dbname> Container specific FixUps
-c <dbname> alter <check> run <yes|no|skip> Update Run Configuration
It should move on to the fixups stage:
After a while you should see this:
upg> Job 102 completed
------------------- Final Summary --------------------
Number of databases [ 1 ]
Jobs finished [1]
Jobs failed [0]
Jobs restored [0]
Jobs pending [0]
---- Drop GRP at your convenience once you consider it is no longer needed ----
Drop GRP from ORCL: drop restore point AUTOUPGRADE_9212_ORCL112040
Please check the summary report at:
/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.html
/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log
The upgrade is complete – you can check the alert log and drop the GRP
Make sure you reset your environment
[oracle@orapri ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
[oracle@orapri ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@orapri ~]$ sqlplus / as sysdba
[oracle@orapri admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 6 12:22:47 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
BANNER_FULL
--------------------------------------------------------------------------------
BANNER_LEGACY
--------------------------------------------------------------------------------
CON_ID
----------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
0
BANNER
--------------------------------------------------------------------------------
BANNER_FULL
--------------------------------------------------------------------------------
BANNER_LEGACY
--------------------------------------------------------------------------------
CON_ID
----------
SQL> drop restore point AUTOUPGRADE_9212_ORCL112040;
Restore point dropped.
Check what is needed to complete the upgrade – open this file in a browser
/tmp/upgrade_log/ORCL/n/postchecks/orcl_postupgrade.html
Where n is the job number
In a real environment, perform these steps to complete the upgrade.
Stop the listener because it’s running from the V11 home, and restart it from the V19 home
[oracle@orapri ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
[oracle@orapri ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@orapri ~]$ lsnrctl stat
LSNRCTL for Linux: Version 11.2.0.4 - Production on 31-JUL-2020 13:55:09
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orapri.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4 – Production
[oracle@orapri ~]$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.4 - Production on 31-JUL-2020 13:55:15
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orapri.localdomain)(PORT=1521)))
The command completed successfully
[oracle@orapri ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
[oracle@orapri ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@orapri ~]$ lsnrctl start
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 31-JUL-2020 13:55:28
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Starting /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/orapri/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orapri.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
You should now run the stats gathering steps
execute dbms_stats.gather_fixed_object_stats
Upgrade should be done.
Upgrade method with dbua
Once the software is installed, run the pre-upgrade step
Set the ORACLE_HOME to the V11 home but run the preupgrade.jar file from the V19 home:
. oraenv
ORCL
echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/dbhome_1
Run this on one line
/u01/app/oracle/product/19.0.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar TERMINAL TEXT
It will produce a report – see the appendix for the full report. Apply the fixes that don’t have (AUTOFIXUP) next to them:
Recovery file dest size
Processes (needs outage)
EM remove
OLAP catalogue remove
e.g
1. Set DB_RECOVERY_FILE_DEST_SIZE initialization parameter to at least 4981
MB. Check alert log during the upgrade to ensure there is remaining free
space available in the recovery area.
DB_RECOVERY_FILE_DEST_SIZE is set at 4182 MB. There is currently 4151 MB
of free space remaining, which may not be adequate for the upgrade.
I had to set this to 8GB – setting it to 5GB failed.
Processes
EM remove
OLAP catalogue remove
@$ORACLE_HOME/olap/admin/catnoamd.sql
I didn’t bother with APEX.
Run the pre-upgrade sql:
@/u01/app/oracle/cfgtoollogs/ORCL/preupgrade/preupgrade_fixups.sql
If any fail, run them manually.
Perform the upgrade
Shut the database
Copy the config files to the new home
Copy the listener.ora, tnsnames.ora and sqlnet.ora files to the new HOME, and update any references to the ORACLE_HOME
If the V11 passwords are not complex, then this is needed in the sqlnet.ora file until they can be reset
SQLNET.ALLOWED_LOGON_VERSION_SERVER=11
Stop the V11 listener and restart from the V19 home
lsnrctl stop
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
lsnrctl start
Either run the dbua gui, or run the dbupgrade shell script, or run the perl and sql scripts – one of these:
Either
dbua
or
$ORACLE_HOME/bin/dbupgrade
or
cd $ORACLE_HOME/rdbms/admin
$ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql
If running the scripts, start the database from the V19 home using ‘startup upgrade’ – this is not needed for the dbua
If running the dbua you need to stop the database and run the GUI from the V19 home.
dbua
The dbua can do some of the pre and post upgrade steps.
Saw this, it failed on the last step, so clicked ‘Ignore’
The log screen showed 21 hours upgrade time but this included waiting for the input to skip the last step – so I suspect the time for that if monitoring would only be a few minutes.
If running the scripts you may need to do this after the upgrade – it might be worth doing it anyway -
@/u01/app/oracle/cfgtoollogs/ORCL/preupgrade/postupgrade_fixups.sql
You should now run the stats gathering steps
execute dbms_stats.gather_fixed_object_stats
Upgrade should be done.
Upgrade using script
Once the software is installed, run the pre-upgrade step
Set the ORACLE_HOME to the V11 home but run the preupgrade.jar file from the V19 home:
. oraenv
ORCL
echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/dbhome_1
Run this on one line
/u01/app/oracle/product/19.0.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar TERMINAL TEXT
It will produce a report – see the appendix for the full report. Apply the fixes that don’t have (AUTOFIXUP) next to them:
Recovery file dest size
Processes (needs outage)
EM remove
OLAP catalogue remove
e.g
1. Set DB_RECOVERY_FILE_DEST_SIZE initialization parameter to at least 4981
MB. Check alert log during the upgrade to ensure there is remaining free
space available in the recovery area.
DB_RECOVERY_FILE_DEST_SIZE is set at 4182 MB. There is currently 4151 MB
of free space remaining, which may not be adequate for the upgrade.
I had to set this to 8GB – setting it to 5GB failed.
Processes
EM remove
OLAP catalogue remove
@$ORACLE_HOME/olap/admin/catnoamd.sql
I didn’t bother with APEX.
Once the manual steps have been done, run the pre-upgrade sql:
@/u01/app/oracle/cfgtoollogs/ORCL/preupgrade/preupgrade_fixups.sql
If any fail, run them manually.
Run utlrp to compile any invalid objects
@$ORACLE_HOME/rdbms/admin/utlrp
Perform the upgrade
Shut the database
Copy the config files to the new home
Copy the listener.ora, tnsnames.ora and sqlnet.ora files to the new HOME, and update any references to the ORACLE_HOME
If the V11 passwords are not complex, then this is needed in the sqlnet.ora file until they can be reset
SQLNET.ALLOWED_LOGON_VERSION_SERVER=11
Stop the V11 listener and restart from the V19 home
lsnrctl stop
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
lsnrctl start
Start the database from the V19 home in upgrade mode
Exit from SQLPlus and run the upgrade script from the shell
Once complete, restart the database and run the post upgrade steps – make sure you are in the V19 home
Check the timezone file and update if needed
Restart in upgrade mode and run the SQL to update the TZ file
Stop and restart the database normally, then run the TZ upgrade command
Run the post-fixup script
@/u01/app/oracle/cfgtoollogs/ORCL/preupgrade/postupgrade_fixups.sql
Edit the /etc/oratab to reflect the new ORACLE_HOME
Upgrade complete
Appendix A – preupgrade report
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2022-08-03T10:53:16
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: ORCL
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: No Patch Bundle applied
Compatible: 11.2.0.4.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
JServer JAVA Virtual Machine [to be upgraded] VALID
Oracle XDK for Java [to be upgraded] VALID
Oracle Workspace Manager [to be upgraded] VALID
OLAP Analytic Workspace [to be upgraded] VALID
Oracle Enterprise Manager Repository [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
Expression Filter [to be upgraded] VALID
Rule Manager [to be upgraded] VALID
Oracle OLAP API [to be upgraded] VALID
==============
BEFORE UPGRADE
==============
REQUIRED ACTIONS
================
1. Set DB_RECOVERY_FILE_DEST_SIZE initialization parameter to at least 4981
MB. Check alert log during the upgrade to ensure there is remaining free
space available in the recovery area.
DB_RECOVERY_FILE_DEST_SIZE is set at 4182 MB. There is currently 4151 MB
of free space remaining, which may not be adequate for the upgrade.
Currently:
Fast recovery area : /u03/fast_recovery_area
Limit : 4182 MB
Used : 31 MB
Available : 4151 MB
The database has archivelog mode enabled, and the upgrade process will
need free space to generate archived logs to the recovery area specified
by initialization parameter DB_RECOVERY_FILE_DEST. The logs generated
must not overflow the limit set by DB_RECOVERY_FILE_DEST_SIZE, as that
can cause the upgrade to not proceed.
RECOMMENDED ACTIONS
===================
2. 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.
Parameter Currently 19 minimum
--------- --------- ------------------
processes 150 300
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.
3. Remove the EM repository.
- Copy the $ORACLE_HOME/rdbms/admin/emremove.sql script from the target
19 ORACLE_HOME into the source 11.2.0.4.0 ORACLE_HOME.
Step 1: If database control is configured, stop EM Database Control,
using the following command
$> emctl stop dbconsole
Step 2: Connect to the database using the SYS account AS SYSDBA
SET ECHO ON;
SET SERVEROUTPUT ON;
@emremove.sql
Without the set echo and serveroutput commands, you will not be able to
follow the progress of the script.
The database has an Enterprise Manager Database Control repository.
Starting with Oracle Database 12c, the local Enterprise Manager Database
Control does not exist anymore. The repository will be removed from your
database during the upgrade. This step can be manually performed before
the upgrade to reduce downtime.
4. Remove OLAP Catalog by running the 11.2.0.4.0 SQL script
$ORACLE_HOME/olap/admin/catnoamd.sql script.
The OLAP Catalog component, AMD, exists in the database.
Starting with Oracle Database 12c, the OLAP Catalog (OLAP AMD) is
desupported and will be automatically marked as OPTION OFF during the
database upgrade if present. Oracle recommends removing OLAP Catalog
(OLAP AMD) before database upgrade. This step can be manually performed
before the upgrade to reduce downtime.
5. Upgrade Oracle Application Express (APEX) manually before the database
upgrade.
The database contains APEX version 3.2.1.00.12. Upgrade APEX to at least
version 18.2.0.00.12.
Starting with Oracle Database Release 18, APEX is not upgraded
automatically as part of the database upgrade. Refer to My Oracle Support
Note 1088970.1 for information about APEX installation and upgrades.
6. (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 11.2.0.4
Oracle Database Performance Tuning Guide.
7. (AUTOFIXUP) Directly grant ADMINISTER DATABASE TRIGGER privilege to the
owner of the trigger or drop and re-create the trigger with a user that
was granted directly with such. You can list those triggers using: SELECT
OWNER, TRIGGER_NAME FROM DBA_TRIGGERS WHERE
TRIM(BASE_OBJECT_TYPE)='DATABASE' AND OWNER NOT IN (SELECT GRANTEE FROM
DBA_SYS_PRIVS WHERE PRIVILEGE='ADMINISTER DATABASE TRIGGER').
There is one or more database triggers whose owner does not have the
right privilege on the database.
The creation of database triggers must be done by users granted with
ADMINISTER DATABASE TRIGGER privilege. Privilege must have been granted
directly.
8. (AUTOFIXUP) Gather statistics on fixed objects prior the upgrade.
None of the fixed object tables have had stats collected.
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.
INFORMATION ONLY
================
9. 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
---------- ---------- -----------
SYSAUX 490 MB 722 MB
SYSTEM 740 MB 1171 MB
TEMP 20 MB 150 MB
UNDOTBS1 30 MB 446 MB
Minimum tablespace sizes for upgrade are estimates.
10. Run $ORACLE_HOME/rdbms/admin/catnoexf.sql located in the new Oracle
Database Oracle home to remove both EXF and RUL.
Expression Filter (EXF) or Rules Manager (RUL) exist in the database.
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.
11. 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.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database ORCL
which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
SQL>@/u01/app/oracle/cfgtoollogs/ORCL/preupgrade/preupgrade_fixups.sql
=============
AFTER UPGRADE
=============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
12. Upgrade the database time zone file using the DBMS_DST package.
The database is using time zone file version 14 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.
13. To identify directory objects with symbolic links in the path name, run
$ORACLE_HOME/rdbms/admin/utldirsymlink.sql AS SYSDBA after upgrade.
Recreate any directory objects listed, using path names that contain no
symbolic links.
Some directory object path names may currently contain symbolic links.
Starting in Release 18c, symbolic links are not allowed in directory
object path names used with BFILE data types, the UTL_FILE package, or
external tables.
14. (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.
15. 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 11.2.0.4
Oracle Database Performance Tuning Guide.
INFORMATION ONLY
================
16. Check the Oracle documentation for the identified components for their
specific upgrade procedure.
The database upgrade script will not upgrade the following Oracle
components: OLAP Catalog,OWB
The Oracle database upgrade script upgrades most, but not all Oracle
Database components that may be installed. Some components that are not
upgraded may have their own upgrade scripts, or they may be deprecated or
obsolete.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database ORCL
which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
SQL>@/u01/app/oracle/cfgtoollogs/ORCL/preupgrade/postupgrade_fixups.sql
==================
PREUPGRADE SUMMARY
==================
/u01/app/oracle/cfgtoollogs/ORCL/preupgrade/preupgrade.log
/u01/app/oracle/cfgtoollogs/ORCL/preupgrade/preupgrade_fixups.sql
/u01/app/oracle/cfgtoollogs/ORCL/preupgrade/postupgrade_fixups.sql
Execute fixup scripts as indicated below:
Before upgrade:
Log into the database and execute the preupgrade fixups
@/u01/app/oracle/cfgtoollogs/ORCL/preupgrade/preupgrade_fixups.sql
After the upgrade:
Log into the database and execute the postupgrade fixups
@/u01/app/oracle/cfgtoollogs/ORCL/preupgrade/postupgrade_fixups.sql
Preupgrade complete: 2022-08-03T10:53:16
