=====Overview===== Database services (services) are logical abstractions for managing workloads in Oracle Database. Services divide workloads into mutually disjoint groupings. Furthermore, services in Oracle are a way to direct a traffic. In the past a user connected directly to the database, that was knows as 2 tier architecture. For a lot of reasons that is not the norm anymore. A part from the security risks that created, direct connections were also hard to organize and load-balance. Especially if you have multiple instances in terms of RAC or you need READ-ONLY operation which you can push to the Data Guard. Services in either are mostly used in conjuction with the Grid Infrastructure and are separated in two groups depending on your database configuration. As we know, Oracle RAC database can be either: * Policy Managed * Admin Managed Therefore, you have different ways to create a service, depending on your configuration. =====Policy Managed===== Police Managed RAC database is divided into server pools. For example you can have the following configuration in which each database has multiple instances divided into server pools: {{ :policymanagedracdatabase.png?400 |}} In that case, the syntax for adding a service is as follows: srvctl add service -db db_unique_name -service service_name [-eval] -serverpool server_pool [-cardinality {UNIFORM | SINGLETON}] [-edition edition_name] [-netnum network_number] [-role "[PRIMARY][,PHYSICAL_STANDBY][,LOGICAL_STANDBY][,SNAPSHOT_STANDBY]" [-policy {AUTOMATIC (default) | MANUAL}] [-notification {TRUE | FALSE}] [-clbgoal {SHORT | LONG}] [-failovertype {NONE|SESSION|SELECT|TRANSACTION}] [-rlbgoal {NONE | SERVICE_TIME | THROUGHPUT}] [-dtp {TRUE | FALSE}] [-failovermethod {NONE | BASIC}] [-failoverretry failover_retries] [-failoverdelay failover_delay] [-pdb pluggable_database] [-sql_translation_profile sql_translation_profile] [-global {TRUE | FALSE}] [-maxlag max_lag_time] [-commit_outcome {TRUE|FALSE}] [-retention retention_time] [-replay_init_time replay_initiation_time (default 5 minutes, 300 seconds)] [-session_state {STATIC | DYNAMIC}] [-pqservice pq_service] [-pqpool pq_pool_list] [-force] Let us break down each parameter here, except the obvious ones (db_unique_name, service_name, server_pool, pdb) * cardinality - Uniform (run the server on ALL instances), Singleton (run it only on one instance at a time) * edition - When an edition is specified for a service, all subsequent connections that specify the service use this edition as the initial session edition. However, if a session connection specifies a different edition, then the edition specified in the session connection is used for the initial session edition. * netnum (RAC / RAC One node only) - Specifies over which network interface the service is provided * role (used with Data Guard) - Specifies the condition for automatic starting of the service. If set to **PRIMARY** and the database role is **PHYSICAL_STANDBY**, the service WON'T start automatically. * policy - Specified if the service will be authomatic started, of course depending on the "role" condition. But if set to Manual, the service will NEVER be automatically started. * notification - Enables / Disables Fast Application Notifications (FAN) for OCI Connections. * failovertype - Enables Application Continuity, if set to "TRANSACTION" and TAF (for OCI) if set to **SELECT** or **SESSION**. If you set -failovertype to TRANSACTION, then you must set -commit_outcome to **TRUE**. * clbgoal (Connection Load Balancing Goal) - Indicates to Oracle for what kind of connections this service will handle. Specify **SHORT** if you will do short transactions and **LONG** if you will use this service for BATCH jobs. * rlbgoal (Runtime Load Balance Goal) - Used by the Load Balance advisor, set to **SERVICE_TIME** to balance connections by response time. Set **THROUGHPUT** to balance connections by throughput. * dtp - Indicates whether a distributed transactio processing should be enabled for that service (either singleton in Police managed or Preferred in single instance in Admin managed) * failovermethod (Oracle RAC Only) - Specifies the TAF Failover method (backward Compatibility). * failoverretry (Application Continuity and TAF only) - For Application Continuity and TAF, this parameter determines the number of attempts to connect after an incident. * failoverdelay (For Application Continuity and TAF Only) - Specifies the delay(in seconds) between reconnect attempts per incident at failover. * sql_translation_profile - Specifies the SQL Translation Profile for Application moved from Non-Oracle Database to Oracle one. * global - Indicates whether this is a Global Data Services service. * maxlag - Maximum replication lag time in seconds. Must be a non-negative integer. The default value is ANY. * commit_outcome - Enable Transaction Guard; when set to TRUE, the commit outcome for a transaction is accessible after the transaction's session fails due to a recoverable outage. * retention - If **commit_outcome** is set to TRUE, then this parameter determines the amount of time (in seconds) that the commit outcome is retained in the database * session_state - For Application Continuity, this parameter specifies whether the session state that is not transactional is changed by the application. Oracle recommends a setting of DYNAMIC for most applications. * replay_init_time - or Application Continuity, this parameter specifies the difference between the time, in seconds, of original execution of the first operation of a request and the time that the replay is ready to start after a successful reconnect. * pqservice - A comma-delimited list of parallel query service names. * pqpool - A comma-delimited list of parallel query server pool names Phew, that was something. I am sure, I am missing something, for a complete list you can check: [[https://docs.oracle.com/database/121/RACAD/GUID-6BC01F04-C46F-47BD-9AAC-CFA88F3D5911.htm#RACAD5013|here]] Now let's check the administer based. I will discuss only the missing / different parameters =====Admin Managed===== The command to add a service in admin managed database is pretty similiar: -preferred preferred_list -available available_list] [-netnum network_number] [-tafpolicy {BASIC | NONE | PRECONNECT}] [-edition edition_name] [-role "[PRIMARY][,PHYSICAL_STANDBY][,LOGICAL_STANDBY][,SNAPSHOT_STANDBY]" [-policy {AUTOMATIC | MANUAL}] [-notification {TRUE | FALSE}] [-clbgoal {SHORT | LONG}] [-failovertype {NONE|SESSION|SELECT|TRANSACTION}] [-rlbgoal {NONE | SERVICE_TIME | THROUGHPUT}] [-dtp {TRUE | FALSE}] [-failovermethod {NONE | BASIC}] [-failoverretry failover_retries] [-failoverdelay failover_delay] [-pdb pluggable_database] [-sql_translation_profile sql_translation_profile] [-global {TRUE | FALSE}] [-maxlag max_lag_time] [-commit_outcome {TRUE|FALSE}] [-retention retention_time] [-replay_init_time replay_initiation_time] [-session_state {STATIC|DYNAMIC}] [-pqservice pq_service] [-pqpool pq_pool_list] [-force] [-verbose] * preferred (Oracle RAC Only) - List of preferred instances where the service to run. The list of preferred instances must be mutually exclusive with the list of available instances. * available (Oracle RAC Only) - List of available instances where the service CAN run in case the preferred aren't available. * tafpolicy - TAF policy specification. You can only use PRECONNECT when you specify the -preferred and -available parameters. These parameters are exclusive for a service on a admin managed database. The rest of the parameters are the same as the policy managed. If it will help you understand services better. You have to answer yourself the following questions. - Where the service will run: (Singleton/Uniform or Preferred/Available) - How we define a failover: (failoverretry/failovermethod/failoverdelay) - What happens when a failover occurs: (tafpolicy, failovertype and others) There are more, but these 3 are the main ones. Good luck. =====Conclusion===== Services are instrument through which we allow access to our database. These services can behave in different ways and it is up to us as DBAs to set them correct, so the client will be able to access his data.