The Oracle Database

Oracleisarelationaldatabase.Inarelationaldatabase,alldataisstoredintwodimensional tablesthatarecomposedofrowsandcolumns.TheOracleDatabaseenablesyoutostoredata, updateit,andefficientlyretrieveit. OracleprovidessoftwaretocreateandmanagetheOracledatabase.Thedatabaseconsistsof physicalandlogicalstructuresinwhichsystem,user,andcontrolinformationisstored.The softwarethatmanagesthedatabaseiscalledtheOracledatabaseserver.Collectively,the softwarethatrunsoracleandthephysicaldatabasearecalledtheOracledatabasesystem.

Common Oracle DBA Tasks

AsanOracleDBA,youcanexpecttobeinvolvedinthefollowingtasks: InstallingOraclesoftware CreatingOracledatabases Performingupgradesofthedatabaseandsoftwaretonewreleaselevels Startingupandshuttingdownthedatabase Managingthedatabasesstoragestructures Managingusersandsecurity Managingschemaobjects,suchastables,indexes,andviews Makingdatabasebackupsandperformingrecoverywhennecessary Proactivelymonitoringthedatabaseshealthandtakingpreventiveorcorrectiveactionas required Monitoringandtuningperformance

Tools for Administering the Database

TheintentofthisbookistoallowyoutoquicklyandefficientlycreateanOracledatabase,andto provideguidanceinbasicdatabaseadministration.Tools for Administering the Database
Introduction 1-3

Thefollowingaresomeoftheproducts,tools,andutilitiesyoucanuseinachievingyourgoalsas adatabaseadministrator: Oracle Universal Installer (OUI) TheOracleUniversalInstallerinstallsyourOraclesoftwareandoptions.Itcanautomatically launchtheDatabaseConfigurationAssistanttoinstalladatabase. Database Configuration Assistant (DBCA) TheDatabaseConfigurationAssistantcreatesadatabasefromtemplatesthataresuppliedby Oracle,oryoucancreateyourown.Itenablesyoutocopyapreconfiguredseeddatabase,thus savingthetimeandeffortofgeneratingandcustomizingadatabasefromscratch. Database Upgrade Assistant ThisDatabaseUpgradeAssistantguidesyouthroughtheupgradeofyourexistingdatabasetoa newOraclerelease. Oracle Net Manager NetManagerguidesyouthroughyourOracleNetnetworkconfiguration. Oracle Enterprise Manager TheprimarytoolformanagingyourdatabaseisOracleEnterpriseManager,awebbased interface.AfteryouhaveinstalledtheOraclesoftware,createdorupgradedadatabase,and configuredthenetwork,youcanuseOracleEnterpriseManagerformanagingyourdatabase.In addition,OracleEnterpriseManageralsoprovidesaninterfaceforperformanceadvisorsandfor OracleutilitiessuchasSQL*LoaderandRecoveryManager.

Checking Prerequisites

Beforeinstallingthesoftware,theinstallerperformsanumberofautomatedprerequisitechecks toensurethatyourmachinefulfillsthebasichardwareandsoftwarerequirementsforanOracle Databaseinstallation.Ifyourmachinedoesnotmeetarequirement,anerrormessageis displayed.Therequirementsmayvarysomewhatdependinguponthetypeofcomputerand operationsystemyouarerunning,butsomeexamplesofprerequisitesinclude: Aminimumof512MBofmemoryisavailable. Sufficientpagingspaceisavailable. Appropriateoperatingsystemservicepacksorpatchesareinstalled. Anappropriatefilesystemformatisbeingused. TheinstallerautomaticallysetsanyoperatingsystemenvironmentvariablesthattheOracle databaseserverrequiresforitsoperation.

Basic Installation Choices

WhenyouinstallOracleDatabaseduringbasicandadvancedinstallations,youmakethe followingchoices. Whatdatabaseproductdoyouwanttoinstall? Youcaninstalloneofthefollowing: Enterprise EditionOracle'sfullfeatureddatabaseproductprovidingdatamanagement forenterpriselevelapplications.Itisengineeredformissioncritical,highsecurityonline transactionprocessing(OLTP)anddatawarehousingenvironments. Standard EditionThisinstallationtypeissuitableforworkgroupordepartmentlevel applications,andforsmalltomediumsizedenterprises.Itisengineeredtoprovidecore relationaldatabasemanagementservicesandoptions.

Personal Edition (Windowsoperatingsystemsonly)Thisinstallationtypeinstallsthe samesoftwareastheEnterpriseEditioninstallationtype,butsupportsonlyasingleuser developmentanddeploymentenvironment. Custom InstallThisinstallationtypeenablesyoutocustomizetheEnterpriseEdition.Use CustomInstalltoaddacomponenttoyourexistinginstallationthatwouldnotnormallybe installedorpreventcertaincomponentsfrombeinginstalled.

Best Practice:

Oraclerecommendsthatyouinstallapreconfigureddatabase, whichisfasterandeasiertodo.Youcancustomizethedatabase later.

TheGlobalDatabaseNameisthefullnameofthedatabasethatuniquelydistinguishesitfrom anyotherdatabase.Theglobaldatabasenameisoftheform database_name.database_domain,asinsales.us.acme.com.Thedatabasenameportion sales isasimplenameyoucallyourdatabase.Thedatabasedomainportionus.acme.com specifiesthedatabasedomaininwhichthedatabaseislocated.Togetherdatabasenameand domainmakeuptheGlobalDatabaseName. Duringbasicinstallation,Oracleautomaticallyinstallsthesampleschemas.Manyofthe examplesinthedatabasedocumentationarebasedonthesampleschemas.. YouwillalsobepromptedtoenteryourdatabaseschemapasswordfortheSYS andSYSTEM accounts,whichenableyoutomanageandadministerthedatabase.

Whatkindofstoragemechanismdoyouwantyourdatabasetouse? Adatabaseincludesseveralfilesthatstoretheuserdata,databasemetadata,aswellas informationrequiredtorecoverfromfailures.Asanadministrator,youdecidewhatkindof storagesubsystemtouseforthesefiles.Youcanselectfromthefollowingoptions: File SystemThisdefaultoptioncreatesdatabasefilesthataremanagedbyyouroperating system'sfilesystem.Youcanspecifythedirectorypathwheredatabasefilesaretobestored. OracleDatabasecancreateandmanagetheactualfiles. Ifyouarenotcertainofwhichoptiontouse,choosethedefaultselectionofFileSystem. Automatic Storage ManagementThisfunctionalityenablesyoutodefineapoolof storage(calledadiskgroup)inwhichOracleautomaticallymanagesdatabasefileplacementand naming.Forenvironmentswithalargenumberofdisks,thisoptionsimplifiesdatabase administrationandmaximizesperformance.Diskmirroringandstripingareperformed automatically.

AutomaticStorageManagementrequiresaseparateinstancetoconfigureandmanagedisks groups.TheinstallerguidesyouthroughthecreationandlaunchingofanAutomaticStorage Managementinstanceanditscomponents.

Raw DevicesThisoptionenablesyoutomanageyourstoragedevicesoutsideofthe operatingsystemfilesystembyprovidingOracleDatabasewithunformattedphysicaldiskspace calledrawdevices.ThismethodisprimarilyusedinOracleRealApplicationClusters(RAC) environments.Ifyouselectthisoption,ensurethattherawdeviceshavebeencreatedwithan operatingsystemcommand,andthattheyarenotbeingusedforanyotherpurposesorbyother Oracledatabases.

WhatareyourDatabaseManagementOptions? Hereyouspecifywhethertomanageyourdatabasecentrallyorlocally.Thecentraldeployment modeenablesyoutomanagemultipletargets,suchasdatabasesandapplicationservers,froma singleconsole.Thelocaldeploymentmodeontheotherhandcanmanageonlyasingledatabase instance. Thecentraldeploymentmoderequiresaspecialagenttobeinstalledonthedatabasehost.Ifno suchagentisfound,thenthisoptionisdisabled. Ifyouaresettingupasingledatabaseforthefirsttime,itisrecommendedthatyoudeploy EnterpriseManagerlocally,whichisthedefault.Youcanlaterinstalladditionaldatabasesand migrateyourdeploymentmodetocentralmanagement. Attheendoftheinterview,asummarywindowdisplaystheoptionsthatyouhavechosen,the requirementsforspacebasedonthoseoptions,andthecomponentsthatwillbeinstalled.Ifyou havechosentoinstalladatabase,thenthisstepisperformedimmediatelyafterthesoftware installationiscomplete.

Installing Oracle Software and Database

Thissectiondescribesthebasicinstallationsteps.Moststepsarecommontoallplatformsand involverunningtheOracleUniversalInstaller.Platformspecificstepsarenoted. Thefollowingstepsprovideasummaryoftheinstallationprocess.Forfurtherassistancealong theway,consulttheonlinehelportheOracle Database Installation Guide foryourplatform. 1.Logontoyourcomputerasamemberoftheadministrativegroupthatisauthorizedtoinstall Oraclesoftwareandtocreateandrunthedatabase.Refertoyouroperatingsystemspecific documentationorcontactyoursystemadministratortodeterminewhetheryouhavethe necessaryprivilegestoinstallnewsoftware. 2.Insertthedistributionmediaforthedatabaseintoyourcomputer.TheAutorunwindowopens automatically.ClickInstall/Deinstall Products. IfdownloadingfromOracle'sWebsite,thenfollowtheinstructionsonthesite. 3.TheOracleUniversalInstallerSelectInstallationMethodwindowappears.Figure21shows whatthiswindowlookslikeonWindows.Installing Oracle Software and Database Installing
Oracle and Building the Database 2-5

Figure 21 Oracle Universal Installer Select Installation Method Window 4.Youmustnowdecidewhichtypeofinstallationtoperform:

