upgrade 11g to 19c

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

You Can subscribe my YouTube channel

You also read to like How to Install Oracle 18c on Windows?

Share this
Share
Share