Re: switchover with DGMGRL
From: Andrew Kerber <andrew.kerber_at_gmail.com>
Date: Tue, 2 Jun 2015 13:49:37 -0500
Message-Id: <C4C0CF0D-F575-4334-A961-A0C1664D097A_at_gmail.com>
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.
>>> 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]$
>>>
>>>
Date: Tue, 2 Jun 2015 13:49:37 -0500
Message-Id: <C4C0CF0D-F575-4334-A961-A0C1664D097A_at_gmail.com>
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-lReceived on Tue Jun 02 2015 - 20:49:37 CEST