List of target upgrade versions from supported source version along with the certificate, data source support.oracle.com.
Direct Upgrade Version List:
Source DB Version | Target DB Version |
18.1 | 19c |
12.2.0.2 | 19c |
12.1.0.2 | 19c |
11.2.04 | 19c |
Indirect Upgrade Version List:
Source DB Version | Intermediate DB Version | Target DB Version |
12.1.0.1 | 12.1.0.2/12.2.0.1 | 19c |
11.2.0.1/11.2.0.2/11.2.0.3 | 11.2.0.4 | 19c |
11.1.0.6/11.1.0.7 | 11.2.0.4 | 19c |
10.2.0.2/10.2.0.3/10.2.0.4/10.2.0.5 | 11.2.0.4/12.1.0.2 | 19c |
10.1.0.5 | 11.2.0.4/12.1.0.2 | 19c |
9.2.0.8 or earlier | 11.2.0.4 | 19c |
19c Certificate:
Below are the environmental details of this demonstration.
COMPONENTS | SOURCE | TARGET |
Database Name & Type | LABDB03, Standalone | LABDB03, Standalone |
Database Version | 12.2.0.1 | 19.3.0.0 |
Oracle Home | /u01/app/oracle/product/12201/db_1 | /u01/app/oracle/product/1930/db_1 |
DB Server Type | Linux, OEL 7.9 | Linux, OEL 7.9 |
We will complete the upgrade from 12c to 19c using DBUA in the below three-part followed by detailed steps.
1. Pre-Checks / Pre-Steps
2. Upgrade Dataase using DBUA
3. Post upgrade Steps
1. Pre-Checks / Pre-Steps
Click here to get all steps for prechecks and follow section 1. Pre-Checks / Pre-Steps.
2. Upgrade Dataase using DBUA
2.1: Set Environment: Set/Export the environment for 19c i.e new ORACLE_HOME
1
2
3
4
|
[oracle@DBsGuruN2 ~]$ export ORACLE_HOME=/u01/app/oracle/product/1930/db_1 [oracle@DBsGuruN2 ~]$ export PATH=$ORACLE_HOME/bin:$PATH [oracle@DBsGuruN2 ~]$ which dbua /u01/app/oracle/product/1930/db_1/bin/dbua |
2.2: Launch DBUA: Launch dbua tool.
1
2
|
[oracle@DBsGuruN2 ~]$ dbua Logs directory: /u01/app/oracle/cfgtoollogs/dbua/upgrade2022-02-25_05-27-12PM |
2.3: Select Database: Select the database name which is required to be upgraded and also put the SYS password then click Next.
2.4: Prerequisite checks: Upgrade checks performed by dbua, once 100% looks good i.e there should not be any warnings, click on Next.
2.5: Upgrade options: Check the below options and click Next:
A. Enable parallel options.
B. Recompile invalid objects while post-upgrade.
C. Upgrade Timezone.
2.6: Backup & recovery options: Select option I have my own backup and recovery strategy since we have our own backup along with guaranteed restored options which have been performed in the prechecks step and click Next.
2.7: Network configuration (Listener): You can select the existing Listener name if it’s running from the same upgraded ORACLE HOME 19c else create a new Listener in case no LISTENER service from the new ORACLE HOME/version and click Next.
2.8: Configuration management: Safely skip this screen if not using EM and click Next.
2.9: Summary: Review the summary carefully which tells you about options opted from step 3 to step 8 along with the OLD & NEW version and HOME of the database. Still, we have the option to edit, upon completion of review/edit click on Finish.
2.10: Upgrade in progress: Take a long breath and closely monitor upgrade progress along with the database alert log and DBUA log. Here you also have an option to Pause your activities.
2.11: Upgrade Results: This is the last screen of the upgrade where you can review the result of the upgrade like database name, before and after upgrade version, components status along with the time taken in the upgrade, Timezone version, etc and click on Close.
3. Post upgrade Steps
3.1: Review upgrade log: Go to upgrade log location and review log files.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
|
[oracle@DBsGuruN2 ~]$ cd /u01/app/oracle/cfgtoollogs/dbua/upgrade2022-02-25_05-27-12PM/labdb03/ [oracle@DBsGuruN2 labdb03]$ ls -lrt total 72168 drwxr-x ---. 3 oracle oinstall 21 Feb 25 17:27 oracle -rw-r -----. 1 oracle oinstall 7884 Feb 25 17:27 preupgrade_driver.sql -rw-r -----. 1 oracle oinstall 15085 Feb 25 17:27 dbms_registry_extended.sql -rw-r -----. 1 oracle oinstall 455876 Feb 25 17:27 preupgrade_package.sql -rw-r -----. 1 oracle oinstall 14016 Feb 25 17:27 parameters.properties -rw-r -----. 1 oracle oinstall 100166 Feb 25 17:27 preupgrade_messages.properties drwxr-x ---. 3 oracle oinstall 24 Feb 25 17:27 upgrade -rw-r -----. 1 oracle oinstall 41134 Feb 25 17:27 components.properties -rw-r -----. 1 oracle oinstall 2 Feb 25 17:27 checksBuffer.tmp -rw-r -----. 1 oracle oinstall 5633 Feb 25 17:27 preupgrade_fixups.sql -rw-r -----. 1 oracle oinstall 8618 Feb 25 17:27 postupgrade_fixups.sql -rw-r -----. 1 oracle oinstall 9275 Feb 25 17:27 upgrade.xml -rw-r -----. 1 oracle oinstall 2742 Feb 25 17:35 PreUpgradeResults.html -rw-r -----. 1 oracle oinstall 398 Feb 25 17:35 PreUpgrade.log -rw-r -----. 1 oracle oinstall 69 Feb 25 17:36 Migrate_Sid.log -rw -------. 1 oracle oinstall 530 Feb 25 17:37 catupgrd_catcon_9223.lst -rw-r -----. 1 oracle oinstall 0 Feb 25 17:58 catupgrd_datapatch_upgrade.err -rw-r -----. 1 oracle oinstall 1332 Feb 25 18:01 catupgrd_datapatch_upgrade.log -rw-r -----. 1 oracle oinstall 9412420 Feb 25 18:03 catupgrd1.log -rw-r -----. 1 oracle oinstall 6068047 Feb 25 18:03 catupgrd2.log -rw-r -----. 1 oracle oinstall 7724647 Feb 25 18:03 catupgrd3.log -rw-r -----. 1 oracle oinstall 38212 Feb 25 18:04 catupgrd_stderr.log -rw-r -----. 1 oracle oinstall 49789767 Feb 25 18:04 catupgrd0.log -rw-r -----. 1 oracle oinstall 10471 Feb 25 18:04 Oracle_Server.log -rw-r -----. 1 oracle oinstall 1732 Feb 25 18:04 upg_summary_CDB_Root.log -rw-r -----. 1 oracle oinstall 115 Feb 25 18:06 Utlprp.log -rw-r -----. 1 oracle oinstall 3037 Feb 25 18:09 UpgradeTimezone.log -rw-r -----. 1 oracle oinstall 672 Feb 25 18:10 PostUpgrade.log -rw-r -----. 1 oracle oinstall 5574 Feb 25 18:11 UpgradeResults.html -rw-r -----. 1 oracle oinstall 99149 Feb 25 18:11 sqls.log [oracle@DBsGuruN2 labdb03]$ cat upg_summary_CDB_Root.log Oracle Database Release 19 Post-Upgrade Status Tool 02-25-2022 18:03:3 Database Name : LABDB03 Component Current Full Elapsed Time Name Status Version HH:MM:SS Oracle Server UPGRADED 19.3.0.0.0 00:10:34 JServer JAVA Virtual Machine UPGRADED 19.3.0.0.0 00:01:35 Oracle XDK UPGRADED 19.3.0.0.0 00:00:33 Oracle Database Java Packages UPGRADED 19.3.0.0.0 00:00:06 OLAP Analytic Workspace UPGRADED 19.3.0.0.0 00:00:13 Oracle Label Security UPGRADED 19.3.0.0.0 00:00:07 Oracle Database Vault UPGRADED 19.3.0.0.0 00:00:15 Oracle Text UPGRADED 19.3.0.0.0 00:00:28 Oracle Workspace Manager UPGRADED 19.3.0.0.0 00:00:24 Oracle Real Application Clusters UPGRADED 19.3.0.0.0 00:00:00 Oracle XML Database UPGRADED 19.3.0.0.0 00:01:27 Oracle Multimedia UPGRADED 19.3.0.0.0 00:00:38 Spatial UPGRADED 19.3.0.0.0 00:03:46 Oracle OLAP API UPGRADED 19.3.0.0.0 00:00:09 Datapatch 00:02:56 Final Actions 00:03:08 Post Upgrade 00:00:25 Total Upgrade Time : 00:24:48 Database time zone version is 26. It is older than current release time zone version 32. Time zone upgrade is needed using the DBMS_DST package. Grand Total Upgrade Time : [0d:0h:26m:36s] |
3.2: Validate oratab: Validate in the file /etc/oratab, new ORACLE_HOME for 19c should be updated by dbua tool.
1
2
|
[oracle@DBsGuruN2 ~]$ cat /etc/oratab | grep -i labdb03 labdb03:/u01/app/oracle/product/1930/db_1:N |
3.3: Validate the Database: Set the environment in the new terminal and login to the database.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
[oracle@DBsGuruN2 ~]$ . oraenv ORACLE_SID = [labdb01] ? labdb03 The Oracle base remains unchanged with value /u01/app/oracle [oracle@DBsGuruN2 ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sun Feb 27 13:53:45 2022 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,STATUS,VERSION FROM V$ DATABASE , V$INSTANCE; NAME OPEN_MODE STATUS VERSION --------- -------------------- ------------ ----------------- LABDB03 READ WRITE OPEN 19.0.0.0.0 |
3.4: Execute postupgrade_fixups.sql: Execute postupgrade_fixups.sql which was generated in step 1.1.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
|
SQL> @/home/oracle/bkp/labdb03_upgrade/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 : 2022-02-25 17:25:15 For Source Database : LABDB03 Source Database Version: 12.2.0.1.0 For Upgrade to Version: 19.0.0.0.0 Preup Preupgrade Action Issue Is Number Preupgrade Check Name Remedied Further DBA Action ------ ------------------------ ---------- -------------------------------- 3. old_time_zones_exist YES None. 4. dir_symlinks YES None. 5. post_dictionary YES None. 6. post_fixed_objects 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 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 message from the preupgrade which explains in more detail what still needs to be done. PL/SQL procedure successfully completed. Session altered. |
3.5: Execute utlrp.sql and validate Objects Status: Execute utlrp.sql and validate invalid objects in the database, should not be any invalid objects after the upgrade. You may ignore invalid objects which are owned by non-default users subject to the status prior to the upgrade.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
|
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql Session altered. TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP UTLRP_BGN 2022-02-25 18:38:41 DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid DOC> objects in the database . Recompilation time is proportional to the DOC> number of invalid objects in the database , so this command may take DOC> a long time to execute on a database with a large number of invalid DOC> objects. DOC> DOC> Use the following queries to track recompilation progress: DOC> DOC> 1. Query returning the number of invalid objects remaining. This DOC> number should decrease with time . DOC> SELECT COUNT (*) FROM obj$ WHERE status IN (4, 5, 6); DOC> DOC> 2. Query returning the number of objects compiled so far. This number DOC> should increase with time . DOC> SELECT COUNT (*) FROM UTL_RECOMP_COMPILED; DOC> DOC> This script automatically chooses serial or parallel recompilation DOC> based on the number of CPUs available (parameter cpu_count) multiplied DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu). DOC> On RAC, this number is added across all RAC nodes. DOC> DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel DOC> recompilation. Jobs are created without instance affinity so that they DOC> can migrate across RAC nodes. Use the following queries to verify DOC> whether UTL_RECOMP jobs are being created and run correctly: DOC> DOC> 1. Query showing jobs created by UTL_RECOMP DOC> SELECT job_name FROM dba_scheduler_jobs DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%' ; DOC> DOC> 2. Query showing UTL_RECOMP jobs that are running DOC> SELECT job_name FROM dba_scheduler_running_jobs DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%' ; DOC># PL/SQL procedure successfully completed. TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP UTLRP_END 2022-02-25 18:38:42 DOC> The following query reports the number of invalid objects. DOC> DOC> If the number is higher than expected, please examine the error DOC> messages reported with each object (using SHOW ERRORS) to see if they DOC> point to system misconfiguration or resource constraints that must be DOC> fixed before attempting to recompile these objects. DOC># OBJECTS WITH ERRORS ------------------- 0 DOC> The following query reports the number of exceptions caught during DOC> recompilation. If this number is non-zero, please query the error DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors DOC> are due to misconfiguration or resource constraints that must be DOC> fixed before objects can compile successfully. DOC> Note: Typical compilation errors (due to coding errors) are not DOC> logged into this table : they go into DBA_ERRORS instead . DOC># ERRORS DURING RECOMPILATION --------------------------- 0 Function created. PL/SQL procedure successfully completed. Function dropped. PL/SQL procedure successfully completed. SQL> SELECT OWNER, COUNT (*) FROM DBA_OBJECTS WHERE STATUS <> 'VALID' GROUP BY OWNER; no rows selected |
3.6: Execute utlusts.sql post-upgrade validation tool: Execute utlusts.sql to validate upgrade status and review the output.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
SQL> @$ORACLE_HOME/rdbms/admin/utlusts.sql Oracle Database Release 19 Post-Upgrade Status Tool 02-25-2022 18:42:5 Database Name : LABDB03 Component Current Full Elapsed Time Name Status Version HH:MM:SS Oracle Server VALID 19.3.0.0.0 00:10:34 JServer JAVA Virtual Machine VALID 19.3.0.0.0 00:01:35 Oracle XDK VALID 19.3.0.0.0 00:00:33 Oracle Database Java Packages VALID 19.3.0.0.0 00:00:06 OLAP Analytic Workspace VALID 19.3.0.0.0 00:00:13 Oracle Label Security VALID 19.3.0.0.0 00:00:07 Oracle Database Vault VALID 19.3.0.0.0 00:00:15 Oracle Text VALID 19.3.0.0.0 00:00:28 Oracle Workspace Manager VALID 19.3.0.0.0 00:00:24 Oracle Real Application Clusters OPTION OFF 19.3.0.0.0 00:00:00 Oracle XML Database VALID 19.3.0.0.0 00:01:27 Oracle Multimedia VALID 19.3.0.0.0 00:00:38 Spatial VALID 19.3.0.0.0 00:03:46 Oracle OLAP API VALID 19.3.0.0.0 00:00:09 Datapatch 00:02:56 Final Actions 00:03:08 Post Upgrade 00:00:25 Post Compile 00:00:01 Total Upgrade Time : 00:24:49 Database time zone version is 32. It meets current release needs. |
NOTE: DST version should be must 32 and this is part of a successful upgrade.
3.7: Set the parameter Compatible: Change the COMPATIBLE parameter value to 19.0.0 to enable to use of all features of the upgraded version. This step is a very crucial step in terms of downgrading the database to the previous version or restoring guaranteed restore point. Make sure before the set a new value of COMPATIBLE parameter no major impact to DB in terms of performance, if possible test application for few days with an existing value of this parameter especially for the lower environment (DEV/TEST/UAT) upgrades so you have enough confidence to change it when you perform in PROD databases upgrade.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SQL> ALTER SYSTEM SET COMPATIBLE= '19.0.0' SCOPE=SPFILE; System altered. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 1241513488 bytes Fixed Size 8896016 bytes Variable Size 687865856 bytes Database Buffers 536870912 bytes Redo Buffers 7880704 bytes Database mounted. Database opened. SQL> SHOW PARAMETER COMPATIBLE; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ compatible string 19.0.0 |
3.8: Drop Guaranteed Restore Point: Drop restore point only after successful validation along with green signal by dependent applications/checkout.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL> COL NAME FOR A25 SQL> COL GUARANTEE_FLASHBACK_DATABASE FOR A31 SQL> SET LINES 333 SQL> SELECT NAME ,GUARANTEE_FLASHBACK_DATABASE, TIME FROM V$RESTORE_POINT; NAME GUARANTEE_FLASHBACK_DATABASE TIME ------------------------- ------------------------------- --------------------------------------------------------------------------- PRE_UPGRADE_LABDB03_DBUA YES 25-FEB-22 04.59.50.000000000 PM SQL> DROP RESTORE POINT PRE_UPGRADE_LABDB03_DBUA; Restore point dropped. SQL> SELECT NAME ,GUARANTEE_FLASHBACK_DATABASE, TIME FROM V$RESTORE_POINT; no rows selected |
3.9: RAC DB Action: Follow the below steps for the RAC database only.
3.9.1: Set cluster_database to TRUE.
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL> ALTER SYSTEM SET CLUSTER_DATABASE= TRUE SCOPE=SPFILE; System altered. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. [oracle@DBsGuruN2 admin]$ srvctl start database -db <DB NAME > SQL> SHOW PARAMETER CLUSTER_DATABASE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cluster_database boolean TRUE |
3.9.2: Upgrade the Oracle Clusterware keys for the database.
1
|
[oracle@DBsGuruN2 admin]$ srvctl upgrade database -db <DB NAME > -o <ORACLE_HOME> |
3.10: Listener Status: Validate the services for the database.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
[oracle@DBsGuruN2 labdb03]$ lsnrctl status LISTENER_19C LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 25-FEB-2022 18:48:23 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DBsGuruN2.labdomain)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER_19C Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 25-FEB-2022 17:32:03 Uptime 0 days 1 hr. 16 min . 19 sec Trace Level off Security ON : Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/1930/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/DBsGuruN2/listener_19c/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DBsGuruN2.labdomain)(PORT=1521))) Services Summary... Service "labdb03" has 1 instance(s). Instance "labdb03" , status READY, has 1 handler(s) for this service... Service "labdb03XDB" has 1 instance(s). Instance "labdb03" , status READY, has 1 handler(s) for this service... The command completed successfully [oracle@DBsGuruN2 labdb03]$ |
3.11: Password file and Pfile: Create a password file and pfile as required and this is a completely optional step.
3.11.1: Password File Creation.
1
2
3
4
|
[oracle@DBsGuruN2 ~]$ cd $ORACLE_HOME/dbs [oracle@DBsGuruN2 dbs]$ orapwd file=orapwlabdb03 password =XXXXXXXX@XXXX entries=10 force =y [oracle@DBsGuruN2 dbs]$ ls -lrt orapwlabdb03 -rw-r -----. 1 oracle oinstall 6144 Feb 27 15:21 orapwlabdb03 |
3.11.2: Pfile Creation.
1
2
3
4
5
6
|
SQL> create pfile from spfile; File created. SQL> !ls -lrt $ORACLE_HOME/dbs/initlabdb03.ora -rw-r -----. 1 oracle oinstall 1108 Feb 27 15:24 /u01/app/oracle/product/1930/db_1/dbs/initlabdb03.ora |
3.12: Others:
3.12.1: Enable cronjobs/scheduler jobs/Triggers if any.
3.12.2: Delete blackout database in OEM.
3.12.3: Start all dependent applications.
This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.
Hope so you like this article!