The Oracle Database

[Link],alldataisstoredintwodimensional [Link], updateit,andefficientlyretrieveit. [Link] physicalandlogicalstructuresinwhichsystem,user,[Link] [Link],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 [Link] for Administering the Database
Introduction 1-3

Thefollowingaresomeoftheproducts,tools,andutilitiesyoucanuseinachievingyourgoalsas adatabaseadministrator: Oracle Universal Installer (OUI) [Link] launchtheDatabaseConfigurationAssistanttoinstalladatabase. Database Configuration Assistant (DBCA) TheDatabaseConfigurationAssistantcreatesadatabasefromtemplatesthataresuppliedby Oracle,[Link],thus savingthetimeandeffortofgeneratingandcustomizingadatabasefromscratch. Database Upgrade Assistant ThisDatabaseUpgradeAssistantguidesyouthroughtheupgradeofyourexistingdatabasetoa newOraclerelease. Oracle Net Manager NetManagerguidesyouthroughyourOracleNetnetworkconfiguration. Oracle Enterprise Manager TheprimarytoolformanagingyourdatabaseisOracleEnterpriseManager,awebbased [Link],createdorupgradedadatabase,and configuredthenetwork,[Link] addition,OracleEnterpriseManageralsoprovidesaninterfaceforperformanceadvisorsandfor OracleutilitiessuchasSQL*LoaderandRecoveryManager.

Checking Prerequisites

Beforeinstallingthesoftware,theinstallerperformsanumberofautomatedprerequisitechecks toensurethatyourmachinefulfillsthebasichardwareandsoftwarerequirementsforanOracle [Link],anerrormessageis [Link] operationsystemyouarerunning,butsomeexamplesofprerequisitesinclude: Aminimumof512MBofmemoryisavailable. Sufficientpagingspaceisavailable. Appropriateoperatingsystemservicepacksorpatchesareinstalled. Anappropriatefilesystemformatisbeingused. TheinstallerautomaticallysetsanyoperatingsystemenvironmentvariablesthattheOracle databaseserverrequiresforitsoperation.

Basic Installation Choices

WhenyouinstallOracleDatabaseduringbasicandadvancedinstallations,youmakethe followingchoices. Whatdatabaseproductdoyouwanttoinstall? Youcaninstalloneofthefollowing: Enterprise EditionOracle'sfullfeatureddatabaseproductprovidingdatamanagement [Link],highsecurityonline transactionprocessing(OLTP)anddatawarehousingenvironments. Standard EditionThisinstallationtypeissuitableforworkgroupordepartmentlevel applications,[Link] relationaldatabasemanagementservicesandoptions.

Personal Edition (Windowsoperatingsystemsonly)Thisinstallationtypeinstallsthe samesoftwareastheEnterpriseEditioninstallationtype,butsupportsonlyasingleuser developmentanddeploymentenvironment. Custom [Link] CustomInstalltoaddacomponenttoyourexistinginstallationthatwouldnotnormallybe installedorpreventcertaincomponentsfrombeinginstalled.

Best Practice:

Oraclerecommendsthatyouinstallapreconfigureddatabase, [Link] later.

TheGlobalDatabaseNameisthefullnameofthedatabasethatuniquelydistinguishesitfrom [Link] database_name.database_domain,[Link] sales [Link] [Link] domainmakeuptheGlobalDatabaseName. Duringbasicinstallation,[Link] examplesinthedatabasedocumentationarebasedonthesampleschemas.. YouwillalsobepromptedtoenteryourdatabaseschemapasswordfortheSYS andSYSTEM accounts,whichenableyoutomanageandadministerthedatabase.

Whatkindofstoragemechanismdoyouwantyourdatabasetouse? Adatabaseincludesseveralfilesthatstoretheuserdata,databasemetadata,aswellas [Link],youdecidewhatkindof [Link]: File SystemThisdefaultoptioncreatesdatabasefilesthataremanagedbyyouroperating system'[Link]. OracleDatabasecancreateandmanagetheactualfiles. Ifyouarenotcertainofwhichoptiontouse,choosethedefaultselectionofFileSystem. Automatic Storage ManagementThisfunctionalityenablesyoutodefineapoolof storage(calledadiskgroup)inwhichOracleautomaticallymanagesdatabasefileplacementand [Link],thisoptionsimplifiesdatabase [Link] automatically.

AutomaticStorageManagementrequiresaseparateinstancetoconfigureandmanagedisks [Link] Managementinstanceanditscomponents.

Raw DevicesThisoptionenablesyoutomanageyourstoragedevicesoutsideofthe operatingsystemfilesystembyprovidingOracleDatabasewithunformattedphysicaldiskspace [Link](RAC) [Link],ensurethattherawdeviceshavebeencreatedwithan operatingsystemcommand,andthattheyarenotbeingusedforanyotherpurposesorbyother Oracledatabases.

WhatareyourDatabaseManagementOptions? [Link] modeenablesyoutomanagemultipletargets,suchasdatabasesandapplicationservers,froma [Link] instance. [Link] suchagentisfound,thenthisoptionisdisabled. Ifyouaresettingupasingledatabaseforthefirsttime,itisrecommendedthatyoudeploy EnterpriseManagerlocally,[Link] migrateyourdeploymentmodetocentralmanagement. Attheendoftheinterview,asummarywindowdisplaystheoptionsthatyouhavechosen,the requirementsforspacebasedonthoseoptions,[Link] havechosentoinstalladatabase,thenthisstepisperformedimmediatelyafterthesoftware installationiscomplete.

