EBS -- Using Python to catch and terminate runaway forms runtime processes / Runaway Forms Runtime Handler

 Whenever I need to develop some tools to ease dev ops or administration, I usually use perl and some bash scripts to do the job.. Sometimes, when I need to go a little more deep, I use C and when I need some GUI in the client-server mode, I use.. (when I need to write a TUI, I use dialog uility in Linux as well).. It's been a long time since I used Python. As far as I remember, the last time I used python was in my senior year at university.

So, today I wanted to make a change and wrote a python program to detect runaway forms processes (frmweb processes that don't have a database session but  have high cumulative cpu time) and terminate them.. 

Actually, I could almost do the same thing with 5-10 lines of perl, python or bash code. But! My goal was to actually prepare something to help friends in the oracle world who are thinking of programming a system and have an eye on Python. So, I wrote this program a little longer and in tutorial mode. In this context, I tried to use as many features as possible. ( functions, logging, 2d array-like objects, iterations on array, oracle db connection, environment variable handling inside the code and so on)

Reports bugs to -> rman.arslan@gmail.com :)

I shared the code/script in the next paragraphs of this arcticle (Under the heading of RUNAWAY FORMS RUNTIME HANDLER), but first let's see an example output that the code generates when we execute it.. Note that, you also see the logfile that the code produces below;

We have some features and I listed there here -> 

