Month: November 2017

cd $ORACLE_HOME/rdbms/admin $
sqlplus / 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;
 
-- 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;
-- needed output:
-- 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;