Zahid Karim
Recent Posts
1.Draw plus and cross sign into PDF via PLSQL using PL_FPDF
2.Draw Triangle directly into PDF via PLSQL using PL_FPDF
3.Write fast and efficient PLSQL - DBMS_PROFILER
4.Fix physically corrupt data blocks using PLSQL - DBMS_REPAIR
5.Encrypt or Decrypt sensitive data using PLSQL - DBMS_CRYPTO
Find me on
Change or switch undo tablespace in Oracle database Upgrade Oracle 10g Release 2 to Oracle 11g Release 2

Oracle 11g Release 2 Pre Upgrade tool utlu112i sql

Posted by Zahid on July 11, 2010.

Oracle 11g Release 2 ships a script utlu112i.sql aka Pre-Upgrade tool. This script can be found in the ORACLE_HOME/rdbms/admin directory. This script checks the current database which you are trying to upgrade to 11gR2 and display a report, if there are any changes need to be done before and after the upgrade. Besides its reporting nature, it is mandatory to run before starting the upgrade itself. If you did not run the Pre-Upgrade Information Tool, the catupgrd.sql (upgrade script) script will terminate with one of the following errors:

ORA-00942: table or view does not exist
ORA-00904: "TZ_VERSION": invalid identifier
ORA-01722: invalid number
If you receive any of these errors, issue the SHUTDOWN ABORT statement, revert to the original Oracle home directory, and run the Pre-Upgrade Information Tool (utlu112i.sql) again.

How to run Oracle 11gR2 Pre-Upgrade tool (utlu112i.sql)

To run the Pre-Upgrade tool the environment should be set like this:

$ORACLE_HOME = Oracle Home which you are planning to upgrade (Old Oracle Home).
$ORACLE_SID = SID of the database being upgraded.
$PATH = should point to the original/old Oracle Home.

Copy the script utlu112i.sql from 11gR2 ORACLE_HOME/rdbms/admin to another directory say /tmp, change to that directory and start sqlplus. Run the script and view the output.

cp /u01/apps/oracle/product/11.2.0/db_1/rdbms/admin/utlu112i.sql /tmp
cd /tmp
sqlplus / as sysdba

spool pre_upgrade.log
@utlu112i.sql
spool off

Example output generated by the Pre-Upgrade tool

