-- =================================================
-- Upgrade of Time Zone File and Flashback Database
-- =================================================

SQL>  select value$, con_id from containers(SYS.PROPS$) where NAME='DST_PRIMARY_TT_VERSION' order by 2;

   VALUE$    CON_ID
_________ _________
43                1
43                3
43                4

SQL> SELECT * FROM V$TIMEZONE_FILE;
          FILENAME    VERSION    CON_ID
__________________ __________ _________
timezlrg_43.dat            43         0

SQL> SELECT TZ_VERSION FROM REGISTRY$DATABASE;
   TZ_VERSION
_____________
           43
  
COLUMN property_name  FORMAT A30
COLUMN property_value FORMAT A20
SELECT property_name, property_value FROM   database_properties WHERE  property_name LIKE 'DST_%' ORDER BY property_name;

              PROPERTY_NAME    PROPERTY_VALUE
___________________________ _________________
DST_PRIMARY_TT_VERSION      43
DST_SECONDARY_TT_VERSION    0
DST_UPGRADE_STATE           NONE

SQL> SELECT DBMS_DST.get_latest_timezone_version FROM   dual;
   GET_LATEST_TIMEZONE_VERSION
______________________________
                            44

SQL> SELECT SUM(BYTES)/1024/1024/1024 "Total_size_w_TSTZ_columns_GB"
	FROM   DBA_SEGMENTS 
	WHERE  SEGMENT_TYPE LIKE 'TABLE%' 	
	AND    (OWNER, SEGMENT_NAME) IN
           (SELECT OWNER, TABLE_NAME FROM   DBA_TAB_COLUMNS WHERE  DATA_TYPE LIKE 'TIMESTAMP%TIME ZONE');

   Total_size_w_TSTZ_columns_GB
_______________________________
                0.0428466796875
 
SELECT OWNER, SEGMENT_NAME, SUM(BYTES)/1024/1024/1024 "SEGMENT_SIZE_W_TSTZ_COLUMNS_GB"
FROM   DBA_SEGMENTS
WHERE  SEGMENT_TYPE LIKE 'TABLE%'
AND    (OWNER, SEGMENT_NAME) IN
         (SELECT OWNER, TABLE_NAME  FROM   DBA_TAB_COLUMNS WHERE  DATA_TYPE LIKE 'TIMESTAMP%TIME ZONE')
GROUP BY OWNER, SEGMENT_NAME;

               OWNER                      SEGMENT_NAME    SEGMENT_SIZE_W_TSTZ_COLUMNS_GB
____________________ _________________________________ _________________________________
SYS                  OPTSTAT_USER_PREFS$                                0.00006103515625
...
GSMADMIN_INTERNAL    AQ$_CHANGE_LOG_QUEUE_TABLE_S                       0.00006103515625

93 rows selected.

-- =================================================
-- CREATE A RESTORE POINT
-- =================================================
CREATE RESTORE POINT BEFORE_TZ_UPG GUARANTEE FLASHBACK DATABASE;
Restore point created.

-- =================================================
-- OFFICIAL MODE
-- =================================================
-- Primary Note DST FAQ : Updated DST Transitions and New Time Zones in Oracle RDBMS and OJVM Time Zone File Patches (Doc ID 412160.1)
-- Download Latest DST released version: RDBMS DSTv4X Patch 99999999 and OJVM DSTv44 Patch 88888888

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=471713475558352&parent=DOCUMENT&sourceId=1665676.1&id=412160.1&_afrWindowMode=0&_adf.ctrl-state=16ngllmizd_230

-- =================================================
-- TIME ZONE UPGRADE
-- =================================================
--1. Check current settings:
SELECT * FROM v$timezone_file;

--2. Startup database in upgrade mode:
shutdown immediate;
startup upgrade;

--3. Start upgrade window:
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;
/

--4. Check primary and secondary time zone versions:
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME; 

--5. Startup database in normal mode:
SQL> shut immediate;
SQL> startup;

--6. Do the upgrade:
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;
/

-- =================================================
-- ALTERNATIVE
-- =================================================
--Create upgrade_tzf.sql

$ cat /oratmp/upgrade_tzf.sql

SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;

SET SERVEROUTPUT ON
DECLARE
  l_tz_version PLS_INTEGER;
BEGIN
  SELECT DBMS_DST.get_latest_timezone_version
  INTO   l_tz_version
  FROM   dual;

  DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);
  DBMS_DST.begin_upgrade(l_tz_version);
END;
/

SHUTDOWN IMMEDIATE;
STARTUP;

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;
/

-- Execute:
$ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin  $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -l /oratmp/ -b upgrade_tzf /oratmp/upgrade_tzf.sql

-- =================================================
-- VALIDATE VERSION
-- =================================================

SQL> SELECT * FROM v$timezone_file;

FILENAME                VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_44.dat              44          0
1 row selected.
Elapsed: 00:00:00.01

-- =================================================
-- REMOVE A RESTORE POINT
-- =================================================
DROP RESTORE POINT BEFORE_TZ_UPG;
Restore point dropped.

-- =================================================
-- HOW TO FALL BACK
-- =================================================
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT
SQL> FLASHBACK DATABASE TO RESTORE POINT BEFORE_TZ_UPG;
SQL> ALTER DATABASE OPEN RESETLOGS;
SQL> SELECT * FROM V$TIMEZONE_FILE;