Increasing Processes, Sessions and Transactions in Oracle

If your maximum number of process and/or sessions exceeds the limit ,oracle will throws following errors 
ORA-12516: TNS:listener could not find available handler with matching protocol stack
ORA-00020: maximum number of processes (%s) exceeded .
When this occurs, the service handlers for the TNS listener become "Blocked" and no new connections can be made.

Below query gives present allocation 
SQL>select name,value from v$parameter where name in ('processes','sessions','transactions');  

Below quey gives current number of process
SQL>select count(*) from v$process; 

Below quey gives current number of session
SQL>select count(*) from v$session;

Below quey gives current number of transaction
SQL>select count(*) from v$transaction;

General formula to calculate process,session and transaction
PROCESSES = 40 to Operating System Dependant
SESSIONS = (1.1 * PROCESSES) + 5
TRANSACTIONS = 1.1 * SESSIONS

ie For increasing process parameter you should consider increasing sesson and transactions parameter as well.

if we need to change do the following ,

SQL>alter system set processes = 1000 scope = spfile;

SQL>alter system set sessions = 1105 scope = spfile;

SQL>alter system set transactions = 1215 scope = spfile;

SQL>shutdown immediate;

SQL>startup;

For checking the current utilization , maximum utilization of process and session
SQL>SELECT upper(resource_name) as resource_name,current_utilization,max_utilization,initial_allocation FROM v$resource_limit WHERE resource_name in ('processes', 'sessions');

How to change the redo log size in RAC

There is no any ALTER... command to resize the REDO logs in orace. So if you want to resize your REDO logs you will need to create a new group with a new size and then drop the old one.

Let's say you have this situation in your RAC for two nodes: ( say rac1 and rac2) .From rac1 node do the following ,

SQL> select GROUP#,MEMBERS,BYTES/1024/1024 MB_Size,status from v$log;
    GROUP#    THREAD#    MEMBERS    MB_SIZE STATUS
   ----------      ----------         ----------      ---------- ----------------
             1                      1                     2                 50 CURRENT
             2                      1                     2                 50 INACTIVE
             3                      2                     2                 50 INACTIVE
             4                      2                     2                 50 CURRENT

and you want to resize all your groups. Lets say you want to set 100M instead of 50M.

Action plan:
1. Add new REDO groups with a new size.

SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 ( '+DATA','+FLASH') SIZE 100M;
SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 6 ( '+DATA','+FLASH') SIZE 100M;
SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 7 ( '+DATA','+FLASH') SIZE 100M;
SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 8 ( '+DATA','+FLASH') SIZE 100M;

Mentioned commands will create 4 new groups with two members in each. Change the volume groups (+DATA and +FLASH) according to your environment .

2. Now you should wait till the Group 1/2/3/4 will start to be INACTIVE so you would be able to drop them. Also you can speed up this process by executing:

SQL> alter system switch logfile;
SQL> alter system checkpoint;

3. To DROP the old groups.

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;

In busy DB it is possible to see something like this during drop operation:
ORA-01623: log 1 is current log for instance RPTDB (thread 1) - cannot drop

in that case you should execute the following again:
SQL> alter system switch logfile;
or
SQL> alter system checkpoint;
or
just continue your tries to drop it.

Note: - I tried to drop a redo log group but it is always CURRENT or ACTIVE. I done alter system switch logfile and alter system checkpoint  repeatedly but it does not become inactive . I overcome this issue by executing  alter system switch logfile  on the other node (rac2).Once all your old GROUPs will be dropped your output would be look like this:

SQL> select GROUP#,MEMBERS,BYTES/1024/1024 MB_Size from v$log;

 GROUP#    THREAD#    MEMBERS    MB_SIZE
 ----------     ----------         ----------     ----------
             5                    1                     2         100
             6                    1                     2         100
             7                    2                     2         100
             8                    2                     2         100

Sourece:-  http://eugene-dba.blogspot.in/2012/10/change-redo-log-size-in-rac.html

How to upgrade oracle client from 10.2.0.3 to 10.2.0.4

Recently i got a request  from my developer  team to install oracle client version 10.2.0.4.

Here is the step that i followed to achieve the  same.

1. First install oracle client version 10.2.0.3 on you computer 

2.Download oracle 10.2.0.4 patch set (6810189) from oracle website.

   For example i downloaded p6810189_10204_Win32 for my windows 32 bit system .

Note:- There is no separate patch set for oracle client, the same patch for oracle database software  can be used for oracle client .

4.Extract the patch set  and run the setup.exe file . On the specify home details  window

choose the appropriate oracle home that you want to upgrade and click next

Once the installation become finished check the version of the client software by tnsping command 

hope it help somebody :)