12.2.0.1 to 19c - Oracle Database Upgradation

 0. Check Compatibility Before Upgrading Oracle Database

1. Environment

PRE-UPGRADE TASKS

2. Backup
3. Run preupgrade script
4. View Preupgrade log
5. Minimum tablespace sizes for upgrade
6. Update INITIALIZATION PARAMETERS
7. Gather DICTIONARY STATS
8. Purge Recyclebin
9. Refresh MVs
10. Run preupgrade_fixups.sql
11. Verify archive log dest size
12. Stop LISTENER
13. Create Flashback Guaranteed Restore Point

UPGRADE TASK

14. Shutdown Database
15. Copy init and password files from one version to new version 
16. Startup DB in Upgrade mode
17. Run dbupgrade
18. Starup DB from New Oracle Home

POST-UPGRADE TASKS 

19. Run catcon.pl to start utlrp.sql
20. Run postupgrade_fixups.sql
21. Upgrade Timezone
22. Run utlusts.sql
23. Run catuppst.sql
24. Re-Run postupgrade_fixups.sql
25. Reverify INVALID OBJECTS
26. Drop Restore point
27. Set COMPATIBALE parameter value
28. Verify DBA_REGISTRY
29. Add TNS Entries in 19c TNS home
30. Password File – orapwCID
31. Edit oratab
32. Back Up the Database

===================================================

/u01/app/oracle/product/12.2.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar TERMINAL TEXT
/u01/app/oracle/product/12.2.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar FILE DIR /home/oracle/CID/preupgrade
/home/oracle/CID/preupgrade/preupgrade.log
  /home/oracle/CID/preupgrade/preupgrade_fixups.sql
  /home/oracle/CID/preupgrade/postupgrade_fixups.sql

@/home/oracle/CID/preupgrade/preupgrade_fixups.sql

2) stop the listener and shutdown the database

3)  Set environment variables to NEW ORACLE_HOME(19.3)

SQL> startup UPGRADE

4) Start the DB Upgrade process:

nohup /u01/app/oracle/product/19.0.0/dbhome_1/perl/bin/perl catctl.pl -l /home/oracle/whileupgrade -n 4 catupgrd.sql &

[oracle@rac1 ~]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/bin/
[oracle@rac1 bin]$ ls -ltr dbupgrade
-rwxr-x---. 1 oracle oinstall 3136 Apr 17  2019 dbupgrade
[oracle@rac1 bin]$

[oracle@rac1 bin]$ nohup ./dbupgrade & <--- Hit ENTER twice

5) SQL> STARTUP

SQL> @utlu121s.sql

SQL> @catuppst.sql

SQL> @postupgrade_fixups.sql;

6) Upgrade Timezone

For releases (18c, 19c), the timezone upgrade scripts are

included in the target ORACLE_HOME under rdbms/admin directory

The following scripts get delivered with Oracle Database 18c onward

    $ORACLE_HOME/rdbms/admin/utltz_countstats.sql

    Script to gives how much TIMESTAMP WITH TIME ZONE data there is in a database using stats info. No restart required.

    

    $ORACLE_HOME/rdbms/admin/utltz_countstar.sql

    Script to approximate how much TIMESTAMP WITH TIME ZONE data there is in a database using a COUNT(*) for each table that has a TSTZ column. This script is useful when using DBMS_DST package or the scripts of utlz_upg_check.sql and utlz_upg_apply.sql scripts.

    

    $ORACLE_HOME/rdbms/admin/utltz_upg_check.sql

    Time zone upgrade check script

$cd $ORACLE_HOME/rdbms/admin/

utltz_upg_check.sql

utltz_upg_apply.sql

Run utlusts.sql

SQL> @/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/utlusts.sql TEXT