Upgrading oracle Database from 10.2.0.1.0 to 10.2.0.4.0 on Windows Server

Hi,
I've upgraded the Database 10.2.0.1.0 to 10.2.0.4.0.Below are the steps with screenshots on the right side for our reference.I have downloaded the PATCH SET 10.2.0.4.0 and applying it to my current Database release 10.2.0.1.0.


Step 1: I’m doing this upgrade by Checking the version initially (10.2.0.1.0)

SQL>select name from v$database;
SQL>select *from v$version; =>Database version is 10.2.0.1.0 as seen in screenshot1.


Step 2:Login to you’re MY ORACLE SUPPORT account =>Click on PATCHES & UPDATES for downloading your patch for Windows 32-bit Operating system(which can
Refer in the screenshot1 in step 1)
In PATCHES & UPDATES =>enter patch number 6810189
You can select upto 5 operation systems but to reduce your search select windows
32 bit as you can see in the screenshot2.

Now,download this patch set .Refer the screenshot 3 for the better overview:

Step 3: Shutdown the oracle Database.

Click on the Read Me tab and save the Read me file so that you can refer this later while doing patch installation.When you are doing this upgrade fro RAC Database the steps may vary which is given in this Read me file.


Step 4: Now download this this patch and copy it to D:\soft 10.4\ path(This can be a place in your ORACLE_HOME Directory or not necessary) But for my reference I copied here.

Set the SID and Shutdown the database as given in the screenshot4.


Step 5: Stop all the services now
My service name is BOSCHDB So I will be stopping the services as given below:

D:\Oracle10g>net stop OracleDBConsoleBOSCHDB

The OracleDBConsoleBoschDB service is stopping........
The OracleDBConsoleBoschDB service was stopped successfully.

D:\Oracle10g>net stop OracleServiceBOSCHDB

The OracleServiceBOSCHDB service is stopping.
The OracleServiceBOSCHDB service was stopped successfully.

D:\Oracle10g>lsnrctl stop

Step 6:Backup your database.

As a precaution or Primary responsibility of DBA and also
Oracle recommends that you create a backup of the Oracle 10g installation before you install the patch set.

Step 7:Check Tablespace Sizes and Set Parameter Values if require
Review the following sections before upgrading a database.

Step 8:Now Launch your setup by going to the path where this patch is copied

cd D:\soft 10.4\Disk1
d:
D:\soft 10.4\Disk1>setup.exe

Specify the location of your Oracle home by Browse:

Create a separate folder (Directory) as below:
D:\Oracle 10.2.0.4.0

I was happy to see this screen where I have to click install

Step9: Upgrade the Database

After you install the patch set, you must perform the following steps on every database associated with the upgraded Oracle home:

Start all services
Go to Control panel=>Administrative tools =>Services=>Start all oracle services as given below or In Start=>run=>services.msc


Step 10: Upgrade your dictionary

SQL> SPOOL patch.log
SQL>@D:\Oracle10g\RDBMS\ADMIN\catupgrd.sql
SQL> SPOOL. off

This script provides a direct upgrade path from these releases to the new Oracle release.

Note 1: For running catupgrd.sql you have to start your Database by using
STARTUP UPGRDADE command.
Note 2:Catupgrd.sql script should be run by going into the above path of ORACLE_HOME/rdbms/admin

Once this script is executed successfully you will see the screen as given below

Step 11:Bounce(restart) your Database for changes to take effect:

SQL> SHUTDOWN
SQL> STARTUP

Step 12: Compile Invalid Objects & Verify our
Database version.

Run the utlrp.sql script to recompile all invalid PL/SQL packages.

SQL> @ D:\Oracle10g\RDBMS\ADMIN\utlrp.sql

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

Hope it helps.


Best regards,

Rafi.

Database upgradation 9i to 10g (Manual Method)

Hi ,

Here are the steps to upgrade the Database from ORACLE 9i to ORACLE 10g:

1. Connect to the database to be upgraded and run utlu101i.sql to determine the preupgrade
tasks to be completed

