Abbas,
It is generally good form to include the list with your replies so everyone
can see the conversation.
Seth Miller
On Thu, Feb 11, 2016 at 2:49 PM, max scalf <oracle.blog3@xxxxxxxxx> wrote:
Abbas,
Thank you for those doc I will check those out, I remember looking at my
notes from the past as we are using NON default 1521 port(security teams
wishes), after enabling the broker configuration we see below entry and
that actually prevented us from doing a switchover with below entry and
hence to get around that we moved it to staticconnectidentifer to TEST_P
and TEST_S...
StaticConnectIdentifier =
'(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=primary)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=TEST_P_DGMGRL)(INSTANCE_NAME=TEST)(SERVER=DEDICATED)))'
Also here is another thing that I forgot to mentioned, If i were to run
"failover to test_s", it takes long time but works just fine. And as this
is part of our FAILOVER test, we bring the primary back up online and
reinstate the primary we are back in business, the show config command does
not hang.... seems like it only hang when primary is down completely.
On Thu, Feb 11, 2016 at 1:49 PM, abu fazal md abbas <
abbas.appsdba@xxxxxxxx> wrote:
Looks like you are using the static listener entry for both
*StaticConnectIdentifier* and *DGConnectIdentifier*, which is not
recommended as per Oracle documentation. Further, you have also defined
static entry for broker "DGB" service which is not recommended.
I would suggest to review the configuration based on the following
documentation
Oracle Data Guard Broker and Static Service Registration (Doc ID
1387859.1)
If the problem still persists, you may consider enabling tracing at
broker level to troubleshoot the issue further. Following is the document,
which can referred be for enabling tracing.
Diagnosing Connection Problems with an enabled Data Guard Broker
Configuration (Doc ID 745201.1)
*Thanks and Regards,*
Abbas
*Abu Fazal Mohammed Abbas*
+91-991-625-7994
Website: www.oraclebuffer.com
------------------------------
*From:* max scalf <oracle.blog3@xxxxxxxxx>
*To:* abu fazal md abbas <abbas.appsdba@xxxxxxxx>
*Cc:* Oracle Mailing List <oracle-l@xxxxxxxxxxxxx>
*Sent:* Friday, 12 February 2016 12:34 AM
*Subject:* Re: DataGuard Failover Test issues
We are using non default port of 1545, but local_listener is not pointing
to anything. We have a static listener entry in listener.ora file....see
below for entries from listener.ora and tnsnames.ora
standby:/oracle/test> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 12 06:01:20 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options
SQL> show parameter local_list%;
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
local_listener string
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release
11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options
standby:/oracle/test>
standby:/oracle/test> cd /oracle/test/11204/network/admin/
standby:/oracle/test/11204/network/admin>
standby:/oracle/test/11204/network/admin> cat listener.ora
LISTENER_test =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = standby)
(PORT = 1545)
)
)
)
)
STARTUP_WAIT_TIME_LISTENER_test = 0
CONNECT_TIMEOUT_LISTENER_test = 10
TRACE_LEVEL_LISTENER_test = OFF
SID_LIST_LISTENER_test=
(SID_LIST =
(SID_DESC =
(SDU = 32768)
(SID_NAME = test)
(GLOBAL_DBNAME=test_S)
(ORACLE_HOME = /oracle/test/11204)
)
(SID_DESC =
(SID_NAME = test)
(GLOBAL_DBNAME=test.WORLD)
(ORACLE_HOME = /oracle/test/11204)
)
(SID_DESC =
(SID_NAME = test)
(GLOBAL_DBNAME=test_S_DGMGRL)
(ORACLE_HOME = /oracle/test/11204)
)
(SID_DESC =
(SID_NAME = test)
(GLOBAL_DBNAME=test_S_DGMGRL.WORLD)
(ORACLE_HOME = /oracle/test/11204)
)
(SID_DESC =
(SID_NAME = test)
(GLOBAL_DBNAME=test_S_XPT.WORLD)
(ORACLE_HOME = /oracle/test/11204)
)
(SID_DESC =
(SID_NAME = test)
(GLOBAL_DBNAME=test_S_DGB.WORLD)
(ORACLE_HOME = /oracle/test/11204)
)
)
standby:/oracle/test/11204/network/admin>
standby:/oracle/test/11204/network/admin> cat tnsnames.ora
test.WORLD=
(DESCRIPTION =
(SDU = 32768)
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = primary)
(PORT = 1545)
)
)
(CONNECT_DATA =
(SID = test)
(GLOBAL_NAME = test.WORLD)
)
)
TEST_P.WORLD=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = TEST.WORLD)
(PROTOCOL = TCP)
(HOST = primary)
(PORT =1545)
)
)
(CONNECT_DATA =
(SID = test)
(GLOBAL_NAME = test)
(SERVICE_NAME = test_P)
)
)
TEST_S.WORLD=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = TEST.WORLD)
(PROTOCOL = TCP)
(HOST = standby)
(PORT =1545)
)
)
(CONNECT_DATA =
(SID = test)
(GLOBAL_NAME = test)
(SERVICE_NAME= test_S)
)
)
standby:/oracle/test/11204/network/admin>
On Thu, Feb 11, 2016 at 12:41 PM, abu fazal md abbas <
abbas.appsdba@xxxxxxxx> wrote:
I was suspecting a discrepancy between broker NetTimeout settings and TNS
retries/delay settings.
However, looks like that is not the case here.
The only setting that seems suspicious to me in the broker setting is the
*"StaticConnectIdentifier"* property which looks to be pointing to a
dynamic listener service. Are you not using a static listener service
(<db_unique_name>_DGMGRL.<db_domain>) for this setup?
Looks like you are also using a non default port (1545) for this
databases?
Can you confirm, if the local_listener of the databases point to that non
default port?
*Thanks and Regards,*
Abbas
*Abu Fazal Mohammed Abbas*
Website: www.oraclebuffer.com
------------------------------
*From:* max scalf <oracle.blog3@xxxxxxxxx>
*To:* abu fazal md abbas <abbas.appsdba@xxxxxxxx>
*Cc:* Oracle Mailing List <oracle-l@xxxxxxxxxxxxx>
*Sent:* Thursday, 11 February 2016 10:04 PM
*Subject:* Re: DataGuard Failover Test issues
Here it is...
DGMGRL> show configuration;
Configuration - test_config
Protection Mode: MaxPerformance
Databases:
test_p - Primary database
test_s - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>
DGMGRL> show database verbose test_p;
Database - test_p
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
test
Properties:
DGConnectIdentifier = 'test_p'
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 = '5'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = '/oracle/test/origlogA/,
/oracle/test/origlogA/, /oracle/test/origlogB/, /oracle/test/origlogB/,
/oracle/test/standbylog, /oracle/test/standbylog'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
SidName = 'test'
StaticConnectIdentifier = 'test_P'
StandbyArchiveLocation = '/oracle/test/oraarch/testarch'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
SUCCESS
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> show database verbose test_s;
Database - test_s
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Apply Rate: 78.00 KByte/s
Real Time Query: OFF
Instance(s):
test
Properties:
DGConnectIdentifier = 'test_s'
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 = '5'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = '/oracle/test/origlogA/,
/oracle/test/origlogA/, /oracle/test/origlogB/, /oracle/test/origlogB/,
/oracle/test/standbylog, /oracle/test/standbylog'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
SidName = 'test'
StaticConnectIdentifier = 'test_S'
StandbyArchiveLocation = '/oracle/test/oraarch/testarch'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
SUCCESS
DGMGRL> exit
standby:/oracle/test>
standby:/oracle/test>
standby:/oracle/test>
standby:/oracle/test>
standby:/oracle/test> tnsping test_s
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on
12-FEB-2016 03:32:18
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
/oracle/test/11204/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS =
(COMMUNITY = TEST.WORLD) (PROTOCOL = TCP) (HOST = standby) (PORT =1545)))
(CONNECT_DATA = (SID = test) (GLOBAL_NAME = test) (SERVICE_NAME= test_S)))
OK (0 msec)
standby:/oracle/test>
standby:/oracle/test> tnsping test_p
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on
12-FEB-2016 03:32:20
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
/oracle/test/11204/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS =
(COMMUNITY = TEST.WORLD) (PROTOCOL = TCP) (HOST = primary) (PORT =1545)))
(CONNECT_DATA = (SID = test) (GLOBAL_NAME = test) (SERVICE_NAME = test_P)))
OK (10 msec)
standby:/oracle/test>
On Thu, Feb 11, 2016 at 10:20 AM, abu fazal md abbas <
abbas.appsdba@xxxxxxxx> wrote:
Hi,
It seems to be an issue with connection timeout settings.
Could you provide the output of the following
dgmgrl> show database verbose test_p
dgmgrl> show database verbose test_s
and then from both primary and standby servers
$ tnsping test_p
$ tnsping test_s
*Thanks and Regards,*
Abbas
*Abu Fazal Mohammed Abbas*
Website: www.oraclebuffer.com
------------------------------
*From:* max scalf <oracle.blog3@xxxxxxxxx>
*To:* Oracle Mailing List <oracle-l@xxxxxxxxxxxxx>
*Sent:* Thursday, 11 February 2016 8:49 PM
*Subject:* Re: DataGuard Failover Test issues
quick update to that, after about 20 mins of hanging i got below error
from show config...
Configuration Status:
ORA-16662: network timeout when contacting a database
DGM-17017: unable to determine configuration status
DGMGRL>
On Thu, Feb 11, 2016 at 9:18 AM, max scalf <oracle.blog3@xxxxxxxxx>
wrote:
hello list,
We are in process of testing failover for our 11.2.0.4 data guard
environement. While under normal circumstance when we ran show
configuration for data guard broker we get success message as shown below
DGMGRL> show configuration verbose;
Configuration - test_config
Protection Mode: MaxPerformance
Databases:
test_p - Primary database
test_s - Physical standby database
Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
ObserverReconnect = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
ObserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>
now when we literally go power off our primary host(test_p) and come over
to standby host(test_p) we do show configuration it just hangs as shown
below, I waited a good 20 mins but no luck
DGMGRL> connect sys/XXXXXX;
Connected.
DGMGRL> show configuration verbose;
i do see message in alert log as show below, once we shutdown primary...
and when i issue show config in data guard broker, no errors in drcTEST.log
file either...
Fri Feb 12 02:13:00 2016
RFS[5]: Possible network disconnect with primary database
Fri Feb 12 02:13:00 2016
RFS[6]: Assigned to RFS process 3786
RFS[6]: Possible network disconnect with primary database
Fri Feb 12 02:13:00 2016
RFS[4]: Possible network disconnect with primary database
Fri Feb 12 02:13:00 2016
RFS[2]: Possible network disconnect with primary database
Fri Feb 12 02:14:32 2016
has anybody seem something similar ?? What am i doing wrong? I have
tested switchover 10's of time successfully but when it comes to failover
the show config part hangs....
