Wednesday, January 5, 2022

Erman Arslan's Oracle Forum / December 2021 - "Q & A Series"

This month again, I did not break the tradition and continued to support customers and the community despite the load of my work (leading my department, mission critical support activities & projects + intellectual gymnastics - physics and stuff like that ) ! :) 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:


Come on, let's see what we've been up to in December 2021.
(+ in the beginning of Jan 2022) 
#amaerman

row cache lock on drop table

Qeustion Form & report upgrade 

How to compile a pls and clear func admin cache

Filesystem Block size by big

Cross edition triggers by sg

Cutover and cleanup by satish

AWR is empty by big

adop cutover failled by big

Database performance monitoring tools by satish

RAC change IP by Roshan

CVE-2021-44228 Advisory for EBS by big

Apache log4j by satish

Patch 20725445: RCA: WRONG BASE AMOUNTS

Planing Migration from Existing Hardware to ODA

multi node setup EBS 12.2 non shared APPL_TOP

12.2.11 and 19c by Laurel

Applied patches to patch edition homes by satish

read only filesystem by Roshan

Quantity record with SET1 and SET2 -EBS 12.2.10

adop fails : ETCC not run in the database node

Cannot send testmail: Invalid role by big

Weblogic conflicts R12.2.5 by satish

APEX login page does not respond by big


Monday, January 3, 2022

RDBMS -- TRIGGER -- ORA-06502: PL/SQL: numeric or value error: character string buffer too small / a WEIRD CASE

This is about an interesting problem, that we encountered after migrating a critical database from HP-UX to Exadata.. I must admit that it was very challenging, the solution touched the foundations of our problem-solving mechanism and probably improved that mechanism for solving these types of problems that we may face in the future, faster.

Before giving you the story, I just want to remind that;

We are just trying to be Bayesians (having our subjective beliefs) and have priors on things ..

In addition to that, there is something that I can consider as a weakness. That is; we can find ourselves in a desperate situation if we get a false evidence (or it is better to say if we happen to misinterpret the evidence), that touches the foundation of our decision making mechanism that we use for finding the cause of the problem that we deal with. This type of an evidence can be an illusion that can be caused by an insufficiently defined error message or by lack of information, by lack of knowledge..

Two more words before start;

-What you get instantly, is not the whole nature of the mechanism.

-Make sure , you didn't come across to a  too non-representative part of  the distribution.

:)

Okay, now let's get down to business and look at our problem;

We migrated a mission critical database (running on a critical environment) to a newly deployed Exadata X8 using XXTS (accelerated with RMAN incremental backups) .. 

Actually, we had already done maybe 10 Exadata migrations in this customer environment, but in this case, we had ORA-06502 errors all over the place.. I mean once we enabled 6502 trace (alter session set events '6502 trace name errorstack forever, level 3') in the database level, we saw that, there were more than one PLSQL that was getting these type of errors.. (probably, overlooked by the testing team..)

Anyways; the error stack was;

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

----- Current SQL Statement for this session (sql_id=1mspsdarsts3r) -----

UPDATE DENEME20 SET TEST_KODU =:B3 , DENEM_KODU =:B2 WHERE SOZLESME_NO =:B1 

----- PL/SQL Stack -----

----- PL/SQL Call Stack -----

  object      line  object

  handle    number  name

0x574be81d0        66  TEST.COMM_BEFORE_UPDATE

0x3d0285ac0       152  package body TEST.UA_DO_IT 

0x46a4c0dc0         2  anonymous block

Okay, here we go.. Let's see what we have done for finding the solution. Let's see the evidences that we collect and let's see the actual cause and the solution :)

1)

It was clear that, the Before Update Trigger TEST.COMM_BEFORE_UPDATE was failing at Line 66.

We checked that line 66 by using tools like Toad and views like dba_source.. (This is the actual mistake actually.. We will come this topic later.). Line 66 was a "select into" clause.. So the trigger was written in a way to query a table and put the value that it gets into a variable, which had the exact same length that the relevant table column had.

2)

