ORA-00020: maximum number of processes exceeded

Hi All,

Hope you might have seen this error in your Oracle Database sometimes.

ORA-00020: maximum number of processes exceeded

What this problem is?

It’s self explanatory; Oracle can’t create a new process for a new session because the value specified by the parameter ‘processes’ has already been crossed.

What could be the exact reason for this problem?

Well, It might be the poor application that keeps on creating  sessions in the Oracle Database, of course processes for those sessions in the Database server, and not closing them but just retaining them or Really that many sessions are being created by the application because of the increased load

If first option is the culprit, You have to ask the Application administrators/Developers to monitor/provide a fix. Otherwise, it’s a pure mistake of the Architects/DBAs or whomever they’re fixed the number of processes to that value which is insufficient to that environment.

Anyway, Who ever do the mistake, DBAs are the one who have the responsibility to restore the services 🙂

So, As a DBA you’ll try to login to the Database and bounce it so that all the sessions/processes will be gone away and everything starts fresh.

What if you’re unable to login to the Database even with the SYS user, which is a must for the DBA to bounce the Database???

The clean/best possible solution is,

Try to find out all the processes with name like ‘oracle*’ by giving ‘ps -ef | grep oracle*’ at the OS level

After that kill some of the processes like ‘oracle<ORACLE_SID>’ by giving a simple kill -9 <PID1> <PID2>….<PIDn>; Don’t kill any oracle background processes like PMON/SMON etc…

This will release some resources and will give a chance to establish new sessions (new processes on the OS)

Then try to connect to the Database and if you’re able to connect, that’s good. You can shut down the instance.

If not try to kill some more processes of that kind I’ve mentioned above and try again.

Remember this just to restore the services but you’re still pending on the permanent fix. Otherwise be ready to visit this page again 🙂

I appreciate your feedback/comments.

CSM

Larry revealed about much awaiting ‘Oracle Database 12C’

Hi

I’ve been watching all the videos and keynote presentations in Openworld 2012.

I’m listing the below highlights, briefly, from Openworld which, I felt, are helpful to you to be aware of.

The main announcements are

1. Oracle Database 12C – will be released in early 2013 – database with multi tenancy feature
2. Oracle’s private cloud – an addition to its public loud which was launched in 2011
3. Exadata X3 engineered systems

Database 12c: the multi-tenant  database will have a container database where we can plug multiple databases. This is really a fundamental shift in the Oracle’s architecture. To explain more, if you have 3 different database one for ERP, one for CRM and one for DW, you can combine all of them into a single database which could reduce memory utilization by reducing the number of processes required by oracle. This will make us fit more number of databases on same hardware compared to earlier releases of Oracle and require less number of DBAs to administer them. According to Oracle’s benchmarking results, it’s 6X less number of resources required.

Oracle’s Private cloud: Oracle announced its private cloud in support to its public cloud. Through the private cloud Oracle will set the cloud infrastructure up in the customer premises itself, behind the customers’ firewall only but the monitoring and administration will be done by Oracle. And Oracle will be entering into the IaaS market as well. So they’ll be having SaaS, PaaS and IaaS with them from 2013. They said explicitly that Amazon.com is their competitor in IaaS market and salesforce.com in SaaS market and they’re already leaders in PaaS with their flagship Weblogic, Java and Solaris etc…

Exadata X3: Super-fast Engineered system with 26TB of memory with 10X compression so you can put 260TB of data directly in memory. And it supports 100GB/sec I/O.

I don’t want to list more features and details as I’m not working for Oracle’s sales 🙂

Please go through Larry’s presentation if you have some time to find out more about these.

Regards,

CSM

“ORA-01081: cannot start already-running ORACLE” While creating a new Database

Hi Geeks,

When I was doing a research on Oracle Database creation, through DBCA, last week, I came across a very interesting scenario which I couldn’t stop sharing with you 🙂

I have a cluster of machines (don’t confuse it with a RAC cluster but just a group of machines required for my application deployment) with 11.2.0.2 installed and a Database instance running.

I got a requirement to remove everything accumulated in the machine as a result of the Oracle installation and the Database creation: The installation directories, the datafiles, oratab entries etc…

After removing all these things, I need to do a fresh installation again, create a Database and make the system running as it was before the reset activity. But the main point to consider here is, we have to do this on a cluster of machines and as quickly as possible.

So, I thought of removing the Databases with DBCA and do a ‘brute-force’ removal of all the physical files. I’ve noticed that the DBCA is taking at least 3-5 minutes for each Database to remove so in a cluster of 10 Databases it’ll add a burden of 30-50 minutes unnecessarily.

So, I’ve decided to remove everything related to Oracle from each machine in a brute-force way and start the installation and Database creation steps.

