Upgrade Time Zone files

Validaremos la version de el archivo TZ:

conn / as sysdba

SELECT * FROM v$timezone_file;

timezlrg_11.dat              11

shutdown immediate;

startup upgrade;

set serveroutput on

— 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

purge dba_recyclebin;

TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;

TRUNCATE TABLE sys.dst$affected_tables;

TRUNCATE TABLE sys.dst$error_table;

alter session set “_with_subquery”=materialize;

alter session set “_simple_view_merging”=TRUE;

EXEC DBMS_DST.BEGIN_UPGRADE(14);

– 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

shutdown immediate

startup

alter session set “_with_subquery”=materialize;

alter session set “_simple_view_merging”=TRUE;

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: SYSMAN.AQ$_MGMT_NOTIFY_QTABLE_S

— Number of failures: 0

— 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

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

SELECT * FROM v$timezone_file;

FILENAME                VERSION

——————– ———-

timezlrg_14.dat              14

select TZ_VERSION from registry$database;

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

commit;