Install 11.5.8 and R12 on same machine

I have installed 11.5.8 and R12 on the same windows machine successfully. I installed R12 on a server running 11.5.8. After installation both run smoothly (but not fast -- my machine is only 2GB in memory and is a standard Pentium 4 PC).
Because disk space was low, I had to compress both instance. No problem arise yet, even on patching.
The only problem was I had to change 11.5.8 to use cygwin because I removed all MKS Toolkit.

Clean up MKS Toolkit if you want to use Cygwin

MKS Toolkit have to be uninstalled if you want to use Cygwin. More than that you will also have to clean up some environment setting left by MKS Toolkit. If you don't clean up, you will get error on Cygwin shell when running some ad programs such as adrelink.

Using Cygwin with Oracle EBS R12

Note from Oracle: Cygwin is free software and is not supported unless you purchase a support contract from a third party providing Cygwin support. We recommend MKS toolkit for all critical production and test systems.

Download the Cygwin Software
Perform the following to download the Cygwin software:

• Navigate to http://www.cygwin.com and click on "Install or Update Now!" to download setup.exe to your machine.
• Run setup.exe and click Next, which will bring up the Choose Installation Type screen.
• Choose Option 2, Download without Installing.
• Click on Next to go through the next few screens, where you will be asked to choose the directory to store the packages (called the Local Package Directory), your Internet connection type, and a download site.
• Click on Next again to get to the Select Packages screen, and select the following packages (click on cygwin to toggle):

• All cygwin Default
• Archive cygwin Default, plus manually select the zip package
• Base cygwin Default, plus manually select the following extra packages: ash, coreutils, findutils, gawk, grep, sed, tar and which
• Devel cygwin Default, plus manually select binutils, gcc, gcc-core, gcc-g++, make and mktemp
• Doc cygwin Default, plus manually select cygwin-doc and man
• Editors cygwin Default, plus manually select vim
• Interpreters cygwin Default, plus manually select gawk
• Shells cygwin Default, plus manually select ash and tcsh
• Utils cygwin Default, plus manually select cygutils, diffutils and file

• Click on Next again to download the selected files.

Install Cygwin
Once the download is complete, perform the following steps to install Cygwin:

• Launch setup.exe again to conduct the install.
• From the Choose Installation Type screen, choose Option 3, Install from Local Directory.
• Click Next, and specify the Root Directory where you wish to install Cygwin (created in Step 2), and DOS as the Default Text File Type.
• Click Next and specify the same Local Package Directory from Step 3.
• Click Next to get to the Select Packages screen.
• Choose All cygwin Install (click on cygwin to toggle). Note: Do not choose All cygwin Default, since this will not include all the necessary files.
• Click on Next again to conduct the installation

Verify Package Versions
Verify that the versions of packages, such as Bash and Make, are supported for use with Oracle Applications Release 11i. See the Attention at the top of this document for supported versions.

To determine the package versions:

C:\> cd
C:\> make --version
C:\> bash --version

Note: Depending on how the packages are installed, the Cygwin Installer may display the incorrect package version information. Use the command-line interface to verify the correct package version.
Note: Make versions 3.79.1-7 and 3.80-1 and Bash versions 3.0-11, 3.1-9, 3.2.9-11, and 3.2.25-16 from Cygwin are supported for use with Oracle E-Business Suite R12. Other versions are currently not supported.

If any of the package versions are not supported, perform the following to update the package:

* Download a supported version of the package from http://cygwin.paracoda.com/release/.
* Overwrite the existing version of the package with the supported version of the package:

C:\> cd
C:\> tar -xjvf \

Update Compatibilty with Shell Scripts
Type these commands to maintain compatibility with existing shell scripts:
C:\> copy gawk.exe awk.exe (answer yes to copy over existing file)
C:\> copy grep.exe egrep.exe
C:\> copy make.exe gnumake.exe
C:\> copy gcc.exe cc.exe

Note: There may be a problem overwriting files, delete the destination file first before overwriting.

Add Cygwin Bin Directory to Path
Add the cygwin bin directory to the system path. For instance:
C:\> set path = %path%;D:\cygwin\bin

Next Step
If you have installed MKS Toolkit before, uninstall it and clean up windows environment from MKS Toolkit leftovers.
On Oracle EBS installation, when you asked to input UNIX Toolkit directory, use D:\cygwin\bin

Stage area is mandatory on R12 install

I have tried to install R12 directly from DVD and failed. I asked metalink and the answer was I have to create stage area. Wow that would be 33 GB.

Oracle Applications R12 for Windows

