Tuesday, September 27, 2011

Oracle database patches

Ah, this is a post of just blah blah and no code at all, but I think it would be helpful to write down all I know about Oracle database patching.

First of all, I'll assume that you have a good reason to patch an Oracle database because not all the patches released are trouble-free, therefore sometimes the fix is worse than the bug. If you can, check a patch in a test database or at least wait some time to see what other people got applying that patch.

Next, it is necessary to have a Metalink account and rights to download patches; having a Metalink account is not enough, and your company has to have an Oracle support contract in order to grant you access to Metalink patches. But there are support contracts and support contracts; if you think that having a Premier Support contract entitles you to download all the patches you would ever need it might be better to read the Metalink article How Patches and Updates Entitlement Works. If you can't get a patch (by Metalink or otherwise), then it's almost useless to know what the current patch is.

Oracle patch information is somewhat difficult to understand, but if you need to know information about patch releases you can check the Critical Patch Updates and Security Alerts page. Here in the Critical Patch Updates section you can learn when is going to be released the next patch (every three months), and you can also subscribe to the RSS feed or email list in order to get this information automatically.

And in the Security Alerts section you will know if there is an outstanding patch for some serious security bug; if you see a security alert after the last patch release it might be worth checking it an applying the patch, but if you can wait for the next patch release it will be included there.

Then, if you keep following the links you will get detailed information about patches, but if you just want to know what the latest PSU is it would be easier to check the Metalink document Oracle Recommended Patches -- Oracle Database; this way you can get quickly the last patch for your database release. And if you want to know which patch is for what release, you can check it in the Metalink document Quick Reference to Patchset Patch Numbers. Finally, if you want to upgrade your database from version X to version Y and you're not sure, it would be helpful to check the Oracle Database Upgrade Path Reference List.

I hope this information will be helpful for someone; as far as I know this knowledge is not included in any Oracle Database certification test even being so important, and there is not a lot books dealing with this.

Monday, September 19, 2011

Example of SQL*Loader

SQL*Loader is an Oracle tool to load text files into a database, therefore you might need to use it sometimes. It's very easy to use and the following example is almost self-explanatory.

First, you have a table like this in your datadabase:

SQL> describe my_table
Name Null? Type
----------------------------------------- -------- ----------------------------
SOMEDATE DATE
FIELD1 VARCHAR2(256)
FIELD2 VARCHAR2(256)


And you want to load data like this from plain text files:

08/10/2011|00:09:52|Some data|THRHTRDHF
08/15/2011|00:10:57|More data|NLHIJKUYJ
08/22/2011|00:11:00|This data|XEFGRGCG
08/26/2011|00:12:27|A lot of data|TVGT

First you have to write a small control file for sqlldr, something like this:

LOAD DATA
APPEND
INTO TABLE my_table
FIELDS TERMINATED BY "|" TRAILING NULLCOLS
(
SOMEDATE "to_date(:SOMEDATE, 'MM/DD/YYYY HH24:MI:SS')",
FIELD1 ,
FIELD2
)

Here you say that you want to append some data in my_table, separated by |, and converts the date found in text files with to_date function. But wait! You might have noticed that there are four fields per line and just three fields in the table; you need to do some basic formatting to the text files before loading them with sqlldr.

With this small script the formatting and loading of files is automated, and you just need to run it with the text file name as a parameter in order to get loaded the file:

conn="myuser/mypass@orcl"

cd /mydir/myloader
mknod loader.pipe p
cat $1|sed 's/|/ /' > loader.pipe &
sqlldr $conn skip=0 errors=999999999 data=loader.pipe control=loader.ctl log=loader.log bad=loader.bad
wait
rm loader.pipe

This way you place the loader script in /mydir/myloader, and the script creates a named pipe to feed sqlldr with sed's formatted data like this:

08/10/2011 00:09:52|Some data|THRHTRDHF
08/15/2011 00:10:57|More data|NLHIJKUYJ
08/22/2011 00:11:00|This data|XEFGRGCG
08/26/2011 00:12:27|A lot of data|TVGT

With a script you can uncompress files on the fly or do more complex transformations to text files before loading them, and in this case you're instructing sqlldr to load as many records as it can ignoring errors (errors=999999999) and to write bad records to a file (bad=loader.bad).

Finally, running this script looks like this:

oracle@myserver:~$ /mydir/myloader/loader /otherdir/file.txt

SQL*Loader: Release 11.1.0.7.0 - Production on Mon Sep 19 11:33:03 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Commit point reached - logical record count 4

More information:

SQL*Loader FAQ
SQL*Loader Command-Line Reference

Wednesday, September 14, 2011

Creating a basic Oracle database

Oracle has good tools to help you create databases (like Enterprise Manager or DBCA), but if you want to create a database by hand or using scripts and you want just the bare database with no extra components, then you may want to check this method. It's almost the same for Oracle 10g and 11g databases, and if you're new to Oracle is a good way to understand how it works.

We are going to create a 10g database for this example, and the first step is to create a Pfile for this database and place it under $ORACLE_HOME/dbs; you have to name it initDBNAME.ora where DBNAME is the name of your database. If you need Pfile examples you can check this example for 10g and also this example for 11g.

