Upgrade Oracle 10.2.0.1 to 10.2.0.2

Upgrade Oracle 10.2.0.1 to 10.2.0.2
1. Patch Set OverviewPatch set release 10.2.0.2. Before installing this patch set you must be need 10.2.0.1 version.2. RequirementsOracle DB: Oracle 10.2.0.1 (later)Operating System: Windows 2000 (Service Pack 2 or higher)3. Pre - Installation Tasks4. Identify prior installationBefore installing this patch you must install oracle 10.2.0.1 (or later version)5. Download Patch setDownload and extract the p4547817_10202_WINNT.zip patch set installation archive to a directory that is not the Oracle home directory or under the Oracle home directory.
6. Backup your database.Oracle recommends that you create a backup of the installed Oracle 10g software before you install the patch set and the database.
I.e., (datafile, redologfile)
7. Check SYSTEM Tablespace Size Ensure that there is at least 50 MB of free space allocated to the SYSTEM tablespace.
query to check tablespace size and freespace



col "Tablespace" for a22col "Used MB" for 99,999,999col "Free MB" for 99,999,999col "Total MB" for 99,999,999select df.tablespace_name "Tablespace",totalusedspace "Used MB",(df.totalspace - tu.totalusedspace) "Free MB",df.totalspace "Total MB",round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))"Pct. Free"from(select tablespace_name,round(sum(bytes) / 1048576) TotalSpacefrom dba_data_files group by tablespace_name) df,(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_namefrom dba_segments group by tablespace_name) tuwhere df.tablespace_name = tu.tablespace_name ;
Resize the system datafile if free space below 50 MB
ALTER DATABASE DATAFILE 'c:\oracle\oradata\orabase\system01.dbf' RESIZE 150M;
Check for free space from the above query again.
8. Set the SHARED_POOL_SIZE and JAVA_POOL_SIZE Initialization Parameters to minimum 150 MB.
Users who have JVM (Java enabled) or JVM and XDB installed on their 10.2.0.1 databases should make sure that the init.ora parameters SHARED_POOL_SIZE and JAVA_POOL_SIZE are each 150 MB or more before running the catpatch.sql upgrade script. Failure to do so could result in an unrecoverable memory failure while running of the script. Please note that JVM and XML DB was shipped as part of the default 10.2.0.1 seed database, and will be present unless the user explicitly installed a 10.2.0.1 instance without them. Set the value of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters as follows:
Note: If the system uses an initialization parameter file, if necessary change the values of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters to at least 150 MB in the initialization parameter file (initsid.ora).
If the system uses sp-file use the below command.
SQL> ALTER SYSTEM SET JAVA_POOL_SIZE=150M SCOPE=SPFILE; System altered.SQL> ALTER SYSTEM SET SHARED_POOL_SIZE=150M SCOPE=SPFILE; System altered.

9. Shutdown oracle database.C:\>set oracle_sid=orclC:\>sqlplus /nologSQL*plus: Release 10.2.0.1.0 - Production on Tue Nov 13 10:49:26 2007Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.SQL> connect sys@orcl as sysdbaEnter password: Connected.SQL> SHUTDOWNDatabase closed.Database dismounted.ORACLE instance shut down.


10. Stop all servicesC:\>set oracle_sid=orclC:\>net stop OracleDBConsoleORCLThe OracleDBConsoleORCLvice is stopping................The OracleDBConsoleORCLservice was stopped successfully.C:\>net stops OracleServiceORCLThe OracleServiceORCL is stopping.The OracleServiceORCLservice was stopped successfully.C:\>net stop OracleOraDB10g_Home1iSQL*Plus...The OracleOraDb10g_home1iSQL*Plus service was stopped successfully.C:\>net stop OracleOraDB10g_Home1TNSListenerThe OracleOraDb10g_home1TNSListener service is stopping.The OracleOraDb10g_home1TNSListener service was stopped successfully.
Note: while patch installation time if you face Distributed Transaction Coordinator still running. You should be stop this serviceC:\>net stop msdtcThe Distributed Transaction Coordinator service is stopping.The Distributed Transaction Coordinator service was stopped successfully
OR CAN STOP ALL THE ORACLE SERVICES FROM SERVICES.MSC FROM THE RUN CONSOLE ON THE START BUTTON.
11. Apply the patch after extracting the zip file (ie, p4547817_10202_WINNT.zip ) to the ORACLE_HOME where 10.2.0.1 is installed.

On welcome screen click Next1) On Specify file location screen, in Name field and Path select Database on which you wish to apply this patch (If you have multiple database installed on your machine you will see drop down menu to select database oracle_home)




2) On summay screen, click Install

Arter the patch is installed on the ORACLE_HOME
12. Upgrade the Database After you install the patch set, you must perform the following steps on every database associated with the upgraded Oracle home:1. Start all oracle services by commands or by services.msc from run console.2. Connect sys userC :\> sqlplus /NOLOGSQL> CONNECT SYS/SYS_password AS SYSDBA3. Enter the following SQL*Plus commands:SQL> STARTUP UPGRADESQL> SPOOL patch. logSQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\catupgrd.sqlSQL> SPOOL OFFReview the patch.log file for errors and inspect the list of components that is displayed at the end of catupgrd.sql script. This list provides the version and status of each SERVER component in the database.
If necessary, rerun the catupgrd.sql script after correcting any problems. 4. Restart the database: SQL> SHUTDOWNSQL> STARTUP5. Compile Invalid ObjectsRun the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time. This step is optional but recommended. SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\utlrp.sqlSQL> select * from v$version;BANNER----------------------------------------------------------------Oracle Database 10g Release 10.2.0.3.0 - ProductionPL/SQL Release 10.2.0.3.0 - ProductionCORE 10.2.0.3.0 ProductionTNS for 32-bit Windows: Version 10.2.0.3.0 - ProductionNLSRTL Version 10.2.0.3.0 - Production

------END OF ORACLE UPGRADATION FROM 10.2.0.1 TO 10.2.0.2-----
Thanks
Harshavardhan_srinivas
Oracle Database administrator
MindTree Consulting Pvt. Ltd.