Clone a PDB in Oracle 12c Multi tanent

Step 1: Let's create PDB_SOURCE for cloning
> create pluggable database pdb_source
  admin user pdb_source_admin identified by password
  role = (dba)
  default tablespace pdb_source_ts datafile '/mnt/san_storage/oradata/orcl12c/pdb_source/pdb_sts_datafile1.dbf' size 50M autoextend on
  file_name_convert = ('/oradata/orcl12c/pdbseed','/oradata/orcl12c/pdb_source');

Step 2: Open the PDB_SOURCE

> alter pluggable database pdb_source open;

Step 3: For coning PDB_SOURCE, first close the PDB_SOURCE and open it in read only mode.

> alter pluggable database pdb_source close;

> alter pluggable database pdb_source open read only;

Step 4: Clone the PDB_SOURCE to PDB_TARGET.

> create pluggable database pdb_target from pdb_source file_name_convert = ('/oradata/orcl12c/pdb_source','/oradata/orcl12c/pdb_target/');

Step 3: Open PDB_TARGET

> alter pluggable database pdb_target open;

To Find/Add/Remove Responsibilities assigned to a User in Bulk in ORACLE APPS

--------------------------------------------------------------------------------------------------------------
To get list of responsibilities assigned to a user
--------------------------------------------------------------------------
select fu.user_name,
  frt.responsibility_name,
  furg.start_date,
  furg.end_date
from fnd_user fu ,
  fnd_user_resp_groups_direct furg ,
  fnd_responsibility_vl frt
where fu.user_id                 = furg.user_id
and frt.responsibility_id        = furg.responsibility_id
and frt.application_id           = furg.responsibility_application_id
and nvl(furg.end_date,sysdate+1) > sysdate
and nvl(frt.end_date,sysdate +1) > sysdate
and fu.user_name                   = :p_user_name ---> fnd_user name is needed

--------------------------------------------------------------------------------------------------------------
To Add bulk of responsibilities to a user
--------------------------------------------------------------------------
set serveroutput on size 1000000;
    DECLARE
    v_user_name VARCHAR2 (100) := &1; ---> fnd_user name is needed
    v_responsibility_name VARCHAR2 (100);
    v_application_name VARCHAR2 (100) := NULL;
    v_responsibility_key VARCHAR2 (100) := NULL;
    v_security_group VARCHAR2 (100) := NULL;
    v_description VARCHAR2 (100) := NULL;
    CURSOR c1
    IS
    SELECT fa.application_short_name,
    fr.responsibility_key,
    frg.security_group_key,
    frt.description,
frt.responsibility_name
    FROM fnd_responsibility fr,
    fnd_application fa,
    fnd_security_groups frg,
    fnd_responsibility_tl frt
    WHERE fr.application_id = fa.application_id
    AND fr.data_group_id = frg.security_group_id
    AND fr.responsibility_id = frt.responsibility_id
    AND frt.LANGUAGE = USERENV ('LANG')
    AND frt.responsibility_name IN (
'responsibility1',
'responsibility2',
'responsibility3',
'responsibility4'
);
    BEGIN
    BEGIN
           FOR C IN C1
      LOOP
       BEGIN
        fnd_user_pkg.addresp (username => v_user_name,
        resp_app => c.application_short_name,
        resp_key => c.responsibility_key,
        security_group => c.security_group_key,
        description => c.description,
        start_date => SYSDATE,
        end_date => NULL);
        COMMIT;
        DBMS_OUTPUT.put_line ('Responsibility ' || c.responsibility_name || ' is attached to the user '|| v_user_name
        || ' Successfully');
            EXCEPTION
        WHEN OTHERS
      THEN
        DBMS_OUTPUT.put_line ('Issue while calling standard api' ||SQLERRM);
       END;
      END LOOP;
     EXCEPTION
     WHEN NO_DATA_FOUND
     THEN
      DBMS_OUTPUT.put_line ('NO data found while attaching responsibilty to the user and the error is '|| SQLERRM);
     WHEN OTHERS
     THEN
     DBMS_OUTPUT.put_line ('Error encountered while attaching responsibilty to the user and the error is '|| SQLERRM);
     END;
    END;
/

--------------------------------------------------------------------------------------------------------------------
To End date List of responsibilities assigned to a user
------------------------------------------------------------------------------
DECLARE
CURSOR c1
IS
SELECT fu.user_name,
fa.application_short_name,
frt.responsibility_name,
fr.responsibility_key,
fsg.security_group_key
FROM fnd_user_resp_groups_all ful,
fnd_user fu,
fnd_responsibility_tl frt,
fnd_responsibility fr,
fnd_security_groups fsg,
fnd_application fa

WHERE fu.user_id = ful.user_id
AND frt.responsibility_id = ful.responsibility_id
AND fr.responsibility_id = frt.responsibility_id
AND fsg.security_group_id = ful.security_group_id
AND fa.application_id = ful.responsibility_application_id
AND frt.language = 'US'
AND fu.user_name = :p_user_name ---> fnd_user name is needed

AND RESPONSIBILITY_NAME IN
('responsibility1',
'responsibility2',
'responsibility3',
'responsibility4');


BEGIN
FOR i IN c1
LOOP
BEGIN
fnd_user_pkg.delresp (username => i.user_name,
resp_app => i.application_short_name,
resp_key => i.responsibility_key,
security_group => i.security_group_key);
COMMIT;
DBMS_OUTPUT.
put_line (
i.responsibility_name || ' has been End Dated Successfully !!!');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.
put_line (
'Inner Exception: '
|| ' - '
|| i.responsibility_key
|| ' - '
|| SQLERRM);
END;
END LOOP;

EXCEPTION

WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);

END;
----------------------------------------------------------------------------------------------------------------------

Complete Workflow Mailer Backend Daily Scripts

--------------------------------------------------------------------------------------------------------------------------
Check workflow mailer service current status --Number of running processes should be greater than 0
---------------------------------------------------------------------------------
 select running_processes
    from apps.fnd_concurrent_queues
   where concurrent_queue_name = 'WFMLRSVC';


-------------------------------------------------------------------------------------------------------------------------
Find current mailer status
---------------------------------------------------------------------------------
select component_status
    from apps.fnd_svc_components
   where component_id =
        (select component_id
           from apps.fnd_svc_components
          where component_name = 'Workflow Notification Mailer');


-------------------------------------------------------------------------------------------------------------------------
Robust script for monitoring the wf_notifications table
---------------------------------------------------------------------------------
select message_type, mail_status, count(*) from wf_notifications
where status = 'OPEN'
GROUP BY MESSAGE_TYPE, MAIL_STATUS

--messages in 'FAILED' status can be resent using the concurrent request 'resend failed workflow notificaitons'
--messages which are OPEN but where mail_status is null have a missing email address for the recipient, but the notification preference is 'send me mail'


-------------------------------------------------------------------------------------------------------------------------
Some messages like alerts don't get a record in wf_notifications table so you have to watch the WF_NOTIFICATION_OUT queue.
---------------------------------------------------------------------------------
select corr_id, retry_count, msg_state, count(*)
from applsys.aq$wf_notification_out
group by corr_id, msg_state, retry_count
order by count(*) desc;

--Messages with a high retry count have been cycling through the queue and are not passed to smtp service.Messages which are 'expired' can be rebuilt using the wfntfqup.sql


-------------------------------------------------------------------------------------------------------------------------
The following SQL to collect all the info except IMAP account password.
---------------------------------------------------------------------------------
select p.parameter_id,
p.parameter_name,
v.parameter_value value
from fnd_svc_comp_param_vals_v v,
fnd_svc_comp_params_b p,
fnd_svc_components c
where c.component_type = 'WF_MAILER'
and v.component_id = c.component_id
and v.parameter_id = p.parameter_id
and p.parameter_name in ('OUTBOUND_SERVER', 'INBOUND_SERVER',
'ACCOUNT', 'FROM', 'NODENAME', 'REPLYTO','DISCARD' ,'PROCESS','INBOX')


------------------------------------------------------------------------------------------------------------------------
Check The Workflow notification has been sent or not
---------------------------------------------------------------------------------
SELECT status, mail_status  FROM wf_notifications WHERE notification_id = 10609859;

SELECT email_address, nvl(WF_PREF.get_pref(name, ‘MAILTYPE’),notification_preference)
FROM wf_roles
WHERE name = upper(‘&recipient_role’);


-------------------------------------------------------------------------------------------------------------------------
Query to get the log file of active workflow mailer and workflow agent listener Container
---------------------------------------------------------------------------------
select fl.meaning,fcp.process_status_code, decode(fcq.concurrent_queue_name,'WFMLRSVC', 'mailer container',
'WFALSNRSVC','listener container',fcq.concurrent_queue_name),
fcp.concurrent_process_id,os_process_id, fcp.logfile_name
from fnd_concurrent_queues fcq, fnd_concurrent_processes fcp , fnd_lookups fl
where fcq.concurrent_queue_id=fcp.concurrent_queue_id and fcp.process_status_code='A'
and fl.lookup_type='CP_PROCESS_STATUS_CODE' and fl.lookup_code=fcp.process_status_code
and concurrent_queue_name in('WFMLRSVC','WFALSNRSVC')
order by fcp.logfile_name;


-------------------------------------------------------------------------------------------------------------------------
Query to Check Workflow Mailer Backlog --State=Ready implies that emails are not being sent & Waiting mailer to send emails
---------------------------------------------------------------------------------
select tab.msg_state, count(*) from applsys.aq$wf_notification_out tab group by tab.msg_state ;


