EBS 问题诊断方法课件

EBS问题诊断方法EBS问题诊断常用方法--OracleSSC--AndrewDuEBS问题诊断方法TopicsHowToGetLevel12TraceHowToGetTraceConcurrentPrograms?HowtogetFormsLevelTraceandFRD?HowToGetFNDDebug?HowToRunSelectedGeneralLedgerProgramsInDebugModeinR11.5?HowToStoreFADebugMessagesInAFile?HowtoGetaDebuginReceivablesin11.5.10?HowtorunaPayablesConcurrentPrograminDebugMode?HowtogenerateadebugOM?HowtoGenerateandReturnthePODocumentApprovalManagerTraceDebugDataHowtoGenerateReceivingTransactionDebugStatementsin11.5.10andR12?HowToDebugWIP?HowtogetDebugInformationforInventoryMaterialTransaction?EBS问题诊断方法HowToGetLevel12Trace?

为什么需要Trace?

提取执行与错误信息获取执行程序/SQL获取执行计划与成本信息获取绑定变量值获取等待事件等EBS问题诊断方法HowToGetLevel12Trace?

Gathering10046trace

SQL_TRACE是Oracle数据库提供的用于进行SQL跟踪的手段，在某种意义上讲，可以说是Oracle最强有力的辅助诊断工具。10046事件是Oracle提供的内部事件，是对SQL_TRACE的增强（包含绑定值与等待事务事件)10046事件可以设置以下四个级别:1-启用标准的SQL_TRACE功能,等价于sql_trace4-Level1加上绑定值(bindvalues)8-Level1+等待事件跟踪12-Level1+Level4+Level8EBS问题诊断方法HowToGetLevel12Trace?

