Saturday, August 8, 2020

Removing a Failed Standby Database From a Data Guard Configuration

A previous post explained steps for removing a standby instance from a data guard configuration. This post explains steps for the same but when the standby being removed has failed and cannot be reached (or connect into).
In a standby configuration with multiple standby databases once instance is unreachable due to hardware failure. The issue is irrecoverable and only option is to rebuild the node and the standby instance. In mean time the existing standby configuration will give an error state due to the unavailability of the failed instance.
DGMGRL> show configuration

Configuration - fc_pp_dg

  Protection Mode: MaxAvailability
  Members:
  ppdb1  - Primary database
    ppdb2  - Physical standby database
    ppdb3  - Physical standby database
      ppdb4  - Physical standby database (receiving current redo)
    ppfs1  - Far sync instance
      ppdb5  - Physical standby database
      ppdb6  - Physical standby database
      ppdb9  - Physical standby database
      ppdb10 - Physical standby database

  Members Not Receiving Redo:
  ppfs2  - Far sync instance (alternate of ppfs1)
  ppdb8  - Physical standby database
    Error: ORA-12170: TNS:Connect timeout occurred

Fast-Start Failover:  Disabled

Configuration Status:
ERROR   (status updated 96 seconds ago)
As the first step remove any references to the failed instance on RedoRoutes.

Then issue the remove command which will succeed with a warning.
DGMGRL> remove database ppdb8;
Warning: ORA-16620: one or more members could not be reached for a remove operation

Removed database "ppdb8" from the configuration
The warning is due to broker being unable to connect to the failed instance to execute the clean up commands. The dataguard broke log shows this.
2020-07-29T12:33:36.403+00:00
Failed to connect to remote database ppdb8. Error is ORA-12170
Metadata Resync failed. Status = ORA-12170
2020-07-29T12:33:48.691+00:00
Failed to connect to remote database ppdb8. Error is ORA-12170
Failed to send message to member ppdb8. Error code is ORA-12170.
Data Guard Broker Status Summary:
  Type                        Name                             Severity  Status
  Configuration               fc_pp_dg                       Warning  ORA-16607: one or more members have failed
  Primary Database            ppdb1                          Success  ORA-0: normal, successful completion
  Physical Standby Database   ppdb2                          Success  ORA-0: normal, successful completion
  Physical Standby Database   ppdb3                          Success  ORA-0: normal, successful completion
  Physical Standby Database   ppdb4                          Success  ORA-0: normal, successful completion
  Physical Standby Database   ppdb5                          Success  ORA-0: normal, successful completion
  Physical Standby Database   ppdb6                          Success  ORA-0: normal, successful completion
  Far Sync Instance           ppfs1                          Success  ORA-0: normal, successful completion
  Far Sync Instance           ppfs2                          Success  ORA-0: normal, successful completion
  Physical Standby Database   ppdb8                            Error  ORA-12170: TNS:Connect timeout occurred
  Physical Standby Database   ppdb9                          Success  ORA-0: normal, successful completion
  Physical Standby Database   ppdb10                         Success  ORA-0: normal, successful completion
2020-07-29T12:34:00.979+00:00
Failed to connect to remote database ppdb8. Error is ORA-12170
Failed to send message to member ppdb8. Error code is ORA-12170.
2020-07-29T12:34:05.646+00:00
REMOVE DATABASE ppdb8
2020-07-29T12:34:17.939+00:00
Failed to connect to remote database ppdb8. Error is ORA-12170
Failed to send message to member ppdb8. Error code is ORA-12170.
Database ppdb8 (0x0a001000) could not be contacted for database removal, status = ORA-12170
2020-07-29T12:34:31.571+00:00
Failed to connect to remote database ppdb8. Error is ORA-12170
Failed to send message to member ppdb8. Error code is ORA-12170.
2020-07-29T12:34:33.297+00:00
Database ppdb8 removal completed with warning ORA-16620
REMOVE DATABASE  completed with warning ORA-16620
However, all the other databases that are part of the dataguard configuration would have had their log_archive_config parameter updated by removing any reference to the failed database.
NAME                           VALUE
------------------------------ -----------------------------------------
log_archive_config             dg_config=(ppdb1,ppdb2,ppdb3,ppdb4,ppfs1,
                               ppdb5,ppdb6,ppdb9,ppdb10,ppfs2)


Once the failed instance is removed the dataguard broke shows status success.
DGMGRL>  show configuration

Configuration - fc_pp_dg

  Protection Mode: MaxAvailability
  Members:
  ppdb1  - Primary database
    ppdb2  - Physical standby database
    ppdb3  - Physical standby database
      ppdb4  - Physical standby database (receiving current redo)
    ppfs1  - Far sync instance
      ppdb5  - Physical standby database
      ppdb6  - Physical standby database
      ppdb9  - Physical standby database
      ppdb10 - Physical standby database

  Members Not Receiving Redo:
  ppfs2  - Far sync instance (alternate of ppfs1)

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 55 seconds ago)
Related Posts
Removing a Standby Database From a Data Guard Configuration
Adding a New Physical Standby to Exiting Data Guard Setup
Posted by at
Labels: , , ,