DBA's Journey

Tuesday, November 1, 2016

Oracle Apps DBA - Useful Queries

Status of Concurrent Manager & Requests:

How to Find Session Details of conurrent requet id:

select a.sid,a.event,a.sql_id,a.blocking_session,a.action from v$session a ,V$process b,apps.fnd_concurrent_requests c where a.paddr=b.addr and b.spid=c.oracle_process_id and c.request_id='&request_id';

How to Find Running Concurrent Programs:

select user_concurrent_program_name from apps.fnd_conc_req_summary_v where status_code='R' and phase_code='R';

Concurrent Manager Status:

set verify off

set lines 256

set trims ON

set pages 60

col concurrent_queue_id format 99999 heading "QUEUE Id"

col concurrent_queue_name format a20 trunc heading "QUEUE Code"

col user_concurrent_queue_name format a30 trunc heading "Concurrent Queue Name"

col max_processes format 999 heading "Max"

col running_processes format 999 heading "Act"

col running format 999 heading "Run"

col target_node format a15 heading "Node"

col status format a12 trunc heading "Status"

col run format 9999 heading 'Run'

col pend format 9999 heading 'Pending'

col cmgr_program FOR a65;

SELECT 'Instance : '

||NAME instance_name

FROM v$database;

Prompt ===========================

Prompt concurrent manager status

Prompt ===========================

SELECT q.concurrent_queue_id,

q.concurrent_queue_name,

q.user_concurrent_queue_name,

q.target_node,

q.max_processes,

q.running_processes,

running.run running,

pending.pend,

Decode(q.control_code, 'D', 'Deactivating',

'E', 'Deactivated',

'N', 'Node unavai',

'A', 'Activating',

'X', 'Terminated',

'T', 'Terminating',

'V', 'Verifying',

'O', 'Suspending',

'P', 'Suspended',

'Q', 'Resuming',

'R', 'Restarting') status

FROM (SELECT concurrent_queue_name,

COUNT(phase_code) run

FROM fnd_concurrent_worker_requests

WHERE phase_code = 'R'

AND hold_flag != 'Y'

AND requested_start_date <= SYSDATE GROUP BY concurrent_queue_name) running, (SELECT concurrent_queue_name, COUNT(phase_code) pend FROM fnd_concurrent_worker_requests WHERE phase_code = 'P' AND hold_flag != 'Y' AND requested_start_date <= SYSDATE GROUP BY concurrent_queue_name) pending, apps.fnd_concurrent_queues_vl q WHERE q.concurrent_queue_name = running.concurrent_queue_name(+) AND q.concurrent_queue_name = pending.concurrent_queue_name(+) AND q.enabled_flag = 'Y' ORDER BY Decode(q.application_id, 0, Decode(q.concurrent_queue_id, 1, 1,4, 2)), Sign(q.max_processes) DESC,q.concurrent_queue_name, q.application_id;

List of Concurrent Requests Completed with Error in last two hours:

set linesize 200 pagesize 200 feedback on;

col USER_CONCURRENT_PROGRAM_NAME for a40

col COMPLETION_TEXT for a60

select a.user_concurrent_program_name , REQUEST_ID,DECODE(phase_code,'C','Completed',phase_code) phase_code, DECODE(status_code,'D', 'Cancelled' ,'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X','Terminated', 'C', 'Normal', status_code) status_code , to_char(ACTUAL_START_DATE,'DD-MON-YYYY HH24:MI:SS') START_DATE ,to_char(actual_completion_date,'DD-MON-YYYY HH24:MI:SS') END_DATE,COMPLETION_TEXT from fnd_concurrent_programs_tl a, fnd_concurrent_requests b where a.concurrent_program_id=b.concurrent_program_id and actual_completion_date > sysdate - (2/24) and phase_code = 'C' and status_code = 'E';

List of Concurrent Requests Completed with Warninng in last two hours:

set linesize 200 pagesize 200 feedback on;

col USER_CONCURRENT_PROGRAM_NAME for a40

col COMPLETION_TEXT for a50

select a.user_concurrent_program_name , REQUEST_ID

,PHASE_CODE , STATUS_CODE , to_char(ACTUAL_START_DATE,'DD-MON-YYYY HH24:MI:SS') START_DATE ,to_char(actual_completion_date,'DD-MON-YYYY HH24:MI:SS') END_DATE,COMPLETION_TEXT from fnd_concurrent_programs_tl a, fnd_concurrent_requests b where a.concurrent_program_id=b.concurrent_program_id and actual_completion_date > sysdate - (2/24) and phase_code = 'C' and status_code = 'G' order by actual_completion_date;

List of Long Running Requests:

set linesize 200 pagesize 200 feedback on;

col USER_CONCURRENT_PROGRAM_NAME for a40;

select a.user_concurrent_program_name , REQUEST_ID

where a.concurrent_program_id=b.concurrent_program_id and

actual_start_date > sysdate - 1 and phase_code = 'R' and status_code = 'R';

Determine which concurrent Manager ran the particular request:

col USER_CONCURRENT_QUEUE_NAME for a100

select b.USER_CONCURRENT_QUEUE_NAME from fnd_concurrent_processes a,

fnd_concurrent_queues_vl b, fnd_concurrent_requests c

where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID

and a.CONCURRENT_PROCESS_ID = c.controlling_manager

and c.request_id = '&conc_reqid';

Determine the details of particular CM request:

set linesize 200 pagesize 200;

col USER_CONCURRENT_PROGRAM_NAME for a40

col completion_text for a50

SELECT request_id, user_concurrent_program_name, DECODE(phase_code,'C','Completed',phase_code)

phase_code, DECODE(status_code,'D', 'Cancelled' ,

'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X','Terminated', 'C', 'Normal', status_code) status_code, to_char(actual_start_date,'dd-mon-yy:hh24:mi:ss') Start_Date, to_char(actual_completion_date,'dd-mon-yy:hh24:mi:ss') completion_date,completion_text FROM apps.fnd_conc_req_summary_v WHERE request_id = '&req_id' ORDER BY 6 DESC;

TRACE Enabled Concurrent Porgrams:

select a.CONCURRENT_PROGRAM_ID, b.USER_CONCURRENT_PROGRAM_NAME "Program_Name", a.CONCURRENT_PROGRAM_NAME "Short_Name", a.APPLICATION_IDfrom apps.fnd_concurrent_programs a, apps.fnd_concurrent_programs_tl b where a.CONCURRENT_PROGRAM_ID=b.CONCURRENT_PROGRAM_ID and a.ENABLE_TRACE='Y';

Workflow Mailer:

Check the status of the componenet:

set pagesize 400

set linesize 120

set pagesize 50

column COMPONENT_NAME format a45

column STARTUP_MODE format a15

column COMPONENT_STATUS format a15

select fsc.COMPONENT_NAME,fsc.STARTUP_MODE,fsc.COMPONENT_STATUS

from APPS.FND_CONCURRENT_QUEUES_VL fcq, fnd_svc_components fsc

where fsc.concurrent_queue_id = fcq.concurrent_queue_id(+)

order by COMPONENT_STATUS , STARTUP_MODE , COMPONENT_NAME;

Find Workflow Mailer logfile:

select fl.meaning,fcp.process_status_code,

decode(fcq.concurrent_queue_name,'WFMLRSVC','maile r container','WFALSNRSVC','listener container',fcq.concurrent_queue_name),

fcp.concurrent_process_id,os_process_id, fcp.logfile_name

from fnd_concurrent_queues fcq, fnd_concurrent_processes fcp , fnd_lookups fl

where fcq.concurrent_queue_id=fcp.concurrent_queue_id and fcp.process_status_code='A'

and fl.lookup_type='CP_PROCESS_STATUS_CODE' and

fl.lookup_code=fcp.process_status_code

and concurrent_queue_name in('WFMLRSVC','WFALSNRSVC')

order by fcp.logfile_name;

Ready Count :

select msg_state,count(*) from applsys.aq$wf_notification_out group by msg_state;

Check The even't status in wf_deferred queue:

Ready = Activity is ready to be processed

Delayed = Activity will be processed later

Retained = Activity was already processed

Exception = Activity had an error

col event_name for a40

col state for a13

select wfd.user_data.event_name EVENT_NAME,

decode(wfd.state,

0, '0 = Ready',

1, '1 = Delayed',

2, '2 = Retained',

3, '3 = Exception',

to_char(substr(wfd.state,1,12))) State,

count(*) COUNT

