Multiple Instances .. Listener Problem

Hi All,

Ours is Oracle 8.1.6 on Red Hat Linux 7.3. We have installed two instances on the Same Server. We Create only one Listener for the Two instances at the server. The problem is when we are trying to connect from clients - The server is misbehaving like sometimes its not allowing us to get connect - Some times it making confusion with the passwords, tablespaces and schemas.

For Example : The First Instance name is INSA and its Sytem password is Manager1 and Second Instance Name is INSB and Its system password is Manager2.

At our Client we configured LOCAL NAMES and the server is in dedicated mode. At the Clients TNSNAMES.ORA FILE we configured the two instances as INSA and INSB.

Then when we are connecting from client the SYSTEM/MANAGER1@INSA in showing the schema of SYSTEM's INSB and some times it is not allowing us to connect. But the same is working fine at the server. Then I thought it might be problem with listner and restarted the listener. then its working fine for some time and again giving the same problem.

Can you please help me in identifing where the problem is and solving this.

no error codes, no help

Its sometimes ora-12154 -TNS: could resolve tnsnames. I checked with tnsping its working fine.
Sometimes its connecting to another instance with this password

well, seems to me is not a Oracle technical problem but rather your configuration problem

When The Listener Status is showing like the below :

Services Summary...
PLSExtProc has 1 service handler(s)
insA has 1 service handler(s)
insA has 1 service handler(s)
insB has 1 service handler(s)
insB has 1 service handler(s)
The command completed successfully

Its working like the below from the client


SQL> connect system/manager1@insA
Connected.
SQL> connect system/manager2@insB
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> connect system/manager1@insB
Connected
- But connecting to insA
or saying this also invalid password

If the Listener is showing only one service for each instance then its working fine

I am also expecting the same. But What That configuration I made wrong ???

Paste here the tnsnames.ora contents from the client where you are getting this error.

INSA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = IPADDRESS)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = insA)
)
)

INSB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = IPADDRESS)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = INSB)
)
)

I added this by using netassist Wizard and I also tried the same by adding SRVR= DEDICATED in CONNECT_DATA

What is IPADDRESS ?? is this host name or you didnot put any ipaddress over there

can u check up both databases started with their own parameter files.Try once to startup with their own parameter files.Iam also getting doubt that your password file is corrupted due to some reason as it is not accepting the INSB system password.As lot of things are mixing up in your problem.Can u check these and what is the result.

Yes I tried this I have started two instances with their corresponding pfiles. They are working fine at the server because I queried some dynamic view to check the datafiles and tablespace from server. Its working fine at the server the problem is same from the clients ... from all clients . I think When we are connecting from the server its bypassing the listener where as from client the listerner might giving problem ... SO ?????

Hey,

Will u please try with Local_listener=TRUE in your parameter file and change the default port to 2048?

Thanks

Quote:

---

Originally posted by akhadar
can u check up both databases started with their own parameter files.Try once to startup with their own parameter files.Iam also getting doubt that your password file is corrupted due to some reason as it is not accepting the INSB system password.As lot of things are mixing up in your problem.Can u check these and what is the result.

---

err, I dont understand why password file is involved here, it is only used when you connect remotely AS SYSDBA or SYSOPER but I dont see anyone here is using those two options when connecting?

Quote:

---

Originally posted by newcomer
Hey,

Will u please try with Local_listener=TRUE in your parameter file and change the default port to 2048?

Thanks

---

Never heard of LOCAL_LISTENER=true, where did you find that ??

Its LOCAL_LISTENER=listener_alias and is used when you want PMON to register with a local listener that does not use TCP/IP port 1521...

and this is a part of dynamic service registration configuration.

Your solution is wrong and way out of context...

What is the result of tnsping for INSA, INSB?

Can you do the above on the box from which you pasted the posted tnsnames.ora and post it here?

The TNSPING for both the instances from the client

TNS Ping Utility for 32-bit Windows: Version 8.1.6.0.0 - Production on 23-APR-2003 14:18:32

(c) Copyright 1997 Oracle Corporation. All rights reserved.

Attempting to contact (ADDRESS=(PROTOCOL=TCP)(HOST=IPADDRESS)(PORT=1521))
OK (10 msec)

I am replacing the ip with ipaddress

I could not understand about LOCAL_LISTENER

Quote:

---

Originally posted by Sekhar
I think When we are connecting from the server its bypassing the listener where as from client the listerner might giving problem ... SO ?????

---

Post Listener.ora..

The following is my listener.ora

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Myhost)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /home/ora8i/m01/app/oracle/product/8.1.6)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = INSA)
(ORACLE_HOME = /home/ora8i/m01/app/oracle/product/8.1.6)
(SID_NAME = INSA)
)
(SID_DESC =
(GLOBAL_DBNAME = INSB)
(ORACLE_HOME = /home/ora8i/m01/app/oracle/product/8.1.6)
(SID_NAME = INSB)
)
)

I think ur listener is correct...but now i doubt if u have started the DB ( corresponding ) with correct instances.

please check below code

Code:

---


-- Please note following things.
-- I have 2 instances TEST and OEMREP.
-- I always start my DB OEMREP with instance name OEMREP but now i change
-- i started DB with instance name as TEST instead of OEMREP ( Edited PFile to use the instance as TEST )
-- my TNS Names would have configured to connect to TEST DB for TEST instance name

SQL> select * from global_name;

GLOBAL_NAME
------------------------------------------------------
OEMREP.DIGITALINDIASW.NET

SQL> select name from v$database;

NAME
---------
OEMREP

