Oracle Database Upgrade – 11gR1 (11.1.0.6) to 11gR2 (11.2.0.1)

Oracle Database Upgrade - 11gR1 (11.1.0.6) to 11gR2 (11.2.0.1)

11.1.0.1.0

11 - Major database release number
 1 - Database maintenance release number
 0 - Application server release number
 1 - Component specific release number
 0 - Platform specific release number   

http://www.oracle.com/technology/software/products/database/index.html

1. Make sure the source environment is in stable state:
    11.1.0.6 Startup Database
    Alert log location: $ORACLE_BASE/diag/rdbms/aitmr93w/aitmr93w/trace

2. Download the 11gR2 software into the stage from where it needs to be installed.

3. Oracle Database Pre-installation Requirements

Checking the Hardware Requirements:-

The system must meet the following minimum hardware requirements:

1. Memory Requirements
    1. At least 1 GB of RAM
    # grep MemTotal /proc/meminfo

    2. Determine the size of the configured swap space
    # grep SwapTotal /proc/meminfo

    The following table describes the relationship between installed RAM and the configured swap space requirement:
    |-------------------------------------------------------|
    |RAM                                     |   Swap Space                         |
    |-------------------------------------------------------|
    |Between 1 GB and 2 GB    |   1.5 times the size of RAM |
    |Between 2 GB and 16 GB  |   Equal to the size of RAM  |
    |More than 16 GB                |   16 GB                                   |
    |-------------------------------------------------------|

    3. To determine the available RAM and swap space, enter the following command:
    # free

2. System Architecture
    # uname -m

3. Disk Space Requirements
    1. At least 1 GB of disk space in the /tmp directory
    # df -h /tmp

    If the free disk space available in the /tmp directory is less than what is required,
    then complete one of the following steps:

    a) Delete unnecessary files from the /tmp directory to meet the disk space
    requirement.
    b) Set the TMP and TMPDIR environment variables when setting the oracle
    user’s environment.

    2. To determine the amount of free disk space on the system, enter the following command:
    # df -h

    The following tables describe the disk space requirements for software files,
    and data files for each installation type on Linux x86:

    Installation Type     Requirement for Software Files (GB)
    Enterprise Edition     3.95
    Standard Edition     3.88

    Installation Type     Disk Space for Data Files (GB)
    Enterprise Edition     1.7
    Standard Edition     1.5

Checking the Software Requirements:-

1. Operating System & Kernel Requirements
    Oracle Enterprise Linux 4 Update 7
    Oracle Enterprise Linux 5 Update 2

    To determine the distribution and version of Linux installed, enter the following command:
    # cat /proc/version
    Alternatively, you can also enter the following command on some distributions of Linux:
    # lsb_release -id

    Oracle Enterprise Linux 4, and Red Hat Enterprise Linux 4:
    2.6.9 or later
    Oracle Enterprise Linux 5, and Red Hat Enterprise Linux 5:
    2.6.18 or later

    # uname -r

2. Package Requirements
    On Linux x86: Package Requirements
    rpm -q binutils-2.15.92.0.2
    rpm -q compat-libstdc++-33-3.2.3
    rpm -q elfutils-libelf-0.97
    rpm -q elfutils-libelf-devel-0.97
    rpm -q gcc-3.4.6
    rpm -q gcc-c++-3.4.6
    rpm -q glibc-2.3.4-2.41
    rpm -q glibc-common-2.3.4
    rpm -q glibc-devel-2.3.4
    rpm -q glibc-headers-2.3.4
    rpm -q libaio-devel-0.3.105
    rpm -q libaio-0.3.105
    rpm -q libgcc-3.4.6
    rpm -q libstdc++-3.4.6
    rpm -q libstdc++-devel-3.4.6
    rpm -q make-3.80
    rpm -q numactl-0.6.4.i386
    rpm -q pdksh-5.2.14
    rpm -q sysstat-5.0.5
    rpm -q unixODBC-2.2.11
    rpm -q unixODBC-devel-2.2.11

