EBS問(wèn)題診斷方法

1EBS問(wèn)題診斷常用方法--OracleSSC--AndrewDu2TopicsHowToGetLevel12TraceHowToGetTraceConcurrentPrograms?HowtogetFormsLevelTraceandFRD?HowToGetFNDDebug?HowToRunSelectedGeneralLedgerProgramsInDebugModeinR11.5?HowToStoreFADebugMessagesInAFile?HowtoGetaDebuginReceivablesin11.5.10?HowtorunaPayablesConcurrentPrograminDebugMode?HowtogenerateadebugOM?HowtoGenerateandReturnthePODocumentApprovalManagerTraceDebugDataHowtoGenerateReceivingTransactionDebugStatementsin11.5.10andR12?HowToDebugWIP?HowtogetDebugInformationforInventoryMaterialTransaction?3HowToGetLevel12Trace?

為什么需要Trace?

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

Gathering10046trace

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

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

SessionTracingaltersessionsettrace'10046';altersessionsettimed_statistics=true;altersessionsetstatistics_level=all;altersessionsetmax_dump_=unlimited;altersessionsetevents'10046tracenamecontextforever,level12';altersessionsetevents'10046tracenamecontextoff';7HowToGetLevel12Trace?

TraceotherSession

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

Whattolookforinthetrace?

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

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_1ENDOFSTMTLastStatemExecutedEntBeforeRollbackFindRollback11HowToGetLevel12Trace?

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”statement12HowToGetLevel12Trace?

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

(TKProfInterpretation)TKProfoutputstructureSQLStatementParse/Execute/FetchstatisticsandtimingsLibraryCacheinformationRowsourceplanEventswaitedforbythestatement15Trace

(TKProfInterpretation)16HowToGetTraceConcurrentPrograms?在并發(fā)程序定義中啟用<EnableTrace>選項(xiàng)

SystemAdministrator->Concurrent->Program->Define17HowToGetTraceConcurrentPrograms?SetProfile<Concurrent:AllowDebugging>enabledhighleveltrace18TopicsHowToGetLevel12TraceHowToGetTraceConcurrentPrograms?HowtogetFormsLevelTraceandFRD?HowToGetFNDDebug?HowToRunSelectedGeneralLedgerProgramsInDebugModeinR11.5?HowToStoreFADebugMessagesInAFile?HowtoGetaDebuginReceivablesin11.5.10?HowtorunaPayablesConcurrentPrograminDebugMode?HowtogenerateadebugOM?HowtoGenerateandReturnthePODocumentApprovalManagerTraceDebugDataHowtoGenerateReceivingTransactionDebugStatementsin11.5.10andR12?HowToDebugWIP?HowtogetDebugInformationforInventoryMaterialTransaction?19HowtogetFormsLevelTraceandFRD?

WhatisFRD?

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

20HowtogetFormsLevelTraceandFRD?1.在用戶層設(shè)置pro

'ICX:FormsLauncher'e.g.updatethevalueof

ICX:FormsLauncherfortheusertobe

?play=&record=collect&log=/tmp/GW0097_frd.log2.用戶重新登錄系統(tǒng)，并獲得啟用FormRuntimeDiagnostics的提示信息.

3.完成測(cè)試后，在應(yīng)用服務(wù)器的下列路徑$FORMS60_TRACE_PATH找到debug文件Note:thedefaultdirectoryfor

$FORMS60_TRACE_PATHis$ORACLE_HOME/forms60/log.$FORMS_TRACE_DIR21HowtogetFormsLevelTraceandFRD?22HowtogetFormsLevelTraceandFRD?FRDlogoutput23HowtogetFormsLevelTraceandFRD?在Form中啟用TraceFormsleveltraceisSQLtracegeneratedbeforethetransactionissavedonaform.ItisenabledfromtheformandcapturesSQLcoderelatedtotheexecutionoftheform.

ThisshouldbeusedwhentheerroroccursontheformBEFOREclickingonthesave.

TypicallytheseareFRMorAPPerrorsbuttherecouldbeothervaryingerrormessages.

啟用步驟:Navigatetothepointintheapplicationrightbeforeyouareexperiencingtheproblem.

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

Help>Diagnostics>Trace>NoTrace

生成的Tracefile存在在數(shù)據(jù)庫(kù)服務(wù)器user_dump_dest對(duì)應(yīng)的目錄中,可使用下列SQL找到具體路徑selectvaluefromV$PARAMETERwherenamelike'user_dump%';

使用tkprof命令解析Trace信息:

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

example:tkprofexplain=<apps/apps>24HowtogetFormsLevelTraceandFRD?25HowToGetFNDDebug?切換到系統(tǒng)管理員職責(zé):Navigate:SystemAdministrator>Profile=>System在用戶層查找FNDproforUserwhoissubmittingtheprocesstodebug.User:UsersubmittingtheprocessProfile:FND:%Debug%在用戶層設(shè)置下列Pro并保存FND:DebugLogEnabled:YesFND:DebugLogLevel:STATEMENTFND:DebugLogModule:%

