About Me

My photo
Bangalore, India
I am an Oracle Certified Professional working in SAP Labs. Everything written on my blog has been tested on my local environment, Please test before implementing or running in production. You can contact me at amit.rath0708@gmail.com.

Monday, September 30, 2013

Disable Case Sensitive in Oracle Database 11g

Prior to 11g Passwords in Oracle are not case sensitive. Now from 11g Oracle passwords are Case sensitive. Parameter sec_case_sensitive_logon has been included in Oracle 11g.

SQL> show parameter logon

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
sec_case_sensitive_logon             boolean                           TRUE

SQL> conn amit/AMIT
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> conn amit/amit
Connected.

Disable Password case sensitive :-

SQL> alter system set sec_case_sensitive_logon=false;

System altered.

SQL> conn amit/AMIT
Connected.

Case Sensitive has been Disabled.

I hope this article helped you.

Regards,
Amit Rath

How to RollBack a Database PSU

PFB steps to Rollback a Database PSU applied on Oracle Database :-

In my case Database PSU is 10.2.0.5.12 and Database is 10.2.0.5

1. Check details of patch applied :-


[oracle@localhost admin]$ 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       : /oracle_home/app
Central Inventory : /oracle11gr2/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 10.2.0.5.1
OUI version       : 10.2.0.5.0
OUI location      : /oracle_home/app/oui
Log file location : /oracle_home/app/cfgtoollogs/opatch/opatch2013-09-30_09-59-14AM.log

Patch history file: /oracle_home/app/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /oracle_home/app/cfgtoollogs/opatch/lsinv/lsinventory2013-09-30_09-59-14AM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (2):

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

Interim patches (1) :

Patch  16619894     : applied on Mon Sep 30 09:57:21 GMT+05:30 2013
Unique Patch ID:  16519126
   Created on 28 Jun 2013, 01:27:47 hrs PST8PDT
   Bugs fixed:
     13596521, 8865718, 11790175, 13489660, 9020537, 9772888, 8650138
     8664189, 10091698, 14275629, 14469008, 10092858, 12551710, 7519406
     9821321, 13349665, 8771916, 7509714, 16619894, 8822531, 10139235
     10159846, 13257247, 8350262, 11792865, 7119382, 13632738, 11724962
     16309604, 16309605, 16309606, 8966823, 9320130, 16961614, 16961615
     13775862, 16961616, 11674645, 16961617, 16961618, 15877957, 7026523
     16961619, 15877958, 15877959, 9399589, 14841459, 9672816, 13503598
     9499302, 9150282, 9448311, 9659614, 13632743, 14220725, 9949948, 8882576
     10327179, 7612454, 7111619, 9711859, 9714832, 9735237, 9952230, 15877960
     12780098, 13561951, 15877961, 15877962, 14665116, 15877963, 8660422
     11066597, 16703112, 16279401, 14546673, 14105702, 14459552, 9713537
     14105703, 14105704, 13483152, 13737773, 13737775, 14269955, 12925532
     12748240, 9694101, 14390396, 12862186, 12862187, 10249537, 14727319
     9586877, 8211733, 6694396, 9548269, 7115910, 7710224, 9337325, 8354642
     7602341, 14076510, 10157402, 11856395, 12565867, 6402302, 10327190
     10269717, 13015379, 11693109, 14023636, 10017048, 8546356, 8394351
     9024850, 13561750, 8224558, 9770451, 9360157, 8488233, 9109487, 10132870
     14841558, 9171933, 16817117, 10173237, 9532911, 10068982, 7361418
     10306945, 8666117, 11725006, 6157713, 10214450, 9184754, 14205448
     8544696, 9767674, 16306019, 9323583, 8277300, 13343467, 16279211
     9726739, 16382448, 13791364, 8412426, 10326338, 10165083, 10208905
     12419392, 6651220, 9145204, 13554409, 11076894, 7450366, 11893577
     8970313, 14492313, 6690853, 6011045, 14492314, 11814891, 10162036
     14492315, 10248542, 14492316, 16742123, 9469117, 13359623, 9952270
     9842573, 13343471, 12710774, 10324526, 14546638, 12419258, 9322219
     8636407, 16056270, 10010310, 12828105, 9689310, 9390484, 13736501
     13736502, 9824435, 13736503, 13736504, 13736505, 13736506, 9963497
     9032322, 13736507, 12551700, 12551701, 14035825, 12551702, 11858315
     12551703, 12551704, 10076669, 16270946, 12551705, 12551706, 14040433
     12551707, 6076890, 14258925, 12551708, 9308296, 13916709, 12827745
     12880299, 14038805, 13923855, 9072105, 8528171, 11737047
--------------------------------------------------------------------------------

OPatch succeeded.

2. Shutdown Database and listener :-

[oracle@localhost admin]$ sqlplus

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Sep 30 10:02:42 2013

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

Enter user-name: /as sysdba

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

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@localhost admin]$ lsnrctl stop

LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 30-SEP-2013 10:06:03

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
The command completed successfully
[oracle@localhost admin]$

3. Before Rollback check that "catbundle_PSU_<DATABASE_NAME>_ROLLBACK.sql" file exist in ORACLE_HOME/rdbms/admin. if exists start the Rollback process :-

[oracle@localhost DBSOFT]$ opatch rollback -id 16619894
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       : /oracle_home/app
Central Inventory : /oracle11gr2/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 10.2.0.5.1
OUI version       : 10.2.0.5.0
OUI location      : /oracle_home/app/oui
Log file location : /oracle_home/app/cfgtoollogs/opatch/opatch2013-09-30_10-08-49AM.log

Patch history file: /oracle_home/app/cfgtoollogs/opatch/opatch_history.txt

RollbackSession rolling back interim patch '16619894' from OH '/oracle_home/app'

Running prerequisite checks...

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 = '/oracle_home/app')

Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files affected by the patch '16619894' for restore. This might take a while...
Execution of 'sh /oracle_home/app/.patch_storage/16619894_Jun_28_2013_01_27_47/original_patch/custom/scripts/pre -rollback 16619894 ':

Return Code = 0

