Upgrade the Oracle database from 12c to 19c with DBUA Utility

PreUpgrade Steps:

1. Connect with database and check the version of Oracle SE or EE.

Select * from V$version;

2. Check the database component is in valid state.

col comp_id for a10
col version for a10
col status for a7
col comp_name for a50
select comp_id,comp_name,version,status from dba_registry;

3. Check all CDB and PDB database in open state.

select name, open_mode,cdb,i.con_id, version from V$database,v$instance i;
select CON_ID, NAME, OPEN_MODE from V$PDBS;

4. Check invalid object and compile them if needed.

select count(*) from  cdb_objects where status = 'INVALID';

--Run UTLRP for compile all objects:
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql

5. Gather the dictionary stats

exec dbms_stats.gather_dictionary_stats;

6. Purge recycle bin

purge dba_recyclebin;

7. Run the Preupgrade utility for all cdb and pdbs in one command. PDB must be in open state.

<newOracleHome>/jdk/bin/java -jar <newOracleHome>/rdbms/admin/preupgrade.jar 

8. Check time zone it will upgrade by DBUA utility.

SELECT version FROM v$timezone_file;
 
-- check also
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

9. Check the hidden parameters in parameter file spfile and remove them if present.

alter session set container=<PDB_Name>;
SELECT name, value from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\' order by name;
Or 
Create pfile from spfile or remove hidden parameters
CREATE PFILE=<location> FROM SPFILE;

10. Check local listener parameter set then remove it.

Show parameter local

Upgrade Process

Start upgrade process with DBUA utility.

1. Open the command shell and set the new Oracle home (i.e 19c in my case):

SET ORACLE_HOME=D:\Software\WINDOWS.X64_193000_db_home
SET PATH=%ORACLE_HOME%/bin;%PATH%

2. Start the DBUA Utility from command shell.

$dbua

3. It will open the DBUA utility:

4. Select the Dataase which need to upgrade and provide the sys username and password.

5. Choose pluggable database.

6. Check the prerequisite checks for Oracle upgradation.

7. Warning occurred in prerequisite so we press YES for continue.

8. Select upgrade options, Select all Parallel, timezone and post recompilation

9. Choose the recovery option, if you did not take manually backup. Backup is must before starting the Upgrade process. If it hang then all database will be lost. So must choose or manually take full backup of the database before start. Example, I have my own cold backup so i choose last option

10. Choose Configure Network as listener.

11. Configure Management or Enterprise Manager EM tool.

12. Summary

13. Database upgrade progress in Oracle

14. Upgrade is completed successfully. 19c database is ready to use.

POST UPGRADE STEPS:

1. Check the database and pdb is open state.

Startup

alter pluggable database all open;

2. Check the invalid objects if present run utlrp.sql

--Run the utlrp script for compile all invalid objects.

cd $ORACLE_HOME/rdbms/admin
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql

3. Verify the timezone is upgraded.