Oracle 11.1.0.7 to 11gR2 Upgrade Steps

1. Download and install the new 11gR2 Oracle in the target machine.

2. After installation copy the ORACLE_HOME/rdbms/admin/utlu112i.sql to some /tmp location as the 

script needs to be executed in the 11.1.0.7 box as pre-requisite step.

Pre-requisite Upgrade Steps in the source(11.1.0.7) box

1. Run the utlu112i.sql script which was copied to /tmp location in the earlier steps.

2. If the script was not run before the upgrade then the upgrade will fail while running the catupgrd.sql.

    Common error which we will face when the above script was not run before the upgrade will be

SQL> SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')
2 FROM registry$database
3 WHERE tz_version != (SELECT version from v$timezone_file);
SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')
*
ERROR at line 1:
ORA-01722: invalid number

3.Login as / as sysdba and run the below commands

      sql> spool upgrade_pre.log

      sql>@utlu112i,sql

      sql>spool off

4. The above spool file will validate whether the upgrade requirements are met or not like it will display the tablespace info,update parameters,deprecated parameters...etc.

5. Run the /rdbms/admin/utlrp.sql to validate invalidate objects.

6. For 11.1.0.7 no need to apply any DST related patches and we can take after the db upgrade.

7. Run the DBMS_STATS.GATHER_DICTIONARY_STATS.

  

     EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

8. No files should be in backup mode.

    sql>select * from v$recover_file;

    sql>select * from v$backup where status != 'NOT ACTIVE';

9. The users SYS and SYSTEM have 'SYSTEM' as their default tablespace.

10. Shutdown the db and take the cold backup or backup using rman.

11. Currently taken the cold backup like copied all the redo logs,datafiles,temp files,control files...to the target db.

Upgrade Steps in Target

1. Set the ENV in the target box(11gR2).

2. Currently copied the init.ora and modified according to the target db.

3. login as / as sysdba

    1. Startup nomount

    2. Recreate the Control file (If the target box is new)

    3. alter database open resetlogs upgrade;

    4. spool upgrade.info

     5.@/rdbms/admin/catupgrd.sql

     6. spool off

12. Will discuss the DST settings in the next blog.