• Handle LD_LIBRARY_PATH environment variable
• Oracle DB Connectivity 
• Get Apps password as an input without showing it in the command line.
• Batch and interactive modes.
• Connect to DB once and use that connection while iterating our 2d array.
• Identifying runaway forms processes (having a process id, but not having a db session & having a high cumulative cpu time. >2h
• Parsing ps output, storing in an 2d array. 
• Logging -- date + findings/actions

RUNAWAY FORMS RUNTIME HANDLER:

#!/usr/bin/python

#First things first; we tell our shell -> use python to interpret and run this script.. This way, we don't need to write pyhton in front of the script name --  everytime we execute this script

#usage : ./runaway_frmweb_handler.py

#we run the script with the forms server OS user / EBS application owner OS user.. ex: applmgr

"""

There may be some cases, where we have forms processes left over and running for a long time without doing anything.

In most of the  cases, we see them spinning on the CPU and we identify them with their huge CPU usage -- time.

This script is used to identify and kill those types of forms processers in order to decrease the unncessary load on the application servers.

Tested on EBS R12 , but it needs to be tested more..

Script Creation Date : 24.01.2021

Last Modification Date : 27.01.2021

Lang: python

Author: Erman Arslan

Features:

Handle LD_LIBRARY_PATH environment variable

Oracle DB Connectivity 

Get Apps password as an input without showing it in the command line.

Batch and interactive modes.

Connect to DB once and use that connection while iterating our 2d array.

Identifying runaway forms processes (having a process id, but not having a db session & having a high cumulative cpu time. >2h

Parsing ps output, storing in an 2d array. 

Logging -- date + findings/actions

"""

######################################################

############## IMPORTING MODULES ##################

######################################################

"""

We import the modules that we need use in our code. import command is similar to #include in C/C++ ..

We could also have imported the required objects only.. (rather than importing the whole module) - Using from <module_name> import <names>

Note that, we also import numpy. Numpy provides a high-performance multidimensional array object, and tools for working with these arrays.

In order to have numpy in our server, we installed pip and then using pip, we installed numpy.

By using the as keyword, we give numpy an alternate name and we use that alternate name in our code.(just to make easier for us to write the name, we use np as an alternate way here..

 

curl https://bootstrap.pypa.io/get-pip.py -o get-pip.py

python get-pip.py

pip install numpy

"""

import subprocess

import os

import sys

import cx_Oracle

import numpy as np

from datetime import datetime

from datetime import date

import logging

import getpass

####note that, we set the LD_LIBRARY_PATH to the instant client directory. We handle it.. We set it to the directoy where oracle client libraries reside.

if ('LD_LIBRARY_PATH' not in os.environ or '/oracle_client/instantclient_12_1' not in os.environ['LD_LIBRARY_PATH']):

   os.environ['LD_LIBRARY_PATH'] = '/oracle_client/instantclient_12_1'

   try:

    os.execv(sys.argv[0], sys.argv)

   except Exception, exc:

    print 'Failed re-exec:', exc

    sys.exit(1)

"""

We install cx_Oracle module, as well.. pip install cx_Oracle==7.3

for EBS 12.1, we install 64 bit oracle client, as we have 32 bit oracle homes in apps nodes..  If we dont have 64 bit oracle client in place,  get the following error:

cx_Oracle.DatabaseError: DPI-1047: Cannot locate a 64-bit Oracle Client library: "libclntsh.so: wrong ELF class: ELFCLASS32". See https://oracle.github.io/odpi/doc/installation.html#linux for help

instantclient-basic-linux.x64-12.1.0.2.0.zip

[root@ebstestdb oracle_client]# unzip instantclient-basic-linux.x64-12.1.0.2.0.zip

Archive:  instantclient-basic-linux.x64-12.1.0.2.0.zip

  inflating: instantclient_12_1/adrci

  inflating: instantclient_12_1/BASIC_README

  inflating: instantclient_12_1/genezi

  inflating: instantclient_12_1/libclntshcore.so.12.1

  inflating: instantclient_12_1/libclntsh.so.12.1

  inflating: instantclient_12_1/libipc1.so

  inflating: instantclient_12_1/libmql1.so

  inflating: instantclient_12_1/libnnz12.so

  inflating: instantclient_12_1/libocci.so.12.1

  inflating: instantclient_12_1/libociei.so

  inflating: instantclient_12_1/libocijdbc12.so

  inflating: instantclient_12_1/libons.so

  inflating: instantclient_12_1/liboramysql12.so

  inflating: instantclient_12_1/ojdbc6.jar

  inflating: instantclient_12_1/ojdbc7.jar

  inflating: instantclient_12_1/uidrvci

  inflating: instantclient_12_1/xstreams.jar

We also soft link the library cd /oracle_client/instantclient_12_1; ln -s libclntsh.so.12.1 libclntsh.so

Note that, we already imported the cx_Oracle module above..

"""

######################################################

############## FUNCTION DEFINITIONS ################

######################################################

# We define our kill, db_conn and a db_check functions just to use some functions in python :)

# Note that, we create a single connection and use that connection while iterating our 2d array..

def SIGKILL_func(forms_pid):

 kill_cmd='kill -9 ' + forms_pid

 os.system(kill_cmd)

def db_conn(apps_pass):

 EBS_tns = cx_Oracle.makedsn('ebstestdb', '1555', service_name='TEST') # if needed, place an 'r' before any parameter in order to address special characters such as '\'.

 global conn

 conn = cx_Oracle.connect(user=r'APPS', password=apps_pass, dsn=EBS_tns)

def db_check(forms_db_pid):

 process_check_query = "select PROCESS from v$session where PROCESS=",forms_db_pid

 process_check_query = ''.join(process_check_query)

 c = conn.cursor()

 c.execute(process_check_query)

 c.fetchone() #we try the fetch one record to populate the c.rowcount properly..

 if (c.rowcount == 0):

  print "This process has no db session, so it is ok to be killed"

  logging.info('This process has no db session, so it is ok to be killed')

  return "killable"

 else:

  c.execute(process_check_query)

  for row in c:

   if(row[0]==forms_db_pid):

    logging.info('This process has db session, so we should not kill it.')

    print "This process has db session, so we should not kill it."

    return "Not killable"

   else:

    logging.info('This is weird')

    print "This is weird.."

    return "Not killable"

######################################################

############## WE START HERE  #######################

######################################################

#We first check our command line arguments and exit if we don't like the command line..

if (len(sys.argv) > 2 ):

 print "Wrong argument given..\n Usage : runaway_frmweb_handler.py or runaway_frmweb_handler.py batch"

 quit()

elif ( len(sys.argv) == 2 ):

  if (sys.argv[1]!="batch"):

   print "Wrong argument given..\n Usage : runaway_frmweb_handler.py or runaway_frmweb_handler.py batch"

   quit()

  else:

   print "Running in batch mode."

else:

 print "Running in interactive mode."

#We start logging to file here... We will log our findings and actions in /tmp/runaway_frmweb_handler.log.

import logging

logging.basicConfig(filename='/tmp/runaway_frmweb_handler.log', filemode='w',encoding='utf-8',format='%(asctime)s %(message)s', datefmt='%m/%d/%Y %I:%M:%S %p', level=logging.INFO)

logging.info('Script started')

#We also get our apps password here. We get apps password  without displaying it in the shell..

apps_password= getpass.getpass("Enter your APPS password: ")

#We connect to the database

db_conn(apps_password)

"""

We build our command to check Linux process, which have high cumulative cpu time..

Cumulative CPU time, "[DD-]hh:mm:ss" format. (alias time).

That cumulative CPU time we get from command corresponds to the TIME+ value that we see in top command output..

Ofcouse we get process ids as well.. We use process ids to kill those runaway processes..

Note that, we execute our command using subprocess call, we get the output and we manipulate the output array with numpy.reshape.

"""

cmd = ['ps -eo pid,cputime,euser,ucmd | grep `whoami` |grep -v grep  | grep frmweb | awk {\'print $1" "$2\'}']

proc = subprocess.Popen(cmd, stdout=subprocess.PIPE, stderr=subprocess.PIPE, shell=True)

process_to_kill_counter=0

o,e = proc.communicate()

ps_array=o.decode('ascii')

ps_array=ps_array.split()

import numpy as np

ps_array_2d = np.reshape(ps_array, (len(ps_array)/2, 2))

array_length=len(ps_array)/2

"""

We check our array and get the process ids of the runaway forms processes.. -if there are any...

We ask if it is okay to  kill those processes that we identify.

That is it.

"""

"""

Note that, if we see 4th digit, I mean if we see a value in the day files of the cumulative cpu time, then we directly consider that process runaway.

If we don't have a 4th digit, we check -> if ( cputime_hour_count>1 ), and decide.. 

We treat a forms process with a cumulative CPU time of more than 2 hours as a runaway. We still get user's confirmation before doing anything..

"""

if (array_length <= 0):

 print "There are no forms processes running"

 logging.info('There are no forms process running, I quit.')

 quit()

for x in range(array_length):

 process_id = ps_array_2d[x][0]

 try:

  cputime = datetime.strptime(ps_array_2d[x][1], '%d-%H:%M:%S')

  print "Found a runaway process with a cumulative cpu time > 1 day ->", process_id

  logging.info('Found a runaway process with a cumulative cpu time > 1 day -> %s', process_id)

  if (db_check(process_id)=="killable"):

   process_to_kill_counter += 1

   if (len(sys.argv) != 2):  # we already control the cmd line arguments in the beginning, so if we are here then it means all arguments are correct, so it is sufficient to check the length..

    answer_input= raw_input("Do you want me to kill it? Y or N :")

    if (answer_input=="Y"):

      logging.info ('Approved! Killing Process id: %s',process_id)

      print "Killing Process id : ", process_id

      SIGKILL_func(process_id)

    else :

      logging.info('Disapproved! I will leave it running')

      print "Okay.. I will leave it running"

   else :

    logging.info ('I m in batch mode, so approved! Killing Process id: %s',process_id)

    print "Killing Process id : ", process_id

    SIGKILL_func(process_id)

 except ValueError:

  cputime = datetime.strptime(ps_array_2d[x][1], '%H:%M:%S')

  cputime_hour_count=cputime.hour

  if ( cputime_hour_count>1 ):

   print "Found a runaway process with a cumulative cpu time > 2h ->", process_id

   logging.info('Found a runaway process with a cumulative cpu time > 2h -> %s',process_id)

   if (db_check(process_id)=="killable"):

    process_to_kill_counter += 1

    if (len(sys.argv) != 2): # we already control the cmd line arguments in the beginning, so if we are here then it means all arguments are correct, so it is sufficient to check the length.. 

     answer_input= raw_input("Do you want me to kill it? Y or N :")

     if (answer_input=="Y"):

      logging.info('Approved! Killing Process id : %s', process_id)

      print "Killing Process id : ", process_id

      SIGKILL_func(process_id)

     else :

      logging.info('Disapproved! I will leave it running')

      print "Okay.. I will leave it running"

    else :

     logging.info ('I m in batch mode, so approved! Killing Process id: %s',process_id)

     print "Killing Process id : ", process_id

     SIGKILL_func(process_id)

if ( process_to_kill_counter==0 ):

 logging.info('No runaway form processes to kill')

 print "No runaway form processes to kill"

conn.close() #we close our database connection at the end

RDBMS-- Parsing the listener log with a straight forward in-database method / by taking that jdbc_url anomaly into consideration :)

Remember; years ago, I shared a method for reading the listener log files in a scriptized way.. This type of a method can be used during security checks, or during a migration project (to see the connections, to know by which applications the connections to the database are made..) 

I want to remind you again; in migration projects, this type of an analysis makes our job easier especially when bundling .(when deciding the migration bundles)

You can review that blog post -> https://ermanarslan.blogspot.com/2016/03/rdbms-listener-logs-jdbc-parsing.html

Today, I give you another method for reading and parsing the listener log files. This one is purely an in-database method, and it is pretty straight forward.

I want to mention that, we handle the listener log lines which include jdbc_url correctly.. As you may already know; HOST parameter in the CONNECT_STRING shows __jdbc__ when the client connects to the database using the Jdbc thin driver. So, in order to determine the the real host name of these jdbc clients, we should use the info recorded in the PROTOCOL_INFO.

Let's see;

First, we create a database directory to reach the listener trace/log file from the database..

create or replace directory LISTENER_LOG as '/u01/dbebs/PROD/db/tech_st/11.2.0/admin/PROD_ebstestdb/diag/tnslsnr/ebstestdb/prod/trace';

We create an external table to read the listener log file using the database directory we just created. (we use our listener log file name in the location caluse..)

create table listener_log_ea ( line varchar2(4000))organization external (type oracle_loader default directory LISTENER_LOG access parameters (records delimited by newline nobadfile nologfile nodiscardfile fields ldrtrim missing field values are null reject rows with all null fields (line char (40000)))location ('prod.log'))reject limit unlimited;

 Lastly we query the external table by taking that jdbc anomaly into consideration :)

with details as
(
select last_value(tstamp ignore nulls) over ( order by tstamp ) as tstamp,
substr(host,1,instr(host,')')-1) host,
substr("USER",1,instr("USER",')')-1) "USER"
from
( select
case when line like '__-___-____ __:__:__ %' then to_date(substr(line,1,20),'DD-MON-YYYY HH24:MI:SS') end tstamp,
case when line like '%HOST=__jdbc__%' then substr(line,instr(line,'(ADDRESS=(PROTOCOL=tcp)(HOST=')+29) else
case when line like '%HOST=%' then substr(line,instr(line,'HOST=')+5) end
end host,
case when line like '%USER=%' then substr(line,instr(line,'USER=')+5) end "USER"
from listener_log_ea
)
)
select *
from details where host is not null

That's it.. That "with" query can be modified according to the needs and the characteristics of the environment.. After all, you got the point..

RDBMS -- DBMS_IJOB.CHANGE_ENV - Change Log_user, Priv_user, Schema_user and NLS_ENV of a DBMS_JOB -- without the need to recreate.

Here is a quick tip for the ones using DBMS_JOB.

Using DBMS_IJOB (undocumented), we can manipulate dbms jobs.

Ofcouse, DBMS_JOB is the best known interface for manipulating jobs, but DBMS_IJOB let us even change the LOG_USER ,PRIV_USER and SCHEMA_USER of a dbms job. In this way, we don't need to recreate the job for such a change. 

Here is a demo;

SQL> DECLARE
X NUMBER;
BEGIN
begin
SYS.DBMS_JOB.SUBMIT
(
job => X
,what => 'null;'
,next_date => to_date('01.27.2021 16:59:35','mm/dd/yyyy hh24:mi:ss')
,interval => 'TRUNC(SYSDATE+1)'
,no_parse => FALSE);
exception
when others then
begin
raise;
end;
end;
END;
/
PL/SQL procedure successfully completed.

SQL> select job,log_user,priv_user,schema_user,interval from dba_jobs where what='null;';

JOB  LOG_USER   PRIV_USER   SCHEMA_USER     INTERVAL
286         SYS              SYS                   SYS               TRUNC(SYSDATE+1)

SQL> begin
for j in (select * from dba_jobs where job = 286)
loop
dbms_ijob.change_env(j.job, 'APPS', 'APPS', 'APPS', j.nls_env);
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.

SQL> select job,log_user,priv_user,schema_user,interval from dba_jobs where what='null;'

JOB  LOG_USER   PRIV_USER   SCHEMA_USER     INTERVAL
286         APPS            APPS                  APPS               TRUNC(SYSDATE+1)

EBS 12.2 19C DB environments -- Suggestions for some issues (including performance related ones) / based on a real life story

In my previous blog posts, I made some suggestions for dealing with problematic discoverer reports, which may have performance issues especially after 19C database upgrades. In fact, we recently worked in a production EBS environment where there were some serious performance problems.(performance problem after the upgrade.) 

I was like a post-upgrade consultant in that project and with the intense work we have done, we have made the environment work with acceptable performance in a short time.

I continue to share some of the problems we encountered in this type of a consultant work and the solutions or workarounds that have been implemented (in a short time and in pressure) to make the system acceptable in terms of functionality, continuity and performance .

Today I want to touch on 3 specific issues .. (which I also posted on my twitter account recently)

*Issue : We also saw occasional instance terminations in EBS 12.2 19C environments. Espically under high load.. Solution -> Patch 29838337: XDB STRESS TEST - HIT ORA 600 [KJBLREPLAY: DUP] (latest RU can also be considered)

*Issue: We saw continious updates on AR_TRX_BAL_SUMMAR_MAIN_SUM ... This updates were increase the load of the database nodes dramatically.. (although, the database was running on Exadata)

UPDATE AR_TRX_BAL_SUMMARY MAIN_SUM SET RECEIPTS_AT_RISK ...
Those looped updates place a serious load on the system.

So here is the recommendation; If you don't use Credit Management and Advanced Collections applications, these updates may be disabled.. See MOS note - 2445550.1

*Issue : Reverse Journals were taking a lot of time to complete..This was related with Reverse Journals/GLPREV - performance and we created a custom index on GL_JE_HEADERS for that..

Index ON GL_JE_HEADERS (ACCRUAL_REV_JE_HEADER_ID)

We have seen the benefit... Index on (ACCRUAL_REV_PERIOD_NAME, LEDGER_ID) -> can also increase the perf. of automatic reversal.. (also see Patch 29464680) 

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

Below, I  share the solution methods for different problems experienced in this environment, as well.

*In case you need to manage the database load manually ; read the following blog post -> 

http://ermanarslan.blogspot.com/2021/01/ebs-122-configuring-application.html

*Read the following blog post for Discoverer - related quick win that we have implemented in this environment-> 

http://ermanarslan.blogspot.com/2020/12/ebs-122-rdbms-optimizerfeaturesenable.html

That 's it for today :)  I hope, this will be helpful to you.

Erman Arslan's Oracle Forum -- December 1, 2020 - January 6, 2021 - "Questions and Answers Series"

Question: How much time do you spend/lose?

Answer: Well, how much time I gain? :) 

Remember, you can ask questions and get remote support using my forum.
Just click on the link named "Erman Arslan's Oracle Forum is available now.
Click here to ask a question", which is available on the main page of Erman Arslan's Oracle Blog -- or just use the direct link: 

https://ermanarslan.blogspot.com/p/forum.html 

30 Issues, 125 Replies and 785 thread views this month :)

Come on, let's see what we've been up to this month. (Do not forget to read the blog posts too :)

Erman Arslan's Oracle Forum - Issues this month:

adapcctl-sh exiting with status 204 in Ebs 12.2 env.

Error Instantiating the OHS Config. Executed in 117109 milliseconds, returning status 1

Post EBS 12.2.9 Upgrade, cutover, oafm -- manager not starting

Question on : guaranteed restore point for cutover /ADOP

Custom TOP & formservlet.ini

XMLTYPE/CLOB and BLOB datatypes - how to replicate these fields from Oracle to Kudu?

DB link access from Oracle to MSSQL

Another question about replicating data from Oracle to Kudu

EBS Business Continuity - synchronize application files between primary and standby site.

Enterprise Manager Hangs After Log In

Workflow Mailer conf.- Failure of server APACHE bridge.

Workflow Mailer - insufficient free space following gc

About Profile options - modification and etc

EBS Solaris to Oracle Exadata + 19C in single step , Steps for DB migration to Exadata.

Can't access conc output files with different responsibilities

EBS 12.1 RAC Migration / Türkçe

Fatal SSL Error - Bug 26040483 UTL_HTTP call to https site fails ORA-28750

EBS 12.2 - how to modify ssl.conf

Goldengate - OGG-06439 & OGG-00918

adpreclone - Large FMW_Home.jar file

Actualize_all is stuck and not proceeding

EBS Customers -- Microsoft's plan to drop support for Transport Layer Security (TLS) protocols 1.0 and 1.1 in its browsers at the end of 2020. Does it really mean that we can no more connect to it,after first of january 2021?

Question on Virtualization- Vmware, KVM, OVM

While proceeding with addnode we got error mentioning CORBA

Weblogic Patch on R12.2 and FS_CLONE

Correct method for applying Weblogic Patches on R12.2

Question / case about RAC node eviction

not able to import the certificates to the wallet

SSL/TLS in multi node EBS with DMZ

EBS 12.2 -- Configuring Application connections manually ( including jdbc connections) - on RAC / on Exadata

In some cases, we may want to configure application connections manually.. As you may already know, EBS 12.2 configures its Forms&Reports, Concurrent processing and web (HTTP Server) connections through the tnsnames.ora file which resides in the Application nodes.. ( tnsnames.ora file located in the directory pointed by $TNS_ADMIN  -- in application nodes)

These TNS configurations are all autoconfig-managed and they are tide to the autoconfig variables named s_tools_twotask, s_cp_twotask and s_weboh_twotask..

In addition to that, the configuration for all the JDBC connections of EBS 's Apps Tier is also automatic managed and it is configured through the s_apps_jdbc_connect_descriptor.

Normally, when we run autoconfig, or when we install the apps with an Oracle RAC database configuration, then we will end up with a connection configuration which is based on scan listeners.. This configuration supports both load balance and fail over..  

This seems good as it leverages the RAC and SCAN-based architecture.. It provides load balancing and failover for databases connections.

However; as I mentioned in the beginning, we may want to change that.. Especially when we have an unbalanced environment where we have a node which is more crowded and loaded than the others.. (this may be caused by several reasons, like manual tns configuration for Discoverer clients or 3rd party applications that are configured to connect to only one of the db nodes..)

So in such a case, we may want to configure all the EBS apps tier connections and make the application tier services connect only a single node (least crowded one). 

In order to make such a configuration, we update the application tier context file and set twotask autoconfig variables to the failover TNS entries (TNS entries which have _FO suffix in our case) present in tnsnames.ora file of the Application node/or nodes

We also set the s_apps_jdbc_connect_descriptor according to our needs. Ofcourse we don't forget to set s_jdbc_connect_descriptor_generation to FALSE, as well.. 

