upgrade 10.2.0.3 to 10.2.0.5

It's better to read the readme file in the patch package and work exactly step by step as the readme file.

OS:rhel 5 update 5 x86_64

DB:oracle 10.2.0.3 single instance based on filesystem

1.stop the dbconsole,listener,oracle instance,isqlplus

2.go to the patchset file directory and run ./runInstaller in GUI environment

3.upgrade

(1)lsnrctl start

to start the listener

(2)startup upgrade to setp into the upgrade mode

 

 
Start the database in the UPGRADE mode:
 SQL> STARTUP UPGRADE
 
 (3)spool the upgrade information
 
Set the system to spool results to a log file for later analysis:
 SQL> SPOOL upgrade_info.log 
 
Run the Pre-Upgrade Information Tool:
 SQL> @/rdbms/admin/utlu102i.sql
 
Turn off the spooling of script results to the log file:
 SQL> SPOOL OFF
 
(4)check out the log of Running pre-upgrade utility tools(utlu102i.sql)
 
SQL> @$ORACLE_HOME/rdbms/admin/utlu102i.sql
Oracle Database 10.2 Upgrade Information Utility    09-12-2012 05:34:57         
.                                                                               
**********************************************************************          
Database:                                                                       
**********************************************************************          
–> name:       TRADE                                                           
–> version:    10.2.0.3.0                                                      
–> compatible: 10.2.0.3.0                                                      
–> blocksize:  8192                                                            
.                                                                               
**********************************************************************          
Tablespaces: [make adjustments in the current environment]                      
**********************************************************************          
–> SYSTEM tablespace is adequate for the upgrade.                              
…. minimum required size: 310 MB                                              
–> UNDOTBS1 tablespace is adequate for the upgrade.                            
…. minimum required size: 393 MB                                              
…. AUTOEXTEND additional space required: 193 MB                               
–> SYSAUX tablespace is adequate for the upgrade.                              
…. minimum required size: 148 MB                                              
–> TEMP tablespace is adequate for the upgrade.                                
…. minimum required size: 58 MB                                               
.                                                                               
**********************************************************************          
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]             
**********************************************************************          
WARNING: –> "sga_target" needs to be increased to at least 327155712           
.                                                                               
**********************************************************************          
Renamed Parameters: [Update Oracle Database 10.2 init.ora or spfile]            
**********************************************************************          
— No renamed parameters found. No changes are required.                        
.                                                                               
**********************************************************************          
Obsolete/Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************          
— No obsolete parameters found. No changes are required                        
.                                                                               
**********************************************************************          
Components: [The following database components will be upgraded or installed]   
**********************************************************************          
–> Oracle Catalog Views                 [upgrade]  VALID                               
–> Oracle Packages and Types    [upgrade]  VALID                               
–> Oracle Workspace Manager     [upgrade]  VALID                               
–> EM Repository                              [upgrade]  VALID                               
.                                                                               
**********************************************************************          
Miscellaneous Warnings                                                          
**********************************************************************          
WARNING: –> Database contains INVALID objects prior to upgrade.                
…. USER TRADE has 52 INVALID objects.                                         
.                                                                               
 
PL/SQL procedure successfully completed.
 
SQL> spool off
 
———————————————————————————————–
 
(3)run catupgrd.sql to upgrade
 
SQL> @?/rdbms/admin/catupgrd.sql
DOC>######################################################################
DOC>######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the user running this script is not SYS.  Disconnect
DOC> and reconnect with AS SYSDBA.
DOC>######################################################################
DOC>######################################################################
DOC>#
 
no rows selected
 
DOC>######################################################################
DOC>######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the database server version is not correct for this script.
DOC> Shutdown ABORT and use a different script or a different server.
DOC>######################################################################
DOC>######################################################################
DOC>#
 
no rows selected
 
DOC>#######################################################################
DOC>#######################################################################
DOC>   The following statement will cause an "ORA-01722: invalid number"
DOC>   error if the database has not been opened for UPGRADE.
DOC>
DOC>   Perform a "SHUTDOWN ABORT"  and
DOC>   restart using UPGRADE.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
 
