TNSNames Service Name Configuration traps in Oracle

In fact, is not a trap, just do not pay attention to, it is easy to configure errors.

This article describes the possible errors caused by load_balance and failover coordination.

When the service name is configured with both Load_balance and failover:

Testrac =

(DESCRIPTION =

(Address_list =

(address = (PROTOCOL = TCP) (HOST = 172.25.198.224) (PORT = 1521))

(address = (PROTOCOL = TCP) (HOST = 172.25.198.225) (PORT = 1521))

(load_balance = ON)

(FAILOVER = off)

)

(Connect_data =

(service_name = Testrac)

)

)

And the Remote_listener parameter is configured:

Sql> SELECT instance_name from v$instance;

Instance_name

----------------

Testrac1

Sql> Show PARAMETER Remote_listener

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

Remote_listener string Listeners_testrac

Check Instance 2:

This column more highlights: http://www.bianceng.cn/database/Oracle/

Sql> SELECT instance_name from v$instance;

Instance_name

----------------

Testrac2

Sql> Show PARAMETER Remote_listener

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

Remote_listener string Listeners_testrac

The Listeners_testrac configuration for the two-node tnsnames on the database server is:

Listeners_testrac =

(Address_list =

(address = (PROTOCOL = TCP) (HOST = RACNODE1-VIP) (PORT = 1521))

(address = (PROTOCOL = TCP) (HOST = RACNODE2-VIP) (PORT = 1521))

)

When instance 2 is turned off, no connection errors are caused:

bash-2.03$ srvctl Stop inst-d testrac-i TESTRAC2

Connection Service Name:

Sql> CONN Yangtk/yangtk@testrac

is connected.

Sql> SELECT instance_name from v$instance;

Instance_name

----------------

Testrac1

Sql> CONN Yangtk/yangtk@testrac

is connected.

Sql> SELECT instance_name from v$instance;

Instance_name

----------------

Testrac1

Sql> CONN Yangtk/yangtk@testrac

is connected.

Sql> SELECT instance_name from v$instance;

Instance_name

----------------

Testrac1

However, if instance 2 is not a problem, only instance 2 of the listener shutdown, then the configuration of this service name may cause an error:

bash-2.03$ srvctl start inst-d testrac-i testrac2

bash-2.03$ srvctl Stop Listener-n Racnode2

Test the connection again:

Sql> CONN Yangtk/yangtk@testrac

is connected.

Sql> SELECT instance_name from v$instance;

Instance_name

----------------

Testrac1

Sql> CONN Yangtk/yangtk@testrac

ERROR:

Ora-12541:tns: No listening program