1 / 50

Tools for the Oracle Applications DBA Toolbox

Tools for the Oracle Applications DBA Toolbox. Jeff Slavitz Oracle Applications DBA Computer Creations Inc Jeff@OracleAppsPro.Com. Three of my Favorite Tools. FNDLOAD to migrate application setup data SED to edit context files Workflow check and cleanup scripts.

wing
Download Presentation

Tools for the Oracle Applications DBA Toolbox

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related