oracledba.help
Create

Upgrade 12c TimeZone

<- Create

Overview

What follows are manual steps to upgrade an Oracle 12c database time zone. The default time zone file shipped with the Oracle 12c Release 1 is version 18. These instructions are derived from Doc ID 1585343.1 and other related documents. To determine your version run the following:

SELECT version FROM v$timezone_file;

Prerequisites

  • Your Oracle database must be on version 12c.
  • Search on Doc ID 1585343.1 to download the latest DBMS_DST scripts to perform the upgrade actions. Ex: DBMS_DST_scriptsV1.9.zip

Special Instructions

Create a unique spool file for each script as you will need to check the output and in most cases the screen goes by too fast. Review each spool file for any issues or special instructions.

spool c:\temp\countstatsTSTZ.spool

Procedure

  1. Run scripts to improve performance of upgrade actions.
    • countstatsTSTZ.sql
    • countstarTSTZ.sql
  2. Run upg_tzv_check.sql script.
    • upg_tzv_check.sql takes no arguments.
    • It will detect the highest installed DST patch automatically and needs no downtime
    • This can be run on a live production database but it WILL purge the dba_recyclebin.
    • A successful run will show at the end: 
INFO: A newer RDBMS DST version than the one currently used is found. 
INFO: Note that NO DST update was yet done. 
INFO: Now run upg_tzv_apply.sql to do the actual RDBMS DST update. 
INFO: Note that the upg_tzv_apply.sql script will  
INFO: restart the database 2 times WITHOUT any confirmation or prompt.
  1. If upg_tzv_check.sql has run successfully then run the upg_tzv_apply.sql to change time zone.
    • Make sure any application accessing or storing TSTZ data is stopped.
    • It will restart the database 2 times without asking any confirmation.
    • Typically upg_tzv_apply.sql will take less time than upg_tzv_check.sql

Checking the Upgrade Status

From a separate SQLPlus console you can run upg_tzv_check.sql to see if the process is hung.

You can also run this:

SELECT count(*) FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';

As long as the count(*) goes down upg_tzv_apply.sql is working it's way trough the dataset, simply wait until it's done. If there is no user TSTZ data in this database then for the next update check the output of countstatsTSTZ.sql or countstarTSTZ.sql and follow the suggestions to remove uneeded TSTZ data.