Erman Arslan's Oracle Forum -- Questions and Answers Series - November 2020

Our support calls and questions we received this month were mainly related to EBS. Response times and update quality are getting better day by day. Besides, we definitely get feedback from our members about the solution of the problems and as a result, everyone learns. 

We are not always technical.. Sometimes, we also switch to advisory consultant mode :)  Especially, for the big tasks like upgrades and migrations..

Remember, we always start with the the following question and answer :) ;

PS, the following question and answer reveals what my motivation is...

Question: How much time do you spend/lose?

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

When we look at Top 100 Oracle Blogs, Erman Arslan's Oracle Blog has a very good ranking. Actually were are there since 2017.. But, it should be better. Actually, forum activity, the quality of the contents and the uniqueness of the articles are not counted for the ranking, but still it should be better.. 

Same for the forum... Yes, I am aware of the need for SEO and awareness raising activities. In this way, more information will be shared and more contribution will be made. Come on, let's see what we've been up to this month. (Do not forget to read the blog posts too :)

Question on EBS 19c database upgrade

Deadlock ORA-00060 problem - EBS

Question : Oracle Database Appliance - EBS preinstall

Unable to add certificates to the wallet

EBS 11.5.9 Compatability

Question : EBS 12.2.9 & 19C upgrade

Profile option for a user

EBS 12.2 fs_clone problem

EBS concurrent program aborted problem

RAC node eviction problem

EBS concurrent request errors

EBS 12.2 login page problem

Question on QR codes in EBS

OPMN port 600 not free

Question on DISPLAY Could not initialize class sun.awt.X11.XToolkit

adcfgclone env variable for patch env question

EBS add node failing

Dataguar broker rac to standalone

Workflow mailer OAF based notifications and SSL terminated-load-balancer-environment

OBIEE - Security Configurations & Custom SSO using a http Header or Cookie

You may remember that I shared a custom SSO solution for OBIEE login.. It was a login wrapper actually.. It was getting the login related info from a 3rd party client (for instance F5 load balancer) and posting all that login data to OBIEE. This was working perfectly. (actually still working properly :)

In this blog post, I will give you some more info about these kinds of implementations. I will give you what can be done using the Oracle documents instead of developing and placing a custom wrapper in front of OBIEE 12C..

Well, the following MOS document satisfies our needs actually.

"OBIEE 11g: Supported Security Options and Configurations (Doc ID 1489438.1)"

It is for OBIEE 11g, but the given link for OBIEE 11.1.1.9 pdf is also applicable to OBIEE 12C, since the security design didn't change since 11.1.1.9 (except lightWeightSSO). 

The 11.1.1.9 link in MOS note 1489438.1 direct us to the Oracle BI 11g Security Configurations document and in that document we find lots of ways to implement Oracle-documented SSO solutions for OBIEE.. The use cases are not only for a custom SSO, but for EBS, Siebel and PeopleSoft security integration as well..  Following use cases are all there - >

• Use Case – Security Integration with  E-Business Suite 
• Use Case – Users in LDAP and Group Membership in Database – FMW Security or Init Block for Authentication
• Use Case – Security Integration with Siebel 
• Use Case – Security Integration with PeopleSoft
• Use Case – BI Mobile
• Use Case – SmartView
• Use Case – Browser SSO integration between BIEE and Hyperion EPM
• Use Case – Authentication and Authorization integration between BIEE and Hyperion EPM 
• Use Case – Security Integration with an Essbase data source
• Use Case – Custom SSO using a http Header or Cookie 

As you may guess, what we need to do is just to think about what we need to do and then choose the right method to follow..

For instance suppose; we have a F5 load balancer in front of our OBIEE 12c. Our clients are using F5 to reach OBIEE. We want our clients to be authenticated from F5, so we want to have a SSO solution for OBIEE. F5 should authenticate the users through this SSO solution and make them reach the OBIEE Home page, without a need to supply OBIEE user and password again. 

