oracle 11g dataguard中dgmgrl怎么用

2025-03-10 技术教程

小编给大家分享一下oracle 11g dataguard中dgmgrl怎么用，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

１　启用dgmgrl
相关参数：
dg_broker_start
dg_broker_config_file1

配置：
alter system set dg_broker_start=true scope=both;
此时数据库会增加dmon进程

[oracle@12crac2~]$ps-ef|grep-i_dmon|grep-vgreporacle26991006:57?00:00:01ora_dmon_db1

对应log:$ORACLE_BASE/diag/rdbms/xxx/xxxx/drc$ORACLE_SID.log
但此时使用是用不了的，如下所示：

DGMGRL>showconfigurationORA-16532:DataGuardbrokerconfigurationdoesnotexist

需手动配置添加：

DGMGRL>helpcreateDGMGRL>createconfiguration'db'asprimarydatabaseisdbconnectidentifierisdb;DGMGRL>helpaddDGMGRL>adddatabase"SBDB1"asconnectidentifieris"sbdb1"maintainedasphysical;DGMGRL>enableconfiguration

注：add database 'SBDB1' ,这里的dg是指database的dbuniquename,而as connect identifier is sbdb1这里的sbdb1是指tnsname.ora连接到standby database的net service name.注意区分大小，默认为小写；
同时检查alert_$ORACLE_SID.log日志可以看到：

RSM0startedwithpid=48,OSid=34551ALTERSYSTEMSETlog_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST','valid_for=(ALL_LOGFILES,ALL_ROLES)'SCOPE=BOTH;ALTERSYSTEMSETlog_archive_trace=0SCOPE=BOTHSID='db1';ALTERSYSTEMSETlog_archive_format='%t_%s_%r.dbf'SCOPE=SPFILESID='db1';ALTERSYSTEMSETstandby_file_management='AUTO'SCOPE=BOTHSID='*';ALTERSYSTEMSETarchive_lag_target=0SCOPE=BOTHSID='*';ALTERSYSTEMSETlog_archive_max_processes=4SCOPE=BOTHSID='*';ALTERSYSTEMSETlog_archive_min_succeed_dest=1SCOPE=BOTHSID='*';

检查drc$ORACLE_SID.log:

CreatingDataGuardBrokerMonitorProcess(DMON)04/03/201823:26:33>>StartingDataGuardBrokerbootstrap<<BrokerConfigurationFileLocations:dg_broker_config_file1="/u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr1SBDB1.dat"dg_broker_config_file2="/u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr2SBDB1.dat"04/03/201823:26:38BrokerConfiguration:"db"ProtectionMode:MaximumPerformanceFast-StartFailover(FSFO):Disabled,flags=0x0,version=0PrimaryDatabase:db(0x01010000)StandbyDatabase:SBDB1,EnabledPhysicalStandby(0x02010000)

所有配置完成，这玩意只能用在企业版
二　检查配置：

DGMGRL>showdatabasesbdb1Object"sbdb1"wasnotfoundDGMGRL>showdatabaseSBDB1Object"sbdb1"wasnotfound

注意大小写

DGMGRL>showdatabase'SBDB1'Database-SBDB1Role:PHYSICALSTANDBYIntendedState:APPLY-ONTransportLag:0seconds(computed0secondsago)ApplyLag:0seconds(computed0secondsago)ApplyRate:0Byte/sRealTimeQuery:ONInstance(s):SBDB1DatabaseStatus:SUCCESS

三　相关测试:snapshot standby,switchover
3.1不开database flashback on 测试：
不用dgmrl的方法参见：https://blog.51cto.com/snowhill/2047857

DGMGRL>convertdatabase'SBDB1'tosnapshotstandby;Convertingdatabase"SBDB1"toaSnapshotStandbydatabase,pleasewait...Database"SBDB1"convertedsuccessfully

这里从alert_sbdb1.log里看，数据库并没有发生重启，只是将会话杀掉了；

SQL>selectopen_mode,database_role,protection_mode,flashback_onfromv$database;OPEN_MODEDATABASE_ROLEPROTECTION_MODEFLASHBACK_ON--------------------------------------------------------------------------READWRITESNAPSHOTSTANDBYMAXIMUMPERFORMANCE**RESTOREPOINTONLY**SQL>truncatetablesystem.test;Tabletruncated.SQL>selectcount(*)fromsystem.test;COUNT(*)----------0DGMGRL>convetdatabase'SBDB1'tophysicalstandby;UnabletoconnecttodatabaseORA-12545:ConnectfailedbecausetargethostorobjectdoesnotexistFailed.Warning:YouarenolongerconnectedtoORACLE.PleasecompletethefollowingstepsandreissuetheCONVERTcommand:shutdowninstance"SBDB1"ofdatabase"SBDB1"startupandmountinstance"SBDB1"ofdatabase"SBDB1"

手动启动备库到mount状态，后面的dgmgrl自动恢复到sbdb1,但在open过程中经常会死在最后一步，相关日志如下：

ALTERDATABASECONVERTTOPHYSICALSTANDBY(SBDB1)Killing3processeswithpids3013,3017,3019(allRFS)inordertodisallowcurrentandfutureRFSconnections.RequestedbyOSprocess3025FlashbackRestoreStartFlashbackRestoreCompleteDropguaranteedrestorepointGuaranteedrestorepointdroppedClearingstandbyactivationID1736364983(0x677ed3b7)Theprimarydatabasecontrolfilewascreatedusingthe'MAXLOGFILES192'clause.Thereisspaceforupto188standbyredologfilesUsethefollowingSQLcommandsonthestandbydatabasetocreatestandbyredologfilesthatmatchtheprimarydatabase:ALTERDATABASEADDSTANDBYLOGFILE'srl1.f'SIZE52428800;ALTERDATABASEADDSTANDBYLOGFILE'srl2.f'SIZE52428800;ALTERDATABASEADDSTANDBYLOGFILE'srl3.f'SIZE52428800;ALTERDATABASEADDSTANDBYLOGFILE'srl4.f'SIZE52428800;ALTERDATABASEADDSTANDBYLOGFILE'srl5.f'SIZE52428800;Waitingforallnon-currentORLstobearchived...Allnon-currentORLshavebeenarchived.Clearingonlineredologfile1/u01/app/oracle/oradata/SBDB1/SBDB1/onlinelog/o1_mf_1_fd4wmho5_.logClearingonlinelog1ofthread1sequencenumber3Clearingonlineredologfile1completeClearingonlineredologfile2/u01/app/oracle/oradata/SBDB1/SBDB1/onlinelog/o1_mf_2_fd4wmjq3_.logClearingonlinelog2ofthread1sequencenumber4Clearingonlineredologfile2completeCompleted:alterdatabaseconverttophysicalstandbyWedApr0401:15:502018PrimarydatabaseisinMAXIMUMPERFORMANCEmodeRFS[3]:AssignedtoRFSprocess3029RFS[3]:Selectedlog5forthread1sequence70dbid1729483220branch965667412WedApr0401:16:112018RFS[4]:AssignedtoRFSprocess3031RFS[4]:Selectedlog6forthread1sequence69dbid1729483220branch965667412WedApr0401:16:112018Expandedcontrolfilesection11from28to203recordsRequestedtogrowby175records;added7blocksofrecordsArchivedLogentry29addedforthread1sequence69ID0x6715d4d4dest1:WedApr0401:16:132018ARC2:BecomingtheactiveheartbeatARCHWedApr0401:17:132018alterdatabaseopenDataGuardBrokerinitializing...

最后一步会假死一下，没关系，直接shutdown abort，再启动，就可以了；由于dgmgrl的存在,他会自动补上命令,如下图所示：

为啥会自动，检查数据库配置：

DGMGRL>showdatabaseverbose'SBDB1';Database-SBDB1Role:PHYSICALSTANDBYIntendedState:APPLY-ONTransportLag:0seconds(computed1secondago)ApplyLag:0seconds(computed1secondago)ApplyRate:0Byte/sRealTimeQuery:ON

3.2 启用Fast start failover

DGMGRL>enablefast_startfailoverError:ORA-16651:requirementsnotmetforenablingfast-startfailoverFailed.DGMGRL>exit[oracle@12crac2~]$oerrora1665116651,0000,"requirementsnotmetforenablingfast-startfailover"//*Cause:Theattempttoenablefast-startfailovercouldnotbecompleted//becauseoneormorerequirementswerenotmet://-TheDataGuardconfigurationmustbeineitherMaxAvailability//orMaxPerformanceprotectionmode.//-TheLogXptModepropertyforboththeprimarydatabaseand//thefast-startfailovertargetstandbydatabasemustbe//settoSYNCiftheconfigurationprotectionmodeissetto//MaxAvailabilitymode.//-TheLogXptModepropertyforboththeprimarydatabaseand//thefast-startfailovertargetstandbydatabasemustbe//settoASYNCiftheconfigurationprotectionmodeissetto//MaxPerformancemode.//-Theprimarydatabaseandthefast-startfailovertargetstandby//databasemustbothhaveflashbackenabled.//-Novalidtargetstandbydatabasewasspecifiedintheprimary//databaseFastStartFailoverTargetpropertypriortotheattempt//toenablefast-startfailover,andmorethanonestandby//databaseexistsintheDataGuardconfiguration.

简单点来说就是：
１确保broker配置为运行在Max Availability模式。
２在primary和standby机器上都启用flashback database，这个在reinstate failed的数据库的时候要用。
３启动observer

启用database flashback 再次enable：