3. Compiler Requirements
    Linux-PAM Library
    Install the latest Linux-PAM (Pluggable Authentication Modules for Linux) library to
    enable the system administrator to choose how applications authenticate users.

    Setting the ORACLE_HOSTNAME Environment Variable
    In Bourne, Bash, or Korn shell:
    $ ORACLE_HOSTNAME=somehost.us.example.com
    $ export ORACLE_HOSTNAME

4. Install 11gR2 (11.2.0.1) Software only

    4.1 Entry into the "oratab" ...
    /etc/oratab -- <ORACLE_SID>:<ORACLE_BASE>/db/11.1:N

    Note: Kindly use local inventory than central as we wont be having access to use central

    4.2 ./runInstaller -invPtrLoc $ORACLE_HOME/oraInst.loc &
    Where the oraInst.loc should have the below entries
    [ For installation on ems6678 otherwise modify it accordingly]

    inst_group=dba;
    inventory_loc=$ORACLE_BASE/db/oraInventory

    Note: Click on "Ignore All" if there are any swap space failures

    Installation Log: $ORACLE_BASE/db/oraInventory/logs/installActions2010-05-06_11-55-10PM.log

5. Pre-Upgrade steps:

    5.1 Executing Pre-Upgrade Tool:
    Before you start the upgrade process, to identify these checks.
        5.1.1 Copy utlu112i.sql from 11gR2 OH i.e $ORACLE_BASE/db/11.2/rdbms/admin
        to $HOME [ Assuming 11gR2 is Installed $ORACLE_BASE/db/11.2 ]
        5.2.2 Login as SYSDBA
        SQL> SPOOL $HOME/UpgDir/upgrade_info.log
        SQL> @utlu112i.sql
        SQL> SPOOL OFF

    Review the log file and take necesary actions. Then continue with upgrade.

    5.2 Correcting the Warnings [if any] found in above step

        5.2.1 WARNING : Database is using an old time zone file version. After the upgrade,
        patch the database time zone file version using DBMS_DST package to record latest time zone file version.
        Sol: From Webiv Note: 815679.1
        * For 11.1.0.6 and 11.1.0.7, after the upgrade to 11.2.0.1
        use DBSM_DST to update to DSTv11 by following <<Note 977512.1>>
        Updating the RDBMS DST version in 11gR2 (11.2.0.1 and up) using DBMS_DST

        5.2.2 Database contains stale optimizer statistics.
        EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
        [OR]
        EXEC DBMS_STATS.DELETE_SCHEMA_STATS('SYS');

        5.2.3 WARNING: --> SYSTEM schema default tablespace has been altered.
        .... The SYSTEM schema default tablespace is currently set to TOOLS.
        .... Prior to upgrading your database please reset the
        .... SYSTEM schema default tablespace to SYSTEM  using the command:
        .... ALTER USER SYSTEM DEFAULT TABLESPACE SYSTEM;
        5.2.4. 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.

    5.3 Shutdown 11gR1 Database

    5.4 Configuring 11gR2 Instance
        5.4.1 Source 11gR2 instance
        5.4.2 Copy parameter file form pfile location to 11gR2/dbs
            Change Parameters
            ------------------------
            *.compatible='11.2.0'
            shared_pool_size increase to 298MB [as suggested -- 325058560]
            user_dump_dest        -- REMOVE
            background_dump_dest  -- REMOVE
            core_dump_dest        -- REMOVE

6. Upgrading Database

    6.1 Prepare Upgrade
        6.1.1 cd $ORACLE_HOME/rdbms/admin
        6.1.2 Login as SYSDBA
            sqlplus '/ as sysdba'
            startup upgrade
            SPOOL upgrade.log
            @catupgrd.sql