Patching component oracle.rdbms, 10.2.0.5.0...
Deleting "kstst.o" from archive "/oracle_home/app/lib/libserver10.a"
Deleting "kststqad.o" from archive "/oracle_home/app/lib/libserver10.a"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/kcbl.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/qecsel.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/ksfd.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/qkexr.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/xty.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/qergh.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/qergs.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/ktsx.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/kdt.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/kkpod.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/kdiss.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/qerix.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/knld.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/ktein.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/kkzu.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/tbsdrv.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/ktec.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/kteop.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/ktsp.o"
.
.
.
.
Updating jar file "/oracle_home/app/sysman/jlib/emjsp.jar" with "/oracle_home/app/.patch_storage/16619894_Jun_28_2013_01_27_47/files//sysman/jlib/emjsp.jar/_database/_dbclone/_dbClone__Warning$__jsp_StaticText.class"
Updating jar file "/oracle_home/app/sysman/jlib/emjsp.jar" with "/oracle_home/app/.patch_storage/16619894_Jun_28_2013_01_27_47/files//sysman/jlib/emjsp.jar/_database/_dist/_streams/_queue/_editQueue.class"
Updating jar file "/oracle_home/app/sysman/jlib/emjsp.jar" with "/oracle_home/app/.patch_storage/16619894_Jun_28_2013_01_27_47/files//sysman/jlib/emjsp.jar/_database/_dist/_streams/_queue/_editQueue$__jsp_StaticText.class"
Copying file to "/oracle_home/app/oc4j/j2ee/oc4j_applications/applications/em/em/admin/rep/editUserSummary.uix"
Copying file to "/oracle_home/app/oc4j/j2ee/oc4j_applications/applications/em/em/WEB-INF/web.xml"

Patching component oracle.xdk.rsf, 10.2.0.5.0...
Updating archive file "/oracle_home/app/lib/libxml10.a"  with "lib/libxml10.a/lpxpar.o"
Updating archive file "/oracle_home/app/lib32/libxml10.a"  with "lib32/libxml10.a/lpxpar.o"

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

Patching component oracle.rdbms.rman, 10.2.0.5.0...
Copying file to "/oracle_home/app/rdbms/admin/recover.bsq"

Patching component oracle.sdo.locator, 10.2.0.5.0...
Updating archive file "/oracle_home/app/lib/libordsdo10.a"  with "lib/libordsdo10.a/mdidx.o"
Updating archive file "/oracle_home/app/lib/libordsdo10.a"  with "lib/libordsdo10.a/mdrcr.o"
Updating archive file "/oracle_home/app/lib/libordsdo10.a"  with "lib/libordsdo10.a/mdrt.o"
Updating archive file "/oracle_home/app/lib/libordsdo10.a"  with "lib/libordsdo10.a/mdopp.o"
Updating archive file "/oracle_home/app/lib/libordsdo10.a"  with "lib/libordsdo10.a/mdgr.o"
Copying file to "/oracle_home/app/md/admin/mdprivs.sql"

Patching component oracle.network.listener, 10.2.0.5.0...

Patching component oracle.network.client, 10.2.0.5.0...
Copying file to "/oracle_home/app/bin/adapters"

Patching component oracle.ovm, 10.2.0.5.0...
Copying file to "/oracle_home/app/rdbms/admin/owmr1116.plb"
Copying file to "/oracle_home/app/rdbms/admin/owmv1116.plb"

Patching component oracle.oem.oemlt, 10.2.0.5.0...
Copying file to "/oracle_home/app/rdbms/admin/execocm.sql"

Patching component oracle.javavm.server, 10.2.0.5.0...
Copying file to "/oracle_home/app/lib/libjox10.so"
Copying file to "/oracle_home/app/lib32/libjox10.so"
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
Running make for target itnslsnr
RollbackSession removing interim patch '16619894' from inventory

The local system has been patched and can be restarted.

OPatch succeeded.

4. Start database and run "catbundle_PSU_ORCL_ROLLBACK.sql" script :-

[oracle@localhost admin]$ sqlplus

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Sep 30 10:10:21 2013

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

Enter user-name: /as sysdba
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  599785472 bytes
Fixed Size                  2098112 bytes
Variable Size             230689856 bytes
Database Buffers          360710144 bytes
Redo Buffers                6287360 bytes
Database mounted.
Database opened.
SQL> @catbundle_PSU_ORCL_ROLLBACK.sql
SQL> COLUMN spool_file NEW_VALUE spool_file NOPRINT
SQL> SELECT '/oracle_home/app/cfgtoollogs/catbundle/' || 'catbundle_PSU_' || name || '_ROLLBACK_' || TO_CHAR(SYSDATE, 'YYYYMonDD_hh24_mi_ss', 'NLS_DATE_LANGUAGE=''AMERICAN''') || '.log' AS spool_file FROM v$database;

SQL> SPOOL &spool_file
SQL> exec dbms_registry.set_session_namespace('SERVER')

PL/SQL procedure successfully completed.

SQL> PROMPT Processing Oracle Enterprise Manager...
Processing Oracle Enterprise Manager...
SQL> ALTER SESSION SET current_schema = SYSMAN;

Session altered.