Oracle Database 11.2 Pre-Upgrade Information Tool    07-11-2010 23:25:25
.
**********************************************************************
Database:
**********************************************************************
--> name:          ORA10g
--> version:       10.2.0.2.0
--> compatible:    10.2.0.2
--> blocksize:     8192
--> platform:      Linux IA (32-bit)
--> timezone file: V4
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
WARNING: --> SYSTEM tablespace is not large enough for the upgrade.
.... currently allocated size: 560 MB
.... minimum required size: 910 MB
.... increase current size by: 350 MB
.... tablespace is NOT AUTOEXTEND ENABLED.
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 457 MB
.... AUTOEXTEND additional space required: 352 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 617 MB
.... AUTOEXTEND additional space required: 287 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 61 MB
.... AUTOEXTEND additional space required: 41 MB
--> EXAMPLE tablespace is adequate for the upgrade.
.... minimum required size: 69 MB
.
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
WARNING: --> "sga_target" needs to be increased to at least 388 MB
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
WARNING: --> "plsql_compiler_flags" old value was "INTERPRETED";
new name is "plsql_code_type" new value is "INTERPRETED"
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
--> "max_enabled_roles"
--> "remote_os_authent"
--> "background_dump_dest" replaced by "diagnostic_dest"
--> "user_dump_dest" replaced by "diagnostic_dest"
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views         [upgrade]  VALID
--> Oracle Packages and Types    [upgrade]  VALID
--> JServer JAVA Virtual Machine [upgrade]  VALID
--> Oracle XDK for Java          [upgrade]  VALID
--> Oracle Workspace Manager     [upgrade]  VALID
--> Messaging Gateway            [upgrade]  VALID
--> OLAP Analytic Workspace      [upgrade]  VALID
--> OLAP Catalog                 [upgrade]  VALID
--> Oracle Label Security        [upgrade]  VALID
--> EM Repository                [upgrade]  VALID
--> Oracle Text                  [upgrade]  VALID
--> Oracle XML Database          [upgrade]  VALID
--> Oracle Java Packages         [upgrade]  VALID
--> Oracle interMedia            [upgrade]  VALID
--> Spatial                      [upgrade]  VALID
--> Data Mining                  [upgrade]  VALID
--> Expression Filter            [upgrade]  VALID
--> Rule Manager                 [upgrade]  VALID
--> Oracle Application Express   [upgrade]
--> Oracle OLAP API              [upgrade]  VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database is using a timezone file older than version 11.
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the 10.2.0.2.0 database timezone version
.... to the latest version which comes with the new release.
WARNING: --> Database contains stale optimizer statistics.
.... Refer to the 11g Upgrade Guide for instructions to update
.... statistics prior to upgrading the database.
.... Component Schemas with stale statistics:
....   SYS
....   WMSYS
....   CTXSYS
WARNING: --> Database contains INVALID objects prior to upgrade.
.... The list of invalid SYS/SYSTEM objects was written to
.... registry$sys_inv_objs.
.... The list of non-SYS/SYSTEM objects was written to
.... registry$nonsys_inv_objs.
.... Use utluiobj.sql after the upgrade to identify any new invalid
.... objects due to the upgrade.
.... USER PUBLIC has 7 INVALID objects.
.... USER FLOWS_010600 has 1 INVALID objects.
.... USER SYS has 1 INVALID objects.
WARNING: --> Database contains schemas with objects dependent on network packages.
.... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
.... USER WKSYS has dependent objects.
.... USER SYSMAN has dependent objects.
.... USER FLOWS_010600 has dependent objects.
WARNING: --> EM Database Control Repository exists in the database.
.... Direct downgrade of EM Database Control is not supported. Refer to the
.... 11g Upgrade Guide for instructions to save the EM data prior to upgrade.
.
WARNING: --> There are materialized view refreshes in progress.
.... Ensure all materialized view refreshes are complete prior to upgrade.
WARNING: --> There are files which need media recovery.
.... Ensure no files need media recovery prior to upgrade.
WARNING: --> There are files in backup mode.
.... Ensure no files are in backup mode prior to upgrade.
WARNING:--> There are outstanding unresolved distributed transactions.
.... Resolve outstanding distributed transactions prior to upgrade.
WARNING:--> A standby database exists.
.... Sync standby database prior to upgrade.
WARNING: --> log_archive_format must be updated.
.... As of 10.1, log_archive_format requires a %r format qualifier
.... be present in its format string.  Your current setting is:
.... log_archive_format='%t_%s.dbf'.
.... Archive Logging is currently OFF, but failure to add the %r to the
.... format string will still prevent the upgraded database from starting up. 
WARNING:--> recycle bin in use.
.... Your recycle bin is turned on and it contains
.... 3 object(s).  It is REQUIRED
.... that the recycle bin is empty prior to upgrading
.... your database.
.... The command:  PURGE DBA_RECYCLEBIN
.... must be executed immediately prior to executing your upgrade.
.

The details about each section of the above output is as below.

Database

This is a general information about the database. For example, name of the database, compatible parameter value, block size and timezone file version. If any change required in these it will be showed here. In above output no change is required.

Tablespaces

This section shows if the tablespaces are sized properly for the upgrade. If a tablespace is undersized a warning will be shown. As you can see in the above report, the system tablespace is not large enough. So increase the size of the system tablespace before starting the upgrade.

Update Parameters

This section shows any parameters that are required to be changed before the upgrade. You just have to change the value to whatever is recommended in this section.

Renamed Parameters

In this section you will see any parameter whose name is changed in the new release. Just remove the old parameter and add the new in the pfile in 11g Oracle Home before starting the upgrade.

Obsolete/Deprecated Parameters

Any parameter that is no more in the new release will be reported in this section. Just remove them from the pfile in 11g Oracle Home before starting the upgrade.

Components

This is a list of components that will be upgraded or installed during the upgrade process.

Miscellaneous Warnings

These are a bunch of warnings about the current database. If you ignore them they may not fail the upgrade process itself, but they may make the upgrade to run slower and may effect some of Oracle features that you use. You need to take care of some of these warnings prior to the upgrade and some after the upgrade.

Lets see the warnings that we need to take care of before starting the upgrade process.

Connect Role

If there are any privileges other then CREATE SESSION granted to Connect Role, the Oracle 11g upgrade will revoke it. So prior to upgrading separately grant those privileges to the all grantees of Connect role.

The following PL/SQL block separately grants all privileges to all grantees of CONNECT tole that are granted to CONNECT role except CREATE SESSION.

SPOOL adjust_connect_role.log