After finishing up the installation and receiving a zero return value from DBCA I thought of cross checking the things and observed that the Database is not created properly; no datafiles were created even but just an entry in /etc/oratab and some other stuff despite the success reported by DBCA…

To my bad, of course good, after seeing the DBCA logs, I found that the Database creation was failing with a very weird error.

ORA-01081: cannot start already-running ORACLE

I’m almost surprised on seeing this message!!!

As we all know that DBCA first prepares an instance and do a clone. So it’s trying to create the instance as per the plan but failing to start the instance saying the instance is already exist!!!

I swear I’ve removed each and every file related to Oracle even the registry entries 🙂
It’s a clean machine with just Oracle installed but why this thing is happening???

See the log below to get a clarity on the issue.

Copying database files
DBCA_PROGRESS : 1%
DBCA_PROGRESS : 2%
ORA-01075: you are currently logged on
ORA-01081: cannot start already-running ORACLE – shut it down first
DBCA_PROGRESS : 4%
DBCA_PROGRESS : 37%
Creating and starting Oracle instance
ORA-01075: you are currently logged on
DBCA_PROGRESS : 38%
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01092: ORACLE instance terminated. Disconnection forced
DBCA_PROGRESS : 100%

After digging in, and doing a fair amount of research, I found the mistake that I’ve committed!!!

I’ve cleaned the physical files but what about the memory structure Oracle built in the operating system’s shared memory segments?

Yes, your guess is correct; The memory structures created by Oracle are remained intact in the shared memory. An ipcs has shown me that there are the latches and semaphores acquired by this particular Oracle instance were not released.

I wouldn’t have come across this issue if I’ve chosen a different name for my instance or with the same name but from different ORACLE_HOME as Oracle’s key to acquire the shared memory structures is the combination of OH and SID. If I use a different OH or SID, the DBCA will try to acquire the latches and the semaphores using this new key.

So I did a shutdown abort (I could do any kind of shutdown in fact but my idea is to do it as fast as possible) before doing the brute-force reset, which would release all the shared memory structures, latches and semaphores cleanly and able to complete my task.

The biggest mystery is why DBCA is not aborting upon noticing this? why it’s reporting a success upon a ‘disastrous failure’??

Oracle confirmed it’s a bug in DBCA not to report failures in silent mode. So you couldn’t just rely on the return status to the OS but have to do a lot more validations to confirm your mission is accomplished 🙂

I welcome your feedback and suggestions.

Regards,
CSM

Can you create objects in a READ ONLY tablespace?

Hi Oracle geeks,
Last Friday, I’ve noticed a strange thing happening in my 11.2.0.3 Database: I’m able to create objects in a tablespace which is marked READ ONLY !!!

Don’t believe this?? See the below

SQL> create tablespace ro datafile ‘/xxx/RO.DBF’ size 10M AUTOEXTEND ON;
Tablespace created.

SQL> alter tablespace ro read only;
Tablespace altered.

SQL> select status from dba_tablespaces where tablespace_name=’RO’;
STATUS
———
READ ONLY

SQL> sho user
USER is “CSM”

SQL> create table csm(a number) tablespace ro;
Table created.

looking strange!!!

but connect as a ‘sys’ user now. and do the same exercise

SQL> conn sys/sys as sysdba
Connected.

SQL> sho user
USER is “SYS”

SQL> create table csm(a number) tablespace ro;
create table csm(a number) tablespace ro
*
ERROR at line 1:
ORA-01647: tablespace ‘RO’ is read-only, cannot allocate space in it

OMG!! it’s working as (un)expected…

After investigating a bit I found the reason behind this strange behavior(only in our perspective) is,

There is an option called ‘deferred_segment_creation’ starting from 11.2 which just adds an entry into the data dictionary but won’t actually create a segment.
So even in case of a locally managed tablespace, Oracle need not update the bitmaps in the current tablespace (RO in our case).
As it is just adding an entry into some of the data dictionary tables, Oracle doesn’t throw any errors.

You can’t even find a segment created in this tablespace

SQL> select segment_name,segment_type from dba_segments where tablespace_name=’RO’;
no rows selected

If you try to create a table by forcing it not to use the deferred_segment mode like this,

SQL> create table csm2(a number) segment creation IMMEDIATE tablespace ro;
create table csm2(a number) segment creation IMMEDIATE tablespace ro
*
ERROR at line 1:
ORA-01647: tablespace ‘RO’ is read-only, cannot allocate space in it

you’ll encounter the ORA-01647 error.

But the biggest question is, why a sys user was unable to do the same???
The answer is, differed segment option is not available for the objects in sys and system schema 🙂

Regards,
CSM