In my previous company I have upgraded one of our test databases from 10.2.0.1 to 10.2.0.3 on Windows 2003.I would like to share those details:
Set the ORACLE_HOME and ORACLE_SID Environment Variables
C:> set ORACLE_HOME= D:oracleproduct10.2.0db_1
c:> set ORACLE_SID=SAM
The oracle_home is the Oracle home directory where the Oracle10g installation that you want to upgrade is installed, and sid is the SID of the database that you want to upgrade.
Stopping All Processes for a Single Instance Installation
Stop Listener ServiceC:lsnrctlLSNRCTL> stop
Stop Enterprise Manager ServiceC: emctl stop dbconsole
Stop iSQLPlus ServiceC: isqlplusctl stop
Then shutdown the database.
C: sqlplus /nolog
C: connect /as sysdbaSQL> SHUTDOWN IMMEDIATE;
Before installation of patch we should take whole backup.
Now unzip the patch (Downloaded from metalink).
Now double-click on setup file to run the upgadation.
Upgrading a Release 10.2.0.3 Database
Required Preupgrade Checks
Check the SYSTEM Tablespace Size
SQL> SELECT sum(bytes/1024/1024) “Size in MB”2 FROM dba_free_space3 WHERE tablespace_name=’SYSTEM’;
Size in MB———-90
We have enough free space in SYSTEM tablespace.If not enough free space in SYSTEM tablespace, then add/resize the datafile as below.
## TO add a datafileALTER TABLESPACE SYSTEMADD ‘ D:oracleproduct10.2.0oradataTOM SYSTEM01.DBF ‘SIZE 100M;
## To resize an existing datafile.ALTER DATABASE DATAFILE ‘ D:oracleproduct10.2.0oradataTOM SYSTEM01.DBF’ RESIZE 500M;
* Set the SHARED_POOL_SIZE and JAVA_POOL_SIZE Initialization Parameters
##Start the database with the NOMOUNT option:
SQL> STARTUP NOMOUNT
## To check initialization parameter file (initsid.ora or spfilesid.ora)
SQL> SHOW PARAMETER PFILE;
## To determine the current values of these parameters:
SQL> SHOW PARAMETER SHARED_POOL_SIZE
SQL> SHOW PARAMETER JAVA_POOL_SIZE
##If the system is using a server parameter file:If necessary, set the value of the SHARED_POOL_SIZE initialization parameter to at least 150 MB:
SQL> ALTER SYSTEM SET SHARED_POOL_SIZE=’150M’ SCOPE=spfile;
If necessary, set the value of the JAVA_POOL_SIZE initialization parameter to at least 150 MB:
SQL> ALTER SYSTEM SET JAVA_POOL_SIZE=’150M’ SCOPE=spfile;
##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).
Shut down the database:
SQL> SHUTDOWN
Upgrading a Release 10.2 Database
##Login as Windows Administrator
##Start the Listener
C:lsnrctlLSNRCTL> START
##Connect to sqlplus as sysdba and start the database in upgrade mode
C:sqlplus /nologSQL> connect /as sysdba
SQL> STARTUP UPGRADE;
##Run the catupgrd.sql scrips
SQL> SPOOL patch.log
SQL> @D:oracleproduct10.2.0db_1RDBMSADMINcatupgrd.sql
SQL> SPOOL OFF;
Review the patch.log file for errors and inspect the list of components that is displayed at the
end of catupgrd.sql script.
## Restart the database:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;
Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time.
SQL> @D:oracleproduct10.2.0db_1RDBMSADMINutlrp.sql
Start All Oracle Related Services
To Check Current Oracle Version
SQL> select * from version;BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – Prod
PL/SQL Release 10.2.0.3.0 – Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 – Production
NLSRTL Version 10.2.0.3.0 – Production
Cheers its completed ….
Expert are always welcome for their valuable comment or suggestion for the above post.
samadhandba 