Tools for the Oracle Applications DBA Toolbox Jeff

Tools for the Oracle Applications DBA Toolbox Jeff Slavitz Oracle Applications DBA Computer Creations Inc Jeff@Oracle. Apps. Pro. Com 2007 Training Day v 1. 2 Tools for the Oracle Applications DBA Toolbox

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

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

Moving Setup Data between Instances Ø A tool exists to automate setup data l l It’s free! It’s supported by Oracle It’s written by Oracle It’s used by Oracle 2007 Training Day v 1. 2 Tools for the Oracle Applications DBA Toolbox 4

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! 2007 Training Day v 1. 2 Tools for the Oracle Applications DBA Toolbox 5

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

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. 2007 Training Day v 1. 2 Tools for the Oracle Applications DBA Toolbox 7

Using 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 2007 Training Day v 1. 2 Tools for the Oracle Applications DBA Toolbox 8

2007 Training Day v 1. 2 Tools for the Oracle Applications DBA Toolbox 9

2007 Training Day v 1. 2 Tools for the Oracle Applications DBA Toolbox 10

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 2007 Training Day v 1. 2 Tools for the Oracle Applications DBA Toolbox 11

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 2007 Training Day v 1. 2 Tools for the Oracle Applications DBA Toolbox 12

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

Ø 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 2007 Training Day v 1. 2 Tools for the Oracle Applications DBA Toolbox 14

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. 2007 Training Day v 1. 2 Tools for the Oracle Applications DBA Toolbox 15

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: Ø l l System Administrator’s Guide – Configuration, Appendix B Metalink note 274667. 1 2007 Training Day v 1. 2 Tools for the Oracle Applications DBA Toolbox 16

FNDLOAD Questions? ? ? 2007 Training Day v 1. 2 Tools for the Oracle Applications DBA Toolbox 17

Reasons to Edit Context Files After cloning usually need to modify Apps and RDBMS context files Ø Oracle’s cloning scripts make most but 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) Ø 2007 Training Day v 1. 2 Tools for the Oracle Applications DBA Toolbox 18

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! 2007 Training Day v 1. 2 Tools for the Oracle Applications DBA Toolbox 19

How sed works Ø Reads line of input Ø Searches for a pattern using regular expression Ø Replace text with vi-like syntax 2007 Training Day v 1. 2 Tools for the Oracle Applications DBA Toolbox 20

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 l l l Search for the line containing s_db_processes Replace the value between >. . < with a new value Write the new line to an output file 2007 Training Day v 1. 2 Tools for the Oracle Applications DBA Toolbox 21

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

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

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

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

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

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

Sed Scripts Ø Refer to Supplemental file on Nor. Cal. OAUG website Ø Edit RDBMS context file l rdbms_editctx. sh Ø Edit applications context file l apps_editctx. sh 2007 Training Day v 1. 2 Tools for the Oracle Applications DBA Toolbox 28

Customizing Context Files with SED Questions? 2007 Training Day v 1. 2 Tools for the Oracle Applications DBA Toolbox 29

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” 2007 Training Day v 1. 2 Tools for the Oracle Applications DBA Toolbox 30

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 2007 Training Day v 1. 2 Tools for the Oracle Applications DBA Toolbox 31

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! 2007 Training Day v 1. 2 Tools for the Oracle Applications DBA Toolbox 32

Ø Periodically review two categories of active workflows: l 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? l Workflows started six months or longer ago • • • These will probably never complete Research needed What is causing workflow to not complete? 2007 Training Day v 1. 2 Tools for the Oracle Applications DBA Toolbox 33

Researching Old Active Workflows Ø Start by looking in WF_ITEMS Ø Begin_Date = date workflow started Ø End_Date l l 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 2007 Training Day v 1. 2 Tools for the Oracle Applications DBA Toolbox 34

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; 2007 Training Day v 1. 2 Tools for the Oracle Applications DBA Toolbox 35

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

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) 2007 Training Day v 1. 2 Tools for the Oracle Applications DBA Toolbox 37

Workflow Status Monitor 2007 Training Day v 1. 2 Tools for the Oracle Applications DBA Toolbox 38

Activity History 2007 Training Day v 1. 2 Tools for the Oracle Applications DBA Toolbox 39

Status Diagram 2007 Training Day v 1. 2 Tools for the Oracle Applications DBA Toolbox 40

Status Monitor Detail Screen Workflow Details 2007 Training Day v 1. 2 Tools for the Oracle Applications DBA Toolbox 41

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

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. Abort. Process API Ø Afterwards run concurrent program Purge Obsolete Workflow Runtime Data to remove workflow data from database 2007 Training Day v 1. 2 Tools for the Oracle Applications DBA Toolbox 43

Workflow Manager 2007 Training Day v 1. 2 Tools for the Oracle Applications DBA Toolbox 44

2007 Training Day v 1. 2 Tools for the Oracle Applications DBA Toolbox 45

2007 Training Day v 1. 2 Tools for the Oracle Applications DBA Toolbox 46

Abort. Process API procedure Abort. Process (itemtype in varchar 2, itemkey in varchar 2, process in varchar 2 default ’’, result in varchar 2 default eng_force); Aborts process execution and cancels outstanding notifications. Ø Any outstanding notifications or sub-processes are set to a status of COMPLETE. Ø 2007 Training Day v 1. 2 Tools for the Oracle Applications DBA Toolbox 47

Abort. Process API Ø Use Sql Plus to abort one workflow: exec WF_ENGINE. ABORTPROCESS( ‘WFERROR’, -- Item type ‘WF 1620’); -- 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. 2007 Training Day v 1. 2 Tools for the Oracle Applications DBA Toolbox 48

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 2007 Training Day v 1. 2 Tools for the Oracle Applications DBA Toolbox 49

Questions? Jeff Slavitz (415) 388 – 3003 Jeff@Oracle. Apps. Pro. Com 2007 Training Day v 1. 2 Tools for the Oracle Applications DBA Toolbox 50