深入解析Oracle.ppt

,深入解析Oracle数据库的初始化、Grid以及云计算,盖国强(eygle)MobileSN:eygleSite:Mail:eygle,WhoamI,ITPUB论坛超级版主OracleACEDirector独立咨询顾问、技术支持顾问个人站点:,2004,2005,2006,2007,2008,Subjects,SIDEA数据库的引导数据库的跟踪与诊断数据库的初始化SIDEBOracle的Grid1.0ORA-00600:internalerrorcode,arguments:OSDEP_INTERNAL,ORA-27302:failureoccurredat:skgpwreset1ORA-27303:additionalinformation:invalidsharedctxORA-27146:post/waitinitializationfailedORA-27300:OSsystemdependentoperation:semgetfailedwithstatus:28ORA-27301:OSfailuremessage:NospaceleftondeviceORA-27302:failureoccurredat:sskgpsemsper,MonDec314:24:302007Errorsinfile/oraclehx/app/admin/sxlss/bdump/sxlss_pmon_360454.trc:ORA-07445:exceptionencountered:coredumpPSP0startedwithpid=3,OSid=422106MMANstartedwithpid=4,OSid=303332DBW0startedwithpid=5,OSid=299324。MonDec314:24:332007PSP0:terminatinginstanceduetoerror472InstanceterminatedbyPSP0,pid=422106,instfix-i|grepMLAllfilesetsfor5.3.0.0_AIX_MLwerefound.Allfilesetsfor5300-01_AIX_MLwerefound.Allfilesetsfor5300-02_AIX_MLwerefound.Allfilesetsfor5300-03_AIX_MLwerefound.Allfilesetsfor5300-04_AIX_MLwerefound.Allfilesetsfor5300-05_AIX_MLwerefound.Notallfilesetsfor5300-06_AIX_MLwerefound.,数据库的启动过程-Mount,mount过程,ORA-00202:controlfile:/opt/oracle/oradata/conner/control01.ctlORA-27037:unabletoobtainfilestatusLinuxError:2:NosuchfileordirectoryAdditionalinformation:3,*.control_files=/opt/oracle/oradata/conner/control01.ctl,/opt/oracle/oradata/conner/control02.ctl,/opt/oracle/oradata/conner/control03.ctl,ThuJun0517:57:102008ALTERDATABASEMOUNTErrorsinfile/databases/app/oracle/diag/rdbms/ora_test/ora_test/trace/ora_test_ora_3479.trc(incident=36154):ORA-00600:internalerrorcode,arguments:kccpb_sanity_check_2,500625,499958,0 x000000000,Incidentdetailsin:/databases/app/oracle/diag/rdbms/ora_test/ora_test/incident/incdir_36154/ora_test_ora_3479_i36154.trcORA-600signalledduring:ALTERDATABASEMOUNT,ORA-600kccpb_sanity_check_2isreportedwhentheSCNinthecontrolfileheaderislowerthantheSCNinacontrolfileblock.Theheadershouldalwaysbeequelorlargerthanthecontrolfileblocks.ArgaSCN,SystemChangeNumber,incontrolblockheader.ArgbSCN,SystemChangeNumber,incontrolfileheader.Argb,数据库的启动过程-Open,open过程,altersessionseteventsimmediatetracenamecontrolflevel8;,*.control_files=/opt/oracle/oradata/conner/control01.ctl,/opt/oracle/oradata/conner/control02.ctl,/opt/oracle/oradata/conner/control03.ctl,unrecoverablescn:0 x0000.0000000004/23/200401:20:52Checkpointcnt:1569scn:0 x0000.0148193906/22/200419:02:22Stopscn:0 xffff.ffffffff06/22/200418:58:05CreationCheckpointedatscn:0 x0000.000000ae07/16/200303:40:10,SQLaltersessionsetevents=10046tracenamecontextforever,level12;Sessionaltered.SQLalterdatabasemount;Databasealtered.SQLalterdatabaseopen;Databasealtered.,WAIT#1:nam=directpathreadela=43p1=1p2=1p3=1WAIT#1:nam=directpathreadela=4p1=2p2=1p3=1WAIT#1:nam=directpathreadela=3p1=3p2=1p3=1WAIT#1:nam=directpathreadela=2p1=4p2=1p3=1WAIT#1:nam=directpathreadela=2p1=201p2=1p3=1WAIT#1:nam=dbfilesequentialreadela=88p1=1p2=417p3=1WAIT#1:nam=dbfilesequentialreadela=91p1=1p2=377p3=1=PARSINGINCURSOR#2len=188dep=1uid=0oct=1lid=0tim=1149404325230977hv=0ad=b700de24createtablebootstrap$(line#numbernotnull,obj#numbernotnull,sql_textvarchar2(4000)notnull)storage(initial50Kobjno56extents(file1block377)ENDOFSTMTPARSE#2:c=0,e=1173,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1149404325230943BINDS#2:EXEC#2:c=0,e=473,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1149404325231638,控制文件及数据文件信息,分析控制文件的内容分析所有数据文件头获得地址信息,altersessionseteventsimmediatetracenameFILE_HDRSlevel10,altersessionseteventsimmediatetracenameCONTROLFlevel10,Tablespace#0-SYSTEMrel_fn:1Creationatscn:0 x0000.0000000704/24/200611:34:39Backuptakenatscn:0 x0004.6c2d657e02/12/200715:54:52thread:1resetlogscount:0 x24dc1f7dscn:0 x0004.6c432ec0recoveredat04/07/200721:04:11status:0 x4rootdba:0 x004001a1chkptcnt:6933ctlcnt:6932,SQLselectgetbfno(0 x004001a1)fromdual;GETBFNO(0X004001A1)-datafile#is:1datablockis:417,Oracle9i独一无二的Cache对象,SQLselectsegment_name,segment_type,header_file,header_block2fromdba_segmentswheresegment_type=CACHE;SEGMENT_NAMESEGMENT_TYPEHEADER_FILEHEADER_BLOCK-1.417CACHE1417,Startdumpdatablockstsn:0file#:1minblk417maxblk419buffertsn:0rdba:0 x004001a1(1/417)CompatibilityentryforBOOTSTRP:Size:24Release0 x134217728By0 x153092096Dumpofmemoryfrom0 x0AA84E4Cto0 x0AA84E50AA84E4000400179y.,SQLselectgetbfno(00400179)fromdual;GETBFNO(00400179)-datafile#is:1datablockis:377,SQLselectsegment_namefromdba_extents2whereblock_idbetween377andblocks+377-1;SEGMENT_NAME-BOOTSTRAP$,深入浅出探索数据库启动,WAIT#1:nam=directpathreadela=43p1=1p2=1p3=1WAIT#1:nam=directpathreadela=4p1=2p2=1p3=1WAIT#1:nam=directpathreadela=3p1=3p2=1p3=1WAIT#1:nam=directpathreadela=2p1=4p2=1p3=1WAIT#1:nam=directpathreadela=2p1=201p2=1p3=1WAIT#1:nam=dbfilesequentialreadela=88p1=1p2=417p3=1WAIT#1:nam=dbfilesequentialreadela=91p1=1p2=377p3=1=PARSINGINCURSOR#2len=188dep=1uid=0oct=1lid=0tim=1149404325230977hv=0ad=b700de24createtablebootstrap$(line#numbernotnull,obj#numbernotnull,sql_textvarchar2(4000)notnull)storage(initial50Kobjno56extents(file1block377)ENDOFSTMTPARSE#2:c=0,e=1173,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1149404325230943BINDS#2:EXEC#2:c=0,e=473,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1149404325231638,Subjects,SIDEA数据库的引导数据库的跟踪与诊断数据库的初始化SIDEBOracle的Grid1.0,TableExtentsIdentified,I/OsIssued,ExadataSmartScanProcessing,Onlytherelevantcolumnscustomer_nameandrequiredrowswhereamount200arearereturnedtohostsCPUconsumedbypredicateevaluationisoffloadedMovingscanprocessingoffthedatabasehostfreeshostCPUcyclesandeliminatesmassiveamountsofunproductivemessagingReturnstheneedle,nottheentirehaystack,2MBofdatareturnedtoserver,RowsReturned,SmartScanConstructedAndSentToCells,SmartScanidentifiesrowsandcolumnswithinterabytetablethatmatchrequest,ConsolidatedResultSetBuiltFromAllCells,SELECTcustomer_nameFROMcallsWHEREamount200;,Oracle11g:ResultCache,ResultCache又可以分为ServerResultCacheClientResultCache,SharedPool,DataDictionaryCache,LibraryCache,ServerResultCache,SQLselect/*+result_cache*/count(*)fromeygle;COUNT(*)-15993Statistics-0recursivecalls0dbblockgets0consistentgets0physicalreads0redosize420bytessentviaSQL*Nettoclient416bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)1rowsprocessed,ExadataPerformanceScales,ExadatadeliversbrawnyhardwareforusebyOraclesbrainysoftwarePerformancescaleswithsizeResultMorebusinessinsightBetterdecisionsImprovedcompetitiveness,TableScanTime,TableSize,1TB,10TB,100TB,1Hour,10Hour,5Hour,TypicalWarehouse,Exadata,ExadataBenefits,ExtremePerformance10Xto100XspeedupfordatawarehousingMorepipestodataMassivelyparallelarchitectureWiderpipestodata5XfasterthanconventionalstorageShiplessdatathroughthepipesProcessdatainstorageUnlimitedScalabilityLinearScalingofDataBandwidthTransaction/JoblevelQualityofServiceMissionCriticalAvailabilityandProtectionDisasterrecovery,backup,point-in-timerecovery,datavalidation,encryption,CloudComputingandOracle,CloudComputingtoday:DevelopersaretheprimaryusersInfrastructurenotenterprisereadyNon-missioncriticalsystemsOraclesgoalistomak