Skip to main content

Oracle 12c to 19c Database Upgrade Manual

By November 15, 2021No Comments

We will see how to upgrade the database to 19C Manually.

Note : You can perform a direct upgrade to 19c from,, and 18c.

Pre-Upgrade Tasks

yum install -y oracle-database-preinstall-19c

yum update -y

Install 19cR3 Software on Linux host



Complete with installation.

Check for Invalid objects

select count(*) from dba_objects where status=’INVALID’;

Run pre-upgrade script

/u01/app/oracle/product/ -jar /u 01/app/oracle/product/19.3.0/db_home/rdbms/admin/preupgrade.jar FILE DIR /home/oracle/preupgrade

View Pre upgrade log

cat /home/oracle/preupgrade/preupgrade.log

Run Oracle Generated FIXUP SCRIPT


All looks good in our case, lets move to verify tablespace sizes for upgrade

set line 1000 set pages 5000 col tablespace_name for a30 col file_name for a80 col free_space for 9999999 compute sum of total_space on report compute sum of free_space on report compute sum of MAX_SPACE on report break on tablespace_name on report nodup

select c.tablespace_name,a.autoextensible,a.file_name,a.total_space,b.free_space, round(b.free_space/a.total_space *100,2) “Free%”,a.max_space from (select file_id,file_name,sum(bytes)/1024/1024 total_space,sum(MAXBYTES)/1024/1024/1024 max_space,autoextensible from dba_data_files group by file_id,file_name,autoextensible) a,(select file_id,nvl(sum(bytes)/1024/1024,0) free_space from dba_free_space group by file_id) b, (select tablespace_name,file_id from dba_data_files) c where a.file_id=b.file_id(+) and a.file_id=c.file_id order by tablespace_name;



Purge Recycle bin


Refresh MVs

declare list_failures integer(3) :=0; begin DBMS_MVIEW.REFRESH_ALL_MVIEWS(list_failures,’C’,’’, TRUE, FALSE); end; /

Verify archive log dest size and Create Flashback Guaranteed Restore Point

archive log list alter system set db_recovery_file_dest_size=10G;

select flashback_on from v$database; select name,open_mode,log_mode from v$database; show parameter compatible show parameter recovery select * from V$restore_point;

create restore point pre_upgrade guarantee flashback database;

col name for a20 col GUARANTEE_FLASHBACK_DATABASE for a10 col TIME for a60 set lines 190 select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;

Shutdown the database

shut immediate

Copy init and password files from 12c to 19c dbs home

cd $ORACLE_HOME/dbs ls -ltr cp orapwprod spfileprod.ora /u01/app/oracle/product/19.3.0/db_home/dbs ls -ltr /u01/app/oracle/product/19.3.0/db_home/dbs/

Set Oracle 19C environment.

export ORACLE_HOME=/u01/app/oracle/product/19.3.0/db_home export ORACLE_SID=prod

Startup DB in Upgrade mode from 19c home

sqlplus / as sysdba startup upgrade;

Run DB upgrade

cd /u01/app/oracle/product/19.3.0/db_home/bin ls -ltr dbupgrade nohup ./dbupgrade & → Press enter 2 times jobs -l disown ps -ef | grep -i

Startup DB from 19c home

sqlplus / as sysdba startup;

select name,open_mode,cdb,version,status from v$database, v$instance; select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;

Post Upgrade Steps

Checked invalid objects and compile using “utlrp.sql”

select count(*) from dba_objects where status=’INVALID’; @/u01/app/oracle/product/19.3.0/db_home/rdbms/admin/utlrp.sql

Run postupgrade_fixups.sql


Upgrade Timezone file check and upgrade

@/u01/app/oracle/product/19.3.0/db_home/rdbms/admin/utltz_upg_check.sql @/u01/app/oracle/product/19.3.0/db_home/rdbms/admin/utltz_upg_apply.sql

SELECT version FROM v$timezone_file;

Run utlusts.sql


Run catuppst.sql


Re-Run postupgrade_fixups.sql


Set COMPATIBLE parameter value to 19.0.0

show parameter COMPATIBLE ALTER SYSTEM SET COMPATIBLE = ‘19.0.0’ SCOPE=SPFILE; shut immediate; startup; show parameter COMPATIBLE

Checked database registry

select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;