Managing Oracle Database Workloads with Services

1. Managing Oracle Database Workloads with Services Pete Dinin NEOOUG Training Seminar May 22, 2009

2. Overview Definitions and background Creating and managing database services Applications of database services

3. Definition – Database Services First introduced in Oracle 8i. Services are logical abstractions for managing workloads. Services divide workloads into mutually disjoint groupings. Each service represents a workload with common attributes, service-level thresholds, and priorities. First introduced in Oracle 8i. Services are logical abstractions for managing workloads. Services divide workloads into mutually disjoint groupings. Each service represents a workload with common attributes, service-level thresholds, and priorities. First introduced in Oracle 8i. Services are logical abstractions for managing workloads. Services divide workloads into mutually disjoint groupings. Each service represents a workload with common attributes, service-level thresholds, and priorities.

4. Connect to Instance (pre 8i) Client connects to listener with a requested instance name (oracle_sid). Listener connects to the instance name requested by the client. Tnsnames connect string specifies host, port and instance name. Client connects to listener with a requested instance name (oracle_sid). Listener connects to the instance name requested by the client. Tnsnames connect string specifies host, port and instance name.Client connects to listener with a requested instance name (oracle_sid). Listener connects to the instance name requested by the client. Tnsnames connect string specifies host, port and instance name.

5. Connect to Service (8i +) Client connects to listener with a requested service name. Listener connects to an instance where the requested service is running. Tnsnames connect string specifies host, port and service name. Client connects to listener with a requested service name. Listener connects to an instance where the requested service is running. Tnsnames connect string specifies host, port and service name.Client connects to listener with a requested service name. Listener connects to an instance where the requested service is running. Tnsnames connect string specifies host, port and service name.

6. Tnsnames Examples Connect to Instance: TEST_INSTANCE = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = testhost)(PORT = 1521)) ) (CONNECT_DATA = (SID = test) ) ) Connect to Service: TEST_SERVICE = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = testhost)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = test_service) ) ) This is an example of a conenct to sid tnsnames entry and a connect to service tnsnames entry This is an example of a conenct to sid tnsnames entry and a connect to service tnsnames entry

7. Service States Within an instance, a service is binary: Started Stopped (not started) Within a clustered database, a service can be mapped to an instance in three ways: Preferred – CRS starts service on instance. Available – CRS starts service on an available instance if it cannot run on an available instance. Not Used – CRS does not use instance for service. Within an instance, a service is binary: Started Stopped (not started) Within a clustered database, a service can be mapped to an instance in three ways: Preferred – CRS starts service on instance. Available –CRS starts service on an available instance if it cannot run on an available instance. Not Used – CRS does not use instance for service.Within an instance, a service is binary: Started Stopped (not started) Within a clustered database, a service can be mapped to an instance in three ways: Preferred – CRS starts service on instance. Available –CRS starts service on an available instance if it cannot run on an available instance. Not Used – CRS does not use instance for service.

8. Multiple Services Instances can (should) have multiple services. A service does not always need to be started.

9. Multiple Instances With Real Application Cluster (RAC) databases, services are required. A service can run on some or all instances.

10. Listener Registration Each instance of database service “registers” with listeners defined in local_listener and remote_listener init.ora parameters. When a connection request is processed by the listener, it “knows” where the service is running, and will pass the connection to the appropriate instance. If the listener does not know of any instances where the service is running, an error is returned. Each instance of database service “registers” with listeners defined in local_listener and remote_listener init.ora parameters. When a connection request is processed by the listener, it “knows” where the service is running, and will pass the connection to the appropriate instance. If the listener does not know of any instances where the service is running, an error is returned.Each instance of database service “registers” with listeners defined in local_listener and remote_listener init.ora parameters. When a connection request is processed by the listener, it “knows” where the service is running, and will pass the connection to the appropriate instance. If the listener does not know of any instances where the service is running, an error is returned.

11. Creating Services Without CRS dbms_service.create_service service_names init.ora parameter With CRS srvctl add service Grid Control – Database > Maintenance > Cluster Managed Database Services dbms_service.modify_service for additional service parameters (failover properties) Without CRS dbms_service.create_service service_names init.ora parameter With CRS srvctl add service Grid Control – Database > Maintenance > Cluster Managed Database Services dbms_service.modify_service for additional service parameters (failover properties)Without CRS dbms_service.create_service service_names init.ora parameter With CRS srvctl add service Grid Control – Database > Maintenance > Cluster Managed Database Services dbms_service.modify_service for additional service parameters (failover properties)