DECLARE
 CURSOR c1 IS
 SELECT grantee 
 FROM dba_role_privs
 WHERE  granted_role = 'CONNECT'
 AND    grantee
        NOT IN
        (
        'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
        'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
        'ORDPLUGINS',  'OEM_MONITOR', 'WKSYS', 'WKPROXY',                
        'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
        'WMSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
        'SI_INFORMTN_SCHEMA', 'XDB', 'ODM'
        );
 CURSOR c2 IS
 SELECT privilege
 FROM   role_sys_privs
 WHERE  role = 'CONNECT'
 AND    privilege != 'CREATE SESSION';
BEGIN
 dbms_output.enable(10000);
 FOR r1 IN c1 LOOP
    FOR r2 IN c2 LOOP
       DBMS_OUTPUT.PUT_LINE('GRANT '||r2.privilege||' TO '||r1.grantee);
       BEGIN
         EXECUTE IMMEDIATE 'GRANT '||r2.privilege||' TO '||r1.grantee;
         DBMS_OUTPUT.PUT_LINE(chr(10)||'Grant succeeded.'||chr(10));
       EXCEPTION
         WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE(chr(10)||'Error: '||sqlerrm||chr(10));
       END;
    END LOOP;
 END LOOP;
 FOR r2 IN c2 LOOP
    DBMS_OUTPUT.PUT_LINE('REVOKE '||r2.privilege||' FROM connect');
    BEGIN
         EXECUTE IMMEDIATE 'REVOKE '||r2.privilege||' FROM CONNECT';
         DBMS_OUTPUT.PUT_LINE(chr(10)||'Revoke succeeded.'||chr(10));
       EXCEPTION
         WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE(chr(10)||'Error: '||sqlerrm||chr(10));
       END;
 END LOOP;
END;
/

SPOOL OFF

Stale Optimizer Statistics

If the current database contains objects with stale optimizer statistics, then its better to collect the statistics for them before starting the upgrade. If you ignore this warning then your upgrade process will be slower depending upon the number of objects with stale optimizer statistics.

SPOOL gather_statitics.log

GRANT ANALYZE ANY TO SYS;

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

EXEC DBMS_STATS.GATHER_SCHEMA_STATS
	( 
	  'WMSYS',
	  options=>'GATHER', 
	  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
	  method_opt => 'FOR ALL COLUMNS SIZE AUTO', 
	  cascade => TRUE
	);


EXEC DBMS_STATS.GATHER_SCHEMA_STATS
	( 'CTXSYS',
	  options=>'GATHER', 
	  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, 
	  method_opt => 'FOR ALL COLUMNS SIZE AUTO', 
	  cascade => TRUE
	);

EXEC DBMS_STATS.GATHER_SCHEMA_STATS
	( 'SYS',
	  options=>'GATHER', 
	  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, 
	  method_opt => 'FOR ALL COLUMNS SIZE AUTO', 
	  cascade => TRUE
	);

SPOOL OFF

The above mentioned routines will collect the optimizer statistics for the users reported in the Pre-Upgrade tool with stale optimizer statistics.

Save EM data

You my ignore this, but keep in mind that if later on you decide to downgrade from 11g to 10g then direct downgrade of EM is not supported. So its better to save the EM data and restore it back in case of a downgrade.

The environment should be setup to OLD oracle HOME i.e.

ORACLE_HOME => 10g HOME
$PATH, LD_LIBRARY_PATH and SHLIB_PATH => oracle 10g HOME
$ORACLE_SID = oracle 10g database sid.

mkdir -p /u01/oem_backup
cd to 11g ORACLE_HOME/bin

$ ./emdwgrd -save -sid ora10g -path /u01/oem_backup
Enter sys password for database ora10g?

Sun Jul 11 13:58:52 2010 - Verify EM DB Control files ... pass
Sun Jul 11 13:58:52 2010 - Validating DB Connection to ora10g ... pass
ENV var EM_REMCP not defined, check if rcp or scp is configured. 
RCP = /usr/bin/rcp -rp, REMSH = /usr/bin/rsh 
shared = 0 
Sun Jul 11 13:58:57 2010 - Creating directory ... created
Sun Jul 11 13:58:57 2010 - Stopping DB Control ... stopped
Sun Jul 11 13:59:02 2010 - Saving DB Control files 
 ... saved
Sun Jul 11 14:00:09 2010 - Recompiling invalid objects ... recompiled
Sun Jul 11 14:00:29 2010 - Exporting sysman schema for ora10g ... exported
Sun Jul 11 14:01:28 2010 - DB Control was saved successfully.
Sun Jul 11 14:01:28 2010 - Starting DB Control ... started
Sun Jul 11 14:03:05 2010 - Dump directory was dropped successfully.

