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.

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
Received on Tue Jun 02 2015 - 20:49:37 CEST

Original text of this message