Next we need to create some directories for the database (for 11g just $ORACLE_BASE/oradata/example):

oracle@myserver:~$ mkdir -p $ORACLE_HOME/admin/example/bdump
oracle@myserver:~$ mkdir $ORACLE_HOME/admin/example/cdump
oracle@myserver:~$ mkdir $ORACLE_HOME/admin/example/udump
oracle@myserver:~$ mkdir $ORACLE_HOME/oradata/example

Before creating the database we also need to set environment variables:

oracle@myserver:~$ export ORACLE_SID=example
oracle@myserver:~$ set|grep ORA
ORACLE_BASE=/opt/oracle/app/oracle
ORACLE_HOME=/opt/oracle/app/oracle/product/10.2.0/db_1
ORACLE_SID=example

Now we start the new database in nomount mode and create it:

oracle@myserver:~$ sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Sep 13 15:11:12 2011

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1272600 bytes
Variable Size 88081640 bytes
Database Buffers 75497472 bytes
Redo Buffers 2920448 bytes
SQL> CREATE DATABASE example
MAXDATAFILES 500
MAXINSTANCES 1
MAXLOGFILES 10
MAXLOGMEMBERS 4
DATAFILE
'?/oradata/example/system01.dbf' size 512M
DEFAULT TEMPORARY TABLESPACE TEMP
TEMPFILE '?/oradata/example/temp01.dbf' size 256M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
LOGFILE
GROUP 1 (
'?/oradata/example/redo01a.rdo',
'?/oradata/example/redo01b.rdo') size 64M,
GROUP 2 (
'?/oradata/example/redo02a.rdo',
'?/oradata/example/redo02b.rdo') size 64M,
GROUP 3 (
'?/oradata/example/redo03a.rdo',
'?/oradata/example/redo03b.rdo') size 64M
SYSAUX
DATAFILE '?/oradata/example/sysaux01.dbf' size 512M
UNDO TABLESPACE UNDO
DATAFILE '?/oradata/example/undo01.dbf' size 512M
/

Database created.

Of course you have to set file names that match the oradata directory created, modify sizes of files if they are too small, place files in proper places like redo logs in different filesystems, and finally modify or add parameters according to your specific needs.

Next we create the USERS tablespace and the database catalog:

SQL> CREATE TABLESPACE USERS LOGGING DATAFILE
'?/oradata/example/users.dbf' SIZE 256M REUSE AUTOEXTEND ON NEXT 16M MAXSIZE 512M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created.

SQL> @?/rdbms/admin/catalog

...

Grant succeeded.


PL/SQL procedure successfully completed.

SQL> @?/rdbms/admin/catproc

...

1 row created.


PL/SQL procedure successfully completed.

SQL> @?/rdbms/admin/catexp

...

0 rows created.


Commit complete.

SQL> @?/rdbms/admin/catblock

...

Synonym created.


Grant succeeded.

SQL> connect system/manager
Connected.
SQL> @?/sqlplus/admin/pupbld

...

DROP PUBLIC SYNONYM PRODUCT_USER_PROFILE
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist



Synonym created.

SQL> @?/rdbms/admin/catdbsyn

...

Synonym created.


Synonym created.

SQL> exit;
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@myserver:~$ sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Sep 13 15:39:32 2011

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @?/rdbms/admin/utlrp.sql

..

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


PL/SQL procedure successfully completed.

Your new database is almost complete, you just have to create an SPfile and check installed components:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1272600 bytes
Variable Size 88081640 bytes
Database Buffers 75497472 bytes
Redo Buffers 2920448 bytes
Database mounted.
Database opened.
SQL> create spfile from pfile;

File created.

SQL> startup force;
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1272600 bytes
Variable Size 88081640 bytes
Database Buffers 75497472 bytes
Redo Buffers 2920448 bytes
Database mounted.
Database opened.
SQL> set pagesize 9999
SQL> col comp_id for a10
SQL> col comp_name for a40
SQL> col version for a15
SQL> column status format a10
SQL> select comp_id, comp_name, version, status from dba_registry;

COMP_ID COMP_NAME VERSION STATUS
---------- ---------------------------------------- --------------- ----------
CATALOG Oracle Database Catalog Views 10.2.0.5.0 VALID
CATPROC Oracle Database Packages and Types 10.2.0.5.0 VALID

And if you like, you can configure PERFSTATS this way:

SQL> CREATE TABLESPACE PERFSTAT DATAFILE
'?/oradata/example/perfstat.dbf' SIZE 192M AUTOEXTEND ON NEXT 16M MAXSIZE 512M
NOLOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON;

Tablespace created.

SQL> define default_tablespace='PERFSTAT'
SQL> define temporary_tablespace='TEMP'
SQL> define perfstat_password='perfstat'
SQL> @?/rdbms/admin/spcreate.sql

...

No errors.

NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.

That's all! You have a minimum brand-new database ready to use; just don't forget adding your database to the oratab file, creating a listener for this database, changing default passwords and disabling default accounts, and doing any administrative thing like these required.

Monday, September 12, 2011

Uninstalling components of an Oracle database

