Home » Server Options » Data Guard » question about broker Switchover command
question about broker Switchover command [message #438058] Thu, 07 January 2010 00:21 Go to next message
dbauser123
Messages: 1
Registered: January 2010
Junior Member
Hi guys
i have one question about switchover cmd . For example, i have 1 primary database called stdby3 and 2 standby databases that are called stdby1 and stdby2. When i performed switchover cmd to stdby2 standby database, why the new primary and standby databases are closed (not mount) after the switchover process is completed


output screen:

DGMGRL> show configuration;

Configuration
Name: stdby1
Enabled: YES
Protection Mode: MaxProtection
Databases:
stdby3 - Primary database
stdby1 - Physical standby database
stdby2 - Physical standby database

Fast-Start Failover: DISABLED

Current status for "stdby1":
SUCCESS

DGMGRL> SWITCHOVER TO stdby2
Performing switchover NOW, please wait...
Operation requires shutdown of instance "stdby3" on database "stdby3"
Shutting down instance "stdby3"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "stdby2" on database "stdby2"
Shutting down instance "stdby2"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "stdby3" on database "stdby3"
Starting instance "stdby3"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.
You are no longer connected to ORACLE
Please connect again.
Unable to start instance "stdby3"
You must start instance "stdby3" manually
Operation requires startup of instance "stdby2" on database "stdby2"
You must start instance "stdby2" manually
Switchover succeeded, new primary is "stdby2"
DGMGRL> show configuration;
Error:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 130 Serial number: 45

DGMGRL> exit;
oracle@localhost ~$ dgmgrl
DGMGRL for Linux: Version 11.1.0.6.0 - Production

Copyright © 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys
Password:
Connected.
DGMGRL> show configuration;
Error:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

DGMGRL> connect sys@stdby3
Password:
Connected.
DGMGRL> show configuration;
Error:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

DGMGRL>

----------------------------------------------------------------
i still could not solve the same problem even i set the global database name
-----------------------------------------------------------------

the following are my two listeners files

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stdby2_DGMGRL)
(SERVICE_NAME = stdby2)
(ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
(SID_NAME = stdby2)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stdby3_DGMGRL)
(SERVICE_NAME = stdby3)
(ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
(SID_NAME = stdby3)
)
)
Re: question about broker Switchover command [message #438229 is a reply to message #438058] Thu, 07 January 2010 11:16 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

>>ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Quote:
12514, 00000, "TNS:listener could not resolve SERVICE_NAME given in connect descriptor"
// *Cause: The SERVICE_NAME in the CONNECT_DATA was not found in
// the listener's tables.
// *Action: Check to make sure that the SERVICE_NAME specified is correct.
// *Comment: This error will be returned if the (database) service has not been
// registered with the listener; a database instance that is part of this
// service may need to be started or configured properly.


- Babu
listener config for DGMGRL [message #439861 is a reply to message #438058] Tue, 19 January 2010 14:33 Go to previous messageGo to next message
vincel
Messages: 6
Registered: January 2010
Location: St Louis
Junior Member
I thought I had all configured right but I can't get DG broker
to bring up the databases:

DGMGRL> switchover to 'SND_P';
Performing switchover NOW, please wait...
Operation requires shutdown of instance "SND" on database "SND_S"
Shutting down instance "SND"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "SND" on database "SND_P"
Shutting down instance "SND"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "SND" on database "SND_S"
Starting instance "SND"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.
You are no longer connected to ORACLE
Please connect again.
Unable to start instance "SND"
You must start instance "SND" manually
Operation requires startup of instance "SND" on database "SND_P"
You must start instance "SND" manually
Switchover succeeded, new primary is "SND_P"

I thought I had the listner entires correct too:

LSNRCTL for HPUX: Version 10.2.0.4.0 - Production on 19-JAN-2010 14:26:47

Copyright (c) 1991, 2007, Oracle. All rights reserved.

Connecting to (ADDRESS=(COMMUNITY=SAP.WORLD)(PROTOCOL=TCP)(HOST=sapsnd)(PORT=1527))
Services Summary...
Service "SND_P" has 1 instance(s).
Instance "SND", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:2 refused:0 state:ready
LOCAL SERVER
Service "SND_P_DGB" has 1 instance(s).
Instance "SND", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:2 refused:0 state:ready
LOCAL SERVER
Service "SND_P_DGMGRL.WORLD" has 1 instance(s).
Instance "SND", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "SND_P_XPT" has 1 instance(s).
Instance "SND", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:2 refused:0 state:ready
LOCAL SERVER
The command completed successfully
----------------------------------------------------------------
sapsnd:orasnd 16> more listener.ora

LISTENER =
(ADDRESS_LIST =
(ADDRESS = (COMMUNITY = SAP.WORLD)(PROTOCOL = TCP)(HOST = sapsnd) (PORT = 1527)
)
)
STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
TRACE_LEVEL_LISTENER = OFF
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME=SND_P_DGMGRL.WORLD)
(SDU = 32768)
(SID_NAME = SND)
(SERVICE_NAME=SND_P.WORLD)
(ORACLE_HOME = /oracle/SND/102_64)
)
)

SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF
----------------------------------------------------------------
and

sapsnddg:orasnd 48> lsnrctl services

LSNRCTL for HPUX: Version 10.2.0.4.0 - Production on 19-JAN-2010 14:24:44

Copyright (c) 1991, 2007, Oracle. All rights reserved.

Connecting to (ADDRESS=(COMMUNITY=SAP.WORLD)(PROTOCOL=TCP)(HOST=sapsnddg.ssmhc.com)(PORT=1527))
Services Summary...
Service "SND_S" has 1 instance(s).
Instance "SND", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:2 refused:0 state:ready
LOCAL SERVER
Service "SND_S_DGB" has 1 instance(s).
Instance "SND", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:2 refused:0 state:ready
LOCAL SERVER
Service "SND_S_DGMGRL.WORLD" has 1 instance(s).
Instance "SND", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "SND_S_XPT" has 1 instance(s).
Instance "SND", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:2 refused:0 state:ready
LOCAL SERVER
The command completed successfully

----------------------------------------------------------------

What am I not matching?
icon12.gif  Re: listener config for DGMGRL [message #439863 is a reply to message #439861] Tue, 19 January 2010 14:45 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
You may need to configure the service name (instead of 'SID_NAME') also in the tnsnames.ora file.

These are your VALID (configured) services:
Service "SND_S" has 1 instance(s).
Service "SND_S_DGB" has 1 instance(s).
Service "SND_S_DGMGRL.WORLD" has 1 instance(s).
Service "SND_S_XPT" has 1 instance(s).

[Updated on: Wed, 20 January 2010 08:46] by Moderator

Report message to a moderator

Re: question about broker Switchover command [message #439865 is a reply to message #438058] Tue, 19 January 2010 15:01 Go to previous messageGo to next message
vincel
Messages: 6
Registered: January 2010
Location: St Louis
Junior Member
Configure the service name in the tnsnames.ora file?
Here is what is in the tnsnames.ora file now:

LISTENER_SND.WORLD =
(ADDRESS = (COMMUNITY = SAP.WORLD)(PROTOCOL = TCP)(HOST = sapsnd)(PORT = 1527))

SND.WORLD =
(DESCRIPTION =
(ADDRESS = (COMMUNITY = SAP.WORLD)(PROTOCOL = TCP)(HOST = sapsnd)(PORT = 1527))
(CONNECT_DATA = ( SID = SND ) ( GLOBAL_NAME = SND.WORLD )
) )

SSND.WORLD =
(DESCRIPTION =
(ADDRESS = (COMMUNITY = SAP.WORLD)(PROTOCOL = TCP)(HOST = sapsnddg.ssmhc.com)(PORT = 1527))
(CONNECT_DATA = (SID = SND) (GLOBAL_NAME = SSND.WORLD)
) )


And the listener.ora file:

LISTENER =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = SAP.WORLD)(PROTOCOL = TCP)(HOST = sapsnd)(PORT = 1527)
) )
STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
TRACE_LEVEL_LISTENER = OFF
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME=SND_P_DGMGRL.WORLD)
(SDU = 32768)
(SID_NAME = SND)
(ORACLE_HOME = /oracle/SND/102_64)
) )
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF


---------------------------------------------
I am still not sure what service(s) I am missing.
The SID is the same on both servers (SND) while the unique names are SND_P and SND_S.

Thanks for your help!
p.s. These are after a revision. I took out the SERVICE_NAME entry hoping that was the issue.

[Updated on: Tue, 19 January 2010 15:02]

Report message to a moderator

