How to show current utilization of sessions, processes and other important settings for a database

Example:

set lines 300
col RESOURCE_NAME format a30
select RESOURCE_NAME,CURRENT_UTILIZATION,MAX_UTILIZATION,INITIAL_ALLOCATION,LIMIT_VALUE
from v$resource_limit
where resource_name in ('processes','sessions');

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION     LIMIT_VALUE
------------------------------ ------------------- --------------- ---------------------- ---------------
processes                                     1498            1500       1500             1500
sessions                                      1511            1517       2272             2272

Documented for Oracle 19c here

How to deal with ORA-00020: maximum number of processes (%s) exceeded

I recently had a situation where access to the database was completely blocked because of the infamous error message

ORA-00020: maximum number of processes (%s) exceeded

Facts:

The database had processes set to 1000.

The Cloud Control agent was spawning hundreds of processes (obviously an error to troubleshoot as a separate action)

Connecting through sqlplus with os authentication (sqlplus / as sysdba) didn't work due to the same reason

At that time, the database had to become available to the users again ASAP.

When I have encountered these situations in the past, I have had to kill all the operating system processes and restart the instance. A brut-force method that is not particularly pretty, but sometimes necessary:

for a in $(ps -ef |grep $ORACLE_SID | grep -v grep | awk '{ print $2}'); do 
>kill -9 $a; 
>done

It normally does the job when you really have no other option.
This time however, after having killed all the processes, Oracle still rejected connections to the database using sqlplus:

sqlplus /nolog

 SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 6 09:07:23 2014

 Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 @ SQL> connect /
 ERROR:
 ORA-00020: maximum number of processes (1000) exceeded

I then found the page by tech.e2sn.com that showed how to use sqlplus with the "preliminary connection".

Simply by using

sqlplus -prelim "/as sysdba"

I was able to connect and shutdown the database with the abort option.

sqlplus -prelim "/ as sysdba"

 SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 6 09:09:15 2014
 Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 SQL> shutdown abort
 ORACLE instance shut down.
 SQL> exit
 Disconnected from ORACLE

After this point the database could once again be restarted:

sqlplus / as sysdba

 SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 6 09:10:38 2014
 Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 Connected to an idle instance.
SQL> startup
 ORACLE instance started.

 Total System Global Area 2137886720 bytes
 Fixed Size                  2248080 bytes
 Variable Size            1258291824 bytes
 Database Buffers          855638016 bytes
 Redo Buffers               21708800 bytes
 Database mounted.
 Databasen opened.

The article referred to above is worth reading, but in short, the -prelim option will not try to create private session structures in the SGA. This allows you to connect to perform debugging or shutdown operations.

How to recreate the control file and rename the database and the datafiles

To change a database name or file names, it can be practical to recreate the control file, and in some cases your only option.

In this example, I will rename a database and its file structure from "OID11UI" to "OID11U1".

Start by generating a file which holds the current layout of all the database's files:

SQL> alter database backup controlfile to trace as '/u01/oracle/product/11204/dbs/cntr_trace.sql' resetlogs;

Database altered.

The generated file typically look like this:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "OID11UI" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u02/oradata/OID11UI/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u02/oradata/OID11UI/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u02/oradata/OID11UI/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u02/oradata/OID11UI/system01.dbf',
  '/u02/oradata/OID11UI/sysaux01.dbf',
  '/u02/oradata/OID11UI/undotbs01.dbf',
  '/u02/oradata/OID11UI/users01.dbf'
CHARACTER SET WE8MSWIN1252
;
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/OID11UI/temp01.dbf'
     SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- End of tempfile additions.

Exchange the word "REUSE" against the word "SET":

CREATE CONTROLFILE SET DATABASE "OID11U1"

Optionally, on the first line, add a pointer to a correct parameter file:

STARTUP NOMOUNT PFILE='/u01/oracle/product/11204/dbs/initOID11U1.ora'

Change all references to "OID11UI" (the old name):

