Shrinking FND_LOG_MESSAGES

EBS Table Inventory

For a variety of reasons, I took inventory of the largest objects (in this particular case, tables) in my R12.2 EBS environment.  I strongly suggest you do the same, upon occasion, as it’s somewhat enlightening.

select
  owner
 ,segment_name
 ,bytes/1024/1024/1024 gb
from
  dba_segments
where
  segment_type = 'TABLE'
order by
  bytes/1024/1024/1024 desc

At the very top of my list was APPLSYS.FND_LOG_MESSAGES, checking in at 29.4GB.  I now had a target.

Read the directions

This is EBS, we can’t just arbitrarily start shooting and hope we don’t break anything.  There’s a number of MOS notes on support describing various aspects of purging this table.  Number one on the list was the concurrent request, “Purge Logs and Closed System Alerts”.  There were also some notes about a bug which prevented this CR from performing correctly, but they didn’t seem relevant to EBS 12.2

Back to my problem child

SQL> select count(*) from applsys.fnd_log_messages;

  COUNT(*)
----------
       308

This jumps out immediately.

SQL> select owner, segment_name, bytes/1024/1024/1024 gb 
from dba_segments where segment_type = 'TABLE' 
and segment_name = 'FND_LOG_MESSAGES';

OWNER                     SEGMENT_NAME              GB
------------------------- ------------------------- ----------
APPLSYS                   FND_LOG_MESSAGES          29.4005127

I run Concurrent Request “Purge Logs and Closed System Alerts” every week, and the log shows it’s completing normal.  Why are 308 rows consuming so much space on disk?  Let’s assume that this table has had no attention since it was first installed about 14 years ago.  There were times when debug was on, to be sure, filling FND_LOG_MESSAGES faster than normal.  But after a quick check of Profile Option Values I see that at present, nobody has debug enabled.

Proving out the solution

Is it possible that 14 years of inserts and deletes could cause this table to have developed a bad case of high water mark 29 GB deep?

SQL> alter table applsys.fnd_log_messages enable row movement;

Table altered.

Elapsed: 00:00:00.08
SQL> alter table applsys.fnd_log_messages shrink space;

Table altered.

Elapsed: 00:56:47.99
SQL> select owner, segment_name, 
bytes/1024/1024/1024 gb from dba_segments 
where segment_type = 'TABLE' 
and segment_name = 'FND_LOG_MESSAGES';

OWNER        SEGMENT_NAME     GB
------------ ---------------- ---------
APPLSYS      FND_LOG_MESSAGES 14.501709
Elapsed: 00:00:00.31

Not bad; we’re down from 29.4GB to 14.5GB without much effort.  Let’s go in for the kill

SQL> alter table applsys.fnd_log_messages move;

Table altered.

Elapsed: 00:01:30.32

SQL> select owner, segment_name, 
bytes/1024/1024/1024 gb from dba_segments 
where segment_type = 'TABLE' 
and segment_name = 'FND_LOG_MESSAGES';

OWNER   SEGMENT_NAME     GB
------- ---------------- ----------
APPLSYS FND_LOG_MESSAGES .000366211
Elapsed: 00:00:00.13

Sweet.  29GB down to .0003GB.

Don’t forget that alter table xyz move renders indexes invalid.

set pagesize 0
select
'alter index '
|| owner
|| '.'
|| index_name
|| ' rebuild;'
from
dba_indexes
where
status = 'UNUSABLE'
;

SQL> alter index applsys.fnd_log_messages_n4 rebuild;

Index altered.

SQL> alter index applsys.fnd_log_messages_n5 rebuild;

Index altered.

SQL> alter index applsys.fnd_log_messages_n7 rebuild;

Index altered.

SQL> alter index applsys.fnd_log_messages_n8 rebuild;

Index altered.

R12.2 ADOP quick Notes

ADOP Patching:

adop phases:

prepare       : Prepare the instance for online patching.
apply         : Apply patch(es) to the Patch Edition.  
finalize      : Ready the instance for cutover.  
abort         : Abort the patching cycle.  
cutover       : Promote the Patch Edition to Run Edition.   
cleanup       : Drop obsolete objects and seed data from Old Editions.
actualize_all : Actualize all objects in the Patch Edition.   
cleanup_full  : Cleanup and drop Old Editions.   
abandon       : yes|no - Abandon failed patches.

