Oracle英文版培训课件之Data Guard Administration：L08-Logical-SQL

CreatingaLogicalStandbyDatabase

byUsingSQLObjectivesAftercompletingthislesson,youshouldbeabletouseSQLcommandstocreatealogicalstandbydatabase.PreparingtoCreatea

LogicalStandbyDatabaseAspreparationforcreatingalogicalstandbydatabase,performthefollowingstepsontheprimarydatabase:Checkforunsupporteddatatypes.BeawareofunsupportedDDLcommands.Ensurerowuniqueness.VerifythattheprimarydatabaseisconfiguredforARCHIVELOGmode.Enablesupplementallogging.Checkthevaluesoftheinitializationparameters.CreatingaLogicalStandbyDatabaseTocreatealogicalstandbydatabasebyusingSQLcommands:Createaphysicalstandbydatabase.StopRedoApplyonthephysicalstandbydatabase.Preparetheprimarydatabasetosupportalogicalstandbydatabase.BuildaLogMinerDictionaryintheredodata.Converttoalogicalstandbydatabase.Openthelogicalstandbydatabase.Verifythatthelogicalstandbydatabaseisperformingproperly.Step1:CreateaPhysicalStandbyDatabasea. Createaphysicalstandbydatabase.b. Ensurethatthephysicalstandbydatabaseiscaughtuptotheprimarydatabase.Step2:StopRedoApplyonthePhysicalStandbyDatabaseBeforeconvertingthephysicalstandbydatabasetoalogicalstandbydatabase,stopRedoApply.RequiredtoavoidapplyingchangespasttheredothatcontainstheLogMinerdictionary.SQL>ALTERDATABASERECOVERMANAGEDSTANDBY2DATABASECANCEL;Step3:PreparethePrimaryDatabasetoSupportaLogicalStandbyDatabasea. PreparetheprimarydatabaseforroletransitionsbysettingLOG_ARCHIVE_DEST_nparametersappropriately.b. SettheLOG_ARCHIVE_DEST_3initializationparameterfortransitioningtoalogicalstandbyrole.c. SetthevalueofUNDO_RETENTIONto3600.LOG_ARCHIVE_DEST_3='LOCATION=/arch/chicago/VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)DB_UNIQUE_NAME=chicago'LOG_ARCHIVE_DEST_STATE_3=enableStep4:BuildaLogMinerDictionaryintheRedoDataBuildaLogMinerdictionaryintheredodatasothatSQLApplycanproperlyinterpretchangesintheredo.Supplementalloggingisautomaticallyenabled.SQL>EXECUTEDBMS_LOGSTDBY.BUILD;Step5:TransitiontoaLogicalStandbyDatabasea. Converttoalogicalstandbydatabase.b. Createanewpasswordfile.c. ShutdowntheinstanceandrestartitinMOUNTmode.d. Adjustinitializationparameters:LOG_ARCHIVE_DEST_nSQL>ALTERDATABASERECOVERTO2LOGICALSTANDBYdb_name;Step6:OpentheLogicalStandbyDatabasea. OpenthenewlogicalstandbydatabasewiththeRESETLOGSoption:b. Starttheapplicationofredodatatothelogicalstandbydatabase:SQL>ALTERDATABASEOPENRESETLOGS;SQL>ALTERDATABASESTARTLOGICALSTANDBY2APPLYIMMEDIATE;Step7:VerifyThattheLogicalStandbyDatabaseIsPerformingProperly

a. Verifythatthearchivedredologfileswereregistered:b. Beginsendingredodatatothestandbydatabase:c. QuerytheDBA_LOGSTDBY_LOGviewtoverifythatthearchivedredologfileswereregistered.SQL>SELECTSEQUENCE#,FIRST_TIME,NEXT_TIME,2DICT_BEGIN,DICT_END3FROMDBA_LOGSTDBY_LOGORDERBYSEQUENCE#;SQL>ALTERSYSTEMARCHIVELOGCURRENT;Step7:VerifyThattheLogicalStandbyDatabaseIsPerformingProperlyd. Verifythatredodataisbeingappliedcorrectly:e. ViewtheV$LOGSTDBYviewtoseecurrent

SQLApplyactivity:f. ChecktheoverallprogressofSQLApply:SQL>SELECTNAME,VALUEFROMV$LOGSTDBY_STATS2WHERENAME='coordinatorstate';SQL>SELECTTYPE,HIGH_SCN,STATUS2FROMV$LOGSTDBY;SQL>SELECTAPPLIED_SCN,LATEST_SCN2FROMV$LOGSTDBY_PROGRESS;AdditionalConfigurationTasksPerformthefollowingtasksasappropriateforyourconfiguration:C