Upload File

essential dba interview questions _ technologylearning.pdf

  • Home
  • Documents
  • Essential DBA Interview Questions _ TechnologyLearning.pdf
98
TechnologyLearning Learning is not a spectator sport.. Let's play INSTALLING AND UPGRADING THE ORACLE SERVER AND APPLICATION TOOLS. ALLOCATING SYSTEM STORAGE AND PLANNING FUTURE STORAGE REQUIREMENTS FOR THE DATABASE SYSTEM. MANAGING PRIMARY DATABASE STRUCTURES (TABLESPACES) MANAGING PRIMARY OBJECTS (TABLE,VIEWS,INDEXES) ENROLLING USERS AND MAINTAINING SYSTEM SECURITY. ENSURING COMPLIANCE WITH ORALCE LICENSE AGREEMENT CONTROLLING AND MONITORING USER ACCESS TO THE DATABASE. MONITORING AND OPTIMIZING THE PERFORMANCE OF THE DATABASE. PLANNING FOR BACKUP AND RECOVERY OF DATABASE INFORMATION. MAINTAIN ARCHIVED DATA ON TAPE BACKING UP AND RESTORING THE DATABASE. CONTACTING ORACLE CORPORATION FOR TECHNICAL SUPPORT. 2.EXPLAIN THE DIFFERENCE BETWEEN A HOT BACKUP AND A COLD BACKUP AND THE BENEFITS ASSOCIATED WITH EACH? A HOT BACKUP IS BASICALLY TAKING A BACKUP OF THE DATABASE WHILE IT IS STILL UP AND RUNNING AND IT MUST BE IN ARCHIVE LOG MODE. A COLD BACKUP IS TAKING A BACKUP OF THE DATABASE WHILE IT IS SHUT DOWN AND DOES NOT REQUIRE BEING IN ARCHIVE LOG MODE. THE BENEFIT OF TAKING A HOT BACKUP IS THAT THE DATABASE IS STILL AVAILABLE FOR USE WHILE THE BACKUP IS OCCURRING AND YOU CAN RECOVER THE DATABASE TO ANY POINT IN TIME. THE BENEFIT OF TAKING A COLD BACKUP IS THAT IT IS TYPICALLY EASIER TO ADMINISTER THE BACKUP AND RECOVERY PROCESS. IN ADDITION, SINCE YOU ARE TAKING COLD BACKUPS THE DATABASE DOES NOT REQUIRE BEING IN ARCHIVE LOG MODE AND THUS THERE WILL BE A SLIGHT PERFORMANCE GAIN AS THE DATABASE IS NOT CUTTING ARCHIVE LOGS TO DISK. 3. EXPLAIN THE DIFFERENCE BETWEEN A DATA BLOCK, AN EXTENT AND A SEGMENT? A DATA BLOCK IS THE SMALLEST UNIT OF LOGICAL STORAGE FOR A DATABASE OBJECT. AS OBJECTS GROW THEY TAKE CHUNKS OF ADDITIONAL STORAGE THAT ARE COMPOSED OF CONTIGUOUS DATA BLOCKS. THESE GROUPINGS OF CONTIGUOUS DATA BLOCKS ARE CALLED EXTENTS. ALL THE EXTENTS THAT AN OBJECT TAKES WHEN GROUPED TOGETHER ARE CONSIDERED THE SEGMENT OF THE DATABASE OBJECT. 4. COMPARE AND CONTRAST TRUNCATE AND DELETE FOR A TABLE? BOTH THE TRUNCATE AND DELETE COMMAND HAVE THE DESIRED OUTCOME OF GETTING RID OF ALL THE ROWS IN A TABLE. THE DIFFERENCE BETWEEN THE TWO IS THAT THE TRUNCATE COMMAND IS A DDL OPERATION AND JUST MOVES THE HIGH WATER MARK AND PRODUCES A NOW ROLLBACK. THE DELETE COMMAND, ON THE OTHER HAND, IS A DML OPERATION, WHICH WILL PRODUCE A ROLLBACK AND THUS TAKE LONGER TO COMPLETE. 5. WHAT COMMAND WOULD YOU USE TO CREATE A BACKUP CONTROL FILE? ALTER DATABASE BACKUP CONTROL FILE TO TRACE. 6. HOW WOULD YOU GO ABOUT INCREASING THE BUFFER CACHE HIT RATIO? USE THE BUFFER CACHE ADVISORY OVER A GIVEN WORKLOAD AND THEN QUERY THE V$DB_CACHE_ADVICE TABLE. IF A CHANGE WAS NECESSARY THEN I WOULD USE THE ALTER SYSTEM SET DB_CACHE_SIZE COMMAND. 7. EXPLAIN THE DIFFERENCE BETWEEN $ORACLE_HOME AND $ORACLE_BASE? ORACLE_BASE IS THE ROOT DIRECTORY FOR ORACLE. ORACLE_HOME LOCATED BENEATH ORACLE_BASE IS WHERE THE ORACLE PRODUCTS RESIDE. 8. WHEN A USER PROCESS FAILS, WHAT BACKGROUND PROCESS CLEANS UP AFTER IT? PMON 9. WHAT BACKGROUND PROCESS REFRESHES MATERIALIZED VIEWS? THE JOB QUEUE PROCESSES. 10. HOW WOULD YOU DETERMINE WHAT SESSIONS ARE CONNECTED AND WHAT RESOURCES THEY ARE WAITING FOR? USE OF V$SESSION AND V$SESSION_WAIT Sachin Thapa 19:31 on November 20, 2014 Tags: apps ( 19 ) , best, core, DBA ( 22 ) , interview, oracle ( 63 ) , question Permalink (https://techknowledgeworld.wordpress.com/2014/11/20/essentialdbainterviewquestions/) Essential DBA Interview Questions DBA INTERVIEW QUESTIONS & ANSWERS 1.WHAT ARE THE RESPONSIBILITIES OF A DATABASE ADMINISTRATOR ?

Upload: sampath-kumar

Post on 19-Aug-2015

20 views

Category:

Documents


5 download

Report

TRANSCRIPT

