It looks like I am getting something new everyday to blog…the latest is from Oracle Application R12 once after I added a new responsibilities to few users.
The Functions those are listed under new responsibility from the HTML page will not launch, instead a popup window appears with the statement “The Xyz Function Is Not Available Under The abcd Responsibility”
I recently had a nightmare with a custom form, that was revamped almost after 6 years of usage. Although the compiling on Production instance doesn’t have any issues, only portions of the form would load & the only few elements displayed were totally misaligned and the cells looked like just a plain straight line…
After many failed attempts, I tried to clear the cache, which we didn’t from a long time. The culprit was the cache. After releasing the cache, I recompiled the form & everything was fine.
With the above issue also, our issue was with the cache. After releasing the cache, the users were able to launch the form from the HTML page itself. If, this didn’t resolve your issues, have a look here
A long thread name? Well the topic is vast, hence the long name. I’ve been dealing with Oracle EBS or applications R12 from last 9 years. Initially I was ONLY developing for the new infrastructure, that gradually changed to taking care of the whole instance.
Painfully, however definitely I did familiarize myself with Linux (RHEL) & the database, exposing myself to building systems with copies of EBS running for development & testing.
Our instance is approximately 650GB as on date, including both application stack and database & a cold backup is hardly 125GB in tar balls. I have attempted & succeeded to build the cloned instances on my home desktop machine many times, however the performance was a huge issue, forcing me to discard the setups quite often.
This time, I decided NOT to discard as my new desktop at home is a beast compared to my previous machine, that was neither less a best ;) & to figure out “something” that will address the “performance” bottleneck.
I created a new VM using Oracle VirtualBox with following specs
6 processors, 20GB memory & 2 fixed size VDI files (120GB, 600GB) respectively for application and database repositories. I was aware of a limitation already, I was setting up the VDI files on a Western Digital Green series 2TB drive! which spins at 5400RPM!
Well, everything went smooth & and I had the instance up and running in couple of hours time & this time, the response of the instance was awesome. I even boasted about finally winning over the “biatch” to my team & sat back feeling “too proud” for the moment.
Next day (Month End 30th April 2019)
I am all excited after figuring out a way to flush GNOME desktop environment & replace it with Xfce & new tricks…
Started the VM at home, started the EBS instance and tried to access the instance from the same machine. I couldn’t even get the login page…something was gravely wrong. I decided to check the performance monitor and found the following:
Slowly I was forced to recognize the terror! The Standard Concurrent manager was configured to process 25 requests at the same time with a cache of 5 & 30 seconds sleep between the requests. Our month end has a number of scheduled jobs + Gather schema statistics in the queue. My VM was breaking up with the I/O. My 2TB storage oriented disk was NOT spinning fast enough to provide the data for the processes & I was left with the BIGGEST question of the hour “Now what?”
I stopped the Concurrent manager, adjusted the processes to 5 for Standard manager & restarted the instance. Left the instance running whole night and 1st May morning, the instance was back to normal performance as whole the scheduled jobs were finished during the night.
Next day I added one 1TB SSD & moved the application & database VDI files over to it. I was able to get the login screen within 2-3 seconds once after the application started from the VM. I submitted number of create accounting and other resource hungry jobs, which were completed in few seconds time…
Now, my setup is ONLY for the sake of it. It doesn’t have many users, it is idle most of the time & almost every day I shutdown the desktop machine after a day’s usage. This might not be the scenario at a real TEST environment. You may need to implement archive logging & RMAN, those all requiring more space & faster access to storage. A Desktop has less resources & the ONLY positive element you are going to live with is the pleasure of building it & knowledge gained while fixing few new issues.
So, can you build a performance oriented R12 using Desktop environment, the short answer is yes. Does it worth the efforts? Well, definitely YOU are the ONE who have to answer it.
Follow the space & soon I will post a thread explaining the entire exercises. If you are in a hurry, you may refer this
The above article loosely explains how to clone R12 instance on Linux 7. However the same could be followed for Linux 6 as well (both RHEL & OEL)
One of the most awkward things that keeps on happening in my life is, I land on my posts/comments those were posted years back, which were definite solutions to few perplexing situations like “Error: Missing ormi[s]://<host>:<port>”
Year 2015, I posted a comment with the below thread
explaining a possible solution to the error in attention…but I missed it.
Few days back I was setting up a VM with Oracle Applcations R12 to test SSD based storage device & landed on the same error! I didn’t have a clue…last four days I was going through each every other notes those I made, was scavenging through tones of blog posts and Oracle support documents, trying out suggestions to overcome the error below:
Executing service control script: …../admin/scripts/adoacorectl.sh stop Timeout specified in context file: 100 second(s)
We had our Oracle Application R12 implemented on RHEL 5 & later cloned the same instance on Oracle Linux 6 for TESTs. One of the major differences between RHEL 5 & 6 was the native support for IPV6 with Linux 6. Not just R12, many Oracle products WERE/ARE not yet completely compatible with IPV6 & the first thing geeks ask you to do is to disable IPV6 (Even today!)
I did, trust me I did it on the interface, I did it in the sysctl.conf & wherever possible & still had the same ormi error, whenever I tried to shutdown the application services. The temporary solution was to re-run the stop all script & finally the script completes with “0” error.
I was NOT happy & I wanted my instance to shutdown gracefully, over a single attempt. I “knew” that Oracle was very sensitive to Network stack, a screwed up hostfile, DNS, firewall…anything that is related to network could cause these kind of issues. So I decided to go through each one of them. So, first was the hosts file.
The default /etc/hosts file from Linux 6 has the entries like below
It looks like, setting up the compatibility for Windows 7 solves the Reports Builder shaking, freezing behavior to a greater extend. Going on with the “Fix” as NOTHING else could be done with a product that is NOT anymore supported by Oracle.
I “trust” Microsoft and I do update my Work/Home/VMs regularly. Ahum! 99.9% of the times I never had to go back and fix anything, that .1% times, things went awfully bad.
I cannot confirm which update, certainly one update within last 2 months have made Oracle Reports Developer 10g/6i loads messier than earlier. Now both version of reports developer will NOT load the layout designer properly.
The interesting factor about the layout designer is:
It hangs more often when you have multiple reports opened from a single instance of the reports developer & you are switching between the layouts.
For example, if you opened Report 1’s layout & closed it, then opening the Report 2’s layout arrests the designer & all the toolbar icons and rest go astray (as you could see with above image) which will force you to end task the developer using Task Manager/or other known measures.
It is irky, shaky, yet you can do your work with multiple sessions of reports developer opened with only one report loaded. There are no guarantees that this IS the ultimate solution. I often have to terminate the report builder and restart it to do my development(s) :(
I’ve more news for you. I did a reinstall routine of Patch 3 for 10g developer & remembering Windows 7 was the last OS certified with the developer suite, changed the compatibility for the Report Developer and set it as Windows 7. I am not sure whether it acts as a mediator, my report builder is much stable as I am jolting these few lines. Keeping fingers crossed.
I am kind of disappointed, to be frank. After long planning, I decided to post about installing Oracle 18c on Windows 10 & started hearing about 19c. Well, shit happens and we will continue with what we were planning to do.
Before getting in to the real business, let me remind you. Oracle stack depends upon hell loads of VC++ components, and missing those packages could make things pretty difficult on Windows. So, as a thumb rule, please make sure that you have all available VC++ packages installed with your Windows box. Never forget that .Net 3.5 is also required by multiple Oracle software on Windows. Finally, do update your Windows.
You may refer this image for the VC++ & .Net components those are installed in my Windows 10 computer.
You can download Oracle 18c installation media for Windows 64Bit from the following link
Please spend a moment to read what Oracle says just below the media link, “Do not install Oracle Database from a directory that contains a space character in it”
That means, while extracting the downloaded ZIP file, make sure you are extracting the archive to a folder like “D:\Oracle18c” NOT “D:\Oracle 18c” or “D:\Oracle Database 18c”
Oracle never liked “space” character in the directory name & if you are going to ignore this, you are going to have true troubles waiting for you mate.
What I have came to conclusion about Oracle 18c installation is:
Oracle is NOT anymore copying all the source files to a new destination. For example, usually when you install a software on Windows, there is installation source, from which necessary files are copied to a new destination (installed folder). This time Oracle is approaching the installation in a different fashion.
It ONLY configures the database.
That means, Once you extract the media, say to a folder called “D:\Oracle18c”, this is the same folder where the database will be installed! You will only be asked for Oracle base location, where the diagnostics and others will be configured.
The above is MY PERSONAL observation & I am yet to refer the installation documents from Oracle for clarify against what I found. Feel free to prove me wrong.
With the below installation example, I have extracted the 18c source media to a folder called “D:\Oracle_Installers\18c”, which will be the installation folder also
Run “setup.exe” as “Administrator”, that brings up a console sort of Windows that lists the installation progress (minimalistic)
We’ll create a database along with the installation, which is quick and effective (for this TEST)
Although we are installing the software on Windows 10, We will select “Server class” for System Class to “experience” the load stress.
Usually I choose “Advanced Install”, so that I can minutely access the different options available for the database like character set, memory etc
Database Edition should be “Enterprise” to experience the same “Cloud” version capabilities.
I’ve one Windows user account “Oracle” that I use for Oracle installations and setup. I suggest you also to create a non-administrator windows user “oracle” or something equivalent specifically for Oracle software.
As I have mentioned in the beginning, “The Software directory is the Oracle Database home directory”, ie, the folder from which you start the setup.exe will be the Database home directory. The path that specified by you will be the Oracle base & usually the diagnostics elements will be placed within.
As a rule, I always select the same password for all vital accounts & the pattern is something like ‘Abcxyz123’. For a TEST lab, this saves me unwanted complexities & less documentation. For a production instance, please follow Oracle’s recommendations.
My laptop boasts one i7 8th generation processor, 16GB DDR-4 memory and 512GB Samsung 860PRO SSD. Yet, the installation takes it’s own time. So if you have less hardware configurations please wait patiently.
You know what to do with JAVA security prompts!
Once the installation over, you will be provided the link to Oracle Enteprise Manager Database Express, which is a minimalistic EM for the database. You can log in and view the database details and interact the database parameters through this interface.
I know you are smart enough to find out that I altered the below image a little bit…Well it looks something like this…
My laptop doesn’t have FLASH installed, hence I am using Internet Explorer for accessing the EM. I have to check whether I can access the EM using Firefox after installing Flash.
That’s all folks. The installation is pretty straight forward other than for the installation folder/directory design part. For a production environment, you may need to truly plan how the folder/directory name is going to be.
Ah, finally! My laptop is lagging beyond expected levels with the database services running.
Have comments & suggestions, please let them come in.
I confirm, I am not a DBA, yet my last 10 years with Oracle EBS experiments have given me opportunities to learn few facts those are not documented generally.
A general approach towards deploying a new setup/custom development for EBS is 1st developed and tested thoroughly for which a business needs testing environment. To provide such, a DBA “clones” or duplicates the existing environment and this process is called cloning.
A majority of the environments may note have the hardware with same configurations as it is available for the Production, this causes the cloned instances to perform poorly, thus making the entire testing pretty painful.
During the initial stages of learning, I had tough time digesting our part time DBA’s arguments and reasoning for the slowness, tied to the hardware limitations. Our TEST server has dual processors and 20GB memory with more than 3 TB storage that is hooked up to a 1GB network interface.
As my confidence kept on building, I started exploring the database setups and realized the DBA had only allocated 2GB for SGA & 1GB for the PGA. While questioned, he gave me all possible excuses like “Well, your instance doesn’t require more” and so on.
We got rid of him & had another DBA in the due course, who was far better and more experienced handling bigger environments. Yet we had this nagging issues related to the test instance being slow, lagging beyond our extended patience & I started asking oracle communities questions about how to speed up things.
“Not being a DBA” had it’s own limitations. Many instructions were beyond my understanding, many sounded impractical or illogical & finally one day I decided to setup the R12 (12.0.6, 11gR2 database) with 3GB for both SGA, PGA following an old discussion available with asktom.
That was the beginning. Our TEST instance performance improved to a level that, it become difficult to distinguish between the production and test instances when the Production instance is a beast (minimum for our environment)
Although, the manual SGA/PGA settings resolved one of the major performance related issues (login page load, opening large forms etc), I was still NOT convinced by the performance (Queries taking long time..). For some other reasons, I had to restart the TEST server (Which usually happens once in many months time) & was amazed to see that the performance gains were distinguishable from earlier times. As soon as I tried to access the instance, the login screen was loaded (not ignoring the cache mechanisms) faster, so did rest of the accesses. Forms based interfaces were loading faster and the queries were returned instantly etc.
I opted for another cloning. Dropped the entire R12 instances and repeated the cloning, adjusted the database parameters and “RESTARTED” the physical server, started the database and application tiers and insured that the performance gains I observed were not just a hit and miss, actually a permanent HIT that exists.
I continued my experiments further. My next attempt was to upgrade the SGA, PGA with more memory and this time I opted for 4GB for each sector. After the database & application restart I realized the poor performance. I was expecting the instance to be faster! Instead I was dealing with an instance that was as horrible as possible prior to my 3GB/restart patterns.
So I rolled back to 3GB SGA, PGA setups and was living with it until our finance consultant asked me for a fresh clone with most recent data. This time I decided to duplicate database using RMAN rather than a traditional cold back restore and cloning.
As RMAN duplicate database doesn’t require me to alter the database (as the spfile is already available), all I needed was to run the “adautoconfig” from the application tier and go online. I felt something awkward
Although the instance was “Okay”, there were few things which were NOT AT all okay. The database alert log was showing redo log files related issues. I checked the redo log files, things were looking Okay against the documents those I were referring to do the RMAN duplicate database…yet
I took the application tier offline and altered the database like following:
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
Restarted database and application tiers & boom, the instance was once again giving me the buttery smooth experience. I ain’t sure, whether it is technically correct, it was all about executing “adautoconfig” against both tiers after database parameter changes. I will ask Oracle community soon to confirm it.
Now, all the above experiments with an obsolete release of Oracle application R12 (12.0.6) over an OS (Linux 6) that’s not supported were performed over many months time. Lack of true technical training must be one of the reasons contributing towards painful experiences.
Well, whatever, today I feel like a winner. Just because I am able to host an instance of the application over a hardware that was labelled obsoleted just because someone didn’t know how to fine tune an instance of Oracle application, even though he is certified to do that!
If you are like me, not certified, a jack of everything, who loves to keep on experimenting, do give the above a try & post me your experiences!
Currently we are developing a small custom module better controlled sub-inventory transfers. This solution involves an approach as described below:
Our engineering division technicians will receive tools from main warehouse & each technician will have a store locator automatically created when the tools are issued against them for the 1st time.
So the requirement was to create stock locators under the main sub-inventory automatically while the issue happens.
We opted to use INV_LOC_WMS_PUB public API to create stock locators and came across an error, that keep on giving us the below error:
‘Could not create locator.’ and the return status was ‘U’. It looks like the API is not widely used, hence there were hardly many references available online & all we had was the Oracle support knowledge base. Unfortunately most of the documents were not referring obsolete versions like 12.0.x & we decided to go through each and every other document that was dealing with the API.
The we came across a document “Cannot Create Locators Using The INV_LOC_WMS_PUB API (Doc ID 283472.1)”, which had the correct solution for the issues we were facing with the API.
Please note, the below instructions ONLY deal with the situations when the return status from the API is “U”
Using one of the INV super user responsibilities, navigate to:
Setup -> Flexfields -> Key -> Segments
Query for “Stock Locators” Flexfield title
Check whether “Allow Dynamic Inserts” checkbox is selected. If not, select it and save. Now onwards you should able to run the API successfully.
Sample API Script (Adjust to your environment, tested against R12 12.0.6, Database:11gR2)
SET DEFINE OFF;
SET SERVEROUTPUT ON SIZE UNLIMITED;
-- Common Declarations
l_api_version NUMBER := 1.0;
x_msg_count NUMBER := 0;
-- WHO columns
l_user_id NUMBER := -1;
l_resp_id NUMBER := -1;
l_application_id NUMBER := -1;
l_row_cnt NUMBER := 1;
l_user_name VARCHAR2(30) := 'ABC';
l_resp_name VARCHAR2(50) := 'XYZ INV Super User';
-- API specific declarations
x_inventory_location_id NUMBER := NULL;
x_locator_exists VARCHAR2(1) := NULL;
-- Get the user_id
WHERE user_name = l_user_name;
-- Get the application_id and responsibility_id
SELECT application_id, responsibility_id
INTO l_application_id, l_resp_id
WHERE responsibility_name = l_resp_name;
FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_resp_id, l_application_id);
dbms_output.put_line('Initialized applications context: '|| l_user_id || ' '|| l_resp_id ||' '|| l_application_id );
-- call API to update material status
( x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, x_inventory_location_id => x_inventory_location_id
, x_locator_exists => x_locator_exists
, p_organization_id => 309 --245
, p_organization_code => '202' -- 'KWH'
, p_concatenated_segments => 'EMP.2716.' --'A5.A5.A5..'
, p_description => 'RAJESH VARGHESE'
, p_inventory_location_type=> 3 --Type Storage Locator
, p_picking_order => NULL
, p_location_maximum_units => NULL
, p_SUBINVENTORY_CODE => 'TOOLS'
, p_LOCATION_WEIGHT_UOM_CODE => NULL
, p_mAX_WEIGHT => NULL
, p_vOLUME_UOM_CODE => NULL
, p_mAX_CUBIC_AREA => NULL
, p_x_COORDINATE => NULL
, p_Y_COORDINATE => NULL
, p_Z_COORDINATE => NULL
, p_PHYSICAL_LOCATION_ID => NULL -- required when creating logical locators
, p_PICK_UOM_CODE => NULL
, p_DIMENSION_UOM_CODE => NULL
, p_LENGTH => NULL
, p_WIDTH => NULL
, p_HEIGHT => NULL
, p_STATUS_ID => 1
, p_dropping_order => NULL
, p_attribute_category => NULL
, p_attribute1 => NULL
, p_attribute2 => NULL
, p_attribute3 => NULL
, p_attribute4 => NULL
, p_attribute5 => NULL
, p_attribute6 => NULL
, p_attribute7 => NULL
, p_attribute8 => NULL
, p_attribute9 => NULL
, p_attribute10 => NULL
, p_attribute11 => NULL
, p_attribute12 => NULL
, p_attribute13 => NULL
, p_attribute14 => NULL
, p_attribute15 => NULL
, p_alias => NULL );
DBMS_OUTPUT.PUT_LINE('Return Status: '||x_return_status);
DBMS_OUTPUT.PUT_LINE('x_locator_exists: '||x_locator_exists||' x_inventory_location_id:'||x_inventory_location_id);
IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
DBMS_OUTPUT.PUT_LINE('Msg Count:'||x_msg_count||' Error Message :'||x_msg_data);
IF ( x_msg_count > 1 ) THEN
FOR i IN 1 .. x_msg_count LOOP
x_msg_data := fnd_msg_pub.get ( p_msg_index => i , p_encoded =>FND_API.G_FALSE ) ;
dbms_output.put_line ( 'message :' || x_msg_data);