Tag: oracle ebs

SSL Enabled Hosting for Oracle ORDS on Oracle EBS Server

On By windows7bugsIn APEX, Linux, Oracle Applications(R12), Oracle relatedLeave a comment

One of the major concerns that we had while upgrading Oracle EBS R12 version from 12.2.10 to 12.2.14 was how to bring the Oracle ORDS instance under the same SSL hood as we didn’t have a plan to have another server dedicated for ORDS. Our current setup is like below, single instance of Oracle EBS R12 12.2.14 SSL enabled and the ORDS instance hosted from the same server using Apache-Tomcat, that listens to default 8080 port.

The business requirement is pretty straight forward. Develop using APEX, access it from the EBS environment, doesn’t mean we are not going to have standalone APEX applications as extensions for EBS.

Now, comes the real issue. How to bring both instances to the same SSL hood? For example, EBS instance is accessed from the URL https://hostname & we must ensure that the ORDS instance also should be accessible from the same hostname like https://hostname/ords

Please note, none of the below hacks are advisable for a PRODUCT environment. While the hack perfectly fits the TESTING environments, the resources for EBS could be severely compromised when the APEX instance becomes hungry for more resources in the form of processing and memory. Follow the Oracle recommendations always for hosting ORDS from a different server. I will post comprehensive suggestions compiled by AI by the end of this post. This hack was tested against an instance of Oracle EBS R12 12.2.14 & should work with R12 12.2.x versions. Point to interest, version 12.2.14 is the initial version of R12 that tightly integrates APEX, hence do not expect your applications start enjoying the same fruits, if you are on a previous release. Regardless, the reverse proxy method should work.

Let us hack

As application manager user, shutdown the application instance and run the below command

grep "ssl.conf" $FND_TOP/admin/driver/fndtmpl.drv

This should provide you an output like the below

      fnd admin/template oracle_apache_ssl_conf_FMW.tmp INSTE8 <s_ohs_instance_loc>/config/OHS/<s_ohs_component> oracle_apache_ssl.conf 600

Once we have the template name, for this case, “oracle_apache_ssl_conf_FMW.tmp”, we should copy this template to $FND_TOP/admin/template/custom folder. If there is no custom folder, create one. Please note, “custom” is the only allowed name and case sensitive. Always read the header area of the template, that will help you to understand whether the template will be merged during the next autoconfig run.

cp $FND_TOP/admin/template/oracle_apache_ssl_conf_FMW.tmp $FND_TOP/admin/template/custom/oracle_apache_ssl_conf_FMW.tmp

Using your favorite text editor, open up the template file from custom folder and add the following entries by the dead end of the file

<IfModule mod_proxy.c>
    ProxyRequests Off
    ProxyPreserveHost On
    ProxyPass /ords http://fullyqualifieddomainname:8080/ords
    ProxyPassReverse /ords http://fullyqualifieddomainname:8080/ords
    ProxyPass /i http://fullyqualifieddomainname:8080/i
    ProxyPassReverse /i http://fullyqualifieddomainname:8080/i
    <Location /ords>
        RequestHeader set X-Forwarded-Proto "https"
        RequestHeader set X-Forwarded-Port "443"
    </Location>
</IfModule>

Here the fully qualified domain name should be replaced with your hostname, for example “apps.example.com”

Now run the below commands

$ADMIN_SCRIPTS_HOME/adadminsrvctl.sh start
perl $AD_TOP/bin/adSyncContext.pl contextfile=$CONTEXT_FILE

It’s time to run “autoconfig“. Please make sure that all the above exercises were performed on the run file edition environment.

Once the autoconfig completes “successfully”, quickly ensure the OHS instance has the “oracle_apache_ssl.conf” file amended with custom template entries.

grep "ProxyPass /ords" $FMW_HOME/webtier/instances/*/config/OHS/*/oracle_apache_ssl.conf

If you see a result like the below

ProxyPass /ords http://fullyqualifieddomainname:8080/ords

