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
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.
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
I’m posting a sample script for creating suppliers, sites and contacts. I’ve referred multiple sample scripts and believe the below code block is a fine tuned one, however standing refinement at all levels. Please note, I haven’t added the API block for creating banks for suppliers. Will, and update the scripts as I make advancements.
[code language=”sql” gutter=”false”]
/* Formatted on 10/5/2015 11:12:16 AM (QP5 v5.163.1008.3004) */
SET DEFINE OFF;
SET SERVEROUTPUT ON;
BEGIN
–Please note: This API was tested against Release 12 (12.0.6)
–You are warned against undesired results, if tried against unsupported application releases
— Supplier MUST have a global level payment method
— So that individual companies can defer the default payment method while sites are created
— I have tried the following @ site levels, didn’t work until at supplier level assigned. You may post corrections with
— Comments section
— if the Payable System setup is set automatic numbering for the suppliers (table ->AP_PRODUCT_SETUP Column -> SUPPLIER_NUMBERING_METHOD = ‘AUTOMATIC’)
— You can get the next number from column NEXT_AUTO_SUPPLIER_NUM
— if you are following manual numbering (Alpha Numeric )
— p_vendor_rec.segment1 :=’865′; –(insert non duplicate number, in case if the supplier numbers are not fetched from a sequence, check your setups)
— We do have an automatic numbering for suppliers, hence the below block is used
— If your setups are not as explained above
— Comment from BEING until p_vendor_rec.segment1 := p_vendor_number;
BEGIN
Select NEXT_AUTO_SUPPLIER_NUM into p_vendor_number from AP_PRODUCT_SETUP
where SUPPLIER_NUMBERING_METHOD= ‘AUTOMATIC’;
EXCEPTION
WHEN NO_DATA_FOUND then
local_failed_at := ‘NUMBER’;
RAISE local_exception;
END;
pos_vendor_pub_pkg.create_vendor_site (
— ——————————
— Input data elements
— ——————————
p_vendor_site_rec => l_vendor_site_rec,
— ———————————
— Output data elements
— ———————————
x_return_status => lc_return_status,
x_msg_count => ln_msg_count,
x_msg_data => lc_msg_data,
x_vendor_site_id => ln_vendor_site_id,
x_party_site_id => ln_party_site_id,
x_location_id => ln_location_id);
IF (lc_return_status <> ‘S’)
THEN
IF ln_msg_count > 1
THEN
FOR i IN 1 .. ln_msg_count
LOOP
DBMS_OUTPUT.put_line (
SUBSTR (FND_MSG_PUB.Get (p_encoded => FND_API.G_FALSE), 1, 255));
END LOOP;
END IF;
local_failed_at := ‘SITE’;
RAISE local_exception;
ELSE
DBMS_OUTPUT.put_line (‘Vendor Site Id: ‘ || ln_vendor_site_id);
DBMS_OUTPUT.put_line (‘Party Site Id: ‘ || ln_party_site_id);
DBMS_OUTPUT.put_line (‘Location Id: ‘ || ln_location_id);
END IF;
EXCEPTION
WHEN local_exception
THEN
IF local_failed_at = ‘SUPPLIER’
THEN
DBMS_OUTPUT.put_line (‘API failed at Supplier Creation’);
ELSIF local_failed_at = ‘SITE’
THEN
DBMS_OUTPUT.put_line (‘API failed at Site Creation’);
ELSIF local_failed_at = ‘CONTACT’
THEN
DBMS_OUTPUT.put_line (‘API failed at Contact Creation’);
ELSIF local_failed_at = ‘NUMBER’
THEN
DBMS_OUTPUT.put_line (‘API failed at getting Supplier Number’);
END IF;
ROLLBACK;
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE (SQLERRM);
ROLLBACK;
END;
[/code]
Recently I tried to build a cloned instance of our production instance over VirtualBox for some emergency issues faced by our inventory module. As this instance was supposed to be only accessed by me, I opted to use my desktop machine for the same. Throughout the last many years I built my own machines, choosing the best available hardware at the time of building them. My current desktop configuration is like following
i7 processor, 16GB memory, 2x1TB 7200 RPM HDD, 2x2TB 5200 RPM HDD, 1x500GB HDD for the OS (Windows 8.1 64Bit)
and throughout the years I built dozens of Virtual Machines using Oracle VirtualBox, mainly for testing un-certified Oracle & other products in a sand-boxed environment, against the crippled VMplayer, VirtualBox’s unrestricted interface supported almost everything I needed from a virtual environment.
So I built my R12 instance, that is around 600GB roughly in size with almost 4.5-5 years of business data, media etc. The following resources were dedicated for the fresh VM
4 processors
10GB memory
40GB fixed size SATA VDI for the Operating System (I used both OEL 5 & OEL 7 64bit)
1.2TB fixed size SATA VDI for the instance files
A dedicated D-Link 10/100MB NIC
Once the instance came online, I removed, cancelled all the scheduled concurrent programs, changed the database level parameters like job_queue_processes etc, however the lag experienced throughout the access attempts remained the same. Sometimes the HTML pages took 5-6 minutes to open, forms based modules took 8-10 minutes to open and timeouts were happening, frustrating me to the most possible levels
That is when I decided to give VMPlayer a try, I converted the existing VDI for the OS as vmdk and created a fresh 850 fixed size vmdk for the instance files and attached the same as SCSI to the VM. Did the complete clone process and to my utter surprise, the login page loaded within a minutes once after the instance was started!
This lead me to do various attempts with the fresh instance, I was able to shutdown the instance much faster, forms were opening faster, though LOVs having more than thousands of items were taking more time than anticipated
Once again, I created another fresh VM with VirtualBox and attached the disks created for VMplayer with it and repeated the tests. Well, I got the same performances from the new VM and somehow I came to a conclusion that, both VirtualBox and VMPlayer provide better I/O for SCSI interfaces compared to plain SATA emulators, ironically, the disks were created over SATA drives!
This difference you may not experience with VMs those are not hosting resource hungry applications like Oracle E-Business Suite. So, if you are attempting what I had described above and notice the differences, please update me with comments section.
*General Note: The below instructions, which were successfully completed and tested are from the prospective of a person who do not have any previous knowledge about cloning, advance level Linux administration etc. If you find it too amateurish, please let us know with the comments session and corrections. We will be more than happy to amend the post.
Most probably few of the errors we received with write permissions missing to /tmp for applprod user should because of missing Rapid Clone patches.
Prerequisites
Oracle VirtualBox (We don’t support a physical system as detailing a cloning for a Linux system wouldn’t fit entirely within the discussed topic)
Minimum 4GB of memory to spare for the Virtual Machine
500GB of free hard disk space (If you don’t have additional 500GB, you can use the same source machine for cloning, by backing up the necessary folders and after removing the E-BIZ folder entirely)
Step #1
Create a folder on a partition where you have 500GB or more space and call it “ebiznew”
Copy and paste both System.vdi and ebs1211db.vdi from your other virtual machine folder, where you have a properly working Vision Instance (We are not going to clone the existing VM using the VirtualBox clone utility)
Change the uuid for both System.vdi and ebs1211db.vid
E:\ebiznew>C:\Program Files\Oracle\VirtualBox\VBoxManage.exe&quot; internalcommands
sethduuid System.vdi
UUID changed to: 5dbdd6e3-c8ff-431d-bdd8-edf31c3012fb
E:\ebiznew>C:\Program Files\Oracle\VirtualBox\VBoxManage.exe&quot; internalcommands
sethduuid ebs1211db.vdi
UUID changed to: 05068998-dc4d-46ef-8f8f-1ae07b786bfa
[code language=”text” gutter=”false”]
E:\ebiznew>"C:\Program Files\Oracle\VirtualBox\VBoxManage.exe" internalcommands
sethduuid System.vdi
UUID changed to: 5dbdd6e3-c8ff-431d-bdd8-edf31c3012fb
E:\ebiznew>"C:\Program Files\Oracle\VirtualBox\VBoxManage.exe" internalcommands
sethduuid ebs1211db.vdi
UUID changed to: 05068998-dc4d-46ef-8f8f-1ae07b786bfa
[/code]
Now start creating a new VM using VirtualBox and attach System.vdi and ebs1211db.vdi under SATA
As we have changed the kernel to NON Xen, you should able to boot up the VM immediately
Once logged in as “root”, let us drop the existing E-BIZ/* folders for cloning purpose
Make sure from your HOST box you can ping the new VM by both hostname and IP address
*Special note: If you have a total of 8GB(only) memory with your box, you have to change the memory allocations for both your VMs (one which already has the Vision instance configured & the new VM on which you are going to execute the cloning) to 2GB each
Boot up you VM with Vision instance configured and running (Aka source system)
Edit /etc/hosts file and add the IP address and hostname of your new VM
As root
vi /etc/hosts
192.168.0.50 ebs1213.rhome.com ebs1213
Where ebs1213 is the new hostname you have set for the new VM. Adjust it according to the name you have chosen
Save and exit the vi editor and try to ping the new VM from your source VM
ping ebs1213.rhome.com
Make sure you are getting ping replies from the new VM
Start dbTier and appsTier sequentially and make sure that you can access the application
Step #2
Now we are all set to “try” our first cloning process
Prior cloning we MUST prepare the source system for cloning. Hence switch user to “oracle” and switch to following folder (Adjust the path according to your environment)
cd /u01/E-BIZ/db/tech_st/11.2.0.2/appsutil/scripts/VIS_appvis
$perl adpreclone.pl dbTier
(provide the apps password when it is promoted)
The pre-clone should complete within few minutes time, and if there are no errors (in our case), we can proceed to prepare the application tier now
As user “Oracle” switch to
/u01/E-BIZ/inst/apps/VIS_appvis/admin/scripts
$perl adpreclone.pl appsTier
(provide the apps password when it is promoted)
Let the pre-clone procedure complete.
Once the pre-clone procedure has been completed, shutdown the application tier and database tier sequentially
Check for FNDLIBR processes, until the count reaches two(2)
$ps –ef | grep FNDLIBR | wc –l
Now we have to copy the database and application tier folders to our target VM. For our exercise we will create individual tar.gz files for both database and application folders
We will create a new folder with /u01 on the source system
As root
[sourcecode language="text"]
#cd /u01
#mkdir backup
#cd backup
# time tar -zcvf db.tar.gz /u01/E-BIZ/db/*
This will create a new tar ball with the name “db.tar.gz” inside /u01/backup folder
Sit back and relax, it is going to take some time
Once the tar process finishes, we will attempt to create a tar ball for the application tier
#time tar -zcvf apps.tar.gz /u01/E-BIZ/apps/apps_st/*
The entire tar processes may take more than couple of hours time (Depending upon the resources you have allocated for the source VM)
[/sourcecode]
Now we will copy the tar files created to our target system using SCP
You will be given a warning, accept the prompt. After that, you will be asked to enter the root password for your target machine, in our case “ebs1213”
If your vision instance is fresh without much data added after it was put online, the tar ball will be something less than 30GB, and the transfer should complete within 16-20 minutes time
Once the database tar ball transferred you can proceed with transferring your application tar bar
Amount of time required will vary depending upon the resources available. Wait until the transfer completes
Now you can shutdown your source system (So that you can release the memory/processors allocated and increase them with your new VM on which we will continue with the cloning process)
Shutdown your new VM (Target system)
Make changes to your new VM with more memory, processors etc
Boot up the new VM and logon to the VM as root
Step #3
We are going to MIMIC a production instance with our new VM (Please note, do not attempt to violate Oracle’s licensing agreements, our intention is not to help you with doing something which is against Oracle’s licensing terms and Vision instance usage)
We will create two new users and add them to existing oinstall and dba groups
As root
Now switch to /u01 folder (or other mount point as you have created it)
We will create a number of folders, which will be entirely different from the Vision Instance structure
Now we will extract the tar balls to appropriate folders
#cd /u01/oraprod/PROD/db
#time tar –zxvf /u01/backup/db.tar.gz
Approximately the extraction process will take around 3 hours for the database tar ball and around one hour for the application tar ball
Once the database tar ball extracted, we will start extracting the application tar ball
cd /u01/applprod/PROD/apps
time tar –zxvf /u01/backup/apps.tar.gz
Now we can start the actual cloning process
Step #4 Cloning the database tier
[sourcecode language="bash"]
[root@visclone u01]# su - oraprod
[oraprod@ebs1213 ~]$ cd /u01/oraprod/PROD/db/tech_st/11.2.0.2/appsutil/clone/bin
[oraprod@ebs1213 bin]$
#
[root@visclone u01]# su - oraprod
[oraprod@ebs1213 ~]$ cd /u01/oraprod/PROD/db/tech_st/11.2.0.2/appsutil/clone/bin
[oraprod@ebs1213 bin]$ perl adcfgclone.pl dbTier
Copyright (c) 2002 Oracle Corporation
Redwood Shores, California, USA
Oracle Applications Rapid Clone
Version 12.0.0
adcfgclone Version 120.31.12010000.8
Enter the APPS password :
Running:
/u01/oraprod/PROD/db/tech_st/11.2.0.2/appsutil/clone/bin/../jre/bin/java -Xmx600M -cp /u01/oraprod/PROD/db/tech_st/11.2.0.2/appsutil/clone/jlib/java:/u01/oraprod/PROD/db/tech_st/11.2.0.2/appsutil/clone/jlib/xmlparserv2.jar:/u01/oraprod/PROD/db/tech_st/11.2.0.2/appsutil/clone/jlib/ojdbc5.jar oracle.apps.ad.context.CloneContext -e /u01/oraprod/PROD/db/tech_st/11.2.0.2/appsutil/clone/bin/../context/db/CTXORIG.xml -validate -pairsfile /tmp/adpairsfile_3095.lst -stage /u01/oraprod/PROD/db/tech_st/11.2.0.2/appsutil/clone 2> /tmp/adcfgclone_3095.err; echo $? > /tmp/adcfgclone_3095.res
Log file located at /u01/oraprod/PROD/db/tech_st/11.2.0.2/appsutil/clone/bin/CloneContext_1007224741.log
Provide the values required for creation of the new Database Context file.
Target System Hostname (virtual or normal) [ebs1213] :
Target Instance is RAC (y/n) [n] :
Target System Database SID : PROD
Target System Base Directory : /u01/oraprod
Target System utl_file_dir Directory List : /usr/tmp
Number of DATA_TOP's on the Target System [1] : 1
Target System DATA_TOP Directory 1 [/u01/E-BIZ/db/apps_st/data] : /u01/oraprod/PROD/db/apps_st/data
Target System RDBMS ORACLE_HOME Directory [/u01/oraprod/db/tech_st/11.1.0] : /u01/oraprod/PROD/db/tech_st/11.2.0.2
Do you want to preserve the Display [appvis:0.0] (y/n) : n
Target System Display [ebs1213:0.0] :
Do you want the the target system to have the same port values as the source system (y/n) [y] ? : n
Target System Port Pool [0-99] : 42
[/sourcecode]
Database cloning will kick start now, and should complete without throwing errors. Some sample outputs like below
[sourcecode language="text"]
Checking the port pool 42
done: Port Pool 42 is free
Report file located at /u01/oraprod/PROD/db/tech_st/11.2.0.2/appsutil/temp/portpool.lst
Complete port information available at /u01/oraprod/PROD/db/tech_st/11.2.0.2/appsutil/temp/portpool.lst
Creating the new Database Context file from :
/u01/oraprod/PROD/db/tech_st/11.2.0.2/appsutil/template/adxdbctx.tmp
The new database context file has been created :
/u01/oraprod/PROD/db/tech_st/11.2.0.2/appsutil/PROD_ebs1213.xml
Log file located at /u01/oraprod/PROD/db/tech_st/11.2.0.2/appsutil/clone/bin/CloneContext_1007224741.log
Check Clone Context logfile /u01/oraprod/PROD/db/tech_st/11.2.0.2/appsutil/clone/bin/CloneContext_1007224741.log for details.
Running Rapid Clone with command:
perl /u01/oraprod/PROD/db/tech_st/11.2.0.2/appsutil/clone/bin/adclone.pl java=/u01/oraprod/PROD/db/tech_st/11.2.0.2/appsutil/clone/bin/../jre mode=apply stage=/u01/oraprod/PROD/db/tech_st/11.2.0.2/appsutil/clone component=dbTier method=CUSTOM dbctxtg=/u01/oraprod/PROD/db/tech_st/11.2.0.2/appsutil/PROD_ebs1213.xml showProgress contextValidated=true
Running:
perl /u01/oraprod/PROD/db/tech_st/11.2.0.2/appsutil/clone/bin/adclone.pl java=/u01/oraprod/PROD/db/tech_st/11.2.0.2/appsutil/clone/bin/../jre mode=apply stage=/u01/oraprod/PROD/db/tech_st/11.2.0.2/appsutil/clone component=dbTier method=CUSTOM dbctxtg=/u01/oraprod/PROD/db/tech_st/11.2.0.2/appsutil/PROD_ebs1213.xml showProgress contextValidated=true
APPS Password :
Beginning database tier Apply - Mon Oct 7 22:50:46 2013
/u01/oraprod/PROD/db/tech_st/11.2.0.2/appsutil/clone/bin/../jre/bin/java -Xmx600M -DCONTEXT_VALIDATED=true -Doracle.installer.oui_loc=/u01/oraprod/PROD/db/tech_st/11.2.0.2/oui -classpath /u01/oraprod/PROD/db/tech_st/11.2.0.2/appsutil/clone/jlib/xmlparserv2.jar:/u01/oraprod/PROD/db/tech_st/11.2.0.2/appsutil/clone/jlib/ojdbc5.jar:/u01/oraprod/PROD/db/tech_st/11.2.0.2/appsutil/clone/jlib/java:/u01/oraprod/PROD/db/tech_st/11.2.0.2/appsutil/clone/jlib/oui/OraInstaller.jar:/u01/oraprod/PROD/db/tech_st/11.2.0.2/appsutil/clone/jlib/oui/ewt3.jar:/u01/oraprod/PROD/db/tech_st/11.2.0.2/appsutil/clone/jlib/oui/share.jar:/u01/oraprod/PROD/db/tech_st/11.2.0.2/appsutil/clone/jlib/oui/srvm.jar:/u01/oraprod/PROD/db/tech_st/11.2.0.2/appsutil/clone/jlib/ojmisc.jar oracle.apps.ad.clone.ApplyDBTier -e /u01/oraprod/PROD/db/tech_st/11.2.0.2/appsutil/PROD_ebs1213.xml -stage /u01/oraprod/PROD/db/tech_st/11.2.0.2/appsutil/clone -showProgress
APPS Password : Log file located at /u01/oraprod/PROD/db/tech_st/11.2.0.2/appsutil/log/PROD_ebs1213/ApplyDBTier_10072250.log
/ 0% completed
[/sourcecode]
Once the cloning completes without errors, the database instance will start automatically and you can proceed with cloning the application tier
Step #5 Cloning the application tier
[sourcecode language="text"]
[oraprod@visclone u01]# su - oraprod
[applprod@ebs1213 ~]$ cd /u01/oraprod/PROD/apps/apps_st/comn/clone/bin
[applprod@ebs1213 bin]$
[applprod@ebs1213 bin]$ perl adcfgclone.pl appsTier
Copyright (c) 2002 Oracle Corporation
Redwood Shores, California, USA
Oracle Applications Rapid Clone
Version 12.0.0
adcfgclone Version 120.31.12010000.8
Enter the APPS password :
Running:
/u01/applprod/PROD/apps/apps_st/comn/clone/bin/../jre/bin/java -Xmx600M -cp /u01/applprod/PROD/apps/apps_st/comn/clone/jlib/java:/u01/applprod/PROD/apps/apps_st/comn/clone/jlib/xmlparserv2.jar:/u01/applprod/PROD/apps/apps_st/comn/clone/jlib/ojdbc14.jar oracle.apps.ad.context.CloneContext -e /u01/applprod/PROD/apps/apps_st/comn/clone/bin/../context/apps/CTXORIG.xml -validate -pairsfile /tmp/adpairsfile_11049.lst -stage /u01/applprod/PROD/apps/apps_st/comn/clone 2> /tmp/adcfgclone_11049.err; echo $? > /tmp/adcfgclone_11049.res
Log file located at /u01/applprod/PROD/apps/apps_st/comn/clone/bin/CloneContext_1007230323.log
Provide the values required for creation of the new APPL_TOP Context file.
Target System Hostname (virtual or normal) [ebs1213] :
Target System Database SID : PROD
Target System Database Server Node [ebs1213] :
Target System Database Domain Name [rhome.com] :
Target System Base Directory : /u01/applprod
Target System Tools ORACLE_HOME Directory [/u01/applprod/apps/tech_st/10.1.2] : /u01/applprod/PROD/apps/tech_st/10.1.2
Target System Web ORACLE_HOME Directory [/u01/applprod/apps/tech_st/10.1.3] : /u01/applprod/PROD/apps/tech_st/10.1.3
Target System APPL_TOP Directory [/u01/applprod/apps/apps_st/appl] : /u01/applprod/PROD/apps/apps_st/appl
Target System COMMON_TOP Directory [/u01/applprod/apps/apps_st/comn] : /u01/applprod/PROD/apps/apps_st/comn
Target System Instance Home Directory [/u01/applprod/inst] : /u01/applprod/PROD/inst
Target System Root Service [enabled] :
Target System Web Entry Point Services [enabled] :
Target System Web Application Services [enabled] :
Target System Batch Processing Services [enabled] :
Target System Other Services [disabled] :
Do you want to preserve the Display [appvis:0.0] (y/n) : n
[/sourcecode]
We had an error, applprod user failing to write to /tmp folder
Target System Display [ebs1213:0.0] : RC-50004: Error occurred in CloneContext: AC-00005: No write permissions for creating the Context file – /tmp/temp.xml Raised by oracle.apps.ad.context.AppsContext Check Clone Context logfile /u01/applprod/PROD/apps/apps_st/comn/clone/bin/CloneContext_1007230323.log for details.
ERROR: Context creation not completed successfully. For additional details review the file /tmp/adcfgclone_11049.err if present.
Hence I have changed the ownership & permissions for /tmp folder
[sourcecode language="bash"]
login as: root
root@ebs1213.rhome.com's password:
Last login: Mon Oct 7 16:18:34 2013 from 192.168.0.200
[root@ebs1213/]# ls -al | grep tmp
drwxrwxrwx 7 root root 20480 Oct 7 23:06 tmp
[root@ebs1213/]# chown -R applprod:dba /tmp
[root@ebs1213/]# chmod -R 777 /tmp
[root@ebs1213/]# ls -al | grep tmp
drwxrwxrwx 7 applprod dba 20480 Oct 7 23:06 tmp
[root@ebs1213/]#
[/sourcecode]
Re-run the application tier cloning as user “applprod”
[sourcecode language="text"]
Checking the port pool 42
done: Port Pool 42 is free
Report file located at /u01/applprod/PROD/inst/apps/PROD_ebs1213/admin/out/portpool.lst
Complete port information available at /u01/applprod/PROD/inst/apps/PROD_ebs1213/admin/out/portpool.lst
UTL_FILE_DIR on database tier consists of the following directories.
1. /usr/tmp
2. /usr/tmp
3. /u01/oraprod/PROD/db/tech_st/11.2.0.2/appsutil/outbound/PROD_ebs1213
4. /usr/tmp
Choose a value which will be set as APPLPTMP value on the target node [1] : 1
Creating the new APPL_TOP Context file from :
/u01/applprod/PROD/apps/apps_st/appl/ad/12.0.0/admin/template/adxmlctx.tmp
The new APPL_TOP context file has been created :
/u01/applprod/PROD/inst/apps/PROD_ebs1213/appl/admin/PROD_ebs1213.xml
Log file located at /u01/applprod/PROD/apps/apps_st/comn/clone/bin/CloneContext_1007233459.log
Check Clone Context logfile /u01/applprod/PROD/apps/apps_st/comn/clone/bin/CloneContext_1007233459.log for details.
Running Rapid Clone with command:
perl /u01/applprod/PROD/apps/apps_st/comn/clone/bin/adclone.pl java=/u01/applprod/PROD/apps/apps_st/comn/clone/bin/../jre mode=apply stage=/u01/applprod/PROD/apps/apps_st/comn/clone component=appsTier method=CUSTOM appctxtg=/u01/applprod/PROD/inst/apps/PROD_ebs1213/appl/admin/PROD_ebs1213.xml showProgress contextValidated=true
Running:
perl /u01/applprod/PROD/apps/apps_st/comn/clone/bin/adclone.pl java=/u01/applprod/PROD/apps/apps_st/comn/clone/bin/../jre mode=apply stage=/u01/applprod/PROD/apps/apps_st/comn/clone component=appsTier method=CUSTOM appctxtg=/u01/applprod/PROD/inst/apps/PROD_ebs1213/appl/admin/PROD_ebs1213.xml showProgress contextValidated=true
APPS Password :
Beginning application tier Apply - Mon Oct 7 23:36:52 2013
/u01/applprod/PROD/apps/apps_st/comn/clone/bin/../jre/bin/java -Xmx600M -DCONTEXT_VALIDATED=true -Doracle.installer.oui_loc=/oui -classpath /u01/applprod/PROD/apps/apps_st/comn/clone/jlib/xmlparserv2.jar:/u01/applprod/PROD/apps/apps_st/comn/clone/jlib/ojdbc14.jar:/u01/applprod/PROD/apps/apps_st/comn/clone/jlib/java:/u01/applprod/PROD/apps/apps_st/comn/clone/jlib/oui/OraInstaller.jar:/u01/applprod/PROD/apps/apps_st/comn/clone/jlib/oui/ewt3.jar:/u01/applprod/PROD/apps/apps_st/comn/clone/jlib/oui/share.jar:/u01/applprod/PROD/apps/apps_st/comn/clone/jlib/oui/srvm.jar:/u01/applprod/PROD/apps/apps_st/comn/clone/jlib/ojmisc.jar oracle.apps.ad.clone.ApplyAppsTier -e /u01/applprod/PROD/inst/apps/PROD_ebs1213/appl/admin/PROD_ebs1213.xml -stage /u01/applprod/PROD/apps/apps_st/comn/clone -showProgress
APPS Password : Log file located at /u01/applprod/PROD/inst/apps/PROD_ebs1213/admin/log/ApplyAppsTier_10072336.log
- 0% completed
Completed Apply...
Mon Oct 7 23:43:12 2013
Do you want to startup the Application Services for PROD? (y/n) [y] : y
Starting application Services for PROD:
Running:
/u01/applprod/PROD/inst/apps/PROD_ebs1213/admin/scripts/adstrtal.sh -nopromptmsg
You are running adstrtal.sh version 120.15.12010000.3
The logfile for this session is located at /u01/applprod/PROD/inst/apps/PROD_ebs1213/logs/appl/admin/log/adstrtal.log
Executing service control script:
/u01/applprod/PROD/inst/apps/PROD_ebs1213/admin/scripts/adopmnctl.sh start
script returned:
****************************************************
You are running adopmnctl.sh version 120.6.12010000.5
Starting Oracle Process Manager (OPMN) ...
opmnctl: opmn started.
adopmnctl.sh: exiting with status 0
adopmnctl.sh: check the logfile /u01/applprod/PROD/inst/apps/PROD_ebs1213/logs/appl/admin/log/adopmnctl.txt for more information ...
.end std out.
.end err out.
****************************************************
Executing service control script:
/u01/applprod/PROD/inst/apps/PROD_ebs1213/admin/scripts/adalnctl.sh start
script returned:
****************************************************
adalnctl.sh version 120.3
Checking for FNDFS executable.
Starting listener process APPS_PROD.
adalnctl.sh: exiting with status 0
adalnctl.sh: check the logfile /u01/applprod/PROD/inst/apps/PROD_ebs1213/logs/appl/admin/log/adalnctl.txt for more information ...
.end std out.
.end err out.
****************************************************
Executing service control script:
/u01/applprod/PROD/inst/apps/PROD_ebs1213/admin/scripts/adapcctl.sh start
script returned:
****************************************************
You are running adapcctl.sh version 120.7.12010000.2
Starting OPMN managed Oracle HTTP Server (OHS) instance ...
opmnctl: opmn is already running.
opmnctl: starting opmn managed processes...
adapcctl.sh: exiting with status 0
adapcctl.sh: check the logfile /u01/applprod/PROD/inst/apps/PROD_ebs1213/logs/appl/admin/log/adapcctl.txt for more information ...
.end std out.
.end err out.
****************************************************
Executing service control script:
/u01/applprod/PROD/inst/apps/PROD_ebs1213/admin/scripts/adoacorectl.sh start
script returned:
****************************************************
You are running adoacorectl.sh version 120.13
Starting OPMN managed OACORE OC4J instance ...
adoacorectl.sh: exiting with status 0
adoacorectl.sh: check the logfile /u01/applprod/PROD/inst/apps/PROD_ebs1213/logs/appl/admin/log/adoacorectl.txt for more information ...
.end std out.
.end err out.
****************************************************
Executing service control script:
/u01/applprod/PROD/inst/apps/PROD_ebs1213/admin/scripts/adformsctl.sh start
script returned:
****************************************************
You are running adformsctl.sh version 120.16.12010000.3
Starting OPMN managed FORMS OC4J instance ...
Calling txkChkFormsDeployment.pl to check whether latest FORMSAPP.EAR is deployed...
Program : /u01/applprod/PROD/apps/apps_st/appl/fnd/12.0.0/patch/115/bin/txkChkFormsDeployment.pl started @ Mon Oct 7 23:44:32 2013
*** Log File = /u01/applprod/PROD/inst/apps/PROD_ebs1213/logs/appl/rgf/TXK/txkChkFormsDeployment_Mon_Oct_7_23_44_31_2013/txkChkFormsDeployment_Mon_Oct_7_23_44_31_2013.log
File "/u01/applprod/PROD/apps/tech_st/10.1.3/j2ee/forms/applications/forms/formsweb/WEB-INF/lib/frmsrv.jar" exists. Proceeding to check the size...
=============================================
*** Latest formsapp.ear has been deployed ***
=============================================
Program : /u01/applprod/PROD/apps/apps_st/appl/fnd/12.0.0/patch/115/bin/txkChkFormsDeployment.pl completed @ Mon Oct 7 23:44:32 2013
Perl script txkChkFormsDeployment.pl got executed successfully
adformsctl.sh: exiting with status 0
adformsctl.sh: check the logfile /u01/applprod/PROD/inst/apps/PROD_ebs1213/logs/appl/admin/log/adformsctl.txt for more information ...
.end std out.
*** ALL THE FOLLOWING FILES ARE REQUIRED FOR RESOLVING RUNTIME ERRORS
*** Log File = /u01/applprod/PROD/inst/apps/PROD_ebs1213/logs/appl/rgf/TXK/txkChkFormsDeployment_Mon_Oct_7_23_44_31_2013/txkChkFormsDeployment_Mon_Oct_7_23_44_31_2013.log
.end err out.
****************************************************
Executing service control script:
/u01/applprod/PROD/inst/apps/PROD_ebs1213/admin/scripts/adoafmctl.sh start
script returned:
****************************************************
You are running adoafmctl.sh version 120.8
Starting OPMN managed OAFM OC4J instance ...
adoafmctl.sh: exiting with status 0
adoafmctl.sh: check the logfile /u01/applprod/PROD/inst/apps/PROD_ebs1213/logs/appl/admin/log/adoafmctl.txt for more information ...
.end std out.
.end err out.
****************************************************
Executing service control script:
/u01/applprod/PROD/inst/apps/PROD_ebs1213/admin/scripts/adcmctl.sh start
script returned:
****************************************************
You are running adcmctl.sh version 120.17.12010000.5
Starting concurrent manager for PROD ...
Starting PROD_1007@PROD Internal Concurrent Manager
Default printer is noprint
adcmctl.sh: exiting with status 0
adcmctl.sh: check the logfile /u01/applprod/PROD/inst/apps/PROD_ebs1213/logs/appl/admin/log/adcmctl.txt for more information ...
.end std out.
.end err out.
****************************************************
Executing service control script:
/u01/applprod/PROD/inst/apps/PROD_ebs1213/admin/scripts/jtffmctl.sh start
script returned:
****************************************************
You are running jtffmctl.sh version 120.3
Validating Fulfillment patch level via /u01/applprod/PROD/apps/apps_st/comn/java/classes
Fulfillment patch level validated.
Starting Fulfillment Server for PROD on port 9342 ...
jtffmctl.sh: exiting with status 0
.end std out.
.end err out.
****************************************************
All enabled services for this node are started.
adstrtal.sh: Exiting with status 0
adstrtal.sh: check the logfile /u01/applprod/PROD/inst/apps/PROD_ebs1213/logs/appl/admin/log/adstrtal.log for more information ...
[applprod@ebs1213 bin]$
[/sourcecode]
A year back, following the Oracle’s blog, got excited and engaged with a tedious task to setup a Vision instance for Oracle R12 12.1.3 using the templates.
Against all the warnings and negative responses, I did manage to setup Oracle VM Manager and other repositories using Oracle Virtual Box and put the Vision instance online.
The efforts were unjustified as the tasks involved were supposed to be carried out by a seasoned DBA with advance level Linux knowledge. Apparently I lost the appetite to continue with the solution and discarded the entire setup along with the data repositories.
which explained a different approach towards setting up a Vision instance using the same Oracle templates for R12 12.1.3 and decided to give it a try.
I must say, John Piwowar @ pythian has put everything in place, so that a novice like me could follow the instructions without making any kind of mistakes and get the vision instance up and running.
Once the vision instance was up and running, I started wondering whether I could try something further. Oracle’s template based approach was involving two independent virtual machines serving two different roles
One server providing the database node
Second server providing the application node
At work I am used to deal with a single server hosting both database and application nodes which makes few things like server administration, OS patching etc easier.
Hence I started pondering the possibilities of merging both database and application servers into a single server.
Right now, after around 48 hours, I have successfully managed to merge both the nodes into a single server (virtual) and online with the Vision instance & I am going to share the entire procedures in detail so that you can also!
PART-1 Collect and arrange your stuff
Minimum Requirements
A desktop/laptop machine with minimum 4GB free RAM for the virtual machine. That means you must have 6-8GB base memory
Loads of free storage space. If your fixed disk doesn’t have minimum 500GB free disk space, I recommend you to buy a 1T external HDD (the read/write speed may be compromised, USB3 could be a minor exception)
Access to a very fast internet connection to download the templates from Oracle e-delivery portal
You have to download 11 files, 38GB by continuing
Pythian post describes a method to use wget to download all the files listed, using a file list input parameter, from a OSx/Linux host.
I downloaded the above files from a Windows machine, and must say it was a painful process.
PART-2 Convert the template files into .img and then .vdi files
I am not going to repeat the steps which are already clearly narrated with pythian article. Only suggest that, don’t use a Windows machine and 7zip to do all the unzip and joining the files. You use a Linux Virtual machine to do those jobs. It would be faster, and it would be the best. I created a Oracle Linux 6 VirtualBox to do those jobs by sharing the downloads folder with the Virtual Machine.
PART-3 Backup everything!
So once the img files are converted as .VDI files, the most import thing is to backup the .VDI files to somewhere else! Yes, you may corrupt the sources available inside the .VDI files while attempting to setup the Vision instance. If you don’t have a backup, you have to go through the painful process of unzipping, combining and converting the .img files to .VDI formats. Trust me, this exercise could easily run into 4-6 hours time, depending upon the efficiency of your hardware.
PART-4 1st Gear, slow start
Create a folder called “myvision” (name it anything)
Copy both System.vdi and ebs1211db.vdi (While converting the .img files I gave the .VDI files the same image names) from your database template folder or backup folder to “myvision” folder
The actual size of ebs1211db.vdi over Windows environment will be approximately 220GB, which will be few GB smaller over Linux environment.
As we are planning to host both the dB and Application tier from a single server, we MUST increase the size of ebs1211db.vdi
Let me explain why, prior going ahead
Oracle’s approach towards the VM setup was purely involving two servers(virtual) and both database & application files are placed on /u01 mount point
ie, Database server has a mount point called /u01 and so does the application server
All the clone scripts available for both database & applications servers have certain scripts hardcoded with /u01 as base path. Hence, one could get into useless activities of editing scripts to change the hardcoded base path values to adjust the cloning (Which was done successfully)
PART-5 2nd Gear, Resize ebs1211db.vdi
Fortunately, ebs1211db.vdi doesn’t have multiple partitions. Hence resizing the volume is pretty easier using Virtual Box command line tools & free linux tools like Gparted
Switch to command prompt (if you are on Windows 7 & 8 make it elevated)
Release ebs1211db.vdi from the Gparted Virtual Machine. Let us start creating a Virtual Machine using Oracle’s VirtualBox. Please note we are using the latest version of VirtualBox, if you are not, please update prior you move ahead with rest of the exercises
Please check the VM configurations I made for my Vision Instance Server with the below image
You can see that I have attached both System.vdi & ebs1211db.vdi files under SATA
Now, most important: You must have a .iso image or a physical DVD/CD for a recent release of Oracle/CentOS/RHEL linux, as we have to alter few attributes of the Virtual Machine we are going to put online.
Oracle has created the VM compiling a XEN kernel, which will not work under VirtualBox & you will fail to boot your Vision Instance VM, unless the kernel pointers are changed and recompiled.
Don’t worry it is pretty easy
Follow pythian article until the new kernel installation part
If you have just downloaded the templates from Oracle, you have the latest release of Oracle Linux embedded with System.img/.vdi file, and you don’t have to install a new kernel.
As root
#rpm –qa | grep kernel
If your uek kernel version matches as one provided with the image or higher, you can safely move ahead with creating a new initrd entry. As root issue the following command
Reboot your Virtual Machine, after removing the Linux media from the CD rom drive (or detach the linux iso image)
If you were following pythian post attentively, your new virtual machine MUST boot, without throwing any errors and prompt you for a login
The default password for root is “ovsroot”
I hope you did change the /etc/fstab entry for “/u01” mounting point prior rebooting the server as mentioned last time and that you have changed the
/etc/sysconfig/oraclevm-template entry to RUN_TEMPLATE_CONF=YES
If you haven’t, please do & restart the Virtual Machine. Optionally you can set a static IP to your box, so that you can start using a ssh client like putty, which will genuinely make many things easier while setting up the server at later stages.
Once the server restarted, you will notice that Oracle will automatically start the cloning process based on the “RUN_TEMPLATE_CONF=YES” entry
Throughout, we were using a hostname “appvis”, hence all the instructions, workarounds will utilize the same hostname. Please adjust the hostname according your preferences
Enter static IP address: 192.168.0.30 Enter netmask: [255.255.255.0] Enter gateway: 192.168.0.1 Enter DNS server: 192.168.0.1
Shutting down interface eth0: [ OK ] Shutting down loopback interface: [ OK ]
Configuring network settings. IP configuration: Static IP address
Bringing up loopback interface: [ OK ] Bringing up interface eth0: [ OK ]
Enter hostname (e.g, host.domain.com): appvis.rhome.com
Network configuration changed successfully. IP configuration: Static IP address IP address: 192.168.0.30 Netmask: 255.255.255.0 Gateway: 192.168.0.1 DNS server: 192.168.0.1 Hostname: appvis.rhome.com
Database node related prompts
Enter the APPS password :apps
Provide the values required for creation of the new Database Context file.
Target System Hostname (virtual or normal) [appvis] :<<press ENTER>>
Target Instance is RAC (y/n) [n] : n<<press ENTER>>
Target System Database SID : VIS
Target System Base Directory : /u01/E-BIZ
Target System utl_file_dir Directory List : /usr/tmp
Number of DATA_TOP’s on the Target System [1] :<<press ENTER>>
Target System DATA_TOP Directory 1 [/u01/E-BIZ/db/apps_st/data] : /u01/E-BIZ/db/apps_st/data
Target System RDBMS ORACLE_HOME Directory [/u02/E-BIZ/db/tech_st/11.1.0] :/u01/E-BIZ/db/tech_st/11.2.0.2
Do you want to preserve the Display [atgtxk05:0.0] (y/n) : n
Target System Display [appvis:0.0] :<<press ENTER>>
Do you want the the target system to have the same port values as the source system (y/n) [y] ? : n
Target System Port Pool [0-99] : 42
Make a note of the System Port Pool you have entered here, as we must use the same while setting up the Application tier, in order to establish a connection to the database & perform the cloning process
Sit back and let the cloning process for database to complete. It may take a while. Once the cloning process is over, the database will be started.
After a successful completion of cloning, the server will come back to a logon prompt
login as “oracle” and the default password is “oracle”
You will be asked to change the password for user oracle and APPS, ignore the prompts this time.
PART-7 4thd Gear, Free on the roads, hacking the Application tier
As I mentioned, what we are going to do is a pure hack, against Oracle’s design structure.
The first step is to create a soft link, which is mandatory for cloning the Application tier
Login as root cd /usr/lib cp libXtst.so.6 libXtst.so.6.backup rm libXtst.so.6 ln -s /usr/X11R6/lib/libXtst.so.6.1 /usr/lib/libXtst.so.6
Change the settings for your new virtual machine and attach ebs1211apps.vdi under SATA
Start the server
As your server starts you will notice that the database server getting started during the boot process itself. I’ll explain the methods to disable the automatic startup later with this post
Once the logon prompt appears, login as root (password: ovsroot)
Let us mount the newly added ebs1211apps.vdi (/dev/sdc1) to a mount point
As root
#mkdir /u02
#mount –t ext3 /dev/sdc1 /u02
The real hacks will take place now
Copy the /u02/E-BIZ/apps folder to /u01/E-BIZ/
As root
#cp –avr /u02/E-BIZ/* /u01/BIZ/
(I slept a whole night after starting the copying process, hence hardly have any idea how long it took, however I am sure you can finish one English movie during the copying period)
A total of 29GB will be copied to /u01/BIZ/apps folder
Once the copy process is over, we are ready to try the Application tier clone.
As prompted with the Database tier clone, a number of prompts you must answer, commonly the following
Enter the APPS password : apps Target System Hostname (virtual or normal) [appvis] : <ENTER to take default> Target System Database SID : VIS Target System Database Server Node [appvis] : gkar Target System Database Domain Name [rhome.com] : <ENTER to take default> Target System Base Directory : /u01/E-BIZ Target System Tools ORACLE_HOME Directory [/u01/E-BIZ/apps/tech_st/10.1.2] : <ENTER to take default> Target System Web ORACLE_HOME Directory [/u01/E-BIZ/apps/tech_st/10.1.3] : <ENTER to take default> Target System APPL_TOP Directory [/u01/E-BIZ/apps/apps_st/appl] : <ENTER to take default> Target System COMMON_TOP Directory [/u01/E-BIZ/apps/apps_st/comn] : <ENTER to take default> Target System Instance Home Directory [/u01/E-BIZ/inst] : <ENTER to take default> Target System Root Service [enabled] : <ENTER to take default> Target System Web Entry Point Services [enabled] : <ENTER to take default> Target System Web Application Services [enabled] : <ENTER to take default> Target System Batch Processing Services [enabled] : <ENTER to take default> Target System Other Services [disabled] : <ENTER to take default> Do you want to preserve the Display [atgtxk-09:0.0] (y/n) : n Target System Display [appvis:0.0] : <ENTER to take default> Do you want the the target system to have the same port values as the source system (y/n) [y] ? : n Target System Port Pool [0-99] : 42 UTL_FILE_DIR on database tier consists of the following directories. 1. /usr/tmp 2. /usr/tmp 3. /u01/E-BIZ/db/tech_st/11.2.0.2/appsutil/outbound/VIS_appvis 4. /usr/tmp Choose 1, default
The cloning will kick start now, and resume until 89% prior ending up with a FATAL error
–Minor update (if it is already NOT too late) 08-October-2013
I was trying to do a cloning & realized that the cloning process terminates with various errors and most of the errors were due to write permissions.
I was able to complete the cloning processes (dbTier & appsTier) individually by changing the permissions on folders like
and after the clone, resetting the ownership of /tmp back to root
chown –R root:root /tmp chmod –R 777 /tmp
Hence the below mentioned error “could be” addressed by changing few OS level rights as mentioned above.
This error is caused by missing “adautocfg.sh” file in the folder /u01/E-BIZ/inst/apps/VIS_appvis/admin/scripts which the auto configuration tries to access.
Without this file in this particular directory, the cloning process will never complete successfully. So we have to find a solution for it (I am not a APPS DBA and genuinely do not have any idea why the heck this file is missing in this particular location. All I could do is a guess work and it is towards avoiding the oracle template configuration wizard part, which initiates few functions. I’ll check it one of the following days and update this post accordingly)
and paste the following (Yes you can do it, just change the VIS_appvis with your CONTEXT name)
#!/bin/sh # dbdrv: none
# # $Header: adautocfg_ux.sh 120.4 2008/02/19 04:28:02 sbandla ship $ # # ############################################################### # # This file is automatically generated by AutoConfig. It will be read and # overwritten. If you were instructed to edit this file, or if you are not # able to use the settings created by AutoConfig, refer to Metalink Note # 387859.1 for assistance. # # ###############################################################
arg=`echo $myarg | sed ‘s/^-//’` case $arg in appspass=*) appspass=`echo $arg | sed ‘s/appspass=//g’` shift ;; nocustom) myparams=”$myparams $arg” shift ;; noversionchecks) myparams=”$myparams $arg” shift ;; promptmsg=*) promptmsg=`echo $arg | sed ‘s/promptmsg=//g’` shift ;; *) echo “$0: unrecognized action specified” exit 1 esac done
if test “${appspass}x” = “x”; then if test “${promptmsg}” != “hide”; then stty -echo printf “Enter the APPS user password:” read appspass printf “\n” stty echo else read appspass fi fi
The cloning process must complete without throwing any further errors and you will be asked whether the application should be started by the end of the cloning process. You can go ahead with starting the application tier.
However, you will notice, the apache HTTP will fail to start, as the CONTEXT FILE will have entries, which were not properly put in place during our last clone process as it has ended up with a FATAL error.
Let us edit the CONTEXT file and correct those entries now.
(This situation arises only if the cloning is done as user root)
Provide the apps password and sit back. It hardly takes few minutes to reset everything. Once the auto configuration part completes, go ahead with starting the application node
Incase if you are not sure about the http port, open up “VIS_appvis.xml” CONTEXT FILE and scan through, you will find it with url address area
Use sysadmin/sysadmin to logon and enjoy!!!
PART-8 5th Gear, Smooth Sailing, Restarting and online
As a system administrator I literally don’t like the idea of starting resource hungry services until the boxes are completed booted up and online. I prefer to start certain services myself and make sure that whatever were started manually completed the startup process successfully.
As root switch to “/etc/init.d” folder
chmod –x ebizdb
and you are done! The database will not startup next time while you are starting your Vision instance.
Go to these folders for starting database and application nodes
Day 3 – Adding Desktop to Oracle’s basic server (system.vdi)
I personally prefer to have a proper GUI, coming from an entirely Windows environment, that is a MUST for many times, regardless whether I am working with Linux or Windows systems
Here are the few things I have done to get the GNOME desktop installed with the basic Oracle linux Server embedded with the Oracle VM templates for Oracle R12 12.1.3
(Please make sure, from your Oracle linux box you have access to internet. Do you a ping www.google.com and confirm you are getting replies)
(Please shutdown both database and application prior proceeding)
(Please increase the size of System.vdi file PRIOR experimenting with any of the below exercises, and be careful while you would use Gparted to do it. Make a backup for system.vdi file and follow the thread provided above with the post to resize the partition (it is easy, I assure))
Around 380MB data will be downloaded and GNOME Desktop will be installed. Sit back and relax
Try to start the Desktop now
As root
#startx
X will crash and you will be provided certain error messages, which are NOT what we want. Hence let us go ahead and “correct” X
#X –config xorg.conf.new
X will start, however, a blank screen with ‘X’ shaped cursor and Ctrl+Alt+Backspace doesn’t kill it (minimum in my case)
Reset the terminal from VirtualBox menu and logon to the box as root once again
#cd /etc/X11
#mv xorg.conf xorg.conf.backup
#startx
Am sure you will be very much pleased to the GUI starting up. However, you don’t have a browser yet, so
#yum install firefox
This will install Firefox 17 (latest from the Oracle public yum repository)
If you want to start the GNOME automatic during the box startup, you need to edit /etc/inittab file
#vi /etc/inittab
and change the line
“id:3:initdefault:”
with
“id:5:initdefault:”
Save the file and exit the editor
Now a final reboot
#init 6
Once the box rebooted, you will be provided a graphical interface for the login process. Login and enjoy your GUI
However, if you would try to install the VirtualBox guest addons you will end up with FATAL errors, reporting missing headers. So you should, as root install the missing header files
#yum install kernel-uek-devel
A 6mb download will install the missing headers and, now you can safely complete the VirtualBox guest addons
One of the best features of Oracle applications is the flexibility to add custom applications and extend the functionality of the business suite. Many time developers who are not well versed with the Oracle’s guidelines for custom development for Oracle applications will totally ignore the pre-requisites for fnd_standard.set_who API to work properly by avoiding to include the following mandatory columns while designing tables
CREATION_DATE DATE
CREATED_BY NUMBER
LAST_UPDATE_DATE DATE
LAST_UPDATED_BY NUMBER
LAST_UPDATE_LOGIN NUMBER
Which will fail the API call and result in “No record history available here” notification
Another possibility is, developer adds these columns with the custom tables at later stages and manually add the columns to the block, without involving the datablock wizard, thus not properly linking the block with newly created columns.
Manually adding the columns with proper column names and data types may not generate an error while compiling, however the API will not able to see those columns.
The best method to avoid this problem is, by running the data block wizard once after new columns are added to the custom table(s)
Run the data block wizard, refresh the data source and make sure you don’t have any column within the left side pan
Recompile and test the custom application once again. 99% this method should solve the fnd_standard.set_who API not updating information.
Okay, we are moving from Client/Server architecture to Weblogic+Developer 11g. One of the major requirements was to design and deploy a menu, which resembles Oracle application navigator.
We managed to design a menu, using LIST item (tlist) manipulation and the complete solution you can download from following link
[sourcecode language='sql' padlinenumbers='true']
Select supplier_number, vendor_name,
sum(amount_remaining) amount_remaining,
sum(b0) "<=30",
sum(b1) "31-60",
sum(b2) "61-90",
sum(b3) "91-180",
sum(b4) "181-270",
sum(b5) "271-360",
sum(b6) ">360"
FROM(
SELECT i.invoice_date,
round(nvl(ps.amount_remaining,0)*nvl(i.exchange_rate, 1),3) amount_remaining,
i.vendor_id, supp.vendor_name, supp.segment1 supplier_number,
CASE
when trunc(trunc(sysdate))-i.invoice_date <= 30 then
NVL(round(nvl(ps.amount_remaining,0)*nvl(i.exchange_rate, 1),3),0)
END b0
,
CASE
when trunc(sysdate)-i.invoice_date BETWEEN 31 AND 60 then
NVL(round(nvl(ps.amount_remaining,0)*nvl(i.exchange_rate, 1),3),0)
END b1,
CASE
when trunc(sysdate)-i.invoice_date BETWEEN 61 AND 90 then
NVL(round(nvl(ps.amount_remaining,0)*nvl(i.exchange_rate, 1),3),0)
END b2,
CASE
when trunc(sysdate)-i.invoice_date BETWEEN 91 AND 180 then
NVL(round(nvl(ps.amount_remaining,0)*nvl(i.exchange_rate, 1),3),0)
END b3 ,
CASE
when trunc(sysdate)-i.invoice_date BETWEEN 181 AND 270 then
NVL(round(nvl(ps.amount_remaining,0)*nvl(i.exchange_rate, 1),3),0)
END b4
,
CASE
when trunc(sysdate)-i.invoice_date BETWEEN 271 AND 360 then
NVL(round(nvl(ps.amount_remaining,0)*nvl(i.exchange_rate, 1),3),0)
END b5 ,
CASE
when trunc(sysdate)-i.invoice_date > 360 then
NVL(round(nvl(ps.amount_remaining,0)*nvl(i.exchange_rate, 1),3),0)
END b6
FROM ap_payment_schedules ps, ap_invoices i, ap_suppliers supp
WHERE i.invoice_id = ps.invoice_id
AND ps.org_id = :P_ORG_ID -- Security takes care of this part, only for other reporting reqs
AND i.vendor_id = supp.vendor_id
AND i.cancelled_date IS NULL
AND ps.amount_remaining <> 0
)
GROUP BY supplier_number, vendor_name
ORDER BY vendor_name
[/sourcecode]
And as usual, the main view is a protected repository, hence you have to initialize the security part in order to fetch data
[sourcecode language='sql' ]
begin
MO_GLOBAL.SET_POLICY_CONTEXT('S',:P_ORG_ID);
end ;
begin
--fnd_global.apps_initialize(:P_USER_ID,:P_RESP_ID,:P_RESP_APPL_ID);
fnd_global.apps_initialize(1353, 50854, 200);
end;
begin
MO_GLOBAL.INIT('SQLAP'); --Payables
--MO_GLOBAL.INIT('PO');
end;
[/sourcecode]
Now enjoy another quality stuff from us :)
PS for Kuwait requirements, we have rounded the figures to 3 digits, alter the script to suite your reporting requirements.
Please find instructions explaining Creating Sales Orders against inventory items with “At Sales Order Issue” attribute
Defining Inventory Attributes
Once the item created, assign the item to Organizations wherever the item would be used for pre-determined business requirements
Switch to Order Management Responsibility and start creating a Sales order for the item you defined above
Book the order and note down the Sales Order Number for Shipping Transaction
Navigate to Shipping Transactions form
Find your Sales Order Details using “Query Manager”
Verify the details and make sure the “pick status” information message as “Ready to Release”
From Actions dropdown menu select “Launch pick release” and press the “Go” button. This will submit the concurrent program “Pick Selection List Generation”. Wait until the concurrent program completed successfully.
Once the concurrent program completed successfully, re-query the sales order using “Query Manager”
Scroll to right and locate the column “Shipped Quantity”. Click on Tools menu and check whether the Serial Numbers menu option is yet enabled, it shouldn’t be…
Now enter the desired quantity in the “Shipped Quantity” column and save the transaction, which will turn the “Backorder Quantity” column mandatory.
Recheck whether the “Serial Numbers” menu option under “Tools” menu has been enabled for populating serial numbers for the item.
The menu option should be enabled for you now, click on “Serial Numbers” menu option and proceed towards generating Serial Numbers for the item.
(Enter the starting serial number and system will populate the end serial number based on the Shipped Quantity you provided in the above step)
Click the “Done” button if everything seems fine and save the transaction.
Click on “Delivery” Sub-Tab and Click on “Ship Confirm” which will popup another window
Accept/Modify according to your specific business requirements and click the “Ok” button
Confirm whether the message states the delivery was successful, if not start investigating the issues. On successful delivery, recall the Sales order and check the line status, it must be “Shipped”