在EBS中启用10046trace切换到<系统管理员>职责Navigate:SystemAdministrator>Profile=>System在用户层查询pro'InitializationSQLStatement-Custom'.User:UsersubmittingtheprocessProfile:InitializationSQLStatement-Custom编辑User字段，输入下列值并保存beginfnd_ctl.fnd_sess_ctl('','','TRUE','TRUE','LOG','ALTERSESSIONSETEVENTS='||''''||'10046TRACENAMECONTEXTFOREVER,LEVEL12'||'''');end;EBS问题诊断方法HowToGetLevel12Trace?

SessionTracingaltersessionsettrace'10046';altersessionsettimed_statistics=true;altersessionsetstatistics_level=all;altersessionsetmax_dump_=unlimited;altersessionsetevents'10046tracenamecontextforever,level12';altersessionsetevents'10046tracenamecontextoff';EBS问题诊断方法HowToGetLevel12Trace?

TraceotherSession

OpenTraceexecsys.dbms_system.set_ev(<sid>,<serial#>,10046,12,'');CloseTraceexecsys.dbms_system.set_ev(<sid>,<serial#>,10046,0,'');EBS问题诊断方法HowToGetLevel12Trace?SQLTracepathshowparameteruser_dump_destselect*fromv$parameterwherenamelike'user_dump_dest'EBS问题诊断方法HowToGetLevel12Trace?

Whattolookforinthetrace?

RollbackErrorSelectreturningnorows(forORA-01403errors)CalltostoredproceduresExecstatements(lastexeccompletedpriortorollback)ForhandledexceptionslookforcallstocodethatthrowserrormessageEBS问题诊断方法HowToGetLevel12Trace?

HowtoreadaRawTrace(note39817.1)CaseStudy:ORA-01403NoDataFoundPARSE#198:c=0,e=60,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=10842EXEC#198:c=0,e=423,p=0,cr=0,cu=20,mis=0,r=0,dep=1,og=4,tim=11382RPCEXEC:c=530000,e=1892352RPCCALL:PROCEDUREAPPS.ARP_STANDARD.DEBUG(LINEINVARCHAR2);RPCBINDS:bind0:dty=1bfp=0ae65b50flg=08avl=54mxl=54val="01:42:27-arxtwmai_form_main.arxtwmai_main(ON-ERROR)+"PARSINGINCURSOR#92len=16dep=0uid=173oct=45lid=173tim=16729hv=2670021379ad='60292bf0'ROLLBACKTOFM_1ENDOFSTMTLastStatemExecutedEntBeforeRollbackFindRollbackEBS问题诊断方法HowToGetLevel12Trace?

Howtoreadarawtrace(cont.)PARSINGINCURSOR#198len=2959dep=1uid=173oct=3lid=173tim=14177hv=3671187712ad='63528a1c'SELECTcm.customer_trx_line_id,cm.previous_customer_trx_line_id,….WHEREcm.previous_customer_trx_line_id=:b0BINDS#198:bind0:dty=11mxl=16(16)mal=00scl=00pre=00oacflg=18oacfl2=1size=16offset=0bfp=40cd29e8bln=16avl=16flg=05value=254825ExecstatementsFindBindvaluesSearchfor“BINDS”

statementEBS问题诊断方法HowToGetLevel12Trace?

RawTrace+Understanding=RootCauseInthepriorexample:TheformthrewanORA-01403nodatafoundCursor#198wasthelaststatementexecutedpriortorollbackandreturnednorows(causeofORA-1403error)SQLbelongingto#198identifiedtheselectstatementIDbelongingtotransactionthatfailedwaslistedasabindNextsteps:DrilldowntothecodeleveltoinvestigatetheissueTARrelated,uploadtheRAWandTKProftracefilesEBS问题诊断方法TracetoolTkprofcommandtkproftrace[explain=user/password][options...]TkprofOptionsprint=integerListonlythefirst'integer'SQLstatements.insert=ListSQLstatementsanddatainsideINSERTstatements.sys=noTKPROFdoesnotlistSQLstatementsrunasuserSYS.record=Recordstatementsfoundinthetracefile.sort=optionSetofzeroormoresortoptionsEBS问题诊断方法Trace

(TKProfInterpretation)TKProfoutputstructureSQLStatementParse/Execute/FetchstatisticsandtimingsLibraryCacheinformationRowsourceplanEventswaitedforbythestatementEBS问题诊断方法Trace

(TKProfInterpretation)EBS问题诊断方法HowToGetTraceConcurrentPrograms?在并发程序定义中启用<EnableTrace>选项

SystemAdministrator->Concurrent->Program->DefineEBS问题诊断方法HowToGetTraceConcurrentPrograms?SetProfile<Concurrent:AllowDebugging>enabledhighleveltraceEBS问题诊断方法TopicsHowToGetLevel12TraceHowToGetTraceConcurrentPrograms?HowtogetFormsLevelTraceandFRD?HowToGetFNDDebug?HowToRunSelectedGeneralLedgerProgramsInDebugModeinR11.5?HowToStoreFADebugMessagesInAFile?HowtoGetaDebuginReceivablesin11.5.10?HowtorunaPayablesConcurrentPrograminDebugMode?HowtogenerateadebugOM?HowtoGenerateandReturnthePODocumentApprovalManagerTraceDebugDataHowtoGenerateReceivingTransactionDebugStatementsin11.5.10andR12?HowToDebugWIP?HowtogetDebugInformationforInventoryMaterialTransaction?EBS问题诊断方法HowtogetFormsLevelTraceandFRD?

WhatisFRD?

FRDisFormsRuntimeDiagnosticsTheFormsRuntimeDiagnostics(FRD)loggingmechanismwasintroducedinForms4.5.10,andisakeytoolforinvestigatinganddebuggingFormsproblems.Whenenabled,itlogseveryuseraction,everytriggerthatfires,everyformsbuilt-incalled,andvaluechanges(deltas)ofFormsitems

EBS问题诊断方法HowtogetFormsLevelTraceandFRD?1.在用户层设置pro

'ICX:FormsLauncher'e.g.updatethevalueof

ICX:FormsLauncherfortheusertobe

?play=&record=collect&log=/tmp/GW0097_frd.log2.用户重新登录系统，并获得启用FormRuntimeDiagnostics的提示信息.

3.完成测试后，在应用服务器的下列路径$FORMS60_TRACE_PATH找到debug文件Note:thedefaultdirectoryfor

$FORMS60_TRACE_PATHis$ORACLE_HOME/forms60/log.$FORMS_TRACE_DIREBS问题诊断方法HowtogetFormsLevelTraceandFRD?EBS问题诊断方法HowtogetFormsLevelTraceandFRD?FRDlogoutputEBS问题诊断方法HowtogetFormsLevelTraceandFRD?在Form中启用TraceFormsleveltraceisSQLtracegeneratedbeforethetransactionissavedonaform.ItisenabledfromtheformandcapturesSQLcoderelatedtotheexecutionoftheform.

ThisshouldbeusedwhentheerroroccursontheformBEFOREclickingonthesave.

TypicallytheseareFRMorAPPerrorsbuttherecouldbeothervaryingerrormessages.

启用步骤:Navigatetothepointintheapplicationrightbeforeyouareexperiencingtheproblem.

Turntraceonby:Help>Diagnostics>Trace>TracewithBinds(Mayrequirepassword)Apop-upmessagewithtraceandnamewillbedisplayed.Notetheandpath.Duplicatetheerrorandthenstopimmediatelyafterwards.

Help>Diagnostics>Trace>NoTrace

生成的Tracefile存在在数据库服务器user_dump_dest对应的目录中,可使用下列SQL找到具体路径selectvaluefromV$PARAMETERwherenamelike'user_dump%';

使用tkprof命令解析Trace信息:

tkprof<>.trc<>.outexplain=<foundusername/password>

example:tkprofexplain=<apps/apps>EBS问题诊断方法HowtogetFormsLevelTraceandFRD?EBS问题诊断方法HowToGetFNDDebug?切换到系统管理员职责:Navigate:SystemAdministrator>Profile=>System在用户层查找FNDproforUserwhoissubmittingtheprocesstodebug.User:UsersubmittingtheprocessProfile:FND:%Debug%在用户层设置下列Pro并保存FND:DebugLogEnabled:YesFND:DebugLogLevel:STATEMENTFND:DebugLogModule:%

EBS问题诊断方法HowToGetFNDDebug?EBS问题诊断方法HowToGetFNDDebug?1.在执行前台功能操作执行操作前，执行下列SQL，SELECTMAX(log_sequence)fromfnd_log_message;完成操作后，执行下列SQL查看Debug输出:selectlog.message_text,log.module

fromfnd_log_messageslog

whereuser_id=&user_id

andlog_sequence>&max_logsequenceorder

bylog.log_sequence;2.执行并发请求，在日志中检查输出信息;EBS问题诊断方法HowToGetFNDDebug?EBS问题诊断方法HowToRunSelectedGeneralLedgerProgramsInDebugModeinR11.5

在用户层设置pro“GL:DebugMode”为Yes.下列程序将产生debug信息

GLCRVL-RevaluationGLPPOS-PostingGLTTRN-TranslationGLCCON-ConsolidationTransferGLAMAS-RunMassAllocationsEBS问题诊断方法HowToStoreFADebugMessagesInAFile?1.执行下列SQL，查询debugfile存放目录value(eg:/volume/d1/tmp_hpoc):selectvaluefromv$parameterwhereupper(name)='UTL_';在用户设置下列pro

FA:PrintDebugtoYesFA:Debug,pleasepastethepathreturnedfromtheabovequeryEBS问题诊断方法HowtorunaPayablesConcurrentPrograminDebugMode?

1.LogintoSystemAdministratorresponsibility.2.NavigatetoConcurrent-Program-Define.3.QueryfortheConcurrentProgramName-PayablesAccountingProcess.4.ClickontheParametersbutton.5.ClickinthelinewiththeparameterDebug.6.ChecktheEnablebox.7.ChecktheDisplaybox.8.Save.9.ChooseYesfortheParameterDebugwhenrunPayablesAccountingProcess.TheLogtheDebugInformation.EBS问题诊断方法HowtorunaPayablesConcurrentPrograminDebugMode?EBS问题诊断方法HowtoGetaDebuginReceivablesin11.5.10?在用户层设置下列Pro:FND:DebugLogEnabled=YESFND:DebugLog=NULLFND:DebugLogLevel=STATEMENT(mostdetailedlog)FND:DebugLogModule=%orar%AR:EnableDebugMessageOutput=YESCheckDebug1.在执行前后功能操作前在SQLPlus中执行下列SQL，SELECTMAX(log_sequence)fromfnd_log_messages;2.完成操作后，执行下列SQL查看Debug输出:SELECTLOG.MESSAGE_TEXT,LOG.MODULEFROMFND_LOG_MESSAGESLOGWhereuser_id=&user_idandlog_sequence>&max_logsequenceORDERBYLOG.LOG_SEQUENCE;EBS问题诊断方法HowtoGetaDebuginReceivablesin11.5.10?AR模块的下列forms可启用特殊的Debug.Transactionsworkbench(ARXTWMAI)Receiptsworkbench(ARXRWMAI)Collections(ARXCWMAI)

启用步骤1.SetPro(同上一页)2.Onthemenu,clickhelp/diagnostic/examine(11.5)3.Changeblockfield,thenchoosefromthelistofvalues:PARAMETER4.Forthefield,thenTYPEin:AR_DEBUG_FLAGThereisnoLOVforthisfield.ExplicitlytypeinAR_DEBUG_FLAG.5.Forthevalue,thenTYPEin:FS<path><file>(example:FS/volume/d1/tmp_hpoc2010debug.log)Path设置可使用下列SQL的输出值：selectvaluefromv$parameterwhereupper(name)='UTL_';6.Debugfile生成数据库服务器EBS问题诊断方法HowtoGetaDebuginReceivablesin11.5.10?EBS问题诊断方法HowtogenerateadebugOM?A.在SalesOrdersform中生成Debug信息:1.在用户层设置下列Pro:OM:DebugLevel–设置为5OM:DebugLogDirectory–(e.g:/volume/d1/tmp_hpoc),执行下列SQL查找数据库服务器路径

selectvaluefromv$parameterwherename=‘UTL_'2.打开Order工作台FromgotoTools->Debugandgetnotemessage(e.g.ThelogXXX).EBS问题诊断方法HowtogenerateadebugOM?B.在并发请求中获得debug信息:在用户层设置proOM:DebugLevelto5.Debug信息将生成并发请求的log文件.

在并发请求的log文件中查看debug信息NavigatetoView>Requests>ReleventRequest_id(button).ClickViewOutput>Save"".EBS问题诊断方法HowtoGenerateandReturnthePODocumentApprovalManagerTraceDebugData1.在OraclePurchasing的应用层设置下列pro:Name:InitializationSQL-CustomValue:beginfnd_ctl.fnd_sess_ctl('','','TRUE','TRUE','LOG','ALTERSESSIONSETEVENTS='||''''||'10046TRACENAMECONTEXTFOREVER,LEVEL12'||'''');end;

Name:PO:SetDebugWorkflowONValue:YesName:PO:SetDebugConcurrentONValue:Yes2.设置并发管理器DocumentManagerprocesses为13.清理tracefile文件目录

和表po_wf_debugNote:Makesurethemaximumtraceissettounlimited4.启动并发管理器DocumentManagerprocess.EBS问题诊断方法HowtoGenerateandReturnthePODocumentApprovalManagerTraceDebugData推荐的附加诊断数据:wfstat.sqlandwfstatus.sqloutputsoftheapprovalpo_wf_debugtablecontent检查Debug信息selectitemtype,itemkey,execution_sequence,debug_messagefrompo_wf_debugwhereitemtype='&ItemType'anditemkey='&ItemKey'orderbyexecution_sequence;检查并发管理器日志信息Sysadmin=>Concurrent=>Managers=>Administration=>PODocumentManager=>Processes=>Active=>ManagerlogEBS问题诊断方法HowtoGenerateReceivingTransactionDebugStatementsin11.5.10andR12Profile设置1.Alltransactions:a.RCV:DebugMode=Yesb.FND:DebugLogEnabled=Yesc.FND:DebugLogLevel=Statementd.FND:DebugLogModule=%

注：可参考FNDDebug设置和debug信息读取2.LinetobetransactedreferencesanItem:a.TP:INVTransactionprocessingmode=On-lineb.INV:DebugTrace=Yesc.INV:DebugLevel=11d.INV:Debugfile(Includingthecompletepath)=<validdirectorypathplus>

注：可参考InventoryMaterialTransactionsDebug设置EBS问题诊断方法HowtoGenerateReceivingTransactionDebugStatementsin11.5.10andR123.RMA,DropShipPurchaseOrderorInternalSalesOrder/Requisition:a.OM:DebugLevel=5b.OM:DebugLogDirectory=<validdirectorypath>注：可参考OMdebug设置

4.RCV:ProcessingMode=ImmediateorBatch:PO:EnableSqlTraceforReceivingProcessor=Yes

说明：此设置将为并发请求<ReceivingTransactionProcessor>生成TraceFileEBS问题诊断方法HowToDebugWIP用户层Pro设置及debug信息读取1.FND:DebugLogEnabled=Yes2.FND:DebugLogLevel=Statement3.FND:DebugLogModule=WIP%selectmessage_textfromfnd_log_messageswhereaudsid=(selectoracle_session_idfromfnd_concurrent_requestswhererequest_id=&MOVE_TRNX_WORKER_REQUEST_ID)orderbylog_sequence;EBS问题诊断方法HowtogetDebugInformationforInventoryMa