Hi,

I will continue to explain Oracle 18c RAC Database Upgrade From 12c (12.1.0.2) to 18c (18.7.0.0.0) on Full Rack Exadata in this article.

 

Oracle Upgrade from 12c to 18c

Read first article before this article.

Oracle 18c RAC Database Upgrade From 12c (12.1.0.2) to 18c (18.7.0.0.0) on Full Rack Exadata -1 29757256 Patch Apply

I will use Manual Upgrade method while upgrading Oracle 18c.

 

 

Oracle 18c Database Upgrade From 12c to 18c

 

If you don’t know Oracle database Upgrade methods and how to upgrade Oracle database, you should read following article.

Oracle Database Upgrade Guide and How to Upgrade Oracle Database

 

 

Oracle 18c Manual Upgrade Method

Manual upgrade steps should be as follows.

1. Take Full backup
2. Gather dictionary stats
3. Purge Recycle bin
4. Check Timezone
5. Run Preupgrade
6. Shutdown 12c Database and Open 18c instance in upgrade mode
7. Upgrade database
8. Run Post Upgrade steps
9. Compile Invalid Objects.
10. Start all Oracle 18c instances and check

 

Steps -1 :  Take a Full backup via RMAN Tool. If you don’t know RMAN Backup, read following article.

 

Full Backup in Online and Offline Mode via RMAN | Oracle RMAN ( Recovery Manager ) Backup Tutorials -3

 

 

 

Steps -2: Gather dictionary stats like following.

 

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.

 

 

Steps -3: Purge recycle bin.

 

SQL> PURGE DBA_RECYCLEBIN ;

DBA Recyclebin purged.

 

 

Steps -4: Check Timezone before upgrade.

 

SQL> SELECT version FROM v$timezone_file;

VERSION
----------
18



SQL> 
SQL> 
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
2 FROM DATABASE_PROPERTIES
3 WHERE PROPERTY_NAME LIKE 'DST_%'
4 ORDER BY PROPERTY_NAME;

PROPERTY_NAME
--------------------------------------------------------------------------------------------------------------------------------
VALUE
------------------------------------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION
18

DST_SECONDARY_TT_VERSION
14

DST_UPGRADE_STATE
DATAPUMP(9)

 

 

Step -5: Run Preupgrade to check database if it is suitable for upgrade or not.

 

Preupgrade.jar is available under new 18c Oracle Home. You can run it like following.

[DEVECI1]/home/oracle $ 
[DEVECI1]/home/oracle $ echo $ORACLE_HOME
/u01/app/oracle/product/12.1.0.2/dbhome_1
[DEVECI1]/home/oracle $ 
[DEVECI1]/home/oracle $ mkdir -p /u01/app18c/preupgrade/
[DEVECI1]/home/oracle $ 
[DEVECI1]/home/oracle $ /u01/app/oracle/product/12.1.0.2/dbhome_1/jdk/bin/java -jar /u01/app18c/rdbms/admin/preupgrade.jar FILE DIR /u01/app18c/preupgrade/


==================
PREUPGRADE SUMMARY
==================
/u01/app18c/preupgrade/preupgrade.log
/u01/app18c/preupgrade/preupgrade_fixups.sql
/u01/app18c/preupgrade/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade log into the database and execute the preupgrade fixups
@/u01/app18c/preupgrade/preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups
@/u01/app18c/preupgrade/postupgrade_fixups.sql

Preupgrade complete: 2019-08-02T22:18:54
[DEVECI1]/home/oracle $ 
[DEVECI1]/home/oracle $

 

 

 

You should run preupgrade_fixups.sql after preupgrade.jar.

[DEVECI1]/home/oracle $ 
[DEVECI1]/home/oracle $ 
[DEVECI1]/home/oracle $ 
[DEVECI1]/home/oracle $ $ORACLE_HOME/bin/sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Aug 2 22:19:39 2019

Copyright (c) 1982, 2014, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> 
SQL> @/u01/app18c/preupgrade/preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by: Oracle Preupgrade Script
Version: 18.0.0.0.0 Build: 6
Generated on: 2019-08-02 22:18:41

For Source Database: DEVECI
Source Database Version: 12.1.0.2.0
For Upgrade to Version: 18.0.0.0.0

Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
1. pending_dst_session NO Manual fixup required.
2. parameter_obsolete NO Manual fixup recommended.
3. invalid_objects_exist NO Manual fixup recommended.
4. apex_manual_upgrade NO Manual fixup recommended.
5. trgowner_no_admndbtrg NO Informational only.
Further action is optional.
6. tablespaces_info NO Informational only.
Further action is optional.
7. parameter_deprecated 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> 
SQL> 

 