Installing Oracle Software and Database

[Link] [Link]. [Link] theway,consulttheonlinehelportheOracle Database Installation Guide foryourplatform. [Link] [Link] documentationorcontactyoursystemadministratortodeterminewhetheryouhavethe necessaryprivilegestoinstallnewsoftware. [Link] [Link]/Deinstall Products. IfdownloadingfromOracle'sWebsite,thenfollowtheinstructionsonthesite. [Link].Figure21shows [Link] Oracle Software and Database Installing
Oracle and Building the Database 2-5

Figure 21 Oracle Universal Installer Select Installation Method Window [Link]:

Basic [Link] [Link] databasebasedontheinformationyouprovide. ForBasicinstallation,youspecifythefollowing: Oracle Home LocationEnterthedirectoryinwhichtoinstalltheOracleDatabase10g [Link] Database10g. Installation TypeSelecteitherEnterprise Edition,Standard Edition,or Personal Edition (Windows Only).Referto"BasicInstallationChoices"onpage22. UNIX DBA Group (LinuxandUnixonly)SpecifytheDBAgroupforyouroperating system. Create Starter Database [Link] [Link] Global Database Name andDatabase Password fortheSYS,SYSTEM,SYSMAN,and DBSNMP [Link]"BasicInstallation Choices". TostarttheBasicInstallation,[Link]. Advanced [Link],use thismethodtoinstallOracleRealApplicationClusters,toupgradeadatabase,toconfigure AutomaticStorageManagement,[Link] [Link]

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

advancedchoices,see"AdvancedInstallationChoices"[Link] Database Installation Guide foryourplatform. [Link]"CheckingPrerequisites" [Link], warning,[Link] [Link],youmustresolvethem manually. Afterverifyingthatyoursystemmeetstheminimumrequirementsforinstallingandconfiguring thechosenproducts,clickNext. [Link] and Unix [Link] machine,thenthefollowingoccurs: [Link] [Link] [Link] patchestoorupgradinganexistinginstallation,[Link] thisareaisdifferentfromtheOraclehome,[Link] tocontinue. [Link],space [Link] [Link] Installwindowappearsshowinginstallationprogress. [Link],theConfiguration Assistants [Link] [Link] database,thentheDatabaseConfigurationAssistantstartsautomaticallyinaseparatewindow. Attheendofdatabasecreation,youarepromptedtounlockuseraccountstomaketheaccounts [Link] andSYSTEM accountsarealreadyunlocked,clickOK tobypass passwordmanagement. [Link] and Unix [Link] Configuration scripts [Link] promptedtoopenanewterminalwindow,[Link] scripts,returntothiswindowandclickOk. [Link] displaysseveralimportantURLs,[Link] thisURLinyourbrowserandloginastheSYS userwiththeassociatedpassword,andconnect [Link]. SeeChapter3,"GettingStartedwithOracleEnterpriseManager". [Link],youcanexittheInstaller.

Using DBCA to Create and Configure a Database

Duringinstallation,[Link] 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 [Link] configurationoptions.

Starting DBCA

TolaunchDBCA: [Link] Oraclesoftwareandcreateandrunthedatabase. [Link],clicktheStart buttonandthenselect Programs,Oracle - home_name,Configuration and Migration Tools,andthen Database Configuration Assistant. TolaunchtheDBCAonUNIX,orasanothermethodonaWindowsoperatingsystem,enterthe followingcommandatasystemprompt:
dbca

Thedbca utilityistypicallylocatedinORACLE_HOME/bin. TheWelcomewindowappears. [Link] tocontinue.

Creating a Database with DBCA

OntheDBCAOperationswindow,selectCreate a Database tobeginawizardthatenables [Link],youareaskedforyourinputonthe following: DatabaseTemplates DatabaseIdentification ManagementOptions DatabaseCredentials StorageOptions DatabaseFileLocations RecoveryConfiguration DatabaseContent InitializationParameters DatabaseStorage CreationOptions [Link] thedefaultparameters,youcanclickFinish [Link] windowinwhichyoumustclickOK [Link] DBCA to Create
and Configure a Database 2-8 Oracle Database 2 Day DBA

Database Templates
[Link],Oracle [Link] Warehouse,General Purpose, andTransaction Processing databases. [Link] Details toseethe [Link] [Link],selectthedefaultGeneral Purpose template. Formorecomplexenvironments,youcanselecttheCustom Database [Link] resultsinamoreextensiveinterview,whichmeansthatitwilltakelongertocreateyourdatabase becauseadatabasecreationscriptmustberun. Formoreinformationaboutusingdatabasetemplates,see"ManagingTemplateswithDBCA"on page212.

Database Identification

IntheGlobal Database Name box,enterthedatabasenameintheform database_name.domain_name. IntheSID box,[Link] 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 [Link],youmustalsoindicatewhich managementservicetouseinthedropdownmenu. SelectUse Database Control for Database Management tomanageyourdatabase [Link],youcanadditionallycheckEnable Email Notifications forOracletoemailyoualertsregardingpotentialproblems,andcheckEnable Daily Backup. ClickHelp formoreinformationabouttheseoptions.

Database Credentials

Inthiswindow,youspecifythepasswordsfortheadministrativeaccountssuchasSYS and [Link]: SelectUse the Same Password for All Accounts andenterthepasswordtousethe samepasswordforallaccounts. SelectUse Different Passwords andspecifypasswordsindividually.

Storage Options

[Link] information,referto"InstallationChoices"[Link] DBCA to Create and

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

Database File Locations

Inthiswindow,youspecifytheOraclehomeanddirectorypathinwhichtoinstalltheOracle [Link]: Use Database File Locations from TemplateSelectingthisoptioninstructsthe [Link],you willhaveanopportunitylatertomakemodificationstodatabasefilenamesandlocations. Use Common Location for All Database FilesThisoptionrequiresyoutospecifya [Link] choosethisoption,youwillhaveanopportunitylatertomakemodificationstodatabase filenamesandlocations. Use Oracle-Managed FilesSelectthisoptiontoeliminatetheneedfortheDBAto [Link] [Link] [Link] redoandonlinelogfilesbyselectingMultiplex Redo Logs and Control [Link] moreaboutredologsandcontrolfiles,refertoChapter9,"PerformingBackupandRecovery". Selectingthisoptionenablesyoutodelegatethecompletemanagementofdatabasefilestothe [Link],location,ortheirsizes.

Recovery Configuration

Whenyoucreateanewdatabase,itisimportanttoconfigurethedatabasesoyoucanrecover [Link]: ChooseSpecify Flash Recovery Area tospecifyabackupandrecoveryareaandspecify [Link] revieworaddadditionallocations,clickFile Location Variables atthebottomofthe window. CheckEnable Archiving toenablearchivingofdatabaseredologs,whichcanbeusedto [Link] EnterpriseManagerorrunningthedatabaseinARCHIVELOG [Link] archivemodesettingsorchangethembyselectingEdit Archive Mode Parameters. OraclerecommendsyouselectEnable [Link] [Link] optionnow,[Link]"ConfiguringYourDatabaseforBasic BackupandRecovery"onpage94.

Database Content

IntheSample Schemas tab,checkSample Schemas toincludetheSampleSchemas (EXAMPLE)[Link] [Link] [Link]. IntheCustom Scripts tab,youcanspecifyoneormoreSQLscriptstorunafteryourdatabase [Link],suchasloadingcustom [Link],checkRun the following scripts;otherwiseacceptthedefault No scripts to [Link] DBCA to Create and
Configure a Database 2-10 Oracle Database 2 Day DBA

afterinstallation,[Link] Help formoreinformation.

Initialization Parameters

Thelinksonthiswindowprovideaccesstowindowsthatenableyouchangedefaultinitialization [Link]: Memory Sizing CharacterSets ConnectionMode Memory Usethiswindowtosettheinitializationparametersthatcontrolhowthedatabasemanagesits [Link]: Typicalrequireslittleconfiguration,andallocatesmemoryasapercentageoftotaloverall [Link],selectTypical [Link] Show Memory Distribution toseehowmuchmemorytheAssistantassignstotheSystem GlobalArea(SGA)andtheProgramGlobalArea(PGA).TolearnmoreaboutPGAandSGA, referto"ManagingMemoryParameters"inChapter5,"ManagingtheOracleInstance". Customrequiresmoreconfiguration,butgivesyoumorecontroloverhowthedatabaseuses [Link] directlyspecifymemorysizesfortheSGAandPGAandtheirsubstructures,suchastheshared poolandbuffercache. Selectoneofthefollowingoptions: SelectAutomatic toallocatespecificamountsofmemorytoSGAandPGA. SelectManual andenterspecificvaluesforeachSGAcomponenttocustomizehowtheSGA memoryisdistributedamongtheSGAmemorysubstructures. TocustomizehowtheSGAmemoryisdistributedamongtheSGAmemorysubstructures,select Manual [Link] memoryareas,clickHelp. Sizing Inthistab,youspecifythesmallestblocksizeandthemaximumnumberofoperatingsystem userprocessesthatcansimultaneouslyconnecttothedatabase. IntheBlock Size list,[Link] [Link] [Link],thisfieldisnotenabledsincethedatabasewillbe [Link],youcanchange blocksize.Selectingablocksizeotherthanthedefault8KBvaluerequiresadvancedknowledge andshouldonlybedonewhenabsolutelyrequired. IntheProcesses field,specifythemaximumnumberofprocessesthatcansimultaneously [Link] [Link] shouldallowforallbackgroundprocesses,suchaslocksandparallelexecutionprocesses. CharacterSets [Link] encodingschemesusedtodisplaycharactersonyourcomputerUsing DBCA to Create and
Configure a Database Installing Oracle and Building the Database 2-11

[Link]. 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,[Link] charactersetisanalternativecharactersetthatenablesyoutostoreUnicodecharactersina [Link] nationalcharactersetisthatprogramminginthenationalcharactersetmightbeeasier. IntheDefault Language list,[Link] defaultlanguagedetermineshowthedatabasesupportslocalesensitiveinformationsuchasday andmonthabbreviations,defaultsortingsequenceforcharacterdata,andwritingdirection(left orright). IntheDefault Date Format list,[Link] formatdeterminestheconventionfordisplayingthehour,day,month,[Link] theUnitedKingdom,thedateformatisDDMMYYYY. ConnectionMode [Link] modes: SelectDedicated Server Mode toallowadedicatedserverprocessforeachuserprocess. Selectthisoptionwhenthenumberoftotalclientsisexpectedtobesmall,orwhendatabase clientswillmakepersistent,longrunningrequeststothedatabase. SelectShared Server Mode toallowseveralclientconnectionstoshareadatabaseallocated [Link] isalsousefulwhendatabasememoryislimitedandforbetterperformancebecauseyoucanhave [Link] youchoosesharedservermode,thenyoumustalsoindicatethenumberofserverprocessesyou [Link], clickHelp.

Database Storage

