Oracle Active Data Guard

1InsertPictureHereOracleActiveDataGuardWhatTheyDidn’tPrintintheManualsJoeMeeksMikeSmithGrantMcAlisterDirectorPrincipalMemberofTechnicalStaffPrincipalDatabaseEngineerHAProductManagementMAADevelopmentAmazon3AgendaInsertPictureHere•Introduction•EnablingActiveDataGuard•BestPractices•AmazonExperience•Appendix•OracleDatabase11gMediaRecoveryPerformance•RedoApplyPerformanceTuning•ClientFailover4•Applications,backups,reportsrunonproductiononlyReal-timeQueriesTraditionalPhysicalStandbyDatabasesInvestmentinDisasterRecoveryPhysicalStandbyDatabaseProductionDatabase5•Offloadread-onlyqueriestoanup-to-datephysicalstandby•PerformfastincrementalbackupsonaphysicalstandbyReal-timeQueriesActiveDataGuard11gInvestmentinImprovedQualityofServicePhysicalStandbyDatabaseProductionDatabaseContinuousRedoShipmentandApplyReal-timeQueryFastIncrementalbackups6What’sNewDataGuard11g•Recovery(redoapply)mustbestoppedtoopenastandbyread-only•SamefunctionalityaspreviousDataGuardreleases•RedoApplyhasexclusiveaccesstodatafiles–readsnotallowed•NotpossibletoguaranteereadconsistencywhileredoapplyisactiveDataGuard11gwiththeActiveDataGuardOption•PhysicalStandbyisopenread-onlywhileredoapplyisactive•Readconsistencyisguaranteed•Redoapplyisnotadverselyaffectedbyread-onlyworkload7ActiveDataGuardDifferenceComparedtoTraditionalReplicationMethodsMorecomplexSimpleFastResourceintensiveDatatyperestrictionsNorestrictionsApplicationtransparentNotapplicationtransparent8ActiveDataGuard11gScale-outQueryPerformancetoWeb-Scale*queriesqueriesActiveDataGuardReaderFarmUsingOracleRACqueriesupdatesProductionDatabaseStandbyDatabasequeriesqueriesDRincluded*9ActiveDataGuard11gScale-outQueryPerformancetoWeb-Scale*ActiveDataGuardReaderFarmProductionDatabaseupdatesStandbyDatabasesOrSingleNodequeriesqueriesqueriesqueriesqueriesDRincluded*10ActiveDataGuard-Licensing•ADatabaseOptionforOracleEnterpriseEdition•ActiveDataGuardlicenseisrequiredwhenusingeither:•Real-timeQuery•RMANblock-changetrackingonastandbydatabase•ActiveDataGuardis100%compatiblewithnewDataGuardfunctionalityincludedwithOracleDatabase11gEnterpriseEdition•S291915-What’sNewinOracleDataGuard11g:RevolutionizingDataProtectionandAvailability11AgendaInsertPictureHere•Introduction•EnablingActiveDataGuard•BestPractices•AmazonExperience•Appendix•OracleDatabase11gMediaRecoveryPerformance•RedoApplyPerformanceTuning•ClientFailover12EnablingActiveDataGuard•BeginwithaDataGuard11gphysicalstandbydatabase•Ifredoapplyisrunning,stopredoapply•Openthestandbydatabaseread-only•Startredoapply•Ifopenread-onlyfailsbecausestandbyinstancewasabortedordatafileswererestoredthen…•Bringtomountstateandstartredoapply•Stopredoapplyandopenread-only•Restartredoapply•IfstandbydatabaseisOracleRAC•Makesureredoapplyisrunningontheapplyinstanceinread-onlymodeBEFOREopeningsubsequentinstancesread-only13DataGuardBroker&EnterpriseManager•DataGuardBrokerCLI•StopredoapplywiththefollowingcommandEDITDATABASE‘RTQ'SETSTATE=‘APPLY-OFF’•Openstandbyread-onlyviaSQL*PlusSQLalterdatabaseopenreadonly;•RestartredoapplyviabrokerCLIEDITDATABASE‘RTQ'SETSTATE=‘APPLY-ON’•OracleEnterpriseManager10g•StopredoapplywithinDataGuardGUI•Openstandbyinread-onlymodeinAdvancedStartupOptions•RestartredoapplywithinDataGuardGUI14GuaranteeofConsistentReads•MaintainedthroughQuerySCN•Identifiesmostrecentreadpoint•Usedbyqueriestoinsureconsistentreads•Currentvaluegivenbycurrent_scnfromv$databaseonstandby•RedoApplyadvancestheQuerySCN•Afteralldependentchangeshavebeenfullyapplied•PropagatedtoallotherinstancesinstandbyRAC15SupportedOperationsforReadOnly•WhenconnectedtoanActiveDataGuardstandbydatabase,read-onlyapplicationscanperform/use:•Selects•Altersession/system•Setrole•Locktable•Callstoredprocedures•DBlinkstowritetoremotedatabases•StoredprocedurestocallremoteproceduresviaDBlinks•SETTRANSACTIONREADONLYfortransactionlevelreadconsistency•Complexqueriese.g.groupingsetqueriesandwithclausequeries16AgendaInsertPictureHere•Introduction•EnablingActiveDataGuard•BestPractices•AmazonExperience•Appendix•OracleDatabase11gMediaRecoveryPerformance•RedoApplyPerformanceTuning•ClientFailover17MAABestPractices•Reportingapplicationworkload•Selectsonly•DML•Routinguserconnections•Newconnections•Roletransitions•Optimizingperformance•SQLTuning•RedoApplyTuning18CandidatesforActiveDataGuard•Read-onlyapplicationsandad-hocqueries•Donotwriteormodifydatabasestate•Donotgenerateredo•Read-mostlyapplications•Applicationsthatperformmanymorereadsthanwrites•e.g.reportingapplicationswithancillarywrites•Allwritesmustberedirectedtoadatabasethatisopenread-write19ActiveDataGuard11gRead-OnlyApplicationModel•Applicationdirectsread-onlyselectstothestandbyReal-timeQueriesPhysicalStandbyDatabaseProductionDatabaseselects20Yesterday’sParadigmAllWorkloadsRunonProduction0500100015002000ProductionDatabaseDataGuardStandbyRead-writeservice480tpsRead-onlyservice640tpsPrimaryserverat100%ofcapacity21ActiveDataGuard11gEasilyOptimizePerformanceforAllWorkloadsread-onlyservice1,350tps+110%Primaryserverat60%ofcapacity0500100015002000ProductionDatabaseActiveDataG