ORA-00020: maximum number of processes exceeded

We got the following error on our Development 12.1.0.2 database

866322-Wed Mar 28 15:56:40 2018
866347:ORA-00020: maximum number of processes (1256) exceeded

An easy way to see how exhausted your processes/sessions are getting is by running:

SELECT 
inst_id,resource_name, 
current_utilization, 
max_utilization, 
limit_value 
FROM gv$resource_limit 
WHERE resource_name in ('processes','sessions')

Which will give something like this – max utilization is the high water mark level:

 

From the screenshot above, you can see the processes on instance 1 have been fully exhausted at some point, but currently are fine.

You can also see which machines/schemas are causing any potential process exhaustion:

select distinct
        s.inst_id,
        s.username,
        s.machine,
        count(*)
from    gv$session s,
        gv$process p
where   s.paddr       =  p.addr
and     s.inst_id     =  p.inst_id
GROUP BY         s.inst_id,
        s.username,
        s.machine
ORDER BY 4 desc;

Nice summary of the difference between Processes, Sessions and Connections from AskTom:

A connection is a physical circuit between you and the database. A connection might be one of many types — most popular begin DEDICATED server and SHARED server. Zero, one or more sessions may be established over a given connection to the database as show above with sqlplus. A process will be used by a session to execute statements. Sometimes there is a one to one relationship between CONNECTION->SESSION->PROCESS (eg: a normal dedicated server connection). Sometimes there is a one to many from connection to sessions (eg: like autotrace, one connection, two sessions, one process). A process does not have to be dedicated to a specific connection or session however, for example when using shared server (MTS), your SESSION will grab a process from a pool of processes in order to execute a statement. When the call is over, that process is released back to the pool of processes.

Insufficient access rights to perform the operation when running setspn

When attempting to add an SPN to a service account for SQL Server, you may get the following error if you are not a domain admin:

setspn -S MSSQLSvc/VSQLDEV01.DOMAIN DOMAIN\SVCACCOUNT.SVC
Checking domain DC=..,DC=....,DC=..,DC=..

Registering ServicePrincipalNames for CN=vsqldev01 svc,OU=Service Accounts,OU=Shared Resources,OU=..,DC=..,DC=
...,DC=..,DC=..
 MSSQLSvc/VSQLDEV01.DOMAIN
Failed to assign SPN on account 'CN=vsqldev01 svc,OU=Service Accounts,OU=Shared Resources,OU=E..,DC=.,DC=...
,DC=..,DC=..', 
error 0x2098/8344 
-> Insufficient access rights to perform the operation.

If your lucky enough, then get your domain admin to give you the required permissinos against the OU in Active Directory. They would need to do the following:

On a Domain Controller, run adsiedit.msc (Doing this via the normal dsa.msc console will not expose the spn permissions that need to be added)

Then run the following sequence of actions:

Right-Click on the OU and select Properties
Select the "Security" tab
Select the "Advanced" tab
 Select the "Add" button
 Enter the security principal name
 security principal
  Ok
 Properties tab
 Apply to:
 Descendant User objects
 Permissions:
 Read servicePrincipalName - Allow
 Write servicePrincipalName - Allow
  Ok
 Ok
Ok

 

Set up Windows Auth to a SQL Instance with SQL Ops Studio on Mac OS

Microsoft released a preview of SQL Ops Studio in the last week or two, and as a Mac user I was interested to see how well the interface would work compared to SSMS.

Here is a quick intro to the produce from Microsoft: https://www.youtube.com/watch?v=s5DopE7ktwo
More details about Ops Studio can be found here: https://docs.microsoft.com/en-us/sql/sql-operations-studio/what-is
Download – https://docs.microsoft.com/en-us/sql/sql-operations-studio/download

For reference, I am using MacOS Sierra version 10.12.6 and this is what I did to get windows authentication working properly.

If you try to get Windows AD Auth working, you might initially see this error:

The link in the message above takes you to: Connect SQL Operations Studio (preview) to your SQL Server using Windows authentication – Kerberos

There are 3 main areas to configure before Windows Auth to a SQL Instance works.

Service Principal Names (SPN)

A service principal name (SPN) is a unique identifier of a service instance. SPNs are used by Kerberos authentication to associate a service instance with a service logon account. This allows a client application to request that the service authenticate an account even if the client does not have the account name.

As an Example. on a SQL Clustered instance called VSQLDEV01, you can check if the SPN is set by running the following in a command prompt/ps terminal:

setspn -L VSQLDEV01

If it doesnt return a row that contains the following:

MSSQLSvc/VSQLDEV01.<DOMAIN.COMPANY.COM>

MSSQLSvc/FQDN:port | MSSQLSvc/FQDN, where:

• MSSQLSvc is the service that is being registered.
• FQDN is the fully qualified domain name of the server.
• port is the TCP port number.

You can add an SPN to register the service account by doing the following:

setspn -A MSSQLSvc/VSQLDEV01.DOMAIN.COMPANY.COM DOMAIN\SQLSERVICEACC

Get Key Distribution Center (KDC) and join Domain

The KDC is usually just the FQDN of your Domain Controller – fairly straightforward to find out via the nltest command on a windows machine:

 nltest /dsgetdc:DOMAIN.COMPANY.COM

Configure KDC in krb5.conf on your mac

Edit the /etc/krb5.conf in an editor of your choice. Configure the following keys

sudo vi /etc/krb5.conf

[libdefaults]
  default_realm = DOMAIN.COMPANY.COM

[realms]
DOMAIN.COMPANY.COM = {
   kdc = dc-33.domain.company.com
}

Then save the krb5.conf file and exit

Test Granting and Retreiving a ticket from the KDC

On your Mac, run the following in a terminal:

kinit username@DOMAIN.COMPANY.COM

Authenticate and then check the ticket has been granted:

Credentials cache: API:9999E999-99CA-9999-AC9C-99A999999D99
        Principal: ADUSER@DOMAIN.COMPANY.COM
Issued                Expires               Principal
Mar  8 07:55:10 2018  Mar  8 17:55:01 2018  krbtgt/DOMAIN@DOMAIN

Hopefully, if the intruction are followed, then you should be ready to go!

Make a connection using Windows Auth via SQL Ops Studio.

To test a connection has authenticated via KERBEROS, you can check in sql once a connection is made:

SELECT 
session_ID, 
connect_time, 
net_transport, 
protocol_type, 
auth_scheme 
FROM sys.dm_exec_connections
WHERE auth_scheme = 'KERBEROS'

Should return your connected session: