Tuesday, December 29, 2020

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!

Saturday, December 19, 2020

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.

Wednesday, December 9, 2020

Oracle Linux KVM Webinar - KVM, OLVM : CPU pinning, migration, failover, installation, management, advantages and all that

Webinarımızı kaçırmayın! Bu kez Oracle KVM için!

Oracle Linux KVM (Kernel-based Virtual Machine)
En güncel sanallaştırma teknolojisinin sağladığı faydaları Oracle ve GTech uzmanlarından dinlemek için webinarımıza davetlisiniz.
Veri tabanı ve orta katman alt yapınızda Oracle tarafından desteklenen sanallaştırma teknolojisinden yararlanın!



Don't miss our webinar!

This time for Oracle KVM.
 
Click here for registration.

"Note that, this webinar will be in Turkish."

Monday, December 7, 2020

Weblogic - App throwing ORA-01722: invalid number /conversion between client & server..

After migrating a custom web application from one Weblogic Server to another; we started to encounter ORA-01722 errors. (recorded in the Admin Server logs..) 

Note that, this custom app was deployed to the Admin Server.. Yeah , it should be deployed to a Managed Server but let's forget about that for now :)

ORA-01722 is an error which may be reported during the conversion of a character string to a number. Basically, it is reported when this conversion can not done correctly.

Database tier was the same, the data was the same (and correct), the code was the same, only the Weblogic tier was different...

This was probably related to some conversions between client (in this case -> Weblogic) and the database.. 

So, we implemented the following solution..
  • Checked the database character set..
  • Set the LANG environment variable (for the Weblogic) equal to the database character set..
With these moves, we actually made client (Weblogic) character set = server character set.. Thus, we ensured that there wouldn't be any conversions while data was flowing from db to the app..

Note that setting LANG also sets the NLS_NUMERIC_CHARACTERS indirectly as that parameter is derived from NLS_TERRITORY and the NLS_TERRITORY is also defined by the LANG itself.

Well, this error could also be caused by the wrong alignment between NLS_NUMERIC_CHARACTERS and the data..

For instance;

SQL> alter session set nls_numeric_characters = '.,';
Session altered.

SQL> select to_number( '10,10' ) from dual;
select to_number( '10,10' ) from dual
                  *
ERROR at line 1:
ORA-01722: invalid number
---

So setting the LANG was a good idea and it solved our issue.

How did we make Weblogic get our LANG setting ?

Well, we set it in the setDomainEnv.sh

In our case, we set "export LANG=tr_TR.ISO-8859-9" and restarted the Admin server..

startWeblogic.sh already calls setDomainEnv.sh for setting the related env variable..

That's it.. 

Friday, December 4, 2020

Weblogic -- Custom application deploy error - after WLS 12.2.1.3 upgrade / prefer-application-packages & prefer-application-resources

This blog post will be closely related to java development and deployment. It is based on a story about a Weblogic upgrade. Actually it is about a custom application that couldn't be to deployed to a Weblogic 12.2.1.3.

We had a mission critical custom application running on Weblogic 10.3.6, and we decided to upgrade this Weblogic 10.3.6 to 12.2.1.3.

The application was also using the BI Publisher.. So we had to upgrade the BI Publisher as well.

Here is quick note, BI Publisher 12.2.1.3 or 12.2.1.4 is not currently certified with Weblogic 12.2.1.4 and that's why we choosed Weblogic 12.2.1.3 as our target Weblogic release.

The upgrade was done out of place.. I mean we installed a fresh Weblogic 12.2.1.3 and BI Publisher 12.2.1.3 to a new server and then deployed our application to this new environment.

The problem was in the deployment.. I mean, we just couldn't deploy our custom application using the WAR file. When we tried to deploy it, the Weblogic console was throwing the following error;

java.lang.NoSuchMethodError: com.google.common.collect.MapMaker.makeComputingMap (Lcom/google/common/base/Function;)Ljava/util/concurrent/ConcurrentMap;

Well, when we analyzed the error, we saw that our code was trying to use makeComputingMap method of the MapMaker class, but the version of that class in Weblogic 12.2.1.3 was high and this makeComputingMap class was already deprecated and removed from the class.

The following was the statement we found in GUAVA Dev.

Deprecated. Caching functionality in MapMaker is being moved to CacheBuilder, with makeComputingMap(com.google.common.base.Function) being replaced by CacheBuilder.build(com.google.common.cache.CacheLoader). See the MapMaker Migration Guide for more details. This method is scheduled for deletion in February 2013.

This wasn't the only error actually, there were also different errors related with Java faces and so on..
So, we needed to change the code, but it wasn't so easy.
That's why we decided to find a workaround, rather than playing with the code.

I will keep it short and directly jump to the workaround we applied..

We needed to find a way to make our code use the old versions of those classes. However; we also needed to upgrade some of the classes to the versions supported by Weblogic 12.2.1.3.

So what we did was the following;

We imported some of the jar files (for instance com.google.common) into our WAR file and told Weblogic to use the jar files inside our WAR file rather than the jar files present in its own  library.
Note that, we can import (including jar files) into a WAR using Linux zip command.. Similary, we can also remove any files from a WAR using the zip command.


In order to make Weblogic to use the jar files ( the classes are inside these jar files), we imported related jar files into to WAR file and updated weblogic.xml (WEB-INF/weblogic.xml) We used wls:prefer-application-packages, wls:prefer-application-resources tags to tell Weblogic to use the jar files present in our WAR.  

Here is the related part of weblogic.xml:

 <wls:weblogic-version>12.2.1</wls:weblogic-version>
        <wls:context-root>ErmanWEB</wls:context-root>
        <wls:container-descriptor>
                <wls:prefer-application-packages>
                        <wls:package-name>com.google.common.*</wls:package-name>
                        <wls:package-name>javax.faces.*</wls:package-name>
                        <wls:package-name>com.sun.faces.*</wls:package-name>
                        <wls:package-name>com.bea.faces.*</wls:package-name>
                </wls:prefer-application-packages>
                <wls:prefer-application-resources>
                        <wls:package-name>com.google.common.*</wls:package-name>
                        <wls:resource-name>javax.faces.*</wls:resource-name>
                        <wls:resource-name>com.sun.faces.*</wls:resource-name>
                        <wls:resource-name>com.bea.faces.*</wls:resource-name>
                </wls:prefer-application-resources>
        </wls:container-descriptor>
</wls:weblogic-web-app>

Attention: we used prefer-application-resources as well.. (not oly prefer-application-packages)

As the names suggest, these instructions made Weblogic to prefer the classes inside the WAR in the first place..

Also, as I mentioned we updated some of the jar files, as the classes they had wasn't supported by Weblogic 12.2... (we updated those jar files, by importing the new jar files with all their dependencies and removing the old ones with all their dependencies.)

In our case, we also upgraded the Richfaces because the current version of the Richfaces jar inside our WAR file, wasn't supported by Oracle Weblogic 12.2.1.3.

Note that, Richfaces which is a component library for JavaServer Faces. So we downloaded the RichFaces 4.5 (with all its dependencies.. some other jars I mean.. ) and bundled with our application (using weblogic.xml), and used filtering classloader to ensure the correct version is used.. ( I mean we used zip command to import the jar into WAR and used prefer-application-packages and prefer-application-resources to ensure that our newly imported version is used.. Also removed the old RichFaces jar file with all its dependencies from the WAR file.)

After these changes, we redeloyed the application without any errors and certified it with Weblogic 12.2.1.3 without touching the code :)

Note that, code change may still be required, especially for the runtime.. But in our case, we didn't change almost anything in the code :)