12. Managing Services – Single Instance Starting & Stopping dbms_service.start_service dbms_service.stop_service alter system set service_names='<list of all services to be running>'; Automatic startup Set service_names init.ora parameter to list of all services to be started at instance startup. Create a startup trigger with logic to determine which services to start using dbms_service.start_service. Starting & Stopping dbms_service.start_service dbms_service.stop_service alter system set service_names='<list of all services to be running>'; Automatic startup Set service_names init.ora parameter to list of all services to be started at instance startup. Create a startup trigger with logic to determine which services to start using dbms_service.start_service.Starting & Stopping dbms_service.start_service dbms_service.stop_service alter system set service_names='<list of all services to be running>'; Automatic startup Set service_names init.ora parameter to list of all services to be started at instance startup. Create a startup trigger with logic to determine which services to start using dbms_service.start_service.

13. Managing Services - RAC Starting and Stopping srvctl start/stop service Grid Control – Database > Maintenance > Cluster Managed Database Services CRS can automatically start services after databases start. Modifying RAC services srvctl modify service Grid Control – Database > Maintenance > Cluster Managed Database Services dbms_service.modify_service can configure failover characteristics of a service Starting and Stopping srvctl start/stop service Grid Control – Database > Maintenance > Cluster Managed Database Services CRS can automatically start services after databases start. Modifying RAC services srvctl modify service Grid Control – Database > Maintenance > Cluster Managed Database Services dbms_service.modify_service can configure failover characteristics of a service Starting and Stopping srvctl start/stop service Grid Control – Database > Maintenance > Cluster Managed Database Services CRS can automatically start services after databases start. Modifying RAC services srvctl modify service Grid Control – Database > Maintenance > Cluster Managed Database Services dbms_service.modify_service can configure failover characteristics of a service

14. Default Services SYS$USERS is the service assigned to a connection made without specifying a service name, such as bequeath or by instance name. SYS$BACKGROUND is assigned to database background processes. RAC Default Service (dbname.domain) is created for all RAC Databases, and runs on all running instances. Default services cannot be managed by the DBA. SYS$USERS is the service assigned to a connection made without specifying a service name, such as bequeath or by instance name. SYS$BACKGROUND is assigned to database background processes. RAC Default Service (dbname.domain) is created for all RAC Databases, and runs on all running instances. Default services cannot be managed by the DBA.SYS$USERS is the service assigned to a connection made without specifying a service name, such as bequeath or by instance name. SYS$BACKGROUND is assigned to database background processes. RAC Default Service (dbname.domain) is created for all RAC Databases, and runs on all running instances. Default services cannot be managed by the DBA.

15. Implementation Tips Define a service for all applications. Do not include instance name in connect strings. If you must connect to a specific RAC instance, create a service for it. Avoid bequeath connections. With RAC, avoid connections using the “dbname.domain” default service, since manageability is limited. Maximum number of services varies by version 10gR1: 64 services (including defaults) 10gR2 and 11gR1: 118 services (including defaults) Certain database features, such as DataPump and Streams, use services as well. Define a service for all applications. Do not include instance name in connect strings. If you must connect to a specific RAC instance, create a service for it. Avoid bequeath connections. With RAC, avoid connections using the “dbname.domain” default service, since manageability is limited. Maximum number of services varies by version 10gR1: 64 services (including defaults) 10gR2 and 11gR1: 118 services (including defaults) Certain database features, such as DataPump and Streams, use services as well.Define a service for all applications. Do not include instance name in connect strings. If you must connect to a specific RAC instance, create a service for it. Avoid bequeath connections. With RAC, avoid connections using the “dbname.domain” default service, since manageability is limited. Maximum number of services varies by version 10gR1: 64 services (including defaults) 10gR2 and 11gR1: 118 services (including defaults) Certain database features, such as DataPump and Streams, use services as well.

16. Applications of Services The following slides are examples of how Database Services can be used in the database infrastructure. Each example requires database services to have been configured to gain the most benefit. The following slides are examples of how Database Services can be used in the database infrastructure. Each example requires database services to have been configured to gain the most benefit.The following slides are examples of how Database Services can be used in the database infrastructure. Each example requires database services to have been configured to gain the most benefit.

