Connecting to Oracle Database

Connection String Attributes

Table 3-1 lists the supported connection string attributes.

The following example uses connection string attributes to connect to Oracle Database:

// C#
 
using System;
using Oracle.DataAccess.Client; 
 
class ConnectionSample
{
  static void Main()
  {
    OracleConnection con = new OracleConnection();
 
    //using connection string attributes to connect to Oracle Database
    con.ConnectionString = "User Id=scott;Password=tiger;Data Source=oracle";
    con.Open();
    Console.WriteLine("Connected to Oracle" + con.ServerVersion);
    
    // Close and Dispose OracleConnection object
    con.Close();
    con.Dispose();
    Console.WriteLine("Disconnected");
  }
}
 

Specifying the Data Source Attribute

This section describes different ways of specifying the data source attribute.

The following example shows a connect descriptor mapped to a TNS alias called sales in the tnsnames.ora file:

sales=
 (DESCRIPTION= 
  (ADDRESS= (PROTOCOL=tcp)(HOST=sales-server)(PORT=1521))
  (CONNECT_DATA= 
     (SERVICE_NAME=sales.us.acme.com)))

"user id=scott;password=tiger;data source=sales";

"user id=scott;password=tiger;data source=" + 
     "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)" + 
     "(HOST=sales-server)(PORT=1521))(CONNECT_DATA="+
     "(SERVICE_NAME=sales.us.acme.com)))"

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

//host:[port]/[service_name]

"user id=scott;password=tiger;data source=//sales-server:1521/sales.us.acme.com"
"user id=scott;password=tiger;data source=//sales-server/sales.us.acme.com" 
"user id=scott;password=tiger;data source=sales-server/sales.us.acme.com"

Connection Pooling

ODP.NET connection pooling is enabled and disabled using the Pooling connection string attribute. By default, connection pooling is enabled. The following are ConnectionString attributes that control the behavior of the connection pooling service:

• Connection Lifetime

• Connection Timeout

• Decr Pool Size

• HA Events

• Incr Pool Size

• Load Balancing

• Max Pool Size

• Min Pool Size

• Pooling

• Validate Connection

Connection Pooling Example

The following example opens a connection using ConnectionString attributes related to connection pooling.

// C#
 
using System;
using Oracle.DataAccess.Client; 
 
 
class ConnectionPoolingSample
{
  static void Main()
  {
    OracleConnection con = new OracleConnection();
 
    //Open a connection using ConnectionString attributes
    //related to connection pooling.
    con.ConnectionString = 
      "User Id=scott;Password=tiger;Data Source=oracle;" + 
      "Min Pool Size=10;Connection Lifetime=120;Connection Timeout=60;" + 
      "Incr Pool Size=5; Decr Pool Size=2";
    con.Open();
    Console.WriteLine("Connection pool successfully created");
 
    // Close and Dispose OracleConnection object
    con.Close();
    con.Dispose();
    Console.WriteLine("Connection is placed back into the pool.");
  }
}

Connection Pool Management

ODP.NET connection pool management provides explicit connection pool control to ODP.NET applications. Applications can explicitly clear connections in a connection pool.

Using connection pool management, applications can do the following:

• Clear connections from connection pools using the ClearPool method.

• Clear connections in all the connection pools in an application domain, using the ClearAllPools method.

When connections are cleared from a pool, ODP.NET repopulates the pool with new connections that have at least the number of connections set by Min Pool Size in the connection string. New connections do not necessarily mean the pool will have valid connections. For example, if the database server is down when ClearPool or ClearAllPools is called, ODP.NET creates new connections, but these connections are still invalid because they cannot connect to the database, even if the database comes up a later time.

It is recommended that ClearPool and ClearAllPools not be called until the application can create valid connections back to the database. .NET developers can develop code that continuously checks whether or not a valid database connection can be created and calls ClearPool or ClearAllPools once this is true.

Connection Pool Performance Counters

Installing Oracle Data Provider for .NET creates a set of performance counters on the target system. These performance counters are published by ODP.NET for each ODP.NET client application. These performance counters can be viewed using Windows Performance Monitor (Perfmon).

In Perfmon, administrators can add ODP.NET counters to the performance monitor graph. ODP.NET performance counters are published under the following Category Name: Oracle Data Provider for .NET. Administrators can choose the ODP.NET counters to monitor after selecting the Oracle Data Provider for .NET category.

