ORA-00020 reported after upgrading the database to 12c

ORA-00020 reported after upgrading the database to 12c , without any changes in the application. We notice a sudden increase in number of background process(pxxx).

CAUSE

 From 12c parallel_min_servers default value calculation has been changed to CPU_COUNT * PARALLEL_THREADS_PER_CPU * 2 from 0. for more details on this, refer to the document:

(Doc ID 1678111.1) PARALLEL_MIN_SERVERS Does Not Limit The Number Of Parallel Processes At Startup in 12c

SOLUTION

 1. Check the current value of parallel_min_server.

show parameter parallel_min_servers

 2. Reduce the parallel_min_servers to a realistic value.

alter system set parallel_min_servers=<value> scope=both;

 This should avoid the ORA-20 caused by this increased number of background processes

Note: You cannot reduce the value for parallel_min_servers in 12.1.0.1, to do install patch 17452799 then reduce the value. This patch is included in 12.1.0.2.

On 12.2, ORA-603 ORA-20 is Raised Even When Maximum Number of Processes Has Not Exceeded

On 12.2.0.1, connecting to a new session may result in ORA-603, ORA-20 even when the PROCESSES resource limit has not been reached.

This document describes this new behavior on 12.2.

> sqlplus system/manager

SQL*Plus: Release 12.2.0.1.0 Production on Fri Oct 20 01:15:50 2017

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

ERROR:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00020: maximum number of processes () exceeded
Process ID: 0
Session ID: 0 Serial number: 0

SOLUTION

Starting with 12.2.0.1, small number of processes are reserved for local SYSDBA user connection.

By default, this number is 2, and ORA-603, ORA-20 will be thrown when the number of processes generated after non-sysdba user's connection reaches PROCESSES - 2.

If it is necessary to revert back to pre-12.2 behavior, set hidden parameter _min_sys_process=0 and restart the database.

alter system set "_min_sys_process"=0 scope=spfile;
shutdown immediate;
startup;

My Oracle 12c RAC on Oracle VirtualBox

In development environments virtualization is a common practice nowadays. Oracle has now even started providing official support for Production RAC databases running on Oracle Virtualized machines. Below we look at a step by step installation process for setting up the Grid Infrastructure Software with a RAC database running on a VirtualBox using Linux Operating System.

Download Software

Download the following Software.

– Oracle Linux 6
– VirtualBox for Window hosts
– Oracle Database 12c Release 1 – Linux x86-64

Oracle VirtualBox Installation

Run the VirtualBox-4.2.16-86992-Win.exe and complete the VirtualBox installation.

Oracle VirtualBox Setup

Create a new Virtual machine.

Name the machine and choose the Linux OS.

Use an appropiate memory size.

Use the default to create new hard disk.

Leave the default VDI disk format.

Use “Dynamically allocated” disk.

Choose file location and size.

Click on “Create” disk.

Choose hardware to use with the virtual machines.

Confirm Network Settings for adapter 1.

Confirm Network Settings for adapter 2.

Click next to complete.

Oracle Linux 6 OS Installation

Choose Enterprise 6 from the Media to install.

Click to start.

Leave the default to install the OS.

Skip testing the media.

Click on Next.

Click on Next.

Choose the language and continue.

Use Basic storage.

Leave deafult. Ensure that the data at the location can be discarded.

Choose the hostname.

Setup Network Interface on Virtualhost

Setup eth0 and eth1.

Confirm settings for eth0.

Confirm settings for eth0.

Confirm settings for eth0.

Confirm settings for eth0.

Confirm settings for eth1.

Confirm settings for eth1.

Confirm settings for eth1.

Choose locale.

Set new Password and confirm.

Choose the appropriate option.

Confirm creating disk partitions.

Choose basic server.

Pick the appropriate choices and continue.

Pick the appropriate choices and continue.

Pick the appropriate choices and continue.

Pick the appropriate choices and continue.

Starting installation process.

Click forward

Agree with license to continue installation

Register for Software updates later.

Confirm to register later

Finish updates setup.

Click on forward.

Confirm your date and time.

Leave the default and click on finish

Initial host configuration completed.

Oracle Grid infrastructure Prerequisites

Step 1:    Verify  oracle-rdbms-server-11gR2-preinstall-1.0-7.el6.x86_64 rpm on both the nodes

[root@12c-vst-rac1 ~]# rpm -qa | grep 11g
oracle-rdbms-server-11gR2-preinstall-1.0-7.el6.x86_64

