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');
Tuesday, February 6, 2018
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;
Thursday, January 11, 2018
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 |
Wednesday, January 3, 2018
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
Thursday, December 28, 2017
Automatic Memory Management (AMM) in Oracle Database 11g R2
Identify Memory Requirement.
-- 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.
Monday, December 18, 2017
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
Monday, December 4, 2017
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
Subscribe to:
Posts (Atom)
-
Data Synchronization Data Synchronization is a process in which some setup data would be synchronized, and this would be more important w...
-
Error: Checking for existing adop sessions. Continuing with existing session [Session ID: 13]. [UNEXPECTED]Master node for the curre...
-
This test will verify Oracle Workflow Java Mailer connection with IMAP Server. $AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=imap -Ddbcfile=...