22.3.11

SAP Note 25383 - Database links, synonyms, remote DB

Symptom:

When using an Oracle based R/3 system, how can you access a table using ABAP in other Oracle databases that are not necessarily R/3 databases?

Other terms

Database link, synonym, ORA-2068, ORA-2080, ORA-2081, abap/no_dsql95, dbs/ora/_retry_on_sql_error, ALTER SESSION

Reason and Prerequisites

Oracle offers the possibility to link one database to another database with a "Database link" and to access tables from the remote database with this database link. By doing this, reading and also writing access can be established to the remote tables, in which Oracle transfers the synchronization of the distributed transaction via a 2 phase commit log.

Below, possibilities for using database links from the R/3 system are described.

Solution

A Database link can be created on the R/3 database by using the following Oracle command:

CREATE PUBLIC DATABASE LINK
CONNECT TO IDENTIFIED BY
USING '';

For the refer to the Oracle documentation.

There are two possible alternatives for accessing a remote table from the R/3 System:

    1. With ABAP Open SQL
    For this, the description of the remote table and its fields in the R/3 data dictionary is required. In the releases smaller than 3.x, you can create and activate a table description in the data dictionary without the table also having to be created on the R/3 database at the same time. However, as of Release 3.x, the table is implicitly created on the R/3 database during activation. In this case, the created table must be explicitly deleted again by using the database utility.
    Instead of the table, you now create a synonym under the table name used in the R/3 data dictionary for the remote table, thus:

    CREATE SYNONYM
    FOR [.]@;

    This synonym enables you to make full use of the ABAP Open SQL language range when accessing the remote table.
    2. With ABAP native SQL
    When using native SQL, no table description is necessary in the data dictionary. The data types of the ABAP variable entered in the SQL statement are set up here. That is, type C fields should be used as host variables in the SQL statement for CHAR or VARCHAR data types and type I or type P fields for NUMBER data types.

    In the native SQL statement, the database link can be directly called:

    EXEC SQL.
    SELECT ... FROM @ ...
    ENDEXEC.

    For simplifying the syntax, you can also use synonyms, although it is not necessary.

Refer to the Oracle manuals in order to obtain the commands for creating a view or a synonym.

Possible problems:

    1. ORA-2068 after restarting the remote DB.

From the performance view, SQL statements are buffered in the R/3 System in a local work process statement cache. When repeatedly calling up the same statement, reparsing does not need to be carried out. However, this means that the connection to the Oracle database process for an SQL statement contained in the statement cache is seperately held through the execution end of the statement (technically, the respective database cursor is held until the statement is replaced in the cache).

If an SQL statement which accesses via a database link on a remote table (such as is described above) is executed, then the R/3 statement cache will also receive this statement (as it does all the other SQL statements). This occurs because the database cursor in question becomes invalid when shutting down the remote database. However, the R/3 statement cache does not notice this occurence and therefore repeatedly attempts to execute the statement with the invalid database cursor.

This problem can only be corrected by a restart of the affected application server for R/3 Releases 3.0C and smaller.

As of Release 3.0D you can avoid this problem during access with ABAP OPEN SQL by setting the R/3 parameter

dbs/ora/_retry_on_sql_error = 2068

in all instance profiles. This parameter causes the affected database cursor to be closed when the error ORA2068 occurs and a new database cursor to be opened when executing the statements again. This occurs implicitly and therefore transparently for the requested ABAP program. However, the parameter is noteffective native SQL accesses.

When using the ABAP native SQL for the remote access with Releases 3.0D - 3.1I it is possible, by setting the R/3 parameter

ABAP/no_dsql95 = NO

to force the used database cursor to close again directly after the execution of a native SQL statement. As of R/3 Release 4.x, this parameter is no longer necessary since the database cursors for Native SQL statements are always immediately released there again. Unfortunately, there is no such option in Release 3.0C and smaller.
It is mandatory that you use the newest 3.1I R/3 kernel if you set parameter ABAP/no_dsql95 since some errors affecting the Native SQL were corrected there. Refer to Note 102445 on how to import the 3.1I kernel.