4/30/2015 EssentialDBAInterviewQuestions|TechnologyLearninghttps://techknowledgeworld.wordpress.com/2014/11/20/essentialdbainterviewquestions/ 1/98TechnologyLearningLearningisnotaspectatorsport..Let'splayINSTALLINGANDUPGRADINGTHEORACLESERVERANDAPPLICATIONTOOLS.ALLOCATINGSYSTEMSTORAGEANDPLANNINGFUTURESTORAGEREQUIREMENTSFORTHEDATABASESYSTEM.MANAGINGPRIMARYDATABASESTRUCTURES(TABLESPACES)MANAGINGPRIMARYOBJECTS(TABLE,VIEWS,INDEXES)ENROLLINGUSERSANDMAINTAININGSYSTEMSECURITY.ENSURINGCOMPLIANCEWITHORALCELICENSEAGREEMENTCONTROLLINGANDMONITORINGUSERACCESSTOTHEDATABASE.MONITORINGANDOPTIMIZINGTHEPERFORMANCEOFTHEDATABASE.PLANNINGFORBACKUPANDRECOVERYOFDATABASEINFORMATION.MAINTAINARCHIVEDDATAONTAPEBACKINGUPANDRESTORINGTHEDATABASE.CONTACTINGORACLECORPORATIONFORTECHNICALSUPPORT.2.EXPLAINTHEDIFFERENCEBETWEENAHOTBACKUPANDACOLDBACKUPANDTHEBENEFITSASSOCIATEDWITHEACH?AHOTBACKUPISBASICALLYTAKINGABACKUPOFTHEDATABASEWHILEITISSTILLUPANDRUNNINGANDITMUSTBEINARCHIVELOGMODE.ACOLDBACKUPISTAKINGABACKUPOFTHEDATABASEWHILEITISSHUTDOWNANDDOESNOTREQUIREBEINGINARCHIVELOGMODE.THEBENEFITOFTAKINGAHOTBACKUPISTHATTHEDATABASEISSTILLAVAILABLEFORUSEWHILETHEBACKUPISOCCURRINGANDYOUCANRECOVERTHEDATABASETOANYPOINTINTIME.THEBENEFITOFTAKINGACOLDBACKUPISTHATITISTYPICALLYEASIERTOADMINISTERTHEBACKUPANDRECOVERYPROCESS.INADDITION,SINCEYOUARETAKINGCOLDBACKUPSTHEDATABASEDOESNOTREQUIREBEINGINARCHIVELOGMODEANDTHUSTHEREWILLBEASLIGHTPERFORMANCEGAINASTHEDATABASEISNOTCUTTINGARCHIVELOGSTODISK.3.EXPLAINTHEDIFFERENCEBETWEENADATABLOCK,ANEXTENTANDASEGMENT?ADATABLOCKISTHESMALLESTUNITOFLOGICALSTORAGEFORADATABASEOBJECT.ASOBJECTSGROWTHEYTAKECHUNKSOFADDITIONALSTORAGETHATARECOMPOSEDOFCONTIGUOUSDATABLOCKS.THESEGROUPINGSOFCONTIGUOUSDATABLOCKSARECALLEDEXTENTS.ALLTHEEXTENTSTHATANOBJECTTAKESWHENGROUPEDTOGETHERARECONSIDEREDTHESEGMENTOFTHEDATABASEOBJECT.4.COMPAREANDCONTRASTTRUNCATEANDDELETEFORATABLE?BOTHTHETRUNCATEANDDELETECOMMANDHAVETHEDESIREDOUTCOMEOFGETTINGRIDOFALLTHEROWSINATABLE.THEDIFFERENCEBETWEENTHETWOISTHATTHETRUNCATECOMMANDISADDLOPERATIONANDJUSTMOVESTHEHIGHWATERMARKANDPRODUCESANOWROLLBACK.THEDELETECOMMAND,ONTHEOTHERHAND,ISADMLOPERATION,WHICHWILLPRODUCEAROLLBACKANDTHUSTAKELONGERTOCOMPLETE.5. WHATCOMMANDWOULDYOUUSETOCREATEABACKUPCONTROLFILE?ALTERDATABASEBACKUPCONTROLFILETOTRACE.6.HOWWOULDYOUGOABOUTINCREASINGTHEBUFFERCACHEHITRATIO?USETHEBUFFERCACHEADVISORYOVERAGIVENWORKLOADANDTHENQUERYTHEV$DB_CACHE_ADVICETABLE.IFACHANGEWASNECESSARYTHENIWOULDUSETHEALTERSYSTEMSETDB_CACHE_SIZECOMMAND.7.EXPLAINTHEDIFFERENCEBETWEEN$ORACLE_HOMEAND$ORACLE_BASE?ORACLE_BASEISTHEROOTDIRECTORYFORORACLE.ORACLE_HOMELOCATEDBENEATHORACLE_BASEISWHERETHEORACLEPRODUCTSRESIDE.8. WHENAUSERPROCESSFAILS,WHATBACKGROUNDPROCESSCLEANSUPAFTERIT?PMON9. WHATBACKGROUNDPROCESSREFRESHESMATERIALIZEDVIEWS?THEJOBQUEUEPROCESSES.10. HOWWOULDYOUDETERMINEWHATSESSIONSARECONNECTEDANDWHATRESOURCESTHEYAREWAITINGFOR?USEOFV$SESSIONANDV$SESSION_WAITSachinThapa19:31onNovember20,2014Tags:apps(19),best,core,DBA(22),interview,oracle(63),questionPermalink(https://techknowledgeworld.wordpress.com/2014/11/20/essentialdbainterviewquestions/)EssentialDBAInterviewQuestionsDBAINTERVIEWQUESTIONS&ANSWERS1.WHATARETHERESPONSIBILITIESOFADATABASEADMINISTRATOR?4/30/2015 EssentialDBAInterviewQuestions|TechnologyLearninghttps://techknowledgeworld.wordpress.com/2014/11/20/essentialdbainterviewquestions/ 2/9811. DESCRIBEWHATREDOLOGSARE?REDOLOGSARELOGICALANDPHYSICALSTRUCTURESTHATAREDESIGNEDTOHOLDALLTHECHANGESMADETOADATABASEANDAREINTENDEDTOAIDINTHERECOVERYOFADATABASE.12. HOWWOULDYOUFORCEALOGSWITCH?ALTERSYSTEMSWITCHLOGFILE13.NAMEATABLESPACEAUTOMATICALLYCREATEDWHENYOUCREATEADATABASE?THESYSTEMTABLESPACE.14.WHATARETHEMINIMUMPARAMETERSSHOULDEXISTINTHEPARAMETERFILE(INIT.ORA)?DBNAMEMUSTSETTOATEXTSTRINGOFNOMORETHAN8CHARACTERSANDITWILLBESTOREDINSIDETHEDATAFILES,REDOLOGFILESANDCONTROLFILESANDCONTROLFILEWHILEDATABASECREATION.DB_DOMAINITISSTRINGTHATSPECIFIESTHENETWORKDOMAINWHERETHEDATABASEISCREATED.THEGLOBALDATABASENAMEISIDENTIFIEDBYSETTINGTHESEPARAMETERS(DB_NAME&DB_DOMAIN)CONTORLFILESLISTOFCONTROLFILENAMESOFTHEDATABASE.IFNAMEISNOTMENTIONEDTHENDEFAULTNAMEWILLBEUSED.DB_BLOCK_BUFFERSTODETERMINETHENOOFBUFFERSINTHEBUFFERCACHEINSGA.PROCESSESTODETERMINENUMBEROFOPERATINGSYSTEMPROCESSESTHATCANBECONNECTEDTOORACLECONCURRENTLY.THEVALUESHOULDBE5(BACKGROUNDPROCESS)ANDADDITIONAL1FOREACHUSER.ROLLBACK_SEGMENTSLISTOFROLLBACKSEGMENTSANORACLEINSTANCEACQUIRESATDATABASESTARTUP.ALSOOPTIONALLYLICENSE_MAX_SESSIONS,LICENSE_SESSION_WARNINGANDLICENSE_MAX_USERS.15. CANONERENAMEATABLESPACE?NO,THISISLISTEDASENHANCEMENTREQUEST148742.WORKAROUND:EXPORTALLOFTHEOBJECTSFROMTHETABLESPACEDROPTHETABLESPACEINCLUDINGCONTENTSRECREATETHETABLESPACEIMPORTTHEOBJECTS16. CANONERESIZETABLESPACESANDDATAFILES?ONECANMANUALLYINCREASEORDECREASETHESIZEOFADATAFILEFROMORACLE7.2USINGTHECOMMAND.ALTERDATABASEDATAFILEFILENAME2RESIZE100MBECAUSEYOUCANCHANGETHESIZESOFDATAFILES,YOUCANADDMORESPACETOYOURDATABASEWITHOUTADDINGMOREDATAFILES.THISISBENEFICIALIFYOUARECONCERNEDABOUTREACHINGTHEMAXIMUMNUMBEROFDATAFILESALLOWEDINYOURDATABASE.MANUALLYREDUCINGTHESIZESOFDATAFILESALLOWSYOUTORECLAIMUNUSEDSPACEINTHEDATABASE.THISISUSEFULFORCORRECTINGERRORSINESTIMATIONSOFSPACEREQUIREMENTS.ALSO,DATAFILESCANBEALLOWEDTOAUTOMATICALLYEXTENDIFMORESPACEISREQUIRED.LOOKATTHEFOLLOWINGCOMMAND:CREATETABLESPACEPCS_DATA_TSDATAFILEC:\ORA_APPS\PCS\PCSDATA1.DBFSIZE3MAUTOEXTENDONNEXT1MMAXSIZEUNLIMITEDDEFAULTSTORAGE(INITIAL10240NEXT10240MINEXTENTS1MAXEXTENTSUNLIMITEDPCTINCREASE0)ONLINEPERMANENT17. WHYANDWHENSHOULDIBACKUPMYDATABASE?BACKUPANDRECOVERYISONEOFTHEMOSTIMPORTANTASPECTSOFADBASJOB.IFYOULOSEYOURCOMPANYSDATA,YOUCOULDVERYWELLLOSEYOURJOB.HARDWAREANDSOFTWARECANALWAYSBEREPLACED,BUTYOURDATAMAYBEIRREPLACEABLE!NORMALLYONEWOULDSCHEDULEAHIERARCHYOFDAILY,WEEKLYANDMONTHLYBACKUPS,HOWEVERCONSULTWITHYOURUSERSBEFOREDECIDINGONABACKUPSCHEDULE.BACKUPFREQUENCYNORMALLYDEPENDSONTHEFOLLOWINGFACTORS:.RATEOFDATACHANGE/TRANSACTIONRATE.DATABASEAVAILABILITY/CANYOUSHUTDOWNFORCOLDBACKUPS?.CRITICALITYOFTHEDATA/VALUEOFTHEDATATOTHECOMPANY.READONLYTABLESPACENEEDSBACKINGUPJUSTONCERIGHTAFTERYOUMAKEITREADONLY.IFYOUARERUNNINGINARCHIVELOGMODEYOUCANBACKUPPARTSOFADATABASEOVERANEXTENDEDCYCLEOFDAYS.IFARCHIVELOGGINGISENABLEDONENEEDSTOBACKUPARCHIVEDLOGFILESTIMEOUSLYTOPREVENTDATABASEFREEZES.ETC.4/30/2015 EssentialDBAInterviewQuestions|TechnologyLearninghttps://techknowledgeworld.wordpress.com/2014/11/20/essentialdbainterviewquestions/ 3/98CAREFULLYPLANBACKUPRETENTIONPERIODS.ENSUREENOUGHBACKUPMEDIA(TAPES)AREAVAILABLEANDTHATOLDBACKUPSAREEXPIREDINTIMETOMAKEMEDIAAVAILABLEFORNEWBACKUPS.OFFSITEVAULTINGISALSOHIGHLYRECOMMENDED.FREQUENTLYTESTYOURABILITYTORECOVERANDDOCUMENTALLPOSSIBLESCENARIOS.REMEMBER,ITSTHELITTLETHINGSTHATWILLGETYOU.MOSTFAILEDRECOVERIESAREARESULTOFORGANIZATIONALERRORSANDMISCOMMUNICATIONS.18.WHATISTHEDIFFERENCEBETWEENRESTORINGANDRECOVERING?RESTORINGINVOLVESCOPYINGBACKUPFILESFROMSECONDARYSTORAGE(BACKUPMEDIA)TODISK.THISCANBEDONETOREPLACEDAMAGEDFILESORTOCOPY/MOVEADATABASETOANEWLOCATION.RECOVERYISTHEPROCESSOFAPPLYINGREDOLOGSTOTHEDATABASETOROLLITFORWARD.ONECANROLLFORWARDUNTILASPECIFICPOINTINTIME(BEFORETHEDISASTEROCCURRED),ORROLLFORWARDUNTILTHELASTTRANSACTIONRECORDEDINTHELOGFILES.SQL>CONNECTSYSASSYSDBASQL>RECOVERDATABASEUNTILTIME20010306:16:00:00USINGBACKUPCONTROLFILE19.WHENCREATINGAUSER,WHATPERMISSIONSMUSTYOUGRANTTOALLOWTHEMTOCONNECTTOTHEDATABASE?GRANTTHECONNECTTOTHEUSER.20.WHATISSTATSPACKANDHOWDOESONEUSEIT?STATSPACKISASETOFPERFORMANCEMONITORINGANDREPORTINGUTILITIESPROVIDEDBYORACLEFROMORACLE8IANDABOVE.STATSPACKPROVIDESIMPROVEDBSTAT/ESTATFUNCTIONALITY,THOUGHTHEOLDBSTAT/ESTATSCRIPTSARESTILLAVAILABLE.FORMOREINFORMATIONABOUTSTATSPACK,READTHEDOCUMENTATIONINFILE$ORACLE_HOME/RDBMS/ADMIN/SPDOC.TXT.INSTALLSTATSPACK:CD$ORACLE_HOME/RDBMS/ADMINSQLPLUS/[email protected]/ASSYSDBA@SPCREATE.SQLENTERTABLESPACENAMESWHENPROMPTEDUSESTATSPACK:SQLPLUSPERFSTAT/PERFSTATEXECSTATSPACK.SNAPTAKEAPERFORMANCESNAPSHOTSEXECSTATSPACK.SNAPOGETALISTOFSNAPSHOTSSELECTSNAP_ID,SNAP_TIMEFROMSTATS$SNAPSHOT@SPREPORT.SQLENTERTWOSNAPSHOTIDSFORDIFFERENCEREPORTOTHERSTATSPACKSCRIPTS:.SPPURGE.SQLPURGEARANGEOFSNAPSHOTIDSBETWEENTHESPECIFIEDBEGINANDENDSNAPIDS.SPAUTO.SQLSCHEDULEADBMS_JOBTOAUTOMATETHECOLLECTIONOFSTATPACKSTATISTICS.SPCREATE.SQLINSTALLSTHESTATSPACKUSER,TABLESANDPACKAGEONADATABASE(RUNASSYS)..SPDROP.SQLDEINSTALLSTATSPACKFROMDATABASE(RUNASSYS).SPPURGE.SQLDELETEARANGEOFSNAPSHOTIDSFROMTHEDATABASE.SPREPORT.SQLREPORTONDIFFERENCESBETWEENVALUESRECORDEDINTWOSNAPSHOTS.SPTRUNC.SQLTRUNCATESALLDATAINSTATSPACKTABLES21. HOWDOYOUADDADATAFILETOATABLESPACE?ALTERTABLESPACEADDDATAFILESIZE22. WHATISSAVEPOINT?FORLONGTRANSACTIONSTHATCONTAINMANYSQLSTATEMENTS,INTERMEDIATEMARKERSORSAVEPOINTSCANBEDECLAREDWHICHCANBEUSEDTODIVIDEATRANSACTIONINTOSMALLERPARTS.THISALLOWSTHEOPTIONOFLATERROLLINGBACKALLWORKPERFORMEDFROMTHECURRENTPOINTINTHETRANSACTIONTOADECLAREDSAVEPOINTWITHINTHETRANSACTION.23.WHATISMEANBYPROGRAMGLOBALAREA(PGA)?ITISAREAINMEMORYTHATISUSEDBYASINGLEORACLEUSERPROCESS.24. HOWDOESONEMANAGEORACLEDATABASEUSERS?ORACLEUSERACCOUNTSCANBELOCKED,UNLOCKED,FORCEDTOCHOOSENEWPASSWORDS,ETC.FOREXAMPLE,ALLACCOUNTSEXCEPTSYSANDSYSTEMWILLBELOCKEDAFTERCREATINGANORACLE9IDBDATABASEUSINGTHEDBCONFIGURATIONASSISTANT(DBCA).DBASMUSTUNLOCKTHESEACCOUNTSTOMAKETHEMAVAILABLETOUSERS.LOOKATTHESEEXAMPLES:ALTERUSERSCOTTACCOUNTLOCKLOCKAUSERACCOUNTALTERUSERSCOTTACCOUNTUNLOCKUNLOCKSALOCKEDUSERSACCOUNTALTERUSERSCOTTPASSWORDEXPIREFORCEUSERTOCHOOSEANEWPASSWORD25. HOWDOESONETUNEORACLEWAITEVENTS?SOMEWAITEVENTSFROMV$SESSION_WAITANDV$SYSTEM_EVENTVIEWS:EVENTNAME: TUNINGRECOMMENDATION:DBFILESEQUENTIALREADTUNESQLTODOLESSI/O.MAKESUREALLOBJECTSAREANALYZED.REDISTRIBUTEI/OACROSSDISKS.4/30/2015 EssentialDBAInterviewQuestions|TechnologyLearninghttps://techknowledgeworld.wordpress.com/2014/11/20/essentialdbainterviewquestions/ 4/98BUFFERBUSYWAITSINCREASEDB_CACHE_SIZE(DB_BLOCK_BUFFERSPRIORTO9I)/ANALYZECONTENTIONFROMSYS.V$BHLOGBUFFERSPACES INCREASELOG_BUFFERPARAMETERORMOVELOGFILESTOFASTERDISKS26. CANONEMONITORHOWFASTATABLEISIMPORTED?IFYOUNEEDTOMONITORHOWFASTROWSAREIMPORTEDFROMARUNNINGIMPORTJOB,TRYONEOFTHEFOLLOWINGMETHODS:METHOD1:SELECTSUBSTR(SQL_TEXT,INSTR(SQL_TEXT,INTO),30)TABLE_NAME,ROWS_PROCESSED,ROUND((SYSDATETO_DATE(FIRST_LOAD_TIME,YYYYMMDDHH24:MI:SS))*24*60,1)MINUTES,TRUNC(ROWS_PROCESSED/((SYSDATETO_DATE(FIRST_LOAD_TIME,YYYYMMDDHH24:MI:SS))*24*60))ROWS_PER_MINFROMSYS.V_$SQLAREAWHERESQL_TEXTLIKEINSERT%INTO%ANDCOMMAND_TYPE=2ANDOPEN_VERSIONS>0FORTHISTOWORKONENEEDSTOBEONORACLE7.3ORHIGHER(7.2MIGHTALSOBEOK).IFTHEIMPORTHASMORETHANONETABLE,THISSTATEMENTWILLONLYSHOWINFORMATIONABOUTTHECURRENTTABLEBEINGIMPORTED.CONTRIBUTEDBYOSVALDOANCAROLA,BS.AS.ARGENTINA.METHOD2:USETHEFEEDBACK=NIMPORTPARAMETER.THISCOMMANDWILLTELLIMPTODISPLAYADOTFOREVERYNROWSIMPORTED.27. CANONEIMPORTTABLESTOADIFFERENTTABLESPACE?ORACLEOFFERSNOPARAMETERTOSPECIFYADIFFERENTTABLESPACETOIMPORTDATAINTO.OBJECTSWILLBERECREATEDINTHETABLESPACETHEYWEREORIGINALLYEXPORTEDFROM.ONECANALTERTHISBEHAVIOURBYFOLLOWINGONEOFTHESEPROCEDURES:PRECREATETHETABLE(S)INTHECORRECTTABLESPACE:.IMPORTTHEDUMPFILEUSINGTHEINDEXFILE=OPTION.EDITTHEINDEXFILE.REMOVEREMARKSANDSPECIFYTHECORRECTTABLESPACES..RUNTHISINDEXFILEAGAINSTYOURDATABASE,THISWILLCREATETHEREQUIREDTABLESINTHEAPPROPRIATETABLESPACES.IMPORTTHETABLE(S)WITHTHEIGNORE=YOPTION.CHANGETHEDEFAULTTABLESPACEFORTHEUSER:.REVOKETHEUNLIMITEDTABLESPACEPRIVILEGEFROMTHEUSER.REVOKETHEUSERSQUOTAFROMTHETABLESPACEFROMWHERETHEOBJECTWASEXPORTED.THISFORCESTHEIMPORTUTILITYTOCREATETABLESINTHEUSERSDEFAULTTABLESPACE..MAKETHETABLESPACETOWHICHYOUWANTTOIMPORTTHEDEFAULTTABLESPACEFORTHEUSER.IMPORTTHETABLE.28. WHATISSQL*LOADERANDWHATISITUSEDFOR?SQL*LOADERISABULKLOADERUTILITYUSEDFORMOVINGDATAFROMEXTERNALFILESINTOTHEORACLEDATABASE.ITSSYNTAXISSIMILARTOTHATOFTHEDB2LOADUTILITY,BUTCOMESWITHMOREOPTIONS.SQL*LOADERSUPPORTSVARIOUSLOADFORMATS,SELECTIVELOADING,ANDMULTITABLELOADS.29. WHATISRMAN?RECOVERYMANAGERISATOOLTHAT:MANAGESTHEPROCESSOFCREATINGBACKUPSANDALSOMANAGESTHEPROCESSOFRESTORINGANDRECOVERINGFROMTHEM.30. WHATISHITRATIO?ITISAMEASUREOFWELLTHEDATACACHEBUFFERISHANDLINGREQUESTSFORDATA.HITRATIO=(LOGICALREADSPHYSICALREADSHITSMISSES)/LOGICALREADS.31. WHYUSERMAN?NOEXTRACOSTSITSAVAILABLEFREERMANINTRODUCEDINORACLE8ITHASBECOMESIMPLERWITHNEWERVERSIONSANDEASIERTHANUSERMANAGEDBACKUPSPROPERSECURITYYOUARE100%SUREYOURDATABASEHASBEENBACKEDUP.ITSCONTAINSDETAILOFTHEBACKUPSTAKENETCINITSCENTRALREPOSITORYFACILITYFORTESTINGVALIDITYOFBACKUPSALSOCOMMANDSLIKECROSSCHECKTOCHECKTHESTATUSOFBACKUP.FASTERBACKUPSANDRESTORESCOMPAREDTOBACKUPSWITHOUTRMANRMANISTHEONLYBACKUPTOOLWHICHSUPPORTSINCREMENTALBACKUPS.ORACLE10GHASGOTFURTHEROPTIMIZEDINCREMENTALBACKUPWHICHHASRESULTEDINIMPROVEMENTOF4/30/2015 EssentialDBAInterviewQuestions|TechnologyLearninghttps://techknowledgeworld.wordpress.com/2014/11/20/essentialdbainterviewquestions/ 5/98PERFORMANCEDURINGBACKUPANDRECOVERYTIMEPARALLELOPERATIONSARESUPPORTEDBETTERQUERYINGFACILITYFORKNOWINGDIFFERENTDETAILSOFBACKUPNOEXTRAREDOGENERATEDWHENBACKUPISTAKEN..COMPAREDTOONLINEBACKUPWITHOUTRMANWHICHRESULTSINSAVINGOFSPACEINHARDDISKRMANANINTELLIGENTTOOLMAINTAINSREPOSITORYOFBACKUPMETADATAREMEMBERSBACKUPSETLOCATIONKNOWSWHATNEEDTOBACKEDUPKNOWSWHATISREQUIREDFORRECOVERYKNOWSWHATBACKUPSAREREDUNDANTUNDERSTANDINGTHERMANARCHITECTUREANORACLERMANCOMPRISESOFRMANEXECUTABLETHISCOULDBEPRESENTANDFIREDEVENTHROUGHCLIENTSIDETARGETDATABASETHISISTHEDATABASEWHICHNEEDSTOBEBACKEDUP.RECOVERYCATALOGRECOVERYCATALOGISOPTIONALOTHERWISEBACKUPDETAILSARESTOREDINTARGETDATABASECONTROLFILE.ITISAREPOSITORYOFINFORMATIONQUERIEDANDUPDATEDBYRECOVERYMANAGERITISASCHEMAORUSERSTOREDINORACLEDATABASE.ONESCHEMACANSUPPORTMANYDATABASESITCONTAINSINFORMATIONABOUTPHYSICALSCHEMAOFTARGETDATABASEDATAFILEANDARCHIVELOG,BACKUPSETSANDPIECESRECOVERYCATALOGISAMUSTINFOLLOWINGSCENARIOS.INORDERTOSTORESCRIPTS.FORTABLESPACEPOINTINTIMERECOVERYMEDIAMANAGEMENTSOFTWAREMEDIAMANAGEMENTSOFTWAREISAMUSTIFYOUAREUSINGRMANFORSTORINGBACKUPINTAPEDRIVEDIRECTLY.BACKUPSINRMANORACLEBACKUPSINRMANAREOFTHEFOLLOWINGTYPERMANCOMPLETEBACKUPORRMANINCREMENTALBACKUPTHESEBACKUPSAREOFRMANPROPRIETARYNATUREIMAGECOPYTHEADVANTAGEOFUINGIMAGECOPYISITSNOTINRMANPROPRIETARYFORMAT..BACKUPFORMATRMANBACKUPISNOTINORACLEFORMATBUTINRMANFORMAT.ORACLEBACKUPCOMPRISESOFBACKUPSETSANDITCONSISTSOFBACKUPPIECES.BACKUPSETSARELOGICALENTITYINORACLE9IITGETSSTOREDINADEFAULTLOCATIONTHEREARETWOTYPEOFBACKUPSETSDATAFILEBACKUPSETS,ARCHIVELOGBACKUPSETSONEMOREIMPORTANTPOINTOFDATAFILEBACKUPSETSISITDONOTINCLUDEEMPTYBLOCKS.ABACKUPSETWOULDCONTAINMANYBACKUPPIECES.ASINGLEBACKUPPIECECONSISTSOFPHYSICALFILESWHICHAREINRMANPROPRIETARYFORMAT.EXAMPLEOFTAKINGBACKUPUSINGRMANTAKINGRMANBACKUPINNONARCHIVEMODEINDOSPROMPTTYPERMANYOUGETTHERMANPROMPTRMAN>CONNECTTARGETCONNECTTOTARGETDATABASE:MAGICUSINGTARGETDATABASECONTROLFILEINSTEADOFRECOVERYCATALOGLETSTAKEASIMPLEBACKUPOFDATABASEINNONARCHIVEMODESHUTDOWNIMMEDIATESHUTDOWNSTHEDATABASESTARTUPMOUNTBACKUPDATABASEITSSTARTBACKINGTHEDATABASEALTERDATABASEOPENWECANFIRETHESAMECOMMANDINARCHIVELOGMODEANDWHOLEOFDATAFILESWILLBEBACKEDBACKUPDATABASEPLUSARCHIVELOGRESTORINGDATABASERESTORINGDATABASEHASBEENMADEVERYSIMPLEIN9I.ITISJUSTRESTOREDATABASE..RMANHASBECOMEINTELLIGENTTOIDENTIFYWHICHDATAFILESHASTOBERESTOREDANDTHELOCATIONOFBACKUPEDUPFILE.ORACLEENHANCEMENTFORRMANIN10GFLASHRECOVERYAREARIGHTNOWTHEPRICEOFHARDDISKISFALLING.MANYDBAARETAKINGORACLEDATABASEBACKUPINSIDETHEHARDDISKITSELFSINCEITRESULTSINLESSERMEANTIMEBETWEENRECOVERABILITY.THENEWPARAMETERINTRODUCEDISDB_RECOVERY_FILE_DEST=/ORACLE/FLASH_RECOVERY_AREABYCONFIGURINGTHERMANRETENTIONPOLICYTHEFLASHRECOVERYAREAWILLAUTOMATICALLYDELETEOBSOLETEBACKUPSANDARCHIVELOGSTHATARENOLONGERREQUIREDBASEDONTHATCONFIGURATIONORACLEHASINTRODUCEDNEWFEATURESININCREMENTALBACKUP4/30/2015 EssentialDBAInterviewQuestions|TechnologyLearninghttps://techknowledgeworld.wordpress.com/2014/11/20/essentialdbainterviewquestions/ 6/98CHANGETRACKINGFILEORACLE10GHASTHEFACILITYTODELIVERFASTERINCREMENTALSWITHTHEIMPLEMENTATIONOFCHANGEDTRACKINGFILEFEATURE.THISWILLRESULTSINFASTERBACKUPSLESSERSPACECONSUMPTIONANDALSOREDUCESTHETIMENEEDEDFORDAILYBACKUPSINCREMENTALLYUPDATEDBACKUPSORACLEDATABASE10GINCREMENTALLYUPDATESBACKUPFEATURESMERGESTHEIMAGECOPYOFADATAFILEWITHRMANINCREMENTALBACKUP.THERESULTINGIMAGECOPYISNOWUPDATEDWITHBLOCKCHANGESCAPTUREDBYINCREMENTALBACKUPS.THEMERGINGOFTHEIMAGECOPYANDINCREMENTALBACKUPISINITIATEDWITHRMANRECOVERCOMMAND.THISRESULTSINFASTERRECOVERY.BINARYCOMPRESSIONTECHNIQUEREDUCESBACKUPSPACEUSAGEBY5075%.WITHTHENEWDURATIONOPTIONFORTHERMANBACKUPCOMMAND,DBASCANWEIGHBACKUPPERFORMANCEAGAINSTSYSTEMSERVICELEVELREQUIREMENTS.BYSPECIFYINGADURATION,RMANWILLAUTOMATICALLYCALCULATETHEAPPROPRIATEBACKUPRATEINADDITION,DBASCANOPTIONALLYSPECIFYWHETHERBACKUPSSHOULDMINIMIZETIMEORSYSTEMLOAD.NEWFEATURESINOEMTOIDENTIFYRMANRELATEDBACKUPLIKEBACKUPPIECES,BACKUPSETSANDIMAGECOPYORACLE9INEWFEATURESPERSISTENTRMANCONFIGURATIONANEWCONFIGURECOMMANDHASBEENINTRODUCEDINORACLE9I,THATLETSYOUCONFIGUREVARIOUSFEATURESINCLUDINGAUTOMATICCHANNELS,PARALLELISM,BACKUPOPTIONS,ETC.THESEAUTOMATICALLOCATIONSANDOPTIONSCANBEOVERRIDDENBYCOMMANDSINARMANCOMMANDFILE.CONTROLFILEAUTOBACKUPSTHROUGHTHISNEWFEATURERMANWILLAUTOMATICALLYPERFORMACONTROLFILEAUTOBACKUP.AFTEREVERYBACKUPORCOPYCOMMAND.BLOCKMEDIARECOVERYIFWECANRESTOREAFEWBLOCKSRATHERTHANANENTIREFILEWEONLYNEEDFEWBLOCKS.WEEVENDONTNEEDTOBRINGTHEDATAFILEOFFLINE.SYNTAXFORITASFOLLOWSBLOCKRECOVERDATAFILE8BLOCK22CONFIGUREBACKUPOPTIMIZATIONPRIORTO9IWHENEVERWEBACKEDUPDATABASEUSINGRMANOURBACKUPALSOUSEDTAKEBACKUPOFREADONLYTABLESPACESWHICHHADALREADYBEENBACKEDUPANDALSOTHESAMEWITHARCHIVELOGTOO.NOWWITH9IBACKUPOPTIMIZATIONPARAMETERWECANPREVENTREPEATBACKUPOFREADONLYTABLESPACEANDARCHIVELOG.THECOMMANDFORTHISISASFOLLOWSCONFIGUREBACKUPOPTIMIZATIONONARCHIVELOGFAILOVERIFRMANCANNOTREADABLOCKINANARCHIVEDLOGFROMADESTINATION.RMANAUTOMATICALLYATTEMPTSTOREADFROMANALTERNATELOCATIONTHISISCALLEDASARCHIVELOGFAILOVERTHEREAREADDITIONALCOMMANDSLIKEBACKUPDATABASENOTBACKEDUPSINCETIME31JAN200214:00:00DONOTBACKUPPREVIOUSLYBACKEDUPFILES(SAYAPREVIOUSBACKUPFAILEDANDYOUWANTTORESTARTFROMWHEREITLEFTOFF).SIMILARSYNTAXISSUPPORTEDFORRESTORESBACKUPDEVICESBTBACKUPSETALLCOPYADISKBACKUPTOTAPE(BACKINGUPABACKUPADDITIONALLYITSUPPORTS.BACKUPOFSERVERPARAMETERFILE.PARALLELOPERATIONSUPPORTED.EXTENSIVEREPORTINGAVAILABLE.SCRIPTING.DUPLEXBACKUPSETS.CORRUPTBLOCKDETECTION.BACKUPARCHIVELOGSPITFALLSOFUSINGRMANPREVIOUSTOVERSIONORACLE9IBACKUPSWERENOTTHATEASYWHICHMEANSYOUHADTOALLOCATEACHANNELCOMPULSORILYTOTAKEBACKUPYOUHADTOGIVEARUNETC.THESYNTAXWASABITCOMPLEXRMANHASNOWBECOMEVERYSIMPLEANDEASYTOUSE..IFYOUCHANGEDTHELOCATIONOFBACKUPSETITISCOMPULSORYFORYOUTOREGISTERITUSINGRMANORWHILEYOUARETRYINGTORESTOREBACKUPITRESULTEDINHANGINGSITUATIONSTHEREISNOMETHODTOKNOWWHETHERDURINGRECOVERYDATABASERESTOREISGOINGTOFAILBECAUSEOFMISSINGARCHIVELOGFILE.COMPULSORYMEDIAMANAGEMENTONLYIFUSINGTAPEBACKUPINCREMENTALBACKUPSTHOUGHUSEDTOCONSUMELESSSPACEUSEDTOBESLOWERSINCEITUSEDTOREADTHEENTIREDATABASETOFINDTHECHANGEDBLOCKSANDALSOTHEYHAVEDIFFICULTTIMESTREAMINGTHETAPEDEVICE..CONSIDERABLEIMPROVEMENTHASBEENMADEIN10GTOOPTIMIZETHEALGORITHMTOHANDLECHANGEDBLOCK.OBSERVATION:INTRODUCEDINORACLE8ITHASBECOMEMOREPOWERFULANDSIMPLERWITHNEWERVERSIONOFORACLE9AND10G.SOIFYOUREALLYDONTWANTTOMISSSOMETHINGCRITICALPLEASESTARTUSINGRMAN.4/30/2015 EssentialDBAInterviewQuestions|TechnologyLearninghttps://techknowledgeworld.wordpress.com/2014/11/20/essentialdbainterviewquestions/ 7/981. WHATHAPPENSWHENYOURUNALTERDATABASEOPENRESETLOGS?THECURRENTONLINEREDOLOGSAREARCHIVED,THELOGSEQUENCENUMBERISRESETTO1,NEWDATABASEINCARNATIONISCREATED,ANDTHEONLINEREDOLOGSAREGIVENANEWTIMESTAMPANDSCN.2.INWHATSCENARIOSOPENRESETLOGSREQUIRED?ANALTERDATABASEOPENRESETLOGSSTATEMENTISREQUIREDAFTERINCOMPLETERECOVERY(POINTINTIMERECOVERY)ORRECOVERYWITHABACKUPCONTROLFILE.3.WHATISSCN(SYSTEMCHANGENUMBER)?THESYSTEMCHANGENUMBER(SCN)ISANEVERINCREASINGVALUETHATUNIQUELYIDENTIFIESACOMMITTEDVERSIONOFTHEDATABASEATAPOINTINTIME.EVERYTIMEAUSERCOMMITSATRANSACTIONORACLERECORDSANEWSCNINREDOLOGS.ORACLEUSESSCNSINCONTROLFILESDATAFILEHEADERSANDREDORECORDS.EVERYREDOLOGFILEHASBOTHALOGSEQUENCENUMBERANDLOWANDHIGHSCN.THELOWSCNRECORDSTHELOWESTSCNRECORDEDINTHELOGFILEWHILETHEHIGHSCNRECORDSTHEHIGHESTSCNINTHELOGFILE.4.WHATISDATABASEINCARNATION?DATABASEINCARNATIONISEFFECTIVELYANEWVERSIONOFTHEDATABASETHATHAPPENSWHENYOURESETTHEONLINEREDOLOGSUSINGALTERDATABASEOPENRESETLOGS.DATABASEINCARNATIONFALLSINTOFOLLOWINGCATEGORYCURRENT,PARENT,ANCESTORANDSIBLINGI)CURRENTINCARNATION:THEDATABASEINCARNATIONINWHICHTHEDATABASEISCURRENTLYGENERATINGREDO.II)PARENTINCARNATION:THEDATABASEINCARNATIONFROMWHICHTHECURRENTINCARNATIONBRANCHEDFOLLOWINGANOPENRESETLOGSOPERATION.III)ANCESTORINCARNATION:THEPARENTOFTHEPARENTINCARNATIONISANANCESTORINCARNATION.ANYPARENTOFANANCESTORINCARNATIONISALSOANANCESTORINCARNATION.IV)SIBLINGINCARNATION:TWOINCARNATIONSTHATSHAREACOMMONANCESTORARESIBLINGINCARNATIONSIFNEITHERONEISANANCESTOROFTHEOTHER.5.HOWTOVIEWINCARNATIONHISTORYOFDATABASE?USINGSQL>SELECT*FROMV$DATABASE_INCARNATIONUSINGRMAN>LISTINCARNATIONHOWEVER,YOUCANUSETHERESETDATABASETOINCARNATIONCOMMANDTOSPECIFYTHATSCNSARETOBEINTERPRETEDINTHEFRAMEOFREFERENCEOFANOTHERINCARNATION.FOREXAMPLEMYCURRENTDATABASEINCARNATIONIS3ANDNOWIHAVEUSEDFLASHBACKDATABASETOSCN3000THENSCN3000WILLBESEARCHINCURRENTINCARNATIONWHICHIS3.HOWEVERIFIWANTTOGETBACKTOSCN3000OFINCARNATION2THENIHAVETOUSE,RMAN>RESETDATABASETOINCARNATION2RMAN>RECOVERDATABASETOSCN30001. GIVEONEMETHODFORTRANSFERRINGATABLEFROMONESCHEMATOANOTHER?ANSWER:THEREARESEVERALPOSSIBLEMETHODS,EXPORTIMPORT,CREATETABLEASSELECT,ORCOPY.2.WHATISTHEPURPOSEOFTHEIMPORTOPTIONIGNORE?WHATISIT?SDEFAULTSETTING?LEVEL:LOWEXPECTEDANSWER:THEIMPORTIGNOREOPTIONTELLSIMPORTTOIGNOREALREADYEXISTSERRORS.IFITISNOTSPECIFIEDTHETABLESTHATALREADYEXISTWILLBESKIPPED.IFITISSPECIFIED,THEERRORISIGNOREDANDTHETABLESDATAWILLBEINSERTED.THEDEFAULTVALUEISN.3.YOUHAVEAROLLBACKSEGMENTINAVERSION7.2DATABASETHATHASEXPANDEDBEYONDOPTIMAL,HOWCANITBERESTOREDTOOPTIMAL?LEVEL:LOWEXPECTEDANSWER:USETHEALTERTABLESPACE..SHRINKCOMMAND.4.IFTHEDEFAULTANDTEMPORARYTABLESPACECLAUSESARELEFTOUTOFACREATEUSERCOMMANDWHATHAPPENS?ISTHISBADORGOOD?WHY?LEVEL:LOWEXPECTEDANSWER:THEUSERISASSIGNEDTHESYSTEMTABLESPACEASADEFAULTANDTEMPORARYTABLESPACE.THISISBADBECAUSEITCAUSESUSEROBJECTSANDTEMPORARYSEGMENTSTOBEPLACEDINTOTHESYSTEMTABLESPACERESULTINGINFRAGMENTATIONANDIMPROPERTABLEPLACEMENT(ONLYDATADICTIONARYOBJECTSANDTHESYSTEMROLLBACKSEGMENTSHOULDBEINSYSTEM).5.WHATARESOMEOFTHEORACLEPROVIDEDPACKAGESTHATDBASSHOULDBEAWAREOF?LEVEL:INTERMEDIATETOHIGHEXPECTEDANSWER:ORACLEPROVIDESANUMBEROFPACKAGESINTHEFORMOFTHEDBMS_PACKAGESOWNEDBYTHESYSUSER.THEPACKAGESUSEDBYDBASMAYINCLUDE:DBMS_SHARED_POOL,DBMS_UTILITY,DBMS_SQL,DBMS_DDL,DBMS_SESSION,DBMS_OUTPUTANDDBMS_SNAPSHOT.THEYMAYALSOTRYTOANSWERWITHTHEUTL*.SQLORCAT*.SQLSERIESOFSQLPROCEDURES.THESECANBEVIEWEDASEXTRACREDITBUTAREN?TPARTOFTHEANSWER.6.WHATHAPPENSIFTHECONSTRAINTNAMEISLEFTOUTOFACONSTRAINTCLAUSE?LEVEL:LOWEXPECTEDANSWER:THEORACLESYSTEMWILLUSETHEDEFAULTNAMEOFSYS_CXXXXWHEREXXXXISASYSTEMGENERATEDNUMBER.THISISBADSINCEITMAKESTRACKINGWHICHTABLETHECONSTRAINTBELONGSTOORWHATTHECONSTRAINTDOESHARDER.7.WHATHAPPENSIFATABLESPACECLAUSEISLEFTOFFOFAPRIMARYKEYCONSTRAINTCLAUSE?LEVEL:LOWEXPECTEDANSWER:THISRESULTSINTHEINDEXTHATISAUTOMATICALLYGENERATEDBEINGPLACEDINTHEN4/30/2015 EssentialDBAInterviewQuestions|TechnologyLearninghttps://techknowledgeworld.wordpress.com/2014/11/20/essentialdbainterviewquestions/ 8/98USERSDEFAULTTABLESPACE.SINCETHISWILLUSUALLYBETHESAMETABLESPACEASTHETABLEISBEINGCREATEDIN,THISCANCAUSESERIOUSPERFORMANCEPROBLEMS.8.WHATISTHEPROPERMETHODFORDISABLINGANDREENABLINGAPRIMARYKEYCONSTRAINT?LEVEL:INTERMEDIATEEXPECTEDANSWER:YOUUSETHEALTERTABLECOMMANDFORBOTH.HOWEVER,FORTHEENABLECLAUSEYOUMUSTSPECIFYTHEUSINGINDEXANDTABLESPACECLAUSEFORPRIMARYKEYS.9.WHATHAPPENSIFAPRIMARYKEYCONSTRAINTISDISABLEDANDTHENENABLEDWITHOUTFULLYSPECIFYINGTHEINDEXCLAUSE?LEVEL:INTERMEDIATEEXPECTEDANSWER:THEINDEXISCREATEDINTHEUSER?SDEFAULTTABLESPACEANDALLSIZINGINFORMATIONISLOST.ORACLEDOESN?TSTORETHISINFORMATIONASAPARTOFTHECONSTRAINTDEFINITION,BUTONLYASPARTOFTHEINDEXDEFINITION,WHENTHECONSTRAINTWASDISABLEDTHEINDEXWASDROPPEDANDTHEINFORMATIONISGONE.10.(ONUNIX)WHENSHOULDMORETHANONEDBWRITERPROCESSBEUSED?HOWMANYSHOULDBEUSED?LEVEL:HIGHEXPECTEDANSWER:IFTHEUNIXSYSTEMBEINGUSEDISCAPABLEOFASYNCHRONOUSIOTHENONLYONEISREQUIRED,IFTHESYSTEMISNOTCAPABLEOFASYNCHRONOUSIOTHENUPTOTWICETHENUMBEROFDISKSUSEDBYORACLENUMBEROFDBWRITERSSHOULDBESPECIFIEDBYUSEOFTHEDB_WRITERSINITIALIZATIONPARAMETER.11.YOUAREUSINGHOTBACKUPWITHOUTBEINGINARCHIVELOGMODE,CANYOURECOVERINTHEEVENTOFAFAILURE?WHYORWHYNOT?LEVEL:HIGHEXPECTEDANSWER:YOUCAN?TUSEHOTBACKUPWITHOUTBEINGINARCHIVELOGMODE.SONO,YOUCOULDN?TRECOVER.12.WHATCAUSESTHESNAPSHOTTOOOLDERROR?HOWCANTHISBEPREVENTEDORMITIGATED?LEVEL:INTERMEDIATEEXPECTEDANSWER:THISISCAUSEDBYLARGEORLONGRUNNINGTRANSACTIONSTHATHAVEEITHERWRAPPEDONTOTHEIROWNROLLBACKSPACEORHAVEHADANOTHERTRANSACTIONWRITEONPARTOFTHEIRROLLBACKSPACE.THISCANBEPREVENTEDORMITIGATEDBYBREAKINGTHETRANSACTIONINTOASETOFSMALLERTRANSACTIONSORINCREASINGTHESIZEOFTHEROLLBACKSEGMENTSANDTHEIREXTENTS.13.HOWCANYOUTELLIFADATABASEOBJECTISINVALID?LEVEL:LOWEXPECTEDANSWER:BYCHECKINGTHESTATUSCOLUMNOFTHEDBA_,ALL_ORUSER_OBJECTSVIEWS,DEPENDINGUPONWHETHERYOUOWNORONLYHAVEPERMISSIONONTHEVIEWORAREUSINGADBAACCOUNT.14.AUSERISGETTINGANORA00942ERRORYETYOUKNOWYOUHAVEGRANTEDTHEMPERMISSIONONTHETABLE,WHATELSESHOULDYOUCHECK?LEVEL:LOWEXPECTEDANSWER:YOUNEEDTOCHECKTHATTHEUSERHASSPECIFIEDTHEFULLNAMEOFTHEOBJECT(SELECTEMPIDFROMSCOTT.EMPINSTEADOFSELECTEMPIDFROMEMP)ORHASASYNONYMTHATPOINTSTOTHEOBJECT(CREATESYNONYMEMPFORSCOTT.EMP)15.ADEVELOPERISTRYINGTOCREATEAVIEWANDTHEDATABASEWON?TLETHIM.HEHASTHEDEVELOPERROLEWHICHHASTHECREATEVIEWSYSTEMPRIVILEGEANDSELECTGRANTSONTHETABLESHEISUSING,WHATISTHEPROBLEM?LEVEL:INTERMEDIATEEXPECTEDANSWER:YOUNEEDTOVERIFYTHEDEVELOPERHASDIRECTGRANTSONALLTABLESUSEDINTHEVIEW.YOUCAN?TCREATEASTOREDOBJECTWITHGRANTSGIVENTHROUGHVIEWS.16.IFYOUHAVEANEXAMPLETABLE,WHATISTHEBESTWAYTOGETSIZINGDATAFORTHEPRODUCTIONTABLEIMPLEMENTATION?LEVEL:INTERMEDIATEEXPECTEDANSWER:THEBESTWAYISTOANALYZETHETABLEANDTHENUSETHEDATAPROVIDEDINTHEDBA_TABLESVIEWTOGETTHEAVERAGEROWLENGTHANDOTHERPERTINENTDATAFORTHECALCULATION.THEQUICKANDDIRTYWAYISTOLOOKATTHENUMBEROFBLOCKSTHETABLEISACTUALLYUSINGANDRATIOTHENUMBEROFROWSINTHETABLETOITSNUMBEROFBLOCKSAGAINSTTHENUMBEROFEXPECTEDROWS.17.HOWCANYOUFINDOUTHOWMANYUSERSARECURRENTLYLOGGEDINTOTHEDATABASE?HOWCANYOUFINDTHEIROPERATINGSYSTEMID?LEVEL:HIGHEXPECTEDANSWER:THEREARESEVERALWAYS.ONEISTOLOOKATTHEV$SESSIONORV$PROCESSVIEWS.ANOTHERWAYISTOCHECKTHECURRENT_LOGINSPARAMETERINTHEV$SYSSTATVIEW.ANOTHERIFYOUAREONUNIXISTODOAPSEF|GREPORACLE|WCL?COMMAND,BUTTHISONLYWORKSAGAINSTASINGLEINSTANCEINSTALLATION.18.AUSERSELECTSFROMASEQUENCEANDGETSBACKTWOVALUES,HISSELECTIS:SELECTPK_SEQ.NEXTVALFROMDUALWHATISTHEPROBLEM?LEVEL:INTERMEDIATEEXPECTEDANSWER:SOMEHOWTWOVALUESHAVEBEENINSERTEDINTOTHEDUALTABLE.THISTABLEISASINGLEROW,SINGLECOLUMNTABLETHATSHOULDONLYHAVEONEVALUEINIT.19.HOWCANYOUDETERMINEIFANINDEXNEEDSTOBEDROPPEDANDREBUILT?LEVEL:INTERMEDIATEEXPECTEDANSWER:RUNTHEANALYZEINDEXCOMMANDONTHEINDEXTOVALIDATEITSSTRUCTUREANDTHENCALCULATETHERATIOOFLF_BLK_LEN/LF_BLK_LEN+BR_BLK_LENANDIFITISN?TNEAR1.0(I.E.GREATERTHAN0.7ORSO)THENTHEINDEXSHOULDBEREBUILT.ORIFTHERATIOBR_BLK_LEN/LF_BLK_LEN+BR_BLK_LENISNEARING0.3.4/30/2015 EssentialDBAInterviewQuestions|TechnologyLearninghttps://techknowledgeworld.wordpress.com/2014/11/20/essentialdbainterviewquestions/ 9/981. Atablespacehasatablewith30extentsinit.Isthisbad?Whyorwhynot.Level:IntermediateExpectedanswer:Multipleextentsinandofthemselvesaren?tbad.Howeverifyoualsohavechainedrowsthiscanhurtperformance.2. HowdoyousetuptablespacesduringanOracleinstallation?Level:LowExpectedanswer:YoushouldalwaysattempttousetheOracleFlexibleArchitecturestandardoranotherpartitioningschemetoensureproperseparationofSYSTEM,ROLLBACK,REDOLOG,DATA,TEMPORARYandINDEXsegments.3. YouseemultiplefragmentsintheSYSTEMtablespace,whatshouldyoucheckfirst?Level:LowExpectedanswer:Ensurethatusersdon?thavetheSYSTEMtablespaceastheirTEMPORARYorDEFAULTtablespaceassignmentbycheckingtheDBA_USERSview.4. WhataresomeindicationsthatyouneedtoincreasetheSHARED_POOL_SIZEparameter?Level:IntermediateExpectedanswer:Poordatadictionaryorlibrarycachehitratios,gettingerrorORA04031.Anotherindicationissteadilydecreasingperformancewithallothertuningparametersthesame.5. Whatisthegeneralguidelineforsizingdb_block_sizeanddb_multi_block_readforanapplicationthatdoesmanyfulltablescans?Level:HighExpectedanswer:Oraclealmostalwaysreadsin64kchunks.Thetwoshouldhaveaproductequalto64oramultipleof64.6. Whatisthefastestquerymethodforatable?Level:IntermediateExpectedanswer:Fetchbyrowid7. ExplaintheuseofTKPROF?WhatinitializationparametershouldbeturnedontogetfullTKPROFoutput?Level:HighExpectedanswer:ThetkproftoolisatuningtoolusedtodeterminecpuandexecutiontimesforSQLstatements.Youuseitbyfirstsettingtimed_statisticstotrueintheinitializationfileandthenturningontracingforeithertheentiredatabaseviathesql_traceparameterorforthesessionusingtheALTERSESSIONcommand.Oncethetracefileisgeneratedyourunthetkproftoolagainstthetracefileandthenlookattheoutputfromthetkproftool.Thiscanalsobeusedtogenerateexplainplanoutput.8. Whenlookingatv$sysstatyouseethatsorts(disk)ishigh.Isthisbadorgood?IfbadHowdoyoucorrectit?Level:IntermediateExpectedanswer:Ifyougetexcessivedisksortsthisisbad.Thisindicatesyouneedtotunethesortareaparametersintheinitializationfiles.ThemajorsortareparameteristheSORT_AREA_SIZeparameter.9. Whenshouldyouincreasecopylatches?Whatparameterscontrolcopylatches?Level:highExpectedanswer:Whenyougetexcessivecontentionforthecopylatchesasshownbytheredocopylatchhitratio.YoucanincreasecopylatchesviatheinitializationparameterLOG_SIMULTANEOUS_COPIEStotwicethenumberofCPUsonyoursystem.10. Wherecanyougetalistofallinitializationparametersforyourinstance?Howaboutanindicationiftheyaredefaultsettingsorhavebeenchanged?Level:LowExpectedanswer:Youcanlookintheinit.orafileforanindicationofmanuallysetparameters.Forallparameters,theirvalueandwhetherornotthecurrentvalueisthedefaultvalue,lookinthev$parameterview.11. Describehitratioasitpertainstothedatabasebuffers.Whatisthedifferencebetweeninstantaneousandcumulativehitratioandwhichshouldbeusedfortuning?Level:IntermediateExpectedanswer:Thehitratioisameasureofhowmanytimesthedatabasewasabletoreadavaluefromthebuffersverseshowmanytimesithadtorereadadatavaluefromthedisks.Avaluegreaterthan8090%isgood,lesscouldindicateproblems.Ifyousimplytaketheratioofexistingparametersthiswillbeacumulativevaluesincethedatabasestarted.Ifyoudoacomparisonbetweenpairsofreadingsbasedonsomearbitrarytimespan,thisistheinstantaneousratioforthattimespan.Generallyspeakinganinstantaneousreadinggivesmorevaluabledatasinceitwilltellyouwhatyourinstanceisdoingforthetimeitwasgeneratedover.12. Discussrowchaining,howdoesithappen?Howcanyoureduceit?Howdoyoucorrectit?Level:highExpectedanswer:RowchainingoccurswhenaVARCHAR2valueisupdatedandthelengthofthenewvalueislongerthantheoldvalueandwon?tfitintheremainingblockspace.Thisresultsintherowchainingtoanotherblock.Itcanbereducedbysettingthestorageparametersonthetabletoappropriatevalues.Itcanbecorrectedbyexportandimportoftheeffectedtable.13. Whenlookingattheestateventsreportyouseethatyouaregettingbusybufferwaits.Isthisbad?Howcanyoufindwhatiscausingit?Level:highExpectedanswer:Bufferbusywaitscouldindicatecontentioninredo,rollbackordatablocks.Youneedtocheckthev$waitstatviewtoseewhatareasarecausingtheproblem.Thevalueofthecountcolumntellswheretheproblemis,theclasscolumntellsyouTUNINGINTERVIEWQUESTIONS4/30/2015 EssentialDBAInterviewQuestions|TechnologyLearninghttps://techknowledgeworld.wordpress.com/2014/11/20/essentialdbainterviewquestions/ 10/98withwhat.UNDOisrollbacksegments,DATAisdatabasebuffers.14. Ifyouseecontentionforlibrarycacheshowcanyoufixit?Level:IntermediateExpectedanswer:Increasethesizeofthesharedpool.15. Ifyouseestatisticsthatdealwithundowhataretheyreallytalkingabout?Level:IntermediateExpectedanswer:Rollbacksegmentsandassociatedstructures.16. Ifatablespacehasadefaultpctincreaseofzerowhatwillthiscause(inrelationshiptothesmonprocess)?Level:HighExpectedanswer:TheSMONprocesswon?tautomaticallycoalesceitsfreespacefragments.17. Ifatablespaceshowsexcessivefragmentationwhataresomemethodstodefragmentthetablespace?(7.1,7.2and7.3only)Level:HighExpectedanswer:InOracle7.0to7.2Theuseofthealtersessionseteventsimmediatetracenamecoalescelevelts#'?commandistheeasiestwaytodefragmentcontiguousfreespacefragmentation.Thets#parametercorrespondstothets#valuefoundinthets$SYStable.Inversion7.3the?altertablespacecoalesce?isbest.Ifthefreespaceisn?tcontiguousthenexport,dropandimportofthetablespacecontentsmaybetheonlywaytoreclaimnoncontiguousfreespace.18. Howcanyoutellifatablespacehasexcessivefragmentation?Level:IntermediateIfaselectagainstthedba_free_spacetableshowsthatthecountofatablespacesextentsisgreaterthanthecountofitsdatafiles,thenitisfragmented.19. Youseethefollowingonastatusreport:redologspacerequests23redologspacewaittime0Isthissomethingtoworryabout?Whatifredologspacewaittimeishigh?Howcanyoufixthis?Level:IntermediateExpectedanswer:Sincethewaittimeiszero,no.Ifthewaittimewashighitmightindicateaneedformoreorlargerredologs.20. Whatcancauseahighvalueforrecursivecalls?Howcanthisbefixed?Level:HighExpectedanswer:Ahighvalueforrecursivecallsiscausebyimpropercursorusage,excessivedynamicspacemanagementactions,andorexcessivestatementreparses.YouneedtodeterminethecauseandcorrectitByeitherrelinkingapplicationstoholdcursors,useproperspacemanagementtechniques(properstorageandsizing)orensurerepeatqueriesareplacedinpackagesforproperreuse.21. Ifyouseeapinhitratiooflessthan0.8intheestatlibrarycachereportisthisaproblem?Ifso,howdoyoufixit?Level:IntermediateExpectedanswer:Thisindicatethatthesharedpoolmaybetoosmall.Increasethesharedpoolsize.22. Ifyouseethevalueforreloadsishighintheestatlibrarycachereportisthisamatterforconcern?Level:IntermediateExpectedanswer:Yes,youshouldstriveforzeroreloadsifpossible.Ifyouseeexcessivereloadsthenincreasethesizeofthesharedpool.23. Youlookatthedba_rollback_segsviewandseethatthereisalargenumberofshrinksandtheyareofrelativelysmallsize,isthisaproblem?Howcanitbefixedifitisaproblem?Level:HighExpectedanswer:Alargenumberofsmallshrinksindicatesaneedtoincreasethesizeoftherollbacksegmentextents.Ideallyyoushouldhavenoshrinksorasmallnumberoflargeshrinks.Tofixthisjustincreasethesizeoftheextentsandadjustoptimalaccordingly.24. Youlookatthedba_rollback_segsviewandseethatyouhavealargenumberofwrapsisthisaproblem?Level:HighExpectedanswer:Alargenumberofwrapsindicatesthatyourextentsizeforyourrollbacksegmentsareprobablytoosmall.Increasethesizeofyourextentstoreducethenumberofwraps.Youcanlookattheaveragetransactionsizeinthesameviewtogettheinformationontransactionsize.25. Inasystemwithanaverageof40concurrentusersyougetthefollowingfromaqueryonrollbackextents:ROLLBACKCUREXTENTSR0111R028R0312R049SYSTEM4Youhaveroomforeachtogrowby20moreextentseach.Isthereaproblem?Shouldyoutakeanyaction?Level:IntermediateExpectedanswer:Nothereisnotaproblem.Youhave40extentsshowingandanaverageof40concurrentusers.Sincethereisplentyofroomtogrownoactionisneeded.26. Youseemultipleextentsinthetemporarytablespace.Isthisaproblem?Level:IntermediateExpectedanswer:Aslongastheyareallthesamesizethisisn?taproblem.Infact,itcanevenimproveperformancesinceOraclewon?thavetocreateanewextentwhenauserneedsone.INSTALLATION/CONFIGURATIONINTERVIEWQUESTIONS4/30/2015 EssentialDBAInterviewQuestions|TechnologyLearninghttps://techknowledgeworld.wordpress.com/2014/11/20/essentialdbainterviewquestions/ 11/981. DefineOFA.Level:LowExpectedanswer:OFAstandsforOptimalFlexibleArchitecture.ItisamethodofplacingdirectoriesandfilesinanOraclesystemsothatyougetthemaximumflexibilityforfuturetuningandfileplacement.2. Howdoyousetupyourtablespaceoninstallation?Level:LowExpectedanswer:Theanswerhereshouldshowanunderstandingofseparationofredoandrollback,dataandindexesandisolationosSYSTEMtablesfromothertables.Anexamplewouldbetospecifythatatleast7disksshouldbeusedforanOracleinstallationsothatyoucanplaceSYSTEMtablespaceonone,redologsontwo(mirroredredologs)theTEMPORARYtablespaceonanother,ROLLBACKtablespaceonanotherandstillhavetwoforDATAandINDEXES.Theyshouldindicatehowtheywillhandlearchivelogsandexportsaswell.Aslongastheyhavealogicalplanforcombiningorfurtherseparationmoreorlessdiskscanbespecified.3. WhatshouldbedonepriortoinstallingOracle(fortheOSandthedisks)?Level:LowExpectedAnswer:adjustkernelparametersorOStuningparametersinaccordancewithinstallationguide.Besureenoughcontiguousdiskspaceisavailable.4. YouhaveinstalledOracleandyouarenowsettinguptheactualinstance.Youhavebeenwaitinganhourfortheinitializationscripttofinish,whatshouldyoucheckfirsttodetermineifthereisaproblem?Level:IntermediatetohighExpectedAnswer:Checktomakesurethatthearchiverisn?tstuck.Ifarchiveloggingisturnedonduringinstallalargenumberoflogswillbecreated.ThiscanfillupyourarchivelogdestinationcausingOracletostoptowaitformorespace.5. WhenconfiguringSQLNETontheserverwhatfilesmustbesetup?Level:IntermediateExpectedanswer:INITIALIZATIONfile,TNSNAMES.ORAfile,SQLNET.ORAfile6. WhenconfiguringSQLNETontheclientwhatfilesneedtobesetup?Level:IntermediateExpectedanswer:SQLNET.ORA,TNSNAMES.ORA7. WhatmustbeinstalledwithODBContheclientinorderforittoworkwithOracle?Level:IntermediateExpectedanswer:SQLNETandPROTOCOL(forexample:TCPIPadapter)layersofthetransportprograms.8. YouhavejuststartedanewinstancewithalargeSGAonabusyexistingserver.Performanceisterrible,whatshouldyoucheckfor?Level:IntermediateExpectedanswer:ThefirstthingtocheckwithalargeSGAisthatitisn?tbeingswappedout.9. WhatOSusershouldbeusedforthefirstpartofanOracleinstallation(onUNIX)?Level:lowExpectedanswer:Youmustuserootfirst.10. WhenshouldthedefaultvaluesforOracleinitializationparametersbeusedasis?Level:LowExpectedanswer:Never11. Howmanycontrolfilesshouldyouhave?Whereshouldtheybelocated?Level:LowExpectedanswer:Atleast2onseparatediskspindles.Besuretheysayonseparatedisks,notjustfilesystems.12. Howmanyredologsshouldyouhaveandhowshouldtheybeconfiguredformaximumrecoverability?Level:IntermediateExpectedanswer:Youshouldhaveatleastthreegroupsoftworedologswiththetwologseachonaseparatediskspindle(mirroredbyOracle).TheredologsshouldnotbeonrawdevicesonUNIXifitcanbeavoided.13. Youhaveasimpleapplicationwithnohottables(i.e.uniformIOandaccessrequirements).HowmanydisksshouldyouhaveassumingstandardlayoutforSYSTEM,USER,TEMPandROLLBACKtablespaces?Expectedanswer:Atleast7,seediskconfigurationanswerabove.1. Describethirdnormalform?Level:LowExpectedanswer:Somethinglike:Inthirdnormalformallattributesinanentityarerelatedtotheprimarykeyandonlytotheprimarykey2. Isthefollowingstatementtrueorfalse:AllrelationaldatabasesmustbeinthirdnormalformWhyorwhynot?Level:IntermediateExpectedanswer:False.While3NFisgoodforlogicaldesignmostdatabases,iftheyhavemorethanjustafewtables,willnotperformwellusingfull3NF.Usuallysomeentitieswillbedenormalizedinthelogicaltophysicaltransferprocess.3. WhatisanERD?Level:LowDATAMODELERINTERVIEWQUESTIONS4/30/2015 EssentialDBAInterviewQuestions|TechnologyLearninghttps://techknowledgeworld.wordpress.com/2014/11/20/essentialdbainterviewquestions/ 12/98Expectedanswer:AnERDisanEntityRelationshipDiagram.Itisusedtoshowtheentitiesandrelationshipsforadatabaselogicalmodel.4. Whyarerecursiverelationshipsbad?Howdoyouresolvethem?Level:IntermediateArecursiverelationship(onewhereatablerelatestoitself)isbadwhenitisahardrelationship(i.e.neithersideisamaybotharemust)asthiscanresultinitnotbeingpossibletoputinatoporperhapsabottomofthetable(forexampleintheEMPLOYEEtableyoucouldn?tputinthePRESIDENTofthecompanybecausehehasnoboss,orthejuniorjanitorbecausehehasnosubordinates).Thesetypeofrelationshipsareusuallyresolvedbyaddingasmallintersectionentity.5. Whatdoesahardonetoonerelationshipmean(onewheretherelationshiponbothendsismust)?Level:LowtointermediateExpectedanswer:Thismeansthetwoentitiesshouldprobablybemadeintooneentity.6. Howshouldamanytomanyrelationshipbehandled?Level:IntermediateExpectedanswer:Byaddinganintersectionentitytable7. Whatisanartificial(derived)primarykey?Whenshouldanartificial(orderived)primarykeybeused?Level:IntermediateExpectedanswer:Aderivedkeycomesfromasequence.Usuallyitisusedwhenaconcatenatedkeybecomestoocumbersometouseasaforeignkey.8. Whenshouldyouconsiderdenormalization?Level:IntermediateExpectedanswer:Wheneverperformanceanalysisindicatesitwouldbebeneficialtodosowithoutcompromisingdataintegrity.ORACLECONCEPTSANDARCHITECTUREDATABASESTRUCTURES1. WhatarethecomponentsofphysicaldatabasestructureofOracledatabase?Oracledatabaseiscomprisedofthreetypesoffiles.Oneormoredatafiles,twoaremoreredologfiles,andoneormorecontrolfiles.2. WhatarethecomponentsoflogicaldatabasestructureofOracledatabase?Therearetablespacesanddatabasesschemaobjects.3. Whatisatablespace?AdatabaseisdividedintoLogicalStorageUnitcalledtablespaces.Atablespaceisusedtogroupedrelatedlogicalstructurestogether.4. WhatisSYSTEMtablespaceandwhenisitcreated?EveryOracledatabasecontainsatablespacenamedSYSTEM,whichisautomaticallycreatedwhenthedatabaseiscreated.TheSYSTEMtablespacealwayscontainsthedatadictionarytablesfortheentiredatabase.7. WhatareSchemaObjects?Schemaobjectsarethelogicalstructuresthatdirectlyrefertothedatabasesdata.Schemaobjectsincludetables,views,sequences,synonyms,indexes,clusters,databasetriggers,procedures,functionspackagesanddatabaselinks.8. Canobjectsofthesameschemaresideindifferenttablespaces?Yes.9. Canatablespaceholdobjectsfromdifferentschemes?Yes.10. WhatisOracletable?AtableisthebasicunitofdatastorageinanOracledatabase.Thetablesofadatabaseholdalloftheuseraccessibledata.Tabledataisstoredinrowsandcolumns.11. WhatisanOracleview?Aviewisavirtualtable.Everyviewhasaqueryattachedtoit.(ThequeryisaSELECTstatementthatidentifiesthecolumnsandrowsofthetable(s)theviewuses.)12. Doaviewcontaindata?Viewsdonotcontainorstoredata.13. Canaviewbasedonanotherview?4/30/2015 EssentialDBAInterviewQuestions|TechnologyLearninghttps://techknowledgeworld.wordpress.com/2014/11/20/essentialdbainterviewquestions/ 13/98Yes.14. Whataretheadvantagesofviews?Provideanadditionalleveloftablesecurity,byrestrictingaccesstoapredeterminedsetofrowsandcolumnsofatable.Hidedatacomplexity.Simplifycommandsfortheuser.Presentthedatainadifferentperspectivefromthatofthebasetable.Storecomplexqueries.15. WhatisanOraclesequence?Asequencegeneratesaseriallistofuniquenumbersfornumericalcolumnsofadatabasestables.16. Whatisasynonym?Asynonymisanaliasforatable,view,sequenceorprogramunit.17. Whatarethetypesofsynonyms?Therearetwotypesofsynonymsprivateandpublic.18. Whatisaprivatesynonym?Onlyitsownercanaccessaprivatesynonym.19. Whatisapublicsynonym?Anydatabaseusercanaccessapublicsynonym.20. Whataresynonymsusedfor?Masktherealnameandownerofanobject.ProvidepublicaccesstoanobjectProvidelocationtransparencyfortables,viewsorprogramunitsofaremotedatabase.SimplifytheSQLstatementsfordatabaseusers.21. WhatisanOracleindex?Anindexisanoptionalstructureassociatedwithatabletohavedirectaccesstorows,whichcanbecreatedtoincreasetheperformanceofdataretrieval.Indexcanbecreatedononeormorecolumnsofatable.22. Howaretheindexupdates?IndexesareautomaticallymaintainedandusedbyOracle.Changestotabledataareautomaticallyincorporatedintoallrelevantindexes.23. Whatareclusters?Clustersaregroupsofoneormoretablesphysicallystorestogethertosharecommoncolumnsandareoftenusedtogether.24. Whatisclusterkey?Therelatedcolumnsofthetablesinaclusterarecalledtheclusterkey.25. Whatisindexcluster?Aclusterwithanindexontheclusterkey.26. Whatishashcluster?Arowisstoredinahashclusterbasedontheresultofapplyingahashfunctiontotherowsclusterkeyvalue.Allrowswiththesamehashkeyvaluearestorestogetherondisk.FollowFollowTechnologyLearningBuildawebsitewithWordPress.com4/30/2015 EssentialDBAInterviewQuestions|TechnologyLearninghttps://techknowledgeworld.wordpress.com/2014/11/20/essentialdbainterviewquestions/ 14/9827. Whencanhashclusterused?Hashclustersarebetterchoicewhenatableisoftenqueriedwithequalityqueries.Forsuchqueriesthespecifiedclusterkeyvalueishashed.Theresultinghashkeyvaluepointsdirectlytotheareaondiskthatstoresthespecifiedrows.28. Whatisdatabaselink?Adatabaselinkisanamedobjectthatdescribesapathfromonedatabasetoanother.29. Whatarethetypesofdatabaselinks?Privatedatabaselink,publicdatabaselink&networkdatabaselink.30. Whatisprivatedatabaselink?Privatedatabaselinkiscreatedonbehalfofaspecificuser.AprivatedatabaselinkcanbeusedonlywhentheownerofthelinkspecifiesaglobalobjectnameinaSQLstatementorinthedefinitionoftheownersviewsorprocedures.31. Whatispublicdatabaselink?PublicdatabaselinkiscreatedforthespecialusergroupPUBLIC.ApublicdatabaselinkcanbeusedwhenanyuserintheassociateddatabasespecifiesaglobalobjectnameinaSQLstatementorobjectdefinition.32. Whatisnetworkdatabaselink?Networkdatabaselinkiscreatedandmanagedbyanetworkdomainservice.AnetworkdatabaselinkcanbeusedwhenanyuserofanydatabaseinthenetworkspecifiesaglobalobjectnameinaSQLstatementorobjectdefinition.33. Whatisdatablock?Oracledatabasesdataisstoredindatablocks.Onedatablockcorrespondstoaspecificnumberofbytesofphysicaldatabasespaceondisk.34. Howtodefinedatablocksize?AdatablocksizeisspecifiedforeachOracledatabasewhenthedatabaseiscreated.AdatabaseusersandallocatedfreedatabasespaceinOracledatablocks.Blocksizeisspecifiedininit.orafileandcannotbechangedlatter.35. Whatisrowchaining?Incircumstances,allofthedataforarowinatablemaynotbeabletofitinthesamedatablock.Whenthisoccurs,thedatafortherowisstoredinachainofdatablock(oneormore)reservedforthatsegment.36. Whatisanextent?Anextentisaspecificnumberofcontiguousdatablocks,obtainedinasingleallocationandusedtostoreaspecifictypeofinformation.37. Whatisasegment?Asegmentisasetofextentsallocatedforacertainlogicalstructure.38. Whatarethedifferenttypesofsegments?Datasegment,indexsegment,rollbacksegmentandtemporarysegment.39. Whatisadatasegment?Eachnonclusteredtablehasadatasegment.Allofthetablesdataisstoredintheextentsofitsdatasegment.Eachclusterhasadatasegment.Thedataofeverytableintheclusterisstoredintheclustersdatasegment.40. Whatisanindexsegment?Eachindexhasanindexsegmentthatstoresallofitsdata.41. Whatisrollbacksegment?Adatabasecontainsoneormorerollbacksegmentstotemporarilystoreundoinformation.42. Whataretheusesofrollbacksegment?Togeneratereadconsistentdatabaseinformationduringdatabaserecoveryandtorollbackuncommittedtransactionsbytheusers.4/30/2015 EssentialDBAInterviewQuestions|TechnologyLearninghttps://techknowledgeworld.wordpress.com/2014/11/20/essentialdbainterviewquestions/ 15/9843. Whatisatemporarysegment?TemporarysegmentsarecreatedbyOraclewhenaSQLstatementneedsatemporaryworkareatocompleteexecution.Whenthestatementfinishesexecution,thetemporarysegmentextentsarereleasedtothesystemforfutureuse.44. Whatisadatafile?EveryOracledatabasehasoneormorephysicaldatafiles.Adatabasesdatafilescontainallthedatabasedata.Thedataoflogicaldatabasestructuressuchastablesandindexesisphysicallystoredinthedatafilesallocatedforadatabase.45. Whatarethecharacteristicsofdatafiles?Adatafilecanbeassociatedwithonlyonedatabase.Oncecreatedadatafilecantchangesize.Oneormoredatafilesformalogicalunitofdatabasestoragecalledatablespace.46. Whatisaredolog?Thesetofredologfilesforadatabaseiscollectivelyknownasthedatabaseredolog.47. Whatisthefunctionofredolog?Theprimaryfunctionoftheredologistorecordallchangesmadetodata.48. Whatistheuseofredologinformation?Theinformationinaredologfileisusedonlytorecoverthedatabasefromasystemormediafailurepreventsdatabasedatafrombeingwrittentoadatabasesdatafiles.49. Whatdoesacontrolfilecontains?DatabasenameNamesandlocationsofadatabasesfilesandredologfiles.Timestampofdatabasecreation.50. Whatistheuseofcontrolfile?WhenaninstanceofanOracledatabaseisstarted,itscontrolfileisusedtoidentifythedatabaseandredologfilesthatmustbeopenedfordatabaseoperationtoproceed.Itisalsousedindatabaserecovery.1. WhatisRMAN?RecoveryManager(RMAN)isautilitythatcanmanageyourentireOraclebackupandrecoveryactivities.WhichFilesmustbebackedup?DatabaseFiles(withRMAN)ControlFiles(withRMAN)OfflineRedologFiles(withRMAN)INIT.ORA(manually)PasswordFiles(manually)2. WhenyoutakeahotbackupputtingTablespaceinbeginbackupmode,OraclerecordsSCN#fromheaderofadatabasefile.WhathappenswhenyouissuehotbackupdatabaseinRMANatblocklevelbackup?HowdoesRMANmarktherecordthattheblockhasbeenbackedup?HowdoesRMANknowwhatblockswerebackedupsothatitdoesnthavetoscanthemagain?In11g,thereisOracleBlockChangeTrackingfeature.Onceenabledthisnew10gfeaturerecordsthemodifiedsincelastbackupandstoresthelogofitinablockchangetrackingfile.DuringbackupsRMANusesthelogfiletoidentifythespecificblocksthatmustbebackedup.ThisimprovesRMANsperformanceasitdoesnothavetoscanwholedatafilestodetectchangedblocks.LoggingofchangedblocksisperformedbytheCTRWprocesswhichisalsoresponsibleforwritingdatatotheblockchangetrackingfile.RMANusesSCNsontheblocklevelandthearchivedredologstoresolveanyinconsistenciesinthedatafilesfromahotbackup.WhatRMANdoesnotrequireistoputthetablespaceinBACKUPmode,thusfreezingtheSCNintheheader.Rather,RMANkeepsthisinformationineitheryourcontrolfilesorintheRMANrepository(i.e.,RecoveryCatalog).3. WhataretheArchitecturalcomponentsofRMAN?1.RMANexecutable2.Serverprocesses3.ChannelsRMANINTERVIEWQUESTIONS4/30/2015 EssentialDBAInterviewQuestions|TechnologyLearninghttps://techknowledgeworld.wordpress.com/2014/11/20/essentialdbainterviewquestions/ 16/984.Targetdatabase5.Recoverycatalogdatabase(optional)6.Mediamanagementlayer(optional)7.Backups,backupsets,andbackuppieces4. WhatareChannels?AchannelisanRMANserverprocessstartedwhenthereisaneedtocommunicatewithanI/Odevice,suchasadiskoratape.AchanneliswhatreadsandwritesRMANbackupfiles.ItisthroughtheallocationofchannelsthatyougovernI/Ocharacteristicssuchas:TypeofI/Odevicebeingreadorwrittento,eitheradiskoransbt_tapeNumberofprocessessimultaneouslyaccessinganI/OdeviceMaximumsizeoffilescreatedonI/OdevicesMaximumrateatwhichdatabasefilesarereadMaximumnumberoffilesopenatatime5. Whyisthecatalogoptional?BecauseRMANmanagesbackupandrecoveryoperations,itrequiresaplacetostorenecessaryinformationaboutthedatabase.RMANalwaysstoresthisinformationinthetargetdatabasecontrolfile.YoucanalsostoreRMANmetadatainarecoverycatalogschemacontainedinaseparatedatabase.Therecoverycatalogschemamustbestoredinadatabaseotherthanthetargetdatabase.6. WhatdoescompleteRMANbackupconsistof?Abackupofallorpartofyourdatabase.ThisresultsfromissuinganRMANbackupcommand.Abackupconsistsofoneormorebackupsets.7.WhatisaBackupset?AlogicalgroupingofbackupfilesthebackuppiecesthatarecreatedwhenyouissueanRMANbackupcommand.AbackupsetisRMANsnameforacollectionoffilesassociatedwithabackup.Abackupsetiscomposedofoneormorebackuppieces.8.WhatisaBackuppiece?AphysicalbinaryfilecreatedbyRMANduringabackup.Backuppiecesarewrittentoyourbackupmedium,whethertodiskortape.Theycontainblocksfromthetargetdatabasesdatafiles,archivedredologfiles,andcontrolfiles.WhenRMANconstructsabackuppiecefromdatafiles,thereareaseveralrulesthatitfollows:AdatafilecannotspanbackupsetsAdatafilecanspanbackuppiecesaslongasitstayswithinonebackupsetDatafilesandcontrolfilescancoexistinthesamebackupsetsArchivedredologfilesareneverinthesamebackupsetasdatafilesorcontrolfilesRMANistheonlytoolthatcanoperateonbackuppieces.IfyouneedtorestoreafilefromanRMANbackup,youmustuseRMANtodoit.Theresnowayforyoutomanuallyreconstructdatabasefilesfromthebackuppieces.YoumustuseRMANtorestorefilesfromabackuppiece.9. WhatarethebenefitsofusingRMAN?10. Incrementalbackupsthatonlycopydatablocksthathavechangedsincethelastbackup.2.Tablespacesarenotputinbackupmode,thusthereisnoextraredologgenerationduringonlinebackups.3.Detectionofcorruptblocksduringbackups.4.ParallelizationofI/Ooperations.5.Automaticloggingofallbackupandrecoveryoperations.6.Builtinreportingandlistingcommands.11.1. WhyandwhenshouldIbackupmydatabase?BackupandrecoveryisoneofthemostimportantaspectsofaDBAsjob.Ifyouloseyourcompanysdata,youcouldverywellloseyourjob.Hardwareandsoftwarecanalwaysbereplaced,butyourdatamaybeirreplaceable!Normallyonewouldscheduleahierarchyofdaily,weeklyandmonthlybackups,howeverconsultwithyourusersbeforedecidingonabackupschedule.Backupfrequencynormallydependsonthefollowingfactors:Rateofdatachange/transactionrateDatabaseavailability/Canyoushutdownforcoldbackups?Criticalityofthedata/ValueofthedatatothecompanyReadonlytablespaceneedsbackingupjustoncerightafteryoumakeitreadonlyGENERALBACKUPANDRECOVERYQUESTIONS4/30/2015 EssentialDBAInterviewQuestions|TechnologyLearninghttps://techknowledgeworld.wordpress.com/2014/11/20/essentialdbainterviewquestions/ 17/98IfyouarerunninginarchivelogmodeyoucanbackuppartsofadatabaseoveranextendedcycleofdaysIfarchiveloggingisenabledoneneedstobackuparchivedlogfilestimeouslytopreventdatabasefreezesEtc.Carefullyplanbackupretentionperiods.Ensureenoughbackupmedia(tapes)areavailableandthatoldbackupsareexpiredintimetomakemediaavailablefornewbackups.Offsitevaultingisalsohighlyrecommended.Frequentlytestyourabilitytorecoveranddocumentallpossiblescenarios.Remember,itsthelittlethingsthatwillgetyou.Mostfailedrecoveriesarearesultoforganizationalerrorsandmiscommunication.2. WhatstrategiesareavailableforbackingupanOracledatabase?ThefollowingmethodsarevalidforbackingupanOracledatabase:Export/ImportExportsarelogicaldatabasebackupsinthattheyextractlogicaldefinitionsanddatafromthedatabasetoafile.SeetheImport/ExportFAQformoredetails.ColdorOfflineBackupsshutthedatabasedownandbackupupALLdata,log,andcontrolfiles.HotorOnlineBackupsIfthedatabaseisavailableandinARCHIVELOGmode,setthetablespacesintobackupmodeandbackuptheirfiles.Alsoremembertobackupthecontrolfilesandarchivedredologfiles.RMANBackupswhilethedatabaseisofflineoronline,usethermanutilitytobackupthedatabase.Itisadvisabletousemorethanoneofthesemethodstobackupyourdatabase.Forexample,ifyouchoosetodoonlinedatabasebackups,alsocoveryourselfbydoingdatabaseexports.AlsotestALLbackupandrecoveryscenarioscarefully.Itisbettertobesafethansorry.Regardlessofyourstrategy,alsoremembertobackupallrequiredsoftwarelibraries,parameterfiles,passwordfiles,etc.IfyourdatabaseisinARCHIVELOGmode,youalsoneedtobackuparchivedlogfiles.3. Whatisthedifferencebetweenonlineandofflinebackups?Ahot(oronline)backupisabackupperformedwhilethedatabaseisopenandavailableforuse(readandwriteactivity).ExceptforOracleexports,onecanonlydoonlinebackupswhenthedatabaseisARCHIVELOGmode.Acold(oroffline)backupisabackupperformedwhilethedatabaseisofflineandunavailabletoitsusers.ColdbackupscanbetakenregardlessifthedatabaseisinARCHIVELOGorNOARCHIVELOGmode.Itiseasiertorestorefromofflinebackupsasnorecovery(fromarchivedlogs)wouldberequiredtomakethedatabaseconsistent.Nevertheless,onlinebackupsarelessdisruptiveanddontrequiredatabasedowntime.Pointintimerecovery(regardlessifyoudoonlineorofflinebackups)isonlyavailablewhenthedatabaseisinARCHIVELOGmode.4.Whatisthedifferencebetweenrestoringandrecovering?Restoringinvolvescopyingbackupfilesfromsecondarystorage(backupmedia)todisk.Thiscanbedonetoreplacedamagedfilesortocopy/moveadatabasetoanewlocation.Recoveryistheprocessofapplyingredologstothedatabasetorollitforward.Onecanrollforwarduntilaspecificpointintime(beforethedisasteroccurred),orrollforwarduntilthelasttransactionrecordedinthelogfiles.SQL>connectSYSasSYSDBASQL>RECOVERDATABASEUNTILTIME20010306:16:00:00USINGBACKUPCONTROLFILERMAN>run{setuntiltimeto_date(04Aug200400:00:00,DDMONYYYYHH24:MI:SS)restoredatabaserecoverdatabase}5. MydatabaseisdownandIcannotrestore.Whatnow?Thisisprobablynottheappropriatetimetobesarcastic,but,recoverywithoutbackupsarenotsupported.Youknowthatyoushouldhavetestedyourrecoverystrategy,andthatyoushouldalwaysbackupacorrupteddatabasebeforeattemptingtorestore/recoverit.Nevertheless,OracleConsultingcansometimesextractdatafromanofflinedatabaseusingautilitycalledDUL(DiskUnLoadLifeisDULwithoutit!).ThisutilityreadsdatainthedatafilesandunloadsitintoSQL*Loaderorexportdumpfiles.Hopefullyyoullthenbeabletoloadthedataintoaworkingdatabase.4/30/2015 EssentialDBAInterviewQuestions|TechnologyLearninghttps://techknowledgeworld.wordpress.com/2014/11/20/essentialdbainterviewquestions/ 18/98NotethatDULdoesnotcareaboutrollbacksegments,corruptedblocks,etc,andcanthusnotguaranteethatthedataisnotlogicallycorrupt.Itisintendedasanabsolutelastresortandwillmostlikelycostyourcompanyalotofmoney!DUDE(DatabaseUnloadingbyDataExtraction)isanothernonOracleutilitythatcanbeusedtoextractdatafromadeaddatabase.6. Howdoesonebackupadatabaseusingtheexportutility?Oracleexportsarelogicaldatabasebackups(notphysical)astheyextractdataandlogicaldefinitionsfromthedatabaseintoafile.Otherbackupstrategiesnormallybackupthephysicaldatafiles.Oneoftheadvantagesofexportsisthatonecanselectivelyreimporttables,howeveronecannotrollforwardfromanrestoredexport.Tocompletelyrestoreadatabasefromanexportfileonepracticallyneedstorecreatetheentiredatabase.Alwaysdofullsystemlevelexports(FULL=YES).Fullexportsincludemoreinformationaboutthedatabaseintheexportfilethanuserlevelexports.FormoreinformationabouttheOracleexportandimportutilities,seetheImport/ExportFAQ.7. HowdoesoneputadatabaseintoARCHIVELOGmode?Themainreasonforrunninginarchivelogmodeisthatonecanprovide24houravailabilityandguaranteecompletedatarecoverability.ItisalsonecessarytoenableARCHIVELOGmodebeforeonecanstarttouseonlinedatabasebackups.IssuethefollowingcommandstoputadatabaseintoARCHIVELOGmode:SQL>CONNECTsysASSYSDBASQL>STARTUPMOUNTEXCLUSIVESQL>ALTERDATABASEARCHIVELOGSQL>ARCHIVELOGSTARTSQL>ALTERDATABASEOPENAlternatively,addtheabovecommandsintoyourdatabasesstartupcommandscript,andbouncethedatabase.ThefollowingparametersneedstobesetfordatabasesinARCHIVELOGmode:log_archive_start=TRUElog_archive_dest_1=LOCATION=/arch_dir_namelog_archive_dest_state_1=ENABLElog_archive_format=%d_%t_%s.arcNOTE1:Remembertotakeabaselinedatabasebackuprightafterenablingarchivelogmode.Withoutitonewouldnotbeabletorecover.Also,implementanarchivelogbackuptopreventthearchivelogdirectoryfromfillingup.NOTE2:ARCHIVELOGmodewasintroducedwithOracle6,andisessentialfordatabasepointintimerecovery.Archivingcanbeusedincombinationwithonlineandofflinedatabasebackups.NOTE3:YoumaywanttosetthefollowingINIT.ORAparameterswhenenablingARCHIVELOGmode:log_archive_start=TRUE,log_archive_dest=,andlog_archive_format=NOTE4:YoucanchangethearchivelogdestinationofadatabaseonlinewiththeARCHIVELOGSTARTTOdirectory'statement.Thisstatementisoftenusedtoswitcharchivingbetweenasetofdirectories.NOTE5:WhenrunningOracleRealApplicationClusters(RAC),youneedtoshutdownallnodesbeforechangingthedatabasetoARCHIVELOGmode.SeetheRACFAQformoredetails.[edit]Ivelostanarchived/onlineREDOLOGfile,canIgetmyDBback?ThefollowingINIT.ORA/SPFILEparametercanbeusedifyourcurrentredologsarecorruptedorblownaway.Itmayalsobehandyifyoudodatabaserecoveryandoneofthearchivedlogfilesaremissingandcannotberestored.NOTE:Cautionisadvisedwhenenablingthisparameterasyoumightenduplosingyourentiredatabase.PleasecontactOracleSupportbeforeusingit._allow_resetlogs_corruption=trueThisshouldallowyoutoopenthedatabase.However,afterusingthisparameteryourdatabasewillbeinconsistent(somecommittedtransactionsmaybelostorpartiallyapplied).Steps:DoaSHUTDOWNNORMALofthedatabaseSettheaboveparameterDoaSTARTUPMOUNTandALTERDATABASEOPENRESETLOGSIfthedatabaseasksforrecovery,useanUNTILCANCELtyperecoveryandapplyallavailablearchiveandonlineredologs,thenissueCANCELandreissuetheALTERDATABASEOPENRESETLOGScommand.4/30/2015 EssentialDBAInterviewQuestions|TechnologyLearninghttps://techknowledgeworld.wordpress.com/2014/11/20/essentialdbainterviewquestions/ 19/98WaitacoupleofminutesforOracletosortitselfoutDoaSHUTDOWNNORMALRemovetheaboveparameter!DoadatabaseSTARTUPandcheckyourALERT.LOGfileforerrors.ExtractthedataandrebuildtheentiredatabaseThissectiondealswithusermanaged,ornonRMANbackups.1. Howdoesonedoofflinedatabasebackups?Shutdownthedatabasefromsqlplusorservermanager.Backupallfilestosecondarystorage(eg.tapes).Ensurethatyoubackupalldatafiles,allcontrolfilesandalllogfiles.Whencompleted,restartyourdatabase.Dothefollowingqueriestogetalistofallfilesthatneedstobebackedup:selectnamefromsys.v_$datafileselectmemberfromsys.v_$logfileselectnamefromsys.v_$controlfileSometimesOracletakesforevertoshutdownwiththeimmediateoption.Asworkaroundtothisproblem,shutdownusingthesecommands:altersystemcheckpointshutdownabortstartuprestrictshutdownimmediateNotethatifyourdatabaseisinARCHIVELOGmode,onecanstillusearchivedlogfilestorollforwardfromanofflinebackup.Ifyoucannottakeyourdatabasedownforacold(offline)backupataconvenienttime,switchyourdatabaseintoARCHIVELOGmodeandperformhot(online)backups.2.Howdoesonedoonlinedatabasebackups?Eachtablespacethatneedstobebackedupmustbeswitchedintobackupmodebeforecopyingthefilesouttosecondarystorage(tapes).Lookatthissimpleexample.ALTERTABLESPACExyzBEGINBACKUP!cpxyzFile1/backupDir/ALTERTABLESPACExyzENDBACKUPItisbettertobackuptablespacefortablespacethantoputalltablespacesinbackupmode.Backingthemupseparatelyincurslessoverhead.Whendone,remembertobackupyourcontrolfiles.Lookatthisexample:ALTERSYSTEMSWITCHLOGFILEForcelogswitchtoupdatecontrolfileheadersALTERDATABASEBACKUPCONTROLFILETO/backupDir/control.dbf'NOTE:Donotrunonlinebackupsduringpeakprocessingperiods.Oraclewillwritecompletedatabaseblocksinsteadofthenormaldeltastoredologfileswhileinbackupmode.Thiswillleadtoexcessivedatabasearchivingandevendatabasefreezes.3. MydatabasewasterminatedwhileinBACKUPMODE,doIneedtorecover?IfadatabasewasterminatedwhileoneofitstablespaceswasinBACKUPMODE(ALTERTABLESPACExyzBEGINBACKUP),itwilltellyouthatmediarecoveryisrequiredwhenyoutrytorestartthedatabase.TheDBAisthenrequiredtorecoverthedatabaseandapplyallarchivedlogstothedatabase.However,fromOracle7.2,onecansimplytaketheindividualdatafilesoutofbackupmodeandrestartthedatabase.ALTERDATABASEDATAFILE/path/filenameENDBACKUPOnecanselectfromV$BACKUPtoseewhichdatafilesareinbackupmode.Thisnormallysavesasignificantamountofdatabasedowntime.Seescriptend_backup2.sqlintheScriptssectionofthissite.FromOracle9ionwards,thefollowingcommandcanbeusedtotakeallofthedatafilesoutofhotbackupmode:ALTERDATABASEENDBACKUPThiscommandmustbeissuedwhenthedatabaseismounted,butnotyetopened.USERMANAGEDBACKUPANDRECOVERY4/30/2015 EssentialDBAInterviewQuestions|TechnologyLearninghttps://techknowledgeworld.wordpress.com/2014/11/20/essentialdbainterviewquestions/ 20/984.DoesOraclewritetodatafilesinbegin/hotbackupmode?Whenatablespaceisinbackupmode,Oraclewillstopupdatingitsfileheaders,butwillcontinuetowritetothedatafiles.Wheninbackupmode,Oraclewillwritecompletechangedblockstotheredologfiles.Normallyonlydeltas(changevectors)areloggedtotheredologs.Thisisdonetoenablereconstructionofablockifonlyhalfofitwasbackedup(splitblocks).Becauseofthis,oneshouldnoticeincreasedlogactivityandarchivingduringonlinebackups.Tosolvethisproblem,simplyswitchtoRMANbackups.ThissectiondealswithRMANbackups:1. WhatisRMANandhowdoesoneuseit?RecoveryManager(orRMAN)isanOracleprovidedutilityforbackingup,restoringandrecoveringOracleDatabases.RMANshipswiththedatabaseserveranddoesntrequireaseparateinstallation.TheRMANexecutableislocatedinyourORACLE_HOME/bindirectory.InfactRMAN,isjustaPro*CapplicationthattranslatescommandstoaPL/SQLinterface.ThePL/SQLcallsarestallicallylinkedintotheOraclekernel,anddoesnotrequirethedatabasetobeopened(mappedfromthe?/rdbms/admin/recover.bsqfile).RMANcandoofflineandonlinedatabasebackups.Itcannot,however,writedirectlytotape,butvarious3rdpartytools(likeVeritas,Omiback,etc)canintegratewithRMANtohandletapelibrarymanagement.RMANcanbeoperatedfromOracleEnterpriseManager,orfromcommandline.Herearethecommandlinearguments:ArgumentValueDescriptiontargetquotedstringconnectstringfortargetdatabasecatalogquotedstringconnectstringforrecoverycatalognocatalognoneifspecified,thennorecoverycatalogcmdfilequotedstringnameofinputcommandfilelogquotedstringnameofoutputmessagelogfiletracequotedstringnameofoutputdebuggingmessagelogfileappendnoneifspecified,logisopenedinappendmodedebugoptionalargsactivatedebuggingmsgnononeshowRMANnnnnprefixforallmessagessendquotedstringsendacommandtothemediamanagerpipestringbuildingblockforpipenamestimeoutintegernumberofsecondstowaitforpipeinputHereisanexample:[oracle@localhostoracle]$rmanRecoveryManager:Release10.1.0.2.0ProductionCopyright(c)1995,2004,Oracle.Allrightsreserved.RMAN>connecttargetconnectedtotargetdatabase:ORCL(DBID=1058957020)RMAN>backupdatabase2. HowdoesonebackupandrestoreadatabaseusingRMAN?ThebiggestadvantageofRMANisthatitonlybackupusedspaceinthedatabase.RMANdoesntputtablespacesinbackupmode,savingonredogenerationoverhead.RMANwillrereaddatabaseblocksuntilitgetsaconsistentimageofit.Lookatthissimplebackupexample.rmantargetsys/***nocatalogrun{allocatechannelt1typediskRMANBACKUPANDRECOVERY4/30/2015 EssentialDBAInterviewQuestions|TechnologyLearninghttps://techknowledgeworld.wordpress.com/2014/11/20/essentialdbainterviewquestions/ 21/98backupformat/app/oracle/backup/%d_t%t_s%s_p%p(database)releasechannelt1}ExampleRMANrestore:rmantargetsys/***nocatalogrun{allocatechannelt1typedisk1. setuntiltimeAug072000:51restoretablespaceusersrecovertablespaceusersreleasechannelt1}Theexamplesaboveareextremelysimplisticandonlyusefulforillustratingbasicconcepts.BydefaultOracleusesthedatabasecontrolfilestostoreinformationaboutbackups.NormallyonewouldrathersetupaRMANcatalogdatabasetostoreRMANmetadatain.ReadtheOracleBackupandRecoveryGuidebeforeimplementinganyRMANbackups.Note:RMANcannotwriteimagecopiesdirectlytotape.OneneedstouseathirdpartymediamanagerthatintegrateswithRMANtobackupdirectlytotape.Alternativelyonecanbackuptodiskandthenmanuallycopythebackupstotape.3. Howdoesonebackupandrestorearchivedlogfiles?OnecanbackuparchivedlogfilesusingRMANoranyoperatingsystembackuputility.Remembertodeletefilesafterbackingthemuptopreventthearchivelogdirectoryfromfillingup.Ifthearchivelogdirectorybecomesfull,yourdatabasewillhang!LookatthissimpleRMANbackupscripts:RMAN>run{2>allocatechanneldev1typedisk3>backup4>format/app/oracle/archback/log_%t_%sp%p5>(archivelogalldeleteinput)6>releasechanneldev17>}Thedeleteinputclausewilldeletethearchivedlogsastheyarebackedup.Listallarchivelogbackupsforthepast24hours:RMAN>LISTBACKUPOFARCHIVELOGFROMTIMEsysdate1Hereisarestoreexample:RMAN>run{2>allocatechanneldev1typedisk3>restore(archiveloglowlogseq78311highlogseq78340thread1all)4>releasechanneldev15>}4. HowdoesonecreateaRMANrecoverycatalog?Startbycreatingadatabaseschema(usuallycalledrman).Assignanappropriatetablespacetoitandgrantittherecovery_catalog_ownerrole.Lookatthisexample:sqlplussysSQL>createuserrmanidentifiedbyrmanSQL>alteruserrmandefaulttablespacetoolstemporarytablespacetempSQL>alteruserrmanquotaunlimitedontoolsSQL>grantconnect,resource,recovery_catalog_ownertorman4/30/2015 EssentialDBAInterviewQuestions|TechnologyLearninghttps://techknowledgeworld.wordpress.com/2014/11/20/essentialdbainterviewquestions/ 22/98SQL>exitNext,logintormanandcreatethecatalogschema.PriortoOracle8ithiswasdonebyrunningthecatrman.sqlscript.rmancatalogrman/rmanRMAN>createcatalogtablespacetoolsRMAN>exitYoucannowcontinuebyregisteringyourdatabasesinthecatalog.Lookatthisexample:rmancatalogrman/rmantargetbackdba/backdbaRMAN>registerdatabaseOnecanalsousetheupgradecatalogcommandtoupgradetoanewRMANrelease,orthedropcatalogcommandtoremoveanRMANcatalog.Thesecommandsneedtobeenteredtwicetoconfirmtheoperation.5. HowdoesoneintegrateRMANwiththirdpartyMediaManagers?ThefollowingMediaManagementSoftwareVendorshaveintegratedtheirmediamanagementsoftwarewithRMAN(OracleRecoveryManager):VeritasNetBackuphttp://www.veritas.com/EMCDataManager(EDM)http://www.emc.com/HPOMNIBack/DataProtectorhttp://www.hp.com/IBMsTivoliStorageManager(formerlyADSM)http://www.tivoli.com/storage/EMCNetworkerhttp://www.emc.com/BrightStorARCserveBackuphttp://www.ca.com/us/datalossprevention.aspxSterlingSoftwaresSAMS:Alexandria(formerlyfromSpectralogic)http://www.sterling.com/sams/SUNsSolsticeBackuphttp://www.sun.com/software/whitepapers/backupnstorage/CommVaultGalaxyhttp://www.commvault.com/etcTheaboveMediaManagementVendorswillprovidefirstlinetechnicalsupport(andinstallationguides)fortheirrespectiveproducts.AcompletelistofsupportedMediaManagementVendorscanbefoundat:http://www.oracle.com/technology/deploy/availability/htdocs/bsp.htmWhenallocatingchannelsonecanspecifyMediaManagementspesificparameters.Herearesomeexamples:NetbackuponSolaris:allocatechannelt1typeSBT_TAPEPARMS=SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so.1NetbackuponWindows:allocatechannelt1typeSBT_TAPEsendNB_ORA_CLIENT=client_machine_nameOmniback/DataProtectoronHPUX:allocatechannelt1typeSBT_TAPEPARMS=SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.sl'or:allocatechanneldev_1typesbt_tapeparmsENV=OB2BARTYPE=Oracle8,OB2APPNAME=orcl,OB2BARLIST=machinename_orcl_archlogs)'6. Howdoesoneclone/duplicateadatabasewithRMAN?ThefirststeptocloneorduplicateadatabasewithRMANistocreateanewINIT.ORAandpasswordfile(usetheorapwdutility)onthemachineyouneedtoclonethedatabaseto.Reviewallparametersandmaketherequiredchanged.Forexample,settheDB_NAMEparametertothenewdatabasesname.Secondly,youneedtochangeyourenvironmentvariables,anddoaSTARTUPNOMOUNTfromsqlplus.ThisdatabaseisreferredtoastheAUXILIARYinthescriptbelow.Lastly,writeaRMANscriptlikethistodothecloning,andcallitwithrmancmdfiledupdb.rcv:4/30/2015 EssentialDBAInterviewQuestions|TechnologyLearninghttps://techknowledgeworld.wordpress.com/2014/11/20/essentialdbainterviewquestions/ 23/98connecttargetsys/secure@origdbconnectcatalogrman/rman@catdbconnectauxiliary/run{setnewnamefordatafile1to/ORADATA/u01/system01.dbf'setnewnamefordatafile2to/ORADATA/u02/undotbs01.dbf'setnewnamefordatafile3to/ORADATA/u03/users01.dbf'setnewnamefordatafile4to/ORADATA/u03/indx01.dbf'setnewnamefordatafile5to/ORADATA/u02/example01.dbf'allocateauxiliarychanneldupdb1typedisksetuntilsequence2thread1duplicatetargetdatabasetodupdblogfileGROUP1(/ORADATA/u02/redo01.log)SIZE200kREUSE,GROUP2(/ORADATA/u03/redo02.log)SIZE200kREUSE}Theabovescriptwillconnecttothetarget(databasethatwillbecloned),therecoverycatalog(togetbackupinfo),andtheauxiliarydatabase(newduplicateDB).Previousbackupswillberestoredandthedatabaserecoveredtothesetuntiltimespecifiedinthescript.Notes:thesetnewnamecommandsareonlyrequiredifyourdatafilenameswilldifferentfromthetargetdatabase.ThenewlyclonedDBwillhaveitsownuniqueDBID.7. CanonerestoreRMANbackupswithoutaCONTROLFILEandRECOVERYCATALOG?DetailsofRMANbackupsarestoredinthedatabasecontrolfilesandoptionallyaRecoveryCatalog.Ifboththesearegone,RMANcannotrestorethedatabase.Insuchasituationonemustextractacontrolfile(orotherfiles)fromthebackuppieceswrittenoutwhenthelastbackupwastaken.Letslookatanexample:Letstakeabackup(partialinourcaseforilustrativepurposes):$rmantarget/nocatalogRecoveryManager:Release10.1.0.2.064bitProductionCopyright(c)1995,2004,Oracle.Allrightsreserved.connectedtotargetdatabase:ORCL(DBID=1046662649)usingtargetdatabasecontrolfileinsteadofrecoverycatalogRMAN>backupdatafile1Startingbackupat20AUG04allocatedchannel:ORA_DISK_1channelORA_DISK_1:sid=146devtype=DISKchannelORA_DISK_1:startingfulldatafilebackupsetchannelORA_DISK_1:specifyingdatafile(s)inbackupsetinputdatafilefno=00001name=/oradata/orcl/system01.dbfchannelORA_DISK_1:startingpiece1at20AUG04channelORA_DISK_1:finishedpiece1at20AUG04piecehandle=/flash_recovery_area/ORCL/backupset/2004_08_20/o1_mf_nnndf_TAG20040820T153256_0lczd9tf_.bkpcomment=NONEchannelORA_DISK_1:backupsetcomplete,elapsedtime:00:00:45channelORA_DISK_1:startingfulldatafilebackupsetchannelORA_DISK_1:specifyingdatafile(s)inbackupsetincludingcurrentcontrolfileinbackupsetincludingcurrentSPFILEinbackupset4/30/2015 EssentialDBAInterviewQuestions|TechnologyLearninghttps://techknowledgeworld.wordpress.com/2014/11/20/essentialdbainterviewquestions/ 24/98channelORA_DISK_1:startingpiece1at20AUG04channelORA_DISK_1:finishedpiece1at20AUG04piecehandle=/flash_recovery_area/ORCL/backupset/2004_08_20/o1_mf_ncsnf_TAG20040820T153256_0lczfrx8_.bkpcomment=NONEchannelORA_DISK_1:backupsetcomplete,elapsedtime:00:00:04Finishedbackupat20AUG04[/code]Now,letsdestroyoneofthecontrolfiles:SQL>showparametersCONTROL_FILESNAMETYPEVALUEcontrol_filesstring/oradata/orcl/control01.ctl,/oradata/orcl/control02.ctl,/oradata/orcl/control03.ctlSQL>shutdownabortORACLEinstanceshutdown.SQL>!mv/oradata/orcl/control01.ctl/tmp/control01.ctlNow,letsseeifwecanrestoreit.FirstweneedtostartthedatabaaseinNOMOUNTmode:SQL>startupNOMOUNTORACLEinstancestarted.TotalSystemGlobalArea289406976bytesFixedSize1301536bytesVariableSize262677472bytesDatabaseBuffers25165824bytesRedoBuffers262144bytesNow,fromSQL*Plus,runthefollowingPL/SQLblocktorestorethefile:DECLAREv_devtypeVARCHAR2(100)v_doneBOOLEANv_maxPiecesNUMBERTYPEt_pieceNameISTABLEOFvarchar2(255)INDEXBYbinary_integerv_pieceNamet_pieceNameBEGINDefinethebackuppieces(namesfromtheRMANLogfile)v_pieceName(1):=/flash_recovery_area/ORCL/backupset/2004_08_20/o1_mf_ncsnf_TAG20040820T153256_0lczfrx8_.bkp'v_pieceName(2):=/flash_recovery_area/ORCL/backupset/2004_08_20/o1_mf_nnndf_TAG20040820T153256_0lczd9tf_.bkp'v_maxPieces:=2Allocateachannel(Usetype=>nullforDISK,type=>sbt_tapeforTAPE)v_devtype:=DBMS_BACKUP_RESTORE.deviceAllocate(type=>NULL,ident=>d1)RestorethefirstControlFileDBMS_BACKUP_RESTORE.restoreSetDataFileCFNAMEmistbetheexactpathandfilenameofacontrolfiletahtwasbackedupDBMS_BACKUP_RESTORE.restoreControlFileTo(cfname=>/app/oracle/oradata/orcl/control01.ctl)dbms_output.put_line(Startrestoring||v_maxPieces||pieces.)FORiIN1..v_maxPiecesLOOP4/30/2015 EssentialDBAInterviewQuestions|TechnologyLearninghttps://techknowledgeworld.wordpress.com/2014/11/20/essentialdbainterviewquestions/ 25/98dbms_output.put_line(Restoringfrompiece||v_pieceName(i))DBMS_BACKUP_RESTORE.restoreBackupPiece(handle=>v_pieceName(i),done=>v_done,params=>null)exitwhenv_doneENDLOOPDeallocatethechannelDBMS_BACKUP_RESTORE.deviceDeAllocate(d1)EXCEPTIONWHENOTHERSTHENDBMS_BACKUP_RESTORE.deviceDeAllocateRAISEEND/Letsseeifthecontrolfilewasrestored:SQL>!lsl/oradata/orcl/control01.ctlrwr1oracledba3096576Aug2016:45/oradata/orcl/control01.ctl[/code]WeshouldnowbeabletoMOUNTthedatabaseandcontinuerecoverySQL>!cp/oradata/orcl/control01.ctl/oradata/orcl/control02.ctlSQL>!cp/oradata/orcl/control01.ctl/oradata/orcl/control03.ctlSQL>alterdatabasemountSQL>recoverdatabaseusingbackupcontrolfileORA00279:change7917452generatedat08/20/200416:40:59neededforthread1ORA00289:suggestion:/flash_recovery_area/ORCL/archivelog/2004_08_20/o1_mf_1_671_%u_.arcORA00280:change7917452forthread1isinsequence#671Specifylog:{=suggested|filename|AUTO|CANCEL}/oradata/orcl/redo02.logLogapplied.Mediarecoverycomplete.Databasealtered.SQL>alterdatabaseopenresetlogsDatabasealtered.DBAProfessionalfocusedInterviewQuestionsAnswers:1. Explainthedifferencebetweenahotbackupandacoldbackupandthebenefitsassociatedwitheach.?Ahotbackupisbasicallytakingabackupofthedatabasewhileitisstillupandrunninganditmustbeinarchivelogmode.Acoldbackupistakingabackupofthedatabasewhileitisshutdownanddoesnotrequirebeinginarchivelogmode.Thebenefitoftakingahotbackupisthatthedatabaseisstillavailableforusewhilethebackupisoccurringandyoucanrecoverthedatabasetoanypointintime.Thebenefitoftakingacoldbackupisthatitistypicallyeasiertoadministerthebackupandrecoveryprocess.Inaddition,sinceyouaretakingcoldbackupsthedatabasedoesnotrequirebeinginarchivelogmodeandthustherewillbeaslightperformancegainasthedatabaseisnotcuttingarchivelogstodisk.2. Youhavejusthadtorestorefrombackupanddonothaveanycontrolfiles.Howwouldyougoaboutbringingupthisdatabase?Iwouldcreateatextbasedbackupcontrolfile,stipulatingwhereondiskallthedatafileswhereandthenissuetherecovercommandwiththeusingbackupcontrolfileclause.3. Howdoyouswitchfromaninit.orafiletoaspfile?Issuethecreatespfilefrompfilecommand.4. Explainthedifferencebetweenadatablock,anextentandasegment.?4/30/2015 EssentialDBAInterviewQuestions|TechnologyLearninghttps://techknowledgeworld.wordpress.com/2014/11/20/essentialdbainterviewquestions/ 26/98Adatablockisthesmallestunitoflogicalstorageforadatabaseobject.Asobjectsgrowtheytakechunksofadditionalstoragethatarecomposedofcontiguousdatablocks.Thesegroupingsofcontiguousdatablocksarecalledextents.Alltheextentsthatanobjecttakeswhengroupedtogetherareconsideredthesegmentofthedatabaseobject.5. GivetwoexamplesofhowyoumightdeterminethestructureofthetableDEPT.?Usethedescribecommandorusethedbms_metadata.get_ddlpackage.6. Wherewouldyoulookforerrorsfromthedatabaseengine?Inthealertlog.7. CompareandcontrastTRUNCATEandDELETEforatable.?Boththetruncateanddeletecommandhavethedesiredoutcomeofgettingridofalltherowsinatable.ThedifferencebetweenthetwoisthatthetruncatecommandisaDDLoperationandjustmovesthehighwatermarkandproducesanowrollback.Thedeletecommand,ontheotherhand,isaDMLoperation,whichwillproducearollbackandthustakelongertocomplete.8. Givethereasoningbehindusinganindex.?Fasteraccesstodatablocksinatable.9. Givethetwotypesoftablesinvolvedinproducingastarschemaandthetypeofdatatheyhold.?Facttablesanddimensiontables.Afacttablecontainsmeasurementswhiledimensiontableswillcontaindatathatwillhelpdescribethefacttables.10. Whattypeofindexshouldyouuseonafacttable?ABitmapindex.11. Givetwoexamplesofreferentialintegrityconstraints.?Aprimarykeyandaforeignkey.12. Atableisclassifiedasaparenttableandyouwanttodropandrecreateit.Howwouldyoudothiswithoutaffectingthechildrentables?Disabletheforeignkeyconstrainttotheparent,dropthetable,recreatethetable,enabletheforeignkeyconstraint.13. ExplainthedifferencebetweenARCHIVELOGmodeandNOARCHIVELOGmodeandthebenefitsanddisadvantagestoeach.?ARCHIVELOGmodeisamodethatyoucanputthedatabaseinforcreatingabackupofalltransactionsthathaveoccurredinthedatabasesothatyoucanrecovertoanypointintime.NOARCHIVELOGmodeisbasicallytheabsenceofARCHIVELOGmodeandhasthedisadvantageofnotbeingabletorecovertoanypointintime.NOARCHIVELOGmodedoeshavetheadvantageofnothavingtowritetransactionstoanarchivelogandthusincreasestheperformanceofthedatabaseslightly.14. Whatcommandwouldyouusetocreateabackupcontrolfile?Alterdatabasebackupcontrolfiletotrace.15. Givethestagesofinstancestartuptoausablestatewherenormalusersmayaccessit.?STARTUPNOMOUNTInstancestartupSTARTUPMOUNTThedatabaseismountedSTARTUPOPENThedatabaseisopened16. WhatcolumndifferentiatestheV$viewstotheGV$viewsandhow?TheINST_IDcolumnwhichindicatestheinstanceinaRACenvironmenttheinformationcamefrom.17. HowwouldyougoaboutgeneratinganEXPLAINplan?Createaplantablewithutlxplan.sql.Usetheexplainplansetstatement_id=tst1intoplan_tableforaSQLstatementLookattheexplainplanwithutlxplp.sqlorutlxpls.sql18. Howwouldyougoaboutincreasingthebuffercachehitratio?4/30/2015 EssentialDBAInterviewQuestions|TechnologyLearninghttps://techknowledgeworld.wordpress.com/2014/11/20/essentialdbainterviewquestions/ 27/98Usethebuffercacheadvisoryoveragivenworkloadandthenquerythev$db_cache_advicetable.IfachangewasnecessarythenIwouldusethealtersystemsetdb_cache_sizecommand.19. ExplainanORA01555Yougetthiserrorwhenyougetasnapshottoooldwithinrollback.Itcanusuallybesolvedbyincreasingtheundoretentionorincreasingthesizeofrollbacks.Youshouldalsolookatthelogicinvolvedintheapplicationgettingtheerrormessage.20. Explainthedifferencebetween$ORACLE_HOMEand$ORACLE_BASE.?ORACLE_BASEistherootdirectoryfororacle.ORACLE_HOMElocatedbeneathORACLE_BASEiswheretheoracleproductsreside.21. Howwouldyoudeterminethetimezoneunderwhichadatabasewasoperating?selectDBTIMEZONEfromdual22. ExplaintheuseofsettingGLOBAL_NAMESequaltoTRUE.SettingGLOBAL_NAMESdictateshowyoumightconnecttoadatabase.ThisvariableiseitherTRUEorFALSEandifitissettoTRUEitenforcesdatabaselinkstohavethesamenameastheremotedatabasetowhichtheyarelinking.23. WhatcommandwouldyouusetoencryptaPL/SQLapplication?WRAP24. ExplainthedifferencebetweenaFUNCTION,PROCEDUREandPACKAGE.?AfunctionandprocedurearethesameinthattheyareintendedtobeacollectionofPL/SQLcodethatcarriesasingletask.Whileaproceduredoesnothavetoreturnanyvaluestothecallingapplication,afunctionwillreturnasinglevalue.Apackageontheotherhandisacollectionoffunctionsandproceduresthataregroupedtogetherbasedontheircommonalitytoabusinessfunctionorapplication.25. Explaintheuseoftablefunctions.?TablefunctionsaredesignedtoreturnasetofrowsthroughPL/SQLlogicbutareintendedtobeusedasanormaltableorviewinaSQLstatement.TheyarealsousedtopipelineinformationinanETLprocess.26. Namethreeadvisorystatisticsyoucancollect.?BufferCacheAdvice,SegmentLevelStatistics,&TimedStatistics27. WhereintheOracledirectorytreestructureareaudittracesplaced?Inunix$ORACLE_HOME/rdbms/audit,inWindowstheeventviewer28. Explainmaterializedviewsandhowtheyareused.?Materializedviewsareobjectsthatarereducedsetsofinformationthathavebeensummarized,grouped,oraggregatedfrombasetables.Theyaretypicallyusedindatawarehouseordecisionsupportsystems.29. Whenauserprocessfails,whatbackgroundprocesscleansupafterit?PMON30. Whatbackgroundprocessrefreshesmaterializedviews?TheJobQueueProcesses.31. Howwouldyoudeterminewhatsessionsareconnectedandwhatresourcestheyarewaitingfor?UseofV$SESSIONandV$SESSION_WAIT32. Describewhatredologsare.?Redologsarelogicalandphysicalstructuresthataredesignedtoholdallthechangesmadetoadatabaseandareintendedtoaidintherecoveryofadatabase.33. Howwouldyouforcealogswitch?ALTERSYSTEMSWITCHLOGFILE34. GivetwomethodsyoucouldusetodeterminewhatDDLchangeshavebeenmade.?4/30/2015 EssentialDBAInterviewQuestions|TechnologyLearninghttps://techknowledgeworld.wordpress.com/2014/11/20/essentialdbainterviewquestions/ 28/98YoucoulduseLogminerorStreams35. Whatdoescoalescingatablespacedo?Coalescingisonlyvalidfordictionarymanagedtablespacesanddefragmentsspacebycombiningneighboringfreeextentsintolargesingleextents.36. WhatisthedifferencebetweenaTEMPORARYtablespaceandaPERMANENTtablespace?Atemporarytablespaceisusedfortemporaryobjectssuchassortstructureswhilepermanenttablespacesareusedtostorethoseobjectsmeanttobeusedasthetrueobjectsofthedatabase.37. Nameatablespaceautomaticallycreatedwhenyoucreateadatabase.?TheSYSTEMtablespace.38. Whencreatingauser,whatpermissionsmustyougranttoallowthemtoconnecttothedatabase?GranttheCONNECTtotheuser.39. Howdoyouaddadatafiletoatablespace?ALTERTABLESPACEADDDATAFILESIZE40. Howdoyouresizeadatafile?ALTERDATABASEDATAFILERESIZE41. Whatviewwouldyouusetolookatthesizeofadatafile?DBA_DATA_FILES42. Whatviewwouldyouusetodeterminefreespaceinatablespace?DBA_FREE_SPACE43. Howwouldyoudeterminewhohasaddedarowtoatable?Turnonfinegrainauditingforthetable.44. Howcanyourebuildanindex?ALTERINDEXREBUILD45. Explainwhatpartitioningisandwhatitsbenefitis.?Partitioningisamethodoftakinglargetablesandindexesandsplittingthemintosmaller,moremanageablepieces.46. YouhavejustcompiledaPL/SQLpackagebutgoterrors,howwouldyouviewtheerrors?SHOWERRORS47. Howcanyougatherstatisticsonatable?TheANALYZEcommand.48. Howcanyouenableatraceforasession?UsetheDBMS_SESSION.SET_SQL_TRACEorUseALTERSESSIONSETSQL_TRACE=TRUE49. WhatisthedifferencebetweentheSQL*LoaderandIMPORTutilities?ThesetwoOracleutilitiesareusedforloadingdataintothedatabase.ThedifferenceisthattheimportutilityreliesonthedatabeingproducedbyanotherOracleutilityEXPORTwhiletheSQL*LoaderutilityallowsdatatobeloadedthathasbeenproducedbyotherutilitiesfromdifferentdatasourcesjustsolongasitconformstoASCIIformattedordelimitedfiles.50. Nametwofilesusedfornetworkconnectiontoadatabase.?TNSNAMES.ORAandSQLNET.ORA4/30/2015 EssentialDBAInterviewQuestions|TechnologyLearninghttps://techknowledgeworld.wordpress.com/2014/11/20/essentialdbainterviewquestions/ 29/9851. Whatisadatabaseinstance?Explain.Adatabaseinstance(Server)isasetofmemorystructureandbackgroundprocessesthataccessasetofdatabasefiles.Theprocessescanbesharedbyalloftheusers.Thememorystructurethatisusedtostorethemostquerieddatafromdatabase.ThishelpsuptoimprovedatabaseperformancebydecreasingtheamountofI/Operformedagainstdatafile.52. WhatisParallelServer?Multipleinstancesaccessingthesamedatabase(onlyinmultiCPUenvironments)53. Whatisaschema?Thesetofobjectsownedbyuseraccountiscalledtheschema.54. Whatisanindex?HowitisimplementedinOracledatabase?Anindexisadatabasestructureusedbytheservertohavedirectaccessofarowinatable.Anindexisautomaticallycreatedwhenauniqueofprimarykeyconstraintclauseisspecifiedincreatetablecommand55. Whatareclusters?Groupoftablesphysicallystoredtogetherbecausetheysharecommoncolumnsandareoftenusedtogetheriscalledcluster.56. Whatisaclusterkey?Therelatedcolumnsofthetablesarecalledtheclusterkey.Theclusterkeyisindexedusingaclusterindexanditsvalueisstoredonlyonceformultipletablesinthecluster.57. WhatarethebasicelementofbaseconfigurationofanOracledatabase?Itconsistsofoneormoredatafiles.oneormorecontrolfiles.twoormoreredologfiles.TheDatabasecontainsmultipleusers/schemasoneormorerollbacksegmentsoneormoretablespacesDatadictionarytablesUserobjects(table,indexes,viewsetc.,)TheserverthataccessthedatabaseconsistsofSGA(Databasebuffer,DictionaryCacheBuffers,Redologbuffers,SharedSQLpool)SMON(SystemMONito)PMON(ProcessMONitor)LGWR(LoGWrite)DBWR(DataBaseWrite)ARCH(ARCHiver)CKPT(CheckPoint)RECODispatcherUserProcesswithassociatedPGS58. Whatisadeadlock?Explain.Twoprocesseswaitingtoupdatetherowsofatable,whicharelockedbyotherprocessesthendeadlockarises.Inadatabaseenvironmentthiswilloftenhappenbecauseofnotissuingtheproperrowlockcommands.Poordesignoffrontendapplicationmaycausethissituationandtheperformanceofserverwillreducedrastically.Theselockswillbereleasedautomaticallywhenacommit/rollbackoperationperformedoranyoneofthisprocessesbeingkilledexternally.DATABASEADMINISTRATION4/30/2015 EssentialDBAInterviewQuestions|TechnologyLearninghttps://techknowledgeworld.wordpress.com/2014/11/20/essentialdbainterviewquestions/ 30/9859. WhatisSGA?TheSystemGlobalAreainanOracledatabaseistheareainmemorytofacilitatethetransferofinformationbetweenusers.Itholdsthemostrecentlyrequestedstructuralinformationbetweenusers.Itholdsthemostrecentlyrequestedstructuralinformationaboutthedatabase.Thestructureisdatabasebuffers,dictionarycache,redologbufferandsharedpoolarea.60. Whatisasharedpool?ThedatadictionarycacheisstoredinanareainSGAcalledthesharedpool.ThiswillallowsharingofparsedSQLstatementsamongconcurrentusers.61. WhatismeanbyProgramGlobalArea(PGA)?ItisareainmemorythatisusedbyasingleOracleuserprocess.62. Whatisadatasegment?Datasegmentarethephysicalareaswithinadatabaseblockinwhichthedataassociatedwithtablesandclustersarestored.63. WhatarethefactorscausingthereparsingofSQLstatementsinSGA?Duetoinsufficientsharedpoolsize.MonitortheratioofthereloadstakesplacewhileexecutingSQLstatements.Iftheratioisgreaterthan1thenincreasetheSHARED_POOL_SIZE.64. WhatisDatabaseBuffers?DatabasebuffersarecacheintheSGAusedtoholdthedatablocksthatarereadfromthedatasegmentsinthedatabasesuchastables,indexesandclustersDB_BLOCK_BUFFERSparameterinINIT.ORAdecidesthesize.65. Whatisdictionarycache?Dictionarycacheisinformationaboutthedatabaseobjectsstoredinadatadictionarytable.66. Whatismeantbyrecursivehints?Numberoftimesprocessesrepeatedlyquerythedictionarytableiscalledrecursivehints.Itisduetothedatadictionarycacheistoosmall.ByincreasingtheSHARED_POOL_SIZEparameterwecanoptimizethesizeofdatadictionarycache.67. Whatisredologbuffer?Changesmadetotherecordsarewrittentotheonlineredologfiles.Sothattheycanbeusedinrollforwardoperationsduringdatabaserecoveries.Beforewritingthemintotheredologfiles,theywillfirstbroughttoredologbuffersinSGAandLGWRwillwriteintofilesfrequently.LOG_BUFFERparameterwilldecidethesize.68. Howwillyouswapobjectsintoadifferenttablespaceforanexistingdatabase?ExporttheuserPerformimportusingthecommandimpsystem/managerfile=export.dmpindexfile=newrite.sql.Thiswillcreatealldefinitionsintonewfile.sql.Dropnecessaryobjects.Runthescriptnewfile.sqlafteralteringthetablespaces.Importfromthebackupforthenecessaryobjects.69. ListtheOptionalFlexibleArchitecture(OFA)ofOracledatabase?HowcanweorganizethetablespacesinOracledatabasetohavemaximumperformance?SYSTEMDatadictionarytables.DATAStandardoperationaltables.DATA2StatictablesusedforstandardoperationsINDEXESIndexesforStandardoperationaltables.INDEXES1Indexesofstatictablesusedforstandardoperations.MEMORYMANAGEMENTDATABASELOGICAL&PHYSICALARCHITECTURE4/30/2015 EssentialDBAInterviewQuestions|TechnologyLearninghttps://techknowledgeworld.wordpress.com/2014/11/20/essentialdbainterviewquestions/ 31/98TOOLSToolstable.TOOLS1Indexesfortoolstable.RBSStandardOperationsRollbackSegments,RBS1,RBS2Additional/SpecialRollbacksegments.TEMPTemporarypurposetablespaceTEMP_USERTemporarytablespaceforusers.USERSUsertablespace.70. Howwillyouforcedatabasetouseparticularrollbacksegment?SETTRANSACTIONUSEROLLBACKSEGMENTrbs_name.71. Whatismeantbyfreeextent?Afreeextentisacollectionofcontinuousfreeblocksintablespace.Whenasegmentisdroppeditsextentsarereallocatedandaremarkedasfree.72.WhichparameterinStorageclausewillreducenumberofrowsperblock?PCTFREEparameterRowsizealsoreducesnoofrowsperblock.73. Whatisthesignificanceofhavingstorageclause?Wecanplanthestorageforatableashowmuchinitialextentsarerequired,howmuchcanbeextendednext,howmuch%shouldleavefreeformanagingrowupdating,etc.,74. HowdoesSpaceallocationtableplacewithinablock?EachblockcontainsentriesasfollowsFixedblockheaderVariableblockheaderRowHeader,rowdate(multiplerowsmayexists)PCTEREE(%offreespaceforrowupdatinginfuture)75. WhatistheroleofPCTFREEparameterisstorageclause?Thisisusedtoreservecertainamountofspaceinablockforexpansionofrows.76. WhatistheOPTIMALparameter?Itisusedtosettheoptimallengthofarollbacksegment.77. WhatisthefunctionalityofSYSTEMtablespace?Tomanagethedatabaseleveltransactionssuchasmodificationsofthedatadictionarytablethatrecordinformationaboutthefreespaceusage.78. Howwillyoucreatemultiplerollbacksegmentsinadatabase?Createadatabase,whichimplicitlycreatesaSYSTEMrollbacksegmentinaSYSTEMtablespace.CreateasecondrollbacksegmentnameR0intheSYSTEMtablespace.Makenewrollbacksegmentavailable(aftershutdown,modifyinit.orafileandstartdatabase)Createothertablespaces(RBS)forrollbacksegments.DeactivaterollbacksegmentR0andactivatethenewlycreatedrollbacksegments.79. Howthespaceutilizationtakesplacewithinrollbacksegments?Itwilltrytofitthetransactioninacyclicfashiontoallexistingextents.Onceitfoundanextentisinusethenitforcedtoacquireanewextent(numberofextentsisbasedontheoptimalsize)80. Whyqueryfailssometimes?Rollbacksegmentdynamicallyextenttohandlelargertransactionsentryloads.4/30/2015 EssentialDBAInterviewQuestions|TechnologyLearninghttps://techknowledgeworld.wordpress.com/2014/11/20/essentialdbainterviewquestions/ 32/98Asingletransactionmaywipeoutallavailablefreespaceintherollbacksegmenttablespace.Thispreventsotheruserusingrollbacksegments.81. Howwillyoumonitorthespaceallocation?ByqueryingDBA_SEGMENTtable/view82. Howwillyoumonitorrollbacksegmentstatus?QueryingtheDBA_ROLLBACK_SEGSviewINUSERollbackSegmen


