| Oracle® Data Provider for .NET Developer's Guide 12c Release 1 (12.1.0.1.0) Part Number E41125-02 |
|
|
PDF · Mobi · ePub |
| Oracle® Data Provider for .NET Developer's Guide 12c Release 1 (12.1.0.1.0) Part Number E41125-02 |
|
|
PDF · Mobi · ePub |
Oracle Data Provider for .NET can connect to Oracle Database in a number of ways, such as using a user name and password, Windows Native Authentication, Kerberos, and Secure Sockets Layer (SSL). This section describes OracleConnection provider-specific features, including:
Table 3-1 lists the supported connection string attributes.
Table 3-1 Supported Connection String Attributes
| Connection String Attribute | Description | Default Value |
|---|---|---|
|
|
Minimum life time (in seconds) of the connection. |
|
|
|
Minimum time (in seconds) to wait for a free connection from the pool. |
|
|
|
Returns an implicit database connection if set to Supported in a .NET stored procedure only |
|
|
|
Oracle Net Services Name, Connect Descriptor, or an easy connect naming that identifies the database to which to connect. |
empty string |
|
|
Administrative privileges: |
empty string |
|
|
Number of connections that are closed when an excessive amount of established connections are unused. |
|
|
|
Controls the enlistment behavior and capabilities of a connection in context of COM+ transactions or |
|
|
|
Enables ODP.NET connection pool to proactively remove connections from the pool when an Oracle RAC service, service member, database, or node goes down. Works with Oracle RAC, Data Guard, or a single database instance. |
|
|
|
Enables ODP.NET connection pool to balance work requests across Oracle RAC instances based on the load balancing advisory and service goal. |
|
|
|
Number of new connections to be created when all connections in the pool are in use. |
|
|
|
Maximum number of connections in a pool. |
|
|
|
Caches metadata information. |
|
|
|
Minimum number of connections in a pool. |
|
|
|
Password for the user specified by |
empty string |
|
|
Retrieval of the password in the connection string. |
|
|
|
Connection pooling. |
|
|
|
Indicates whether or not a transaction is local or distributed throughout its lifetime. |
promotable |
|
|
User name of the proxy user. |
empty string |
|
|
Password of the proxy user. |
empty string |
|
|
Enables or disables self-tuning for a connection. |
|
|
|
Statement cache purged when the connection goes back to the pool. |
|
|
|
Statement cache enabled and cache size, that is, the maximum number of statements that can be cached. |
|
|
|
Oracle user name. |
empty string |
|
|
Validation of connections coming from the pool. |
|
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");
}
}
See Also:
"OracleConnection Properties" for detailed information on connection attributes
"OracleCommand Object" for detailed information on statement caching
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)))
To connect as scott/tiger using the TNS Alias, a valid connection appears as follows:
"user id=scott;password=tiger;data source=sales";
ODP.NET also allows applications to connect without the use of the tnsnames.ora file. To do so, the entire connect descriptor can be used as the "data source".
The connection string appears as follows:
"user id=scott;password=tiger;data source=" +
"(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)" +
"(HOST=sales-server)(PORT=1521))(CONNECT_DATA="+
"(SERVICE_NAME=sales.us.acme.com)))"
The easy connect naming method enables clients to connect to a database without any configuration.
Prior to using the easy connect naming method, make sure that EZCONNECT is specified by the NAMES.DIRECTORY_PATH parameter in the sqlnet.ora file as follows:
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
With this enabled, ODP.NET allows applications to specify the "Data Source" attribute in the form of:
//host:[port]/[service_name]
Using the same example, some valid connection strings follow:
"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"
If the port number is not specified, 1521 is used by default.
See Also:
Oracle Database Net Services Administrator's Guide for details and requirements in the section Using Easy Connect Naming MethodODP.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
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.");
}
}
When connection pooling is enabled (the default), the Open and Close methods of the OracleConnection object implicitly use the connection pooling service, which is responsible for pooling and returning connections to the application.
The connection pooling service creates connection pools by using the ConnectionString property as a signature, to uniquely identify a pool.
If there is no existing pool with the exact attribute values as the ConnectionString property, the connection pooling service creates a new connection pool. If a pool already exists with the requested signature, a connection is returned to the application from that pool.
When a connection pool is created, the connection pooling service initially creates the number of connections defined by the Min Pool Size attribute of the ConnectionString property. This number of connections is always maintained by the connection pooling service for the connection pool.
At any given time, these connections are in use by the application or are available in the pool.
The Incr Pool Size attribute of the ConnectionString property defines the number of new connections to be created by the connection pooling service when more connections are needed in the connection pool.
When the application closes a connection, the connection pooling service determines whether or not the connection lifetime has exceeded the value of the Connection Lifetime attribute. If so, the connection pooling service closes the connection; otherwise, the connection goes back to the connection pool. The connection pooling service enforces the Connection Lifetime only when a connection is going back to the connection pool.
The Max Pool Size attribute of the ConnectionString property sets the maximum number of connections for a connection pool. If a new connection is requested, but no connections are available and the limit for Max Pool Size has been reached, then the connection pooling service waits for the time defined by the Connection Timeout attribute. If the Connection Timeout time has been reached, and there are still no connections available in the pool, the connection pooling service raises an exception indicating that the connection pool request has timed-out.
The Validate Connection attribute validates connections coming out of the pool. This attribute should be used only when absolutely necessary, because it causes a round-trip to the database to validate each connection immediately before it is provided to the application. If invalid connections are uncommon, developers can create their own event handler to retrieve and validate a new connection, rather than using the Validate Connection attribute. This generally provides better performance.
The connection pooling service closes connections when they are not used; connections are closed every 3 minutes. The Decr Pool Size attribute of the ConnectionString property provides connection pooling service for the maximum number of connections that can be closed every 3 minutes.
Beginning with Oracle Data Provider for .NET release 11.1.0.6.20, enabling connection pooling by setting "pooling=true" in the connection string (which is the case by default) will also pool operating system authenticated connections.
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:
Note:
These APIs are not supported in a .NET stored procedure.Clear connections from connection pools using the ClearPool method.
Clear connections in all the connection pools in an application domain, using the ClearAllPools method.
See Also:
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.
Table 3-2 Performance Counters for Connection Pooling
| Performance Counter | Valid Values | Description |
|---|---|---|
|
None |
|
Not enabled (Default) |
|
|
|
Number of sessions being established with the Oracle Database every second. |
|
|
|
Number of sessions being severed from the Oracle Database every second. |
|
|
|
Number of active connections originating from connection pools every second. |
|
|
|
Number of active connections going back to the connection pool every second. |
|
|
|
Total number of active connection pools. |
|
|
|
Number of inactive connection pools. |
|
|
|
Total number of connections in use. |
|
|
|
Total number of connections available for use in all the connection pools. |
|
|
|
Number of pooled active connections. |
|
|
|
Number of non-pooled active connections. |
|
|
|
Number of connections which were garbage-collected implicitly. |
|
|
|
Number of connections that will be soon available in the pool. User has closed these connections, but they are currently awaiting actions such transaction completion before they can be placed back into the pool as free connections. |
Publication of individual performance counters is enabled or disabled using the registry value PerformanceCounters of type REG_SZ. This registry value is under:
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ODP.NET\Assembly_Version
where Assembly_Version is the full assembly version number of Oracle.DataAccess.dll.
Multiple performance counters can be obtained by adding the valid values. For example, if PerformanceCounters is set to 3, both HardConnectsPerSecond and HardDisconnectsPerSecond are enabled.
Performance counters can be set using an app.config entry. Since app.config entries take precedence over the registry value setting, they can be used for a specific application.
An app.config entry uses name/value pairs as in the following example:
<configuration>
<oracle.dataaccess.client>
<settings>
<add name="PerformanceCounters"
value="3"/>
</settings>
</oracle.dataaccess.client>
</configuration>
Performance counters are published instance-wise, that is, for each process, different values of the performance counters are published. The instance name is based on AppDomain name, AppDomain Id and Process Id and displayed in the following form:
AppDomain_Name[Process Id, AppDomain Id]
For example, if a process named App1.exe uses ODP.NET 2.x in default appdomain and the process id is 234 then the instance name would be App1.exe [234, 1].
Oracle Database 12c introduced a new feature, Pluggable Databases, which enable an Oracle database to contain a portable collection of schemas, schema objects, and nonschema objects that appears to ODP.NET as a separate database. This self-contained collection is called a pluggable database (PDB).
ODP.NET 12c and higher can connect to PDBs, which clients access through database services. Database services have an optional PDB property. When a PDB is created, a new default database service is created automatically. The service has the same name as the PDB and can be used to access the PDB using the easy connect syntax or the net service name. This service is intended primarily for performing administrative tasks. It is recommended that you create additional services for use in your applications.
All ODP.NET features can be used with PDBs with the following exceptions:
Continuous Query Notification
Client Result Cache
Fast Application Notification events using Oracle Notification Service
Switching from one PDB to another PDB using the ALTER SESSION SET CONTAINER statement
See Also:
Managing Pluggable Databases in Oracle Database Administrator's GuideEdition-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.
See Also:
For more information on Editions refer to the Oracle Database Administrator's Guide and Oracle Database Development GuideODP.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>
Note:
ODP.NET only supports deployment-time configuration of Edition.
ODP.NET does not support usage of the "ALTER SESSION" statement to modify the Edition during the lifetime of a process.
ODP.NET, Managed Driver does not support Edition-Based Redefinition.
This section discusses optimizations for the following products:
Oracle Real Application Clusters (Oracle RAC) is a cluster database with a shared cache architecture that overcomes the limitations of traditional shared-nothing and shared-disk approaches to provide highly scalable and available database solutions for business applications. See Oracle Real Application Clusters Administration and Deployment Guide.
Oracle Data Guard provides one or more standby databases to protect Oracle data from failures, disasters, human error, and data corruptions for high availability in mission critical applications. See Oracle Data Guard Concepts and Administration.
Oracle GoldenGate replicates data among heterogeneous data environments. It enables high availability solutions, real-time data integration, transactional change data capture, data replication, transformations, and verification between operational and analytical enterprise systems. See Oracle Database High Availability Overview.
Global Data Services (GDS), new in Oracle Database 12c, provides database workload management features across replicated databases, such as Data Guard and GoldenGate.
ODP.NET supports Oracle Real Application Clusters (Oracle RAC), Data Guard, and GoldenGate transparently, meaning you do not need to change ODP.NET code to use these Oracle components. To further take advantage of these technologies, ODP.NET offers connection pooling optimization features for achieving better application high availability and performance. You can do this through configuring ODP.NET to receive, respond, and send database status messages to .NET applications.
These optimization configurations include the use of features such as Fast Application Notification (FAN), Runtime Connection Load Balancing, and Fast Connection Failover (FCF).
These connection pooling optimizations can improve high availability and performance for Oracle Real Application Clusters and Global Data Services products:
Fast Application Notification (FAN) is a high availability and load balancing notification mechanism that Oracle RAC, Data Guard, and GoldenGate use to notify ODP.NET applications about cluster configuration and service-level information, including status changes such as UP or DOWN events and server load. FAN UP and DOWN events can apply to instances, services, and nodes. Based on information received, ODP.NET can adjust its connection pool accordingly to improve application availability and performance. See Oracle Real Application Clusters Administration and Deployment Guide for more information about FAN.
With FAN, Oracle RAC, Data Guard, and GoldenGate use one of two Oracle messaging infrastructures to send notifications to ODP.NET applications:
Oracle Notification Service (ONS)
Oracle Streams Advanced Queueing (AQ).
Table 3-3 describes when each messaging system is used and the ODP.NET-related client configuration.
Table 3-3 Configurations for ODP.NET Driver Types
| ODP.NET Driver Type | Database Server Version | FAN Infrastructure | Configuration | Manual ONS Configuration Locations |
|---|---|---|---|---|
|
managed |
12.1 and later |
ONS |
Automatic or Manual |
Either of these two files:
|
|
managed |
11.2 and earlier |
ONS |
Manual |
Either of these two files:
|
|
unmanaged |
12.1 and later |
ONS |
Automatic or Manual |
|
|
unmanaged |
11.2 and earlier |
AQ |
Automatic |
N/A |
To configure the .NET configuration file, see "onsConfig section". To configure the ONS configuration file, see "Client Side ONS Daemon Configuration". To configure oraaccess.xml, see Oracle Call Interface Programmer's Guide."
See "Configuring a Port to Listen for Database Notifications" for more information on AQ configuration for FAN.
For automatic ONS configuration, developers can add more nodes and ports for ODP.NET to listen to, in addition to the nodes and ports that ODP.NET obtains from the database automatically.
ODP.NET applications do not require code changes to migrate from the AQ to ONS FAN infrastructure. However, some ODP.NET client configuration changes may be necessary when migrating to ONS, a newer database server version, or from ODP.NET, Unmanaged Driver to the managed driver, as documented above.
On the database server side, FAN must be set up and configured.
Using FAN Messages from the database, ODP.NET can do the following:
With Runtime Connection Load Balancing, ODP.NET load balances connections among Oracle RAC nodes, services, and service members and GDS resources. This feature improves ODP.NET response time and ensures better resource allocation of server resources. See "Runtime Connection Load Balancing".
With the Fast Connection Failover (FCF) feature, Oracle RAC, Data Guard, and GoldenGate can inform the ODP.NET connection pool if database nodes, services, service members, or the databases have gone down. These DOWN messages indicate which connections in the pool are invalid and must be removed. See Oracle Database Development Guide.
With Runtime Connection Load Balancing, Oracle Data Provider for .NET balances work requests across Oracle RAC instances based on the load balancing advisory and service goal. Because workloads can constantly change, load balancing occurs when the application requests a new connection. Thus, ODP.NET optimizes service levels by connecting users to the least loaded nodes in real-time.
In Oracle Database 12c, Runtime Connection Load Balancing has been extended to Oracle Data Guard and Oracle GoldenGate so that ODP.NET 12c connections can be load balanced with these two database services as part of Global Data Services. No ODP.NET applications require code changes to use Global Data Services if they are already using Runtime Connection Load Balancing.
When Runtime Connection Load Balancing is enabled:
The ODP.NET connection pool dispenses connections based on the load balancing advisory and service goal.
The ODP.NET connection pool also balances the number of connections to each service member providing the service, based on the load balancing advisory and service goal.
By default, this feature is disabled. To enable runtime connection load balancing, include "Load Balancing=true" in the connection string. This feature can only be used if "pooling=true". If "Load Balancing=true" is set and the connection attempts to connect to a single-instance database, an OracleException is thrown with an error of "ORA-1031: insufficient privileges." In order to use Runtime Connection Load Balancing, specific Oracle server configurations must be set.
The following connection string example enables Runtime Connection Load Balancing:
"user id=scott;password=tiger;data source=erp;load balancing=true;"
See Also:
Oracle Database Net Services Administrator's Guide to set up the Oracle Net configuration that Runtime Connection Load Balancing requires
Oracle Real Application Clusters Administration and Deployment Guide for the required Oracle RAC configuration
Oracle Database Global Data Services Concepts and Administration Guide
When an Oracle RAC service, service member, node, or a Data Guard database fails, the severed ODP.NET connection objects may continue to exist in the application. If users attempt to use these invalid connections, they will encounter errors. FCF enables ODP.NET to free these severed connections proactively and quickly. Users then will be able to use the application after a server side failure without manual intervention from an administrator.
In Oracle Database 12c, FCF has been extended to Oracle Data Guard and Oracle GoldenGate for ODP.NET 12c connections through Global Data Services. No ODP.NET applications require code changes to use Global Data Services if they already use FCF.
ODP.NET applications can enable FCF through the High Availability Events, "HA Events", connection string attribute. When HA Events are enabled:
ODP.NET connection pool proactively removes connections from the pool when a Global Data Service or Oracle RAC service, service member, node, or database goes down.
ODP.NET proactively forces threads waiting for responses from the downed database to exit out from the existing call to avoid any hangs. When such a connection is then returned to the pool, any resource associated with that connection is freed.
ODP.NET establishes connections to existing Oracle instances if the removal of severed connections brings the total number of connections below the "min pool size", upon the next connection request.
By default this feature is disabled. To enable HA events, include "HA Events=true" and "pooling=true" in the connection string.
The following connection string example enables HA Events:
"user id=scott;password=tiger;data source=erp;HA events=true;"
See Also:
Oracle Database Net Services Administrator's Guide to set up the Oracle Net configuration that FCF requires
Oracle Real Application Clusters Administration and Deployment Guide for the required Oracle RAC configuration
Oracle Database Global Data Services Concepts and Administration Guide
FCF not only provides high availability services for unplanned outages, such as node failures, but also for planned outages, such as server repairs, upgrades, and changes, to minimize service disruption to ODP.NET application users.
When a database service is set to be stopped or relocated, a FAN message is published with a planned reason code. A FCF-aware ODP.NET connection pool ("HA Events=true") receives the notification and commences to close idle connections, no longer allowing new connections to that specific database service. Active connections to that specific database service remain until users complete their tasks and the connection is returned to the pool. Thus, no users must stop work mid-stream due to a planned outage.
Eventually, all users complete their tasks and no connections remain to that database service. The database administrator can then stop the service for the planned outage task. This feature allows the database service to be stopped as quickly as possible without end user disruption.
Note:
ODP.NET, Unmanaged Driver supports FAN Planned Outage. ODP.NET, Managed Driver currently does not.A typical planned outage scenario is as follows:
There is a need to upgrade, patch, or repair a software or hardware issue on a database server. Relocate the service from the instance, then shut down the service on the instance.
The FAN planned DOWN event clears idle sessions from the ODP.NET connection pool immediately and marks active sessions to be released at the next check-in. These FAN actions drain the sessions from this instance without disrupting the users.
Existing connections on other instances remain usable, and new connections can be opened to these other instances.
Not all sessions, in all cases, will check their connections into the pool. It is best practice to have a timeout period after which the instance is forcibly shut down, evicting any remaining client connections.
Once the upgrade, patch, or repair is complete, restart the instance and the service on the original node. ODP.NET can now use the instance again.
When connection pools are created for a single-instance database, pool size attributes are applied to the single service. Similarly, when connection pools are created for an Oracle RAC database, the pool size attributes are applied to a service and not to service members. For example, if "Min Pool Size" is set to N, ODP.NET does not create N connections for each service member. Instead, it creates, at minimum, N connections for the entire service, where N connections are distributed among the service members.
The following pool size connection string attributes are applied to a service.
Min Pool Size
Max Pool Size
Incr Pool Size
Decr Pool Size
Transaction Guard allows ODP.NET applications to use at-most-once execution in case of planned and unplanned outages and repeated submissions.
Starting with Oracle Database 12c, ODP.NET 12c returns a logical transaction ID (LTXID) that can be used to determine the outcome of the last open transaction in a database session following an outage. Without Transaction Guard, applications that attempt to retry operations following outages can cause logical corruption by committing duplicate transactions.
After an outage, one of the traditional problems for recovering applications had been the non-durable commit message sent back to the client. If there is a break between the client and the server, the client sees an error message indicating that the communication failed (also known as a recoverable error). This error does not inform the application if the submission executed any commit operations, or if a procedural call ran to completion while executing all expected commits. The error also does not indicate session state changes or intermittent failures. Worse yet, the application may still be running disconnected from the client.
These recoverable errors can cause end users or applications to attempt replay by issuing duplicate transaction submissions or other forms of logical corruption. The transaction cannot be validly resubmitted if the non-transactional state is incorrect or if it is committed. Continuing to process a committed but not completed call can result in the application using a database session that is in the wrong state.
ODP.NET 12c support for Transaction Guard allows applications to eliminate duplicate transactions automatically and transparently, and in a manner that scales. Applications use LTXID to eliminate these duplicates. At runtime, a LTXID is automatically held in the session at both the client and server for each database transaction. At commit, the LTXID persists as part of the transaction commitment. The database maintains the LTXID post-commit for as long as the administrator has set the retention period. The application can then request the next LTXID for the next transaction.
When a failure occurs, such as a node, network, or database failure, ODP.NET applications can deterministically conclude whether the transaction committed by querying its status using the LTXID. If not committed, the ODP.NET application can learn what state the transaction is in and whether it is recoverable and can be retried through the returned OracleException. ODP.NET applications can then take the appropriate action, such as resubmitting the transaction if it is not committed.
Note:
ODP.NET, Unmanaged Driver supports Transaction Guard and recoverable error detection. ODP.NET, Managed Driver currently does not support either.
Transaction Guard supports only local transactions. It does not support distributed transactions.
The Transaction Guard feature is enabled or disabled through the Oracle service-level configuration through the COMMIT_OUTCOME setting. By default, it is not enabled. This setting can be changed without bringing down the database. Only new connections created against the service will use the new setting.
The following is an example ODP.NET Transaction Guard application scenario:
An ODP.NET application receives a FAN down event or error. FAN automatically aborts the dead session and the application receives an OracleException. A Transaction Guard application built to handle errors transparently would do the following:
Check the value of the OracleException.IsRecoverable property. If the value is true, that application can chose to re-submit the existing transaction based on the current transaction status as described in the following steps. If the value is false, the application should roll-back, re-execute, and re-submit the current transaction.
The application retrieves the last LTXID from the failed session.
OracleConnection.LogicalTransactionId
The application obtains a new session.
The application retrieves the transaction status, OracleLogicalTransactionStatus, using the LTXID with the call OracleConnection.GetLogicalTransactionStatus(byte[] ltxid).
Note:
Grant theEXECUTE privilege on the DBMS_APP_CONT package to the database users that retrieve the transaction status using the LTXID.The LTXID transaction status indicates two aspects of the outcome of the transaction:
Did it commit successfully? OracleLogicalTransactionStatus.Committed
Did it complete successfully? OracleLogicalTransactionStatus.UserCallCompleted
| Committed Value | UserCallCompleted Value | Outcome |
|---|---|---|
True |
True |
The transaction was successful. The result can be returned to the application. |
False |
False |
The transaction was not successful. The application can resubmit the transaction again. |
True |
False |
The transaction committed, but there may be additional state, such as row counts or nested PL/SQL logic, that prevents the application from continuing as expected. |
using System;
using Oracle.DataAccess.Client;
class TransactionGuardDemo
{
static void Main(string[] args)
{
// Establish a connection to an Oracle database for the transaction
string txnConStr = "user id=hr;password=hr;data source=oracle";
OracleConnection txnCon = new OracleConnection(txnConStr);
txnCon.Open();
// Begin a local transaction
OracleTransaction txn = txnCon.BeginTransaction();
// Do transactional work
OracleCommand cmd = new OracleCommand(
"update employees set salary=salary+10 where employee_id=100",
txnCon);
cmd.ExecuteNonQuery();
try
{
// Commit the transaction
txn.Commit();
}
catch
{
// Obtain the logical transaction id
byte[] logicalTransactionId = txnCon.LogicalTransactionId;
if (logicalTransactionId != null)
{
// Establish a connection to an Oracle database
// for checking the status
string statusConStr = "user id=hr;password=hr;data source=oracle";
OracleConnection statusCon = new OracleConnection(statusConStr);
statusCon.Open();
// Obtain the transaction status for the
// specified logical transaction id
OracleLogicalTransactionStatus status =
statusCon.GetLogicalTransactionStatus(logicalTransactionId);
if (status != null)
{
// Output the status of the logical transaction
Console.WriteLine("Has the transaction been committed? " +
status.Committed);
Console.WriteLine("Has the user call been completed? " +
status.UserCallCompleted);
}
}
}
}
}
See Also:
Oracle Database Development Guide for more information on Transaction Guard
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.
Note:
Operating System Authentication is not supported in a .NET stored procedure.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");
}
}
See Also:
Oracle Database Platform Guide for Microsoft Windows for information on how to set up Oracle Database to authenticate database users using Windows user login credentialsOracle 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");
}
}
See Also:
DBA Privilege "Supported Connection String Attributes" for further information on privileged connections in the databaseOracle 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");
}
}
}
Note:
The OpenWithNewPassword method should be used only when the user password has expired, not for changing the password.
If connection pooling is enabled, then invoking the OpenWithNewPassword method also clears the connection pool. This closes all idle connections created with the old password.
See Also:
"OpenWithNewPassword"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");
}
}
See Also:
Oracle Database Development Guide for details on designing a middle-tier server using proxy users
Oracle Database SQL Language Reference for the description and syntax of the proxy clause for the ALTER USER statement
Oracle Database Security Guide section "Standard Auditing in a Multitier Environment"
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.
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
See Also:
"End-to-End Application Tracing" in the Oracle Database Performance Tuning Guide
Transparent Application Failover (TAF) is a feature in Oracle Database that provides high availability.
Note:
ODP.NET, Managed Driver does not support TAF nor TAF callbacks.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.
Given the delays that failovers can cause, applications may wish to be notified by a TAF callback. ODP.NET supports the TAF callback function through the Failover event of the OracleConnection object, which allows applications to be notified whenever a failover occurs. To receive TAF callbacks, an event handler function must be registered with the Failover event.
When a failover occurs, the Failover event is raised and the registered event handler is invoked several times during the course of reestablishing the connection to another Oracle instance.
The first call to the event handler occurs when Oracle Database First detects an instance connection loss. This allows the application to act accordingly for the upcoming delay for the failover.
If the failover is successful, the Failover event is raised again when the connection is reestablished and usable. At this time, the application can resynchronize the OracleGlobalization session setting and inform the application user that a failover has occurred.
If failover is unsuccessful, the Failover event is raised to inform the application that a failover did not take place.
The application can determine whether or not the failover is successful by checking the OracleFailoverEventArgs object that is passed to the event handler.
The following example registers an event handler method called OnFailover:
// 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();
}
}
The Failover event invokes only one event handler. If multiple Failover event handlers are registered with the Failover event, only the event handler registered last is invoked.
Note:
Distributed transactions are not supported in an environment where failover is enabled.
|
 Copyright © 2002, 2013, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|