oracle 10g data guard broker ORA-16607 故障处理案例

Posted on by YongJing(敬勇)

为了更简单的管理data guard可以配置data guard broker来进行管理，配置broker过程如下：

[oracle@oracle11g ~]$ dgmgrl xxx/xxxxx@xxx
DGMGRL for Linux: Version 10.2.0.5.0 - Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> help

The following commands are available:

add            Add a standby database to the broker configuration
connect        Connect to an Oracle instance
create         Create a broker configuration
disable        Disable a configuration, a database, or Fast-Start Failover
edit           Edit a configuration, database, or instance
enable         Enable a configuration, a database, or Fast-Start Failover
exit           Exit the program
failover       Change a standby database to be the primary database
help           Display description and syntax for a command
quit           Exit the program
reinstate      Change a disabled database into a viable standby database
rem            Comment to be ignored by DGMGRL
remove         Remove a configuration, database, or instance
show           Display information about a configuration, database, or instance
shutdown       Shutdown a currently running Oracle instance
start          Start Fast-Start Failover observer
startup        Start an Oracle database instance
stop           Stop Fast-Start Failover observer
switchover     Switch roles between the primary database and a standby database

Use "help " to see syntax for individual commands

DGMGRL> show configuration
Error: ORA-16532: Data Guard broker configuration does not exist

Configuration details cannot be determined by DGMGRL
DGMGRL> help create

Create a broker configuration

Syntax:

  CREATE CONFIGURATION  AS
    PRIMARY DATABASE IS 
    CONNECT IDENTIFIER IS ;

创建broker配置文件

DGMGRL> create configuration 'broker_dg' as primary database is test connect identifier is test;
Configuration "broker_dg" created with primary database "test"
DGMGRL> show configuration

Configuration
  Name:                broker_dg
  Enabled:             NO
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    test - Primary database

Current status for "broker_dg":
DISABLED

DGMGRL> help show configuration

Display information about a configuration, database, or instance

Syntax:

  SHOW CONFIGURATION;

  SHOW DATABASE [VERBOSE]  [];

  SHOW INSTANCE [VERBOSE]  []
    [ON DATABASE ];

DGMGRL> help add

Add a standby database to the broker configuration

Syntax:

  ADD DATABASE  AS
    CONNECT IDENTIFIER IS 
    MAINTAINED AS {PHYSICAL|LOGICAL};

向配置文件添加备库(物理备库test_dg)

DGMGRL> add database test_dg as connect identifier is test_dg maintained as physical;
Database "test_dg" added
DGMGRL> show configuration

Configuration
  Name:                broker_dg
  Enabled:             NO
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    test    - Primary database
    test_dg - Physical standby database

Current status for "broker_dg":
DISABLED

启用broker配置

DGMGRL> enable configuration
Enabled.

显示broker配置信息,显示如下错误信息:

DGMGRL> show configuration

Configuration
  Name:                broker_dg
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    test    - Primary database
    test_dg - Physical standby database

Current status for "broker_dg":
Warning: ORA-16607: one or more databases have failed

显示主库test的状态报告

DGMGRL> show database test statusreport
STATUS REPORT
       INSTANCE_NAME   SEVERITY ERROR_TEXT

显示备库test_dg的状态报告，显示如下错误信息:

DGMGRL> show database test_dg statusreport
Error: ORA-16664: unable to receive the result from a remote database

显示主库test的详细信息

DGMGRL> show database verbose test

Database
  Name:            test
  Role:            PRIMARY
  Enabled:         YES
  Intended State:  ONLINE
  Instance(s):
    test

  Properties:
    InitialConnectIdentifier        = 'test'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    Dependency                      = ''
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '180'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '10'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '/u03/app/oracle/oradata/test/, /u01/app/oracle/oradata/test/, /u03/app/oracle/oradata/test_ldg/, /u01/app/oracle/oradata/test/'
    LogFileNameConvert              = '/u03/app/oracle/oradata/test/, /u01/app/oracle/oradata/test/, /u03/app/oracle/oradata/test_ldg/, /u01/app/oracle/oradata/test/'
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'xxxxxx'
    SidName                         = 'test'
    LocalListenerAddress            = '(ADDRESS=(PROTOCOL=tcp)(HOST=xxxxxx)(PORT=1521))'
    StandbyArchiveLocation          = '/u02/archive/'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    LatestLog                       = '(monitor)'
    TopWaitEvents                   = '(monitor)'

Current status for "test":
SUCCESS

显示备库test_dg的详细信息，显示如下错误:

DGMGRL> show database verbose test_dg