SQL> @?/sysman/admin/emdrep/sql/core/latest/ecm/ecm_util_pkgdef.sql
SQL> Rem
SQL> Rem $Header: ecm_util_pkgdef.sql 31-oct-2003.14:19:54 kchiasso Exp $
SQL> Rem
SQL> Rem ecm_util_pkgdef.sql
SQL> Rem
SQL> Rem Copyright (c) 2002, 2003, Oracle Corporation.  All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem         ecm_util_pkgdef.sql - <one-line expansion of the name>
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem         <short description of component this file declares/defines>
SQL> Rem
SQL> Rem    NOTES
SQL> Rem         <other useful comments, qualifications, etc.>
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    vkhizder    10/31/03 - adding functions for getting
SQL> Rem                           patchsets/patches/bugs for a home
SQL> Rem    groyal      08/28/03 - Enhance concat to support a limit
SQL> Rem    jmansur     08/21/03 - update get_clone_source to match latest design
SQL> Rem    shuberma    04/16/03 - Removing procedure that is not longer used
SQL> Rem    shuberma    02/12/03 - Document new column in returned cursor
SQL> Rem    shuberma    01/03/03 - Adding procedure for clone home source
SQL> Rem    shuberma    12/23/02 - Adding an optional argument to the concat_col call
SQL> Rem    rmenon      10/18/02 - added CONCAT_COMPONENT_VERSION definition
SQL> Rem    rpinnama    05/15/02 - rpinnama_reorg_rep_scripts
SQL> Rem    rpinnama    05/15/02 - Created
SQL> Rem
SQL>
SQL> rem********************************************************************
SQL> rem
SQL> rem  PURPOSE
SQL> rem
SQL> rem        The ECM_UTIL package contains procedures and functions for various
SQL> rem        purposes including returning a list of target names for jobs, or admins
SQL> rem        for rules.
SQL> rem
SQL> rem  PROCEDURES and FUNCTIONS
SQL> rem
SQL> rem   JOB_TARGET_LIST
SQL> rem        ARGUMENTS:  All are IN parameters unless otherwise noted.
SQL> rem          job_id -- The internal job id for which to return the target list.
SQL> rem         RETURNS: a VARCHAR2 which is the space separated target list.
SQL> rem
SQL> rem   HOST_HOME_TARGET_LIST
SQL> rem        ARGUMENTS:  All are IN parameters unless otherwise noted.
SQL> rem          host_name -- The name of the host for which to compare for ORACLE_HOME.
SQL> rem          oracle_home -- The path of the ORACLE_HOME for which to compare the host.
SQL> rem          target_type -- The type of target for which to compare for ORACLE_HOME.
SQL> rem         RETURNS: a VARCHAR2 which is the comma separated target list.
SQL> rem
SQL> rem  NOTES
SQL> rem
SQL> rem   The methods in this package do not make any assumptions about
SQL> rem   transacations.  Essentially, it's up to the call to commit or rollback,
SQL> rem   unless otherwise noted.
SQL> rem
.
.
.
.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Session altered.

Updating registry...

1 row created.

Commit complete.

Check the following log file for errors:
SQL> @?/cpu/scripts/bug11057369.sql
SQL> Rem
SQL> Rem $Header: rdbms/admin/bug11057369.sql st_server_jheng_rfi_bug-11057369/1 2011/02/25 18:31:55 jheng Exp $
SQL> Rem
SQL> Rem bug11057369.sql
SQL> Rem
SQL> Rem Copyright (c) 2011, Oracle and/or its affiliates. All rights reserved.
SQL> Rem
.
.
.
.
Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Session altered.

Updating registry...

1 row created.

Commit complete.

Check the following log file for errors:
/oracle_home/app/cfgtoollogs/catbundle/catbundle_PSU_ORCL_ROLLBACK_2013Sep30_10_11_02.log

Check Log file /oracle_home/app/cfgtoollogs/catbundle/catbundle_PSU_ORCL_ROLLBACK_2013Sep30_10_11_02.log for any errors occured.

5. Check for Invalid objects :-

SQL> @utlrp.sql

TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2013-09-30 10:11:28

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>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.

TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END  2013-09-30 10:11:29

PL/SQL procedure successfully completed.

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.

6. Check that Patch has been successfully rollbacked :-

[oracle@localhost admin]$ 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       : /oracle_home/app
Central Inventory : /oracle11gr2/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 10.2.0.5.1
OUI version       : 10.2.0.5.0
OUI location      : /oracle_home/app/oui
Log file location : /oracle_home/app/cfgtoollogs/opatch/opatch2013-09-30_10-11-56AM.log

Patch history file: /oracle_home/app/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /oracle_home/app/cfgtoollogs/opatch/lsinv/lsinventory2013-09-30_10-11-56AM.txt

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

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

There are no Interim patches installed in this Oracle Home.
--------------------------------------------------------------------------------
OPatch succeeded.


I hope this article helped you.

Regards,
Amit Rath

Sunday, September 29, 2013

Applying Database PSU 10.2.0.5.12 to Oracle database 10.2.0.5

Yesterday I applied Database PSU (includes CPU July2013) 10.2.0.5.12 to Oracle Database version 10.2.0.5 . PFB steps to apply :-

Download the Latest PSU patch (p16619894_10205_Linux-x86-64.zip) from Metalink. In my case Patch number is 16619894.

Backup your Database and ORACLE_HOME before applying PSU.

1. Prerequisites to Apply PSU :-

[oracle@localhost ~]$ which opatch
/oracle_home/app/OPatch/opatch
[oracle@localhost ~]$
[oracle@localhost ~]$ cd /DBSOFT/
[oracle@localhost DBSOFT]$ opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./16619894
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.

PREREQ session

Oracle Home       : /oracle_home/app
Central Inventory : /oracle11gr2/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 10.2.0.4.9
OUI version       : 10.2.0.5.0
OUI location      : /oracle_home/app/oui
Log file location : /oracle_home/app/cfgtoollogs/opatch/opatch2013-09-27_16-19-20PM.log

Patch history file: /oracle_home/app/cfgtoollogs/opatch/opatch_history.txt

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

From output its clear that My Opatch Version is lower than the Patch version which has to be applied . Patch has to be applied with same or higher OPatch version otherwise we will get below mentioned error :-

[oracle@localhost 16619894]$ 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       : /oracle_home/app
Central Inventory : /oracle11gr2/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 10.2.0.4.9
OUI version       : 10.2.0.5.0
OUI location      : /oracle_home/app/oui
Log file location : /oracle_home/app/cfgtoollogs/opatch/opatch2013-09-27_16-32-38PM.log

Patch history file: /oracle_home/app/cfgtoollogs/opatch/opatch_history.txt

ApplySession applying interim patch '16619894' to OH '/oracle_home/app'
ApplySession failed: ApplySession failed to prepare the system.

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

2. Download the latest Opatch version from Metalink and copy it in inside ORACLE_HOME

[oracle@localhost 16619894]$pwd
/oracle_home/app
[oracle@localhost app]$ unzip p6880880_102000_Linux-x86-64.zip
Archive:  p6880880_102000_Linux-x86-64.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
replace OPatch/ocm/lib/emocmutl.jar? [y]es, [n]o, [A]ll, [N]one, [r]ename: y
  inflating: OPatch/ocm/lib/emocmutl.jar
  inflating: OPatch/ocm/bin/emocmrsp
  inflating: OPatch/ocm/ocm_platforms.txt
replace OPatch/crs/patch112.pl? [y]es, [n]o, [A]ll, [N]one, [r]ename: y
  inflating: OPatch/crs/patch112.pl
replace OPatch/crs/crsdelete.pm? [y]es, [n]o, [A]ll, [N]one, [r]ename: y
  inflating: OPatch/crs/crsdelete.pm