However even if all DB cursors were closed, the first access via the database link after the remote DB was started terminates in every work process with the error ORA-2068 because of an Oracle error. This is probably due to the fact that the Oracle shadow process assigned to the R/3 work process, does not register the "dying" of the corresponding shadow process on the remote DB, and therefore confirms the first access with ORA-2068. Subsequently it establishes a new link to the remote DB, and further accesses are executed correctly. As workaround for this Oracle problem an SQL statement which terminated ORA-2068 is now repeated implicitly in the R/3 kernel also for ABAP Native SQL. This kernel change is valid as of Support Package level

4.0B: 650
4.5B: 404 (it is sufficient to replace the Shared Library 'dboraslib.)

    2. Use of utilization on the remote DB.
A further problem when accessing with database links is that an Oracle (shadow) process is started when first accessing the remote database with the database link. That means, if the SQL statement in question is carried out in several R/3 work processes, just as many Oracle processes are started on the remote database. Of course, these Oracle processes allocate resources to the remote machine (even if they are no longer doing anything) and therefore are preferably not seen for a long period of time by the database administrator of the remote database.

For this reason, Oracle offers the possibility to explicitly close database links and, as a result, to release the resources allocated to the remote database again. The affected SQL command is:

ALTER SESSION CLOSE DATABASE LINK

If a remote access is only occasionally carried out, it is useful, under the circumstances, to close the database link (which is opened in the execution of the SQL statements) immediately after the carrying out of the SQL statements. However, Oracle only allows the execution of the command above if the database transaction in which the remote access is carried out commits and the respective database cursor has been closed. Otherwise, the attempt to close the database link leads to the Oracle errors ORA-2080 or ORA-2081.

If you use the remote access via ABAP Open SQL with a kernel release older than 4.6D, it is unfortunately not possible to fix this error because there is no option of closing a DB cursor after carrying out an open SQL statement in the R/3 statement cache. This means that, in this case, the database link can no longer be excluded from the ABAP program. The resources allocated to the remote database remain until the affected R/3 work process ends or the remote database is shut down. As of kernel release 4.6D, however, it is possible to close an open SQL statement immediately after executing it by using the open SQL hint SAP_FORCE_CLOSE_CURSOR. For more information about specifying hints in open SQL statements, see Note 772497. The hint SAP_FORCE_CLOSE_CURSOR is available in kernel Release 4.6D as of patch level 1391 and 6.20 as of patch level 549 (and with any higher release).

If the remote accesses are exclusively performed via ABAP Native SQL, thus the DB cursors are closed immediately after executing the SQL statement, database links can be closed explicitly again in the ABAP program also:

....
EXEC SQL.
SELECT . ... FROM @ ...
ENDEXEC.
....
EXEC SQL.
COMMIT
ENDEXEC.
* The commit is necessary, otherwise the database link
* cannot be closed (ORA-2080)

EXEC SQL.
ALTER SESSION CLOSE DATABASE LINK
ENDEXEC.
* The resources are now released again on the Remote-DB

Unlike Open SQL, Native(=EXEC) SQL statements up to and including kernel Release 4.6D are closed again immediately after they have been executed . However, as of kernel release 6.20, open and native SQL statements are processed in the same way, that is, native SQL statements are also written to the statement cache and are kept open for Oracle until they have been removed from the Cache. This means that the above code fragment no longer works as of Release 6.20 and you receive an SQL error "ORA-2080: database link is in use" when you close the database link. The old response can be re-established, however, by setting the profile parameter "dbs/ora/close_stmt_after_exec = 1". This causes all native SQL statements to be closed immediately after they are carried out, as is the case with releases earlier than 6.20
By -

1 comment:

Subscribe to: Post Comments (Atom)