OpenSpirit - Support - End-User FAQ - OpenSpirit Version 2.5.0

Admin/Install >> Oracle/JDBC

	How can I determine as to who is consuming the database connections?
	How can I increase the number of concurrent connections to an Oracle Database instance?
	What does the message "ERROR: ORA-12154:TNS:could not resolve service name" mean?
	What does the message, java.sql.SQLException: The Network Adapter could not establish the connection, mean in the trader log file?
	How can I find out how much space is being used in the ADS by the OSP persistence objects?
	How can I find out how big my ADS database is and where the data files are?
	What does it mean if I get an Oracle 1033 error message?
	I'm getting the error "The default table space for the OpenSpirit account appears to be invalid. Please provide an Oracle account with a properly created default table space." during the installation where one specifies the OpenSpirit Oracle Account!

How can I determine who is consuming the database connections?

A JDBC SQLException with the message, "java.sql.SQLException: ORA-00020: maximum number of processes (nn) exceeded" means that the Oracle database has reached the maximum number of connections allowed by the instance. There is no easy way to determine who is taking up all of the connections, but there are a few tables in the database that help to investigate the problem. These are system views/tables. They are V$SESSION, V$PROCESS, V$ACCESS, V$SQLTEXT. The table V$PRCESS lists all of the processes in the instance and there may be more than one session per process in the instance. The relationship between the V$PROCESS and V$SESSION tables is based on the column PADDR in the session table and the column ADDR in the process table. Here are a few helpful SQL statements that will give you an idea as to what is going on:

•	This lists all of the processes in the instance, but it is only useful if you have different UserNames. If you have all your connections done through the same user name, such as OpenSpirit, then it might not be very helpful. SELECT ADDR, PID, SPID, USERNAME, SERIAL#, TERMINAL PROGRAM FROM V$PROCESS;
•	This lists all of the sessions in the Oracle instance. You can get an idea as to who is active and who is logged on. Again, it is not very beneficial if all your users share the same oracle username to log on. You can usually tell who has data servers running because both the OpenWorks and GeoFrame devkit use different oracle usernames to log on. SELECT SID SessionID, USERNAME OracleUserName, decode(COMMAND, 2, 'INSERT', 3, 'SELECT', 6, 'UPDATE', 7, 'DELETE', 44, 'COMMIT', 45, 'ROLLBACK', NULL, '', 'DDL COMMAND') COMMAND, LOCKWAIT, STATUS, SCHEMANAME, OSUSER, PROCESS osprocess, TERMINAL osterminal, TYPE FROM V$SESSION;
•	This gives you an idea as to which tables belonging to 'OSP' are being accessed. Sometimes seeing what table is being accessed can be helpful in finding out who is doing what. SELECT OWNER, OBJECT FROM V$ACCESS WHERE OWNER = 'OSP';
•	This statement is less useful. It will give you an idea as to which statement the session is currently processing. If you are familiar with the Auxiliary Data Store structure, you may be able to tell what process is active at that time. This is a snapshot of the time the select statement is executed. If you run this repeatedly, you may be able to tell what statement is using a lot of time. SELECT A.SID SessionID, A.USERNAME OracleUSerName, A.LOCKWAIT, A.SCHEMANAME, A.OSUSER, A.PROCESS OSPROCESS, B.PIECE, B.SQL_TEXT FROM V$SESSION A, V$SQLTEXT B WHERE A.USERNAME NOT IN ('SYS', 'SYSTEM') AND A.SQL_ADDRESS = B.ADDRESS AND A.SQL_HASH_VALUE = B.HASH_VALUE ORDER BY A.SID, A.SQL_ADDRESS, A.SQL_HASH_VALUE, B.PIECE;

Consult the Oracle Database Administrator reference to get more details on these tables. If the DBA didn't grant public select privilege on these tables/views, you have to log on as a DBA to run the select statements.

How can I increase the number of concurrent connections to an Oracle Database instance?

There are database processess and a parameter that can be set in the Oracle init.ora file. Edit the proper init.ora file that is usually under the $ORACLE_HOME/dbs directory. Set the parameter processes to the desired number. The entry and processes = 100, and will set the limit to 100 concurrent connections. Shutdown the database and restart. If the database fails to restart properly, then it could be that the processes number exceeds the Unix system parameter. You may need to update the semaphores of the database machine. Refer to your Oracle database installation and administration guide for the system parameter requirements.

What does the message "ERROR: ORA-12154:TNS:could not resolve service name" mean?

