OraFAQ Forum: RAC & Failsafe » 2 node RAC always connect to 2nd instance

Home » Server Options » RAC & Failsafe » 2 node RAC always connect to 2nd instance (11.2.0.4 SE, SUSE Linux Enterprise Server 11 SP2 (x86_64) - Kernel \r (\l).)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

2 node RAC always connect to 2nd instance [message #642149]

Fri, 04 September 2015 00:20

juniordbanewbie
Messages: 250
Registered: April 2014

Senior Member

Hi,

currently I have a 2 node RAC, but the problems is any time I used a grid scan to connect, it always connect to the 2nd instance. I've gone through MOS 975457.1 all the troubleshooting steps, but still could not know how to resolve it.

Code: [Select all] [Show/ hide]

oracle@imdb1:~> dig +short imdb-scan.imworld.net
10.131.100.90

Code: [Select all] [Show/ hide]

oracle@imdb1:~> ping -c 3 imdb-scan.imworld.net
PING imdb-scan.imworld.net (192.168.50.90) 56(84) bytes of data.
64 bytes from imdb-scan.imworld.net (192.168.50.90): icmp_seq=1 ttl=64 time=0.034 ms
64 bytes from imdb-scan.imworld.net (192.168.50.90): icmp_seq=2 ttl=64 time=0.039 ms
64 bytes from imdb-scan.imworld.net (192.168.50.90): icmp_seq=3 ttl=64 time=0.043 ms

Code: [Select all] [Show/ hide]

oracle@imdb1:~> srvctl status scan -v
SCAN VIP scan1 is enabled
SCAN VIP scan1 is running on node imdb1
oracle@imdb1:~> srvctl status scan_listener -v
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node imdb1

=>this clearly indicates that listener_scan1 is on node imdb1

Code: [Select all] [Show/ hide]

04:29:46 SCHEDULER@imdb-scan.imworld.net:1521/orcl[]> SELECT sys_context('userenv','instance') FROM dual;

SYS_CONTEXT('USERENV','INSTANCE')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2

=>2nd instance

Code: [Select all] [Show/ hide]

tail -f $GRID_BASE/diag/tnslsnr/imdb2/listener/trace/listener.log

04-SEP-2015 04:29:41 * (CONNECT_DATA=(SERVICE_NAME=+ASM2)(INSTANCE_NAME=+ASM2)(UR=A)(CID=(PROGRAM=emagent)(HOST=imdb2)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.50.83)(PORT=31429)) * establish * +ASM2 * 0
04-SEP-2015 04:29:46 * (CONNECT_DATA=(SERVICE_NAME=orcl)(CID=(PROGRAM=sqlplus@imdb1)(HOST=imdb1)(USER=oracle))(SERVER=dedicated)(INSTANCE_NAME=orcl2)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.50.82)(PORT=33597)) * establish * orcl * 0
04-SEP-2015 04:29:48 * service_update * orcl2 * 0
04-SEP-2015 04:29:50 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=imdb2)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186647552)) * status * 0
Fri Sep 04 04:29:54 2015

=>from line containing 04-SEP-2015 04:29:46
=>this indicates that the connection route is through imdb1 through instance orcl2

Code: [Select all] [Show/ hide]

ls -l $GRID_BASE/diag/tnslsnr/imdb1
oracle@imdb1:~> ls -l $GRID_BASE/diag/tnslsnr/imdb1
total 8
drwxr-xr-x 13 oracle oinstall 4096 Oct 22  2012 listener
drwxr-xr-x 13 oracle oinstall 4096 Sep 11  2014 listener_11204

there's no scan_listenerx logs as well

Code: [Select all] [Show/ hide]

oracle@imdb1:~> crsctl stat res ora.orcl.db -l
NAME=ora.orcl.db
TYPE=ora.database.type
CARDINALITY_ID=1
DEGREE_ID=1
TARGET=ONLINE
STATE=ONLINE on imdb1

CARDINALITY_ID=2
DEGREE_ID=1
TARGET=ONLINE
STATE=ONLINE on imdb2

=> this command indicates that orcl1 is on imdb1 and orcl2 is on imdb2=>indicates that there's something not correct in the listener log

Code: [Select all] [Show/ hide]

oracle@imdb1:~> olsnodes -n -i -t -s
imdb1  1       192.168.50.88   Active  Unpinned
imdb2  2       192.168.50.89   Active  Unpinned

=>this command indicates that first node is imdb1 and second node is imdb2

Code: [Select all] [Show/ hide]

oracle@imdb1:~> srvctl config database -d orcl -a -v
Database unique name: orcl
Database name: orcl
Oracle home: /u01/app/oracle/product/11.2.0.4/db_std
Oracle user: oracle
Spfile: +DATA/orcl/spfileorcl.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: orcl
Database instances: orcl1,orcl2
Disk Groups: DATA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed
oracle@imdb1:~>