Database
  Name:            test_dg
  Role:            PHYSICAL STANDBY
  Enabled:         YES
  Intended State:  ONLINE
  Instance(s):
    test_dg

  Properties:
    InitialConnectIdentifier        = 'test_dg'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    Dependency                      = ''
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '180'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '2'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '/u03/app/oracle/oradata/test_ldg/, /u03/app/oracle/oradata/test/, /u01/app/oracle/oradata/test/, /u03/app/oracle/oradata/test/'
    LogFileNameConvert              = '/u03/app/oracle/oradata/test_ldg/, /u03/app/oracle/oradata/test/, /u01/app/oracle/oradata/test/, /u03/app/oracle/oradata/test/'
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'jingyong1'
    SidName                         = 'test_dg'
    LocalListenerAddress            = '(ADDRESS=(PROTOCOL=tcp)(HOST=jingyong1)(PORT=1521))'
    StandbyArchiveLocation          = '/u03/app/oracle/archive/'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    LatestLog                       = '(monitor)'
    TopWaitEvents                   = '(monitor)'

Current status for "test_dg":
Error: ORA-16664: unable to receive the result from a remote database

显然是物理备库test_dg出了故障，检查备库的drctest_dg.log该日志文件在oracle10g中存储bdump文件中:

DG 2015-08-04-17:07:48        0 2 0 NSV0: Failed to connect to remote database test. Error is ORA-12514
DG 2015-08-04-17:07:48        0 2 0 NSV0: Failed to send message to site test. Error code is ORA-12514.
DG 2015-08-04-17:07:48        0 2 0 DMON: Database test returned ORA-12514
DG 2015-08-04-17:07:48        0 2 0       for opcode = CTL_GET_STATUS, phase = BEGIN, req_id = 1.1.886847999
DG 2015-08-04-17:07:59        0 2 0 RSM 0 received GETPROP request: rid=0x02010000, pid=54
DG 2015-08-04-17:07:59        0 2 0 Database Resource: Get Property InconsistentProperties
DG 2015-08-04-17:07:59        0 2 0 RSM Warning: Property 'ArchiveLagTarget' has inconsistent values:METADATA='0', SPFILE='', DATABASE='0'
DG 2015-08-04-17:07:59        0 2 0 RSM0: HEALTH CHECK WARNING: ORA-16714: the value of property ArchiveLagTarget is inconsistent with the database setting
DG 2015-08-04-17:07:59        0 2 0 RSM Warning: Property 'LogArchiveMaxProcesses' has inconsistent values:METADATA='2', SPFILE='', DATABASE='2'
DG 2015-08-04-17:07:59        0 2 0 RSM0: HEALTH CHECK WARNING: ORA-16714: the value of property LogArchiveMaxProcesses is inconsistent with the database setting
DG 2015-08-04-17:07:59        0 2 0 RSM Warning: Property 'LogArchiveMinSucceedDest' has inconsistent values:METADATA='1', SPFILE='', DATABASE='1'
DG 2015-08-04-17:07:59        0 2 0 RSM0: HEALTH CHECK WARNING: ORA-16714: the value of property LogArchiveMinSucceedDest is inconsistent with the database setting
DG 2015-08-04-17:07:59        0 2 0 SPFILE is missing value for property 'LogArchiveTrace' with sid='test_dg'
DG 2015-08-04-17:07:59        0 2 0 RSM Warning: Property 'LogArchiveTrace' has inconsistent values:METADATA='0', SPFILE='(missing)', DATABASE='0'
DG 2015-08-04-17:07:59        0 2 0 RSM0: HEALTH CHECK WARNING: ORA-16714: the value of property LogArchiveTrace is inconsistent with the database setting
DG 2015-08-04-17:07:59        0 2 0 SPFILE is missing value for property 'LogArchiveFormat' with sid='test_dg'
DG 2015-08-04-17:07:59        0 2 0 RSM Warning: Property 'LogArchiveFormat' has inconsistent values:METADATA='%t_%s_%r.dbf', SPFILE='(missing)', DATABASE='%t_%s_%r.dbf'
DG 2015-08-04-17:07:59        0 2 0 RSM0: HEALTH CHECK WARNING: ORA-16714: the value of property LogArchiveFormat is inconsistent with the database setting
DG 2015-08-04-17:07:59        0 2 0 Database Resource GetProperty succeeded
DG 2015-08-04-17:07:59  2010000 4 886848003 DMON: MON_PROPERTY operation completed
DG 2015-08-04-17:07:59        0 2 0 NSV0: Failed to connect to remote database test. Error is ORA-12514
DG 2015-08-04-17:07:59        0 2 0 NSV0: Failed to send message to site test. Error code is ORA-12514.
DG 2015-08-04-17:07:59        0 2 0 DMON: Database test returned ORA-12514
DG 2015-08-04-17:07:59        0 2 0       for opcode = MON_PROPERTY, phase = NULL, req_id = 1.1.886848003
DG 2015-08-04-17:08:03        0 2 0 DRCX: could not find task req_id=1.1.886847999 for PROBE.

从上面的信息中可以看到如下信息:

RSM Warning: Property 'ArchiveLagTarget' has inconsistent values:METADATA='0', SPFILE='', DATABASE='0'
DG 2015-08-04-17:07:59        0 2 0 RSM0: HEALTH CHECK WARNING: ORA-16714: the value of property ArchiveLagTarget is inconsistent with the database setting
DG 2015-08-04-17:07:59        0 2 0 RSM Warning: Property 'LogArchiveMaxProcesses' has inconsistent values:METADATA='2', SPFILE='', DATABASE='2'
DG 2015-08-04-17:07:59        0 2 0 RSM0: HEALTH CHECK WARNING: ORA-16714: the value of property LogArchiveMaxProcesses is inconsistent with the database setting
DG 2015-08-04-17:07:59        0 2 0 RSM Warning: Property 'LogArchiveMinSucceedDest' has inconsistent values:METADATA='1', SPFILE='', DATABASE='1'
DG 2015-08-04-17:07:59        0 2 0 RSM0: HEALTH CHECK WARNING: ORA-16714: the value of property LogArchiveMinSucceedDest is inconsistent with the database setting
DG 2015-08-04-17:07:59        0 2 0 SPFILE is missing value for property 'LogArchiveTrace' with sid='test_dg'
DG 2015-08-04-17:07:59        0 2 0 RSM Warning: Property 'LogArchiveTrace' has inconsistent values:METADATA='0', SPFILE='(missing)', DATABASE='0'
DG 2015-08-04-17:07:59        0 2 0 RSM0: HEALTH CHECK WARNING: ORA-16714: the value of property LogArchiveTrace is inconsistent with the database setting
DG 2015-08-04-17:07:59        0 2 0 SPFILE is missing value for property 'LogArchiveFormat' with sid='test_dg'
DG 2015-08-04-17:07:59        0 2 0 RSM Warning: Property 'LogArchiveFormat' has inconsistent values:METADATA='%t_%s_%r.dbf', SPFILE='(missing)', DATABASE='%t_%s_%r.dbf'
DG 2015-08-04-17:07:59        0 2 0 RSM0: HEALTH CHECK WARNING: ORA-16714: the value of property LogArchiveFormat is inconsistent with the database setting

这里显示
‘ArchiveLagTarget’ has inconsistent values:METADATA=’0′, SPFILE=”, DATABASE=’0′
这说明archive_lag_target参数spfile文件的值与database,metadata的值不相同(它们都为0)。

‘LogArchiveMaxProcesses’ has inconsistent values:METADATA=’2′, SPFILE=”, DATABASE=’2′ 这说明log_archive_max_processes参数spfile文件的值与database,metadata的值不相同(它们都为2)。

‘LogArchiveMinSucceedDest’ has inconsistent values:METADATA=’1′, SPFILE=”, DATABASE=’1′ 这说明log_archive_min_succeed_dest参数spfile文件的值与database,metadata的值不相同(它们都为1)。

‘LogArchiveTrace’ has inconsistent values:METADATA=’0′, SPFILE='(missing)’, DATABASE=’0′ 这说明log_archive_trace参数spfile文件的值与database,metadata的值不相同（它们都为0)。

‘LogArchiveFormat’ with sid=’test_dg’
DG 2015-08-04-17:07:59 0 2 0 RSM Warning: Property ‘LogArchiveFormat’ has inconsistent values:METADATA=’%t_%s_%r.dbf’, SPFILE='(missing)’, DATABASE=’%t_%s_%r.dbf’ 这说明log_archive_format参数spfile文件的值与database,metadata的值不相同（它们都为’%t_%s_%r.dbf’)。

对以上不一致参数进行修改

SQL> alter system set log_archive_max_processes=2 scope=spfile;

System altered.

SQL> alter system set archive_lag_target=0 scope=spfile;

System altered.

SQL> alter system set log_archive_min_succeed_dest=1 scope=spfile;

System altered.

SQL> alter system set log_archive_trace=0 scope=spfile;

System altered.


SQL> alter system set log_archive_format='%t_%s_%r.dbf' scope=spfile;

System altered.

再次检查broker配置

DGMGRL> show database verbose test_dg

Database
  Name:            test_dg
  Role:            PHYSICAL STANDBY
  Enabled:         YES
  Intended State:  ONLINE
  Instance(s):
    test_dg

  Properties:
    InitialConnectIdentifier        = 'test_dg'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    Dependency                      = ''
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '180'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '2'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '/u03/app/oracle/oradata/test_ldg/, /u03/app/oracle/oradata/test/, /u01/app/oracle/oradata/test/, /u03/app/oracle/oradata/test/'
    LogFileNameConvert              = '/u03/app/oracle/oradata/test_ldg/, /u03/app/oracle/oradata/test/, /u01/app/oracle/oradata/test/, /u03/app/oracle/oradata/test/'
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'jingyong1'
    SidName                         = 'test_dg'
    LocalListenerAddress            = '(ADDRESS=(PROTOCOL=tcp)(HOST=jingyong1)(PORT=1521))'
    StandbyArchiveLocation          = '/u03/app/oracle/archive/'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    LatestLog                       = '(monitor)'
    TopWaitEvents                   = '(monitor)'

Current status for "test_dg":
SUCCESS

DGMGRL> show configuration

Configuration
  Name:                broker_dg
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    test    - Primary database
    test_dg - Physical standby database

Current status for "broker_dg":
SUCCESS

现在已经能成功显示broker配置中的数据库信息。

发表评论

电子邮件地址不会被公开。

Proudly powered by WordPress | Indrajeet by Sus Hill.