Oracle Applications R12 for Windows finally released. I am downloading it now. I'm going to try it on test server (which is not a server, just a PC with 2GB RAM and plenty of disk space). It contains of 44 parts with total of 35 GB. It has almost been 2 weeks of download. I limit myself only to download 1 file at a time.

Creating FSG with beginning, debits, credits, and ending balance report

I have managed to get FSG debit and credit equal to trial balance - summary 1 . I need to calculate debit and credit on FSG first. Note that I was trying to make YTD report and this was done on column set.
Beginning Balance = BAL-Actual (FY Start) amount type.
For FSG Debit Column, I need to create 2 other column first. One for YTD-actual amount type and DR activity on account assignment, say A column. One for BAL-Actual (FY Start) amount type and DR activity on account assingment. This will be B column. Both are not shown. Then the real and shown debit column = A - B.
The same applies to Credit Column with CR activity.
Ending Balance will equal to Beginning Balance + Debit Column - Credit Column.

On the case of PTD report, I can use DR and CR activity directly. But I have to use YTD (-1 offset) on beginning balance and YTD on ending balance. There is no calculation on PTD report.

Windows 2003 Service Pack 2 is safe

Just updated to Windows 2003 SP2. No problem has been found. My version of Oracle Applications is 11.5.8.

Discoverer Plus access through firewall

Follow the directions below to setup the Gatekeeper. Skip the NAT & PAT steps if you are not using them.

1. Run gkconfig.bat. You can find it E:\\VBROKER\bin
   
2. You should start on the MAIN tab
3. Put gatekeeper.ior in the IOR File
4. Put gatekeeper.log in the Log File
5. Change to the EXTERIOR tab
6. Put the physical IP address of the server where the Gatekeeper is listening into Exterior Address, this is the IP address of your server running Discoverer Server
7. Put the open port in the firewall into Exterior Port, I use port 15000, you must exclude this port on Windows Firewall
8. File => Save As
9. Save the file as gatekeeper.properties in the following location:
   Windows: \Apache\Apache\htdocs\discwb4\applet
10. Start the Gatekeeper
    Ensure that HKEY_LOCAL_MACHINE\Software\Oracle\DCW4_START_GATEKEEPER in the Windows registry is set to 1 & Restart the OracleDiscoverer4i Service in Services to start the Gatekeeper
11. Ensure a new gatekeeper.ior file was created. It should be in the same location you saved the gatekeeper.properties file, the applet directory. Check the file timestamp. This file contains information that the client applet reads to determine the settings of the gatekeeper.properties.

Enable Thousands Separators/Delimiters

To enable thousand separator/delimiter, login as system administrator, then open system profile option Currency: Thousands Separator, set it to Yes.
If you want to change separator character, open ICX: Numeric Characters. Set it to ,. if you use . as decimal separator and , as thousand separator. Set it to ., if you use , as decimal separator and , as thousand separator.

Login to Discoverer using Oracle Applications Users

To login to Oracle Discoverer Desktop and Oracle Discoverer go to Tools --> Options menu. Then choose Connection tab. There are three radio buttons, choose Connect to applications EULs or Connect to both standard and applications EULs. Standard EULs will allow you to login with database users. Applications EULs will allow you to login with Oracle Applications users.
After selecting the radio button, enter APPLSYSPUB/PUB on Gateway User ID (GWYUID) / password. Enter APPS on Foundation Name (FNDNAM).
Logout and connect to database. Enter Oracle Applications user name and password. Make sure the user has already been given an access to business area and appropriate privileges. On connect dialog check on Oracle Applications User check box.

Change Oracle Applications Host Name

I found a very good link to rename Oracle Applications host name. This can be useful to restore a backup to other machine or cloning the application.
This is the link: http://oraclesoon.blogspot.com/2006/11/oracle-e-business-suite-is-dependent-on.html.

Restoring Oracle Applications Backup

You have a backup and something happened to your Oracle Applications so you need to restore the backup. This restore process assumes that your Oracle Application instance is still exist, including Oracle services.
To restore the applications, make sure no Oracle services is running, including the database.
Use this script to shutdown entire applications (copy and run it on command window):

CALL e:\oracle\itb2comn\admin\scripts\ITB2\adstpall.cmd

(remember to change all itb2 or ITB2 to your instance name).



Then use this sql script to shutdown the database (save it as c:\shutdown.sql):

connect internal
shutdown immediate
exit

Run this script on windows command:

CALL e:\oracle\itb2db\8.1.7\ITB2.cmd
SET ORACLE_SID=ITB2
svrmgrl @c:\shutdown.sql
net stop "OracleServiceITB2"

