1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!
reach.santanubanerjee

Upgrading oracle 10g to 11g 2014-02-10

Upgrading oracle 10g to 11g

  1. reach.santanubanerjee
    Step by Step Upgrading Oracle 10g to Oracle 11g
    February 24, 2011 by samadhan
    Recently we did database upgrade from 10g to Oracle 11g.I would like share that activity with you.
    Pre-Requisite:
    You should have the Oracle database 10g, which you want to migerate.
    Also here we are upgrading to Oracle Database 11g – Beta 6 (11.1.0.6)
    Step 1) Installing Oracle 11g Home
    We cannot upgrade the existing Oracle Home, since 11g is not a patchset. We have to install 11g oracle home as a seperate ORACLE_HOME in parallel to 10g Oracle Home.
    Example my 10g Oracle Home is : /u01/app/oracle/oracle/product/10.2.0
    then my 11g Oracel Home is : /u01/app/oracle/oracle/product/11.1.0
    Just a parallel 11.1.0 directory can be created and we can install oracle home in this location.
    Start the installation using the below command
    ./runInstaller -invPtrLoc /u01/app/oracle/oracle/product/11.1.0/oraInst
    Screen 1 – Select Product Install
    select “Oracle Database 11g”
    Screen 2 – Select Installation Method
    Choose “Advanced Installation”
    Screen 3 – Specify Inventory directory and creditials
    Note: We are providing local inventory here inside the corresponding ORACLE_HOME location.
    Screen 4 – Select Installation Type
    Choose “Enterprise Edition”
    Screen 5 – Installation Location
    Oracle Base as parent directory of ORACLE HOME
    Screen 6 – Product Specific Pre-requisite Checks
    It may gives below warning, we can ignore and proceed further
    Screen 7 – Upgrade an Existsing Database
    Choose “No”
    Screen 8 – Select Configuration Option
    Choose “Install Software Only”
    Screen 9 – Privileged system groups
    Based on the group of oracle user, this value has to be set.
    Screen 10 – Summary
    Click on “Install”
    At the end of installation, installer will ask to run root.sh script. Do not press OK button.
    Run root.sh as a root user and once done, press OK button. This will complete the software installation for Oracle Database 11g.
    Step 2) Pre-Upgrade Utility
    In 11g Home you installed, go to $ORACLE_HOME/rdbms/admin and copy the file utlu111i.sql to some temp location.
    [oracle]$ cd $ORACLE_HOME
    [oracle]$ cd rdbms/admin/
    [oracle]$ pwd
    /u01/app/oracle/oracle/product/product/11.1.0/db_1/rdbms/admin
    [oracle]$ cp utlu111i.sql /tmp
    The utility will give the output in the form of recommendations to be implemented before starting the upgrade. Unless these requirements are met, the upgrade will fail.
    Most of the time issue use to come up with time zone….
    Then login to the 10g oracle database and run the above sql you copied.
    Oracle Database 11.1 Pre-Upgrade Information Tool 23-02-2011 01:34:07
    .
    **********************************************************************
    Database:
    **********************************************************************
    –> name: ORCL
    –> version: 10.2.0.1.0
    –> compatible: 10.2.0.1.0
    –>blocksize: 8192
    –> platform: Linux IA (32-bit)
    –>timezone file: V2
    .
    **********************************************************************
    Tablespaces: [make adjustments in the current environment]
    **********************************************************************
    –> SYSTEM tablespace is adequate for the upgrade.
    .
    .
    .
    .
    WARNING: –> Database contains schemas with objects dependent on network
    packages.
    …. Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
    …. USER SYSMAN 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.
    .
    PL/SQL procedure successfully completed.
    The utility will give the output in the form of recommendations to be implemented before starting the upgrade. Unless these requirements are met, the upgrade will fail.
    Step 3) Executing the recommended steps
    Following are the critical steps to be executed based on above warnings. These commands are to be executed while connecting to database from 10g Oracle Home
    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.
    Finding the Version of existing timezone files:
    SQL> select * from v$timezone_file;
    FILENAME VERSION
    ———— ———-
    timezlrg.dat 2
    SQL> SELECT CASE COUNT(DISTINCT(tzname))
    WHEN 183 then 1
    WHEN 355 then 1
    WHEN 347 then 1
    WHEN 377 then 2
    WHEN 186 then case COUNT(tzname) WHEN 636 then 2 WHEN 626 then 3 ELSE 0 end
    WHEN 185 then 3
    WHEN 386 then 3
    WHEN 387 then case COUNT(tzname) WHEN 1438 then 3 ELSE 0 end
    WHEN 391 then case COUNT(tzname) WHEN 1457 then 4 ELSE 0 end
    WHEN 392 then case COUNT(tzname) WHEN 1458 then 4 ELSE 0 end
    WHEN 188 then case COUNT(tzname) WHEN 637 then 4 ELSE 0 end
    WHEN 189 then case COUNT(tzname) WHEN 638 then 4 ELSE 0 end
    ELSE 0 end VERSION
    FROM v$timezone_names;

    VERSION
    ———-
    2
    If the Version of the existing timezone is less than 4, then apply the patch for Version 4 timezone files.
    Check the database version
    SQL> select banner 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
    For 10.2.0.1 check the metalink note ID 413671.1. We have a table which defines the patch to be applied.
    Always try to use the official patch
    The script (and on 10g also the csv file) are normally delivered through installation of a patch in the Oracle home. Please note that before using this note you are advised to double check that the time zone patches are not available for your patchset. Applying the “correct” patch through opatch is always preferable to the manual method described in this note.
    If there is no official patchset for the version you are currently having then you can Identify the utltzuv2.sql & timezdif.csv combination patch for a different patchset, but same release.
    For example if you run 10.2.0.1 and you are trying to find the utltzuv2.sql script & timezdif.csv file you can find the correct patch 5632264 for 10.2.0.2 and this will be applicable to 10.2.0.1 as well.
    Please follow the metalink note ID 396387.1
    Once you identify the correct patchset(5632264 for 10.2.X), download the same and unzip it.
    [oracle]$ unzip p5632264_10202_LINUX.zip
    [oracle]$ ls
    etc files README.txt
    [oracle]$ cd files/oracore/zoneinfo
    [oracle]$ ls
    readme.txt timezlrg.dat timezone.dat
    Backup $ORACLE_HOME/oracore/zoneinfo directory
    [oracle]$ cp -R $ORACLE_HOME/oracore/zoneinfo $ORACLE_HOME/oracore/zoneinfo_backup
    Copy the .dat files
    [oracle]$ cp timezone.dat timezlrg.dat $ORACLE_HOME/oracore/zoneinfo
    Bounce the database and check the TIMEZONE version again
    SQL> select * from v$timezone_file;
    FILENAME VERSION
    ———— ———-
    timezlrg.dat 4
    SQL> SELECT CASE COUNT(DISTINCT(tzname))
    WHEN 183 then 1
    WHEN 355 then 1
    WHEN 347 then 1
    WHEN 377 then 2
    WHEN 186 then case COUNT(tzname) WHEN 636 then 2 WHEN 626 then 3 ELSE 0 end
    WHEN 185 then 3
    WHEN 386 then 3
    WHEN 387 then case COUNT(tzname) WHEN 1438 then 3 ELSE 0 end
    WHEN 391 then case COUNT(tzname) WHEN 1457 then 4 ELSE 0 end
    WHEN 392 then case COUNT(tzname) WHEN 1458 then 4 ELSE 0 end
    WHEN 188 then case COUNT(tzname) WHEN 637 then 4 ELSE 0 end
    WHEN 189 then case COUNT(tzname) WHEN 638 then 4 ELSE 0 end
    ELSE 0 end VERSION
    FROM v$timezone_names;
    VERSION
    ———-
    4
    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
    …. SYSMAN
    Gather Dictionary stats:
    Connect as sys user and gather statistics
    SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
    SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘SYS’);
    PL/SQL procedure successfully completed.
    SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘SYSMAN’);
    PL/SQL procedure successfully completed.
    Step 4) Run Pre-Upgrade Utility again
    After executing the recommended steps, run the pre-upgrade utility once again to make sure, you don’t get any critical warnings.
    Run the pre-upgrade utility script on 10g database while connecting from 10g oracle home.
    If every thing looks fine, Shut down the database from 10g Oracle Home
    This time make sure you dont have the critical warnings like the one with TIMEZONE version.
    Step 5) Starting Upgrade
    Source the following variables for 11g Oracle Home
    [oracle]$ export ORACLE_HOME=/u01/app/oracle/oracle/product/product/11.1.0/db_1
    [oracle]$ export PATH=$ORACLE_HOME/bin:$PATH
    [oracle]$ export ORACLE_SID=orcl
    [oracle]$ export TNS_ADMIN=$ORACLE_HOME/network/admin
    connected to the database sys as sysdba
    sqlplus “/ as sysdba” –> will be connected to idle instance
    SQL> startup upgrade
    ORA-32004: obsolete and/or deprecated parameter(s) specified
    ORACLE instance started.
    Total System Global Area 611000320 bytes
    Fixed Size 1301588 bytes
    Variable Size 201327532 bytes
    Database Buffers 402653184 bytes
    Redo Buffers 5718016 bytes
    Database mounted.
    Database opened.
    SQL> SPOOL upgrade.log
    SQL> @catupgrd.sql
    Once the upgrades finishes. It will shut down the database automatically.
    Login again as sysdba and startup in normal mode.
    Check the dba_registry for the components and its status
    Step 6) Post-Upgrade Steps
    Once the upgrade completes, restart the instance to reinitialize the system parameters for normal operation.
    SQL> STARTUP
    Run utlu111s.sql to display the results of the upgrade:
    SQL> @?/rdbms/admin/utlu111s.sql
    .
    Oracle Database 11.1 Post-Upgrade Status Tool 23-02-2011 05:22:40
    .
    Component Status Version HH:MM:SS
    .
    Oracle Server
    . VALID 11.1.0.6.0 00:19:02
    JServer JAVA Virtual Machine
    . VALID 11.1.0.6.0 00:02:55
    Oracle Workspace Manager
    . VALID 11.1.0.6.0 00:00:54
    OLAP Analytic Workspace
    . VALID 11.1.0.6.0 00:00:26
    OLAP Catalog
    . VALID 11.1.0.6.0 00:00:58
    Oracle OLAP API
    . VALID 11.1.0.6.0 00:00:25
    Oracle Enterprise Manager
    . VALID 11.1.0.6.0 00:11:00
    Oracle XDK
    . VALID 11.1.0.6.0 00:00:53
    Oracle Text
    . VALID 11.1.0.6.0 00:00:50
    Oracle XML Database
    . VALID 11.1.0.6.0 00:03:52
    Oracle Database Java Packages
    . VALID 11.1.0.6.0 00:00:21
    Oracle Multimedia
    . VALID 11.1.0.6.0 00:04:25
    Spatial
    . VALID 11.1.0.6.0 00:05:18
    Oracle Expression Filter
    . VALID 11.1.0.6.0 00:00:13
    Oracle Rules Manager
    . VALID 11.1.0.6.0 00:00:12
    Gathering Statistics
    . 00:04:03
    Total Upgrade Time: 00:55:57
    PL/SQL procedure successfully completed.
    Run catuppst.sql, located in the ORACLE_HOME/rdbms/admin directory, to perform upgrade actions that do not require the database to be in UPGRADE mode:
    SQL> @?/rdbms/admin/catuppst.sql
    Run utlrp.sql to recompile
    SQL> select count(*) from dba_objects
    2 where status = ‘INVALID’;
    COUNT(*)
    ———-
    1576
    SQL> @?/rdbms/admin/utlrp.sql
    SQL> select count(*) from dba_objects
    2 where status = ‘INVALID’;
    COUNT(*)
    ———-
    0
    This completes the upgrade.