For a such a case given above, we follow the instructions given under the title of "Use Case Custom SSO using a http Header or cooke Method 1"..  If we have also BI Mobile users, then we have to check the "Use Case – BI Mobile" as well..

Following additional recommendations and reminders should also be kept in mind while applying this method;

Make sure to have a header variable with username. 

Create an asserter with the same variable, so that the variable value will be used for username

Make sure to have username available under users and groups either on Default Authenticator OR on an external authenticator

Modify weblogic.ear if you want to do anymore principals accepted (weblogic.xml and web.xml modification)

That's it for today :) My next blog post about OBIEE will be about implementing a Custom Authentication Provider.. That tricky subject is currently in my focus, and I will tell you my story once I implement it.

OBIEE - Standalone HTTP Server configuration with OBIEE + Compression + Caching the response data

We have seen a performance issue in an mission critical OBIEE 12C environment. This environment was configured to reach the ODV through links and the elapsed time during the initial load of the OBIEE pages was not acceptable. The issue was not related with the databases or Application Servers. So it was a pure web issue. During the web traces, we found lots of files were being downloaded to the client side when a client tried to reach an OBIEE page. This was a problem for the clients which have a limited network and that's why we thought that it is better to have a seperate Oracle HTTP Server (caching and compression enabled) in front of the OBIEE... This way, we could be able decrease the bytes that are normally downloaded to the client when the client reach an OBIEE web page.

Besides, we usually see a seperate HTTP Server in a several Oracle Applications. E-Business Suite for instance; have an Oracle HTTP server in front of its core services. So why not in OBIEE?

This OBIEE in question was 12C and it was configured to work with SSL/HTTPS. So, our HTTP Server should also have been configured with SSL/HTTPS.

The decision was made. The work was planned but not yet done..

Still, I m writing this to make you see the plan..

Anyways, following is the plan that we follow while implementing this kind of a configuration.

-Download and Install Oracle HTTP Server -- This is straight forward, so I move on.

-Configure SSL/HTTPS for the Oracle HTTP Server -- We can configure the wallet using the jks of the OBIEE/Weblogic --  This article seems okay to follow for this job -> https://oracle-base.com/articles/12c/oracle-http-server-ohs-configure-ssl

-Configure Oracle HTTP Server to work with OBIEE. -> How To Configure Standalone Oracle HTTP Server (OHS) With OBIEE (Doc ID 2308798.1)

-Enable compression on the Oracle HTTP Server -- This MOS note written for Webcenter should do the job -> How to Add Compression on the Oracle HTTP Server to Reduce the Download Size of Webcenter Spaces Deployment (Doc ID 1219013.1)

- Enable caching on the Oracle HTTP Server using mod_cache and mod_cache_disk modules. -> >http://httpd.apache.org/docs/2.4/mod/mod_cache.html  .. Yes.. Oracle HTTP Server includes the Apache mod_cache and mod_cache_disk modules to cache response data.

That is it ! We hope that after taking these actions, clients will continue their business faster while browsing OBIEE pages..  Especially the clients with limited network connections .. After this action plan, they should continue on their way with more acceptable waits.( As long as  the SQLs on the DB side that work to fill the related pages keep up with this speed. :)

I will update this post, when we are done and everyting is as we expected :)

--Here is the update :) The configurationa above works like a charm. We have implemented it yesterday and it worked as  we expected.

I will update this blog post once again. - exactly after receiving the answer "yes now ODV and OBIEE pages are faster" from users :)

Weblogic - RAC node failure tests - Data Source / Attempt to set connection harvestable to false and/or Attempt to operate on Connection that is already closed.

Recently implemented a Weblogic Cluster and a multi node Oracle RAC Database in a mission critical project.

During the RAC node failure tests, we saw that the managed servers were getting stuck during these test.. Moreover; when we tested the the data sources on them, we got errors.