adop patch log directory:

<INSTALL BASE>/fs_ne/EBSapps/log/adop

adop patch process cycle steps:

Download any required technology patches and unzip the contents. The patch contents may be unzipped into  $NE_BASE/EBSapps/patch.

1. Prepare the system for patching. 

   source <EBS_ROOT>/EBSapps.env run

   $ adop phase=prepare 

2. Apply technology patches to the Oracle Home under the Patch f/s using the information below. 

   source <EBS_ROOT>/EBSapps.env patch 

3. Apply any Oracle E-Business Suite patches planned for this patching cycle 

   $ adop phase=apply patches=<patch_list> 

4. After all patches have been successfully applied, complete the patching cycle. 

   $ adop phase=finalize 
   $ adop phase=cutover 

Note: Below steps can be done when applications are up and running 

5. source <EBS_ROOT>/EBSapps.env run 
   
   $ adop phase=cleanup

6. To complete the process and synchronize the technology level between patch and run f/s. 

   $ adop phase=fs_clone

adop hotpatch steps (we cannot abort hotpatch and abondon cannot be done):

Hotpatch which can apply directly on run fs

$ adop phase=apply patches=<patch_list> hotpatch=yes

After hotpatch please run phase=cleanup and phase=fs_clone to sync the run fs with patch fs to prepare for next patching cycle

adop re-apply patch forcefully:

If we try to re-apply the patch which is already applied/exists then adop patch terminates 

with below message and by default it takes N when prompted. to overcome this we need to

re-apply patch with options=forceapply.

This Patch seems to have been applied already.
Would you like to continue anyway  [N] ? N *

$ adop phase=apply patches=<patch list> hotpatch=yes options=forceapply

adop deal with "Continue As If It Were Successful" error:

$ adop phase=apply patches=<patch list> abandon=no restart=yes flags=autoskip

To define workers in adop:

$ adop phase=apply patches=<patch list> workers=5

To define patchtop in adop:

$ adop phase=apply patches=<patch list> patchtop=<patch location base>

adop merge patch:

$ adop phase=apply patches=<patch list> merge=yes

Restarting adop From A Failed Session:

 $ adop phase=abort
 $ adop phase=cleanup cleanup_mode=full
 $ adop phase=fs_clone 

Then reapply the patch

adop apply for language patch:

$ adop phase=apply patches=18023722_ESA:u18023722.drv

adop non-interactive with patch top and define driver:

$ adop phase=apply options=nocopyportion patchtop=$XLA_TOP/patch/115 patches=driver:xla5584908.drv

adop Steps to follow to skip the failed workers:

1. Use adctrl and select option#8 (This will not be visible) to skip thefailed jobs
2. Restart adop using "restart=yes" parameter

** If the failed jobs are numerous, then better to re-start this patch once again with autoskip option.

ie.  adop restart=no abandon=yes flags=autoskip 

This command will restart the patch once again from starting onwards and will skip all the failures if any comes. But make sure to review the log file at the end of the patch application that you have skipped the failures that you want to.

Weblogic Server Smart Update Patching :

Steps to apply weblogic server smart update patch:

Refer Note: 
How to Apply WebLogic Server (WLS) Patches Using Smart Update [Video] (Doc ID 876004.1)

1.Download the patch 
2.Copy the files (for example, E5W8.jar and WGQJ.jar) and the patch-catalog_xxx.xml from the zip file to the target machine. You do not need the readme file.
Path: $FMW_HOME/utils/bsu/cache_dir

3.cd $FMW_HOME/utils/bsu

To install patch:
./bsu.sh -prod_dir=$FMW_HOME/wlserver_10.3 -patchlist=7FC9 -verbose -install

To Verify:
./bsu.sh -prod_dir=$FMW_HOME/wlserver_10.3 -status=applied -verbose -view | grep 7FC9

To Rollback:
./bsu.sh -remove -patchlist=7FC9 -prod_dir=$FMW_HOME/wlserver_10.3 -verbose

We can also apply the Smart Update patch in graphical (GUI) mode.

Steps to apply opatch on FMW Web Tier HOME :

