During the prepare window, you can run any of the following scripts present in the $ORACLE_HOME/rdbms/admin directory to check how much data will need to be updated in the database during the time zone data upgrade:

• utltz_countstats.sql

  This script shows the optimizer statistics of num_rows of all the tables having TIMESTAMP WITH TIME ZONE (TSTZ) data.

  Note:

  Run the utltz_countstats.sql script only when the database optimizer statistics are up to date, else run the utltz_countstar.sql script. If you run the utltz_countstats.sql script, then you need not run the utltz_countstar.sql script.

• utltz_countstar.sql

  This script shows the result of the count(*) operation for all the tables having TIMESTAMP WITH TIME ZONE (TSTZ) data.

  Note:

  The utltz_countstar.sql script may take a considerable amount of time to complete its execution.

The DBMS_SCHEDULER table generally contains a large amount of time zone data. In case the data in this table is not needed, then delete it using the following command before you run the upgrade steps. Stop the main jobs before running this command as it may not delete all the data from the DBMS_SCHEDULER table, if some of the main jobs in a chain of jobs are still running.

exec dbms_scheduler.purge_log;

The other tables that may contain a large amount of time zone data are the SYS.WRI$_OPTSTAT_HISTGRM_HISTORY and SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY tables. In case you do not need this data, then you may delete it using the following commands:

-- check the number of rows in the tables
select count(*) from SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;
select count(*) from SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;

-- check the data retention period of the stats
-- the default value is 31
select systimestamp - dbms_stats.get_stats_history_availability from dual;

-- disable stats retention
exec dbms_stats.alter_stats_history_retention(0);

-- remove all the stats
exec DBMS_STATS.PURGE_STATS(systimestamp);

-- check the result of the purge operation
select count(*) from SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;
select count(*) from SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;

You may set the data retention period back to its original value using the following command once the time zone data upgrade is completed:

exec dbms_stats.alter_stats_history_retention(31);

During the upgrade window, you can run the following scripts present in the $ORACLE_HOME/rdbms/admin directory to upgrade the time zone data in the database:

1. Run the utltz_upg_check.sql script from the $ORACLE_HOME directory:

   spool utltz_upg_check.log
   @utltz_upg_check.sql
   spool off

   The following information is displayed on the screen after successful execution of the script:

   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 utltz_upg_apply.sql to do the actual RDBMS DST update.
   INFO: Note that the utltz_upg_apply.sql script will
   INFO: restart the database 2 times WITHOUT any confirmation or prompt.

   The script also writes the following information in the alert.log file:

   utltz_upg_check sucessfully found newer RDBMS DSTv new_time_zone_version and took number_of_minutes minutes to run.

   If the utltz_upg_check.sql script displays the following error, check the previous message displayed on the screen and proceed accordingly.

   ORA-20xxx: Stopping script - see previous message...

2. Run the utltz_upg_apply.sql script from the $ORACLE_HOME directory after the utltz_upg_check.sql script is executed successfully:

   spool utltz_upg_apply.log
   @utltz_upg_apply.sql
   spool off

   Note:

   The following are the prerequisites for running the utltz_upg_apply.sql script:

   • No application should query or insert time zone data in the database during the time zone upgrade process.

   • In an RAC environment, the RAC database must be started as a single database instance.

   • In a multitenant environment, all the PDBs must be shut down before running the utltz_upg_apply.sql script on the CDB.

   Also, note the following:

   • The utltz_upg_apply.sql script automatically restarts the database multiple times during its execution.

   • The utltz_upg_apply.sql script generally takes less time to execute than the utltz_upg_check.sql script.

   The following information is displayed on the screen after successful execution of the utltz_upg_apply.sql script:

   INFO: The RDBMS DST update is successfully finished.
   INFO: Make sure to exit this sqlplus session.
   INFO: Do not use it for timezone related selects.

   The TZ_VERSION column in the Registry$database table now gets updated with the new time zone version.

   If the script displays the following error message, then check the previous message displayed on the screen and proceed accordingly.

   ORA-20xxx: Stopping script - see previous message...

set PAGES 1000

-- query the V$SESSION_LONGOPS view
select TARGET, TO_CHAR(START_TIME,'HH24:MI:SS - DD-MM-YY'),
       TIME_REMAINING, SOFAR, TOTALWORK, SID, SERIAL#, OPNAME
from V$SESSION_LONGOPS
where sid in
      (select SID from V$SESSION where CLIENT_INFO = 'upg_tzv')
      and
      SOFAR < TOTALWORK
order by START_TIME;