Basic InstallationSelectthisoptiontoquicklyinstallOracleDatabase10g.Thismethod requiresminimaluserinput.Itinstallsthesoftwareandoptionallycreatesageneralpurpose databasebasedontheinformationyouprovide. ForBasicinstallation,youspecifythefollowing: Oracle Home LocationEnterthedirectoryinwhichtoinstalltheOracleDatabase10g software.YoumustspecifyanewOraclehomedirectoryforeachnewinstallationofOracle Database10g. Installation TypeSelecteitherEnterprise Edition,Standard Edition,or Personal Edition (Windows Only).Referto"BasicInstallationChoices"onpage22. UNIX DBA Group (LinuxandUnixonly)SpecifytheDBAgroupforyouroperating system. Create Starter Database Checkthisboxtocreateadatabaseduringinstallation.Oracle recommendsthatyoucreateastarterdatabaseforfirsttimeinstallations.Youmustentera Global Database Name andDatabase Password fortheSYS,SYSTEM,SYSMAN,and DBSNMP administratoraccounts.TheGlobalDatabaseNameisdescribedin"BasicInstallation Choices". TostarttheBasicInstallation,clickNext.ThePreparingtoInstallprogressbarappears. Advanced InstallationSelectthisoptiontocustomizeyourinstallation.Forexample,use thismethodtoinstallOracleRealApplicationClusters,toupgradeadatabase,toconfigure AutomaticStorageManagement,ortoconfigureautomatedbackups.Selectingthisoptionsteps youthroughinstallationstepsnotdocumentedhere.FormoreinformationabouttheUsing

DBCA to Create and Configure a Database 2-6 Oracle Database 2 Day DBA

advancedchoices,see"AdvancedInstallationChoices"inthischapter.AlsoseetheOracle Database Installation Guide foryourplatform. 5.TheProductSpecificPrerequisiteCheckswindowappears.Referto"CheckingPrerequisites" onpage21.TheInstallerperformsanumberofenvironmentchecksandindicatessuccess, warning,orfailure.Detailsofthechecksareprovided.Theinstallationcanonlyproceedwith checkstatusofSucceededorWarning.Ifyouhavefailedchecks,youmustresolvethem manually. Afterverifyingthatyoursystemmeetstheminimumrequirementsforinstallingandconfiguring thechosenproducts,clickNext. 6.Linux and Unix Only.IfthisisthefirsttimeyouareinstallinganyOraclesoftwareonthis machine,thenthefollowingoccurs: TheSpecifyInventoryDirectorywindowappears.Youmustspecifyadirectoryforinstallation filesandthenameofanoperatingsystemgroupthathaswritepermissiontothedirectory.OUI usesthislocationtokeeptrackofallOraclesoftware.Thisinformationisusedwhileapplying patchestoorupgradinganexistinginstallation,andwhiledeinstallingOraclesoftware.Notethat thisareaisdifferentfromtheOraclehome,whichyouspecifylater.ClickNext tocontinue. 7.Asummaryscreenappearsshowinginformationsuchasyourglobalsettings,space requirementsandthenewproductstobeinstalled.ClickInstall tostarttheinstallation.The Installwindowappearsshowinginstallationprogress. 8.Attheendoftheinstallationphase,theConfiguration Assistants windowappears.This windowliststheconfigurationassistantsthatarestartedautomatically.Ifyouarecreatinga database,thentheDatabaseConfigurationAssistantstartsautomaticallyinaseparatewindow. Attheendofdatabasecreation,youarepromptedtounlockuseraccountstomaketheaccounts accessible.BecausetheSYS andSYSTEM accountsarealreadyunlocked,clickOK tobypass passwordmanagement. 9.Linux and Unix Only.TheExecute Configuration scripts windowappears.Youare promptedtoopenanewterminalwindow,andtorunscriptsastherootuser.Afteryourunthe scripts,returntothiswindowandclickOk. 10.Yourinstallationanddatabasecreationisnowcomplete.TheEndofInstallationwindow displaysseveralimportantURLs,oneofwhichisforEnterpriseManager.Youcannavigateto thisURLinyourbrowserandloginastheSYS userwiththeassociatedpassword,andconnect asSYSDBA.YouuseEnterpriseManagertoperformcommondatabaseadministrationtasks. SeeChapter3,"GettingStartedwithOracleEnterpriseManager". 11.AfternotingtheinformationintheEndofInstallationscreen,youcanexittheInstaller.

Using DBCA to Create and Configure a Database

Duringinstallation,youcandirecttheinstallertocreateandconfigureanewdatabase.Ifyou havealreadydoneso,youcanskiptothenextchapter. IfyouinstallOraclesoftwareonly,however,orifyouwanttocreateadditionaldatabaseswith thereleasesoftwarethatyoujustinstalled,thenyoucanusetheDatabaseConfiguration Assistant(DBCA). DBCAenablesyoutoperformthefollowingtasks: CreatingaDatabasewithDBCAUsing DBCA to Create and Configure a Database Installing
Oracle and Building the Database 2-7

ConfiguringDatabaseOptionswithDBCA DeletingaDatabasewithDBCA ManagingTemplateswithDBCA ConfiguringAutomaticStorageManagementwithDBCA OnlineHelpisavailablebyclickingHelp.Itprovidesinformationthatguidesyouinselecting configurationoptions.

Starting DBCA

TolaunchDBCA: 1.Logontoyourcomputerasamemberoftheadministrativegroupthatisauthorizedtoinstall Oraclesoftwareandcreateandrunthedatabase. 2.TolaunchDBCAonaWindowsoperatingsystem,clicktheStart buttonandthenselect Programs,Oracle - home_name,Configuration and Migration Tools,andthen Database Configuration Assistant. TolaunchtheDBCAonUNIX,orasanothermethodonaWindowsoperatingsystem,enterthe followingcommandatasystemprompt:
dbca

Thedbca utilityistypicallylocatedinORACLE_HOME/bin. TheWelcomewindowappears. 3.ClickNext tocontinue.

Creating a Database with DBCA

OntheDBCAOperationswindow,selectCreate a Database tobeginawizardthatenables youtoconfigureandcreateadatabase.Duringtheinterview,youareaskedforyourinputonthe following: DatabaseTemplates DatabaseIdentification ManagementOptions DatabaseCredentials StorageOptions DatabaseFileLocations RecoveryConfiguration DatabaseContent InitializationParameters DatabaseStorage CreationOptions Notethatmostwindowsofthewizardprovideadefaultsettingthatyoucanaccept.Toacceptall thedefaultparameters,youcanclickFinish atanystep.DBCAdisplaysafinalConfirmation windowinwhichyoumustclickOK toinitiatethedatabasecreation.Using DBCA to Create
and Configure a Database 2-8 Oracle Database 2 Day DBA

Database Templates
Thiswindowenablesyoutoselectthetypeofdatabaseyouwanttocreate.Bydefault,Oracle shipspredefinedtemplates.TherearetemplatesforData Warehouse,General Purpose, andTransaction Processing databases. Thetemplatescontainsettingsoptimizedforworkload.ClickShow Details toseethe configurationforeachtypeofdatabase.Choosethetemplatesuitedtothetypeofworkloadyour databasewillsupport.Ifyouarenotsurewhichtochoose,selectthedefaultGeneral Purpose template. Formorecomplexenvironments,youcanselecttheCustom Database option.Thisoption resultsinamoreextensiveinterview,whichmeansthatitwilltakelongertocreateyourdatabase becauseadatabasecreationscriptmustberun. Formoreinformationaboutusingdatabasetemplates,see"ManagingTemplateswithDBCA"on page212.

Database Identification

IntheGlobal Database Name box,enterthedatabasenameintheform database_name.domain_name. IntheSID box,entertheOraclesystemidentifier.TheSIDdefaultstothedatabasenameand uniquelyidentifiestheinstancethatrunsthedatabase.

Management Options

UsethiswindowtosetupyourdatabasesoitcanbemanagedwithOracleEnterpriseManager, whichprovidesWebbasedmanagementtoolsforindividualdatabases,aswellascentral managementtoolsformanagingyourentireOracleenvironment. CheckConfigure the Database with Enterprise Manager to use Enterprise Manager. Selectoneofthefollowingoptions: IftheOracleManagementAgenthasbeeninstalledonyourhostcomputer,thenyouhavethe optionofselectingcentralmanagementbyselectingUse Grid Control for Database Management.Ifyouselectthistypeofmanagement,youmustalsoindicatewhich managementservicetouseinthedropdownmenu. SelectUse Database Control for Database Management tomanageyourdatabase locally.Ifyouchoosethisoption,youcanadditionallycheckEnable Email Notifications forOracletoemailyoualertsregardingpotentialproblems,andcheckEnable Daily Backup. ClickHelp formoreinformationabouttheseoptions.

Database Credentials

Inthiswindow,youspecifythepasswordsfortheadministrativeaccountssuchasSYS and SYSTEM.Selectoneofthefollowingoptions: SelectUse the Same Password for All Accounts andenterthepasswordtousethe samepasswordforallaccounts. SelectUse Different Passwords andspecifypasswordsindividually.

Storage Options

Specifythetypeofstoragemechanismyouwouldlikeyourdatabasetouse.Formore information,referto"InstallationChoices"earlierinthischapter.Using DBCA to Create and

Configure a Database Installing Oracle and Building the Database 2-9

Database File Locations

