Technological recipes that I've held to prepare some solutions DBA environment. (by Felipe Donoso Bastias)

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


Share:

0 Comments:

Post a Comment

Copyright © DBA TIPS | Powered by Blogger
Design by SimpleWpThemes | Blogger Theme by NewBloggerThemes.com | Free Blogger Templates