Steps to upgrade oracle database from 10g to 11g
Steps to upgrade oracle database from 10g to 11g
This post covers step by step upgrade of Oracle Database from version 10g (10.2.0.1) to 11g R1 (11.1.0.6) on Unix. You can use this document (with few additional steps mentioned in upgrade guide) to upgrade from 9i or for database on Windows Operating System.
.
.
Important points before you start 11g upgrade
1. Read Chapter 1, 2 & 3 of Database Upgrade Guide here here
2. Read 11g Installation Guide for your operating system from here
3. Direct Upgrade is possible from 10.2.0.1 and higher
4. There are two methods to upgrade database
a) Database Upgrade Assistant (DBUA) and
b) Manual Upgrade
1. Read Chapter 1, 2 & 3 of Database Upgrade Guide here here
2. Read 11g Installation Guide for your operating system from here
3. Direct Upgrade is possible from 10.2.0.1 and higher
4. There are two methods to upgrade database
a) Database Upgrade Assistant (DBUA) and
b) Manual Upgrade
A.Upgrade oracle 10.2.0.1 to oracle 10.2.0.4 on AIX 5.3 with Database Upgrade Assistant (DBUA
1.If you don’t have installable Oracle Patchset 10.2.0.4 (6810189)
You can download the patchset 6810189 from following link.
2.Shut down all processes in the Oracle home that might be accessing a database, for example Oracle Enterprise Manager Database Control:
$ emctl stop dbconsole
$ lsnrctl stop
3.Shut down all database instances running in the Oracle home directory, where you need to install the patch set.
4.Shut down all listeners running in the Oracle home directory, where you need to install the patch set.
5.Connect To server ….using Xmanager
Check xclock if above screen do not appear then set DISPLAY variable.export DISPLAY= ipaddress:1.0 then check xclock again run .profilecheck ORACLE_HOMEecho $ORACLE_HOME
then go to path where installer is kept../runInstaller if following error comes then cancel the installation. Rename the fuser. Details are given below screen shot.Oracle universal installer has detected that thereare processes running in the currently selected Oracle home.the following processes need to be shutdown before continuing.IZ67400: FUSER GIVES INCORRECT PIDS WITH -X OPTION This APAR is a duplicate of IZ71207. Registration is required to review patches IZ67400 and/or IX71207 on IBM web site.Alternatively, the following workaround can be used:As root:
1) rename fuser mv /usr/sbin/fuser /usr/sbin/fuser_renamed
2) touch /usr/sbin/fuser
3) chmod +x /usr/sbin/fuser
As oracle software owner:
1) Try to install the Oracle software (fuser can now return nothing, hence the Installer should continue) After successful install, as root:
1) rename fuser back to its original name
mv /usr/sbin/fuser_renamed /usr/sbin/fuser
Take other session and connect to server using root user then run root.sh.
# sh /oracle/app/oracle/root.sh
After installation software.
Refer following sequence of commands.
1.SQL> STARTUP UPGRADE
2.SQL> SPOOL patch.log
3.SQL> @?/rdbms/admin/catupgrd.sql
Check version of components
SQL>select comp_name,version,status from dba_registry;
Check status of all objects
SQL> select count(1),status from all_objects group by status;
Compiling all invalid objects.
SQL>@/rdbms/admin/utlrp.sql
DB is upgraded to 10.2.0.4 successfully.
We are going to use Manual Upgrade
5. Install 11g database software in different ORACLE_HOME from source Database
5. Install 11g database software in different ORACLE_HOME from source Database
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)
a) When prompted for Upgrade an Existing Database (Select NO)
b) On Select Configuration Option select Install Software Only
Use Installation Guide from here
Step 2 – Copy following files to new directory from ORACLE 11g Home.
Complete path is as given below
1.2 Analyze database using pre-upgrade information tool ( utlu111i.sql sql script available at 11g_oracle_homne/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/utlu111i.sql
SQL>spool upgrade_info.log
SQL>@/11g_oracle_home/rdbms/admin/utlu111i.sql
1.3 Check the output of the Pre-Upgrade Information Tool in upgrade_info.log and fix any issues
(/appvolume/oracle11/app/product/11.2.0/dbhome_1/rdbms/admin)
utlu112i.sql — Oracle 11gR1 Pre-Upgrade tool (utlu111i.sql)
utltzuv2.sql — Output of this file will display current version of
sql>@utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 04-30-2012 19:28:09
. Tablespaces: [make adjustments in the current environment]
pdate Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
–> background_dump_dest 11.1 DEPRECATED replaced by
“diagnostic_dest”
–> user_dump_dest 11.1 DEPRECATED replaced by
“diagnostic_dest”
–> core_dump_dest 11.1 DEPRECATED replaced by
“diagnostic_dest”
.Components: [The following database components will be upgraded or installed]
SQL> @check_stale_stats.sql
——————————————————————————————————— CTXSYS schema contains stale statistics use the following to gather the statistics –
——————————————————————————————————-
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS(‘CTXSYS’,OPTIONS=>’GATHER’, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => ‘FOR ALL COLUMNS SIZE AUTO’, CASCADE => TRUE);
– There are no stale statistics in DMSYS schema.
– There are no stale statistics in EXFSYS schema.
——————————————————————————————————-
– MDSYS schema contains stale statistics use the following to gather the statistics –
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS(‘MDSYS’,OPTIONS=>’GATHER’, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => ‘FOR ALL COLUMNS SIZE AUTO’, CASCADE => TRUE);
——————————————————————————————————
– OLAPSYS schema contains stale statistics use the following to gather the statistics –
——————————————————————————————————-
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS(‘OLAPSYS’,OPTIONS=>’GATHER’, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => ‘FOR ALL COLUMNS SIZE AUTO’, CASCADE => TRUE);
– There are no stale statistics in ORDSYS schema.
——————————————————————————————————-
– SYS schema contains stale statistics use the following to gather the statistics –
——————————————————————————————————-
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS(‘SYS’,OPTIONS=>’GATHER’, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => ‘FOR ALL COLUMNS SIZE AUTO’, CASCADE => TRUE);
——————————————————————————————————
– SYSMAN schema contains stale statistics use the following to gather the statistics –
——————————————————————————————————-
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS(‘SYSMAN’,OPTIONS=>’GATHER’, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => ‘FOR ALL COLUMNS SIZE AUTO’, CASCADE => TRUE);
——————————————————————————————————-
– WMSYS schema contains stale statistics use the following to gather the statistics –
——————————————————————————————————-
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS(‘WMSYS’,OPTIONS=>’GATHER’, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => ‘FOR ALL COLUMNS SIZE AUTO’, CASCADE => TRUE);
——————————————————————————————————-
– XDB schema contains stale statistics use the following to gather the statistics –
——————————————————————————————————-
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS(‘XDB’,OPTIONS=>’GATHER’, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => ‘FOR ALL COLUMNS SIZE AUTO’, CASCADE => TRUE);
Obsolete/Deprecated Parameters: [Update Oracle Database 11.1 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 utlu111i.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 utlu111i.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;
If time zone file version is less than 4 then apply time zone patch 5632264 manually for 10.2.0.1 database (more on How to manually upgrade time zone file version on database coming soon … )
SQL> select * from v$timezone_file;
If time zone file version is less than 4 then apply time zone patch 5632264 manually for 10.2.0.1 database (more on How to manually upgrade time zone file version on database coming soon … )
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;
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 ;
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.1.0′
*.diagnostic_dest=’/11g_base’ (11g Base Directory)
b) Change
*.compatible=’10.2.0.1.0′
to
*.compatible=’11.1.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 1 (11.1) :
export ORACLE_HOME=/u01/oracle/11gbase/11.1.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 upgrade
SQL> startup
SQL>@?/rdbms/admin/utlu111s.sql
SQL> startup
SQL>@?/rdbms/admin/utlu111s.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
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’;
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;
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
.References
- Oracle 11g Installation Guide
- Oracle Database 11g Upgrade Guide
- 429825.1 Complete Checklist for Manual Upgrades to 11gR1
- 744693.1 ORA-00001 For SYS.DIANA_VERSION$ During RDBMS Upgrade From 11.1.0.6 To 11.1.0.7
- 413671.1 Applying “version 4″ Time Zone Files on an Oracle Database
- 396387.1 Workarounds when Database time zone patches are not available for your patchset
- 396671.1 Usage of utltzuv2.sql before updating time zone files in Oracle 10
- 730057.1 Upgrading to 11g Fails with ORA-01722: invalid number
No comments:
Post a Comment