Description
In this article I will demonstrate an overview on manually upgrading Oracle database from 11.2.0.4 to 19.3.0.0 on Oracle Linux 7 64bit platform.
In this example, the source database version is 11.2.0.4. So we can go for a direct upgrade to 19c.
Below Oracle versions can be directly upgraded to 19c. Refer this document
- 11.2.0.4
- 12.1.0.2
- 12.2.0.1
- 18c
We will be covering the following steps:
- Installing Oracle 19.3.0.0 binaries
- Executing the preupgrade jar tool
- Performing the preupgrade actions
- Upgrade the database
- Perform the postupgrade actions
Environment Details:
Source Hostname: ol7-dev
Database version: 11.2.0.4
Database Name: oradev
ORACLE_HOME: /u01/app/oracle/product/11.2.0/db_1
Target Hostname: ol7-dev
Databaes Version: 19.3.0.0
Database name: oradev
ORACLE_HOME: /u01/app/oracle/product/19.3.0/dbhome_1
1. Installing Oracle 19c binaries.
I have already completed the installation of 19c, Refer here for step by step installation of Oracle 19.3.0.0 on Linux 7 64bit.
2. Executing the pre-upgrade jar tool
Pre-upgrade information tool is used to determine the instance readiness before upgrading the database. The pre-upgrade script will generate the fix for many issues before you upgrade to new Oracle home.
The Pre-upgrade Information Tool is available in the new release Oracle home, in path ORACLE_HOME/rdbms/admin/preupgrade.jar
$ORACLE_BASE/product/19.3.0/dbhome_1/rdbms/admin/preupgrade.jar
Run the pre-upgrade tool
Make sure to run the tool from source ORACLE_HOME.
$ORACLE_BASE/product/11.2.0/db_1/jdk/bin/java -jar $ORACLE_BASE/product/19.3.0/dbhome_1/rdbms/admin/preupgrade.jar DIR /u01/preupgrade
Output:
export ORACLE_SID=oradev
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
[oracle@ol7-dev u01]$ $ORACLE_BASE/product/11.2.0/db_1/jdk/bin/java -jar $ORACLE_BASE/product/19.3.0/dbhome_1/rdbms/admin/preupgrade.jar DIR /u01/preupgrade
==================
PREUPGRADE SUMMARY
==================
/u01/preupgrade/preupgrade.log
/u01/preupgrade/preupgrade_fixups.sql
/u01/preupgrade/postupgrade_fixups.sql
Execute fixup scripts as indicated below:
Before upgrade:
Log into the database and execute the preupgrade fixups
@/u01/preupgrade/preupgrade_fixups.sql
After the upgrade:
Log into the database and execute the postupgrade fixups
@/u01/preupgrade/postupgrade_fixups.sql
Preupgrade complete: 2020-11-07T22:23:28
[oracle@ol7-dev u01]$
Detailed output is generated in DIR path, in my case /u01/preupgrade/preupgrade.log
3. Performing the pre-upgrade actions
Run the preupgrade_fixups.sql
SQL> @/u01/preupgrade/preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 1
Generated on: 2020-11-07 22:23:20
For Source Database: ORADEV
Source Database Version: 11.2.0.4.0
For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
1. parameter_min_val NO Manual fixup recommended.
2. em_present NO Manual fixup recommended.
3. amd_exists NO Manual fixup recommended.
4. apex_manual_upgrade NO Manual fixup recommended.
5. dictionary_stats YES None.
6. trgowner_no_admndbtrg YES None.
7. pre_fixed_objects YES None.
8. tablespaces_info NO Informational only.
Further action is optional.
9. exf_rul_exists NO Informational only.
Further action is optional.
10. rman_recovery_version NO Informational only.
Further action is optional.
The preupgrade_fixups.sql output lists multiple recommendations which has to be fix manually. We will fix the below recommendations manually and re-run the preupgrade_fixups.sql
1. parameter_min_val NO Manual fixup recommended.
2. em_present NO Manual fixup recommended.
3. amd_exists NO Manual fixup recommended.
4. apex_manual_upgrade NO Manual fixup recommended.
Recommendation 1: parameter_min_val
1. 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.
Action: Increase the process parameter
sqlplus "/as sysdba"
alter system set processes=300 scope=spfile;
shutdown immediate;
startup;
Recommendation 2: Remove the EM repositor
Stop the em dbconsole
[oracle@ol7-dev u01]$ emctl stop dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation. All rights reserved.
https://ol7-dev:1158/em/console/aboutApplication
Stopping Oracle Enterprise Manager 11g Database Control ...
... Stopped.
Copy emremove.sql from target 19c ORACLE_HOME/rdbms/admin to source 11g ORACLE_HOME/rdbms/admin
[oracle@ol7-dev admin]$ cp /u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/emremove.sql /u01/app/oracle/product/11.2.0/db_1/rdbms/admin/
Connect to the database using SYS user and run emremove.sql
[oracle@ol7-dev admin]$ sqlplus
SQL*Plus: Release 11.2.0.4.0 Production on Sat Nov 7 22:53:58 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @$ORACLE_BASE/product/11.2.0/db_1/rdbms/admin/emremove.sql
old 70: IF (upper('&LOGGING') = 'VERBOSE')
new 70: IF (upper('VERBOSE') = 'VERBOSE')
PL/SQL procedure successfully completed.
SQL>
Recommendation 3: Remove OLTP catalog
Connect to the database as SYS user and remove OLAP Catalog by running the 11.2.0.4.0 SQL script under $ORACLE_HOME/olap/admin/catnoamd.sql script.
SQL> @$ORACLE_HOME/olap/admin/catnoamd.sql
Synonym dropped.
Synonym dropped.
.
<output truncated>
.
Type dropped.
PL/SQL procedure successfully completed.
Role dropped.
PL/SQL procedure successfully completed.
1 row deleted.
SQL>
Recommendation 4: Upgrade Oracle Application Express (APEX) manually.
To upgrade to latest Application Express, download the latest version from the below link here
Upgrading APEX
Check the current APEX Version
SQL> Select Comp_name, status, Version
From Dba_Registry
where comp_id='APEX';
COMP_NAME STATUS VERSION
---------------------------------------- ------------ ------------------------------
Oracle Application Express VALID 3.2.1.00.12
SQL>
From the directory which holds the APEX unzipped software, connect to sqlplus as SYS user and run apexins.sql
cd /u01/software/db-soft/apex
sqlplus /as sysdba
SQL> @apexins.sql SYSAUX SYSAUX TEMP /i/
Check the upgraded APEX Version
SQL> Select Comp_name, status, Version
From Dba_Registry
where comp_id='APEX';
COMP_NAME STATUS VERSION
---------------------------------------- ------------ ------------------------------
Oracle Application Express VALID 20.2.0.00.20
SQL>
Note: No Actions required from Recommendation 5, 6 and 7
Recommendation 8: Extend the tablespaces with minimum size requirement
8. 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 550 MB 775 MB
SYSTEM 750 MB 1178 MB
TEMP 29 MB 150 MB
UNDOTBS1 90 MB 446 MB
Alter the datafile size:
alter database datafile '/u01/app/oracle/oradata/oradev/system01.dbf' resize 1178M;
alter database datafile '/u01/app/oracle/oradata/oradev/sysaux01.dbf' resize 775M;
alter database datafile '/u01/app/oracle/oradata/oradev/undotbs01.dbf' resize 446M;
alter database tempfile '/u01/app/oracle/oradata/oradev/temp01.dbf' resize 150M;
Now re-run the preupgrade_fixups.sql
SQL> @/u01/preupgrade/preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 1
Generated on: 2020-11-07 22:23:20
For Source Database: ORADEV
Source Database Version: 11.2.0.4.0
For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
1. parameter_min_val YES None.
2. em_present YES None.
3. amd_exists YES None.
4. apex_manual_upgrade YES None.
5. dictionary_stats YES None.
6. trgowner_no_admndbtrg YES None.
7. pre_fixed_objects YES None.
8. tablespaces_info NO Informational only.
Further action is optional.
9. exf_rul_exists NO Informational only.
Further action is optional.
10. rman_recovery_version 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>
4. Upgrading the database
Once the pre-upgrade actions are completed, shutdown the database to start the upgrade process
sqlplus /as sysdba
shutdown immediate;
exit
Copy the spfile, password file and listener file from old ORACLE_HOME to new 19c ORACLE_HOME
[oracle@ol7-dev ~]$ cp $ORACLE_HOME/dbs/orapworadev /u01/app/oracle/product/19.3.0/dbhome_1/dbs/
[oracle@ol7-dev ~]$ cp $ORACLE_HOME/dbs/spfileoradev.ora /u01/app/oracle/product/19.3.0/dbhome_1/dbs/
[oracle@ol7-dev ~]$ cp $ORACLE_HOME/network/admin/listener.ora /u01/app/oracle/product/19.3.0/dbhome_1/network/admin
Stop listener running on 11g home and start it from 19c home
[oracle@ol7-dev ~]$ lsnrctl stop
[oracle@ol7-dev ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
[oracle@ol7-dev ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@ol7-dev ~]$ lsnrctl start
Start the database from 19c ORACLE_HOME and start the upgrade.
export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
sqlplus /as sysdba
startup upgrade;
--output--
[oracle@ol7-dev ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
[oracle@ol7-dev ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@ol7-dev ~]$
[oracle@ol7-dev ~]$ sqlplus
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 8 04:48:01 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to an idle instance.
SQL>
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 1258290752 bytes
Fixed Size 8896064 bytes
Variable Size 805306368 bytes
Database Buffers 436207616 bytes
Redo Buffers 7880704 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> exit
Run the DB Upgrade utility
$ORACLE_HOME/bin/dbupgrade
--output--
Argument list for [/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in c = 0
Do not run in C = 0
.
.
.
<output truncated>
.
.
.
Grand Total Time: 3342s
LOG FILES: (/u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/oradev/upgrade20201108045317/catupgrd*.log)
Upgrade Summary Report Located in:
/u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/oradev/upgrade20201108045317/upg_summary.log
Grand Total Upgrade Time: [0d:0h:55m:42s]
[oracle@ol7-dev ~]$
DB is in shutdown state after the dbupgrade process. Start the database
sqlplus / as sysdba
startup
exit
5. Performing the post-upgrade actions
Connect to sqlplus and run the postupgrade_fixups.sql script
sqlplus / as sysdba
@/u01/preupgrade/postupgrade_fixups.sql
--output--
SQL> @/u01/preupgrade/postupgrade_fixups.sql
Session altered.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Package created.
No errors.
Package body created.
PL/SQL procedure successfully completed.
No errors.
Package created.
No errors.
Package body created.
No errors.
Executing Oracle POST-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 1
Generated on: 2020-11-07 22:23:25
For Source Database: ORADEV
Source Database Version: 11.2.0.4.0
For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
11. old_time_zones_exist NO Manual fixup recommended.
12. dir_symlinks YES None.
13. post_dictionary YES None.
14. post_fixed_objects NO Informational only.
Further action is optional.
15. upg_by_std_upgrd YES None.
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 upgrade is not fully complete. To resolve the outstanding issues, start by reviewing the postupgrade_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 messagefrom the preupgrade which explains in more detail what still needs to be done.
PL/SQL procedure successfully completed.
Session altered.
SQL>
Output from postupgrade_fixups.sql recommeds to upgrade the time zone file version and Gather statistics on fixed objects. Below is the recommended action from preupgrade.jar output file.
RECOMMENDED ACTIONS
===================
11. 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.
14. 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.
Upgrade the database timezone file
Connect to sqlplus and start the database in upgrade mode
sqlplus / as sysdba
shutdown immediate;
startup upgrade;
Check the current timezone file version
SELECT * FROM v$timezone_file;
--output:
SQL> SELECT * FROM v$timezone_file;
FILENAME VERSION CON_ID
-------------------- ---------- ----------
timezlrg_14.dat 14 0
Start the upgrade window
SET SERVEROUTPUT ON
DECLARE
l_tz_version PLS_INTEGER;
BEGIN
l_tz_version := DBMS_DST.get_latest_timezone_version;
DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);
DBMS_DST.begin_upgrade(l_tz_version);
END;
--output
SQL> SET SERVEROUTPUT ON
DECLARE
l_tz_version PLS_INTEGER;
BEGIN
l_tz_version := DBMS_DST.get_latest_timezone_version;
DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);
DBMS_DST.begin_upgrade(l_tz_version);
END;
/SQL> 2 3 4 5 6 7 8
l_tz_version=32
An upgrade window has been successfully started.
PL/SQL procedure successfully completed.
Start the database in normal mode
shutdown immediate;
startup;
Do the upgrade
SET SERVEROUTPUT ON
DECLARE
l_failures PLS_INTEGER;
BEGIN
DBMS_DST.upgrade_database(l_failures);
DBMS_OUTPUT.put_line('DBMS_DST.upgrade_database : l_failures=' || l_failures);
DBMS_DST.end_upgrade(l_failures);
DBMS_OUTPUT.put_line('DBMS_DST.end_upgrade : l_failures=' || l_failures);
END;
--output
SQL> SET SERVEROUTPUT ON
DECLARE
l_failures PLS_INTEGER;
BEGIN
DBMS_DST.upgrade_database(l_failures);
DBMS_OUTPUT.put_line('DBMS_DST.upgrade_database : l_failures=' || l_failures);
DBMS_DST.end_upgrade(l_failures);
DBMS_OUTPUT.put_line('DBMS_DST.end_upgrade : l_failures=' || l_failures);
END;
/SQL> 2 3 4 5 6 7 8 9
Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
Number of failures: 0
.
.
<output truncated>
.
.
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
DBMS_DST.upgrade_database : l_failures=0
An upgrade window has been successfully ended.
DBMS_DST.end_upgrade : l_failures=0
PL/SQL procedure successfully completed.
SQL>
Check the new timezone settings
SELECT * FROM v$timezone_file;
--output
SQL> SELECT * FROM v$timezone_file;
FILENAME VERSION CON_ID
-------------------- ---------- ----------
timezlrg_32.dat 32 0
Gather statistics on fixed objects
Connect to sqlplus as sys user and execute DBMS_STATS.GATHER_FIXED_OBJECTS_STATS
sqlplus / as sysdba
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS
exit
--output
SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
PL/SQL procedure successfully completed.
SQL>
Now re-run the postupgrade_fixups.sql
sqlplus / as sysdba
@/u01/preupgrade/postupgrade_fixups.sql
exit
--output
SQL> @/u01/preupgrade/postupgrade_fixups.sql
Session altered.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Package created.
No errors.
Package body created.
PL/SQL procedure successfully completed.
No errors.
Package created.
No errors.
Package body created.
No errors.
Executing Oracle POST-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 1
Generated on: 2020-11-07 22:23:25
For Source Database: ORADEV
Source Database Version: 11.2.0.4.0
For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
11. old_time_zones_exist YES None.
12. dir_symlinks YES None.
13. post_dictionary YES None.
14. post_fixed_objects NO Informational only.
Further action is optional.
15. upg_by_std_upgrd YES None.
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 upgrade is not fully complete. To resolve the outstanding issues, start by reviewing the postupgrade_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 messagefrom the preupgrade which explains in more detail what still needs to be done.
PL/SQL procedure successfully completed.
Session altered.
SQL>
Database has been successfully upgraded to 19c.
[oracle@ol7-dev ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Nov 13 20:07:34 2020
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 name, open_mode, version from v$database, v$instance;
NAME OPEN_MODE VERSION
--------- -------------------- -----------------
ORADEV READ WRITE 19.0.0.0.0
SQL>
Visit Today : 112
Total Visit : 423657
Total Hits : 1018907
Who's Online : 6