SQL> conn scott@oemrep.digitalindiasw.net
Enter password:
ERROR:
ORA-01017: invalid username/password; logon denied


SQL> conn scott@test.digitalindiasw.net
Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

---

BTW if by chance, U are using SPFILE and u edited Pfile & think that defualtly ( On startup ) oracle would use this pfile...the answer would be no...it wud rather use SPFILE.

SO if u hav made ne chages then while startup give path of pfile.

Well if u have done that correctly....

try changing ur TNS names file to use SID instead of SERVICE_NAME in the connect to data string.

Abhay.

Both The instances are up. and it is connecting properly from the server but not from the client.

Now I tried by changing service with SID .. Still the problem is same.

Do the following on the server...

1. login into insa database

select * from v$database;

select * from v$instance;

2. login into insb database

select * from v$database;

select * from v$instance;

Paste the result of the above here.

HTHU.

[oracle@ciberdb oracle]$ env | grep ORACLE_SID
ORACLE_SID=insa
[oracle@ciberdb oracle]$ sqlplus system/ora987

SQL*Plus: Release 8.1.6.0.0 - Production on Thu Apr 24 03:43:42 2003

(c) Copyright 1999 Oracle Corporation. All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.6.1.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production

SQL> select * from v$database;

DBID NAME CREATED RESETLOGS_CHANGE# RESETLOGS
---------- --------- --------- ----------------- ---------
PRIOR_RESETLOGS_CHANGE# PRIOR_RES LOG_MODE CHECKPOINT_CHANGE#
----------------------- --------- ------------ ------------------
ARCHIVE_CHANGE# CONTROL CONTROLFI CONTROLFILE_SEQUENCE# CONTROLFILE_CHANGE#
--------------- ------- --------- --------------------- -------------------
CONTROLFI OPEN_RESETL VERSION_T OPEN_MODE
--------- ----------- --------- ----------
2935114683 INSA 17-APR-03 1 17-APR-03
0 NOARCHIVELOG 344217
343372 CURRENT 17-APR-03 29883 344217
23-APR-03 NOT ALLOWED 17-APR-03 READ WRITE


SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_
----------------- --------- ------- --- ---------- ------- -----------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE
---------- --- ----------------- ------------------
1 insa
mydb
8.1.6.1.0 23-APR-03 OPEN NO 1 STOPPED
ALLOWED NO ACTIVE PRIMARY_INSTANCE

[oracle@ciberdb oracle]$ ORACLE_SID=insb;export ORACLE_SID
[oracle@ciberdb oracle]$ env | grep ORACLE_SID
ORACLE_SID=insb
[oracle@ciberdb oracle]$ sqlplus system/manager

SQL*Plus: Release 8.1.6.0.0 - Production on Thu Apr 24 03:45:58 2003

(c) Copyright 1999 Oracle Corporation. All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.6.1.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production

SQL> select * from v$database;

DBID NAME CREATED RESETLOGS_CHANGE# RESETLOGS
---------- --------- --------- ----------------- ---------
PRIOR_RESETLOGS_CHANGE# PRIOR_RES LOG_MODE CHECKPOINT_CHANGE#

----------------------- --------- ------------ ------------------
ARCHIVE_CHANGE# CONTROL CONTROLFI CONTROLFILE_SEQUENCE# CONTROLFILE_CHANGE#
--------------- ------- --------- --------------------- -------------------
CONTROLFI OPEN_RESETL VERSION_T OPEN_MODE
--------- ----------- --------- ----------
1910859409 INSB 21-APR-03 1 21-APR-03
0 NOARCHIVELOG 191431
191086 CURRENT 21-APR-03 7012 191613
22-APR-03 NOT ALLOWED 21-APR-03 READ WRITE


SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_
----------------- --------- ------- --- ---------- ------- -----------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE
---------- --- ----------------- ------------------
1 insb
mydb
8.1.6.1.0 22-APR-03 OPEN NO 1 STOPPED
ALLOWED NO ACTIVE PRIMARY_INSTANCE

Ya, that looks OK.

Well, one more thing, create a tnsnames.ora on the server and connect to both databases using the tnsnames.ora on the server.

sqlplus system/ora987@insa

sqlplus system/manager@insb

If you succeed, copy this tnsnames.ora to the client and check from there.

HTH.

i tried by creating the tnsnames.ora at server - Its not working properly - Giving the problem.

Hello,
Did you tried by restarting the Listener? You can reload the Listener.
Give listener stats and check whether there are any service handlers for your databases.

Thanks,

Quote:

---

Originally posted by Sekhar
i tried by creating the tnsnames.ora at server - Its not working properly - Giving the problem.

---

You mean, connecting to the databases at the server using tnsnames.ora is not working?

Post the errors you are getting at the server when you use tnsnames.ora. What problem are you getting?

HTHU

[oracle@ciberdb admin]$ sqlplus system/passwordA@INSA

SQL*Plus: Release 8.1.6.0.0 - Production on Thu Apr 24 08:26:39 2003

(c) Copyright 1999 Oracle Corporation. All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


But the Same is connecting if I am giving

sqlplus system/passwordB@INSB - But Connecting to instance B.

BTB, I tried all the options with listener ( restarting, reloading)

The listener status is

INSA has 1 service handler(s)
INSA has 1 service handler(s)
INSB has 1 service handler(s)
INSB has 1 service handler(s)

If the same is showing 1 Service Handler only like ( for this I am stopping and again restarting the listener)
INSA has 1 service handler(s)
INSB has 1 service handler(s)

Then They are connecting properly.