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;

No comments: