06 November, 2024

OEM Agent Installation


./agentDeploy.sh AGENT_BASE_DIR=/u001/oracle/product/agent13c OMS_HOST=oem-dev.xxx.com EM_UPLOAD_PORT=1159 AGENT_REGISTRATION_PASSWORD=****** -ignorePrereqs -force AGENT_PORT=3872



And if tmp have less space

====================

./agentDeploy.sh AGENT_BASE_DIR=/u001/oracle/product/agent13c OMS_HOST=oem-dev.xxx.com EM_UPLOAD_PORT=1159 AGENT_REGISTRATION_PASSWORD=****** -ignorePrereqs -force AGENT_PORT=3872 "SCRATCHPATH=/u001/oracle/product/tmp"

01 September, 2023

Useful Scripts

To Find session details using SID.


set verify off

col sid format 99999

col machine format a10

col program format a25 trunc

col username format a10      

col logontime format a15

col osuser format a10 trunc

col proginfo format a30 trunc

accept trgtsid number default 0 prompt 'What is the SID : '

select to_char(s.logon_time,'mm/dd hh24:mi:ss') logontime,

       s.sid,s.serial#,s.status,s.type,s.username,s.osuser,s.machine,

       s.module || ' - ' || s.program proginfo,

       s.process,p.spid, s.sql_hash_value

from v$session s, v$process p

where sid = &trgtsid

and p.addr = s.paddr;



To Find the process details


set echo off

set serveroutput on size 999999

set verify off

set feedback off

accept uxproc prompt 'Enter Unix process id: '

DECLARE

  v_sid number;

  vs_cnt number;

  s sys.v_$session%ROWTYPE;

  p sys.v_$process%ROWTYPE;

  cursor cur_c1 is select sid from sys.v_$process p, sys.v_$session s  where  p.addr  = s.paddr and  (p.spid =  &uxproc or s.process = '&uxproc');

BEGIN

    dbms_output.put_line('=====================================================================');

        select nvl(count(sid),0) into vs_cnt from sys.v_$process p, sys.v_$session s  where  p.addr  = s.paddr and  (p.spid =  &uxproc or s.process = '&uxproc');

        dbms_output.put_line(to_char(vs_cnt)||' sessions were found with '||'&uxproc'||' as their unix process id.');

         dbms_output.put_line('=====================================================================');

        open cur_c1;

        LOOP

      FETCH cur_c1 INTO v_sid;

            EXIT WHEN (cur_c1%NOTFOUND);

                select * into s from sys.v_$session where sid  = v_sid;

                select * into p from sys.v_$process where addr = s.paddr;

                dbms_output.put_line('SID/Serial  : '|| s.sid||','||s.serial#);

                dbms_output.put_line('Foreground  : '|| 'PID: '||s.process||' - '||s.program);

                dbms_output.put_line('Shadow      : '|| 'PID: '||p.spid||' - '||p.program);

                dbms_output.put_line('Terminal    : '|| s.terminal || '/ ' || p.terminal);

                dbms_output.put_line('OS User     : '|| s.osuser||' on '||s.machine);

                dbms_output.put_line('Ora User    : '|| s.username);

                dbms_output.put_line('Details     : '|| s.action||' - '||s.module);

                dbms_output.put_line('Status Flags: '|| s.status||' '||s.server||' '||s.type);

                dbms_output.put_line('Tran Active : '|| nvl(s.taddr, 'NONE'));

                dbms_output.put_line('Login Time  : '|| to_char(s.logon_time, 'Dy HH24:MI:SS'));

                dbms_output.put_line('Last Call   : '|| to_char(sysdate-(s.last_call_et/60/60/24), 'Dy HH24:MI:SS') || ' - ' || to_char(s.last_call_et/60, '99990.0') || ' min');

                dbms_output.put_line('Lock/ Latch : '|| nvl(s.lockwait, 'NONE')||'/ '||nvl(p.latchwait, 'NONE'));

                dbms_output.put_line('Latch Spin  : '|| nvl(p.latchspin, 'NONE'));

                dbms_output.put_line('Current SQL statement:');

                for c1 in ( select * from sys.v_$sqltext  where HASH_VALUE = s.sql_hash_value order by piece)

                loop

                dbms_output.put_line(chr(9)||c1.sql_text);

                end loop;

                dbms_output.put_line('Previous SQL statement:');

                for c1 in ( select * from sys.v_$sqltext  where HASH_VALUE = s.prev_hash_value order by piece)

                loop

                dbms_output.put_line(chr(9)||c1.sql_text);

                end loop;

                dbms_output.put_line('Session Waits:');

                for c1 in ( select * from sys.v_$session_wait where sid = s.sid)

                loop

        dbms_output.put_line(chr(9)||c1.state||': '||c1.event);

                end loop;

--  dbms_output.put_line('Connect Info:');

--  for c1 in ( select * from sys.v_$session_connect_info where sid = s.sid) loop

--    dbms_output.put_line(chr(9)||': '||c1.network_service_banner);

--  end loop;

                dbms_output.put_line('Locks:');

                for c1 in ( select  /*+ RULE */ decode(l.type,

          -- Long locks

                      'TM', 'DML/DATA ENQ',   'TX', 'TRANSAC ENQ',

                      'UL', 'PLS USR LOCK',

          -- Short locks

                      'BL', 'BUF HASH TBL',  'CF', 'CONTROL FILE',

                      'CI', 'CROSS INST F',  'DF', 'DATA FILE   ',

                      'CU', 'CURSOR BIND ',

                      'DL', 'DIRECT LOAD ',  'DM', 'MOUNT/STRTUP',

                      'DR', 'RECO LOCK   ',  'DX', 'DISTRIB TRAN',

                      'FS', 'FILE SET    ',  'IN', 'INSTANCE NUM',

                      'FI', 'SGA OPN FILE',

                      'IR', 'INSTCE RECVR',  'IS', 'GET STATE   ',

                      'IV', 'LIBCACHE INV',  'KK', 'LOG SW KICK ',

                      'LS', 'LOG SWITCH  ',

                      'MM', 'MOUNT DEF   ',  'MR', 'MEDIA RECVRY',

                      'PF', 'PWFILE ENQ  ',  'PR', 'PROCESS STRT',

                      'RT', 'REDO THREAD ',  'SC', 'SCN ENQ     ',

                      'RW', 'ROW WAIT    ',

                      'SM', 'SMON LOCK   ',  'SN', 'SEQNO INSTCE',

                      'SQ', 'SEQNO ENQ   ',  'ST', 'SPACE TRANSC',

                      'SV', 'SEQNO VALUE ',  'TA', 'GENERIC ENQ ',

                      'TD', 'DLL ENQ     ',  'TE', 'EXTEND SEG  ',

                      'TS', 'TEMP SEGMENT',  'TT', 'TEMP TABLE  ',

                      'UN', 'USER NAME   ',  'WL', 'WRITE REDO  ',

                      'TYPE='||l.type) type,

                                  decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',

                       4, 'S',    5, 'RSX',  6, 'X',

                       to_char(l.lmode) ) lmode,

                                   decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',

                         4, 'S', 5, 'RSX', 6, 'X',

                         to_char(l.request) ) lrequest,

                                        decode(l.type, 'MR', o.name,

                      'TD', o.name,

                      'TM', o.name,

                      'RW', 'FILE#='||substr(l.id1,1,3)||

                            ' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,

                      'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,

                      'WL', 'REDO LOG FILE#='||l.id1,

                      'RT', 'THREAD='||l.id1,

                      'TS', decode(l.id2, 0, 'ENQUEUE', 'NEW BLOCK ALLOCATION'),

                      'ID1='||l.id1||' ID2='||l.id2) objname

                                from  sys.v_$lock l, sys.obj$ o

                                where sid   = s.sid

                                        and l.id1 = o.obj#(+) )

                        loop

                        dbms_output.put_line(chr(9)||c1.type||' H: '||c1.lmode||' R: '||c1.lrequest||' - '||c1.objname);

                        end loop;

                        dbms_output.put_line('=====================================================================');

        END LOOP;

        dbms_output.put_line(to_char(vs_cnt)||' sessions were found with '||'&uxproc'||' as their unix process id.');

        dbms_output.put_line('Please scroll up to see details of all the sessions.');

        dbms_output.put_line('=====================================================================');

        close cur_c1;

exception

    when no_data_found then

      dbms_output.put_line('Unable to find process id &&uxproc!!!');

          dbms_output.put_line('=====================================================================');

      return;

    when others then

      dbms_output.put_line(sqlerrm);

      return;

END;

/

undef uxproc

set heading on

set verify on

set feedback on

set echo on



To find currently running requests details.

set pages 300
set lines 300 
col os form A7 head AppProc
col spid form a6 head DBProc
col program form A43 trunc
set pages 38
col time form 9999.99 head Elapsed
col "Req Id" form 9999999999
col "Parent" form a8
col "Prg Id" form 999999
col qname head "Concurrent Manager Queue" format a25 trunc
col sid format 99999 head SID
col Parent for a9
set recsep off
select q.concurrent_queue_name || ' - ' || target_node qname 
      ,a.request_id "Req Id" 
      ,decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent"
      ,a.concurrent_program_id "Prg Id"
      ,a.phase_code,a.status_code
      ,b.os_process_id "OS"
      ,vs.sid
      ,vp.spid
      ,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 "Time"
      ,c.concurrent_program_name||' - '||
       c2.user_concurrent_program_name "program"
from APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
    ,applsys.fnd_concurrent_queues q
    ,APPLSYS.fnd_concurrent_programs_tl c2
    ,APPLSYS.fnd_concurrent_programs c
    ,v$session vs
    ,v$process vp
where a.controlling_manager = b.concurrent_process_id
  and a.concurrent_program_id = c.concurrent_program_id
  and a.program_application_id = c.application_id
  and c2.concurrent_program_id = c.concurrent_program_id
  and a.phase_code in ('I','P','R','T')
  and b.queue_application_id = q.application_id
  and b.concurrent_queue_id = q.concurrent_queue_id
  and c2.language = 'US'
  and vs.process (+) = b.os_process_id
  and vs.paddr = vp.addr (+)
order by 1,2
/


CON_AVG_MIN_MAX


set linesize 200
col username for a10
col status for a10
col phase  for a10
col PNAME for a70
col request_id for 99999999999
col PNAME  for a40
select
b.user_name username,
a.USER_CONCURRENT_PROGRAM_NAME  as PNAME,
avg((nvl(ACTUAL_COMPLETION_DATE,sysdate)-a.REQUESTED_START_DATE)*24) avg_Hrs_running,  
max((nvl(ACTUAL_COMPLETION_DATE,sysdate)-a.REQUESTED_START_DATE)*24) Max_Hrs_running,
min((nvl(ACTUAL_COMPLETION_DATE,sysdate)-a.REQUESTED_START_DATE)*24) Min_Hrs_running
from
apps.fnd_conc_req_summary_v a,
apps.fnd_user  b
where
phase_code = 'C' and status_code in  ('C','E','X','G') and
a.REQUESTED_START_DATE >= sysdate-10 and
upper(a.USER_CONCURRENT_PROGRAM_NAME) like upper('%Interface%Trip%Stop%') and
a.REQUESTED_BY=b.user_id
group by b.user_name,a.USER_CONCURRENT_PROGRAM_NAME;




GetOrclID.sh


DT=$(date +"%d%m%Y")
if [ -d $DT ];then
   mv $DT $DT'_BKP'
   mkdir $DT
else
   mkdir $DT
fi
cat /dev/null > ldapcmd
chmod +x ldapcmd
cat /dev/null > UserGuid.txt
cat /dev/null > /app/orclas/admin/scripts/users/inconsUser/04MAR16/RemoveGuid.sh
cat /dev/null > /app/orclas/admin/scripts/users/inconsUser/04MAR16/RemoveSub.sh
cat /dev/null > /app/orclas/admin/scripts/users/inconsUser/04MAR16/DrpUser.sh
BSPTH='/app/orclas/admin/scripts/users/inconsUser/'${DT}'/'
RmGuidFL='/app/orclas/admin/scripts/users/inconsUser/'${DT}'/RemoveGuid.sh'
RmSubsFL='/app/orclas/admin/scripts/users/inconsUser/'${DT}'/RemoveSubs.sh'
RmUserFL='/app/orclas/admin/scripts/users/inconsUser/'${DT}'/RemoveUser.sh'
cat /dev/null > $RmGuidFL
cat /dev/null > $RmSubsFL
cat /dev/null > $RmUserFL
while read line
do
echo 'ldapsearch -h hostname.domain.com -p 1389 -D "cn=orcladmin" -w xxxxxx -b "cn='$line',ou=na,cn=users,dc=corp,dc=net" -s sub "objectclass=*" orclguid' > ldapcmd
    oguid=`ldapcmd |grep orclguid|cut -d '=' -f2`
echo $oguid >> UserGuid.txt
echo xxxxxx|ldifwrite connect="oiddb" basedn="cn=$line,ou=na,cn=users,dc=corp,dc=net" ldiffile="$BSPTH$line.ldif"

 echo 'echo xxxxxx|bulkdelete connect=oiddb basedn="orclOwnerGUID='$oguid',cn=ACCOUNTS,cn=subscription_data,cn=subscriptions,orclApplicationCommonName=EBIZ_PRD,cn=EBusiness,cn=Products,cn=OracleContext,dc=corp,dc=net"' >> $RmGuidFL 

echo 'echo xxxxxx|bulkdelete connect=oiddb basedn="cn='${line}',ou=na,cn=users,dc=corp,dc=net"' >> $RmUserFL 

echo "dn:cn=ACCOUNTS,cn=subscription_data,cn=subscriptions,orclApplicationCommonName=EBIZ_PRD,cn=EBusiness,cn=Products,cn=OracleContext,dc=corp,dc=net" > $BSPTH$line'_SubRemove.ldif'
echo "changetype: modify" >> $BSPTH$line'_SubRemove.ldif'
echo "delete: uniquemember" >> $BSPTH$line'_SubRemove.ldif'
echo "uniquemember: cn=${line},ou=na,cn=users,dc=corp,dc=net" >> $BSPTH$line'_SubRemove.ldif'

echo '$ORACLE_HOME/bin/ldapmodify -h hostname1.domain.com -p 1389 -D cn=orcladmin -w xxxxxx -v -f '$line'_SubRemove.ldif;' >> $RmSubsFL
# done < /app/orclas/admin/scripts/users/UserOrclID.txt
done < $1
# done < /app/orclas/admin/scripts/users/UserOrclID1.txt




OID Scripts


applmgr@hostname:/app/orclas/admin/scripts/fix_users> cat searchOIDuser.sh
ldapsearch -h hostname.domin.com -p 1389 -D "cn=orcladmin" -w xxxxxx "cn=$1"
applmgr@hostname:/app/orclas/admin/scripts/fix_users> cat searchusrAD.sh
ldapsearch -h hostname.domin.com -p 3268 -D "svc-oracle-oid@domin.com" -w oracleOID1 -b "dc=corp,dc=net" -s sub "cn=$1" dn
applmgr@hostname:/app/orclas/admin/scripts/fix_users> cat findGUID.sh
ldapsearch -h hostname.domin.com -p 1389 -D "cn=orcladmin" -w xxxxxx -b "cn=$1,ou=na,cn=users,dc=corp,dc=net" -s sub "uid=*" orclguid
applmgr@hostname:/app/orclas/admin/scripts/fix_users> cat findSubscription.sh
ldapsearch -h hostname.domin.com -p 1389 -D cn=orcladmin -w xxxxxx -b "orclOwnerGUID=$1,cn=accounts,cn=subscription_data,cn=subscriptions,orclapplicationcommonname=EBIZ_PRD,cn=ebusiness,cn=products,cn=oraclecontext,dc=corp,dc=net" -s sub objectclass=* dn
applmgr@hostname:/app/orclas/admin/scripts/fix_users> cat findGUID_External.sh
ldapsearch -h hostname.domin.com -p 1389 -D "cn=orcladmin" -w xxxxxx "cn=$1" orclguid


applmgr@hostname:/app/orclas/admin/scripts/users/inconsUser> cat GetOrclID.sh
DT=$(date +"%d%m%Y")
if [ -d $DT ];then
   mv $DT $DT'_BKP'
   mkdir $DT
else
   mkdir $DT
fi
cat /dev/null > ldapcmd
chmod +x ldapcmd
cat /dev/null > UserGuid.txt
cat /dev/null > /app/orclas/admin/scripts/users/inconsUser/04MAR16/RemoveGuid.sh
cat /dev/null > /app/orclas/admin/scripts/users/inconsUser/04MAR16/RemoveSub.sh
cat /dev/null > /app/orclas/admin/scripts/users/inconsUser/04MAR16/DrpUser.sh
BSPTH='/app/orclas/admin/scripts/users/inconsUser/'${DT}'/'
RmGuidFL='/app/orclas/admin/scripts/users/inconsUser/'${DT}'/RemoveGuid.sh'
RmSubsFL='/app/orclas/admin/scripts/users/inconsUser/'${DT}'/RemoveSubs.sh'
RmUserFL='/app/orclas/admin/scripts/users/inconsUser/'${DT}'/RemoveUser.sh'
cat /dev/null > $RmGuidFL
cat /dev/null > $RmSubsFL
cat /dev/null > $RmUserFL
while read line
do
        echo 'ldapsearch -h hostname.domin.com -p 1389 -D "cn=orcladmin" -w xxxxxx -b "cn='$line',ou=na,cn=users,dc=corp,dc=net" -s sub "objectclass=*" orclguid' > ldapcmd
    oguid=`ldapcmd |grep orclguid|cut -d '=' -f2`
        echo $oguid >> UserGuid.txt
echo xxxxxx|ldifwrite connect="oiddb" basedn="cn=$line,ou=na,cn=users,dc=corp,dc=net" ldiffile="$BSPTH$line.ldif"

 echo 'echo xxxxxx|bulkdelete connect=oiddb basedn="orclOwnerGUID='$oguid',cn=ACCOUNTS,cn=subscription_data,cn=subscriptions,orclApplicationCommonName=EBIZ_PRD,cn=EBusiness,cn=Products,cn=OracleContext,dc=corp,dc=net"' >> $RmGuidFL

echo 'echo xxxxxx|bulkdelete connect=oiddb basedn="cn='${line}',ou=na,cn=users,dc=corp,dc=net"' >> $RmUserFL

echo "dn:cn=ACCOUNTS,cn=subscription_data,cn=subscriptions,orclApplicationCommonName=EBIZ_PRD,cn=EBusiness,cn=Products,cn=OracleContext,dc=corp,dc=net" > $BSPTH$line'_SubRemove.ldif'
echo "changetype: modify" >> $BSPTH$line'_SubRemove.ldif'
echo "delete: uniquemember" >> $BSPTH$line'_SubRemove.ldif'
echo "uniquemember: cn=${line},ou=na,cn=users,dc=corp,dc=net" >> $BSPTH$line'_SubRemove.ldif'

echo '$ORACLE_HOME/bin/ldapmodify -h hostname.domin.com -p 1389 -D cn=orcladmin -w xxxxxx -v -f '$line'_SubRemove.ldif;' >> $RmSubsFL
# done < /app/orclas/admin/scripts/users/UserOrclID.txt
done < $1
# done < /app/orclas/admin/scripts/users/UserOrclID1.txt
applmgr@hostname:/app/orclas/admin/scripts/users/inconsUser>



applmgr@hostname:/app/orclas/admin/scripts/users/inconsUser> cat GetOrclID_ExtUsr.sh
DT=$(date +"%d%m%Y")
if [ -d $DT ];then
   mv $DT $DT'_BKP'
   mkdir $DT
else
   mkdir $DT
fi
cat /dev/null > ldapcmd
chmod +x ldapcmd
cat /dev/null > UserGuid.txt
cat /dev/null > /app/orclas/admin/scripts/users/inconsUser/04MAR16/RemoveGuid.sh
cat /dev/null > /app/orclas/admin/scripts/users/inconsUser/04MAR16/RemoveSub.sh
cat /dev/null > /app/orclas/admin/scripts/users/inconsUser/04MAR16/DrpUser.sh
BSPTH='/app/orclas/admin/scripts/users/inconsUser/'${DT}'/'
RmGuidFL='/app/orclas/admin/scripts/users/inconsUser/'${DT}'/RemoveGuid.sh'
RmSubsFL='/app/orclas/admin/scripts/users/inconsUser/'${DT}'/RemoveSubs.sh'
RmUserFL='/app/orclas/admin/scripts/users/inconsUser/'${DT}'/RemoveUser.sh'
cat /dev/null > $RmGuidFL
cat /dev/null > $RmSubsFL
cat /dev/null > $RmUserFL
while read line
do
        echo 'ldapsearch -h hostname.domin.com -p 1389 -D "cn=orcladmin" -w xxxxxx -b "cn='$line',ou=na,cn=odcustomer,cn=odexternal,cn=users,dc=corp,dc=net" -s sub "objectclass=*" orclguid' > ldapcmd
    oguid=`ldapcmd |grep orclguid|cut -d '=' -f2`
        echo $oguid >> UserGuid.txt
echo xxxxxx|ldifwrite connect="oiddb" basedn="cn=$line,ou=na,cn=odcustomer,cn=odexternal,cn=users,dc=corp,dc=net" ldiffile="$BSPTH$line.ldif"

 echo 'echo xxxxxx|bulkdelete connect=oiddb basedn="orclOwnerGUID='$oguid',cn=ACCOUNTS,cn=subscription_data,cn=subscriptions,orclApplicationCommonName=EBIZ_PRD,cn=EBusiness,cn=Products,cn=OracleContext,dc=corp,dc=net"' >> $RmGuidFL

echo 'echo xxxxxx|bulkdelete connect=oiddb basedn="cn='${line}',ou=na,cn=odcustomer,cn=odexternal,cn=users,dc=corp,dc=net"' >> $RmUserFL

echo "dn:cn=ACCOUNTS,cn=subscription_data,cn=subscriptions,orclApplicationCommonName=EBIZ_PRD,cn=EBusiness,cn=Products,cn=OracleContext,dc=corp,dc=net" > $BSPTH$line'_SubRemove.ldif'
echo "changetype: modify" >> $BSPTH$line'_SubRemove.ldif'
echo "delete: uniquemember" >> $BSPTH$line'_SubRemove.ldif'
echo "uniquemember: cn=${line},ou=na,cn=odcustomer,cn=odexternal,cn=users,dc=corp,dc=net" >> $BSPTH$line'_SubRemove.ldif'

echo '$ORACLE_HOME/bin/ldapmodify -h hostname.domin.com -p 1389 -D cn=orcladmin -w xxxxxx -v -f '$line'_SubRemove.ldif;' >> $RmSubsFL
# done < /app/orclas/admin/scripts/users/UserOrclID.txt
done < $1
# done < /app/orclas/admin/scripts/users/UserOrclID1.txt
applmgr@hostname:/app/orclas/admin/scripts/users/inconsUser>





applmgr@hostname:/app/orclas/admin/scripts/users/inconsUser> cat /app/ebs/appl_share/scripts/fix_user.sql
select user_guid from apps.fnd_user where user_name='&1';
 declare
 c fnd_sso_util.userCursor;
 iter number:=0;
 begin
   open c for select * from fnd_user
   where user_name ='&1' and user_guid is null;
   fnd_sso_util.link_batch(c);
   iter:=iter+1;
   dbms_output.put_line('Users updated are: ' || iter);
   close c;
   if( iter=0) then
        dbms_output.put_line(iter);
   end if;
 end;
/
applmgr@hostname:/app/orclas/admin/scripts/users/inconsUser>


28 May, 2023

Script not working via crontab

Issue:  

I have written one script which is running fine when i ran it manually  but same script when i have scheduled  in crontab not working.


Solution: 

Source .bash_profile  in your script. It should work.

Especially when you use EOF , you must source .bash_profile in the script

. ~/.bash_profile



30 January, 2023

Online Patching (ADOP) in Oracle EBS R12.2

 Online patching is supported by the capability of storing multiple application editions in the database, and the provision of a dual application tier file system. At any given point in time, one of these file systems is designated as run (part of the running system) and the other as patch (either being patched or awaiting the start of the next patching cycle).


For applying a patch in R12.2 you need to use adop and run through all below phases in sequence mentioned below.

1) adop phase=prepare
2) adop phase=apply patches=<patch_number1>,<patch_number2> workers=<number_of_worker>
3) adop phase=finalize workers=<number_of_worker> (called automatically)
4) adop phase=cutover workers=<number_of_worker>
5) adop phase=cleanup (called automatically)

OR

Running all phases in single command:

adop phase=prepare,apply,finalize,cutover,cleanup patches=<patch_number1>,<patch_number2>
——————————————————————————————————————–

 

DESCRIPTION OF EACH PHASE

1) PREPARE PHASE DETAILS

Used to start a new online patching cycle

How to execute:
A) Set the environment by executing (sourcing) the run file system environment file:
$ source <EBS install base>/EBSapps.env run
B) Verify envirionment
You can confirm that the environment is properly set by examining the relevant environment variables:

$ echo $FILE_EDITION
 run
$ echo $TWO_TASK
 dbSID

C) Download Patches

Download patches to be applied and place then in the $PATCH_TOP directory of your system. This directory is pre-created by the install in the non-editioned file system (fs_ne) and should not be changed.

Important: On a multi-node system with non-shared file systems, you must copy the patch files to each separate $PATCH_TOP directory, so that the patch files are available from the same location on all nodes.
D) Unzip the patch

$ unzip <patch>.zip

E) Run Prepare Command

Prepare the system for patching by running the following command to start a new patching cycle:

$ adop phase=prepare

 

What it will do:

• Checks whether to perform a cleanup, which will be needed if the user failed to invoke cleanup after the cutover phase of a previous online patching cycle.
• Checks the integrity of the database data dictionary. If any corruption is found, adop exits with an error.
• Checks system configuration on each application tier node. A number of critical settings are validated to ensure that each application tier node is correctly registered, configured, and ready for patching.
• Checks for the existence of the “Online Patching In Progress” (ADZDPATCH) concurrent program. This program prevents certain predefined concurrent programs from being started, and as such needs to be active while a patching cycle is in progress (that is, while a database patch edition exists). If the ADZDPATCH program has not yet been requested to run, a request is submitted.
Note: ADZDPATCH is cancelled later on when the cutover phase is complete.
• Checks to see if the patch service has been created. adop requires that a special database service exists for the purpose of connecting to the patch edition. This service is created automatically, but its continued existence is validated on each prepare.

It can be checked by the database parameter SERVICE_NAME

SQL> show parameter service_name
NAME TYPE VALUE
 ------------------------------------ ----------- --------------- 
service_names string dba, ebs_patch

Here dba is the SID of our database and ebs_patch is additional service_name which is required by online patching tool.

If you look at tnsnames.ora file in the Application tier $TNS_ADMIN directory you will find below kind of entry:

<SID>_patch=
 (DESCRIPTION=
 (ADDRESS=(PROTOCOL=tcp)(HOST=<your_database_server_name>)(PORT=<your_database_port>))
 (CONNECT_DATA=
 (SERVICE_NAME=ebs_patch)
 (INSTANCE_NAME=<your_database_SID>)
 )
 )

During patching phase, adop will use this tns entry to connect to database.

• Invokes the TXK script $AD_TOP/patch/115/bin/txkADOPPreparePhaseSynchronize.pl to synchronize the patches which have been applied to the run APPL_TOP, but not the patch APPL_TOP.
• Checks the database for the existence of a patch edition, and creates one if it does not find one.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

2) APPLY PHASE DETAILS

In the apply phase, adop applies the specified patches to the system. Patches are applied to the patch edition of the database and file system.

How to execute:

Example:

$ adop phase=apply patches=1234,7891 workers=8

Where 1234 and 7891 are the patch numbers

What it will do:

If a post-installation patch step mentions any tasks that need to be performed explicitly, where they are run from depends on the type of patching:

• In a normal online patching cycle, the steps should be executed from the patch file system after the apply phase.
• If the patch is being applied in hotpatch mode or downtime mode, the steps should be executed from the run file system after the apply phase.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

3) FINALIZE PHASE DETAILS

The finalize phase will be executed while the application is still online. It is used to perform any remaining processing that is needed to ensure the system is ready for the fastest possible cutover.

Used to perform the final patching operations that can

How to execute:

$ adop phase=finalize

What it will do:

• Pre-compute DDL that needs to be run at cutover.
• Compile all invalid objects.
• Validate that the system is ready for cutover.
If finalize_mode=full, compute statistics for key data dictionary tables for improved
performance.

VERY IMPORTANT 1 : Up to this phase, you can run a special phase called abort, which will undo the changes made so far in the patching cycle. After cutover is complete, however, you cannot do this.

VERY IMPORTANT 2 : In an online patching cycle, the requisite JAR files are initially stored in the $APPL_TOP/admin/<SID>/out directory, and then uploaded into the database during the cutover phase. Therefore, the out directory must not be deleted at least until cutover (next phase) is complete.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

4) CUTOVER PHASE DETAILS

Used to perform the transition to the patched environment. Shuts down application tier services, makes the patch edition the new run edition, and then restarts application tier services. This is the only phase the involves a brief downtime.

Important: No users should remain on the system during cutover, as there will be a short downtime period while the application tier services are restarted. Also, any third-party processes connected to the
old run edition of the database should be shut down, or they will be terminated automatically.

How to execute:

$ adop phase=cutover

What it will do:
• Shut down internal concurrent manager. The adop utility signals the internal concurrent manager to shut down, but will wait for any existing concurrent requests to finish before it proceeds with cutover actions.

Note: Cutover will take longer if it has to wait for long-running concurrent processes to complete. In such a case, you can expect to see an informational message of the form: [STATEMENT] [END 2013/10/28 23:47:16] Waiting for ICM to go downIf you do not want to wait for in-progress concurrent requests to finish normally, you can terminate the internal concurrent manager by executing the adcmctl.sh abort command from a different shell.

• Shut down application tier services: All application tier services are brought down. During this period, the system is unavailable to users.
• Cutover database: Promote patch database edition to become the new run database edition, using adzdpmgr.pl script.
• Cutover file system: Promote patch file system to become the new run file system, switching the $FILE_EDITION values in the patch and run enviroments. The current patch APPL_TOP becomes the new run APPL_TOP, and the current run APPL_TOP becomes the new patch APPL_TOP. Terminate old database sessions: Terminate any database connections to the old run edition of the database.
• Start application tier services: Application tier services are restarted, on the new run edition. The system is now available again to users
• ADZDPATCH concurrent program is cancelled when the cutover phase is complete.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

5) CLEANUP PHASE DETAILS

Important: If you fail to run the cleanup phase explicitly, it will be run automatically on the next prepare cycle, but this will cause a delay in starting your next online patching cycle.

This adop phase is used to remove obsolete code and data from old editions.

How to execute:

$ adop phase=cleanup

What it will do:

• Various actions are performed during cleanup, including dropping (removing) obsolete: Crossedition triggers, Seed data, Editioned code objects (covered objects), Indexes, Columns, Editions.
Using parameter cleanup_mode:

a) cleanup_mode=quick – Performs minimum cleanup, including removal of obsolete crossedition triggers and seed data.

Use quick cleanup when you need to start the next patching cycle as soon as possible. For example, if you want to start a new patching cycle right away, but have not yet run cleanup from the previous patching cycle, you can use quick cleanup mode to complete the essential cleanup tasks as fast as possible.

b) cleanup_mode=standard – Does the same as quick mode, and also drops (removes) obsolete editioned code objects (covered objects).

This is the default mode , so does not need to be specified.
c) cleanup_mode=full – Performs maximum cleanup, which drops all obsolete code and data from earlier editions

Use full cleanup when you want to recover the maximum amount of space in the database. If you have run a large number of patching cycles, or applied a very large patch such as a rollup, significant space may be consumed by obsolete table columns and recovered by running a full cleanup. A full cleanup should only be performed when there is no immediate need to start a new patching cycle.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

THERE ARE TWO SPECIAL PHASES:

A) ABORT PHASE DETAILS

Abort PHASE is conditional phase. This phase cannot be specified with any other phase.

If for some reason either the prepare or apply phase failed or gave problems, you can abort the patching cycle at either of these points by running a special phase with the Command. The actions taken will be discarded (rollbacked).

IMPORTANT: This abort command is only available up to (but not including) the cutover phase. After cutover, the system is running on the new edition, and abort is no longer possible for that patching cycle.
How to execute:

The command to perform this operation is:

$ adop phase=abort

What it will do:

• Confirms that there is an in-progress online patching cycle, so the abort call is therefore valid.
• Checks for the existence of a patch edition and drops one if it exists.
• Cancels the ADZDPATCH concurrent program, if it is running.
• Deletes the rows inserted for the pending session ID from the ad_adop_sessions and ad_adop_session_patches tables.

VERY IMPORTANT: After running abort, a full cleanup must be performed. The cleanup command is: adop phase=cleanup cleanup_mode=full). This will remove any columns that were added by the patch but are no longer needed because of the abort. If they are not removed, they may cause problems in a later patching cycle.

Alternatively, you can run a combined command to abort the patching cycle and perform a full cleanup:

$ adop phase=abort,cleanup cleanup_mode=full

If any attempt was made to apply patches to the patch edition, after abort you must run the fs_clone phase (adop phase=fs_clone) to recreate the patch file system.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

B) FS_CLONE PHASE DETAILS

The fs_clone phase is a command (not related to adcfgclone.pl) that is used to synchronize the patch file system with the run file system. The fs_clone phase should only be run when mentioned as part of a specific documented procedure.

How to execute:

The fs_clone phase is run using the following command:

$ adop phase=fs_clone

What it will do:

This phase is useful if the APPL_TOPs have become very unsynchronized (meaning that there would be a large number of delta patches to apply). It is a heavyweight process, taking a backup of the entire current patch APPL_TOP and then cloning the run APPL_TOP to create a new patch APPL_TOP. As this method requires more time and disk space, it should only be used when the state of the patch file system is unknown. This command must be invoked from the run file system, before the next prepare phase is run.

Note: The patch file system requires at least 25 GB of free disk space to be available for adop operations, including fs_clone. If there is insufficient free space, the adop operation will fail.

If an fs_clone operation fails, you can rerun it with the option force=yes to restart it from the beginning (with the same session ID), or force=no to restart it from the point where it failed.

——————————————————————————————————————–

IMPORTANT POINTS REGARDING ONLINE PATCHING:

1. adop utility is put under $APPL_TOP_NE/ad/bin. It is a wrapper script which calls internally the perl script $AD_TOP/bin/adzdoptl.pl which does actual work of applying the patch.

2. adop will automatically set its environment as required, but it is the user’s responsibility to set the environment correctly for any other commands that may be run. Set the run edition environment whenever executing commands that you intend to affect the run edition.

For example:

$ . <EBS_ROOT>/EBSapps.env run
 $ adstrtal.sh

Set the patch edition environment whenever you intend to execute commands that affect the patch edition.
For example:

$ . <EBS_ROOT>/EBSapps.env patch
 $ sqlplus apps/apps @my_custom_patch_script.sql

3. All the phases need to be completed and you can’t skip any of these. For example, if you try to skip prepare phase, you may get error message like “Apply phase can only be run while in a patching cycle, i.e. after prepare phase.”
4. After an online patching cycle is started, you should not perform any configuration changes in the run edition file system. Any that are made will not be propagated and will therefore be lost after cutover is complete.
5. You should not attempt to clone an Oracle E-Business Suite system while an online patching cycle is in progress.
6. The prepare, apply, and fs_clone phases all require at least 10GB of free disk space. All other phases require 1GB of free space. A warning message will be displayed if less than the needed amount is available.
7. The directories where you extracted the patches applied in a given patching cycle must be retained, in the same location and with the same contents, until the next prepare phase completes. This is also a requirement for patches applied in a hotpatch session.
8. Maintenance Mode is not needed for online patching, and so Maintenance Mode is not available in Oracle E-Business Suite Release 12.2.

——————————————————————————————————————-

ADOP ON MULTI-NODE

In a multi-node environment, one application tier node will be designated as the primary node. This is the node where the Admin Server is located, and will usually also be the node that runs Oracle HTTP Server. All other application tier nodes are designated as secondary nodes.

adop commands are invoked by a user on the primary node. Internally, adop uses Secure Shell (ssh) to automatically execute required patching actions on all secondary nodes. You must set up passwordless ssh connectivity from the primary node to all secondary nodes.

If a node unexpectedly becomes inaccessible via ssh, it will be abandoned by adop, and the appropriate further actions taken. Consider a scenario where the adop phase=prepare command is run in a system with ten application tier nodes. The command is successful on nine nodes, but fails on the tenth. In such a case, adop will identify the services enabled on nodes 1-9. If they are sufficient for Oracle E-Business Suite to continue to run normally, adop will mark node 10 as abandoned and then proceed with its patching actions. If they are not sufficient, adop will proceed no further.

25 January, 2023

How to compile a form manually in 12.2

We have found in certain instances that it seems like adop is trying to compile the new form file against the package(s) in the RUN database rather than the PATCH database and therefore trying to compile new new form against the old packages. This is just a working theory. But when this happens, we skip the job and continue with the patch to completion. Once the patch is completed and cutover, we find we can successfully compile the form from command line:

 

1. Source RUN edition as EBS Applications owner


2. Back up your current copy of the *.fmx version of the form.

cp $FND_TOP/forms/US/FNDRSRUN.fmx $FND_TOP/forms/US/FNDRSRUN.fmx.BAK


3. Add $AU_TOP/forms/US to the FORMS_PATH environment variable.


export FORMS_PATH=$FORMS_PATH:$AU_TOP/forms/US


4. Compile the form, for example:

frmcmp_batch  module=$AU_TOP/forms/US/FNDRSRUN.fmb userid=apps/xxxxxxx output_file=$FND_TOP/forms/US/FNDRSRUN.fmx module_type=form compile_all=yes


check errors and make sure it compile successfully. If so, you should have a new version of FNDRSRUN.fmx in $FND_TOP/forms/US


 

19 January, 2023

How to drop old editions with the actualize_all phase

In EBS R12.2, as each online patching cycle is completed, the database will accumulate an additional old database edition.

An additional column ZD_EDITION_NAME is populated in the seed tables. If the number of these grows too large, system performance will start to be affected. 

When the number of old database editions reaches 25 or more, we should consider dropping all old database editions by running the adop actualize_all phase and then performing a full cleanup.

I normally consider to do it, once the count reaches 15 editions in my Production environment.


Note: This procedure will take a large amount of time (significantly longer than a normal patching cycle), and should only be performed when there is no immediate need to start a new patching cycle.


Before starting, you should ensure that the system has the recommended database patches and latest AD-TXK code level installed.


When no patches need to be applied in Online Patching

To proceed, run the following commands in the order shown:

$ adop phase=prepare
$ adop phase=actualize_all
$ adop phase=finalize finalize_mode=full
$ adop phase=cutover
$ adop phase=cleanup cleanup_mode=full

Old database editions would be cleared now


OR

Every-time online patching is performed:

$ adop phase=prepare
$ adop phase=apply patches=1,2,3
$ adop phase=actualize_all
$ adop phase=finalize finalize_mode=full
$ adop phase=cutover
$ adop phase=cleanup cleanup_mode=full

It has to be performed just before phase=finalize/cutover

09 November, 2021

ORA-04063: Package Body "APPS.AD_ZD_ADOP" Has Errors when running adop

While running adop prepare,  the error is encountered on APPS.AD_ZD_ADOP . 

When checking further the package body is in invalid state.



Error:

When we try to compile manually, we observe below error.


SQL> alter package APPS.AD_ZD_ADOP compile body;
Warning: Package Body altered with compilation errors.
SQL> show err
Errors for PACKAGE BODY APPS.AD_ZD_ADOP:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2686/3 PL/SQL: Statement ignored
2686/7 PLS-00201: identifier 'SYS.DBMS_METADATA_UTIL' must be declared


Solution:


1. Connect as sysdba and 
   grant execute on DBMS_METADATA_UTIL to apps;
2. alter package APPS.AD_ZD_ADOP compile body;

The package should be compiled normally now.

OEM Agent Installation

./agentDeploy.sh AGENT_BASE_DIR=/u001/oracle/product/agent13c OMS_HOST=oem-dev.xxx.com EM_UPLOAD_PORT=1159 AGENT_REGISTRATION_PASSWORD=*****...