Max,
Yes, I am referring to the static connect identifier.
edit database standby set property StaticConnectIdentifier =
'(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=standby)*(PORT=1545*
))(CONNECT_DATA=(SERVICE_NAME=standby_DGMGRL)(INSTANCE_NAME=prime)(SERVER=DEDICATED)))';
Seth Miller
On Tue, Jun 2, 2015 at 2:39 PM, max scalf <oracle.blog3@xxxxxxxxx> wrote:
Hi Seth,
are you talking about StaticConnectIdentifier? if so how do i edit and
point that to my service name?
On Tue, 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]$