26HowToGetFNDDebug?27HowToGetFNDDebug?1.在執(zhí)行前臺(tái)功能操作執(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ā)請(qǐng)求，在日志中檢查輸出信息;28HowToGetFNDDebug?29HowToRunSelectedGeneralLedgerProgramsInDebugModeinR11.5

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

GLCRVL-RevaluationGLPPOS-PostingGLTTRN-TranslationGLCCON-ConsolidationTransferGLAMAS-RunMassAllocations30HowToStoreFADebugMessagesInAFile?1.執(zhí)行下列SQL，查詢(xún)debugfile存放目錄value(eg:/volume/d1/tmp_hpoc):selectvaluefromv$parameterwhereupper(name)='UTL_';在用戶設(shè)置下列pro

FA:PrintDebugtoYesFA:Debug,pleasepastethepathreturnedfromtheabovequery31HowtorunaPayablesConcurrentPrograminDebugMode?

1.LogintoSystemAdministratorresponsibility.2.NavigatetoConcurrent-Program-Define.3.QueryfortheConcurrentProgramName-PayablesAccountingProcess.4.ClickontheParametersbutton.5.ClickinthelinewiththeparameterDebug.6.ChecktheEnablebox.7.ChecktheDisplaybox.8.Save.9.ChooseYesfortheParameterDebugwhenrunPayablesAccountingProcess.TheLogtheDebugInformation.32HowtorunaPayablesConcurrentPrograminDebugMode?33HowtoGetaDebuginReceivablesin11.5.10?在用戶層設(shè)置下列Pro:FND:DebugLogEnabled=YESFND:DebugLog=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;34HowtoGetaDebuginReceivablesin11.5.10?AR模塊的下列forms可啟用特殊的Debug.Transactionsworkbench(ARXTWMAI)Receiptsworkbench(ARXRWMAI)Collections(ARXCWMAI)

啟用步驟1.SetPro(同上一頁(yè))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設(shè)置可使用下列SQL的輸出值：selectvaluefromv$parameterwhereupper(name)='UTL_';6.Debugfile生成數(shù)據(jù)庫(kù)服務(wù)器35HowtoGetaDebuginReceivablesin11.5.10?36HowtogenerateadebugOM?A.在SalesOrdersform中生成Debug信息:1.在用戶層設(shè)置下列Pro:OM:DebugLevel–設(shè)置為5OM:DebugLogDirectory–(e.g:/volume/d1/tmp_hpoc),執(zhí)行下列SQL查找數(shù)據(jù)庫(kù)服務(wù)器路徑

selectvaluefromv$parameterwherename=‘UTL_'2.打開(kāi)Order工作臺(tái)FromgotoTools->Debugandgetnotemessage(e.g.ThelogXXX).37HowtogenerateadebugOM?B.在并發(fā)請(qǐng)求中獲得debug信息:在用戶層設(shè)置proOM:DebugLevelto5.Debug信息將生成并發(fā)請(qǐng)求的log文件.

在并發(fā)請(qǐng)求的log文件中查看debug信息NavigatetoView>Requests>ReleventRequest_id(button).ClickViewOutput>Save"".38HowtoGenerateandReturnthePODocumentApprovalManagerTraceDebugData1.在OraclePurchasing的應(yīng)用層設(shè)置下列pro:Name:InitializationSQL-CustomValue:beginfnd_ctl.fnd_sess_ctl('','','TRUE','TRUE','LOG','ALTERSESSIONSETEVENTS='||''''||'10046TRACENAMECONTEXTFOREVER,LEVEL12'||'''');end;

Name:PO:SetDebugWorkflowONValue:YesName:PO:SetDebugConcurrentONValue:Yes2.設(shè)置并發(fā)管理器DocumentManagerprocesses為13.清理tracefile文件目錄

和表po_wf_debugNote:Makesurethemaximumtraceissettounlimited4.啟動(dòng)并發(fā)管理器DocumentManagerprocess.39HowtoGenerateandReturnthePODocumentApprovalManagerTraceDebugData推薦的附加診斷數(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=>Managerlog40HowtoGenerateReceivingTransactionDebugStatementsin11.5.10andR12Profile設(shè)置1.Alltransactions:a.RCV:DebugMode=Yesb.FND:DebugLogEnabled=Yesc.FND:DebugLogLevel=Statementd.FND:DebugLogModule=%

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

注：可參考InventoryMaterialTransactionsDebug設(shè)置41HowtoGenerateReceivingTransactionDebugStatementsin11.5.10andR123.RMA,DropShipPurchaseOrderorInternalSalesOrder/Requisition:a.OM:DebugLevel=5b.OM:DebugLogDirectory=<validdirectorypath>注：可參考OMdebug設(shè)置

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

說(shuō)明：此設(shè)置將為并發(fā)請(qǐng)求<ReceivingTransactionProcessor>生成TraceFile42HowToDebugWIP用戶層Pro設(shè)置及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;43HowtogetDebugInformationforInve