DMZ configuration R12

DMZ configuration

Implemented DMZ for the project, its not very difficult to implement but bit complex to troubleshoot.Most complex on troubleshooting firewall issue.

My architecture goes like this…

LB -> Load balancer
RP -> Reverse Proxy server

LB –>RP–>EBS(ISUPPLIER NODE) (APPS TIER) –> EBS (db tier)

Load balancer — Not much involvement, F5 load balancer … asked sysadmin to point to reverse proxy URL on port 8080.

Reverse proxy server — Installed standalone OHS (oracle HTTP server), I had trouble while redirection because I chosen a wrong software OAs (Oracle Application server)

Test from RP –> EBS .. telnet <EBS host> port number

EBS —- Mostly context file changes

Create Context File
– create /{inst_name}/inst/apps/$INST_NAME_$dmz_hostname/appl/admin folder for new MT server
– copy context file from Private MT server into the above location with new name.
– Modify the context file as under:
– change all the references to Private MT server to Public MT server, except the following:

s_cphost
s_javamailer_imaphost
s_wfhost
s_smtphost
s_mwahost

– change following parameters for the Public URL
# webentryhost: company (public URL)
# webentrydomain: domain.com (public URL)
# login_page: https://company.domain.com/oa_servlet/AppsLogin
#s_external_url:https://company.domain.com
# s_active_webport : 443

login to the private mt server as ap{inst_name}.

Run the following command (replace the apps-schema-name/apps-passwd with actual values)

sqlplus <apps-schema-name>/<apps-passwd> @<FND_TOP>/patch/115/sql/txkChangeProfH.sql SERVRESP

Change the Node Trust Level Profile Option
– add node to fnd_nodes using the Sysadmin –> Install –> nodes
– update “Node Trust Level” profile option for that node as “External”
– Assign Self Service Responsibilities to sysadmin

Set “Responsibility Trust Level”

9) Update Agent profiles as DMZ server level to point to DMZ URL .

Login as system Admin Resp – Profiles – Select DMZ server name and search for required profiles .

– Application Framework Agent
– Applications Web Agent
– Applications JSP Agent
– Apps Servlet Agent

Update URL for DMZ host to point to DMZ URL.
Do not update URL at site level !

Run adconfig as under:
adconfig.sh contextfile=/{inst_name}/inst/apps/$INST_NAME_$dmz_hostname/appl/admin/$INST_NAME_$dmz_hostname.xml appspass=apps run=INSTE8_SETUP

Verify DMZ Setup.
– start all services on dmz server (keep the private mt down)
– loging to the DMZ url
– browse through the links to see if the basic navigation works fine.

Bulk collect comes handy for plsql

Recently I got a chance to automate some stuff in pl/sql . I am able to complete 99% of the program but stuck with ora-01422

01422, 00000, "exact fetch returns more than requested number of rows"
// *Cause: The number specified in exact fetch is less than the rows returned.
// *Action: Rewrite the query or change number of rows requested

I googled but I didnt find anything useful , most of  the advise to use "rownum=1" or "Raise exception error – too many rows" but I want all the rows , I tried with cursor but its not effective.

Finally , I got a link to store more than one row that is BULK COLLECT . BULK COLLECT is very fast to store the result in output.

Syntax to use bulk collect

type inst_t is table of varchar(200); — A Table Type
<variable>   inst_t;

 SELECT INSTANCE_ID bulk collect into <variable > FROM <Table name>

Long Data type

Long data type is a most trouble data type to DBA’s. Instead of Long use CLOB or BLOB. If the table is created with Long datatype,use to_lob function to change to CLOB or BLOB

 a LONG into a CLOB 

 a LONG RAW into a BLOB

Pasting reply from the forum post

Once choice could be to export/import the table.
Now would be the time to consider converting it to BLOB:

SQL> create table t(col long raw) ;

Table created.

SQL> insert into t values (utl_raw.cast_to_raw('long')) ;

1 row created.

SQL> create table t2 as select to_lob(col) col from t ;

Table created.

SQL> desc t2
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ----
 COL                                                            BLOB

SQL> insert into t2 select to_lob(col) from t ;

1 row created.

SQL>

ScribeFire makes life easier for Bloggers

ScribeFire makes life easier for Bloggers . ScribeFire is Firefox addon through we can edit ,create and delete the blog contents . Its very handy and lighter to use . Thanks to Firefox .

This blog is one year old

I take this opportunity to thank everyone encouraged me to start this blog

Difference in registry key for Windows

This topic is common to all . I found its very interesting . It will be helpful to Windows oracle APPS customer.

HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet

ControlSet001 = Set of boot information used to boot the system.

