Oracle Database 12c release 1 (12.1) introduced the Parallel Upgrade Utility, catctl.pl. This utility reduces the total amount of time it takes to perform an upgrade by loading the database dictionary in parallel, and by using multiple SQL processes to upgrade the database. Performing parallel upgrades of components enables you to take advantage of your CPU capacity. Oracle continues to make improvements to the upgrade process to simplify both manual upgrades, and upgrades performed with the Database Upgrade Assistant (DBUA). DBUA and the manual upgrade procedures take advantage of the new Parallel Upgrade Utility.
You can run a shell command, dbupgrade, which starts up catctl.pl from the command line, instead of requiring you to run it from Perl.
The dbupgrade shell command is located in the file path $ORACLE_HOME/rdbms/bin on Linux and UNIX, and %ORACLE_HOME%\rdbms\bin on Windows. You can provide any command arguments that are valid for catctl.pl to the shell command. Either run the command directly from the new Oracle home path, or set a user environment variable to point to the file path.
In Oracle 12.1 catcpl.pl had to be run using perl like
$ORACLE_HOME/perl/bin/perl catctl.pl -n 8 -l /home/oracle catupgrd.log
Starting with Oracle 12.2, we can use dbupgrade utility which starts up catctl.pl instead to run it from perl.
Step 1: Setting up the environment variable and run necessary jar files
[oracle@MYCOMP ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1 [oracle@MYCOMP ~]$ export ORACLE_BASE=/u01/app/oracle [oracle@MYCOMP ~]$ export ORACLE_SID=ORCL [oracle@MYCOMP ~]$ export PATH=.:$ORACLE_HOME/bin:$PATH
The preupgrade.jar file is located to the oracle 12.2 $ORACLE_HOME/rdbms/admin. We can execute it using the TERMINAL or FILE options. The TERMINAL option will send output in the terminal and with FILE we will have the output in a file.
[oracle@DATA-Redaction test]$ $ORACLE_HOME/jdk/bin/java -jar /home/oracle/test/preupgrade.jar TERMINAL TEXT Report generated by Oracle Database Pre-Upgrade Information Tool Version 126.96.36.199.0 Upgrade-To version: 188.8.131.52.0 ======================================= Status of the database prior to upgrade ======================================= Database Name: ORCL Container Name: CDB$ROOT Container ID: 1 Version: 184.108.40.206.0 Compatible: 220.127.116.11.0 Blocksize: 8192 Platform: Linux x86 64-bit Timezone File: 18 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 Real Application Clusters [to be upgraded] OPTION OFF Oracle Workspace Manager [to be upgraded] VALID OLAP Analytic Workspace [to be upgraded] VALID Oracle Label Security [to be upgraded] VALID Oracle Database Vault [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 Oracle OLAP API [to be upgraded] VALID ......... Preupgrade generated files: /u01/app/oracle/cfgtoollogs/ORCL/preupgrade/preupgrade_fixups.sql /u01/app/oracle/cfgtoollogs/ORCL/preupgrade/postupgrade_fixups.sql
As specified if we want the output in a file, we can use the option FILE instead of TERMINAL. The output directory is $ORACLE_BASE/cfgtoollogs/$ORACLE_SID
[oracle@DATA-Redaction test]$ $ORACLE_HOME/jdk/bin/java -jar /home/oracle/test/preupgrade.jar FILE TEXT Preupgrade generated files: /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
Step 2: Run preupgrade_fixups.sql on Oracle 12.1
[oracle@DATA-Redaction test]$ sqlplus / as sysdba SQL*Plus: Release 18.104.22.168.0 Production on Thu Aug 10 18:08:45 2017 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 22.214.171.124.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics and Real Application Testing options SQL> @/u01/app/oracle/cfgtoollogs/ORCL/preupgrade/preupgrade_fixups.sql Executing Oracle PRE-Upgrade Fixup Script Auto-Generated by: Oracle Preupgrade Script Version: 126.96.36.199.0 Build: 1 Generated on: 2017-08-10 18:08:00 For Source Database: ORCL Source Database Version: 188.8.131.52.0 For Upgrade to Version: 184.108.40.206.0 Executing in container: CDB$ROOT Fixup Check Name Status Further DBA Action ---------- ------ ------ ----------------- min_recovery_area_size Passed None dictionary_stats Passed None PL/SQL procedure successfully completed. Session altered.
Step 3: Prepare the environment variable for Oracle 12.2 and copy spfile in new home
[oracle@DATA-Redaction ~]$ which dbupgrade /u01/app/oracle/product/220.127.116.11/dbhome_1/bin/dbupgrade [oracle@DATA-Redaction ~]$ echo $ORACLE_HOME /u01/app/oracle/product/18.104.22.168/dbhome_1 [oracle@DATA-Redaction ~]$ export ORACLE_SID=ORCL [oracle@DATA-Redaction ~]$ cp /u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfileORCL.ora /u01/app/oracle/product/22.214.171.124/dbhome_1/dbs/ [oracle@DATA-Redaction ~]$
Step 4: Start the database in the new environment in an upgrade mode
[oracle@DATA-Redaction ~]$ sqlplus / as sysdba SQL*Plus: Release 126.96.36.199.0 Production on Thu Aug 10 18:15:08 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to an idle instance. SQL>startup upgrade ORACLE instance started. Total System Global Area 2768240640 bytes Fixed Size 8796624 bytes Variable Size 704644656 bytes Database Buffers 1979711488 bytes Redo Buffers 75087872 bytes Database mounted. Database opened. SQL>
Step 5: Run the dbupgrade utility
Here n stands for number of processes and l stands for log file location
[oracle@DBtest bin]$ dbupgrade -n 2 -l /u01/app/ Argument list for [/u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/catctl.pl] 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/app/ Priority List Name L = 0 Upgrade Mode active M = 0 SQL Process Count n = 2 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: [188.8.131.52.0] STATUS: [production] BUILD: [RDBMS_184.108.40.206.0_LINUX.X64_170125] /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/orahome = [/u01/app/oracle/product/12.2.0/dbhome_1] /u01/app/oracle/product/12.2.0/dbhome_1/bin/orabasehome = [/u01/app/oracle/product/12.2.0/dbhome_1] catctlGetOrabase = [/u01/app/oracle/product/12.2.0/dbhome_1] Analyzing file /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/catupgrd.sql Log file directory = [/u01/app/] catcon: ALL catcon-related output will be written to [/u01/app//catupgrd_catcon_26467.lst] catcon: See [/u01/app//catupgrd*.log] files for output generated by scripts catcon: See [/u01/app//catupgrd_*.lst] files for spool files, if any Number of Cpus = 2 Database Name = ORCL DataBase Version = 220.127.116.11.0 Parallel SQL Process Count (PDB) = 2 Parallel SQL Process Count (CDB$ROOT) = 2 Concurrent PDB Upgrades = 1 PDB$SEED Open Mode = [READ ONLY] NO UPGRADE WILL BE PERFORMED PDB1 Open Mode = [READ WRITE] NO UPGRADE WILL BE PERFORMED Generated PDB Inclusion:[NONE] Components in [CDB$ROOT] Installed [APS CATALOG CATJAVA CATPROC CONTEXT DV JAVAVM OLS ORDIM OWM SDO XDB XML XOQ] Not Installed [APEX EM MGW ODM RAC WK] ------------------------------------------------------ Phases [0-115] Start Time:[2017_08_10 18:13:10] Container Lists Inclusion:[CDB$ROOT] Exclusion:[NONE] ------------------------------------------------------ ..... ..... ..... LOG FILES: (/u01/app/catupgrd*.log) Upgrade Summary Report Located in: /u01/app/upg_summary.log Grand Total Upgrade Time: [0d:1h:18m:05s]
Step 6: Run the postupgrade_fixups.sql
SQL> @/u01/app/oracle/cfgtoollogs/ORCL/preupgrade/postupgrade_fixups.sql Session altered. PL/SQL procedure successfully completed. Session altered. PL/SQL procedure successfully completed. Session altered.
Cheers!!! Upgrade has completed