Materialized View Refreshes

If there are currently materialized view refreshes are going on then let them first complete and then start the upgrade.

The following query will show if there are any refresh on materialized views going on.

SELECT DISTINCT(TRUNC(last_refresh))
FROM dba_snapshot_refresh_times;

Files need media recovery

Make sure there is no file which needs media recovery. If you see this warning use the following query to identify the file which needs media recovery and perform media recovery for it.

SELECT * FROM v$recover_file;

Files in backup mode

Make sure there is no file in backup mode. If you see this warning use the following query to identify the file which are in backup mode and issue end backup for those files.

SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';

Unresolved distributed transactions

If you see this warning that means there are some outstanding unresolved distributed transactions. You must resolve them before starting the upgrade.

SELECT * FROM dba_2pc_pending;

--If this query returns any rows, then issue the following statements:

SQL> SELECT local_tran_id FROM dba_2pc_pending;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry('');
SQL> COMMIT;

Standby database exists

You will see this warning if you have a standby database. You need to sync the standby database with the primary database before proceeding with the upgrade.

Log Archive Format

If you see this warning that means the initialization parameter log_archive_format doesn't have %r qualifier in it. The upgraded database won't start unless you add %r in the value of log_archive_format.

Recyclebin

If your recyclebin contains dropped objects, you will see this warning. Just empty the recyclebin before the upgrade.

PURGE DBA_RECYCLEBIN;

With purging recyclebin fixing the current database for 11g upgrade is complete. Now is the time to start the upgrade process. Once the upgrade is complete them we need to perform the following as suggested by the Pre-Upgrade tool.

Access Control to Network Utility Packages

From 11g the built-in packages which access the network resources e.g. UTL_HTTP, UTL_SMTP, UTL_MAIL etc. now requires an access control list to be used. If you see this warning that means there are some objects in your database which are using one of these packages. Once the upgrade is complete you need to configure an Access Control List for the users who are using the packages otherwise your applications will fail.

/* 
  To see if there are any objects depending upon network packages like UTL_TCP , 
  UTL_SMTP etc.
*/
SELECT owner , name , type , referenced_name FROM DBA_DEPENDENCIES
WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDR')
  AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS');

If there are any found then use this routine to create ACL's for these users.

DECLARE
  ACL_PATH  VARCHAR2(4000);
  CURSOR C1 IS
  SELECT distinct owner FROM DBA_DEPENDENCIES
  WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDR')
  AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS');
BEGIN
  FOR R1 IN C1 LOOP
  BEGIN
  SELECT acl INTO acl_path FROM dba_network_acls
  WHERE host = 'host_name' AND lower_port IS NULL AND upper_port IS NULL;
	IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(acl_path, 
                                         r1.owner,'connect') IS NULL THEN
		DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl_path,
                                         r1.owner, TRUE, 'connect');
	END IF;
  EXCEPTION
     WHEN no_data_found THEN
       DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('myACL.xml',
                                         'ACL for network packages',
                                         r1.owner, 
                                         TRUE, 
                                         'connect');
       DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('myACL.xml','host_name');
END;
COMMIT;
END LOOP;
END;
/

The host_name in this routine is the name of the network resource being accessed by the package. For example for UTL_MAIL it may be your mail server, and for UTL_HTTP it may be the address of the website to which UTL_HTTP connects.

For further details on Access Control Lists please visit the link below.
Oracle 11g Access Control List for External Network Services

Invalid Objects

This is a warning that there are invalid objects in the database and are stored in two tables registry$sys_inv_objs and registry$nonsys_inv_objs. Once the upgrade is complete run the script utluiobj.sql in 11g $ORACLE_HOME/rdbms/admin to see if the upgrade caused any other objects to be invalid.

SQL> @utluiobj.sql
.
Oracle Database 11.1 Post-Upgrade Invalid Objects Tool 07-11-2010 19:13:45
.
This tool lists post-upgrade invalid objects that were not invalid
prior to upgrade (it ignores pre-existing pre-upgrade invalid objects).
.
Owner                     Object Name                     Object Type
.

PL/SQL procedure successfully completed.

Make sure there is nothing reported by this script.

Using DBMS_DST package to upgrade the timezone file version

If you see a warning stating that your current timezone file version is lower then 11, you need to upgrade it to version 11 if you are using data with timezone information or you have plans to use timezone data in future.

Oracle 11g provides a built-in package DBMS_DST that can be used to evaluate the current timezone data before the timezone file version upgrade and does the upgrade of timezone file version as well.