Step 2:  Adjust /etc/hosts on both the nodes as below

[root@12c-vst-rac1 ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
###########Public ips############################################
10.0.0.121      12c-vst-rac1.vitalsofttech.com  12c-vst-rac1
10.0.0.122      12c-vst-rac2.vitalsofttech.com  12c-vst-rac2
###########Private ips############################################
192.168.0.121   12c-vst-rac1-priv.vitalsofttech.com     12c-vst-rac1-priv
192.168.0.122   12c-vst-rac2-priv.vitalsofttech.com     12c-vst-rac2-priv
###########Virtual ips############################################
10.0.0.123      12c-vst-rac1-vip.vitalsofttech.com      12c-vst-rac1-vip
10.0.0.124      12c-vst-rac2-vip.vitalsofttech.com      12c-vst-rac2-vip
###########scan ips############################################
10.0.0.125      vstracdb-scan.vitalsofttech.com  vstracdb-scan

Step 3:  On both the nodes update “/etc/security/limits.d/90-nproc.conf” and change
[root@12c-vst-rac2 ~]# cat /etc/security/limits.d/90-nproc.conf
# Default limit for number of user's processes to prevent
# accidental fork bombs.
# See rhbz #432903 for reasoning.
*          -       nproc     16384
root       soft    nproc     unlimited

Step 4: Change the setting of SELinux to permissive by editing the “/etc/selinux/config” file, making sure the SELINUX flag is set as follows.
SELINUX=permissive

Step 5: If you have the Linux firewall enabled, you will need to disable or configure it
# service iptables stop
# chkconfig iptables off

Step 6: Either configure NTP, or make sure it is not configured so the Oracle Cluster Time Synchronization Service (ctssd) can synchronize the times of the RAC nodes.

# service ntpd stop
Shutting down ntpd:                                        [  OK  ] # chkconfig ntpd off
# mv /etc/ntp.conf /etc/ntp.conf.orig
# rm /var/run/ntpd.pid

Note: we would be installing grid infrastructure with grid user and oracle software with oracle user

Step 7: Execute the all below steps as user root on both the nodes
Add the below at the end of file “/etc/security/limits.conf”

grid   soft   nofile   1024
grid   hard   nofile   65536
grid   soft   nproc    2047
grid   hard   nproc    16384
grid   soft   stack    10240
grid   hard   stack    32768

Append the /etc/resolv.conf file on both nodes, as below

[root@12c-vst-rac2 ~]# cat /etc/resolv.conf
search localdomain

[root@12c-vst-rac2 ~]# service dnsmasq restart
Starting dnsmasq:                                          [  OK  ]

Delete the existing oracle user and groups created by the OS.
userdel oracle
rm -rf /home/oracle
rm -rf /var/mail/oracle
groupdel oinstall
groupdel dba

Create new groups for grid user and oracle user

groupadd -g 1000 oinstall
groupadd -g 1010 dba
groupadd -g 1020 asmadmin
groupadd -g 1030 asmdba

Create the Grid user.

useradd -u 1100 -g oinstall -G asmadmin,asmdba grid
mkdir -p /u01/app/12.1.0/grid  ####{grid-home}
mkdir -p /u01/app/grid  ######{grid-base}
chown -R grid:oinstall /u01
passwd grid

Step 8: Logging as grid user and edit  .bash profile.
export ORACLE_HOME=/u01/app/12.1.0/grid
export ORACLE_SID=+ASM1
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_BASE=/u01/app/grid
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlibNote: use ORACLE_SID as per the node for 1st node use +ASM1 and for 2nd node use +ASM2 in .bash_profile

Create the oracle OS user.

useradd -u 1101 -g oinstall -G dba,asmdba oracle
mkdir -p /u01/app/oracle/product/12.1.0  ######(oracle home)
chown -R  oracle:oinstall /u01/app/oracle    #### (oracle base)
chmod -R 775 /u01
passwd oracle

Log in as Oracle user and edit  .bash profile.

export ORACLE_HOME=/u01/app/oracle/product/12.1.0
export ORACLE_SID=vstracdb1
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_BASE=/u01/app/oracle
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

Note:use ORACLE_SID as per the node for 1st node use vstracdb1 and for 2nd node use vstracdb2 in .bash_profile

Step 8: Create Shared disks.

First shutdown both the nodes.

Go to virtualbox system settings>storage of node12c-vst-rac1 and add a fixed size harddisk

Select create new disk

Select VID and click Next

Select Fixed size and click next

Click on create

Click OK

Click File>Virtual Media Manager and select newly created disk

Click modify > shareable and Click OK

Go to virtualbox system settings>storage of node12c-vst-rac2 and add newly created shareable hard disk

Step 9: Configure oracleasm on both the nodes as root user.

[root@12c-vst-rac1 ~]# oracleasm configure -i
Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver.  The following questions will determine whether the driver is
loaded on boot and what permissions it will have.  The current values
will be shown in brackets ('[]').  Hitting <ENTER> without typing an
answer will keep that current value.  Ctrl-C will abort.

Default user to own the driver interface []: grid
Default group to own the driver interface []: asmadmin

Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]:

Writing Oracle ASM library driver configuration: done

[root@12c-vst-rac1 ~]# oracleasm init
Creating /dev/oracleasm mount point: /dev/oracleasm
Loading module "oracleasm": oracleasm
Configuring "oracleasm" to use device physical block size
Mounting ASMlib driver filesystem: /dev/oracleasm

Verify the shared disk /dev/sdb exist on both nodes using below command
[root@12c-vst-rac1 ~]# fdisk -l /dev/sdb
Disk /dev/sdb: 4260 MB, 4260364288 bytes
255 heads, 63 sectors/track, 517 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000

Create a new partition in /dev/sdb using the below option (this has to be done only on one node.

[root@12c-vst-rac1 ~]# fdisk /dev/sdb
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0xd763cd1b.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Warning: DOS-compatible mode is deprecated. It's strongly recommended to
switch off the mode (command 'c') and change display units to
sectors (command 'u').
Command (m for help): n

Command action
e   extended
p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-517, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-517, default 517):
Using default value 517
Command (m for help): w

The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

Verify the new partition /dev/sdb1 on both the nodes using the below command.
[root@12c-vst-rac1 ~]# fdisk -l /dev/sdb

Disk /dev/sdb: 9663 MB, 9663676416 bytes
255 heads, 63 sectors/track, 1174 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x9590bc78
Device Boot      Start         End      Blocks   Id  System
/dev/sdb1               1        1174     9430123+  83  Linux

Run oracleasm createdisk vstsharedisk /dev/sdb1 on node1 (12c-vst-rac1)

[root@12c-vst-rac1 ~]# oracleasm createdisk vstsharedisk /dev/sdb1

Writing disk header: done
Instantiating disk: done

[root@ol6-12c-vst-rac1 ~]# oracleasm scandisks
Reloading disk partitions: done
Cleaning any stale ASM disks...
Scanning system for ASM disks...

[root@ol6-12c-vst-rac1 ~]# oracleasm listdisks
VSTSHAREDISK

Execute the below steps on node 2 (12c-vst-rac2) to scan and list newly created oracleasm disk

[root@12c-vst-rac2 ~]# oracleasm configure -i
Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver.  The following questions will determine whether the driver is
loaded on boot and what permissions it will have.  The current values
will be shown in brackets ('[]').  Hitting <ENTER> without typing an
answer will keep that current value.  Ctrl-C will abort.

Default user to own the driver interface []: grid
Default group to own the driver interface []: asmadmin

Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]:
Writing Oracle ASM library driver configuration: done

[root@12c-vst-rac2 ~]# oracleasm init
Creating /dev/oracleasm mount point: /dev/oracleasm
Loading module "oracleasm": oracleasm
Configuring "oracleasm" to use device physical block size
Mounting ASMlib driver filesystem: /dev/oracleasm

[root@12c-vst-rac2 ~]# oracleasm scandisks
Reloading disk partitions: done
Cleaning any stale ASM disks...
Scanning system for ASM disks...
Instantiating disk "VSTSHAREDISK"

[root@12c-vst-rac2 ~]# oracleasm listdisks
VSTSHAREDISK

Note : copied the Oracle grid Infrastructure and oracle database software to / Software directory.

Grid Infrastructure Installation

Install the following package from the Oracle grid media as the root user on both the nodes.

[root@12c-vst-rac1 rpm]#  cd /softwares/grid/rpm
[root@12c-vst-rac1 rpm]# rpm -ivh cvuqdisk-1.0.9-1.rpm
Preparing...                ########################################### [100%] Using default group oinstall to install package
1:cvuqdisk               ########################################### [100%]/softwares/grid/rpm
login as grid user on node1 and perform the following step
[grid@ol6-12c-vst-rac1 ~]$ cd /softwares/grid/
[grid@ol6-12c-vst-rac1 grid]$ ls
install   rpm           runInstaller  stage
response  runcluvfy.sh  sshsetup      welcome.html

