-- 
-- the actual commands are listed in BOLD
-- the next steps use <the new DST version number> in the statements
-- simply replace it with the actual number ( 11, 15 etc)
-- of the RDBMS DST version you want to update to.

conn / as sysdba

-- 
-- If there are objects containing TSTZ data in recycle bin, 
-- please purge the bin now. 

purge dba_recyclebin;

--
-- Set client_info so one can use: 
-- select .... from V$SESSION where CLIENT_INFO = 'upg_tzv';

EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('upg_tzv')

-- 
-- this alter session might speed up DBMS_DST on some db's
-- see Bug 10209691 / Bug 12658443

alter session set "_with_subquery"=materialize;

-- 
-- to avoid the issue in note 1407273.1

alter session set "_simple_view_merging"=TRUE;

-- 
-- start prepare window
-- these steps will NOT update any data yet.

exec DBMS_DST.BEGIN_PREPARE(18)

--
-- Sample DBMS_DST.BEGIN_PREPARE error if the 12.1.0.x DST patch for the requested DST version is not installed:
-- 
-- SQL> exec DBMS_DST.BEGIN_PREPARE(19)
-- BEGIN DBMS_DST.BEGIN_PREPARE(19); END;
-- 
-- *
-- ERROR at line 1:
-- ORA-30094: failed to find the time zone data file for version 19 in
-- $ORACLE_HOME/oracore/zoneinfo
-- ORA-06512: at "SYS.DBMS_DST", line 57
-- ORA-06512: at "SYS.DBMS_DST", line 1258
-- ORA-06512: at line 1
-- 
-- FIX: install the 12.1.0.x patch for the DST version you want to use. See note 412160.1
--
-- Sample DBMS_DST.BEGIN_PREPARE error if the requested new DST version is the current or a lower than the current timezone version:
--
-- SQL> exec DBMS_DST.BEGIN_PREPARE(4);
-- BEGIN DBMS_DST.BEGIN_PREPARE(4); END;
-- 
-- *
-- ERROR at line 1:
-- ORA-56921: invalid time zone version
-- ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
-- ORA-06512: at "SYS.DBMS_DST", line 1252
-- ORA-06512: at line 1
--
-- FIX: you cannot "downgrade" DST, there is no need to do this. The new DST version needs to be higher than the current DST_PRIMARY_TT_VERSION
--

-- 
-- check for prepare status

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

-- 
-- output should be
-- PROPERTY_NAME VALUE
-- ------------------------------ ------------------------------
-- DST_PRIMARY_TT_VERSION <the old DST version number>
-- DST_SECONDARY_TT_VERSION <the new DST version number>
-- DST_UPGRADE_STATE PREPARE
-- 

-- 
-- truncate logging tables if they exist.

TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;

-- 
-- log affected data

set serveroutput on
BEGIN
DBMS_DST.FIND_AFFECTED_TABLES
(affected_tables => 'sys.dst$affected_tables',
log_errors => TRUE,
log_errors_table => 'sys.dst$error_table');
END;
/

--
-- If DBMS_DST.FIND_AFFECTED_TABLES failes with
-- ERROR at line 1:
-- ORA-01882: timezone region not found
-- ORA-06512: at "SYS.DBMS_DST", line 284
-- ORA-06512: at "SYS.DBMS_DST", line 1511
-- ORA-06512: at line 2
-- then first of all run the Fix1882.sql script found in Note 414590.1 using the server home sqlplus and then retry DBMS_DST.FIND_AFFECTED_TABLES
--

--
-- Now check what tables have affected data in TSTZ columns.
-- If dst$affected_tables has no rows then there is no actual data to update by DBMS_DST
-- If dst$affected_tables has rows it simply means those rows need 
-- to be updated by DBM_DST during the DST upgrade (= point 4)
-- because they contain timezones that are affected by the DST upgrade
-- 

SELECT * FROM sys.dst$affected_tables;

-- 
-- If dst$affected_tables has rows then you can see in dst$error_table
-- if there are any rows with a "problem" and what kind of problem there are in those rows.
-- Note that if there are rows in dst$affected_tables 
-- this does not mean there need to be rows in dst$error_table
-- 

SELECT * FROM sys.dst$error_table;

-- 
-- error_on_overlap_time is error number ORA-1883
-- error_on_nonexisting_time is error number ORA-1878
-- 
-- for an explanation of the reported data please see
-- "Error Handling when Upgrading Time Zone File and Timestamp with Time Zone Data"
-- For the "error_on_overlap_time" and "error_on_nonexisting_time" you do not HAVE to
-- take action on this data to upgrade the DST version, but it is advised
-- to at least to check the results AFTER the update.
--

-- 
-- all "error_on_overlap_time" rows

SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1883';

-- 
-- all "error_on_nonexisting_time" rows

SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1878';

-- 
-- check for all other possible problems

SELECT * FROM sys.dst$error_table where ERROR_NUMBER not in ('1878','1883');

--
-- 1882 errors will be resolved by DBMS_DST if the cause is the issue explained in Note 414590.1 
-- those should be corrected during the actual update of the dst version. It is however possible some 
-- other reasons may cause 1882 but in that case  DBMS_DST.FIND_AFFECTED_TABLES would have also errored 
-- out with ora-1882.
--

-- 
-- end prepare window, the rows above will stay in those tables.

EXEC DBMS_DST.END_PREPARE;

-- 
-- check if this is ended

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

-- 
-- output should be
-- PROPERTY_NAME VALUE
-- ---------------------------- ------------------------------
-- DST_PRIMARY_TT_VERSION <the old DST version number>
-- DST_SECONDARY_TT_VERSION 0
-- DST_UPGRADE_STATE NONE