That means your custom template was merged and you can proceed to next configuration modification. This time we will be making a change to Apache-Tomcat web server. Switch to your Apache-tomcat installation folder, for example ‘/u01/apache-tomcat/conf’, make a backup for the file server.xml. Now, edit the server.xml file and replace the block that looks like this

<Connector port="8080" protocol="HTTP/1.1"
               connectionTimeout="20000"
               redirectPort="8443" />

with the below

<Connector port="8080" protocol="HTTP/1.1"
           connectionTimeout="20000"
           proxyName="fullyqualifieddomainname"
           proxyPort="443"
           scheme="https"
           secure="true"
           SSLEnabled="false"
           URIEncoding="UTF-8" />

Save the configuration file. Start EBS application tier and Apache-Tomcat server sequentially. You should able to access your ORDS instance by calling https://hostname/ords now. In addition you may able to make these entries using enterprise manager OHS plug-ins.

By bringing the ORDS instance under the same SSL hood, you are going to avoid multiple access issues, one of them being the “mixed content” error when the EBS tries to access the APEX applications using ORDS that is HTTP. Although this hack is to survive future autoconfig and patching cycles, NOT AT ALL recommended.

Please consider the suggestions compiled by one of the major AI agents, as listed below.

Prompt

Currently we are hosting both ORACLE EBS R12 and APEX under the same hostname. Can you find Oracle’s recommendations for independent hosts for each product? Further help me to understand the implication incase if we continue with the current setup, achieved by modifying ssl.conf using template for reverse proxy. Maximize your explanations, recommendations and assessment.

AI response

Oracle’s official guidance on deploying E-Business Suite (EBS R12) and Oracle Application Express (APEX) emphasizes separation of concerns in architecture. Let’s break this down into recommendations, implications, and strategic assessment.

🔹 Oracle’s Recommendations

  • Independent Middle Tiers: Oracle recommends deploying APEX and EBS on separate middle-tier hosts.
    • EBS R12 relies on Oracle WebLogic + Forms/Servlets stack.
    • APEX runs on Oracle REST Data Services (ORDS), typically deployed on a standalone WebLogic, Tomcat, or Oracle HTTP Server.
  • Reverse Proxy Usage: Oracle supports reverse proxying (via Oracle HTTP Server or Apache) but recommends distinct hostnames or subdomains (e.g., ebs.company.com vs. apex.company.com) for clarity, isolation, and SSL management.
  • Security & Maintainability: Oracle’s technical papers highlight that mixing workloads under one hostname complicates SSL termination, cookie/session management, and audit compliance.

🔹 Implications of Current Setup (Same Hostname via ssl.conf Reverse Proxy)

AreaRisk/Implication
SecurityShared hostname increases risk of cross-application cookie leakage, session hijacking, and difficulty in applying distinct SSL/TLS policies.
Audit & ComplianceRegulatory audits (SOX, GDPR, VAT compliance) often require clear segregation of application boundaries. Mixing under one hostname weakens traceability.
Operational ComplexityReverse proxy rules in ssl.conf must be carefully maintained. Any misconfiguration can expose APEX apps through EBS paths or vice versa.
PerformanceBoth stacks compete for network and SSL termination resources. Troubleshooting latency or bottlenecks becomes harder.
Patching & UpgradesEBS patches (WebLogic, Forms) and APEX/ORDS upgrades have different cycles. Sharing a hostname risks downtime overlap and dependency conflicts.
Disaster RecoveryHostname-level coupling complicates failover. Independent hosts allow selective DR strategies (e.g., APEX-only failover without impacting EBS).

