《深入解析Oracle》由会员分享，可在线阅读，更多相关《深入解析Oracle（34页珍藏版）》请在金锄头文库上搜索。
1、 2007-2009 E All rights reserved.1 2007-2009 E All rights reserved.2深入解析Oracle数据库的初始化、Grid以及云计算盖国强 (eygle)Mobile:MSN: Site : Mail: 2007-2009 E All rights reserved.3Who am IoITPUB论坛超级版主oOracleACEDirectoro独立咨询顾问、技术支持顾问o个人站点:20042005200620072008 2007-2009 E All rights reserved.4SubjectsSIDEA数据库的引导数据库的跟
2、踪与诊断数据库的初始化SIDEBOracle的Grid1.0&Grid2.0ExadataOracle的云计算 2007-2009 E All rights reserved.5SQL startup nomountORACLE instance started.Total System Global Area 80811208 bytesFixed Size 451784 bytesVariable Size 37748736 bytesDatabase Buffers 41943040 bytesRedo Buffers 667648 bytes数据库的启动过程-NomountNomoun
3、t过程SQLstartupnomount;ORA-00600:internalerrorcode,arguments:OSDEP_INTERNAL,ORA-27302:failureoccurredat:skgpwreset1ORA-27303:additionalinformation:invalidsharedctxORA-27146:post/waitinitializationfailedORA-27300:OSsystemdependentoperation:semgetfailedwithstatus:28ORA-27301:OSfailuremessage:Nospaceleft
4、ondeviceORA-27302:failureoccurredat:sskgpsemsperMonDec314:24:302007Errors in :ORA-07445: exception encountered: core dump PSP0startedwithpid=3,OSid=422106MMANstartedwithpid=4,OSid=303332DBW0startedwithpid=5,OSid=299324。MonDec314:24:332007PSP0: terminating instance due to error 472Instance terminated
5、 by PSP0, pid = 422106instfix -i|grep MLAllfor5.3.0.0_AIX_MLwerefound.Allfor5300-01_AIX_MLwerefound.Allfor5300-02_AIX_MLwerefound.Allfor5300-03_AIX_MLwerefound.Allfor5300-04_AIX_MLwerefound.Allfor5300-05_AIX_MLwerefound.Not all for 5300-06_AIX_ML were found. 2007-2009 E All rights reserved.6数据库的启动过程
6、-Mountmount过程ORA-00202:controlfile:/opt/oracle/oradata/conner/control01.ctlORA-27037:unabletoobtainLinuxError:2:NosuchdirectoryAdditionalinformation:3*.control_files=/opt/oracle/oradata/conner/control01.ctl,/opt/oracle/oradata/conner/control02.ctl,/opt/oracle/oradata/conner/control03.ctlThuJun0517:5
7、7:102008ALTERDATABASEMOUNTErrorsin(incident=36154):ORA-00600:internalerrorcode,arguments:kccpb_sanity_check_2,500625,499958,0x000000000,Incident details in:/databases/app/oracle/diag/rdbms/ora_test/ora_test/incident/incdir_36154/ora_test_ora_3479_i36154.trcORA-600signalledduring:ALTERDATABASEMOUNTOR
8、A-600kccpb_sanity_check_2isreportedwhentheSCNinthecontrolislowerthantheSCNinacontrol.Theheadershouldalwaysbeequelorlargerthanthecontrol.ArgaSCN,SystemChangeNumber,incontrolblockheader.ArgbSCN,SystemChangeNumber,incontrol.Argb 2007-2009 E All rights reserved.7数据库的启动过程-Openopen过程altersessionseteventsi
9、mmediatetracenamecontrolflevel8;*.control_files=/opt/oracle/oradata/conner/control01.ctl,/opt/oracle/oradata/conner/control02.ctl,/opt/oracle/oradata/conner/control03.ctlunrecoverablescn:0x0000.0000000004/23/200401:20:52Checkpoint cnt:1569 scn: 0x0000.01481939 06/22/2004 19:02:22Stop scn: 0xffff.fff
10、fffff 06/22/2004 18:58:05CreationCheckpointedatscn:0x0000.000000ae07/16/200303:40:10SQLaltersessionsetevents=10046tracenamecontextforever,level12;Sessionaltered.SQLalterdatabasemount;Databasealtered.SQLalterdatabaseopen;Databasealtered.WAIT#1:nam=directpathreadela=43p1=1p2=1p3=1WAIT#1:nam=directpath
11、readela=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=dbreadela=88p1=1p2=417p3=1WAIT#1:nam=dbreadela=91p1=1p2=377p3=1=PARSINGINCURSOR#2len=188dep=1uid=0oct=1lid=0tim=11494hv=0ad=b700de24create t
12、able bootstrap$ ( line# number not null, obj# number not null, sql_text varchar2(4000) not null) storage (initial 50K objno 56 extents (file 1 block 377)ENDOFSTMTPARSE#2:c=0,e=1173,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=11494BINDS#2:EXEC#2:c=0,e=473,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=11494 2007-
13、2009 E All rights reserved.8控制文件及数据文件信息分析控制文件的内容 分析所有数据文件头获得地址信息 altersessionseteventsimmediatetracenamelevel10altersessionseteventsimmediatetracenameCONTROLFlevel10Tablespace#0-SYSTEMrel_fn:1Creationatscn:0x0000.0000000704/24/200611:34:39Backuptakenatscn:0x0004.6c2d657e02/12/200715:54:52thread:1res
14、etlogscount:0x24dc1f7dscn:0x0004.6c432ec0recoveredat04/07/200721:04:11status:0x4root dba:0x004001a1 chkptcnt:6933ctlcnt:6932SQLselectgetbfno(0x004001a1)fromdual;GETBFNO(0X004001A1)-datafile#is:1datablockis:417 2007-2009 E All rights reserved.9Oracle9i独一无二的Cache对象SQLselectsegment_name,segment_type,he
15、ader_2fromdba_segmentswheresegment_type=CACHE;SEGMENT_NAMESEGMENT_TYPEHEADER_-1.417CACHE1417Startdumpdatablockstsn:0file#:1minblk417maxblk419buffertsn:0rdba:0x004001a1(1/417)CompatibilityentryforBOOTSTRP:Size:24Release0x134217728By0x153092096Dumpofmemoryfrom0x0AA84E4Cto0x0AA84E50AA84E4000400179 y.SQ
16、Lselectgetbfno(00400179)fromdual;GETBFNO(00400179)-datafile#is:1datablockis:377SQLselectsegment_namefromdba_extents2whereblock_idbetween377andblocks+377-1;SEGMENT_NAME-BOOTSTRAP$ 2007-2009 E All rights reserved.10深入浅出探索数据库启动WAIT#1:nam=directpathreadela=43p1=1p2=1p3=1WAIT#1:nam=directpathreadela=4p1=
17、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=dbreadela=88p1=1p2=417p3=1WAIT#1:nam=dbreadela=91p1=1p2=377p3=1=PARSINGINCURSOR#2len=188dep=1uid=0oct=1lid=0tim=11494hv=0ad=b700de24create table bootstr
18、ap$ ( line# number not null, obj# number not null, sql_text varchar2(4000) not null) storage (initial 50K objno 56 extents (file 1 block 377)ENDOFSTMTPARSE#2:c=0,e=1173,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=11494BINDS#2:EXEC#2:c=0,e=473,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=11494 2007-2009 E All r
19、ights reserved.11SubjectsSIDEA数据库的引导数据库的跟踪与诊断数据库的初始化SIDEBOracle的Grid1.0&Grid2.0Oracle的云计算 2007-2009 E All rights reserved.12WhatsOracleGridFirstintroducedOracleDatabase9i-OracleDatabase10gOracleGridKeywords 2007-2009 E All rights reserved.13OracleGridProducts 2007-2009 E All rights reserved.14Evolut
20、ionoftheDataCenterValueTimeBigMonolithicserversHAClustersScalableandAvailableClustersOracleGridASDBStorage14 2007-2009 E All rights reserved.15Grid1.0PoolingofresourcesacrossapplicationsFlexibleresourcesharingandmanagementHighestpossiblescalabilityandavailabilityBuiltusinglow-costcomputersandlow-cos
21、tstorageWorksforexistingapplicationswithnochangesFullyintegratedandsimpletomanageHighQualityANDLowCostApplicationServerDatabaseStorage 2007-2009 E All rights reserved.16ManyServices,OneDatabaseDWOLTP 1OLTP 2OLTP 3OLTP 4Node-4Node-3Node-2Node-1Node-6Node-5BatchReporting16 2007-2009 E All rights reser
22、ved.17EvolutionoftheDataCenter:Grid2.0ValueTimeGrid2.0Grid1.0ASDBStorageHAClustersScalableandAvailableClustersBigMonolithicservers17 2007-2009 E All rights reserved.18Grid2.0:DefiningCharacteristicsGrid1.0FlexibleinfrastructureOptimizationwithinatierUnbreakableSpecializeddeploymentsValueforsomePersi
23、stentstoragefordatabasedataGrid2.0DynamicinfrastructureEnd-to-endoptimizationAlwaysonlineStandarddeploymentValueforallPersistentstorageforalldata 2007-2009 E All rights reserved.19Oracleuserexperienceinsight 2007-2009 E All rights reserved.20Grid2.0+ExadataCurrentwarehousedeploymentsoftenhavebottlen
24、eckslimitingthemovementofdatafromdiskstoserversStorageArrayinternalbottlenecksonprocessorsandFibreChannelLoopsLimitedFibreChannelhostbusadaptersinserversUnderconfiguredandcomplexSANsPipesbetweendisksandserversare10xto100xtooslowfordatasize 2007-2009 E All rights reserved.21SolutionsToDataBandwidthBo
25、ttleneckAddmorepipesMassivelyparallelarchitectureMakethepipeswider5XfasterthanconventionalstorageShiplessdatathroughthepipesProcessdatainstorage 2007-2009 E All rights reserved.22OptimizedStorageProductfortheOracleDatabaseExtremeI/OandSQLProcessingperformancefordatawarehousingCombinationofhardwarean
26、dsoftwareOracleExadataStorageServerHardwarebySoftwareby 2007-2009 E All rights reserved.23HPOracleDatabaseMachinePre-ConfiguredHighPerformanceDataWarehouse8DL360OracleDatabaseservers2quad-coreIntelXeon,32GBRAMOracleEnterpriseLinuxOracleRAC14ExadataStorageCells(SASorSATA)Upto14TBuncompresseduserdatao
27、nSASUpto46TBuncompresseduserdataonSATA4InfiniBandswitches1GigabitEthernetswitchKeyboard,Video,Mouse(KVM)hardwareHardwareWarranty3YRParts/3YRLabor/3YROn-site24X7,4HourresponsetimeAddmoreracksforunlimitedscalability 2007-2009 E All rights reserved.24ExadataArchitectureDBServerDBInstanceDBRMASMDBServer
28、DBInstanceDBRMASMDBServerDBInstanceDBRMASMOELCELLSRVMSRSIORMExadataCelliDB Protocol over InfiniBand with Path FailoverInfiniBandSwitch/NetworkCellControlCLIEnterpriseManagerOELCELLSRVMSRSIORMExadataCellOELCELLSRVMSRSIORMExadataCell24 2007-2009 E All rights reserved.25TraditionalScanProcessingWithtra
29、ditionalstorage,alldatabaseintelligenceresidesinthedatabasehostsVerylargepercentageofdatareturnedfromstorageisdiscardedbydatabaseserversDiscardeddataconsumesvaluableresources,andimpactstheperformanceofotherworkloadsI/OsExecuted:1terabyteofdatareturnedtohostsDBHostreducesterabyteofdatato1000customern
30、amesthatarereturnedtoclientRowsReturnedSELECTcustomer_nameFROMcallsWHEREamount200;TableExtentsIdentifiedI/OsIssued 2007-2009 E All rights reserved.26ExadataSmartScanProcessingOnlytherelevantcolumnscustomer_nameandrequiredrowswhereamount200arearereturnedtohostsCPUconsumedbypredicateevaluationisoffloa
31、dedMovingscanprocessingoffthedatabasehostfreeshostCPUcyclesandeliminatesmassiveamountsofunproductivemessagingReturnstheneedle,nottheentirehaystack2MBofdatareturnedtoserverRowsReturnedSmartScanConstructedAndSentToCellsSmartScanidentifiesrowsandcolumnswithinterabytetablethatmatchrequestConsolidatedRes
32、ultSetBuiltFromAllCellsSELECTcustomer_nameFROMcallsWHEREamount200; 2007-2009 E All rights reserved.27Oracle11g:ResultCacheResultCache又可以分为ServerResultCacheClientResultCacheShared PoolData DictionaryCacheLibraryCacheServer Result CacheSQLselect/*+result_cache*/count(*)fromeygle;COUNT(*)-15993Statisti
33、cs-0recursivecalls0dbblockgets0consistentgets0physicalreads0redosize420bytessentviaSQL*Nettoclient416bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)1rowsprocessed 2007-2009 E All rights reserved.28ExadataPerformanceScalesExadatadeliversbrawnyhardwareforuseb
34、yOraclesbrainysoftwarePerformancescaleswithsizeResultMorebusinessinsightBetterdecisionsImprovedcompetitivenessTableScanTimeTableSize1TB10TB100TB1Hour10Hour5HourTypicalWarehouseExadata 2007-2009 E All rights reserved.29ExadataBenefitsExtremePerformance 10X to 100X10X to 100X speedupfordatawarehousing
35、MorepipestodataMassivelyparallelarchitectureWiderpipestodata5XfasterthanconventionalstorageShiplessdatathroughthepipesProcessdatainstorageUnlimitedScalabilityLinearScalingofDataBandwidthTransaction/JoblevelQualityofServiceMissionCriticalAvailabilityandProtectionDisasterrecovery,backup,point-in-timer
36、ecovery,datavalidation,encryption 2007-2009 E All rights reserved.30CloudComputingandOracleCloudComputingtoday:DevelopersaretheprimaryusersInfrastructurenotenterprisereadyNon-missioncriticalsystemsOraclesgoalistomakecloudcomputingrelevanttoenterprisesbyProvidingrightsetofproductsandservicesLeadthein
37、dustryeffortsindevelopingCloudstandards,ensuredatasecurity,etc.Amazonis#1CloudComputingvendor&Oracles1stPartnerEC2 2007-2009 E All rights reserved.31CurrentDatabaseBackupBestPracticeDatabaseFilesRMANBackupLocalDiskBackupRMANTapeInterfaceOffsiteTapeStorageBackuptoLocalDiskMigratebackupsfromdisktotape
38、Movetapestooff-sitelocation(e.g.IronMountain) 2007-2009 E All rights reserved.32NewOracleSecureBackupinterfacetomovedatabasebackupstoCloudWorkswith9iandhigherDBversionsAmazonS3supportedtodayMoreReliablethantapes,FasterRestoresEliminatestapebackupandoffsitetapemanagementoverheadCloudBackup&CloudDatabaseexampleOffsiteBackupsintheCloudDatabaseFilesRMANBackupLocalDiskBackupSecureBackupCloudModuleAmazonS3EncryptionRMANTapeInterfaceCompression 2007-2009 E All rights reserved.33SummaryFront-EndServerExtendRACBack-EndStorageExtendExadataConceptorFactGridCloud? 2007-2009 E All rights reserved.34
