Manually upgrade the Database 10g2 (10.2.0.4) to Oracle database 11g2 on Redhat Linux 5

1. Run utlrp.sql to recompile any remaining stored PL/SQL and Java code.

SQL> @utlrp.sql

2. Check dba_registry

column comp_name format a40
set pagesize 50
set line 2000
select trim(comp_name) comp_name , version , status from dba_registry;

3. Verify that all expected packages and classes are valid:

SQL> select count(*) from dba_objects where status <>’VALID’;
SQL>select owner,object_type,count(*) from dba_objects where status <>’VALID’ group by owner,object_type order by 1,2;

4. Backup the database.


5. Analyze the existing instance using the

SQL>@$ORACLE_11G_HOME/rdbms/admin/utlu112i.sql script

6. Shut down the instance:

SQL> SHUTDOWN IMMEDIATE

7. If your operating system is Linux or UNIX, then make the following checks:

a. The oratab file points to your Oracle Database 11g Rel. 2 Oracle home.
b. Your ORACLE_SID is set correctly
c. The following environment variables point to the Oracle Database 11g2 directories:
– ORACLE_HOME
– PATH
d. Any scripts that clients use to set the ORACLE_HOME value must point to the New Oracle home.
Note: If you are upgrading a cluster database, then perform these checks on all nodes in which this cluster database has instances configured.

8. Log in to the system as the owner of the Oracle Database 11g Release 2 (11.2)

Oracle home directory. At a system prompt, change to the $ORACLE_HOME/rdbms/admin directory.

9. Copy init or spfile file to 11g ORACLE_HOME.


10. Connect to the database instance as a user with SYSDBA privileges.

Start the instance by issuing the following command:

SQL> STARTUP UPGRADE

11. Set the system to spool results to a log file for later verification of success:

SQL> SPOOL upgrade.log

12. Run the catupgrd.sql script:

SQL> @catupgrd.sql This scripts runs the desired upgrade scripts and Shuts Down the database.


13. Restart the instance to reinitialize the system parameters for normal operation.

SQL> STARTUP

14. Run utlu112s.sql to display the results of the upgrade:

SQL> @$ORACLE_HOME/rdbms/admin/utlu112s.sql

15. (Optional ) 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

16. Run utlrp.sql to recompile any remaining stored PL/SQL and Java code.

SQL> @utlrp.sql

17. 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;

18. Exit SQL* Plus