upgrade database oracle 10g to oracle 11g

upgrade 10g to 11g:

@/opt/oracle/product/11.2.0.3/rdbms/admin/utlu112i.sql from 10g---precheck
startup upgrade
@?/rdbms/admin/catupgrd.sql
@?/rdbms/admin/utlrp.sql
@/opt/oracle/product/11.2.0.3/rdbms/admin/utlu112s.sql from 11g-----post check


1.$ORACLE_HOME/rdbms/admin/utlu112i.sql - copy this from oracle 11g to /tmp and run it

Please note that the database should be started using the Source Oracle Home.

$ sqlplus '/ as sysdba'
SQL> spool upgrade_info.log
SQL> @utlu112i.sql
SQL> spool off


2.run $ORACLE_HOME/rdbms/admin/utlrp.sql (multiple times) to validate the invalid objects in the database, until there is no change in the number of invalid objects.
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> @utlrp.sql

3.Check that National Characterset (NLS_NCHAR_CHARACTERSET) is UTF8 and AL16UTF16.

select value from NLS_DATABASE_PARAMETERS where parameter = 'NLS_NCHAR_CHARACTERSET';

If this is UTF8 or AL16UTF16 then no action is needed.


3. When upgrading to Oracle Database 11g Release 2 (11.2), optimizer statistics are collected for dictionary tables that lack statistics. This statistics collection can be time consuming for databases with a large number of dictionary tables, but statistics gathering only occurs for those tables that lack statistics or are significantly changed during the upgrade

$ sqlplus "/as sysdba"

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

4. Ensure that no files need media recovery:

Sql> SELECT * FROM v$recover_file;

5. Stop the listener for the database:

Make pfile from spfile;

6 lsnrctl stop

7. take backup of source db

8. Make a backup of the init.ora file.Comment out obsolete parameters

* The DIAGNOSTIC_DEST initialization parameter replaces the USER_DUMP_DEST, BACKGROUND_DUMP_DEST.

Note: Once the Parameter file is modified as per your requirement, copy the file to $ORACLE_HOME/dbs (11g Oracle Home )

9. Set Environment Variables:

If your operating system is UNIX then complete this step, else skip to next Step.

A. Make sure the following environment variables point to the Oracle database software 11g Release directories:

- ORACLE_BASE
- ORACLE_HOME
- PATH
$ export ORACLE_HOME=
$ export PATH=$ORACLE_HOME/bin:$PATH
$ export ORACLE_BASE=
Note : If ORACLE_BASE is not known, after setting Path towards 11g Oracle Home, execute 'orabase', which will point the location of base.
$ orabase
/uo1/app/oracle

B. Update the oratab entry, to set the new ORACLE_HOME pointing towards ORCL and disable automatic startup

Sample /etc/oratab

#orcl:/opt/oracle/product/10.2/db_1:N
orcl:/opt/oracle/product/11.2/db_1:N

Note : After /etc/oratab is updated to have sid and Oracle Home (11.2), you can execute oraenv (/usr/local/bin/oraenv) and set the environment. The input has to be the sid which is entered in /etc/oratab against 11g home.
for Instance,

[oracle@localhost ~]$ . oraenv


10. Upgrade Database:At the operating system prompt, change to the $ORACLE_HOME/rdbms/admin directory of 11gR2 Oracle Home.

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> startup UPGRADE

Set the system to spool results to a log file for later verification after the upgrade is completed and start the upgrade script.

SQL> set echo on
SQL> SPOOL upgrade.log
SQL> @catupgrd.sql
SQL> spool off

11 Post upgrade :-

Start the database and run the Post-Upgrade Status Tool $ORACLE_HOME/rdbms/admin/utlu112s.sql which provides a summary of the upgrade at the end of the spool log. It displays the status of the database components in the upgraded database and the time required to complete each component upgrade. Any errors that occur during the upgrade are listed with each component and must be addressed
$ sqlplus "/as sysdba"
SQL> STARTUP
SQL> @utlu112s.sql

12. Recompile Invalid Objects:

This script can be run concurrently with utlrp.sql. Run utlrp.sql to recompile any remaining stored PL/SQL and Java code in another session.
SQL> @utlrp.sql

13 upgrade timezone if required