We use Oracle Database 10g Express Edition Release 10.2.0.1.0 for our RoR development and were hitting the TNS:listener error all the time. This made concurrent development a real pain and we couldn't leave staging servers and sqldeveloper sessions up.
Google searches turned up really scant information - mainly advices to increase the PROECESSES limit. I am not a DBA, so wasn't sure what needed to be done.
In any case, the following solution worked like a charm and I hope others who have this issue find this post useful.
source /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.csh
setenv ORACLE_SID XE
And then execute the following steps to increase the number processes and sessions:
1) Connect to the database using the sys or system user:
sqlplus SYSTEM/
2) To confirm the PROCESSES and SESSIONS values, run the following script:
sql> col name format A30
Sql> col value format A30
sql> select name, value from v$parameter where name in ('processes','sessions');
3) SQL> alter system set processes=300 scope=spfile;
4) SQL> alter system set sessions=300 scope=spfile;
5) Bounce(restart) the database to reflect the changes
6) To confirm new values run the script (2).
Change Static parameters in SPFILE:
Changing static parameters requires the SPFILE option for the SCOPE clause with ALTER SYSTEM Statement and changes applies to SPFILE only. The changes cannot be applied to active Instance and the database needs to be restarted to take effect the modified parameters.
You can identify the static parameters using below query
SQL> select name, value, issys_modifiable from v$parameter
where name='processes';
NAME VALUE ISSYS_MODIFIABLE
------------------- -------------- ---------------------------
Processes 150 FALSE
All parameter that have the column ISSYS_MODIFIABLE value FALSE in the V$PARAMETER view are STATIC parameters and remaining are DYNAMIC parameters in Oracle database.
The ALTER SYSTEM command with SCOPE=SPFILE the will not update the value in the V$PARAMETER view but it will show the new value in the V$SPPARAMETER view as Oracle SPFILE parameter is updated.
For ex:-
SQL> alter system set processes=200 scope=both;
alter system set processes=200 scope=both
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
Above statement failed because “processes” is Static parameter and can be used with SCOPE=SPFILE only.
SQL> alter system set processes=200 scope=spfile;
System altered.
You can view the new value in V$SPPARAMETER view as the SPFILE is updated.
SQL> select name, value, isspecified from v$spparameter
where name ='processes';
NAME VALUE ISSYS_MODIFIABLE
------------------- -------------- ---------------------------
Processes 150 TRUE
Changing Static parameters in PFILE:
Edit the “$ORACLE_HOME/dbs/init.ora” file with new value and bounce the database to take effect the modified parameters.
Identify weather using PFILE or SPFILE:
To check SPFILE or PFILE used by Database or instance, run below commands.
You can see the Oracle SPFILE location.
SQL > show parameter spfile;
NAME TYPE VALUE
------------------ ----------- -----------------------------------------
spfile string /oracle/v10201/dbs/spfileOradb.ora
SQL > select name, value from v$parameter where name = 'spfile';
NAME VALUE
------------ ------------------------------------------------------------
spfile /oracle/v10201/dbs/spfileOradb.ora
SQL> select count(1) from v$spparameter where isspecified = 'TRUE';
Returns a NON-ZERO value if SPFILE is in use.
Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/
Changing static parameters requires the SPFILE option for the SCOPE clause with ALTER SYSTEM Statement and changes applies to SPFILE only. The changes cannot be applied to active Instance and the database needs to be restarted to take effect the modified parameters.
You can identify the static parameters using below query
SQL> select name, value, issys_modifiable from v$parameter
where name='processes';
NAME VALUE ISSYS_MODIFIABLE
------------------- -------------- ---------------------------
Processes 150 FALSE
All parameter that have the column ISSYS_MODIFIABLE value FALSE in the V$PARAMETER view are STATIC parameters and remaining are DYNAMIC parameters in Oracle database.
The ALTER SYSTEM command with SCOPE=SPFILE the will not update the value in the V$PARAMETER view but it will show the new value in the V$SPPARAMETER view as Oracle SPFILE parameter is updated.
For ex:-
SQL> alter system set processes=200 scope=both;
alter system set processes=200 scope=both
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
Above statement failed because “processes” is Static parameter and can be used with SCOPE=SPFILE only.
SQL> alter system set processes=200 scope=spfile;
System altered.
You can view the new value in V$SPPARAMETER view as the SPFILE is updated.
SQL> select name, value, isspecified from v$spparameter
where name ='processes';
NAME VALUE ISSYS_MODIFIABLE
------------------- -------------- ---------------------------
Processes 150 TRUE
Changing Static parameters in PFILE:
Edit the “$ORACLE_HOME/dbs/init.ora” file with new value and bounce the database to take effect the modified parameters.
Identify weather using PFILE or SPFILE:
To check SPFILE or PFILE used by Database or instance, run below commands.
You can see the Oracle SPFILE location.
SQL > show parameter spfile;
NAME TYPE VALUE
------------------ ----------- -----------------------------------------
spfile string /oracle/v10201/dbs/spfileOradb.ora
SQL > select name, value from v$parameter where name = 'spfile';
NAME VALUE
------------ ------------------------------------------------------------
spfile /oracle/v10201/dbs/spfileOradb.ora
SQL> select count(1) from v$spparameter where isspecified = 'TRUE';
Returns a NON-ZERO value if SPFILE is in use.
Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/
- show parameter spfile;
- SQL> select name, value from v$parameter where name in ('processes','sessions', 'parallel_max_servers', 'db_file_multiblock_read_count', 'spfile' );
