Oracle 12c to 19c Database Upgrade
Upgrade Oracle databases from 12c to 19c step-by-step.
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
Backup your database before performing the upgrade and also check application compatibility with the new version of the Oracle database.
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
Connect to the database and verify tablespace sizes for upgrade. Make sure to have good free space in system and sysaux tablespaces
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;
Check for Invalid objects and fix it using utlrp script
SQL> select count(*) from dba_objects where status='INVALID';
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;
Install Oracle 19c
Create 19c home directory and give ownership to Oracle user
mkdir -p /u01/app/oracle/product/19.3/db_home
Download and Install Oracle 19c Software
cd /u01/app/oracle/product/19.3/db_home
unzip -qo /tmp/LINUX.X64_193000_db_home.zip
#for silent installation
./runInstaller -ignorePrereq -waitforcompletion -silent \
-responseFile ${ORACLE_HOME}/install/response/db_install.rsp \
oracle.install.option=INSTALL_DB_SWONLY \
ORACLE_HOSTNAME=${HOSTNAME} \
UNIX_GROUP_NAME=oinstall \
INVENTORY_LOCATION=/u01/app/oraInventory \
SELECTED_LANGUAGES=en,en_GB \
ORACLE_HOME=/u01/app/oracle/product/19.3/db_home \
ORACLE_BASE=/u01/app/oracle \
oracle.install.db.InstallEdition=EE \
oracle.install.db.OSDBA_GROUP=dba \
oracle.install.db.OSBACKUPDBA_GROUP=dba \
oracle.install.db.OSDGDBA_GROUP=dba \
oracle.install.db.OSKMDBA_GROUP=dba \
oracle.install.db.OSRACDBA_GROUP=dba \
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false \
DECLINE_SECURITY_UPDATES=true
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
View Pre upgrade log
cat /home/oracle/preupgrade/preupgrade.log
Run Oracle Generated FIXUP SCRIPT
@/home/oracle/preupgrade/preupgrade_fixups.sql
Upgrade using DBUA
Invoke Database Upgrade Assistant DBUA from 19c home
cd /u01/app/oracle/product/19.3.0/db_home/bin/
./dbua
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
Verify Timezone version
SQL> SELECT version FROM v$timezone_file;
Verify INVALID objects
SQL> select count(1) from dba_objects where status='INVALID';
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
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;
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;
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
Monitor upgrade log
cd /u01/app/oracle/product/19.3.0/db_home/bin
more nohup.out
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
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;
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
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)
Run postupgrade_fixups.sql
@/home/oracle/preupgrade/postupgrade_fixups.sql
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
SQL> @/u01/app/oracle/product/19.3.0/db_home/rdbms/admin/utltz_upg_apply.sql
SQL> SELECT version FROM v$timezone_file;
Run utlusts.sql
SQL> @/u01/app/oracle/product/19.3.0/db_home/rdbms/admin/utlusts.sql TEXT
Run catuppst.sql
Do not run this in UPGRADE mode.
SQL> @/u01/app/oracle/product/19.3.0/db_home/rdbms/admin/catuppst.sql
Re-Run postupgrade_fixups.sql
@/home/oracle/preupgrade/postupgrade_fixups.sql
Reverify INVALID OBJECTS
SQL> select count(*) from dba_objects where status='INVALID';
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
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;
Edit oratab
cat /etc/oratab | grep -i prod
vi /etc/oratab
Done!