The target database was created with a different NLS Language and territory setting. Different than the source.. So, those parameters were changed directly. On the other hand, as you already know that , NLS_DATABASE_PARAMETER can not be changed after the database creation.. But! this should not be a problem, because those parameters were changed in the instance level.. 

Still, this situation have created a big question mark for us.. Because, the "select into"  that we identified by checking the line 66 of that trigger could fail because of a char conversion problem.. Besides, we had some Oracle Support documens to support this idea.. That is, in some Oracle Support notes, we saw that a wrong NLS Lang could indirectly trigger some bugs in the code..

3)

We also suspected from NLS SEMANTICS settings. We checked it both in the database level, and  PLSQL object level ( DBA_PLSQL_OBJECT_SETTINGS) (that is set during the compile time) , but it was set to BYTES everywhere.. Both in the source and target..

4)

Most of the failing PLSQL codes were triggered by the DBMS SCHEDULER.. So there were database jobs.. They were not DBMS Jobs, but still they were scheduled using the Database engine, and it was the database itself that was executing these failing codes. Although, the NLS setting of these scheduler jobs were correct, we suspected that maybe database was setting those NLS parameters wrong in its execution time.. Maybe dbms scheduler was still using NLS_DATABASE_PARAMETER.. (there is no info about that bytheway, so it was just an educated guess at that moment:)

5)

A Sql Developer tool, installed in another client machine (different than our client machine where we have other database tools installed) could run those PLSQLs without any problems! I mean, direct executions.. (not using DBMS SCHEDULER).. However, DBMS SCHEDULER was still failing and we were still failing -- even if we run those PLSQLs directly, we were getting ORA-06502.. But, Sql Developer on that client could run them without any problems! 

We also checked the NLS preferences of this SQL Developer and saw that they were set correctly. We also saw that, even when we change the NLS setting of the session and set them to a wrong NLS Lang and territory; Sql Developer could run those problematic PLSQLs without getting any errors.. (Note that, this was a wrong try.. Actually, Sql Developer gets its env from its preferences..So it was actually no surprise :)

6)

We had similar cases in the other database environments ( Core Banking environment, Reporting environments) and we had a memory about them, a memory was telling us these of errors were already solved earlier.. It was telling -> just check the NLS setting :)  -- what an intuition :)

Well, you see how all these things bring us closer to the NLS settings, but they also lead us to ineffectiveness and insolvency.

The actual solution comes at this moment, we suddenly get the following info (we recognized someting);

In the call stack we actually got the correct line number, but our interpreations was wrong;

I mean, our error stack told us line 66 of that Before Update Trigger was failing and that was correct.

But! the line 66 was not that "select into" clause.

the line info in dba_source or toad didn't give us the correct info..

In case of triggers; the code includes both a trigger definition and PLSQL Block , so it it in the form of "Trigger Definition + PLSQL block"

ORA-06502 is a PL SQL error, so the Line 66 should be in the PL SQL block.. So we need to discard those definition lines and start counting from the word DECLARE...

In that case, we found the failing line is a variable assigment.. A variable assignment that was done using the return of a custom function and that custom function was the real cause, because it had a fixed variable string for storing the hostname.. After the Exadata migration, the hostname (FQDN) was extended, so it just couldn't fit and that's what caused the error. A substr modification or a variable size modification made us workaround that bug caused by the custom code, but at the end of the day, it made us learn something important and made us update our priors. 

--Note: That SQL Developer was running on a client which had a short hostname, and that's why it could fit in the relevant variable and that's why that Sql Developer didn't get any errors during our test runs.

Okay that is it! But still, look back at the first paragraph and think before you finish reading:)


Wednesday, December 8, 2021

EBS R12 -- WF Mailer java.lang.StackOverflowError at org.apache.regexp.RE.matchNodes

Today's blog post is about a problem in Workflow Mailer (WF Mailer). This time, I will directly give the definition of the problem along with the solution offer, because I have already written lots of articles on this subject.. So, I think my followers should already know all the things about the WF Mailer itself and the mechanism of the workflow outbound & inbound.

