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 220.127.116.11.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 descriptorExisting 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 BROSTGrid 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.
[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 usageConclusion
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.
- Sponsored: 64% off Code Black Drone with HD Camera
Our #1 Best-Selling Drone--Meet the Dark Night of the Sky!
I am working on a production issue. I do not think that we have a database issue but I am graphing some performance metrics to make sure. I made a new graph in my PythonDBAGraphs program.
It shows the average number of active sessions for a given minute. It prompts you for start and stop date and time. It works best with a relatively small interval or the graph gets too busy. Red is sessions active on CPU and blue is all active sessions. This graph is a production database today. Activity peaked around mid day.
It is kind of like the OEM performance screen but at least having it in Python lets me tinker with the graph to meet my needs. Check out the README on the GitHub link above if you want to run this in your environment.
Well, it is that time of year again; the streets of San Francisco will be crowded with members of the Oracle community! As everyone in the Oracle community descends onto the bay area, there will be excitement about the things that will be announced this year. Sure a lot of it is going to be about “cloud” and what direction Oracle is taking with their “cloud” strategy. Besides, “cloud” there will be a lot of good things to take in as well. As I look through the online session catalog for 2016, I’m interested in the topics related to Oracle GoldenGate.
This year there appears to be a good number of Oracle GoldenGate sessions at Oracle Open World, to be specific there are 21 scheduled during the event. I have listed a few of the ones I think will be interesting and will make an attempt to attend; always check the session catalog because what I think is good you may not.
- CON6632 – Oracle GoldenGate for Big Data
- CON7318 – Getting Started with Oracle GoldenGate
- CON6551 – New Oracle GoldenGate 12.3 Services Architecture (beta stuff)
- CON6633 – Accelerate Cloud Onboarding with Oracle GoldenGate Cloud Service
- CON6634 – Faster Design, Development and Deployment with Oracle GoldenGate Studio (should be updated from last year session)
- CON6558 – Best Practice for High Availability and Performance Tuning for Oracle GoldenGate
- UGF616 – Oracle GoldenGate and Apache Kafka: A Deep Dive into Real-Time Data Streaming
- THT7817 – Real-Time and Batch Data Ingestion into Data Lake with Oracle GoldenGate Cloud Service
- UGF5120 – Oracle GoldenGate and Baseball: Five Fundamentals Before Jumping to the Cloud
As we are within two weeks of Oracle Open World, I hope everyone is ready to go and looking forward to seeing others from the community.
Filed under: General