Set the Environment as below (replace <INSTALL_BASE> path as required): 
 
$ export ORACLE_HOME=$FMW_HOME/webtier
$ export PATH=$ORACLE_HOME/OPatch:$PATH

$ opatch lsinventory 


Apply opatch:

$ opatch apply

Steps to apply opatch on FMW oracle_common HOME :

Set the Environment as below:

$ export ORACLE_HOME=$FMW_HOME/oracle_common
$ export PATH=$ORACLE_HOME/OPatch:$PATH

$ opatch lsinventory 

Apply opatch:

$ opatch apply

Queries :

Query to check if a patch is applied in Oracle EBS R12.2.x:

In Oracle E Business Suite (ebs erp) R12.2.x you cannot query the AD_BUGS table to check if patches have been applied..
The AD_BUGS table may have entries for patches that were applied but later the patching cycle was aborted (not really applied).


The way to check whether a patch is really applied is to use the AD_PATCH.IS_PATCH_APPLIED PL/SQL function.

Usage:

select AD_PATCH.IS_PATCH_APPLIED(\'$release\',\'$appltop_id\',\'$patch_no\',\'$language\') 

from dual;

Example sql:

SELECT adb.bug_number,ad_patch.is_patch_applied('11i', 1045, adb.bug_number)
FROM ad_bugs adb
WHERE adb.bug_number in (20034256);

or for single app tier installations:

select ad_patch.is_patch_applied('R12',-1,20034256) from dual;

Expected results:

EXPLICIT = applied
NOT APPLIED = not applied / aborted

Note: If you are sure patch is applied, but showing as not applied then do the following workaround.

1. Start adadmin after source the RUN FS env.
2. Select "2. Maintain Applications Files menu" in "AD Administration Main Menu".
3. In "Maintain Applications Files", select "4. Maintain snapshot information".
4. Select "2. Update current view snapshot" in the "Maintain Snapshot Information".
5. Select "1. Update Complete APPL_TOP" in the "Maintain Current View Snapshot Information".

Query to check current AD patchset:

SELECT a.application_short_name, b.patch_level
FROM fnd_application_vl a,fnd_product_installations b
WHERE a.application_id = b.application_id
  and application_short_name = 'AD';

Query to check patches applied correctly and in the expected sequence:

1.1.Run this sql statement:

   select * from ad_adop_session_patches order by end_date desc;

1.2. Run this piece of sql code:

   set pagesize 200;
   set linesize 160;
   column adop_session_id format 999999999999;
   column bug_number format a15;
   column status format a15;
   column applied_file_system_base format a23;
   column patch_file_system_base format a23;
   column adpatch_options format a15;
   column node_name format a15;
   column end_date format a15;
   column clone_status format a15;

   select ADOP_SESSION_ID, BUG_NUMBER, STATUS, APPLIED_FILE_SYSTEM_BASE, PATCH_FILE_SYS   TEM_BASE, ADPATCH_OPTIONS, NODE_NAME, END_DATE, CLONE_STATUS
   from ad_adop_session_patches
   order by end_date desc;
 

Below are possible values of STATUS column:

N - Not Applied In the current node but applied in other nodes
R - Patch Application is going on.
H - Patch failed in the middle. (Hard Failure)
F - Patch failed in the middle but user tried to skip some failures.
S - Patch Application succeeded after skipping the failed jobs.
Y - Patch Application succeeded.
C - Reserved for clone and config_clone. Indicates clone completed

Query to Check AD and TXK C Patch levels:

SELECT codelevel FROM AD_TRACKABLE_ENTITIES WHERE abbreviation in ('txk','ad');

[Troubleshooting] Oracle Apps R12.2 Online Patching ADOP : Prepare Phase Issue

This post covers, Online Patching (ADOP)  issue reported by one of the trainees in Oracle AppsDBA Training  with prepare phase.

Note: There are five phases in Online Patching (ADOP) in R12.2  PREPARE -> APPLY -> FINALIZE -> CUTOVER  -> CLEANUP .

• To know more about Online Patching (ADOP) click here or for FREE video on How to find patches in EBS R12, check here 

Troubleshooting ADOP Prepare Phase Issue:

1. Run Prepare Phase:

Any Patching Cycle starts with Prepare Phase and to start it run below command

adop phase=prepare

Prepare phase is failing with error message

    [UNEXPECTED]fs_clone has failed.

        [UNEXPECTED]Error calling runPendingConfigClone subroutine.

2. Look at ADOP logs:

First thing in troubleshooting is to look at log file (in this case ADOP log file)

Note: Kindly refer this post to know more on log file location in R12.2 Oracle Apps R12.2 Log Files Location every Apps DBA must know

A) Error in adop logs: 

/u01/oracle/PRD122/fs_ne/EBSapps/log/adop/6/prepare_20171109_032332/PRD122_1705ecloud05/log/adop.log

Note: Here 

• /u01/oracle/PROD is Oracle EBS install base directory
• fs_ne Non-Editioned File System
• 6 – adop session id
• prepare_20171109_032332 – <phase>_<date>_<time>
• PRD122_1705ecloud05 – CONTEXT NAME

Oracle Apps R12.2 has Dual File System (fs1 & fs2), which helps organizations to have less downtime during patching.  By introducing duplicate file system, Oracle has reduced patching downtime and improved availability of the system.

We can focus on three major parts of the E-Business Suite 12.2 file system:

• File System 1 (fs1):  /u01/oracle/PROD/fs1
• File System 2 (fs2):  /u01/oracle/PROD/fs2
• Non-Editioned File System (fs_ne):  /u01/oracle/PROD/fs_ne

Thu Nov  9 03:34:15 2017

*******FATAL ERROR*******

PROGRAM : (/u01/oracle/PRD122/fs1/EBSapps/appl/ad/12.0.0/patch/115/bin/txkADOPPreparePhaseSynchronize.pl)

TIME    : Thu Nov  9 03:34:15 2017

FUNCTION: main::migrateCloneComponentApply [ Level 1 ] 

ERRORMSG: /u01/oracle/PRD122/fs1/EBSapps/comn/adopclone_1705ecloud05/bin/adclone.pl did not go through successfully.

[UNEXPECTED]Error occurred running “perl /u01/oracle/PRD122/fs1/EBSapps/appl/ad/12.0.0/patch/115/bin/txkADOPPreparePhaseSynchronize.pl -contextfile=/u01/oracle/PRD122/fs1/inst/apps/PRD12111_1705ecloud05/appl/admin/PRD12111_1705ecloud05.xml -patchcontextfile=/u01/oracle/PRD122/fs2/inst/apps/PRD12111_1705ecloud05/appl/admin/PRD12111_1705ecloud05.xml -promptmsg=hide -console=off -mode=migrate -sync_mode=copy -sessionid=6 -timestamp=20171109_032332 -outdir=/u01/oracle/PRD122/fs_ne/EBSapps/log/adop/6/20171109_032332/prepare/1705ecloud05”

        [UNEXPECTED]occurred during CONFIG_CLONE Patch File System from Run File System, running command: “perl /u01/oracle/PRD122/fs1/EBSapps/appl/ad/12.0.0/patch/115/bin/txkADOPPreparePhaseSynchronize.pl -contextfile=/u01/oracle/PRD122/fs1/inst/apps/PRD12111_1705ecloud05/appl/admin/PRD12111_1705ecloud05.xml -patchcontextfile=/u01/oracle/PRD122/fs2/inst/apps/PRD12111_1705ecloud05/appl/admin/PRD12111_1705ecloud05.xml -promptmsg=hide -console=off -mode=migrate -sync_mode=copy -sessionid=6 -timestamp=20171109_032332 -outdir=/u01/oracle/PRD122/fs_ne/EBSapps/log/adop/6/20171109_032332/prepare/1705ecloud05”.

        [PROCEDURE] [START 2017/11/09 03:34:15] Updating status of patches

        [STATEMENT] SQL statement : ”  update ad_adop_session_patches

  set status=’F’

  where applied_file_system_base = ‘/u01/oracle/PRD122/fs1’ and

  patch_file_system_base = ‘/u01/oracle/PRD122/fs2′ and bug_number=’CONFIG_CLONE’ and status <> ‘Y’

  and appltop_id=195 and node_name=’1705ecloud05′”

        [PROCEDURE] [END   2017/11/09 03:34:16] Updating status of patches

 [STATEMENT] SQL statement : ”  update ad_adop_session_patches

  set status=’F’

  where applied_file_system_base = ‘/u01/oracle/PRD122/fs1’ and

  patch_file_system_base = ‘/u01/oracle/PRD122/fs2′ and bug_number=’CONFIG_CLONE’ and status <> ‘Y’

  and appltop_id=195 and node_name=’1705ecloud05′”

        [PROCEDURE] [END   2017/11/09 03:34:16] Updating status of patches

        [UNEXPECTED]fs_clone has failed.

        [UNEXPECTED]Error calling runPendingConfigClone subroutine.

B) Error in fs_clone logs: 

Note: Prepare Phase, creates clone of Patch File System from Run File System and as per previous log, error was during this Cloning so we need to look at clone logs.

$INST_TOP/admin/log/clone/FSCloneApplyAppsTier_<timestamp>.log

 START: Instantiating the OHS Config.

Running /u01/oracle/PRD122/fs2/FMW_Home/webtier/perl/bin/perl  -I/u01/oracle/PRD122/fs2/FMW_Home/webtier/perl/lib/5.10.0 -I/u01/oracle/PRD122/fs2/FMW_Home/webtier/perl/lib/site_perl/5.10.0 -I/u01/oracle/PRD122/fs2/EBSapps/appl/au/12.0.0/perl -I/u01/oracle/PRD122/fs2/FMW_Home/webtier/ohs/mod_perl/lib/site_perl/5.10.0/x86_64-linux-thread-multi /u01/oracle/PRD122/fs2/EBSapps/appl/fnd/12.0.0/patch/115/bin/txkSetOHSConfig.pl cfgCloneInstance -ctxfile=/u01/oracle/PRD122/fs2/inst/apps/PRD12111_1705ecloud05/appl/admin/PRD12111_1705ecloud05.xml -archvloc=/u01/oracle/PRD122/fs1/EBSapps/comn/adopclone_1705ecloud05/FMW/OHS/ohsarchive.jar -mvpln=/u01/oracle/PRD122/fs1/EBSapps/comn/adopclone_1705ecloud05/FMW/OHS/moveplan.xml -t2plogloc=/u01/oracle/PRD122/fs1/inst/apps/PRD12111_1705ecloud05/admin/log/clone/ohsT2PApply -logdir=/u01/oracle/PRD122/fs1/inst/apps/PRD12111_1705ecloud05/admin/log/clone -console=off -promptmsg=hide

Script Executed in 37703 milliseconds, returning status 255

ERROR: Script failed, exit code 255

Error Message  :1

  [PLUGIN][OHS] – ERROR – Nov 9, 2017 03:34:15 – CLONE-26009   OHS T2P failed.

  [PLUGIN][OHS] – CAUSE – Nov 9, 2017 03:34:15 – CLONE-26009   Unable to start OS component.

  [PLUGIN][OHS] – ACTION – Nov 9, 2017 03:34:15 – CLONE-26009   Check clone log and error file and ohs log file /u01/oracle/PRD122/fs2/FMW_Home/webtier/instances/EBS_web_PRD12111_OHS1/diagnostics/logs/OHS/EBS_web_PRD12111/console~OHS~1.log for root cause.

 

READ  [Video] Oracle Apps DBA (R12.2) Installation : FREE Training

C) Error in OHS logs: 

Note: Clone process clones, Fusion Midddleware (WebLogic & OHS) as per the previous log, the error was during this startup of OHS server so we need to look at OHS logs.

/u01/oracle/PRD122/fs2/FMW_Home/webtier/instances/EBS_web_PRD12111_OHS1/diagnostics/logs/OHS/EBS_web_PRD12111/console~OHS~1.log

 /u01/oracle/PRD122/fs2/FMW_Home/webtier/ohs/bin/apachectl startssl: execing httpd

httpd.worker: bad user name applmgr11

3. Root Cause:

Root cause as identified in previous OHS log was, wrong owner permission for OHS files

Checked httpd.conf file and wherever it is having applmgr11,  changed to applmgr user permission

 cd /u01/oracle/PRD122/fs1/inst/apps/PRD12111_1705ecloud05/appl/admin/cutover/OHS/EBS_web_PRD12111