STARTUP NOMOUNT PFILE='/u01/oracle/product/11204/dbs/initOID11U1.ora'
CREATE CONTROLFILE SET DATABASE "OID11U1" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u02/oradata/OID11U1/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u02/oradata/OID11U1/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u02/oradata/OID11U1/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u02/oradata/OID11U1/system01.dbf',
  '/u02/oradata/OID11U1/sysaux01.dbf',
  '/u02/oradata/OID11U1/undotbs01.dbf',
  '/u02/oradata/OID11U1/users01.dbf'
CHARACTER SET WE8MSWIN1252
;
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/OID11U1/temp01.dbf'
     SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- End of tempfile additions.

Change the folder name that oracle uses:

usu0oid01:OID11UI>cd /u02/oradata
usu0oid01:OID11UI>mv OID11UI OID11U1

Change the control_files directive in the parameter file:
Before

*.control_files='/u02/oradata/OID11UI/control01.ctl','/u02/oradata/OID11UI/control02.ctl'

After:

*.control_files='/u02/oradata/OID11U1/control01.ctl','/u02/oradata/OID11U1/control02.ctl'

Remove the old controlfiles physically from disk:

cd /u02/oradata/OID11U1
rm *.ctl

Shutdown the database:

SQL> shutdown abort
ORACLE instance shut down.

Change the oracle profile in the operating system:

cd
vi .profile

Before:

export ORACLE_SID=OID11UI

After:

export ORACLE_SID=OID11U1

Source the new profile, and check that the ORACLE_SID environmental variable is correctly set:

. .profile
usu0oid01:OID11U1>echo $ORACLE_SID
OID11U1

Start sqlplus and run script:

usu0oid01:OID11UI>sqlplus / as sysdba @cntr_trace.sql

SQL*Plus: Release 11.2.0.4.0 Production on Wed May 28 14:00:50 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

ORACLE instance started.

Total System Global Area 1720328192 bytes
Fixed Size                  2247072 bytes
Variable Size            1107297888 bytes
Database Buffers          603979776 bytes
Redo Buffers                6803456 bytes

Control file created.

Database altered.

Tablespace altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

Check the location of the data files after the change:

SQL> select file_name from dba_data_files union select member from v$logfile  union  select name from v$controlfile;

FILE_NAME
--------------------------------------------------
/u02/oradata/OID11U1/control01.ctl
/u02/oradata/OID11U1/control02.ctl
/u02/oradata/OID11U1/redo01.log
/u02/oradata/OID11U1/redo02.log
/u02/oradata/OID11U1/redo03.log
/u02/oradata/OID11U1/sysaux01.dbf
/u02/oradata/OID11U1/system01.dbf
/u02/oradata/OID11U1/undotbs01.dbf
/u02/oradata/OID11U1/users01.dbf

Lag en spfile (anbefales):

SQL> create spfile from pfile; 

File created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Startup the database for normal use:

SQL> startup

What is the CJQ0 background process?

When starting the Oracle database you may see something like the following in the alert log:

Tue Mar 04 12:40:50 2014
CJQ0 started with pid=27, OS id=5526

This means your database is starting the optional background process for job queue processing

Oracle Database uses job queue processes to run user jobs.

The initialization parameter JOB_QUEUE_PROCESSES states the maximum number of job queue processes that can concurrently run on an instance.

Source: Oracle Documentation

How to disable flashback archiver process, introduced to support Oracle 11gR1 feature "Flashback Data Archive"

If you do not use the flashback data archive feature, and have no plans to do so, you might as well disable the process, even though it is defined as "lightweight".

First, confirm that the operating system process is running:

$ ps -ef | grep FBDA
oracle 18199 8264 0 13:22:12 pts/5 0:00 grep FBDA

--> The background proceess FBDA is running in the background

Check the setting
sqlplus / as sysdba:

select a.ksppinm  "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value"
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and substr(ksppinm,1,1)='_'
and ksppinm like '%archiver%'
order by a.ksppinm;


Parameter                            Session Value  Instance Value
----------------------------------- --------------- --------------
_disable_flashback_archiver         0                0
_flashback_archiver_partition_size  0                0

by setting the parameter _disable_flashback_archiver and restarting your instance, you disable the flashback archiver process:

alter system set "_disable_flashback_archiver"=1 scope=spfile;