Step -6: Set cluster_database parameter to False, then shutdown all Instances via srvctl command.

Then open Oracle 18c instance in upgrade mode.

 

SQL> alter system set cluster_database=false scope=spfile sid='*';

System altered.

[DEVECI1]  $ srvctl stop database -d DEVECI



[DEVECI1]/u01/app18c/dbs $ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Sat Aug 3 02:32:35 2019
Version 18.7.0.0.0

Copyright (c) 1982, 2018, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup upgrade
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 1.0573E+11 bytes
Fixed Size 29906232 bytes
Variable Size 4.6976E+10 bytes
Database Buffers 5.8519E+10 bytes
Redo Buffers 207720448 bytes
Database mounted.
Database opened.
SQL> 
SQL> 
SQL> exit
Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.7.0.0.0
[DEVECI1]/u01/app18c/dbs $

 

 

 

 

Upgrade Oracle 12c to 18c

Step -7:  After open database in upgrade mode, run catctl.pl and catupgrd.sql to upgrade database.

 

[DEVECI1]/u01/app18c/rdbms/admin $ 
[DEVECI1]/u01/app18c/rdbms/admin $ nohup $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catctl.pl -d $ORACLE_HOME/rdbms/admin -l /home/oracle/ -n 96 catupgrd.sql &
[DEVECI1]/u01/app18c/rdbms/admin $ cat nohup.out

Argument list for [catctl.pl]
For Oracle internal use only A = 0
Run in c = 0
Do not run in C = 0
Input Directory d = 0
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = 0
Child Process I = 0
Log Dir l = /u01/app18c/diagnostics
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 48
SQL PDB Process Count N = 0
Open Mode Normal o = 0
Start Phase p = 0
End Phase P = 0
Reverse Order r = 0
AutoUpgrade Resume R = 0
Script s = 0
Serial Run S = 0
RO User Tablespaces T = 0
Display Phases y = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0

catctl.pl VERSION: [18.0.0.0.0]
STATUS: [Production]
BUILD: [RDBMS_18.7.0.0.0DBRU_LINUX.X64_190624]


/u01/app18c/rdbms/admin/orahome = [/u01/app18c]
/u01/app18c/bin/orabasehome = [/u01/app18c]
catctlGetOraBaseLogDir = [/u01/app18c]

Analyzing file /u01/app18c/rdbms/admin/catupgrd.sql

Log file directory = [/u01/app18c/diagnostics]

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app18c/diagnostics/catupgrd_catcon_365231.lst]

catcon::set_log_file_base_path: catcon: See [/u01/app18c/diagnostics/catupgrd*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/u01/app18c/diagnostics/catupgrd_*.lst] files for spool files, if any


Number of Cpus = 96
Database Name = DEVECI
DataBase Version = 12.1.0.2.0
Parallel SQL Process Count = 8
Components in [DEVECI]
Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT JAVAVM ORDIM OWM RAC SDO XDB XML XOQ]
Not Installed [DV EM MGW ODM OLS WK]

