HOW TO UPGRADE ORACLE 10G TO 11G STANDALONE
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
SQL>@/11g_oracle_home/rdbms/admin/utlu112i.sql
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”
**************************************************
–> “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.
…. 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) 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.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
*.compatible=’10.2.0.1.0′
to
*.compatible=’11.2.0′
*.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
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
export ORACLE_SID=TEST
export PATH=$ORACLE_HOME/bin:$PATH
2.3 Start Upgrade
sqlplus “/as sysdba”
sqlplus “/as sysdba”
SQL> startup upgrade
Check shared_pool & java_pool size, to set new values
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)
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
/11gBase_Install/diag/rdbms/[sid]/[SID]/trace/alert_[SID].log
2.4 Run Post-Upgrade Status Tool provides a summary of the upgradeSQL> startup
SQL>@?/rdbms/admin/utlu112s.sql
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 modeSQL>@?/rdbms/admin/catuppst.sql
2.6 Compile Invalid ObjectsSQL>@?/rdbms/admin/utlrp.sql
Check invalid objectsSQL> select count(*) from dba_objects where status like ‘INVALID’;
Check invalid objectsSQL> 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
Comments
Post a Comment