Kamran Agayev’s Oracle Blog

Kamran Agayev’s Oracle Blog

Archive for December, 2010

My friend Porus Havewala’s first EM book can be ordered now

Posted by Kamran Agayev A. on December 20, 2010

I would like to let you know that my friend Porus Havewala’s book can be ordered now from the following link:

http://www.rampant-books.com/book_1001_advanced_techniques_oem_grid_control.htm

Some monthes ago I’ve taken an exclusive interview with him, you can read that interview from the following link:

https://kamranagayev.wordpress.com/2010/04/22/exclusive-interview-with-porus-homi-havewala/

For those who doesn’t know Porus Havewala, he is working as a Senior Manager (Database Management) in the Enterprise Technology team of Oracle Corporation based in Singapore, and specializes in Oracle Enterprise Manager.  He is an Oracle Employee ACE and was previously awarded the prestigious “Oracle ACE Director” title by Oracle USA. He has extensive experience in Oracle technology since 1994, including as a Senior Production DBA, Senior Database Consultant, Database Architect, E-Business Technical DBA, Development DBA, and Database Designer Modeler. He has also worked in Oracle India in the ACS (Advanced Customer Services) department and is an enthusiast for Oracle technology, especially Oracle Enterprise Manager Grid Control and RMAN, on which he has conducted seminars for large MNCs and implemented powerful enterprise tools.

Advertisements

Posted in Administration | 1 Comment »

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

Posted by Kamran Agayev A. on December 14, 2010

When you implement the resource limit, the sessions that exceed the IDLE limit is marked as SNIPED in V$SESSION view and you may get “ORA-00020: maximum number of processes (%s) exceeded” error because Oracle doesn’t kill that session in OS level and it assumes it as a “process”. So for this, you need to kill those sessions manually

Here I show a little demonstration of the whole process: – First of all, set the RESOURCE_LIMIT parameter to TRUE to enforce the resource limit in database profiles

SQL> show parameter resource_limit
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_limit                       boolean     FALSE

SQL> alter system set resource_limit=true;
System altered.

– Then create a profile and set IDLE_TIME  to 1 minute:

SQL> create profile test_profile limit
  2  idle_time 1;
Profile created.

– Create a user and assign the profile to that user:

SQL> grant dba to usr identified by usr;
Grant succeeded.

SQL> alter user usr profile test_profile;
User altered.

– Change the PROCESSES parameter to make the maximum number of operating system processes lower

SQL> show parameter process
NAME                                 TYPE        VALUE
processes                            integer     150

SQL> alter system set processes=25 scope=spfile;
System altered.

SQL> startup force

SQL> show parameter processes
NAME                                 TYPE        VALUE
processes                            integer     25

SQL> select count(1) from v$process;

  COUNT(1)
----------
        22

Now open two different terminals and connect to the database with USR user:

sqlplus usr/usr

Check the view V$PROCESS. It should be 24

SQL> select count(1) from v$process;

  COUNT(1)
----------
        24

Now open third terminal and try to connect to the database with the user USR.You will get an error because the count of the processes will reach the limit:

[oracle@localhost ~]$ sqlplus usr/usr

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

Enter user-name: 

SQL> 

Now wait for a minute to reach the limit of the IDLE_LIMIT resource (we’ve set it to 1 minute) and query the SYSDATE from any USR session:

SQL> select s.status, count(1), s.username from v$process p, v$session s
where paddr(+)=addr
group by s.status, s.username
order by 1;

STATUS     COUNT(1) USERNAME
-------- ---------- ------------------------------
ACTIVE            1 SYS
ACTIVE           16
INACTIVE          2 SYS
SNIPED            2 USR
                  3

That’s the issue. If you try to connect to the database, you’ll get ORA-00020 error again. Please note that SNIPED doesn’t mean that it’s KILLED. It is not either killed, nor active. The user is not able to run any query, however it holds a process on OS level:

SQL> select count(1) from v$process;

  COUNT(1)
----------
        24

Run any query with already connected (and SNIPED) USR user. You’ll get the following error:

SQL> select sysdate from dual;
select sysdate from dual
*
ERROR at line 1:
ORA-02396: exceeded maximum idle time, please connect again
SQL>

Now query V$SESSION and V$PROCESS views again:

SQL> select s.status, count(1), s.username from v$process p, v$session s
where paddr(+)=addr
group by s.status, s.username
order by 1;

STATUS     COUNT(1) USERNAME
-------- ---------- ------------------------------
ACTIVE            1 SYS
ACTIVE           16
INACTIVE          2 SYS
SNIPED            1 USR
                  4

SQL> select count(1) from v$process;

  COUNT(1)
----------
        24

The process will be free only when you “exit” from Sql*Plus. Exit from the session that you got an error and query V$PROCESS again:

SQL> select count(1) from v$process;

  COUNT(1)
----------
        23

To kill the SNIPED sessions you have two options. The first option is to run ALTER SYSTEM KILL SESSION command. For this you need to get SID and SERIAL# of the sniped session.

SQL> select sid, s.serial#, status from v$process p, v$session s
where paddr=addr 
and s.username='USR';

       SID    SERIAL# STATUS
---------- ---------- --------
         9         10 SNIPED

SQL> alter system kill session '9,10' immediate;
System altered.

SQL> select s.status, count(1), s.username from v$process p, v$session s
where paddr(+)=addr
group by s.status, s.username
order by 1;  

STATUS     COUNT(1) USERNAME
-------- ---------- ------------------------------
ACTIVE            1 SYS
ACTIVE           16
INACTIVE          2 SYS
KILLED            1 USR
                  3

After some seconds you’ll see that the session is cleared from both views:

SQL> /

STATUS     COUNT(1) USERNAME
-------- ---------- ------------------------------
ACTIVE            1 SYS
ACTIVE           16
INACTIVE          2 SYS
                  3

However, due to some bugs, sometimes you may not get the sessions killed using ALTER SYSTEM KILL SESSSION command. For this, you need to kill the process from OS level.

SQL> select spid, status from v$process p, v$session s
where paddr=addr 
and s.username='USR';  

SPID         STATUS
------------ --------
2795         SNIPED

[oracle@localhost ~]$ kill -9 2795

SQL> select spid, status from v$process p, v$session s
where paddr=addr 
and s.username='USR';  

no rows selected

SQL>

Run any sql command on the killed session:

SQL> select sysdate from dual;
select sysdate from dual
*
ERROR at line 1:
ORA-03135: connection lost contact

SQL>

(For more information on killing sniped sessions, refer to MOS 96170.1)

Posted in Administration | 4 Comments »