Oracle service secrets: quiesce tactically

2 min read

Sep 2, 2016 12:00:00 AM

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. [code]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.[/code] New sessions using the service name will receive an ORA-12514 error when trying to connect: [code]brbook:~ brost$ ./sqlcl/bin/sql brost/******@192.168.78.101:1521/DEMO_BATCH.PYTHIAN.COM SQLcl: Release 4.2.0.16.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[/code]

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. [code]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[/code]

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. [code][oracle@ractrial1 ~]$ srvctl stop service -db orcl42 -service racdemo_batch [-force] [oracle@ractrial1 ~]$ 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[/code]

Conclusion

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.

On this page

Ready to unlock value from your data?

With Pythian, you can accomplish your data transformation goals and more.

Speak with Pythian consultants now →

What We Do

Oracle service secrets: quiesce tactically

Existing sessions are allowed to continue

Grid Infrastructure has option to force disconnects

Conclusion

Share this

Share this

More resources

Oracle service secrets: group and filter

Issues with Oracle secure external password stores

TNS-12564 Errors and the Importance of the Parameter LOCAL_LISTENER During a Data Guard Switchover

Ready to unlock value from your data?