Note that, s_jdbc_connect_descriptor_generation setting is very important.. I mean, if we set it or leave it as is (I mean if it is set to TRUE), then the apps tier autoconfig will overwrite the jdbc url with the scan-based & load balanced one... So if it iset to true, autoconfig will revert the change that we do for the  jdbc url.. Note that, jdbc url is used by all the jdbc connections in EBS + dbc file is also created with that url..

Following are the twotask and jdbc url related settings that I made in environment where customer wanted me to make all the services (including Concurrent Processing, Forms and Weblogic) connect to a RAC Node.. (Exadata Node 1 in this case). As the TNS Entries and jdbc url settings are based on failover mode (not the load balance mode), the services will always connect that RAC Node (Exadata Node 1), unless that RAC Node is down. So this supports failover a well.

Note that, this is tested in an EBS 12.2.9 environment with an Oracle 19C Database ( running on Exadata Cloud at Customer)

<TWO_TASK oa_var="s_tools_twotask" osd="unix">PROD_FO</TWO_TASK>

<CP_TWOTASK oa_var="s_cp_twotask">PROD_FO</CP_TWOTASK>

<TWO_TASK oa_var="s_weboh_twotask" osd="unix">PROD_FO</TWO_TASK>

<jdbc_url oa_var="s_apps_jdbc_connect_descriptor">jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=no)(FAILOVER=yes)(ADDRESS=(PROTOCOL=TCP)(HOST=exadata01-vip)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ebs_PROD))(ADDRESS=(PROTOCOL=TCP)(HOST=exadata02-vip)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ebs_PROD)))</jdbc_url>

<jdbc_url_generation_check oa_var="s_jdbc_connect_descriptor_generation">false</jdbc_url_generation_check>

After those settings, we run autoconfig on apps Tier and that's it! (ofcourse, we need to shutdown the apps tier before running autoconfig)

One more thing, you can check your jdbc url and ensure its correct before writing it to the s_apps_jdbc_connect_descriptor.. I know, there are many ways to do that.. But! You can also use SQLPLUS to do this job... Easy and clean :)

Here is an example for the syntax:

sqlplus 'apps/apps_password@(DESCRIPTION=(LOAD_BALANCE=no)(FAILOVER=yes)(ADDRESS=(PROTOCOL=TCP)(HOST=exadata01-vip)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ebs_PROD))(ADDRESS=(PROTOCOL=TCP)(HOST=exadata02)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ebs_PROD)))'

I hope it was useful. Take care of yourselves :)

EBS R12 / RDBMS -- optimizer_features_enable setting for discoverer desktop sessions "only".

Discoverer is not certified with Oracle Database 19C.. So, EBS 19C upgrade customer should keep that in mind.. Oracle Support says, "Discoverer will not be tested or certified on Oracle Database 19C". Besides, Discoverer moved to Sustaining Support on Dec 2014. So, Business Intelligence Enterprise Edition or Oracle Analytics Cloud is the recommended reporting platform now..

Anways, we have tested discoverer with 19C in a couple of EBS environment and I can say that Discoverer is working with 19C. But! It may have severe performance problems..

So, EBS 19C Upgrade customers should test their discoverer workload carefully.  If they can't fix their performance problem quickly, we may have a workaround. 

A trigger like below may save their day. Trigger below will set the optimizer_features_enable parameter to the old version (in this case 11.2.0.4). This move can be a try and may be a quick win in some cases.. Ofcourse this is a short-term fix.. I mean, even if it saves the day for certain cases, the real cause behind those slow running queries must be found.

Note that, with only some minor updates, the trigger can be changed to make the same settings for other programs as well.

Optimizer_features_enable parameter -> https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/OPTIMIZER_FEATURES_ENABLE.html

--11.2.0.4 optimizer_features_enable setting for discoverer desktop sessions "only".--

Note that, this is like a customization and it is your choice to take the risk.

CREATE OR REPLACE TRIGGER SYSTEM.set_optimizer_parameter_disco
AFTER LOGON
ON DATABASE when (user in ('APPS'))
DECLARE
v_program v$session.program%TYPE;
CURSOR user_prog
IS
SELECT program
FROM v$session
WHERE audsid = SYS_CONTEXT ('USERENV', 'SESSIONID');
BEGIN
OPEN user_prog;
FETCH user_prog
INTO v_program;
CLOSE user_prog;
IF LOWER (v_program) LIKE ('%dis51%')
THEN
EXECUTE IMMEDIATE 'alter session set optimizer_features_enable="11.2.0.4"';
END IF;
END;
/

The following underscore paramaters may also help.. (in our case, they helped a lot..)

_optimizer_mjc_enabled=false

_optimizer_cartesian_enabled=false

So in our case, we included the parameters (above) to our Disco trigger as well..

