Oracle service secrets: quiesce tactically
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]