Here is a detailed guide about removing components of an Oracle database, but first I would like to state that I'm not a big fan of this (it would be better to create a database with just the components you need), and:

- Oracle in general discourages removing components of a database;
- this procedure is a bit different from Oracle documentation;
- you have to have a good understanding of Oracle Database software;
- you have to make a cold backup of your databases before trying this procedure;
- and it might be better to have a good reason for uninstalling components just in case you end with a corrupted database.

Having said that, the following procedure takes a 11g database with a lot of components and example schemas and ends with just the database engine. This is not intended to run as a script and it would be better to execute steps in the same order and to try it first in a test database.

Lets start with getting information about all the installed components:

SQL> set linesize 150
SQL> set pagesize 9999
SQL> col comp_id for a10
SQL> col comp_name for a40
SQL> col version for a15
SQL> select comp_id, comp_name, version, status from dba_registry;

COMP_ID COMP_NAME VERSION STATUS
---------- ---------------------------------------- --------------- ---------------------------------
OWB OWB 11.2.0.1.0 VALID
APEX Oracle Application Express 3.2.1.00.10 VALID
EM Oracle Enterprise Manager 11.2.0.1.0 VALID
AMD OLAP Catalog 11.2.0.1.0 VALID
SDO Spatial 11.2.0.1.0 VALID
ORDIM Oracle Multimedia 11.2.0.1.0 VALID
XDB Oracle XML Database 11.2.0.1.0 VALID
CONTEXT Oracle Text 11.2.0.1.0 VALID
EXF Oracle Expression Filter 11.2.0.1.0 VALID
RUL Oracle Rules Manager 11.2.0.1.0 VALID
OWM Oracle Workspace Manager 11.2.0.1.0 VALID
CATALOG Oracle Database Catalog Views 11.2.0.1.0 VALID
CATPROC Oracle Database Packages and Types 11.2.0.1.0 VALID
JAVAVM JServer JAVA Virtual Machine 11.2.0.1.0 VALID
XML Oracle XDK 11.2.0.1.0 VALID
CATJAVA Oracle Database Java Packages 11.2.0.1.0 VALID
APS OLAP Analytic Workspace 11.2.0.1.0 VALID
XOQ Oracle OLAP API 11.2.0.1.0 VALID

18 rows selected.

Next, we can drop example schemas if not needed:

SQL> drop user OE cascade;

User dropped.

SQL> drop user SH cascade;

User dropped.

SQL> drop user BI cascade;

User dropped.

SQL> drop user HR cascade;

User dropped.

SQL> drop user SCOTT cascade;

User dropped.

To remove Oracle Application Express we can do this:

SQL> @?/apex/apxremov.sql
...Removing Application Express
old 1: alter session set current_schema = &APPUN
new 1: alter session set current_schema = APEX_030200

Session altered.


PL/SQL procedure successfully completed.


no rows selected

old 2: if '&UPGRADE' = '1' then
new 2: if '1' = '1' then

PL/SQL procedure successfully completed.


Session altered.


PL/SQL procedure successfully completed.

old 1: drop user &APPUN cascade
new 1: drop user APEX_030200 cascade

User dropped.

old 2: if '&UPGRADE' = '1' then
new 2: if '1' = '1' then

PL/SQL procedure successfully completed.

old 5: if '&UPGRADE' = '1' then
new 5: if '1' = '1' then

PL/SQL procedure successfully completed.

old 2: if '&UPGRADE' = '1' then
new 2: if '1' = '1' then

PL/SQL procedure successfully completed.

...Application Express Removed
SQL> drop package HTMLDB_SYSTEM;

Package dropped.

SQL> drop PUBLIC SYNONYM HTMLDB_SYSTEM;

Synonym dropped.

SQL> select owner, object_type, status, count(*) from dba_objects
where status <> 'VALID' group by owner, object_type, status;

no rows selected

SQL> select comp_id, comp_name, version, status from dba_registry where comp_id='APEX';

no rows selected

As you can see, at the end of the procedure there are no invalid objects and the APEX component is not in the registry anymore; in general you have to look for invalid objects every time you remove a component and also check dba_registry to be sure the component was removed properly.

Next, to remove Oracle Workspace Manager component you can do the following steps:

SQL> @?/rdbms/admin/owmuinst.plb

Procedure created.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Procedure dropped.

SQL> select owner, object_type, object_name from dba_objects
where status <> 'VALID' order by owner, object_type, object_name;

no rows selected

SQL> select comp_id, comp_name, version, status from dba_registry where comp_id='OWM';

no rows selected

Here again we look for invalid objects and check dba_registry; it would be a good practice to run utlrp before uninstalling components to check if the database has invalid objects and therefore not to blame the uninstall procedure if you end with some invalid objects.

Next we can remove Enterprise Manager this way:

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

System altered.

SQL> EXEC sysman.emd_maintenance.remove_em_dbms_jobs;

PL/SQL procedure successfully completed.

SQL> EXEC sysman.setEMUserContext('',5);

PL/SQL procedure successfully completed.

