Sunday 27 January 2013

Oracle: .profile sample file for oracle environment


ORACLE_BASE=/fmstest;export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/ora1024;export ORACLE_HOME
export CLASSPATH=$ORACLE_BASE/jre/1.1.8,$ORACLE_HOME/product/jlib
export  LD_RUN_PATH=$ORACLE_HOME/lib64:/usr/lib:/lib
export AIXTHREAD_SCOPE=S
export PATH=$ORACLE_HOME/bin:/usr/bin:/usr/local/bin:$ORACLE_HOME/perl/bin
NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1;export NLS_LANG
ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data;export ORA_NLS33
ORACLE_SID=wrklght;export ORACLE_SID
export LIBPATH=$ORACLE_HOME/lib
set -o vi
export ORACLE_LIB=$ORACLE_HOME/lib:/usr/lib:/lib:/usr/ucblib
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/network/lib:/usr/lib
export PS1="`hostname`:"'$PWD>'
echo "################################################"  >> .sh_history
who am i | awk ' { print $6 }' >> .sh_history
echo "################################################"   >> .sh_history
######################################################################

Oracle: Configure the .oraenv file


1. If you are creating a database manually you have to put the entry in ORATAB file.
If you are creating database using DBCA then the database entry will automatically
filed in oratab file.
2- Location of oratab file is /etc/oratab

3. Then edit the oratab file. It will look like below:
# A colon, ':', is used as the field terminator. A new line terminates
# the entry. Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME::
#
# The first and second fields are the system identifier and home
# directory of the database respectively. The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
*:/u01/app/oracle/product/9.2.0:N
TSH1:/u01/app/oracle/product/9.2.0:N
juli:/u01/app/oracle/product/9.2.0:N
orcl:/u01/app/oracle/product/10.2.0/db_1:N
+ASM:/u01/app/oracle/product/10.2.0/db_1:N
rmanrep:/u01/app/oracle/product/10.2.0/db_1:N
mom:/u01/app/oracle/product/10.2.0/db_1:N
preetha:/u01/app/oracle/product/10.2.0/db_1:N

4. Then run the file: . .bash_profile or .profile
5. Then run . oraenv
6. Then enter the name of the database(instance name) you wanna open
7. This is all about running a oraenv file

Sunday 13 January 2013

ORA-00018 maximum number of sessions exceeded


ORA-00018 maximum number of sessions exceeded

Cause: All session state objects are in use.
Action: Increase the value of the SESSIONS initialization parameter.

Reference: Oracle Documentation

ORA-00018 comes under "Oracle Database Server Messages". These messages are generated 
by the Oracle database server when running any Oracle program.

How to increase SESSION initialization parameter:

1. Login as sysdba
 sqlplus / as sysdba
 
2. Check Current Setting of Parameters
 sql> show parameter sessions
 sql> show parameter processes
 sql> show parameter transactions

3. If you are planning to increase "sessions" parameter you should also plan to increase 
"processes and "transactions" parameters.
A basic formula for determining  these parameter values is as follows:
  processes=x
  sessions=x*1.1+5
  transactions=sessions*1.1
  
4. These paramters can't be modified in memory. You have to modify the spfile only 
(scope=spfile) and bounce the instance.
 sql> alter system set processes=500 scope=spfile;
 sql> alter system set sessions=555 scope=spfile;
 sql> alter system set transactions=610 scope=spfile;
 sql> shutdown abort
 sql> startup 
 

Number of Visitors