Check Profile Options Level: Site/Responsibility/User

select
b.user_profile_option_name "Long Name"
, a.profile_option_name "Short Name"
, decode(to_char(c.level_id),'10001','Site'
,'10002','Application'
,'10003','Responsibility'
,'10004','User'
,'Unknown') "Level"
, decode(to_char(c.level_id),'10001','Site'
,'10002',nvl(h.application_short_name,to_char(c.level_value))
,'10003',nvl(g.responsibility_name,to_char(c.level_value))
,'10004',nvl(e.user_name,to_char(c.level_value))
,'Unknown') "Level Value"
, c.PROFILE_OPTION_VALUE "Profile Value"
, c.profile_option_id "Profile ID"
, to_char(c.LAST_UPDATE_DATE,'DD-MON-YYYY HH24:MI') "Updated Date"
, nvl(d.user_name,to_char(c.last_updated_by)) "Updated By"
from
apps.fnd_profile_options a
, apps.FND_PROFILE_OPTIONS_VL b
, apps.FND_PROFILE_OPTION_VALUES c
, apps.FND_USER d
, apps.FND_USER e
, apps.FND_RESPONSIBILITY_VL g
, apps.FND_APPLICATION h
where
--a.application_id = nvl(401, a.application_id)
--and a.profile_option_name = nvl('INV', a.profile_option_name)
b.user_profile_option_name like '%Debug%' -- 'AFLOG_ENABLED'
and a.profile_option_name = b.profile_option_name
and a.profile_option_id = c.profile_option_id
and a.application_id = c.application_id
and c.last_updated_by = d.user_id (+)
and c.level_value = e.user_id (+)
and c.level_value = g.responsibility_id (+)
and c.level_value = h.application_id (+)
--and c.level_id=10003
and e.end_date is null
order by
b.user_profile_option_name, c.level_id,
decode(to_char(c.level_id),'10001','Site'
,'10002',nvl(h.application_short_name,to_char(c.level_value))
,'10003',nvl(g.responsibility_name,to_char(c.level_value))
,'10004',nvl(e.user_name,to_char(c.level_value))
,'Unknown');

Identify Long running Concurrent programs

-- Identify Long running Concurrent programs with child details and run time Parameters

select /*+ ORDERED USE_NL(x fcr fcp fcptl)*/
       fcr.request_id    "Request ID",
       fcr.requested_by "User",
       substr(DECODE (FCR.DESCRIPTION,  NULL,
       FCPTL.USER_CONCURRENT_PROGRAM_NAME,
       FCR.DESCRIPTION||' ('||FCPTL.USER_CONCURRENT_PROGRAM_NAME||')'),1,80)"Program Name",
       (fcr.actual_completion_date - fcr.actual_start_date)*1440 "Elapsed Time",
       oracle_process_id "Trace File ID" ,
       fcr.phase_code "Phase",
       fcr.status_code "Status",
       to_char(fcr.request_date,'DD-MON-YYYY HH24:MI:SS')   "Submitted",
       (fcr.actual_start_date - fcr.request_date)*1440 "Delay",
       to_char(fcr.actual_start_date,'DD-MON-YYYY HH24:MI:SS')  "Start Time",
       to_char(fcr.actual_completion_date, 'DD-MON-YYYY HH24:MI:SS') "End Time",
       fcr.argument_text "Parameters"
  from (select /*+ index (fcr1 FND_CONCURRENT_REQUESTS_N3) */
               fcr1.request_id
          from fnd_concurrent_requests fcr1
         where 1=1
         start with fcr1.request_id = :RequestID
       connect by prior fcr1.request_id = fcr1.parent_request_id) x,
       fnd_concurrent_requests fcr,
       fnd_concurrent_programs fcp,
       fnd_concurrent_programs_tl fcptl
 where fcr.request_id = x.request_id
   and fcr.concurrent_program_id = fcp.concurrent_program_id
   and fcr.program_application_id = fcp.application_id
   and fcp.application_id = fcptl.application_id
   and fcp.concurrent_program_id = fcptl.concurrent_program_id
   and fcptl.language = 'US'
 order by 1


 -- To find current running SQL text for a request with performance/hanging issues - refer to Doc ID 186472.1
