Tools for the Oracle Applications DBA Toolbox

1. Tools for the Oracle Applications DBA Toolbox Jeff Slavitz Oracle Applications DBA Computer Creations Inc Jeff@OracleAppsPro.Com

2. Three of my Favorite Tools • FNDLOAD to migrate application setup data • SED to edit context files • Workflow check and cleanup scripts Tools for the Oracle Applications DBA Toolbox

3. Moving Setup Data between Instances • How do you migrate setup data between instances? • Concurrent program definitions • Value sets • Traditional method is manual data entry • Slow • Prone to error Tools for the Oracle Applications DBA Toolbox

4. Moving Setup Data between Instances • A tool exists to automate setup data • It’s free! • It’s supported by Oracle • It’s written by Oracle • It’s used by Oracle Tools for the Oracle Applications DBA Toolbox

5. FNDLOAD • Not well publicized but very useful! • Similar to database export/import but for certain application objects • Downloads application object into portable text file • Text file can be edited to create new objects • Text file can be uploaded into any instance • Works between version of Applications! Tools for the Oracle Applications DBA Toolbox

6. FNDLOADable Objects • Printer Styles • Lookups • Descriptive Flexfields with all of specific Contexts • Key Flexfield Structures • Concurrent Programs • Value Sets • Value Sets with values • Profile Options • Request Groups • Request Sets • Responsibilities • Menus Tools for the Oracle Applications DBA Toolbox

7. Using FNDLOAD • Call from UNIX command line FNDLOAD apps/pwd 0 Y mode configfile datafile entity [ param ...] < 0 Y > - Concurrent program flags Mode - UPLOAD or DOWNLOAD. UPLOAD causes the datafile to be uploaded to thedatabase. DOWNLOAD causes the loader to fetch rows and write them to the datafile. Tools for the Oracle Applications DBA Toolbox

8. Using FNDLOAD FNDLOAD apps/pwd 0 Y mode configfile datafile entity [ param ...] congle - The configuration file to use (usually with a suffix of .lct) datale - The data file to write (usually with a suffix of .ldt, but not enforced or supplied by the loader). If the data file already exists, it will be overwritten. entity - The entity to upload or download. [param] - Zero or more additional parameters. Each parameter is in the form NAME=VALUE. Metalink note 274667.1 lists command line to download all object types Tools for the Oracle Applications DBA Toolbox

9. Tools for the Oracle Applications DBA Toolbox

10. Tools for the Oracle Applications DBA Toolbox

11. FNDLOAD download example FNDLOAD apps/xxx 0 Y \ DOWNLOAD \ (mode) $FND_TOP/patch/115/import/afcpprog.lct \ (configfile) FNDLOAD_TST.ldt \ (datafile) PROGRAM \ (entity) APPLICATION_SHORT_NAME="XXFI" \ (parameters) CONCURRENT_PROGRAM_NAME="FNDLOAD_TST“ • Different input .lct file and parameters depending on what you want to download Tools for the Oracle Applications DBA Toolbox

12. FNDLOAD Output Downloading PROGRAM to the data file FNDLOAD_TST.ldt Downloaded EXECUTABLE FNDLOAD_TST XXFI Downloaded VALUE_SET 10 Characters Downloaded DESC_FLEX XXFI $SRS$.FNDLOAD_TST Downloaded PROGRAM FNDLOAD_TST XXFI Tools for the Oracle Applications DBA Toolbox

13. .ldt File • Section 1 – Entity Definitions # -- Begin Entity Definitions -- DEFINE PROGRAM KEY CONCURRENT_PROGRAM_NAME VARCHAR2(30) KEY APPLICATION_SHORT_NAME VARCHAR2(50) CTX OWNER VARCHAR2(4000) BASE LAST_UPDATE_DATE VARCHAR2(75) TRANS USER_CONCURRENT_PROGRAM_NAME VARCHAR2(240) BASE EXEC REFERENCES EXECUTABLE BASE EXECUTION_METHOD_CODE VARCHAR2(1) BASE ARGUMENT_METHOD_CODE VARCHAR2(1) BASE QUEUE_CONTROL_FLAG VARCHAR2(1) … Tools for the Oracle Applications DBA Toolbox

14. Section 2 - Data Definitions BEGIN EXECUTABLE "FNDLOAD_TST" "XXFI" OWNER = "JEFFSLAVITZ" LAST_UPDATE_DATE = "2006/12/04" EXECUTION_METHOD_CODE = "Q" EXECUTION_FILE_NAME = "fndload_tst" USER_EXECUTABLE_NAME = "FNDLOAD TEST" DESCRIPTION = "Test concurrent program to demonstrate use of FNDLOAD" END EXECUTABLE … • Modify .ldt file as desired to create new entity Tools for the Oracle Applications DBA Toolbox