Atreelistingdisplaysthestoragestructureofyourdatabase(controlfiles,datafiles,redolog groups,andsoforth).Ifyouarenotsatisfiedwiththestoragestructureorparameters,thenyou [Link] 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 [Link],youcanchangeyourconfigurationfroma [Link] configuredforusewithyourdatabase,forexample,OracleLabelSecurityorOracleOLAP.

Deleting a Database with DBCA

IntheOperationswindow,selectDelete a Database toremoveadatabasefromthesystem. Whenyouselectthisoption,[Link] Windows,anyassociatedservicesarealsodeleted.

Managing Templates with DBCA

[Link] [Link] templatesincludesdatabaseoptions,initializationparameters,andstorageattributes(for datafiles,tablespaces,controlfiles,andonlineredologs). Templatescanbeusedjustlikescripts,buttheyaremorepowerfulthanscriptsbecauseyouhave [Link] databasesfilestothecorrectlocationstakeslesstimethancreatingthemasnew. Templatesarestoredinthefollowingdirectory:

ORACLE_HOME/assistants/dbca/templates

Advantages of Using Templates

Usingtemplateshasthefollowingadvantages: [Link]. Bycreatingatemplatecontainingyourdatabasesettings,youcaneasilycreateaduplicate databasewithoutspecifyingparameterstwice. [Link]. [Link].

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)[Link] template,databasecreationisfasterbecausethephysicalfilesand [Link] asacopyoftheseeddatabase,ratherthanhavingtobebuilt. Youcanchangeonlythefollowing: Nameofthedatabase Destinationofthedatafiles Numberofcontrolfiles Numberofredologgroups Initializationparameters Otherchangescanbemadeafterdatabasecreationusingcustomscripts thatcanbeinvokedbyDBCA,commandlineSQLstatements,orthe OracleEnterpriseManager. Thedatafilesandonlineredologsfortheseeddatabasearestoredina [Link] [Link] [Link] [Link] file. Nonseed .dbt No [Link] [Link] 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 [Link] [Link] [Link] 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,[Link] DBUAwritesdetailedtraceandlogfilesandproducesacompleteHTMLreportforlater [Link],theDBUAautomaticallylocksnewuseraccountsintheupgraded [Link](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 [Link] backtotheoriginalserverunlessyourestoreyourdatabase. Ifyourestoreyourdatabasemanually(notusingtheDBUA),then removethefollowingfilebeforestartingtheDBUA: $10.2OracleHome/cfgtoollogs/dbua/logs/Welcome_<SID>.t [Link] 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 [Link]. However,iftheprocessisnotrunningbecauseofasystemrestartorotherevent,youcan manuallystartitatthecommandline. Tostartthedbconsole process: 1.NavigateintoyourORACLE_HOME/bin directory [Link]: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: [Link],ControlPanel,AdministrativeTools,[Link]. [Link] serviceislistedas OracleDBConsoleORACLE_SID,whereORACLE_SID [Link] listedintheStatuscolumn,[Link] appears. [Link],ensurethattheStartupTypeiseitherManualorAutomaticandnot [Link],[Link]. YoucanalsousetheServicespagetostoptheprocess.

Accessing the Oracle Enterprise Manager Database Control

Attheendofasuccessfuldatabasecreation,OracleEnterpriseManagerisautomatically [Link],providedthedbconsoleprocessisrunning ontheserver,youcannavigatetoyourDatabaseControlfromyourclientbrowserasfollows: [Link]: [Link] Forexample,ifyouinstalledthedatabaseonahostcomputernamedcomp42,andtheinstaller indicatedthatyourEnterpriseManagerConsoleHTTPportnumberis5500(alsorecordedinthe $ORACLE_HOME/install/[Link] file),enterthefollowingURL: [Link] Ifthedatabaseisup,EnterpriseManagerdisplaystheDatabaseControlLoginpage. Ifthedatabaseisdownandneedstoberestarted,EnterpriseManagerdisplaysthe Startup/[Link],clickStartup/[Link] arethenpromptedforthehostandtargetdatabaseloginusernamesandpasswords,whichyou [Link],useSYS andthepasswordyouspecifiedduring installation. ClickOK [Link],clickYES tostartthedatabasein [Link] Database Control Administrative Privileges 3-4 Oracle Database 2 Day
DBA

[Link]

initiallycouldbeSYS [Link] databaseinstallation. EnterpriseManagerdisplaystheDatabaseHomepage(Figure34). Thepropertypagesacrossthetopofthepageenableyoutoaccessperformance,administration, [Link] isdiscussedinotherchaptersofthisbook. ThevarioussectionsoftheDatabaseHomepageandrelatedlinksprovideinformationaboutthe [Link],theAlerts,Related Alerts,and Diagnostic Summary sectionswarnyouoferrorsandperformanceproblemsthatare [Link] abouttheproblemarea,[Link] discussedinChapter10,"MonitoringandTuningtheDatabase".
Figure 34 Database Home Page

Granting Database Control Administrative Privileges

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

[Link] orSYSTEM database

user. [Link] atthetopoftheDatabaseHomepage. [Link] intheleftnavigationbar. Ifyouneedtocreateanewadministrator,youmustfirstcreatetheuserandthenassign [Link],see"Administering DatabaseUsers"onpage78 [Link] tocreateanewEnterpriseManageruserbyassigningthemanagement privilegestoanexistingdatabaseuser. [Link] popupwindow. [Link]. [Link] databaseuserisnowincludedinthelistofmanagementusersontheSetupAdministratorspage. AlltheusersshownonthispagecanlogintotheDatabaseControlandperformdatabase managementtasks.

AclientisanyapplicationthatneedstoconnecttotheOracledatabasetosendorretrievedata. AnOracleclientapplicationcanresideonanymachineprovidedithasOracleclientsoftware installed. [Link] 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

[Link] usernameandpassword,[Link] connectdescriptoritself,[Link] commonconnectidentifiersisanetservicename,[Link] exampleshowsaconnectstringthatusesnetservicenamemydb astheconnectidentifier.

CONNECT tatahms/tatahms@production

Local Naming
Thelocalnamingmethodstoresconnectdescriptors,identifiedbytheirnetservicename,ina local(ontheclient)[Link] 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,[Link],usethefollowing:
lsnrctl start

[Link],navigatetotheListener: listener_namepagebyclickingListener [Link],click Stop. Youcanalsostopitatthecommandlineusingthefollowing:

lsnrctl stop

Overview of an Instance and Instance Management

YourOracledatabaseiscomprisedofasetofoperatingsystemfilescontainingdataenteredby usersorapplicationsandstructuralinformationaboutthedatabaseitselfcalleddatabase [Link]. Inorderforyoutovieworupdatethedatacontainedinthedatabase,Oracleneedstostartaset ofprocesses,calledbackgroundprocesses,andneedstoallocatesomememorytobeusedduring [Link] [Link],thedatabaseinstancemustbestarted. Whenthedatabaseinstanceisnotavailable,yourdataissafeinthedatabasebutitcannotbe accessedbyanyuserorapplication. [Link] theinstanceisstarted,aninitializationparameterfileisreadandtheinstanceisconfigured accordingly. TheOracleinstanceandtheOracledatabaseareseparateentities,althoughtheterminstanceis [Link],theyaredistinguishedasfollows: AnOracleinstanceconsistsofthesharedmemorystructuresandbackgroundprocessesthatrun [Link](forexample,whenyouhave notyetcreatedadatabase),andifadatabaseexists,itcanbeopenornot. AOracledatabasereferstothephysicalandlogicalcomponentsofaspecificdatabase,andits operation.

About Initialization Parameters

Instancemanagementinvolvesconfiguringparametersthataffectthebasicoperationofthe [Link] serverreadstheseparametersatdatabasestartupandmonitorsthemwhilethedatabaseis [Link],[Link] aretwotypesofparameterfiles,andwhetherthesedynamicchangesarepersistentacross databaseshutdownandstartupdependsuponthetypeofparameterfileyouareusing. Serverparameterfile This,thepreferredformofinitializationparameterfile,iscontainedinabinaryfilethatcanbe [Link] not [Link] machinethatOracleisrunningon,andispersistentacrossshutdownandstartup. Textinitializationparameterfile Thistypeofinitializationparameterfilecanbereadbythedatabaseserver,butitisnotwrittento [Link],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)[Link] [Link] areathatisanonsharedareaofmemorycreatedbyOraclewhenaserverprocessisstarted. ThePGAisusedtoprocessSQLstatementsandtoholdlogonandothersessioninformation. TheamountofPGAmemoryusedanditscontentdependsontheinstanceconfiguration,thatis, whethertheinstanceisrunningindedicatedserverorsharedservermode.