Okay.. Let's get to the point without wasting time. 

The problem was in the IMAP side.. Some of the approved orders (approved via emails) was left in not-approved status in the EBS system. It was clear that the Inbound of the WF Mailer couldn't do its job. Besides, the newly approved orders could not be reflected to the EBS system either.

A statement level showed the following;

[Nov 10, 2021 10:04:07 PM EET]:1636574647912:-1:-1:SP-SERVER1:10.10.10.1:-1:-1:1:20420:SYSADMIN(0):-1:Thread[inboundThreadGroup1,5,inboundThreadGroup]:1505677572:72687:1636574641893:212:UNEXPECTED:[SVC-GSM-WFMLRSVC-695685-10006 : oracle.apps.fnd.cp.gsc.Processor.run()]:java.lang.StackOverflowError

at org.apache.regexp.RE.matchNodes(RE.java:1358)

at org.apache.regexp.RE.matchNodes(RE.java:1358)

at org.apache.regexp.RE.matchNodes(RE.java:1358)

at org.apache.regexp.RE.matchNodes(RE.java:1358)

A search on MOS knowledge base pointed to the following;

Workflow Mailer Stopped Working java.lang.StackOverflowError at org.apache.regexp.RE.matchNodes(RE.java:1104) (Doc ID 1505015.1) ** Especially this one.

Java.lang.StackOverflowError When Mailer Processes Inbound Email (Doc ID 2611474.1)

Once the problematic email was analyzed, we found an unexpected character code there, just beneath the NID line.

Here is the relevant part of the email;

Action:

 'Approve'

Note: ''

NID[347449/295312543679791762909718721451122233183@WFMAILPT]

Oracle ACE /

+27201

Explanation & the solution:

There was a problematic email on the IMAP side (an email, with an unexpected content sitting in the inbox of IMAP, or in the PROCESS folder). 

Remember, WF Mailer can not easily handle these kinds of unexpected things.. Actually, It could be clearly seen that there was a &#43 character just after the NID line. Note that, this HTML characters correspond to the a plus sign. 

The mobile device of the client that responded(Approved or Rejected) this WF Mailer notification  may have placed this code into this response. This caused WF mailer to crash with stack overflow. 

The WF Mailer just could not process this email, but it couldn't ignore this email either.. 

It just couldn't continue. So this email must have been removed from the inbox of the EBS WF Mailer's IMAP account. (The required EBS side operation can be done manually in this case). 

Once this problematic email that sits on IMAP Inbox/or on PROCESS folder, was removed, and just after the WF Mailer was bounced, the problem dissapeared.. WF Mailer could just do okay and the problem was solved.

Saturday, December 4, 2021

EBS 12.2 -- Can not login after upgrade -- Login failed. Please verify your login information or contact the system administrator.

The issue appears in upgrade customers, and the login page just fails with the following;

Login failed. Please verify your login information or contact the system administrator.

If you have done an EBS 12.2 (for instance EBS 12.2.10, 12.2.11) upgrade and can't login to EBS.. (using SYSADMIN or any other user);

If you face a situation where you end up with Login Failed message when you enter the credenatials on Login page and click submit;

If you tried changing/resetting the passwords using FNDCPASS-like utilities, but the issue still persists;

If FND_WEB_SEC.VALIDATE_LOGIN returns Y for your users,

If there are no significant errors  in oacore_server1 out, diag or log files +  Apache/OHS diag files, 

then ->

run the EBS upgrade Analyzer! Again we have an analyzer to use ! : ) When saying "we" , I don't mean "me", actually Oracle has one :)

E-Business Suite Upgrade Analyzer - 12.X to 12.2.X ( Doc ID 2634237.1 )