[root@1705ecloud05 EBS_web_PRD12111]# grep applmgr11 *

httpd.conf:        User applmgr11

[root@1705ecloud05 EBS_web_PRD12111]# chown applmgr:dba httpd.conf

[root@1705ecloud05 EBS_web_PRD12111]#

   User applmgr

        #Group GROUP_TEMPLATE

</IfModule>

4. Change in Context File

Note: After updating user to applmgr in httpd.conf, OHS start-up was still failing

So checked $CONTEXT_FILE and below two entries having applmgr11 user on both run (fs1) and patch (fs2) file system and causing the issue

<appsuser oa_var=”s_appsuser” osd=”unix”>applmgr11</appsuser>
<sysadminmail oa_var=”s_sysadmin_mail” osd=”unix”>applmgr11@1705ecloud05.k21academy.com</sysadminmail>
/u01/oracle/PRD122/fs1/inst/apps/PRD12111_1705ecloud05/appl/admin/PRD12111_1705ecloud05.xml

Note: $CONTEXT_FILE, captured all configuration parameters. In Oracle R12, we are having two context file:

Application context file called the <CONTEXT_NAME>.xml contains the configuration information for the application tier & is located in $APPl_TOP/admin for each node of application tier.

Database context file called the <CONTEXT_NAME>.xml contains the configuration information for the database tier & is located in $ORACLE_HOME/appsutil/

Fix:

1. Changed user to applmgr on both run and patch file system under $CONTEXT_FILE and run autoconfig, then restart prepare phase again

<appsuser oa_var=”s_appsuser” osd=”unix”>applmgr</appsuser>
<sysadminmail oa_var=”s_sysadmin_mail” osd=”unix”>applmgr@1705ecloud05.k21academy.com</sysadminmail>
/u01/oracle/PRD122/fs1/inst/apps/PRD12111_1705ecloud05/appl/admin/PRD12111_1705ecloud05.xml

Run Prepare Phase Again:

adop phase=prepare restart=yes

The Node Manager is already shutdown

NodeManager log is located at /u01/oracle/PRD122/fs2/FMW_Home/wlserver_10.3/common/nodemanager/nmHome1

adnodemgrctl.sh: exiting with status 2

adnodemgrctl.sh: check the logfile /u01/oracle/PRD122/fs2/inst/apps/PRD12111_1705ecloud05/logs/appl/admin/log/adnodemgrctl.txt for more information …

The prepare phase completed successfully.

adop exiting with status = 0 (Success)

Checked ADOP Status 

adop -status

ADOP (C.Delta.8)
Session Id: 6
Command: status
Output: /u01/oracle/PRD122/fs_ne/EBSapps/log/adop/6/20171109_085910/adzdshowstatus.out
===============================================================
Node Name       Node Type  Phase           Status          Started             Finished      Elapsed
————— ———- ————— ————— ——————– ——————– ————
1705ecloud05    master     PREPARE         COMPLETED       2017/11/06 22:41:13 2017/11/09 08:47:02  58:05:49
APPLY           NOT STARTED
FINALIZE        NOT STARTED
CUTOVER         NOT STARTED
CLEANUP         NOT STARTED

File System Synchronization Type: Light

adop exiting with status = 0 (Success)

In this scenario all you need to do is apply the pre-requisite patch first.

1)
Adop phase=apply patch=abcdef

Then once this is done continue with the patch you were applying earlier.
Adop phase=apply patches=11111,22222,33333,44444,55555

Note: There is another way to do it if there were no patches appled prior to to the above patches in this patching cycle you could also abort the patching cycle and start fresh patching cycle
2)
Adop phase=abort,cleanup
adop phase=prepare
Then continue with above mentioned prerequisite patch ( abcdef) and the respective patch later (11111,22222,33333,44444,55555)
But in this scenario the second mentioned is not required.

EBS login analyzer

EBS login analyzer

E-Business Suite Applications Login Analyzer (Doc ID 2319360.1)

 perl checkLogin.pl

  Enter the "APPS" password:

  Enter the "APPLSYSPUB" password:

  Prompt: Enter the Application User Name

  If you experiencing a login issue for a single, *specific* E-Business

  Suite User Account, please enter that user account name here.

  Otherwise press [Enter] if the issue affects all users.