Background Process DatabaseWriter (DBWn) LogWriter(LGWR)

Description Thedatabasewriterwritesmodifiedblocksfromthedatabase buffercachetothefilesondisk.Oracleallowsamaximumof20 databasewriterprocesses. [Link] entriesaregeneratedintheredologbufferoftheSGAandthelog writerprocesswritestheredologentriessequentiallyintoan onlineredologfile. Atspecifictimes,allmodifieddatabasebuffersintheSGAare writtentothedatafilesbyadatabasewriterprocess(DBWn).This [Link] forsignallingDBWn atcheckpointsandupdatingallofthe datafilesandcontrolfilesofthedatabasetoindicatethemost recentcheckpoint. Thesystemmonitorperformscrashrecoverywhenafailed instancestartsupagain. Theprocessmonitorperformsprocessrecoverywhenauser [Link] resourcesthatthefailedprocesswasusing. Whenthedatabaseisrunninginarchivelogmode,oneormore archiverprocessescopytheredologfilestoarchivalstoragewhen [Link], seeChapter9,"PerformingBackupandRecovery".

Checkpoint

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

Background Process DatabaseWriter (DBWn) LogWriter(LGWR)

Description Thedatabasewriterwritesmodifiedblocksfromthedatabase buffercachetothefilesondisk.Oracleallowsamaximumof20 databasewriterprocesses. [Link] entriesaregeneratedintheredologbufferoftheSGAandthelog writerprocesswritestheredologentriessequentiallyintoan onlineredologfile.

Checkpoint

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

Atspecifictimes,allmodifieddatabasebuffersintheSGAare writtentothedatafilesbyadatabasewriterprocess(DBWn).This [Link] forsignallingDBWn atcheckpointsandupdatingallofthe datafilesandcontrolfilesofthedatabasetoindicatethemost recentcheckpoint. Thesystemmonitorperformscrashrecoverywhenafailed instancestartsupagain. Theprocessmonitorperformsprocessrecoverywhenauser [Link] resourcesthatthefailedprocesswasusing. Whenthedatabaseisrunninginarchivelogmode,oneormore archiverprocessescopytheredologfilestoarchivalstoragewhen [Link], seeChapter9,"PerformingBackupandRecovery".

Control Files