SQL> DECLARE
CURSOR c1 IS
SELECT owner, synonym_name name
FROM dba_synonyms
WHERE table_owner = 'SYSMAN';
BEGIN
FOR r1 IN c1
LOOP
IF r1.owner = 'PUBLIC' THEN
EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM '||r1.name;
ELSE
EXECUTE IMMEDIATE 'DROP SYNONYM '||r1.owner||'.'||r1.name;
END IF;
END LOOP;
END;
/

PL/SQL procedure successfully completed.

SQL> DROP USER mgmt_view CASCADE;

User dropped.

SQL> DROP USER sysman CASCADE;

User dropped.

SQL> DROP ROLE mgmt_user;

Role dropped.

SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

System altered.

SQL> select owner, object_type, status, count(*) from dba_objects
where status <> 'VALID' group by owner, object_type, status;

no rows selected

SQL> select comp_id, comp_name, version, status from dba_registry where comp_id='EM';

no rows selected

For some uninstalls it is necessary to restart the database, and in general it is a good idea to remove components while the database is not in use, restarting the database both at the beginning and at the end of the procedure.

This is the case for uninstalling Spatial:

SQL> set pagesize 0
SQL> set feed off
SQL> spool drop_spatial.sql
SQL> select 'drop public synonym "' || synonym_name || '";' from dba_synonyms where table_owner='MDSYS';
drop public synonym "/106e9897_GeorGrayscaleEntry";
drop public synonym "/10862847_Intersection";
drop public synonym "/1138e59d_DBFReaderJGeomLogica";

...

drop public synonym "oracle/spatial/util/WKB";
drop public synonym "oracle/spatial/util/WKBasis";
drop public synonym "oracle/spatial/util/WKT";
SQL> spool off;
SQL> @drop_spatial.sql
SP2-0734: unknown command beginning "SQL> selec..." - rest of line ignored.
SP2-0734: unknown command beginning "SQL> spool..." - rest of line ignored.
SQL> commit;
SQL> set feed on
SQL> set pagesize 9999
SQL> drop user MDSYS cascade;
drop user MDSYS cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-14452: attempt to create, alter or drop an index on temporary table already in use


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@myserver:~$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 9 13:05:52 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 196681728 bytes
Fixed Size 1335444 bytes
Variable Size 117444460 bytes
Database Buffers 71303168 bytes
Redo Buffers 6598656 bytes
Database mounted.
Database opened.
SQL> drop user MDSYS cascade;

User dropped.

SQL> set linesize 150
SQL> set pagesize 9999
SQL> col comp_id for a10
SQL> col comp_name for a40
SQL> col version for a15
SQL> column owner format a30
SQL> column object_type format a20
SQL> select owner, object_type, object_name from dba_objects
where status <> 'VALID' order by owner, object_type, object_name;

no rows selected

SQL> select comp_id, comp_name, version, status from dba_registry where comp_id='SDO';

no rows selected

In some cases the uninstall scripts does not remove public synonyms and misses other objects, therefore you have to remove them by means of making a script, removing object by object or running utlrp.

If you want to remove Oracle Multimedia you can follow these steps:

SQL> drop user ORDSYS cascade;

User dropped.

SQL> drop user ORDPLUGINS cascade;

User dropped.

SQL> drop user SI_INFORMTN_SCHEMA cascade;

User dropped.

SQL> set pagesize 0
SQL> set feed off
SQL> spool drop_ordim.sql
SQL> select 'drop public synonym "'||b.object_name||'";' from dba_synonyms a, dba_objects b
where b.status='INVALID' and b.object_type='SYNONYM' and b.owner='PUBLIC'
and a.synonym_name=b.object_name and a.table_owner not in (select username from dba_users);
drop public synonym "/6d99aea6_MediaLibLoadExceptio";
drop public synonym "ORD_DICOM_ADMIN";
drop public synonym "ORD_DICOM";

...

drop public synonym "SI_AVERAGECOLOR";
drop public synonym "SI_STILLIMAGE";
drop public synonym "SI_COLOR";
SQL> spool off;
SQL> @drop_ordim;
SP2-0734: unknown command beginning "SQL> selec..." - rest of line ignored.
SP2-0734: unknown command beginning "SQL> spool..." - rest of line ignored.
SQL> commit;
SQL> set feed on
SQL> set pagesize 9999
SQL> select owner, object_type, status, count(*) from dba_objects
where status <> 'VALID' group by owner, object_type, status;

no rows selected

SQL> select comp_id, comp_name, version, status from dba_registry where comp_id='ORDIM';

no rows selected

These procedures are posted as transcripts in order to force people to read carefully and understand what's going on; if you are brave enough you can take just the SQL> sentences and assembly a script to speed up the uninstall process.

Next we will remove Oracle Rules Manager this way:

SQL> @?/rdbms/admin/catnorul.sql

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

...

PL/SQL procedure successfully completed.


Session altered.


PL/SQL procedure successfully completed.

SQL> drop FUNCTION EXFSYS.RLM$WLNCHK;

Function dropped.

SQL> @?/rdbms/admin/utlrp

TIMESTAMP
------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2011-09-09 15:05:41

1 row selected.

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>#

OBJECTS WITH ERRORS
-------------------
0

1 row selected.

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

