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

eric0435發表於2015-08-04
Oracle 10g

為了更簡單的管理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配置中的資料庫資訊。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-1761838/，如需轉載，請註明出處，否則將追究法律責任。

相關文章