replace OPatch/crs/crspatch.pm? [y]es, [n]o, [A]ll, [N]one, [r]ename: y
  inflating: OPatch/crs/crspatch.pm
replace OPatch/crs/s_crsconfig_defs? [y]es, [n]o, [A]ll, [N]one, [r]ename: y
  inflating: OPatch/crs/s_crsconfig_defs
replace OPatch/crs/crsconfig_lib.pm? [y]es, [n]o, [A]ll, [N]one, [r]ename: A
  inflating: OPatch/crs/crsconfig_lib.pm
  inflating: OPatch/crs/oracss.pm
  inflating: OPatch/crs/auto_patch.pl
  inflating: OPatch/crs/s_crsconfig_lib.pm
  inflating: OPatch/opatch
  inflating: OPatch/opatchprereqs/opatch/runtime_prereq.xml
  inflating: OPatch/opatchprereqs/opatch/opatch_prereq.xml
  inflating: OPatch/opatchprereqs/opatch/rulemap.xml
  inflating: OPatch/opatchprereqs/prerequisite.properties
  inflating: OPatch/opatchprereqs/oui/knowledgesrc.xml
  inflating: OPatch/opatch.ini
  inflating: OPatch/emdpatch.pl
  inflating: OPatch/opatch.pl
  inflating: OPatch/jlib/opatchprereq.jar
  inflating: OPatch/jlib/opatchactions.jar
  inflating: OPatch/jlib/opatchutil.jar
  inflating: OPatch/jlib/opatchfmw.jar
  inflating: OPatch/jlib/opatchext.jar
  inflating: OPatch/jlib/opatch.jar
  inflating: OPatch/opatch.bat
  inflating: OPatch/docs/Prereq_Users_Guide.txt
  inflating: OPatch/docs/FAQ
  inflating: OPatch/docs/Users_Guide.txt
  inflating: OPatch/README.txt

[oracle@localhost OPatch]$ 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       : /oracle_home/app
Central Inventory : /oracle11gr2/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 10.2.0.5.1
OUI version       : 10.2.0.5.0
OUI location      : /oracle_home/app/oui
Log file location : /oracle_home/app/cfgtoollogs/opatch/opatch2013-09-27_16-59-28PM.log

Patch history file: /oracle_home/app/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /oracle_home/app/cfgtoollogs/opatch/lsinv/lsinventory2013-09-27_16-59-28PM.txt

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

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

There are no Interim patches installed in this Oracle Home.

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

OPatch succeeded.

Opatch version is upgraded to the latest version .

3. As Its a Single Instance Database. We need to Shutdown database as well as Listeners and EM's :-

[oracle@localhost ~]$ sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 27 13:20:57 2013

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

Enter user-name: /as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[oracle@localhost ~]$ emctl stop dbconsole
TZ set to Asia/Calcutta
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation.  All rights reserved.
http://localhost.localdomain:1158/em/console/aboutApplication
Stopping Oracle Enterprise Manager 10g Database Control ...
 ...  Stopped.
[oracle@localhost ~]$ lsnrctl stop

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 27-SEP-2013 13:23:34

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
The command completed successfully
[oracle@localhost ~]$ isqlplusctl stop
iSQL*Plus 10.2.0.1.0
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Stopping iSQL*Plus ...
iSQL*Plus stopped.

4. Apply the Patch on Database :-

[oracle@localhost OPatch]$
[oracle@localhost OPatch]$
[oracle@localhost OPatch]$ cd /DBSOFT/
[oracle@localhost DBSOFT]$ ls -ltr
total 4351288
-rw-r--r-- 1 oracle oinstall      35417 Apr 22  2010 utltzpv4.sql
drwxr-xr-x 7 oracle oinstall       4096 Nov  3  2010 OPatch
-rw-r--r-- 1 oracle oinstall  801603584 Nov  9  2011 10201_database_linux_x86_64.cpio
-rw-r--r-- 1 root   root     1239269270 Sep 24 12:41 linux.x64_11gR2_database_1of2.zip
-rw-r--r-- 1 root   root     1111416131 Sep 24 13:13 linux.x64_11gR2_database_2of2.zip
-rw-r--r-- 1 oracle oinstall       6475 Sep 27 10:32 utltzpv4.zip
-rw-r--r-- 1 oracle oinstall 1249857866 Sep 27 13:07 p8202632_10205_Linux-x86-64.zip
-rw-r--r-- 1 root   root       21714481 Sep 27 14:59 p16619894_10205_Linux-x86-64.zip
drwxr-xr-x 5 oracle oinstall       4096 Sep 27 16:01 16619894
-rw-r--r-- 1 root   root       27412455 Sep 27 16:56 p6880880_102000_Linux-x86-64.zip
[oracle@localhost DBSOFT]$
[oracle@localhost DBSOFT]$ cd 16619894/
[oracle@localhost 16619894]$ 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       : /oracle_home/app
Central Inventory : /oracle11gr2/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 10.2.0.5.1
OUI version       : 10.2.0.5.0
OUI location      : /oracle_home/app/oui
Log file location : /oracle_home/app/cfgtoollogs/opatch/opatch2013-09-27_17-00-06PM.log

Patch history file: /oracle_home/app/cfgtoollogs/opatch/opatch_history.txt

ApplySession applying interim patch '16619894' to OH '/oracle_home/app'

Running prerequisite checks...
Patch 16619894: Optional component(s) missing : [ oracle.rdbms.dv, 10.2.0.5.0 ] , [ oracle.rdbms.dv.oc4j, 10.2.0.5.0 ] , [ oracle.network.cman, 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 = '/oracle_home/app')

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 '16619894' for restore. This might take a while...
Backing up files affected by the patch '16619894' for rollback. This might take a while...
Execution of 'sh /DBSOFT/16619894/custom/scripts/pre -apply 16619894 ':

Return Code = 0

Patching component oracle.rdbms, 10.2.0.5.0...
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/kcbl.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/qecsel.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/ksfd.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/qkexr.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/xty.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/qergh.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/qergs.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/ktsx.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/kdt.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/kkpod.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/kdiss.o"
.
.
.
.

