Manual upgrade 11gR2 to later Version Time Zone Upgrade

conn / as sysdba

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;

purge dba_recyclebin;

-- clean used tables

TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;

TRUNCATE TABLE sys.dst$affected_tables;

TRUNCATE TABLE sys.dst$error_table;

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

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

-- will be seen

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

SELECT * FROM sys.dst$error_table;

-- check if this select gives

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

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE 'DST_%'

ORDER BY PROPERTY_NAME;

-- gives this output:

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

-- now restart the database

shutdown immediate

startup

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

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:

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;

/

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

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE 'DST_%'

ORDER BY PROPERTY_NAME;

SELECT * FROM v$timezone_file;

exit;

conn / as sysdba

SELECT VERSION FROM v$timezone_file;

select TZ_VERSION from registry$database;

If select TZ_VERSION from registry$database still shows 4 , then please perform

conn / as sysdba

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

commit;