This note is protected by password and is a Read Only.

Oracle 11.2.0.2 DST Upgrade

  1. check current RDBMS DST version and "DST UPGRADE STATUS".
    SELECT PROPERTY_NAME||' = '||SUBSTR(property_value, 1, 30) value
    FROM DATABASE_PROPERTIES
    WHERE PROPERTY_NAME LIKE 'DST_%'
    ORDER BY PROPERTY_NAME;

    The DST_PRIMARY_TT_VERSION result should match the value from this query:
    SELECT version FROM v$timezone_file;

  2. Check UPFRONT if there is affected data that cannot be resolved automatically.
    Note that this can be done on a working, live database.
    conn / as sysdba

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

    exec DBMS_DST.BEGIN_PREPARE(14)

    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;

    The output should be:
    PROPERTY_NAME VALUE
    ------------------------------ ------------------------------
    DST_PRIMARY_TT_VERSION <the old DST version number>
    DST_SECONDARY_TT_VERSION 14
    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
    BEGIN
    DBMS_DST.FIND_AFFECTED_TABLES
    (affected_tables => 'sys.dst$affected_tables',
    log_errors => TRUE,
    log_errors_table => 'sys.dst$error_table');
    END;
    /

    Check what tables have affected data that cannot be resolved automatically. If this gives no rows then there is no problem at all.
    SELECT * FROM sys.dst$affected_tables;

    If previous select gives rows then you can see what kind of problem there are in those rows
    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 a 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.
    SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1883';
    SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1878';
    SELECT * FROM sys.dst$error_table where ERROR_NUMBER not in ('1878','1883');

    When updating from DSTv1 or DSTv2 (mainly after 9.2 upgrades) to a higher DST version it is possible to have also '1882' errors. The cause is explained in Note 414590.1. These can be ignored, they will be corrected during the actual update of the dst version.

    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;

    The result should be:
    PROPERTY_NAME VALUE
    ---------------------------- ------------------------------
    DST_PRIMARY_TT_VERSION =
    DST_SECONDARY_TT_VERSION = 0
    DST_UPGRADE_STATE = NONE

  3. Do the actual RDBMS DST version update of the database
  4. Assuming all non-existing time and overlap times in previous step are solved or logged, so using for DBMS_DST.UPGRADE_DATABASE error_on_overlap_time => FALSE and error_on_nonexisting_time => FALSE); For RAC the database should be in single instance mode , as required by the "startup UPGRADE".
    conn / as sysdba
    shutdown immediate;
    startup upgrade;
    set serveroutput on

    Make sure that the upgrade is in the correct mode, if the DST_UPGRADE_STATE is "PREPARE" then you did not end the prior steps correctly.
    SELECT PROPERTY_NAME||' = '||SUBSTR(property_value, 1, 30) value
    FROM DATABASE_PROPERTIES
    WHERE PROPERTY_NAME LIKE 'DST_%'
    ORDER BY PROPERTY_NAME;

    If there are objects containing TSTZ data in recycle bin, please purge the bin now. Otherwise dbms_dst.begin_upgrade will report "ORA-38301: Can not perform DDL/DML over objects in Recycle Bin".
    purge dba_recyclebin;

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

    Start the upgrade. You should see "An upgrade window has been successfully starte" after executing the following.
    EXEC DBMS_DST.BEGIN_UPGRADE(14);

    Run the following statment and see if it gives the correct results:
    SELECT PROPERTY_NAME||' = '||SUBSTR(property_value, 1, 30) value
    FROM DATABASE_PROPERTIES
    WHERE PROPERTY_NAME LIKE 'DST_%'
    ORDER BY PROPERTY_NAME;

    Check what tables need to update:
    SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';

    Restart the DB
    shutdown immediate
    startup

    Upgrade the tables who need action
    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;
    /

    The ouput of this will be a list of tables like:
    Table list: SYSMAN.AQ$_MGMT_NOTIFY_QTABLE_S
    Number of failures: 0
    ....
    Table list: SYSMAN.MGMT_PROV_ASSIGNMENT
    Number of failures: 0
    Table list: SYSMAN.MGMT_CONFIG_ACTIVITIES
    Number of failures: 0
    Failures:0

    If there are no failures, end the upgrade:
    VAR fail number
    BEGIN
    DBMS_DST.END_UPGRADE(:fail);
    DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
    END;
    /

    The output of the code above should be:
    An upgrade window has been successfully ended.
    Failures:0

    Final verification:

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

    The output of the next statment should be: timezlrg_14.dat 14
    SELECT * FROM v$timezone_file;

  5. Applying a new RDBMS DST version to 11.2 clients.
  6. For Oracle client simply apply the RDBMS DST patch using Opatch.
    Oracle 11.2 Clients will use the highest timezlrg_XX.dat found in the ORACLE_HOME.
    This can be overwritten by setting explicit the ORA_TZFILE variable (which is by default not set), aldo there is in real life little need to do so.
    A DSTv13 11.2 client can perfectly connect to an non-DSTv13 server for example. The DST version only comes to play when actually using DST related information in SQL.

    References

    NOTE:1201253.1 - Actions For DST Updates When Upgrading To Or Applying The 11.2.0.2 Patchset
    NOTE:412160.1 - Updated DST transitions and new Time Zones in Oracle Time Zone File patches
    NOTE:815679.1 - Actions For DST Updates When Upgrading To 11.2.0.1 Base Release
    NOTE:1255474.1 - Different Time Zone Version In Registry$Database And V$Timezone_file

None Private Note List:

URL Note Summary Days before delete
AllowedHtml <ul> <li><br> - Line break.</li> <li><o... 30137
BigDataFishig <b>#RealTimeData + #BigData + #Fishing</b>...teach... 30975
ChatSample Lee: Hello there, so can you explain how to chat u... 30137
Command-Line-Shortcuts <h3>Basic Keyboard Shortcuts</h3><b>Up/Down Arrows... 30223
dba <h2 style="display: inline;">Timeline:</h2> 21-Ja... 30166
dst_upgrade <h1>Oracle 11.2.0.2 DST Upgrade</h1><ol><li><b>che... 30201
Feedback ... 30081
hadoop_class_at_ebay eBay's North Campus, Parley room 2161 N. First ... 30260
mysql Get the size of a MYSQL Dababase: [code]SELECT ta... 30136
outlook-meeting-assassin <h1>Outlook Meeting Assassin</h1>A Simple Outlook ... 30695
Programming-Quotes <h3>Programming Quotes</h3> Java is to JavaScri... 30223
scrum <h2 style="margin: 0px 0px 0px 0px; padding: 0px 0... 867
vi <h2>vi cheat sheet</h2><b><u>Starting & ending com... 30142
websiteTricks-transperent [code]<!-- div.background { width:500px; h... 30081
xdb_installation <h1>Oracle XML DB (XDB) Installation</h1><ol> <li... 30201
zzzzzzzzzzz whycghkghfjjfhkctshsaqzmypqgrehghtmfggfhgoygvfjknm... 652

What else can I do with this?