Oracle rac which instance am i connected to

This document explains how you can take advantage of Oracle RAC features such as load balancing and connection failover using the DataDirect Connect for JDBC Oracle driver to connect your data critical applications to data.

Connecting to an Oracle RAC system is similar to connecting to a single instance of an Oracle database. For example, the following URL establishes a connection to the database instance Accting In a RAC environment, multiple Oracle instances share the same physical data.

The specific instance that is connected to is determined by a number of factors, including which instances are available and the load on those instances. Typically, the application does not need to know which instance to which it is connected.

DataDirect Connect ; for JDBC Oracle driver also supports retrieving specific connection information, including connection failover and client load balancing instructions, from a tnsnames. In a tnsnames. The following example shows connection information in a tnsnames. Alternatively, DataDirect Connect for JDBC provides a way to enable connection failover and client load balancing through driver properties specified in a connection URL or data source.

For example, the following connection URL enables both of these features:. Both connection failover and TAF provide a connection retry feature that allows a connection to be retried automatically until a connection with another RAC node is successfully re-established.

The primary difference between connection failover and TAF is that the former method provides protection for connections at connect time and the latter method provides protection for connections that have already been established. Also, because the state of the transaction must be stored at all times, TAF requires more processing overhead than connection failover. Enabling connection failover allows a driver to attempt to connect on another node if the connection attempt on one node fails.

When an application requests a connection to an Oracle database server via the driver, the driver does not connect to the database server directly.

Instead, the driver sends a connection request to a listener process, which forwards the request to the appropriate Oracle database instance. If the service name in the connection request specifies the RAC system database name, the requested listener selects one of the registered instances to forward the connection request to, based on the load each of the instances is experiencing. For example, if Instance A and B are operating under a heavy load, a connection request to Listener A results in the connection being forwarded to Instance C.

Because the requested listener selects from a set of active instances in the RAC to forward connection requests to, it should not route the connection request to an instance that is not running. You may think that connection failover is not needed in an Oracle RAC system; however, if the requested listener is down or the timing of an instance going down is such that the requested listener is not yet aware that an instance is down, the connection request can fail.

Add a comment. Active Oldest Votes. For your current session? Improve this answer. HampusLi HampusLi 3, 14 14 silver badges 14 14 bronze badges. OK, so this shows me how my own session is connected. Is there a chance I can monitor others as well? If I login using sqlplus to check the webapp that logs with a different session?

Andrew Schulman 8, 21 21 gold badges 29 29 silver badges 45 45 bronze badges. Hakan Hakan 71 1 1 bronze badge. Ladadadada Jenny D A little context for what this query does, or any text besides the query itself would be great The connection broker implements the pool functionality and performs the multiplexing of inbound connections from the clients to a pool of dedicated servers with sessions. When a client must perform database work, the connection broker picks up a dedicated server from the pool and assigns it to the client.

Subsequently, the client is directly connected to the dedicated server until the request is served. After the server finishes processing the client request, the server goes back into the pool and the connection from the client is restored to the connection broker.

A naming method is a resolution method used by a client application to resolve a connect identifier to a connect descriptor when attempting to connect to a database service. Users initiate a connection request by providing a connect string. A connect string includes a user name and password, along with a connect identifier.

A connect identifier can be the connect descriptor or a name that resolves to a connect descriptor. The connect descriptor contains:. A database service name or Oracle system identifier SID.

The following CONNECT command uses a connect string that has a complete connect descriptor as the connect identifier instead of a net service name.

The string should be entered on a single line. It is shown on two lines because of page width. One of the most common connect identifiers is a net service name , a simple name for a service. When net service name sales is used, connection processing takes place by first mapping sales to the connect descriptor. This mapped information is accessed by naming method s. The following naming methods are available:.

Selecting the appropriate naming method for mapping names to connect descriptors depends upon the size of the organization. For large organizations with several databases, use directory naming to store names in a centralized directory server. For an Internet network, configure the application Web servers needed to connect to the databases with the local naming method.

Table summarizes the relative advantages and disadvantages of each naming method and provides recommendations for using them in the network. Table Naming Methods: Advantages and Disadvantages. Stores net service names and their connect descriptors in a localized configuration file named tnsnames. Disadvantage : Requires local configuration of all net service name and address changes. Stores connect identifiers in a centralized LDAP-compliant directory server to access a database service.

Centralizes network names and addresses in a single place, facilitating administration of name changes and updates. This eliminates the need for an administrator to make changes to what potentially could be hundreds or even thousands of clients. Requires minimal user configuration. The user can provide only the name of the database host to establish a connection. Eliminates the need to create and maintain a local names configuration file tnsnames.

Disadvantage : Available only in a limited environment, as indicated in the Recommended for column. Advantage : Enables administrators to load Oracle net service name into their native name service using tools and utilities with which they are already familiar. Disadvantage : Requires a third-party naming services that cannot be administered using Oracle Net products. After the network components are started you should be able to make a connection across the network. How you make a connection depends upon the naming method , and the tool used for the connection.

The connect string takes the following format:. On most operating systems, you can define a default connect identifier. When using the default, a connect identifier does not need to be specified in the connect string.

Connect identifiers used in a connect string cannot contain spaces, unless enclosed within single quotation marks ' or double quotation marks ". In the following examples, a connect identifier and a connect descriptor that contain spaces are enclosed within single quotation marks:.

Single quotation marks are required if double quotation marks are used in a connect identifier. For example:. Similarly, double quotation marks are required if a single quotation marks are used in a connect identifier. For some configurations, such as Oracle Real Application Clusters, multiple listeners on multiple nodes can be configured to handle client connection requests for the same database service.

In the following example, sales. A multiple-listener configuration also enables you to leverage the failover and load balancing features, either individually or in combination with each other. The following topics describe the features:. The connect-time failover feature enables clients to connect to another listener if the initial connection to the first listener fails. The number of listener protocol addresses determines how many listeners are tried.

Without connect-time failover, Oracle Net attempts a connection with only one listener. TAF fails over and reestablishes application-to-service connections. It enables client applications to automatically reconnect to the database if the connection fails and, optionally, resume a SELECT statement that was in progress. The client load balancing feature enables clients to randomize connection requests among the listeners.

Oracle Net progresses through the list of protocol addresses in a random sequence, balancing the load on the various listeners. Without client load balancing, Oracle Net progresses through the list of protocol addresses sequentially until one succeeds. The connection load balancing feature improves connection performance by balancing the number of active connections among multiple dispatchers. We're using Oracle RAC. I believe it's 12g with an 11g driver.

APM identifies the different RAC nodes individually in the metric tree using their host names, so I might have db01, db02, and db03, which are all part of the same logical DB.

However some of these nodes are in different data centers than my app server. The response times are noticeably different. That's no surprise, but what IS a surprise is that the response times are consistent for long periods of time, then suddenly change. Then all of a sudden those same queries start returning response times that are much higher, say 50ms.

Coincidentally, the other db nodes are about ms away from my app server. This is especially obvious when the connection switches from a far away data center to a closer one. Suddenly my response times are coming back faster than the speed of light!

Does anyone know what's going on here? I'm not super familiar with Oracle RAC, but it looks like connections are not always getting routed to the same node but APM reports them as if they were. I hope Sridevi's response was helpful. Marking as answered. You may post followup questions as needed.