Inthiswindow,youspecifytheOraclehomeanddirectorypathinwhichtoinstalltheOracle software.Chooseoneofthefollowing: Use Database File Locations from TemplateSelectingthisoptioninstructsthe DBCAtousethedirectoryinformationasspecifiedinthetemplate.Ifyouchoosethisoption,you willhaveanopportunitylatertomakemodificationstodatabasefilenamesandlocations. Use Common Location for All Database FilesThisoptionrequiresyoutospecifya newdirectoryfortheOraclehome.Allthedatabasefileswillbecreatedinthislocation.Ifyou choosethisoption,youwillhaveanopportunitylatertomakemodificationstodatabase filenamesandlocations. Use Oracle-Managed FilesSelectthisoptiontoeliminatetheneedfortheDBAto directlymanageoperatingsystemfilescomprisinganOracledatabase.Youspecifydefault locationcalledadatabaseareaforallyourfiles.Oraclethereafterautomaticallycreatesand deletesfilesinthislocationasrequired.Youalsohavetheoptiontocreatemultiplecopiesofyour redoandonlinelogfilesbyselectingMultiplex Redo Logs and Control Files.Tolearn moreaboutredologsandcontrolfiles,refertoChapter9,"PerformingBackupandRecovery". Selectingthisoptionenablesyoutodelegatethecompletemanagementofdatabasefilestothe database.Younolongerneedtospecifythefilenames,location,ortheirsizes.

Recovery Configuration

Whenyoucreateanewdatabase,itisimportanttoconfigurethedatabasesoyoucanrecover yourdataintheeventofasystemfailure.Youcanselectthefollowingoptions: ChooseSpecify Flash Recovery Area tospecifyabackupandrecoveryareaandspecify itsdirectorylocationandsize.Youcanusevariablestoidentifysomestandardlocations.To revieworaddadditionallocations,clickFile Location Variables atthebottomofthe window. CheckEnable Archiving toenablearchivingofdatabaseredologs,whichcanbeusedto recoveradatabase.SelectingthisoptionisthesameasenablingArchiveLogModeinOracle EnterpriseManagerorrunningthedatabaseinARCHIVELOG mode.Youcanacceptthedefault archivemodesettingsorchangethembyselectingEdit Archive Mode Parameters. OraclerecommendsyouselectEnable Archiving.Selectingthisoptionprovidesbetter protectionforyourdatabaseinthecaseofsoftwareorhardwarefailure.Ifyoudonotselectthis optionnow,youcansetuparchivelogmodelater.See"ConfiguringYourDatabaseforBasic BackupandRecovery"onpage94.

Database Content

IntheSample Schemas tab,checkSample Schemas toincludetheSampleSchemas (EXAMPLE)tablespaceinyourdatabase.TheSampleSchemasprovideacommonplatformfor examples.OraclebooksandeducationalmaterialscontainexamplesbasedupontheSample Schemas.Oraclerecommendstheybeincludedinyourdatabase. IntheCustom Scripts tab,youcanspecifyoneormoreSQLscriptstorunafteryourdatabase iscreated.Scriptsareusefulforperformingpostinstallationtasks,suchasloadingcustom schemas.Tospecifyscripts,checkRun the following scripts;otherwiseacceptthedefault No scripts to run.NotethatifyouchoosetorunscriptsUsing DBCA to Create and
Configure a Database 2-10 Oracle Database 2 Day DBA

afterinstallation,yourscriptsmustincludeaconnectstringthatidentifiesthedatabase.Click Help formoreinformation.

Initialization Parameters

Thelinksonthiswindowprovideaccesstowindowsthatenableyouchangedefaultinitialization parametersettings.Theseparametersfallintothefollowingcategories: Memory Sizing CharacterSets ConnectionMode Memory Usethiswindowtosettheinitializationparametersthatcontrolhowthedatabasemanagesits memoryusage.Youcanchoosefromoneofthefollowingapproachestomemorymanagement: Typicalrequireslittleconfiguration,andallocatesmemoryasapercentageoftotaloverall physicalsystemmemory.Tousethismethod,selectTypical andenterapercentagevalue.Click Show Memory Distribution toseehowmuchmemorytheAssistantassignstotheSystem GlobalArea(SGA)andtheProgramGlobalArea(PGA).TolearnmoreaboutPGAandSGA, referto"ManagingMemoryParameters"inChapter5,"ManagingtheOracleInstance". Customrequiresmoreconfiguration,butgivesyoumorecontroloverhowthedatabaseuses systemmemory.Thisoptionismeantformoreexperienceddatabaseadministrators.Youcan directlyspecifymemorysizesfortheSGAandPGAandtheirsubstructures,suchastheshared poolandbuffercache. Selectoneofthefollowingoptions: SelectAutomatic toallocatespecificamountsofmemorytoSGAandPGA. SelectManual andenterspecificvaluesforeachSGAcomponenttocustomizehowtheSGA memoryisdistributedamongtheSGAmemorysubstructures. TocustomizehowtheSGAmemoryisdistributedamongtheSGAmemorysubstructures,select Manual andenterspecificvaluesforeachSGAcomponent.Foracompletedescriptionofthese memoryareas,clickHelp. Sizing Inthistab,youspecifythesmallestblocksizeandthemaximumnumberofoperatingsystem userprocessesthatcansimultaneouslyconnecttothedatabase. IntheBlock Size list,enterthesizeinbytesoracceptthedefault.Oracledatabasedatais storedintheseblocks.Onedatablockcorrespondstoaspecificnumberofbytesofphysicalspace ondisk.Whileusingpredefinedtemplates,thisfieldisnotenabledsincethedatabasewillbe createdwiththedefaultblocksizeof8KB.Butwhileusingthecustomoption,youcanchange blocksize.Selectingablocksizeotherthanthedefault8KBvaluerequiresadvancedknowledge andshouldonlybedonewhenabsolutelyrequired. IntheProcesses field,specifythemaximumnumberofprocessesthatcansimultaneously connecttothedatabase.Enterthenumberoracceptthedefaultof150.Thedefaultvalueforthis parameterisgoodenoughformanyenvironments.Thevalueshouldbe6orgreater.Thisvalue shouldallowforallbackgroundprocesses,suchaslocksandparallelexecutionprocesses. CharacterSets Usethiswindowtodefinethecharactersetsusedbyyourdatabase.Charactersetsarethe encodingschemesusedtodisplaycharactersonyourcomputerUsing DBCA to Create and
Configure a Database Installing Oracle and Building the Database 2-11

screen.Choosingacharactersetdetermineswhatlanguagescanberepresentedinthedatabase. ForDatabase Character Set,selectfromoneofthefollowingoptions: Use the DefaultSelectthisoptionifyouneedtosupportonlythelanguagecurrentlyused bytheoperatingsystemforallyourdatabaseusersandyourdatabaseapplications. Use Unicode (AL32UTF8)Selectthisoptionifyouneedtosupportmultiplelanguagesfor yourdatabaseusersandyourdatabaseapplications. Choose from the list of character setsSelectthisoptionifyouwanttheOracle Databasetouseacharactersetotherthanthedefaultcharactersetusedbytheoperatingsystem. IntheNational Character Set list,selectacharactersetoracceptthedefault.Thenational charactersetisanalternativecharactersetthatenablesyoutostoreUnicodecharactersina databasethatdoesnothaveaUnicodedatabasecharacterset.Anotherreasonforchoosinga nationalcharactersetisthatprogramminginthenationalcharactersetmightbeeasier. IntheDefault Language list,selectadefaultdatabaselanguageoracceptthedefault.The defaultlanguagedetermineshowthedatabasesupportslocalesensitiveinformationsuchasday andmonthabbreviations,defaultsortingsequenceforcharacterdata,andwritingdirection(left orright). IntheDefault Date Format list,selectadateformatoracceptthedefault.Thedefaultdate formatdeterminestheconventionfordisplayingthehour,day,month,andyear.Forexamplein theUnitedKingdom,thedateformatisDDMMYYYY. ConnectionMode Usethiswindowtoselectthedatabasemode.Youcanrunthedatabaseineitherofthefollowing modes: SelectDedicated Server Mode toallowadedicatedserverprocessforeachuserprocess. Selectthisoptionwhenthenumberoftotalclientsisexpectedtobesmall,orwhendatabase clientswillmakepersistent,longrunningrequeststothedatabase. SelectShared Server Mode toallowseveralclientconnectionstoshareadatabaseallocated poolofresources.Usethismodewhenalargenumberofusersneedtoconnecttothedatabase.It isalsousefulwhendatabasememoryislimitedandforbetterperformancebecauseyoucanhave moreclientconnectionstothedatabasethanindedicatedservermodewiththesamememory.If youchoosesharedservermode,thenyoumustalsoindicatethenumberofserverprocessesyou wanttocreatewhenaninstanceisstartedup.Formoreinformationaboutsettingthisparameter, clickHelp.

Database Storage

Atreelistingdisplaysthestoragestructureofyourdatabase(controlfiles,datafiles,redolog groups,andsoforth).Ifyouarenotsatisfiedwiththestoragestructureorparameters,thenyou canmakechanges.YoucancreateanewobjectwithCreate anddeleteexistingobjectswith Delete. Notethatifyouselectedoneofthepreconfiguredtemplatesforadatabase,thenyoucannotadd orremovecontrolfiles,datafiles,orundosegments.

Creation Options

Checkanyofthefollowingoptions:Using DBCA to Create and Configure a Database 2-12

Oracle Database 2 Day DBA

Create DatabaseChecktocreateyourdatabaseatthistime. Save as a Database TemplateChecktosavethedatabasedefinitionasatemplateto useatanothertime. Generate Database Creation ScriptsChecktogenerateaSQLdatabasecreation scriptthatyoucanrunatalatertime.

Configuring Database Options with DBCA

IntheOperationswindow,selectConfigure Database Options tochangevariousaspects ofyourdatabaseconfiguration.Forexample,youcanchangeyourconfigurationfroma dedicatedservertoasharedserver.Youcanadddatabaseoptionsthathavenotbeenpreviously configuredforusewithyourdatabase,forexample,OracleLabelSecurityorOracleOLAP.

Deleting a Database with DBCA

IntheOperationswindow,selectDelete a Database toremoveadatabasefromthesystem. Whenyouselectthisoption,DBCAdeletesallthefilesassociatedwiththisdatabase.On Windows,anyassociatedservicesarealsodeleted.

Managing Templates with DBCA