(please change itb2 or ITB2 to your instance name).

Copy the backup to the existing Oracle Applications directory. There may be some overwrite errors during this copy process, just ignore them.

After the copy process completed, start up your database using this command on windows command:

net start "OracleServiceITB2"

Then start all Oracle Services using this command:

CALL e:\oracle\itb2comn\admin\scripts\ITB2\adstrtal.cmd

Backing up entire application

I use this backup steps on my 11.5.8 on Windows 2003 Server Enterprise Edition. You need three scripts to automate this backup process. During backup process aplication will be shut down.

The first script is a windows command file. This is the script, copy and save as backup.cmd:
echo ==========================
echo SISKEU Backup Started at
time /T
date /T
CALL e:\oracle\itb2appl\ITB2.cmd
CALL e:\oracle\itb2comn\admin\scripts\ITB2\adstpall.cmd
ren E:\oracle\itb2appl\au\11.5.0\resource\CUSTOM.plx CUSTOM.plx.old
copy E:\oracle\itb2appl\au\11.5.0\resource\CUSTOM.plx.new E:\oracle\itb2appl\au\11.5.0\resource\CUSTOM.plx
CALL e:\oracle\itb2db\8.1.7\ITB2.cmd
SET ORACLE_SID=ITB2
svrmgrl @c:\shutdown.sql
e:\oracle\itb2comn\util\wait.exe 10000
net stop "OracleServiceITB2"
e:\oracle\itb2comn\util\wait.exe 10000
rem xcopy e:\oracle\itb2data d:\backup2006\itb2data /E /Y /K /H /R /Q /I
net start "OracleServiceITB2"
e:\oracle\itb2comn\util\wait.exe 10000
SET ORACLE_SID=ITB2
svrmgrl @c:\startup.sql
e:\oracle\itb2comn\util\wait.exe 10000
rem xcopy e:\oracle\itb2appl d:\backup2006\itb2appl /E /Y /K /H /R /Q /I
rem xcopy e:\oracle\itb2comn d:\backup2006\itb2comn /E /Y /K /H /R /Q /I
rem xcopy e:\oracle\itb2db d:\backup2006\itb2db /E /Y /K /H /R /Q /I
rem xcopy e:\oracle\itb2ora d:\backup2006\itb2ora /E /Y /K /H /R /Q /I
CALL e:\oracle\itb2comn\admin\scripts\ITB2\adstrtal.cmd
echo SISKEU Backup Completed at:
time /T
date /T
echo ===========================
Second one is sql script to shutdown the database. This is the script, copy and save it as shutdown.sql:
connect internal
shutdown immediate
exit
The last one is a script to startup the database. This is the script, copy and save it as startup.sql:
connect internal
startup
exit

Use the first file and run it on command prompt or you can put it on scheduled task.

Before you run it, change ITB2 on that file to your oracle instance name and change path to suite your installation. Save two other file to a directory, remember to change the directory on backup script to directory where you extracted them.

Resolving down Concurrent Managers

On Windows 2003 Server, concurrent managers are occasionally down. I still don't know what causing it. To resolve this problem, follow these steps:

1. Shutdown the concurrent manager windows service using “net stop OracleConcMgr[your instance name]” on command prompt, for example “net stop OracleConcMgrITB2″.
2. Make sure no FNDLIBR processes are running. Check this on Windows Task Manager, Processes tab.
3. Run the concurrent manager cleaner script using SQLPlus. Follow instruction on that script. This is the script, copy and save to cmclean.sql:
   REM
   REM FILENAME
   REM cmclean.sql
   REM DESCRIPTION
   REM Clean out the concurrent manager tables
   REM NOTES
   REM Usage: sqlplus @cmclean
   REM
   REM
   REM $Id: cmclean.sql,v 1.4 2001/04/07 15:55:07 pferguso Exp $
   REM
   REM
   REM +======================================================================+
   set verify off;
   set head off;
   set timing off
   set pagesize 1000
   column manager format a20 heading 'Manager short name'
   column pid heading 'Process id'
   column pscode format a12 heading 'Status code'
   column ccode format a12 heading 'Control code'
   column request heading 'Request ID'
   column pcode format a6 heading 'Phase'
   column scode format a6 heading 'Status'
   WHENEVER SQLERROR EXIT ROLLBACK;
   DOCUMENT
   WARNING : Do not run this script without explicit instructions
   from Oracle Support
   *** Make sure that the managers are shut down ***
   *** before running this script ***
   *** If the concurrent managers are NOT shut down, ***
   *** exit this script now !! ***
   #
   accept answer prompt 'If you wish to continue type the word ''dual'': '
   set feed off
   select null from &answer;
   set feed on
   REM Update process status codes to TERMINATED
   prompt
   prompt ------------------------------------------------------------------------
   prompt -- Updating invalid process status codes in FND_CONCURRENT_PROCESSES
   set feedback off
   set head on
   break on manager
   SELECT concurrent_queue_name manager,
   concurrent_process_id pid,
   process_status_code pscode
   FROM fnd_concurrent_queues fcq, fnd_concurrent_processes fcp
   WHERE process_status_code not in ('K', 'S')
   AND fcq.concurrent_queue_id = fcp.concurrent_queue_id
   AND fcq.application_id = fcp.queue_application_id;
   set head off
   set feedback on
   UPDATE fnd_concurrent_processes
   SET process_status_code = 'K'
   WHERE process_status_code not in ('K', 'S');
   REM Set all managers to 0 processes
   prompt
   prompt ------------------------------------------------------------------------
   prompt -- Updating running processes in FND_CONCURRENT_QUEUES
   prompt -- Setting running_processes = 0 and max_processes = 0 for all managers
   UPDATE fnd_concurrent_queues
   SET running_processes = 0, max_processes = 0;
   REM Reset control codes
   prompt
   prompt ------------------------------------------------------------------------
   prompt -- Updating invalid control_codes in FND_CONCURRENT_QUEUES
   set feedback off
   set head on
   SELECT concurrent_queue_name manager,
   control_code ccode
   FROM fnd_concurrent_queues
   WHERE control_code not in ('E', 'R', 'X')
   AND control_code IS NOT NULL;
   set feedback on
   set head off
   UPDATE fnd_concurrent_queues
   SET control_code = NULL
   WHERE control_code not in ('E', 'R', 'X')
   AND control_code IS NOT NULL;
   REM Also null out target_node for all managers
   UPDATE fnd_concurrent_queues
   SET target_node = null;
   REM Set all 'Terminating' requests to Completed/Error
   REM Also set Running requests to completed, since the managers are down
   prompt
   prompt ------------------------------------------------------------------------
   prompt -- Updating any Running or Terminating requests to Completed/Error
   set feedback off
   set head on
   SELECT request_id request,
   phase_code pcode,
   status_code scode
   FROM fnd_concurrent_requests
   WHERE status_code = 'T' OR phase_code = 'R'
   ORDER BY request_id;
   set feedback on
   set head off
   UPDATE fnd_concurrent_requests
   SET phase_code = 'C', status_code = 'E'
   WHERE status_code ='T' OR phase_code = 'R';
   REM Set all Runalone flags to 'N'
   REM This has to be done differently for Release 10
   prompt
   prompt ------------------------------------------------------------------------
   prompt -- Updating any Runalone flags to 'N'
   prompt
   set serveroutput on
   set feedback off
   declare
   c pls_integer := dbms_sql.open_cursor;
   upd_rows pls_integer;
   vers varchar2(50);
   tbl varchar2(50);
   col varchar2(50);
   statement varchar2(255);
   begin
   select substr(release_name, 1, 2)
   into vers
   from fnd_product_groups;
   if vers >= 11 then
   tbl := 'fnd_conflicts_domain';
   col := 'runalone_flag';
   else
   tbl := 'fnd_concurrent_conflict_sets';
   col := 'run_alone_flag';
   end if;
   statement := 'update ' || tbl || ' set ' || col || '=''N'' where ' || col || ' = ''Y''';
   dbms_sql.parse(c, statement, dbms_sql.native);
   upd_rows := dbms_sql.execute(c);
   dbms_sql.close_cursor(c);
   dbms_output.put_line('Updated ' || upd_rows || ' rows of ' || col || ' in ' || tbl || ' to ''N''');
   end;
   /
   prompt
   prompt ------------------------------------------------------------------------
   prompt Updates complete.
   prompt Type commit now to commit these updates, or rollback to cancel.
   prompt ------------------------------------------------------------------------
   prompt
   set feedback on
   REM <= Last REM statment -----------------------------------------------------
   
4. Start concurrent manager services using “net start OracleConMgr[your instance name]”.
   
5. Wait for 5 to 10 minutes. Check Concurrent Managers status on Oracle Application Manager or Oracle Application using sysadmin. If the number of target is equal to actual, then concurrent managers are up.

If the Concurrent Managers are still not up, then you may have to restart the server. This is the last option, since this means downtime. Warn all users before restarting the server.