Shulou(Shulou.com)06/01 Report--

This article will explain in detail the solution that Oracle11gR2 RAC can not connect to the database using scan IP. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Environment:

[grid@rac1 ~] $cat / etc/hosts

# Do not remove the following line, or various programs

# that require network functionality will fail.

127.0.0.1 localhost.localdomain localhost

:: 1 localhost6.localdomain6 localhost6

# Public

192.168.2.101 rac1

192.168.2.102 rac2

# Private

192.168.0.101 rac1-priv

192.168.0.102 rac2-priv

# Virtual

192.168.2.111 rac1-vip

192.168.2.112 rac2-vip

# SCAN

192.168.2.200 rac-scan

Phenomenon:

The sqlplus tool on the windows client cannot connect using IP of scan. The error is as follows:

C:\ Users\ WJW > sqlplus system/qweasd@192.168.2.200:1521/orcl

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 17 12:35:28 2012

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

ERROR:

ORA-12545: Connect failed because target host or object does not exist

Check the status of each component, normal:

[grid@rac1] $crs_stat-t

Name Type Target State Host

Ora.DATA.dg ora....up.type ONLINE ONLINE rac1

Ora....ER.lsnr ora....er.type ONLINE ONLINE rac1

Ora....N1.lsnr ora....er.type ONLINE ONLINE rac1

Ora.asm ora.asm.type ONLINE ONLINE rac1

Ora.eons ora.eons.type ONLINE ONLINE rac1

Ora.gsd ora.gsd.type ONLINE ONLINE rac1

Ora....network ora....rk.type ONLINE ONLINE rac1

Ora.oc4j ora.oc4j.type ONLINE ONLINE rac2

Ora.ons ora.ons.type ONLINE ONLINE rac1

Ora.orcl.db ora....se.type ONLINE ONLINE rac1

Ora....SM1.asm application ONLINE ONLINE rac1

Ora....C1.lsnr application ONLINE ONLINE rac1

Ora.rac1.gsd application ONLINE ONLINE rac1

Ora.rac1.ons application ONLINE ONLINE rac1

Ora.rac1.vip ora....t1.type ONLINE ONLINE rac1

Ora....SM2.asm application ONLINE ONLINE rac2

Ora....C2.lsnr application ONLINE ONLINE rac2

Ora.rac2.gsd application ONLINE ONLINE rac2

Ora.rac2.ons application ONLINE ONLINE rac2

Ora.rac2.vip ora....t1.type ONLINE ONLINE rac2

Ora.scan1.vip ora....ip.type ONLINE ONLINE rac1

Check the status of the listener, normal:

[grid@rac1 ~] $lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0-Production on 17-MAY-2012 13:27:40

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Connecting to (DESCRIPTION= (ADDRESS= (PROTOCOL=IPC) (KEY=LISTENER)

STATUS of the LISTENER

-

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.1.0-Production

Start Date 17-MAY-2012 12:19:51

Uptime 0 days 1 hr. 7 min. 50 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File / u01/app/grid/network/admin/listener.ora

Listener Log File / u01/app/oracle/diag/tnslsnr/rac1/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION= (ADDRESS= (PROTOCOL=ipc) (KEY=LISTENER)

(DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=192.168.2.101) (PORT=1521))

(DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=192.168.2.111) (PORT=1521))

Services Summary...

Service "+ ASM" has 1 instance (s).

Instance "+ ASM1", status READY, has 1 handler (s) for this service...

Service "orcl" has 1 instance (s).

Instance "orcl1", status READY, has 1 handler (s) for this service...

Service "orclXDB" has 1 instance (s).

Instance "orcl1", status READY, has 1 handler (s) for this service...

The command completed successfully

[grid@rac2 ~] $lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0-Production on 17-MAY-2012 13:28:23

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Connecting to (DESCRIPTION= (ADDRESS= (PROTOCOL=IPC) (KEY=LISTENER)

STATUS of the LISTENER

-

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.1.0-Production

Start Date 17-MAY-2012 12:19:51

Uptime 0 days 1 hr. 8 min. 32 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File / u01/app/grid/network/admin/listener.ora

Listener Log File / u01/app/oracle/diag/tnslsnr/rac2/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION= (ADDRESS= (PROTOCOL=ipc) (KEY=LISTENER)

(DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=192.168.2.112) (PORT=1521))

(DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=192.168.2.102) (PORT=1521))

Services Summary...

Service "+ ASM" has 1 instance (s).

Instance "+ ASM2", status READY, has 1 handler (s) for this service...

Service "orcl" has 1 instance (s).

Instance "orcl2", status READY, has 1 handler (s) for this service...

Service "orclXDB" has 1 instance (s).

Instance "orcl2", status READY, has 1 handler (s) for this service...

The command completed successfully

[grid@rac1 ~] $lsnrctl status listener_scan1

LSNRCTL for Linux: Version 11.2.0.1.0-Production on 17-MAY-2012 13:29:09

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Connecting to (DESCRIPTION= (ADDRESS= (PROTOCOL=IPC) (KEY=LISTENER_SCAN1)

STATUS of the LISTENER

-

Alias LISTENER_SCAN1

Version TNSLSNR for Linux: Version 11.2.0.1.0-Production

Start Date 17-MAY-2012 12:21:31

Uptime 0 days 1 hr. 7 min. 37 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File / u01/app/grid/network/admin/listener.ora

Listener Log File / u01/app/grid/log/diag/tnslsnr/rac1/listener_scan1/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION= (ADDRESS= (PROTOCOL=ipc) (KEY=LISTENER_SCAN1)

(DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=192.168.2.200) (PORT=1521))

Services Summary...

Service "orcl" has 2 instance (s).

Instance "orcl1", status READY, has 1 handler (s) for this service...

Instance "orcl2", status READY, has 1 handler (s) for this service...

Service "orclXDB" has 2 instance (s).

Instance "orcl1", status READY, has 1 handler (s) for this service...

Instance "orcl2", status READY, has 1 handler (s) for this service...

The command completed successfully

Reason:

Metalink description: Client is able to resolve all Fully Qualified Domain Name (FQDN) SCAN and VIP name but not short ones (without domain name) as its in different domain

Even though FQDN names were specified during Grid Infrastructure setup, due to bug 9150053 by default DBCA set database parameter local_listener to short node VIP name

While database is created.

Therefore, just change the host in the database instance parameter local_listener to the IP address of vip

Solution:

Rac1:

SQL > show parameter local_listener

NAME TYPE VALUE

-

Local_listener string (DESCRIPTION= (ADDRESS_LIST= (AD)

DRESS= (PROTOCOL=TCP) (HOST=rac1

-vip) (PORT=1521)

SQL > alter system set local_listener=' (DESCRIPTION= (ADDRESS_LIST= (ADDRESS= (PROTOCOL=TCP) (HOST=192.168.2.111) (PORT=1521) 'scope=both sid='orcl1'

SQL > alter system register

Rac2:

SQL > show parameter local_listener

NAME TYPE VALUE

-

Local_listener string (DESCRIPTION= (ADDRESS_LIST= (AD)

DRESS= (PROTOCOL=TCP) (HOST=rac2

-vip) (PORT=1521)

SQL > alter system set local_listener=' (DESCRIPTION= (ADDRESS_LIST= (ADDRESS= (PROTOCOL=TCP) (HOST=192.168.2.112) (PORT=1521) 'scope=both sid='orcl2'

SQL > alter system register

The connection was then successful using the windows client:

C:\ Users\ WJW > sqlplus system/qweasd@192.168.2.200:1521/orcl

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 17 13:14:39 2012

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP

Data Mining and Real Application Testing options

So much for the solution that Oracle11gR2 RAC can not connect to the database using scan IP. I hope the above content can be of some help and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.