documentation distinguishes between an Oracle instance and an
A database is the set of files and associated permanent
control information that constitute the persistent, managed
An instance is the set or processes and associated control
information that manipulate the content of a database.
Since (unless Oracle Parallel Server feature is used) there is
always a one-to-one relationship between an instance and a
database, this is not always a useful distinction. Common usage
tends to blur these distinctions, with the combined instance and
database merely being referred to as an Oracle database. In this
article, I’ve tried to use the terms as they are used in the Oracle
documentation, but perhaps not always successfully.
Identifying and locating an Oracle
In a client-server
or n-tier environment there must be a mechanism for the Oracle
database client (which may be an MTS component) to locate and
establish communication with the processes that comprise the
target Oracle instance. Oracle provides several mechanisms for
solving this problem. Net8, the part of Oracle that communicates
between Oracle client programs and Oracle instance, implements
One way is to have a set of configuration files on each Oracle
client computer that has the required information. These files
provide a mapping between a TNS or Net Service name and the
instance, which is often on a different computer.
Another mechanism, which avoids having to replicate the
mapping information to all clients, is the Oracle Name Service.
Instances of the Oracle Name Service on different computers, in
conjunction with the Listener service, co-operate to maintain the
required name to computer and instance mappings in an
"Enterprise". A client only needs to know how to
contact a Name Service instance and can then use that service to
find out how to communicate with any Oracle instance
known to any of the Name Service instances.
The Net Service Name exposed by the Oracle Name Service
or recorded in the TNSName configuration files is used in the ADO
Connection’s Data Source property to identify the particular Oracle
instance that is to be associated with that connection. The
Oracle Client uses whatever Oracle name resolution mechanisms it
has been configured to resolve the Net Service Name to the target
This Net Service Name is also used by the Oracle Services for
MTS to establish communication with the particular Oracle
instance it is configured to co-operate with.
Security for any
database is an important issue. Oracle has a fairly sophisticated
built in security system. However, when users use many services,
including one or more Oracle databases, maintaining the user
information separately for each service gets to be a user and
administrative nightmare. Users get multiple user names and
passwords to remember and the passwords have to be changed
every so often.
It may be possible, in the general, theoretical sense, to keep
all security related information in one place. However, particularly
when products from multiple vendors are used together, we have
no way to reach this utopia yet. Each service has unique things that
need to be "secured" and the same user may have
different rights and permissions for different instances of a service.
However, there are ways to reduce the problem and Oracle
provides facilities for this.
Instead of "authenticating" users by passwords
stored in the Oracle database, Oracle allows you to use the
authentication service of the operating system it is installed on or
more sophisticated "enterprise" schemes, such as
Kerberos. Using the operating system’s authentication service is
called OS Authentication.
Even though authentication is handled by the operating system,
each user must still be identified to Oracle and be granted the
appropriate System Privileges and Roles. On
Windows NT and Windows 2000, this is done using the Oracle
Administration Assistant for NT. Using this tool you can create
External OS Users and grant External OS Roles to
See the Oracle Administrator’s Guide, Chapter 8:
Authenticating Database Users with Windows for additional
References - Documentation
MS KB Article
Q193893 INFO: Using Oracle Databases with Microsoft
Transaction Server Using Microsoft Transaction Server with
Oracle8 (does not apply if the Oracle supplied Oracle OLE DB
Provider is used; see Background:)
MS KB article Q241202:
HOWTO: Produce a Manufactured Hierarchical Recordset
Base on an Existing Recordset.
MS KB article Q244661:
INFO: Microsoft Oracle ODBC Driver and Provider Support
Connectivity to Oracle 8I
ADO Data Types
Cross-Reference (ADO, OLE DB, VB etc.)
ADO Connect strings for various
providers, including Oracle’s OLE DB Provider
Since 1975, Bruce has lived in Victoria and worked for the BC
Assessment, first as an application programmer, and now as
System Architect. Part of his job is solving problems others can’t
solve and figuring out how to best use new technologies in the
BCA applications. This often involves "breaking new
ground" and is well known by his co-workers for devouring
vendor documentation and digging out how things work.
Over the years, Bruce has worked on many different computer
systems and become fluent in several programming languages,
including 360 Assembler, COBOL, PL/1 and VB. Having worked
with it for many years, he is an expert in IBM’s IMS DB/DC.
Bruce is also an avid reader and frequent contributor to various
Microsoft software newsgroups.
Bruce is married and has three children; two boys and a girl.