DBCAtemplatesareXMLfilesthatcontaininformationrequiredtocreateadatabase.Templates areusedinDBCAtocreatenewdatabasesandcloneexistingdatabases.Theinformationin templatesincludesdatabaseoptions,initializationparameters,andstorageattributes(for datafiles,tablespaces,controlfiles,andonlineredologs). Templatescanbeusedjustlikescripts,buttheyaremorepowerfulthanscriptsbecauseyouhave theoptionofcloningadatabase.Cloningsavestimebecausecopyinganalreadycreatedseed databasesfilestothecorrectlocationstakeslesstimethancreatingthemasnew. Templatesarestoredinthefollowingdirectory:

ORACLE_HOME/assistants/dbca/templates

Advantages of Using Templates

Usingtemplateshasthefollowingadvantages: Theysaveyoutime.Ifyouuseatemplateyoudonothavetodefinethedatabase. Bycreatingatemplatecontainingyourdatabasesettings,youcaneasilycreateaduplicate databasewithoutspecifyingparameterstwice. Theyareeasilyedited.Youcanquicklychangedatabaseoptionsfromthetemplatesettings. Templatesareeasytoshare.Theycanbecopiedfromonemachinetoanother.

Types of Templates

Templatesaredividedintothefollowingtypes: Seedtemplates Nonseedtemplates ThecharacteristicsofeachareshowninTable21.

Table 21 DBCA Template Types Type File Include Database Structure Extensi Datafile on s? .dbc Seed Yes Thistypeoftemplatecontainsboththestructureandthephysical datafilesofanexisting(seed)database.Whenyouselectaseed template,databasecreationisfasterbecausethephysicalfilesand schemaofthedatabasehavealreadybeencreated.Yourdatabasestarts asacopyoftheseeddatabase,ratherthanhavingtobebuilt. Youcanchangeonlythefollowing: Nameofthedatabase Destinationofthedatafiles Numberofcontrolfiles Numberofredologgroups Initializationparameters Otherchangescanbemadeafterdatabasecreationusingcustomscripts thatcanbeinvokedbyDBCA,commandlineSQLstatements,orthe OracleEnterpriseManager. Thedatafilesandonlineredologsfortheseeddatabasearestoredina compressedformatinanotherfilewitha.dfj extension.The corresponding.dfj fileslocationisstoredinthe.dbc file. Nonseed .dbt No Thistypeoftemplateisusedtocreateanewdatabasefromscratch.It containsthecharacteristicsofthedatabasetobecreated.Nonseed templatesaremoreflexiblethantheirseedcounterpartsbecauseall datafilesandonlineredologsarecreatedtoyourspecification(not copied),andnames,sizes,andotherattributescanbechangedas required.

Table 21 DBCA Template Types Type File Include Database Structure Extensi Datafile on s?

Seed

.dbc

Yes

Nonseed

.dbt

No

This type of template contains both the structure and the physical datafiles of an existing (seed) database. When you select a seed template, database creation is faster because the physical files and schema of the database have already been created. Your database starts as a copy of the seed database, rather than having to be built. You can change only the following: Name of the database Destination of the datafiles Number of control files Number of redo log groups Initialization parameters Other changes can be made after database creation using custom scripts that can be invoked by DBCA, commandline SQL statements, or the Oracle Enterprise Manager. The datafiles and online redo logs for the seed database are stored in a compressed format in another file with a .dfj extension. The corresponding .dfj files location is stored in the .dbc file. This type of template is used to create a new database from scratch. It contains the characteristics of the database to be created. Non-seed templates are more flexible than their seed counterparts because all datafiles and online redo logs are created to your specification (not copied), and names, sizes, and other attributes can be changed as required.

Overview of the Database Upgrade Assistant

TheDatabaseUpgradeAssistant(DBUA)interactivelystepsyouthroughtheupgradeprocess andconfiguresthedatabaseforthenewOracleDatabase10g release.TheDBUAautomatesthe upgradeprocessbyperformingallofthetasksnormallyperformedmanually.TheDBUAmakes appropriaterecommendationsforconfigurationoptionssuchastablespacesandredologs.You canthenactontheserecommendations. TheDBUAprovidessupportforRealApplicationClusters(RAC)andAutomaticStorage Management(ASM).

Before the Upgrade

TheDatabaseUpgradeAssistantdoesnotbegintheupgradeuntilitcompletesallofthe followingpreupgradesteps: Checksforanyinvaliduseraccountsorroles Checksforanyinvaliddatatypesorinvalidobjects Checksforanydesupportedcharactersets Checksforadequateresources,includingrollbacksegments,tablespaces,andfreediskspace ChecksforanymissingSQLscriptsneededfortheupgrade Backsupallnecessaryfiles(optional)

During the Upgrade

TheDatabaseUpgradeAssistantautomaticallyperformsthefollowingtasks: Modifiesorcreatesnewrequiredtablespaces Invokestheappropriateupgradescripts Archivesredologs Disablesarchivingduringtheupgradephase Whiletheupgradeisrunning,theDBUAshowstheupgradeprogressforeachcomponent.The DBUAwritesdetailedtraceandlogfilesandproducesacompleteHTMLreportforlater reference.Toenhancesecurity,theDBUAautomaticallylocksnewuseraccountsintheupgraded database.TheDBUAthenproceedstocreatenewconfigurationfiles(initializationparameterand listenerfiles)inthenewOraclehome.

Oracle Database Version Restrictions

TheDatabaseUpgradeAssistantsupportsthefollowingversionsoftheOracleDatabasefor upgradingtoOracleDatabase10g: Oracledatabaseversion8.0.6(singleinstancedatabasesonly) Oracle8i Release8.1.7(singleinstanceandRealApplicationClustersdatabases) Oracle9i Release1(9.0.1)(singleinstanceandRealApplicationClustersdatabases) Oracle9i Release9.2.0(singleinstanceandRealApplicationClusterdatabases) Ifyourdatabaseversionisnotinthislist,youneedtoupgradefirsttotheclosestreleaseaslisted. Youcanthenupgradethedatabasetothecurrentrelease.

Note:

Ifyoustoptheupgrade,butdonotrestorethedatabase,thenyou shouldnotrestarttheDBUAuntilyoustartuptheexisting databaseinUPGRADE modeusingthe10.2server.Youcannotgo backtotheoriginalserverunlessyourestoreyourdatabase. Ifyourestoreyourdatabasemanually(notusingtheDBUA),then removethefollowingfilebeforestartingtheDBUA: $10.2OracleHome/cfgtoollogs/dbua/logs/Welcome_<SID>.t xt.ThepresenceofthisfileindicatestotheDBUAthatthisisare runoperation.

TolaunchtheDBUAontheWindowsoperatingsystemchoosethefollowingmenuoption:

Start > Programs > Oracle - HOME_NAME > Configuration and Migration Tools > Database Upgrade Assistant

TolaunchtheDBUAonaUNIXorLinuxoperatingsystem,enterthefollowingatacommand prompt:
dbua

Thiscommandistypicallyfoundinyour$ORACLE_HOME/bin directory.

Starting and Stopping the Oracle Enterprise Manager Console

ToaccesstheOracleEnterpriseManagerConsolefromaclientbrowser,thedbconsole process needstoberunningontheserver.Thisprocessisautomaticallystartedafterinstallation. However,iftheprocessisnotrunningbecauseofasystemrestartorotherevent,youcan manuallystartitatthecommandline. Tostartthedbconsole process: 1.NavigateintoyourORACLE_HOME/bin directory 2.Runthefollowingstatement:Accessing the Oracle Enterprise Manager Database Control
Getting Started with Oracle Enterprise Manager 3-3

./emctl start dbconsole

Additionally,youcanstoptheprocessandviewitsstatus. Tostopthedbconsole process:

./emctl stop dbconsole

Toviewthestatusofthedbconsole process:
./emctl status dbconsole

Starting dbconsole in Windows

InWindows,inadditiontousingthecommandline,youcanstartthedbconsole processasa service. Tostartdbconsole asaservice: 1.ClickStart,ControlPanel,AdministrativeTools,Services.TheServicespageappears. 2.OracleservicesbeginwithOracle.Thedbconsole serviceislistedas OracleDBConsoleORACLE_SID,whereORACLE_SID isyourSID.Thestatusofthisprocessis listedintheStatuscolumn,eitherStartedorStopped.Doubleclicktheservice.Thepropertypage appears. 3.Inthepropertiespage,ensurethattheStartupTypeiseitherManualorAutomaticandnot Disabled.ClickStart,iftheprocessisnotalreadystarted.ClickOK. YoucanalsousetheServicespagetostoptheprocess.

Accessing the Oracle Enterprise Manager Database Control

Attheendofasuccessfuldatabasecreation,OracleEnterpriseManagerisautomatically launchedandthedatabaseisstarted.Atothertimes,providedthedbconsoleprocessisrunning ontheserver,youcannavigatetoyourDatabaseControlfromyourclientbrowserasfollows: 1.PointyourWebbrowsertothefollowingURL: http://hostname:portnumber/em Forexample,ifyouinstalledthedatabaseonahostcomputernamedcomp42,andtheinstaller indicatedthatyourEnterpriseManagerConsoleHTTPportnumberis5500(alsorecordedinthe $ORACLE_HOME/install/portlist.ini file),enterthefollowingURL: http://comp42:5500/em Ifthedatabaseisup,EnterpriseManagerdisplaystheDatabaseControlLoginpage. Ifthedatabaseisdownandneedstoberestarted,EnterpriseManagerdisplaysthe Startup/ShutdownandPerformRecoverypage.Ifthisisthecase,clickStartup/Shutdown.You arethenpromptedforthehostandtargetdatabaseloginusernamesandpasswords,whichyou mustenter.Forthedatabaseuserandpassword,useSYS andthepasswordyouspecifiedduring installation. ClickOK tostartthedatabase.IntheConfirmationscreen,clickYES tostartthedatabasein openmode.Granting Database Control Administrative Privileges 3-4 Oracle Database 2 Day
DBA