Oracle must know the location of some administration files in order to work properly. One of the most important files is tnsnames.ora. This file defines the TWO_TASK, ORACLE_SID, and PORT. These variables are very important. If you supplied a TNS_ADMIN directory that is not correct, you will get errors related to TNS. Make sure the TNS_ADMIN directories entered during installation and configuration are the correct directories and that they contain a valid tnsnames.ora file. You can refer to some hints in the Troubleshooting Guide for further information.

What does the message, java.sql.SQLException: The Network Adapter could not establish the connection, mean in the trader log file?

This SQLException is thrown when the process fails to use the JDBC driver in connecting to the database using the given instance information. Check the host name, port number, Oracle SID, userid, and password entered during the installation of the OpenFusion. This information can be found in the file openfusion.properties in the OpenFusion classes directory. If all the information is correct, the problem may be because the network or OpenFusion database is down.

How can I find out how much space is being used in the ADS with the OSP persistence objects?

You can find out how much space is used by the OSP persistence objects by each user with the following sql statement. First log onto sqlplus as the owner of the persistence object tables.

  SELECT USER_ID, COUNT(*), SUM(BYTES),
  AVG(BYTES)
    FROM  COP_OBJECT A, COP_SERVER B
    WHERE A.ID = B.ID
    GROUP BY B.USER_ID;

Count(*) returns the number of persisted objects for the user.
Sum(Bytes) returns the total number of bytes used to store the persisted objects for the user.
AVG(Bytes) returns the average number of bytes per object for the user.

Here is an example result:

USER_ID   COUNT(*)    SUM(BYTES)     AVG(BYTES)
-------   ---------    --------     ----------
abc            1693    18743226    11071.0136
def               3        7769     2589.66667
ghi             139      867660     6242.15827
jkl              10       48800     4880
mno             985     5316588     5397.55127
pqr               2        2246     1123
stu            2471    98325903    39791.9478

How can I find out how big my ADS database is and where the data files are?

You can find out the amount of space allocated to the ADS database by using the following sql statements. The views used in the statement are system views. If your DBA hasn't granted the public select privileges to these views, you will have to log onto sqlplus as a DBA to use the views.

  SELECT FILE_NAME, BYTES FROM
  SYS.DBA_DATA_FILES
   WHERE TABLESPACE_NAME IN
    (SELECT DISTINCT TABLESPACE_NAME FROM
    SYS.DBA_SEGMENTS
     WHERE OWNER = 'OSP2001');

This statement will return the list of files used to store database objects such as, tables, indices, etc, that are created by the owner 'OSP2001'. Replace OSP2001 with your ADS oracle username. Here is a result of the above statement:

FILE_NAME                           BYTES
---------------------------------   --------
/usr/local/oracle/dbs/OSP2001       398458880

What does it mean if I get an Oracle 1033 error message?

Cause: Oracle Database is not running or is in the process of starting up.

Solution: If the problem persists, it may be caused by the deletion of an Oracle table space file prior to dropping the table space within Oracle. You can usually ascertain whether this is the problem by observing the problem data file during the Oracle startup logs.

First, NEVER delete an Oracle Tablespace file prior to dropping it from Oracle. However, if this occurs, the following procedure will repair the database, allowing it to startup properly.

Get into a proper Oracle environment. You can usually find a file in the Oracle installation directory called Oracle_Runtime.csh. Source this file and then check to be sure that ORACLE_SID is set to the instance name of the instance you want to repair.

% Run svrmgrl

SVRMGR> connect internal
(If it asks for a password then you are entering svrmgrl with the wrong account.)

Now you must do the following commands, maybe several times, before you can start successfully. When you do a startup at first it should complain about the deleted data file. This is the data file that needs to be dropped in the alter command below:

SVRMGR> shutdown abort

SVRMGR> startup

SVRMGR> alter database datafile '' offline drop;

Sometimes you need to perform the three above commands several times before it takes properly.

I'm getting the error "The default table space for the OpenSpirit account appears to be invalid. Please provide an Oracle account with a properly created default table space." during the installation where one specifies the OpenSpirit Oracle Account!

The installer is looking for the existence of an empty table named OPENSPIRIT

To verify that the oracle account's default table space is a table space dedicated to the OpenSpirit installation.

If you create the table space through our installer it will create this table.

We also provide a script on our web site that can be used to create the table space outside of our installer:

ADSCreateTableSpace.sh

If you already have a table space, you can go into sqlplus and issue the following command.

CREATE TABLE openspirit (id NUMBER PRIMARY KEY);

Site Map • Legal • Privacy