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 upgrade 11g to 19c manually

Source Environment

AboutSource Details
OS DetailsRedhat Linux 7.6 (64 bit)
Oracle Database version11.2.0.4
Database NameOCP
ORACLE_HOME path/u01/app/oracle/product/11.2.0.4/dbhome_1

Target Environment

AboutTarget Details
OS DetailsRedhat Linux 7.6 (64 bit)
Oracle Database version19.2.0.0
Database NameOCP
ORACLE_HOME path/u01/app/oracle/product/19.2.0.0/dbhome_1

Listed below versions that support direct upgrade

These 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 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 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

Pre-upgrade script check your database is ready to upgrade or not, it is available in new oracle home extension is “.jar“.

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 like:

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

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

Executing preupgrade_fixups.sql

The preupgrade_fixups.sql check and fix if there is any issue related to upgrade.

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 available in bin folder, so using cd command goes on bin location.

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

It will take a few minutes in execution, wait till finished. Once dbupgrade finished now a startup database 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 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

1 thought on “How to upgrade 11g to 19c manually?”

Leave a Comment

%d bloggers like this:
Enable Notifications    OK No thanks