-------------------------------------------------------------------------------------------------------------------------
Check any particular Alert Message email has be pending by Mailer
---------------------------------------------------------------------------------
select decode(wno.state,
0, '0 = Pending in mailer queue',
1, '1 = Pending in mailer queue',
2, '2 = Sent by mailer on '||to_char(DEQ_TIME),
3, '3 = Exception', 4,'4 = Wait', to_char(state)) State,
to_char(DEQ_TIME),
wno.user_data.TEXT_VC
from wf_notification_out wno
where corrid='APPS:ALR'
and upper(wno.user_data.TEXT_VC) like '%<Subject of Alert Email>%';


-------------------------------------------------------------------------------------------------------------------------
Check Whether workflow background Engine is working for given workflow or not in last 2 days -- Note: Workflow Deferred activities are run by workflow background engine.
---------------------------------------------------------------------------------
select a.argument1,a.phase_code, a.status_code ,a.actual_start_date,a.* from fnd_concurrent_requests a
where CONCURRENT_PROGRAM_ID =
(select concurrent_program_id from fnd_concurrent_programs where
CONCURRENT_PROGRAM_NAME='FNDWFBG')
and last_update_Date>sysdate-2 and argument1='<Workflow Item Type>'
order by last_update_date desc


--------------------------------------------------------------------------------------------------------------------------
Query to get the log file of active workflow mailer and workflow agent listener Container
---------------------------------------------------------------------------------
select fl.meaning,fcp.process_status_code, decode(fcq.concurrent_queue_name,'WFMLRSVC', 'mailer container',
'WFALSNRSVC','listener container',fcq.concurrent_queue_name),
fcp.concurrent_process_id,os_process_id, fcp.logfile_name
from fnd_concurrent_queues fcq, fnd_concurrent_processes fcp , fnd_lookups fl
where fcq.concurrent_queue_id=fcp.concurrent_queue_id and fcp.process_status_code='A'
and fl.lookup_type='CP_PROCESS_STATUS_CODE' and fl.lookup_code=fcp.process_status_code
and concurrent_queue_name in('WFMLRSVC','WFALSNRSVC')
order by fcp.logfile_name;


-------------------------------------------------------------------------------------------------------------------------
Linux Shell script Command to get outbound error in Mailer
---------------------------------------------------------------------------------
grep -i '^\[[A-Za-z].*\(in\|out\).*boundThreadGroup.*\(UNEXPECTED\|ERROR\).*exception.*' <logfilename> | tail -10 ;
--Note: All Mailer log files starts with name FNDCPGSC prefix


--------------------------------------------------------------------------------------------------------------------------
Linux Shell script Command to get inbound processing error in Mailer 
---------------------------------------------------------------------------------
grep -i '^\[[A-Za-z].*.*inboundThreadGroup.*\(UNEXPECTED\|ERROR\).*exception.*' <logfilename> | tail -10 ;
----------------------------------------------------------------------------------------------------
1) Identify the concurrent tiers node where mailer runs 
---------------------------------------------------------------------------------------------------- 
select target_node
from fnd_concurrent_queues where concurrent_queue_name like 'WFMLRSVC%';
---------------------------------------------------------------------------------------------------- 
2) Gather other parameters values necessary for the SMTP telnet test: 
---------------------------------------------------------------------------------------------------- 
SELECT b.component_name,
       c.parameter_name,
       a.parameter_value
FROM fnd_svc_comp_param_vals a,
     fnd_svc_components b,
     fnd_svc_comp_params_b c
WHERE b.component_id = a.component_id
     AND b.component_type = c.component_type
     AND c.parameter_id = a.parameter_id
     AND c.encrypted_flag = 'N'
     AND b.component_name like '%Mailer%'
     AND c.parameter_name in ('OUTBOUND_SERVER', 'REPLYTO')
ORDER BY c.parameter_name;
---------------------------------------------------------------------------------------------------- 
3) Perform the SMTP telnet test as follows: 
---------------------------------------------------------------------------------------------------- 
3.1) Log on to the node where mailer runs (to identify it, please refer to step 1)

This is mandatory. SMTP telnet test is only meaningful when it is performed from the concurrent tier where mailer runs.

3.2) From mailer node, issue the following commands one by one:
telnet hostname.domain 25
EHLO OPRDCM1
MAIL FROM: mailserver@email.com
RCPT TO: abc@xyz.com
DATA
Subject: Test message

Test message body 
. 
quit
--------------------------------------------------------------------------------------------------------------------------

Moving PDB in between CDB's

Step 1: Let's create PDB1 for demo
> create pluggable database pdb1
  admin user pdb1_admin identified by password
  role = (dba)
  default tablespace pdb1_ts datafile '/mnt/san_storage/oradata/orcl12c/pdb1/pdb1_ts_datafile1.dbf' size 50M autoextend on
  file_name_convert = ('/oradata/orcl12c/pdbseed','/oradata/orcl12c/pdb1');