Actually, all the urls were configured properly. The datasources were configured to use SCAN and service names to connect to the Oracle RAC database.. However; the managed servers couldn't manage a RAC node failure.

Once, we checked the logs, we saw two crucial things -> 

java.io.IOException: Attempt to set connection harvestable to false but the connection is already closed.

java.sql.SQLException: Attempted operation on Connection that is already closed.

These exceptions seemed to blink at us.

It was obvious that, weblogic was harvesting the connections to ensure that a specified number of connections are always available in the pool.. Weblogic was doing it for improving performance by minimizing connection initialization, but it was still hoding the connections to the failing node, and the managed servers and our applications were getting errors while trying to use them.

There was no ONS or Gridlink configuration, so, we needed to tell the regular datasource/weblogic to not to use those connections and the quickest way for that was to set "Test Connections On Reserve" and "Test Frequency" parameters.

Information about these parameters:

Test Connections on Reserve : Enables WebLogic Server to test a connection before giving it to a client. (Requires that you specify a Test Table Name.)

The test adds a small delay in serving the client's request for a connection from the pool, but ensures that the client receives a viable connection.

This test is required for connection pools used in a multi data source that use the failover algorithm.

Test Frequency : The number of seconds between when WebLogic Server tests unused connections. (Requires that you specify a Test Table Name.) Connections that fail the test are closed and reopened to re-establish a valid physical connection. If the test fails again, the connection is closed.

When set to 0, periodic testing is disabled.

Those properties are configurable through Weblogic console.

These parameters were exactly under "Domain Structure > Services -> Data Sources > Data Source name > Configuration > Connection Pool > Advanced Options"

As for the solution; we set the "Test Connections On Reserve" to true and Test Frequency to an optimized value (in seconds) according to our environment.

Also, we choosed to use "SQL SELECT 1 FROM DUAL" , because of the following restrictions of "SQL ISVALID" ->

[ISVALID] can improve the performance; however it cannot check correctly in a few cases; When there is a problem in the parser, execution or other function (except connection) on the Database side or When Database is into a shutdown mode but not yet disconnecting.

Well, this is about failover, but what about WLS - DB load balancing? Well, Load balancing database connections really resides on the RAC DB setup :)

For your information ! :)

One more thing -> this was the quick win in this special case. Of course, the recommended configuration is actually the following;

-Configure ONS in RAC
-Convert those generic Data Source to Grid Link Data Sources
-Enable FAN and set Test Connections On Reserve to true in Weblogic.
-Select the "Remove Infected Connections Enabled" check box & Restart WLS.

EBS R12 -- Workflow Mailer DISCARD problem / actually IOS auto link (auto phone number recognition) problem

 Recently dealed with an interesting issue, which I want to share with you.

I was dealing with a workflow mailer problem.. Actually it was related with the inbound/IMAP..

The issue was occuring on some mobile users.. That is, they couldn't approve their notifications through their emails.

Actually, they were able to approve it, but the appropriate actions couldn't be taken in the system and the workflow notifications were staying in OPEN state.. It was obvious that, the workflow mailer wasn't able to perform its actions.

So, I set the loglevel to statement, reproduced the issue and checked the log..

The log was telling me the same generic story, that I don't want to dig in in this blog post, but I have written several blog posts about the workflow mailer and throubleshooting it. So if you want to get these kinds of details, please search by blog with the keywork "workflow"

Anyways, it was obvious that the problem was caused by a client issue, and that's why I decided to take a look at the mobile devices of the problematic users.. Those problematic users were all using Iphones and they got the latest updates.

They didn't any issue in approving/denying the notifications and sending the email responses. The users were trained and they were doing their jobs correctly.

However; when I checked the IMAP mail account. (IMAP mail account used by the Workflow mailer), I saw that those emails that were sent by these problematic users were sent to DISCARD folder.

Workflow mailer log was also verifying that in a little generic way..