17. Connection Control Control access to the database at the service level. Some examples: Stop a service used by an application middle-tier while upgrading an application. Stop ad-hoc query users from connecting during batch processing windows. Normally a stop command only stops new connections. Using dbms_service.disconnect_session or srvctl stop service (with the -f flag), existing connections to that service can be killed as well. Control access to the database at the service level. Some examples: Stop a service used by an application middle-tier while upgrading an application. Stop ad-hoc query users from connecting during batch processing windows. Normally a stop command only stops new connections. Using dbms_service.disconnect_session or srvctl stop service (with the -f flag), existing connections to that service can be killed as well. Control access to the database at the service level. Some examples: Stop a service used by an application middle-tier while upgrading an application. Stop ad-hoc query users from connecting during batch processing windows. Normally a stop command only stops new connections. Using dbms_service.disconnect_session or srvctl stop service (with the -f flag), existing connections to that service can be killed as well.

18. SQL Tracing a Service SQL Tracing can be enabled at the service level. Ideal if you have an application with connection pooling, where you do not know which session contains the code you want to trace. dbms_monitor.serv_mod_act_trace_enable ('service_name'); dbms_monitor.serv_mod_act_trace_disable ('service_name'); SQL Tracing can be enabled at the service level. Ideal if you have an application with connection pooling, where you do not know which session contains the code you want to trace. dbms_monitor.serv_mod_act_trace_enable ('service_name'); dbms_monitor.serv_mod_act_trace_disable ('service_name'); SQL Tracing can be enabled at the service level. Ideal if you have an application with connection pooling, where you do not know which session contains the code you want to trace. dbms_monitor.serv_mod_act_trace_enable ('service_name'); dbms_monitor.serv_mod_act_trace_disable ('service_name');

19. Performance Views by Service Available for sql queries: Service_name column in v$session Service-level performance views which contain the same data as the corresponding system and session views. v$service_event v$service_wait_class v$service_stats v$active_session_history contains a column service_hash, which can be joined to the name_hash column of dba_services to get the service_name. Service-level views are also presented in Grid Control under Instance Performance > Top Consumers Available for sql queries: Service_name column in v$session Service-level performance views which contain the same data as the corresponding system and session views. v$service_event v$service_wait_class v$service_stats v$active_session_history contains a column service_hash, which can be joined to the name_hash column of dba_services to get the service_name. Service-level views are also presented in Grid Control under Instance Performance > Top Consumers Available for sql queries: Service_name column in v$session Service-level performance views which contain the same data as the corresponding system and session views. v$service_event v$service_wait_class v$service_stats v$active_session_history contains a column service_hash, which can be joined to the name_hash column of dba_services to get the service_name. Service-level views are also presented in Grid Control under Instance Performance > Top Consumers

20. System Utilization by App When multiple applications share a database, what percentage of the workload belongs to each application? If each application has a service, you can use any service-level metric. Sample using DB CPU and ignoring SYS services: SELECT service_name, TRUNC (RATIO_TO_REPORT (SUM (VALUE)) OVER (), 4) * 100 percentage FROM gv$service_stats WHERE stat_name = 'DB CPU' AND service_name NOT LIKE 'SYS%' GROUP BY service_name ORDER BY 2 DESC; This can be used for TCO calculations as well. When multiple applications share a database, what percentage of the workload belongs to each application? If each application has a service, you can use any service-level metric. Sample using DB CPU and ignoring SYS services: SELECT service_name, TRUNC (RATIO_TO_REPORT (SUM (VALUE)) OVER (), 4) * 100 percentage FROM gv$service_stats WHERE stat_name = 'DB CPU' AND service_name NOT LIKE 'SYS%' GROUP BY service_name ORDER BY 2 DESC; This can be used for TCO calculations as well.When multiple applications share a database, what percentage of the workload belongs to each application? If each application has a service, you can use any service-level metric. Sample using DB CPU and ignoring SYS services: SELECT service_name, TRUNC (RATIO_TO_REPORT (SUM (VALUE)) OVER (), 4) * 100 percentage FROM gv$service_stats WHERE stat_name = 'DB CPU' AND service_name NOT LIKE 'SYS%' GROUP BY service_name ORDER BY 2 DESC; This can be used for TCO calculations as well.

21. 11g Scheduler With the 11g Oracle Scheduler, job classes can be defined for jobs. Job classes can be defined with a service name. This will cause the jobs in that job class to run on the specified database service. With the 11g Oracle Scheduler, job classes can be defined for jobs. Job classes can be defined with a service name. This will cause the jobs in that job class to run on the specified database service.With the 11g Oracle Scheduler, job classes can be defined for jobs. Job classes can be defined with a service name. This will cause the jobs in that job class to run on the specified database service.

