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
Oracle 11g Case Sensitive Passwords Upgrade Oracle 10g Release 2 to Oracle 11g Release 1

Oracle 11g Release 1 Pre Upgrade tool utlu111i sql

Posted by Zahid on July 28, 2010.

While upgrading an oracle database to 11g Release 1, you should analyze your database before upgrading it. Oracle 11g Release 1 ships a script utlu111i.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 11gR1 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 the Pre-Upgrade Script if you are planning to upgrade the database manually.

How to run Oracle 11gR1 Pre-Upgrade tool (utlu111i.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 utlu111i.sql from 11gR1 ORACLE_HOME/rdbms/admin to another directory say /tmp, move to that directory and start sqlplus. Run the script and view the output.

cp /u01/app/oracle/product/11.1.0/db_1/rdbms/admin/utlu111i.sql /tmp
cd /tmp
sqlplus / as sysdba

spool pre_upgrade.log
@utlu111i.sql
spool off

Example output generated by the Pre-Upgrade tool

Oracle Database 11.1 Pre-Upgrade Information Tool    07-15-2010 09:14:02
.
**********************************************************************
Database:
**********************************************************************
--> name:          ORCL
--> version:       10.2.0.1.0
--> compatible:    10.2.0.1
--> blocksize:     8192
--> platform:      Linux IA (32-bit)
--> timezone file: V2
.
**********************************************************************
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.1 init.ora or spfile]
**********************************************************************
WARNING: --> "sga_target" needs to be increased to at least 388 MB
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.1 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.1 init.ora or spfile]
**********************************************************************
--> "max_enabled_roles"
--> "remote_os_authent"
--> "background_dump_dest" replaced by "diagnostic_dest"
--> "user_dump_dest" replaced by "diagnostic_dest"
--> "core_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
--> Oracle Ultra Search          [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 an old timezone file version.
.... Patch the 10.2.0.1.0 database to timezone file version 4
.... BEFORE upgrading the database. Re-run utlu111i.sql after
.... patching the database to record the new timezone file version.
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.

Understanding utlu111i.sql output

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 parameter file 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.

Warnings to be fixed before upgrade

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

Timezone File Version

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

Always try to use the official patch for your database version. Sometimes this may happen that there is no official patch for your database version, e.g. if you are using Oracle 10.2.0.1 and want to upgrade the timezone file version to 4, there is no official patchset available for that. In that case you may use the patch 5632264 which is an offcial patchset for 10.2.0.2.
Please follow the metalink note ID 359145.1. to identify which patchset you require.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> select version from v$timezone_file;

   VERSION
----------
         2

I am running Oracle 10.2.0.1.0 and my timezone file version is 2.

Before you upgrade your timezone file version, you must run utltzuv2.sql to see if there is data that will be affected by the timezone file version upgrade. The script utltzuv2.sql creates a table named sys.sys_tzuv2_temptab, and fills it with data that may get affected by the timezone file version.

$ cd $ORACLE_HOME/rdbms/admin/
$ sqlplus / as sysdba

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 29 01:43:31 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> @utltzuv2.sql
DROP TABLE sys.sys_tzuv2_temptab
               *
ERROR at line 1:
ORA-00942: table or view does not exist



Table created.


PL/SQL procedure successfully completed.


Commit complete.

SQL> select * from sys.sys_tzuv2_temptab;   

no rows selected

-- Since I have no timezone data stored in the database so sys.sys_tzuv2_temptab is empty.

But if you see any data reported by the script utltzuv2.sql, then you need to backup that data before the timezone file version upgrade and restore it back after the tz file upgrade.
Visit Upgrading the Time Zone File to see how to backup and restore timezone data before and after the timezone file version upgrade.

Download the patchset for your release version and unzip it.

/* 
  Since I am running 10.2.0.1 so I would use patch p5632264 as suggested 
  by Metalink Note: 359145.1.
*/

$ unzip p5632264_10202_LINUX.zip 
$ cd 5632264
$ ls
etc  files  README.txt
$ cd files/oracore/zoneinfo/
$ ls
readme.txt  timezlrg.dat  timezone.dat

-- Backup the $ORACLE_HOME/oracore/zoneinfo

$ cp -R $ORACLE_HOME/oracore/zoneinfo $ORACLE_HOME/oracore/zoneinfo_backup

/* 
  Now copy the timezone.data and timezlrg.dat files from patchset to 
  $ORACLE_HOME/oracore/zoneinfo directory. 
*/

$ pwd
/home/oracle/5632264/files/oracore/zoneinfo
$ cp timezone.dat timezlrg.dat $ORACLE_HOME/oracore/zoneinfo

-- Now bounce the database.

$ sqlplus / as sysdba

SQL> shutdown immediate

SQL> startup

SQL> select version from v$timezone_file;    

   VERSION
----------
         4

The database timezone file version is now upgraded to version 4. You need to re-run the pre-upgrade tool again to record the new timezone file version.

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 that are granted to CONNECT role, to all grantees of CONNECT role 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;

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

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

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

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?

Wed Jul 28 18:36:47 2010 - Verify EM DB Control files ... pass
Wed Jul 28 18:36:47 2010 - Validating DB Connection to ora10g ... pass
ENV var EM_REMCP not defined, check if rcp or scp is configured. 
Wed Jul 28 18:36:56 2010 - Creating directory ... created
Wed Jul 28 18:36:56 2010 - Stopping DB Control ... stopped
Wed Jul 28 18:37:03 2010 - Saving DB Control files 
 ... saved
Wed Jul 28 18:38:04 2010 - Recompiling invalid objects ... recompiled
Wed Jul 28 18:39:19 2010 - Exporting sysman schema for ora10g ... exported
Wed Jul 28 18:43:15 2010 - DB Control was saved successfully.
Wed Jul 28 18:43:15 2010 - Starting DB Control ... started
Wed Jul 28 18:45:00 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.

Warnings to be fixed after upgrade

Now is the time to start the upgrade process. Once the upgrade is complete then 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 UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, and UTL_INADDR, which access the network resources now require 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 utlrp.sql to recompile any remaining stored PL/SQL and Java code.

SQL> @utlrp.sql

--Verify that all expected packages and classes are valid

SQL> SELECT count(*) FROM dba_invalid_objects;
SQL> SELECT distinct object_name FROM dba_invalid_objects;