from applsys.wf_deferred wfd

group by wfd.user_data.event_name, wfd.state

order by 3 desc, 1 asc;

select decode(wfd.state,

0, '0 = Ready',

1, '1 = Delayed',

2, '2 = Retained',

3, '3 = Exception',

to_char(substr(wfd.state,1,12))) State,

count(*) COUNT

from applsys.wf_deferred wfd

group by wfd.state

order by 2 desc, 1 asc;

R122 adop queries:

adop session status:

set pagesize 200;

set linesize 160;

col PREPARE_STATUS format a15

col node_name format a30

col APPLY_STATUS format a15

col CUTOVER_STATUS format a15

col ABORT_STATUS format a15

col STATUS format a15

select NODE_NAME,ADOP_SESSION_ID, PREPARE_STATUS , APPLY_STATUS ,CUTOVER_STATUS , CLEANUP_STATUS , ABORT_STATUS , STATUS from AD_ADOP_SESSIONS where ADOP_SESSION_ID=66;

Note: Y denotes that the phase is done

N denotes that the phase has not been completed

X denotes that the phase is not applicable

R denotes that the phase is running (in progress)

F denotes that the phase has failed

P (is applicable only to APPLY phase) denotes at least one patch is already applied for the session id

C denotes that the status of this ADOP session has completed

cutover statuses:

cutover_status='Y' 'COMPLETED'

cutover_status not in ('N','Y','X') and status='F' 'FAILED'

cutover_status='0' 'CUTOVER STARTED'

cutover_status='1' 'SERVICES SHUTDOWN COMPLETED'

cutover_status='3' 'DB CUTOVER COMPLETED'

cutover_status='D' 'FLIP SNAPSHOTS COMPLETED'

cutover_status='4' 'FS CUTOVER COMPLETED'

cutover_status='5' 'ADMIN STARTUP COMPLETED'

cutover_status='6' 'SERVICES STARTUP COMPLETED'

cutover_status='N' 'NOT STARTED'

cutover_status='X' 'NOT APPLICABLE'

fs_clone status:

set linesize 300

col node_name format a15

col clone_status format a20

col driver_file_name format a20

col clone_status format a20

select node_name,clone_status,driver_file_name,status,to_char(start_date,'DD-MON-YYYY HH:MI:SS PM'),to_char(end_date,'DD-MON-YYYY HH:MI:SS PM') from ad_adop_session_patches where adop_session_id=89;

To find the patches applied in the particular adop session:

select distinct BUG_NUMBER from AD_ADOP_SESSION_PATCHES where adop_session_id='&session_id';

set linesize 300;

select distinct bug_number,node_name,to_char(start_date ,'DD-MON-YYYY HH24:MI:SS') START_DATE,to_char(end_date,'DD-MON-YYYY HH24:MI:SS')END_DATE from ad_adop_session_patches where adop_session_id=67 group by bug_number,node_name,start_date,end_date order by bug_number;

select distinct bug_number,node_name,to_char(start_date ,'DD-MON-YYYY HH24:MI:SS') START_DATE,to_char(end_date,'DD-MON-YYYY HH24:MI:SS')END_DATE from ad_adop_session_patches where adop_session_id=&session_id and bug_number='&bug_number' group by bug_number,node_name,start_date,end_date order by bug_number;

AD,TXK VERSIONS:

SELECT abbreviation, codelevel FROM AD_TRACKABLE_ENTITIES WHERE abbreviation in ('txk','ad');

ETCC QUERIES:

set linesize 300;

col node_name format a10;

col database_name format a10;

col check_message format a50;

select database_name,node_name ,check_date ,component_name,component_version,check_message from apps.TXK_TCC_RESULTS where database_name='&DB_NAME';

Monday, October 17, 2016

Autoconfig

Autoconfig:

Oracle Applications has a complex architecture with many pieces of technology. For example Apache Web server, Apache Jserv, Forms Listener servlet (or forms server) etc. each of these has its own set of configuration files , which need to be set up correctly to function properly. Moreover Oracle Applications uses many profile options (e.g. Applications Web Agent, Applications Framework Agent etc) and other bits of information in the database to make the navigation seamless. These also need to be set correctly to function properly.

