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
12.2.0.1.0
Upgrade-To version: 12.2.0.1.0

=======================================
Status of the database prior to upgrade
=======================================

Database Name: ORCL
Container Name: CDB$ROOT
Container ID: 1
Version: 12.1.0.2.0
Compatible: 12.1.0.2.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 12.1.0.2.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 12.1.0.2.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: 12.2.0.1.0 Build: 1
Generated on: 2017-08-10 18:08:00

For Source Database: ORCL
Source Database Version: 12.1.0.2.0
For Upgrade to Version: 12.2.0.1.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/12.2.0.1/dbhome_1/bin/dbupgrade

[oracle@DATA-Redaction ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/12.2.0.1/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/12.2.0.1/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 12.2.0.1.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: [12.2.0.1.0]
STATUS: [production]
BUILD: [RDBMS_12.2.0.1.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 = 12.2.0.1.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

Comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.