DBMS_SERVICE

如果使用的是Oracle Restart或RAC不要用DBMS_SERVICE，而是使用SRVCTL工具操作

The DBMS_SERVICE package allows the creation, deletion, starting, and stopping of services in a single instance. Additionally, it provides the ability to disconnect all sessions that connect to an instance with a service name.

Oracle Real Application Clusters (RAC) or Oracle Restart can manage service names across instances as administered through SRVCTL.

（一）DBMS_SERVICE package provides constants

下面是DBMS_SERVICE中用到的选项值

1.  Constants Used in Calling Arguments

GOAL_NONE:

Disables Load Balancing Advisory

GOAL_SERVICE_TIME:

Load Balancing Advisory is based on elapsed time for work done in the service plus available bandwidth to the service

GOAL_THROUGHPUT：

Load Balancing Advisory is based on the rate that work is completed in the service plus available bandwidth to the service

2. Constants Used in Connection Balancing Goal Arguments

CLB_GOAL_SHORT： 

Connection load balancing uses Load Balancing Advisory, when Load Balancing Advisory is enabled (either goal_service_timeor goal_throughput). When GOAL=NONE (no load balancing advisory), connection load balancing uses an abridged advice based on CPU utilization.

CLB_GOAL_LONG：

Balances the number of connections for each instance using session count for each service. This setting is recommended for applications with long connections such as forms. This setting can be used with Load Balancing Advisory when the connection pool is sized to accommodate gravitation within the pool itself (without adding or removing connections). The latter is the most efficient design.

3.  Constants Used in High Availability Attribute Arguments for FAN, Application Continuity, Transaction Guard and TAF

FAILOVER_METHOD_NONE：

Server side TAF is not enabled for this service

FAILOVER_METHOD_BASIC：

Server side TAF method is BASIC. BASIC is the only value currently supported. This means that a new connection is established at failure time.

FAILOVER_TYPE_NONE：

Server side TAF type is NONE

FAILOVER_TYPE_SESSION：

Server side TAF failover type is SESSION. At failure time, if the failover type is SESSION, TAF reconnects to a surviving node and re-establish a vanilla database session. Customizations (for example, ALTER SESSION) must be re-executed in a failover callback.

FAILOVER_TYPE_SELECT:

Server side TAF failover type is SELECT

FAILOVER_RETRIES:

Number of connection attempts when failover occurs. Specifies the number of times for Application Continuity and TAF to attempt the reconnect and re-authenticate pair. The value must be an integer greater than 0. The default in Oracle Database 12c Release 1 (12.1) for Application Continuity is 30.

FAILOVER_RESTORE_NONE:

The initial state is not restored before replaying for Application Continuity and TAF. This is recommended for OCI applications that use Application Continuity and build their own state in the request. For example, SQLPLUS.

FAILOVER_RESTORE_BASIC:

This is the recommended value for Java and ODP.NET applications using Application Continuity. The initial states that the user knows are restored automatically before replaying. If the user needs additional states, a callback must be registered.

FAILOVER_DELAY：

Number of seconds delay between each connection attempt. This is the delay that Application Continuity and TAF waits if a reconnect and re-authentication fails. The value must be an integer greater than 0. The default in Oracle Database 12c Release 1 (12.1)is 10s when using Application Continuity. Using FAILOVER_DELAY the failover can be delayed until the service is next available. This can work well in conjunction with a planned outage that may make a service temporarily unavailable (such as for several minutes).

STOP_OPTION_NONE：

Sessions are not disconnected.

STOP_OPTION_IMMEDIATE：

Sessions are disconnected immediately after the drain_timeoutexpires.

STOP_OPTION_TRANSACTIONAL

Sessions are disconnected after the transactions during the drain_timeout. The sessions disconnect immediately when drain_timeout expires.

DYNAMIC:

For Application Continuity, this parameter specifies whether the session state that is not transactional is changed by the application during request execution. A value of DYNAMIC is recommended for all applications. If you are in any doubt, or the application can be customized, you must use DYNAMIC.

Note: If a TAF callback has been registered, then the failover retries and failover delay are ignored. If an error occurs, TAF continues to re-attempt the connect and authentication as long as the callback returns a value of OCI_FO_RETRY. Any delay must be coded into the callback logic

（二）CREATE_SERVICE Procedure

使用ALTER SYSTEM SET SERVICE_NAMES会隐式在数据字典创建service，并自动开始此服务，你可以使用下面过程来start/stop/modify/disconnect更改此服务，但不能delete

使用create_service创建服务后来需要start_service启用服务才会用

CREATE_SERVICE过程中有service的详细属性, service的属性应该有不少是相关JDBC/OCI的连接属性的。目前感觉用途不大，集群服务端的TAF通过srvctl设置，所以也不会使用此包。用时再了解吧

This procedure creates a service name in the data dictionary. Services are also created in the data dictionary implicitly when you set the service in the service_name parameter or by means of the ALTER SYSTEM SET SERVICE_NAMES command.

DBMS_SERVICE.CREATE_SERVICE(

   service_name               IN VARCHAR2,

   network_name               IN VARCHAR2,

   parameter_array            IN TABLE OF VARCHAR2(100));

DBMS_SERVICE.CREATE_SERVICE(

   service_name               IN VARCHAR2,

   network_name              IN VARCHAR2,

   goal                       IN NUMBER DEFAULT NULL,

   dtp                        IN BOOLEAN DEFAULT NULL,

   aq_ha_notifications        IN BOOLEAN DEFAULT NULL,

   failover_method            IN VARCHAR2 DEFAULT NULL,

   failover_type              IN VARCHAR2 DEFAULT NULL,

   failover_retries           IN NUMBER DEFAULT NULL,

   failover_delay             IN NUMBER DEFAULT NULL,

   clb_goal                   IN NUMBER DEFAULT NULL,

   edition                    IN VARCHAR2 DEFAULT NULL);

1. Service_name与network_name:

Service_name为数据库服务名，network_name是用service_name注册到监听的监听服务名

1. parameter_array表示service属性的数组，使用见下面的示例
2. goal: Runtime Load Balancing Goal being used to create run-time load balancing and connection load balancing advice. 不设置为则为none

Workload management goal directive for the service. Valid values:

1. dtp: Declares the service to be for X/Open Distributed Transaction Processing (DTP) or any distributed transaction (especially XA)
2. aq_ha_notifications: Determines whether Fast Application Notification (FAN) is enabled for OCI/OCCI/ODP. In Oracle Database12c, FAN uses Oracle Notification Services (ONS). This parameter is still used to enable FAN. FAN is recommended for all High Availability systems, and is on by default for Application Continuity
3. failover_method

Failover TYPE for the service for Application Continuity and TAF. 

If the failover_type is set to TRANSACTION on the service, this automatically sets COMMIT_OUTCOME to TRUE.

JDBC Replay Driver uses the FAILOVER_TYPE service attribute setting of TRANSACTION for TRANSACTION failover.

OCI uses the older settings of SELECT and SESSION.

The server only accepts FAILOVER_METHOD = BASIC with the TRANSACTION setting.

failover_type

TRANSACTION for Application Continuity. SELECT or BASIC for TAF.

failover_retries

Number of connection retries for Application Continuity and TAF. Using the failover_retries and failover_delay parameters, the failover can be delayed until the service is next available. This parameter is for connecting. It does not control the number of failovers, which is 3 for each incident for Application Continuity.

failover_delay

Delay in seconds between connection retries for Application Continuity and TAF. The default is 10 seconds for Application Continuity. Do not use a 0-second delay if the service needs time to failover and register. Long delays are good for planned outages and to failover to Data Guard. Short delays work well with RAC when the service is already available.

