Upgrade Oracle DST in 12cR1

Introduction

Few days ago, I have to do a work with upgrade DST in Oracle. Really, the upgrade is very easy, I followed steps in the next metalink note:

Updating the RDBMS DST version in 12c Release 1 (12.1.0.1 and up) using DBMS_DST (Doc ID 1509653.1)

Attention, The DST upgrade may require reboot of the instance.

Review current DST version:

(This steps are the same in the note in paragraph 3a [3a) check current RDBMS DST version and "DST UPGRADE STATUS" in your 12.1.0.x database:

] )

SQL> SELECT version FROM v$timezone_file;

VERSION

----------

I have old DST version, i need upgrade. Its important that the column “DST_UPGRADE_STATE“ it has NONE value. Wheter you see another value you have to follow the steps en the paragraph :Solving DST_UPGRADE_STATE in UPGRADE, PREPARE or DATAPUMP status.

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE 'DST_%'

ORDER BY PROPERTY_NAME;

SQL> 2 3 4

PROPERTY_NAME

--------------------------------------------------------------------------------

VALUE

--------------------------------------------------------------------------------

DST_PRIMARY_TT_VERSION

DST_SECONDARY_TT_VERSION

DST_UPGRADE_STATE

NONE

Steps for prepare DST upgrade:

Follow step by step as described in the paragraph :

3b) Check UPFRONT using DBMS_DST if there is affected data that cannot be resolved automatically in your 12.1.0.x database:

-- the actual commands are listed in BOLD

-- the next steps use <the new DST version number> in the statements

-- simply replace it with the actual number ( 11, 15 etc)

-- of the RDBMS DST version you want to update to.

conn / as sysdba

-- If there are objects containing TSTZ data in recycle bin,

-- please purge the bin now.

purge dba_recyclebin;

-- Set client_info so one can use:

-- select .... from V$SESSION where CLIENT_INFO = 'upg_tzv';

EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('upg_tzv')

-- this alter session might speed up DBMS_DST on some db's

-- see Bug 10209691 / Bug 12658443

alter session set "_with_subquery"=materialize;

-- to avoid the issue in note 1407273.1

alter session set "_simple_view_merging"=TRUE;

-- start prepare window

-- these steps will NOT update any data yet.