Copying file to "/oracle_home/app/rdbms/admin/bundledata_PSU.xml"
Copying file to "/oracle_home/app/rdbms/lib/kkxwtp.o"
Copying file to "/oracle_home/app/rdbms/lib/ksms.o"
Copying file to "/oracle_home/app/rdbms/lib/jox.o"
Copying file to "/oracle_home/app/rdbms/mesg/oraus.msg"
Copying file to "/oracle_home/app/rdbms/mesg/oraus.msb"
Copying file to "/oracle_home/app/psu/10.2.0.5.12/catpsu.sql"
Copying file to "/oracle_home/app/psu/10.2.0.5.12/catpsu_rollback.sql"

Patching component oracle.rdbms.rsf, 10.2.0.5.0...
Updating archive file "/oracle_home/app/lib/libgeneric10.a"  with "lib/libgeneric10.a/qcop.o"
Updating archive file "/oracle_home/app/lib/libgeneric10.a"  with "lib/libgeneric10.a/kgh.o"
Updating archive file "/oracle_home/app/lib/libgeneric10.a"  with "lib/libgeneric10.a/kgl.o"
Updating archive file "/oracle_home/app/lib/libgeneric10.a"  with "lib/libgeneric10.a/kgl2.o"
Updating archive file "/oracle_home/app/lib/libgeneric10.a"  with "lib/libgeneric10.a/qcs2.o"
Updating archive file "/oracle_home/app/lib/libgeneric10.a"  with "lib/libgeneric10.a/kgx.o"
Updating archive file "/oracle_home/app/lib/libgeneric10.a"  with "lib/libgeneric10.a/kgkp.o"
Updating archive file "/oracle_home/app/lib/libgeneric10.a"  with "lib/libgeneric10.a/kgsk.o"
Updating archive file "/oracle_home/app/lib/libgeneric10.a"  with "lib/libgeneric10.a/kgski.o"
Updating archive file "/oracle_home/app/lib/libgeneric10.a"  with "lib/libgeneric10.a/kgls.o"
.
.
.
.

Patching component oracle.javavm.server, 10.2.0.5.0...
Copying file to "/oracle_home/app/lib/libjox10.so"
Copying file to "/oracle_home/app/lib32/libjox10.so"
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
Running make for target itnslsnr
ApplySession adding interim patch '16619894' to inventory

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

The local system has been patched and can be restarted.

OPatch succeeded.
[oracle@localhost 16619894]$

Patch has been Successfully applied. You can Check Logfile for any errors 
/oracle_home/app/cfgtoollogs/opatch/opatch2013-09-27_17-00-06PM.log

5. Post Installation Instructions :-

a. Start Database :-

[oracle@localhost admin]$ sqlplus

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Sep 27 17:06:22 2013

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

Enter user-name: /as sysdba
Connected to an idle instance.

SQL>
SQL> startup
ORACLE instance started.

Total System Global Area  599785472 bytes
Fixed Size                  2098112 bytes
Variable Size             218106944 bytes
Database Buffers          373293056 bytes
Redo Buffers                6287360 bytes
Database mounted.
Database opened.

Run the CatBundle script :-

SQL> @catbundle.sql psu apply

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Generating apply and rollback scripts...
Check the following file for errors:
/oracle_home/app/cfgtoollogs/catbundle/catbundle_PSU_ORCL_GENERATE_2013Sep27_17_06_50.log
Apply script: /oracle_home/app/rdbms/admin/catbundle_PSU_ORCL_APPLY.sql
Rollback script: /oracle_home/app/rdbms/admin/catbundle_PSU_ORCL_ROLLBACK.sql

PL/SQL procedure successfully completed.

Executing script file...

SQL> COLUMN spool_file NEW_VALUE spool_file NOPRINT
SQL> SELECT '/oracle_home/app/cfgtoollogs/catbundle/' || 'catbundle_PSU_' || name || '_APPLY_' || TO_CHAR(SYSDATE, 'YYYYMonDD_hh24_mi_ss', 'NLS_DATE_LANGUAGE=''AMERICAN''') || '.log' AS spool_file FROM v$database;

SQL> SPOOL &spool_file
SQL> exec dbms_registry.set_session_namespace('SERVER')

PL/SQL procedure successfully completed.

SQL> PROMPT Processing Oracle Enterprise Manager...
Processing Oracle Enterprise Manager...
SQL> ALTER SESSION SET current_schema = SYSMAN;

Session altered.

SQL> @?/sysman/admin/emdrep/sql/core/latest/ecm/ecm_util_pkgdef.sql
SQL> Rem
SQL> Rem $Header: emdw_src_1/source/oracle/sysman/emdrep/sql/core/latest/ecm/ecm_util_pkgdef.sql /st_recommended_10.2.0.5.0sa/1 2011/04/27 02:54:51 agor Exp $
SQL> Rem
SQL> Rem ecm_util_pkgdef.sql
SQL> Rem
SQL> Rem Copyright (c) 2002, 2011, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem      ecm_util_pkgdef.sql - <one-line expansion of the name>
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem      <short description of component this file declares/defines>
SQL> Rem
SQL> Rem    NOTES
SQL> Rem      <other useful comments, qualifications, etc.>
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    agor        04/23/11 - Backport of txn niramach_bug-8837368 .
SQL> Rem    vkhizder    10/31/03 - adding functions for getting
SQL> Rem                           patchsets/patches/bugs for a home
SQL> Rem    groyal      08/28/03 - Enhance concat to support a limit
SQL> Rem    jmansur     08/21/03 - update get_clone_source to match latest design
SQL> Rem    shuberma    04/16/03 - Removing procedure that is not longer used
SQL> Rem    shuberma    02/12/03 - Document new column in returned cursor
SQL> Rem    shuberma    01/03/03 - Adding procedure for clone home source
SQL> Rem    shuberma    12/23/02 - Adding an optional argument to the concat_col call
SQL> Rem    rmenon      10/18/02 - added CONCAT_COMPONENT_VERSION definition
SQL> Rem    rpinnama    05/15/02 - rpinnama_reorg_rep_scripts
SQL> Rem    rpinnama    05/15/02 - Created
SQL> Rem
.
.
.
.
.
.
..
Grant succeeded.

Grant succeeded.

Grant succeeded.

Session altered.

Updating registry...

1 row created.

Commit complete.

Check the following log file for errors:
/oracle_home/app/cfgtoollogs/catbundle/catbundle_PSU_ORCL_APPLY_2013Sep27_17_06_57.log