So, we run the analyzer and check its output.. ( we don't be lazy for running these analyzers, they actually save us time!)

Most probably, the analyzer will tell you that the following patch is missing in your system;

Patch 32357755: SYSADMIN (WHEN PASSWORD REUSE IS ON) PASSWORD CHANGE DOES NOT WORK AFTER UPGRADE FROM R12.1.3 TO R12.2.

Just apply the patch and the issue will dissapear..

Note that, as you may have already noticed the definition of this patch doesn't directly map to our issue, but the conditions that I gave above actually redirects us to this patch ( even without checking the analyzer output).. This may be the insight of this blog post. When it comes to Oracle patching and documentation and especially the patch definitions, I 'm the voice of the skeptics:) 

So that's why I've put that long "if" part in the beginning and only then, that precise "then" part is given :)

EBS 12.2 / ISG enablement - isgDesigner.xml:369: Only deploy or undeploy is supported.

This blog post is for the ones who are trying to enable Integrated SOA Gateway on EBS 12.2.. This task is also done in 12.2 upgrade projects -- if you were already using ISG or planning to use it on your newly upgraded EBS 12.2.

You know what, the things are getting even more complex for EBS world.. I mean, for an upgrade you start with a document, then it redirects you to another one, and that another one redirects you to a different one, and then you come back to the first one and then you find yourself redirected to a new one :) Sometimes, it makes you feel like you are in some kind of a nested redirect loop :) 

So please be careful and do your own documentation while doing these things in your TEST environments..  Missing or ignoring a single thing will bring you some catastrophes later -- you can be sure about that : )

Well.. Documentation to follow for this task is as follows:

Installing Oracle E-Business Suite Integrated SOA Gateway, Release 12.2 (Doc ID 1311068.1)

Anyways; this issue appears in section 8, while configuring the REST services 

Remember -> SOA Suite is REQUIRED when using SOAP web services.

 -->

Section 8: Configuring Oracle E-Business Suite Integrated SOA Gateway for REST Services on Release 12.2.x Upgraded From Oracle E-Business Suite Release 12.1.

Here is the failed execution and the error..

ant -f $JAVA_TOP/oracle/apps/fnd/isg/ant/isgDesigner.xml -DserviceType=REST -Dactions=upgrade -Dverbose=ON

Error:

BUILD FAILED

/u01/oracle/PTK12_2/fs2/EBSapps/comn/java/classes/oracle/apps/fnd/isg/ant/isgDesigner.xml:369: Error:

Only deploy or undeploy is supported.

--

So it seems like there is something wrong with the isgDesigner.xml.. (despite the fact that you may be on  AD Delta 13 and TXK Delta 13.)

Don't push yourself hard for analyzing these types of issues, we already have a tool called ISG Setup Analyzer.

EBS Integrated SOA Gateway (ISG) Setup Analyzer (Doc ID 2462729.1)

Okay let's jump to the conclusion.. In this case, ISG analyzer will report you that ISG Consolidated Patch for 12.2 (21_4_3) (Patch) is missing in your EBS system.. So just install the patch and re-execute the failing ant command and that's it !:)

Erman Arslan's Oracle Forum / November 2021 - "Q & A Series"

This month again, I did not break the tradition and continued to support customers and the community despite the load of my work (leading my department, mission critical support activities & projects + intellectual gymnastics - physics and stuff like that ) ! :)

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:  


Come on, let's see what we've been up to in November+ in the beginning of December 2021. #amaerman

Quantity record with SET1 and SET2 in EBS 12.2.10 by ANIETO

adop fails : ETCC not run in the database node by big

Cannot send testmail: Invalid role by big

Applied patches to patch edition homes by satish

Weblogic conflicts R12.2.5 by satish

APEX login page does not respond by big

Weblogic logon/pwd refused by big

read only filesystem by Roshan

jar signing code failed. by Naseem khan

Datafiles redistribution across mountpoints by Karthik

Unable to logout from DMZ in EBS 12.2 by raja

Is Oracle Certification easy to pass? by Tasmina

Form & report upgrade by raiq1

12.2.11 and 19c by Laurel

system encountered error. by raiq1

Ebs shutdown error by satish

WorkFlow Mailer stopped suddenly by big

Planing for Migration from Existing Hardware to ODA by raiq1