Re: question about broker Switchover command [message #439867 is a reply to message #439865] Tue, 19 January 2010 15:25 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
11g needs service name:

SND.WORLD =
(DESCRIPTION =
(ADDRESS = (COMMUNITY = SAP.WORLD)(PROTOCOL = TCP)(HOST = sapsnd)(PORT = 1527))
(CONNECT_DATA = (SERVICE_NAME = SND) ( GLOBAL_NAME = SND.WORLD )
) )

SSND.WORLD =
(DESCRIPTION =
(ADDRESS = (COMMUNITY = SAP.WORLD)(PROTOCOL = TCP)(HOST = sapsnddg.ssmhc.com)(PORT = 1527))
(CONNECT_DATA = (SERVICE_NAME = SSND) (GLOBAL_NAME = SSND.WORLD)
) )

Re: question about broker Switchover command [message #439872 is a reply to message #438058] Tue, 19 January 2010 15:54 Go to previous messageGo to next message
vincel
Messages: 6
Registered: January 2010
Location: St Louis
Junior Member
I removed SID and replaced it with SERVICE_NAME. Now I can't get into DGMGRL:

DGMGRL for HPUX: Version 10.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/*****@snd
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.
DGMGRL> connect sys/********@snd_p
Unable to connect to database
ORA-12154: TNS:could not resolve the connect identifier specified

-----------------------
New tnsnames.ora:
LISTENER_SND.WORLD =
(ADDRESS = (COMMUNITY = SAP.WORLD)(PROTOCOL = TCP)(HOST = sapsnd)(PORT = 1527))

SND.WORLD =
(DESCRIPTION =
(ADDRESS = (COMMUNITY = SAP.WORLD)(PROTOCOL = TCP)(HOST = sapsnd)(PORT = 1527))
(CONNECT_DATA = ( SERVICE_NAME = SND ) ( GLOBAL_NAME = SND.WORLD )
)
)

SSND.WORLD =
(DESCRIPTION =
(ADDRESS = (COMMUNITY = SAP.WORLD)(PROTOCOL = TCP)(HOST = sapsnddg.ssmhc.com)(PORT = 1527))
(CONNECT_DATA = (SERVICE_NAME = SSND) (GLOBAL_NAME = SSND.WORLD)
)
)

---------------------------------------------
Though I don't have a SERVICE of that name according to this output. Where did my SND connection go? Should it be in the LISTENER.ORA?

Service "SND_P" has 1 instance(s).
Service "SND_P_DGB" has 1 instance(s).
Service "SND_P_DGMGRL.WORLD" has 1 instance(s).
Service "SND_P_XPT" has 1 instance(s).


------------------------------------------------------
SND pings:
sapsnd:orasnd 23> tnsping snd
TNS Ping Utility for HPUX: Version 10.2.0.4.0 - Production on 19-JAN-2010 15:50:57
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
/oracle/SND/102_64/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (COMMUNITY = SAP.WORLD)(PROTOCOL = TCP)(HOST = sapsnd)(PORT = 1527)) (CONNECT_DATA =
( SERVICE_NAME = SND) ( GLOBAL_NAME = SND.WORLD)))
OK (10 msec)
Re: question about broker Switchover command [message #439874 is a reply to message #438058] Tue, 19 January 2010 15:58 Go to previous messageGo to next message
vincel
Messages: 6
Registered: January 2010
Location: St Louis
Junior Member
Still unhappy:

DGMGRL> switchover to 'SND_S'
Performing switchover NOW, please wait...
Operation requires shutdown of instance "SND" on database "SND_P"
Shutting down instance "SND"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "SND" on database "SND_S"
Shutting down instance "SND"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "SND" on database "SND_P"
Starting instance "SND"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.
You are no longer connected to ORACLE
Please connect again.
Unable to start instance "SND"
You must start instance "SND" manually
Operation requires startup of instance "SND" on database "SND_S"
You must start instance "SND" manually
Switchover succeeded, new primary is "SND_S"
icon5.gif  Re: question about broker Switchover command [message #439980 is a reply to message #439874] Wed, 20 January 2010 07:49 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
Does your sqlnet.ora file have the following?:

NAMES.DEFAULT_DOMAIN=world

[Updated on: Wed, 20 January 2010 08:45] by Moderator

Report message to a moderator

Re: question about broker Switchover command [message #439984 is a reply to message #438058] Wed, 20 January 2010 08:02 Go to previous message
vincel
Messages: 6
Registered: January 2010
Location: St Louis
Junior Member
Yes.

AUTOMATIC_IPC = ON
TRACE_LEVEL_CLIENT = OFF
SQLNET.EXPIRE_TIME = 0
NAMES.DEFAULT_DOMAIN = WORLD
NAME.DEFAULT_ZONE = WORLD

I also found this:
If you are using a nondefault port number (for example, 
something other than port 1521) for the local listener address, 
you must set the LOCAL_LISTENER initialization parameter on each 
instance that is part of a broker configuration to a listener 
address that is reachable by all members of the configuration. 

To enable DGMGRL to restart instances during the course
of broker operations, a service with a specific name must be
statically registered with the local listener of each instance. 
The value for the GLOBAL_DBNAME attribute must be set to a 
concatenation of db_unique_name_DGMGRL.db_domain. For example, 
in the LISTENER.ORA file:

LISTENER = (DESCRIPTION = 
     (ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=host_name)
     (PORT=port_num))))
SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=sid_name)
     (GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain)
     (ORACLE_HOME=oracle_home)))


Which I have done since I use port 1527.
Previous Topic: Redo Transport Error
Next Topic: Archive logs - Corrupted
Goto Forum:
  


Current Time: Thu Mar 28 09:29:59 CDT 2024