The crucial thing was hidden in the emails.. I mean the emails that were sitting in the IMAP's DISCARD folder.. In the first glance, the format of those emails seemed correct. However; when we looked more closely at those emails sitting in the DISCARD folder of the IMAP account, we saw that; the NID numbers ( 7 digit numbers in our case) in those emails were converted into html links.

After analyzing further, we saw that, the mail transfer agent (actually the Mobile OS (IOS 1.4)) converts the NIDs in those approval emails to html links ( when the user approves a approval request (through email - imap) from its Mobile OS (IOS 14.1))

So IOS 14.1 in Iphone interprets 7 digit numbers as Mobile/Telephone numbers and converts those 7 digit numbers to html links. (it builds the links with the “tel:” prefix)

This was the reason behind this DISCARD behaviour.

We have analyzed the issue further (acutally there was no need to do that , but still:) by taking the actions given in the following MOS note; 

Oracle Workflow ATG Support: Java Mailer Setup Diagnostic Test (Doc ID 748421.1)

11i-12 Troubleshooting Workflow Java Notification Mailer - Technical Reference (Doc ID 1191125.1) 

At the end of the day, the issue was clear.

As for the solutions or workarounds, we thought about taken the following actions;

1) A customization in the mailer code.. (to make it ignore those a href attributes)

2) A patch for the workflow mailer.. (to ignore those a href attributes, to make Workflow mailer to ignore those “a href” tags and just read the NIDs intelligently)

3) This issue seemed to be caused by NIDs to be 7 digit numbers.. So if we could have 8 digit Workflow NIDs , the issue wouldn’t appear ).. So maybe we could increase the workflow NIDs in the system manually? For instance , currently the NID counter is at 3014900, we could increase this sequence manually to a 8 digit number (For instance to 10000000 and make workflow NIDs to continue from there)

4) Disablng this autolink feature in IOS..

Well, we have 4 solutions/workarounds, but none of them applied in this case.

We can't customize the mailer code, cause it is forbidden in the first place.

We can't apply a patch for workflow mailer , because there is no patch for this and the issue is actually not caused by Oracle.

We can't update the NID counter, because there is no documented way for doing that.. Actually, even if we can update it, this move will be an unsupported one.

We can't disable the autolink feature in IOS, cause there is no on/off switch for this feature -> https://discussions.apple.com/thread/8185290?answerId=32662540022

What do we do then?

Well , we can use some manual workarounds.. I mean we can teach the users to update the workflow emails before sending them back.. ( they can  just erase one digit and write that digit again..). Seems this works, as we don't get the a href attribute in the emails once we do this move .. However, this requires some manual actions and it is in the client side!

We can raise a SR to the Apple Support.. Actually, we will do that.. I will update you once we get response from Apple.. 

One other solution may be using Mobile Approvals Application..

This is the end of this interesting story :) I hope you find it useful..

EBS 12.1 -- Apps + DB Failover test / Instructions

In a mission ciritcal environment, we have at least one disaster site for EBS.  

In order to make it work, we use dataguard to replicate the db tier, and we generally use rsync or any similar tool or method (such as storage replication, vm replication and so on) to replicate the apps tier.

Normally as a core dba, a disaster failover test means a dataguard fail over operation, however; in EBS world it is a little different, as we have an apps tier and an apps schema to manage in addition to the database tier.

Actually, I wrote several blog posts about these subjects, but in this one; I want to give you some direct instructions to accomplish such a failover test.. 

This tests can also be done while implementing the business continuity in EBS environments, just to be sure that the disaster implementation is accurate.

The scenarios for these tests may vary, however; I will give you the instructions for a specific one. for hte scenario which is mostly used one actually...

