-- or just use the direct link:
-------------- "Erman Arslan's Oracle Forum is available now. Click here to ask a question. " --------------
Wednesday, January 5, 2022
Erman Arslan's Oracle Forum / December 2021 - "Q & A Series"
-- or just use the direct link:
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)
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
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.
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:
Monday, November 8, 2021
Erman Arslan's Oracle Forum / October 2021 - "Questions and Answers Series"
Question: How much time do you spend/lose?
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:
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;
Example:
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
We had a call to ad_zd.get_edition() function there, in that line..
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 BACKUPSBug 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).





