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.
oracle@imdb1:~> dig +short imdb-scan.imworld.net
10.131.100.90
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
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
04:29:46 SCHEDULER@imdb-scan.imworld.net:1521/orcl[]> SELECT sys_context('userenv','instance') FROM dual;
SYS_CONTEXT('USERENV','INSTANCE')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2
=>2nd instance
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
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
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
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
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.
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!
|
|
|
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: 8949 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 |
|
|
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
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
|
|
|
|
|
|
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
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
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 #642288 is a reply to message #642197] |
Sun, 06 September 2015 17:41 |
|
juniordbanewbie
Messages: 250 Registered: April 2014
|
Senior Member |
|
|
OKay
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
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!
|
|
|