Upgrade Standalone from Oracle 10g to 11g

Oracle upgrade Oracle 10g to 11g Standalone

 

Upgrade Steps

1. Prepare to Upgrade

1.1 Install 11g in different (new) ORACLE_HOME with following consideration
a) When prompted for Upgrade an Existing Database (Select NO)

b) On Select Configuration Option  select Install Software Only

 

 

.
1.2 Analyze database using pre-upgrade information tool ( utlu112i.sql sql script available at 11g_oracle_home/rdbms/admin/utlu111i.sql  run it from source database i.e. 10g)

sqlplus “/as sysdba”
SQL>spool upgrade_info.log
SQL>@/11g_oracle_home/rdbms/admin/utlu112i.sql

1.3 Check the output of the Pre-Upgrade Information Tool in upgrade_info.logand fix any issues

Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**************************************************
–> “background_dump_dest” replaced by  “diagnostic_dest”
–> “user_dump_dest” replaced by  “diagnostic_dest”
–> “core_dump_dest” replaced by  “diagnostic_dest”

To fix this obsolete parameter, comment out from initialization parameter file and replace with new parameter like comment above three deprecated parameters and add *.diagnostic_dest

WARNING: –> Database is using an old timezone file version.
…. Patch the 10.2.0.1.0 database to timezone file version 4
…. BEFORE upgrading the database.  Re-run utlu112i.sql after
…. patching the database to record the new timezone file version.

To find time zone file version on source database (10g) run
SQL> select * from v$timezone_file;

1.4 Check invalid objects
SQL> select object_name, owner, object_type from all_objects where status like ‘INVALID’;

1.5 Check version & status of all database components
SQL>select comp_name,version, status from dba_registry;

1.6 Take backup of source database i.e. 10g database

1.7 If you are using spfile, create pfile
SQL> create pfile from spfile ;

This will create pfile in 10g $ORACLE_HOME/dbs/init[SID].ora

1.8 Copy initialization file (pfile) from source (10g) to target (11g)

1.9 Adjust initialization parameter specific to 11g like

a) Remove *.background_dump_dest, *.core_dump_dest, *.user_dump_dest and add
*.diagnostic_dest=’/11g_base’  (11g Base Directory)
b) Change
*.compatible=’10.2.0.1.0′
to
*.compatible=’11.2.0′

2. Upgrade Database

2.1 Shut down source database (10g) - Your downtime starts here
sqlplus “/as sysdba”
SQL>shutdown immediate

2.2 Set your environment variables to Oracle Database 11g Release 2 (11.2) :

export ORACLE_HOME=/u01/oracle/11gbase/11.2.0
export ORACLE_SID=TEST
export PATH=$ORACLE_HOME/bin:$PATH

2.3 Start Upgrade
sqlplus “/as sysdba”

SQL> startup upgrade 
Check shared_pool & java_pool size, to set new values

SQL>alter system set java_pool_size=512M;
SQL>alter system set shared_pool_size=800M;

SQL> spool upgrade.log 
SQL> @?/rdbms/admin/catupgrd.sql  (Upgrade process shut down database after catupgrd.sql)

Check alert log in 11g at
/11gBase_Install/diag/rdbms/[sid]/[SID]/trace/alert_[SID].log

2.4 Run Post-Upgrade Status Tool provides a summary of the upgrade
SQL> startup
SQL>@?/rdbms/admin/utlu112s.sql

If you hit error like”ORA-00001: unique constraint (SYS.I_DIANA_VERSION) violated” check metalink note # 744693.1

2.5 Perform upgrade actions that do not require the database to be in UPGRADE mode
SQL>@?/rdbms/admin/catuppst.sql

2.6 Compile Invalid Objects
SQL>@?/rdbms/admin/utlrp.sql
Check invalid objects
SQL> select count(*) from dba_objects where status like ‘INVALID’;

3. Post Upgrade steps

3.1 Check status of database components
SQL>select comp_name,version, status from dba_registry;

3.2 Copy tnsnames.ora, listener.ora, sqlnet.ora and include file from source (10g) oracle_home to target (11g) oracle_home

3.3 Back up upgraded (11g) database