If a memeber instance in a broker configuration is down you may see this error message
[oracle@oel71002 trace]$ dgmgrl sys/'oracle'@ICP01DMA
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Thu Nov 25 10:08:37 2021
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "ICP01DMA"
Connected as SYSDBA.
DGMGRL>
DGMGRL> show configuration
Configuration - ICP01DMA_DR
Protection Mode: MaxPerformance
Members:
ICP01DMA - Primary database
ICC01DMA - Physical standby database
ICC11DMA - Physical standby database
Error: ORA-16664: unable to receive the result from a member
Fast-Start Failover: DISABLED
Configuration Status:
ERROR (status updated 44 seconds ago)you can see that there are 2 instances of standby
DGMGRL> shwo database 'ICC11DMA'
Unrecognized command "shwo", try "help"
DGMGRL> show database 'ICC11DMA'
Database - ICC11DMA
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: (unknown)
Apply Lag: (unknown)
Average Apply Rate: (unknown)
Real Time Query: OFF
Instance(s):
ICC11DMA_1 (apply instance)
ICC11DMA_3but in relaity there are 3 instances now and the one present in broker configuration is down
[oracle@oel71603 dbs]$ srvctl status database -d ICC11DMA
Instance ICC11DMA_1 is running on node oel71601
Instance ICC11DMA_2 is running on node oel71603
Instance ICC11DMA_3 is not running on node oel71604we will try to start it but it seems there are no public threads for this instance
[oracle@oel71603 dbs]$ srvctl start instance -d ICC11DMA_3 -i ICC11DMA_3
PRCD-1120 : The resource for database ICC11DMA_3 could not be found.
PRCR-1001 : Resource ora.icc11DMA_3.db does not exist
[oracle@oel71603 dbs]$ srvctl start instance -d ICC11DMA -i ICC11DMA_3
PRCR-1013 : Failed to start resource ora.icc11DMA.db
PRCR-1064 : Failed to start resource ora.icc11DMA.db on node oel71604
CRS-5017: The resource action "ora.icc11DMA.db start" encountered the following error:
ORA-01620: no public threads are available for mounting
. For details refer to "(:CLSN00107:)" in "/u01/orabase/diag/crs/oel71604/crs/trace/crsd_oraagent_oracle.trc".
CRS-2674: Start of 'ora.icc11DMA.db' on 'oel71604' failedSQL> select group#,thread#,status, bytes/1024/1024 "size in MB" from v$log;
GROUP# THREAD# STATUS size in MB
---------- ---------- ---------------- ----------
3 1 UNUSED 200
4 1 UNUSED 200
1 4 UNUSED 200
2 4 UNUSED 200
SQL> select thread#,group#,STATUS from v$standby_log;
THREAD# GROUP# STATUS
---------- ---------- ----------
1 11 ACTIVE
1 12 UNASSIGNED
1 13 UNASSIGNED
4 21 UNASSIGNED
4 22 ACTIVE
4 23 UNASSIGNED
6 rows selected.so we will add the public thread and standby redo logs as well
recover managed standby database cancel;
alter system set standby_file_management=MANUAL scope=both sid='*';
ALTER DATABASE ADD LOGFILE THREAD 3 group 5 size 200M;
ALTER DATABASE ADD LOGFILE THREAD 3 group 6 size 200M;
ALTER DATABASE ADD standby LOGFILE THREAD 3 group 31 size 200M;
ALTER DATABASE ADD standby LOGFILE THREAD 3 group 32 size 200M;
ALTER DATABASE ADD standby LOGFILE THREAD 3 group 33 size 200M;
SQL> SQL> select group#,thread#,status, bytes/1024/1024 "size in MB" from v$log;
GROUP# THREAD# STATUS size in MB
---------- ---------- ---------------- ----------
3 1 UNUSED 200
4 1 UNUSED 200
6 3 UNUSED 200
5 3 UNUSED 200
2 4 UNUSED 200
1 4 UNUSED 200
6 rows selected.
SQL> select thread#,group#,STATUS from v$standby_log;
THREAD# GROUP# STATUS
---------- ---------- ----------
1 11 ACTIVE
1 12 UNASSIGNED
1 13 UNASSIGNED
4 21 UNASSIGNED
4 22 ACTIVE
4 23 UNASSIGNED
3 31 UNASSIGNED
3 32 UNASSIGNED
3 33 UNASSIGNED
9 rows selected.
alter system set standby_file_management=AUTO scope=both sid='*';now instance is started
[oracle@oel71603 dbs]$ srvctl status database -d ICC11DMA
Instance ICC11DMA_1 is running on node oel71601
Instance ICC11DMA_2 is running on node oel71603
Instance ICC11DMA_3 is running on node oel71604
now we have to remove the standby database and add it back so that all three insatnces are registered in broker configuration
DGMGRL> remove database 'ICC11DMA'
Removed database "ICC11DMA" from the configuration
DGMGRL> ADD DATABASE 'ICC11DMA' AS CONNECT IDENTIFIER IS 'ICC11DMA' MAINTAINED AS PHYSICAL;
Database "ICC11DMA" added
DGMGRL> ENABLE DATABASE 'ICC11DMA'
Enabled.
DGMGRL> show database 'ICC11DMA'
Database - ICC11DMA
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 8 seconds (computed 1 second ago)
Average Apply Rate: 4.95 MByte/s
Real Time Query: OFF
Instance(s):
ICC11DMA_1
ICC11DMA_2
ICC11DMA_3 (apply instance)
Database Status:
SUCCESScheck configuartion
DGMGRL> show database 'ICC01DMA'
Database - ICC01DMA
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 170.00 KByte/s
Real Time Query: OFF
Instance(s):
ICC01DMA_1 (apply instance)
ICC01DMA_2
Database Status:
SUCCESS
DGMGRL> show database 'ICC11DMA'
Database - ICC11DMA
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 485.00 KByte/s
Real Time Query: OFF
Instance(s):
ICC11DMA_1
ICC11DMA_2
ICC11DMA_3 (apply instance)
Database Status:
SUCCESS
DGMGRL> show configuration
Configuration - ICP01DMA_DR
Protection Mode: MaxPerformance
Members:
ICP01DMA - Primary database
ICC01DMA - Physical standby database
ICC11DMA - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 56 seconds ago)
DBATracker 