Managing these profile options and configuration files manually may give errors.So Autoconfig is a tool provided by the Oracle E-Business Suite to maintain these files and profile options. We can say AutoConfig is a collection of template configuration files that are used in combination with the appropriate database and applications context files to produce standardized environments.

Autoconfig maintains the environment specific values for the environment in an xml file (stored in $INST_TOP/appl/admin). When autoconfig run it overwrites the existing configuration files with new ones that it creates by merging the templates and the context file, it also updates the database by running the script it instantiates (merging a template with the values in the context file is termed as instantiating the template in Oracle parlance) from the script templates. That’s essentially what autoconfig does i.e. Instantiating configuration files from templates and updating database with values from the xml file. It does not move directories, check for availability of ports, update DNS entries and so on. These have to be done manually

How does it work?

Each configuration file has one corresponding template file (provided by autoconfig patches, stored in $PROD_TOP/admin/template directory). Profile options and other instance specific information in the database is maintained by many sql scripts, called from shell/perl scripts. These scripts also have corresponding template files (also provided by autoconfig patches, in $PROD_TOP/admin/templates).
In these template files all the environment specific values are replaced by placeholders (like %s_webhost%).

How to customize the template for autoconfig files:

First of all we need to decide which file need to be customized and if it is possible to do it

You cannot customize all AutoConfig template files.The AutoConfig template file cannot be customized if the "LOCK" keyword appears in the template file's entry in the product driver file. AutoConfig ignores custom template files that are marked with "LOCK".

For example, the following entry in /admin/driver/adtmpl.drv would prevent customization of the file adconfig.txt:
ad admin/template adconfig.txt INSTE8 /admin adconfig.txt 600 LOCK

Steps to customize the Autoconfig unlock files

1) You must be knowing the product top for the file.So Create the custom directory in $PROD_TOP/admin/template
Example
a. mkdir $FND_TOP/admin/template/custom
b. chmod 777 $FND_TOP/admin/template/custom

2) Determine the AutoConfig template file one wants to customize:
Execute the following command to find out the corresponding AutoConfig template file for the configuration file one want to customize:

Application /bin/adtmplreport.sh contextfile= target=
Database /appsutil/bin/adtmplreport.sh contextfile= target=

For example
$AD_TOP/bin/adtmplreport.sh contextfile=/appl/admin/.xml
target=$FND_TOP/admin/template/appsweb.cfg

The above command would return "$FND_TOP/admin/template/forms_web_1012_cfg.tmp", means
the "appsweb.cfg" file was created from the source Autoconfig template $FND_TOP/admin/template/forms_web_1012_cfg.tmp.

3) Copy the "forms_web_1012_cfg.tmp" into custom directory created earlier and then edit it to your requirements

a.Copy the template $FND_TOP/admin/template/forms_web_1012_cfg.tmp to $FND_TOP/admin/template/custom

b. Amend this template file and then run autoconfig ensuring that all applications middle tier processes are not running prior to running autoconfig

Some important points to remember:

1) If new templates comes ,the changes would be lost. If the patch which replaces the file runs the autoconfig,it would show any complain for new template. But if you run autoconfig manually and you have new version of customized template in admin/template,then it will complain about it

2) We can list all the customized template done through the command

$AD_TOP/bin/adtmplreport.sh contextfile= listcustom

Important Questions:

1.What is the location of adconfig log file?
$INST_TOP/admin/log/

2. Is it possible to restore a autoconfig run?
Partially. Adconfig will create a restore script at $INST_TOP/admin/out/. This restore.sh will copy the backed up files before autoconfig run to its original locations. But the profile values updated in the database can’t be restored back.

3. How to run autoconfig in test mode?
adchkcfg.sh script at $AD_TOP/bin. This script will run autoconfig in test
mode and create the difference file which tells us what is going to change, when u actually run autoconfig.

4. In how many phases autoconfig will run?

Ans : Autoconfig will run in 3 phases.

1.INIT – Instantiate the drivers and templates
2.SETUP – Fill the templated with values from xml and create files
3.PROFILE – Update the profile values in database.

5. Execute the following command to find out the corresponding AutoConfig template file for the configuration file one want to customize:

Application /bin/adtmplreport.sh contextfile= target=
Database /appsutil/bin/adtmplreport.sh contextfile= target=

For example
$AD_TOP/bin/adtmplreport.sh contextfile=/appl/admin/.xml
target=$FND_TOP/admin/template/appsweb.cfg

