oracleocp043试题分类汇总.docx
《oracleocp043试题分类汇总.docx》由会员分享，可在线阅读，更多相关《oracleocp043试题分类汇总.docx（101页珍藏版）》请在冰豆网上搜索。
oracleocp043试题分类汇总
性能调优
1.Youobservethatadatabaseperformancehasdegradedover
aperiodoftime.Whileinvestigatingthereason,youfindthesizeof
thedatabasebuffercacheisnotlargeenoughtocachealltheneededdata
blocks.Whichadvisorycomponentwoldyoureferto,inordertodetermine
thatrequiredsizeofthedatabasebuffercache?
A.MemoryAdvisor
B.SegmentAdvisor
C.SQLTuningAdvisor
D.SQLAccessAdvisor
E.AutomaticDatabaseDiagnosticMonitor（ADDM）
A
发现数据库运行一段时间后性能下降了，调查发现原因是因为buffercache不能装下所有需要的数据块，用个工具确定数据库所需要的buffercache的大小。
2.Exhibit
--显示所有日志在同一个目录下
Inyourproductiondatabase,thetotalwaitsandthetimewaitedforlog
fileparallelwritearesignificantlyhigh.Whileinvestigatingthe
reason,youfindthattherearethreeredologgroupswithtwomembers
ineachgroup,andallredologmembersareplacesonasinglephysical
disk.Whatactionwouldyoutaketominimizethewaits?
A.Startthelogwriterslaveprocesses
B.Increasethenumberofredologfiles
C.Increasethesizeoftheredologbuffer
D.Placetheredologfilesonthedifferentdisks.
E.Increasethenumberoflogwriterprocesses.
Answer:
D
等待并行写日志的时间明显很高。
调查发现三个日志文件组，每组由两个成员，都在同一个物理磁盘上。
通过什么方法解决问题？
--主要是不懂题目意思会出错。
24.YouaredesigninganapplicationforCertkiller.comandyouhave
beenaskedtodesignadatabasetabletofacilitatemonthlybillgen
eration.Thebillwouldincludedetailsofcustomercalls,listedin
chronologicalorder.Whichmethodwouldyoufollowtoachievethisob
jectivewithoutincreasingtheoverheadofsortingtherows?
A.createahashclustertostorethedata
B.createanindexclustertostorethedata
C.createapartitionedtabletostorethedata
D.createasortedhashclustertostorethedata
E.createaheaptablewithrowidtostorethedata
Answer:
D
27.Inwhichscenarioswouldyourebuildanindex?
（Chooseallthata
pply.）
A.whenyouneedtodisabletheindexusage
B.whenyouneedtochangestorageoptions
C.whenyouneedtoenableindexmonitoring
D.whenyouneedtomovetheindextoanothertablespace
Answer:
B,D
35.Whiledesigningyourdatabase,youhavecreatedtheEMPLOYEEStab
leasanindex-organized（IOT）.Youwanttocreateabitmapindexon
theJOD_IDcolumntomakequeriesfaster.Whichtaskmusthavebeenc
ompletedsothatyouareabletocreatethebitmapindex?
A.Aprimarykeymusthavebeencreated.
B.Amappingtablemusthavebeencreated.
C.Anoverflowtablespacemusthavebeenspecified.
D.ThePCTTHRESHOLDoptionmusthavebeenspecified.
Answer:
B
51.YouwanttousetheSQLTuningAdvisortogeneraterecommendation
sforbadlywrittenSQLstatementsinyourdevelopmentenvironment.W
hichthreesourcescanyouselectfortheadvisortoanalyze?
（Choose
three.）
A.TopSQL
B.snapshots
C.SQLTuningsets
D.indexaccesspath
E.optimizerstatistics
F.materializedviewlogs
Answer:
A,B,C
54.Youfindthattheexecutiontimeofreportsinyourdatawarehouse
applicationissignificantlyhigh.Yoususpectthelackofindexesto
bethereasonforthedegradationinperformance.Whichadvisorycomp
onentwouldyoureferto,inordertodeterminetheappropriateindex
es?
A.MemoryAdvisor
B.SegmentAdvisor
C.SQLAccessAdvisor
D.AutomaticWorkloadRepository（AWR）
E.AutomaticDatabaseDiagnosticMonitor（ADDM）
Answer:
C
你发现你的数据仓库中的执行报告时间非常慢，你怀疑是缺少相应的索引，
可以使用SQLAccessAdvisor（SQL访问指导）
SQL访问指导：
处理方案问题并确定最佳数据访问路径（如索引和实体化视
图）。
实体化视图=物化试图
物化视图是包括一个查询结果的数据库对像，它是远程数据的的本地副本，
或者用来生成基于数据表求和的汇总表。
物化视图存储基于远程表的数据，也可
以称为快照。
68.Younoticedthattheindextablespaceinyourdatabaserequiresa
recovery.However,insteadofperformingamediarecovery,youdecide
dtore-createtheindexesinanewtablespace.Whichtwooptionswou
ldyouusetoreducethetimeittakestore-createtheindexes?
（Cho
osetwo.）
A.ONLINE
B.REVERSE
C.PARALLEL
D.COMPRESS
E.NOLOGGING
F.COMPUTESTATISTICS
Answer:
C,E
关于控制文件恢复
3.Oneofthetablespaceisread-onlyinyourdatabase.Thelossofall
controlfileforcedyoutorecreatethecontrolfile.Whichoperationdo
youneedtoperformafterre-creatingthecontrolfileandopeningthe
database?
A.Dropandre-createtheread-onlytablespaces
B.Renametheread-onlydatafiletotheircorrectfilenames.
C.Changethetablespacestatusfromread/writetoread-only.
D.Re-createtheread-onlytablespacebecauseitisautomatically
removed.
Answer:
B
本来就是readonly，控制文件丢失，重建了控制文件，重新打开数据库的时候需要做什么？
重命名那个表空间对应的文件就可以了。
这里是将readonly,如果是普通情况？
道理在什么地方？
日志
7.Inyourdatabase,onlineredologfilesaremultiplexedandoneofthe
membersinagroupislostduetomediafailure?
Howwouldyourecoverthelostredologmember?
A.importthedatabasefromthelastexport
B.restoreallthemembersinthegroupfromthelastbackup
C.dropthelostmemberfromthedatabaseandthenaddanewmembertothe
group
D.restoreallthedatabasefilesfromthebackupandthenperforma
completerecovery
E.restoreallthedatabasefilesfromthebackupandthenperforman
incompleterecovery
Answer:
C
Redogroup里如果有多个成员，那么他们是冗余的。
每个成员里的内容都
一样，所以删除再添加一个即可。
19.YouareusinganAutomaticStorageManagement（ASM）instancetom
anagethefilesofyourproductiondatabase.Youhavetwodiskgroups
DG1andDG2withonedeviceeach.Intheparameterfileoftheprodu
ctiondatabase,thefollowingparametershavebeenspecified:
DB_CREATE_ONLINE_LOG_DEST_1='+dg1'
DB_CREATE_ONLINE_LOG_DEST_2='+dg2'
Whatwouldbetheimpactofthissetting?
A.Whenanewloggroupisadded,itwouldhaveonememberineachdi
skgroup.
B.Whenanewloggroupisadded,itwouldhavetwomembersineachd
iskgroup.
C.Whenanewtablespaceisadded,itwouldhaveonedatafileineac
hdiskgroup.
D.Whenanewlogfileisadded,itwouldhaveonememberspreadacro
ssthediskgroups.
Answer:
A
DB_CREATE_ONLINE_LOG_DEST_1='+dg1'
DB_CREATE_ONLINE_LOG_DEST_2='+dg2'
当一个新的日志组加入了以后，会在每个磁盘组中有个成员。
Editor’snotes:
DB_CREATE_ONLINE_LOG_DEST_n（wheren=1,2,3,...5）specifiesthedefault
locationforOracle-managedcontrolfilesandonlineredologs.Ifmorethanone
DB_CREATE_ONLINE_LOG_DEST_nparameterisspecified,thenthecontrolfileoronline
redologismultiplexedacrossthelocationsoftheother
DB_CREATE_ONLINE_LOG_DEST_nparameters.Onememberofeachonlineredologis
createdineachlocation,andonecontrolfileiscreatedineachlocation.
Specifyingatleasttwoparametersprovidesgreaterfaulttoleranceforthe
controlfilesandonlineredologsifoneofthelocationsshouldfail.
Ifafilesystemdirectoryisspecifiedasthedefaultlocation,thenthe
directorymustalreadyexist;Oracledoesnotcreateit.Thedirectorymusthave
appropriatepermissionsthatallowOracletocreatefilesinit.Oraclegenerates
uniquenamesforthefiles,andafilethuscreatedisanOracle-managedfile.
db_recovery_file_dest参数启用redo、control、rmanbackups、archivedlogs、flashback
logs文件的omf管理,类似的db_create_online_log_dest_n参数启用redo、control的omf
管理,从中我们也可以看出redo、control文件的重要性！
！
这三个参数对redo、control
文件位置的影响：
1.位置优先级：
【control_files初始化参数】>db_create_online_log_dest_n>
db_recovery_file_dest>=db_create_file_dest
此处的等于号比较有意思：
如果没有db_create_online_log_dest_n,而存在
db_create_file_dest时，db_recovery_file_dest就相当
db_create_online_log_dest_2，db_create_file_dest则相当于
db_create_online_log_dest_1
2.redo文件的多路复用
通常应该通过指定db_create_online_log_dest_n来达到该目的！
3.如果三个参数指定相同的值会怎样？
自己猜吧：
）
4.omf文件的命名规则
<_dest_参数路径>///o1_mf_%t_%u_.dbf
（做题目的时候容易想的跟选的不一致）
46.Aredologfileiscorruptedwhilethedatabaseisopen;asacon
sequence,databaseoperationsarestoppedbecausearchivingcannotco
ntinue.Whatwouldyoudotosolvetheproblem?
A.cleartheredologgroup
B.performredologfileimport
C.performanincompleterecovery
D.performaredologrecoveryusingRecoveryManager（RMAN）
E.shutdownthedatabaseandopenthedatabaseintheNOARCHIVELOGm
ode
Answer:
A
数据库在打开的状态下日志坏掉，然后数据库自动关了，那个坏掉的日志没
有归档。
只需要使用命令clearredologgroup就行了，同时由于缺少了一部
分archivelog需要整个数据库重新备份一下
52.Thelossofwhichtwotypesoffilesmayrequirearecoverywith
theRESETLOGSoption?
（Choosetwo.）
A.controlfiles
B.passwordfile
C.archivedlogfiles
D.system-criticaldatafilesforwhichalltheredoentriesare
present
E.non-system-criticaldatafilesforwhichalltheredoentries
arepresent
Answer:
A,C
几点总结：
（eygle:
）
1:
只要拥有当前日志文件,就可以执行完全恢复,是否需要resetlogs方式打开,
取决是否使用备份的控制文件。
2:
如果日志文件没有损失,可以通过重建控制文件的方法来完成完全恢复,这种
情况下,不再需要通过resetlogs方式打开数据库。
3:
如果恢复执行到某一个日志文件停止,数据库要以读写方式打开,则必须执行
resetlgos打开。
（warehous:
）
selectfile#,checkpoint_change#fromv$datafile;（来自控制文件）
selectfile#,checkpoint_change#fromv$datafile_header;（来自数据文件）
当两个结果不相等,需openresetlogs,即截断多余的scn
103.YouareusingOracleDatabase10g.ThelogLOG_ARCHIVE_FORMATpa
rameterissetto'LOG%t_%_s_%r.dbf'.
Whyis%rusedinthefilenameformat?
A.Touniquelyidentifythearchivedlogfileswiththerestoreopera
tion.
B.Touniquelyidentifythearchivedlogfileswiththeredologgrou
pnumber.
C.Touniquelyidentifythearchivedlogfilesforeachincarnationo
fthedatabase.
D.Touniquelyidentifythearchivedlogfileswiththenumberofrec
overyoperationsperformed.
Answer:
C
112.Becauseofhardwarefailure,youdecidedtodroparedologmemb
erfromthedatabase.
Whichconditionshouldbemettodroparedologfile?
A.Theredologfileshouldbelongtoanactivegroup.
B.Theredologfileshouldbelongtoaninactivegroup.
C.Theredologfileshouldbelongtothecurrentgroup.
D.Theredologfil
升级会员 