ORA-12516, TNS:listener could not find available handler with matching protocol stack

In one of my SOA project, we are running SOA Suite 11g - 11.1.1.4  on Weblogic 11g Cluster in Bladframe machine. Backend used here for SOA Schema database is Oracle 11g RAC database.

After couple of weeks of successfully execution in developement environment we hit this issue of  ORA-12516, TNS:listener. This error generally means there is no connection available to the database.We need to configure the database to allow more connection.

As suggested by Oracle, we should have number of processes setting to 200 for SOA Suite 11g. This setting is already in place and still we hit the ORA-12516, TNS:listener-issue. This happens mostly because of wrong Data sources connection settings, which allows to exapand its connection pool to hundreds of connections.

Even while restarting the weblogic server you will not be able to get the weblogic server in running mode , because all data sources will fail while trying to connect to database and you server state will go to Admin mode because of this.

Solution to ORA-12516, TNS:listener Error

1. Loging to one of the database node using sqlplus as sys or system (basically sysdba role required)
2. Increase the number processes and sessions by executing below commands
• ALTER SYSTEM SET PROCESSES=400 SCOPE=SPFILE;
• ALTER SYSTEM SET SESSIONS=400 SCOPE=SPFILE;
3. Bounce the database again to take these changes
• SHUTDOWN IMMEDIATE;
• STARTUP;
4. To Verify the settings are updated properly; execute below sql commands
1. SHOW PARAMETERS PROCESSES;
2. SHOW PARAMETERS SESSIONS;

If you are using Oracle RAC database you may want to perform these steps again on another node.

Installing Oracle XE 11g Database on Redhat 5.6 / Linux 64 bit

Recently Oracle has announced the Beta release of Oracle Express Edition (XE) 11g. Below is the step by step process for the installation of Oracle XE 11g database on Linux x86-64 machine.

Installation of Oracle XE 11g is pretty straight forward process and all step by step installation steps are included here in this tutorial. All XE version of oracle are very easy to install and configure while you are in development mode.

Oracle XE 11g Installation steps :

1. You can download the most recent version of Oracle XE 11g database by refering the details on http://www.oracle.com/technetwork/database/express-edition/overview/index.html.
2. This installation will require minimum of 1.5 GB of RAM, so please make sure you have required space available on your machine.
3. Swap space required is 2 GB or twice the size of RAM. Whichever is less.
1. To check current you can run command swapon -s on shell.
4. And to start the installation it requires root permission on linux box.
5. execute the below command to install the Oracle XE 11g 
6. Oracle does not provide a way to install Oracle XE 11g on particular directory. It will install it directly on root /u01 but if you want to install this on directory of your choice use my post Hacker's choice.
7. rpm -ivh oracle-xe-11.2.0-0.5.x86_64.rpm below is the command with output
8.  rpm -ivh oracle-xe-11.2.0-0.5.x86_64.rpm
   Preparing...                ########################################### [100%]
      1:oracle-xe              ########################################### [100%]
   Executing post-install steps...
   
   You must run '/etc/init.d/oracle-xe configure' as the root user to configure the database.
   
9. Execute below command to configure Oracle XE 11g database.
   

   $ /etc/init.d/oracle-xe configure

10. It will ask for HTTP Port , database listener port, and password for the database which will be used for SYS and SYSTEM. Below is the complete installation output.
11. --> /etc/init.d/oracle-xe configure
    
    Oracle Database 11g Express Edition Configuration
    -------------------------------------------------
    This will configure on-boot properties of Oracle Database 11g Express
    Edition.  The following questions will determine whether the database should
    be starting upon system boot, the ports it will use, and the passwords that
    will be used for database accounts.  Press to accept the defaults.
    Ctrl-C will abort.
    
    Specify the HTTP port that will be used for Oracle Application Express [8080]:
    
    Specify a port that will be used for the database listener [1521]:
    
    Specify a password to be used for database accounts.  Note that the same
    password will be used for SYS and SYSTEM.  Oracle recommends the use of
    different passwords for each database account.  This can be done after
    initial configuration:
    Confirm the password:
    
    Do you want Oracle Database 11g Express Edition to be started on boot (y/n) [y]:
    
    Starting Oracle Net Listener...Done
    Configuring database...Done
    Starting Oracle Database 11g Express Edition instance...Done
    Installation completed successfully.
    
     
12. Now start the Oracle XE 11g database by using below command
13. --> /etc/init.d/oracle-xe start
14. If you want to stop command is --> /etc/init.d/oracle-xe stop

    Uninstalling the Oracle XE 11g #

    --> rpm -qa | grep oracle 

    --> rpm -e oracle-xe

Hacker's Choice - How to Install Oracle 11g XE on Specified Targeted Directory

If you are installing Oracle 11g XE on Linux machine, it is getting installed by default on root (/). And it does not provide a way to change.

Oracle is not providing a way to specify the destination directory / user defined directory for Oracle XE 11g Database. So here is the hack for how to install Oracle XE database on directory of your choice.

create a symbolic link with the name /u01 in root directory which point to /app/oracle.
For Oracle XE 11g  use below commands
--> cd /
--> ln -s / u01

Same trick works for Oracle XE 10g as well.

And .... yeah enough you are done. Go ahead with your normal installation and it will be installed to /app/oracle.