EBS問題診斷常用方法TopicsHowToGetLevel12TraceHowToGetTraceFileForConcurrentPrograms?HowtogetFormsLevelTraceandFRD?HowToGetFNDDebug?HowToRunSelectedGeneralLedgerProgramsInDebugModeinR11.5?HowToStoreFADebugMessagesInAFile?HowtoGetaDebuginReceivablesin11.5.10?HowtorunaPayablesConcurrentPrograminDebugMode?HowtogenerateadebugfileinOM?HowtoGenerateandReturnthePODocumentApprovalManagerTraceDebugDataHowtoGenerateReceivingTransactionDebugStatementsin11.5.10andR12?HowToDebugWIP?HowtogetDebugInformationforInventoryMaterialTransaction?HowToGetLevel12Trace?

為什么需要Trace?

提取執(zhí)行與錯誤信息獲取執(zhí)行程序/SQL獲取執(zhí)行計劃與成本信息獲取綁定變量值獲取等待事件等HowToGetLevel12Trace?

Gathering10046trace

SQL_TRACE是Oracle數(shù)據(jù)庫提供的用于進行SQL跟蹤的手段，在某種意義上講，可以說是Oracle最強有力的輔助診斷工具。10046事件是Oracle提供的內(nèi)部事件，是對SQL_TRACE的增強（包含綁定值與等待事務事件)10046事件可以設置以下四個級別:1-啟用標準的SQL_TRACE功能,等價于sql_trace4-Level1加上綁定值(bindvalues)8-Level1+等待事件跟蹤12-Level1+Level4+Level8HowToGetLevel12Trace?

在EBS中啟用10046trace切換到<系統(tǒng)管理員>職責Navigate:SystemAdministrator>Profile=>System在用戶層查詢profileoption'InitializationSQLStatement-Custom'User:UsersubmittingtheprocessProfile:InitializationSQLStatement-Custom編輯User字段，輸入下列值并保存beginfnd_ctl.fnd_sess_ctl('','','TRUE','TRUE','LOG','ALTERSESSIONSETEVENTS='||''''||'10046TRACENAMECONTEXTFOREVER,LEVEL12'||'''');end;ALTERSESSIONSETTRACEFILE_IDENTIFIER='trace_file'max_dump_file_size=unlimitedEVENTS='10046TRACENAMECONTEXTFOREVER,LEVEL12'HowToGetLevel12Trace?

SessionTracingaltersessionsettracefile_identifier='10046';altersessionsettimed_statistics=true;altersessionsetstatistics_level=all;altersessionsetmax_dump_file_size=unlimited;altersessionsetevents'10046tracenamecontextforever,level12';altersessionsetevents'10046tracenamecontextoff';settimed_statistics系統(tǒng)統(tǒng)計信息的粒度，需要設置為true,否則基于時間的統(tǒng)計都沒有了HowToGetLevel12Trace?

TraceotherSession

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

Whattolookforinthetrace?

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

HowtoreadaRawTrace(note39817.1)CaseStudy:ORA-01403NoDataFoundRPCEXEC: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)+"ROLLBACKTOFM_1ENDOFSTMTLastStatemExecutedEntBeforeRollbackFindRollbackHowToGetLevel12Trace?

Howtoreadarawtrace(cont.)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=optionSetofzeroormoresortoptionssort=optionSetofzeroormoreofthefollowingsortoptions:prscntnumberoftimesparsewascalledprscpucputimeparsingprselaelapsedtimeparsingprsdsknumberofdiskreadsduringparseprsqrynumberofbuffersforconsistentreadduringparseprscunumberofbuffersforcurrentreadduringparseprsmisnumberofmissesinlibrarycacheduringparseexecntnumberofexecutewascalledexecpucputimespentexecutingexeelaelapsedtimeexecutingexedsknumberofdiskreadsduringexecuteexeqrynumberofbuffersforconsistentreadduringexecuteexecunumberofbuffersforcurrentreadduringexecuteexerownumberofrowsprocessedduringexecuteexemisnumberoflibrarycachemissesduringexecutefchcntnumberoftimesfetchwascalledfchcpucputimespentfetchingfchelaelapsedtimefetchingfchdsknumberofdiskreadsduringfetchfchqrynumberofbuffersforconsistentreadduringfetchfchcunumberofbuffersforcurrentreadduringfetchfchrownumberofrowsfetchedTracefileanalyze