no rows selected
 
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statements will cause an "ORA-01722: invalid number"
DOC> error if the SYSAUX tablespace does not exist or is not
DOC> ONLINE for READ WRITE, PERMANENT, EXTENT MANAGEMENT LOCAL, and
DOC> SEGMENT SPACE MANAGEMENT AUTO.
DOC>
DOC> The SYSAUX tablespace is used in 10.1 to consolidate data from
DOC> a number of tablespaces that were separate in prior releases.
DOC> Consult the Oracle Database Upgrade Guide for sizing estimates.
DOC>
DOC> Create the SYSAUX tablespace, for example,
DOC>
DOC> create tablespace SYSAUX datafile 'sysaux01.dbf'
DOC>     size 70M reuse
DOC>     extent management local
DOC>     segment space management auto
DOC>     online;
DOC>
DOC> Then rerun the catupgrd.sql script.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
 
no rows selected
 
 
no rows selected

…………………………………………………………………
…………………………………………………………………
…………………………………………………………………
…………………………………………………………………
…………………………………………………………………
…………………………………………………………………
 
No errors.
old   2:   IF ('&EM_REPOS_MODE' = 'CENTRAL') THEN
new   2:   IF ('SYSAUX' = 'CENTRAL') THEN
old   3:     :script_name := '&EM_SQL_ROOT/ias/ias_procs.sql';
new   3:     :script_name := '?/sysman/admin/emdrep/sql/ias/ias_procs.sql';
old   5:     :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
new   5:     :script_name := '?/sysman/admin/emdrep/sql/core/latest/admin/admin_do_nothing.sql';
 
PL/SQL procedure successfully completed.
 
 
1 row selected.
 
old   2:   IF ('&EM_REPOS_MODE' = 'CENTRAL') THEN
new   2:   IF ('SYSAUX' = 'CENTRAL') THEN
old   3:     :script_name := '&EM_SQL_ROOT/ocs/ocs_procs.sql';
new   3:     :script_name := '?/sysman/admin/emdrep/sql/ocs/ocs_procs.sql';
old   5:     :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
new   5:     :script_name := '?/sysman/admin/emdrep/sql/core/latest/admin/admin_do_nothing.sql';
 
PL/SQL procedure successfully completed.
 
1 row selected.
 
old  13:                   WHERE owner ='&EM_REPOS_USER'
new  13:                   WHERE owner ='SYSMAN'
 
PL/SQL procedure successfully completed.
 
old  15:     :script_name := '&EM_SQL_ROOT/core/v10102/core_data_upgrade.sql';
new  15:     :script_name := '?/sysman/admin/emdrep/sql/core/v10102/core_data_upgrade.sql';
old  18:     :script_name := '&EM_SQL_ROOT/core/v101040/core_data_upgrade.sql';
new  18:     :script_name := '?/sysman/admin/emdrep/sql/core/v101040/core_data_upgrade.sql';
old  21:     :script_name := '&EM_SQL_ROOT/core/v101050/core_data_upgrade.sql';
new  21:     :script_name := '?/sysman/admin/emdrep/sql/core/v101050/core_data_upgrade.sql';
old  24:     :script_name := '&EM_SQL_ROOT/core/v101060/core_data_upgrade.sql';
new  24:     :script_name := '?/sysman/admin/emdrep/sql/core/v101060/core_data_upgrade.sql';
old  26:     :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
new  26:     :script_name := '?/sysman/admin/emdrep/sql/core/latest/admin/admin_do_nothing.sql';
 
PL/SQL procedure successfully completed.
 
 
1 row selected.
 
 
PL/SQL procedure successfully completed.
 
 
PL/SQL procedure successfully completed.
 
 
Commit complete.
 
 
PL/SQL procedure successfully completed.
 