EXECUTE IMMEDIATE 'alter session set "_optimizer_mjc_enabled"=false' ;

EXECUTE IMMEDIATE 'alter session set "_optimizer_cartesian_enabled"=false' ;

What about the scheduled Disco reports?

Well, we needed to modify the EUL5_BATCH_USER package to make the scheduled Disco reports get our alter session settings. 

We just added our alter session settings to the EUL5_BATCH_USER ( into the PROCEDURE ExecuteQuery) and that worked. 

Note that, you need to be careful while modifying the EUL package, or creating a Disco trigger.. (you need to get downtime for this -- just in case..) 

You need to test your environment well..

These moves are unsupported.. So the risk is yours.. But! In our case they saved our day!

EBS R12 - Workflow mailer ORA-00054 due to bug 18723483 / Lock issues in Approval Workflows

This blog post is about a lock issue encountered in EBS R12.  

The environment was an EBS 12.1.3, and workflow mailer was configured both for outbound and inbound. ( 1 process for outbound, 1 process for inbound.. The IMAP account used by the mailer was dedicated to it.. So no other environment was using that IMAP account other than the wf mailer of this problematic environment. Having dedicated IMAP accounts for each EBS environment is a must bytheway.)

In this post, we will see how workflow mailer can lock some records and prevent EBS forms operations from taking actions on those records..

Our story is about an approval workflow and the problem was escalated to me with the following decription : "Users are unable to give approval for certain requests. They get ORA-00054 while trying to approve those requests" .

First thing I checked wast the locks in the EBS database.. It could easily be seen that, we had some TX locks on WF_NOTIFICATIONS and WF_NOTIFICATIONS_OUT.. 

The Workflow mailer seemed the owner of the session that was holding the locks. But why? How could workflow mailer hold those locks for so long? Could there be a SMTP or IMAP problem, an unexpected an error in mailer's log? 

The answer was No. Everyting was clear and that made me revisit the dynamics of the worklow mailer..

--note that we had a lock on OUT queue, so probably the cause was related with the outbound..

So, here is the general process that is executed by the workflow mailer while sending an email;

WF Mailer dequeues a given notification from WF_NOTIFICATION_OUT queue. This places a lock on that message in WF_NOTIFICATION_OUT queue (1st LOCK here)

Next, it builds a MIME message for that notification and sends it as e-mail. (If it fails here(a fatal failure), the lock it got in the first step may not be released)

Then, it locks that given notification in WF_NOTIFICATIONS table. updates the STATUS and MAIL_STATUS columns. (2nd LOCK here..)

Finally, GSC layer issues a commit (COMMIT is here)

Well, this made me check 2 things;

*"processor close on read timeout" checkbox should be checked.

*WF background engine may cause those locks somehow.. So it is better to optimize it using the following recommendation;

-Run a background engine to handle only deferred activities every 5 to 60 minutes.

-Run a background engine to handle only timed out activities every 1 to 24 hours as needed.

-Run a background engine to handle only stuck processes once a week to once a month, when the     load on the system is low.

However; "processor close on read timeout" was already checked and background engine configuration was already optimized according to the recommendation.. 

It was the WF mailer that was causing this lock issue and it seemed that we were dealing with an undocumented behaviour.. So I checked the bugs..

Anyway let me come to the conclusion now.. 

Well, the cause was a bug.. Bug 18723483... Do not worry, the development is working on it :) 

Besides, we have a quick and easy workaround.. 

We just restart the workflow mailer and that's it.. Lock is released and we continue our work .. :)

That's it for today. Have a nice weekend.

Oracle Linux KVM & OLVM advantages, benefits, important information : hard partitioning, Hypervisor type, Intel Vt-x or Vt-d , OCI migrations and so on.

We talked about these subjects in our webinar last week. We talked about the advantages of using Oracle Linux KVM and the real life stories based on our Oracle Linux KVM implementations.

Actually, I met KVM in 2017 during my ODA X6 implementations.. 

Remember that post -> 

ODA- KVM Virtualization for ODA X6-2S/X6-2M/X6-2L !!

I also wrote a blog post about the upcoming end date of OVM. 

Let's remember that as well ->

Upcoming End date of OVM Premier Support. It is time to consider KVM + OLVM (especially for the new projects)

Today, I will share some more insights with you .. 

As mentioned, the actual motivation of this blog post is the benefits that we have gained using Oracle Linux KVM and OLVM in our projects.

Let's start and go over the important information about Oracle Linux KVM .. (I will also try to give asnwers to some questions that may come to mind on the way..)

Oracle Linux KVM is the new virtualization techology of Oracle.

It is based on Oracle Linux and it is available through a kernel module ( kvm.ko)

KVM is the acronym for Kernel-Based virtual machine.. 

