Wednesday, April 21, 2010

init parameter PROCESSES

Initialization parameter PROCESSES defines the max number of user connections that can access the database at a same time. The default value for the parameter is 300.

If the max number exceeds the parameter value, new connection will not be able to connect to the database. Grid Control will report the connection problem and the database will give error in trace file:

ORA-00020: maximum number of processes (300) exceeded

In that case, the issues can be confirmed by number of OS sessions and by database view v$process:

$ echo $ORACLE_SID
$ ps -ef | grep $ORACLE_SID | wc -l
299

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

COUNT(*)
----------
297

To increase the value, run an alter statement. Then a database bounce is necessary.

SQL> alter system set processes=500 scope=pfile;

I read that below three parameters are related by a formula (in 10G, but might not be always). If database reports ORA-00018 error, you may also consider modifying parameter PROCESSES (or find why the number of sessions jumped).
ORA-00018: maximum number of sessions exceeded
processes=x
sessions=x*1.1+5
transactions=sessions*1.1

Thursday, April 8, 2010

Oracle Client Patchset

When Oracle releases a database (RDBMS) version, it releases a corresponding client. For example, for 11.2.0.1.0 database server, aix.ppc64_11gR2_client.zip and aix.ppc32_11gR2_client.zip are client files. If you want to upgrade your client (i.e. from 11.2.0.1 to 11.2.0.3), you have to use the server patchset. Document 438049.1 says "For database and client upgrade use the same patch set. There is no separate patch set for client".

Document 207303.1 provides a matrix summarizing client and server combinations that are supported Oracle.

To install Oracle client utilities, choose "Custom" during the installation. Document 437377.1 shows how to install SQL*Loader on the 10G client:

The steps to install SQL*Loader in client in Custom Installation :

1) Start the Oracle Universal Installer (OUI) from the client source (CD)

2) Choose 'Installation Type'
==> Select the "Custom" and then continue with 'Next'

3) Choose the ORACLE_HOME, continue with 'Next'

4) In the 'Available Product Components' Screen
==> Select the "Oracle Database Utilities" and then continue with 'Next'
Note: Only components with a check mark are installed.

5) At the "Summary" screen Click "Install"

Finish the installation. This will Install SQL Loader.

You will be able to find the "sqlldr.exe" (or "sqlldr") in the directory %ORACLE_HOME%\bin in Windows and $ORACLE_HOME\bin in UNIX