Using DBMS_DST package to upgrade the timezone file
version
If you see a warning stating that
your current timezone file version is lower then 11, you need to upgrade it to
version 11 if you are using data with timezone information or you have plans to
use timezone data in future.
Oracle 11g provides a built-in
package DBMS_DST that can be used to evaluate the current timezone data before
the timezone file version upgrade and does the upgrade of timezone file version
as well.
The DBMS_DST package helps performing
these tasks by creating a prepare window and upgrade window.
DBMS_DST
prepare window
Use this window to validate the
current timezone data that you have. How will it be affected by the timezone
file version upgrade? Are there gonna be some errors during the upgrade?
(Only user prepare window if you are currently using data with timezone)
(Only user prepare window if you are currently using data with timezone)
$ sqlplus / as sysdba
SQL> startup
SQL> set serveroutput on
SQL>
SQL> EXEC
DBMS_DST.BEGIN_PREPARE(11);
A prepare window has been
successfully started.
PL/SQL procedure successfully
completed.
SQL> 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 4
DST_SECONDARY_TT_VERSION 11
DST_UPGRADE_STATE PREPARE
/*
The above query shows the primary and
secondary timezone file version and the Upgrade
state of the timezone file.
*/
SQL> EXEC
DBMS_DST.CREATE_AFFECTED_TABLE('my_affected_tables');
SYS.MY_AFFECTED_TABLES has been
successfully created.
PL/SQL procedure successfully
completed.
SQL> EXEC
DBMS_DST.CREATE_ERROR_TABLE('my_error_table');
SYS.MY_ERROR_TABLE has been
successfully created.
PL/SQL procedure successfully
completed.
/*
Two tables have been created to record the
affected data and and any errors that may
arise during the upgrade.
*/
SQL> truncate table
my_affected_tables;
Table truncated.
SQL> truncate table
my_error_table;
Table truncated.
-- Empty out the
tables.
begin
DBMS_DST.FIND_AFFECTED_TABLES
(
affected_tables =>
'my_affected_tables',
log_errors => TRUE,
log_errors_table => 'my_error_table'
);
end;
/
PL/SQL procedure successfully
completed.
/*
The DBMS_DST.FIND_AFFECTED_TABLES will fill up
the affected timezone data into
affected_table and any errors in the
log_error_table.
*/
SQL> SELECT * FROM
my_affected_tables;
no rows selected
SQL> SELECT * FROM
my_error_table;
no rows selected
/*
If you will have some affected timezone data
and if there are any errors you may review
them by reading the affected_table and
error_table.
*/
SQL> EXEC DBMS_DST.END_PREPARE;
A prepare window has been
successfully ended.
PL/SQL procedure successfully
completed.
-- End the prepare
window.
SQL> SELECT PROPERTY_NAME,
SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME; 2 3
4
PROPERTY_NAME VALUE
------------------------------
------------------------------
DST_PRIMARY_TT_VERSION 4
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
-- The UPGRADE_STATE
has become NONE and secondary timezone file version is 0.
Please look at the link below to see
how to handle errors in current timezone data reported by the prepare window.
Error Handling when Upgrading Time Zone File and Timestamp with Time Zone Data
Error Handling when Upgrading Time Zone File and Timestamp with Time Zone Data
DBMS_DST
upgrade window
Now is the time to upgrade the
database timezone file version to 11. For that we will use the DBMS_DST upgrade
window. This will be done by starting up the database in upgrade mode.
SQL> shutdown immediate
SQL> startup upgrade
SQL> EXEC
DBMS_DST.BEGIN_UPGRADE(11);
PL/SQL procedure successfully completed.
-- Start the DBMS_DST
upgrade window.
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 11
DST_SECONDARY_TT_VERSION 4
DST_UPGRADE_STATE UPGRADE
--Primary timezone
file version is now 11 and secondary is 4. The upgrade state is UPGRADE.
SQL> SELECT OWNER, TABLE_NAME,
UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES;
OWNER TABLE_NAME UPG
------------------------------
------------------------------ ---
SYS
AQ$_AQ_PROP_TABLE_S NO
.
/* A few more objects
with UPGRADE_IN_PROGRESS=NO */
.
IX
AQ$_ORDERS_QUEUETABLE_L YES
/*
Most of the dictionary tables are upgraded
with the DBMS_DST.BEGIN_UPGRADE call.
The tables that are not yet upgraded have a
"YES" value in the UPGRADE_IN_PROGRESS column
in the above query.
To upgrade these tables we need to call
DBMS_DST.UPGRADE_DATABASE. And if you don't you
cannot end the DBMS_DST upgrade window. And
you cannot call it while staying in the
upgrade mode. Please shutdown the database
and start it up normally.
*/
shutdown immediate
startup
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 =>
TRUE,
error_on_nonexisting_time => TRUE);
DBMS_OUTPUT.PUT_LINE('Failures:'||
:numfail);
END;
/
Failures: 0
PL/SQL procedure successfully
completed.
SQL> print :numfail
NUMFAIL
----------
0
/* If numfail is
greater then 0 DBMS_DST.END_UPGRADE will fail. */
BEGIN
DBMS_DST.END_UPGRADE(:numfail);
END;
/
PL/SQL procedure successfully
completed.
-- The upgrade window
is ended.
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 11
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
The timezone file version is
upgraded to 11 now.
For more details about handling the timezone
file warning see the link below:
No comments:
Post a Comment