Tuesday, February 17, 2009

Identifying Your Oracle Database Software Release

Identifying Your Oracle Database Software Release
Because the Oracle database server continues to evolve and can require maintenance, Oracle periodically produces new releases. Because only some users initially subscribe to a new release or require specific maintenance, multiple releases of the product can exist simultaneously.
As many as five numbers may be required to fully identify a release. The significance of these numbers is discussed below.

Release Number Format
To understand the release level nomenclature used by Oracle, examine the following example of an Oracle database server labeled "Release 9.2.0.1.0."

Figure 1-1 Example of an Oracle Release Number

Note:
Starting with release 9.2, maintenance releases of Oracle are denoted by a change to the second digit of a release number. In previous releases, the third digit indicated a particular maintenance release.

Major Database Release Number
This is the most general identifier. It represents a major new edition (or version) of the software that contains significant new functionality.

Database Maintenance Release Number
This digit represents a maintenance release level. Some new features may also be included.

Application Server Release Number
This digit reflects the release level of the Oracle9i Application Server (Oracle9iAS).

Component Specific Release Number
This digit identifies a release level specific to a component. Different components can have different numbers in this position depending upon, for example, component patch sets or interim releases.

Platform Specific Release Number
This digit identifies a platform specific release. Usually this is a patch set. Where different platforms require the equivalent patch set, this digit will be the same across the effected platforms.

Checking Your Current Release Number
To identify the release of the Oracle database server that is currently installed and to see the release levels of other Oracle components you are using, query the data dictionary view PRODUCT_COMPONENT_VERSION. A sample query is shown below. Other product release levels may increment independently of the database server.

COL PRODUCT FORMAT A35
COL VERSION FORMAT A15
COL STATUS FORMAT A15

SELECT * FROM PRODUCT_COMPONENT_VERSION;

PRODUCT VERSION STATUS

----------------------------------- --------------- ---------------

NLSRTL 9.2.0.1.0 Production

Oracle9i Enterprise Edition 9.2.0.1.0 Production

PL/SQL 9.2.0.1.0 Production

TNS for Solaris: 9.2.0.1.0 Production

It's important to convey to Oracle the information displayed by this query when you report problems with the software.
Optionally, you can query the V$VERSION view to see component-level information.

What is an Index Organized Table (IOT) and when should an IOT be used?

An IOT keeps its data stored according to the primary key column values for the table. IOTs store the data as if the entire table was stored in an index.A normal index only stores the indexed columns in the index; an IOT stores all of the table's columns in the index. IOTs are new in Oracle8.As the table's data is stored as an index, the rows of the table do not have physical RowIDs. Therefore, you cannot select the RowID pseudocolumn values from an IOT.

Oracle8i introduces a new feature for IOTs called logical RowIDs. Logical RowIDs allow the construction of secondary indexes and improve index performance allowing the IOT performance to match the performance of regular indexes.

Prior to Oracle8i you could not create additional indexes on an IOT. The only valid index being the primary key index. As of Oracle 8i you can create secondary indexes on IOTs. An IOT will require less space than a regular table since there are no RowIDs.

Suppose you create the following IOT:

CREATE TABLE iottest
(areacode CHAR(3) primary key,
city VARCHAR2 (50))
ORGANIZATION INDEX TABLESPACE users
PCTTHRESHOLD 10
OVERFLOW TABLESPACE users;

An IOT is appropriate if you will always be accessing the areacode information by the areacode column in the where clause of your queries. To limit the amount of internal management with the IOT you should only create an IOT if the data is very static. If the data in your table changes frequently you should use a regular table with indexes. In general, IOTs are most efffective when the primary key constitutes a large part of the tables columns.If the IOT contains many frequently accessed columns that are not part of the primary key, then the IOT will need to repeatedly access it's overflow area.

Use of the pctthreshold clause is optional. When specified, pctthreshold indicates a threshold limit that any row of data can occupy in an index-organized table block.In the example, the value set for this clause is 10, which means that any row in the table that takes up 10 percent or more of its data block for storage will have nonkey column data moved into an overflow tablespace defined using the overflow tablespace keywords. If overflow tablespace is specified without defining a pctthreshold, the default pctthreshold of 50 will be used.A row bigger than pctthreshold in an IOT might destroy the dense storage of the rows in the index. In cases where this condition might occur, an overflow tablespace clause should be used.

