11G Upgrade‎ > ‎

Upgrading Oracle database from 10.2.0.4.0 to 11.2.0.1.0


Pre Upgrade


STEP 1: Install The Software:

Download Oracle Database 11gR2 software . Execute runInstaller to install “SOFTWARE ONLY” option. Make sure that you select “software only” option.


STEP 2: Run Pre-Upgrade Information tool

I have installed the software under “/u01/app/oracle/product/11.2.0/dbhome_1″ location. Once the software is installed, then go to location $ORACLE_HOME/rdbms/admin and copy utlu112i.sql script to /tmp directory. Now login to 10g database “/ as sysdba” and startup the 10g database

va-idb01:RPTDB:/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin $

va-idb01:RPTDB:/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin $ls utlu*.sql
utlu112i.sql  utlu112s.sql  utlu112x.sql  utluiobj.sql  utlurl.sql    utlusts.sql


va-idb01:RPTDB:/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin $cp utlu112i.sql /tmp

va-idb01:RPTDB:/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin $
SQL > spool /tmp/upgrade.spl
SQL > @/tmp/utlu112i.sql
SQL > spool off

va-idb01:RPTDB:/u01/home/oracle $sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Apr 15 13:36:22 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> spool /tmp/upgrade.spl

SQL> SQL> @/tmp/utlu112i.sql

Oracle Database 11.2 Pre-Upgrade Information Tool    04-15-2011 13:36:49
.
**********************************************************************
Database:
**********************************************************************
--> name:          RPTDB
--> version:       10.2.0.4.0
--> compatible:    10.2.0.3.0
--> blocksize:     8192
--> platform:      AIX-Based Systems (64-bit)
--> timezone file: V4
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 2389 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 2290 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 61 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No update parameter changes are required.
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No obsolete parameters found. No changes are required
.
**********************************************************************
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
--> Oracle XML Database          [upgrade]  VALID
--> Oracle Java Packages         [upgrade]  VALID
--> Oracle interMedia            [upgrade]  VALID
--> Expression Filter            [upgrade]  VALID
--> Rule Manager                 [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.4.0 database timezone version
.... to the latest version which comes with the new release.
WARNING: --> Database contains schemas with stale optimizer statistics.
.... Refer to the Upgrade Guide for instructions to update
.... schema statistics prior to upgrading the database.
.... Component Schemas with stale statistics:
....   SYS
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 SYSTEM has 1 INVALID objects.
.... USER PUBLIC has 1 INVALID objects.
.... USER SYS has 3 INVALID objects.
.... USER MDSYS has 2 INVALID objects.
.... USER CDR_VIEW has 2 INVALID objects.
.... USER CDRVIEWBAK has 4 INVALID objects.
.... USER ADMN has 1 INVALID objects.
.... USER CDRVIEWAPP has 4 INVALID objects.
.... USER TTTAPP has 2 INVALID objects.
.... USER TTTBACK has 3 INVALID objects.
WARNING: --> Database contains schemas with objects dependent on network
packages.
.... Refer to the Upgrade Guide for instructions to configure Network ACLs.
.... USER ADMN has dependent objects.
WARNING:--> recycle bin in use.
.... Your recycle bin turned on.
.... 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.
.

PL/SQL procedure successfully completed.

SQL> SQL>

Using DBUA utility

Execute the DBUA from 11gR2 software home as

$ cd $ORACLE_HOME/bin
$./dbua

Post Upgrade:
Upgrade Steps Description of Figure 1-1 follows

Subscribe to posts

Configuring Fine-Grained Access to External Network Services After Upgrading Oracle Database

posted Jun 1, 2012, 2:16 PM by Sachchida Ojha

Oracle Database 11g includes fine-grained access control to the UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, or UTL_INADDR packages using Oracle XML DB. If you have applications that use these packages, then you must install Oracle XML DB if it is not installed. You must also configure network access control lists (ACLs) in the database before these packages can work as they did in earlier releases.

The following example first looks for any ACL currently assigned to host_name. If one is found, then the example grants user_name the CONNECT privilege in the ACL, only if that user does not have this privilege. If no ACL exists for host_name, then the example creates a new ACL called ACL_name, grants the CONNECT privilege to user_name, and assigns the ACL to host_name.
DECLARE
  acl_path  VARCHAR2(4000);
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, 
                                         'user_name','connect') IS NULL THEN
    DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl_path,
                                         'user_name', TRUE, 'connect');
END IF;
EXCEPTION
  WHEN no_data_found THEN
    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('ACL_name.xml',
      'ACL description', 'user_name', TRUE, 'connect');
    DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('ACL_name.xml','host_name');
END;
COMMIT;

1-1 of 1