Oracle Application R12 | Print custom report using CUSTOM.pll

Hello guys,

Happy New Year!

Today we will see how we can use CUSTOM.pll for enabling special menus and printing a custom report by invoking the special menu that we activate using the library.

We are going to use Oracle’s seeded form “POXPOEPO” AKA Purchase Orders.

Please make sure that you make a backup for the CUSTOM.pll prior making below said modifications. CUSTOM.pll is “always” found uner $AU_TOP/resources folder

We’ll enable the SPECIAL15 menu item for the exercise.

Load up CUSTOM.pll using Oracle Forms Designer & make sure you are connected to database before loading the library file.

Attach APPCORE, APPCORE2 libraries with your copy of CUSTOM.pll

Attach FNDCONC.pll library for calling printing related activities

Your CUSTOM.pll should look like the above after attaching said libraries.

Add the block as seen with the image by the bottom of your CUSTOM.pll package body.

BEGIN
  IF (event_name = 'WHEN-NEW-FORM-INSTANCE') THEN
     IF (l_form_name = 'POXPOEPO' AND l_block_name = 'PO_HEADERS') THEN	 
    -- 	fnd_message.debug(l_form_name);
     	app_special2.instantiate('SPECIAL15', '&Print', 'prord',TRUE,'LINE');
     END IF;
  END IF;
  
END;

Now you can proceed with writing code for what happens when “SPECIAL15” event happens

BEGIN
  IF (event_name = 'SPECIAL15') THEN
     IF (l_form_name = 'POXPOEPO' AND l_block_name = 'PO_HEADERS') THEN	 
     	print_po(name_in('PO_HEADERS.ORG_ID'),name_in('PO_HEADERS.SEGMENT1'));
     	--fnd_message.debug('Will Print This Order');
    --app_special2.instantiate('SPECIAL15', '&Print', 'prord',TRUE,'LINE');
     END IF;
  END IF;
  
END;

Here, I am calling a procedure that I defined with CUSTOM.pll for handling print requests.

and the package body is as following (not another image, I am going to save some efforts for you)

procedure print_po(p_org_id IN NUMBER, p_order_number IN VARCHAR2) is
		l_ord_num               NUMBER := 0;
		l_ord_type_name         VARCHAR2 (240);
		l_req_id_Rep            NUMBER;
		l_request_completed     BOOLEAN := FALSE;
		l_req_phase             VARCHAR2 (20);
		l_req_status            VARCHAR2 (1000);
		l_req_dev_phase         VARCHAR2 (1000);
		l_req_dev_status        VARCHAR2 (1000);
		l_req_message           VARCHAR2 (1000);
		l_conc_mgr_status       NUMBER;
		p_call_stat             NUMBER;
		p_activep_stat          NUMBER;
		
		l_order_category_code   NUMBER;
		l_report_name       VARCHAR2 (40);
		l_ret_report_name       VARCHAR2 (40);
		l_req_id 								NUMBER;
		l_order_type_name				VARCHAR2(30);
		
		--
		
		l_signing_person VARCHAR2(240);
		l_person_designation VARCHAR2(240);
		
		
BEGIN
--fnd_message.debug('Will Print This Order');

/*This is a custom procedure that checks whether the concurrent manager is online or not, you can safely comment this line
   --Check the status of Concurrent Manager
   apps.xx_conc_mgr_status_p (p_call_stat, p_activep_stat);


   IF p_call_stat <> 0
   THEN
      fnd_message.set_string ('Concurrent Manager Status Unknown');
      fnd_message.show;
   ELSE
      IF p_activep_stat > 0
      THEN
         NULL;                       --Message('ICM is running' || l_activep);
      ELSE
         fnd_message.set_string (
            'Concurrent Manager is down, Please try printing the invoice later');
         fnd_message.show;
         RAISE form_trigger_failure;
      END IF;
   END IF;

   --Checking concurrent manager status end----
 --  MESSAGE ('Concurrent manager status: up & running');
*/

 BEGIN
