Oracle Application R12|Payables|FRM-40735 WHEN-VALIDATE-ITEM trigger raised unhandled exception ORA-01403

October 21, 2018

Hello guys

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.

posterror

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!

regards,

rajesh

Advertisements

How to change the size of Online Redologs?

October 4, 2018

Uwe Hesse

change

Having just reviewed this article I can confirm that it is still valid also for 12c: The exact same commands can be used to change logfile sizes with that version.

If you deal with Oracle Databases for years on a daily basis like I do, you sometimes forget that things that seem elementary to you ain’t necessarily so for others, especially newbies in the Oracle realm. I noticed that when I saw some discussions in forums about redo logfiles and their sizes.

When I go back in time to 1998, that’s where I was the first time responsible for an Oracle Database (version 7, but some things never change). I inherited that Database from the previous DBA who did the setup, creating quite small log groups of a couple 100 KBs in size – which was not uncommon these days,  because Oracle templates mentioned that size as “medium”, if I…

View original post 813 more words


Enable Database Enterprise Manager Console on Oracle EBS R12.1.1

June 16, 2018

I’m yet to check this one out. Reblogging to avoid losing the link among the ocean of bookmarks those I already have.

Joey Out Of The Box

First login as applmgr user, shutdown all application tier process using .
Login as oracle user to linux. Source the database environment.
Ensureis set in the init.ora for the
database using this command:

SQL> show parameter
REMOTE_LOGIN_PASSWORDFILE;

Create a password file using this command on linux:

Then execute this command:

(remember to change SID and port if needed).
You will be asked some question:

SYSMAN schema will be dropped and recreated.
While dropping the scema, database will be quisced.
Database sessions that blocking the quiescing process must be killed.
Use sqlplus to identify the sessions:

Make note on SID and SERIAL#.
Kill the session using this sql command:

(don’t forget to change SID and SERIAL#).
On create schema process you may need to increase the size of SYSAUX tablespace.
After the process completed you can access the enterprise manager on internet browser using this URL:

You may also have…

View original post 18 more words


Oracle E-Business Suite R12 | SGA vs PGA vs NOT being a DBA!

May 7, 2018

Hi guys

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:

Processor:


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

Storage space


[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)!

regards,

rajesh

 

 


Windows Subsystem for Linux | Ubuntu | LAMP Server

April 16, 2018

Hi guys

Usually I prefer a virtual machine for all my Linux/Development requirements. Well, Microsoft is “trying” to setup a Linux subsystem within Windows 10 from a while, which is “yet” to be fully functional in many means. Many benchmarks proven that Microsoft Subsystem for Linux for has the poorest I/O which Microsoft claims, will be addressed in the future releases.

So, why someone will choose WSL against a VM which has better functionalities? From an eager inquisitive person’s perspectives, I will choose WSL for just couple of reasons and they are:

WSL could access the full hardware, when a VM has limited resources. Ie, say your computer running has 4 cores and 8GB memory, you will be limited to 2 cores for your VM (anything beyond that would cause unexpected issues) and maximum of 4GB memory. Here WSL don’t have such limitations, it could access all your 4 cores and the maximum memory & could be launched like just another win32 application!

I’ve been trying to setup WSL using both Ubuntu & OpenSUSE Leap 42.3 over my Windows 10 1709 & I had better results with Ubuntu. OpenSuse has many issues those were beyond my skills to address and resolve. Hence, I chose to stick with Ubnutu for now.

So, installation of WSL on Windows 10 recent versions were made pretty easier compared to the previous releases. A seasoned Windows user could install Ubuntu for WSL very easily by following the instructions below

1

Once rebooted, from the Windows store, search for Ubuntu or OpenSUSE and just install it! That simple it is.2

Once installed, as mentioned with the first image, one can start the WSL either from command prompt or by clicking the tile that is available with the start menu.

As a developer, I would take the pain to setup the WSL for ONLY few reasons like testing PHP application(s) those are based on MySQL database (and outta curiosity)

So next, let us see we can setup LAMP server (LAMP is an archetypal model of web service stacks, named as an acronym of the names of its original four open-source components: the Linux operating system, the Apache HTTP Server, the MySQL relational database management system (RDBMS), and the PHP programming language. The LAMP components are largely interchangeable and not limited to the original selection. As a solution stack, LAMP is suitable for building dynamic web sites and web applications) on our new WSL

You may refer to this post for a detailed information about how to install individual components like apache, MySQL and PHP. Please note, the link that I provided deals with PHP5, which is not anymore available from Ubuntu repositories, hence I chose to go ahead with PHP7.0 for my WSL instance

Issue the following command(s) to install Apache, MySQL,PHP and phpMyAdmin


sudo apt-get install apache2
sudo apt-get install mysql-server

During the installation, you will be prompted for setting up MySQL root credentials, which is mandatory in order to access the MySQL server at later stages using phpmyadmin module

For PHP, you must install libapache2-mod-php, else you would experience php pages being blank when functions like phpinfo(); are called.


sudo apt-get install php libapache2-mod-php

Once the packages are installed (without errors) you can restart apache to verify everything is working fine


sudo service apache2 restart or sudo /etc/init.d/apache2 restart

If your windows box already has IIS listening to port 80 and a Windows instance for MySQL is listening to port 3306, then you must change the ports for both apache and MySQL before restarting the services.

For changing the port of Apache server edit the following file


/etc/apache2/ports.conf

For changing the port of MySQL server edit the following file


/etc/mysql/mysql.conf.d/mysqld.cnf

Although no real show stoppers, these couple or warning/error messages could make you feel irritated whenever apache and MySQL services are started

Apache:

[core:warn] [pid 47:tid 140569079777152] (92)Protocol not available: AH00076: Failed to enable APR_TCP_DEFER_ACCEPT

Edit the file


/etc/apache2/apache2.conf

add the following line by the end of the file


AcceptFilter http none

 

Restart the apache service

MySQL

No directory, logging in with HOME=/

Edit the file


/etc/passwd

and change the MySQL user details like following


mysql:x:112:117:MySQL Server,,,:/var/lib/mysql:/bin/false

Restart all services to make sure that there are no more warning/error messages

So far so good, let us install phpmyadmin, which you will use to manage your MySQL instance


sudo apt-get install phpmyadmin php-mbstring php-gettext

During the installation you will be prompted for selecting the http server stack and password for MySQL root account. You must provide the root password that you have set during the MySQL installation at this prompt

(Refer https://www.digitalocean.com/community/tutorials/how-to-install-and-secure-phpmyadmin-on-ubuntu-16-04 for more details)

Once the phpmyadmin is installed, you need to manually enable couple of extensions and they are dealt like following:


#sudo phpenmod mcrypt

#sudo phpenmod mbstring

Restart all services


sudo service apache2 restart

sudo service MySQL restart

Example:


rajesh@rt04:/etc/apache2$ sudo service apache2 restart
* Restarting Apache httpd web server apache2 [ OK ]
rajesh@rt04:/etc/apache2$ sudo service mysql restart
* Stopping MySQL database server mysqld [ OK ]
* Starting MySQL database server mysqld [ OK ]
rajesh@rt04:/etc/apache2$

Now you can see that there are no errors or warning while starting both apache and MySQL. So let us go ahead and check phpmyadmin

34

That’s all folks!

There is no shutdown for WSL, just type “exit” and you are good. I am yet to test whether mere exiting does anything harmful to running instances of apache and MySQL. Until I find them…

Finally

 

5

I don’t really understand why, yet, many users are asking for a “way” to ssh into the Linux subsystem. The pre-installed openBDS ssh server doesn’t allow a connection from clients like putty. Hence a re-installation of SSH server is required.


sudo apt-get remove --purge openssh-server
sudo apt-get install openssh-server
sudo service ssh --full-restart

Create a firewall inbound rule if required

Now you should able to connect to WSL instance using a client like putty.

regards,

rajesh

 


PowerShell Script To Send Message To Network Computer(s)

March 28, 2018

Subodh's Blog

Some time back, we had a requirement of sending a text message to all the client computers to inform about some activity.

I have created a little script which can send a POP UP MESSAGE to the computer(s), and it will simply prompt for input, and at the time of finish it will show you all the statistics.

The script can accept a single computer name or multiple computer names separated with comma (,) or path of txt file which is containing the list of computers.

When you run this script it will ask you for below Input.

Message :- Type your message which you want to send to the computers. Like Hi, “Hello, Update are being installed on your computer, pleas wait” Etc.

Computer Name: – Type the computer name to which you want to send the message, if you type PC1, it will send message to PC1 computer.

You…

View original post 439 more words


Using _ALLOW_RESETLOGS_CORRUPTION in case of corruption: How to recover & open the database ?

March 20, 2018

Not your everyday task, yet, when nothing else you could do with a database that won’t start, give it a try. If you have valid Oracle support, then execute only after Oracle confirms.

Please note, there are no guarantees that using the “_ALLOW_RESETLOGS_CORRUPTION” hidden parameter will bring your database up and running. For example, I have made several attempts to duplicate a database in the same machine, few of the exercises were like following:

At home, I copied .dbf files from a live database and created a new database using the “_ALLOW_RESETLOGS_CORRUPTION” which was successful, when the same approach failed without any hopes on my work laptop where I have the same 10.2.0.3 to 11.2.0.4 upgraded instance.

Other hand whenever I copied the .dbf files from a shutdown database and tried to create a standby database, it always succeeded with “alter database open resetlogs”

So, don’t be over confident about recovering a database using “another” hidden parameter. If you are lucky, you may, on the other hand ONLY a proper backup will help you to recover from a significant crash.

So, start backing up your database on regular intervals. I suggest RMAN with archivelogs which helps you to backup the database without downtime.

Tales From A Lazy Fat DBA

Recently while opening a TEST RAC database we found the database down and was not available, we tried to start the database but received communication errors and left us clueless. Due to some urgent POC activity scheduled on the database we started investigating the root cause right away for error.
ORA-03113: end-of-file on communication channel

[oracle@testdbdixit ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 26 01:04:23 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to an idle instance.
SQL> startup
ORA-03113: end-of-file on communication channel

We tried to open the database in MOUNT mode and it reached to phase easily without any error.

SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area 1.6034E+10 bytes
Fixed Size 2269072 bytes
Variable Size 2449473648 bytes
Database Buffers 1.3556E+10 bytes
Redo Buffers 26480640 bytes
Database mounted.

But the recovery of any kind got failed because of the…

View original post 632 more words