Re: switchover with DGMGRL

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_at_gmail.com> 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_at_gmail.com> wrote:
>
> Same thing...
>
> [oracle_at_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_at_gmail.com>
> 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_at_gmail.com> wrote:
>>
>> Sorry, disregard the last status...here is the new one...i had to bounce
>> the listener....but still the same issue...
>>
>> [oracle_at_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_at_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_at_standby admin]$
>>
>>
>> On Tue, Jun 2, 2015 at 1:29 PM, Andrew Kerber <andrew.kerber_at_gmail.com>
>> wrote:
>>
>>> lsnrctl status output on standby side?
>>>
>>> Sent from my iPad
>>>
>>> On Jun 2, 2015, at 1:19 PM, max scalf <oracle.blog3_at_gmail.com> wrote:
>>>
>>> I changed the listener.ora file on standby as below...but still get the
>>> error...
>>>
>>> on standby...
>>> [oracle_at_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_at_standby admin]$
>>>
>>>
>>>
>>> on primary ...
>>> [oracle_at_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_at_gmail.com>
>>> 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_at_gmail.com> 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_at_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_at_prime ~]$ cd /ora_home/app/product/11.2/dbhome/network/admin/
>>>> [oracle_at_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_at_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_at_prime admin]$
>>>>
>>>>
>>>>
>>>>
>>>> Here is the listener and tns for standby server...
>>>>
>>>> [oracle_at_standby ~]$ cd /ora_home/app/product/11.2/dbhome/network/admin/
>>>> [oracle_at_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_at_standby admin]$
>>>> [oracle_at_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_at_standby admin]$
>>>>
>>>>
>>>
>>
>

--
http://www.freelists.org/webpage/oracle-l