As ODP.NET performance counters are not enabled by default, administrators must enable the specific counters of interest before attempting to monitor them. In addition, at least one ODP.NET instance must be actively running when attempting to monitor using Perfmon.

Oracle Data Provider for .NET enables or disables publishing performance counters for connection pooling, using registry entries.

Table 3-2 lists the performance counters used for connection pooling with their valid registry values.

<configuration>
  <oracle.dataaccess.client>
  <settings>
    <add name="PerformanceCounters"
         value="3"/>
  </settings>
  </oracle.dataaccess.client>
</configuration>

Edition-Based Redefinition

Edition-based redefinition enables you to upgrade the database component of an application even while the application is being used. This minimizes or eliminates downtime for the application.

ODP.NET 11g Release 2 (11.2.0.1), and higher, supports specifying an Edition at deployment time when used with Oracle Database 11.2 or later. Applications can specify an Edition at deployment time using the registry or configuration file.

An application can create the following registry entry of type REG_SZ:

HKLM\Software\Oracle\ODP.NET\version\Edition

Here version is the version of ODP.NET, and Edition is a valid Edition string value.

An application can alternatively use the web.config or application.config configuration file to specify the Edition at deployment time. The machine.config configuration file can be used to specify the Edition for all applications that use a particular version of the .NET framework.

The following example sets the Edition to E1 in a configuration file for .NET 2.0, 3.0, and 3.5:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
 <oracle.dataaccess.client>
  <settings>
   <add name="Edition" value="E1"/>
  </settings>
 </oracle.dataaccess.client>
</configuration>

Connecting to an Oracle Real Application Clusters (Oracle RAC) Database

This section discusses optimization and other aspects of connection and connection pooling for an Oracle Real Application Clusters (Oracle RAC) database. Oracle RAC is the technology that makes grids possible for Oracle database by providing the ability to access the database from multiple instances, each running on nodes in a cluster.

Operating System Authentication

Oracle Database can use Windows user login credentials to authenticate database users. To open a connection using Windows user login credentials, the User Id connection string attribute must be set to a slash (/). If the Password attribute is provided, it is ignored.

Beginning with Oracle Data Provider for .NET release 11.1.0.6.20, all connections, including those using operating system authentication, can be pooled. Connections are pooled by default, and no configuration is required, as long as pooling is enabled.

The following example shows the use of operating system authentication:

/* Create an OS-authenticated user in the database
   Assume init.ora has OS_AUTHENT_PREFIX set to "" and <OS_USER>
   is any valid OS or DOMAIN user.
 
     create user <OS_USER> identified externally;
     grant connect, resource to <OS_USER>;
 
   Login through OS Authentication and execute the sample.  See Oracle 
   documentation for details on how to configure an OS-Authenticated user
*/
 
// C#
 
using System;
using Oracle.DataAccess.Client; 
 
class OSAuthenticationSample
{
  static void Main()
  {
    OracleConnection con = new OracleConnection();
 
    //Establish connection using OS Authentication   
    con.ConnectionString = "User Id=/;Data Source=oracle;";
    con.Open();
    Console.WriteLine("Connected to Oracle" + con.ServerVersion);
 
    // Close and Dispose OracleConnection object
    con.Close();
    con.Dispose();
    Console.WriteLine("Disconnected");
  }
}

Privileged Connections

Oracle allows database administrators to connect to Oracle Database with either SYSDBA or SYSOPER privileges. This is done through the DBA Privilege attribute of the ConnectionString property.

The following example connects scott/tiger as SYSDBA:

// C#
 
using System;
using Oracle.DataAccess.Client; 
 
class PrivilegedConnectionSample
{
  static void Main()
  {
    OracleConnection con = new OracleConnection();
 
    //Connect scott/tiger as SYSDBA   
    con.ConnectionString = "User Id=scott;Password=tiger;" + 
      "DBA Privilege=SYSDBA;Data Source=oracle;";
    con.Open();
    Console.WriteLine("Connected to Oracle" + con.ServerVersion);
 
    // Close and Dispose OracleConnection object
    con.Close();
    con.Dispose();
    Console.WriteLine("Disconnected");
  }
}

Password Expiration

Oracle allows users passwords to expire. ODP.NET lets applications handle the password expiration by providing a new method, OpenWithNewPassword, that opens the connection with a new password.