22. Database Resource Manager The Database Resource Manager allows priority to be given to user processes based on consumer groups. Consumer groups can be defined based on various client attributes, including service name. This could allow certain services to have a higher priority in the database than others. The Database Resource Manager allows priority to be given to user processes based on consumer groups. Consumer groups can be defined based on various client attributes, including service name. This could allow certain services to have a higher priority in the database than others.The Database Resource Manager allows priority to be given to user processes based on consumer groups. Consumer groups can be defined based on various client attributes, including service name. This could allow certain services to have a higher priority in the database than others.

23. DataGuard DataGuard enables failover to standby database. But how do clients find the new database location ? Primary Database Configuration Local listener references local machine Remote listener references standby machine Service initially started Standby Database Configuration Local listener references standby machine Remote listener references primary machine Service initially stopped Tnsnames entry for service references both listeners. Client-side load balancing (load_balance=yes) is optional, however is shown in the following example. DataGuard enables failover to standby database. But how do clients find the new database location ? Primary Database Configuration Local listener references local machine Remote listener references standby machine Service initially started Standby Database Configuration Local listener references standby machine Remote listener references primary machine Service initially stopped Tnsnames entry for service references both listeners. Client-side load balancing (load_balance=yes) is optional, however is shown in the following example.DataGuard enables failover to standby database. But how do clients find the new database location ? Primary Database Configuration Local listener references local machine Remote listener references standby machine Service initially started Standby Database Configuration Local listener references standby machine Remote listener references primary machine Service initially stopped Tnsnames entry for service references both listeners. Client-side load balancing (load_balance=yes) is optional, however is shown in the following example.

24. DataGuard Example Normally, both listeners redirect clients to the service on the primary database.

25. Moving an Application An application needs to be moved from one shared database to another. Assumes that tnsnames or other centralized naming is used. Move all underlying schemas and database objects to the new database. Create and start the service on new database. Edit the tnsnames entry for the service to direct to the new listener (if necessary). There is no need to change any connection strings in the application tier. To avoid tnsnames changes altogether, the old listener redirect connections to the new database using techniques described for DataGuard. An application needs to be moved from one shared database to another. Assumes that tnsnames or other centralized naming is used. Move all underlying schemas and database objects to the new database. Create and start the service on new database. Edit the tnsnames entry for the service to direct to the new listener (if necessary). There is no need to change any connection strings in the application tier. To avoid tnsnames changes altogether, the old listener redirect connections to the new database using techniques described for DataGuard. An application needs to be moved from one shared database to another. Assumes that tnsnames or other centralized naming is used. Move all underlying schemas and database objects to the new database. Create and start the service on new database. Edit the tnsnames entry for the service to direct to the new listener (if necessary). There is no need to change any connection strings in the application tier. To avoid tnsnames changes altogether, the old listener redirect connections to the new database using techniques described for DataGuard.

26. RAC Workload Distribution Each application shown in the diagram above has a corresponding database service. To move an application’s workload to a different instance, just re-configure the database service. Each application shown in the diagram above has a corresponding database service. To move an application’s workload to a different instance, just re-configure the database service.Each application shown in the diagram above has a corresponding database service. To move an application’s workload to a different instance, just re-configure the database service.

27. RAC Instance Failover Within a service an instance can be preferred (green) or available (yellow).

28. RAC Parallel Slaves – 11g 10g – Parallel slaves are managed with parallel_instance_group and instance_group parameters. 11g – Parallel slaves have “service affinity”. Parallel slaves will be spawned on instances where the parent session’s service is a preferred instance. Note that the parallel_instance_group and instance_group parameters are deprecated in 11g as well. 10g – Parallel slaves are managed with parallel_instance_group and instance_group parameters. 11g – Parallel slaves have “service affinity”. Parallel slaves will be spawned on instances where the parent session’s service is a preferred instance. Note that the parallel_instance_group and instance_group parameters are deprecated in 11g as well.10g – Parallel slaves are managed with parallel_instance_group and instance_group parameters. 11g – Parallel slaves have “service affinity”. Parallel slaves will be spawned on instances where the parent session’s service is a preferred instance. Note that the parallel_instance_group and instance_group parameters are deprecated in 11g as well.

29. Thank you. Thank you for your time and attention. If you have any additional questions, please feel free to contact me at pete@dinin.net