Oracle 10g OCP 042 题库 1-30 题 共168题
声明：对于答案的相关的说明，是个人对Oracle的理解。
1.Becauseofapoweroutage,instancefailurehasoccurred.Fromwhatpointintheredologdoesrecoverybeginandwheredoesitend?
A.Currentredologandinactiveredolog
B.Checkpointpositiontoendofredolog
C.Beginningofredologtoendofredolog
D.Allredologsbeforethepointoflastcommit
E.Beginningofredologtocheckpointposition.
答案B.Checkpoint之前的数据已经写入到数据文件。所以用restore就可以恢复。而checkpoint之后的数据没有写入到数据文件，所以需要进行recovery。Recovery时，对于已经commit的数据，前滚写入到数据文件。没有commit的数据，进行回滚。
2.Whichtwooperationscanbeflashedbackusingtheflashbacktechnology?(choosetwo)
A.Dropusersmith;
B.Droptableemployees;
C.Droptablespaceusers;
D.Altertablesales_repdroppartitionp1;
E.Altertableemployeesdropcolumndesig_id;
答案：AB。
OracleFlashback技术总结
http://blog.****.net/tianlesoftware/archive/2009/10/16/4677378.aspx
3.Youhavetoshutdownthedatabaseinstancewiththeabortoptionbecauseofahardwarefailure.Whichstatementistrueaboutthesubsequentopeningofthedatabase?
A.Thedatabasewouldopennormally.
B.Thedatabasewouldnotopen,anditwouldstopatmountstage.
C.Thedatabasewouldopenalterautomaticallyperforminginstancerecovery.
D.Thedatabasewouldnotopen,andhavetoperformdatabaserecoverytoopenit.
答案：C.
如果实例异常关闭（宕机，shutdownabort），并且数据文件，控制文件，联机日志都没有丢失。在下次启动时，要利用联机日志的内容进行恢复，这种恢复就是实例恢复（InstanceRecovery）。
InstanceRecovery主要包括3个阶段：
1）根据联机日志内容进行Rollover。
2）打开数据库，提供服务
3）SMON或者用户进程进行Rollback。
具体参考Oracle备份与恢复概述中的3.2节恢复种类
http://blog.****.net/tianlesoftware/archive/2010/04/16/5490733.aspx
4.Youbackedupthecontrolfiletotrace.Whichstatementistrueaboutthetracefilegenerated?
A.Thetracefileisinbinaryformat.
B.ThetracefilehasaSQLscriptstore-createthecontrolfile.
C.Thetracefileisabackupsetcreatedduringthebackupofthecontrolfile.
D.Thetracefilecontainstheinstructionstomanuallyre-createthecontrolfile.
E.Thetracefileisanimagecopyofthecontrolfilecreatedduringthebackupofthethecontrolfile.
答案：B
联机文档：
AnalternativetotheCREATECONTROLFILEstatementisALTERDATABASEBACKUPCONTROLFILETOTRACE,whichgeneratesaSQLscriptinthetracefiletore-createthecontrolfile
http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_5003.htm
5.WhilerunningtheOracleUniversalInstalleronaUnixplatformtoinstallOracleDatabase10gsoftware,youarepromptedtorunorainstRoot.sh.Whatdoesthisscriptsaccomplish?
A.Itcreatesthepointerfile.
B.Itcreatesthebasedirectory.
C.Itcreatestheinventorypointerfile.
D.ItcreatestheOracleuserforinstallation.
E.ItmodifiestheUnixkernelparmeterstomatchOracle'srequirement.
答案：C
可以查看$ORACLE_BASE/oraInventory/orainstRoot.sh脚本的内容。该脚本实际上完成了以下工作：
（1）创建softwareinventorylocationpointerfile:/etc/oraInst.loc，内容为
inventory_loc=$ORACLE_BASE/oraInventory
inst_group=oinstall
修改该文件属性:chmod644/etc/oraInst.loc
（2）创建inventorydirectory:$ORACLE_BASE/oraInventory
修改文件属性：chmod-R770$ORACLE_BASE/oraInventory
chgrpoinstall$ORACLE_BASE/oraInventory
oraInventory目录是用来存储oracle安装的所有软件组件的信息的，每个组件可能占用150k的空间.
6.WhilesettingupanOracledatabaseforoneofyourcriticalapplications,youwanttoensurethatthedatabaseisbackedupatregularintervalswithoutyourintervention(介入).Whatshouldyoudotoachievetheobjective?
A.Configurethedatabasetoruninarchivelogmode.
B.ConfiguretheFlashrecoveryareatoenableautomaticdatabasebackup.
C.SchedulethedatabasebackupusingDBMS_JOBpackageaftercreatingthedatabase.
D.Schedulethedatabasebackupusingrecoverymanager(RMAN)commandsaftercreatingthedatabase.
E.SchedulethedatabasebackupusingDatabaseConfigurationAssistant(DBCA)whilecreatingthedatabase.
答案：E.
刚看到这个答案的时候，以为答案有问题。Google一下，真有。我们注意看题目，在安装数据库的时候，确保数据库在没有干预的情况下规则的备份。只有E中的DBCA工具合适了。而且DBCA还真有这个功能(玩了几年Oracle，都没有留意到，杯具中...)
7.TheapplicationdevelopmentteamhasdevelopedPL/SQLproceduresandfunctionsfordifferentpurposesandcallsthemasandwhenrequired.Theloadingofindividualproceduresorfunctionsintomemorydegradesperformancewitheverycall.Also,itcausesasecurityproblemforindividualsubprogramsandlossofprogramunitswhenthewholesystemistransportedintoanewlocation.Whichmethodwouldyourecommendtotheapplicationdeveloperstosolvethisproblem?
A.Avoidingtheuseofcursorsinthesubprograms.
B.Usinganonymous（匿名）PL/SQLblocksinsteadofsubprograms.
C.Referringtoviewsinsteadoftablesinsidethesubprograms.
D.creatingPL/SQLpackagestoincludeinterrelated(相关关联)subprograms.
答案：D.
8.ViewtheExhibit.
YourOracle10gdatabasehas6tablespacesinwhich:
-TEMPisthedefaulttemporarytablespace
-UNDOTBS1isthedefaultundotablespace
-USERSisthedefaultpermanenttablespace
Inthisdatabase,whichthreetablespacescanbemadeoffline?(Choosethree.)
A)TEMP
B)PROD
C)USERS
D)SYSAUX
E)SYSTEM
F)UNDOTBS1
答案:BCD。
可以参考在线文档中OverviewofTablespaces小节的内容。
http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/physical.htm#CNCPT1089
9.ThejuniorDBAinyourorganizationhasaccidentallydeletedthealertlogfile.Whatwillyoudotocreatenewalertlogfile?
A.Createthenewtextfileasalert.log
B.Youhavetorecoverthealertlogfilefromthevalidbackup.
C.ChangethevaluefortheBACKGROUND_DUMP_DESTparameter.
D.Noactionrequired.Thefilewouldbecreatedautomaticallybytheinstance.
答案：D.
10.Whileplanningtomanagemorethanonedatabaseinyoursystem,youperformthefollowingactivities:
（1）organizedifferentcategoriesoffilesintoindependentsubdirectories.
（2）Useconsistentnamingconventionfordatabasefiles.
（3）Separateadministrativeinformationpertainingtoeachdatabase.
Whichoptioncorresponds(一致)tothetypeofactivitiesperformedbyyou?
A.OracleManagedFiles.
B.OracleGridArchitecture.
C.OptimalFlexiblearchitecture.
D.Oracledatabasearchitecture.
答案：C
OptimalFlexibleArchitecture
http://download.oracle.com/docs/cd/B28359_01/install.111/b32002/app_ofa.htm#i633068
11.Datafilesofwhichthreetablespacecanberecoveredbyperforminganopenrecovery?(Choosethree)
A.TEMP
B.UNDO
C.INDEX
D.SYSAUX
E.SYSTEM
答案：ACD
12.Yourtnsnames.orafilehasthefollowingentryfortheservicealiasORCL:
ORCL=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=10.156.24.216)(PORT=1521))
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=orcl.oracle.com)
)
)
TheTNSPINGcommandexecutessuccessfullywhentestedwithORCLbutyouarenotabletoconnecttothe
databaseinstancewiththefollowingcommand:
SQL>CONNECTscott/[email protected]
Whatcouldbethereasonforthis?
A)Thelistenerisnotrunningattheserver.
B)TheTNS_ADMINenvironmentalvariableissettoawrongvalue.
C)Theorcl.oracle.comdatabaseserviceisnotregisteredwiththelistener.
D)TheDEFAULT_DOMAINparameterissettoawrongvalueintheSQLNET.ORAfile.
答案：C.
OracleListener动态注册与静态注册
http://blog.****.net/tianlesoftware/archive/2010/04/30/5543166.aspx
13.ViewtheExhibit,andexaminethealertmessages.
YouaddedspacetotheTESTtablespacetobringthespaceusagebelowthethresholdvalue.
WhichstatementistrueabouttheTablespaceFullalert?
A)Thealertispurgedbecauseitisathresholdalert.
B)Thealertisclearedandtransferredtothealertlogfile.
C)Thealertisautomaticallyclearedandsenttothealerthistory.
D)ThealertappearsinOracleEnterpriseManagerDatabaseControluntilitismanuallycleared.
答案：C
14.Whichisthememoryareathatiscreatedwhenadedicatedserverprocessisstarted,andcontainsdataandcontrolinformationforthatserverprocess?
A.SGA
B.PGS
C.SharedPool
D.StreamsPool
答案：B
Oracle内存架构详解
http://blog.****.net/tianlesoftware/archive/2010/05/16/5594080.aspx
15.WhichtwostatementsaboutRecoveryManager(RMAN)backupsaretrue?(Choosetwo).
A.Onlineredologfilescanbebackedup.
B.Archivedredologfilesarebackedup.
C.Onlyuseddatablockscanbebackedupasbackupsets.
D.Onlyconsistentdatabasebackupscanbeperformed.
E.RMANbackupcanbetakenonlyifthedatabaseisconfiguredinARCHIVELOGmode.
答案：BC
如何搭建RMAN备份平台
http://blog.****.net/tianlesoftware/archive/2010/07/16/5740896.aspx
16.WhichthreepiecesofinformationaretobemanatorilyprovidedwhilecreatinganewlistenerusingEnterpriseManagerdatabaseControl?(Choosethree).
A.Theportusedbythelistener.
B.Theprotocolusedbythelistener.
C.Theservernamewherethelistenerruns.
D.Thelogfileandtracefiledestinationforthelistener.
E.Thedatabaseservicestoberegisteredwiththelistener.
答案：ABC
17.TheSCOTTuserhasanindexontheITEM_DESCcolumnoftheITEMtable.Aspartoftheyear-endingtask,SCOTTupdatestheITEM_DESCcolumnformostoftherowsintheITEMtable.Howdoesthischangetothetableaffecttheindex?
A.Anupdateinaleafrowtakesplace.
B.Theindexbecomesinvalidaftertheupdate.
C.Theleafblockcontainingtherowtobeupdatedismarkedasinvalid.
D.Arowintheleafblockoftheindexforthekeyvalueisdeletedandinserted.
答案：D
18.Inthemiddleofatransaction,ausersessionwasabnormallyterminatedbuttheinstanceisstillupandthedatabaseisopen.Whichtwostatementsaretrueinthescenario（方案）?(Choosetwo).
A.Eventviewergivesmoredetailsonthefailure.
B.Thealertlogfilegivesdetailedinformationaboutthefailure.
C.PMONrollsbackthetransactionandreleasesthelocks.
D.SMONrollsbackthetransactionandreleasesthelocks.
E.Thetransactionisrolledbackupbythenextsessionthatreferstoanyoftheblocksupdatedbythefailedtransaction.
F.Datamodifiedbythetransactionuptothelastcommitbeforetheabnormalterminationisretainedinthedatabase.
答案：CF
19.Theapplicationworkloadonyourdatabaseissamebwtween10a.m.And11a.m.Onweekdays.Suddenlyyouobservepoorperformancebetween10a.m.And11a.m.Inthemiddleoftheweek.Howwouldyouidentifythechangesinconfigurationsettings,workloadprofile,andstatisticstodiagnosethepossiblecausesoftheperformancedegradation?
A.ByusingtheSQLaccessadvisor
B.Byusingtheautomaticworkloadrepositoryreport.
C.Byrunningtheautomaticdatabasediagnosticmonitor(ADDM)
D.Byusingtheautomaticworkloadrepository(AWR)compareperiodreport.
E.Byanalyzingtheoutputofthev$ACTIVE_SESSION_HISTORYview.
答案：D
OracleAWR介绍
http://blog.****.net/tianlesoftware/archive/2009/10/17/4682300.aspx
20.YourdatabaseisinNOARCHIVELOGmode.Afterwhichtwooperationsyoushouldtakethebackupofthecontrolfile?
A.Addinganewusertothedatabase.
B.Droppingauserfromthedatabase.
C.Droppingatablefromthedatabase.
D.Droppingadatafilefromatablespace.
E.Addinganewtablespacetothedatabase.
答案：DE.控制文件里保存了数据文件的信息，所以添加删除都会修改控制文件里的内容，所以需要修改。
Oracle控制文件
http://blog.****.net/tianlesoftware/archive/2009/12/13/4974440.aspx
21.WhichthreestatementsaretrueregardingthelogicalstructureoftheOracledatabase?(Choosethree).
A.Eachsegmentcontainsoneormoreextents.
B.Multipletablespacecansharesingledatafile.
C.AdatablockisthesmallestunitofI/Ofordatafiles.
D.Itispossibletohavetablespaceofdifferentblocksizesinadatabase.
E.EachdatablockinthedatabasealwayscorrespondstooneOSblock.
答案：ACD
表空间(tableSpace)段(segment)盘区(extent)块(block)关系
http://blog.****.net/tianlesoftware/archive/2009/12/13/4962476.aspx
22.Youexecutedthefollowingcommandtostartthedatabase:
SQL>STARTUP
ORACLEinstancestarted.
TotalSystemGlobalArea281018368bytes
FixedSize789000bytes
VariableSize229635576bytes
DatabaseBuffers50331648bytes
RedoBuffers262144bytes
ORA-00205:errorinidentifyingcontrolfile,checkalertlogformoreinfo
Whichviewwouldyouqueryatthisstagetoinvestigatethismissingcontrolfile?
A)V$INSTANCE
B)V$CONTROLFILE
C)DBA_CONTROL_FILES
D)V$DATABASE_PROPERTIES
E)V$CONTROLFILE_RECORD_SECTION
答案:B
23.WhichtwoarevalidlockinglevelsthatareusedbytransactionsinanOracledatabase?(Choosetwo)
A.Rowlevel
B.Blocklevel
C.Objectlevel
D.Schemalevel
E.Databaselevel
答案：AC
在这个地方研究了半天，也不是十分明白。
ORACLE锁机制
http://blog.****.net/tianlesoftware/archive/2009/10/20/4696896.aspx
http://download.oracle.com/docs/cd/E11882_01/server.112/e10713/consist.htm#CNCPT1341
24.Yourdatabaseisstartedbyusingtheserverparameterfile(SPFILE).Youissuedthiscommandtochange
thevalueoftheLOG_BUFFERinitializationparameter:
ALTERSYSTEMSETLOG_BUFFER=24MSCOPE=BOTH;
Whatwouldbetheoutcomeofthiscommand?
A)ThecommandwouldreturnanerrorbecauseLOG_BUFFERisastaticparameter.
B)Theparametervaluewouldbechangedanditwouldcomeintoeffectimmediately.
C)Youneedtorestartthedatabasesothatparameterchangescancomeintoeffect.
D)ThecommandwouldsucceedonlyifinitializationparameterLOG_ARCHIVE_MAX_PROCESSissettovalue2.
答案：A.
LOG_BUFFERspecifiestheamountofmemory(inbytes)thatOracleuseswhenbufferingredoentriestoaredologfile.Redologentriescontainarecordofthechangesthathavebeenmadetothedatabaseblockbuffers.TheLGWRprocesswritesredologentriesfromthelogbuffertoaredologfile.
Thelogbuffersizedependsonthenumberofredostrandsinthesystem.Oneredostrandisallocatedforevery16CPUsandhasadefaultsizeof2MB.Oracleallocatesaminimumof2redostrandsperinstance.Whenthelogbuffersizeisnotspecified,anyremainingmemoryintheredogranulesisgiventothelogbuffer.
LOG_BUFFER
http://download.oracle.com/docs/cd/E11882_01/server.112/e10820/initparams127.htm#REFRN10094
SQL>selectname,issys_modifiablefromv$parameterwherename='log_buffer';
NAMEISSYS_MOD
-------------------- ---------
log_bufferFALSE
通过这个查询，我们知道，修改这个参数必须重启数据库。
25.WhichtwostatementsaretrueabouttherolesintheOracledatabase?(Choosetwo)
A.Arolecanbegrantedtoitself.
B.Rolesareownedbythesysuser.
C.Rolescanbegrantedtootherroles.
D.Arolecannotbeassignedexternalauthentication.
E.Arolecancontainbothsystemandobjectprivileges.
答案：CE
Oracle用户及角色介绍
http://blog.****.net/tianlesoftware/archive/2009/11/09/4786956.aspx
26.Youhavebeenrecentlyhiredasadatabaseadministrator.Yourseniormanagerasksyoutostudythe
productiondatabaseserverandsubmitareportonthesettingsdonebythepreviousDBA.Whileobserving
theserversettings,youfindthatthefollowingparameterhasbeensetintheparameterfileofthedatabase:
REMOTE_OS_AUTHENT=TRUE
WhatcouldhavebeenthereasontosetthisparameterasTRUE?
A)toenableoperatingsystemauthenticationforaremoteclient
B)torestrictthescopeofadministrationtoidenticaloperatingsystems
C)toallowthestartupandshutdownofthedatabasefromaremoteclient
D)toenabletheadministrationoftheoperatingsystemfromaremoteclient
E)todisabletheadministrationoftheoperatingsystemfromaremoteclient
答案：A
参考：http://download.oracle.com/docs/cd/E11882_01/network.112/e10746/asoauth.htm#ASOAG9769
27.Viewthisparametersettinginyourdatabase:
DB_CREATE_FILE_DEST='D:/oracle/product/10.2.0/oradata/oracle'
Youcreatedatablespacebyusingthiscommand:
CREATETABLESPACEUSERS;
WhichtwostatementsaretrueabouttheUSERStablespace?(Choosetwo.)
A)Thetablespacehastwodatafiles.
B)Anerrorisreportedandtablespacecreationfails.
C)Datafilesarecreatedwithnamesgeneratedbytheinstance.
D)Thetablespacecanbeextendedwithoutspecifyingthedatafile.
E)DatafilesbelongingtotheUSERStablespacecannotberenamed.
答案：CD
28.ViewtheExhibittoseethesourceandtargetdatabases.
Youhavecreatedadatabaselink,devdb.us.oracle.com,betweenthedatabasesPRODDBandDEVDB.You
wanttoimportschemaobjectsoftheHRuserusingOracleDataPumpfromthedevelopmentdatabase,
DEVDB,totheproductiondatabase,PRODDB.Youexecutethefollowingcommandonthetargetdatabase
server:
$impdpsystem/managerdirectory=DB_DATA
dumpfile=schemas.dat
schemas=hr
flashback_time=2004-02-0309:00
Thecommandfails,displayingthefollowingerror:
ORA-39001:invalidargumentvalue
ORA-39000:baddumpfilespecification
ORA-31640:unabletoopendumpfile"/home/oracle/schema/schemas.dat"forread
ORA-27037:unabletoobtainfilestatus
Whatwouldyoudotoovercometheerror?
A)removethedumpfileoptioninthecommand
B)removetheflashback_timeoptioninthecommand
C)addtheuser,SYSTEM,totheschemasoptioninthecommand
D)addnetwork_link=devdb.us.oracle.comoptioninthecommand
E)removetheschemasoptionandaddthenetwork_link=devdb.us.oracle.comoptioninthecommand
F)removethedumpfileoptionandaddthenetwork_link=devdb.us.oracle.comoptioninthecommand
答案：F
29.WhatistheimplicationofsettingtheinitializationparameterFAST_START_MTTR_TARGETto0inyour
database?
A)MTTRAdvisorwouldbedisabled.
B)RedoLogAdvisorwouldbedisabled.
C)Automatictuningofcheckpointwouldbedisabled.
D)Checkpointinformationwouldnotbewrittentothealertlogfile.
答案：C
FAST_START_MTTR_TARGETenablesyoutospecifythenumberofsecondsthedatabasetakestoperformcrashrecoveryofasingleinstance.Whenspecified,FAST_START_MTTR_TARGETisoverriddenbyLOG_CHECKPOINT_INTERVAL.
http://download.oracle.com/docs/cd/E11882_01/server.112/e10820/initparams084.htm#REFRN10058
30.UsersofHRschemacomplainaboutslower-than-normalperformance.OninvestigationtheDBAfoundthatmaintenancewasrecentlyperformedonsomeofthetables.TheDBAtracedthequerythattakeslongerthannormaltoexecute.
ViewtheExhibitexhibit_before.
AftertheDBAresolvestheproblem,thequeryperformsnormally.
ViewtheExhibitexhibit_after.
WhatactionwouldtheDBAhavetakentoresolvetheperformanceproblem?
A)analyzedtheEMPLOYEEStabletocollectthecurrentstatistics
B)movedtheEMPLOYEEStableintoalocallymanagedtablespace
C)movedtheEMPLOYEEStabletoanotherlocationinthesametablespace
D)reorganizedtheassociatedindexesfortheEMPLOYEEStablethatwereinanunusablestate
E)movedtheindexesassociatedwiththeEMPLOYEEStabletothesametablespacewheretheEMPLOYEEStableexists
答案：D
------------------------------------------------------------------------------
Blog：http://blog.****.net/tianlesoftware
网上资源：http://tianlesoftware.download.****.net
相关视频：http://blog.****.net/tianlesoftware/archive/2009/11/27/4886500.aspx
DBA1群：62697716(满);DBA2群：62697977
DBA3群：63306533;聊天群：40132017