DGMGRL>enablefast_startfailoverEnabled.DGMGRL>showconfigurationConfiguration-dbProtectionMode:MaxAvailabilityDatabases:db-PrimarydatabaseWarning:ORA-16819:fast-startfailoverobservernotstartedSBDB1-(*)PhysicalstandbydatabaseWarning:ORA-16819:fast-startfailoverobservernotstartedFast-StartFailover:ENABLEDConfigurationStatus:WARNING

[oracle@12crac2~]$oerrora1681916819,0000,"fast-startfailoverobservernotstarted"//*Cause:Theobserverforfast-startfailoverwasnotstarted.Asa//result,fast-startfailovercouldnothappeninthecaseof//aprimarydatabasefailure.//*Action:Startthefast-startfailoverobserverbyusing,forexample,the//DGMGRLSTARTOBSERVERcommand.

启动observer:

DGMGRL>startobserverObserverstarted

再次查看配置：

DGMGRL>showconfigurationConfiguration-dbProtectionMode:MaxAvailabilityDatabases:db-PrimarydatabaseSBDB1-(*)PhysicalstandbydatabaseFast-StartFailover:ENABLEDConfigurationStatus:SUCCESS

3.3　switchover
手工switchover 步骤：https://blog.51cto.com/snowhill/1951592
现在更简化下，用dgmgrl只需一个命令：SWITCHOVER TO <standby database name>;

DGMGRL>switchoverto'SBDB1';PerformingswitchoverNOW,pleasewait...Operationrequiresaconnectiontoinstance"SBDB1"ondatabase"SBDB1"Connectingtoinstance"SBDB1"...Connected.Newprimarydatabase"SBDB1"isopening...Operationrequiresstartupofinstance"db1"ondatabase"db"Startinginstance"db1"...UnabletoconnecttodatabaseORA-12514:TNS:listenerdoesnotcurrentlyknowofservicerequestedinconnectdescriptorFailed.Warning:YouarenolongerconnectedtoORACLE.Pleasecompletethefollowingstepstofinishswitchover:startupinstance"db1"ofdatabase"db"

这个failed不用担心，手动启动一下原主库就可以完成切换了；
切换完了，检查下：

DGMGRL>showconfigurationConfiguration-dbProtectionMode:MaxAvailabilityDatabases:SBDB1-Primarydatabasedb-(*)PhysicalstandbydatabaseFast-StartFailover:ENABLEDConfigurationStatus:SUCCESS

再切回来：

DGMGRL>switchovertodbPerformingswitchoverNOW,pleasewait...Operationrequiresaconnectiontoinstance"db1"ondatabase"db"Connectingtoinstance"db1"...Connected.Newprimarydatabase"db"isopening...Operationrequiresstartupofinstance"SBDB1"ondatabase"SBDB1"Startinginstance"SBDB1"...

如还有啥疑问看官方参考：https://docs.oracle.com/cd/E11882_01/server.112/e40771/sofo.htm#DGBKR385

四　dgmgrl 调整参数：
4.1调整应用延迟
edit database 'SBDB1' set property DelayMins=1 ;
其实就是如下命令：

ALTERSYSTEMSETlog_archive_dest_2='service="sbdb1"','LGWRASYNCNOAFFIRMdelay=1optionalcompression=disablemax_failure=0max_connections=1reopen=300db_unique_name="SBDB1"net_timeout=30','valid_for=(all_logfiles,primary_role)'SCOPE=BOTH;ALTERSYSTEMSWITCHALLLOGFILEstart(db1)ALTERSYSTEMSWITCHALLLOGFILEcomplete(db1)

4.２调整异步同步模式
edit database 'SBDB1' set property LogXptMode=sync;
对应的命令如下：
ALTER SYSTEM SET log_archive_dest_2='service="sbdb1"','LGWR SYNC AFFIRM delay=1 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="SBDB1" net_timeout=30','valid_for=(all_logfiles,primary_role)' SCOPE=BOTH;
当然所有能用broker配置的参数，不要用sqlplus来干；
五　相关报错处理

broker错误一般为大小写不一致，数据库配置和broker配置不一致引起，启用broker后，dataguard相关的配置都最好用broker管理；
5.1 ora-16541

DGMGRL>showconfigurationORA-16541:databaseisnotenabled

此错误出现一个节点，一般是由于配置不一致引起

[oracle@12crac1dbs]$oerrora1654116541,00000,"databaseisnotenabled"//*Cause:Thedatabasespecifiedintherequestwasnotenabled.//*Action:Selectanenableddatabaseandreissuetherequest.

再另一个节点上查看，一般报的错不一样

DGMGRL>showconfigurationConfiguration-dbProtectionMode:MaxAvailabilityDatabases:db-PrimarydatabaseWarning:ORA-16792:configurablepropertyvalueisinconsistentwithdatabasesettingSBDB1-PhysicalstandbydatabaseWarning:ORA-16792:configurablepropertyvalueisinconsistentwithdatabasesettingFast-StartFailover:DISABLED

