[20140416]使用dgmgrl管理dataguard(4).txt
参考链接：
http://blog.itpub.net/267265/viewspace-1142649/
http://blog.itpub.net/267265/viewspace-1143027/
http://blog.itpub.net/267265/viewspace-1143058/
下面讲解已经使用了dgmgrl来管理dataguard,再使用sqlplus修改一些相关参数会出现什么情况呢？
我的测试环境：primary的tnsnames别名test，standby主机的tnsnames别名testdg。数据库版本：
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
1.修改某个属性看看。
SCOTT@test> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------- ------- ----------------------------------------------------------------------------------------------------
log_archive_dest_2 string SERVICE=testdg LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdg
DGMGRL> show database verbose test
Database - test
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
test
Properties:
DGConnectIdentifier = 'test'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'auto'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '/u01/app/oracle11g/oradata/test, /u01/app/oracle11g/oradata/test'
LogFileNameConvert = '/u01/app/oracle11g/oradata/test, /u01/app/oracle11g/oradata/test'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
SidName = 'test'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hisdg)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=test_DGMGRL.com)(INSTANCE_NAME=test)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/u01/app/oracle11g/archivelog'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
SUCCESS
-- 11G 的redo有一个特性可以压缩redo，减少网络的传输。修改这个参数看看。
--help edit 看看帮助，很容易知道修改的语法。
-- EDIT DATABASE SET PROPERTY = ;
DGMGRL> edit database test set property RedoCompression='ENABLE';
Property "redocompression" updated
DGMGRL> show database test RedoCompression
RedoCompression = 'ENABLE'
--检查发现参数log_archive_dest_2并没有修改。
SCOTT@test> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------- ------- ----------------------------------------------------------------------------------------------------
log_archive_dest_2 string SERVICE=testdg LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdg
-- enable configuration 以及 enable database ，一样无效。
-- 说明在dgmgrl下修改一些参数，对应的spfile文件并没有修改。
2.再换一个参数standby_file_management看看.
SCOTT@test> show parameter standby_file_management
NAME TYPE VALUE
------------------------ ------- ------
standby_file_management string auto
DGMGRL> show database verbose test StandbyFileManagement
StandbyFileManagement = 'auto'
DGMGRL> edit database test set property StandbyFileManagement='manual';
Property "standbyfilemanagement" updated
DGMGRL> show database verbose test StandbyFileManagement
StandbyFileManagement = 'manual'
SCOTT@test> show parameter standby_file_management
NAME TYPE VALUE
------------------------ ------- -------
standby_file_management string manual
--可以发现修改在dgmgrl下修改参数standby_file_management，同时也修改了spfile文件里面的参数。
3.这回反向操作，通过sqlplus修改参数看看情况如何。
SYS@test> alter system set standby_file_management=auto scope=both ;
System altered.
SYS@test> show parameter standby_file_management
NAME TYPE VALUE
------------------------ -------- ------
standby_file_management string AUTO
DGMGRL> show database verbose test StandbyFileManagement
StandbyFileManagement = 'manual'
--可以发现dgmgrl的配置里面没有改变。
4.继续测试：
SYS@test> alter system set standby_file_management=manual scope=both ;
System altered.
DGMGRL> edit database test set property StandbyFileManagement='auto';
Property "standbyfilemanagement" updated
SYS@test> show parameter standby_file_management
NAME TYPE VALUE
------------------------ ------- -------
standby_file_management string auto
--再次证明修改在dgmgrl下修改参数standby_file_management，同时也修改了spfile文件里面的参数。
SYS@test> alter system set standby_file_management=manual scope=both ;
System altered.
--说明：正常情况下standby_file_management=auto，这样在主数据库下建立数据文件，在备用库会自动建立。
--我现在修改spfile的参数standby_file_management=manual scope=both ;
DGMGRL> show database verbose test
Database - test
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
test
Warning: ORA-16714: the value of property StandbyFileManagement is inconsistent with the database setting
Properties:
DGConnectIdentifier = 'test'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'ENABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'auto'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '/u01/app/oracle11g/oradata/test, /u01/app/oracle11g/oradata/test'
LogFileNameConvert = '/u01/app/oracle11g/oradata/test, /u01/app/oracle11g/oradata/test'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
SidName = 'test'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hisdg)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=test_DGMGRL.com)(INSTANCE_NAME=test)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/u01/app/oracle11g/archivelog'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
WARNING
-- 出现WARNING。提示Warning: ORA-16714: the value of property StandbyFileManagement is inconsistent with the database setting不符合。
SCOTT@test> alter system set standby_file_management=auto scope=both ;
System altered.
--再次使用show database verbose test查看，一切正常！
总结：
可以说明一旦你使用dgmgrl来管理，你必须一直使用它来管理dataguard，而不要使用sqlplus来修改相关参数，这样会出现一些参数不一致的问题。
就像Apress.Expert.Consolidation.in.Oracle.Database.12c.Nov.2013.pdf电子文档提到的那样：
Caution ■ Do not try and modify Data guard related parameters in the initialization files! You would only confuse the
Broker. If you did anyway, you had to do a manual reconciliation of the Broker and database parameters. P353
When used, Data Guard Broker will rely on its own binary configuration files and additional background
processes to configure the relevant initialization parameters upon instance start; it will also monitor the databases
in the configuration. In clustered environments the configuration files need to be on shared storage. ASM, raw
devices, and cluster file systems are possible candidates to store the files; ASM really is the best choice. You do not
have to replicate the configuration on each database. Rather, the broker will automatically preserve the single image
view of your Data Guard configuration by replicating changes to all databases involved. You should not try to issue
SQL commands through sqlplusto modify the Data Guard configuration because your changes are likely to be
overwritten the next time the Data Guard broker starts: once the broker, always the broker; unless you remove or
disable the Broker configuration. P339
--你一旦使用dgmgrl，就一直使用它，除非禁用它。