Tools for the Oracle Applications DBA Toolbox

1
Tools for the Oracle Applications DBA Toolbox

• Jeff Slavitz
• Oracle Applications DBA
• Computer Creations Inc
• Jeff_at_OracleAppsPro.Com

2
Three of my Favorite Tools

• FNDLOAD to migrate application setup data
• SED to edit context files
• Workflow check and cleanup scripts

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

4
Moving Setup Data between Instances

• A tool exists to automate setup data
• Its free!
• Its supported by Oracle
• Its written by Oracle
• Its used by Oracle

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!

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

7
Using FNDLOAD

• Call from UNIX command line
• FNDLOAD apps/pwd 0 Y mode configfile datafile
  entity param ...
• lt 0 Y gt - 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.

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 NAMEVALUE.
• Metalink note 274667.1 lists command line to
  download all object types

9
(No Transcript)
10
(No Transcript)
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

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

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)

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

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.

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 Administrators Guide Configuration,
  Appendix B
• Metalink note 274667.1

17
FNDLOAD

• Questions???

18
Reasons to Edit Context Files

• After cloning usually need to modify Apps and
  RDBMS context files
• Oracles 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)

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!

20
How sed works

• Reads line of input
• Searches for a pattern using regular expression
• Replace text with vi-like syntax

21
Sed Example

• From the RDBMS context file after cloning
• ltdbprocesses oa_var"s_db_processes"gt1300lt/dbproce
  ssesgt
• What you want in your non-Prod instance
• ltdbprocesses oa_var"s_db_processes"gt200lt/dbproc
  essesgt
• How sed works
• Search for the line containing s_db_processes
• Replace the value between gt .. lt with a new value
• Write the new line to an output file

22

• BEFORE
• ltdbprocesses oa_var"s_db_processes"gt1300lt/dbproce
  ssesgt
• AFTER
• ltdbprocesses oa_var"s_db_processes"gt200lt/dbproces
  sesgt
• s/\(\"s_db_processes\"gt\).\(lt\)/\1200\2/
• HUH????
• Scary at first but its just a vi command
• s/ search string / replacement string /

23

• BEFORE
• ltdbprocesses oa_var"s_db_processes"gt1300lt/dbproce
  ssesgt
• s/\(\"s_db_processes\"gt\).\(lt\)/\1200\2/
• Search string
• \( \"s_db_processes\"gt \) pattern 1

24

• BEFORE
• ltdbprocesses oa_var"s_db_processes"gt1300lt/dbproce
  ssesgt
• s/\(\"s_db_processes\"gt\).\(lt\)/\1200\2/
• Search string
• pattern 1 PLUS
• . one or more characters

25

• BEFORE
• ltdbprocesses oa_var"s_db_processes"gt1300lt/dbproce
  ssesgt
• s/\(\"s_db_processes\"gt\).\(lt\)/\1200\2/
• Search string
• substring 1 PLUS one or more characters PLUS
• \( lt \) pattern 2

26

• BEFORE
• ltdbprocesses oa_var"s_db_processes"gt1300lt/dbproce
  ssesgt
• s/\(\"s_db_processes\"gt\).\(lt\)/\1200\2/
• Replacement string
• \1 200 \2
• pattern 1 200 pattern 2
• AFTER
• ltdbprocesses oa_var"s_db_processes"gt200lt/dbproces
  sesgt

27

• Use with environment variables too
• BEFORE
• lttemp_dir oa_var"s_temp"gt/apps51/test/common/temp
  lt/temp_dirgt
• SED command
• s\(\"s_temp\"gt\).\(lt\)\1/apps02/tmp/DBNAME\2
  
• AFTER
• lttemp_dir oa_var"s_temp"gt/apps02/tmp/testlt/temp_d
  irgt

28
Sed Scripts

• Refer to Supplemental file on NorCalOAUG website
• Edit RDBMS context file
• rdbms_editctx.sh
• Edit applications context file
• apps_editctx.sh

29
Customizing Context Files with SED

• Questions?

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

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

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!

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?

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

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 lt SYSDATE - 180
• GROUP by item_type
• HAVING count() gt 1000

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

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)

38
Workflow Status Monitor
39
Activity History
40
Status Diagram
41
Status Monitor Detail ScreenWorkflow Details
42
WF_SNAPSHOT.sql

• OLD ACTIVE workflows (WF started gt 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

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

44
Workflow Manager
45
(No Transcript)
46
(No Transcript)
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.

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.

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

50

• Questions?
• Jeff Slavitz
• (415) 388 3003
• Jeff_at_OracleAppsPro.Com