You can use OCI to access Oracle TimesTen In-Memory Database and Oracle TimesTen Application-Tier Database Cache. See Oracle TimesTen In-Memory Database C Developers Guide for information about Times Ten support for Oracle Call Interface.
Topics
This section describes the following topic:About Continuous Query Notification.
Continuous Query Notification (CQN) enables client applications to register queries with the database and receive notifications in response to DML or DDL changes on the objects or in response to result set changes associated with the queries. The notifications are published by the database when the DML or DDL transaction commits.
During registration, the application specifies a notification handler and associates a set of interesting queries with the notification handler. A notification handler can be either a server-side PL/SQL procedure or a client-side C callback. Registrations are created at either the object level or the query level. If registration is at the object level, then whenever a transaction changes any of the registered objects and commits, the notification handler is invoked. If registration is at the query level, then whenever a transaction commits changes such that the result set of the query is modified, the notification handler is invoked, but if the changes do not affect the result set of the query, the notification handler is not invoked.
See Also:
Oracle Database Development Guide, "Using Continuous Query Notification" for a complete discussion of the concepts of this feature and using OCI and PL/SQL interfaces to create CQN registrations
One use of continuous query notification is in middle-tier applications that must have cached data and keep the cache as recent as possible for the back-end database.
The notification includes the following information:
Query IDs of queries whose result sets have changed. This is if the registration was at query granularity.
Names of the modified objects or changed rows.
Operation type (INSERT, UPDATE, DELETE, ALTER TABLE, DROP TABLE).
ROWIDs of the changed rows and the associated DML operation (INSERT, UPDATE, DELETE).
Global database events (STARTUP, SHUTDOWN). In Oracle Real Application Cluster (Oracle RAC) the database delivers a notification when the first instance starts or the last instance shuts down.
See Also:
Describes topics about database startup and shutdown.
The OCI functions OCIDBStartup() and OCIDBShutdown() provide the minimal interface needed to start and shut down an Oracle database.
Before calling OCIDBStartup(), the C program must connect to the server and start a SYSDBA or SYSOPER session in the preliminary authentication mode. This mode is the only one permitted when the instance is not up, and it is used only to start the instance. A call to OCIDBStartup() starts one server instance without mounting or opening the database. To mount and open the database, end the preliminary authentication session and start a regular SYSDBA or SYSOPER session to execute the appropriate ALTER DATABASE statements.
An active SYSDBA or SYSOPER session is needed to shut down the database. For all modes other than OCI_DBSHUTDOWN_ABORT, make two calls to OCIDBShutdown(): one to initiate shutdown by prohibiting further connections to the database, followed by the appropriate ALTER DATABASE commands to dismount and close it; and the other call to finish shutdown by bringing the instance down. In special circumstances, to shut down the database as fast as possible, call OCIDBShutdown() in the OCI_DBSHUTDOWN_ABORT mode, which is equivalent to SHUTDOWN ABORT in SQL*Plus.
Both of these functions require a dedicated connection to the server. ORA-106 is signaled if an attempt is made to start or shut down the database when it is connected to a shared server through a dispatcher.
The OCIAdmin administration handle C data type is used to make the interface extensible. OCIAdmin is associated with the handle type OCI_HTYPE_ADMIN. Passing a value for the OCIAdmin parameter, admhp, is optional for OCIDBStartup() and is not needed by OCIDBShutdown().
To perform a startup, you must be connected to the database as SYSOPER or SYSDBA in OCI_PRELIM_AUTH mode. You cannot be connected to a shared server through a dispatcher.
To use a client-side parameter file (pfile), the attribute OCI_ATTR_ADMIN_PFILE must be set in the administration handle using OCIAttrSet(); otherwise, a server-side parameter file (spfile) is used. In the latter case, pass (OCIAdmin *)0. A call to OCIDBStartup() starts one instance on the server.
Example 10-1 shows sample code that uses a client-side parameter file (pfile) that is set in the administration handle and performs a database startup operation.
To perform a shutdown, you must be connected to the database as SYSOPER or SYSDBA. You cannot be connected to a shared server through a dispatcher. When shutting down in any mode other than OCI_DBSHUTDOWN_ABORT, use the following procedure:
OCIDBShutdown() in OCI_DEFAULT, OCI_DBSHUTDOWN_TRANSACTIONAL, OCI_DBSHUTDOWN_TRANSACTIONAL_LOCAL, or OCI_DBSHUTDOWN_IMMEDIATE mode to prohibit further connections.ALTER DATABASE commands to close and dismount the database.OCIDBShutdown() in OCI_DBSHUTDOWN_FINAL mode to shut down the instance.Example 10-1 Calling OCIDBStartup() to Perform a Database Startup Operation
...
/* Example 0 - Startup: */
OCIAdmin *admhp;
text *mount_stmt = (text *)"ALTER DATABASE MOUNT";
text *open_stmt = (text *)"ALTER DATABASE OPEN";
text *pfile = (text *)"/ade/viewname/oracle/work/t_init1.ora";
/* Start the authentication session */
checkerr(errhp, OCISessionBegin (svchp, errhp, usrhp,
OCI_CRED_RDBMS, OCI_SYSDBA|OCI_PRELIM_AUTH));
/* Allocate admin handle for OCIDBStartup */
checkerr(errhp, OCIHandleAlloc((void *) envhp, (void **) &admhp,
(ub4) OCI_HTYPE_ADMIN, (size_t) 0, (void **) 0));
/* Set attribute pfile in the admin handle
(do not do this if you want to use the spfile) */
checkerr (errhp, OCIAttrSet( (void *) admhp, (ub4) OCI_HTYPE_ADMIN,
(void *) pfile, (ub4) strlen(pfile),
(ub4) OCI_ATTR_ADMIN_PFILE, (OCIError *) errhp));
/* Start up in NOMOUNT mode */
checkerr(errhp, OCIDBStartup(svchp, errhp, admhp, OCI_DEFAULT, 0));
checkerr(errhp, OCIHandleFree((void *) admhp, (ub4) OCI_HTYPE_ADMIN));
/* End the authentication session */
OCISessionEnd(svchp, errhp, usrhp, (ub4)OCI_DEFAULT);
/* Start the sysdba session */
checkerr(errhp, OCISessionBegin (svchp, errhp, usrhp, OCI_CRED_RDBMS,
OCI_SYSDBA));
/* Mount the database */
checkerr(errhp, OCIStmtPrepare2(svchp, &stmthp, errhp, mount_stmt, (ub4)
strlen((char*) mount_stmt),
(CONST OraText *) 0, (ub4) 0, (ub4) OCI_NTV_SYNTAX, (ub4)
OCI_DEFAULT));
checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4)0,
(OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT));
checkerr(errhp, OCIStmtRelease(stmthp, errhp, (OraText *)0, 0, OCI_DEFAULT));
/* Open the database */
checkerr(errhp, OCIStmtPrepare2(svchp, &stmthp, errhp, open_stmt, (ub4)
strlen((char*) open_stmt),
(CONST OraText *)0, (ub4)0, (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4)0,
(OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT));
checkerr(errhp, OCIStmtRelease(stmthp, errhp, (OraText *)0, 0, OCI_DEFAULT));
/* End the sysdba session */
OCISessionEnd(svchp, errhp, usrhp, (ub4)OCI_DEFAULT);
...
Example 10-2 Calling OCIDBShutdown() in OCI_DBSHUTDOWN_FINAL Mode
/* Example 1 - Orderly shutdown: */
...
text *close_stmt = (text *)"ALTER DATABASE CLOSE NORMAL";
text *dismount_stmt = (text *)"ALTER DATABASE DISMOUNT";
/* Start the sysdba session */
checkerr(errhp, OCISessionBegin (svchp, errhp, usrhp, OCI_CRED_RDBMS,
OCI_SYSDBA));
/* Shutdown in the default mode (transactional, transactional-local,
immediate would be fine too) */
checkerr(errhp, OCIDBShutdown(svchp, errhp, (OCIAdmin *)0, OCI_DEFAULT));
/* Close the database */
checkerr(errhp, OCIStmtPrepare2(svchp, &stmthp, errhp, close_stmt, (ub4)
strlen((char*) close_stmt),
(CONST OraText *)0, (ub4)0, (ub4) OCI_NTV_SYNTAX,
(ub4) OCI_DEFAULT));
checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4)0,
(OCISnapshot *) NULL,
(OCISnapshot *) NULL, OCI_DEFAULT));
checkerr(errhp, OCIStmtRelease(stmthp, errhp, (OraText *)0, 0, OCI_DEFAULT));
/* Dismount the database */
checkerr(errhp, OCIStmtPrepare2(svchp, &stmthp, errhp, dismount_stmt,
(ub4) strlen((char*) dismount_stmt), (CONST OraText *)0, (ub4)0,
(ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4)0,
(OCISnapshot *) NULL,
(OCISnapshot *) NULL, OCI_DEFAULT));
checkerr(errhp, OCIStmtRelease(stmthp, errhp, (OraText *)0, 0, OCI_DEFAULT));
/* Final shutdown */
checkerr(errhp, OCIDBShutdown(svchp, errhp, (OCIAdmin *)0,
OCI_DBSHUTDOWN_FINAL));
/* End the sysdba session */
checkerr(errhp, OCISessionEnd(svchp, errhp, usrhp, (ub4)OCI_DEFAULT));
...
Example 10-3 Calling OCIDBShutdown() in OCI_DBSHUTDOWN_ABORT Mode
/* Example 2 - Shutdown using abort: */
...
/* Start the sysdba session */
...
checkerr(errhp, OCISessionBegin (svchp, errhp, usrhp, OCI_CRED_RDBMS,
OCI_SYSDBA));
/* Shutdown in the abort mode */
checkerr(errhp, OCIDBShutdown(svchp, errhp, (OCIAdmin *)0,
OCI_DBSHUTDOWN_ABORT));
/* End the sysdba session */
checkerr(errhp, OCISessionEnd(svchp, errhp, usrhp, (ub4)OCI_DEFAULT));
...
Example 10-2 shows sample code that uses a client-side parameter file (pfile) that is set in the administration handle that performs an orderly database shutdown operation.
Example 10-3 shows a shutdown example that uses OCI_DBSHUTDOWN_ABORT mode.
See Also:
This section describes the following topics:
ROWID is a globally unique identifier for a row in a database. It is created at the time the row is inserted into the table, and destroyed when it is removed. ROWID values have several important uses. They are unique identifiers for rows in a table. They are the fastest way to access a single row and can show how the rows in the table are stored.
Implicit fetching of ROWIDs in SELECT ... FOR UPDATE statements means that the ROWID is retrieved at the client side, even if it is not one of the columns named in the select statement. The position parameter of OCIDefineByPos() is set to zero (0). These host variables can be specified for retrieving the ROWID pseudocolumn values:
SQLT_CHR (VARCHAR2)
SQLT_VCS (VARCHAR)
SQLT_STR (NULL-terminated string)
SQLT_LVC (LONG VARCHAR)
SQLT_AFC (CHAR)
SQLT_AVC (CHARZ)
SQLT_VST (OCI String)
SQLT_RDD (ROWID descriptor)
The SELECT ... FOR UPDATE statement identifies the rows that are to be updated and then locks each row in the result set. This is useful when you want to base an update on the existing values in a row. In that case, you must ensure that another user does not change the row.
When you specify character buffers for storing the values of the ROWIDs (for example, if getting it in SQLT_STR format), allocate enough memory for storing ROWIDs. Remember the differences between the ROWID data type and the UROWID data type. The ROWID data type can only store physical ROWIDs, but UROWID can store logical ROWIDs (identifiers for the rows of index-organized tables) as well. The maximum internal length for the ROWID type is 10 bytes; it is 3950 bytes for the UROWID data type.
Dynamic define is equivalent to calling OCIDefineByPos() with mode set as OCI_DYNAMIC_FETCH. Dynamic defines enable you to set up additional attributes for a particular define handle. It specifies a callback function that is invoked at runtime to get a pointer to the buffer into which the fetched data or a piece of it is to be retrieved.
The attribute OCI_ATTR_FETCH_ROWID must be set on the statement handle before you can use implicit fetching of ROWIDs, in this way:
OCIAttrSet(stmthp, OCI_HTYPE_STMT, 0, 0 , OCI_ATTR_FETCH_ROWID, errhp);
Dynamic define is not compatible with implicit fetching of ROWIDs. In normal scenarios this mode allows the application to provide buffers for a column, for each row; that is, a callback is invoked every time a column value is fetched.
This feature, using OCIDefineByPos() for position 0, is for fetching an array of data simultaneously into the user buffers and getting their respective ROWIDs at the same time. It allows for fetching of ROWIDs with SELECT....FOR UPDATE statements even when ROWID is not one of the columns in the SELECT query. When fetching the data one by one into the user buffers, you can use the existing attribute OCI_ATTR_ROWID.
If you use this feature to fetch the ROWIDs, the attribute OCI_ATTR_ROWID on the statement handle cannot be used simultaneously to get the ROWIDs. You can only use one of them at a time for a particular statement handle.
See Also:
Use the fragment of a C program in Example 10-4 to build upon.
Example 10-4 Implicit Fetching of ROWIDs
#include <oci.h>
int main()
{
...
text *mySql = (text *) "SELECT emp_name FROM emp FOR UPDATE";
text rowid[100][15] = {0};
text empName[100][15] = {0};
...
/* Set up the environment, error handle, etc. */
...
/* Prepare the statement - select ... for update. */
if (OCIStmtPrepare (select_p, errhp,
mySql, strlen(mySql), OCI_NTV_SYNTAX, OCI_DEFAULT))
{
printf ("Prepare failed \n");
return (OCI_ERROR);
}
/* Set attribute for implicit fetching of ROWIDs on the statement handle. */
if (OCIAttrSet(select_p, OCI_HTYPE_STMT, 0, 0, OCI_ATTR_FETCH_ROWID, errhp))
{
printf ("Unable to set the attribute - OCI_ATTR_FETCH_ROWID \n");
return OCI_ERROR;
}
/*
* Define the positions: 0 for getting ROWIDs and other positions
* to fetch other columns.
* Also, get the define conversion done implicitly by fetching
* the ROWIDs in the string format.
*/
if (OCIDefineByPos ( select_p,
&defnp0,
errhp,
0,
rowid[0],
15,
SQLT_STR,
(void *) ind,
(void *) 0,
(void *) 0,
OCI_DEFAULT) ||
OCIDefineByPos(select_p,
&defnp1,
errhp,
1,
empName[0],
15,
SQLT_STR,
(void *) 0,
(void *) 0,
(void *) 0,
OCI_DEFAULT)
)
{
printf ("Failed to define\n");
return (OCI_ERROR);
}
/* Execute the statement. */
if (errr = OCIStmtExecute(svchp,
select_p,
errhp,
(ub4) 5,
(ub4) 0,
(OCISnapshot *) NULL,
(OCISnapshot *) NULL,
(ub4) OCI_DEFAULT))
{
if (errr != OCI_NO_DATA)
return errr;
}
printf ("Column 0 \t Column 1\n");
printf ("_________ \t ________\n");
for (i =0 ;i<5 i++)
{
printf("%s \t %s \n", rowid[i], empName[i]);
}
return OCI_SUCCESS;
}
Beginning with Oracle Database 12c Release 1 (12.1) , PL/SQL can return results (cursors) implicitly from stored procedures and anonymous PL/SQL blocks. OCIStmtGetNextResult() is provided to retrieve and process the implicit results.
PL/SQL provides a subprogram RETURN_RESULT in the DBMS_SQL package to return the result of an executed statement as shown in Example 10-5. In the current release, only SELECT query result-sets can be implicitly returned by a PL/SQL procedure block. OCIStmtGetNextResult() returns an OCI statement handle on which the usual OCI define and fetch calls are done to retrieve the rows.
Example 10-6 shows a PL/SQL stored procedure to implicitly return result-sets (cursors) to the client.
Example 10-7 shows the same approach using an anonymous PL/SQL block sent by the client. This example shows how applications can use the implicit results feature to implement batching of SQL statements from an OCI application. An OCI application can dynamically form a PL/SQL anonymous block to execute multiple and variable SELECT statements and return the corresponding cursors using DBMS_SQL.RETURN_RESULT.
Example 10-8 lists an OCI program showing how to use the OCIStmtGetNextResult() call to retrieve and process the implicit results returned by a PL/SQL stored procedure (see Example 10-6) or an anonymous PL/SQL block (see Example 10-7).
OCIStmtGetNextResult() can be called iteratively by the application to retrieve each implicit result from an executed PL/SQL statement. Applications retrieve each result-set sequentially but can fetch rows from any result-set independently. The top-level OCI statement handle tracks all the associated result-set statement handles. Freeing or releasing the top-level OCI statement handle automatically closes and frees all the implicit result-sets.
The attribute OCI_ATTR_IMPLICIT_RESULT_COUNT is provided on the OCI statement handle to determine the number of implicit results available.
The rtype parameter of OCIStmtGetNextResult() returns the type of the result. In this release only the type: OCI_RESULT_TYPE_SELECT is supported. The describe metadata of the returned result set can be accessed similar to any SELECT ResultSet.
Note:
The following OCI code can be used in external procedures too, to fetch from the implicit results. In that case, OCI_PREP2_IMPL_RESULTS_CLIENT should be passed as the mode to the OCIStmtPrepare2() call.
Example 10-5 DBMS_SQL RETURN_RESULT Subprogram
procedure return_result(rc in out sys_refcursor,
to_client in boolean default true);
procedure return_result(rc in out integer,
to_client in boolean default true);
Example 10-6 A PL/SQL Stored Procedure to Implicitly Return Result-Sets (Cursors) to the Client
CREATE PROCEDURE foo AS c1 sys_refcursor; c2 sys_refcursor; begin open c1 for select * from emp; dbms_sql.return_result(c1); --return to client -- open 1 more cursor open c2 for select * from dept; dbms_sql.return_result (c2); --return to client end;
Example 10-7 An Anonymous PL/SQL Block to Implicitly Return Result-Sets (Cursors) to the Client
declare c1 sys_refcursor; c2 sys_refcursor; begin open c1 for select * from emp; dbms_sql.return_result (c1); --return to client -- open 1 more cursor open c2 for select * from dept; dbms_sql.return_result (c2); --return to client end;
Example 10-8 Using OCIStmtGetNextResult() to Retrieve and Process the Implicit Results Returned by Either a PL/SQL Stored Procedure or Anonymous Block
OCIStmt *stmthp;
ub4 rsetcnt;
void *result;
ub4 rtype;
char *sql = "begin foo; end;";
/* Prepare and execute the PL/SQL procedure. */
OCIStmtPrepare2(svchp, &stmthp, errhp, (oratext *)sql, strlen(sql),
NULL, 0, OCI_NTV_SYNTAX, OCI_DEFAULT);
OCIStmtExecute(svchp, stmthp, errhp, 1, 0,
(const OCISnapshot *)0,
(OCISnapshot *)0, OCI_DEFAULT);
/* Now check if any implicit results are available. */
OCIAttrGet((void *)stmthp, OCI_HTYPE_STMT, &rsetcnt, 0,
OCI_ATTR_IMPLICIT_RESULT_COUNT, errhp);
/* Loop and retrieve the implicit result-sets.
* ResultSets are returned in the same order as in the PL/SQL
* procedure/block.
*/
while (OCIStmtGetNextResult(stmthp, errhp, &result, &rtype,
OCI_DEFAULT) == OCI_SUCCESS)
{
/* Check the type of implicit ResultSet, currently
* only supported type is OCI_RESULT_TYPE_SELECT
*/
if (rtype == OCI_RESULT_TYPE_SELECT)
{
OCIStmt *rsethp = (OCIStmt *)result;
/* Perform normal OCI actions to define and fetch rows. */
}
else
printf("unknown result type %d\n", rtype);
/* The result set handle should not be freed by the user. */
}
OCIStmtRelease(stmthp, errhp, NULL, 0, OCI_DEFAULT); /* Releases the statement handle. */
See Also:
Statement Handle Attributes for more information about the attribute OCI_ATTR_IMPLICIT_RESULT_COUNT.
OCI applications can use client memory to take advantage of the OCI result cache to improve response times of repeated queries.
See Also:
Oracle Database Development Guide for complete information about using the OCI client result cache
Describes topics about client statement cache auto-tuning.
Auto-tuning optimizes OCI client session features of mid-tier applications to gain higher performance without the need to reprogram your OCI application. Auto tuning operations like increasing or decreasing cache memory happens implicitly during OCIStmtPrepare2() and OCIStmtRelease() calls on a periodic basis. Calling OCIAttrGet() with OCI_ATTR_STMTCACHESIZE on the service handle gives the current cache size being used, if you need to check.
It is possible for the coded OCI client statement cache size setting to be sub optimal. This can happen, for example, with changing the workload causing a different working set of SQL statements. If the size is too low, it will cause excess network activity and more parses at the server. If the size is too high, there will be excess memory used. It can be difficult for the client side application to always keep this cache size optimal.
Auto-tuning automatically reconfigures the OCI statement cache size on a periodic basis. Auto-tuning is achieved by providing a deployment time setting that provides an option to reconfigure OCI statement caching to resolve this potential performance issue.
These settings are provided as connect string based deployment settings in the client oraaccess.xml file that overrides manual settings to the user configuration of OCI features.
The more specific benefit of auto-tuning client statement cache is to transparently detect, monitor, and adjust the statement cache size to improve performance or decrease memory usage.
Developers and DBAs can expect to see the following benefits of using auto-tuning for their OCI client applications:
Reduced time and effort in diagnosing and fixing performance problems with each part of their system, such as statement caching
Minimized manual modifications needed to configurations of this OCI feature to improve performance. Usually, this manual correction requires applications to restart more than once with different configuration parameters, thus further reducing the high availability of the client
One solution that can be used by all OCI applications to improve performance right out-of-the-box without having to make any application changes
OCI applications are freed from making custom implementations (that can be error prone) to auto-tune their OCI application to optimize performance and memory usage. Here auto-tuning is limited to internal automatic tuning of OCI Client-side statement cache size only.
The following connection specific parameters in oraccess.xml can be set per configuration alias or across all connect strings using default connection specific parameters.
Values specified in the client oraaccess.xml configuration file override programmatic settings.
See Also:
About Specifying Defaults for Connection Parameters for more information about setting per configuration alias or across all connect strings using default connection specific parameters
This parameter is optional and sets the limit for the statement caching tunable component.
<statement_cache> <size>100</size> </statement_cache>
The limit is the maximum number of statements that can be cached per session. If auto-tuning is enabled or not, this setting in oraaccess.xml overrides the programmatic setting of OCI statement cache size.
If auto-tuning is enabled, this setting will be the upper bound on statement cache size while its being dynamically tuned.
If the session is not using statement caching APIs as in OCIStmtPrepare2() and OCIStmtRelease(), this setting is ignored.
Default values are as follows:
If auto-tuning is enabled, statement caching is dynamically tuned and the initial statement cache size is set to 100 statements.
If auto-tuning is disabled, this setting serves as the deployment setting of statement caching size, overriding any programmatic setting.
This section specifies auto tune parameters.
If the OCI session is not using statement caching APIs as in OCIStmtPrepare2() or OCIStmtRelease(), auto tuning parameters are ignored for that session. It is possible in a process that some sessions or connections can have auto-tuning enabled and some disabled.
See Also:
This parameter turns auto tuning on or off. The default is auto tuning off (FALSE) or disabled.
<auto_tune> <enable>true</enable> </auto_tune>
Auto-tuning is enabled along with internal default settings as described in <statement_cache>.
This parameter is optional.
<auto_tune> <enable>true</enable> <ram_threshold>0.1</ram_threshold> </auto_tune>
The default value is 0.01%. It is specified as percentage of installed RAM. This specifies the total memory available across the auto tuning sessions in a process sharing this setting. This setting can be specified per process or per connect string alias.
Note that if specified per connect string alias, the total auto tuning memory used by a client process can add up.
Therefore, it may be preferable to specify auto tuning limits in the <default_parameters> section of oraaccess.xml file. This way you have a common pool of memory for all sessions in a client process.
A smaller limit uses less RAM for auto tuning, but minimizes the chance other programs running on the system do not degrade in performance.
This parameter must be specified within the <auto_tune></auto_tune> deployment setting.
See Also:
This parameter is optional.
<auto_tune> <enable>true</enable> <memory_target>40M</memory_target> </auto_tune>
Specified in bytes. Default is undefined. It specifies the total memory available across the auto tuning sessions in a process sharing this setting. This setting can be specified per process or per connect string alias.
Note that if specified per connect string alias, the total auto tuning memory used by a client process can add up.
Therefore, it may be preferable to specify auto tuning limits in the <default_parameters> section of oraaccess.xml file. This way you have a common pool of memory for all sessions in a client process.
This parameter must be specified within the <auto_tune></auto_tune> deployment setting.
Using this parameter ensures the use of a consistent memory limit for auto tuning irrespective of installed RAM on that system.
If not specified, the auto tuning memory limit is based on the <ram_threshold> parameter setting.
If both <ram_threshold> and <memory_target> parameters are specified, the effective limit is the minimum of the two parameters.
See Also:
Lists and describes a comparison of all auto-tuning parameters.
Table 10-1 shows a comparison of the connection specific auto-tuning parameters.
Table 10-1 Comparison of Some Connection Specific Auto-Tuning Parameters
| Parameter | Setting and Semantics | For Auto-Tuning or Deployment Setting |
|---|---|---|
|
|
Optional setting. Per session cache size. |
If auto-tuning is enabled (see "Enabling and Disabling OCI Client Auto-Tuning"), this is the upper bound of each sessions statement cache size while its tuned by auto tuning. Or else it refers to the deployment setting for statement caching. |
|
|
Optional setting. Specify this parameter to use auto-tuning. Applies to all connections using this connect string or all connections if null connect string is specified. |
Only auto-tuning related |
|
|
Optional setting. Converts the percentage setting to a memory value based on installed RAM on that client or mid-tier system. This is the upper limit of memory used for auto tuning within a client process. For installed RAM of 8GB, not specifying this parameter gives 800 KB of memory among the sessions. Note each connection can potentially have its own setting of auto tuning parameters so these values can add up for the whole process based on configuration settings. It is preferable to use this parameter hence in the |
Only auto-tuning related. If auto-tuning is disabled, this parameter setting is ignored. This parameter must be specified within the |
|
|
Optional setting. This is the upper limit of memory used for auto tuning within a client process. Note each connection can potentially have its own setting of auto tuning parameters so these values can add up for the whole process based on configuration settings. It is preferable to use this parameter hence in the See "File (oraaccess.xml) Properties" for a description of the syntax. Value is in bytes. 1,048,576 bytes is 1 MB. |
Only auto-tuning related. If auto-tuning is disabled, this parameter setting is ignored. This parameter must be specified within the |
The following are some usage examples showing use and interaction of client statement cache auto-tuning parameters that are also connection specific parameters.
<statement_cache> <size>100</size> </statement_cache>
The programmatic statement cache size will be replaced by this setting. Auto-tuning is disabled and cache is managed per LRU. In this case, the application developer believes the OCI application statement prefetching programmatic settings do not need to be overridden.
<auto_tune> <enable>true</enable> </auto_tune>
Auto-tuning is enabled along with internal default settings as described in <statement_cache>.
<statement_cache> <size>100</size> </statement_cache> <auto_tune> <enable>true</enable> <memory_target>40M</memory_target> </auto_tune>
This statement caching deployment setting of 100 will replace the programmatic statement cache size and because auto-tuning is enabled, statement caching will be auto-tuned. The memory target setting is in effect because auto-tuning is enabled.
Auto tuning will always try to limit total statement cache memory used around a memory target. If a memory target is not specified, it is based on the percentage of total installed RAM.
In this case, the memory limit is the specified memory target.
The following conditions enable and disable OCI client auto-tuning:
Auto-tuning is enabled when the client oraaccess.xml <auto_tune> section is added with enable specified as true, <enable>true</enable>
Auto-tuning is disabled by default or when enable is set to false, <enable>false</enable> in oraaccess.xml under the <auto_tune> section.
The following are some guidelines to use when setting the auto-tuning parameters:
When either client response, memory allocation, or client CPU is high and you want to gain performance without rebuilding the OCI application, you can use <auto_tune> settings or deployment <statement_cache> settings. Auto tuning may also decrease the network bytes transferred between client and server.
When AWR or ADDM reports lots of parses and you cannot or you may prefer not to programmatically modify the statement cache size, you can specify auto-tuning for statement cache or use the deployment statement cache setting <statement_cache>.
Describes the OCI client-side deployment parameters using oraaccess.xml.
Starting with Oracle Database Release 12c Release 1 (12.1), Oracle provides an oraaccess.xml file, a client-side configuration file. You can use the oraaccess.xml file to configure selected OCI parameters (some of which are accepted programatically in various OCI API calls), thereby allowing OCI behavior to be changed during deployment without modifying the source code that calls OCI.
Updates to the oraaccess.xml file will not affect already running clients. In order to pick up any updates to the oraaccess.xml file, already running clients need to be restarted.
The oraaccess.xml file is read from the directory specified by the TNS_ADMIN environment variable in regular and instant client installations. This is the $ORACLE_HOME/network/admin directory on UNIX operating systems and the %ORACLE_HOME%\NETWORK\ADMIN directory on Microsoft Windows operating systems, if TNS_ADMIN is not set in regular client installations.
When equivalent parameters are set both in the sqlnet.ora and oraaccess.xml files, the oraaccess.xml file setting takes precedence over the corresponding sqlnet.ora file setting.
In such cases, Oracle recommends using the oraaccess.xml file settings moving forward. For any network configuration, the sqlnet.ora file continues to be the primary file as network level settings are not supported in the oraaccess.xml file.
Describes the high-level structure of the oraaccess.xml file.
The oraaccess.xml file has a top-level node <oraaccess> with the following three elements:
<default_parameters> - This element describes any default parameter settings shared across connections. These default parameters include:
Defaults for global parameters - These global parameters can only be specified once and hence are applicable to all connections and cannot be overridden at the connection level. These parameters are specified using the following tags:
<events> - Creates the OCI Environment in OCI_EVENTS mode, which is required for Fast Application Notification (FAN) and runtime connection load balancing
<result_cache> - Sets OCI client result cache parameters
<diag> - Sets OCI fault diagnosability parameters
Defaults for connection-specific parameters - Connection parameters can be set to different values for specific connections. However, they too can be defaulted, and overridden on a per connection string basis as needed. These defaults are shared across all connections (unless overridden at the connection level, which is described in the <config_descriptions> list item) that follows. These defaults are specified by the following tags:
<prefetch> - Sets the number of prefetch rows for all queries; specified using the <rows> parameter.
<statement_cache> - Sets the maximum number of statements that can be cached per session; specified using the <size> parameter.
<auto tune> - Consists of: <enable> to turn auto tuning on or off; <ram_threshold>, which sets the memory threshold for auto-tuning to stop using more memory when available physical memory on the client system has reached this threshold; and <memory_target>, which sets the memory limit that OCI auto-tuning can use per client process.
<fan_subscription_failure_action> - Sets the action upon subscription failure to be either the value trace or error.
<ons> - Sets a variety of ONS client-side deployment configuration parameters used for FAN notifications.
<config_descriptions> - This element associates a configuration alias element (<config_alias>), which is basically a name, with a specific set of parameters (<parameters>) that contain one or more connection parameters. These connection parameters are the same connection parameters within the element <default_parameters> described previously, namely: <prefetch>, <statement_cache>, <auto_tune>, <fan_subscription_failure_action> and <ons>.
<connection_configs> - This element associates one or more connection strings used by an application with a config alias, thus allowing multiple connection string elements to share the same set of parameters.
A connection configuration element (<connection_config>) associates a connection string element (<connection_string>) with a configuration alias element (<config_alias>).
A connection string is indirectly associated with a set of parameters through the configuration alias, which allows multiple connection string elements to share the same set of parameters.
The sections that follow describe these client-side deployment parameters in more detail.
As described, the <default_parameters> tag allows specifying default values for various OCI parameters.
Of these, some parameters can only be specified once and hence apply to all connections. These global parameters are described using the following tags:
<events>
This creates the OCI Environment in OCI_EVENTS mode, which is required for Fast Application Notification (FAN) and runtime connection load balancing.
<events> true <!--value could be false also --> </events>
<result_cache>
<max_rset_rows> - Maximum size of any result set in rows in the per-process query cache. Equivalent to OCI_RESULT_CACHE_MAX_RSET_ROWS in the sqlnet.ora file.
<max_rset_size> - Maximum client result cache size. Set the size to 32,768 bytes (32 Kilobytes (KB)) or greater. Equivalent to OCI_RESULT_CACHE_MAX_RSET_SIZE in the sqlnet.ora file.
<max_size> - Maximum size in bytes for the per-process query cache. Specifying a size less than 32,768 in the client disables the client result cache feature. Equivalent to OCI_RESULT_CACHE_MAX_SIZE in the sqlnet.ora file.
<result_cache>
<max_rset_rows>10</max_rset_rows>
<max_rset_size>65535</max_rset_size>
<max_size>65535</max_size>
</result_cache>
When equivalent parameters are set both in the sqlnet.ora and oraaccess.xml files, the oraaccess.xml file setting takes precedence over the corresponding sqlnet.ora file setting.
See Table 10-2 for a listing of equivalent OCI parameter settings.
<diag>
You can specify the following elements:
<adr_enabled> - Enables or disables diagnosability. Equivalent to DIAG_ADR_ENABLED in the sqlnet.ora file. Values: true or false.
<dde_enabled> - Enables or disables DDE. Values: true or false.
<adr_base> - Sets the ADR base directory, which is a system-dependent directory path string to designate the location of the ADR base to use in the current ADRCI session. Equivalent to ADR_BASE in the sqlnet.ora file. Value: directory path for ADR base directory.
<sighandler_enabled> - Enables or disables OCI signal handler. Values: true or false.
<restricted> - Enables or disables full dump files. Oracle Database client contains advanced features for diagnosing issues, including the ability to dump diagnostic information when important errors are detected. By default, these dumps are restricted to a small subset of available information, to ensure that application data is not dumped. However, in many installations, secure locations for dump files may be configured, ensuring the privacy of such logs. In such cases, it is recommended to turn on full dumps; this can greatly speed resolution of issues. Full dumps can be enabled by specifying a value of false. Values: true or false.
<trace_events> - Indicates the trace event number and the level of tracing to be in effect. Currently only event 10883 is supported. The available levels are 5 and 10.
<diag>
<adr_enabled>false</adr_enabled>
<dde_enabled>false</dde_enabled>
<adr_base>/foo/adr</adr_base>
<sighandler_enabled>false</sighandler_enabled>
<restricted>true</restricted>
<trace_events>
<trace_event>
<number>10883</number>
<level>5</level>
</trace_event>
</trace_events>
</diag>
When equivalent parameters are set both in the sqlnet.ora and oraaccess.xml files, the oraaccess.xml file setting takes precedence over the corresponding sqlnet.ora file setting.
See Table 10-2 for a listing of equivalent OCI parameter settings.
Table 10-2 Equivalent OCI Parameter Settings in oraaccess.xml and sqlnet.ora
| Parameter Group | oraaccess.xml Parameters | sqlnet.ora Parameters |
|---|---|---|
|
OCI client result cache |
|
|
|
OCI client result cache |
|
|
|
OCI client result cache |
|
|
|
OCI fault diagnosability |
|
|
|
OCI fault diagnosability |
|
|
|
OCI fault diagnosability |
|
|
See Also:
Oracle Database Development Guide for information about deployment time settings for client result cache and client configuration file parameters
Oracle Database Net Services Reference for more information about ADR diagnostic parameters in the sqlnet.ora file
You can specify the following connection parameters that are shared across connections:
<prefetch> - Specifies prefetch row count for SELECT statements.
<prefetch> <rows>100</rows> </prefetch>
Setting this parameter appropriately can help reduce round-trips to the database, thereby improving application performance.
Note that this only overrides the OCI_ATTR_PREFETCH_ROWS parameter (whether explicitly specified by the application or not). If the application has specified OCI_ATTR_PREFETCH_MEMORY explicitly, then the actual prefetch row count will be determined by using both constraints. The OCI_ATTR_PREFETCH_MEMORY constraint equivalent cannot be specified in the oraaccess.xml file.
Also note that OCI prefetching may still get disabled if the SELECT statement fetches columns of specific data types. For more details, see "About Fetching Results" for information about limitations of OCI prefetch.
<statement_cache> - Specifies the number of OCI Statement handles that can be cached per session.
<statement_cache> <size>100</size> </statement_cache>
Caching statement handles improves repeat execute performance by reducing client side and server side CPU consumption and network traffic.
Note that for this parameter to take effect, the application must be programmed to use OCIStatementPrepare2() and OCIStatementRelease() calls (and not the older OCISatementPrepare() and OCIHandleFree() equivalents for getting and disposing of statement handles.
<auto_tune> - Used to enable OCI Auto tuning.
See "<auto_tune>". See "<ram_threshold>". See "<memory_target>".
<auto_tune> <enable>true</enable> <ram_threshold>0.1</ram_threshold><!--percentage --> <memory target>2M</memory_target> </auto_tune>
Enabling auto-tuning can help OCI automatically tune the statement-cache size based on specified memory constraints. This can help dynamically tune the statement cache size to an appropriate value based on runtime application characteristics and available memory resources.
Note that for auto tuning OCI Statement Cache, the application must be programmed to use OCIStatementPrepare2() and OCIStatementRelease() calls (and not the older OCISatementPrepare() and OCIHandleFree() equivalents for getting and disposing of statement handles.
<fan_subscription_failure_action> - Used to determine how OCI responds to a failure to subscribe for FAN notifications.
A value of trace records any failure to subscribe for FAN notifications (if FAN is enabled) in the trace file and OCI proceeds ignoring the failure. A value of error makes OCI return an error if an attempt to subscribe for FAN notifications fails.
<fan>
<!--only possible values are "trace" and "error" -->
<subscription_failure_action>
trace
</subscription_failure_action>
</fan>
<ons> - Sets up Oracle Notification Service (ONS) parameters.
You can specify the following connection parameters:
<subscription_wait_timeout> - Length of time in seconds the client waits for its subscription to the ONS server.
<auto_ config> - true or false. If true, the configuration specified in this section will augment the auto configuration information that the client receives from the database. If false, it will override the same.
<thread_stack_size> - Size in bytes of the event notification thread stack.
<debug> - true or false. Whether debug mode is on (true) or off (false).
<servers> - Host list with ports and connection distribution.
<ons>
<!--values or in seconds -->
<subscription_wait_timeout>
5
</subscription_wait_timeout>
<auto_config>true</auto_config> <!--boolean -->
<threadstacksize>100k</threadstacksize>
<debug>true</debug>
<servers>
<address_list>
<name>pacific</name>
<max_connections> 3 <\max_connections>
<hosts>
10.228.215.121:25293,
10.228.215.122:25293
</hosts>
</address_list>
<address_list>
<name>Europe</name>
<max_connections>3<\max_connections>
<hosts>
myhost1.mydomain.com:25273,
myhost2.mydomain.com:25298,
myhost3.mydomain.com:30004
</hosts>
</address_list>
</servers>
</ons>
See Also:
Oracle Universal Connection Pool for JDBC Developer's Guide for information about ONS configuration parameters
Using the oraaccess.xml file also allows you to override the very same set of connection-specific parameters at the connection-string level as well.
This allows for overriding those connection-specific parameters based on requirements of individual applications.
Using the <config_descriptions> tag, you can specify a set of connection-specific parameters (<parameters>) to be associated with a configuration alias (<config_alias>, which creates a named group of connection-specific parameters). Thereafter, using the <connection_configs> tag, you can associate one or more connection-strings (specified using the <connection-string> tag) with a <config_alias>. This permits a level of indirection that allows multiple <connection_string> elements to share the same set of <parameters>.
Example 1
This example shows a very simple oraaccess.xml file configuration that highlights defaulting of global and connection parameters applicable across all connections.
<?xml version="1.0" encoding="ASCII" ?>
<!--
Here is a sample oraaccess.xml.
This shows defaulting of global and connection parameters
across all connections.
-->
<oraaccess xmlns="http://xmlns.oracle.com/oci/oraaccess"
xmlns:oci="http://xmlns.oracle.com/oci/oraaccess"
schemaLocation="http://xmlns.oracle.com/oci/oraaccess
http://xmlns.oracle.com/oci/oraaccess.xsd">
<default_parameters>
<prefetch>
<rows>50</rows>
</prefetch>
<statement_cache>
<size>100</size>
</statement_cache>
<result_cache>
<max_rset_rows>100</max_rset_rows>
<max_rset_size>10K</max_rset_size>
<max_size>64M</max_size>
</result_cache>
</default_parameters>
</oraaccess>
Example 2
This example shows connection parameters being overridden at the connection level.
<?xml version="1.0" encoding="ASCII" ?>
<!--
Here is a sample oraaccess.xml.
This highlights some connection parameters being
overridden at the connection level
-->
<oraaccess xmlns="http://xmlns.oracle.com/oci/oraaccess"
xmlns:oci="http://xmlns.oracle.com/oci/oraaccess"
schemaLocation="http://xmlns.oracle.com/oci/oraaccess
http://xmlns.oracle.com/oci/oraaccess.xsd">
<default_parameters>
<prefetch>
<rows>50</rows>
</prefetch>
<statement_cache>
<size>100</size>
</statement_cache>
<auto_tune>
<enable>true</enable>
<ram_threshold>2.67</ram_threshold>
<memory_target>204800</memory_target>
</auto_tune>
<result_cache>
<max_rset_rows>100</max_rset_rows>
<max_rset_size>10K</max_rset_size>
<max_size>64M</max_size>
</result_cache>
</default_parameters>
<!--
Create configuration descriptions, which are
groups of connection parameters associated with
a config_alias.
-->
<config_descriptions>
<config_description>
<config_alias>bar</config_alias>
<parameters>
<prefetch>
<rows>20</rows>
</prefetch>
</parameters>
</config_description>
<config_description>
<config_alias>foo</config_alias>
<parameters>
<statement_cache>
<size>15</size>
</statement_cache>
</parameters>
</config_description>
</config_descriptions>
<!--
Now map the connection string used by the application
with a config_alias.
-->
<connection_configs>
<connection_config>
<connection_string>hr_db</connection_string>
<config_alias>foo</config_alias>
</connection_config>
<connection_config>
<connection_string>finance_db</connection_string>
<config_alias>bar</config_alias>
</connection_config>
</connection_configs>
</oraaccess>
Example 3
This example highlights setup for FAN notifications.
<?xml version="1.0" encoding="ASCII" ?>
<!--
Here is a sample for oraaccess.xml for
setting up for FAN notifications.
-->
<oraaccess xmlns="http://xmlns.oracle.com/oci/oraaccess"
xmlns:oci="http://xmlns.oracle.com/oci/oraaccess"
schemaLocation="http://xmlns.oracle.com/oci/oraaccess
http://xmlns.oracle.com/oci/oraaccess.xsd">
<default_parameters>
<fan>
<!-- only possible values are "trace" or "error" -->
<subscription_failure_action>
error
</subscription_failure_action>
</fan>
<ons>
<subscription_wait_timeout>
5
</subscription_wait_timeout>
<auto_config>true</auto_config>
</ons>
<events>true</events>
</default_parameters>
</oraaccess>
Example 4
This example highlights an advanced oraaccess.xml file configuration usage with manual ONS settings. Manual ONS settings should be used rarely.
<?xml version="1.0" encoding="ASCII" ?>
<!--
Here is a sample for oraaccess.xml that highlights
manual ONS settings.
-->
<oraaccess xmlns="http://xmlns.oracle.com/oci/oraaccess"
xmlns:oci="http://xmlns.oracle.com/oci/oraaccess"
schemaLocation="http://xmlns.oracle.com/oci/oraaccess
http://xmlns.oracle.com/oci/oraaccess.xsd">
<default_parameters>
<fan>
<!-- only possible values are "trace" or "error" -->
<subscription_failure_action>
error
</subscription_failure_action>
</fan>
<ons>
<subscription_wait_timeout>
5
</subscription_wait_timeout>
<auto_config>true</auto_config>
<!--This provides the manual configuration for ONS.
Note that this functionality should not need to be used
as auto_config can normally discover this
information. -->
<servers>
<address_list>
<name>pacific</name>
<max_connections>3</max_connections>
<hosts>10.228.215.121:25293, 10.228.215.122:25293</hosts>
</address_list>
<address_list>
<name>Europe</name>
<max_connections>3</max_connections>
<hosts>myhost1.mydomain.com:25273,
myhost2.mydomain.com:25298,
myhost3.mydomain.com:30004</hosts>
</address_list>
</servers>
</ons>
<events>true</events>
</default_parameters>
</oraaccess>
See Also:
About Specifying Defaults for Connection Parameters about overriding the very same set of connection-specific parameters at the connection-string level
Listed are some high level rules with regards to the oraaccess.xml file syntax stated here for simplicity. The XML schema specified in the oraaccess.xsd file is the ultimate formal reference for oraaccess syntax:
The contents of the file are case sensitive, and all elements (tags, parameter names) are in lower case.
TBD: XML Schema (Provide definition of schema, and also the details of where it is located (most likely $ORACLE_HOME/rdbms/admin/)) to validate the file. If file is invalid, the application throws a proper error and stops.
Comments are allowed between parameters (nodes); for example, Comment "<!-- comments -->".
For the syntax with respect to the order of the parameters, see the XML Schema: oraaccess.xsd file (see information about the oraaccess.xsd file later in this list).
For memory size, valid values and formats are 100, 100k, 100K, 1000M, and 1121m. This means only suffixes 'M', 'm', 'K', 'k', or no suffix are allowed. 'K' or 'k' means kilobytes and 'M' or 'm' means megabytes. No suffix means the size is in bytes.
<ram_threshold> should be a decimal number between 0 and 100 and indicates a percentage value.
Where a number is expected, only positive unsigned integers are allowed; no sign is allowed. An example of a valid number usage is <statement_cache> <size>100</size> </statement_cache>).
Configuration alias names (<config_alias>foo</config_alias>) are not case-sensitive
String parameters (such as <config_alias>) are not expected to be quoted.
These rules are all encapsulated in the schema definition.
OCI will report an error if OCI is provided an invalid oraaccess.xml file.
Before deploying an oraaccess.xml file, Oracle recommends that you validate it with the Oracle supplied XML schema file: oraaccess.xsd. The schema file is installed under ORACLE_HOME/rdbms/admin in a regular client and under instantclient_12_1/sdk/admin in an instant client SDK. Customers can use their own favorite XML validation tools to perform the validation after modifying the oraaccess.xml file.
Sample oraaccess.xml files can be found in the ORACLE_HOME/rdbms/demo directory in a regular client and in the instantclient_12_1/sdk/demo in an instant client. The parameters in these files are for demonstration purpose only and should be modified and tested as per the application's requirement before deploying it.
Fault diagnosability was introduced into OCI in Oracle Database 11g Release 1 (11.1). An incident (an occurrence of a problem) on the OCI client is captured without user intervention in the form of diagnostic data: dump files or core dump files. Up to Release 11.2.0.1, the diagnostic data was stored in an Automatic Diagnostic Repository (ADR) subdirectory created for the incident. For example, if a Linux or UNIX application fails with a NULL pointer reference, then the core file is written in the ADR home directory (if it exists) instead of the operating system directory. The ADR subdirectory structure and a utility to deal with the output, ADR Command Interpreter (ADRCI), are discussed in the following sections. However, beginning with Release 11.2.0.2, the diagnostic data is stored in the current directory.
An ADR home is the root directory for all diagnostic data for an instance of a particular product such as OCI and a particular operating system user. ADR homes are grouped under the same root directory, the ADR base.
Fault diagnosability and the ADR structure for Oracle Database are described in detail in the discussion of managing diagnostic data in Oracle Database Administrator's Guide.
Describes how the location of the ADR base is determined.
The location of the ADR base is determined by OCI in the following order:
For all diagnosability parameters, OCI first looks in the file oraaccess.xml. If these parameters are not set there, then OCI looks next in sqlnet.ora (if it exists) for a statement such as (Linux or UNIX):
ADR_BASE=/foo/adr
adr (the name of a directory) must exist and be writable by all operating system users who execute OCI applications and want to share the same ADR base. foo stands for a path name. The location of sqlnet.ora is given in the directory $TNS_ADMIN (%TNS_ADMIN% on Windows). If there is no $TNS_ADMIN then the current directory is used. If ADR_BASE is set and one sqlnet.ora is shared by all users, then OCI stops searching when directory adr does not exist or is not writable by the user. If ADR_BASE is not set, then OCI continues the search, testing for the existence of certain directories.
For example, if sqlnet.ora contains the entry ADR_BASE=/home/chuck/test then the ADR base is /home/chuck/test/oradiag_chuck and the ADR home could be something like /home/chuck/test/oradiag_chuck/diag/clients/user_chuck/host_4144260688_11.
$ORACLE_BASE (%ORACLE_BASE% on Windows) exists. In this case, the client subdirectory exists because it was created during installation of the database using Oracle Universal Installer.
For example, if $ORACLE_BASE is /home/chuck/obase then the ADR base is /home/chuck/obase and the ADR home could be similar to /home/chuck/obase/diag/clients/user_chuck/host_4144260688_11.
$ORACLE_HOME (%ORACLE_BASE% on Windows) exists. In this case, the client subdirectory exists because it was created during installation of the database using Oracle Universal Installer.
For example, if $ORACLE_HOME is /ade/chuck_l1/oracle then the ADR base is /ade/chuck_l1/oracle/log and the ADR home could be similar to /ade/chuck_l1/oracle/log/diag/clients/user_chuck/host_4144260688_11.
Operating system home directory: $HOME on Linux or UNIX, or %USERPROFILE% on Windows. On Linux or UNIX the location could be something like this for user chuck: /home/chuck/oradiag_chuck. On Windows, a folder named Oracle is created under C:\Documents and Settings\chuck.
For example, in an Instant Client, if $HOME is /home/chuck then the ADR base is /home/chuck/oradiag_chuck and the ADR home could be /home/chuck/oradiag_chuck/diag/clients/user_chuck/host_4144260688_11.
On Windows, if the application is run as a service, the home directory option is skipped.
Temporary directory in the Linux or UNIX operating system: /var/tmp.
For example, in an Instant Client, if $HOME is not writable, then the ADR base is /var/tmp/oradiag_chuck and the ADR home could be /var/tmp/oradiag_chuck/diag/clients/user_chuck/host_4144260688_11.
Temporary directories in the Windows operating system, searched in this order:
%TMP%
%TEMP%
%USERPROFILE%
Windows system directory
If none of these directory choices are available and writable, or the ADR base is not created and there are no diagnostics.
ADRCI is a command-line tool that enables you to view diagnostic data within the ADR and to package incident and problem information into a zip file for Oracle Support to use. You can use ADRCI interactively and from a script. A problem is a critical error in OCI or the client. Each problem has a problem key. An incident is a single occurrence of a problem and is identified by a unique numeric incident ID. Each incident has a problem key that is a set of attributes: the ORA error number, error parameter values, and other information. Two incidents have the same root cause if their problem keys match.
See Also:
Oracle Database Utilities for an introduction to ADRCI
What follows is a launch of ADRCI in a Linux system, a use of the HELP command for the SHOW BASE command, and then the use of the SHOW BASE command with the option -PRODUCT CLIENT, which is necessary for OCI applications. The ADRCI commands are case-insensitive. User input is shown in bold.
% adrci ADRCI: Release 12.1.0.0.0 - Development on Thu Oct 13 14:17:46 2011 Copyright (c) 1982, 2009, Oracle. All rights reserved. adrci> help show base Usage: SHOW BASE [-product <product_name>] Purpose: Show the current ADR base setting. Options: [-product <product_name>]: This option allows users to show the given product's ADR Base location. The current registered products are "CLIENT" and "ADRCI". Examples: show base -product client show base adrci> show base -product client ADR base is "/ade/chuck_l3/oracle/log"
Next, the SET BASE command is described:
adrci> help set base Usage: SET BASE <base_str> | -product <product_name> Purpose: Set the ADR base to use in the current ADRCI session. If there are valid ADR homes under the base, all homes will will be added to the current ADRCI session. Arguments: <base_str>: It is the ADR base directory, which is a system-dependent directory path string. -product <product_name>: This option allows users to set the given product's ADR Base location. The current registered products are "CLIENT" and "ADRCI". Notes: On platforms that use "." to signify current working directory, it can be used as base_str. Example: set base /net/sttttd1/scratch/someone/view_storage/someone_v1/log set base -product client set base . adrci> quit
When ADRCI is started, the default ADR base is for the rdbms server. $ORACLE_HOME is set to /ade/chuck_l3/oracle:
% adrci
ADRCI: Release 12.1.0.0.0 - Development on Thu Oct 13 14:17:46 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
ADR base = "/ade/chuck_l3/oracle/log"
For OCI application incidents you must check and set the base:
adrci> show base -product client ADR base is "/ade/chuck_l3/oracle/log" adrci> set base /ade/chuck_l3/oracle/log
For Instant Client there is no $ORACLE_HOME, so the default base is the user's home directory:
adrci> show base -product client ADR base is "/home/chuck/oradiag_chuck" adrci> set base /home/chuck/oradiag_chuck adrci> show incidents ADR Home = /home/chuck/oradiag_chuck/diag/clients/user_chuck/host_4144260688_11: ************************************************************************* INCIDENT_ID PROBLEM_KEY CREATE_TIME ------------------------------------------------------------------------- 1 oci 24550 [6] 2007-05-01 17:20:02.803697 -07:00 1 rows fetched adrci>
See Also:
To disable diagnosability, turn off diagnostics by setting the following parameters in sqlnet.ora (the default is TRUE):
DIAG_ADR_ENABLED=FALSE DIAG_DDE_ENABLED=FALSE
To turn off the OCI signal handler and reenable standard operating system failure processing, place the following parameter setting in sqlnet.ora:
DIAG_SIGHANDLER_ENABLED=FALSE
As noted previously, ADR_BASE is used in sqlnet.ora to set the location of the ADR base.
Oracle Database client contains advanced features for diagnosing issues, including the ability to dump diagnostic information when important errors are detected. By default, these dumps are restricted to a small subset of available information, to ensure that application data is not dumped. However, in many installations, secure locations for dump files may be configured, ensuring the privacy of such logs. In such cases, it is recommended to turn on full dumps; this can greatly speed resolution of issues. Full dumps can be enabled by adding the following line to the sqlnet.ora file used by your Oracle Database client installation:
DIAG_RESTRICTED=FALSE
To verify that diagnosability features are working correctly:
sqlnet.log in your application's TNS_ADMIN directory for error messages indicating that diagnosability could not be started (normally this is due to invalid directory names or permissions).See Also:
Oracle Database Net Services Reference for the ADR parameter settings in sqlnet.ora
Oracle Database Net Services Administrator's Guide for more information about the structure of ADR
In Oracle Database Release 11.2 (or later) you can use different versions of the time zone file on the client and server; this mode of operation was not supported before Oracle database Release 11.2.
Both client and server must be 11.2 or later to operate in such a mixed mode. This section discusses the ramifications of operating in such a mode. To avoid these ramifications use the same time zone file version for client and server.
The following behavior is seen when the client and server use different time zones file versions. Note that the use of different time zone file versions only affects the handling of TIMESTAMP WITH TIMEZONE (TSTZ) data type values.
The OCI Datetime and Interval APIs listed here unconditionally raise an error when the input parameters are of TSTZ type. This is because these operations depend on the local time zone file on the client that is not synchronized with the database. Continuing with the computation in such a configuration can result in inconsistent computations across the client and database tiers.
OCIDateTimeCompare() OCIDateTimeConstruct() OCIDateTimeConvert() OCIDateTimeSubtract() OCIIntervalAdd() OCIIntervalSubtract() OCIIntervalFromTZ() OCIDateTimeGetTimeZoneName() OCIDateTimeGetTimeZoneOffset()Foot 1 OCIDateTimeSysTimeStamp()
There is a performance penalty when you retrieve or modify TSTZ values. The performance penalty arises because of the additional conversions needed to compensate for the client and server using different time zone file versions.
If new time zone regions are defined by the more recent time zone file, you can see an error operating on a TIMESTAMP WITH TIMEZONE value belonging to the new region on a node that has a time zone file version that does not recognize the new time zone region.
Applications that manipulate opaque type or XMLType instances or both containing TSTZ type attributes must use the same time zone file version on client and server to avoid data loss.
See Also:
Oracle Database Globalization Support Guide for information about upgrading the time zone file and timestamp with time zone data
The multitenant architecture enables an Oracle database to contain a portable collection of schemas, schema objects, and nonschema objects that appear to an Oracle client as a separate database. A multitenant container database (CDB) is an Oracle database that includes one or more pluggable databases (PDBs).
OCI clients can connect to a PDB using a service whose pluggable database property has been set to the relevant PDB.
In general, OCI calls behave the same way whether connected to a pluggable database or a normal database. OCI calls and features that require special consideration with a CDB are described in the sections that follow:
See Also:
Oracle Database Administrator’s Guide for more information about PDBs and for more details about configuring the services to connect to various PDBs
An attempt to logon in OCI_PRELIM_AUTH mode when connected to any container other than CDB$ROOT will result in an ORA-24542 error.
An attempt to issue OCIDBStartup() when connected to any container other than CDB$ROOT results in an ORA-24543 error.
An attempt to issue OCIDBShutdown() when connected to any container other than CDB$ROOT results in an ORA-24543 error. When OCIDBShutdown() is issued connected to CDB$ROOT, it brings down the whole instance.
OCI Continuous Query Notification (CQN) is not supported with CDB.
OCI Client Result Cache is not supported with CDB.
OCI applications linked against a client library older than release 12.1 and connecting to a pluggable database will not be able to utilize Fast Application Notification (FAN) High Availability (HA) functionality when connected as a normal (non-common) user. As a workaround, such applications should connect as a common user. This restriction does not exist for release 12.1 OCI clients.
The ALTER SESSION SET CONTAINER statement can be used to switch an OCI connection from one pluggable database to another. However, applications that use the ALTER SESSION SET CONTAINER statement to switch between pluggable databases need to ensure that their usage is consistent with the OCI restrictions described as follows.
The ALTER SESSION SET CONTAINER statement is disallowed for OCI migratable sessions (such as sessions created with OCI_MIGRATE mode during logon) and the combination results in an ORA-65135 error.
The ALTER SESSION SET CONTAINER statement is not supported with OCI connection pool (which is the old OCI connection pool API) and the combination results in an ORA-65135 error.
The ALTER SESSION SET CONTAINER statement is not supported in conjunction with OCI session switching (wherein multiple OCI user handles share the same OCI server handle).
The ALTER SESSION SET CONTAINER statement is not supported if the application uses TIMESTAMP WITH TIMEZONE or TIMESTAMP WITH LOCAL TIMEZONE data types in OCI, and if the application switches between pluggable databases having different database time zone settings or different database time zone file version settings, as this could lead to incorrect data being read or written for TIMESTAMP WITH TIMEZONE and TIMESTAMP WITH LOCAL TIMEZONE data types.
The ALTER SESSION SET CONTAINER statement is not supported if the command is used to switch an OCI connection between any two pluggable databases whose character sets are different as this could lead to incorrect data being read or written for character types.
If the client initially connects to a container with a EXTENDED MAX_STRING_SIZE setting, and then within the same session switches to a container (using an ALTER SESSION SET CONTAINER statement) with an STANDARD MAX_STRING_SIZE setting, then a subsequent OCIStmtExecute() call will result in an ORA-14697 error if an attempt is made to use any bind variables of size greater than 4000 bytes. For more information about MAX_STRING_SIZE, see Oracle Database SQL Language Reference.
An attempt to fetch from an OCI statement handle using OCIStmtFetch() or OCIStmtFetch2() in the context of a different container than the one in which it was executed will result in an ORA-65108 error.
OCI client result cache is disabled if an ALTER SESSION SET CONTAINER statement is done in OCI.
Fast Application Notification (FAN) and Runtime Connection Load Balancing notifications are not supported for applications that switch connections between pluggable databases using an ALTER SESSION SET CONTAINER statement.
The ALTER SESSION SET CONTAINER statement sets the current transaction, if any, to read only and any attempt to perform any of the OCI transaction calls (OCITransStart(), OCITransDetach(), OCITransCommit(), OCITransRollback(), OCITransPrepare(), OCITransMultiPrepare() , OCITransForget()) will return an error in the new container. In order to issue any of these calls, you need to switch back to the original container.
If an OCISubscriptionUnRegister() call is attempted in the context of an incorrect container (different from the container on which the corresponding OCISubscriptionRegister() call was done), then an ORA-24950 is returned.
A OCIDescribeAny() call with OCI_PTYPE_DATABASE describes the database to which the connection is connected. After an ALTER SESSION SET CONTAINER statement is done, if the application wants to see the current database description, the OCIDescribeAny() call will need to be reissued.
Calls to any OCI Any Data, collection, or object functions that are used to manipulate an object from a different container are not supported.
An OCIObjectFlush() call is supported only in the container where the object instance was created with an OCIObjectNew() call.
Oracle recommends that OCIObjectFlush() be called prior to switching containers with an ALTER SESSION SET CONTAINER statement. Note that an OCIObjectFlush() call will start a transaction if one is not already started.
An OCIObjectFlush() call done after switching containers may return an error if a transaction was already started earlier on another container by the same session (either as a result of explicit DMLs or as a result of an OCIObjectFlush()call).
An OCIObjectFlush() call only flushes objects dirtied in the context of the container in which the OCIObjectFlush() call is issued.
Various session attributes may change on an ALTER SESSION SET CONTAINER statement. If an application caches these attributes, their settings may no longer be the same after an ALTER SESSION SET CONTAINER statement. Examples of attributes that can be obtained with an OCIAttrGet() call and which can change on an ALTER SESSION SET CONTAINER statement include the following:
Since Oracle Database 11g Release 2, Oracle Streams provides enhanced APIs, known as eXtended Streams (XStream) Out and XStream In, to enable high performance, near real-time information-sharing infrastructure between Oracle databases and non-Oracle databases, non-RDBMS Oracle products, file systems, third party software applications, and so on.
XStream is built on top of Streams infrastructure.
See Also:
XStream Out allows a remote client to attach to an outbound server (a Streams apply process) and extract row changes in the form of Logical Change Records (LCRs).
To use XStream Out, a capture and an apply process must be created similar to other Streams setup. All data types supported by Oracle Streams including LOB, LONG, and XMLType are supported by XStreams. Such an apply process is called an outbound server. The capture and the outbound server may or may not be on the same database instance. After the capture and the outbound server have started, row changes will be captured and sent to the outbound server. An external client can then connect to this outbound server using OCI. After the connection is established, the client can loop waiting for LCRs from the outbound server. The client can register a client-side callback to be invoked each time an LCR is received. At anytime, the client can detach from the outbound server as needed. Upon restart, the outbound server knows where in the redo stream to start streaming LCRs to the client.
See Also:
Oracle Streams Concepts and Administration for the basics of LCRs
Oracle Database XStream Guide for more details of XStreams concepts
An LCR stream must be repeatable.
An LCR stream must contain a list of assembled and committed transactions.
LCRs from one transaction are contiguous. There is no interleaving of transactions in the LCR stream.
Each transaction within an LCR stream must have an ordered list of LCRs and a transaction ID.
The last LCR in each transaction must be a commit LCR.
Each LCR must have a unique position.
The position of all LCRs within a single transaction and across transactions must be strictly increasing.
If the outbound server or the client aborts abnormally, the connection between the two is automatically broken. The client needs to maintain the processed low position to properly recover after a restart.
The processed low position is a position below which all LCRs have been processed by the client. This position should be maintained by the client while applying each transaction. Periodically this position is sent to the server while the client executes XStream Out APIs. This position indicates to the server that the client has processed all LCRs below or equal to this position; thus, the server can purge redo logs that are no longer needed.
Upon restart, the client must re-attach to the outbound server. During the attach call, the client can notify the outbound server of the last position received by the client. The outbound server then sends LCRs with position greater than this last position. If the client does not specify the last position (that is, a NULL is specified), the outbound server will retrieve the processed low position from its system tables and derive the starting position to mine the redo logs. It will send to the client the LCRs with position greater than this processed low position.
To replicate non-Oracle data into Oracle databases use XStream In which allows a remote client to attach to an inbound server (a Streams apply process) and send row and DDL changes in the form of LCRs.
An external client application connects to the inbound server using OCI. After the connection is established, the client application acts as the capture agent for the inbound server by streaming LCRs to it. A client application can attach to only one inbound server per database connection. Each inbound server only allows one client attaching to it.
XStream In uses the following features of Oracle Streams:
High performance processing of DML changes using an apply process and, optionally, apply process parallelism.
Apply process features such as SQL generation, conflict detection and resolution, error handling, and customized processing with apply handlers.
Streaming network transmission of information with minimal network round trips.
XStream In supports all of the data types that are supported by Oracle Streams, including LOBs, LONG, LONG RAW, and XMLType. A client application sends LOB and XMLType data to the inbound server in chunks. Several chunks make up a single column value of LOB or XMLType.
The processed low position is the position below which the inbound server no longer requires any LCRs. This position corresponds with the oldest SCN for an Oracle Streams apply process that applies changes captured by a capture process.
The processed low position indicates that the LCRs less than or equal to this position have been processed by the inbound server. If the client re-attaches to the inbound server, it only needs to send LCRs greater than the processed low position because the inbound server discards any LCRs that are less than or equal to the processed low position.
If the client application aborts abnormally, then the connection between the client application and the inbound server is automatically broken. Upon restart, the client application retrieves the processed low position from the inbound server and instructs its capture agent to retrieve changes starting from this processed low position.
To limit the recovery time of a client application using the XStream In interface, the client application can send activity, such as empty transactions, periodically to the inbound server. When there are no LCRs to send to the server, the client can send a row LCR with a commit directive to advance the inbound server's processed low position. This activity acts as an acknowledgement so that the inbound server's processed low position can be advanced. The LCR stream sent to an inbound server must follow the LCR stream properties for XStream Out defined above.
Stream position refers to the position of an LCR in a given LCR stream.
For transactions captured outside Oracle databases the stream position must be encoded in certain format (for example, base-16 encoding) that supports byte comparison. The stream position is key to the total order of transaction stream sent by clients using the XStream In interface.
Describes the security characteristics of XStreams.
XStream Out allows regular users to receive LCRs without requiring system level privileges. System level privileges, such as DBA role, are required to configure XStream Out. The user who configures XStream Out can specify a regular user as the connect user who can attach to an outbound server to receive LCRs.
XStream In allows regular users to update tables in its own schema without requiring system level privileges (for example, DBA) to configure XStream In.
XStream cannot assume that the connected user to the inbound or outbound server is trusted.
OCI clients must connect to an Oracle database prior to attaching to an XStream outbound or inbound server created on that database. The connected user must be the same as the connect_user configured for the attached outbound server or the apply_user configured for the attached inbound server; otherwise, an error is raised.
See Also:
Oracle Database XStream Guide for more about configuring Oracle XStreams
Footnote Legend
Footnote 1:Returns an ORA-01805 error when timezone files on the client and server do not match (regions are not synchronized); returns OCI_SUCCESS when region time zone values are the same (represent the same instant in UTC), though the TIME ZONE offsets are different.