It is considered in the Type 1 category. Yes.. I know there is a confusion on this topic. We might say that KVM is not directly running on Bare Metal.. However; it is categorized as Type 1 because it is based on a Kernel module.. When we look from this perspective, KVM is running in kernel mode on bare metal and uses a hardware virtualizer. Besides, KVM guests are mostly running in direct execution mode.

Reference the following document (an old but good one) for other opinions on this topic; 

KVM reignites Type 1 vs. Type 2 hypervisor debate

So, KVM is a HW assisted full virtualization solution. (There are also paravirtualized virtualization drivers). It provides virtualization with qemu, a loadable kernel module and Linux. It speeds up the access to physical host .. (kvm.ko)

It requires Intel Vt-x (HW assisted virtualization) and it supports Intel Vt-d (PCI Passthrough).

KVM is an open source software. The kernel component of KVM has been included in mainline Linux from 2.6.20. The Userspace component is included in the mainline Qemu from 1.3 onwards. 

Let's make a quick overview about the features of KVM;

• Supports 32 and 64 bit guests. (On 64 bit hosts)
• Supports Full + Hardware Assisted Virtualization
• Supports paravirtualized drivers (virtio)
• Virtual Machine Snapshot feature available
• VM Live/online migration feature
• VM cloning feature
• Virtual machines can be set up with templates.
• Supports PCI passthrough.
• Supports Kernel samepage merging.
• It is very fast because it is a Type 1 virtualization (Like VMware and Hyper-V…)
• "Zero" License Cost (Open Source)

Let's take a quick look to the advantages of Oracle Linux KVM;

• Complete server virtualization and management solution with zero license costs
• Single software distribution for Oracle Linux OS or Oracle Linux KVM
• Single vendor Support. ( Oracle Linux support included Oracle KVM support). That isw we can create SRs about Oracle Linux KVM using Oracle Support!

• Virtual machine cloning feature and ready + customizable templates to speed up the development and provisioning processes ..
• Easy to implement and install.
• Easy to manage and configure using Oracle Linux Virtualization Manager and Enteprise Manager. ( strong GUI - OLVM)

• Provides ability to apply patches with Ksplice without service interruption. (Ksplice is used in Autonomous Linux in OCI as well..)
• Hard Partitioning support provides efficient Oracle application and database licensing. (CPU Pinning - using olvm-vmcontrol)
  
• Full Stack management with Oracle Enterprise Manager.
• It is the virtualization technology that is used in Oracle Cloud Infrastructure!
• Thick and thin provisioning.. (setting VM memory sizes in a way similar to what we do in the data layer sga_target and sga_max_size - style memory configurations for VMs)
• Easy migration option for migrating virtual machines from on-prem to Oracle Cloud Infrastructure. (using cloud-init - imex4vm!)

• Used in Oracle Enginereed Systems (so it is stable)
• Oracle Database is supported & certified to run on KVM !

What we have gained by using KVM as the virtualization solution in our Projects?

As mentioned, we have made projects using Oracle Linux KVM and currently we are running mission critical production RAC databases and applications on it.. We have also implemented clusters and DR platforms on top of it. So let's se what we have gained...

• Quick installation (Level 1 Linux admistration knowledge is almost sufficient)
• Quick provisioning
• Hardware Compatibility
• Good documentation for KVM and OLVM
• Single Vendor Support
• Easy memory management for guest VMs
• Successful disaster recovery implementations and tests.
• Easy to use and user friendly interface
• Virtualization with hard partitioning (aligned with the Licenses)
• Complete compatibility and ability to ease OCI migrations.

All in all, there are many reasons to use Oracle Linux KVM for the virtualization layer. On the other hand, I don't see any reason not to use it.(especially for Oracle customers..)

One more thing..

Actually an aswer for of the questions you may ask;

Do we support and recognize KVM as a license partitioning technology on Redhat Linux or is it only Oracle Linux with Oracle Linux Virtualisation Manager running?

Asnwer : Hard Partitioning is only supported with Oracle Linux KVM .. ( + we need to use the Oracle Linux Virtualization Manager - olvm-vmcontrol to enable the CPU pinning)

That's it.. Please feel free to ask your questions.. If you have questions, you know what do to right? :)

For your questions, please create an issue into my forum.

Forum Link: http://ermanarslan.blogspot.com.tr/p/forum.html

Register and create an issue in the related category. ( I just created a separate category for Oracle Linux KVM and OLVM)

I will support you from there.

KVM & OLVM -- Support on Erman Arslan's Oracle Forum - Ask me questions about KVM ! starting from today..

Do you need support for Oracle Linux KVM? Do you have questions?

I just created a separate category for Oracle Linux KVM and OLVM.

For your questions, please create an issue into my forum.

Forum Link: http://ermanarslan.blogspot.com.tr/p/forum.html

Register and create an issue in the related category. 

I will support you from there.