How to Fix Incomplete RDBMS DST Upgrading in 12c

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:

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
  1. Try to end the upgrading
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

2. 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

3. 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.

4. Check the upgrading result:

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)

Advertisements
This entry was posted in 12c new features, Upgrading and Patching and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s