[Link] [Link],Oracle [Link],thecontrolfileshouldhave [Link],threecopiesofthecontrolfileare automaticallycreated. Ifanycontrolfilefails,[Link] controlfilecopyintact,however,youcanshutdownyourdatabaseandrecreatethefailed controlfilefromoneoftheothercontrolfiles,[Link] deletethefailedcontrolfilefromtheCONTROL_FILES initializationparameterandrestartyour [Link],dependingupon circumstances.

Displaying Control File Information

ClicktheControl Files [Link] [Link],theAdvancedand [Link] thisinformationarecontainedintheonlinehelp.

Online Redo Log Files

[Link] [Link],which arealsocalledredorecords. [Link] modifieddatafrombeingpermanentlywrittentothedatafiles,thenthechangescanbeobtained fromtheredolog,[Link], Oracleallowsamultiplexedredologsothattwoormoreidenticalcopiesoftheonlineredolog canbemaintainedondifferentdisks. Yourdatabase'[Link] [Link] isdefinedbyanumber,suchasgroup1. Thedatabaselogwriterprocesswritesredorecordsfromthememorybuffertoaredologgroup [Link].

Thelogwriterperformsthisactioninacircularfashionsothattheoldestgroupisoverwrittenby themostrecentredorecords.

Multiplexing the Redo Log

Whilenotmultiplexingredologgroupsisacceptableinasmalldatabaseenvironment,consider [Link] betterprotectionfordatainthecaseofinstanceormediafailure. Tomultiplexyourredolog,[Link] [Link](all havethesamenumberofmembers),[Link] requiredthatadatabasehaveaminimumoftworedologgroups. Tocreateamultiplexedredologforyourdatabase: [Link],clickRedo Log Groups. TheRedoLogGroupspageappears. [Link]. TheEditRedoLogGrouppageappears. [Link] Log Members section,clickAdd. TheAddRedoLogMemberpageappears. [Link],ifyourexistingmemberfile [Link],[Link]. [Link],butitis [Link],ifthereisadrivefailure, youstillhaveaccesstoonemember. [Link]. [Link] toacceptyourchanges. [Link]. Toseethestatuschangeonalogswitch: [Link]. [Link] menu,selectSwitch logfile. [Link]. YoucanseethatthenextgroupsstatuschangesfromINACTIVE toCURRENT.

Archive Log Files

Whenyouarchiveyourredolog,youwriteredologfilestoanotherlocationpriortotheirbeing [Link], includingastandbydatabase. Thesecopiesofredologfilesextendtheamountofredodatathatcanbesavedandusedfor [Link],butOraclerecommends thatyouenablearchiving.

Tablespaces

[Link] [Link] tempfiles. Therearevarioustypesoftablespaces,includingthefollowing: Undotablespace Adatabaserunninginautomaticundomanagementmodetransparentlycreatesandmanages [Link] forundodatakeptinthattablespace. Permanenttablespaces Thesetablespacesareusedtostorepermanentobjectsanddata. Temporarytablespaces Temporarytablespacesimprovetheconcurrenceofmultiplesortoperations,reducetheir overhead,[Link] [Link](extentallocationand deallocation)islocallymanaged.

Datafiles

[Link] tothesefilesinanOracleproprietaryformatthatcannotbereadbyprogramsotherthanthe [Link] tablespaces. Datafilescanbebrokendownintothefollowingcomponents: Segmentsandextents [Link],tablesarestoredindata segments,whereasindexesarestoredinindexsegments. [Link] ofaspecifiedsizeforasegment,butifthatextentfills,thenmoreextentscanbeallocated. Datablock Datablocks,alsocalleddatabaseblocks,arethesmallestunitofI/[Link] [Link] DBAatdatabasecreationordefaultedbyOracle. Afterthedatabasehasbeencreated,itisnotpossibletochangethedefaultblocksizewithoutre [Link],itispossibletocreatetablespacewithablocksizedifferent [Link] nonstandardblocksize,seeOracle Database Administrator's Guide.

Viewing Datafile Information

ClickDatafiles [Link] [Link] [Link] canalsoclickthenameofthetablespacetodisplaytheattributesoftheowningtablespace.

Overview of Schemas and Common Schema Objects

[Link] [Link] areasofthedatabasetoholddata,suchastablesorindexes,orcanconsistjustofadefinition, suchasaviewsorsynonyms.

Estimating the Size of New Tables

DuringtablecreationwithEnterpriseManager,[Link] enablesyoutodeterminewhetheryouhavesufficientroominyourdatabaseorontheavailable [Link],thenyoucanstillcreatethetablebutnot populateituntilyouhaveobtainedthenecessarystorage. Toestimatethetablesize: [Link],clickTables. TheTablespageappears. [Link]. TheCreateTable:TableOrganizationpageappears. [Link], Heap Organized andclickContinue. TheCreateTablepageappears. [Link],thenclick Estimate Table Size. TheEstimateTableSizepageappears. [Link] Row Count,entertheprojectednumberofrowsinthetableandclick Estimate Table Size EnterpriseManagerreturnsitsestimateinMB.

Creating Indexes

Oracleautomaticallycreatestheindexesnecessarytosupportdataintegritydefinedwith [Link],youmight wanttoaddanindextothecolumnsyoudefineinachildtablewhenaddingaforeignkey [Link],youshouldexaminetheperformanceofyour [Link]. Afterindexcreation,Oracleautomaticallysynchronizestheindexwithanysubsequentinserts, updates,ordeletestothebasetable. IndexesaregenerallyofvaluetoqueriesandtoSQLstatementsthatneedtooperateonasingle, [Link] byincreasingtheprocessingoverheadforstatementsthatadd,modify,[Link] cases,astatementcouldusetwoormoreindexesandtheoptimizerpicksjustoneofthem. Unlessotherstatementscantakeadvantageoftheunusedindexes,theyarenotprovidingany [Link],youmightfindyourselfdeletingindexesifyoucreatetoomany.