Suppose, you have an EBS environment. (multi node, RAC or single node, non-RAC it doesn't matter)

You have a vm replication for the apps tier, and we have a dataguard configuration for the db tier.

Now, you want to test this disaster environment without touching the PROD.. I mean, your PROD will continue running, while you do your test in your disaster environment.. 

In this case, the plan should be as follows; 

• Stop the data guard and apps tier replication.
• Save the state of the standby db tier. (guaranteed restore point)
• Backup tnsnames.ora, listener.ora in your standby db tier, to revert back the changes when your tests are done.    
• Open the standby db tier read/write.
• Build a new context file for the standby db tier (we suppose that the hostname of the standby db tier is different from PROD env.)
• Run fnd_conc_clone.setup_clean;
• Run autoconfig on standby db tier. 
• Manually edit the context file of the standby apps tier according the standby database tier and its listener
• Run autoconfig on standby apps tier. (if the hostname of standby apps tier is different from the Prod apps tier -> run adcfgclone.pl instead)
• Do the following updates (if hostname of the standby apps tier is different from the prod apps tier -> just in case... )

            SQL > update apps.fnd_concurrent_requests set logfile_node_name = <new application tier node>, outfile_node_name = <new application tier node> where logfile_node_name = <old application tier node> and outfile_node_name = <old application tier node>;

            SQL>update apps.fnd_conc_req_outputs set file_node_name=<new applications tier node>

where file_node_name=<old applications tier node>;

• Change instance specific profile options if necessary
• Change printer settings if necessary
• Change workflow configuration settings if necessary
• APPLCSF variable if necessary
• Start standby apps services

Do your test! That 's it. Once your test are done, 

• Shutdown the standby apps services
• Restore standby database to the guaranteed restore point
• Resume dataguard flow from where it left off. (configure the standby tier's listener and everything back to their originals , according to the standby role)
• Resume apps tier replication / vm replication.

Note that, this is the method we follow most of the time .

It is the required method when you don't follow the exact steps while you are building your EBS Disaster environments .. I mean the steps documented in  Oracle Support (MOS).. Those are the actual steps you need to follow while you are creating/configuring the EBS disaster environments.

I see that, most of the time, EBS disaster configurations are made without following these steps line  by line..

I'm actually talking about documents like the following;

Business Continuity for Oracle E-Business Suite Release 12.1 Using Oracle 12c Physical Standby Database (Doc ID 1900663.1)

Business Continuity for Oracle E-Business Suite Release 12.2 Using Oracle 12c (12.1.0.2) Physical Standby Database (Doc ID 1963472.1)

Look we have those kinds of documents even for 19C  :) -> 

Business Continuity for Oracle EBS 12.1 on Oracle Database 19c Using Physical Host Names (Doc ID 2567091.1)
Business Continuity for Oracle EBS Release 12.2 on Oracle Database 19c Using Physical Host Names (Doc ID 2617787.1)

Normally, we must follow the Business Continuity documents for implementing EBS Disaster Recovery environment, and if we follow those documents line by line; the failover and switchover steps are easier.. 

For instance, we don't need to run adcfgclone.pl even if the hostname of the standby apps tier is different from the prod apps tier.

Weblogic -- Unsupported Major.Minor Version 52.0 , while making the custom application available to all clients

This issue was escalated to me during a Custom application migration process. A custom application was trying to be migrated from one Weblogic Server to another Weblogic Server, and although, all the things were in place, the application couldn't be deployed to the new Welogic. It was staying in Prepared state, but couldn't be actived for servicing all the requests.

Source Weblogic was running on Windows and target Weblogic was running on Linux.

Source Weblogic was configured to use JDK 1.8 and it was the default java configured for its Weblogic OS user.  However; in the target Weblogic, we had JDK 1.7.. Target Weblogic was configured to use JDK 1.7 and it was also the default java configured for its Weblogic OS user.

The problem was actually arised just after the deploy, when we tried to start the custom application..

The error in Weblogic console was related with a java major version mismatch.. (Ofcourse, I can say it very easily now after long analysis :) 

"Unsupported major.minor version 52.0" was the error which was seen in the logs and it was directly related with the Java major version of the environment. It was related with the class file format, and the problem was caused by using a lower JDK (lower than the one used in compile time) during runtime.

Here is the list of major version number of the class file format being used.

Java SE 15 = 59 (0x3B hex),

Java SE 14 = 58 (0x3A hex),

Java SE 13 = 57 (0x39 hex),

Java SE 12 = 56 (0x38 hex),

Java SE 11 = 55 (0x37 hex),

Java SE 10 = 54 (0x36 hex),[3]

Java SE 9 = 53 (0x35 hex),[4]

Java SE 8 = 52 (0x34 hex),

Java SE 7 = 51 (0x33 hex),

Java SE 6.0 = 50 (0x32 hex),

Java SE 5.0 = 49 (0x31 hex),

JDK 1.4 = 48 (0x30 hex),

JDK 1.3 = 47 (0x2F hex),

JDK 1.2 = 46 (0x2E hex),

JDK 1.1 = 45 (0x2D hex).

As you see above, Major Version 52 maps to Java Se 8, so JDK 8..

In our case, Source Weblogic was configured to use JDK 8 as I already mentioned.. So the cause was the JDK 7 which was being used in the target Weblogic.

As for the solution, we upgraded the JDK version of the target environment :) We made Weblogic to use JDK8 and we made the OS user of the Weblogic use JDK 8 for locating its default java (just in case).. So, we made configuration for JDK 8 in in OS & WLS configurations. (acutally installed JDK 8 on the Linux OS as well)