2.LogintothedatabaseusingausernamethatisauthorizedtoaccesstheDatabaseControl.This

initiallycouldbeSYS orSYSTEM.Usethepasswordyouspecifiedfortheaccountduringthe databaseinstallation. EnterpriseManagerdisplaystheDatabaseHomepage(Figure34). Thepropertypagesacrossthetopofthepageenableyoutoaccessperformance,administration, andmaintenancepagesformanagingyourdatabase.Thefunctionalityprovidedbythesepages isdiscussedinotherchaptersofthisbook. ThevarioussectionsoftheDatabaseHomepageandrelatedlinksprovideinformationaboutthe databasesenvironmentandhealth.Forexample,theAlerts,Related Alerts,and Diagnostic Summary sectionswarnyouoferrorsandperformanceproblemsthatare impactingtheoperationofyourdatabase.Youcanclicktheprovidedlinkstoseemoredetail abouttheproblemarea,andeventoobtainrecommendationsforresolvingtheproblem.Thisis discussedinChapter10,"MonitoringandTuningtheDatabase".
Figure 34 Database Home Page

Granting Database Control Administrative Privileges

WhenyoulogintotheOracleEnterpriseManagerDatabaseControlusingtheSYS orSYSTEM useraccount,youarelogginginastheOracleEnterpriseManagersuperuser.Thesearetheonly accountsthatareautomaticallygrantedtherolesandprivilegesrequiredtoaccessallthe managementfunctionalityprovidedtheDatabaseControl. Tograntmanagementaccesstootherdatabaseusers,usethefollowingprocedure:Setting Up
Enterprise Manager Preferences Getting Started with Oracle Enterprise Manager 3-5

1.StartyourWebbrowserandlogintotheDatabaseControlastheSYS orSYSTEM database

user. 2.ClickSetup atthetopoftheDatabaseHomepage. 3.ClickAdministrators intheleftnavigationbar. Ifyouneedtocreateanewadministrator,youmustfirstcreatetheuserandthenassign administrativeprivilegesasdescribedinthissection.Tocreateanewuser,see"Administering DatabaseUsers"onpage78 4.ClickCreate tocreateanewEnterpriseManageruserbyassigningthemanagement privilegestoanexistingdatabaseuser. 5.ClicktheflashlighticonnexttotheNamefieldandselectanexistingdatabaseuserfromthe popupwindow. 6.EnterthepasswordfortheselecteduserandclickFinish. EnterpriseManagerassignsthemanagementprivilegestotheselecteduser.Theselected databaseuserisnowincludedinthelistofmanagementusersontheSetupAdministratorspage. AlltheusersshownonthispagecanlogintotheDatabaseControlandperformdatabase managementtasks.

AclientisanyapplicationthatneedstoconnecttotheOracledatabasetosendorretrievedata. AnOracleclientapplicationcanresideonanymachineprovidedithasOracleclientsoftware installed. OracleNetisasoftwarecomponentthatresidesontheclientandtheOracledatabaseserver.Itis responsibleforestablishingandmaintainingtheconnectionbetweentheclientapplicationand theserver,aswellasexchangingmessagesbetweenthem,usingindustrystandardprotocols. For the client application and a database to communicate, the client application must specify location details for the database it wants to connect to and the database must provide some sort of identification, or address.

Connection Requests

Usersinitiateaconnectionrequestbyprovidingaconnectstring.Aconnectstringincludesa usernameandpassword,alongwithaconnectidentifier.Thisconnectidentifiercanbethe connectdescriptoritself,oranamethatresolvestotheconnectdescriptor.Oneofthemost commonconnectidentifiersisanetservicename,asimplenameforaservice.Thefollowing exampleshowsaconnectstringthatusesnetservicenamemydb astheconnectidentifier.

CONNECT tatahms/tatahms@production

Local Naming
Thelocalnamingmethodstoresconnectdescriptors,identifiedbytheirnetservicename,ina local(ontheclient)configurationfilenamedtnsnames.ora locatedinthe ORACLE_HOME/network/admin directory.

Directory Naming

ThedirectorynamingmethodstoresconnectidentifiersinacentralizedLDAPcompliant directoryservertoaccessadatabaseservice.

Easy Connect Naming

TheeasyconnectnamingmethodenablesclientstoconnecttoanOracledatabaseserverbyusing aTCP/IPconnectstringconsistingofahostnameandoptionalportandservicename:
CONNECT username/password@host[:port][/service_name]

Forexample:
CONNECT hr/hr@my-server:1521/mydb

Theeasyconnectnamingmethodrequiresnoconfiguration.

External Naming

TheexternalnamingmethodstoresnetservicenamesinasupportednonOraclenamingservice. Thesesupportedthirdpartyservicesinclude: NetworkInformationService(NIS)ExternalNaming DistributedComputingEnvironment(DCE)CellDirectoryServices(CDS)

Starting and Shutting Down the Listener

TheOraclelistenerissetuptostartautomaticallywheneveryourservermachineisrestarted. However,whenyoursystemencountersunforeseencircumstances,orwhenyouhavemanually stoppedthelistener,youcanrestartitatthecommandline.Todoso,usethefollowing:
lsnrctl start

YoucanuseEnterpriseManagertostopthelistener.Todoso,navigatetotheListener: listener_namepagebyclickingListener ontheHomepage.Toshutdownthelistener,click Stop. Youcanalsostopitatthecommandlineusingthefollowing:

lsnrctl stop

Overview of an Instance and Instance Management

YourOracledatabaseiscomprisedofasetofoperatingsystemfilescontainingdataenteredby usersorapplicationsandstructuralinformationaboutthedatabaseitselfcalleddatabase metadata.Informationisstoredpersistentlyinthesefiles. Inorderforyoutovieworupdatethedatacontainedinthedatabase,Oracleneedstostartaset ofprocesses,calledbackgroundprocesses,andneedstoallocatesomememorytobeusedduring databaseoperation.ThebackgroundprocessesandmemoryallocatedbyOracletogethermake upaninstance.Thereforebeforethedatabasecanbeused,thedatabaseinstancemustbestarted. Whenthedatabaseinstanceisnotavailable,yourdataissafeinthedatabasebutitcannotbe accessedbyanyuserorapplication. Thepropertiesofadatabaseinstancearespecifiedusinginstanceinitializationparameters.When theinstanceisstarted,aninitializationparameterfileisreadandtheinstanceisconfigured accordingly. TheOracleinstanceandtheOracledatabaseareseparateentities,althoughtheterminstanceis oftenusedtomeanoneortheotherorboth.Technically,theyaredistinguishedasfollows: AnOracleinstanceconsistsofthesharedmemorystructuresandbackgroundprocessesthatrun theOracledatabase.Youcanhaveaninstancewithoutadatabase(forexample,whenyouhave notyetcreatedadatabase),andifadatabaseexists,itcanbeopenornot. AOracledatabasereferstothephysicalandlogicalcomponentsofaspecificdatabase,andits operation.

About Initialization Parameters

Instancemanagementinvolvesconfiguringparametersthataffectthebasicoperationofthe databaseinstance.Theseparametersarecalledinitializationparameters.TheOracledatabase serverreadstheseparametersatdatabasestartupandmonitorsthemwhilethedatabaseis running.Theyarestoredinmemory,wheremanyofthemcanbechangeddynamically.There aretwotypesofparameterfiles,andwhetherthesedynamicchangesarepersistentacross databaseshutdownandstartupdependsuponthetypeofparameterfileyouareusing. Serverparameterfile This,thepreferredformofinitializationparameterfile,iscontainedinabinaryfilethatcanbe writtentoandreadbythedatabaseserver.Itmust not beeditedmanually.Itresidesonthe machinethatOracleisrunningon,andispersistentacrossshutdownandstartup. Textinitializationparameterfile Thistypeofinitializationparameterfilecanbereadbythedatabaseserver,butitisnotwrittento bytheserver.Inthisfile,youcansetinitializationparameterswithatexteditorforthemtobe persistentacrossshutdownandstartup.

The System Global Area (SGA)

TheSGAisasharedmemoryareathatcontainsdataandcontrolinformationfortheinstance. Multipleuserscansharedatawithinthismemoryarea(controlledbyOracle)andinformation storedintheSGAcanavoidrepeatedaccessfromphysicaldisk,atimeconsumingoperation. Foroptimalperformance,theSGAshouldbelargeenoughtoavoidfrequentdiskreadsand writes. TheSGAhasseveralsubcomponentsaslistedinthefollowingtable:Overview of an Instance
and Instance Management Managing the Oracle Instance 5-3

Program Global Area (PGA)

Aprogramglobalarea(PGA)isamemoryareausedbyasingleOracleserverprocess.Aserver processisaprocessthatservicesaclientsrequests.EachserverprocesshasitsownprivatePGA areathatisanonsharedareaofmemorycreatedbyOraclewhenaserverprocessisstarted. ThePGAisusedtoprocessSQLstatementsandtoholdlogonandothersessioninformation. TheamountofPGAmemoryusedanditscontentdependsontheinstanceconfiguration,thatis, whethertheinstanceisrunningindedicatedserverorsharedservermode.

Background Process DatabaseWriter (DBWn) LogWriter(LGWR)

Description Thedatabasewriterwritesmodifiedblocksfromthedatabase buffercachetothefilesondisk.Oracleallowsamaximumof20 databasewriterprocesses. Thelogwriterprocesswritesredologentriestodisk.Redolog entriesaregeneratedintheredologbufferoftheSGAandthelog writerprocesswritestheredologentriessequentiallyintoan onlineredologfile. Atspecifictimes,allmodifieddatabasebuffersintheSGAare writtentothedatafilesbyadatabasewriterprocess(DBWn).This eventiscalledacheckpoint.Thecheckpointprocessisresponsible forsignallingDBWn atcheckpointsandupdatingallofthe datafilesandcontrolfilesofthedatabasetoindicatethemost recentcheckpoint. Thesystemmonitorperformscrashrecoverywhenafailed instancestartsupagain. Theprocessmonitorperformsprocessrecoverywhenauser processfails.Itisresponsibleforcleaningupthecacheandfreeing resourcesthatthefailedprocesswasusing. Whenthedatabaseisrunninginarchivelogmode,oneormore archiverprocessescopytheredologfilestoarchivalstoragewhen thelogfilesarefulloralogswitchoccurs.Formoreinformation, seeChapter9,"PerformingBackupandRecovery".