Index Types

[Link] sections. Standard(Btree)andBitmap

Astandard,Btreeindexcontainsanentryforeachvalueintheindexkeyalongwithanaddress [Link] indexinanOracledatabase. [Link] [Link], however,abitmapindexrequiresmoreoverheadduringrowoperationsonthetableandshould beusedprimarilyfordatawarehouseenvironmentsasdescribedintheOracle Database Data Warehousing Guide. AscendingandDescending Thedefaultsearchthroughanindexisfromlowesttohighestvalue,wherecharacterdatais sortedbyASCIIvalues,numericdatafromsmallesttolargestnumber,anddatefromtheearliest [Link]. Youcancauseindexsearchestoreversethesearchorderbycreatingtherelatedindexwiththe descendingoption. ColumnandFunctional Typically,anindexentryisbasedonthevalueorvaluesfoundinthetable'scolumnorcolumns. [Link],youcancreateafunctionbasedindexinwhichtheindexed [Link],tofindcharacterdatathatcanbeinmixedcase, youcoulduseafunctionbasedindextolookforthevaluesasiftheywereallinuppercase characters. SingleColumnandConcatenated Youcancreateanindexonjustonecolumn,whichiscalledasinglecolumnindex,oronmultiple columns,[Link] columnsarelikelytobeincludedintheWHERE clauseoffrequentlyexecutedSQLstatements. Forconcatenatedindexes,youshoulddefinethecolumnsusedintheindexcarefullysothatthe columnwiththefewestduplicatevaluesisnamedfirst,thecolumnwithnextfewestduplicate valuesissecond,[Link] Indexes 8-16 Oracle
Database 2 Day DBA

valuesormanyrowswithNULL valuesshouldnotbeincludedorshouldbethelastnamed columnsintheindexdefinition. NonpartitionedandPartitioned Aswithtables,[Link],itis usefultopartitionanindexwhentheassociatedtableisalsopartitionedandtheindexusesthe samepartitioningschemeasthetable,[Link] [Link],orglobal,indexona partitionedtable.

Managing Views
[Link] [Link],butinsteadderivetheirdatafrom [Link]. Liketables,viewscanbequeried,updated,insertedinto,anddeletedfrom,withsome [Link] provideanadditionallevelofsecuritybyrestrictingaccesstoapredeterminedsetofrowsand [Link]. ManyimportantviewsareintheSYS [Link] theychangeonlywhenachangeismadetothedatadictionary,forexample,whenanewtableis createdorauserisgrantednewprivileges. Manydatadictionarytableshavethreecorrespondingviews: ADBA_ viewdisplaysallrelevantinformationintheentiredatabase.DBA_ viewsareintended onlyforadministrators. AnALL_ viewdisplaysalltheinformationaccessibletothecurrentuser,includinginformation fromthecurrentuser'sschemaaswellasinformationfromobjectsinotherschemas,ifthe currentuserhasaccesstothoseobjectsthroughprivilegesorroles. AUSER_ [Link] privilegesarerequiredtoquerytheseviews. ThecolumnsoftheALL_,DBA_,andUSER_ viewscorrespondingtoasingledatadictionary tableareusuallynearlyidentical. [Link] namesofdynamicperformanceviews,availableonlytoadministrators,startwiththecharacters V$ andareoftenreferredtoastheV$ views. CompletedescriptionsoftheviewsintheSYS schemaarecontainedintheOracle Database Reference.

Overview of Database Backup and Recovery

ThefocusinOraclebackupandrecoveryisgenerallyonthephysicalbackupofdatabasefiles, [Link] recoveryfacilitiesbuiltintoEnterpriseManagerincludedatafiles,controlfiles,serverparameter files(SPFILEs),[Link]. Thebackupmechanismsthatworkatthephysicallevelprotectagainstdamageatthefilelevel, suchastheaccidentaldeletionofadatafileorthefailureofadiskdrive.

Logicallevelbackups,suchasexportingdatabaseobjectsliketablesortablespaces,areauseful [Link],logicalbackupscannotprotect [Link]. OracleDatabase'sflashbackfeaturesprovidearangeofphysicalandlogicaldatarecoverytools asefficient,[Link] enableyoutoreversetheeffectsofunwanteddatabasechangeswithoutrestoringdatafilesfrom backuporperformingmediarecovery.

Oracle Backup, Restore, and Recovery Concepts

Tobackupyourdatabaseistomakecopiesofyourdatafiles,controlfile,andarchivedredologs (ifyourdatabaserunsinARCHIVELOG mode).Restoringadatabasefromabackupmeans copyingthephysicalfilesthatmakeupthedatabasefromabackupmedium(diskortape)to [Link] updatingdatabasefilesrestoredfromabackupwiththechangesmadetothedatabasesincethe backup,typicallyusingredologfiles.

Consistent and Inconsistent Backups

