[20140416]使用dgmgrl管理dataguard(4).txt

[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,就一直使用它,除非禁用它。

时间: 2024-09-20 21:44:41

[20140416]使用dgmgrl管理dataguard(4).txt的相关文章

[20140416]使用dgmgrl管理dataguard(5).txt

[20140416]使用dgmgrl管理dataguard(5).txt 参考链接: http://blog.itpub.net/267265/viewspace-1142649/ http://blog.itpub.net/267265/viewspace-1143027/ http://blog.itpub.net/267265/viewspace-1143058/ http://blog.itpub.net/267265/viewspace-1143126/ 前面我提到一旦使用dgmgrl

[20140418]使用dgmgrl管理dataguard(6).txt

[20140418]使用dgmgrl管理dataguard(6).txt 参考链接: http://blog.itpub.net/267265/viewspace-1142649/ http://blog.itpub.net/267265/viewspace-1143027/ http://blog.itpub.net/267265/viewspace-1143058/ http://blog.itpub.net/267265/viewspace-1143126/ http://blog.itp

[20140420]使用dgmgrl管理dataguard(9).txt

[20140420]使用dgmgrl管理dataguard(9).txt 参考链接: http://blog.itpub.net/267265/viewspace-1142649/ http://blog.itpub.net/267265/viewspace-1143027/ http://blog.itpub.net/267265/viewspace-1143058/ http://blog.itpub.net/267265/viewspace-1143126/ http://blog.itp

[20140420]使用dgmgrl管理dataguard(8).txt

[20140420]使用dgmgrl管理dataguard(8).txt 参考链接: http://blog.itpub.net/267265/viewspace-1142649/ http://blog.itpub.net/267265/viewspace-1143027/ http://blog.itpub.net/267265/viewspace-1143058/ http://blog.itpub.net/267265/viewspace-1143126/ http://blog.itp

[20140418]使用dgmgrl管理dataguard(7).txt

[20140418]使用dgmgrl管理dataguard(7).txt 参考链接: http://blog.itpub.net/267265/viewspace-1142649/ http://blog.itpub.net/267265/viewspace-1143027/ http://blog.itpub.net/267265/viewspace-1143058/ http://blog.itpub.net/267265/viewspace-1143126/ http://blog.itp

[20151109]使用dgmgrl管理dataguard(15)

[20151109]使用dgmgrl管理dataguard(15).txt 参考链接: http://blog.itpub.net/267265/viewspace-1142649/ http://blog.itpub.net/267265/viewspace-1143027/ http://blog.itpub.net/267265/viewspace-1143058/ http://blog.itpub.net/267265/viewspace-1143126/ http://blog.it

[20140420]使用dgmgrl管理dataguard(10)

[20140420]使用dgmgrl管理dataguard(10).txt 参考链接: http://blog.itpub.net/267265/viewspace-1142649/ http://blog.itpub.net/267265/viewspace-1143027/ http://blog.itpub.net/267265/viewspace-1143058/ http://blog.itpub.net/267265/viewspace-1143126/ http://blog.it

[20140422]使用dgmgrl管理dataguard(12)

[20140422]使用dgmgrl管理dataguard(12).txt 参考链接: http://blog.itpub.net/267265/viewspace-1142649/ http://blog.itpub.net/267265/viewspace-1143027/ http://blog.itpub.net/267265/viewspace-1143058/ http://blog.itpub.net/267265/viewspace-1143126/ http://blog.it

[20140422]使用dgmgrl管理dataguard(11)

[20140422]使用dgmgrl管理dataguard(11).txt 参考链接: http://blog.itpub.net/267265/viewspace-1142649/ http://blog.itpub.net/267265/viewspace-1143027/ http://blog.itpub.net/267265/viewspace-1143058/ http://blog.itpub.net/267265/viewspace-1143126/ http://blog.it