[grid@ol6-12c-vst-rac1 grid]$ ./runInstaller

Skip Oracle software updates.

Choose to configure Standard cluster.

Choose typical installation and continue.

Proceed with typical installation.

Add/confirm the cluster nodes.

Click on next.

Oracle checking SSH connectivity between all nodes.

Press ok to continue.

Verify network interfaces.

Provide/confirm SCAN name.

Type in the passwords.

Provide ASM disk discovery paths.

View disks available and confirm.

Provide/confirm inventory location.

Click on next.

Pre-verification in progress.

Resolve or skip and issues discovered.

Confirm and continue.

Click on install.

Installation in progress.

Installation almost completed. Run the root and orainsRoot scripts.

Run the above scripts in the mentioned order.

[root@12c-vst-rac1 ~]# /u01/app/oraInventory/orainstRoot.sh
Changing permissions of /u01/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.
Changing groupname of /u01/app/oraInventory to oinstall.
The execution of the script is complete.

[root@12c-vst-rac1 ~]# /u01/app/12.1.0/grid/root.sh
Performing root user operation for Oracle 12c
The following environment variables are set as:
ORACLE_OWNER= grid
ORACLE_HOME=  /u01/app/12.1.0/grid
Enter the full pathname of the local bin directory: [/usr/local/bin]:
Copying dbhome to /usr/local/bin ...
Copying oraenv to /usr/local/bin ...
Copying coraenv to /usr/local/bin ...
Creating /etc/oratab file...

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /u01/app/12.1.0/grid/crs/install/crsconfig_params
2013/07/07 11:08:07 CLSRSC-363: User ignored prerequisites during installation

Final installaton steps in progress.

Grid Infrastructure installation completed! Moving to database software installation.

[grid@12c-vst-rac1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ONLINE  ONLINE       12c-vst-rac1             STABLE ora.DATA.dg
ONLINE  ONLINE       12c-vst-rac2             STABLE ora.LISTENER.lsnr
ONLINE  ONLINE       12c-vst-rac1             STABLE
ONLINE  ONLINE       12c-vst-rac2             STABLE ora.asm
ONLINE  ONLINE       12c-vst-rac1             Started,STABLE
ONLINE  ONLINE       12c-vst-rac2             Started,STABLE ora.net1.network
ONLINE  ONLINE       12c-vst-rac1             STABLE
ONLINE  ONLINE       12c-vst-rac2             STABLE ora.ons
ONLINE  ONLINE       12c-vst-rac1             STABLE
ONLINE  ONLINE       12c-vst-rac2             STABLE

--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.12c-vst-rac1.vip
1        ONLINE  ONLINE       12c-vst-rac1             STABLE ora.12c-vst-rac2.vip
1        ONLINE  ONLINE       12c-vst-rac2             STABLE ora.LISTENER_SCAN1.lsnr
1        ONLINE  ONLINE       12c-vst-rac1             STABLE ora.cvu
1        ONLINE  ONLINE       12c-vst-rac1             STABLE ora.oc4j
1        OFFLINE OFFLINE                               STABLE ora.scan1.vip
1        ONLINE  ONLINE       12c-vst-rac1             STABLE
--------------------------------------------------------------------------------

Oracle 12c RAC Installation

Database Software Installation

Go to the runInstaller directory location.

cd /softwares/database/runInstaller

Launch runInstaller.

Choose appropriately and click on next.

Confirm choice and continue.

Skip software updates.

Choose install database software only.

Choose RAC database installation.

SSH connectivity check in progress.

SSH Verification completed.

Choose the appropriate language.

Pick the appropriate database edition.

Confirm Software install location.

Verify user and groups then click on next.

Prerequisite check in progress.

Prerequisite check completed.

Installation Database Software.

Copy and run root.sh script as root user.

Database software installation completed! Moving to executing DBCA.

Launch the dbca binary.

Provide/confirm the directory and other information.

Pre-requisite check in progress.

Pre-requisite check completed.

Datababase creation summary. Click on finish.

Database creation in progress.

Database creation in progress.

Note the database details.

Installation completed!

Thanks to Natik  Ameen reference -  http://www.vitalsofttech.com/oracle-12c-rac-installation-on-oracle-linux-6-using-virtualbox/