The DBMS_DST package helps performing these tasks by creating a prepare window and upgrade window.

DBMS_DST prepare window

Use this window to validate the current timezone data that you have. How will it be affected by the timezone file version upgrade? Are there gonna be some errors during the upgrade?
(Only user prepare window if you are currently using data with timezone)

$ sqlplus / as sysdba
SQL> startup
SQL> set serveroutput on
SQL> 
SQL> EXEC DBMS_DST.BEGIN_PREPARE(11);
A prepare window has been successfully started.

PL/SQL procedure successfully completed.

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         4
DST_SECONDARY_TT_VERSION       11
DST_UPGRADE_STATE              PREPARE

/*
 The above query shows the primary and secondary timezone file version and the Upgrade 
 state of the timezone file.
*/

SQL> EXEC DBMS_DST.CREATE_AFFECTED_TABLE('my_affected_tables');
SYS.MY_AFFECTED_TABLES has been successfully created.

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_DST.CREATE_ERROR_TABLE('my_error_table');
SYS.MY_ERROR_TABLE has been successfully created.

PL/SQL procedure successfully completed.

/* 
 Two tables have been created to record the affected data and and any errors that may 
 arise during the upgrade.
*/

SQL> truncate table my_affected_tables;

Table truncated.

SQL> truncate table my_error_table;

Table truncated.

-- Empty out the tables.

begin 
    DBMS_DST.FIND_AFFECTED_TABLES
     ( 
      affected_tables  => 'my_affected_tables',
      log_errors       => TRUE,
      log_errors_table => 'my_error_table'
     ); 
end;
/

PL/SQL procedure successfully completed.

/*
 The DBMS_DST.FIND_AFFECTED_TABLES will fill up the affected timezone data into 
 affected_table and any errors in the log_error_table.
*/

SQL> SELECT * FROM my_affected_tables;

no rows selected

SQL> SELECT * FROM my_error_table;

no rows selected

/*
 If you will have some affected timezone data and if there are any errors you may review 
 them by reading the affected_table and error_table.
*/

SQL> EXEC DBMS_DST.END_PREPARE;
A prepare window has been successfully ended.

PL/SQL procedure successfully completed.

-- End the prepare window.

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

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

-- The UPGRADE_STATE has become NONE and secondary timezone file version is 0.

Please look at the link below to see how to handle errors in current timezone data reported by the prepare window.
Error Handling when Upgrading Time Zone File and Timestamp with Time Zone Data

DBMS_DST upgrade window

Now is the time to upgrade the database timezone file version to 11. For that we will use the DBMS_DST upgrade window. This will be done by starting up the database in upgrade mode.

SQL> shutdown immediate

SQL> startup upgrade

SQL> EXEC DBMS_DST.BEGIN_UPGRADE(11);

PL/SQL procedure successfully completed.

-- Start the DBMS_DST upgrade window.

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         11
DST_SECONDARY_TT_VERSION       4
DST_UPGRADE_STATE              UPGRADE

--Primary timezone file version is now 11 and secondary is 4. The upgrade state is UPGRADE.

SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES;

OWNER                          TABLE_NAME                     UPG
------------------------------ ------------------------------ ---
SYS                            AQ$_AQ_PROP_TABLE_S            NO
.
/* A few more objects with UPGRADE_IN_PROGRESS=NO */
.
IX                             AQ$_ORDERS_QUEUETABLE_L        YES

/* 
  Most of the dictionary tables are upgraded with the DBMS_DST.BEGIN_UPGRADE call. 
  The tables that are not yet upgraded have a "YES" value in the UPGRADE_IN_PROGRESS column
  in the above query.
  To upgrade these tables we need to call DBMS_DST.UPGRADE_DATABASE. And if you don't you
  cannot end the DBMS_DST upgrade window. And you cannot call it while staying in the 
  upgrade mode. Please shutdown the database and start it up normally.
*/

shutdown immediate

startup

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     => TRUE,
    error_on_nonexisting_time => TRUE);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/
Failures: 0
PL/SQL procedure successfully completed.

SQL> print :numfail

   NUMFAIL
----------
         0

/* If numfail is greater then 0 DBMS_DST.END_UPGRADE will fail. */

BEGIN
  DBMS_DST.END_UPGRADE(:numfail);
END;
/

PL/SQL procedure successfully completed.

-- The upgrade window is ended.

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         11
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

The timezone file version is upgraded to 11 now.

For more details about handling the timezone file warning see the link below:
Fixing the Time Zone File Version