------------------------------------------------------
Phases [0-108] Start Time:[2019_08_03 02:33:21]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [DEVECI] Files:1 Time: 39s
*************** Catalog Core SQL ***************
Serial Phase #:1 [DEVECI] Files:5 Time: 21s
Restart Phase #:2 [DEVECI] Files:1 Time: 0s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [DEVECI] Files:19 Time: 4s
Restart Phase #:4 [DEVECI] Files:1 Time: 0s
************* Catalog Final Scripts ************
Serial Phase #:5 [DEVECI] Files:7 Time: 8s
***************** Catproc Start ****************
Serial Phase #:6 [DEVECI] Files:1 Time: 8s
***************** Catproc Types ****************
Serial Phase #:7 [DEVECI] Files:2 Time: 7s
Restart Phase #:8 [DEVECI] Files:1 Time: 0s
**************** Catproc Tables ****************
Parallel Phase #:9 [DEVECI] Files:66 Time: 6s
Restart Phase #:10 [DEVECI] Files:1 Time: 0s
************* Catproc Package Specs ************
Serial Phase #:11 [DEVECI] Files:1 Time: 40s
Restart Phase #:12 [DEVECI] Files:1 Time: 0s
************** Catproc Procedures **************
Parallel Phase #:13 [DEVECI] Files:94 Time: 3s
Restart Phase #:14 [DEVECI] Files:1 Time: 0s
Parallel Phase #:15 [DEVECI] Files:118 Time: 6s
Restart Phase #:16 [DEVECI] Files:1 Time: 0s
Serial Phase #:17 [DEVECI] Files:17 Time: 1s
Restart Phase #:18 [DEVECI] Files:1 Time: 0s
***************** Catproc Views ****************
Parallel Phase #:19 [DEVECI] Files:32 Time: 6s
Restart Phase #:20 [DEVECI] Files:1 Time: 0s
Serial Phase #:21 [DEVECI] Files:3 Time: 5s
Restart Phase #:22 [DEVECI] Files:1 Time: 0s
Parallel Phase #:23 [DEVECI] Files:24 Time: 71s
Restart Phase #:24 [DEVECI] Files:1 Time: 0s
Parallel Phase #:25 [DEVECI] Files:12 Time: 47s
Restart Phase #:26 [DEVECI] Files:1 Time: 0s
Serial Phase #:27 [DEVECI] Files:1 Time: 0s
Serial Phase #:28 [DEVECI] Files:3 Time: 2s
Serial Phase #:29 [DEVECI] Files:1 Time: 0s
Restart Phase #:30 [DEVECI] Files:1 Time: 0s
*************** Catproc CDB Views **************
Serial Phase #:31 [DEVECI] Files:1 Time: 0s
Restart Phase #:32 [DEVECI] Files:1 Time: 1s
Serial Phase #:34 [DEVECI] Files:1 Time: 0s
***************** Catproc PLBs *****************
Serial Phase #:35 [DEVECI] Files:289 Time: 13s
Serial Phase #:36 [DEVECI] Files:1 Time: 0s
Restart Phase #:37 [DEVECI] Files:1 Time: 0s
Serial Phase #:38 [DEVECI] Files:2 Time: 4s
Restart Phase #:39 [DEVECI] Files:1 Time: 0s
*************** Catproc DataPump ***************
Serial Phase #:40 [DEVECI] Files:3 Time: 36s
Restart Phase #:41 [DEVECI] Files:1 Time: 0s
****************** Catproc SQL *****************
Parallel Phase #:42 [DEVECI] Files:13 Time: 45s
Restart Phase #:43 [DEVECI] Files:1 Time: 1s
Parallel Phase #:44 [DEVECI] Files:11 Time: 3s
Restart Phase #:45 [DEVECI] Files:1 Time: 1s
Parallel Phase #:46 [DEVECI] Files:3 Time: 0s
Restart Phase #:47 [DEVECI] Files:1 Time: 0s
************* Final Catproc scripts ************
Serial Phase #:48 [DEVECI] Files:1 Time: 3s
Restart Phase #:49 [DEVECI] Files:1 Time: 0s
************** Final RDBMS scripts *************
Serial Phase #:50 [DEVECI] Files:1 Time: 20s
************ Upgrade Component Start ***********
Serial Phase #:51 [DEVECI] Files:1 Time: 0s
Restart Phase #:52 [DEVECI] Files:1 Time: 0s
********** Upgrading Java and non-Java *********
Serial Phase #:53 [DEVECI] Files:2 Time: 307s
***************** Upgrading XDB ****************
Restart Phase #:54 [DEVECI] Files:1 Time: 0s
Serial Phase #:56 [DEVECI] Files:3 Time: 6s
Serial Phase #:57 [DEVECI] Files:3 Time: 3s
Parallel Phase #:58 [DEVECI] Files:9 Time: 1s
Parallel Phase #:59 [DEVECI] Files:25 Time: 3s
Serial Phase #:60 [DEVECI] Files:4 Time: 4s
Serial Phase #:61 [DEVECI] Files:1 Time: 0s
Serial Phase #:62 [DEVECI] Files:31 Time: 1s
Serial Phase #:63 [DEVECI] Files:1 Time: 0s
Parallel Phase #:64 [DEVECI] Files:6 Time: 1s
Serial Phase #:65 [DEVECI] Files:2 Time: 11s
Serial Phase #:66 [DEVECI] Files:3 Time: 23s
**************** Upgrading ORDIM ***************
Restart Phase #:67 [DEVECI] Files:1 Time: 0s
Serial Phase #:69 [DEVECI] Files:1 Time: 1s
Parallel Phase #:70 [DEVECI] Files:2 Time: 18s
Serial Phase #:71 [DEVECI] Files:1 Time: 30s
Restart Phase #:72 [DEVECI] Files:1 Time: 0s
Parallel Phase #:73 [DEVECI] Files:2 Time: 5s
Serial Phase #:74 [DEVECI] Files:2 Time: 1s
***************** Upgrading SDO ****************
Restart Phase #:75 [DEVECI] Files:1 Time: 0s
Serial Phase #:77 [DEVECI] Files:1 Time: 20s
Serial Phase #:78 [DEVECI] Files:1 Time: 0s
Restart Phase #:79 [DEVECI] Files:1 Time: 1s
Serial Phase #:80 [DEVECI] Files:1 Time: 12s
Restart Phase #:81 [DEVECI] Files:1 Time: 0s
Parallel Phase #:82 [DEVECI] Files:3 Time: 20s
Restart Phase #:83 [DEVECI] Files:1 Time: 0s
Serial Phase #:84 [DEVECI] Files:1 Time: 2s
Restart Phase #:85 [DEVECI] Files:1 Time: 0s
Serial Phase #:86 [DEVECI] Files:1 Time: 5s
Restart Phase #:87 [DEVECI] Files:1 Time: 0s
Parallel Phase #:88 [DEVECI] Files:4 Time: 25s
Restart Phase #:89 [DEVECI] Files:1 Time: 0s
Serial Phase #:90 [DEVECI] Files:1 Time: 0s
Restart Phase #:91 [DEVECI] Files:1 Time: 0s
Serial Phase #:92 [DEVECI] Files:2 Time: 4s
Restart Phase #:93 [DEVECI] Files:1 Time: 0s
Serial Phase #:94 [DEVECI] Files:1 Time: 0s
Restart Phase #:95 [DEVECI] Files:1 Time: 1s
******* Upgrading ODM, WK, EXF, RUL, XOQ *******
Serial Phase #:96 [DEVECI] Files:1 Time: 10s
Restart Phase #:97 [DEVECI] Files:1 Time: 0s
*********** Final Component scripts ***********
Serial Phase #:98 [DEVECI] Files:1 Time: 1s
************* Final Upgrade scripts ************
Serial Phase #:99 [DEVECI] Files:1 Time: 240s
******************* Migration ******************
Serial Phase #:100 [DEVECI] Files:1 Time: 49s
*** End PDB Application Upgrade Pre-Shutdown ***
Serial Phase #:101 [DEVECI] Files:1 Time: 0s
Serial Phase #:102 [DEVECI] Files:1 Time: 0s
Serial Phase #:103 [DEVECI] Files:1 Time: 383s
***************** Post Upgrade *****************
Serial Phase #:104 [DEVECI] Files:1 Time: 3s
**************** Summary report ****************
Serial Phase #:105 [DEVECI] Files:1 Time: 0s
*** End PDB Application Upgrade Post-Shutdown **
Serial Phase #:106 [DEVECI] Files:1 Time: 0s
Serial Phase #:107 [DEVECI] Files:1 Time: 0s
Serial Phase #:108 [DEVECI] Files:1 Time: 40s