RMAN delete obsolete by Roshan

permission to file by Roshan

PCA-X8- Engineered system - OEL 6.9 by Sheikh Rehan

Last PSU by big

Disk addition to diskgroup by satish

Ebs apex integration by satish

Integration from oracle to PostgreSQL database by satish

Purge/Archival in ebs by satish

Authentication in login page takes long time by satish

Monday, November 8, 2021

Erman Arslan's Oracle Forum / October 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. 29 issues this month!
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:  


Come on, let's see what we've been up to in October+ in the beginning of November 2021. #amaerman
Do not forget to read the blog posts too :)



PCA-X8- Engineered system - OEL 6.9 by Sheikh Rehan

Ebs apex integration by satish

Planing for Migration from Existing Hardware to ODA by raiq1

permission to file by Roshan

Integration from oracle to PostgreSQL database by satish

Purge/Archival in ebs by satish

Authentication in login page takes long time by satish

Purging ebs data by satish

ORA-01111: Name For Data File Is Unknown by big

Erp apex integration by satish

No data in Shareable memory section of AWR by big

service statistics in AWR by big

Load avrage in AWR by big

Log buffer by big

Bursting error R12.2 by satish

Bursting ssl R12.2.5 by satish

EBS DR by satish

CPU busy by oracle by big

Apex in rac nodes by satish

Ad_zd and wf_event valid but still shows invalid ot dropped by Mohsin Patel

2572809.1 confusion by satish

DR setup for ebs by satish

Reduce downtime for patching by satish

workflow notifications not received by raiq1

ORA-29024: Certificate validation failure by big

High load average in test server by satish

Workflow oam configuration by satish

Applying missing patches ETCC by satish

dualfs by big


Friday, November 5, 2021

EBS 12.2 -- ORA-06508 + ORA-04065 for APPS.AD_ZD / 6508 trace name errorstack level 3 and all that

Yesterday, I worked on a weird case... The case was on a production EBS 12.2 instance. The problem appeared after recreating the APPS.AD_ZD package body along with its spec.. 

Actually, this kind of a compilation should not be done manually (and suddenly:), but it seems some "consultant" (yes in quotation marks! :) recommended such an action to fix some ADOP problems and then the environment ended up with lots of ORA-06508 errors, thus lots of OAF and Forms pages could not function. 

Lots of ORA-06508 blocking lots of EBS operations (and as you may guess)-> preventing the Business.. 

So it is needless to say it was a Severity 1... Well.. At the end of the story, I have been contacted to fix the problem ASAP.

You know, I like to work on the details/on these weird cases, because those tiny little gaps/problems/errors/unexpected things that we see in those details/in such cases, have the potential to reveal lots information..

Actually, I was already familiar with these kinds of errors and I even wrote some blog post about them .. (https://ermanarslan.blogspot.com/2015/03/ebs-r12-app-fnd-01926-ora-06508-ora.html

But! this one was different. 

Following is an example of an ORA-06508 error encountered on a standard EBS form;


Normally, firstly, I suggested the following action plan;

1)Shutdown apps tier services on all apps nodes. Ensure there are no apps-related OS processes left running there.
2)Run utlrp.sql - sqlplus “/as sysdba” @$ORACLE_HOME/rdbms/admin/urltp.sql
3)Run apps schema compile using adadmin.
4)Run -> Alter package AD_ZD compile
5)Alter package AD_ZD compile body
6)Repeat step 2&3
7)Restart the EBS database
8)Start Apps Tier and retest

Note that, there were no invalid objects in the database and AD_ZD was already valid and seemed healthy..

Well, this action plan didn't help and then I saw a grant on the AD_ZD table.. An exectute grant was given manually.. Remember we have EBR (Edition Based Redefinition) in EBS 12.2, so we need to pay attention to that..  So I told the admin to revoke that grant and use AD_ZD.grant_privs to grant it back. (of course, I told -> take those necessary compilation actions in case you see an increase in the count of invalid objects)

Example: exec AD_ZD.GRANT_PRIVS ('EXECUTE','AD_ZD','OAM_MONITOR_ROLE');

This was a good move but it didn't solve the issue either. -- Still, alesson learned there..(for the admin at least)

Then I decided the check the call stack.. So I made the admin set the following event in the database and reproduce the error; 

ALTER SYSTEM SET EVENTS '6508 trace name errorstack level 3’;

After setting the event and reproducing the error, we checked the event trace and the call stack was there;

----- Error Stack Dump -----

ORA-04065: package body "APPS.AD_ZD" 

ORA-06508: PL/SQL: APPS.AD_ZD"

----- SQL Statement (None) -----

Current SQL information unavailable - no cursor.

----- PL/SQL Stack -----

----- PL/SQL Call Stack -----

object line object

handle number name

327fccceb8 1074 package body APPS.WF_CORE

253df749c0 489 package body APPS.WF_EVENT

253df749c0 2141 package body APPS.WF_EVENT

253df749c0 3021 package body APPS.WF_EVENT

319d17c378 190 package body APPS.JTF_TASK_WF_EVENTS_PVT

30ccddb468 2137 package body APPS.JTF_TASK_ASSIGNMENTS_PVT

30ccddb468 1213 package body APPS.JTF_TASK_ASSIGNMENTS_PVT

31e85eb340 4080 package body APPS.JTF_TASKS_PVT

2efd1fc9d0 4477 package body APPS.JTF_TASKS_PUB

It was cearly seen that the code in 1074th line of WF_CORE package was failing..
We had a call to ad_zd.get_edition() function there, in that line.. 
However; AD_ZD package was already valid and when we tried to execute the same function via sqlplus or/ Toad (using an APPS session), we could succeed without any problem..

This was one of my AHA moments actually.. Just after seeing the contents of the trace and ensuring the APPZ_ZD.GET_EDITION can be executed by an Apps session, I started to think that there was a kind of a timestamp discrepancy exist for the objects that we see in the call stack of the event trace.

At the end of the story, I suggested compiling WF_CORE,WF_EVENT and all the other packages listed above in the call stack.. Of course these kinds of compilations would make some dependent objects invalid and that's why I also suggested to run utlrp.sql and apps schema compile as the post actions..The compilations saved the day, resolved the timestamp discrepancies and the issue was solved! 

It was a risky move (compiling a bunch of standard packages), but it was necessary in this case..


a reminder -> Don't forget to disable the event '6508 trace name errorstack level 3' after reproducing the error and catching the call stack :) Those events are just for diagnosing purposes, so they should not be  left enabled.

Friday, October 29, 2021

Exadata X8M-2 & PCA X8-2 -- Part 3 Migration / add-on - XTTS may fail ORA-17503: ksfdopn:2 Failed to open file

We are continuing our journey on Exadata and PCA migration, and here is the tip of the day;

Although XXTS know reached the 4th version, 4th generation, there are still some cases that it can't handle, and there are still some bugs that we need to pay attention to. Especially when your source database consists of a high number of datafiles, multiple incremental backups to restore and when you use a high degree of parallelism.

Well.. you may end up with the following; while trying to roll forward your target database.. I mean, when applying incremental backups to the datafile copies on the destination system ... (Remember-> we use RMAN incremental backups for accelerating our XXTS migation operations) 

-- Main Document: V4 Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup (Doc ID 2471245.1)

So, you may end up with;

ORA-17503: ksfdopn:2 Failed to open file +DATAC1/ERM/DATAFILE/erm_732.dbf

ORA-19625: error identifying file +DATAC1/ERM/DATAFILE/erm_732.dbf

ORA-15173: entry 'erm_732.dbf' does not exist in directory 'DATAFILE'

Don't push yourself hard to find your workaround, because there are issues associated with the following bugs ->

bug 31620676: V4 XTTs RECOVERY FAILS WITH MULTIPLE INCREMENTAL BACKUPS

Bug 31054229 : XTTS V4 CREATING INCORRECT RECOVER COMMAND -- note that, this bug currently being investigated by Oracle.. Status : 11 - Code/Hardware Bug (Response/Resolution). Escalated

