Shulou(Shulou.com)05/31 Report--

What this article shares to you is about the solution of ora-12545 connection error. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

When remote clients connect to RAC databases, ORA-12545 errors often occur when connecting through a unified service name. After querying it on METALINK, it turned out to be a small bug of Oracle.

Environment: ORACLE 11G R2 RAC database, two nodes.

Phenomenon:

[oracle@ted35 ~] $sqlplus / nolog

SQL*Plus: Release 11.2.0.1.0 Production on Friday June 15 11:57:00 2012

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

SQL > conn sys/ted@123e@prodb as sysdba

ERROR:

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

Fault analysis: check with tnsping and find that you can ping. This is because tnsping only checks whether the IP address and port can be connected. As for the database instance status, it does not check which services are registered.

Let's take a look at the configuration of the local database TNSNAMES:

TESTRAC =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = 10.58.12.124) (PORT = 1521))

(ADDRESS = (PROTOCOL = TCP) (HOST = 10.58.12.125) (PORT = 1521))

(LOAD_BALANCE = yes)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = 10.58.12.124)

(FAILOVER_MODE =

(TYPE = SELECT)

(METHOD = BASIC)

(RETRIES = 180)

(DELAY = 5)

)

)

Oracle is described in the document Note:364855.1:RAC Connection Redirected To Wrong Host/IP ORA-12545, and the solution is given: modify the initialization parameter LOCAL_LISTENER in the database (Note: this method needs to restart listening and restart the instance after modifying the initialization parameters)

SQL > conn / as sysdba

Connected.

SQL > ALTER SYSTEM SET LOCAL_LISTENER ='(ADDRESS = (PROTOCOL = TCP) (HOST = 10.58.12.124) (PORT = 1521)) 'SID =' prodb1'

The system has changed.

Log in to another node

SQL > conn / as sysdba

Connected.

SQL > ALTER SYSTEM SET LOCAL_LISTENER ='(ADDRESS = (PROTOCOL = TCP) (HOST = 10.58.12.125) (PORT = 1521)) 'SID =' prodb2'

The system has changed.

After setting up, try to connect to the database again:

SQL > conn sys/ted@123e@prodb as sysdba

Connected.

SQL > conn sys/ted@789e@prodb as sysdba

Connected.

After the modification, the same error did not occur again.

We can also solve this problem by modifying only the client-side hosts file.

First modify the HOSTS file on the linux host

Root@prodb1 # vi / etc/hosts

#

10.58.12.120 prodb1-public # Public

10.58.12.124 prodb1 # Virtual

23.23.23.120 prodb1-priv # Private

10.58.12.121 prodb2-public # Public

10.58.12.125 prodb2 # Virtual

23.23.23.121 prodb2-priv # Private

10.58.12.126 prodb-scan # SCAN

Then the configuration in the client's tnsnames.ora is as follows:

$more $ORACLE_HOME/network/admin/tnsnames.ora

# tnsnames.ora Network Configuration File: / data/oracle/product/11.2/database/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

LISTENERS_prodb =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = 10.58.12.124) (PORT = 1521))

(ADDRESS = (PROTOCOL = TCP) (HOST = 10.58.12.125) (PORT = 1521))

)

LISTENER_prodb1 =

(ADDRESS = (PROTOCOL = TCP) (HOST = 10.58.12.124) (PORT = 1521))

LISTENER_prodb2 =

(ADDRESS = (PROTOCOL = TCP) (HOST = 10.58.12.125) (PORT = 1521))

Prodb =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = 10.58.12.124) (PORT = 1521))

(ADDRESS = (PROTOCOL = TCP) (HOST = 10.58.12.125) (PORT = 1521))

(LOAD_BALANCE = yes)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = prodb)

(FAILOVER_MODE =

(TYPE = SELECT)

(METHOD = BASIC)

(RETRIES = 180)

(DELAY = 5)

)

)

)

Prodb1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = 10.58.12.124) (PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = prodb)

(INSTANCE_NAME = prodb1)

)

)

Prodb2 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = 10.58.12.125) (PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = prodb)

(INSTANCE_NAME = prodb2)

)

)

Try to connect to the remote RAC database below:

SQL > conn sys/ted@123e@prodb as sysdba; is connected.

SQL > conn sys/ted@789e@prodb as sysdba; is connected.

In fact, this error can be avoided by adding the ip and hostname information of the server where the RAC instance is located in the local hosts file.

Also note that in a RAC environment, any changes must be made with caution. Once the RAC environment is installed, the hostname cannot be modified at will unless you delete the node, modify the Hostname, and then add the node. It is also important that the host name must be the same as the public name.

These are the solutions to ora-12545 connection errors. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.