Cat Bundle script Completed . 

b. Check for invalid Objects :-

SQL>  @utlrp.sql

TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2013-09-28 00:04:49

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>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.

TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END  2013-09-28 00:04:50

PL/SQL procedure successfully completed.

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.

No Invalid Objects . PSU applied Successfully on Database.

6. Check with OPacth lsinventory that Patch successfully applied and bugs are fixed or not :-

[oracle@localhost admin]$ 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       : /oracle_home/app
Central Inventory : /oracle11gr2/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 10.2.0.5.1
OUI version       : 10.2.0.5.0
OUI location      : /oracle_home/app/oui
Log file location : /oracle_home/app/cfgtoollogs/opatch/opatch2013-09-28_00-06-02AM.log

Patch history file: /oracle_home/app/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /oracle_home/app/cfgtoollogs/opatch/lsinv/lsinventory2013-09-28_00-06-02AM.txt

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

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

Interim patches (1) :

Patch  16619894     : applied on Fri Sep 27 17:02:06 GMT+05:30 2013
Unique Patch ID:  16519126
   Created on 28 Jun 2013, 01:27:47 hrs PST8PDT
   Bugs fixed:
     13596521, 8865718, 11790175, 13489660, 9020537, 9772888, 8650138
     8664189, 10091698, 14275629, 14469008, 10092858, 12551710, 7519406
     9821321, 13349665, 8771916, 7509714, 16619894, 8822531, 10139235
     10159846, 13257247, 8350262, 11792865, 7119382, 13632738, 11724962
     16309604, 16309605, 16309606, 8966823, 9320130, 16961614, 16961615
     13775862, 16961616, 11674645, 16961617, 16961618, 15877957, 7026523
     16961619, 15877958, 15877959, 9399589, 14841459, 9672816, 13503598
     9499302, 9150282, 9448311, 9659614, 13632743, 14220725, 9949948, 8882576
     10327179, 7612454, 7111619, 9711859, 9714832, 9735237, 9952230, 15877960
     12780098, 13561951, 15877961, 15877962, 14665116, 15877963, 8660422
     11066597, 16703112, 16279401, 14546673, 14105702, 14459552, 9713537
     14105703, 14105704, 13483152, 13737773, 13737775, 14269955, 12925532
     12748240, 9694101, 14390396, 12862186, 12862187, 10249537, 14727319
     9586877, 8211733, 6694396, 9548269, 7115910, 7710224, 9337325, 8354642
     7602341, 14076510, 10157402, 11856395, 12565867, 6402302, 10327190
     10269717, 13015379, 11693109, 14023636, 10017048, 8546356, 8394351
     9024850, 13561750, 8224558, 9770451, 9360157, 8488233, 9109487, 10132870
     14841558, 9171933, 16817117, 10173237, 9532911, 10068982, 7361418
     10306945, 8666117, 11725006, 6157713, 10214450, 9184754, 14205448
     8544696, 9767674, 16306019, 9323583, 8277300, 13343467, 16279211
     9726739, 16382448, 13791364, 8412426, 10326338, 10165083, 10208905
     12419392, 6651220, 9145204, 13554409, 11076894, 7450366, 11893577
     8970313, 14492313, 6690853, 6011045, 14492314, 11814891, 10162036
     14492315, 10248542, 14492316, 16742123, 9469117, 13359623, 9952270
     9842573, 13343471, 12710774, 10324526, 14546638, 12419258, 9322219
     8636407, 16056270, 10010310, 12828105, 9689310, 9390484, 13736501
     13736502, 9824435, 13736503, 13736504, 13736505, 13736506, 9963497
     9032322, 13736507, 12551700, 12551701, 14035825, 12551702, 11858315
     12551703, 12551704, 10076669, 16270946, 12551705, 12551706, 14040433
     12551707, 6076890, 14258925, 12551708, 9308296, 13916709, 12827745
     12880299, 14038805, 13923855, 9072105, 8528171, 11737047
--------------------------------------------------------------------------------

OPatch succeeded.

I hope this article helped you.

Regards,
Amit Rath


Saturday, September 28, 2013

step by step Upgrading Oracle Database 10.2.0.1 to 10.2.0.5 in RedHat Linux 5

PFB steps to upgrade Oracle Database 10.2.0.1 to 10.2.0.5 :-

1. Download patchset p8202632_10205_Linux-x86-64.zip  from metalink for respective OS, in my case for RHEL 5.

2. Unzip patchset to respective directory .

3. Backup your database before Upgrade.

4. Prerequisites for applying patchset 10.2.0.5

a.  Check Version of all registry components :-

SQL> Column comp_name format a40
SQL> Column version format a12
SQL> Column status format a6
SQL> Select comp_name, version, status from sys.dba_registry;

COMP_NAME                                VERSION      STATUS
---------------------------------------- ------------ ------
Oracle Database Catalog Views            10.2.0.1.0   VALID
Oracle Database Packages and Types       10.2.0.1.0   VALID
Oracle Workspace Manager                 10.2.0.1.0   VALID
JServer JAVA Virtual Machine             10.2.0.1.0   VALID
Oracle XDK                               10.2.0.1.0   VALID
Oracle Database Java Packages            10.2.0.1.0   VALID
Oracle Expression Filter                 10.2.0.1.0   VALID
Oracle Data Mining                       10.2.0.1.0   VALID
Oracle Text                              10.2.0.1.0   VALID
Oracle XML Database                      10.2.0.1.0   VALID
Oracle Rules Manager                     10.2.0.1.0   VALID

COMP_NAME                                VERSION      STATUS
---------------------------------------- ------------ ------
Oracle interMedia                        10.2.0.1.0   VALID
OLAP Analytic Workspace                  10.2.0.1.0   VALID
Oracle OLAP API                          10.2.0.1.0   VALID
OLAP Catalog                             10.2.0.1.0   VALID
Spatial                                  10.2.0.1.0   VALID
Oracle Enterprise Manager                10.2.0.1.0   VALID

17 rows selected.

b. Check version of database :-

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

c. Check INVALID objects in database :-

SQL> select object_name,status from dba_objects where status='INVALID';

no rows selected

if invalid objects exists then run below command :-

SQL> exec utl_recomp.recomp_serial ();

PL/SQL procedure successfully completed.

d. Check Version of TimeZone, Manage your data with timezone before upgrade :-