7. Post-Upgrade steps:

    7.1 Post-Upgrade Tool
    Run utlu112s.sql to display the results of the upgrade:
        SQL> SPOOL $HOME/UpgDir/postupgrade_status.log
        SQL> @utlu112s.sql

        Oracle Database 11.2 Post-Upgrade Status Tool           05-07-2010 01:08:37
        Component                                Status         Version  HH:MM:SS
        Oracle Server                            VALID      11.2.0.1.0  00:14:38
        Oracle Text                              VALID      11.2.0.1.0  00:00:36
        Gathering Statistics                                            00:02:59
        Total Upgrade Time: 00:18:15

    PL/SQL procedure successfully completed.

    7.2 Post-Upgrade Actions
    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> SPOOL $HOME/UpgDir/postupgrade_status.log
        SQL> @catuppst.sql

    7.3 Compiling Invalid Objects:
    Compiling Ivalid objects:
        SQL> SPOOL $HOME/UpgDir/Compile_Invalid.log
        SQL> @utlrp.sql
        SQL> SPOOL OFF

    7.4 Updating the RDBMS DST version in 11gR2 (11.2.0.1 and up) -- Webiv Note 977512.1
    Oracle 11.2.0.1 has by default all RDBMS DST updates from DSTv1 to DSTv11 included in the software installation.
    These files are found in $ORACLE_HOME/oracore/zoneinfo and have a prefix indicating the DST version.

        7.4.1 check current RDBMS DST version and "DST UPGRADE STATUS".
            conn / as sysdba
            SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
            FROM DATABASE_PROPERTIES
            WHERE PROPERTY_NAME LIKE 'DST_%'
            ORDER BY PROPERTY_NAME;

            Check that the output gives

            -- PROPERTY_NAME VALUE
            -- ------------------------------ ------------------------------
            -- DST_PRIMARY_TT_VERSION <the old DST version number>
            -- DST_SECONDARY_TT_VERSION 0
            -- DST_UPGRADE_STATE NONE

            -- DST_PRIMARY_TT_VERSION should match the value found when selecting

        7.4.2 SELECT version FROM v$timezone_file;
            VERSION
            ----------
            4

        7.4.3 Do the actual RDBMS DST version update of the database
            7.4.3.1 Change DB into Upgrade Mode
                conn / as sysdba
                shutdown immediate;
                startup upgrade;
                set serveroutput on
                purge dba_recyclebin;

            7.4.3.2 Clean used tables
                TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
                TRUNCATE TABLE sys.dst$affected_tables;
                TRUNCATE TABLE sys.dst$error_table;

            7.4.3.3 start upgrade window
                EXEC DBMS_DST.BEGIN_UPGRADE(<the new DST version number>);
                SQL> EXEC DBMS_DST.BEGIN_UPGRADE(11);

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

                SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';

            7.4.3.4 Restart the Database
                shutdown immediate
                startup
                set serveroutput on

            7.4.3.5 Upgrade the tables who need action
                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 => FALSE,
                error_on_nonexisting_time => FALSE);
                DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
                END;
                /

                VAR fail number
                BEGIN
                DBMS_DST.END_UPGRADE(:fail);
                DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
                END;
                /

            7.4.3.6 Verify the Timezone Upgrade
                SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
                FROM DATABASE_PROPERTIES
                WHERE PROPERTY_NAME LIKE 'DST_%'
                ORDER BY PROPERTY_NAME;

                SELECT * FROM v$timezone_file;

8. Miscellaneous Database Checkups
    8.1 Database, Datafile and Backup

        select name,open_mode,log_mode,database_role,force_logging,flashback_on from v$database;
        select * from v$recover_file;
        select distinct status,count(*) from v$datafile group by status;
        select name, RECOVER from v$datafile_header where RECOVER != 'NO';
        select name, ERROR from v$datafile_header where ERROR is NOT NULL;
        select name, ts# from v$datafile where upper(name) like '%MISSING%';
        select NAME, FILE# from v$datafile where status='RECOVER';
        select status,count(*) from v$backup group by status;

    8.2 DBA Registry

        set lines 150 pages 500
        column COMP_NAME format a45
        column version format a15
        column status format a12
        select comp_name,version,status from dba_registry;

    8.3 DBA Registry History

        set lines 150 pages 500
        column action format a15
        column namespace format a15
        column comments format a20  wrap
        column ACTION_TIME format a30
        col BUNDLE_SERIES format a12
        column version format a15
        select * from dba_registry_history;

The above information is with respect to my first DB Upgrade.

Regards,
Raj.