Checkpoint

SystemMonitor (SMON) ProcessMonitor (PMON) Archiver(ARCn)

Background Process DatabaseWriter (DBWn) LogWriter(LGWR)

Description Thedatabasewriterwritesmodifiedblocksfromthedatabase buffercachetothefilesondisk.Oracleallowsamaximumof20 databasewriterprocesses. Thelogwriterprocesswritesredologentriestodisk.Redolog entriesaregeneratedintheredologbufferoftheSGAandthelog writerprocesswritestheredologentriessequentiallyintoan onlineredologfile.

Checkpoint

SystemMonitor (SMON) ProcessMonitor (PMON) Archiver(ARCn)

Atspecifictimes,allmodifieddatabasebuffersintheSGAare writtentothedatafilesbyadatabasewriterprocess(DBWn).This eventiscalledacheckpoint.Thecheckpointprocessisresponsible forsignallingDBWn atcheckpointsandupdatingallofthe datafilesandcontrolfilesofthedatabasetoindicatethemost recentcheckpoint. Thesystemmonitorperformscrashrecoverywhenafailed instancestartsupagain. Theprocessmonitorperformsprocessrecoverywhenauser processfails.Itisresponsibleforcleaningupthecacheandfreeing resourcesthatthefailedprocesswasusing. Whenthedatabaseisrunninginarchivelogmode,oneormore archiverprocessescopytheredologfilestoarchivalstoragewhen thelogfilesarefulloralogswitchoccurs.Formoreinformation, seeChapter9,"PerformingBackupandRecovery".

Control Files

Acontrolfiletracksthephysicalcomponentsofthedatabaseandothercontrolinformation.Itis essentialtothefunctioningofthedatabase.Becauseoftheimportanceofthecontrolfile,Oracle recommendsthatthecontrolfilebemultiplexed.Inotherwords,thecontrolfileshouldhave multipleidenticalcopies.FordatabasescreatedwithDBCA,threecopiesofthecontrolfileare automaticallycreated. Ifanycontrolfilefails,thenyourdatabasebecomesunavailable.Aslongasyoustillhavea controlfilecopyintact,however,youcanshutdownyourdatabaseandrecreatethefailed controlfilefromoneoftheothercontrolfiles,thenrestartyourdatabase.Anotheroptionisto deletethefailedcontrolfilefromtheCONTROL_FILES initializationparameterandrestartyour databasewiththeremainingcontrolfiles.Thereareotheroptions,dependingupon circumstances.

Displaying Control File Information

ClicktheControl Files linktoopentheControlFilesGeneralpage.Thispageshowswhether yourdatabasehasamultiplexedcontrolfile.InadditiontotheGeneralpage,theAdvancedand RecordSectionpagesgiveyoumoredetailedinformationaboutyourcontrolfile.Explanationsof thisinformationarecontainedintheonlinehelp.

Online Redo Log Files

EveryOracledatabasehasasetoftwoormoreonlineredologfiles.Thesetofredologfilesis collectivelyknownastheredologforthedatabase.Aredologismadeupofredoentries,which arealsocalledredorecords. Theprimaryfunctionoftheredologistorecordallchangesmadetodata.Ifafailureprevents modifieddatafrombeingpermanentlywrittentothedatafiles,thenthechangescanbeobtained fromtheredolog,soworkisneverlost.Toprotectagainstafailureinvolvingtheredologitself, Oracleallowsamultiplexedredologsothattwoormoreidenticalcopiesoftheonlineredolog canbemaintainedondifferentdisks. Yourdatabase'sredologconsistsofgroupsofredologfiles.Agroupconsistsofaredologfile anditsmultiplexedcopies.Eachidenticalcopyissaidtobeamemberofthatgroup.Eachgroup isdefinedbyanumber,suchasgroup1. Thedatabaselogwriterprocesswritesredorecordsfromthememorybuffertoaredologgroup untilthegroupfillsuporalogswitchoperationisrequested.Itthenwritestothenextgroup.

Thelogwriterperformsthisactioninacircularfashionsothattheoldestgroupisoverwrittenby themostrecentredorecords.

Multiplexing the Redo Log

Whilenotmultiplexingredologgroupsisacceptableinasmalldatabaseenvironment,consider addingmultiplexingasyourdatabasegrowsinusersandapplications.Multiplexingprovides betterprotectionfordatainthecaseofinstanceormediafailure. Tomultiplexyourredolog,youmustaddmemberstoeachredologgroup.Thisexampleadds onemembertoeachredologgroup.Itisnotrequiredthatredologgroupsbesymmetrical(all havethesamenumberofmembers),butinpracticeitisrecommendedthatthisbethecase.Itis requiredthatadatabasehaveaminimumoftworedologgroups. Tocreateamultiplexedredologforyourdatabase: 1.FromtheStoragesectionoftheAdministrationpage,clickRedo Log Groups. TheRedoLogGroupspageappears. 2.SelectagroupandclickEdit. TheEditRedoLogGrouppageappears. 3.IntheRedo Log Members section,clickAdd. TheAddRedoLogMemberpageappears. 4.Enterthefilenameforthenewredologmember.Forexample,ifyourexistingmemberfile nameisREDO01.log,thenyoumightnamethismemberREDO01a.log. Enterthefiledirectoryoracceptthedefault.Youcancreatethisfileinthesamedirectory,butitis recommendedthatyoustoremembersonseparatedrives.Thatway,ifthereisadrivefailure, youstillhaveaccesstoonemember. 5.ClickContinue. 6.ClickApply toacceptyourchanges. 7.Repeatthesestepsforeveryexistinggroup. Toseethestatuschangeonalogswitch: 1.NavigatetotheRedoLogGroupspage. 2.FromtheActions menu,selectSwitch logfile. 3.ClickGo. YoucanseethatthenextgroupsstatuschangesfromINACTIVE toCURRENT.

Archive Log Files

Whenyouarchiveyourredolog,youwriteredologfilestoanotherlocationpriortotheirbeing overwritten.Thislocationiscalledthearchivelog.Youcanarchivetomultiplelocations, includingastandbydatabase. Thesecopiesofredologfilesextendtheamountofredodatathatcanbesavedandusedfor recovery.Archivingcanbeeitherenabledordisabledforthedatabase,butOraclerecommends thatyouenablearchiving.

Tablespaces

Adatabaseconsistsofoneormoretablespaces.Atablespaceisalogicalstructurecreatedbyand knownonlytotheOracledatabaseserver.Atablespaceconsistsofoneormoredatafilesor tempfiles. Therearevarioustypesoftablespaces,includingthefollowing: Undotablespace Adatabaserunninginautomaticundomanagementmodetransparentlycreatesandmanages undosegments.Youcancreateanundotablespaceanddeterminethemaximumretentiontime forundodatakeptinthattablespace. Permanenttablespaces Thesetablespacesareusedtostorepermanentobjectsanddata. Temporarytablespaces Temporarytablespacesimprovetheconcurrenceofmultiplesortoperations,reducetheir overhead,oravoidOraclespacemanagementoperationsaltogether.Temporarytablespacesare themostefficienttablespacesfordisksorts.Spacemanagement(extentallocationand deallocation)islocallymanaged.

Datafiles

Datafilesaretheoperatingsystemfilesthatholdthedatawithinthedatabase.Thedataiswritten tothesefilesinanOracleproprietaryformatthatcannotbereadbyprogramsotherthanthe databaseserver.Tempfilesareaspecialclassofdatafilesthatareassociatedonlywithtemporary tablespaces. Datafilescanbebrokendownintothefollowingcomponents: Segmentsandextents Asegmentcontainsaspecifictypeofdatabaseobject.Forexample,tablesarestoredindata segments,whereasindexesarestoredinindexsegments. Anextentisacontiguoussetofdatablockswithinasegment.Oracleinitiallyallocatesanextent ofaspecifiedsizeforasegment,butifthatextentfills,thenmoreextentscanbeallocated. Datablock Datablocks,alsocalleddatabaseblocks,arethesmallestunitofI/Otodatabasestorage.An extentconsistsofseveralcontiguousdatablocks.Thedefaultdatabaseblocksizeisspecifiedbya DBAatdatabasecreationordefaultedbyOracle. Afterthedatabasehasbeencreated,itisnotpossibletochangethedefaultblocksizewithoutre creatingthedatabase.Nevertheless,itispossibletocreatetablespacewithablocksizedifferent thanthedefaultblocksize.Fordetailsonhowtocreatetablespaceswithanondefaultor nonstandardblocksize,seeOracle Database Administrator's Guide.

Viewing Datafile Information

ClickDatafiles toopentheDatafilespage.Youcanusethispagetoviewinformationabout yourdatafiles.SelectadatafileandclickView todisplaymoreattributesforthedatafile.You canalsoclickthenameofthetablespacetodisplaytheattributesoftheowningtablespace.

Overview of Schemas and Common Schema Objects

Aschemaisacollectionofdatabaseobjects.Aschemaisownedbyadatabaseuserandhasthe samenameasthatuser.Schemaobjectsarelogicalstructurescreatedbyusers.Objectscandefine areasofthedatabasetoholddata,suchastablesorindexes,orcanconsistjustofadefinition, suchasaviewsorsynonyms.

Estimating the Size of New Tables