/* I am picking up the reports names (concurrent_program_name from FND_CONCURRENT_PROGRAMS_VL view as we have different layouts for companies
you can set up a value for l_report_name while variable is defined
---
--
   SELECT execution_file_name,STRING1, STRING2
     INTO l_report_name, l_signing_person, l_person_designation
     FROM omspoprintreg
    WHERE 1 = 1 AND organization_id = p_org_id
          AND TRUNC (SYSDATE) BETWEEN start_date_active
                                  AND NVL (end_date_active, SYSDATE);
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      fnd_message.set_string (
         'No reports defined for this type of transaction, Please contact OM Super User');
      fnd_message.show;
      RAISE form_trigger_failure;
END;

--   FND_MESSAGE.DEBUG('Printing Order '||p_org_id||' order number '||p_order_number);
*/
--
--   
   l_req_id :=
      fnd_request.submit_request ('PO',
                                  l_report_name,
                                  NULL,
                                  SYSDATE,
                                  FALSE,
                                  P_ORG_ID,
                                  P_ORDER_NUMBER, 
                                  l_signing_person,
                                  l_person_designation,
                                  chr(0));
--You cannot setup :SYSTEM.MESSAGE_LEVEL within CUSTOM.pll, hence use COPY 
--to suppress messages like 'Two records saved'

   COPY('25','SYSTEM.MESSAGE_LEVEL');
   COMMIT;
  
  -- FND_MESSAGE.DEBUG('Your request id is '||l_req_id);


   l_request_completed :=
      fnd_concurrent.wait_for_request (request_id   => l_req_id,
                                       INTERVAL     => 1,
                                       phase        => l_req_phase,
                                       status       => l_req_status,
                                       dev_phase    => l_req_dev_phase,
                                       dev_status   => l_req_dev_status,
                                       MESSAGE      => l_req_message);
  --:SYSTEM.Message_Level := '25';
  COMMIT;
  editor_pkg.report (l_req_id, 'Y');

end print_po;

That’s it!

Now copy the CUSTOM.pll to $AU_TOP/resources & compile it

frmcmp_batch module=CUSTOM.pll userid=apps/apps output_file=CUSTOM.plx module_type=LIBRARY batch=yes compile_all=special

If you don’t have syntax errors or other, you must have the fresh CUSTOM.pll

Please make sure that no users are currently online while you are compiling the CUSTOM.pll (This is only applicable to cases where the CUSTOM.pll is already deployed for different forms)

Log on to the instance, access Purchase Orders form & you should see a new menu item under “Tools” menu enabled

While CUSTOM.pll implements “editor_pkg.report” by attaching FNDCONC.pll, FORMS personalization will fail to provide the same functionality as most of the seeded forms do not have FNDCONC library attached to them by default. If you don’t want to use editor_pkg.report to open the completed report, you may create a database level procedure to submit the request(s) and call the same against SPECIAL(n) menu item through FORMS personalization.

Enjoy!

rajesh

Advertisements

Oracle Application R12 | The Function Is Not Available Under The Responsibility

Hello guys

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

https://knoworacle.wordpress.com/2012/11/03/the-function-is-not-available-under-the-responsibility/

regards,

rajesh

Oracle E-Business Suite | Should you clone on your desktop using Virtual Machine?

Hello guys

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)

regards,

rajesh

 

Oracle Application R12 | Error: Missing ormi[s] host port

Hello guys

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

https://aboutsoa.wordpress.com/2009/06/22/error-missing-ormishostport/

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) 

script returned:
****************************************************
ERROR : Timed out( 100000 ): Interrupted Exception

You are running adoacorectl.sh version 120.13

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

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4

::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

When Linux 5 have | Glad that I had a machine to cross check

127.0.0.1 localhost.localdomain localhost

::1 localhost6.localdomain6 localhost6

Immediately I decided to give it a try, I chopped the unnecessary strings from the hosts file & started the application tier & tried to shutdown!

That was it, no more
“Error: Missing ormi[s]://<host>:<port>”

No hacking of the Context files or disabling the IPV6 protocol is required, which could complicate SSH tunnel etc (as per Linux guys, I am not a Linux guy yet ;) in those means)

So, now you have a working solution, you have your IPV4 & IPV6 both enabled & an instance shuts down as you expect. Well, party time guys!

regards,

rajesh

Oracle Application | Inventory | Cannot Create Locators Using The INV_LOC_WMS_PUB API

Hi guys

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:

  1. Setup -> Flexfields -> Key -> Segments
  2. Query for “Stock Locators” Flexfield title
  3. Check whether “Allow Dynamic Inserts” checkbox is selected. If not, select it and save. Now onwards you should able to run the API successfully.

stocklo

Sample API Script (Adjust to your environment, tested against R12 12.0.6, Database:11gR2)

