Re: switchover with DGMGRL

From: Seth Miller <sethmiller.sm_at_gmail.com>
Date: Tue, 2 Jun 2015 14:46:41 -0500
Message-ID: <CAEueRAVzRevjCYSsU_F7pd24R=FhCbQgKFAY5BLdzU7g+y54fw_at_mail.gmail.com>

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_at_gmail.com> 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_at_gmail.com>
> 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_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

Received on Tue Jun 02 2015 - 21:46:41 CEST

This message: [ Message body ]
Next message: Andrew Kerber: "Re: switchover with DGMGRL"
Previous message: Seth Miller: "Re: RMAN Confusion"
Maybe in reply to: max scalf: "switchover with DGMGRL"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message