Upgrade Oracle Database 10g to 11g

Prerequisites Check

Run the Pre-Upgrade Information Tool for Collecting Pre-Upgrade Information

Step 1
      Log in to the system as the owner of the Oracle Database 11g Release 2 (11.2) Oracle Home directory.
      Copy the Pre-Upgrade Information Tool (utlu112i.sql) from the Oracle Database 11g Release 2 (11.2) ORACLE_HOME/rdbms/admin directory to a directory outside of the Oracle Home, such as the temporary directory on your system.
      $ORACLE_HOME/rdbms/admin/utlu112i.sql
Step 2
      Change to the directory where utlu112i.sql had been copied in the previous step.
      Start SQL*Plus and connect to the database instance as a user with SYSDBA privileges. Then run and spool the utlu112i.sql file. 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
Check the spool file and examine the output of the Upgrade Information Tool.

Remove Public SYNONYM XMLCONCAT

$ sqlplus / as sysdba
SQL> select SYNONYM_NAME,OWNER from all_synonyms where SYNONYM_NAME = 'XMLCONCAT';
SQL> drop public synonym XMLCONCAT;
Synonym dropped.

Improve upgrade performance

To decrease the amount of downtime incurred when collecting statistics, you can collect statistics prior to performing the actual database upgrade. As of Oracle Database 10g Release 1 (10.1), Oracle recommends that you use the DBMS_STATS.GATHER_DICTIONARY_STATS procedure to gather these statistics. For example, you can enter the following:
$ sqlplus "/as sysdba"
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

Recompile Invalid Objects at least twice

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> @utlrp.sql

Upgrading

Starting Upgrade Using DBUA:
Enter the following command at a system prompt in the Oracle Database 11g Release 2 (11.2.0.x) environment:
Make sure that you are invoking the DBUA utility from the target Oracle home only .
Login as oracle user,
$ dbua

Follow the instructions in DBUA to finish upgrade without Upgrading Enterprise Manager, it will be reconfigured.

Post Upgrading

Change parameters for new Instance

mkdir -p /usr2/app/oracle/product/11.2.0.3/admin/<DBINSTANCE>/adump
mkdir -p $ORACLE_HOME/admin/<DBINSTANCE>/cdump
SQL> show parameter core_dump_dest
SQL> alter system set core_dump_dest='/usr2/app/oracle/product/11.2.0.3/admin/<DBINSTANCE>/cdump' scope=both;
SQL> show parameter audit
SQL> alter system set audit_file_dest='/usr2/app/oracle/product/11.2.0.3/admin/<DBINSTANCE>/adump' scope=spfile;
SQL> show parameter DB_RECOVERY_FILE_DEST
SQL> alter system set db_recovery_file_dest='/usr2/app/oracle/product/11.2.0.3/flash_recovery_area/<DBINSTANCE>' scope=both;
SQL> show parameter compatible;
SQL> ALTER SYSTEM SET COMPATIBLE = '11.2.0' SCOPE=spfile;

Change LOG_MODE

$ cd $ORACLE_HOME/dbs
$ orapwd file=orapw<DBINSTANCE> entries=10
$ sqlplus / as sysdba
SQL> SELECT LOG_MODE FROM SYS.V$DATABASE;
LOG_MODE
------------
NOARCHIVELOG

QL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1820540928 bytes
Fixed Size                  2229304 bytes
Variable Size             452987848 bytes
Database Buffers         1358954496 bytes
Redo Buffers                6369280 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.

Enable Audit Feature (Optional)

Login as sysdba
$ sqlplus / as sysdba
SQL> alter system set audit_trail = 'DB_EXTENDED';
SQL> shutdown immediate
SQL> startup

References

Complete Checklist to Upgrade the Database to 11gR2 using DBUA [ID 870814.1]
https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=481180426703885&id=870814.1&_afrWindowMode=0&_adf.ctrl-state=mqfm1zxvp_311