Database Reference
In-Depth Information
FROM v$session
WHERE username='APP_USER'
/
Row 1 fetched. Hit enter to continue fetching ...
SID SERIAL# LOGON_TIME CLIENT_IDENTIFIER MODULE ACTION FAILED_OVER
--- ------- ---------- ----------------- -------- ------ -----------
139 76 30-JUL-07 perl.exe YES
1 Row(s) processed.
The preceding output shows that session failover has occurred (
FAILED_OVER=YES
). The
question is whether or not the transaction completed successfully.
DBB> DELETE FROM hr.employees WHERE employee_id IN (180, 190)
/
0 Row(s) Processed.
As implied by the lack of any errors, the transaction did complete successfully and the rows
were truly deleted since rerunning the identical
DELETE
statement did not find any matching rows.
The exact same functionality demonstrated is available at instance level with
SHUTDOWN
TRANSACTIONAL
. Thus, at the beginning of a maintenance window, all database sessions connected
to an instance may be shifted to other instances providing the service requested by the client.
The optional keyword
LOCAL
to
SHUTDOWN
TRANSACTIONAL
applies to distributed database envi-
ronments, not RAC environments. When
LOCAL
is specified, the instance waits only for local
transactions to complete, but not for distributed transactions.
Session Disconnection and DBMS_SERVICE
Oracle10
g
and Oracle11
g
include the package
DBMS_SERVICE
for managing instance services
and TAF. For the first time, this package allows the configuration of TAF on the server-side
instead of the client-side configuration file
tnsnames.ora
.
DBMS_SERVICE
is the most sophisti-
cated approach to services so far. In a RAC cluster, it is called behind the scenes when cluster
services are created with the DBCA, but it may be used directly in both RAC and single instance
environments. In a RAC environment, cluster database services should be configured with DBCA,
since it sets up the integration between Oracle Clusterware and
DBMS_SERVICE
. Services created
with
DBMS_SERVICE
do not restart automatically on instance startup. Oracle Clusterware performs
this task, given that the appropriate cluster resources were created by DBCA.
Setting Up Services with DBMS_SERVICE
To create a service, at least a service name and a network name must be provided. The service
name is an identifier, which is stored in the data dictionary. The network name is the instance
service name (see
Instance Service Name vs. Net Service Name
in this topic's Introduction), which is
registered with the listener and needs to be referenced as the
SERVICE_NAME
in a client-side Net
service name description in
tnsnames.ora
.
DBMS_SERVICE.CREATE_SERVICE
inserts services into the dictionary table
SERVICE$
, which
underlies the view
DBA_SERVICES
. The procedure
DBMS_SERVICE.START_SERVICE
adds the network
name to the initialization parameter
SERVICE_NAMES
, such that the service is registered with the
listener. When registering the service with the listener, the value of the initialization parameter