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.
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.
- Take Full backup
- Gather dictionary stats
- Purge Recycle bin
- Check Timezone
- Run Preupgrade
- Shutdown 12c Database and Open 18c instance in upgrade mode
- Upgrade database
- Run Post Upgrade steps
- Compile Invalid Objects.
- 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.
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