The following example uses the OracleConnection OpenWithNewPassword method to connect with a new password of panther:

/* Database Setup
connect / as sysdba;
drop user testexpire cascade;
-- create user "testexpire" with password "testexpire"
grant connect , resource to testexpire identified by testexpire; 
alter user testexpire password expire;
*/
 
 
// C#
 
using System;
using Oracle.DataAccess.Client; 
 
class PasswordExpirationSample
{
  static void Main()
  {
    OracleConnection con = new OracleConnection();
 
    try
    {
      con.ConnectionString = 
        "User Id=testexpire;Password=testexpire;Data Source=oracle";
      con.Open();
      Console.WriteLine("Connected to Oracle" + con.ServerVersion);
    }
    catch (OracleException ex)
    {
      Console.WriteLine(ex.Message);
 
      //check the error number 
      //ORA-28001 : the password has expired
      if (ex.Number == 28001)
      {
        Console.WriteLine("\nChanging password to panther");
        con.OpenWithNewPassword("panther");
        Console.WriteLine("Connected with new password.");
      }
    }
    finally
    {
      // Close and Dispose OracleConnection object
      con.Close();
      con.Dispose();
      Console.WriteLine("Disconnected");
    }
  }
}

Proxy Authentication

With proper setup in the database, proxy authentication enables middle-tier applications to control the security by preserving database user identities and privileges, and auditing actions taken on behalf of these users. This is accomplished by creating and using a proxy database user that connects and authenticates against the database on behalf of a database user (that is, the real user) or database users.

Proxy authentication can then be used to provide better scalability with connection pooling. When connection pooling is used in conjunction with proxy authentication, the proxy authenticated connections can be shared among different real users. This is because only the connection and session established for the proxy is cached. An additional session is created for the real user when a proxy authenticated connection is requested, but it will be destroyed appropriately when the proxy authenticated connection is placed back into the pool. This design enables the application to scale well without sacrificing security.

ODP.NET applications can use proxy authentication by setting the "Proxy User Id" and "Proxy Password" attributes in the connection string. The real user is specified by the "User Id" attribute. Optionally, to enforce greater security, the real user's password can be provided through the "Password" connection string attribute. When using distributed transactions in conjunction with proxy authentication, the real user's password is no longer optional, and it must be supplied.

The following example illustrates the use of ODP.NET proxy authentication:

/*  Log on as DBA (SYS or SYSTEM) that has CREATE USER privilege.
    Create a proxy user and modified scott to allow proxy connection.
  
     create user appserver identified by eagle;
     grant connect, resource to appserver;
     alter user scott grant connect through appserver;
*/
 
// C#
 
using System;
using Oracle.DataAccess.Client; 
 
class ProxyAuthenticationSample
{
  static void Main()
  {
    OracleConnection con = new OracleConnection();
 
    // Connecting using proxy authentication
    con.ConnectionString = "User Id=scott;Password=tiger;" + 
      "Data Source=oracle;Proxy User Id=appserver;Proxy Password=eagle; ";
    con.Open();
    Console.WriteLine("Connected to Oracle" + con.ServerVersion);
 
    // Close and Dispose OracleConnection object
    con.Close();
    con.Dispose();
    Console.WriteLine("Disconnected");
  }
}

Dynamic Distributed Transaction Enlistment

For those applications that dynamically enlist in distributed transactions through the EnlistDistributedTransaction of the OracleConnection object, the "enlist" connection string attribute must be set to a value of either "dynamic" or "true". If "enlist=true", the connection enlists in a transaction when the Open method is called on the OracleConnection object, if it is within the context of a COM+ transaction or a System.Transactions. If not, the OracleConnection object does not enlist in a distributed transaction, but it can later enlist explicitly using the EnlistDistributedTransaction or the EnlistTransaction method. If "enlist=false", the connection cannot enlist in the transaction.

