Mar 2, 2017

Upgrade Oracle DST in 12cR1

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


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
14


DST_SECONDARY_TT_VERSION
0


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


No comments:

Post a Comment