------------------------------------------------------
Phases [0-108] End Time:[2019_08_03 03:00:33]
------------------------------------------------------

Grand Total Time: 1653s

*** WARNING: ERRORS FOUND DURING UPGRADE ***

1. Evaluate the errors found in the upgrade logs
and determine the proper action.
2. Rerun the upgrade when the problem is resolved

REASON:

LOG FILES: (/u01/app18c/diagnostics/catupgrd*.log)

Upgrade Summary Report Located in:
/u01/app18c/diagnostics/upg_summary.log

Grand Total Upgrade Time: [0d:0h:27m:33s]
[DEVECI1]/u01/app18c/rdbms/admin $

 

 

 

 

Step -8: run @utlu122s.sql sql for Post upgrade task like following.

 

[DEVECI1]/u01/app18c/dbs $ cd $ORACLE_HOME/rdbms/admin
[DEVECI1]/u01/app18c/rdbms/admin $ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Sat Aug 3 02:30:18 2019
Version 18.7.0.0.0

Copyright (c) 1982, 2018, Oracle. All rights reserved.

Connected to an idle instance.

SQL> @utlu122s.sql

 

 

Step -9:  Run utlrp.sql to compile all invalid objects.

 

[DEVECI1]/u01/app18c/rdbms/admin $ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Sat Aug 3 02:30:18 2019
Version 18.7.0.0.0

Copyright (c) 1982, 2018, Oracle. All rights reserved.

Connected to an idle instance.

SQL> @utlrp.sql

 

 

 

Step -10: Start all Oracle 18c instances and check.

Set cluster_database to true again for all instances startup.

 

SQL> alter system set cluster_database=true scope=spfile sid='*';

System altered.

SQL>

 

After set cluster_database true, then you can startup all Oracle 18c instances.

 

[DEVECI1]/u01/app18c/dbs $ srvctl upgrade database -d DEVECI -o /u01/app18c

[DEVECI1]/u01/app18c/dbs $ srvctl start database -d DEVECI

Database upgrade is completed now.

 

 

 

 

Do you want to learn Oracle Database Performance Tuning detailed, then read the following articles.

Performance Tuning and SQL Tuning Tutorial in the Oracle Database