| setup connect-time failover [message #446147] |
Fri, 05 March 2010 13:39  |
jxh461
Messages: 185
Registered: March 2005
|
Senior Member |
|
|
Please help me understand how connet-time failover is setup.
I am following lab instruction from my Oracle Admin Workshop book.
The exercise is to create a 2nd listener to support connect-time failover. I have two listeners up and running. I connected the second listener using Static Database Registration in OEM, and then I started it.
When I looked in the listener.ora file, I was expecting to find the entries for FAILOVER=TRUE but it is not there.
Both listeners are designed to connect to the same instance 'orcl'.
My questions:
1) Is the FAILOVER entry supposed to be done automatically when the
sendcond listener is created ? or is it always a manual entry ?
2) How can I test that it is working ?
Thanks in advance for your explaination and please be gentle and polite.
|
|
|
|
| Re: setup connect-time failover [message #446167 is a reply to message #446147] |
Fri, 05 March 2010 15:08  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Connect time failover is implemented on the client side, so you should be looking in your tnsnames.ora file, not the listener.ora file. Furthermore, when you check it in the Net Services Reference, note the default value for FAILOVER.
The test is easy: see whether you can get a connection with one listener running, the other listener running, both running, or neither running.
|
|
|
|
| Re: setup connect-time failover [message #446173 is a reply to message #446167] |
Fri, 05 March 2010 16:32 |
jxh461
Messages: 185
Registered: March 2005
|
Senior Member |
|
|
contents of tnsnames.ora in /u01/app/oracle/product/10.2.0/db_1/network/admin
no entry for failover:
********************************************************
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = jhlinux.class.com)(PORT = 1522))
LISTNER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = jhlinux.class.com)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC2))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
****************************************************
The is no entry for FAILOVER in NetProperties file. Is this where you meant when you said Net Services References ?
Contents of listener.ora has the following:
******************************************************
LOGGING_LISTENER2 = OFF
LISTENER2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = jhlinux.class.com)(PORT = 1561))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
)
SID_LIST_LISTENER2 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(SID_NAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
)
)
TRACE_LEVEL_LISTENER2 = OFF
ORCL =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC2))
(ADDRESS = (PROTOCOL = TCP)(HOST = jhlinux.class.com)(PORT = 1522))
)
)
***********************************************************
I am able to login when either one of the listeners is stopped,
when both are stopped (???), and when both are started.
I guess it is a good thing that I can connect to my server regardless. The other thing that is not clear to me is when you said look on the client side. I think that I am doing everything on the server side as I am not logging in from a remote machine. I am doing everything on the same machine where I have Oracle database installed. Is that defeating my purpose ?
Thanks for taking time to respond, I hate moving to the next chapter without clear understanding.
|
|
|
|
| Re: setup connect-time failover [message #446190 is a reply to message #446173] |
Sat, 06 March 2010 02:12 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
The Net Services Reference is the volume of the Oracle documentation set that describes every entry that you can put into your listener.ora file (which is on the server side), the tnsnames.ora file (which is the client side local naming file) and your sqlnet.ora file (which is your profile file, and can exist on both sides).
You have no entry in your tnsnames file that will enable failover. I do not believe that you are connecting through a listener at all - I think you are using an IPC connection. You had better post a copy/paste of what you are doing.
What documentation are you following?
|
|
|
|
| Re: setup connect-time failover [message #446501 is a reply to message #446190] |
Mon, 08 March 2010 22:53 |
jxh461
Messages: 185
Registered: March 2005
|
Senior Member |
|
|
Here are the status of my listeners. The both seem to be using tcp connection:
[oracle@jhlinux ~]$ lsnrctl status listener
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 08-MAR-2010 23:46:46
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias ORCL
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 05-MAR-2010 17:31:05
Uptime 3 days 6 hr. 15 min. 40 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jhlinux.class.com)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@jhlinux ~]$ lsnrctl status listener2
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 08-MAR-2010 23:41:13
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=jhlinux.class.com)(PORT=1561)))
STATUS of the LISTENER
------------------------
Alias LISTENER2
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 05-MAR-2010 17:27:11
Uptime 3 days 6 hr. 14 min. 2 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jhlinux.class.com)(PORT=1561)))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
--------------------------------------------------
However I am not sure what to make of the following entry in tnsnames.ora
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC2))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
At some point before I attempted to test connect-time failover
the above (PLSExtProc) was displaying in the status of LISTENER.
Now it is showing (orcl). I think what happened is that I deleted the orcl listener in OEM instead of disabling and when I restored it I used tcp/ip.
Also while one of my listeners is listed as ORCL in OEM it is recognizing the alias set in tnsnames.ora of LISTENER.
I am making a effort to understand this concept so that later I will know what I am doing when I try to access my database from another machine connected to the same router. Thanks for all the insight you are giving me.
|
|
|
|
| Re: setup connect-time failover [message #446520 is a reply to message #446501] |
Tue, 09 March 2010 00:53 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
As I said before, I do not believe that you are connecting through a listener at all. What is the connect string you are using? Here are two examples, the first does not use a listener and will therefore work when the listener(s) are done, the second attempts to use a listener and fails:
orcl>
orcl> conn scott/tiger
Connected.
orcl> conn scott/tiger@orcl
ERROR:
ORA-12541: TNS:no listener
Warning: You are no longer connected to ORACLE.
>
What documentation are you following?
(ps - just you wait for 11.2, three scan listeners and any number of node listeners makes it a bit more complicated)
|
|
|
|
| Re: setup connect-time failover [message #447240 is a reply to message #446520] |
Fri, 12 March 2010 10:53 |
jxh461
Messages: 185
Registered: March 2005
|
Senior Member |
|
|
Thanks for your patience and continuous help.
I think the problem here is me not knowing what I am doing.
I have not setup a client whatsoever so I am not using the listener to connect to the server. I am doing everything from the linux command line and I am connecting as follows:
[oracle@jhlinux[/email][/email] ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 12 11:08:27 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> connect sys / as sysdba
Enter password:
Connected.
SQL>
also:
[oracle@jhlinux[/email][/email] ~]$ sqlplus scott/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 12 11:09:56 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
-------------------------------------------------------
However, things just got complicated (for me) as I shut down my Linux server with out shutting down my database. Now, I am not able to bring up OEM and I beleive the listener has to be up for that to work.
This is what I am doing:
1) I start my Linux OS
2) To start OEM,At the command line I do
emctl start dbconsole
3) To verify that OEM is ready I do
[oracle@jhlinux[/email][/email] admin]$ emctl status dbconsole
TZ set to US/Eastern
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://jhlinux.class.com:1158/em/console/aboutApplication
Oracle Enterprise Manager 10g is running.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/10.2.0/db_1/jhlinux.class.com_orcl/sysman/log
4) To check that no listener is up at this time
[oracle@jhlinux[/email][/email] admin]$ lsnrctl status listener
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 12-MAR-2010 11:30:58
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
[oracle@jhlinux[/email][/email] admin]$ lsnrctl status listener2
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 12-MAR-2010 11:32:42
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=jhlinux.class.com)(PORT=1561)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
5) To start the listener
LSNRCTL> start listener
Starting /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jhlinux.class.com)(PORT=1521)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias listener
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 12-MAR-2010 11:39:57
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jhlinux.class.com)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
6) Now I go to the OEM url to bring up OEM and I get the following messages.
DATABASE INSTANCE:
Host jhlinux.class.com
Port 1522
SID orcl
Oracle Home /u01/app/oracle/product/10.2.0/db_1
LISTENER
Status Down
Host jhlinux.class.com
Port 1522
Name LISTENER
Oracle Home /u01/app/oracle/product/10.2.0/db_1
Location /u01/app/oracle/product/10.2.0/db_1/network/admin
Details TNS-12541: TNS:no listener
AGENT CONNECTION TO INSTANCE
Status Failed
Details ORA-12541: TNS:no listener (
DBD ERROR: CIServerAttach)
To answer your question, I used Installation Guide 10g Release 2(10.2) for Linux x86-64 B15667-03 to do my installation
Also I am concerned that the port referenced in OEM (1522) is
not the same as the one on the listener (1521). Why ???
Current listener.ora file
-----------------------------------------------------
LOGGING_LISTENER2 = OFF
LISTENER2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = jhlinux.class.com)(PORT = 1561))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
)
TRACE_LEVEL_ORCL = OFF
SID_LIST_LISTENER2 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(SID_NAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
)
)
TRACE_LEVEL_LISTENER2 = OFF
ORCL =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = jhlinux.class.com)(PORT = 1521))
)
)
)
---------------------------------------------------------
current tnsnames.ora file
----------------------------------------------------------
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = jhlinux.class.com)(PORT = 1522))
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = jhlinux.class.com)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC2))
)
(CONNECT_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC2))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
---------------------------------------------------------
[Updated on: Fri, 12 March 2010 11:09] Report message to a moderator |
|
|
|
| Re: setup connect-time failover [message #447282 is a reply to message #447240] |
Fri, 12 March 2010 21:51 |
jxh461
Messages: 185
Registered: March 2005
|
Senior Member |
|
|
I have been able to restore my OEM to where it recognizes the default listener. I did the following:
emca -config dbcontrol db -repos recreate
Thank you very much for helping me look into my problem. I will try to test connect-time failover after I establish connection from a remote client.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