-- query the V$SESSION and V$SQLAREA views
select S.SID, S.SERIAL#, S.SQL_ID, S.PREV_SQL_ID,
       S.EVENT#, S.EVENT, S.P1TEXT, S.P1, S.P2TEXT,
       S.P2, S.P3TEXT, S.P3, S.TIME_REMAINING_MICRO,
       S.SEQ#, S.BLOCKING_SESSION, BS.PROGRAM "Blocking Program",
       Q1.SQL_TEXT "Current SQL", Q2.SQL_TEXT "Previous SQL"
from V$SESSION S, V$SQLAREA Q1, V$SQLAREA Q2, V$SESSION BS
where S.SQL_ID = Q1.SQL_ID(+) and
      S.PREV_SQL_ID = Q2.SQL_ID(+) and
      S.BLOCKING_SESSION = BS.SID(+) and
      S.CLIENT_INFO = 'upg_tzv';

During the prepare window, you can get the information about the data that will be affected during the time zone upgrade process using the following steps:

During the upgrade window, you can upgrade the time zone data using the following steps:

This example illustrates updating DST behavior to Oracle Database 11g, release 2 for which the default time zone version is 14. First, assume that your current database is using time zone version 3, and also assume you have an existing table t, which contains timestamp with time zone data.

Connect to the database as the user scott and execute the following statements:

DROP TABLE t;
CREATE TABLE t (c NUMBER, mark VARCHAR(25), ts TIMESTAMP WITH TIME ZONE);
 
INSERT INTO t VALUES(1, 'not_affected', 
                    to_timestamp_tz('22-sep-2006 13:00:00 america/los_angeles',
                                    'dd-mon-yyyy hh24:mi:ss tzr tzd'));
INSERT INTO t VALUES(4, 'affected_err_exist',
                     to_timestamp_tz('11-mar-2007 00:30:00 america/st_johns',
                                     'dd-mon-yyyy hh24:mi:ss tzr tzd'));
INSERT INTO t VALUES(6, 'affected_no_err',
                     to_timestamp_tz('11-mar-2007 01:30:00 america/st_johns',
                                     'dd-mon-yyyy hh24:mi:ss tzr tzd'));
INSERT INTO t VALUES(14, 'affected_err_dup', 
                     to_timestamp_tz('21-sep-2006 23:30:00 egypt',
                                     'dd-mon-yyyy hh24:mi:ss tzr tzd'));
COMMIT;
 

Then, optionally, you can start a prepare window to check the affected data and potential semantic errors where there is an overlap or non-existing time. To do this, you should start a window for preparation to migrate to time zone version 14. It is assumed that you have the necessary privileges. These privileges are controlled with the DBMS_DST package.

As an example, first, prepare the window.

connect / as sysdba
set serveroutput on
EXEC DBMS_DST.BEGIN_PREPARE(14);
 
A prepare window has been successfully started.
 
PL/SQL procedure successfully completed.
 

Note that the argument 14 causes the time zone version 14 to be used in this statement. After this window is successfully started, you can check the status of the DST in DATABASE_PROPERTIES as shown in the following example:

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

You will see the output similar to the following:

PROPERTY_NAME                  VALUE
---------------------------    ---------
DST_PRIMARY_TT_VERSION         3
DST_SECONDARY_TT_VERSION       14
DST_UPGRADE_STATE              PREPARE

Next, you can execute DBMS_DST.FIND_AFFECTED_TABLES to find all the tables in the database that are affected if you upgrade from version 3 to version 14. This table contains the table owner, table name, column name, row count, and error count. Here, you have the choice of using the defaults for error tables (sys.dst$error_table) and affected tables (sys.dst$affected_table) or you can create your own. In this example, we create our own tables by using DBMS_DST.CREATE_ERROR_TABLE and DBMS_DST.CREATE_AFFECTED_TABLE and then pass to FIND_AFFECTED_TABLES as shown below.

Connect to the database as the user scott and execute the following statements:

EXEC DBMS_DST.CREATE_AFFECTED_TABLE('my_affected_tables');
EXEC DBMS_DST.CREATE_ERROR_TABLE('my_error_table');
 

It is a good idea to make sure that there are no rows in these tables. You can do this by truncating the tables:

TRUNCATE TABLE my_affected_tables;
TRUNCATE TABLE my_error_table;

Then, you can execute FIND_AFFECTED_TABLES to see which tables are impacted during the upgrade:

connect / as sysdba

BEGIN
   DBMS_DST.FIND_AFFECTED_TABLES(affected_tables  => 'scott.my_affected_tables',
                                 log_errors       => TRUE,
                                 log_errors_table => 'scott.my_error_table');
