ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
Purpose
This package allows an application to manage services and sessions connected with a specific service name. The difference to the previous package is that additional parameters like the global flag can be set.
Oracle Real Application Cluster (RAC) has a functionality to manage service names across instances. This package allows the creation, deletion, starting and stopping of services in both RAC and single instance.
Additionally it provides the ability to disconnect all sessions which connect to the instance with a service name when RAC removes that service name from the instance.
This package was specifically defined with GSM in mind. Prospective users are advised to talk to the file owner before using this package.
AUTHID
DEFINER
Data Types
TYPE svc_parameter_array IS TABLE OF VARCHAR2(100)
INDEX BY VARCHAR2(100);
Dependencies
DBA_SERVICES
DBMS_SERVICE_ERR
SERVICE$
DBMS_GSM_CLOUDADMIN
DBMS_SERVICE_PRVT_LIB
SVC_PARAMETER_LIST_T
DBMS_GSM_DBADMIN
GV$ACTIVE_SERVICES
SVC_PARAMETER_T
DBMS_SERVICE
GV$INSTANCE
V$PDBS
DBMS_SERVICE_CONST
PLITBLM
X$KSUSE
Documented
No
Exceptions
Error Code
Reason
ORA-44304
Service <service_name> does not exist
First Available
12.1
Security Model
Owned by SYS with with EXECUTE granted to the DBA and GSMADMIN_INTERNAL roles and SYSRAC.
Creates a new service$ entry for this service name
dbms_service_prvt.create_service(
service_name IN VARCHAR2,
network_name IN VARCHAR2,
cluster_attributes IN svc_parameter_array,
db_attributes IN svc_parameter_array,
is_called_by_crs IN BOOLEAN DEFAULT FALSE,
srvc_context IN NUMBER DEFAULT 1);
PRAGMA SUPPLEMENTAL_LOG_DATA(create_service, NONE);
Cluster Attributes
available
A comma separated list of available databases
locality
Service region locality. Must be ANYWHERE or LOCAL_ONLY
region_failover
Service is enabled for region failover
role
Database role the database must be in to start this service
preferred
A comma separated list of preferred databases
preferred_all
All databases in the pool are preferred
Data Guard Broker Atributes
failover_primary
Enable service to failover to primary. This is only applicable to services with the role PHYSICAL_STANDBY.
lag
Specifes the lag of the service
policy
Management policy for the service. Can be automatic or manual.
tafpolicy
TAF client policy
Database Attributes
aq_ha_notifications
Determines whether HA events are sent via AQ for this service.
commit_outcome
Persist outcome of transactions
dtp
Declares the service to be for DTP or distributed transactions.
edition
The initial session edition
failover_delay
The TAF failover delay for the service
failover_method
The TAF failover method for the service
failover_retries
The TAF failover retries for the service
failover_type
The TAF failover type for the service
global
Global service
goal
The workload management goal directive of the service. Valid values
-- are : DBMS_SERVICE.GOAL_SERVICE_TIME,
-- DBMS_SERVICE.GOAL_THROUGHPUT,
-- DBMS_SERVICE.GOAL_NONE.
is_called_by_crs
Is this function invoked by CRS? (MUST ONLY BE SET BY CRS)
pdb
The initial pdb
retention_timeout
Timeout when the transaction outcome is retained
replay_initiation_timeout
Timeout when replayed is disabled
session_state_consistency
Consistency of session state: static or dynamic
sql_translation_name
Name of SQL translation unit
srvc_context
Which service context does this apply to (DB and/or OCR)?
col network_name format a20
col edition format a30
SELECT name, network_name, pdb, edition
FROM dba_services;
DECLARE
sn VARCHAR2(30) := 'MLIBSERV';
nn VARCHAR2(30) := 'MLIBSERV';
ca dbms_service_prvt.svc_parameter_array;
da dbms_service_prvt.svc_parameter_array;
BEGIN
dbms_service_prvt.create_service(sn, nn, ca, da, NULL, 1);
END;
/
dbms_service_prvt.delete_service(
service_name IN VARCHAR2,
is_called_by_crs IN BOOLEAN DEFAULT FALSE,
srvc_context IN NUMBER DEFAULT 1);
PRAGMA SUPPLEMENTAL_LOG_DATA(delete_service, NONE);
SELECT name, network_name, pdb, edition
FROM dba_services;
Returns TRUE if any of the session connected with this service have ever used java in the database
dbms_service_prvt.is_java_service(service_name IN VARCHAR2)
RETURN BOOLEAN;
PRAGMA SUPPLEMENTAL_LOG_DATA(is_java_service, READ_ONLY);
BEGIN
IF dbms_service_prvt.is_java_service('SYS$BACKGROUND') THEN
dbms_output.put_line('TRUE');
ELSE
dbms_output.put_line('FALSE');
END IF;
END;
/ FALSE
dbms_service.modify_service(
service_name IN VARCHAR2,
cluster_attributes IN svc_parameter_array,
db_attributes IN svc_parameter_array,
is_called_by_crs IN BOOLEAN DEFAULT FALSE,
srvc_context IN NUMBER DEFAULT 1);
PRAGMA SUPPLEMENTAL_LOG_DATA(modify_service, NONE);
CLUSTER_ATTRIBUTE Values
DB_ATTRIBUTES Values
available
aq_ha_notifications
failover_primary
commit_outcome
lag
dtp
locality
edition
policy
failover_delay
preferred
failover_method
preferred_all
failover_retries
region_failover
failover_type
role
global
tafpolicy
is_called_by_crs
pdb
replay_initiation_timeout
retention_timeout
session_state_consistency
sql_translation_name
srvc_context
col network_name format a20
col edition format a30
SELECT name, network_name, pdb, edition
FROM dba_services;
DECLARE
sn VARCHAR2(30) := 'MLIBSERV';
ca dbms_service_prvt.svc_parameter_array;
da dbms_service_prvt.svc_parameter_array;
BEGIN
da('EDITION') := 'ORA$BASE';
dbms_service_prvt.modify_service(sn, ca, da, FALSE, 1);
END;
/
col name format a55
col network_name format a55
SELECT name, network_name
FROM dba_services;
NAME
NETWORK_NAME
-------------------------------------------------------
-----------------------------------------------------
SYS$BACKGROUND
SYS$USERS
test21dbXDB
test21dbXDB
test21db_iad25g.sub07212035450.testvcn01.oraclevcn.com
test21db_iad25g.sub07212035450.testvcn01.oraclevcn.com
In single instance it stops the service specified by service_name. In RAC will call out to CRS to stop the service, optionally on the instance specified. Calls clscrs_stop_resource.
dbms_service_prvt.stop_service(
service_name IN VARCHAR2,
all_nodes IN BOOLEAN DEFAULT FALSE,
is_called_by_crs IN BOOLEAN DEFAULT FALSE,
stop_option IN VARCHAR2 DEFAULT NULL,
drain_timeout IN NUMBER DEFAULT NULL,
replay IN BOOLEAN DEFAULT TRUE);
PRAGMA SUPPLEMENTAL_LOG_DATA(stop_service, NONE);