How to fix the pending_dst_session error when running the preupgrade script

If you receive an error during the precheck-phase of an upgrade from 18c to 19c, which points to "pending_dst_sessions", like this:

you can follow Doc ID 1509653.1 "Updating the RDBMS DST version in 12c Release 1 (12.1.0.1 ) and above using DBMS_DST"

In short, execute the following:

SELECT version FROM v$timezone_file;

In my case the result was

   VERSION
----------
        32

Then, check the following:

col property_name format a40
col value format a40
set lines 200
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

Result:

PROPERTY_NAME                            VALUE
---------------------------------------- --------------------
DST_PRIMARY_TT_VERSION                   32
DST_SECONDARY_TT_VERSION                 31
DST_UPGRADE_STATE                        UPGRADE

Then, create a file called "dst.sql" and add the following:

col property_name format a40
col value format a40
set lines 200
alter session set "_with_subquery"=materialize;
alter session set "_simple_view_merging"=TRUE;
set serveroutput on
VAR numfail number
BEGIN
DBMS_DST.UPGRADE_DATABASE(:numfail,
parallel => TRUE,
log_errors => TRUE,
log_errors_table => 'SYS.DST$ERROR_TABLE',
log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
error_on_overlap_time => FALSE,
error_on_nonexisting_time => FALSE);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/
VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
exit

Execute the script:

oracle@myserver.mydomain.com:[testdb01]# sqlplus / as sysdba @dst.sql

Output:

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.14.0.0.0


Session altered.


Session altered.

Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "DVSYS"."SIMULATION_LOG$"
Number of failures: 0
Table list: "DVSYS"."AUDIT_TRAIL$"
Number of failures: 0
Failures:0

PL/SQL procedure successfully completed.

An upgrade window has been successfully ended.
Failures:0

PL/SQL procedure successfully completed.


PROPERTY_NAME                            VALUE
---------------------------------------- --------------------
DST_PRIMARY_TT_VERSION                   32
DST_SECONDARY_TT_VERSION                 0
DST_UPGRADE_STATE                        NONE

SQL> exit

The error should now be fixed and you can try to run the prechecks for the upgrade once more.