6.How to Create context file in R12?

Invoke adbldxml.pl from Database Tier only. A new context file on the database tier can be created using the following command :
perl $ORACLE_HOME/appsutil/bin/adbldxml.pl [template=<contextfile_template>][out=<contextfile_name>

In order to generate the Applications context file on Applications tier, run the adclonectx.pl script.
perl [COMMON_TOP]/clone/bin/adclonectx

7.How to retrieve context file if we lost/corrupted?

To retrieve the applications tier context file ( if it is lost or deleted accidentally) , execute the following command on the applications tier :
perl <COMMON_TOP>/clone/bin/adclonectx.pl retrieve

8. What is driver file and its role?
The driver files list the names and locations of the template files that need to have context variables replaced. They also define the phases into which instantiation is divided, and specify the commands that are to be executed for specific products. When AutoConfig runs, it cycles through the various <PROD>_TOP/admin/driver directories looking for driver files such as adtmpl.drv, fndtmpl.drv, and icxtmpl.drv.

Phases of Operation

As AutoConfig parses the driver files, it carries out a series of actions, grouped into several distinct phases:

INSTE8 - Instantiates AutoConfig template files to the AutoConfig configuration files specified in the relevant template driver files.

INSTE8_SETUP - Executes setup scripts that carry out activities not involving connection to the database.

INSTE8_PRF - Executes setup scripts that update profile options.

INSTE8_APPLY - Executes setup scripts that carry out activities involving updates to the database.

BINCPY - Copies the file mentioned from the source file to the configuration file, creating parent directories for the latter if necessary. AutoConfig will report an error if the source file cannot be found.

BINCPY_IGERR - Copies the file mentioned from the source file to the configuration file, creating parent directories for the latter if necessary. AutoConfig will not report an error if the source file cannot be found.

AutoConfig carries out these actions in the following order:

All INSTE8 and BINCPY actions - Carries out all file instantiations called for during INSTE8, INSTE8_SETUP, INSTE8_PRF and INSTE8_APPLY, and all copying from source files to target configuration files.

INSTE8_SETUP actions - For the files that were instantiated in Step 1, AutoConfig runs all SETUP scripts.

INSTE8_PRF actions - For the files that were instantiated in Step 1, AutoConfig runs all PRF scripts.

INSTE8_APPLY actions - For the files that were instantiated in Step 1, AutoConfig runs all APPLY scripts.

At the end of this process, the required configuration files and profile options have been created for the E-Business Suite installation.

FNDCPASS

What is FNDCPASS?

To change the password in Oracle Applications 11i & R12, The FND binary which will help us is doing these things is FNDCPASS. This is present in $FND_TOP/bin directory.

Below is the usage for FNDCPASS.You can just type FNDCPASS and press enter , it will give you these details.

$ FNDCPASS

Usage: FNDCPASS logon 0 Y system/password mode username new_password

where logon is username/password[@connect]

system/password is password of the system account of that database

mode is SYSTEM/USER/ORACLE

username is the username where you want to change its password

new password is the new password in unencrypted format.

Few Imp Points to Note:

a.The tables that FNDCPASS uses are FND_USER and FND_ORACLE_USERID.
All the application passwords and schema passwords are stored in these two tables along with DBA_USERS.so always backup the above 3 tables before using FNDCPASS.

b. Ensure that the entire Oracle Applications system has been shut down before changing any schema passwords.

c.Never update apps, applsys and other product schema password directly from database using the alter command.Database core schema's like sys,system passwords can be changed using ALTER command in database.Applsyspub is a public database schema used in Oracle Applications , This account password can be changed using FNDCPASS.

d.In Oracle Apps 11i (Not needed in R12( when changing the password for APPS it is important to manually change the APPS password in the following:

$IAS_ORACLE_HOME\Apache\modplsql\cfg\wdbsvr.app file
$FND_TOP/resource/wfmail.cfg
$AD_TOP/admin/template/CGIcmd.dat

e.FND_USER and FND_ORACLE_USERID. The FND_USER table stores application user account passwords and the FND_ORACLE_USERID table stores internal Oracle Applications database account passwords.

example:

FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS WELCOME

FNDCPASS apps/apps 0 Y system/manager ORACLE GL GL1

FNDCPASS apps/apps 0 Y system/manager USER VISION WELCOME

0 & Y are flags for FND Executable like FNDCPASS & FNDLOAD where

0 is request id (request ID 0 is assigned to request ID's which are not submitted via Submit Concurrent Request Form.

'Y' indicates the method of invocation. i.e. it is directly invoked from the command-line not from the Submit Request Form.

1)FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS WELCOME

The above syntax is used for changing the password for apps and applsys.Apps and applsys are database schemas of Oracle Applications.Alter user command should never be used to change the apps & applsys accounts.Oracle recommends using FNDCPASS only to change apps and applsys password.Please note that above command will change the password for both apps and applsys.

When we try to change the password, internally the following will take place.

a.Validations of current applsys & system password
b.Re-encrypt all password in FND_USER & FND_ORACLE_USERID
c.Update apps & applsys password in FND_ORACLE_USERID table.
d.Update DBA_USERS table as well.

2)FNDCPASS apps/apps 0 Y system/manager ORACLE GL GL1

The above syntax is used for changing password for any other product schema like AP,AR,GL etc.

When we try to change the password, internally the following will take place.

a.Validations of current applsys & system password
b.update GL new password in FND_ORACLE_USERID table
c.Update DBA_USERS table as well.

3)FNDCPASS apps/apps 0 Y system/manager USER VISION WELCOME

The above syntax is used for changing the application level passwords like sysadmin etc. front end applications user password.

When we try to change the password, internally the following will take place.

a.Validations of current applsys & system password
b.Update VISION NEW password in FND_USER table.

Tuesday, September 20, 2016

Openssl Commands

Openssl Command to test protocol support:

openssl s_client -connect jayuat.domain.com:443 -tls1_2
openssl s_client -connect jayuat.domain.com:443 -tls1_1
openssl s_client -connect jayuat.domain.com:443 -tls1
openssl s_client -connect jayuat.domain.com:443 -ssl2
openssl s_client -connect jayuat.domain.com:443 -ssl3

Monday, September 19, 2016

11g Fusion Middleware: Forms And Reports Architecture

FORMS:

$MW_HOME=/as10g/jay_uat/product/middleware/11.1 (Top level directory used by fusion middleware)

configuration and log files of opmn managed process will be under $ORACLE_INSTANCE i.e /as10g/jay_uat/product/middleware/11.1/fr_inst_uat2

Fusion Middleware forms architecture Flow:

Verification url for typical installation would be http://hostname:port/forms/frmservlet/

Initially Request will go to http server . Apache conf files will be under $ORACLE_INSTANCE/config/OHS/ohs1/httpd.conf and location match for forms
will be defined in $ORACLE_INSTANCE/config/OHS/ohs1/moduleconf/forms.conf . This forms.conf will have the hostname and port number of WLS_FORMS managed server.

<Location /forms>
SetHandler weblogic-handler
WebLogicCluster jaydev16.domain.com:9005 (port number which has been defined in $DOMAIN_HOME/config/config.xml)
DynamicServerList OFF
</Location>

Based on this file it will attach the request from http://hostname:port/forms/frmservlet/ to the correct managed server.

Custom Application Forms Architecture:

This is the url http://jaydev16.domain.com:8060/forms/frmservlet?config=snflwr_05000000_proxy_JAYUAT

so for this ,basic forms url should work.so as a first troubleshooting stuff,we have to make sure http://jaydev16.domain.com:8060/forms/frmservlet is working.

weblogic WLS_FORMS managed server config files will be under $DOMAIN_HOME/config/fmwconfig/servers/WLS_FORMS/applications/formsapp_11.1.1/config/

for WLS_FORMS ,main config files will be formsweb.cfg. It will have location match section like this. which has java file location and database connectivity etc

[snflwr_05000000_proxy_JAYUAT]
baseHTML=/as10g/jay_uat/snflwr/5.0.0.0/appserver/web_files/forms/base.htm
baseHTMLjinitiator=/as10g/jay_uat/snflwr/5.0.0.0/appserver/web_files/forms/basejpi.htm
baseHTMLjpi=/as10g/jay_uat/snflwr/5.0.0.0/appserver/web_files/forms/basejpi.htm
baseHTMLie=/as10g/jay_uat/snflwr/5.0.0.0/appserver/web_files/forms/baseie.htm
userid=SAPUB/xxxxxxxxxxxxxx@JAYUAT
envfile=/as10g/jay_uat/snflwr/5.0.0.0/appserver/conf/formservlet.ini

envfile is one of the important parameter. The forms rum time process (client) will use this file to set the environment (like $TNS_ADMIN,$ORACLE_INSTANCE).
based on this only it will resolve database name.Here anyfile which connnects through
http://jaydev16.domain.com:8060/forms/frmservlet?config=snflwr_05000000_proxy_JAYUAT will use /as10g/jay_uat/snflwr/5.0.0.0/appserver/conf/formservlet.ini file to set the environment.

so if there is any problem in database connectivity from forms,make sure formsweb.cfg is pointing to correct env file and check that env file is having proper values or not

if we don't specify any env file,there is another one file called default.env will be used.

Reports :

Similar to forms,For reports http://hostname:port/reports/rwservlet/ should work as basic test.
Similar to forms ,location match reports will be defined under $ORACLE_INSTANCE/config/OHS/ohs1/moduleconf/reports_ohs.conf. so based on this file,it will
attach the request to propert managed server WLS_REPORTS.

<Location /reports>
SetHandler weblogic-handler
WebLogicHost jaydev16.domain.com
WebLogicPort 9009 (WLS_REPORTS port number which has been defined in $DOMAIN_HOME/config/config.xml)
</Location>

and also one more thing there is one file called rwnetwork.conf .port number defined in
that file should be same in all the places.Below are locations .

$ORACLE_INSTANCE/config/ReportsToolsComponent/ReportsTools/rwnetwork.conf
$ORACLE_INSTANCE/config/ReportsServerComponent/rep_snflwr_05000000_JAYUAT/rwnetwork.conf
$DOMAIN_HOME/config/fmwconfig/servers/WLS_REPORTS/applications/reports_11.1.1.2.0/configuration/rwnetwork.conf

Reports URL:(THis is just sample url to explain important parameters)
https://JAYUAT.domain.com:8061/reports/rwservlet?cmdkey=snflwr_report_JAYUAT&server=rep_snflwr_05000000_JAYUAT&delimited_hdr=no&delimiter=none&destype=cache&mimetype=application/pdf&mode=default&report=saad6010

cmdkey --> It will be having database credentials.it will be defined in $DOMAIN_HOME/config/fmwconfig/servers/WLS_REPORTS/applications/reports_11.1.1.2.0/configuration/cgicmd.dat

;;;;;;;;;;;;;;;;;;;;;;;;;;
snflwr_report_JAYUAT: userid=SARPT/xxxxxxxxxxxxxx@JAYUAT %*
;;;;;;;;;;;;;;;;;;;;;;;;;;

server --> Report server name.we have created on report server(rep_snflwr_05000000_JAYUAT)
under opmnctl.

Config files:$ORACLE_INSTANCE/config/ReportsServerComponent/rep_snflwr_05000000_JAYUAT/rwserver.conf

This file will have the details of where it has to pick the reports.

<engine id="rwEng" class="oracle.reports.engine.EngineImpl" initEngine="1" maxEngine="1" minEngine="0" engLife="1" maxIdle="30" callbackTimeOut="60000" classPath="/
as10g/jay_uat/snflwr/5.0.0.0/appserver/reports/IDADataMatrix.jar:/as10g/jay_uat/snflwr/5.0.0.0/appserver/reports/LinearBarCode.jar">
<property name="sourceDir" value="/as10g/jay_uat/snflwr/5.0.0.0/appserver/reports:/as10g/jay_uat/snflwr/5.0.0.0/appserver/ext/reports"/>

Tuesday, November 1, 2016

Oracle Apps DBA - Useful Queries

Monday, October 17, 2016

Autoconfig

Autoconfig:

How to customize the template for autoconfig files:

Steps to customize the Autoconfig unlock files

Some important points to remember:

Important Questions:

FNDCPASS

Few Imp Points to Note:

Tuesday, September 20, 2016

Openssl Commands

Openssl Command to test protocol support:

Monday, September 19, 2016

11g Fusion Middleware: Forms And Reports Architecture

FORMS:

Reports :

How to Compile Forms , Reports & Custom.pll in R12.2