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>

Find the current SQL in the database

select username, sql_text from v$session s, v$sqlarea sql where s.sql_address = sql.address and s.sql_hash_value = sql.hash_value;

Important for Windows Oracle Apps customer

Unlike UNIX oracle apps ,Windows bit different and tricky to clean the old instance .  If your a Windows R12 oracle apps customer this post will be very useful .  Please follow the note to delete the failed/old oracle instance .

How to Remove an Oracle E-Business Suite Release 12 Windows Environment (Metalink Note 567507.1)

More information available on Steven Chan blog – http://blogs.oracle.com/stevenChan/2008/07/new_whitepaper_cleaning_up_app.html “New Whitepaper: Cleaning Up Apps 12 on Windows“

Enabling flashback on Oracle

Flashback query is not enabled by default and must be turned on in following sequence. We will set retention to 10 hours (600 minutes), set recovery size up to 2 GB in file “/recovery/flashback”:

shutdown immediate;
startup mount;
alter database archivelog;
alter system set DB_FLASHBACK_RETENTION_TARGET=600;
alter system set DB_RECOVERY_FILE_DEST_SIZE=2G;
alter system set DB_RECOVERY_FILE_DEST=’/recovery/flashback’;
alter database flashback on;
alter database open;

Deleting Windows Services using perl

Use it at your own risk

serviceDel.pl

print “Deleting Windows Services perl….\n”;
my $cmd =”sc query \”<SERVICE NAME>\” | grep RUNNING | cut -c33-“;
my $ret =`$cmd`;
chomp($ret);
$ret =~ s/^\s+//;
$ret =~ s/\s+$//;

if(“$ret” eq “RUNNING”){
my $cmd = “sc stop \”<SERVICE NAME>\””;
my $rt=system($cmd);
print “Stop service returned $rt…\n”;
}

while(true){
sleep(5);
my $cmd =”sc query \”<SERVICE NAME>\” | grep STOPPED | cut -c33-“;
my $c = `$cmd`;
chomp($c);
$c =~ s/^\s+//;
$c =~ s/\s+$//;
if ( “$c” eq “STOPPED”){print “SERVICE STOPPED \n”;last;}
print “SERVICE iS RUNNING \n”;
}

my $cmd = “sc delete \”<SERVICE NAME>\””;
my $rt  =  system($cmd);
print “Delete Service returned $rt…\n”;

ARCHIVE LOG START is DEPRECATED in 11g

In 11g database ,”alter system archive log start;” this statement is not working because from the alert logs archive log start is deprecated . So ALTER DATABASE ARCHIVELOG is enough to start archive process .

ARCHIVE LOG START has been deprecated – see ALTER DATABASE ARCHIVELOG

Rename Oracle Table Syntax

alter table table_name rename to new_table_name;

Applications Short Name for most of the Apps Products

