How to upgrade 11g to 19c manually?

In this article, we are going to discuss how to upgrade Oracle 11g (11.2.0.4) to 19c (19.2.0.0) manually. Follow me step by step carefully.

Start to upgrade 11g to 19c manually

Source Environment

About	Source Details
OS Details	Redhat Linux 7.6 (64 bit)
Oracle Database version	11.2.0.4
Database Name	OCP
ORACLE_HOME path	/u01/app/oracle/product/11.2.0.4/dbhome_1

Target Environment

About	Target Details
OS Details	Redhat Linux 7.6 (64 bit)
Oracle Database version	19.2.0.0
Database Name	OCP
ORACLE_HOME path	/u01/app/oracle/product/19.2.0.0/dbhome_1

Listed below are versions that support a direct upgrade

These are all versions we can upgrade directly.

• 11.2.0.4
• 12.1.0.1
• 12.1.0.2
• 12.2.0.1

Important points before the upgrade

Before the upgrade database, you must remember these points.

1. Take full database backup using RMAN.
2. The recycle bin must be empty.
3. Gather dictionary stats.
4. Run pre-upgrade.
5. Run pre-upgrade fixups.

Timezone version

The timezone version must be upgraded for an easy upgrade.

SQL> SELECT version FROM v$timezone_file; 

OR

 SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES 
           WHERE PROPERTY_NAME LIKE 'DST_%' 
           ORDER BY PROPERTY_NAME; 

Start to upgrade 11g to 19c

1. Take full database backup using RMAN

Using the following query we can take a full database and controlfile backup using RMAN.

$> rman target/

RMAN>run {
             backup format 'C:\Users\sysadmin\Desktop\dbtest\%d_DB_%T_%u' database;
             backup current controlfile format 'C:\Users\sysadmin\Desktop\dbtest\%d_C_%T_%u';
            }

Make sure no backup is running during database up-gradation.

SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';

 no rows selected

2. The recycle bin must be empty

You must check the recycle bin must be empty if not, it will create an error during up-gradation.

SQL> purge dba_recyclebin;

 DBA Recyclebin purged.

3. Gather dictionary stats

Gather stats to make fast up-gradation.

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

 PL/SQL procedure successfully completed.

4. Run Pre upgrade

The pre-upgrade script checks whether your database is ready to upgrade or not, it is available in the new oracle home extension is “.jar“.

The pre-upgrade file location is ” /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar“

$ /u01/app/oracle/product/11.2.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar FILE DIR /u01/preupgrade/ 

The above command will create three files:

1. preupgrade.log
2. preupgrade_fixups.sql
3. postupgrade_fixups.sql

After completing the “preupgrade.jar” script Now run “preupgrade_fixups.sql” and then run “postupgrade_fixups.sql“.

Executing preupgrade_fixups.sql

The preupgrade_fixups.sql check and fixes if there is any issue related to upgrading.

SQL>@/u01/preupgrade/preupgrade_fixups.sql 

Oracle Database upgrade from 11g to 19c

Shutdown the database and copy the parameter and password file from 11g home to 19c home.

SQL> shu immediate
Database closed. 
Database dismounted. 
ORACLE instance shut down. 

Copy parameter and password file now.

$ cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileocp.ora /u01/app/oracle/product/19.0.0/dbhome_1 

$ cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwocp.ora /u01/app/oracle/product/19.0.0/dbhome_1 

Startup Database in Upgrade mode

Export new oracle_home for oracle 19c and start the database in upgrade mode.

$ export ORACLE_SID=OCP
$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1 
$ export PATH=/u01/app/oracle/product/19.0.0/dbhome_1:$PATH 
$ sqlplus / as sysdba 

SQL> startup upgrade

dbupgrade utility

The dbupgrade utility is available in the bin folder, so using the cd command goes to the bin location.

$export ORACLE_SID=ocp
$cd  /u01/app/oracle/product/19.0.0/dbhome_1/bin
$./dbupgrade

It will take a few minutes to execute, wait till finished. Once dbupgrade is finished now a startup database is normally.

SQL>startup

SQL> @catuppst.sql 

Compile all invalid objects

During upgradation some objects got invalid, so we need to compile it.

SQL>?@utlrp.sql

Update compatible parameters

SQL>show parameter compatible;
 NAME                        TYPE    VALUE 
----------------------- -------- --------- 
compatible                 string  11.2.0.4.0 
noncdb_compatible   boolean FALSE 


 SQL> ALTER SYSTEM SET COMPATIBLE = '19.0.0' SCOPE=SPFILE; 

Take restart of your database and compile fixed objects stats.

SQL> show parameter compatible;  
NAME                      TYPE     VALUE 
--------------------- -------- --------- 
compatible               string   19.0.0 
noncdb_compatible boolean  FALSE 

Compile fixed objects stats

SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

PL/SQL procedure successfully completed.

Post upgrade steps

Starting to upgrade the time zone version.

SQL>startup upgrade

Run postupgrade fixups.

SQL> @/u01/preupgrade/postupgrade_fixups.sql

Congratulations your database has been upgraded. Check database name and version using the following query.

 SQL> select name,version,open_mode from v$database,v$instance; 

Now Oracle 11g to 19c up-gradation has been done successfully.

Read - How to Change password without showing the actual password on screen?

Connect with me on:

Instagram: https://www.instagram.com/shripaldba
Linkedin: https://www.linkedin.com/in/shripal-singh
Twitter: https://twitter.com/ocptechnology
Facebook: https://www.facebook.com/ocptechnology
YouTube:https://www.youtube.com/ocptechnology