• Arun Kumar

Oracle 12c to 19c Database Upgrade

In this article, we will be looking at two most commonly used methods of upgrading Oracle database from 12c to 19c.

You can perform a direct upgrade to 19c from 11.2.0.4, 12.1.0.2, 12.2.0.1 and 18c


Pre-Upgrade Tasks


Make sure you backup your database before performing the upgrade and also check application compatibility with the new version of the Oracle database.


Direct upgrade to 19c is possible for 11.2.0.4, 12.1.0.2, 12.2.0.1 and 18c versions. The upgrade steps are all same for the various supported versions.


Run the 19c pre-install package on Linux to complete all OS level pre-requisites

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


Upgrade using DBUA


Install 19cR3 Software on Linux host

unzip LINUX.X64_193000_db_home.zip

./runinstaller

Choose to Install the database software only option

oracle database 19c installer - setup software only
oracle database 19c installer - single instance database installation
oracle database 19c installer - enterprise edition
oracle database 19c installer - oracle base location
oracle database 19c installer - operating system groups
oracle database 19c installer - root script execution
oracle database 19c installer - perform prerequisite checks
oracle database 19c installer - summary
oracle database 19c installer - install product
oracle database 19c installer - finish

Check for Invalid objects

SQL> select count(*) from dba_objects where status='INVALID';

Run pre-upgrade script

/u01/app/oracle/product/12.2.0.1/db_home/jdk/bin/java -jar  /u                        01/app/oracle/product/19.3.0/db_home/rdbms/admin/preupgrade.jar FILE DIR /home/oracle/preupgrade
oracle 12c to 19c database upgrade - Run pre-upgrade script

View Pre upgrade log

cat /home/oracle/preupgrade/preupgrade.log 

Run Oracle Generated FIXUP SCRIPT

@/home/oracle/preupgrade/preupgrade_fixups.sql
oracle 12c to 19c database upgrade - preupgrade_fixups.sql

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;

oracle 12c to 19c database upgrade - verify tablespace sizes for upgrade

Gather DICTIONARY STATS

SET ECHO ON;
SET SERVEROUTPUT ON;
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

Purge Recycle bin

PURGE DBA_RECYCLEBIN;

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;
oracle 12c to 19c database upgrade - Flashback Guaranteed Restore Point

Run DBUA

cd /u01/app/oracle/product/19.3.0/db_home/bin/
./dbua
database upgrade assistant - dbua select database
dbua prerequisite checks
dbua - select upgrade options
dbua - use flashback and guaranteed restore point for recovery options
dbua - configure network for 19c
dbua  - configure management options for the database
dbua - database upgrade summary
oracle 12c to 19c database upgrade - oracle database upgrade progress
oracle 12c to 19c database upgrade - dbua upgrade results

Database upgrade has been completed successfully, and the database is ready for use!


Post Upgrade Tasks


Verify /etc/oratab and check if ORACLE_HOME location has changed to 19c home

cat /etc/oratab | grep -i prod
post upgrade tasks - verify 19c oracle home

Verify Timezone version

SQL> SELECT version FROM v$timezone_file;

Verify INVALID objects

SQL> select count(1) from dba_objects where status='INVALID';
oracle 12c to 19c database upgrade - verify timezone and invalid objects

Verify DBA_REGISTRY

col COMP_ID for a10
col COMP_NAME for a40
col VERSION for a15
set lines 180
set pages 999
select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;

Run postupgrade_fixups.sql, this script already ran by DBUA under the post-upgrade section. However, we have executed it again

SQL> @/home/oracle/preupgrade/postupgrade_fixups.sq
oracle 12c to 19c database upgrade - check dba_registry
oracle 12c to 19c database upgrade - oracle post upgrade verify dba_registory

Drop Restore point

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;

SQL> drop restore point PRE_UPGRADE;

SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;

Update COMPATIBLE parameter


If the value of the COMPATIBLE parameter is changed to 19.0.0 then if for some reasons database needs to be downgraded to 12.2.0.1 the DBA would not have any option other than export/import to downgrade the database. But if this parameter is left unchanged for some time to see how the database performs after upgrade then it is very easy and fast to downgrade the database if for some reason it is required to be downgraded.

Good practice - Change this parameter only after 1 month of database upgrade!
SQL> show parameter COMPATIBLE
SQL> ALTER SYSTEM SET COMPATIBLE = '19.0.0' SCOPE=SPFILE;
SQL> shut immediate;

SQL> startup;
SQL> show parameter COMPATIBLE
SQL> select name,open_mode,version from v$database,v$instance;
oracle 12c to 19c database upgrade - update compatible parameter


Upgrade using Manual Method


All the steps will be same until we fire DBUA in our previous upgrade method. Shutdown the database

SQL> SELECT NAME,OPEN_MODE FROM V$DATABASE;

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/

Startup DB in Upgrade mode from 19c home

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