Application short names for all product
—————————————————————————
PFT Performance_Analyzer
RMG Risk_Manager
FTP Transfer_Pricing
DNA Development
BLC Utility_Billing
PTX Patch_Tracking_System
EMS Environment_Management_System
RCM Regulatory_Capital_Manager
FND Application_Object_Library
SYSADMIN System_Administration
AU Application_Utilities
AD Applications_DBA
SHT Applications_Shared_Technology
SQLGL General_Ledger
OFA Assets
ALR Alert
RG Application_Report_Generator
CS Service
CCT Telephony_Manager
ECX XML_Gateway
EC e-Commerce_Gateway
ICX Self-Service_Web_Applications
XTR Treasury
AZ Application_Implementation
BIS Applications_BIS
SQLAP Payables
PO Purchasing
CHV Supplier_Scheduling
AR Receivables
PN Property_Manager
QA Quality
CE Cash_Management
FRM Report_Manager
EAA SEM_Exchange
BSC Balanced_Scorecard
ABM Activity_Based_Management
EVM Value_Based_Management
FEM Strategic_Enterprise_Management
PA Projects
AS Sales_Foundation
CN Incentive_Compensation
POM Exchange
OE Order_Entry
WMS Warehouse_Management
turing_Scheduling
INV Inventory
MWA Mobile_Applications
WSM Shop_Floor_Management
FII Financial_Intelligence
OPI Operations_Intelligence
POA Purchasing_Intelligence
HRI Human_Resources_Intelligence
ISC Supply_Chain_Intelligence
AN Sales_Analysis
OKC Contracts_Core
CSC Customer_Care
CSD Depot_Repair
CSF Field_Service
CSS Support
OKS Service_Contracts
ME Controlled_Availability_Product
BIM Marketing_Intelligence
BIC Customer_Intelligence
IES Scripting
AMV Marketing_Encyclopedia_System
AST TeleSales
ASF Sales_Online
CSP Spares_Management
OKX Contracts_Integration
ODQ Data_Query
AMS Marketing
XNM Marketing_for_Communications
XNC Sales_for_Communications
XNS Service_for_Communications
XNP Number_Portability
XDP Provisioning
FPT Banking_Center
IEO Interaction_Center_Technology
GMA Process_Manufacturing_Systems
GMI Process_Manufacturing_Inventory
GMD Process_Manufacturing_Product_Development
GME Process_Manufacturing_Process_Execution
GMP Process_Manufacturing_Process_Planning
GMF Process_Manufacturing_Financials
GML Process_Manufacturing_Logistics
GR Process_Manufacturing_Regulatory_Management
PMI Process_Manufacturing_Intelligence
AX Global_Accounting_Engine
AK Common_Modules-AK
XLA Subledger_Accounting
ONT Order_Management
QP Advanced_Pricing
RLM Release_Management
VEA Automotive
WSH Shipping_Execution
IBA iMarketing
IBE iStore
IBU iSupport
IBY iPayment
IBP Bill_Presentment_&_Payment
BIL Sales_Intelligence
BIX Interaction_Center_Intelligence
IEM Email_Center
OZP Trade_Planning
OZF Trade_Management
OZS iClaims
ASG CRM_Gateway_for_Mobile_Devices
JTF CRM_Foundation
IEX Collections
IEU Universal_Work_Queue
ASO Order_Capture
CSR Scheduler
IEB Interaction_Blending
MFG Manufacturing
BOM Bills_of_Material
ENG Engineering
MRP Master_Scheduling/MRP
CRP Capacity
WIP Work_in_Process
CST Cost_Management
CZ Configurator
RLA Release_Management_Integration_Kit
VEH Automotive_Integration_Kit
PJM Project_Manufacturing
FLM Flow_Manufacturing
MSO Constraint_Based_Optimization
MSC Advanced_Supply_Chain_Planning
RHX Advanced_Planning_Foundation
OKE Project_Contracts
PER Human_Resources
PAY Payroll
FF FastFormula
DT DateTrack
SSP SSP
BEN Advanced_Benefits
HXT Time_and_Labor
HXC Time_and_Labor_Engine
OTA Learning_Management
JA Asia/Pacific_Localizations
JE European_Localizations
JG Regional_Localizations
JL Latin_America_Localizations
GHR US_Federal_Human_Resources
PQH Public_Sector_HR
PQP Public_Sector_Payroll
PSB Public_Sector_Budgeting
GMS Grants_Accounting
PSP Labor_Distribution
IGW Grants_Proposal
IGS Student_Systems
IGF Financial_Aid
IGC Contract_Commitment
PSA Public_Sector_Financials
IPA Capital_Resource_Logistics_-_Projects
CUI Network_Logistics_-_Inventory
CUP Network_Logistics_-_Purchasing
CUF Capital_Resource_Logistics_-_Financials
CUS Network_Logistics
CUN Network_Logistics_-_NATS
CUA Capital_Resource_Logistics_-_Assets
FV Federal_Financials
CUE Billing_Connect
EAM Enterprise_Asset_Management
TE Transportation_Execution
IGI Public_Sector_Financials_International
ITG Internet_Procurement_Enterprise_Connector
MSR Inventory_Optimization
PV Partner_Management
ASL Sales_Offline
POS iSupplier_Portal
AHM Hosting_Manager
ASP Field_Sales/Palm_Devices
BIV Service_Intelligence
CSI Install_Base
CSL Field_Service/Laptop
CUG Citizen_Interaction_Center
IMT iMeeting_(obsolete)
OKI Contracts_Intelligence
IEC Advanced_Outbound_Telephony
CSE Enterprise_Install_Base
OKO Contracts_for_Sales
JTS CRM_Self_Service_Administration
JTM Mobile_Application_Foundation
AHL Complex_Maintenance_Repair_and_Overhaul
OKB Contracts_for_Subscriptions_(obsolete)
IMC Customers_Online
XNI Install_Base_Intelligence
OKR Contracts_for_Rights
IPD Product_Development
ENI Product_Intelligence
BNE Web_Applications_Desktop_Integrator
QRM Risk_Management
PON Sourcing
OKL Lease_Management
IBC Content_Manager
AMF Fulfillment_Services
QOT Quoting
CSM Field_Service/Palm
DOM Document_Managment_and_Collaboration
Advanced_Product_Catalog
DDD CADView-3D
PJI Project_Intelligence
XNB eBusiness_Billing
ZFA Financial_Analyzer
ZSA Sales_Analyzer
CLN Supply_Chain_Trading_Connector_for_RosettaNet
EDR E-Records
PRP Proposals
AMW Internal_Controls_Manager
XLE Legal_Entity_Configurator
ASN Sales
MST Transportation_Planning
FUN Financials_Common_Modules
GCS Global_Consolidation_System
XDO XML_Publisher
ZX E-Business_Tax
LNS Loans
IA iAssets
FPA Portfolio_Analyzer
ZPB Enterprise_Planning_and_Budgeting
———————————————————————————–