-- prints all requests currently running for a request set or can be used for any single running request
-- note that if no SQL is returned, then the process may be working in RAM Memory - examples are MRP or ASCP Memory Based Planner process
-- REQUIRED VALUE - Enter the Request ID that launched the Request set being investigated

select
request_id,
to_char(sid) sid
, to_char(serial#) serial#
, vq.sql_id SQLID
, vs.osuser
, vs.machine
, sql_text
, vs.process
from
apps.fnd_concurrent_requests fcr,
v$session vs,
v$sqltext vq
where
vs.process = fcr.os_process_id
and vs.sql_address = vq.address
and fcr.status_code = 'R'
and fcr.phase_code = 'R'
   and request_id in
  (select fcr.request_id
  from (select /*+ index (fcr1 FND_CONCURRENT_REQUESTS_N3) */
          fcr1.request_id
       from fnd_concurrent_requests fcr1
         where 1=1
         start with fcr1.request_id = &request_id
          connect by prior fcr1.request_id = fcr1.parent_request_id) x,
          fnd_concurrent_requests fcr,
          fnd_concurrent_programs fcp,
          fnd_concurrent_programs_tl fcptl
          where fcr.request_id = x.request_id
          and fcr.concurrent_program_id = fcp.concurrent_program_id
          and fcr.program_application_id = fcp.application_id
          and fcp.application_id = fcptl.application_id
          and fcp.concurrent_program_id = fcptl.concurrent_program_id
          and fcptl.language = 'US')
order by request_id, serial#, piece;

EBS R12.2. Start and Stop Procedure (Individual Components) on Application Tier

Stop/Start Individual services EBS 12.2

Scripts Location:$ADMIN_SCRIPTS_HOME

Stop

Component	Command
Fullfillment Serer Services	$jtffmctl.sh stop
Concurrent Manager Service	$adcmctl.sh stop apps/apps
Managed Server for Forms web  Services	$admanagedsrvctl.sh stop forms-c4ws_server1 Enter Weblogic Admin Password:
Managed Server for Fusion MiddleWare  Services	$admanagedsrvctl.sh stop oafm_server1 Enter Weblogic Admin Password:
Managed Server for FormsServices	$admanagedsrvctl.sh stop forms_server1 Enter Weblogic Admin Password:
Managed Server for OACORE Services	$admanagedsrvctl.sh stop oacore_server1 Enter Weblogic Admin Password:
Apache Services	$adapchctl.sh stop
Oracle Process Manager	$adopmnctl.sh stop
Application Listener	$adadlctl stop
Weblogic Admin Server	$adadminsrvctl.sh stop Enter Weblogic Admin Password:
Node Manager	$adnodemgrctl.sh stop Enter Weblogic Admin Password:

Start

Component	Command
Node Manager	$adnodemgrctl.sh start Enter Weblogic Admin Password:
Weblogic Admin Server	$adadminsrvctl.sh start Enter Weblogic Admin Password:
Application Listener	$adadlctl start
Oracle Process Manager	$adopmnctl.sh start
Apache Services	$adapchctl.sh start
Managed Server for OACORE Services	$admanagedsrvctl.sh start oacore_server1 Enter Weblogic Admin Password:
Managed Server for FormsServices	$admanagedsrvctl.sh start forms_server1 Enter Weblogic Admin Password:
Managed Server for Fusion MiddleWare  Services	$admanagedsrvctl.sh start oafm_server1 Enter Weblogic Admin Password:
Managed Server for Forms web  Services	$admanagedsrvctl.sh start forms-c4ws_server1 Enter Weblogic Admin Password:
Concurrent Manager Service	$adcmctl.sh start apps/apps
Fullfillment Serer Services	$jtffmctl.sh start

Changing weblogic password in EBS R12.2

Following steps used to change Oracle WebLogic User Password in EBS R12.2.
Make sure EBSapps.env executed for run file system

1. Shut down all application tier services.
$ $ADMIN_SCRIPTS_HOME/adstpall.sh

2. Start Admin Server.
$ $ADMIN_SCRIPTS_HOME/adadminsrvctl.sh start

3. Please check whether the "boot.properties" file exists under $EBS_DOMAIN_HOME/servers/<server name>/data/nodemanager directory.  If yes, take a backup of the file, manually remove it.

4. Change Oracle WebLogic User password by executing below command.

perl $FND_TOP/patch/115/bin/txkUpdateEBSDomain.pl -action=updateAdminPassword

Make sure the above command completes successfully.

5. Try logging in to WLS Admin console with the new password to confirm the password change.

6. Start all application tier services:
$ $ADMIN_SCRIPTS_HOME/adstrtal.sh

Doc ID 2178617.1

Automatic Memory Management (AMM) in Oracle Database 11g R2

Identify Memory Requirement.

Following queries show you how to display the relevant memory information and how to combine with a single statement to calculate the required values.

-- Individual values.
COLUMN name FORMAT A30
COLUMN value FORMAT A10

SELECT name, value
FROM   v$parameter
WHERE  name IN ('pga_aggregate_target', 'sga_target')
UNION
SELECT 'maximum PGA allocated' AS name, TO_CHAR(value) AS value
FROM   v$pgastat
WHERE  name = 'maximum PGA allocated';

-- Calculate MEMORY_TARGET
SELECT sga.value + GREATEST(pga.value, max_pga.value) AS memory_target
FROM (SELECT TO_NUMBER(value) AS value FROM v$parameter WHERE name = 'sga_target') sga,
     (SELECT TO_NUMBER(value) AS value FROM v$parameter WHERE name = 'pga_aggregate_target') pga,
     (SELECT value FROM v$pgastat WHERE name = 'maximum PGA allocated') max_pga;

Assuming our required setting was 10G, we might issue the following statements.

CONN / AS SYSDBA
-- Set the static parameter. Leave some room for possible future growth without restart.
ALTER SYSTEM SET MEMORY_MAX_TARGET=15G SCOPE=SPFILE;

-- Set the dynamic parameters. Assuming Oracle has full control.
ALTER SYSTEM SET MEMORY_TARGET=10G SCOPE=SPFILE;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=0 SCOPE=SPFILE;
ALTER SYSTEM SET SGA_TARGET=0 SCOPE=SPFILE;
ALTER SYSTEM SET SGA_MAX_SIZE=0 SCOPE=SPFILE;


-- Restart instance.
SHUTDOWN IMMEDIATE;
STARTUP;

Once the database is restarted the MEMORY_TARGET parameter can be amended as required without an instance restart.

Oracle FNDLOAD Scripts

FNDLOAD

Tips and Examples Using FNDLOAD (Doc ID 735338.1)

Download Script

Upload Script

1. Lookups

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_CUSTOM_LKP.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="XXCUST" LOOKUP_TYPE="XX_LOOKUP_TYPE"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_CUSTOM_LKP.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

2. Concurrent Program

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_CP.ldt PROGRAM APPLICATION_SHORT_NAME="XXCUST" CONCURRENT_PROGRAM_NAME="XX_CONCURRENT_PROGRAM"

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_CP.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

3. Profile

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct XX_CUSTOM_PRF.ldt PROFILE PROFILE_NAME="XX_PROFILE_NAME" APPLICATION_SHORT_NAME="XXCUST"

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct XX_CUSTOM_PRF.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

4. Request Set and Link

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XX_CUSTOM_RS.ldt REQ_SET REQUEST_SET_NAME='REQUEST_SET_NAME'

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct  XX_CUSTOM_RS.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

5. FND Message

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_CUSTOM_MESG.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME="XXCUST" MESSAGE_NAME="MESSAGE_NAME%"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_CUSTOM_MESG.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

6. D2K FORMS

$FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FRM.ldt FORM FORM_NAME="FORM_NAME"
      

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FRM.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

7. Form Function

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FUNC.ldt FUNCTION FUNCTION_NAME="FORM_FUNCTION_NAME"

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FUNC.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

8. Alerts

FNDLOAD apps/apps 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct XX_CUSTOM_ALR.ldt ALR_ALERTS APPLICATION_SHORT_NAME=XXCUST ALERT_NAME="XX - Alert Name"

FNDLOAD apps/apps 0 Y UPLOAD $ALR_TOP/patch/115/import/alr.lct XX_CUSTOM_ALR.ldt CUSTOM_MODE=FORCE

9. Value Set

$FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_CUSTOM_VS.ldt VALUE_SET FLEX_VALUE_SET_NAME="XX Value Set Name"

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct XX_CUSTOM_VS.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

10. Data Definition and Associated Template

FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD  $XDO_TOP/patch/115/import/xdotmpl.lct XX_CUSTOM_DD.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME='XXCUST' DATA_SOURCE_CODE='XX_SOURCE_CODE' TMPL_APP_SHORT_NAME='XXCUST' TEMPLATE_CODE='XX_SOURCE_CODE'

FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XX_CUSTOM_DD.ldt

11. DATA_TEMPLATE (Data Source .xml file)

java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD -DB_USERNAME apps -DB_PASSWORD apps -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX_HOST_NAME)(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=XX_SERVICE_NAME)))' -LOB_TYPE DATA_TEMPLATE -LOB_CODE XX_TEMPLATE -APPS_SHORT_NAME XXCUST -LANGUAGE en -lct_FILE $XDO_TOP/patch/115/import/xdotmpl.lct -LOG_FILE $LOG_FILE_NAME

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD apps -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX_HOST_NAME)(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=XX_SERVICE_NAME)))' -LOB_TYPE DATA_TEMPLATE -LOB_CODE XX_TEMPLATE -XDO_FILE_TYPE XML -FILE_NAME $DATA_FILE_PATH/$DATA_FILE_NAME.xml -APPS_SHORT_NAME XXCUST -NLS_LANG en -TERRITORY US -LOG_FILE $LOG_FILE_NAME