15. FNDLOAD upload example FNDLOAD apps/xxx 0 Y \ UPLOAD \ (mode) $FND_TOP/patch/115/import/afcpprog.lct \ (configfile) FNDLOAD_TST.ldt \ (datafile) PROGRAM \ (entity) APPLICATION_SHORT_NAME="XXFI" \ (parameters) CONCURRENT_PROGRAM_NAME="FNDLOAD_TST“ • Same format as Download command except specify UPLOAD. Tools for the Oracle Applications DBA Toolbox

16. FNDLOAD Summary • Migrate objects between instances from Test to Prod or from one version of Apps to another • Create new objects based on existing objects • Automate further by writing a wrapper concurrent program • For more information see: • System Administrator’s Guide – Configuration, Appendix B • Metalink note 274667.1 Tools for the Oracle Applications DBA Toolbox

17. FNDLOAD Questions??? Tools for the Oracle Applications DBA Toolbox

18. Reasons to Edit Context Files • After cloning usually need to modify Apps and RDBMS context files • Oracle’s cloning scripts make mostbut not all necessary changes to context file variables (javamailer_reply_to, ecx_log_dir ..) • May want to set some context file variables set to custom value (e.g. ifile locations) • Test instance might need to be smaller than Production (dbcache_size, db_processes) Tools for the Oracle Applications DBA Toolbox

19. Customizing Context Files • How do you customize RDBMS and Applications context files? • Manual edits are time consuming and error prone • Ideal solution is UNIX script • SED is one answer! Tools for the Oracle Applications DBA Toolbox

20. How sed works • Reads line of input • Searches for a pattern using regular expression • Replace text with vi-like syntax Tools for the Oracle Applications DBA Toolbox

21. Sed Example • From the RDBMS context file after cloning <dbprocesses oa_var="s_db_processes">1300</dbprocesses> • What you want in your non-Prod instance <dbprocesses oa_var="s_db_processes">200</dbprocesses> • How sed works • Search for the line containing s_db_processes • Replace the value between > .. < with a new value • Write the new line to an output file Tools for the Oracle Applications DBA Toolbox

