In the last post of this series about Oracle net services, I talked about how services can help you identify performance issues faster and easier by tagging connections with service names. Today I am introducing you to the idea of temporarily disabling connections during maintenance with the help of services.
During deployments, testing or reorganizations it might be necessary to prevent clients from connecting to the database while still allowing access for DBAs to do their work. Some methods to do this include temporarily locking application user accounts or putting the database in quiesce mode. But with services, you now also have a more tactical approach to this issue.
My example assumes a single instance with two services DEMO_BATCH and DEMO_OLTP. And let’s assume that we need to temporarily disable batch services, maybe just to reduce system load due to those activities or maybe because we are reorganizing the objects used by the batch processes.
To disable a service in a single instance we can either remove it from the SERVICE_NAMES instance parameter or use the DBMS_SERVICE package.
SELECT NAME FROM V$ACTIVE_SERVICES; NAME ---------------------------------------------------------------- DEMO_BATCH DEMO_OLTP ORCLXDB ORCL.PYTHIAN.COM SYS$BACKGROUND SYS$USERS exec DBMS_SERVICE.STOP_SERVICE('DEMO_BATCH'); PL/SQL procedure successfully completed.
New sessions using the service name will receive an ORA-12514 error when trying to connect:
brbook:~ brost$ ./sqlcl/bin/sql email@example.com:1521/DEMO_BATCH.PYTHIAN.COM SQLcl: Release 184.108.40.206.175.1027 RC on Thu Aug 18 13:12:27 2016 Copyright (c) 1982, 2016, Oracle. All rights reserved. USER = brost URL = jdbc:oracle:thin:@192.168.78.101:1521/DEMO_BATCH.PYTHIAN.COM Error Message = Listener refused the connection with the following error: ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
Existing sessions are allowed to continue
Note that stopping will only affect new connections. Existing sessions that used the DEMO_BATCH service are allowed to continue until they disconnect or you kill them. This gives you the flexibility of a grace period where you just wait for existing sessions to finish their work and disconnect by themselves.
SELECT NAME FROM V$ACTIVE_SERVICES WHERE NAME = 'DEMO_BATCH'; no rows selected SELECT SERVICE_NAME, USERNAME FROM V$SESSION WHERE SERVICE_NAME='DEMO_BATCH'; SERVICE_NAME USERNAME -------------------- ------------------------------ DEMO_BATCH BROST
Grid Infrastructure has option to force disconnects
If you are using grid infrastructure and manage services through srvctl this behaviour is basically the same but you get an extra “force” switch to also disconnect existing sessions while stopping a service.
[[email protected] ~]$ srvctl stop service -db orcl42 -service racdemo_batch [-force] [[email protected] ~]$ srvctl stop service -h Stops the service. Usage: srvctl stop service -db <db_unique_name> [-service "<service_name_list>"] [-serverpool <pool_name>] [-node <node_name> | -instance <inst_name>] [-pq] [-global_override] [-force [-noreplay]] [-eval] [-verbose] -db <db_unique_name> Unique name for the database -service "<serv,...>" Comma separated service names -serverpool <pool_name> Server pool name -node <node_name> Node name -instance <inst_name> Instance name -pq To perform the action on parallel query service -global_override Override value to operate on a global service.Ignored for a non-global service -force Disconnect all sessions during stop or relocate service operations -noreplay Disable session replay during disconnection -eval Evaluates the effects of event without making any changes to the system -verbose Verbose output -help Print usage
Creating extra services on a database allows you to stop and start them for maintenance which can be used as a convenient way to lock out only certain parts of an application while leaving user accounts unlocked to connect via different services.
There is a procedure DISCONNECT_SESSION in DBMS_SERVICE package which may be used to disconnect currently running sessions if we don’t use Grid Infrastructure/Oracle Restart for service management.
Its parameter “disconnect_option” has three possible values in 12.1: POST_TRANSACTION, IMMEDIATE, and NOREPLAY https://docs.oracle.com/database/121/ARPLS/d_serv.htm#ARPLS68024
It’s not so convenient as the srvctl approach, though.