By Mouhamadou Diaw

Oracle 12.2 is released and we will have to upgrade. And in this blog we are going to talk about upgrading to oracle 12.2 using the dbupgrade utility.

Of course the dbua tool is still available and can be also used.

Oracle Database 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.

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.

In this article we will describe the steps for upgrade using dbupgrade.

The first step is to run the preupgrade information tool preupgrade.jar tool which replaced the preupgrd.sql and utluppkg.sql scripts. This tool can be run from operating system command line. Remember that in 12.1, the Pre-Upgrade Information Tool was run within SQL*Plus as a SQL file.

Following environment variables must be set. Indeed ORACLE_HOME must point to the earlier release.

[oracle@serveroracle ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
[oracle@serveroracle ~]$ export ORACLE_BASE=/u01/app/oracle
[oracle@serveroracle ~]$ export ORACLE_SID=ORCL
[oracle@serveroracle ~]$ 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@serveroracle:~[ORCL] $ORACLE_HOME/jdk/bin/java -jar /u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin/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: ORCL
Container ID: 0
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
...
...
...
Preupgrade generated files:
/u01/app/oracle/cfgtoollogs/ORCL/preupgrade/preupgrade_fixups.sql
/u01/app/oracle/cfgtoollogs/ORCL/preupgrade/postupgrade_fixups.sql
oracle@serveroracle:~[ORCL]

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@serveroracle ~]$ $ORACLE_HOME/jdk/bin/java -jar /u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin/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
[oracle@serveroracle ~]$

Once the execution done, the tool will identify possible issues during the upgrade and will also generate 2 scripts
 preupgrade_fixups.sql: fixes issues that an automated script can fix safely. This script must be run before the upgrade
 postupgrade_fixups.sql: fixes the upgrade issues that can be automatically fixed after the upgrade.
For all other issues we have to manually fix them.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> @preupgrade_fixups.sql

Once done we are ready to run the dbupgrade utility.

Prepare the environment

oracle@serveroracle:/home/oracle/ [rdbm12201] which dbupgrade
/u01/app/oracle/product/12.2.0.1/dbhome_1/bin/dbupgrade
oracle@serveroracle:/home/oracle/ [rdbm12201] echo $ORACLE_HOME
/u01/app/oracle/product/12.2.0.1/dbhome_1
oracle@serveroracle:/home/oracle/ [rdbm12201] export ORACLE_SID=ORCL

Copy the parameter file to the oracle 12.2 ORACLE_HOME/dbs

oracle@serveroracle:/home/oracle/ [ORCL] 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@serveroracle:/home/oracle/ [ORCL]

Start the database in the new environment in an upgrade mode

oracle@serveroracle:/home/oracle/ [ORCL] which sqlplus
/u01/app/oracle/product/12.2.0.1/dbhome_1/bin/sqlplus
oracle@serveroracle:/home/oracle/ [ORCL] sqlplus
SQL*Plus: Release 12.2.0.1.0 Production on Thu May 4 16:24:22 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 394264576 bytes
Fixed Size 8621136 bytes
Variable Size 293602224 bytes
Database Buffers 83886080 bytes
Redo Buffers 8155136 bytes
Database mounted.
Database opened.
SQL>

And then run the dbupgrade utility

oracle@serveroracle:/home/oracle/upgrade/ [ORCL] dbupgrade -n 2 -l /home/oracle/upgrade
Argument list for [/u01/app/oracle/product/12.2.0.1/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 = /home/oracle/upgrade
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]
...
...
...
LOG FILES: (/home/oracle/upgrade/catupgrd*.log)
Upgrade Summary Report Located in:
/home/oracle/upgrade/upg_summary.log
Grand Total Upgrade Time: [0d:1h:38m:21s]
oracle@serveroracle:/home/oracle/upgrade/ [ORCL]

Once the upgraded finished without errors, we can run the postupgrade_fixups.sql.

SQL*Plus: Release 12.2.0.1.0 Production on Thu May 4 23:17:10 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> @postupgrade_fixups.sql

Hope that this article will help