Well, this was the tip of the day :)  In our case, it was a minor but blocker error, so I found it useful to share with your. See you.

Erman Arslan's Oracle Forum -- Questions and Answers Series - October 2020

Some months are busier, some months are a little quieter. 

But in any case, this is us. Those who try to improve their technical skills by sharing their knowledge. Those who enjoy their work while dealing with different and new issues reported from different parts of the world ... 

We always start with the the following question and answer :) ;

PS, this question and answer reveals what my motivation is. ->

Question: How much time do you spend/lose?
Answer: Well, how much time I gain? :) 

In October, again I tried to answer all the questions. I gave advices when necessary, and provided guidance for the solutions when I had enough info about those problems and the environments where those problems arise. 

This month, we also have EBS-oriented issues, but also database and goldengate-oriented issues.

Take a look at the issues and related topics in Erman Arslan's Oracle Forum. Collect the harvest you can gather from the support and technical directions provided!

Question -> unsetting env variable - during EBS 12.2 cloning

Strange errors on Wo-rkflow-mailer-OAF-based-notifications-and-SSL-terminated-load-balancer-environment

Allowed jsp feature for EBS 12.1

Question - Goldengate replication - trails, kudu and striim

Rman backup problem - EBS R12 env

EBS adding node problem + Solaris fork & swap problem

Unable to start oracledbconsoleorcl

Question - CDC peformance, Logminer rates..

Goldengate DDL replication Heterogeneous env, includes Striim and Kudu

adcfgclone fails - EBS 12.2

Question on EBS R12 - RAC migration

ora-01426: NUMERIC OVERLFLOW + ORA-12012

EBS 12.2 - ssl configuration - manual edit for ssl.conf

EBS 12.2 - DB ACS - HTTP access problem

Kudu - Striim Time difference -- just a comment

ER diagram - what kinds of info does it provide to us?

Unable to implement Digital signature In EBS 12-2

Convert a MongoDB JSON collection to SQL - a comment

NFS mount permission problem - NFS Share from Solaris and mount it from Linux

Solaris - Linux nfs share mount / mount.nfs: Connection refused

Question on AWR - CPU to parse elapsed ratio is between 30 to 90 % in a day during working hrs.

MPDP - ORA-39083 ORA-01917 (user or role does not exist) Errors

Goldengate - get a unique value which would help in determining the sequence of operation

Tool to convert Oracle SQL to Kudu SQL / INT64(-5)} is not supported for target filed -- just a comment