DuringtablecreationwithEnterpriseManager,youcanestimatethesizeofthetable.Thisaction enablesyoutodeterminewhetheryouhavesufficientroominyourdatabaseorontheavailable diskstostorethetable.Ifyoudonothaveroom,thenyoucanstillcreatethetablebutnot populateituntilyouhaveobtainedthenecessarystorage. Toestimatethetablesize: 1.IntheDatabaseObjectssectionoftheAdministrationpage,clickTables. TheTablespageappears. 2.ClickCreate. TheCreateTable:TableOrganizationpageappears. 3.SelectStandard, Heap Organized andclickContinue. TheCreateTablepageappears. 4.Enterthetableandschemanamesaswellasyourcolumnnamesanddatatypes,thenclick Estimate Table Size. TheEstimateTableSizepageappears. 5.InProjected Row Count,entertheprojectednumberofrowsinthetableandclick Estimate Table Size EnterpriseManagerreturnsitsestimateinMB.

Creating Indexes

Oracleautomaticallycreatestheindexesnecessarytosupportdataintegritydefinedwith constraintswhenyouaddorenablethoseconstraints.Forperformancepurposes,youmight wanttoaddanindextothecolumnsyoudefineinachildtablewhenaddingaforeignkey constraint.Beforeyouaddadditionalindexes,youshouldexaminetheperformanceofyour database.Youcanthencompareperformanceafterthenewindexesareadded. Afterindexcreation,Oracleautomaticallysynchronizestheindexwithanysubsequentinserts, updates,ordeletestothebasetable. IndexesaregenerallyofvaluetoqueriesandtoSQLstatementsthatneedtooperateonasingle, existingroworasmallnumberofexistingrows.Toomanyindexescancauseseriousproblems byincreasingtheprocessingoverheadforstatementsthatadd,modify,ordeleterows.Insome cases,astatementcouldusetwoormoreindexesandtheoptimizerpicksjustoneofthem. Unlessotherstatementscantakeadvantageoftheunusedindexes,theyarenotprovidingany benefit.Therefore,youmightfindyourselfdeletingindexesifyoucreatetoomany.

Index Types

Indexescanbecategorizedinanumberofways.Theprimaryoptionsarelistedinthefollowing sections. Standard(Btree)andBitmap

Astandard,Btreeindexcontainsanentryforeachvalueintheindexkeyalongwithanaddress totherowwherethevalueisstored.ABtreeindexisthedefaultandmostcommontypeof indexinanOracledatabase. Abitmapindexusesstringsofbitstoencapsulatevaluesandpotentialrowaddresses.Itismore compactthanaBtreeandcanperformsometypesofretrievalmoreefficiently.Forgeneraluse, however,abitmapindexrequiresmoreoverheadduringrowoperationsonthetableandshould beusedprimarilyfordatawarehouseenvironmentsasdescribedintheOracle Database Data Warehousing Guide. AscendingandDescending Thedefaultsearchthroughanindexisfromlowesttohighestvalue,wherecharacterdatais sortedbyASCIIvalues,numericdatafromsmallesttolargestnumber,anddatefromtheearliest tothelatestvalue.Thisdefaultbehaviorisperformedinindexescreatedasascendingindexes. Youcancauseindexsearchestoreversethesearchorderbycreatingtherelatedindexwiththe descendingoption. ColumnandFunctional Typically,anindexentryisbasedonthevalueorvaluesfoundinthetable'scolumnorcolumns. Thisisacolumnindex.Alternatively,youcancreateafunctionbasedindexinwhichtheindexed valueisderivedfromthetabledata.Forexample,tofindcharacterdatathatcanbeinmixedcase, youcoulduseafunctionbasedindextolookforthevaluesasiftheywereallinuppercase characters. SingleColumnandConcatenated Youcancreateanindexonjustonecolumn,whichiscalledasinglecolumnindex,oronmultiple columns,whichiscalledaconcatenatedindex.Concatenatedindexesareusefulwhenallofthe columnsarelikelytobeincludedintheWHERE clauseoffrequentlyexecutedSQLstatements. Forconcatenatedindexes,youshoulddefinethecolumnsusedintheindexcarefullysothatthe columnwiththefewestduplicatevaluesisnamedfirst,thecolumnwithnextfewestduplicate valuesissecond,andsoon.ColumnswithmanyduplicateManaging Indexes 8-16 Oracle
Database 2 Day DBA

valuesormanyrowswithNULL valuesshouldnotbeincludedorshouldbethelastnamed columnsintheindexdefinition. NonpartitionedandPartitioned Aswithtables,youcancreateyourindexeswithorwithoutpartitions.Inmostsituations,itis usefultopartitionanindexwhentheassociatedtableisalsopartitionedandtheindexusesthe samepartitioningschemeasthetable,knownasalocalindex.Youneednotpartitionanindex liketheunderlyingtable.Youmayevencreateanonpartitioned,orglobal,indexona partitionedtable.

Managing Views
Viewsarecustomizedpresentationsofdatainoneormoretablesorotherviews.Youcanthink ofthemasstoredqueries.Viewsdonotactuallycontaindata,butinsteadderivetheirdatafrom thetablesuponwhichtheyarebased.Thesetablesarereferredtoasthebasetablesoftheview. Liketables,viewscanbequeried,updated,insertedinto,anddeletedfrom,withsome restrictions.Alloperationsperformedonaviewactuallyaffectthebasetablesoftheview.Views provideanadditionallevelofsecuritybyrestrictingaccesstoapredeterminedsetofrowsand columnsofatable.Theyalsohidedatacomplexityandstorecomplexqueries. ManyimportantviewsareintheSYS schema.Thedatadictionaryviewsarecalledstaticbecause theychangeonlywhenachangeismadetothedatadictionary,forexample,whenanewtableis createdorauserisgrantednewprivileges. Manydatadictionarytableshavethreecorrespondingviews: ADBA_ viewdisplaysallrelevantinformationintheentiredatabase.DBA_ viewsareintended onlyforadministrators. AnALL_ viewdisplaysalltheinformationaccessibletothecurrentuser,includinginformation fromthecurrentuser'sschemaaswellasinformationfromobjectsinotherschemas,ifthe currentuserhasaccesstothoseobjectsthroughprivilegesorroles. AUSER_ viewdisplaysalltheinformationfromtheschemaofthecurrentuser.Nospecial privilegesarerequiredtoquerytheseviews. ThecolumnsoftheALL_,DBA_,andUSER_ viewscorrespondingtoasingledatadictionary tableareusuallynearlyidentical. Theviewsthatmonitorongoingdatabaseactivityarecalleddynamicperformanceviews.The namesofdynamicperformanceviews,availableonlytoadministrators,startwiththecharacters V$ andareoftenreferredtoastheV$ views. CompletedescriptionsoftheviewsintheSYS schemaarecontainedintheOracle Database Reference.

Overview of Database Backup and Recovery

ThefocusinOraclebackupandrecoveryisgenerallyonthephysicalbackupofdatabasefiles, whichpermitthefullreconstructionofyourdatabase.Thefilesprotectedbythebackupand recoveryfacilitiesbuiltintoEnterpriseManagerincludedatafiles,controlfiles,serverparameter files(SPFILEs),andarchivedredologfiles.Withthesefilesyourdatabasecanbereconstructed. Thebackupmechanismsthatworkatthephysicallevelprotectagainstdamageatthefilelevel, suchastheaccidentaldeletionofadatafileorthefailureofadiskdrive.

Logicallevelbackups,suchasexportingdatabaseobjectsliketablesortablespaces,areauseful supplementtophysicalbackupsforsomepurposes.Nevertheless,logicalbackupscannotprotect yourentiredatabase.Aneffectivebackupstrategymustbebaseduponphysicallevelbackups. OracleDatabase'sflashbackfeaturesprovidearangeofphysicalandlogicaldatarecoverytools asefficient,easytousealternativestophysicalandlogicalbackups.Theflashbackfeatures enableyoutoreversetheeffectsofunwanteddatabasechangeswithoutrestoringdatafilesfrom backuporperformingmediarecovery.

Oracle Backup, Restore, and Recovery Concepts

Tobackupyourdatabaseistomakecopiesofyourdatafiles,controlfile,andarchivedredologs (ifyourdatabaserunsinARCHIVELOG mode).Restoringadatabasefromabackupmeans copyingthephysicalfilesthatmakeupthedatabasefromabackupmedium(diskortape)to theirlocationsduringnormaldatabaseoperation.Recoveryofyourdatabaseistheprocessof updatingdatabasefilesrestoredfromabackupwiththechangesmadetothedatabasesincethe backup,typicallyusingredologfiles.

Consistent and Inconsistent Backups

Abackupisconsistentorinconsistent.Abackupisconsistentwhentherearenochangesinthe redologthathavenotalreadybeenappliedtothedatafilesatthetimeofthebackup. Tomakeaconsistentbackup,yourdatabasemusthavebeenshutdownnormallyandcannotbe reopenedforthedurationofthebackup.Inshuttingdownthedatabase,allcommittedchanges intheredologarewrittentothedatafiles,sothedatafilesareinatransactionconsistentstate. Thisprocessisknownasanofflinebackupbecausetheentiredatabaseisofflinefortheduration ofthebackup. Incontrasttoaconsistentbackup,aninconsistentbackupismadewhilethedatabaseisopen.In aninconsistentbackup,theonlineredologscontainchangesthathavenotyetbeenappliedtothe datafiles.Thedatafilesarenotinatransactionconsistentstate.Thedatabasemustberunin ARCHIVELOG modetopreservetheredolog.Theonlineredologatthemomentofthebackup mustbearchivedandbackedupalongwiththedatafilestopreservethesechanges.

Restoring from Consistent and Inconsistent Backups Whenyourestoreyourdatafilesfromaconsistentbackup,youcanopenthedatabase immediately.Whendatafilesarerestoredfromaninconsistentbackup,youcannotopenthe databaseuntilcommittedchangesrecordedintheredologsareappliedtothedatafiles,bringing