=>database is administrator managed not policy managed

I also realize something usual the node vip don't seem to have alias.

Code: [Select all] [Show/ hide]

oracle@imdb1:~> srvctl status vip -n imdb1 -v
VIP 192.168.50.88 is enabled
VIP 192.168.50.88 is running on node: imdb1
oracle@imdb1:~> srvctl status vip -n imdb2 -v
VIP 192.168.50.89 is enabled
VIP 192.168.50.89 is running on node: imdb2

I learn from the person who installed the grid infrastructure that he encounters DNS timeout during pre grid infrastructure installation verification. I also check that there's no even a single imdb-scan entries on both nodes /etc/hosts.

How should I resolve this issue of listener_scan1 residing on imdb1 while the connection is not on imdb1.

many thanks in advance!

Report message to a moderator

Re: 2 node RAC always connect to 2nd instance [message #642153 is a reply to message #642149]

Fri, 04 September 2015 01:39

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

You have not shown any evidence that all your sessions are going to one node.
Can you logon through the SCAN a few times concurrently, and then run these queries:

select inst_id,host_name,instance_name from gv$instance;
select inst_id,username from gv$session where username is not null;

HOwever, your installation is little odd: you appear to have only one SCAN address. You should discuss this with whomever did the install.
It is correct that the SCAN is not in your hosts files, but there should be multiple addresses in your DNS (unless you use GPnP, as you should)
THere may be other anomalies too.

Lastly, you seem to be assuming a relationship between node name, node number, instance name, and instance number. There is none.

[Updated on: Fri, 04 September 2015 01:42]

Report message to a moderator

Re: 2 node RAC always connect to 2nd instance [message #642158 is a reply to message #642153]

Fri, 04 September 2015 02:39

juniordbanewbie
Messages: 250
Registered: April 2014

Senior Member

Code: [Select all] [Show/ hide]

oracle@imdb1:~> sqlplus scheduler/scheduler@imdb-scan.imworld.net:1521/orcl

SQL*Plus: Release 11.2.0.4.0 Production on Fri Sep 4 08:19:21 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

08:19:21 SCHEDULER@imdb-scan.imworld.net:1521/orcl[]> select inst_id,host_name,instance_name from gv$instance;

   INST_ID HOST_NAME                                                        INSTANCE_NAME
---------- ---------------------------------------------------------------- ----------------
         2 imdb2                                                           orcl2
         1 imdb1                                                           orcl1

Elapsed: 00:00:00.01
08:19:25 SCHEDULER@imdb-scan.imworld.net:1521/orcl[]> select host_name,instance_name from v$instance;

HOST_NAME                                                        INSTANCE_NAME
---------------------------------------------------------------- ----------------
imdb2                                                           orcl2

Code: [Select all] [Show/ hide]

oracle@imdb2:~> sqlplus scheduler/scheduler@imdb-scan.imworld.net:1521/orcl

SQL*Plus: Release 11.2.0.4.0 Production on Fri Sep 4 08:20:54 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

08:20:54 SCHEDULER@imdb-scan.imworld.net:1521/orcl[]> select inst_id,host_name,instance_name from gv$instance;

   INST_ID HOST_NAME                                                        INSTANCE_NAME
---------- ---------------------------------------------------------------- ----------------
         2 imdb2                                                           orcl2
         1 imdb1                                                           orcl1

Elapsed: 00:00:00.01
08:21:31 SCHEDULER@imdb-scan.imworld.net:1521/orcl[]> select host_name,instance_name from v$instance;

HOST_NAME                                                        INSTANCE_NAME
---------------------------------------------------------------- ----------------
imdb2                                                           orcl2

As can be seem above whether it's from imdb1 or imdb2, it is always to orcl2 on imdb2. This is despite the fact that scan_listener1 is on imdb1

What should I do next? stop the scan_listener, remove scan_listener, add scan_listener again?

and many many thanks

Report message to a moderator

Re: 2 node RAC always connect to 2nd instance [message #642160 is a reply to message #642158]

Fri, 04 September 2015 04:02

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

Is there a reason for you not doing what I asked?
You need to do what I asked in order to demonstrate that all your connections go to one instance.

Report message to a moderator

Re: 2 node RAC always connect to 2nd instance [message #642188 is a reply to message #642160]

Fri, 04 September 2015 11:24

juniordbanewbie
Messages: 250
Registered: April 2014

Senior Member

sorry just missed out your query

Code: [Select all] [Show/ hide]

16:56:22 SYS@orcl1[AS SYSDBA]> select inst_id,username, machine, service_name from gv$session where username is not null AND type != 'BACKGROUND';

   INST_ID USERNAME                       MACHINE
---------- ------------------------------ ----------------------------------------------------------------
         1 GDR                            hpux03
         1 ECO                            hpux03
         1 GDR                            hpux03
         1 PULSE_SOT                      hpux02
         1 GDR                            hpux02
         1 PULSE                          hpux03
         1 NFM                            hpux02
         1 EMAC                           hpux02
         1 DBSNMP                         imdb1
         1 PROMIS                         hpux03
         1 SYS                            imdb1
         1 SYS                            imdb1
         1 SAP                             hpux02
         1 GDR                            hpux02
         1 RM32                           hpux03
         1 SCHEDULER                      hpux03
         2 PROMIS                         hpux03
         2 DBSNMP                         imdb2
         2 SCHEDULER                      imdb1
         2 DBSNMP                         imdb2
         2 DBSNMP                         imdb2
         2 ECO                            hpux02
         2 SAP                             hpux02
         2 SYS                            imdb2
         2 PROMIS                         hpux03
         2 SYS                            imdb2

it does say that a lot of session got connected to instance 1, but the real problem is some of the connection might be using the node-vip instead of imdb-scan as my client tnsnames.ora does have a number of entries that is not using imdb-scan. from http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_3016.htm#REFRN30223 I know the service_name, but I don't know how these session are connected, i.e whether it is through easy connect or tnsnames.ora

Code: [Select all] [Show/ hide]

ORCLVIP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = imdb1-vip)(PORT = 1521))
	(ADDRESS = (PROTOCOL = TCP)(HOST = imdb2-vip)(PORT = 1521))
	(LOAD_BALANCE = ON)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

and many many thanks for the assistance given!

Report message to a moderator

Re: 2 node RAC always connect to 2nd instance [message #642190 is a reply to message #642149]

Fri, 04 September 2015 11:37

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

So your new sessions go to inst_id 2, because the default connect time load balancing algorithm
will attempt to even out the session disrtibution. Behaviour looks perfect to me.

Report message to a moderator

Re: 2 node RAC always connect to 2nd instance [message #642196 is a reply to message #642190]

Fri, 04 September 2015 12:04

juniordbanewbie
Messages: 250
Registered: April 2014

Senior Member

but still it could not explain the fact that scan_listener was on imdb1, but I got connected to orcl2

Code: [Select all] [Show/ hide]

04-SEP-2015 04:29:46 * (CONNECT_DATA=(SERVICE_NAME=orcl)(CID=(PROGRAM=sqlplus@imdb1)(HOST=imdb1)(USER=oracle))(SERVER=dedicated)(INSTANCE_NAME=orcl2)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.50.82)(PORT=33597)) * establish * orcl * 0

orcl1 resides on imdb1 and not orcl2

the real issue is imdb-scan is not working as expected.

there's also the unexplained missing scan_listener logs

Code: [Select all] [Show/ hide]

ls -l $GRID_BASE/diag/tnslsnr/imdb1
oracle@imdb1:~> ls -l $GRID_BASE/diag/tnslsnr/imdb1
total 8
drwxr-xr-x 13 oracle oinstall 4096 Oct 22  2012 listener
drwxr-xr-x 13 oracle oinstall 4096 Sep 11  2014 listener_11204

and many many thanks for your kind assistance!

[Updated on: Fri, 04 September 2015 12:05]

Report message to a moderator

Re: 2 node RAC always connect to 2nd instance [message #642197 is a reply to message #642196]

Fri, 04 September 2015 12:19

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

You need to do some reading. If you don;t want to read the docs that describe how a SCAN listener works,
you could try this very simple desdcription,

11.2 GI networking

Report message to a moderator

Re: 2 node RAC always connect to 2nd instance [message #642288 is a reply to message #642197]

Sun, 06 September 2015 17:41

juniordbanewbie
Messages: 250
Registered: April 2014

Senior Member

OKay

Code: [Select all] [Show/ hide]

oracle@imdb1:~> srvctl status scan -v
SCAN VIP scan1 is enabled
SCAN VIP scan1 is running on node imdb1
oracle@imdb1:~> srvctl status scan_listener -v
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node imdb1

=>listener_scan1 is on imdb1

Code: [Select all] [Show/ hide]

08:20:54 SCHEDULER@imdb-scan.imworld.net:1521/orcl[]> select inst_id,host_name,instance_name from gv$instance;

   INST_ID HOST_NAME                                                        INSTANCE_NAME
---------- ---------------------------------------------------------------- ----------------
         2 imdb2                                                           orcl2
         1 imdb1                                                           orcl1

=>implies that orcl1 is on imdb1,
=>does that natually means that any connection through imdb-scan.imworld.net will also go through orcl1

thanks and many many thanks!

Report message to a moderator

Previous Topic:	Error: kkjcre1p: unable to spawn jobq slave process
Next Topic:	Cannot connect through single client access name scan after 1st node pubic cable is plugged off

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 02 14:02:50 CDT 2025