SQL> spool /tmp/upgrade.txt
SQL>@$ORACLE_HOME/rdbms/admin/utlu101i.sql

2. Resize the redo log files if they are smaller than 4 MB

3. Adjust the size of the tablespaces where the dictionary objects are stored.

4. Perform a cold backup of the database.

5. Shut down the database (do not perform a SHUTDOWN ABORT; perform only SHUTDOWN
IMMEDIATE or SHUTDOWN NORMAL). On Windows you will have to do NET STOP, ORADIM -
DELETE from the old Oracle home directory and ORADIM -NEW from the new Oracle 10g
home directory

6. Copy the parameter file (initDB.ora or spfileDB.ora) and password file from the old Oracle home directory to the Oracle 10g Oracle home directory. The default location for parameter file is $ORACLE_HOME/dbs on Unix platforms and ORACLE_HOME\database on Windows. Adjust the following parameters:

* Adjust the COMPATIBLE parameter; the minimum value required is 9.2.0 for the upgrade.
If you set this to 10.0, you will never be able to downgrade the database to 9i.

* Update the initialization parameters. You must remove obsolete parameters.

* Set the DB_DOMAIN parameter properly.

* Make sure memory parameters have at least the minimum size required for upgrade:
SHARED_POOL_SIZE (96MB for 32-bit platforms, 144MB for 64-bit), PGA_AGGREGATE_
TARGET (24MB), JAVA_POOL_SIZE (48MB), and LARGE_POOL_SIZE (8MB). Use the
sizes recommended by the preinstall verification utility

7. Make sure all the environment variables are set to correctly reference the Oracle 10g Oracle
home. On Unix, verify ORACLE_HOME, PATH, ORA_NLS33, and LD_LIBRARY_PATH.

8. Use SQL*Plus, and connect to the database using the SYSDBA privilege. Start the instance by using the STARTUP UPGRADE mode.

9. Create the SYSAUX tablespace with the following attributes:
* online
* permanent
* read write
* extent management local
* segment space management auto

The syntax could be as follows:

CREATE TALESPACE sysaux
DATAFILE ‘/ora01/oradata/OR0109/sysaux.dbf’ SIZE 500M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

10. Run the upgrade script from the $ORACLE_HOME/rdbms/admin directory. Based on the version of the old database, the name of the upgrade script varies. The following lists the old release and the upgrade script name:

Database Version Script to Run
8.0.6 —-u0800060.sql
8.1.7 —-u0801070.sql
9.0.1 —-u0900010.sql
9.2.0 —-u0902000.sql

For example, to upgrade an Oracle 8.1.7 database to Oracle 10g, you must run
u0801070.sql.
SQL> spool ora8i7upg.log
SQL> @?/rdbms/admin/u0801070.sql
SQL> spool off

If you get any errors during the upgrade script execution, reexecute the script after fixing the error. The postupgrade status utility—utlu101s.sql—gives the name of specific script to run to fix the failed component.

11. Run the utlu101s.sql utility with the TEXT option. It queries the DBA_SERVER_REGISTRY to determine upgrade status and provides information about invalid or incorrect component upgrades. It also provides names of scripts to rerun to fix the errors.

Here is an example (output truncated to fit in single line):

ora10g> @$ORACLE_HOME/rdbms/admin/utlu101s.sql TEXT
PL/SQL procedure successfully completed.

12. Shut down and restart the instance to reinitialize the system parameters for normal operation.
The restart also performs Oracle 10g database initialization for JServer Java Virtual
Machine and other components. Perform a clean shutdown (SHUTDOWN IMMEDIATE);
starting the instance flushes all caches, clears buffers, and performs other housekeeping activities. This is an important step to ensure the integrity and consistency of the upgraded database.

13. Run the utlrp.sql script to recompile all invalid objects.
ora10g> @$ORACLE_HOME/rdbms/admin/utlrp.sql

14. Update the listener.ora file with the new database information.

15. Back up the database.

Once upgrading the database there will be some degradation in the performance …so check the performance through AWR and ADDM….


Best regards,

Rafi.