EBS问题诊断方法

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

为什么需要Trace?

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

Gathering10046trace

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

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

SessionTracingaltersessionsettracefile_identifier='10046';altersessionsettimed_statistics=true;altersessionsetstatistics_level=all;altersessionsetmax_dump_file_size=unlimited;altersessionsetevents'10046tracenamecontextforever,level12';altersessionsetevents'10046tracenamecontextoff';HowToGetLevel12Trace?

TraceotherSession

OpenTraceexecsys.dbms_system.set_ev(<sid>,<serial#>,10046,12,'');CloseTraceexecsys.dbms_system.set_ev(<sid>,<serial#>,10046,0,'');HowToGetLevel12Trace?SQLTracepathshowparameteruser_dump_destselect*fromv$parameterwherenamelike'user_dump_dest'HowToGetLevel12Trace?

Whattolookforinthetrace?

RollbackErrorSelectreturningnorows(forORA-01403errors)CalltostoredproceduresExecstatements(lastexeccompletedpriortorollback)ForhandledexceptionslookforcallstocodethatthrowserrormessageHowToGetLevel12Trace?

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=1078209519010842EXEC#198:c=0,e=423,p=0,cr=0,cu=20,mis=0,r=0,dep=1,og=4,tim=1078209519011382RPCEXEC: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=1078209519016729hv=2670021379ad='60292bf0'ROLLBACKTOFM_1ENDOFSTMTLastStatemExecutedEntBeforeRollbackFindRollbackHowToGetLevel12Trace?

Howtoreadarawtrace(cont.)PARSINGINCURSOR#198len=2959dep=1uid=173oct=3lid=173tim=1078209518764177hv=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”statementHowToGetLevel12Trace?

RawTrace+Understanding=RootCauseInthepriorexample:TheformthrewanORA-01403nodatafoundCursor#198wasthelaststatementexecutedpriortorollbackandreturnednorows(causeofORA-1403error)SQLbelongingto#198identifiedtheselectstatementIDbelongingtotransactionthatfailedwaslistedasabindNextsteps:DrilldowntothecodeleveltoinvestigatetheissueTARrelated,uploadtheRAWandTKProftracefilesTracefileanalyzetoolTkprofcommandtkproftracefileoutfile[explain=user/password][options...]TkprofOptionsprint=integerListonlythefirst'integer'SQLstatements.insert=filenameListSQLstatementsanddatainsideINSERTstatements.sys=noTKPROFdoesnotlistSQLstatementsrunasuserSYS.record=filenameRecordstatementsfoundinthetracefile.sort=optionSetofzeroormoresortoptionsTracefileanalyze

(TKProfInterpretation)TKProfoutputstructureSQLStatementParse/Execute/FetchstatisticsandtimingsLibraryCacheinformationRowsourceplanEventswaitedforbythestatementTracefileanalyze

(TKProfInterpretation)HowToGetTraceFileForConcurrentPrograms?在并发程序定义中启用<EnableTrace>选项

SystemAdministrator->Concurrent->Program->DefineHowToGetTraceFileForConcurrentPrograms?SetProfile<Concurrent:AllowDebugging>enabledhighleveltraceTopicsHowToGetLevel12TraceHowToGetTraceFileForConcurrentPrograms?HowtogetFormsLevelTraceandFRD?HowToGetFNDDebug?HowToRunSelectedGeneralLedgerProgramsInDebugModeinR11.5?HowToStoreFADebugMessagesInAFile?HowtoGetaDebuginReceivablesin11.5.10?HowtorunaPayablesConcurrentPrograminDebugMode?HowtogenerateadebugfileinOM?HowtoGenerateandReturnthePODocumentApprovalManagerTraceDebugDataHowtoGenerateReceivingTransactionDebugStatementsin11.5.10andR12?HowToDebugWIP?HowtogetDebugInformationforInventoryMaterialTransaction?HowtogetFormsLevelTraceandFRD?

WhatisFRD?

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

HowtogetFormsLevelTraceandFRD?1.在用户层设置profileoption

'ICX:FormsLauncher'e.g.updatethevalueof

ICX:FormsLauncherfortheusertobe

http://hostname:port/dev60cgi/f60cgi?play=&record=collect&log=/tmp/GW0097_frd.loghttp://hostname:port/forms/frmservlect?record=collect+log=logfile.log2.用户重新登录系统，并获得启用FormRuntimeDiagnostics的提示信息.

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

$FORMS60_TRACE_PATHis$ORACLE_HOME/forms60/log.$FORMS_TRACE_DIRHowtogetFormsLevelTraceandFRD?HowtogetFormsLevelTraceandFRD?FRDlogoutputHowtogetFormsLevelTraceandFRD?在Form中启用TraceFormsleveltraceisSQLtracegeneratedbeforethetransactionissavedonaform.ItisenabledfromtheformandcapturesSQLcoderelatedtotheexecutionoftheform.

ThisshouldbeusedwhentheerroroccursontheformBEFOREclickingonthesave.

TypicallytheseareFRMorAPPerrorsbuttherecouldbeothervaryingerrormessages.

启用步骤:Navigatetothepointintheapplicationrightbeforeyouareexperiencingtheproblem.

Turntraceonby:Help>Diagnostics>Trace>TracewithBinds(Mayrequirepassword)Apop-upmessagewithtracefilelocationandnamewillbedisplayed.Notethefilenameandpath.Duplicatetheerrorandthenstopimmediatelyafterwards.

Help>Diagnostics>Trace>NoTrace

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

使用tkprof命令解析Trace信息:

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

example:tkproffile1234.trcfile1234.outexplain=<apps/apps>HowtogetFormsLevelTraceandFRD?HowToGetFNDDebug?切换到系统管理员职责:Navigate:SystemAdministrator>Profile=>System在用户层查找FNDprofileoptionsforUserwhoissubmittingtheprocesstodebug.User:UsersubmittingtheprocessProfile:FND:%Debug%在用户层设置下列Profilevalues并保存FND:DebugLogEnabled:YesFND:DebugLogLevel:STATEMENTFND:DebugLogModule:%

HowToGetFNDDebug?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.执行并发请求，在日志中检查输出信息;HowToGetFNDDebug?HowToRunSelectedGeneralLedgerProgramsInDebugModeinR11.5

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

GLCRVL-RevaluationGLPPOS-PostingGLTTRN-TranslationGLCCON-ConsolidationTransferGLAMAS-RunMassAllocationsHowToStoreFADebugMessagesInAFile?1.执行下列SQL，查询debugfile存放目录value(eg:/volume/d1/tmp_hpoc):selectvaluefromv$parameterwhereupper(name)='UTL_FILE_DIR';在用户设置下列profileoption

FA:PrintDebugtoYesFA:DebugFilename,pleasepastethepathreturnedfromtheabovequeryHowtorunaPayablesConcurrentPrograminDebugMode?

1.LogintoSystemAdministratorresponsibility.2.NavigatetoConcurrent-Program-Define.3.QueryfortheConcurrentProgramName-PayablesAccountingProcess.4.ClickontheParametersbutton.5.ClickinthelinewiththeparameterDebug.6.ChecktheEnablebox.7.ChecktheDisplaybox.8.Save.9.ChooseYesfortheParameterDebugwhenrunPayablesAccountingProcess.TheLogFileContainstheDebugInformation.HowtorunaPayablesConcurrentPrograminDebugMode?HowtoGetaDebuginReceivablesin11.5.10?在用户层设置下列ProfileOption:FND:DebugLogEnabled=YESFND:DebugLogFilename=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;HowtoGetaDebuginReceivablesin11.5.10?AR模块的下列forms可启用特殊的Debug.Transactionsworkbench(ARXTWMAI)Receiptsworkbench(ARXRWMAI)Collections(ARXCWMAI)

启用步骤1.SetProfileFND%DEUBG%(同上一页)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_FILE_DIR';6.Debugfile生成数据库服务器HowtoGetaDebuginReceivablesin11.5.10?HowtogenerateadebugfileinOM?A.在SalesOrdersform中生成Debug信息:1.在用户层设置下列ProfileOption:OM:DebugLevel–设置为5OM:DebugLogDirectory–(e.g:/volume/d1/tmp_hpoc),执行下列SQL查找数据库服务器路径

selectvaluefromv$parameterwherename=‘UTL_FILE_DIR'2.打开Order工作台FromgotoTools->Debugandgetnotemessage(e.g.ThelogfileisXXX).HowtogenerateadebugfileinOM?B.在并发请求中获得debug信息:在用户层设置profileoptionOM:DebugLevelto5.Debug信息将生成并发请求的log文件.

在并发请求的log文件中查看debug信息NavigatetoView>Requests>ReleventRequest_id(button).ClickViewOutput>Savefileas"filename.txt".HowtoGenerateandReturnthePODocumentApprovalManagerTraceDebugData1.在OraclePurchasing的应用层设置下列profileoptions: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:Makesurethemaximumtracefilesizeissettounlimited4.启动并发管理器DocumentManagerprocess.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=>ManagerlogHowtoGenerateReceivingTransactionDebugStatementsin11.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)=<validdirectorypathplusfilename>

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

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

说明：此设置将为并发请求<ReceivingTransactionProcessor>生成TraceFileHowToDebugWIP用户层ProfileOption设置及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;HowtogetDebugInformat