SQL> set lin 400
SQL> select version from v$timezone_file;

   VERSION
----------
         2

if current timezone version equals 2 then move forward with upgrade process
if current timezone version greater than 4 then check on metalink Note 553812.1.
if current timezone is less than 4 then PFB steps:-

Download utltzpv4.zip from metalink and run it on your database :-

[oracle@localhost DBSOFT]$ unzip utltzpv4.zip
Archive:  utltzpv4.zip
  inflating: utltzpv4.sql
[oracle@localhost DBSOFT]$ sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 27 10:53:14 2013

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

Enter user-name: /as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> @utltzpv4.sql
DROP TABLE sys.sys_tzuv2_temptab CASCADE CONSTRAINTS
               *
ERROR at line 1:
ORA-00942: table or view does not exist

Table created.

DROP TABLE sys.sys_tzuv2_affected_regions CASCADE CONSTRAINTS
               *
ERROR at line 1:
ORA-00942: table or view does not exist

Table created.

Your current timezone version is 2!
now checking all TIMESTAMP WITH TIMEZONE data..
.
Do a select * from sys.sys_tzuv2_temptab; to see if any TIMEZONE
WITH TIMEZONE data is affected by the update to RDBMS DSTv4 in the patchset.
.
Any table with YES in the nested_tab column (last column) needs
a manual check as these are nested tables.

PL/SQL procedure successfully completed.

Commit complete.

SQL> column table_owner format a4
column column_name format a18
select * from sys_tzuv2_temptab;SQL> SQL>

TABL TABLE_NAME                     COLUMN_NAME          ROWCOUNT NES
---- ------------------------------ ------------------ ---------- ---
SYS  SCHEDULER$_JOB                 LAST_ENABLED_TIME           3
SYS  SCHEDULER$_JOB                 LAST_END_DATE               1
SYS  SCHEDULER$_JOB                 LAST_START_DATE             1
SYS  SCHEDULER$_JOB                 START_DATE                  1
SYS  SCHEDULER$_JOB_RUN_DETAILS     REQ_START_DATE              1
SYS  SCHEDULER$_JOB_RUN_DETAILS     START_DATE                  1
SYS  SCHEDULER$_WINDOW              LAST_START_DATE             2

7 rows selected.

If the output of above query returns "NO ROWS" then move forward with the upgrade.
If output contains column names containing TZ data, which will be affected by the upgrade then see metalink Note 553812.1.
If output contains scheduler objects owned by SYS then we can ignore those, but if it contains user owned objects then take a backup and restore them after the upgrade.

4. Start the Upgrade :-

a. Stop database , listener, EM console, isqlplus(optional)

[oracle@localhost ~]$ sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 27 13:20:57 2013

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

Enter user-name: /as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[oracle@localhost ~]$ emctl stop dbconsole
TZ set to Asia/Calcutta
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation.  All rights reserved.
http://localhost.localdomain:1158/em/console/aboutApplication
Stopping Oracle Enterprise Manager 10g Database Control ...
 ...  Stopped.
[oracle@localhost ~]$ lsnrctl stop

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 27-SEP-2013 13:23:34

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
The command completed successfully
[oracle@localhost ~]$ isqlplusctl stop
iSQL*Plus 10.2.0.1.0
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Stopping iSQL*Plus ...
iSQL*Plus stopped.

b. unzip the p8202632_10205_Linux-x86-64.zip file , and run start applying patch

[oracle@localhost Disk1]$ ./runInstaller 
Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be redhat-3, SuSE-9, SuSE-10, redhat-4, redhat-5, redhat-6, UnitedLinux-1.0, asianux-1, asianux-2, asianux-3, enterprise-4, enterprise-5 or SuSE-11
                                      Passed

All installer requirements met.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2013-09-27_01-32-30PM. Please wait ...[oracle@localhost Disk1]$







Kernal parameters for 10.2.0.1 and 10.2.0.5 are different , Kindly change that.






Now Oracle Home is patched with 10.2.0.5 patch , Now upgrade your database :-

[oracle@localhost ~]$ cd $ORACLE_HOME
[oracle@localhost app]$ pwd
/oracle_home/app
[oracle@localhost app]$ cd rdbms/admin/

c. Start the database in UPGRADE mode :-

[oracle@localhost admin]$ sqlplus

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Sep 27 14:31:38 2013

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

Enter user-name: /as sysdba
Connected to an idle instance.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area  599785472 bytes
Fixed Size                  2098112 bytes
Variable Size             163580992 bytes
Database Buffers          427819008 bytes
Redo Buffers                6287360 bytes
Database mounted.
Database opened.

d . Run the preupgrade information tool :-

SQL> spool ugrade.log
SQL> @utlu102i.sql
Oracle Database 10.2 Upgrade Information Utility    09-27-2013 14:32:49
.
**********************************************************************
Database:
**********************************************************************
--> name:       ORCL
--> version:    10.2.0.1.0
--> compatible: 10.2.0.1.0
--> blocksize:  8192
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 490 MB
.... AUTOEXTEND additional space required: 10 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 403 MB
.... AUTOEXTEND additional space required: 368 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 256 MB
.... AUTOEXTEND additional space required: 16 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 58 MB
.... AUTOEXTEND additional space required: 38 MB
--> EXAMPLE tablespace is adequate for the upgrade.
.... minimum required size: 69 MB
.
**********************************************************************
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
-- No update parameter changes are required.
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
-- No obsolete parameters found. No changes are required
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views         [upgrade]  VALID
--> Oracle Packages and Types    [upgrade]  VALID
--> JServer JAVA Virtual Machine [upgrade]  VALID
--> Oracle XDK for Java          [upgrade]  VALID
--> Oracle Java Packages         [upgrade]  VALID
--> Oracle Text                  [upgrade]  VALID
--> Oracle XML Database          [upgrade]  VALID
--> Oracle Workspace Manager     [upgrade]  VALID
--> Oracle Data Mining           [upgrade]  VALID
--> OLAP Analytic Workspace      [upgrade]  VALID
--> OLAP Catalog                 [upgrade]  VALID
--> Oracle OLAP API              [upgrade]  VALID
--> Oracle interMedia            [upgrade]  VALID
--> Spatial                      [upgrade]  VALID
--> Expression Filter            [upgrade]  VALID
--> EM Repository                [upgrade]  VALID
--> Rule Manager                 [upgrade]  VALID
.
PL/SQL procedure successfully completed.

