- Increased the SGA max memory to 5GB and target to 4GB, restarted the database (40% of total available memory)
- Left PGA at 3GB (Thus totaling the memory dedicated for the database to 8GB out of 20GB available)
- From the database tier, executed “adautoconfig”
- Execute “adautoconfig” from the application tier
- Shutdown database and restarted the physical server
Oracle Application R12|Payables|FRM-40735 WHEN-VALIDATE-ITEM trigger raised unhandled exception ORA-01403October 21, 2018
Recently one of our accountants forwarded me a screenshot, that was showing “FRM-40735 WHEN-VALIDATE-ITEM trigger raised unhandled exception ORA-01403” while he was trying to enter invoices against a “NEWLY” created vendor/supplier.
Our Oracle Application R12 (12.0.6) is considered 99.99999% stable, without a single technical or functional issue that really become a show stopper throughout last many years.
Well, this particular issue looked perplexing as it was not dealt by Oracle Application’s error reporting & slowly we started dwelling Oracle support documents those were dealing with the given forms error “FRM-40735 WHEN-VALIDATE-ITEM trigger raised unhandled exception ORA-01403”
Most of the documentations where mentioning about IBY duplicate pay party, which was not our case. Hence, I decided to open the associated form APXINWKB.fmb & located the WHEN-VALIDATE-ITEM trigger associated with the column “Purchase Order Number”. I couldn’t find any irregularities between an order that didn’t raise the error and this particular Purchase order did raise the exception, which was unhandled.
After two days of continuous attempts, I remembered that such errors happen in other forms modules when we had missing information for new vendors/suppliers. Must be due to a bug, there were times when site level details were NOT populated to organizations level details for a vendor/customer & I decided to go through all mandatory elements those were expected while creating a new vendor/supplier.
I sat with my colleague and we reached to “Payment Method”, and realized that the default payment method was not set for this particular vendor against the organization where we were getting this unhandled exception.
Once the payment method was set, the invoice was posted for the vendor successfully! So, if you come across these kind of unhandled exceptions across Oracle’s proprietary forms those deal with payments/invoices, prior exhausting yourself with cloning and patching, make sure you have all the mandatory elements for customer/vendors are properly filled in and assigned to all the organizations.
Hope this finding helps few consultants out there!
Okay, I was silent for couple of months. I took a much needed break and back to work now. As few of you may already know, I am not a DBA (Certified), yet I have dealt with Oracle databases throughout my career & today was “another day” when I came across something new after restoring RMAN backup to a TEST environment.
Actually the entire “how to document” was provided by a APPS DBA friend (thanks to such geeks who are never bothered about someone else “learning the tricks” and challenging them! Geeks remain geeks) & without giving much attention to few elements, I “successfully” duplicated the 11g R2 database. Once after the database came online, I realized that the instance was pretty slow & immediately monitored the alert logs.
I started reading few entries like following:
Thread 1 cannot allocate new log, sequence 56 Private strand flush not complete Current log# 3 seq# 55 mem# 0: /u01/oratest/TEST/db/apps_st/data/redo03a.log Current log# 3 seq# 55 mem# 1: /u01/oratest/TEST/db/apps_st/data/redo03b.log Beginning log switch checkpoint up to RBA [0x38.2.10], SCN: 5986177240123 Thread 1 advanced to log sequence 56 (LGWR switch) Current log# 4 seq# 56 mem# 0: /u01/oratest/TEST/db/apps_st/data/redo04a.log Current log# 4 seq# 56 mem# 1: /u01/oratest/TEST/db/apps_st/data/redo04b.log Completed checkpoint up to RBA [0x38.2.10], SCN: 5986177240123 Thu Oct 04 12:14:14 2018 Beginning log switch checkpoint up to RBA [0x39.2.10], SCN: 5986177240998 Thread 1 advanced to log sequence 57 (LGWR switch) Current log# 1 seq# 57 mem# 0: /u01/oratest/TEST/db/apps_st/data/redo01a.log Current log# 1 seq# 57 mem# 1: /u01/oratest/TEST/db/apps_st/data/redo01b.log Thread 1 cannot allocate new log, sequence 58 Private strand flush not complete Current log# 1 seq# 57 mem# 0: /u01/oratest/TEST/db/apps_st/data/redo01a.log Current log# 1 seq# 57 mem# 1: /u01/oratest/TEST/db/apps_st/data/redo01b.log Thu Oct 04 12:14:25 2018 Beginning log switch checkpoint up to RBA [0x3a.2.10], SCN: 5986177241136 Thread 1 advanced to log sequence 58 (LGWR switch) Current log# 2 seq# 58 mem# 0: /u01/oratest/TEST/db/apps_st/data/redo02a.log Current log# 2 seq# 58 mem# 1: /u01/oratest/TEST/db/apps_st/data/redo02b.log Thu Oct 04 12:14:47 2018 Thread 1 cannot allocate new log, sequence 59
I landed over a discussion at https://community.oracle.com/thread/364032?start=0&tstart=0 & few others, and at many places I read suggestions towards the redo log files getting filled too fast because of the smaller sizes allocated. I check the production instance, the redo log files were of size 1000M when the TEST instance log files were of size 100M!
So my next requirement was to resize the redo log files without damaging the database.
I came across an excellent post here https://uhesse.com/2010/01/20/how-to-change-the-size-of-online-redologs/ that explains how to create new redo log files and to drop the old ones without affecting the database (or users). The best part is, you don’t even have to take the database offline for any of the suggested activities.
So if you ever face such a situation, give it a try. You would be happy like me :)
I know the subject title is not very professional this time. Yet, I want to make a claim that I figured out something, for which I spent more than couple of years time and have followed up few Oracle community threads (without much interesting results)
We had to retired a hardware that was recommended by the Oracle EBS implementation partner, within 2 years once after we went online with the R12 instance. We had 10g 10.2.0.3 with the instance, things were getting messy and slow & the new support partner recommended for a better hardware.
I always had eyes on this retired server. It had Linux, hence we couldn’t come up with a practical requirement to integrate the Linux server with our Windows domain environment and it was kept switched off until the virtualization project came online.
We needed “something” to hold a copy of the EBS instance while it was being virtualized.
So, I cloned this machine & before continuing let me describe what this is hardware is like:
Architecture: x86_64 CPU op-mode(s): 32-bit, 64-bit Byte Order: Little Endian CPU(s): 8 On-line CPU(s) list: 0-7 Thread(s) per core: 1 Core(s) per socket: 4 Socket(s): 2 NUMA node(s): 1 Vendor ID: GenuineIntel CPU family: 6 Model: 23 Model name: Intel(R) Xeon(R) CPU E5420 @ 2.50GHz Stepping: 6 CPU MHz: 1992.000 BogoMIPS: 4987.29 Virtualization: VT-x L1d cache: 32K L1i cache: 32K L2 cache: 6144K NUMA node0 CPU(s): 0-7
[root@erp-bak ~]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sdj2 59G 12G 44G 22% / tmpfs 9.7G 3.9M 9.7G 1% /dev/shm /dev/sdj1 2.0G 325M 1.5G 18% /boot /dev/sdj5 738G 531G 170G 76% /u01 /dev/mapper/mpathbp1 591G 320G 242G 57% /u02 /dev/mapper/mpathcp1 197G 38G 150G 20% /u03 /dev/sdf1 591G 70M 561G 1% /u04 /dev/sdi1 269G 59M 256G 1% /u05
in addition to the local disks this server have partitions mounted from a IBM SAN.
Once the clone was done, I realized that the instance was extremely slow & our part time DBA started making excuses like “See that’s why we are changing the hardware” (He had 2G SGA and 1G PGA with 20 job_queue_processes against nearly 1TB database)
I opened few discussions with Oracle communities and was pointed towards a tone of documents suggesting me how to fine tune the hardware and database for better performances. Actually nothing were applicable as I didn’t have much hands on experiences with a database & I couldn’t find a person who could really HELP me.
Then I started taking interest about database technology, which I should have years back & came across SGA/PGA and JVM etc & as I had an idle instance, started trying out whatever I have “learned” against it.
While doing the 11g R2 the hard way I realized that I can use AMM and forget about tuning different parameters for memory optimization. Well, still the goddamn instance lagged like hell & I was almost done with it!.
Few of the persistent issues were:
After a cold boot
- The login form would load at client end after waiting almost 3-4 minutes, which gets faster during consecutive attempts.
- It takes ages for to open the concurrent programs window
- Our custom forms & LOVs lag to extremes and so on..
Even shutting down the instance for anything was turning into a nightmare as the database always took more than 15-20 minutes and I had to kill multiple processes manually in order to bring it offline!
Then on a different note, while trying to learn SQL learning I landed against an ask Tom thread, where the asker says “I have setup both SGA and PGA 3GB”, still the SQL runs slow…
I did a fresh clone. Our database was upgraded to 11g almost year back. The default clone had 1G for both SGA and PGA. I altered them with 3G and 3G & bullseye
I went back and altered the SGA and PGA with 4G which was 40% of the total physical memory available for the hardware. I did three shutdowns and restarts of the physical server, did a dozen application and database startup to confirm that what I am experiencing is NOT a once in bluemoon phenomena. Each of my attempt to shutdown the database gracefully were completed within few seconds, not a single time I had to kill the Linux processes to bring it down!
I modified one of the main forms for a custom application and changed few VIEW calls with better logic & I can’t be happier!
Now, said that, don’t rush to me saying “I also did 4G for SGA and PGA and moron I still have a slow instance”. There are many factors affecting the performance of your database and application & most important few are:
Age of your hardware, especially the spinning disks. The aged they are, the worse the performance is going to be as there is hell loads of I/O happens when you are accessing/processing the data from a database.
Recently I was going through a MS SQL discussion about Multi-Tenant architecture and one of the contributors were discussing about a hosting firm that keeps on changing their hardware once in 6 months. I think he was just BLUFFING! ;)
I hope someone gets benefitted by the minor finding I have made YESTERDAY (6th May 2018)!
Recently I was trying to understand the AUTOMATIC MEMORY MANAGEMENT feature of 11g onwards database & ended up in a drastic situation where I changed MEMORY_TARGET parameter with a value higher than MEMORY_MAX_TARGET
This has blocked the database from starting & a quick googling brought me to correct solution
As sys, connect to idle database
#sqlplus / as sysdba
SQL> create pfile from spfile;
–Applies to where spfile is used instead of pfile
Edit the pfile & comment/delete the MEMORY_MAX_TARGET parameter, get back to SQL>
SQL>create spfile from pfile;
This should resolve the error ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET
Hope this helps few folks out there!
Recently we migrated our database from 10gR2 (10.2.0.3) to 11GR2 for our EBS environment and got stuck with a new issue, the tablespace details take hours to get generated (Regardless the clients used, SQL Developer/Toad or SQL scripts)
By following the instructions, as sys we purged “dba_recyclebin” which was having approximately 262 objects and voila! The database started generating the tablespace details within a fraction of second.
Hope this helps few others out there!