ControlSet002 = Set of boot information used for “Last Known
Good” configuration.

CurrentControlSet = Maps to either 001 or 002 above, depending
on how the system was booted. All edits should be done to this set,
as doing so will assure you’re modifying whichever control set is
currently in force.

How to start the mobile web application in R12.

Starting the Mobile Web Application (mwa)

Go to $INST_TOP/admin/scripts . Check mwactl its instantiated by Autoconfig.

Windows -mwactl.cmd start
UNIX -mwactl.sh start

In windows ,Once you fired the above command .It will open two or three command prompt with below message.

Message
———
“MWA Telnet server Release: 1.0.8.4 [December 12th 2002]”
E:\oracle\VIS\apps\tech_st\10.1.3\appsutil\jdk\bin\java.exe -Doracle.apps.mwa=E:
\oracle\VIS\inst\apps\VIS_ap703app -Doracle.apps.inst=E:\oracle\VIS\inst\apps\VI
S_ap703app -mx256m -ms128m -cp “E:\oracle\VIS\apps\tech_st\10.1.3\appsutil\jdk\
lib\dt.jar;E:\oracle\VIS\apps\tech_st\10.1.3\appsutil\jdk\lib\tools.jar;E:\oracl
e\VIS\apps\tech_st\10.1.3\appsutil\jdk\jre\lib\rt.jar;E:\oracle\VIS\apps\
apps_st\comn\java\lib\appsborg2.zip;E:\oracle\VIS\apps\apps_st\comn\java
\classes”

oracl
e.apps.mwa.presentation.telnet.Listener 10202
Created server socket : listening on port 10202
Server startup is successful.

Two ways to check what are the ports are available for mobile application:

1. From the above message you can find what are the ports are open for the mobile application .. here 10202 in this port is listening .
2. Login to sysadmin/sysadmin.
   system administrator–>Oracle Applications Manager–>Hosts—->view configuration .

you can find the “MSCA Server Port Number 10200-10205”

To test whether MWA is started . Type
“telnet hostname.domainname 10200”

Compilation and Translation error when compiling JSP on R12

This error will occur on R12 environment and AS 10.1.3.0 (Windows instance not sure about unix instance)

Error:
——–
[5424] !!TRANSLATION ERROR(0) ahlapprCreateSpaceARMain.jsp:
java.io.IOException: The filename, directory name, or volume label syntax is
incorrect

Problem:
———
In windows environment,after new installation i tried to compile JSP and i used to below command to compile.
———————————————————————-
perl -x %fnd_top%\patch\115\bin\ojspCompile.pl –compile –flush –quiet
—————————————————————————————————
Note in windows env you need to mention perl -x in the prefix.After hard days breaking my head whats gone wrong with the installation.After i find out drive letter for jsp_dir in ojspCompile.conf is small.JSP class files is class sensitive .So its unable to determine the path.

Location of ojspCompile.conf : %INST_TOP%\appl\admin\

solution:
——–
1. Change the drive letter to Upper case in the ojspCompile.conf
For e.g.
jsp_dir = e:\VIS\apps\apps_st\comn\webapps\oacore\html
to
jsp_dir = E:\VIS\apps\apps_st\comn\webapps\oacore\html

2.Raise an SR ask for new ojspCompile.pl

3.Add a following piece of code in ojspCompile.pl (Use it at your own risk )
----------------------------------------------------
if($ISWINNT) {
my $jsp_dir_nt = ucfirst($jsp_dir);
$jsp_dir = $jsp_dir_nt;
}
----------------------------------------------------

Apps login error

Error :-
——-
Unable to login: &REASON has been detected in &ROUTINE

Problem:
———
log file -OACoreGroup.0.stderr f
[fnd.common.logging.DebugEventManager.handlerException]:

java.sql.SQLException: ORA-01000: maximum open cursors exceeded

Solution:
——–
ORA-01000: maximum open cursors exceeded

– Increase the SGA_TARGET parameter to 1GB

– Increase the OPEN_CURSORS parameter in init.ora file present in database

To see if you’ve set OPEN_CURSORS high enough, monitor v$sesstat for the maximum opened cursors current. If your sessions are running close to the limit increase the value of OPEN_CURSORS parameter in init.ora file as per your requirement. You can run the following query which has a sample output

SQL> select max(a.value) as highest_open_cur, p.value as

 max_open_cur from v$sesstat a, v$statname b, v$parameter p

where a.statistic# = b.statistic#and b.name = 'opened cursors current'and

p.name= 'open_cursors'  group by p.value;

How to find forms version using command line?

Execute following command,first line will show the version.

$ORACLE_HOME/bin/frmcmp help=y