old  16:     :script_name := '&EM_SQL_ROOT/db/v10102/db_data_upgrade.sql';
new  16:     :script_name := '?/sysman/admin/emdrep/sql/db/v10102/db_data_upgrade.sql';
old  19:     :script_name := '&EM_SQL_ROOT/db/v101040/db_data_upgrade.sql';
new  19:     :script_name := '?/sysman/admin/emdrep/sql/db/v101040/db_data_upgrade.sql';
old  22:     :script_name := '&EM_SQL_ROOT/db/v102010/db_data_upgrade.sql';
new  22:     :script_name := '?/sysman/admin/emdrep/sql/db/v102010/db_data_upgrade.sql';
old  24:     :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
new  24:     :script_name := '?/sysman/admin/emdrep/sql/core/latest/admin/admin_do_nothing.sql';
 
PL/SQL procedure successfully completed.
 
1 row selected.
 
Registering job types
old   2:   IF ('&EM_REPOS_MODE' = 'CENTRAL') THEN
new   2:   IF ('SYSAUX' = 'CENTRAL') THEN
old   3:     :script_name := '&EM_SQL_ROOT/ias/ias_data_upgrade.sql';
new   3:     :script_name := '?/sysman/admin/emdrep/sql/ias/ias_data_upgrade.sql';
old   5:     :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
new   5:     :script_name := '?/sysman/admin/emdrep/sql/core/latest/admin/admin_do_nothing.sql';
 
old   2:   IF ('&EM_REPOS_MODE' = 'CENTRAL') THEN
new   2:   IF ('SYSAUX' = 'CENTRAL') THEN
old   3:     :script_name := '&EM_SQL_ROOT/ocs/ocs_data_upgrade.sql';
new   3:     :script_name := '?/sysman/admin/emdrep/sql/ocs/ocs_data_upgrade.sql';
old   5:     :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
new   5:     :script_name := '?/sysman/admin/emdrep/sql/core/latest/admin/admin_do_nothing.sql';
 
Submitting DBMS jobs.
old   5:   IF NOT ('&EM_REPOS_MODE' = 'CENTRAL') THEN
new   5:   IF NOT ('SYSAUX' = 'CENTRAL') THEN
 
TIMESTAMP
——————————————————————————–
COMP_TIMESTAMP EM         2012-09-12 05:49:50
DBUA_TIMESTAMP EM         VALID       2012-09-12 05:49:50
 
 
TIMESTAMP
——————————————————————————–
COMP_TIMESTAMP UPGRD_END  2012-09-12 05:51:41
.
Oracle Database 10.2 Upgrade Status Utility           09-12-2012 05:51:41
.
Component                                Status         Version  HH:MM:SS
Oracle Database Server                    VALID      10.2.0.5.0  00:10:23
Oracle Workspace Manager                  VALID      10.2.0.5.0  00:00:46
Oracle Enterprise Manager                 VALID      10.2.0.5.0  00:01:05
.
Total Upgrade Time: 00:14:05
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC>   The above PL/SQL lists the SERVER components in the upgraded
DOC>   database, along with their current version and status.
DOC>
DOC>   Please review the status and version columns and look for
DOC>   any errors in the spool log file.  If there are errors in the spool
DOC>   file, or any components are not VALID or not the current version,
DOC>   consult the Oracle Database Upgrade Guide for troubleshooting
DOC>   recommendations.
DOC>
DOC>   Next shutdown immediate, restart for normal operation, and then
DOC>   run utlrp.sql to recompile any invalid application objects.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL> spool off
 
————————————————————————————————————-
 
(4) recompile the invalid objects
 
SQL> @?/rdbms/admin/utlrp.sql
 
TIMESTAMP
——————————————————————————–
COMP_TIMESTAMP UTLRP_BGN  2012-09-12 06:03:09
DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
 
TIMESTAMP
——————————————————————————–
COMP_TIMESTAMP UTLRP_END  2012-09-12 06:03:35
DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
 
OBJECTS WITH ERRORS
——————-
                  0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
 
ERRORS DURING RECOMPILATION
—————————
                          0
SQL> spool off
 
job is done!
 
–EOF–