Hi. I worked with the op last night and resolved the problem. The
dgconnectidentifier was pointing to a dynamic service. We changed the tnsnames
entry for the dgconnectidentifier to point to the static service and that
resolved the issue.
Sent from my iPad
On Jun 3, 2015, at 9:51 AM, Seth Miller <sethmiller.sm@xxxxxxxxx> wrote:
Niall,
Isn't LOCAL_LISTENER used only for dynamic service registration? If the
services are statically registered with the listeners, there shouldn't be any
need to set the LOCAL_LISTENER parameter.
Seth Miller
On Wed, Jun 3, 2015 at 5:37 AM, Niall Litchfield
<niall.litchfield@xxxxxxxxx> wrote:
Hi
You seem to have a somewhat complex setup (unless details have been edited
to protect security). You'll want to read, and get your head around Oracle
Data Guard Broker and Static Service Registration (Doc ID 1387859.1) . It
looks to me as though you're in the maintain configuration manually section
of that doc.
You have
HOST: PRIME
SID: PRIME
LISTENER : LISTENER_PRIME port 1540
HOST: STANDBY
SID: STANDBY
LISTENER: LISTENER_STANDBY port 1545
You've I think edited the static connect identifiers to correctly try to
connect to <db_unique_name>_DGMGRL on the 2 ports above. I suspect that you
haven't updated LOCAL_LISTENER to point at the non-standard listener. To get
your current configuration to work you'll need I think to update
local_listener appropriately and then recreate the dg broker config. Is
there a reason that you aren't using 1521, and/or a different port on
standby from primary ?
On Tue, Jun 2, 2015 at 10:08 PM, max scalf <oracle.blog3@xxxxxxxxx> wrote:
I still get the same error...after editing StaticConnectIdentifier for both
prime and standby...
DGMGRL> show database prime StaticConnectIdentifier;
StaticConnectIdentifier =
'(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prime)(PORT=1540))(CONNECT_DATA=(SERVICE_NAME=prime_DGMGRL)(INSTANCE_NAME=prime)(SERVER=DEDICATED)))'
DGMGRL>
DGMGRL> show database standby StaticConnectIdentifier;
StaticConnectIdentifier =
'(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=standby)(PORT=1545))(CONNECT_DATA=(SERVICE_NAME=standby_DGMGRL)(INSTANCE_NAME=prime)(SERVER=DEDICATED)))'
DGMGRL> show configuration;
Configuration - prime
Protection Mode: MaxPerformance
Databases:
prime - Primary database
standby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> switchover to standby;
Performing switchover NOW, please wait...
Operation requires a connection to instance "prime" on database "standby"
Connecting to instance "prime"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in
connect descriptor
Failed.
Warning: You are no longer connected to ORACLE.
connect to instance "prime" of database "standby"
DGMGRL>
On Tue, Jun 2, 2015 at 2:49 PM, Andrew Kerber <andrew.kerber@xxxxxxxxx>
wrote:
Nice catch. Don't know why I didn't see that. Blame it on age.
Sent from my iPhone
On Jun 2, 2015, at 2:06 PM, Seth Miller <sethmiller.sm@xxxxxxxxx> wrote:
Max,
Your standby listener is listening on port 1545 but dgmgrl says the
standby connection is on port 1521. Either add the port to your listener
or change the connection string in dgmgrl.
Seth Miller
On Tue, Jun 2, 2015 at 1:49 PM, Andrew Kerber <andrew.kerber@xxxxxxxxx>
wrote:
This is one of those cases where I really need to log in and experiment.
Anyway, the problem is your static listener entry isn't pointing to the
right place. Set up a tnsnames entry and try to connect to the standby
while it is down from sqlplus in the primary. Do you have password file
set up by the way ? On the standby that is.
Sent from my iPad
On Jun 2, 2015, at 1:41 PM, max scalf <oracle.blog3@xxxxxxxxx> wrote:
Same thing...
[oracle@prime admin]$ dgmgrl sys/abcd1234
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> switchover to standby;
Performing switchover NOW, please wait...
Operation requires a connection to instance "prime" on database
"standby"
Connecting to instance "prime"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in
connect descriptor
Failed.
Warning: You are no longer connected to ORACLE.
connect to instance "prime" of database "standby"
DGMGRL>
On Tue, Jun 2, 2015 at 1:39 PM, Andrew Kerber
<andrew.kerber@xxxxxxxxx> wrote:
Oh, do this. Connect to dgmgrl with sys/syspassword rather than a /
and see if that fixes it.
Sent from my iPad
On Jun 2, 2015, at 1:36 PM, max scalf <oracle.blog3@xxxxxxxxx> wrote:
Sorry, disregard the last status...here is the new one...i had to
bounce the listener....but still the same issue...
[oracle@standby admin]$ lsnrctl reload LISTENER_STANDBY
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 03-JUN-2015
03:35:54
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=standby)(PORT=1545)))
The command completed successfully
[oracle@standby admin]$ lsnrctl status LISTENER_STANDBY
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 03-JUN-2015
03:35:57
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=standby)(PORT=1545)))
STATUS of the LISTENER
------------------------
Alias LISTENER_STANDBY
Version TNSLSNR for Linux: Version 11.2.0.4.0 -
Production
Start Date 02-JUN-2015 21:32:29
Uptime 0 days 6 hr. 3 min. 28 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File
/ora_home/app/product/11.2/dbhome/network/admin/listener.ora
Listener Log File
/ora_home/app/product/11.2/dbhome/log/diag/tnslsnr/standby/listener_standby/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=standby.localdomain)(PORT=1545)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1545)))
Services Summary...
Service "prime" has 1 instance(s).
Instance "prime", status UNKNOWN, has 1 handler(s) for this
service...
Service "standby_DGMGRL" has 1 instance(s).
Instance "prime", status UNKNOWN, has 1 handler(s) for this
service...
The command completed successfully
[oracle@standby admin]$
On Tue, Jun 2, 2015 at 1:29 PM, Andrew Kerber
<andrew.kerber@xxxxxxxxx> wrote:
lsnrctl status output on standby side?
Sent from my iPad
On Jun 2, 2015, at 1:19 PM, max scalf <oracle.blog3@xxxxxxxxx>
wrote:
I changed the listener.ora file on standby as below...but still get
the error...
on standby...
[oracle@standby admin]$ cat listener.ora
LISTENER_STANDBY =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1545))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1545))
)
)
ADR_BASE_LISTENER_TESTDB = /ora_home
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_PRIME=ON #
line added by Agent
SID_LIST_LISTENER_STANDBY=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=prime)
(SID_NAME=prime)
(ORACLE_HOME=/ora_home/app/product/11.2/dbhome)
)
(SID_DESC=
(GLOBAL_DBNAME=standby_DGMGRL)
(SID_NAME=prime)
(ORACLE_HOME=/ora_home/app/product/11.2/dbhome)
)
)
[oracle@standby admin]$
on primary ...
[oracle@prime admin]$ dgmgrl /
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration;
Configuration - prime
Protection Mode: MaxPerformance
Databases:
prime - Primary database
standby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> switchover to standby;
Performing switchover NOW, please wait...
Operation requires a connection to instance "prime" on database
"standby"
Connecting to instance "prime"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service
requested in connect descriptor
Failed.
Warning: You are no longer connected to ORACLE.
connect to instance "prime" of database "standby"
DGMGRL>
On Tue, Jun 2, 2015 at 1:16 PM, Andrew Kerber
<andrew.kerber@xxxxxxxxx> wrote:
On the standby, the static dgmgrl entry should be
db_unique_name_dgmgrl. Which should be standby_dgmgrl it looks
like.
Sent from my iPad
On Jun 2, 2015, at 1:04 PM, max scalf <oracle.blog3@xxxxxxxxx>
wrote:
Hello all,
I am trying to do a switchover for my test setup for DG, it work
just fine if i do it with SQLPLUS but with data guard broker it
does not work.
primary host is called prime, secondary host is called standby
and DB_name is PRIME while DB_UNIQUE_NAME is PRIME and STANDBY
here is what i get when i do switchover with data guard broker.
I have also pasted my listener and tns file below for both prime
and standby. Would this be related to the parameter
"StaticConnectIdentifier" ??
[oracle@prime admin]$ dgmgrl /
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration;
Configuration - prime
Protection Mode: MaxPerformance
Databases:
prime - Primary database
standby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> swithover to standby;
Unrecognized command "swithover", try "help"
DGMGRL> switchover to standby;
Performing switchover NOW, please wait...
Operation requires a connection to instance "prime" on database
"standby"
Connecting to instance "prime"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service
requested in connect descriptor
Failed.
Warning: You are no longer connected to ORACLE.
connect to instance "prime" of database "standby"
DGMGRL>
DGMGRL> show database verbose prime;
Database - prime
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
prime
Properties:
DGConnectIdentifier = 'prime'
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 = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
SidName = 'prime'
StaticConnectIdentifier =
'(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prime)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=prime_DGMGRL)(INSTANCE_NAME=prime)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/data/oradata/prime/arch'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
SUCCESS
DGMGRL>
DGMGRL>
DGMGRL> show database verbose standby;
Database - standby
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: 285.00 KByte/s
Real Time Query: OFF
Instance(s):
prime
Properties:
DGConnectIdentifier = 'standby'
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 = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert =
'/data/oradata/prime/redoA/, /data/oradata/prime/redoA/,
/data/oradata/prime/redoB/, /data/oradata/prime/redoB/,
/data/oradata/prime/standbylog, /data/oradata/prime/standbylog'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
SidName = 'prime'
StaticConnectIdentifier =
'(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=standby)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=standby_DGMGRL)(INSTANCE_NAME=prime)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/data/oradata/prime/arch'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
SUCCESS
DGMGRL>
here is the TNS and listener file for primary
[oracle@prime ~]$ cd
/ora_home/app/product/11.2/dbhome/network/admin/
[oracle@prime admin]$ cat listener.ora
LISTENER_PRIME =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prime)(PORT = 1540))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1540))
)
)
ADR_BASE_LISTENER_TESTDB = /ora_home
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_PRIME=ON #
line added by Agent
SID_LIST_LISTENER_PRIME=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=prime)
(SID_NAME=prime)
(ORACLE_HOME=/ora_home/app/product/11.2/dbhome)
)
(SID_DESC=
(GLOBAL_DBNAME=prime_DGMGRL)
(SID_NAME=prime)
(ORACLE_HOME=/ora_home/app/product/11.2/dbhome)
)
)
[oracle@prime admin]$ cat tnsnames.ora
LISTENER_PRIME =
(ADDRESS = (PROTOCOL = TCP)(HOST = prime)(PORT = 1540))
PRIME =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prime)(PORT = 1540))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prime)
)
)
# Note service_name = prime, as the DB is still going to be
called prime
# it is only the db_unique_name that standby
STANDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1545))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prime)
)
)
[oracle@prime admin]$
Here is the listener and tns for standby server...
[oracle@standby ~]$ cd
/ora_home/app/product/11.2/dbhome/network/admin/
[oracle@standby admin]$ cat listener.ora
LISTENER_STANDBY =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1545))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1545))
)
)
ADR_BASE_LISTENER_TESTDB = /ora_home
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_PRIME=ON #
line added by Agent
SID_LIST_LISTENER_STANDBY=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=prime)
(SID_NAME=prime)
(ORACLE_HOME=/ora_home/app/product/11.2/dbhome)
)
(SID_DESC=
(GLOBAL_DBNAME=prime_DGMGRL)
(SID_NAME=prime)
(ORACLE_HOME=/ora_home/app/product/11.2/dbhome)
)
)
[oracle@standby admin]$
[oracle@standby admin]$ cat tnsnames.ora
LISTENER_STANDBY =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1545))
PRIME =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prime)(PORT = 1540))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prime)
)
)
# Note service_name = prime, as the DB is still going to be
called prime
# it is only the db_unique_name that standby
STANDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1545))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prime)
)
)
[oracle@standby admin]$
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