Step 2: Open the PDB1

> alter pluggable database pdb1 open;

Step 3: For Moving PDB1, first close the PDB1

> alter pluggable database pdb1 close;

Step 4: Unplug the database

> alter pluggable database pdb1 unplug into 'mnt/san_storage/oradata/orcl12c/pdb1/pdb1.xml';

Step 5: Drop the database PDB1 keeping the datafiles;

> drop pluggable database pdb1 keep datafiles;

Step 6: Now plug PDB1 to the new CDB

> connect cdb$root of new CDB as sysdba

> create pluggable database pdb1_new using '/mnt/san_storage/oradata/orcl12c/pdb1/pdb1.xml' nocopy tempfile reuse;

Note : We can give new pdb name while using xml file or we can use the same name.

Step 7: Open newely plugged PDB1_NEW in the new CDB.

> alter pluggable database pdb1_new open;

How to Create, Connect and Drop Pluggable database(PDB)

Some conceptual points for Create PLUGGABLE database -

• Oracle 12c uses SEED$PDB as template to create new PDB's.

• When you create a new PDB from SEED$PDB, you have to define admin user for that new PDB. This creates a local user inside the PDB.

• Admin command also grants the PDB_DBA role to the ADMIN user that we created.

• By default "CREATE PLUGGABLE DATABASE" command does not grant any privileges to the PDB_DBA role itself. So our admin user is powerless.

• In order to let this user to control PDB, we have to explicitly grant dba role to PDB user.

Following is the command used to create new pluggable database in Oracle 12c.
> CREATE PLUGGABLE DATABASE my_new_pdb

   ADMIN USER my_pdb_admin IDENTIFIED BY password

   ROLES = (dba)

   DEFAULT TABLESPACE my_tbs 

   DATAFILE '/mnt/san_storage/oradata/orcl12c/my_new_pdb/mytbs01.dbf' SIZE 50M AUTOEXTEND ON

   FILE_NAME_CONVERT=('/oradata/orcl12c/pdbseed/','/oradata/orcl12c/my_new_pdb/');

To Check if this PDB is created run the following command
> select con_id, name from v$pdbs;

To connect the PDB, run following commands -
> alter pluggable database my_new_pdb open;
> exit;

sqlplus my_pdb_admin/password@localhost:1523/my_new_pdb
> show con_name;
> show con_id;

Or, You can also use "alter session" command to directly connect to any container
> alter session set container = my_new_pdb;
> alter session set container = cbd$root;


To drop PDB use the following commands
> alter pluggable database my_new_pdb close;
> drop pluggable database my_new_pdb including datafiles;

How to check database size in Oracle

A DBA works on many aspects of database like cloning, backup, performance tuning etc. In every aspect of database administration, most of the times resolution depends upon the size of database. For example, DBA can implement DB FULL backup strategy on a very small database when compared to DB INCREMENTAL strategy on a very large database.

Use below script to check db size along with Used space and free space in database:

col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size" , round(sum(used.bytes) / 1024 / 1024 / 1024 ) - round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space" , round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space" from (select bytes from v$datafile union all select bytes from v$tempfile union all select bytes from v$log) used , (select sum(bytes) as p from dba_free_space) free group by free.p /

How to disable firewall in Linux 7

Firewall Status 

The below command will show you the current status “Active” in case firewall is running: 

# systemctl status firewalld

Firewall stop / start 
# service firewalld stop 
# service firewalld start 

You can start/stop Linux firewall with below commands:

Firewall Disable / Enable 
You can enable/disable firewall completely on Linux with below commands: 
# systemctl disable firewalld 
# systemctl enable firewalld

Remove Oracle Table Lock

1. Run the following to get session id of locked table.

     SELECT SESSION_ID 

     FROM DBA_DML_LOCKS 

     WHERE NAME = 'Table Name';

  2. Use this session id to find SERIAL# by using following SELECT statment

     SELECT SID,SERIAL# 
     FROM V$SESSION 
     WHERE SID IN (SELECT SESSION_ID 
     FROM DBA_DML_LOCKS 
     WHERE NAME = 'Table Name');

     3.  Use ALTER SYSTEM command to KILL SESSION and this will release the lock:

     ALTER SYSTEM KILL SESSION 'SID,SERIALl#';

Steps to upgrade Oracle Database 10.2.0.1 to 10.2.0.5 in RedHat Linux 5

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 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
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 SOFT]$ unzip utltzpv4.zip

Archive:  utltzpv4.zip

  inflating: utltzpv4.sql

[oracle@localhost SOFT]$ 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;

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 / as sysdba

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.

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

SQL> shut 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> 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> shut 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.