themtoatransactionconsistentstate.Theprocessofapplyingchangesfromtheredologtothe datafilesrestoredfromaninconsistentbackupiscalledmediarecovery.

Media Recovery

Ifyourestorethearchivedredologsandthedatafilesfrombackups,thenyoumustperform mediarecoverybeforeyoucanopenthedatabase.Anydatabasetransactionsinthearchivedredo logsnotalreadyreflectedinthedatafilesareappliedtothedatafiles,bringingthemtoa transactionconsistentstatebeforethedatabaseisopened. Mediarecoveryrequiresacontrolfile,datafiles(typicallyrestoredfrombackup),andonlineand archivedredologscontainingchangessincethetimethedatafileswerebackedup.Media recoveryismostoftenusedtorecoverfromamediafailure,suchasthelossofafileordisk,ora usererror,suchasthedeletionofthecontentsofatable. Therearetwoformsofmediarecovery:completerecoveryandpointintimerecovery.In completerecovery,datafilesarerestoredfrombackup;allchangesfromthearchivedandonline redologsareappliedtothedatafiles.Thedatabaseisreturnedtoitsstateatthetimeoffailure andcanbeopenedwithnolossofcommittedchanges. Inpointintimerecovery,youreturnyourdatabasetoitscontentsatatargettimeofyour choosinginthepast.Youstartwithabackupofdatafilescreatedpriortothetargettimeanda completesetofarchivedredologfilesfromthetimeofthatbackupthroughthetargettime. Duringrecovery,eachchangebetweenthebackuptimeandthetargettimeisappliedtothe datafiles. Pointintimerecoverycanreturnyourwholedatabasetoitsstateatanytimebetweenthetimeof yourbackupandthemostrecentchangeinthearchivedredologs.Allchangesafterthetarget timearediscarded.Pointintimerecoveryisalsosometimescalledincompleterecoverybecause youdonotrecoverthecompletesetofchangestoyourdatabase. EnterpriseManagerprovidesaconvenientinterfacetobothcompleteandpointintimerecovery intheformofaRecoveryWizard.However,thisbookfocusesoncompleterecovery.Pointin timerecoveryisdiscussedatmorelengthinOracle Database Backup and Recovery Basics.

Configuring Your Database for Basic Backup and Recovery

TotakemaximumadvantageofOracleDatabasefeaturesthatautomaticallymanagebackupand recoveryfilesandprocesses,configureyourdatabaseasfollows: Useaflashrecoveryarea,whichautomatesstoragemanagementformostbackuprelatedfiles. RunyourdatabaseinARCHIVELOG mode,sothatyoucanperformonlinebackupsandhave datarecoveryoptionssuchascompleteandpointintimemediarecovery. Usetheflashrecoveryareaasanarchivedlogdestinationforyourdatabase. Youmustalsosetanumberofpoliciesgoverningwhichfilesarebackedup,whatformatisused tostorebackupsondisk,andwhenfilesbecomeeligiblefordeletionfromtheflashrecoveryarea.

Configuring Your Database for Basic Backup and Recovery

TotakemaximumadvantageofOracleDatabasefeaturesthatautomaticallymanagebackupand recoveryfilesandprocesses,configureyourdatabaseasfollows: Useaflashrecoveryarea,whichautomatesstoragemanagementformostbackuprelatedfiles. RunyourdatabaseinARCHIVELOG mode,sothatyoucanperformonlinebackupsandhave datarecoveryoptionssuchascompleteandpointintimemediarecovery. Usetheflashrecoveryareaasanarchivedlogdestinationforyourdatabase. Youmustalsosetanumberofpoliciesgoverningwhichfilesarebackedup,whatformatisused tostorebackupsondisk,andwhenfilesbecomeeligiblefordeletionfromtheflashrecoveryarea.

Planning Space Usage and Location for the Flash Recovery Area

Youshouldplacetheflashrecoveryareaonaseparatediskfromtheworkingsetofdatabase files.Otherwise,thediskbecomesasinglepointoffailureforyourdatabase. Theamountofdiskspacetoallocatefortheflashrecoveryareadependsuponthesizeand activitylevelsofyourdatabase,whichdeterminethesizeofyourdatafilesandredologsfilesas wellasyourrecoveryobjectives.Yourobjectivesdictatewhatkindsofbackupsyouuse,when youmakethem,andhowlongyoumustkeepthem.

Retention Policy and the Flash Recovery Area Space management in the flash recovery area is governed by a backup retention policy. A retention policy determines when files are obsolete, meaning that they are no longer neededtomeetyourdatarecoveryobjectives.
Retentionpoliciescanbebasedonredundancyofbackupsoronarecoverywindow. Underaredundancybasedpolicy,theflashrecoveryareaconsidersabackupofafileobsolete onlywhentheRMANrepositoryhasrecordsofaspecifiednumberofmorerecentbackupsof thatfile.Forexample,assumeyourpolicyrequiresthattwobackupsofeachfilebekept.You makebackupsnightlystartingonaMondaynight.AftertheWednesdaynightbackupsucceeds, theMondaynightbackupbecomesredundantbecausetheTuesdayandWednesdaybackupsare available. Underarecoverywindowbasedpolicy,youspecifyatimeintervalmeasuredindays.Files becomeobsoleteonlywhentheyarenolongerneededforsuccessfulcompleterecoveryorpoint intimerecoverytoanypointwithinthatnumberofdaysintothepast.Forexample,assumethat youspecifyarecoverywindowofthreedays.Abackupofalldatafilesfromatleastthreedays agomustberetained,alongwithafullsetofarchivedredologsgeneratedsincethatbackup.

Credentials for Performing Oracle Enterprise Manager Backup and Recovery

Youmusthavethepropercredentialstoperformsomeoftheconfigurationtasksforbackupand recovery,andtoschedulebackupjobsandperformrecovery.Thefollowingcredentialsmaybe required: TheOracleuseryouusewhenyoulogintoEnterpriseManager Thehostoperatingsystemuserwhosecredentialsyouprovidewhenperformingbackupand recoverytasks ToperformorscheduleRMANtasks,youmusteitherlogintoEnterpriseManagerasauserwith SYSDBA privileges,orprovidehostoperatingsystemcredentialsforauserwhoisamemberof theDBA group.Thehostoperatingsystemusermustalsohaveexecutepermissionforthe RMANcommandlineclient. Fortasksrequiringhostoperatingsystemcredentials,aHostCredentialsformappearsatthe bottomofthepageusedtoperformthetask(seeFigure91).EnterpriseManagerusesthe credentialswhenitinvokesRMANtoperformjobsyourequestedorscheduled.

Configuring the Flash Recovery Area

Youcanconfigureaflashrecoveryareawhenfirstcreatingthedatabase.Ifyoudidnotperform thistaskatdatabasecreationtime,however,thenyoucancreateaflashrecoveryareaforyour databasenow. Toconfigureaflashrecoveryarea: 1.Onthehostoperatingsystem,createadirectorytoholdtheflashrecoveryarea.Makesurethat thepermissionsforthisdirectoryallowOracletocreatefileshere. 2.FromtheDatabaseHomepage,clickMaintenance. TheMaintenancepropertypageappears. 3.IntheBackup/Recoverysection,selectRecovery Settings. TheRecoverySettingspageappears. 4.IntheFlashRecoverysection,enterthepathtotheflashrecoveryarealocation(thepathtothe directoryondiskyoucreatedinstep1),andyourdesiredflashrecoveryareasize.Makesurethe Apply changes to SPFILE only boxisnotchecked,thenclickApply tosaveyoursettings. Itisimportanttomonitorspaceusageintheflashrecoveryareatoensurethatitislargeenough tocontainbackupsandotherrecoveryrelatedfiles.TheHighAvailabilitysectionofthehome pageliststhepercentageofflashrecoveryareaspaceavailable.ClickUsable Flash Recovery Area tonavigatetotheRecoverySettingspage,whichcontainsaFlashRecovery AreaUsagegraphshowinghowmuchspaceisallocatedtoeachtypeoffileandhowmuchspace isfree.

Performing Offline Database Backups Whenperforminganofflinebackup,thedatabaseinstanceshutsdown,thenrestartsandentersa MOUNTED stateforthedurationoftheofflinebackup.Theofflinebackuprunsinthe background,generatingnouservisibleoutputinthebrowser.Thefactthatthedatabaseisnot openaffectsthepagesyouseefromEnterpriseManagerwhiletheofflinebackupruns. Afteryousubmitthebackupjob,astatuspageshouldappearindicatingthatthejobhasbeen successfullysubmitted.Theoutputalsoincludesanotificationthatthedatabasewillbeshut downandmountedaspartoftheofflinebackup,andthatyoumustwaitforthebackupto complete. Whenthedatabaseisshutdownandrestarted,theEnterpriseManagerapplicationmustalso shutdownforabrieftime.DuringtheperiodwhenEnterpriseManagerisshutdown,itcannot respondtoattemptstorefreshthepage. AfterEnterpriseManagerrestartsbutthedatabaseisnotopen,EnterpriseManagerreportsthatit cannotconnecttotheinstance.TheDatabaseInstancesectionofthepagereportsthecurrentstate ofthedatabaselistenerandtheinstance(unmountedormounted)asthedatabaseperformsthe offlinebackup.ItalsoofferstheoptionsofStartup orPerform Recovery.

Backup Management: Concepts

AbackuprecordedintheRMANrepositorycanbeinoneofthefollowingstates: Available,meaningthatthebackupisstillpresentondiskortape,asrecordedintherepository Expired,meaningthatthebackupnolongerexistsondiskortapebutisstilllistedinthe repository Unavailable,meaningthatthebackupistemporarilynotavailablefordatarecoveryoperations (because,forexample,itisstoredonatapethatisstoredoffsiteoradiskthatisnotmountedat themoment) Backupscanalsobeobsolete.Anobsoletebackupisonewhich,basedonthecurrently configuredretentionpolicy,isnolongerneededtosatisfydatarecoverygoals.