(TKProfInterpretation)TKProfoutputstructureSQLStatementParse/Execute/FetchstatisticsandtimingsLibraryCacheinformationRowsourceplanEventswaitedforbythestatementNote29012.1TKPROFUsage-QuickReference.mhtTKProfInterpretation(9iandabove)[ID760786.1]Parse-statisticsfromparsingthecursor.Thisincludesinformationforplangenerationetc.Execute-statisticsfortheexecutionphaseofacursorFetch-statisticsforactuallyfetchingtherowscount-numberoftimeseachindividualactivityhasbeenperformedonthisparticularcursorcpu-cputimeusedbythiscursorelapsed-elapsedtimeforthiscursor(includesthecputime)disk-Thisindicatesthenumberofblocksreadfromdisk.Generallyitwouldbepreferableforblockstobereadfromthebuffercacheratherthandisk.query-ThiscolumnisincrementedifabufferisreadinConsistentmode.AConsistentmodebufferisonethathasbeengeneratedtogiveaconsistentreadsnapshotforalongrunningtransaction.current-Thiscolumnisincrementedifabufferisfoundinthebuffercachethatisnewenoughforthecurrenttransactionandisincurrentmode(anditisnotaCRbuffer).Thisappliestobuffersthathavebeenreadintothecacheaswellasbuffersthatalreadyexistinthecacheincurrentmode.rows-RowsretrievedbythisstepTracefileanalyze

(TKProfInterpretation)HowToGetTraceFileForConcurrentPrograms?在并發(fā)程序定義中啟用<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.用戶重新登錄系統(tǒng)，并獲得啟用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存在在數(shù)據(jù)庫服務器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?切換到系統(tǒng)管理員職責:Navigate:SystemAdministrator>Profile=>System在用戶層查找FNDprofileoptionsforUserwhoissubmittingtheprocesstodebug.User:UsersubmittingtheprocessProfile:FND:%Debug%在用戶層設置下列Profilevalues并保存FND:DebugLogEnabled:YesFND:DebugLogLevel:STATEMENTFND:DebugLogModule:%

HowToGetFNDDebug?HowToGetFNDDebug?1.在執(zhí)行前臺功能操作執(zhí)行操作前，執(zhí)行下列SQL，SELECTMAX(log_sequence)fromfnd_log_message;完成操作后，執(zhí)行下列SQL查看Debug輸出:selectlog.message_text,log.module

fromfnd_log_messageslog

whereuser_id=&user_id

andlog_sequence>&max_logsequenceorder

bylog.log_sequence;2.執(zhí)行并發(fā)請求，在日志中檢查輸出信息;HowToGetFNDDebug?HowToRunSelectedGeneralLedgerProgramsInDebugModeinR11.5

在用戶層設置profileoption“GL:DebugMode”為Yes.下列程序?qū)a(chǎn)生debug信息

GLCRVL-RevaluationGLPPOS-PostingGLTTRN-TranslationGLCCON-ConsolidationTransferGLAMAS-RunMassAllocationsHowToStoreFADebugMessagesInAFile?1.執(zhí)行下列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??在用戶層設置下列ProfileOption:FND:DebugLogEnabled=YESFND:DebugLogFilename=NULLFND:DebugLogLevel=STATEMENT(mostdetailedlog)FND:DebugLogModule=%orar%AR:EnableDebugMessageOutput=YESCheckDebug1.在執(zhí)行前后功能操作前在SQLPlus中執(zhí)行下列SQL，SELECTMAX(log_sequence)fromfnd_log_messages;2.完成操作后，執(zhí)行下列SQL查看Debug輸出:SELECTLOG.MESSAGE_TEXT,LOG.MODULEFROMFND_LOG_MESSAGESLOGWhereuser_id=&user_idandlog_sequence>&max_logsequenceORDERBYLOG.LOG_SEQUENCE;?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生成數(shù)據(jù)庫服務器?HowtogenerateadebugfileinOM?A.在SalesOrdersform中生成Debug信息:1.在用戶層設置下列ProfileOption:OM:DebugLevel–設置為5OM:DebugLogDirectory–(e.g:/volume/d1/tmp_hpoc),執(zhí)行下列SQL查找數(shù)據(jù)庫服務器路徑

selectvaluefromv$parameterwherename=‘UTL_FILE_DIR'2.打開Order工作臺FromgotoTools->Debugandgetnotemessage(e.g.ThelogfileisXXX).HowtogenerateadebugfileinOM?B.在并發(fā)請求中獲得debug信息:在用戶層設置profileoptionOM:DebugLevelto5.Debug信息將生成并發(fā)請求的log文件.

在并發(fā)請求的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.設置并發(fā)管理器DocumentManagerprocesses為13.清理tracefile文件目錄

和表po_wf_debugNote:Makesurethemaximumtracefilesizeissettounlimited4.啟動并發(fā)管理器DocumentManagerprocess.HowtoGenerateandReturnthePODocumentApprovalManagerTraceDebugData推薦的附加診斷數(shù)據(jù):wfstat.sqlandwfstatus.sqloutputsoftheapprovalpo_wf_debugtablecontent檢查Debug信息selectitemtype,itemkey,execution_sequence,debug_messagefrompo_wf_debugwhereitemtype='&ItemType'anditemkey='&ItemKey'orderbyexecution_sequence;檢查并發(fā)管理器日志信息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

說明：此設置將為并發(fā)請求<ReceivingTransactionProcessor>生成TraceFileHowToDebugWIP用戶層ProfileOption設置及debug信息讀取1.FND:DebugLogEnabled=Yes2.FND:DebugLogLevel=Statement3.FND:DebugLogModule=WIP%selectmessage_textfromfnd_l