12. RTF TEMPLATE (Report Layout .rtf file)

java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD -DB_USERNAME apps -DB_PASSWORD apps -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX_HOST_NAME)(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=XX_SERVICE_NAME)))' -LOB_TYPE TEMPLATE -LOB_CODE XX_TEMPLATE -APPS_SHORT_NAME XXCUST -LANGUAGE en -TERRITORY US -lct_FILE $XDO_TOP/patch/115/import/xdotmpl.lct -LOG_FILE $LOG_FILE_NAME

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD apps -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX_HOST_NAME)(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=SERVICE_NAME)))' -LOB_TYPE TEMPLATE -LOB_CODE XX_TEMPLATE -XDO_FILE_TYPE RTF -FILE_NAME $RTF_FILE_PATH/$RTF_FILE_NAME.rtf -APPS_SHORT_NAME XXCUST -NLS_LANG en -TERRITORY US -LOG_FILE $LOG_FILE_NAME 

Running ADOP Fails With Error "ETCC Not Run In The Database Node"

Error:     ETCC not run in the database node [Node Name]
                The EBS Technology Codelevel Checker needs to be run on the database node.
                It is available as Patch 17537119.

Cause:
Mismatch in host name between FND_NODES and TXK_TCC_RESULTS tables.

Solution:
UPDATE applsys.txk_tcc_results SET node_name ='<node name>';


Run ADOP again and see if it fixes the issue.

Doc ID 2191678.1