edition

If this argument has a non-NULL value, this provides the initial session edition for subsequent database connections using this service that do not specify an edition. If no value is specified, this argument has no effect.

During service creation or modification, no validation is performed on this parameter.

At connection time, if the connecting user does not have USE privilege on the edition, or the edition does not exist, this raises the error ORA-38802 (edition does not exist).

drain_timeout

If this parameter is defined, all sessions connected to that service are drained by the client drivers and pools using Fast Connection Failover (FCF). The drain_timeout can be set on the service, so stopping and relocating drains for this time by default.

stop_option

Provides options to terminate a session. The supported values are:

TRANSACTIONAL：drain_timeout applies to the transactions. After the drain_timeout expire, the sessions are immediately killed.

IMMEDIATE：The sessions are killed after drain_timeout expires.

NONE：Sessions are not terminated. However, these parameter values can be overridden using the command line.

commit_outcome

Determines whether transaction COMMIT outcome is accessible after the COMMIT has executed. While the database guarantees that COMMIT is durable, this ensures that the outcome of the COMMIT is durable. Applications use the feature to probe the status of the commit last executed after an outage, and is available to applications to determine an outcome. Note:

Invoking the GET_LTXID_OUTCOME Procedure of the DBMS_APP_CONT package requires that the commit_outcome attribute be set

commit_outcome has no effect on active Data Guard and read-only databases.

commit_outcome is only allowed on the database service and on user-defined database services

retention_timeout

Used in conjunction with commit_outcome, it determines the amount of time (in seconds) that the COMMIT_OUTCOME is retained. Default is 24 hours (86400). Maximum value is 30 days (2592000).

replay_initiation_timeout

For Application Continuity, replay_initiation_timeout is the difference between the time of original execution of first operation of a request, and the time that the replay is ready to start after a successful reconnect. Replay initiation time is measured from the time that the request was originally submitted until the time that replay has connected and is ready to replay. When replay is expected, keep this value high. Default is 900 seconds.

session_state_consistency

Describes how nontransactional is changed during a request (values are DYNAMIC or STATIC). This parameter is considered only if failover_type is set to TRANSACTION for Application Continuity. Examples of session state are NLS settings, optimizer preferences, event settings, PL/SQL global variables, temporary tables, advanced queues, LOBs, and result cache. If these values change after the request starts, set to DYNAMIC (default). Almost all applications should use DYNAMIC mode. If you are unsure, use DYNAMIC mode.

sql_translation_name

Name of SQL translation unit

clb_goal

Method used for Connection Load Balancing (see Table 144-2)

DBMS_SERVICE.CREATE_SERVICE('ernie.example.com','ernie.example.com');

DECLARE

   params dbms_service.svc_parameter_array;

   BEGIN

      params('FAILOVER_TYPE')            :='TRANSACTION';

      params('REPLAY_INITIATION_TIMEOUT'):=1800;

      params('RETENTION_TIMEOUT')        :=86400;

      params('FAILOVER_DELAY')           :=10;

      params('FAILOVER_RETRIES')         :=30;

      params('DRAIN_TIMEOUT')            :=60;

      params('STOP_OPTION')              :='DBMS_SERVICE.STOP_OPTION_IMMEDIATE';

      params('FAILOVER_RESTORE')         :='DBMS_SERVICE.FAILOVER_RESTORE_BASIC';

      params('commit_outcome')           :='true';

      params('aq_ha_notifications')      :='true';

      DBMS_SERVICE.MODIFY_SERVICE('GOLD',params);

   END;

（三）DELETE_SERVICE Procedure

DBMS_SERVICE.DELETE_SERVICE(

   service_name   IN VARCHAR2);

（四）DISCONNECT_SESSION Procedure

This procedure disconnects sessions with the named service at the current instance.

