| SERVICE_NAME confusion [message #406687] |
Fri, 05 June 2009 06:43  |
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 #407038 is a reply to message #406687] |
Mon, 08 June 2009 04:19  |
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 #407371 is a reply to message #406687] |
Tue, 09 June 2009 23:44 |
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