1 row selected.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

SQL> select owner, object_type, status, count(*) from dba_objects
where status <> 'VALID' group by owner, object_type, status;

no rows selected

SQL> select comp_id, comp_name, version, status from dba_registry where comp_id='RUL';

COMP_ID COMP_NAME VERSION STATUS
---------- ---------------------------------------- --------------- ---------------------------------
RUL Oracle Rules Manager 11.2.0.1.0 REMOVED

1 row selected.

Sometimes there are dependencies between components so the removal order is important; for example, if you remove first Oracle Expression Filter you will end with a lot of invalid objects of Oracle Rules Manager. Therefore, it is important to understand component dependencies first in order to know when and what to uninstall in order to avoid ending with a corrupted database.

Next we can remove Oracle Expression Filter following these steps:

SQL> @?/rdbms/admin/catnoexf.sql

PL/SQL procedure successfully completed.


User dropped.


Package dropped.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

SQL> select owner, object_type, status, count(*) from dba_objects
where status <> 'VALID' group by owner, object_type, status;

no rows selected

SQL> select comp_id, comp_name, version, status from dba_registry where comp_id='EXF';

no rows selected

Some components are easier to uninstall and others require more manual work, and in general uninstalling components is easier and cleaner in an 11g database than in a 10g database; for example, if you want to remove just Oracle XML Database you can do it this way:

SQL> @?/rdbms/admin/catnoqm.sql

PL/SQL procedure successfully completed.


Index dropped.


Index dropped.

...


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Table dropped.

SQL> @?/rdbms/admin/catmeta.sql

...

Grant succeeded.


PL/SQL procedure successfully completed.





1 row selected.

SQL> @?/rdbms/admin/catpprvt.sql

...

37 /

Package body created.

SQL> show errors;
No errors.
SQL>
SQL> @?/rdbms/admin/utlrp

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2011-09-09 15:28:42

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>#

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


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

SQL> drop FUNCTION AWM_CREATEXDSFOLDER;

Function dropped.

SQL> drop PROCEDURE VALIDATE_ORDIM;

Procedure dropped.

SQL> drop package body HTMLDB_SYSTEM;

Package body dropped.

SQL> drop view ALL_XML_SCHEMAS;

View dropped.

SQL> drop view ALL_XML_SCHEMAS2;

View dropped.

SQL> select owner, object_type, status, count(*) from dba_objects
where status <> 'VALID' group by owner, object_type, status;

no rows selected

SQL> select comp_id, comp_name, version, status from dba_registry where comp_id='XDB';

no rows selected

Just in case you have a database with very little memory: in order to remove some components you need to have the minimum SGA memory recommended by Oracle (160 megabytes) or the procedure will fail, therefore it might be better to set SGA memory to be at least 160 megabytes.

Here are the steps to uninstall Oracle Text component:

SQL> @?/ctx/admin/catnoctx.sql

Session altered.


PL/SQL procedure successfully completed.

dropping all ctxsys objects...

...


dropping user ctxsys...

Role dropped.


User dropped.

SQL> drop procedure sys.validate_context;

Procedure dropped.

SQL> select owner, object_type, object_name from dba_objects
where status <> 'VALID' order by owner, object_type, object_name;

no rows selected

SQL> select comp_id, comp_name, version, status from dba_registry where COMP_ID='CONTEXT';

no rows selected

Again, it would be better to restart the database and remove components where it's not in use, but you can skip this step and try to remove components if you're lucky enough; next we will remove OLAP Analytic Workspace this way:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@myserver:~$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 9 15:44:26 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 196681728 bytes
Fixed Size 1335444 bytes
Variable Size 138415980 bytes
Database Buffers 50331648 bytes
Redo Buffers 6598656 bytes
Database mounted.
Database opened.
SQL> @?/olap/admin/catnoaps.sql

Procedure created.


PL/SQL procedure successfully completed.


Procedure dropped.


1 row deleted.

SQL> @?/rdbms/admin/utlrp.sql

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2011-09-09 15:44:52

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>#

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


PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

SQL> set linesize 150
SQL> set pagesize 9999
SQL> col comp_id for a10
SQL> col comp_name for a40
SQL> col version for a15
SQL> column owner format a30
SQL> column object_type format a20
SQL> select owner, object_type, object_name from dba_objects
where status <> 'VALID' order by owner, object_type, object_name;

no rows selected

SQL> select comp_id, comp_name, version, status from dba_registry where COMP_ID='APS';

no rows selected

You can get rid Oracle OLAP API off this way:

SQL> @?/olap/admin/olapidrp.plb

Package dropped.


Package dropped.


Package dropped.


Package dropped.


Package dropped.


Package dropped.


Package dropped.


Package dropped.


Package dropped.


Package dropped.


Package dropped.


SQL> @?/olap/admin/catnoxoq.sql

Package dropped.


Package dropped.


Type dropped.

...

Package dropped.


PL/SQL procedure successfully completed.


1 row deleted.

SQL> @?/rdbms/admin/utlrp.sql

TIMESTAMP
------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2011-09-09 15:49:19

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>#

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


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