For applications that cannot be rebuilt using "Enlist=dynamic", a registry string value, named DynamicEnlistment, of type REG_SZ, should be created and set to 1 under HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ODP.NET\Assembly_Version where Assembly_Version is the full assembly version number of Oracle.DataAccess.dll.If ODP.NET is properly installed, there should already be registry string values such as StatementCacheSize, TraceFileName, and so forth, under the same ODP.NET key. Dynamic Enlistment can also be configured through an XML configuration file. For details, see "Configuring Oracle Data Provider for .NET".If the DynamicEnlistment registry key is set to 0 (or if the registry entry does not exist), it does not affect the application in any way. However, if DynamicEnlistment is set to 1, "Enlist=false" is treated the same as "Enlist=dynamic", enabling applications to enlist successfully through the EnlistDistributedTransaction method without any code change. Having DynamicEnlistment set to 1 does not affect OracleConnection objects that have "Enlist=true" or "Enlist=dynamic" in the connection string.

Client Identifier and End-to-End Tracing

The client identifier is a predefined attribute from the Oracle application context namespace USERENV. It is similar to proxy authentication because it can enable tracking of user identities. However, client identifier does not require the creation of two sessions (one for the proxy user and another for the end user) as proxy authentication does. In addition, the client identifier does not have to be a database user. It can be set to any string. But most importantly, by using client identifier, ODP.NET developers can use application context and Oracle Label Security, and configure Oracle Virtual Private Database (VPD) more easily. To set the client identifier, ODP.NET applications can set the ClientId property on the OracleConnection object after opening a connection. If connection pooling is enabled, the ClientId is reset to null whenever a connection is placed back into the pool.

The client identifier can also be used for end-to-end application tracing. End-to-end tracing simplifies the process of diagnosing performance problems in multitier environments. In multitier environments, a request from an end client is routed to different database sessions by the middle tier making it difficult to track a client across different database sessions. End-to-end tracing uses the client identifier to uniquely trace a specific end-client through all tiers to the database server.

ODP.NET exposes the ActionName, ClientId, ClientInfo, and ModuleName write-only properties on the OracleConnection object. These properties correspond to the following end-to-end tracing attributes:

• Action - Specifies an action, such as an INSERT or UPDATE operation, in a module

• ClientId - Specifies an end user based on the logon ID, such as HR.HR

• Client info - Specifies user session information

• Module - Specifies a functional block, such as Accounts Receivable or General Ledger, of an application

Transparent Application Failover (TAF) Callback Support

Transparent Application Failover (TAF) is a feature in Oracle Database that provides high availability.

TAF enables an application connection to automatically reconnect to another database instance if the connection gets severed. Active transactions roll back, but the new database connection, made by way of a different node, is identical to the original. This is true regardless of how the connection fails.

With TAF, a client notices no loss of connection as long as there is one instance left serving the application. The database administrator controls which applications run on which instances, and also creates a failover order for each application.

When a session fails over to another database, the NLS settings that were initially set on the original session are not carried over to the new session. Therefore, it is the responsibility of the application to set these NLS settings on the new session.

// C#
 
using System;
using Oracle.DataAccess.Client; 
 
class TAFCallBackSample
{
  public static FailoverReturnCode OnFailover(object sender, 
                                              OracleFailoverEventArgs eventArgs)
  {
    switch (eventArgs.FailoverEvent)
    {
      case FailoverEvent.Begin :
        Console.WriteLine(
          " \nFailover Begin - Failing Over ... Please standby \n");
        Console.WriteLine(
          " Failover type was found to be " + eventArgs.FailoverType);
        break;
 
      case FailoverEvent.Abort :
        Console.WriteLine(" Failover aborted. Failover will not take place.\n");
        break;
 
      case FailoverEvent.End :
        Console.WriteLine(" Failover ended ...resuming services\n");
        break;
 
      case FailoverEvent.Reauth :
        Console.WriteLine(" Failed over user. Resuming services\n");
        break;
 
      case FailoverEvent.Error :
        Console.WriteLine(" Failover error gotten. Sleeping...\n");
        return FailoverReturnCode.Retry;
 
      default :
        Console.WriteLine("Bad Failover Event: %d.\n", eventArgs.FailoverEvent);
        break;
    }
    return FailoverReturnCode.Success;
  } /* OnFailover */
 
  static void Main()
  {
    OracleConnection con = new OracleConnection();
 
    con.ConnectionString = "User Id=scott;Password=tiger;Data Source=oracle;";
    con.Open();
    con.Failover += new OracleFailoverEventHandler(OnFailover);
    Console.WriteLine("Event Handler is successfully registered");
 
    // Close and Dispose OracleConnection object
    con.Close();
    con.Dispose();
  }
}