Home » RDBMS Server » Networking and Gateways » SERVICE_NAME confusion (10g, sles 9)
SERVICE_NAME confusion [message #406687] Fri, 05 June 2009 06:43 Go to next message
dirkm
Messages: 86
Registered: November 2008
Location: Centurion - South Africa
Member
On one server I have 2 databases: oradev, and oratest.

I am using one listener, with both databases connecting on the same port (1521).

I restored oratest from production, and did not rename the database, so it is now called oraadmin (where it used to be called oratest).

I changed the TNS entry to specify the service_name oraadmin.mxgroup.co.za, for ORATEST.


In their client they connect specifying one of the 2 entries below, ORADEV, or ORATEST.
The problem now is, when our developers connect to the database (from their clients), using the ORATEST entry below, the somehow connect to ORADEV.


tnsnames.ora:
-------------

ORADEV =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = mxgendb)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = oradev.mxgroup.co.za)
)
)

ORATEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = mxgendb.caretech.co.za)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oraadmin.mxgroup.co.za)
)
)


After further investigation:
----------------------------

From the Net Services Admin Guide

The service name is specified by the SERVICE_NAMES parameter in the initialization parameter file.

So:
---

I found that both the service_names for oradev & oratest are the same: oraadmin.mxgroup.co.za


In the parameter file for Dev (initoradev.ora): *.service_names='oraadmin.mxgroup.co.za'

In the parameter file for Test (initoraadmin.ora): *.service_names='oraadmin.mxgroup.co.za'


I am having trouble understanding how the service_name resolves. Why are they connecting to the database oradev, and not the other database ? Or is it that simple - 2 databases cannot have the same service_name ?

If so, can I just restore a database, keep the name the same, but change the service_name to a unique name, and then start it up ?


Dirk

Re: SERVICE_NAME confusion [message #406778 is a reply to message #406687] Fri, 05 June 2009 16:03 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

I am totoaly confuesed your question.

Connect Sys user try to check your service name; If you want you can modify.

thanks
Re: SERVICE_NAME confusion [message #407038 is a reply to message #406687] Mon, 08 June 2009 04:19 Go to previous messageGo to next message
dirkm
Messages: 86
Registered: November 2008
Location: Centurion - South Africa
Member
Ok. I have 2 databases on the same host. When the users try and connect to the Test database (using in their tns ORATEST), then they connect to the Dev database instead (which is ORADEV).

Is it because they specify in their tns:

(SERVICE_NAME = oraadmin.mxgroup.co.za)


In ORADEV database:

SQL> show parameter service

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string oraadmin.mxgroup.co.za


In ORATEST database:

SQL> show parameter service

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string oraadmin.mxgroup.co.za
Re: SERVICE_NAME confusion [message #407342 is a reply to message #407038] Tue, 09 June 2009 16:33 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


Instead of service name specify; Try to set SID

Thanks
Re: SERVICE_NAME confusion [message #407371 is a reply to message #406687] Tue, 09 June 2009 23:44 Go to previous message
nqtrung
Messages: 25
Registered: April 2007
Junior Member
You must to use GLOBAL_DBNAME parameter when config listener:

SID_LIST_LISTENER1 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = oradev.mxgroup.co.za)
(ORACLE_HOME = /u01/app/oracle/product/10.2/db_1)
(SID_NAME = oradev)
)
)

SID_LIST_LISTENER2 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = oraadmin.mxgroup.co.za)
(ORACLE_HOME = /u01/app/oracle/product/10.2/db_1)
(SID_NAME = oratest)
)
)


LISTENER1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = mxgendb)(PORT = 1521))
)
)

LISTENER2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = mxgendb.caretech.co.za)(PORT = 1521))
)
)

Good lucks

[Updated on: Tue, 09 June 2009 23:45]

Report message to a moderator

Previous Topic: ORA-24756: Transaction does not exist
Next Topic: ORA-12541 TNS Does not currently know of service.....
Goto Forum:
  


Current Time: Thu Mar 28 16:34:27 CDT 2024