Oracle | DBMS_SERVICE

Oracle DBMS_SERVICE_PRVT Version 21c

General Information

Library Note

Morgan's Library Page Header

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.

Source {ORACLE_HOME}/rdbms/admin/dbmssrv.sql

Subprograms

CREATE_SERVICE	IS_JAVA_SERVICE	RENAME_PDB_ATTRIBUTE
DELETE_SERVICE	MIGRATE_TO_12_2	START_SERVICE
GET_HASH	MODIFY_SERVICE	STOP_SERVICE
GET_TOPOLOGY

CREATE_SERVICE

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;

      /

DELETE_SERVICE

Marks a service$ entry as deleted 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;

      

      exec dbms_service_prvt.delete_service('MLIBSERV');

      

      SELECT name, network_name, pdb, edition

      FROM dba_services;

GET_HASH

Computes the hash value for a service name dbms_service_prvt.get_topology(service_name IN VARCHAR2) RETURN NUMBER; PRAGMA SUPPLEMENTAL_LOG_DATA(get_hash, READ_ONLY);

SELECT name

      FROM v$services

      ORDER BY 1;

      

      SELECT dbms_service_prvt.get_hash('SYS$BACKGROUND')

      FROM dual;

      

      DBMS_SERVICE_PRVT.GET_HASH('SYS$BACKGROUND')

      --------------------------------------------

                                         165959219

GET_TOPOLOGY

The topology function returns the instances the specified service is currently active dbms_service_prvt.get_topology(service_name IN VARCHAR2) RETURN VARCHAR2;

SELECT dbms_service_prvt.get_topology('SYS$BACKGROUND')

      FROM dual;

      

      DBMS_SERVICE_PRVT.GET_TOPOLOGY('SYS$BACKGROUND')

      -------------------------------------------------

      test21db

IS_JAVA_SERVICE

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

      

      PL/SQL procedure successfully completed.

MIGRATE_TO_12_2

Updates the service data dictionary tables to 12.2 dbms_service_prvt.migrate_to_12_2; PRAGMA SUPPLEMENTAL_LOG_DATA(migrate_to_12_2, NONE);

exec dbms_service_prvt.migrate_to_12_2;

MODIFY_SERVICE

Modifies an existing service

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

RENAME_PDB_ATTRIBUTE

Changes the pdb_name of all qualifying services to new_pdb_name dbms_service_prvt.rename_pdb_attribute( pdb_name IN VARCHAR2, new_pdb_name IN VARCHAR2); PRAGMA SUPPLEMENTAL_LOG_DATA(rename_pdb_attribute, NONE);

This has the potential to be dangerous so I am electing to not create a demo and suggest you not do so either unless you have a throw-away database.

START_SERVICE

In single instance starts the service with this service_name. In RAC will optionally start the service only on the instance specified. dbms_service_prvt.start_service( service_name IN VARCHAR2, all_nodes IN BOOLEAN DEFAULT FALSE, is_called_by_crs IN BOOLEAN DEFAULT FALSE); PRAGMA SUPPLEMENTAL_LOG_DATA(start_service, NONE);

exec dbms_service_prvt.start_service('MLIBSERV');

STOP_SERVICE

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);

exec dbms_service_prvt.stop_service('MLIBSERV');