I have a 8.0.3 and 9.2 on a windows2000 server. I have two listeners, listener for 8.0.3 on port 1526 and listener for 9.2 on port 1521 configured and started properly and tnsnames with sid and oracle_home configured properly.
The problem I am having is if I start 9.2 on sqlplus803 executable (in c:\orant\bin) I get the above error. Same thing with 9.2. If I start 803 with sqlplus 92 executable (in D:\ora92) I get ora-12500?
Any one had this error before?
GURUZ,
Can you answer this one? I have no problem in starting either one. I just want to use one listener/tnsnames.ora and then I am having problems.
Post ur Listeners & TNSNames.
Here we go: I am posting these to someone who WISHES to be a DBA: God help us all>>
ORA8.0.3:LISTENER
################
# Filename......: listener.ora
# Node..........: local.world
# Date..........: 24-MAY-94 13:23:20
################
LISTENER =
(ADDRESS_LIST =
(ADDRESS=
(PROTOCOL= TCP)
(Host= 172.18.50.37)
(Port= 1526)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = DCC1)
(ORACLE_HOME = C:\ORANT)
)
(SID_DESC =
(SID_NAME = TEST92)
(ORACLE_HOME = D:\ORA92)
)
)
ORA8.0.3 TNSNAMES.ORA
DCC1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.50.37)(PORT = 1526))
)
(CONNECT_DATA = (SID = DCC1)
)
)
TEST92 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.50.37)(PORT = 1526))
)
(CONNECT_DATA = (SID = TEST92)
)
)
ORA92 LISTENER
# LISTENER.ORA Network Configuration File: D:\ora92\network\admin\listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.50.37)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = TEST92)
(ORACLE_HOME = D:\ora92)
)
(SID_DESC =
(SID_NAME = DCC1)
(ORACLE_HOME = C:\ORANT)
)
)
ORA92 TNSNAMES.ORA
# TNSNAMES.ORA Network Configuration File: D:\ora92\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
DCC1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.50.37)(PORT = 1521))
)
(CONNECT_DATA =
(SID = DCC1)
)
)
TEST92 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.50.37)(PORT = 1521))
)
(CONNECT_DATA =
(SID = TEST92)
(SERVICE_NAME = TEST92)
)
)
I have made the changes, try this
ORA8.0.3:LISTENER
################
# Filename......: listener.ora
# Node..........: local.world
# Date..........: 24-MAY-94 13:23:20
################
LISTENER1 =
(ADDRESS_LIST =
(ADDRESS=
(PROTOCOL= TCP)(Host= 172.18.50.37)(Port= 1526)
)
)
SID_LIST_LISTENER1 =
(SID_LIST =
(SID_DESC =
(SID_NAME = DCC1)(ORACLE_HOME = C:\ORANT)
)
)
ORA8.0.3 TNSNAMES.ORA
DCC1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.50.37)(PORT = 1526))
)
(CONNECT_DATA = (SID = DCC1)
)
)
TEST92 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.50.37)(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME = TEST92)
)
)
ORA92 LISTENER
# LISTENER.ORA Network Configuration File: D:\ora92\network\admin\listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.50.37)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = TEST92)(ORACLE_HOME = D:\ora92)
)
)
ORA92 TNSNAMES.ORA
# TNSNAMES.ORA Network Configuration File: D:\ora92\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
DCC1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.50.37)(PORT = 1526))
)
(CONNECT_DATA =
(SID = DCC1)
)
)
TEST92 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.50.37)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TEST92)
)
)
adewri,
Thanks for the reply. I made the changes. I could connect to both databases from ora92 sqlplus executable but from the 803 side I still get the error:
SQL> connect sys/pwd@test92 as sysdba;
ERROR:
ORA-12154: TNS:could not resolve service name
Also i) I want to use only one listener not two.
ii) I want to use only one sqlplus executable. I am perplexed why which sqlplus executable one uses makes a difference in the login?
change
ORA8.0.3 TNSNAMES.ORA
TEST92 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.50.37)(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME = TEST92)
)
)
to
TEST92 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.50.37)(PORT = 1521))
)
(CONNECT_DATA = (SID = TEST92)
)
)
if you want to use only one listener then
go to the registry
HKEY_LOCAL_MACHINE>SOFTWARE>ORACLE
create a new string value
TNS_ADMIN and insert $ORACLE_HOME/network/admin as value
where $ORACLE_HOME is 9i home
and stop the 8.0.3 listener...
use 9i sqlplus as it incorporates all 8i sqlplus features and more...
Thanks for your help adewri
Everything worked till I changed it to one listener. I configured my ora92 listerer like this so that I could eliminate 803 listener. Now I am getting ora-12500 again if I connect to 803 from 92sqlplus.
# LISTENER.ORA Network Configuration File: D:\ora92\network\admin\listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.50.37)(PORT = 1521)
)
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(Host = 172.18.50.37)(Port = 1526)
)
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = TEST92)
(ORACLE_HOME = D:\ora92)
)
(SID_DESC =
(SID_NAME = DCC1)
(ORACLE_HOME = C:\ORANT)
)
)
# TNSNAMES.ORA Network Configuration File: D:\ora92\network\admin\tnsnames.ora
DCC1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.50.37)(PORT = 1526)
)
)
(CONNECT_DATA = (SID = DCC1)
)
)
TEST92 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.50.37)(PORT = 1521)
)
)
(CONNECT_DATA = (SERVICE_NAME = TEST92)
)
)
I did put TNS_ADMIN to ora92 tnsnames.ora
--Change the listener to
# LISTENER.ORA Network Configuration File: D:\ora92\network\admin\listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.50.37)(PORT = 1521)
)
)
)
)
---- no need to enter the entry for 803 if you are using the same listener name and port.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = TEST92)
(ORACLE_HOME = D:\ora92)
)
(SID_DESC =
(SID_NAME = DCC1)
(ORACLE_HOME = C:\ORANT)
)
)
-- and change tns to
# TNSNAMES.ORA Network Configuration File: D:\ora92\network\admin\tnsnames.ora
DCC1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.50.37)(PORT = 1521)
)
)
(CONNECT_DATA = (SID = DCC1)
)
)
TEST92 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.50.37)(PORT = 1521)
)
)
(CONNECT_DATA = (SERVICE_NAME = TEST92)
)
)
I did put TNS_ADMIN to ora92 tnsnames.ora
--- point this to D:\ora92\network\admin (directory) not the tnsnames.ora file
I swear to god that I did exactly as told. I cut and pasted the listerner.ora and tnsnames.ora and TNS_ADMIN for one lisetner. Still I get ORA-12500 when I tried to connect 803 from 92. I solved the problem by going back to two listeners.
Just wondering - is this a 803 bug? or win2000 config issue?
Thanks for all your help.
enter /3GB in C:\Boot.ini of your m/c like this
multi(0)disk(0)rdisk(0)partition(1)\WINNTSVR="Microsoft Windows 2000 Advanced Server" /3GB /fastdetect
Windows 2000 Advanced Server allows to increase the memory available to the process from 2GB to 3GB. This is referred to as 4GT, or 4-gigabyte tuning, because it changes the distribution of memory within the 4GB process address space, giving only 1GB to the OS, and leaving the rest for the process. The feat can be accomplished by adding the switch, /3GB, to the boot.ini file.
Mohit Mahajan
DBA.
I was just browsing thro' the pages and saw this problem I dont even know since what time u have been posting the stuff but I faced the same problem some time back and following thing worked for me ....
enter /3GB in C:\Boot.ini of your m/c like this and reboot.
multi(0)disk(0)rdisk(0)partition(1)\WINNTSVR="Microsoft Windows 2000 Advanced Server" /3GB /fastdetect
Windows 2000 Advanced Server allows to increase the memory available to the process from 2GB to 3GB. This is referred to as 4GT, or 4-gigabyte tuning, because it changes the distribution of memory within the 4GB process address space, giving only 1GB to the OS, and leaving the rest for the process. The feat can be accomplished by adding the switch, /3GB, to the boot.ini file.
Hope it'll work.
Mohit Mahajan
DBA.
Quote:
Originally posted by newbie5
I swear to god that I did exactly as told. I cut and pasted the listerner.ora and tnsnames.ora and TNS_ADMIN for one lisetner.
Where did u add TNS_ADMIN? I mean where in registry did u add?
Did u add to Oracle_Home of 9i as adewri said???
If u did and using listener of 9i then u shudnt get the error...
In ur listener & TNS names give SID instead if Service_Name to see if it works......
Abhay.
The correct place to put TNS_ADMIN in registry is:
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
and I have put it there and verified from lsnrctl that the listener.ora file is being pulled from the tns_admin directory.
Is this ur oracle 9i Home?Quote:
Originally posted by newbie5
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
Abhay.
Mohit,
I thought the change you indicated is only necessary if the memory is more than 2GB. I have only 1G memory on my win2k server.
Are you sure about this?