🔹 Strategic Assessment & Recommendations

  1. Move Toward Independent Hosts
    • Assign separate DNS names and SSL certificates.
    • Example: ebs.company.com → EBS R12 stack; apex.company.com → ORDS/APEX stack.
    • This aligns with Oracle’s recommended middle-tier deployment architecture.
  2. If You Continue Current Setup
    • Harden reverse proxy rules: ensure strict path-based routing (/apex/* vs /OA_HTML/*).
    • Isolate cookies: configure SameSite, Secure, and HttpOnly flags to prevent leakage.
    • Maintain separate SSL contexts within ssl.conf for each app.
    • Document routing logic for audit purposes.
  3. Future-Proofing
  • Independent hosts simplify cloud migration (OCI, AWS).
  • Easier to scale APEX independently (APEX often grows faster with custom apps).
  • Supports hybrid DR strategies and microservices-style extension of EBS.

🔹 My Assessment

Continuing with the same hostname setup is technically feasible but audit-unfriendly, harder to maintain, and risk-prone. Oracle’s stance is clear: separate middle tiers and hostnames provide cleaner architecture, stronger compliance, and operational resilience.

Oracle EBS R12 | Monitoring tablespace | Sending email using utl_smtp package

On By windows7bugsIn Linux, NOT a DBA, Oracle Applications(R12), Oracle Database 19c, Oracle PL/SQLLeave a comment

Today we will checkout a PL/SQL procedure that will keep monitoring Oracle EBS application specific Tablespaces and send emails to concerned parties whenever the free storage falls below a certain percentage. The below stored procedure checks the free spaces of all Tablespaces that have a name starting with “APPS”. 

create or replace procedure XXTBLSPCE_MAIL( errbuf  OUT    VARCHAR2, retcode   OUT    VARCHAR2) AS
cursor c1 is
SELECT F.TABLESPACE_NAME,
(T.TOTAL_SPACE -  F.FREE_SPACE) "USED_MB",
(F.FREE_SPACE) "FREE_MB",
(T.TOTAL_SPACE) "TOTAL_MB",
(ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)) PER_FREE
 FROM   (
 SELECT       TABLESPACE_NAME,
 ROUND (SUM (BLOCKS*(SELECT VALUE/1024
 FROM V$PARAMETER
 WHERE NAME = 'db_block_size')/1024)
 ) FREE_SPACE
 FROM DBA_FREE_SPACE
 WHERE TABLESPACE_NAME LIKE 'APPS%'
 GROUP BY TABLESPACE_NAME
 ) F,
 (
 SELECT TABLESPACE_NAME,
 ROUND (SUM (BYTES/1048576)) TOTAL_SPACE
 FROM DBA_DATA_FILES
 GROUP BY TABLESPACE_NAME
 ) T
 WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME
 AND (ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)) < 10;
 
 /* Mail specific variables*/

mailhost VARCHAR2 (30) := 'smtp.yourmaildomain.com';
mail_conn utl_smtp.connection;
crlf VARCHAR2(2) := CHR (13) || CHR (10);
message_1 CLOB;
v_email varchar2(16000);
v_sub varchar2(10000);
vcount NUMBER := 0;

name_array     DBMS_SQL.varchar2_table;

CC_parties     VARCHAR2 (2000) := NULL;

Cursor ec1 is
(select 'rec1.it@yourmaildomain.com'  user_email from dual
union all
select 'rec2.it@yourmaildomain.com'  user_email from dual
);


 v_Mail_Exception Exception;
 
 --15/10/2020
 
   l_host_name varchar2(240);
   l_db_name varchar2(240);
 
 BEGIN
 
  BEGIN
        select sys_context ( 'USERENV', 'DB_NAME' ) db_name,
        sys_context ( 'USERENV', 'SERVER_HOST' ) db_host into l_db_name, l_host_name
        from dual;
        EXCEPTION 
        WHEN NO_DATA_FOUND THEN
        l_host_name := 'NO HOST';
        l_db_name := 'NO DBNAME';
        
        END;
        

mail_conn := utl_smtp.open_connection (mailhost, 25);
utl_smtp.helo (mail_conn, mailhost);
utl_smtp.mail (mail_conn, 'sender@yourmaildomain.com');

FOR m IN ec1
   LOOP
      vcount := vcount + 1;
      name_array (VCOUNT) := m.user_email;
   END LOOP;
   
   

 FOR n IN name_array.FIRST .. name_array.LAST
   LOOP
      CC_parties := CC_parties || ';' || name_array (n);
      UTL_SMTP.Rcpt (mail_conn, name_array (n));
   END LOOP;

if CC_parties is NULL then
RAISE v_Mail_Exception;
end if;

for ts in c1 loop
if (ts.FREE_MB < 1024) then
if (trim(message_1) is not null) then
message_1 := message_1||ts.tablespace_name ||' is running out of storage space. Current free space is '||ts.FREE_MB||' MB. ';
else
message_1 := ts.tablespace_name ||' is running out of storage space. Current free space is '||ts.FREE_MB||' MB. ';
end if;
end if;
end loop;

if (trim(message_1) is not null) then
message_1 := message_1||' ( Note: add datafiles with size nothing less than 2GB for APPS_TS tablespaces.) '; 

v_sub := 'Subject: '||'Table Space(s) running out of space! Urgent(Instance: '||l_host_name||', Database: '||l_db_name||' )';
 message_1 :=  'From: Oracle Database sender  < sender@yourmaildomain.com >' ||crlf||v_sub||crlf||'To:'||CC_parties||crlf||crlf||crlf||message_1;
 
 utl_smtp.data(mail_conn, 'MIME-Version: 1.0' ||CHR(13)||
 CHR(10)||'Content-type: text/html' || CHR(13)||CHR(10)||message_1);
 utl_smtp.quit (mail_conn);
 
 end if;
 

EXCEPTION
WHEN v_Mail_Exception Then
   null;
WHEN OTHERS THEN

--err_code := SQLCODE;
--err_msg := SUBSTR(SQLERRM, 1, 200);
message_1 := 'Failed to send email, error explanation:  '||SQLCODE||'  '||SQLERRM;

v_sub := 'Subject: '||'Table Space(s) running out of space! Urgent';
message_1 := 'From: Oracle Database sender  < sender@yourmaildomain.com >' ||crlf||v_sub||crlf||crlf||message_1;

 utl_smtp.data(mail_conn, 'MIME-Version: 1.0' ||CHR(13)||
 CHR(10)||'Content-type: text/html' || CHR(13)||CHR(10)||message_1);
 utl_smtp.quit (mail_conn);
 

 End;

Create a concurrent program and submit as a scheduled, setting up frequencies based on your specific requirements. The above program works perfectly under the below conditions.

Oracle EBS R12 12.2.10, 19c multi-tenant database, Local SMTP server.

Oracle Forms Developer 10g crashes when opening modules

On By windows7bugsIn Oracle FormsLeave a comment

Oracle developer Forms version 10g is still used. Used by Oracle for their E-Business Suite & they will continue using this obsolete, unstable version until the day EBS is discontinued. So, let us see how to make the best out of what is available.

One of the major nuisances a developer (definitely) will come across while using this obsolete version of Oracle Developer on Windows 7 or later versions of Microsoft OS is, opening a module that has underlying database level objects changed (procedures/functions/packages), this IDE crashes without generating any error logs. Simple as it is. This could completely cripple the developer, especially when rapid developments miss to document the changes.

Is it possible to open FMB files without being connected to database? it is another BIG NO NO. Developer 10G will crash immediately incase if you try to open an FMB file that has database objects referred. So what is the next possible solution?

Well there is a solution that is dirty, having to install Oracle Forms Developer 12c (12.2.1.4). Just install the suite as standalone without Weblogic. This version is fault tolerant and will not try to validate the database level object calls & crash. By this, you can verify, compile and see what objects were changed, non-existing etcetera.

Oh yes, make sure you have copied your source FMB files to a new folder prior opening them with Forms Developer 12c!. Always remember, Oracle EBS R12 only compiles FMB files created using Oracle forms 10g. You could lose your original files forever if you forgot to do that.

Here is an excellent article explains how to install standalone Oracle Forms 12c.

https://oracle-base.com/articles/12c/standalone-forms-builder-12c-installation-on-windows-1221