SQL> select owner, object_type, object_name from dba_objects
where status <> 'VALID' order by owner, object_type, object_name;

no rows selected

SQL> select comp_id, comp_name, version, status from dba_registry where COMP_ID='XOQ';

no rows selected

And if you want to remove all the OLAP stuff, you can finish removing the OLAP Catalog component following these steps:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@myserver:~$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 9 15:52:00 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 196681728 bytes
Fixed Size 1335444 bytes
Variable Size 138415980 bytes
Database Buffers 50331648 bytes
Redo Buffers 6598656 bytes
Database mounted.
Database opened.
SQL> @?/olap/admin/catnoamd.sql

Synonym dropped.


Synonym dropped.


Synonym dropped.

...

Synonym dropped.


User dropped.


Role dropped.

SQL> set linesize 150
SQL> set pagesize 9999
SQL> col comp_id for a10
SQL> col comp_name for a40
SQL> col version for a15
SQL> column owner format a30
SQL> column object_type format a20
SQL> select owner, object_type, object_name from dba_objects
where status <> 'VALID' order by owner, object_type, object_name;

no rows selected

SQL> select comp_id, comp_name, version, status from dba_registry where COMP_ID='AMD';

no rows selected

Removing Java support from an Oracle database (not related to Java at operating system level) is more complex that previous procedures and some components are dependent from Java support, therefore this is the last component we're going to uninstall. It is accomplished this way:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@myserver:~$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 9 16:13:42 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 196681728 bytes
Fixed Size 1335444 bytes
Variable Size 150998892 bytes
Database Buffers 37748736 bytes
Redo Buffers 6598656 bytes
Database mounted.
SQL> alter system set "_system_trig_enabled" = false scope=memory;

System altered.

SQL> alter system enable restricted session;

System altered.

SQL> alter database open;

Database altered.

SQL> @?/rdbms/admin/catnojav.sql

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Trigger dropped.


Trigger dropped.


Trigger dropped.


Trigger dropped.


Call completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Java dropped.


Java dropped.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

SQL> @?/xdk/admin/rmxml.sql

PL/SQL procedure successfully completed.


Procedure created.


PL/SQL procedure successfully completed.

...

PL/SQL procedure successfully completed.


Procedure dropped.


PL/SQL procedure successfully completed.

SQL> @?/javavm/install/rmjvm.sql

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Package created.

...

PL/SQL procedure successfully completed.

SQL>
SQL> REM
SQL> REM end java object removal
SQL> REM
SQL>
SQL> drop table sys.JAVA$RMJVM$AUX3;

Table dropped.

SQL> drop table sys.JAVA$RMJVM$AUX2;

Table dropped.

SQL> drop table sys.JAVA$RMJVM$AUX;

Table dropped.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@myserver:~$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 24 13:38:49 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 196681728 bytes
Fixed Size 1335444 bytes
Variable Size 155193196 bytes
Database Buffers 33554432 bytes
Redo Buffers 6598656 bytes
Database mounted.
Database opened.

At this point you have ended with a (hopefully working) database with just Oracle Database Catalog Views, Oracle Database Packages and Types and OWB components (you cannot uninstall OWB), but in order to fix some problems with imp/exp and datapump related to uninstalling Java you have to run the following scripts:

SQL> @?/rdbms/admin/catdph.sql

Grant succeeded.

BEGIN
*
ERROR at line 1:
ORA-20002: Unable to create table IMPDP_STATS: already exists
ORA-06512: at "SYS.DBMS_STATS", line 10202
ORA-06512: at line 2



Grant succeeded.

...

Package created.


Package body created.


PL/SQL procedure successfully completed.

SQL> @?/rdbms/admin/prvtcxml.plb

Library created.


Package body created.

SQL> @?/rdbms/admin/catdpb.sql
CREATE ROLE datapump_exp_full_database
*
ERROR at line 1:
ORA-01921: role name 'DATAPUMP_EXP_FULL_DATABASE' conflicts with another user
or role name


CREATE ROLE datapump_imp_full_database
*
ERROR at line 1:
ORA-01921: role name 'DATAPUMP_IMP_FULL_DATABASE' conflicts with another user
or role name

...

Grant succeeded.


Grant succeeded.


Grant succeeded.

SQL> @?/rdbms/admin/dbmspump.sql

Type created.


Type created.


Library created.


Type body created.


Type body created.

SQL> @?/rdbms/admin/utlrp

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2011-08-24 13:47:21

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> 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


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

You can check the components left and invalid objects just to be sure, and would be a good idea to check the alert log too:

SQL> set linesize 150
SQL> set pagesize 9999
SQL> col comp_id for a10
SQL> col comp_name for a40
SQL> col version for a15
SQL> column OBJECT_NAME format a40
SQL> select owner, object_type, object_name from dba_objects
where status <> 'VALID' order by owner, object_type, object_name;

no rows selected

SQL> select comp_id, comp_name, version, status from dba_registry;