[code language=”sql” gutter=”false”]

SET DEFINE OFF;
SET SERVEROUTPUT ON SIZE UNLIMITED;

Declare

— Common Declarations
l_api_version NUMBER := 1.0;
x_return_status VARCHAR2(2);
x_msg_count NUMBER := 0;
x_msg_data VARCHAR2(2000);

— 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;

BEGIN

— MTL_ITEM_LOCATIONS

— Get the user_id
SELECT user_id
INTO l_user_id
FROM fnd_user
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
FROM fnd_responsibility_vl
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
DBMS_OUTPUT.PUT_LINE(‘=======================================================’);
DBMS_OUTPUT.PUT_LINE(‘Calling INV_LOC_WMS_PUB.CREATE_LOCATOR’);

—fnd_profile.put(‘MFG_ORGANIZATION_ID’,245) ;

INV_LOC_WMS_PUB.CREATE_LOCATOR
( 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(‘=======================================================’);
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);
END LOOP;
END IF;
END IF;
DBMS_OUTPUT.PUT_LINE(‘=======================================================’);
END;

[/code]

 

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

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

Oracle EBS | Restoring database from RMAN backup

Hi guys

I always wondered how the heck a RMAN backup could be restored to Oracle EBS environment. Last two years, I spent considerable time learning Oracle database technology, especially about the backup and restore procedures (learning a lesson after a disaster)

We have the following approach towards Oracle EBS backup:

Daily RMAN hot backup (Friday’s exempted as we do a full cold backup), with 7 days retention policy.

Weekly cold backup for both application and database tiers

Cold backup is written to a backup server, then copied to an external driver (USB attached). This way the cold backup remains in 3 different places.

Usually whenever a fresh clone requested, we were building them against the latest cold backups. Many cases a Friday cold backup clone is NOT what the consultants want, they need much recent data on a Thursday! This has forced me to start pondering over google to find a properly written document to support my own experiments and not like other areas of database, the availability of decent documents were scarce!

So I approached one of my APPS DBA friends, who was always there to help me & readily made me available with a document that he used for his specific environment. After some tweaks I was able to successfully restore the RMAN BACKUP to Oracle EBS environment without troubles (Well, the time it took were much more than that from a cold backup cloning)

I assume, if you are going through this write-up, you have the below setups:

  1. Single instance Oracle EBS R12 Deployment
  2. You are using Oracle 11g R2
  3. You are on LINUX
  4. You already have a cloned instance, properly configured SPFILE & listener services
  5. You have environment parameters set and called through .bash_profile(s)

Steps

copy RMAN files from Production server to TEST server using SCP. The document asks me to create the same path structure, haven’t tried other options yet. For example, the Production server has /u03/RMAN/DAILYBKP for the daily RMAN backups, hence I made the same available with TEST instance as well. The below command copies the RMAN backups those are one day older from the current date to TEST instance.

[code language=”text” gutter=”false”]time scp `find -maxdepth 1 -type f -mtime -1` root@erptest.xyz.com:/u03/RMAN/DAILYBKP[/code]

Once the files are copied, shutdown both application and database instances respectively.

Delete the .dbf files (data files) from the “apps_st/data/” folder.

Example, my TEST server has the data files here “/u01/oratest/TEST/db/apps_st/data”, so I did a rm -rf * here to remove all the files available in this folder. We’ve approximately 500GB data files in this folder.

Once the files are deleted, open a terminal and switch to Oracle database user. Source the environment incase if the .bash_profile is not set to.

Issue “sqlplus / as sysdba” command

Startup the database in not mounted state

startup nomount

Start another terminal and switch to Oracle database user. Now let us start RMAN recovery

Before attempting the RMAN duplicate, make sure the ORACLE user has full ownership on the backup folder. If not, RMAN will start reporting the controlfile not found & other associated errors and the duplication will fail.

I’ve a cronjob, executed as root to copy the rman files everyday to TEST instance, hence the default ownership on the backup folder is set to root, that I realized today (4th Feb 2019) while trying to duplicate the database!

rman auxiliary /

That should connect you to RMAN duplicate database mode.

Now, the most important few things. The RMAN backup will have datafile names associated with path information. For example, my production server keeps the datafiles in the absolute path “/u05/oraprod/PROD/db/apps_st/data/”, where in my TEST server has the cloned database instance over “/u01/oratest/TEST/db/apps_st/data”. So we will have to rename the database files prior they are restored. The RMAN run command set that you will create will take care of renaming the files.