/*Here you should to use your target DST versión, for mi case is: 18.

exec DBMS_DST.BEGIN_PREPARE(<the new DST version number>)

--Sample DBMS_DST.BEGIN_PREPARE error if the 12.1.0.x DST patch for the requested DST version is not installed:

-- SQL> exec DBMS_DST.BEGIN_PREPARE(19)

-- BEGIN DBMS_DST.BEGIN_PREPARE(19); END;

-- *

-- ERROR at line 1:

-- ORA-30094: failed to find the time zone data file for version 19 in

-- $ORACLE_HOME/oracore/zoneinfo

-- ORA-06512: at "SYS.DBMS_DST", line 57

-- ORA-06512: at "SYS.DBMS_DST", line 1258

-- ORA-06512: at line 1

-- FIX: install the 12.1.0.x patch for the DST version you want to use. See note 412160.1

-- Sample DBMS_DST.BEGIN_PREPARE error if the requested new DST version is the current or a lower than the current timezone version:

-- SQL> exec DBMS_DST.BEGIN_PREPARE(4);

-- BEGIN DBMS_DST.BEGIN_PREPARE(4); END;

-- *

-- ERROR at line 1:

-- ORA-56921: invalid time zone version

-- ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79

-- ORA-06512: at "SYS.DBMS_DST", line 1252

-- ORA-06512: at line 1

-- FIX: you cannot "downgrade" DST, there is no need to do this. The new DST version needs to be higher than the current DST_PRIMARY_TT_VERSION

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

-- output should be

-- PROPERTY_NAME VALUE

-- ------------------------------ ------------------------------

-- DST_PRIMARY_TT_VERSION <the old DST version number>

-- DST_SECONDARY_TT_VERSION <the new DST version number>

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

set serveroutput on

BEGIN

DBMS_DST.FIND_AFFECTED_TABLES

(affected_tables => 'sys.dst$affected_tables',

log_errors => TRUE,

log_errors_table => 'sys.dst$error_table');

END;

-- If DBMS_DST.FIND_AFFECTED_TABLES failes with

-- ERROR at line 1:

-- ORA-01882: timezone region not found

-- ORA-06512: at "SYS.DBMS_DST", line 284

-- ORA-06512: at "SYS.DBMS_DST", line 1511

-- ORA-06512: at line 2

-- then first of all run the Fix1882.sql script found in Note 414590.1 using the server home sqlplus and then retry DBMS_DST.FIND_AFFECTED_TABLES

-- Now check what tables have affected data in TSTZ columns.

-- If dst$affected_tables has no rows then there is no actual data to update by DBMS_DST

-- If dst$affected_tables has rows it simply means those rows need

-- to be updated by DBM_DST during the DST upgrade (= point 4)

-- because they contain timezones that are affected by the DST upgrade

SELECT * FROM sys.dst$affected_tables;

-- If dst$affected_tables has rows then you can see in dst$error_table

-- if there are any rows with a "problem" and what kind of problem there are in those rows.

-- Note that if there are rows in dst$affected_tables

-- this does not mean there need to be rows in dst$error_table

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 an 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.

-- all "error_on_overlap_time" rows

SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1883';

-- all "error_on_nonexisting_time" rows

SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1878';

-- check for all other possible problems

SELECT * FROM sys.dst$error_table where ERROR_NUMBER not in ('1878','1883');

-- 1882 errors will be resolved by DBMS_DST if the cause is the issue explained in Note 414590.1

-- those should be corrected during the actual update of the dst version. It is however possible some

-- other reasons may cause 1882 but in that case DBMS_DST.FIND_AFFECTED_TABLES would have also errored

-- out with ora-1882.

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

-- output should be

-- PROPERTY_NAME VALUE

-- ---------------------------- ------------------------------

-- DST_PRIMARY_TT_VERSION <the old DST version number>

-- DST_SECONDARY_TT_VERSION 0

-- DST_UPGRADE_STATE NONE

Upgrade of DST

(Pay attention pls, in this step you will shutdown the instance)

-- the actual commands are listed in BOLD

-- the next steps use <the new DST version number> in the statements

-- simply replace it with the actual number ( 11, 15 etc)

-- of the RDBMS DST version you want to update to.

conn / as sysdba

shutdown immediate;

startup upgrade;

set serveroutput on

-- in a PDB startup upgrade will not work

-- alter pluggable database open upgrade need to be used

-- ALTER pluggable DATABASE CLOSE immediate;

-- ALTER pluggable DATABASE OPEN upgrade;

-- check if previous prepare window is ended

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE 'DST_%'

ORDER BY PROPERTY_NAME;

-- output should be

-- PROPERTY_NAME VALUE

-- ---------------------------- ------------------------------

-- DST_PRIMARY_TT_VERSION <the old DST version number>

-- DST_SECONDARY_TT_VERSION 0

-- DST_UPGRADE_STATE NONE

-- If DST_UPGRADE_STATE is "PREPARE" then you did not ended the prepare window in step 3)

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

-- Set client_info so one can use:

-- select .... from V$SESSION where CLIENT_INFO = 'upg_tzv';

EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('upg_tzv')

-- this alter session might speed up DBMS_DST on some db's

-- see Bug 10209691 / Bug 12658443

alter session set "_with_subquery"=materialize;

-- to avoid the issue in note 1407273.1

alter session set "_simple_view_merging"=TRUE;

-- start upgrade window

EXEC DBMS_DST.BEGIN_UPGRADE(<the new DST version number>);

Pls remember in my case it was 18

-- the message

-- "An upgrade window has been successfully started."

-- will be seen

-- Sample DBMS_DST.BEGIN_UPGRADE error if a previous (prepare) window was not ended:

-- SQL> EXEC DBMS_DST.BEGIN_UPGRADE(19);

-- BEGIN DBMS_DST.BEGIN_UPGRADE(19); END;

-- *

-- ERROR at line 1:

-- ORA-56920: a prepare or upgrade window or an on-demand or datapump-job loading

-- of a secondary time zone data file is in an active state

-- ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79

-- ORA-06512: at "SYS.DBMS_DST", line 1054

-- ORA-06512: at line 1

-- FIX: You NEED to end the "PREPARE" window in the previous step BEFORE doing the UPGRADE.

-- Or in other words, you did not do the "EXEC DBMS_DST.END_PREPARE;" step in point 3)

-- Sample DBMS_DST.BEGIN_UPGRADE error if the requested DST version / patch is not installed:

-- SQL> EXEC DBMS_DST.BEGIN_UPGRADE(19);

-- BEGIN DBMS_DST.BEGIN_UPGRADE(19); END;

-- *

-- ERROR at line 1:

-- ORA-30094: failed to find the time zone data file for version 19 in

-- $ORACLE_HOME/oracore/zoneinfo

-- ORA-06512: at "SYS.DBMS_DST", line 57

-- ORA-06512: at "SYS.DBMS_DST", line 1076

-- ORA-06512: at line 1

-- FIX: Install the 12.1.0.x patch for the DST version you want to use. See note 412160.1

-- Sample DBMS_DST.BEGIN_UPGRADE error if the database is not in upgrade mode:

-- SQL> EXEC DBMS_DST.BEGIN_UPGRADE(19);

-- BEGIN DBMS_DST.BEGIN_UPGRADE(19); END;

-- *

-- ERROR at line 1:

-- ORA-56926: database must be in UPGRADE mode in order to start an upgrade windo

-- ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79

-- ORA-06512: at "SYS.DBMS_DST", line 1091

-- ORA-06512: at line 1

-- FIX: start the database in UPGRADE mode

-- check if this select give no rows, if it does something went wrong

SELECT * FROM sys.dst$error_table;

-- check if this select

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE 'DST_%'

ORDER BY PROPERTY_NAME;

-- gives this output:

-- PROPERTY_NAME VALUE

-- --------------------------- ------------------------------

-- DST_PRIMARY_TT_VERSION <the new DST version number>

-- DST_SECONDARY_TT_VERSION <the old DST version number>

-- DST_UPGRADE_STATE UPGRADE

-- Optionally you can check what user tables still need to be updated using DBMS_DST.UPGRADE_DATABASE

-- BEGIN_UPGRADE upgrades system tables that contain TSTZ data and marks user tables (containing TSTZ data) with the UPGRADE_IN_PROGRESS property.

-- even if this select gives no rows you still need to do to the rest of the steps

-- it simply gives an indication of how many user objects need to processed in the later steps

-- some oracle provided users may be listed here, that is normal

SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';

-- now restart the database - this will also work fine in a PDB

-- NOTE: Oracle support has seen SR's where some customers stop here, the upgrade is NOT finished yet - please DO follow the next steps !!!!!

shutdown immediate

startup

-- at this point the database can actually be used note however that the

-- upgrade_database will take exclusive locks on the tables when they are actually upgraded

-- so it might provoke issues.

alter session set "_with_subquery"=materialize;

alter session set "_simple_view_merging"=TRUE;

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

-- ouput of this will be a list of tables like:

-- Table list: "APEX_040200"."WWV_FLOW_FEEDBACK_FOLLOWUP"

-- Number of failures: 0

-- Table list: "APEX_040200"."WWV_FLOW_FEEDBACK"

-- Number of failures: 0

-- Table list: "APEX_040200"."WWV_FLOW_WORKSHEET_NOTIFY"

-- Number of failures: 0

-- Table list: "APEX_040200"."WWV_FLOW_DEBUG_MESSAGES2"

-- Number of failures: 0

-- Table list: "APEX_040200"."WWV_FLOW_DEBUG_MESSAGES"

-- Number of failures: 0

-- Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"

-- Number of failures: 0

-- Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"

-- Number of failures: 0

-- Failures:0

-- this select should , if no errors where given also give "no rows", if it does something went wrong

SELECT * FROM sys.dst$error_table;

-- if there where no failures then end the upgrade.

VAR fail number

BEGIN

DBMS_DST.END_UPGRADE(:fail);

DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);

END;

-- output that will be seen:

-- An upgrade window has been successfully ended.

-- Failures:0

-- Sample DBMS_DST.END_UPGRADE error if DBMS_DST.UPGRADE_DATABASE was not issued:

-- *

-- ERROR at line 1:

-- ORA-56929: Ending an upgrade window failed

-- ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79

-- ORA-06512: at "SYS.DBMS_DST", line 1169

-- ORA-06512: at line 2

-- FIX: start database normally and run DBMS_DST.UPGRADE_DATABASE

-- last checks

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE 'DST_%'

ORDER BY PROPERTY_NAME;

-- needed output:

-- PROPERTY_NAME VALUE

-- ---------------------------- ------------------------------

-- DST_PRIMARY_TT_VERSION <the new DST version number>

-- DST_SECONDARY_TT_VERSION 0

-- DST_UPGRADE_STATE NONE

In this step you should to view new version of DST

SELECT * FROM v$timezone_file;

-- needed output:

-- FILENAME VERSION

-- ------------------ ----------

-- timezlrg_<new version>.dat <new version>

Note: make sure to exit this session, do not use it for timezone related selects , it still uses the old timezone version

So, our DST version is updated. If you want, can query the registry$database, and you should to see the same version results:

SELECT VERSION FROM v$timezone_file;

select TZ_VERSION from registry$database;

If you see any difference, you can to do the next update to the internal table:

conn / as sysdba

update registry$database set TZ_VERSION = (select version FROM v$timezone_file);

commit;

That is all.

Bye

DBA TIPS (by Felipe Donoso)

Mar 2, 2017