12.1 to 18c - Oracle database upgradation (Doc ID 2418045.1)

 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

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

Run Preupgrade to check database if it is suitable for upgrade or not.
Preupgrade.jar is available under new 18c Oracle Home. 

You can run it like following.

[DEVECI1]/home/oracle $ /u01/app/oracle/product/12.1.0.2/dbhome_1/jdk/bin/java -jar /u01/app18c/rdbms/admin/preupgrade.jar FILE DIR /u01/app18c/preupgrade/
Preupgrade generated files:
/u01/app18c/preupgrade/preupgrade.log
/u01/app18c/preupgrade/preupgrade_fixups.sql
/u01/app18c/preupgrade/postupgrade_fixups.sql
Log into the database and execute the preupgrade_fixups.sql

@/u01/app18c/preupgrade/preupgrade_fixups.sql

2) stop the listener and shutdown the database

3)  Set environment variables to NEW ORACLE_HOME(18.3)

SQL> startup UPGRADE

4) Start the DB Upgrade process:

$] cd $ORACLE_HOME/bin

./dbupgrade  -n 2 -l /home/oracle/12-2_upgrade/logs

-->  DB will down

5) SQL> STARTUP

SQL> @utlu121s.sql

SQL> @Catuppst.sql

SQL> @utlrp.sql

SQL> @postupgrade_fixups.sql;

I have version 18.  So I will go for the DST upgrade as well. For the DST upgrade, 

I have followed the Oracle MOS note Doc ID 1585343.1.

Check current version:

SQL> SELECT version FROM v$timezone_file;

VERSION

———-

26

Check the database properties related to DST:

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

$

$ sqlplus / as sysdba

Connected to an idle instance.

SQL>

SQL>

SQL> startup upgrade;

ORACLE instance started.

SQL>

SQL> SET SERVEROUTPUT ON

DECLARE

l_tz_version PLS_INTEGER;

BEGIN

l_tz_version := DBMS_DST.get_latest_timezone_version;

DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);

DBMS_DST.begin_upgrade(l_tz_version);

END;

/

 

l_tz_version=31

An upgrade window has been successfully started.

PL/SQL procedure successfully completed.

SQL>

SQL> SHUTDOWN IMMEDIATE;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL>

SQL> exit

$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Sep 6 15:06:51 2019

Version 18.4.0.0.0

Connected to an idle instance.

SQL> STARTUP;

ORACLE instance started.

SQL> SET SERVEROUTPUT ON

DECLARE

l_failures PLS_INTEGER;

BEGIN

DBMS_DST.upgrade_database(l_failures);

DBMS_OUTPUT.put_line('DBMS_DST.upgrade_database : l_failures=' || l_failures);

DBMS_DST.end_upgrade(l_failures);

DBMS_OUTPUT.put_line('DBMS_DST.end_upgrade : l_failures=' || l_failures);

END;

/SQL>   2    3    4    5    6    7    8    9

Table list: """"GSMADMIN_INTERNAL"""".""""AQ$_CHANGE_LOG_QUEUE_TABLE_S""""

Number of failures: 0

Table list: """"GSMADMIN_INTERNAL"""".""""AQ$_CHANGE_LOG_QUEUE_TABLE_L""""

Number of failures: 0

DBMS_DST.upgrade_database : l_failures=0

An upgrade window has been successfully ended.

DBMS_DST.end_upgrade : l_failures=0

PL/SQL procedure successfully completed.

SQL> SELECT * FROM v$timezone_file;

FILENAME                VERSION     CON_ID

-------------------- ---------- ----------

timezlrg_31.dat              31          0

SQL> select TZ_VERSION from registry$database;

TZ_VERSION

----------

        18

SQL> update registry$database set TZ_VERSION = (select version FROM v$timezone_file);

1 row updated.

SQL> select TZ_VERSION from registry$database;

TZ_VERSION

----------

        31"