SQL>
SQL> spool off

Reflect the changes mentioned by above utility and start the Upgrade process

SQL> spool final_upgrade.log
SQL> @catupgrd.sql
DOC>######################################################################
DOC>######################################################################
DOC>    The following statement will cause an "ORA-01722: invalid number"
DOC>    error if the user running this script is not SYS.  Disconnect
DOC>    and reconnect with AS SYSDBA.
DOC>######################################################################
DOC>######################################################################
DOC>#

no rows selected

DOC>######################################################################
DOC>######################################################################
DOC>    The following statement will cause an "ORA-01722: invalid number"
DOC>    error if the database server version is not correct for this script.
DOC>    Shutdown ABORT and use a different script or a different server.
DOC>######################################################################
DOC>######################################################################
DOC>#

no rows selected

DOC>#######################################################################
DOC>#######################################################################
DOC>   The following statement will cause an "ORA-01722: invalid number"
DOC>   error if the database has not been opened for UPGRADE.
DOC>
DOC>   Perform a "SHUTDOWN ABORT"  and
DOC>   restart using UPGRADE.
DOC>#######################################################################
DOC>#######################################################################
DOC>#

no rows selected

DOC>#######################################################################
DOC>#######################################################################
DOC>    The following statements will cause an "ORA-01722: invalid number"
DOC>    error if the SYSAUX tablespace does not exist or is not
DOC>    ONLINE for READ WRITE, PERMANENT, EXTENT MANAGEMENT LOCAL, and
DOC>    SEGMENT SPACE MANAGEMENT AUTO.
DOC>
DOC>    The SYSAUX tablespace is used in 10.1 to consolidate data from
DOC>    a number of tablespaces that were separate in prior releases.
DOC>    Consult the Oracle Database Upgrade Guide for sizing estimates.
DOC>
DOC>    Create the SYSAUX tablespace, for example,
DOC>
DOC>     create tablespace SYSAUX datafile 'sysaux01.dbf'
DOC>         size 70M reuse
DOC>         extent management local
DOC>         segment space management auto
DOC>         online;
DOC>
DOC>    Then rerun the catupgrd.sql script.
DOC>#######################################################################
DOC>#######################################################################
DOC>#

no rows selected

.
.
.
..
.
..
.

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UPGRD_END  2013-09-27 14:59:32
.
Oracle Database 10.2 Upgrade Status Utility           09-27-2013 14:59:32
.
Component                                Status         Version  HH:MM:SS
Oracle Database Server                    VALID      10.2.0.5.0  00:08:52
JServer JAVA Virtual Machine              VALID      10.2.0.5.0  00:02:22
Oracle XDK                                VALID      10.2.0.5.0  00:00:24
Oracle Database Java Packages             VALID      10.2.0.5.0  00:00:18
Oracle Text                               VALID      10.2.0.5.0  00:00:26
Oracle XML Database                       VALID      10.2.0.5.0  00:01:35
Oracle Workspace Manager                  VALID      10.2.0.5.0  00:00:47
Oracle Data Mining                        VALID      10.2.0.5.0  00:00:26
OLAP Analytic Workspace                   VALID      10.2.0.5.0  00:00:21
OLAP Catalog                              VALID      10.2.0.5.0  00:01:07
Oracle OLAP API                           VALID      10.2.0.5.0  00:00:54
Oracle interMedia                         VALID      10.2.0.5.0  00:02:57
Spatial                                   VALID      10.2.0.5.0  00:02:01
Oracle Expression Filter                  VALID      10.2.0.5.0  00:00:13
Oracle Enterprise Manager                 VALID      10.2.0.5.0  00:01:13
Oracle Rule Manager                       VALID      10.2.0.5.0  00:00:08
.
Total Upgrade Time: 00:25:24
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC>   The above PL/SQL lists the SERVER components in the upgraded
DOC>   database, along with their current version and status.
DOC>
DOC>   Please review the status and version columns and look for
DOC>   any errors in the spool log file.  If there are errors in the spool
DOC>   file, or any components are not VALID or not the current version,
DOC>   consult the Oracle Database Upgrade Guide for troubleshooting
DOC>   recommendations.
DOC>
DOC>   Next shutdown immediate, restart for normal operation, and then
DOC>   run utlrp.sql to recompile any invalid application objects.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#

Upgrade completed. 

5. Post Upgrade Steps :-

a. Shut Down your database and restart it.

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

Total System Global Area  599785472 bytes
Fixed Size                  2098112 bytes
Variable Size             209718336 bytes
Database Buffers          381681664 bytes
Redo Buffers                6287360 bytes
Database mounted.
Database opened.

b. Check Invalid objects after upgrade :-

SQL> @utlrp.sql

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2013-09-27 15:41:49
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>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END  2013-09-27 15:42:55
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

c. Check Version of database and all components:-

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> Column comp_name format a40
SQL> Column version format a12
SQL> Column status format a6
SQL> Select comp_name, version, status from sys.dba_registry;

COMP_NAME                                VERSION      STATUS
---------------------------------------- ------------ ------
Oracle Database Catalog Views            10.2.0.5.0   VALID
Oracle Database Packages and Types       10.2.0.5.0   VALID
Oracle Workspace Manager                 10.2.0.5.0   VALID
JServer JAVA Virtual Machine             10.2.0.5.0   VALID
Oracle XDK                               10.2.0.5.0   VALID
Oracle Database Java Packages            10.2.0.5.0   VALID
Oracle Expression Filter                 10.2.0.5.0   VALID
Oracle Data Mining                       10.2.0.5.0   VALID
Oracle Text                              10.2.0.5.0   VALID
Oracle XML Database                      10.2.0.5.0   VALID
Oracle Rule Manager                      10.2.0.5.0   VALID
Oracle interMedia                        10.2.0.5.0   VALID
OLAP Analytic Workspace                  10.2.0.5.0   VALID
Oracle OLAP API                          10.2.0.5.0   VALID
OLAP Catalog                             10.2.0.5.0   VALID
Spatial                                  10.2.0.5.0   VALID
Oracle Enterprise Manager                10.2.0.5.0   VALID

Database is Successfully Upgraded from 10.2.0.1 to 10.2.0.5. 

I hope this article helped you.

Regards,
Amit Rath