Create a RMAN command set like below (adjust accordingly)

My production instance database name is “PROD” and my TEST instance name is “TEST”, so the duplicate database command should be adjusted accordingly.

[code language=”text” gutter=”false”]
run
{
ALLOCATE AUXILIARY CHANNEL c1 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL c2 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL c3 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL c4 DEVICE TYPE disk;
duplicate database to “TEST” backup location ‘/u03/RMAN/DAILYBKP’ nofilenamecheck
db_file_name_convert=(‘/u05/oraprod/PROD/db/apps_st/data/’,’/u01/oratest/TEST/db/apps_st/data/’)
LOGFILE
GROUP 1 (
‘/u01/oratest/TEST/db/apps_st/data/redo01a.log’,
‘/u01/oratest/TEST/db/apps_st/data/redo01b.log’
) SIZE 1000M ,
GROUP 2 (
‘/u01/oratest/TEST/db/apps_st/data/redo02a.log’,
‘/u01/oratest/TEST/db/apps_st/data/redo02b.log’
) SIZE 1000M ,
GROUP 3 (
‘/u01/oratest/TEST/db/apps_st/data/redo03a.log’,
‘/u01/oratest/TEST/db/apps_st/data/redo03b.log’
) SIZE 1000M ,
GROUP 4 (
‘/u01/oratest/TEST/db/apps_st/data/redo04a.log’,
‘/u01/oratest/TEST/db/apps_st/data/redo04b.log’
) SIZE 1000M ;
RELEASE CHANNEL c1;
RELEASE CHANNEL c2;
RELEASE CHANNEL c3;
RELEASE CHANNEL c4;
}[/code]

You may keep the RMAN RUN commands saved in a text file for future use!

At the RMAN prompt execute the newly created RMAN RUN command set. It is going to be a long wait, the bigger the database, the more time you wait for the restoration to happen.

Wait until the RMAN processes are finished. You should be back to RMAN prompt

From a SQL Plus session, shutdown the database

Now restart the database at mount state.

startup mount

Disable Archive logging prior doing anything else

SQL> alter database noarchivelog;

Shutdown the database. Now we will do the post cloning activities against the database instance.

Switch to the appsutil/clone/bin over $ORACLE_HOME path. Example “/u01/oratest/TEST/db/tech_st/11.2.0/appsutil/clone/bin”

Issue the following:

perl adcfgclone.pl dbTechStack

Input details for your instance and complete the post cloning activities. This shouldn’t take much time!

Once the post cloning activities are completed, switch to ..install/TEST_erptest folder, example: “/u01/oratest/TEST/db/tech_st/11.2.0/appsutil/install/TEST_erptest”

Start SQLPlus from this location and execute “adupdlib.sql”

SQL> @adupdlib.sql so

Once the procedure run finishes, issue COMMIT

Commit;

Run Autoconfig for your Application instance and you should be online soon.

Oracle Applications R12 | Java applet font scaling issues

We are using Oracle applications R12 12.0.6 with JDK 6.0 while most of the clients are using JRE 7/8 based on different business requirements (With full knowledge that there would be unexpected results due to the compiled versions). We’ve observed a peculiar issue with the JRE applet once after the upgrade to Windows 10 version 1803. The Java applet started showing too small fonts while launched from displays with high DPI resolutions.

Digging in brought me to the following discussion:

https://superuser.com/questions/988379/how-do-i-run-java-apps-upscaled-on-a-high-dpi-display

Although there are multiple recommendations, for EBS environment, Please follow the below instructions:

Launch the JAVA applet by opening a forms based module.

Open task manager and locate “Java(TM) Platform…”, right click and open properties. This will open the properties for “jp2launcher.exe”. Click on “Compatibility” and click on “Change high DPI settings”. Now check “Override high DPI scaling behavior. Scaling performed by:” to “Application”. Refer the image for more details.

Once the changes are made to the properties of the executable, restart the box. It could be specific case, the changes were not applied until a restart for me.

Although it fixes the font scaling issues, the toolbar area of the Oracle forms are remains scaled with pretty small icons. I am yet to find out a fix for this minor annoyance. May be quite soon.

 

regards,

 

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

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:

[code language=”text” gutter=”false”]

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

[/code]

Storage space

[code language=”text” gutter=”false”]

[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

[/code]

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