DBMS_SERVICE.DISCONNECT_SESSION(

   service_name         IN VARCHAR2,

   disconnect_option      IN NUMBER DEFAULT POST_TRANSACTION;

disconnect_option：

The options, package constants, are expressed as NUMBER:

1. POST_TRANSACTION = 0:

session disconnects after the current transaction commits or rolls back

1. IMMEDIATE = 1: session disconnects immediately
2. NOREPLAY = 2: session disconnects immediately and be flagged to not be replayed by application continuity, that is IMMEDIATE and NOREPLAY together

Note: 使用上面的值时不要加单引或双引号 

IMMEDIATE or POST_TRANSACTION and NOREPLAY is automatically translated as 1 or 0 or 2 respectively. However, passing a string literal (quoted using either the ' or " characters, such as "IMMEDIATE" or 'POST_TRANSACTION' or 'NOREPLAY') raises an error.

This procedure can be used in the context of a single instance as well as with Oracle Real Application Clusters.

This subprogram does not return until all corresponding sessions are disconnected. Therefore, use the DBMS_JOB package or put the SQL session in background if the caller does not want to wait for all corresponding sessions to be disconnected.

DBMS_SERVICE.DISCONNECT_SESSION('ernie.example.com');

If a service is using application continuity, and you do not want the sessions replayed but simply terminated, use the following:

EXECUTE DBMS_SERVICE.DISCONNECT_SESSION('service name', DBMS_SERVICE.NOREPLAY);

（五）MODIFY_SERVICE Procedure

DBMS_SERVICE.MODIFY_SERVICE(

   service_name               IN VARCHAR2,

   parameter_array            IN svc_parameter_array);

This overload is maintained for backward compatibility:

DBMS_SERVICE.MODIFY_SERVICE(

   service_name               IN VARCHAR2,

   goal                       IN NUMBER DEFAULT NULL,

   dtp                        IN BOOLEAN DEFAULT NULL,

   aq_ha_notifications        IN BOOLEAN DEFAULT NULL,

   failover_method            IN VARCHAR2 DEFAULT NULL,

   failover_type              IN VARCHAR2 DEFAULT NULL,

   failover_retries           IN NUMBER DEFAULT NULL,

   failover_delay             IN NUMBER DEFAULT NULL,

   clb_goal                   IN NUMBER DEFAULT NULL,

   edition                    IN VARCHAR2 DEFAULT NULL,

   modify_edition             IN BOOLEAN DEFAULT FALSE;

Although users can modify the edition attribute while the service is up and running, it may not be safe to do so. Users must proceed with caution because this causes new connections to be connected at the new edition, while the existing connection is not affected. This can cause mid-tier operations to connect to the wrong edition.

（六）START_SERVICE Procedure

This procedure starts a service.

DBMS_SERVICE.START_SERVICE(

   service_name  IN VARCHAR2,

   instance_name IN VARCHAR2);

instance_name:

Name of the instance where the service must be activated (optional). 

NULL results in starting of the service on the local instance.

In single instance, this can only be the current instance or NULL.

Specify DBMS_SERVICE.ALL_INSTANCES to start the service on all configured instances.

Examples

DBMS_SERVICE.START_SERVICE('ernie.example.com');

（七）STOP_SERVICE Procedure

DBMS_SERVICE.STOP_SERVICE(

SERVICE_NAME    IN VARCHAR2,

INSTANCE_NAME  IN VARCHAR2,   

STOP_OPTION   IN VARCHAR2,

DRAIN_TIMEOUT IN NUMBER,

NOREPLAY        IN BOOLEAN);

drain_timeout:The time in seconds for the session to drain.

stop_option:

To specify how sessions are stopped with draining. The possible values are as follows:

IMMEDIATE: sessions are aborted immediately after the time specified in drain_timeout.

TRANASCTIONAL: applies for transactions. After the transaction expires, the sessions are immediately terminated.

NONE: sessions are not terminated.

Noreplay :Disconnected sessions are not recovered by Application Continuity.