Upgrade Oracle Database from 12.1.0.2 to 12.2.0.1 using DBUA
5 min readSupported Upgrade Paths
Direct upgrades are possible from existing databases with the following versions.
Destination Source
=========== ======
12c Release 1 (12.1.0.2) 10.2.0.5+, 11.1.0.7+, 11.2.0.2+ or 12.1.0.1
12c Release 2 (12.2.0.1) 11.2.0.3+ or 12.1.0.1 or 12.1.0.2
- Check the existing version:
[oracle@sha-infotech ~]$ cat /etc/oratab
cdb1:/u01/app/oracle/product/12.1.0/dbhome_1:N
[oracle@sha-infotech ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Nov 29 15:50:34 2018
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, OLAP, Advanced Analytics and Real Application Testing options
SQL> select * from v$version;
BANNER CON_ID
---------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
CORE 12.1.0.2.0 Production 0
TNS for Linux: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0
SQL> select name from v$database;
NAME
------
CDB1
- Gather dictionary statistics.
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
3. PRE-UPGRADE STEPS:
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. Run the preupgrade tool The Pre-Upgrade Information Tool produces 3 scripts. preupgrade.log : The results of all the checks performed. You need to check this to see if it is safe to continue with the upgrade. preupgrade_fixups.sql : A fixup script that should be run before the upgrade. postupgrade_fixups.sql : A fixup script that should be run after the upgrade.
[oracle@sha-infotech ~]$ OLD_ORACLE_HOME/jdk/bin/java -jar $NEW_ORACLE_HOME/rdbms/admin/preupgrade.jar
[oracle@sha-infotech ~]$ $ORACLE_HOME/jdk/bin/java -jar /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/preupgrade.jar
Preupgrade generated files: /u01/app/oracle/cfgtoollogs/cdb1/preupgrade/preupgrade.log /u01/app/oracle/cfgtoollogs/ cdb1/preupgrade/preupgrade_fixups.sql /u01/app/oracle/cfgtoollogs/ cdb1/preupgrade/postupgrade_fixups.sql
[oracle@sha-infotech ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Nov 29 15:50:34 2018
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, OLAP, Advanced Analytics and Real Application Testing options
SQL>@/u01/app/oracle/cfgtoollogs/ cdb1/preupgrade/preupgrade_fixups.sql
- Backup Full offline
Make sure to take a full backup before you begin the upgrade steps, in case of upgrade failure.
5. Set environment variables to NEW ORACLE_HOME(12.2)
[oracle@sha-infotech ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
[oracle@sha-infotech ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@sha-infotech ~]$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib
6. Run DBUA
[oracle@sha-infotech ~]$ cd $ORACLE_HOME/bin
[oracle@sha-infotech bin]$ ./dbua
7. Manual Upgrades
The way you run the JAR file depends on what you are trying to achieve, as described in the upgrade
documentation for your version. The following example does the pre-upgrade checks for the whole
database, including all PDBs if you are using a container database.
[oracle@sha-infotech ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
[oracle@sha-infotech ~]$ export ORACLE_BASE=/u01/app/oracle
[oracle@sha-infotech ~]$ export ORACLE_SID=cdb1
[oracle@sha-infotech ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@sha-infotech ~]$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 8 -l /home/oracle catupgrd.log
PRE-UPGRADE STEPS:
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 SQLPlus as a SQL file. Run the preupgrade tool The Pre-Upgrade Information Tool produces 3 scripts.
preupgrade.log : The results of all the checks performed. You need to check this to see if it is safe to continue with the upgrade. preupgrade_fixups.sql : A fixup script that should be run before the upgrade. postupgrade_fixups.sql : A fixup script that should be run after the upgrade.
[oracle@sha-infotech ~]$ OLD_ORACLE_HOME/jdk/bin/java -jar $NEW_ORACLE_HOME/rdbms/admin/preupgrade.jar
[oracle@sha-infotech ~]$ $ORACLE_HOME/jdk/bin/java -jar /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/preupgrade.jar
Preupgrade generated files:
/u01/app/oracle/cfgtoollogs/cdb1/preupgrade/preupgrade.log /u01/app/oracle/cfgtoollogs/ cdb1/preupgrade/preupgrade_fixups.sql /u01/app/oracle/cfgtoollogs/ cdb1/preupgrade/postupgrade_fixups.sql
[oracle@sha-infotech ~]$ sqlplus / as sysdba
SQLPlus: Release 12.1.0.2.0 Production on Thu Nov 29 15:50:34 2018
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, OLAP, Advanced Analytics and Real Application Testing options
SQL> @/u01/app/oracle/cfgtoollogs/ cdb1/preupgrade/preupgrade_fixups.sql
Set Environment variables to NEW ORACLE_HOME(12.2)
[oracle@sha-infotech ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
[oracle@sha-infotech ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@sha-infotech ~]$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib
Copy the parameter and password files from the old home to the oracle 12.2
ORACLE_HOME/dbs
[oracle@sha-infotech ~]$ cp /u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfilecdb1.ora /u01/app/oracle/product/12.2.0/dbhome_1/dbs/
[oracle@sha-infotech ~]$ cp /u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwcdb1 /u01/app/oracle/product/12.2.0/dbhome_1/dbs/
If the “preupgrade.log” file contains references to deprecated initialization parameters, make the
suggested changes to the parameters now before continuing.
Edit the “/etc/oratab” file, setting the new ORACLE_HOME value.
cdb1:/u01/app/oracle/product/12.2.0/dbhome_1:Y Make sure the environment is set to the 12c home.
Start the database in the new environment in an upgrade mode.
[oracle@sha-infotech bin]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production onThu Nov 29 15:50:34 2018
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 bytess
Database mounted.
Database opened.
SQL> EXIT;
Run the new Parallel Upgrade Utility (catctl.pl).
[oracle@sha-infotech bin]$ cd $ORACLE_HOME/rdbms/admin
[oracle@sha-infotech bin]$ $ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql
You can alter the level of parallelism using the “-n” parameter.
[oracle@sha-infotech bin]$ cd $ORACLE_HOME/rdbms/admin
[oracle@sha-infotech bin]$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 8 catupgrd.sql
You can see an example of the output from this utility here.
If we had no errors, the “catuppst.sql” script would have been run as part of the upgrade. Since we did have errors, we need to run it manually.
SQL> @catuppst.sql
If the "postupgrade_fixups.sql" file contained any recommendations, run it now.
SQL> @/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql
The following items are not essential, but the upgrade manual suggests it is a best practice to run them.
The following item is probably included in your postupgrade_fixups.sql script.
SQL>EXECUTE DBMS_STATS.gather_fixed_objects_stats;
Recompile invalid objects.
SQL>@utlrp.sql
Check for newly invalid objects.
SQL>@utluiobj.sql
Run again to check the final outcome of the upgrade.
Startup the upgraded database and run the “utlu121s.sql” script to check the summary of the upgrade
results.
[oracle@sha-infotech bin]$ sqlplus / as sysdba
SQL> STARTUP
SQL> @utlu121s.sql
