Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu


1. Prepare to Upgrade

1.1 Install 11g in different (new) ORACLE_HOME with following considerationa) 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
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 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) runSQL> select * from v$timezone_file;
1.4 Check invalid objectsSQL> select object_name, owner, object_type from all_objects where status like ‘INVALID’;

1.5 Check version & status of all database componentsSQL>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 pfileSQL> 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
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
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
2.4 Run Post-Upgrade Status Tool provides a summary of the upgradeSQL> startup
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 modeSQL>@?/rdbms/admin/catuppst.sql
2.6 Compile Invalid ObjectsSQL>@?/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 componentsSQL>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


Popular posts from this blog

Script to find sessions that are blocking other sessions in PostgreSQL

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

PostgreSQL pgBadger

How to Return a Result Set from a PostgreSQL Stored Procedure