Oracle 10g OCP 043解析(185题)

QQ:54110058清江石声明：对于答案的相关的说明，是个人对Oracle的理解和收集相关资料整理,主要参考了tianlesoftware兄资料,供大家参考学习。2011-08-02119跟43(已经删除)相同1.Youobservethatadatabaseperformancehasdegradedoveraperiodoftime.Whileinvestigatingthereason,youfindthesizeofthedatabasebuffercacheisnotlargeenoughtocachealltheneededdatablocks.Whichadvisorycomponentwoldyoureferto,inordertodeterminethatrequiredsizeofthedatabasebuffercache?A.MemoryAdvisorB.SegmentAdvisorC.SQLTuningAdvisorD.SQLAccessAdvisorE.AutomaticDatabaseDiagnosticMonitor(ADDM)Answer:A你发现你的数据高速缓存区（DatabaseBufferCache）不够用了，可以使用MemoryAdvisor来确定其大小,自动数据库诊断监视器(ADDM)：执行自上而下的实例分析，确定问题和潜在的原因，并提供修复问题的建议案。ADDM可潜在地调用其他指导。SGA指导（MemoryAdvisor）：根据系统全局区(SGA)中各个组件的访问模式，负责优化和建议SGA的大小。AdequatephysicalmemoryhasasignificantimpactontheperformanceofyourOracleDatabase.Withitsautomaticmemorymanagementcapabilities,OracleDatabasecanautomaticallyadjustthememorydistributionamongthevariousSGAandPGAcomponentsforoptimalperformance.Theseadjustmentsaremadewithintheboundariesofthetotalamountofmemorythatyouallocatetothedatabase.ADDMperiodicallyevaluatestheperformanceofyourdatabasetodetermineperformanceproblems.IfADDMfindsthatthecurrentamountofavailablememoryisinadequateandadverselyaffectingperformance,thenitcanrecommendthatyouincreasememoryallocations.YoucanselectnewmemoryallocationsusingtheMemoryAdvisors.Additionally,youcanusetheMemoryAdvisorstoperformwhat-ifanalysisonthefollowing:ThedatabaseperformancebenefitsofaddingphysicalmemorytoyourdatabaseThedatabaseperformanceimpactofreducingthephysicalmemoryavailabletoyourdatabaseWiththeMemoryAdvisors,youcanobtainmemorysizingadviceasfollows:Ifautomaticmemorymanagementisenabled,thenyoucangetadviceforsettingthetargetamountofmemorytoallocatetotheOracleinstance.Ifautomaticmemorymanagementisdisabledandautomaticsharedmemorymanagementisenabled,thenyoucangetadviceonconfiguringthetargetsizesoftheSGAandinstancePGA.Ifonlymanualsharedmemorymanagementisenabled,thenyoucangetadviceonsizingthesharedpool,buffercache,andinstancePGA.:D把日志建在不同的磁盘上来分散i/o压力,提高写入速度3.Oneofthetablespaceisread-onlyinyourdatabase.Thelossofallcontrolfileforcedyoutorecreatethecontrolfile.Whichoperationdoyouneedtoperformafterre-creatingthecontrolfileandopeningthedatabase?A.Dropandre-createtheread-onlytablespacesB.Renametheread-onlydatafiletotheircorrectfilenames.C.Changethetablespacestatusfromread/writetoread-only.D.Re-createtheread-onlytablespacebecauseitisautomaticallyremoved.Answer:B因为你的tablespace是read-only的，所以你只需要用rename来update一下controlfile就可以了.alterdatabaserenamefile'D:\ORACLE\ORA92\DATABASE\MISSING00005'to'E:\DBDATA\ORADATA\RMIS\EXAMPLE01.DBF';4.Youhavesetsomeoftheinitializationparametersas:DB_BLOCK_SIZE=8KBSGA_MAX_SIZE=2GBSGA_TARGET=0SHARED_POOL_SIZE=120MBDB_CHCHE_SIZE=896MBSTREAM_POOL_SIZE=0LARGE_POOL_SIZE=110MBWhichtwostatementsarecorrect?(Choosetwo).A.YoucannotsetavaluefortheDB_8K_CHCHE_SIZEparameter.B.Ifyouincreasethesizeofthelargepoolto120MB,thenthememoryallocatedtothesharedpollwillbereducedto110MB.C.IfthevalueforSGA_TARGETischangedto1GBandSHARED_POOL_SIZEis120MB,thenmemorycannotbetakenfromthesharedpool,evenifthesharedpoolhasfreespaceavailable.D.Ifanapplicationattemptstoallocatemorethan120MBfromthesharedpoolandfreespaceisavailableinthebufferpool,thenthefreespacefromthebufferpoolisallocatedtothesharedpool.Answer:AC已经设置了DB_BLOCK_SIZE=8k就不能设置DB_8K_CACHE_SIZE了如果指定了sharedpoolsize那么就不能再缩小了，只能增大不能使用DB_nK_CACHE_SIZE参数来调整标准块的大小.例如,假如DB_BLOCK_SIZE设置为2K,设置DB_2K_CACHE_SIZE.等于2K是无效的.标准缓存的块大小总是由DB_CACHE_SIZE决定的.Oracle10g中，与内存相关的参数可以归为两类：q自动调优的SGA参数：目前这些参数包括DB_CACHE_SIZE、SHARED_POOL_SIZE、LARGE_POOL_SIZE和JAVA_POOL_SIZE。(注意LOG_BUFFER不在自动之列)q手动SGA参数：这些参数包括LOG_BUFFER、STREAMS_POOL、DB_NK_CACHE_SIZE、DB_KEEP_CACHE_SIZE和DB_RECYCLE_CACHE_SIZE。在Oracle10g中，任何时候你都能查询V$SGAINFO，来查看SGA的哪些组件的大小可以调整。注意要使用自动SGA内存管理，参数STATISTICS_LEVEL必须设置为TYPICAL或ALL。如果不支持统计集合，数据库就没有必要的历史信息来确定大小。采用自动SGA内存管理时，确定自动调整组件大小的主要参数是SGA_TARGET，这个参数可以在数据库启动并运行时动态调整，昀大可以达到SGA_MAX_SIZE参数设置的值（默认等于SGA_TARGET，所以如果想增加SGA_TARGET，就必须在启动数据库实例之前先把SGA_MAX_SIZE设置得大一些）。数据库会使用SGA_TARGET值，再减去其他手动设置组件的大小（如DB_KEEP_CACHE_SIZE、DB_RECYCLE_CACHE_SIZE等），并使用计算得到的内存量来设置默认缓冲区池、共享池、大池和Java池的大小。在运行时，实例会根据需要动态地对这4个内存区分配和撤销内存。如果共享池内存用光了，实例不会向用户返回一个ORA-04031“UnabletoallocateNbytesofsharedmemory”（无法分配N字节的共享内存）错误，而是会把缓冲区缓存缩小几MB（一个颗粒的大小），再相应地增加共享池的大小。5.Youareperformingablockmediarecoveryonthetools01.dbfdatafileintheSALESdatabaseusingRMAN.Whichtwostatementsarecorrectinthisscenario?(Choosetwo.)A.YoumustensurethattheSALESdatabaseismountedoropen.B.Youmustrestoreabackupco