576 Oracle DBA Interview Questions

95180048 Oracle DBA Interview Questions Answers

51977749 Oracle Apps DBA Interview

Oracle DBA Interview Questions 1

29 Essential AngularJS Interview Questions

Oracle DBA Interview Questions Answered_ Technical (1) JAI MAA DURGA

Oracle DBA Job Interview Questions

Guide to Screening Candidates: 30 Essential … to Screening Candidates: 30 Essential Behavioral ... Candidates: 30 Essential Behavioral Interview ... 30 Essential Behavioral Interview

Oracle DBA Interview Question4

Oracle DBA interview questions

DataBase Administrator (DBA) Interview Questions and Answers

Oracle DBA Interview Questions Answered_ Technical

Oracle DBA Interview Questions Answers - Dbametrix · PDF fileOracle DBA Interview Questions Answers By ... will help you prepare for an Oracle DBA interview by providing and insight

All Oracle DBA Interview Questions

Oracle DBA Interview Question1

Database Administrator (DBA) Interview Questions And ......Database Administrator (DBA) Interview Questions And Answers Global Guideline . COM Database Administrator (DBA) Job Interview

Oracle dba interview questions with answer

03 Essential Unix (and Linux) for the Oracle DBA

The Interview Tips of Oracle DBA

SQL Server DBA Phone Interview Questions

DBA Beginning Beekeepers Workshop - Essential Beekeeping Equipment

Oracle Apps DBA Interview Questions

Interview questions for Oracle, DBA, developer candidates

Interview Question for Oracle DBA

Oracle DBA Interview Questions

MIKE AULT’S INTERVIEW QUESTIONS FOR … Interview... · Web viewTitle MIKE AULT’S INTERVIEW QUESTIONS FOR ORACLE, DBA, DEVELOPER CANDIDATES Author BellSouth Business Systems Last

DBA Interview Q&A

162922963 DBA Interview Questions

Oracle DBA Interview Questions Answers - Dbametrix · Oracle DBA Interview Questions Answers By Gitesh Trivedi Publisher: Kendba Services Copy right protected @ Kendba Services

DBA Interview q

dba interview quetions

33104459 All Oracle DBA Interview Questions

13499947 Oracle DBA Interview Question5

Top Interview Tips: The Employer’s Essential Handbook · Top Interview Tips: The Employer’s Essential Handbook A comprehensive guide to interview preparation, questions, tactics,

Database Administrator (DBA) Job Interview Preparation Guide