Luckily, we have workarounds documened in MOS and the ones given in the following MOS document clearly help ->

V4 XTTs: Restore Returns Errors (ORA-19625 or ORA-19641) With Large Number of Datafiles (Doc ID 2689397.1)

We just do some tricks in res.txt and apply one incremental at a time to these both bugs...

However; this workaround alone may not be sufficient in certain cases, where you have a high degree of parallelism. I mean;

It is also recommended to use a lower degree of parallelism in xtt.properties to prevent those RMAN errors . In our case, we concluded that XTTS can not manage 128 parallel thread/workers so, we decreased it to a lower value (32) .

This was a real life case and I wanted to share it with you.. I hope it helps..

Wednesday, October 27, 2021

EBS -- Workflow Mailer -- Validation Error Occured & Wrong Header on outbound e-mails / some "Apps DBA gymnastics" :)

We are approaching the end of the week and even the month. I 'm having busy days with the critical migration and upgrade projects, and that's why having hard time getting here.. but! the month is not over without an EBS post :)

Okay! It is time to do some Apps DBA gymnastics :)  Wf mailer is today's subject!

-- If you want to read some advanced stuff about the wf mailer, you can also read the following blog posts;

https://ermanarslan.blogspot.com/2014/09/ebs-r12-notification-mailer.html

https://ermanarslan.blogspot.com/2016/09/ebs-r12-after-disabling-ssl-on-apps.html

https://ermanarslan.blogspot.com/2020/12/ebs-r12-workflow-mailer-ora-00054-due.html

https://ermanarslan.blogspot.com/2014/08/ebs-workflow-notification-mailer.html

Okay. Today, 2 different issues on the same subject, the workflow notification mailer.

The first one is, the unexpected "Validation Error Occured". Always expect the unexpected! :) 

The SMTP server IP adress was correct. Apps node could connect to the SMTP server and we tested reaching the port with telnet. We even used SMTP command to send an email through linux command line ..There was no auth required, so we had relay. But! no matter what we did , the same error was encountered.. There were no reasons. This was a new EBS 12.1.3 clone and it was obvious that this workflow mailer configuration screen could not manage it! :)

The solution was updating the workflow mailer configuration using @afsvcpup.sql from backend..

$ cd $FND_TOP/sql

$ sqlplus apps/***

SQL> @afsvcpup.sql

Actually, we fixed all the inbound related incorrect configuration that was still there.. The configuration of the source env was still there in the config tables. Unfortuneatly, OAM - WF mailer configuration page (System Administrator > Oracle Application Manager > Workfow > Notification Mailers > Edit ) was still trying to validate them.. 

We updated a bunch of configuration parameters and then retried the same action in the same configuration page.. This time (as expected), we could configure the outbound..

Note that, we didn't have inbound for this clone env but the config was still there! We didn't have SSL  config for the mailer either, but we had SSL there.. So the config was copied from the source as is and that's was the cause.. 

Just updated the following problematic ( incorrect for the clone env) configuration parameters;

Inbound SSL Enabled

Inbound Server Name

Inbound Thread Count

Outbound Server Name

SSL Trust store

Inbound Server Name

The second issue was a peace of cake :) but still want to share it with you.

After configuring the mailer, we tested it and saw that, the value in the From field of the test mail was wrong, as you may expect; we still had the source config even there!

We updated the value of the From field through the advanced config page of the workflow mailer and the issue dissapeared.

 System Administrator > Oracle Application Manager > Workfow > Notification Mailers > Edit > Advanced > step 4 of 8

Well. That 's it.. Don't tell me "these are not interesting", remember I already wrote in the beginning -> 

I m writing this post for doing some Apps DBA  gymnastics  :) and they are real life cases :)

Note that, I'm thinking about writing a patch for these 2 issues. If I have find some time and space :), I may write a custom patch for it.. We 'll see.. A successful clone shouldn't copy the workflow mailer configuration from source to target as is (without changing anything).