Month: April 2016

There is a database upgraded from 11.2.0.4 to 12.1.0.2 by using “Complete Checklist for Upgrading to Oracle Database 12c Release 1 using DBUA (Doc ID 1516557.1)”. For some reason, the DST upgrading was not complete successfully after the upgrading checking:

Current DST state is still “Upgrade”.

SQL>SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
    FROM DATABASE_PROPERTIES
    WHERE PROPERTY_NAME LIKE 'DST_%'
    ORDER BY PROPERTY_NAME;

PROPERTY_NAME			    VALUE
----------------------------------- ------------------------------
DST_PRIMARY_TT_VERSION		    18
DST_SECONDARY_TT_VERSION	    14
DST_UPGRADE_STATE		    UPGRADE

Try to end the upgrading, still got issues.

SQL>shutdown immediate
SQL>startup

SQL>alter session set "_with_subquery"=materialize;
SQL>alter session set "_simple_view_merging"=TRUE;


SQL>VAR fail number
SQL>BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/
Number of failures: 2
BEGIN
*
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 1289
ORA-06512: at line 

SQL>SELECT * FROM sys.dst$error_table;

no rows selected

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

OWNER            TABLE_NAME                    UPG
---------------- ----------------------------  ----  
GSMADMIN_INTERNAL AQ$_CHANGE_LOG_QUEUE_TABLE_L YES
GSMADMIN_INTERNAL AQ$_CHANGE_LOG_QUEUE_TABLE_S YES

Start database normally and run DBMS_DST.UPGRADE_DATABASE.

SQL>set serveroutput on
SQL>VAR numfail number
SQL>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;
/

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

PL/SQL procedure successfully completed.



SQL>SELECT * FROM sys.dst$error_table;

no rows selected

End the upgrading.

SQL> VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/
An upgrade window has been successfully ended.
Failures:0

PL/SQL procedure successfully completed.

Check the successful upgrading.

SQL>SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
      FROM DATABASE_PROPERTIES
     WHERE PROPERTY_NAME LIKE 'DST_%'
  ORDER BY PROPERTY_NAME;  

PROPERTY_NAME             VALUE
------------------------- -------
DST_PRIMARY_TT_VERSION       18
DST_SECONDARY_TT_VERSION      0
DST_UPGRADE_STATE          NONE


SQL>SELECT * FROM v$timezone_file;

FILENAME		VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_18.dat 	     18 	 0

Reference:

Updating the RDBMS DST version in 12c Release 1 (12.1.0.1 and up) using DBMS_DST (Doc ID 1509653.1)

Scripts to automatically update the RDBMS DST (timezone) version in an 11gR2 or 12cR1 database . (Doc ID 1585343.1)