ORA-12541: TNS:no listener ( ERROR coming when one node of RAC database is off

[agrawaltanu1]

Hi
I've a RAC Database running on two nodes on WINDOWS 2000.
When One node(name- tgvirtualtwo) runs alone and other node(name-tgvirtualone) is off, it's working fine. However when the node(name-tgvirtualone) runs alone, and tgvirtualtwo is off, I'm unable to connect to database. Error showing ORA-12541: TNS:no listener .
I also checked oracle services.They are all running.

Here is my listener configurations:

TgVirtualTwo configurations
--------------------------
# listener.ora.tgvirtualtwo Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\listener.ora.tgvirtualtwo
# Generated by Oracle configuration tools.

LISTENER_TGVIRTUALTWO =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = tgvirtualtwo-vip)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.2)(PORT = 1521)(IP = FIRST))
)
)
TgvirtualOne configurations:
--------------------------
LISTENER_TGVIRTUALONE =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = tgvirtualone-vip)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.8)(PORT = 1521)(IP = FIRST))
)
)


I also tried changing host to 10.0.0.1( private ip of tgvirtualone) in place of 192.168.0.8 but to no avail.

Please help !

[Bore]

I Suspect that the listener you use runs only on one of the nodes, OR the client side tnsnames.ora points ONLY to one of your listeners. Hence when the node with the listener is down, there really is no listener.
Put the tnsnames.ora as well

[agrawaltanu1]

TNS.ora file for tgvirtualtwo:
--------------------------------
# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORCLRAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = tgvirtualtwo-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = tgvirtualone-vip)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclrac.tgvirtual)
)
)

ORCLRAC2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = tgvirtualone-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclrac.tgvirtual)
(INSTANCE_NAME = orclrac2)
)
)

ORCLRAC1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = tgvirtualtwo-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclrac.tgvirtual)
(INSTANCE_NAME = orclrac1)
)
)

LISTENERS_ORCLRAC =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = tgvirtualtwo-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = tgvirtualone-vip)(PORT = 1521))
)

TGVIRTUALONE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = tgvirtualtwo-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = tgvirtualone-vip)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = tgvirtualone.tgvirtual)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

TNS.ora file for tgvirtualone:
--------------------------------

# tnsnames.ora.tgvirtualone Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora.tgvirtualone
# Generated by Oracle configuration tools.

ORCLRAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = tgvirtualtwo-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = tgvirtualone-vip)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclrac.tgvirtual)
)
)

ORCLRAC2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = tgvirtualone-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclrac.tgvirtual)
(INSTANCE_NAME = orclrac2)
)
)

ORCLRAC1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = tgvirtualtwo-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclrac.tgvirtual)
(INSTANCE_NAME = orclrac1)
)
)

LISTENERS_ORCLRAC =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = tgvirtualtwo-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = tgvirtualone-vip)(PORT = 1521))
)

TGVIRTUALONE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = tgvirtualtwo-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = tgvirtualone-vip)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = tgvirtualone.tgvirtual)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

[davey23uk]

the clients tnsnames.ora - not the servers

[agrawaltanu1]

Here is my client's tnsnames.ora file.
-----------------------------------

# tnsnames.ora Network Configuration File: f:\oracle\product\10.2.0\client_2\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

tns_tg_db_primary =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.20)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = tgorcl2)
)
)

ORCLRAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = tgvirtualtwo-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = tgvirtualone-vip)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclrac.tgvirtual)
)
)

[agrawaltanu1]

I observed one more thing while trying to fix this.

I logged in to database(in the node which was not shut down), using command :
conn sys as sysdba.

Then it connected to idle instance.

Then I used the command startup, which then started the oracle instance. After that I could connet to database from outside as sysdba only. For other users, it's showing oracle initialization or shutdown in progress. That was a bit strange though.

So does that mean, Out of my two nodes, if one node is shutdown, it's not starting oracle instance by default ?

And if so, how to fix this.

[Bore]

See, the error says NO LISTENER!!!!! Maybe you have problems with the instance as well, but first go fix the listener problem and then dig forward

Regards
Boris

[agrawaltanu1]

But, I don't know how to fix the listener problem, since the listener file was generated using net configuration assistant. I didn't do any manual changes in them.

Please help.

[Bore]

As I already mentioned, that error means that the client is looking for a listener where the listener is not present.
That would mean
1) The client looks at wrong place (host/port)
2) The listener is not working

Please let us know
1) From which machine you try to log
2) Look at the tnsnames.ora and see if the address of this listener is present there, coresponding to your connect string
3) Look at the sqlnet.ora and see what the policy is (should be try all addresses until one succeeds or something)
The Oracle Net Assistant also has interface to these files.
The listener.ora file does not seems to have any problems

Regards
Boris

[agrawaltanu1]

I've already posted tnsnames.ora and listener.ora file of client and servers. As you can see from them, the listener name(as in the connect string) orclrac is there in the listener.ora files of server nodes.

As for from which computer i'm logging in, actually i'm using vmware for rac implementation. So i'm logging in from the host operating system.

Also, i amn't getting anything from sqlnet.ora file of the client.
It looks as below.
-------------
# sqlnet.ora Network Configuration File: f:\oracle\product\10.2.0\client_2\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES)


Nothing has been mentioned about the policy.