22. BEFORE: <dbprocesses oa_var="s_db_processes">1300</dbprocesses> AFTER: <dbprocesses oa_var="s_db_processes">200</dbprocesses> s/\(\"s_db_processes\">\).*\(<\)/\1200\2/ HUH???? Scary at first but it’s just a vi command: s/ search string / replacement string / Tools for the Oracle Applications DBA Toolbox

23. BEFORE: <dbprocesses oa_var="s_db_processes">1300</dbprocesses> s/\(\"s_db_processes\">\).*\(<\)/\1200\2/ Search string: \( \"s_db_processes\"> \) pattern #1 Tools for the Oracle Applications DBA Toolbox

24. BEFORE: <dbprocesses oa_var="s_db_processes">1300</dbprocesses> s/\(\"s_db_processes\">\).*\(<\)/\1200\2/ Search string: pattern #1 PLUS .* one or more characters Tools for the Oracle Applications DBA Toolbox

25. BEFORE: <dbprocesses oa_var="s_db_processes">1300</dbprocesses> s/\(\"s_db_processes\">\).*\(<\)/\1200\2/ Search string: substring #1 PLUS one or more characters PLUS \( < \) pattern #2 Tools for the Oracle Applications DBA Toolbox

26. BEFORE: <dbprocesses oa_var="s_db_processes">1300</dbprocesses> s/\(\"s_db_processes\">\).*\(<\)/\1200\2/ Replacement string: \1 200 \2 pattern #1 + 200 + pattern #2 AFTER: <dbprocesses oa_var="s_db_processes">200</dbprocesses> Tools for the Oracle Applications DBA Toolbox

27. Use with environment variables too BEFORE: <temp_dir oa_var="s_temp">/apps51/test/common/temp</temp_dir> SED command: s:\(\"s_temp\">\).*\(<\):\1/apps02/tmp/${DBNAME}\2: AFTER: <temp_dir oa_var="s_temp">/apps02/tmp/test</temp_dir> Tools for the Oracle Applications DBA Toolbox

28. Sed Scripts • Refer to Supplemental file on NorCalOAUG website • Edit RDBMS context file • rdbms_editctx.sh • Edit applications context file • apps_editctx.sh Tools for the Oracle Applications DBA Toolbox

29. Customizing Context Files with SED Questions? Tools for the Oracle Applications DBA Toolbox

30. Workflow Housekeeping • Completed workflow data remains in database • May want to retain for some period for workflow problem research • Need to purge old workflow data with concurrent program “Purge Obsolete Workflow Runtime Data” Tools for the Oracle Applications DBA Toolbox

31. Purgeable Worfkflows • Parent workflow is complete AND all children workflows of parent workflow are complete • Running “Purge Obsolete Workflow Runtime Data” purges all related workflow and notification data for parent and child Tools for the Oracle Applications DBA Toolbox

32. Unpurgeable Workflow Data • Active workflows waiting on a notification response, no matter how old • Completed workflows which have an active child workflows • Some workflows that end in error • Unpurgeable workflow data remains in your database forever! Tools for the Oracle Applications DBA Toolbox

33. Periodically review two categories of active workflows: • Workflows started in the last 3-6 months • Is the workflow legitimately still active? • Is there an error in the workflow? • What is causing the workflow not to complete? • Workflows started six months or longer ago • These will probably never complete • Research needed • What is causing workflow to not complete? Tools for the Oracle Applications DBA Toolbox

34. Researching Old Active Workflows • Start by looking in WF_ITEMS • Begin_Date = date workflow started • End_Date • Not Null = date workflow ended • Null = workflow is still active • Look at item types that have lots of old active workflows to get low hanging fruit Tools for the Oracle Applications DBA Toolbox

35. High Count Old Active Workflows SELECT item_type, MIN(begin_date), MAX(begin_date), COUNT(*) FROM wf_items WHERE end_date IS NULL AND begin_date < SYSDATE - 180 GROUP by item_type HAVING count(*) > 1000; Tools for the Oracle Applications DBA Toolbox

36. ITEM_TYP MIN_BEGIN MAX_BEGIN COUNT -------- --------- --------- -------- HRSSA 29-NOV-05 09-JUN-06 1,260 HXCEMP 04-OCT-04 05-JUN-06 5,335 JTFTASK 23-MAR-04 08-JUN-06 1,601 OEOH 02-OCT-01 02-JUN-06 2,220 OEOL 02-OCT-01 02-JUN-06 4,887 PACRMUPD 15-DEC-05 09-JUN-06 1,505 WFERROR 05-NOV-01 09-JUN-06 7,806 Tools for the Oracle Applications DBA Toolbox

37. Researching Old Active Workflows • Once you know the offending item types, find out what is wrong • Workflow Status Monitor is a good tool • Use a SQL script – my example, WF_SNAPSHOT.sql (in Supplemental file on Nor Cal OAUG website) Tools for the Oracle Applications DBA Toolbox

38. Workflow Status Monitor Tools for the Oracle Applications DBA Toolbox

39. Activity History Tools for the Oracle Applications DBA Toolbox

40. Status Diagram Tools for the Oracle Applications DBA Toolbox

41. Status Monitor Detail ScreenWorkflow Details Tools for the Oracle Applications DBA Toolbox

42. WF_SNAPSHOT.sql OLD ACTIVE workflows (WF started > 6 mos ago) These are waiting for an activity to complete. ITEM_TYP MIN_BEGIN MAX_BEGIN COUNT -------- --------- --------- -------- APEXP 04-OCT-04 09-JUN-06 371 APWRECPT 14-DEC-05 16-JAN-06 3 HRSSA 29-NOV-05 09-JUN-06 1,260 HXCEMP 04-OCT-04 05-JUN-06 5,335 JTFTASK 23-MAR-04 08-JUN-06 1,601 OEOH 02-OCT-01 02-JUN-06 2,220 OEOL 02-OCT-01 02-JUN-06 4,887 PACRMUPD 15-DEC-05 09-JUN-06 1,505 SERVEREQ 10-OCT-05 06-JAN-06 408 WFERROR 05-NOV-01 09-JUN-06 7,806 Code in Supplemental File on NorCalOAUG website Tools for the Oracle Applications DBA Toolbox

43. Aborting Workflows • Use Workflow Manager for aborting individual workflows or all workflows of a particular item type • Or write your own SQL script and use WF_ENGINE.AbortProcess API • Afterwards run concurrent program Purge Obsolete Workflow Runtime Data to remove workflow data from database Tools for the Oracle Applications DBA Toolbox

44. Workflow Manager Tools for the Oracle Applications DBA Toolbox

45. Tools for the Oracle Applications DBA Toolbox

46. Tools for the Oracle Applications DBA Toolbox

47. AbortProcess API procedure AbortProcess (itemtype in varchar2, itemkey in varchar2, process in varchar2 default ’’, result in varchar2 default eng_force); • Aborts process execution and cancels outstanding notifications. • Any outstanding notifications or sub-processes are set to a status of COMPLETE. Tools for the Oracle Applications DBA Toolbox

48. AbortProcess API • Use Sql Plus to abort one workflow: exec WF_ENGINE.ABORTPROCESS( ‘WFERROR’, -- Item type ‘WF1620’); -- Item key • Write a PL/SQL program to abort a range of workflows. See ABORT_WF.sql in Supplemental file for an example of how to do this. Tools for the Oracle Applications DBA Toolbox

49. Workflow Housekeeping Summary • Schedule ‘Purge Obsolete Workflow Runtime Data’ to run on a regular basis • Periodically check for extremely old active workflows • Use SQL and Workflow Status Monitor to research why these workflows are not completing • Use SQL or Workflow Manager to abort these workflows Tools for the Oracle Applications DBA Toolbox

50. Questions? Jeff Slavitz (415) 388 – 3003 Jeff@OracleAppsPro.Com Tools for the Oracle Applications DBA Toolbox