COMP_ID COMP_NAME VERSION STATUS
---------- ---------------------------------------- --------------- ---------------------------------
OWB OWB 11.2.0.1.0 VALID
CATALOG Oracle Database Catalog Views 11.2.0.1.0 VALID
CATPROC Oracle Database Packages and Types 11.2.0.1.0 VALID
JAVAVM JServer JAVA Virtual Machine 11.2.0.1.0 REMOVED
XML Oracle XDK 11.2.0.1.0 REMOVED
CATJAVA Oracle Database Java Packages 11.2.0.1.0 REMOVED

6 rows selected.

More information:

Manually Install/De-Install Database Options in Oracle Database 10gR2
Transcript of removing components in a 10g database
Transcript of removing components in an 11g database

Tuesday, September 6, 2011

Upgrading Oracle Database to 10.2.0.5.4

If you want to patch your Oracle Database installation from 10.2.0.5.0 to 10.2.0.5.4 (applying a PSU), then you might follow this simple procedure:

First of all, you have to shutdown all your instances, stop listeners, Enterprise Manager and everything running under the Oracle home being patched. Then you need to download and unpack patch number 12419392 from Metalink and have at least OPatch version 10.2.0.5.0; lower versions of OPatch will not let you apply this PSU:

oracle@myserver:/tmp/12419392$ opatch version
Invoking OPatch 10.2.0.4.9

OPatch Version: 10.2.0.4.9

OPatch succeeded.
oracle@myserver:/tmp/12419392$ opatch apply
Invoking OPatch 10.2.0.4.9

Oracle Interim Patch Installer version 10.2.0.4.9
Copyright (c) 2009, Oracle Corporation. All rights reserved.


Oracle Home : /opt/oracle/app/oracle/product/10.2.0/db_1
Central Inventory : /usr/lib/oracle/xe/oraInventory
from : /etc/oraInst.loc
OPatch version : 10.2.0.4.9
OUI version : 10.2.0.5.0
OUI location : /opt/oracle/app/oracle/product/10.2.0/db_1/oui
Log file location : /opt/oracle/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch2011-09-06_11-15-41AM.log

Patch history file: /opt/oracle/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch_history.txt

ApplySession applying interim patch '12419392' to OH '/opt/oracle/app/oracle/product/10.2.0/db_1'
ApplySession failed: ApplySession failed to prepare the system.

Patch 12419392 requires OPatch version 10.2.0.5.0.
The OPatch version being used (10.2.0.4.9) doesn't meet the minimum version required by the patch(es). Please download latest OPatch from My Oracle Support.

System intact, OPatch will not attempt to restore the system

OPatch failed with error code 73

If you need to upgrade OPatch then you could get the last version from Metalink (patch 6880880); after downloading this patch you just have to unpack it under the Oracle home:

oracle@myserver:/tmp/12419392$ cd $ORACLE_HOME
oracle@myserver:/opt/oracle/app/oracle/product/10.2.0/db_1$ unzip /tmp/p6880880_102000_LINUX.zip
Archive: /tmp/p6880880_102000_LINUX.zip
extracting: OPatch/ocm/ocm.zip
inflating: OPatch/ocm/lib/osdt_jce.jar
inflating: OPatch/ocm/lib/osdt_core3.jar
inflating: OPatch/ocm/lib/emocmclnt-14.jar

...

inflating: OPatch/docs/FAQ
inflating: OPatch/docs/Users_Guide.txt
inflating: OPatch/README.txt
oracle@myserver:/opt/oracle/app/oracle/product/10.2.0/db_1$ opatch version
Invoking OPatch 10.2.0.5.1

OPatch Version: 10.2.0.5.1

OPatch succeeded.

If you are ready to apply the patch (no Oracle software running at this point), then you have to go to the patch directory an run opatch from there:

oracle@myserver:/tmp$ cd 12419392
oracle@myserver:/tmp/12419392$ sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Sep 6 12:13:54 2011

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@myserver:/tmp/12419392$ opatch apply
Invoking OPatch 10.2.0.5.1

Oracle Interim Patch Installer version 10.2.0.5.1
Copyright (c) 2010, Oracle Corporation. All rights reserved.


Oracle Home : /opt/oracle/app/oracle/product/10.2.0/db_1
Central Inventory : /usr/lib/oracle/xe/oraInventory
from : /etc/oraInst.loc
OPatch version : 10.2.0.5.1
OUI version : 10.2.0.5.0
OUI location : /opt/oracle/app/oracle/product/10.2.0/db_1/oui
Log file location : /opt/oracle/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch2011-09-06_11-45-23AM.log

Patch history file: /opt/oracle/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch_history.txt

ApplySession applying interim patch '12419392' to OH '/opt/oracle/app/oracle/product/10.2.0/db_1'

Running prerequisite checks...
Patch 12419392: Optional component(s) missing : [ oracle.rdbms.dv, 10.2.0.5.0 ] , [ oracle.rdbms.dv.oc4j, 10.2.0.5.0 ]
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name:

You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: Y

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.


Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/opt/oracle/app/oracle/product/10.2.0/db_1')

Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files and inventory (not for auto-rollback) for the Oracle Home
Backing up files affected by the patch '12419392' for restore. This might take a while...
Backing up files affected by the patch '12419392' for rollback. This might take a while...
Execution of 'sh /tmp/12419392/custom/scripts/pre -apply 12419392 ':