sqlplus / as sysdba
SQL> startup upgrade;
oracle 12c to 19c database upgrade - startup db in upgrade mode from 19c home
select name,open_mode,cdb,version,status from v$database, v$instance;

Run dbupgrade

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 catctl.pl
oracle 12c to 19c database upgrade - run dbupgrade

Monitor upgrade log

cd /u01/app/oracle/product/19.3.0/db_home/bin

more nohup.out
oracle 12c to 19c database upgrade - monitor upgrade log
cd /u01/app/oracle/product/19.3.0/db_home/cfgtoollogs/prod/upgrade20210131020428/

ls -ltr *.log

tail -f catupgrd0.log
tail -f catupgrd1.log
tail -f catupgrd2.log
tail -f catupgrd3.log
oracle 12c to 19c database upgrade - check upgrade logs

Summary Report

oracle 12c to 19c database upgrade - oracle database upgrade summary report

Startup DB from 19c home

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

sqlplus / as sysdba
startup;

select name,open_mode,cdb,version,status from v$database, v$instance;

col COMP_ID for a10
col COMP_NAME for a40
col VERSION for a15
set lines 180
set pages 999
select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;
oracle 12c to 19c database upgrade - startup db from 19c home
oracle 12c to 19c database upgrade - startup db 19c home

Post Upgrade Steps


Run utlrp.sql, run catcon.pl to start utlrp.sql, and to recompile any remaining invalid objects

cd /u01/app/oracle/product/19.3.0/db_home/rdbms/admin/ nohup sqlplus "/ as sysdba" @utlrp.sql > /home/oracle/utlrp.out 2>&1 &


SQL> select count(*) from dba_objects where status='INVALID';

SQL> select count(*) from dba_objects where status='INVALID' and owner in ('SYS','SYSTEM');

SQL> @/u01/app/oracle/product/19.3.0/db_home/rdbms/admin/utlrp.sql
oracle 12c to 19c database upgrade - post upgrade steps utlrp.sql

Use the following queries to track recompilation progress. Query returning the number of invalid objects remaining. This number should decrease with time

SQL> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6)
oracle 12c to 19c database upgrade - track recompilation process in oracle
oracle 12c to 19c database upgrade - dba_objects
oracle 12c to 19c database upgrade - pl/sql procedure

Run postupgrade_fixups.sql

@/home/oracle/preupgrade/postupgrade_fixups.sql
oracle 12c to 19c database upgrade - run postupgrade_fixups.sql
oracle 12c to 19c database upgrade - executing oracle post-upgrade

Upgrade Timezone file

SQL> SELECT version FROM v$timezone_file;

SQL> @/u01/app/oracle/product/19.3.0/db_home/rdbms/admin/utltz_upg_check.sql
oracle 12c to 19c database upgrade - Upgrade timezone file v$timezone_file
SQL> @/u01/app/oracle/product/19.3.0/db_home/rdbms/admin/utltz_upg_apply.sql
oracle 12c to 19c database upgrade - utltz_upg_apply.sql
SQL> SELECT version FROM v$timezone_file;
oracle 12c to 19c database upgrade - v$timezone_file

Run utlusts.sql

SQL> @/u01/app/oracle/product/19.3.0/db_home/rdbms/admin/utlusts.sql TEXT
oracle 12c to 19c database upgrade - utlusts.sql

Run catuppst.sql

Do not run this in UPGRADE mode.
SQL> @/u01/app/oracle/product/19.3.0/db_home/rdbms/admin/catuppst.sql
oracle 12c to 19c database upgrade - run catuppst.sql

Re-Run postupgrade_fixups.sql

@/home/oracle/preupgrade/postupgrade_fixups.sql
oracle 12c to 19c database upgrade - oracle postupgrade_fixups.sql

Reverify INVALID OBJECTS

SQL> select count(*) from dba_objects where status='INVALID';
oracle 12c to 19c database upgrade - invalid objects

Drop Restore point

SQL> 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;

SQL> drop restore point PRE_UPGRADE;

SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;

Set COMPATIBLE parameter value to 19.0.0

SQL> show parameter COMPATIBLE

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

SQL> shut immediate;

SQL> startup;

SQL> show parameter COMPATIBLE
oracle 12c to 19c database upgrade - alter system set compatible

Verify DBA_REGISTRY

SQL> col COMP_ID for a10
col COMP_NAME for a40
col VERSION for a15
set lines 180
set pages 999
select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;
oracle 12c to 19c database upgrade - dba_registry

Edit oratab

cat /etc/oratab | grep -i prod

vi /etc/oratab
oracle 12c to 19c database upgrade - update /etc/oratab

Done!

Recent Posts

See All

Oracle Database Migration to AWS Cloud

Find different ways to migrate an Oracle database from on-premises to AWS cloud using different tools available for migration. You could choose any migration method based on your environment and needs

Reclaim Unused Space in Oracle

Over a period of time, updates and deletes on objects within a tablespace can create pockets of empty space that individually are not large enough to be reused for new data. This type of empty space i