[Link] redologthathavenotalreadybeenappliedtothedatafilesatthetimeofthebackup. Tomakeaconsistentbackup,yourdatabasemusthavebeenshutdownnormallyandcannotbe [Link],allcommittedchanges intheredologarewrittentothedatafiles,sothedatafilesareinatransactionconsistentstate. Thisprocessisknownasanofflinebackupbecausetheentiredatabaseisofflinefortheduration ofthebackup. Incontrasttoaconsistentbackup,[Link] aninconsistentbackup,theonlineredologscontainchangesthathavenotyetbeenappliedtothe [Link] ARCHIVELOG [Link] mustbearchivedandbackedupalongwiththedatafilestopreservethesechanges.

Restoring from Consistent and Inconsistent Backups Whenyourestoreyourdatafilesfromaconsistentbackup,youcanopenthedatabase [Link],youcannotopenthe databaseuntilcommittedchangesrecordedintheredologsareappliedtothedatafiles,bringing

[Link] datafilesrestoredfromaninconsistentbackupiscalledmediarecovery.

Media Recovery

Ifyourestorethearchivedredologsandthedatafilesfrombackups,thenyoumustperform [Link] logsnotalreadyreflectedinthedatafilesareappliedtothedatafiles,bringingthemtoa transactionconsistentstatebeforethedatabaseisopened. Mediarecoveryrequiresacontrolfile,datafiles(typicallyrestoredfrombackup),andonlineand [Link] recoveryismostoftenusedtorecoverfromamediafailure,suchasthelossofafileordisk,ora usererror,suchasthedeletionofthecontentsofatable. Therearetwoformsofmediarecovery:[Link] completerecovery,datafilesarerestoredfrombackup;allchangesfromthearchivedandonline [Link] andcanbeopenedwithnolossofcommittedchanges. Inpointintimerecovery,youreturnyourdatabasetoitscontentsatatargettimeofyour [Link] completesetofarchivedredologfilesfromthetimeofthatbackupthroughthetargettime. Duringrecovery,eachchangebetweenthebackuptimeandthetargettimeisappliedtothe datafiles. Pointintimerecoverycanreturnyourwholedatabasetoitsstateatanytimebetweenthetimeof [Link] [Link] youdonotrecoverthecompletesetofchangestoyourdatabase. EnterpriseManagerprovidesaconvenientinterfacetobothcompleteandpointintimerecovery [Link],[Link] 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 [Link],thediskbecomesasinglepointoffailureforyourdatabase. Theamountofdiskspacetoallocatefortheflashrecoveryareadependsuponthesizeand activitylevelsofyourdatabase,whichdeterminethesizeofyourdatafilesandredologsfilesas [Link],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 [Link],[Link] [Link], theMondaynightbackupbecomesredundantbecausetheTuesdayandWednesdaybackupsare available. Underarecoverywindowbasedpolicy,[Link] becomeobsoleteonlywhentheyarenolongerneededforsuccessfulcompleterecoveryorpoint [Link],assumethat [Link] agomustberetained,alongwithafullsetofarchivedredologsgeneratedsincethatbackup.

Credentials for Performing Oracle Enterprise Manager Backup and Recovery

Youmusthavethepropercredentialstoperformsomeoftheconfigurationtasksforbackupand recovery,[Link] required: TheOracleuseryouusewhenyoulogintoEnterpriseManager Thehostoperatingsystemuserwhosecredentialsyouprovidewhenperformingbackupand recoverytasks ToperformorscheduleRMANtasks,youmusteitherlogintoEnterpriseManagerasauserwith SYSDBA privileges,orprovidehostoperatingsystemcredentialsforauserwhoisamemberof theDBA [Link] RMANcommandlineclient. Fortasksrequiringhostoperatingsystemcredentials,aHostCredentialsformappearsatthe bottomofthepageusedtoperformthetask(seeFigure91).EnterpriseManagerusesthe credentialswhenitinvokesRMANtoperformjobsyourequestedorscheduled.

Configuring the Flash Recovery Area

[Link] thistaskatdatabasecreationtime,however,thenyoucancreateaflashrecoveryareaforyour databasenow. Toconfigureaflashrecoveryarea: [Link],[Link] thepermissionsforthisdirectoryallowOracletocreatefileshere. [Link],clickMaintenance. TheMaintenancepropertypageappears. [Link]/Recoverysection,selectRecovery Settings. TheRecoverySettingspageappears. [Link],enterthepathtotheflashrecoveryarealocation(thepathtothe directoryondiskyoucreatedinstep1),[Link] Apply changes to SPFILE only boxisnotchecked,thenclickApply tosaveyoursettings. Itisimportanttomonitorspaceusageintheflashrecoveryareatoensurethatitislargeenough [Link] [Link] Flash Recovery Area tonavigatetotheRecoverySettingspage,whichcontainsaFlashRecovery AreaUsagegraphshowinghowmuchspaceisallocatedtoeachtypeoffileandhowmuchspace isfree.

Performing Offline Database Backups Whenperforminganofflinebackup,thedatabaseinstanceshutsdown,thenrestartsandentersa MOUNTED [Link] background,[Link] openaffectsthepagesyouseefromEnterpriseManagerwhiletheofflinebackupruns. Afteryousubmitthebackupjob,astatuspageshouldappearindicatingthatthejobhasbeen [Link] downandmountedaspartoftheofflinebackup,andthatyoumustwaitforthebackupto complete. Whenthedatabaseisshutdownandrestarted,theEnterpriseManagerapplicationmustalso [Link],itcannot respondtoattemptstorefreshthepage. AfterEnterpriseManagerrestartsbutthedatabaseisnotopen,EnterpriseManagerreportsthatit [Link] ofthedatabaselistenerandtheinstance(unmountedormounted)asthedatabaseperformsthe [Link] orPerform Recovery.

Backup Management: Concepts

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