此错误为参数与dgmgrl 里的不一致引起，检查启用dg　broker之后的配置，发现更改改了log_file_name_convert,db_file_name_convert引起，在另一个节点做如下配置就可以了

DGMGRL>editdatabasedbsetpropertyLogFileNameConvert='/u01/app/oracle/oradata/SBDB1/,+data';Property"logfilenameconvert"updatedDGMGRL>editdatabasedbsetpropertyDbFileNameConvert='/u01/app/oracle/oradata/SBDB1/,+data';Property"dbfilenameconvert"updatedDGMGRL>editdatabase'SBDB1'setpropertyDbFileNameConvert='+data,/u01/app/oracle/oradata/SBDB1/';Property"dbfilenameconvert"updatedDGMGRL>editdatabase'SBDB1'setpropertyLogFileNameConvert='+data,/u01/app/oracle/oradata/SBDB1/';Property"logfilenameconvert"updatedDGMGRL>enableconfigurationDGMGRL>showconfigurationConfiguration-dbProtectionMode:MaxAvailabilityDatabases:db-PrimarydatabaseSBDB1-PhysicalstandbydatabaseFast-StartFailover:DISABLEDConfigurationStatus:SUCCESS

5.2 ora-16820

DGMGRL> show configuration
Configuration - db
Protection Mode: MaxAvailability
Databases:
SBDB1 - Primary database
Error: ORA-16820: fast-start failover observer is no longer observing this database
db - (*) Physical standby database
Error: ORA-16820: fast-start failover observer is no longer observing this database
Fast-Start Failover: ENABLED
Configuration Status:
ERROR

此错误比较扯淡,stop observer,start observer就可以了;

5.3 ora-16661

启用fast failover后,非正常关闭两个节点,再次启动数据库,一般会出现两个节点都是primary 的情况,这时启动dgmgrl会报:

DGMGRL> show configuration
Configuration - db
Protection Mode: MaxAvailability
Databases:
SBDB1 - Primary database
Error: ORA-16820: fast-start failover observer is no longer observing this database
db - (*) Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated
Fast-Start Failover: ENABLED
Configuration Status:
ERROR
而另外一节点报错如下:
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16649: possible failover to another database prevents this database from
being opened
检查数据库状态也不怎么对:
SQL> select database_role,FLASHBACK_ON from v$database;
DATABASE_ROLE FLASHBACK_ON
PRIMARY YES
DGMGRL> show configuration
ORA-16795: the standby database needs to be re-created
看到此错误也别真去recreated standby,在确认你的配置上没有问题后,直接在另外一个节点:
DGMGRL> reinstate database db
Reinstating database "db", please wait...
Reinstatement of database "db" succeeded
这时后启动的节点会变成physical standby,再次stop observer,start observer就可以了;

5.4 Error: ORA-16525: the Data Guard broker is not yet available
主库或者备的库的db_broker_start参数设置不对；
5.5 ORA-16797: database is not using a server parameter file
没用spfile启动数据库

5.6 ora-16610 主库或者备库的dg_broker_config_file_1/2参数设置不对

DGMGRL>showconfigurationConfiguration-1ProtectionMode:MaxPerformanceDatabases:prod2-Primarydatabasestandby-PhysicalstandbydatabaseFast-StartFailover:DISABLEDConfigurationStatus:ORA-16610:command"Brokeroperation"inprogressDGM-17017:unabletodetermineconfigurationstatus

查看drcstandby.log

drcx:cannotopenconfigurationfile"/home/oracle/broker/standby2.data"ORA-27037:unabletoobtainfilestatusLinux-x86_64Error:2:NosuchfileordirectoryAdditionalinformation:308/02/201810:52:38drcx:cannotopenconfigurationfile"/home/oracle/broker/standby1.dat"ORA-27037:unabletoobtainfilestatusLinux-x86_64Error:2:NosuchfileordirectoryAdditionalinformation:3DMONRegisteringservicestandby_DGBwithlistener(s)08/02/201810:52:41

解决办法：

dgmgrl>disableconfiguration;sql>altersystemsetdg_broker_start=false;sys@standby>altersystemsetdg_broker_config_file1='/home/oracle/broker/standby1.dat';sys@standby>altersystemsetdg_broker_config_file2='/home/oracle/broker/standby2.dat';DGMGRL>enableconfigurationDGMGRL>showconfigurationConfiguration-1ProtectionMode:MaxPerformanceDatabases:prod2-Primarydatabasestandby-PhysicalstandbydatabaseFast-StartFailover:DISABLEDConfigurationStatus:SUCCESS

以上是“oracle 11g dataguard中dgmgrl怎么用”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注亿速云行业资讯频道！