END;
/

Then, check the affected tables:

SELECT * FROM scott.my_affected_tables;
 
TABLE_OWNER   TABLE_NAME   COLUMN_NAM   ROW_COUNT  ERROR_COUNT
-----------   ----------   ----------   ---------  -----------
SCOTT         T            TS                   3            2
 

Then, check the error table:

SELECT * FROM scott.my_error_table;
 
TABLE_OWNER   TABLE_NAME  COLUMN_NAME   ROWID                ERROR_NUMBER
-----------   ----------  -----------   ------------------   ------------
SCOTT         T           TS            AAAPW3AABAAANzoAAB           1878
SCOTT         T           TS            AAAPW3AABAAANzoAAE           1883
 

These errors can be corrected as described in "Upgrade Error Handling". Then, end the prepare window, as in the following statement:

EXEC DBMS_DST.END_PREPARE;
 
A prepare window has been successfully ended.
 
PL/SQL procedure successfully completed.
 

After this, you can check the DST status in DATABASE_PROPERTIES:

SELECT property_name, SUBSTR(property_value, 1, 30) value
FROM database_properties
WHERE property_name LIKE 'DST_%'
ORDER BY property_name;
 
PROPERTY_NAME                 VALUE
------------------------      --------
DST_PRIMARY_TT_VERSION        3
DST_SECONDARY_TT_VERSION      0
DST_UPGRADE_STATE             NONE
 

Next, you can use the upgrade window to upgrade the affected data. To do this, first, start an upgrade window. Note that the database must be opened in UPGRADE mode before you can execute DBMS_DST.BEGIN_UPGRADE. In Oracle RAC, only one instance can be started. BEGIN_UPGRADE upgrades all dictionary tables in one transaction, so the invocation will either succeed or fail as one whole. During the procedure's execution, all user tables with TSTZ data are marked as an upgrade in progress. See Oracle Database Upgrade Guide for more information.

Also, only SYSDBA can start an upgrade window. If you do not open the database in UPGRADE mode and invoke BEGIN_UPGRADE, you will see the following error:

EXEC DBMS_DST.BEGIN_UPGRADE(14);
BEGIN DBMS_DST.BEGIN_UPGRADE(14); END;
 
*
ERROR at line 1:
ORA-56926: database must be in UPGRADE mode in order to start an upgrade window
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DST", line 1021
ORA-06512: at line 1
 

So, BEGIN_UPGRADE upgrades system tables that contain TSTZ data and marks user tables (containing TSTZ data) with the UPGRADE_IN_PROGRESS property. This can be checked in DBA_TSTZ_TABLES, and is illustrated later in this example.

There are two parameters in BEGIN_UPGRADE that are for handling semantic errors: error_on_overlap_time (error number ORA-1883) and error_on_nonexisting_time (error number ORA-1878). If the parameters use the default setting of FALSE, Oracle converts the data using a default conversion and does not signal an error. See "Upgrade Error Handling" for more information regarding what they mean, and how to handle errors.

The following call can automatically correct semantic errors based on some default values when you upgrade the dictionary tables. If you do not ignore semantic errors, and you do have such errors in the dictionary tables, BEGIN_UPGRADE will fail. These semantic errors are populated into sys.dst$error_table.

EXEC DBMS_DST.BEGIN_UPGRADE(14);
An upgrade window has been successfully started.
 
PL/SQL procedure successfully completed.
 

After this, you can check the DST status in DATABASE_PROPERTIES, as in the following:

SELECT property_name, SUBSTR(property_value, 1, 30) value
FROM database_properties
WHERE property_name LIKE 'DST_%'
ORDER BY property_name;
 
PROPERTY_NAME               VALUE
------------------------    --------
DST_PRIMARY_TT_VERSION            14
DST_SECONDARY_TT_VERSION           3
DST_UPGRADE_STATE            UPGRADE
 

Then, check which user tables are marked with UPGRADE_IN_PROGRESS:

SELECT owner, table_name, upgrade_in_progress FROM dba_tstz_tables;
 
OWNER   TABLE_NAME                      UPGRADE_IN_PROGRESS
-----   -------------------------       -------------------
SYS     WRI$_OPTSTAT_AUX_HISTORY        NO
SYS     WRI$_OPTSTAT_OPR                NO
SYS     OPTSTAT_HIST_CONTROL$           NO
SYS     SCHEDULER$_JOB                  NO
SYS     KET$_AUTOTASK_STATUS            NO
SYS     AQ$_ALERT_QT_S                  NO
SYS     AQ$_KUPC$DATAPUMP_QUETAB_S      NO
DBSNMP  MGMT_DB_FEATURE_LOG             NO
WMSYS   WM$VERSIONED_TABLES             NO
SYS     WRI$_OPTSTAT_IND_HISTORY        NO
SYS     OPTSTAT_USER_PREFS$             NO
SYS     FGR$_FILE_GROUP_FILES           NO
SYS     SCHEDULER$_WINDOW               NO
SYS     WRR$_REPLAY_DIVERGENCE          NO
SCOTT   T                               YES
IX      AQ$_ORDERS_QUEUETABLE_S         YES
...
 

In this output, dictionary tables (in the SYS schema) have already been upgraded by BEGIN_UPGRADE. User tables, such as SCOTT.T, have not been and are in progress.

Now you can perform an upgrade of user tables using DBMS_DST.UPGRADE_DATABASE. All tables must be upgraded, otherwise, you will not be able to end the upgrade window using the END_UPGRADE procedure. Before this step, you must restart the database in normal mode. An example of the syntax is as follows:

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     => TRUE,
            error_on_nonexisting_time => TRUE);

DBMS_OUTPUT.PUT_LINE('Number of tables failed to upgrade:'|| :numfail);
END;
/

If there are any errors, you should correct them and use UPGRADE_TABLE on the individual tables. In that case, you may need to handle tables related to materialized views, such as materialized view base tables, materialized view log tables, and materialized view container tables. There are a couple of considerations to keep in mind when upgrading these tables. First, the base table and its materialized view log table have to be upgraded atomically. Next, the materialized view container table has to be upgraded after all its base tables and the materialized view log tables have been upgraded. In general, Oracle recommends that you handle semantic errors by letting Oracle Database take the default action.

For the sake of this example, let us assume there were some errors in SCOTT.T after you ran UPGRADE_DATABASE. In that case, you can check these errors by using the following query:

SELECT * FROM sys.dst$error_table;
 
TABLE_OWNER   TABLE_NAME  COLUMN_NAME   ROWID                ERROR_NUMBER
-----------   ----------  -----------   ------------------   ------------
SCOTT         T           TS            AAAO2XAABAAANrgAAD           1878
SCOTT         T           TS            AAAO2XAABAAANrgAAE           1878
 

In the output, you can see the errors having number 1878. This error means that an error has occurred for a non-existing time.

To continue with this example, assume that SCOTT.T has a materialized view log scott.mlog$_t, and that there is a single materialized view on SCOTT.T. Then, assume that this 1878 error has been corrected.

Finally, you can upgrade the table, materialized view log and materialized view as follows:

VAR numfail number;
BEGIN
  DBMS_DST.UPGRADE_TABLE(:numfail,
           table_list                => 'SCOTT.t, SCOTT.mlog$_T',
           parallel                  => TRUE,
           continue_after_errors     => FALSE,
           log_errors                => TRUE,
           log_errors_table          => 'SYS.DST$ERROR_TABLE',
           error_on_overlap_time     => FALSE,
           error_on_nonexisting_time => TRUE,
           log_triggers_table        => 'SYS.DST$TRIGGER_TABLE',
           atomic_upgrade            => TRUE);
 
 DBMS_OUTPUT.PUT_LINE('Number of tables failed to upgrade:'|| :numfail);
END;
/

VAR numfail number;
BEGIN
  DBMS_DST.UPGRADE_TABLE(:numfail, 
           table_list                => 'SCOTT.MYMV_T',
           parallel                  => TRUE,
           continue_after_errors     => FALSE,
           log_errors                => TRUE, 
           log_errors_table          => 'SYS.DST$ERROR_TABLE',
           error_on_overlap_time     => FALSE,
           error_on_nonexisting_time => TRUE,
           log_triggers_table        => 'SYS.DST$TRIGGER_TABLE',
           atomic_upgrade            => TRUE);
 
  DBMS_OUTPUT.PUT_LINE('Number of tables failed to upgrade:'|| :numfail);
END;
/ 

The atomic_upgrade parameter enables you to combine the upgrade of the table with its materialized view log.

After all the tables are upgraded, you can invoke END_UPGRADE to end an upgrade window as shown below:

VAR numfail number;
BEGIN
  DBMS_DST.END_UPGRADE(:numfail);
  DBMS_OUTPUT.PUT_LINE('Number of tables failed to upgrade:'|| :numfail);
END;
/

The upgrade window ends if all the affected tables are upgraded successfully, else the output shows how many tables did not upgrade successfully.