Return Code = 0

Patching component oracle.rdbms, 10.2.0.5.0...
Updating archive file "/opt/oracle/app/oracle/product/10.2.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kcbl.o"
Updating archive file "/opt/oracle/app/oracle/product/10.2.0/db_1/lib/libserver10.a" with "lib/libserver10.a/qecsel.o"
Updating archive file "/opt/oracle/app/oracle/product/10.2.0/db_1/lib/libserver10.a" with "lib/libserver10.a/ksfd.o"

...

Updating jar file "/opt/oracle/app/oracle/product/10.2.0/db_1/sysman/jlib/emjsp.jar" with "/sysman/jlib/emjsp.jar/_database/_dbObjectsList$__jsp_StaticText.class"
Updating jar file "/opt/oracle/app/oracle/product/10.2.0/db_1/sysman/jlib/emjsp.jar" with "/sysman/jlib/emjsp.jar/_database/_dbObjectsList.class"
Copying file to "/opt/oracle/app/oracle/product/10.2.0/db_1/oc4j/j2ee/oc4j_applications/applications/em/em/admin/rep/editUserSummary.uix"

Patching component oracle.xdk.rsf, 10.2.0.5.0...
Updating archive file "/opt/oracle/app/oracle/product/10.2.0/db_1/lib/libxml10.a" with "lib/libxml10.a/lpxpar.o"

Patching component oracle.precomp.common, 10.2.0.5.0...

Patching component oracle.rdbms.rman, 10.2.0.5.0...
Running make for target client_sharedlib
Running make for target ioracle
Running make for target iwrap
Running make for target client_sharedlib
Running make for target proc
Running make for target irman
ApplySession adding interim patch '12419392' to inventory

Verifying the update...
Inventory check OK: Patch ID 12419392 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 12419392 are present in Oracle Home.

The local system has been patched and can be restarted.


OPatch succeeded.

After patching Oracle Database software you have to apply this PSU to all your Oracle databases as well:

oracle@myserver:/tmp/12419392$ sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Sep 6 12:55:53 2011

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1272576 bytes
Variable Size 134219008 bytes
Database Buffers 25165824 bytes
Redo Buffers 7114752 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/catbundle.sql psu apply

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

...

Updating registry...

1 row created.


Commit complete.

Check the following log file for errors:
/opt/oracle/app/oracle/product/10.2.0/db_1/cfgtoollogs/catbundle/catbundle_PSU_ORACLE_APPLY_2011Sep06_12_57_08.log
SQL> @?/rdbms/admin/utlrp.sql

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2011-09-06 13:02:31

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>#

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


PL/SQL procedure successfully completed.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

You have to end with no objects with errors nor recompilation errors after running utlrp. Finally, you can check the applied PSU looking the inventory with opatch:

oracle@myserver:/tmp/12419392$ opatch lsinventory
Invoking OPatch 10.2.0.5.1

Oracle Interim Patch Installer version 10.2.0.5.1
Copyright (c) 2010, Oracle Corporation. All rights reserved.


Oracle Home : /opt/oracle/app/oracle/product/10.2.0/db_1
Central Inventory : /usr/lib/oracle/xe/oraInventory
from : /etc/oraInst.loc
OPatch version : 10.2.0.5.1
OUI version : 10.2.0.5.0
OUI location : /opt/oracle/app/oracle/product/10.2.0/db_1/oui
Log file location : /opt/oracle/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch2011-09-06_12-00-03PM.log

Patch history file: /opt/oracle/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /opt/oracle/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2011-09-06_12-00-03PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (3):

Oracle Database 10g 10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 3 10.2.0.4.0
Oracle Database 10g Release 2 Patch Set 4 10.2.0.5.0a
There are 3 products installed in this Oracle Home.


Interim patches (1) :

Patch 12419392 : applied on Tue Sep 06 11:49:54 GMT-06:00 2011
Unique Patch ID: 13710478
Created on 25 May 2011, 01:06:08 hrs PST8PDT
Bugs fixed:
6402302, 10269717, 10327190, 8865718, 10017048, 9024850, 8394351, 8546356
9360157, 9770451, 9020537, 9772888, 8664189, 10091698, 12551710, 7519406
10132870, 8771916, 9109487, 10173237, 10068982, 8350262, 11792865
11724962, 11725006, 9184754, 8544696, 9320130, 7026523, 8277300, 9726739
8412426, 12419392, 6651220, 9150282, 9659614, 9949948, 10327179, 8882576
7612454, 9711859, 9714832, 10248542, 9952230, 9469117, 9952270, 8660422
10324526, 12419258, 9713537, 10010310, 9390484, 9963497, 12551700
12551701, 10249537, 12551702, 12551703, 8211733, 12551704, 9548269
12551705, 12551706, 9337325, 12551707, 7602341, 12551708, 9308296
10157402, 11737047



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

OPatch succeeded.


Disclaimer: This procedure is just to apply this PSU having and Oracle Database 10.2.0.5.0 installation in a non-RAC environment, otherwise the procedure is different. It would be a good idea to read the README.html file included in the patch distribution, and I'm not endorsing the use of this patch in any way.