To retrieve Oracle8
IOT information from the Data Dictionary issue the following SQL:
-----------------------------------------------------------------
SELECT t.table_name AS ?IOT?, o.table_name AS ?Overflow?,
i.index_name AS ?Index?, o.tablespace_name AS ?Overflow TS?,
i.tablespace_name AS ?Index TX?, i.pct_threshold
FROM dba_tables t, dba_tables o, dba_indexes i
WHERE t.owner = o.owner
AND t.table_name = o.iot_name
AND t.owner = i.owner
AND t.table_name = i.table_name AND t.owner = ?SCOTT?;

To retrieve post Oracle8

IOT information from the Data Dictionary issue the following SQL:
-----------------------------------------------------------------

SQL> SELECT table_name, tablespace_name, iot_name, iot_type
FROM dba_tables
where table_name = 'IOTTEST'

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
IOT_NAME IOT_TYPE
------------------------------ ------------
IOTTEST
IOT
SQL> SELECT object_name, object_id
FROM dba_objects
WHERE object_name = 'IOTTEST';

OBJECT_NAME OBJECT_ID
------------------------------------- ---------
IOTTEST 74858

SQL> SELECT index_name, index_type, tablespace_name, table_name
FROM dba_indexes
WHERE table_name = 'IOTTEST';

INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
TABLESPACE_NAME TABLE_NAME
------------------------------ ------------------------------
SYS_IOT_TOP_74858 IOT - TOP
USERS IOTTEST


Now using the object_id obtained from dba_objects

SQL> SELECT segment_name, tablespace_name, segment_type
FROM dba_segments
WHERE segment_name like '%74858%';

SEGMENT_NAME
--------------------------------------------------------------
TABLESPACE_NAME SEGMENT_TYPE
------------------------------ ------------------
SYS_IOT_OVER_74858
USERS TABLE

SYS_IOT_TOP_74858 USERS INDEX

IOT Restrictions:
-----------------
1. A primary key must be specified otherwise ORA-25175 is returned.
2. If pctthreshold is defined and an overflow segment is not defined,
rows exceeding the threshold are rejected with an ORA-1429 error.
3. Cannot create an IOT of object types.
4. An IOT can contain columns of LOB and nested table types, but only
if the table is not partitioned.
5. IOTs must be reorganized using the move clause of the alter table
command.

in Vs Exists???

The two are processed very very differently.

Select * from T1 where x in ( select y from T2 )

is typically processed as:

select * from t1, ( select distinct y from t2 ) t2
where t1.x = t2.y;

The subquery is evaluated, distinct'ed, indexed (or hashed or sorted) and then joined to the original table -- typically.

As opposed to

select * from t1 where exists ( select null from t2 where y = x )

That is processed more like:

for x in ( select * from t1 ) loop
if ( exists ( select null from t2 where y = x.x ) then
OUTPUT THE RECORD
end if
end loop

It always results in a full scan of T1 whereas the first query can make use of an index on T1(x).

So, when is where exists appropriate and in appropriate?

Lets say the result of the subquery
( select y from T2 )

is "huge" and takes a long time. But the table T1 is relatively small and executing ( select null from t2 where y = x.x ) is very very fast (nice index on t2(y)). Then the exists will be faster as the time to full scan T1 and do the index probe into T2 could be less then the time to simply full scan T2 to build the subquery we need to distinct on.

Lets say the result of the subquery is small -- then IN is typicaly more appropriate.

If both the subquery and the outer table are huge -- either might work as well as the other -- depends on the indexes and other factors.

Clustered table???

Clustered table
A clustered table provides an optional method for storing table data. A cluster is made up of a table or group of tables that share the same data blocks, which are grouped together because they share common columns and are often used together.

Clusters have the following characteristics:

· Clusters have a cluster key, which is used to identify the rows that need to be stored together.

· The cluster key can consist of one or more columns.

· Tables in a cluster have columns that correspond to the cluster key.

· Clustering is a mechanism that is transparent to the applications using the tables. Data in a clustered table can be manipulated as though it were stored in a regular table.

· Updating one of the columns in the cluster key may migrate the row.

· The cluster key is independent of the primary key. The tables in a cluster can have a primary key, which may be the cluster key or a different set of columns.

· Clusters are usually created to improve performance. Random access to clustered data may be faster, but full table scans on clustered tables are generally slower.

· Clusters renormalize the physical storage of tables without affecting the logical structure.

When to Rebuild an Index???

You have to periodically check your indexes to see if they become skewed and,therefore, good candidates for rebuild.

A skewed index has many records clumped close together on the index tree due to their similar indexed values. When an index is skewed, parts of an index are accessed more frequently than others. As a result, disk contention may occur, creating a bottleneck in performance. It is important to periodically examine your indexes to determine if they have become skewed and might need to be rebuilt.

Here is a sample procedure on how to identify the skewed indexes:

1. Gather statistics on your indexes. For large indexes (over one hundred thousand records in the underlying table), use ESTIMATE instead of COMPUTE STATISTICS.

For example:

SQL> analyze index A1_PK compute statistics;

Index analyzed.

2. Run the script given below - index_check.sql - to find out how skewed each index is.

This query checks on all indexes that belong to user SCOTT:

SQL> select index_name, blevel,
decode(blevel,0,'OK BLEVEL',1,'OK BLEVEL',
2,'OK BLEVEL',3,'OK BLEVEL',4,'OK BLEVEL','BLEVEL HIGH') OK
from dba_indexes
where owner='SCOTT';

INDEX_NAME BLEVEL OK
---------------------------------------- ------ ----
A1_PK BLEVEL HIGH
A1_UK BLEVEL HIGH
BUDVERPORT_BUDVERPORT2_UK 1 OK BLEVEL
BUDVERPORT_BV_FK_I 2 OK BLEVEL
BUDVERPORT_CHAR_CL_FK_I 1 OK BLEVEL
BUDVERPORT_DIRCTE_FK_I 3 OK BLEVEL
BUDVERPORT_FUND_TYPE_FK_I 1 OK BLEVEL
BUDVERPORT_OMB_SUBFCT_FK_I 1 OK BLEVEL
BUDVERPORT_ORG_FK_I 0 OK BLEVEL
BUDVERPORT_PL_TITLE_FK_I 1 OK BLEVEL
BUDVERPORT_RDC_FK_I 1 OK BLEVEL
S_INVENTORY_PRODID_WARID_PK BLEVEL HIGH
S_ITEM_ORDID_ITEMID_PK BLEVEL HIGH
S_ITEM_ORDID_PRODID_UK BLEVEL HIGH
S_LONGTEXT_ID_PK BLEVEL HIGH
S_ORD_ID_PK BLEVEL HIGH
S_PRODUCT_ID_PK BLEVEL HIGH
S_PRODUCT_NAME_UK BLEVEL HIGH
S_REGION_ID_PK BLEVEL HIGH
S_REGION_NAME_UK BLEVEL HIGH
S_TITLE_TITLE_PK BLEVEL HIGH
S_WAREHOUSE_ID_PK BLEVEL HIGH

3. The BLEVEL (or branch level) is part of the B-tree index format and relates to the number of times Oracle has to narrow its search on the index while searching for a particular record. In some cases, a separate disk hit is requested for each BLEVEL. Prior to 9i, if the BLEVEL is more than 4,it is recommended to rebuild the index. As database are getting bigger and bigger, BLEVEL may not be a good indicator of time to rebuild the index. BLEVEL > 4 may suggest an evaluation of whether the partitioning option could help you.

Note: If you do not analyze the index, the index_check.sql script will show "BLEVEL HIGH" for such an index.

4. Gather more index statistics using the VALIDATE STRUCTURE option
of the ANALYZE command to populate the INDEX_STATS virtual table. This table does not contain an OWNER column and assumes you are looking for statistics for indexes created by your active session only.

SQL> analyze index SCOTT.ORG_PK validate structure;

Index analyzed.

SQL> select DEL_LF_ROWS*100/decode(LF_ROWS, 0, 1, LF_ROWS)
PCT_DELETED, (LF_ROWS-DISTINCT_KEYS)*100/
decode(LF_ROWS,0,1,LF_ROWS) DISTINCTIVENESS
from index_stats
where NAME='&index_name';

Enter value for index_name: ORG_PK

PCT_DELETED DISTINCTIVENESS
----------- ---------------
0 0

The PCT_DELETED column shows what percent of leaf entries (index
entries) have been deleted and remain unfilled. The more deleted
entries exist on an index, the more unbalanced the index becomes.
If the PCT_DELETED is 20% or higher, the index is candidate for
rebuilding.If you can afford to rebuild indexes more frequently,
then do so if the value is higher than 10%. Leaving indexes with
high PCT_DELETED without rebuild might cause excessive redo
allocation on some systems.

The DISTINCTIVENESS column shows how often a value for the
column(s) of the index is repeated on average. For example, if a
table has 10000 records and 9000 distinct SSN values, the formula
would result in (10000-9000) x 100 / 10000 = 10. This shows a good
distribution of values.If, however, the table has 10000 records
and only 2 distinct SSN values, the formula would result in
(10000-2) x 100 /10000 = 99.98. This shows that there are very few
distinct values as a percentage of total records in the column.
Such columns are not candidates for a rebuild but good candidates
for bitmapped indexes.

Friday, February 13, 2009

How to check Corruption in Database Using DBVERIFY

The primary tool for checking for corruption in an Oracle database is DBVERIFY. It can be used to perform a physical data structure integrity check on data files whether the database is online or offline. The big benefit of this is that DBVERIFY can be used to check backup data files without adding load to the database server. You invoke DBVERIFY from the operating system command line like this:

$ dbv file=data01.dbf logfile=verify.log blocksize=8192 feedback=100

In this example data01.dbf is the data file to check, and the tablespace this file belongs to has a block size of 8192 bytes. The feedback parameter tells DBVERIFY to draw a period on the screen after every 100 pages (blocks) of the file are verified.
In the log file you’ll see output like this:

DBVERIFY - Verification starting : FILE = data01.dbf

DBVERIFY - Verification complete
Total Pages Examined : 640
Total Pages Processed (Data) : 631
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 9
Total Pages Empty : 0
Total Pages Marked Corrupt : 0
Total Pages Influx : 0

The Total Pages Failing values show the number of blocks that failed either the data block or index block checking routine. The Total Pages Marked Corrupt figure shows the number of blocks for which the cache header is invalid, thereby making it impossible for DBVERIFY to identify the block type. And the Total Pages Influx is the number of blocks for which DBVERIFY could not get a consistent image. (This could happen if the database is open when DBVERIFY is run. DBVERIFY reads blocks multiple times to try to get a consistent image, but DBVERIFY cannot get a consistent image of pages that are in flux.)
If you want to verify only a portion of a data file, you can specify a starting and ending block when running DBVERIFY. If you want to verify the entire database, you can generate a short shell script to run DBVERIFY on every data file in the database.
You can do this easily using SQL*Plus:
SQL> SPOOL dbv_on_all_files.sh
SQL> SELECT ‘dbv file=’ file_name
2 ‘ logfile=file’ ROWNUM
3 ‘.log blocksize=8192′
4 FROM dba_data_files;
SQL> SPOOL OFF
After running the shell script you can quickly scan all of the DBVERIFY log files with Unix commands like:
$ grep Failing file*.log
$ grep Corrupt file*.log
$ grep Influx file*.log
You can also use DBVERIFY to validate a single data or index segment. To do this you must be logged onto the database with SYSDBA privileges. During the verification the segment is locked; if the segment is an index then the parent table is also locked.

There are other ways to check for database corruption besides DBVERIFY. You can take a full database export, with the dump file optionally specified as a null device. This will read every row in every user table in the database, discovering any corrupted data blocks along the way. However, this technique does not access every index entry or the entire data dictionary.

If you want to check one table and all of its indexes, you can use the ANALYZE statement to read every row of the table, read every entry in each of the table’s indexes, and make sure the table and index data are consistent with each other:

SQL> ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE;
This will lock the table, preventing DML on the table, unless you specify the ONLINE keyword. Online validation reduces the amount of validation performed to allow for concurrency.
There are several ways to check for corruption in an Oracle database, but the DBVERIFY tool is the most versatile. DBVERIFY does not limit concurrency or DML while it is running, and it can be run against a database backup. Just remember that if DBVERIFY detects corruption in your database and you are planning to recover the corrupt file from a backup, you should perform a DBVERIFY validation on the backup file before beginning the recovery. This will tell you if the corruption exists in the backup also.

Upgrading OracleAS 10g Portal from 10.1.2.0.2 to 10.1.4.2 on Windows

Introduction
Upgrading Portal can be a puzzle and a challenge, and it's not always fun.

This guide assumes that:
you have a fresh installation of Oracle Application Server 10g Release 2 (10.1.2.0.2)

Important:
Throughout these instructions, you need to replace example paths (e.g. "D:\ora101202mt") with your paths, example instance names (e.g. "ora101202mt") with your instance names, and example server names (e.g. "donald.disney.com") with your server names, unless you actually use similar values as the ones showed here (which makes things much easierWhy?). To be on the safe side, avoid using spaces or periods in your permanent and temporary paths!

You should always read the documentation and notes that come with all the products and patches so that you may be prepared for some of the expected and unexpected situations!

Before you begin
1. If you have not rebooted since you started installing Application Server, then reboot now.

2. Ensure that the database, TNS and Infrastructure processes are running:
net start OracleServiceORCL
net start Oracleora102dbTNSListener
cd /d D:\ora101202infra\opmn\binopmnctl startall

3. Ensure that all Middle-Tier processes are stopped:
cd /d D:\ora101202mt\opmn\bin
opmnctl stopall

Download software
Portal Upgrade (10.1.4) - 176 MB
Patch 5983622 (OracleAS 10.1.2.3) - 1.7 GB

Upgrading Portal to 10.1.4
1. If your metadata repository database is version 10.2.0.2 or higher, you need to edit C:\stage\mrua_051020\mrua\mrua.xml, adding the following entry (using version 10.2.0.2 as an example):
<Version ReleaseNumber="10.2.0.2"
DisplayName="OracleAS supported database">
</Version>
For more information, see Note 361176.1 on MetaLink.

2. Run the upgrade script (check the parameter values by inspecting the ias.properties file in the D:\ora101202infra\config folder):
cd /d C:\stage\mrua_051020\mrua
mrua.bat -oracle_home D:\ora101202infra -oid_host donald.disney.com -oid_ssl_port 636

3. When prompted, enter the password for the database SYS user account, e.g. welcome1

4. When prompted, enter the Oracle Internet Directory administrator password, e.g. welcome1

5. When the script finishes (after approximately 30 minutes), check the upgrade.log file in the D:\ora101202infra\upgrade\temp\portal folder

6. Review the Post-Upgrade Tasks in the online documentation.

7. If you would like to configure Oracle Text Indexes to Synchronize on Commit:
cd /d D:\ora101202mt\portal\admin\plsql\wws

sqlplusw portal@orclexec

wwv_context.commit_sync('WWSBR_CORNER_CTX_INDX', true);exec wwv_context.commit_sync('WWSBR_DOC_CTX_INDX', true);exec wwv_context.commit_sync('WWSBR_PERSP_CTX_INDX', true);exec wwv_context.commit_sync('WWSBR_THING_CTX_INDX', true);exec wwv_context.commit_sync('WWSBR_TOPIC_CTX_INDX', true);exec wwv_context.commit_sync('WWSBR_URL_CTX_INDX', true);

@textstat.sql

8. If you need to add text elements, please see "Configuring OracleAS 10g (10.1.2.0.2)".

Upgrading OracleAS to 10.1.2.3
1. Download Patch 5983622 (1.7 GB)
2. Unzip patch to C:\stage\ias\p5983622

Middle-Tier
1. Start Installer, e.g. C:\stage\ias\p5983622\Disk1\setup.exe
2. Click Next
3. As Destination, select ora101202mt (folder D:\ora101202mt)
4. Click Next
5. Select Oracle Application Server and Developer Suite 10g Rel 2 Software Update 10.1.2.3.0 (already selected)
6. Click Next
7. Enter ias_admin password
8. Click Next
9. Enter password for cn=orcladmin
10. Click Next
11. Click OK to accept that processes will be shut down
12. If Windows Security Alert pops up, click Unblock
13. Click Install
14. When update is finished, click Exit and then Yes

Infrastructure
1. Stop Middle-Tier processes and all other clients
2. Start Installer, e.g. C:\stage\ias\p5983622\Disk1\setup.exe
3. Click Next
4. As Destination, select ora101202infra (folder D:\ora101202infra)
5. Click Next
6. Select Oracle Application Server and Developer Suite 10g Rel 2 Software Update 10.1.2.3.0 (already selected)
7. Click Next
8. Enter ias_admin password
9. Click Next
10. Enter password for cn=orcladmin
11. Click Next
12. Enter password for Oracle Certificate Authority Administrator
13. Click Next
14. Enter password for the SYS schema in the Metadata Repository database
15. Click Next
16. Click OK to accept that processes will be shut down
17. If Windows Security Alert pops up, click Unblock
18. Click Install
19. When update is finished, click Exit and then Yes

Upgrading Portal Metadata Repository to 10.1.4.2
1. Verify that component PORTAL is found in app_registry:
connect sys@orcl as sysdba
select version, status from app_registry where comp_id = 'PORTAL';
2. Stop Middle-Tier processes and clients, but leave Infrastructure and Database processes running
3. Start Installer, e.g. C:\stage\ias\p5983622\Disk1\setup.exe
4. Click Next
5. As Destination, select ora101202mt (folder D:\ora101202mt)
6. Click Next
7. Select Oracle Application Server 10g Portal Metadata Repository Update
8. Click Next
9. Click OK to verify that the database version is correct and that the software update has been performed on the Middle-Tier and the Infrastructure
10. Select Immediately install the update (already selected)
11. Click Next
12. Enter ias_admin password
13. Click Next
14. Enter password for cn=orcladmin
15. Click Next
16. Click Next to accept the Database Connection String (e.g. donald.disney.com:1521:orcl.disney.com:ORCL.DISNEY.COM)
17. Click Next
18. Enter password for the SYS schema in the Metadata Repository database
19. Click Next
20. Select OracleAS 10g Portal (schema PORTAL) and OracleAS 10g Wireless (schema WIRELESS)
21. Click Next
22. Click OK to verify that processes have been stopped
23. Click Install
24. When update is finished, click Exit and then Yes

More Information
Staging a Test Environment from a Production Environment
Oracle Application Server Portal Installation and Upgrade Guide
OracleAS Portal Upgrade
Readme for Patch 4960210
Note 260554.1: Master Note for OracleAS Portal Install Issues
Note 262623.1: Master Note for OracleAS Portal Upgrade Issues
Note 281400.1: Troubleshooting Portal Errors when applying MR Updates from Oracle AS Patchsets 9.0.4.x / 10.1.2.x
Note 361176.1: Database Version 10.2.0.2.0 or 10.2.0.3.0 Is Not Supported When Upgrading Portal to 10.1.4
Note 276620.1: How to "clone" a Portal Repository
Note 249212.1: Support Position for Oracle Products Running on VMWare Virtualized Environments

Upgrade the Oracle database from 10.1.0.2.0 to 10.1.0.5.0

This article describes the process of upgrading the Oracle Database Server 10g Release 10.1.0.2 to Oracle Database 10g Release 10.1.0.5 on Windows XP SP2.

1. Patch Set Overview
You can apply the Oracle Database 10g release 10.1.0.5 patch set to the following Oracle Database 10g release 1 (10.1.0.2, 10.1.0.3, or 10.1.0.4) installations.

2. Oracle Universal Installer Version Requirements
This patch set includes Oracle Universal Installer release 10.1.0.5, which is also installed when you install this patch set.

3. System Requirements
Hardware requirement : Intel Pentium
OS requirement : Windows XP Prof
Oracle DB : 10gr1(10.1.0.2) or later

4. Pre-installation Tasks
I. Identify the Oracle Database Installation
This is not a complete software distribution. You must install it in an existing Oracle Database 10g Oracle home.

II. Download and Extract the Installation Software
Download and extract the p4505133_10105_WINNT.zip patch set.

III. Shut Down Oracle Databases
C:\>sqlplus sys@ORCL/pwd as sysdba
SQL>shutdown immediate;
C:\>sqlplus sys@CATDB/pwd as sysdba
SQL>shutdown immediate;

IV. Stop All Services
C:\>net stop OracleOraDb10g_home1iSQL*Plus
..
The OracleOraDb10g_home1iSQL*Plus service was stopped successfully.
C:\>net stop OracleServiceORCL
The following services are dependent on the OracleServiceORCL service.
Stopping the OracleServiceORCL service will also stop these services.
OracleDBConsoleorcl
Do you want to continue this operation? (Y/N) [N]: Y
The OracleDBConsoleorcl service is stopping....
The OracleDBConsoleorcl service was stopped successfully.
The OracleServiceORCL service is stopping.
The OracleServiceORCL service was stopped successfully.
C:\>net stop OracleServiceCATDB
The OracleServiceCATDB service is stopping.......
The OracleServiceCATDB service was stopped successfully.

V. Stop the listener:
C:\>LSNRCTL stop
LSNRCTL for 32-bit Windows: Version 10.1.0.2.0 - Production on 13-JUN-2007 16:30
:58
Copyright (c) 1991, 2004, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
The command completed successfully
C:\>net stop OracleCSService
The OracleCSService service was stopped successfully.

VI. Back up the SYSTEM.

5.Installation Tasks
I. Run SETUP.EXE from where you download/extract your patch.
In the Name field in the Destination section, select the 10.1.0.x Oracle home that you want to update from the drop down list, then click Next.

On the Summary screen, click Install.

Upgrading Release 10.1 to Oracle Database 10g Release 1 (10.1.0.5)

1.Check SYSTEM TBS should have 10mb Free Space.
2.SHARED_POOL_SIZE or JAVA_POOL_SIZE at least 150 MB.

SQL> alter tablespace SYSTEM
2 add datafile 'c:\oracle\product\10.1.0\oradata\orcl\system02.dbf' size 20m;
Tablespace altered.

SQL> alter system set SHARED_POOL_SIZE = 150M SCOPE=spfile;
System altered.

SQL> alter system set JAVA_POOL_SIZE = 150M SCOPE=spfile;
System altered.

SQL> SHUTDOWN
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP UPGRADE
ORACLE instance started.
Total System Global Area 356515840 bytes
Fixed Size 789400 bytes
Variable Size 330036328 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.

SQL> SPOOL patch.log

SQL> @C:\oracle\product\10.1.0\db_1\rdbms\admin\catpatch.sql
DOC>######################################################################
DOC>######################################################################
DOC> The following PL/SQL block will cause an ORA-20000 error and
DOC> terminate the current SQLPLUS session if the user is not SYS.
DOC> Disconnect and reconnect with AS SYSDBA.
DOC>######################################################################DOC>######################################################################DOC>#PL/SQL procedure successfully completed
.....
[OUTPUT CUT]

SQL> SPOOL OFF

Restart the database
SQL>SHUTDOWN
SQL>STARTUP

Run the utlrp.sql script to recompile all invalid PL/SQL packages
SQL> @C:\oracle\product\10.1.0\db_1\rdbms\admin\utlrp.sql
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Prod
PL/SQL Release 10.1.0.5.0 - Production
CORE 10.1.0.5.0 Production
TNS for 32-bit Windows: Version 10.1.0.5.0 - Production
NLSRTL Version 10.1.0.5.0 - Production

Known Issues
select dbms_java.full_ncomp_enabled from dual
*
ERROR at line 1:
ORA-29558: JAccelerator (NCOMP) not installed. Refer to Install Guide for
instructions.
ORA-06512: at "SYS.DBMS_JAVA", line 236

IGNORE
---> If you do not use Java in the database, then ignore this error message.

Don't Ignore
--->If you use Java in the database, then install the NCOMP libraries by selecting the Oracle Database 10g Products installation type from the Oracle Database Companion CD.

If RMAN CATALOG is also configure then upgrade RMAN CATALOG
C:\>rman CATALOG=rman/cat@CATDB
Recovery Manager: Release 10.1.0.5.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
connected to recovery catalog database
RMAN> UPGRADE CATALOG;
recovery catalog owner is RMAN
enter UPGRADE CATALOG command again to confirm catalog upgrade
RMAN> UPGRADE CATALOG;
recovery catalog upgraded to version 10.01.00
DBMS_RCVMAN package upgraded to version 10.01.00
DBMS_RCVCAT package upgraded to version 10.01.00