|
Using Oracle
Hit List Enterprise and Hit List Live can store their database
in any combination of Microsoft Access, Microsoft SQL Server
6.5/7.0 or Oracle 7/8 databases. For most purposes, the default
Microsoft Access choice will provide better performance. However,
Oracle or SQL Server are the preferred choices for very large
sites where you expect the database to grow to several gigabytes
or where many users may want to run reports simultaneously.
Performance Note: Oracle and SQL Server are robust multi-user
databases. However, for most uses, Microsoft Access will offer
substantially better performance. If you choose to use Oracle
or SQL Server, please understand that performance will be
generally slower. Additionally, many users choose to use a
combination of Microsoft Access and Oracle or SQL Server databases,
reserving Oracle and SQL Server for the few virtual domains
where they really need the extra storage capacity.
Performance Monitoring. Hit List performance can be monitored
and tuned by using the Performance Monitor tool (PerfMon)
that comes with Microsoft Windows NT. Hit List Live, in particular,
includes a wide variety of PerfMon objects that monitor packet
sniffing and database activity. See the Performance Monitoring
topic in this document for more information.
Oracle
1. Placing Hit List on the same computer as Oracle will reduce
network congestion, and improve the overall speed of processing
for most sites unless your site has a very high-performance
Oracle system running somewhere else. If using both Oracle
and Hit List together on the same computer, we suggest 512MB
of RAM. Also, using a multi-processor machine will improve
performance. Very large sites (such as more than 1GB of daily
logs) may see better performance by having Oracle reside on
a remote machine if the remote machine is substantially faster
than the NT machine that Hit List is running on. Oracle may
be running on any platform; NT is not required. In all cases,
using a RAID 0 disk array, rather than standard IDE or SCSI
drives ,will provide vastly better performance (like 200%
to 500% speed improvement).
2. Create a database for Hit List to use. Create a tablespace
for Hit List to store information. If you are using a fixed
size datafile for the user data we suggest a default size
of at least the size of the log files you wish to process
if you need to store Detail data. If Summary data will suffice
(keep in mind that almost all the built-in reports run just
using Summary data), you can set the datafile to perhaps one-third
the size of the logs. For most users, we suggest allowing
Oracle to grow the datafiles as required.
3. For optimal performance, use different datafiles on different
physical disk drives for each tablespace created (system,
rollback segments, indices, and temp). This will help distribute
the physical IO load and reduce contention for resources between
dissimilar operations.
4. Ensure that ODBC is installed and that you have a System
DSN set to the newly created Hit List database. Hit List has
been tested and verified to work with version 2.573.252309
and later of Microsoft's ODBC Driver for Oracle. New ODBC
drivers are available for download on Microsoft's web site
at www.microsoft.com
Important! The tested Oracle driver is called Microsoft
ODBC for Oracle. Do not confuse this with the older driver
called Microsoft ODBC Driver for Oracle as the later
driver will not work correctly with Hit List. Also, you will
need to install at least some Oracle-supplied client software
to get the Microsoft ODBC for Oracle driver to work
correctly. If you can create a DSN with the driver, you have
the software you need. If the driver fails and says that you
need more software, please run the Oracle setup program and
select Client Software.
5. After you start Hit List, select the Database tab in Tools/Options
dialog then enter an appropriate ODBC datasource, user name
and password to connect to the Hit List database on Oracle.
6. Parameters to improve Oracle performance with Hit List:
db_block_buffers - This is your data cache. Setting
this configuration value as high as possible will improve
overall performance. We suggest a setting of at least 600.
processes - This is the number of processes that can simultaneously
connect. We suggest a setting of at least 200.
open_cursors - This is the maximum number of cursors
Oracle can open at once. Cursors are allocated for the processing
of SQL statements. We suggest a setting of at least 250.
Running out of available processes or cursors can prevent
Hit List from processing updates properly. These parameters
are kept in the Oracle initialization file (commonly called
init.ora).
Important Note: Hit List is very sensitive to specific
Oracle permissions. If you have any trouble using Hit List
with Oracle, please refer to the detailed Oracle Configuration
guide.
Note: The data Hit List stores is converted to lower case
characters for string values due to issues related to Oracle
sorting and dictionary order. When linking tables using DataLink
for reports it is important to use all lower case for string
comparisons of Hit List data.
Oracle Configuration
Hit List is designed to work efficiently with Oracle 7 and
8 databases. Hit List communicates to the database via ADO/ODBC
which implies that you should confirm that your local Windows
NT machine can communicate via ODBC to the Oracle database
(often not the case). We've found that this is relatively
easy for Oracle running on Windows NT but requires some careful
configuration, especially regarding permissions, when Oracle
is running on a Unix platform. The following step-by-step
checklist should help you solve most problems but, as always,
Hit List Consulting and Technical Support is available for
further assistance.
Note: This only applies if you are not using an Oracle
TNSNames server. If you are, you'll have to talk with your
Oracle DBA about connectivity issues.
Setting up the Client and checking network connectivity
You must have the Oracle Client installed. This must include:
A. SQL*Net (plus "protocol adapters", i.e. NetBEUI,
TCP/IP, Named Pipes, etc.)
B. ODBC driver (despite not probably using it)
C. SQL*Plus
Set up your SQL*Net alias, using "SQL*Net easy config"
Required here:
Oracle SID name
Machine name or IP address of the Oracle server machine
Port the server is listening on (will be either 1521 (the
default) or 1526)
Arbitrary alias name of your choice
Reboot machine
Test the alias using TNS ping
From Command window (CMD) do a TNS Ping to the machine you
need to talk to
For example:
TNSPing alias.world (as set up above, include the extension
world after your alias)
This tells your set up alias to go out and connect to the
database, establishing that your network connectivity is fine.
This does not guarantee that you can do anything once connected,
but it should establish that you can connect.
You should get an error here if this doesn't work. You must
resolve any issues at this step before continuing.
Things to look for in the case of an error:
1. Version of SQL*NET needs to be "higher" than
the database, i.e. SQL*NET 2.3.2.1 will not work with Oracle
server 7.3.2.2 and SQL*Net 2.3.4 will work with Oracle server
7.3.3.5
Note: The versions numbers of SQL*Net all start with 2 instead
of 7 but the latter three numbers need to be higher as seen
in the example
2. Descriptive error message. The error message returned
should be relatively informative as to what might be going
on (e.g. Oracle not found)
3. If you set the database to port 1526 but the client expects
(i.e.TNS Ping) port 1521, it will connect using TNS Ping but
SQL*Plus will not work because it's expecting port 1521 if
you don't otherwise indicate a port. Your Oracle DBA can provide
the correct port number.
4. When testing the SQL*NET alias with TNSPing, always use
alias.world. Using anything else may cause a failure.
5. If another machine has connectivity and your machine does
not, you can copy two files from the "connectable"
machine to yours as a test. These files are SQL*Net.ora and
TNSNames.ora. Use the Find facility in Windows NT to find
these files. By default, one will be in a folder named c:\OraNT\Network\Admin
and the other in a folder named c:\OraNT\Net80\Admin.
Note: You can't copy files from a "Net8" computer
to a "SQL*Net" computer as the two systems are incompatible.
This is only an issue if you have multiple Oracle client setups,
some of which use Oracle 8 and some of which use the older
versions of SQL*Net.
Assuming TNSPing has worked, then you've established network
connectivity and can move to (2).
Testing the database connection with SQL*Plus
1. Log into the Oracle database using SQL*Plus. For example,
scott/tiger@alias.world
The key here is to test with the same Oracle user that Hit
List will eventually use. If you use something else, you only
prove that you can connect with that name, which, given different
permission levels for different users, is an entirely separate
issue.
2. Run some test SQL statements that exercise the same features
that Hit List will require. This includes the ability to create
and drop tables and stored procedure, add data, update existing
data and retrieve data.
Suggested examples:
create table Test(ID number,Name varchar2(255));
insert into Test (ID,Name) values (1,'my test');
select * from Test;
delete Test;
select * from Test;
drop table Test;
If all of these work successfully, you've established that
everything works on the Oracle side and you can go to the
next step. If any of these don't work, Hit List won't work
either. This is almost always caused by insufficient permissions.
Hit List requires a specific set of privileges, as listed
below. Check with your DBA if you aren't sure which privileges
you have.
Roles:
CONNECT
RESOURCE
System Privileges:
UNLIMITED TABLESPACE
Note: There have been some problems with permissions that
have been described by customers as more than adequate for
normal operation but have failed to allow HitList to properly
function. It is, therefore, essential to use exactly these
settings.
Test with Oracle's ODBCTest Application
The Oracle Client installation should have installed a program
called ODBCTest. We'll use this to test basic ODBC connectivity.
If you haven't already done so, set up a System DSN in the
Control Panel ODBC applet that uses the Microsoft ODBC driver
for Oracle. Ensure that you are using the Microsoft ODBC for
Oracle driver version 2.573.292700 from May 27, 1998 or later.
If not, you can get this driver from Microsoft's web site
at http://www.microsoft.com/data/mdac2.htm. Download the 6.2
MB installation as you do not need the SDK.
Start ODBCTest and pick the datasource that you just created.
Again, ensure that this datasource is using the Microsoft
ODBC for Oracle driver not an older Microsoft or Oracle-supplied
driver as these will not work.
Enter the same SQL statements you did in the SQL*Plus test
above.
If everything works here the datasource should work and Hit
List should work fine. If this doesn't work, contact Technical
Support.
|
