A major premise of this book is that application developers will use Personal Oracle as a tool for prototyping Oracle applications. The purpose of this chapter is to discuss the elements you need to consider when upsizing your Personal Oracle prototype to a client/server architecture. For an Oracle database server, you can choose either the Oracle Workgroup Server or the Oracle7 Enterprise Server. This chapter presents the distinguishing features in both products.
Ten years ago most database applications were terminal based--;a database resided on a single computer to which some number of CRTs were attached. Sometimes a terminal controller served as an intermediary between the central computer and a large number of CRTs. In both cases the computer was responsible for almost everything--;painting each user's screen, navigating each user through a screen, validating data entry, and updating the database.
Recently, many applications have made the transition to an entirely different architecture: client/server. The client/server architecture consists of three principal elements. (See Figure 29.1.)
The transition to the client/server architecture has been driven by several factors.
The client machine in a client/server architecture doesn't have to be either a PC or a Macintosh. The client could just as easily be a CRT managed by an application server. (See Figure 29.2.) As lovers of the latest technology, we sometimes forget that an information system exists to satisfy a business or organizational requirement. In fact, supplying 200 data entry clerks with CRTs rather than PCs might make perfect business sense. And this reasoning is precisely what is driving the development of network computers--;which Oracle Corporation is squarely behind.
Middleware is the software layer that enables a client application to communicate with a database server. For an Oracle database, you have two choices for middleware: SQL*Net version 1 or SQL*Net version 2.
SQL*Net v1 is a software component that enables a client machine to establish a remote connection with a server machine running an Oracle database. SQL*Net v1 uses a listener process called orasrv to wait for connection requests. When a connection request is validated, orasrv creates a process on the server that is dedicated to routing the remote Oracle user's SQL requests to the Oracle server. SQL*Net v1 has a separate version for each popular communication protocol such as TCP/IP, Named Pipes, IPX/SPX, and DECNet.
SQL*Net v2 serves the same purpose as SQL*Net v1--;to facilitate remote connections to an Oracle database. However, SQL*Net v2 provides some additional functionalities, which include the following:
TipAlthough many installations are still using SQL*Net v1, Oracle Corporation is slowly phasing out this product. You should plan on using SQL*Net v2 wherever possible.
The Oracle Workgroup Server is a repackaging of the Oracle7 Server. It is intended for sites that won't be needing support for distributed database operations or parallel query capability. Accordingly, it doesn't include the distributed or parallel options. The Oracle Workgroup Server includes the same Windows-based database administration tools that Personal Oracle uses. At present the Oracle Workgroup Server is available on the following platforms:
Choosing one of these operating systems involves considerations that have little to do with Oracle. One important factor is whether the operating system can support multiple CPUs. As an installation option, the Workgroup Server will install a default database that is configured to meet the processing requirements of most small workgroups. Of course, you can change the initialization parameters or restructure the default database to meet your specific needs. Once you've developed a database with Personal Oracle, you can upsize to the Oracle Workgroup Server using the following strategy:
To establish a connection to the Oracle Workgroup Server--;or any Oracle7 Server--;you need to specify three parameters:
Figure 29.3 demonstrates how SQL*Plus can establish a remote connection to an Oracle Workgroup Server. (Refer to Chapter 7, "Accessing Personal Oracle7 with SQL*Plus," for more information on using SQL*Plus.)
A connect string or database alias identifies the database to which a connection should be made. Chapter 30, "Distributed Database Features of Personal Oracle7 for Windows 95," contains more information on using a database alias.
The Oracle7 Server isn't restricted to UNIX. The ports of the Oracle7 Server include DEC VMS, HP MPE, and IBM MVS. However, the Oracle7 Server for UNIX--;regardless of version--;has the largest number of installed sites. Accordingly, the following discussion touches on some of the issues that you should be aware of when migrating from Personal Oracle to the Oracle7 Server for UNIX. Of course, for full details, refer to the Oracle Installation and Configuration Guide that accompanies the Oracle software for your system.
To log in to a UNIX account on the server and access an Oracle database, you'll need to define at least four environment variables in your UNIX account .cshrc file:
Here's an example of how these environment variables might be set for an actual installation:
ORACLE_BASE=/u2/oracle ORACLE_HOME=/u2/oracle/product/7.1.4 ORACLE_TERM=vt220 ORACLE_SID=prod
In addition, you need to add $ORACLE_HOME/bin to your PATH if you want to use any Oracle programs, such as SQL*Plus, to access the Oracle database.
To properly install Oracle, have the UNIX system administrator create a new user named oracle and a new group named dba. The oracle UNIX account owns the Oracle software and database files. The dba group specifies UNIX users who have the right to start up and shut down an Oracle instance.
Here are some guidelines on implementing SQL*Net v1 for the Oracle7 Server for UNIX. I am assuming that the communications protocol is TCP/IP.
You need to add the following line to the services file, which can usually be found in the /etc directory:
orasrv 1525/tcp
The SQL*Net v1 listener process is orasrv. Its role is to listen on the port specified in the services file for a request to establish an Oracle connection. When it receives this request, orasrv creates a dedicated server process. The line shown above reserves port 1525 for use by orasrv.
Oracle supplies a utility named tcpctl, which controls the orasrv process. From the oracle UNIX account, start the orasrv process by issuing the tcpctl start command.
myhost:<4> tcpctl start tcpctl: log file is /u4/oracle/product/7.1.4/tcp/log/orasrv.log tcpctl: SID mapping file is /var/opt/oracle/oratab tcpctl: server will be run under oracle tcpctl: logging mode is on orasrv: Release 1.2.7.8.1 - Production on Tue Jun 20 08:22:18 1995 Copyright Oracle Corporation 1979, 1994. All rights reserved. Starting server on port 1525. tcpctl: server has been started
You can also determine the status of the orasrv process by issuing the command tcpctl status.
myhost<2> tcpctl status tcputl: Status summary follows Server is running: Started : 19-JUN-95 11:13:40 Last connection : 19-JUN-95 11:34:33 Total connections : 10 Total rejections : 0 Active subprocesses : 0 ORACLE SIDs : prod Default SID : (null) Logging mode is ENABLED. DBA logins are DISABLED. OPS$ logins are ENABLED. OPS$ROOT logins are DISABLED. Orasrv is detached from the terminal. Break mode = OUT OF BAND. Debug level = 1 No timeout (on orasrv handshaking). Length of listen queue = 10 Orasrv logfile = /u4/oracle/product/7.1.4/tcp/log/orasrv.log Orasrv mapfile = /var/opt/oracle/oratab
Oracle uses a file named /etc/oratab for specifying which Oracle instances should start automatically whenever the system starts. Edit oratab with vi so that each line consists of the following:
instance-name:Oracle-home-directory:startup-option
instance-name is the Oracle instance to be started; Oracle-home-directory is the directory for instance-name's Oracle home directory; and startup-option is either Y or N.
For example, to automatically start up the prod instance, oratab would contain the following line:
prod:/u2/oracle/product/7.1.4:Y
An Oracle utility named dbstart reads the contents of oratab at system startup. Follow the directions in the Oracle Installation and Configuration Guide that describe how to edit the system startup file so that dbstart is executed when the operating system is started.
The distributed option is available only with the Oracle Enterprise Server--;not with Personal Oracle or the Oracle Workgroup Server. The mechanisms that are available with the distributed option include the following:
I explain the function of each of these capabilities in the following few sections.
You can think of a database link as a pointer to an Oracle connection--;it points to an Oracle database using a connect string. In addition, a database link identifies the Oracle user and password to be used for the database connection. For instance, as the database administrator for the Planetary Hospital Corporation, Al Hitchcock defines two database links.
SQL> create database link hobart_tasmania_facility 2 connect to ahitchcock identified by vertigo 3 using 'T:137.138.139.140:patient'; Database link created. SQL> create database link antananarivo_madagasc_facility 2 connect to ahitchcock identified by n_by_nw 3 using 'T:231.232.233.234:patient'; Database link created.
Once these database links have been defined, he can use them in distributed queries and transactions.
A distributed query is a join of two or more tables that reside in two or more different databases. Each table in a distributed query may reference each table by using a database link. For instance, you can construct a list of the patients in both the Chicago hospital and the Detroit hospital whose fevers are higher than 103 degrees Fahrenheit with the following code:
select Patient_ID, Body_Temp_Deg_F, Last_Name, First_Name from Patient@Chicago where Body_Temp_Deg_F > 103.0 union select Patient_ID, Body_Temp_Deg_F, Last_Name, First_Name from Patient@Detroit where Body_Temp_Deg_F > 103.0;
Chicago and Detroit are database links.
A distributed transaction is a series of database changes that affect two or more databases residing on two or more machines. You must set the Oracle initialization parameter distributed_transactions to 1 for an Oracle database to support distributed transactions. Once again using the example of the Planetary Hospital Corporation, imagine that a patient in the Kansas City, Kansas, hospital was being transferred to the Kansas City, Missouri, hospital. Recording the transfer requires updating the Patient table in each hospital's database to reflect the patient's discharge from the Kansas City, Kansas, hospital and the same patient's admission to the Kansas City, Missouri, hospital.
SQL> delete from Patient@Kansas_City_Kansas 2 where 3 Patient_ID = 9876; 1 row updated. SQL> insert into Patient@Kansas_City_Missouri 2 (Patient_ID, Last_Name, First_Name, ... 3 values 4 (9876, ... 1 row inserted. SQL> commit; Commit complete.
Oracle uses a mechanism called a two-phase commit to ensure that the transaction is successfully committed in each affected database. For example, if the inserted row is rejected from the Kansas City, Missouri, database for any reason, the entire transaction is rolled back; Patient ID 9876 will not be deleted from the Kansas City, Kansas, database. An Oracle background process named RECO (an abbreviation for Recoverer) is responsible for helping each Oracle database to recover from a failed distributed transaction.
As you will learn in Chapter 30, a snapshot is a mechanism for replicating a table from a remote database. The replicated table may be read-only--;it can't be modified. You can refresh the snapshot at a regular interval, or you can perform a manual refresh as needed. Snapshots have several advantages:
For example, a corporate database contains a status code table that is updated at a regular interval. The corporate DBA creates a snapshot of this status code table in the local database in each field office. Whenever she makes a change to the status code table, the DBA remembers to perform a manual refresh of each snapshot. (See Figure 29.4.)
A basic Oracle installation consists of a single database that is accessed by a single Oracle instance. However, with the parallel server, multiple instances can access the same database files simultaneously. The parallel server can provide improved performance by using additional CPU resources to process SQL requests. To address the possibility of two or more instances attempting to modify the same information simultaneously, Oracle uses up to 10 additional background processes, named LCK0 through LCK9, to lock a resource in use by an instance.
Release 7.1 of the Oracle7 Server introduced a new capability--;the parallel query option. The parallel query option is distinct from the Oracle parallel server; to use the parallel query option, you do not need to have the parallel server. The parallel query option enables an Oracle database to take advantage of a multiprocessor machine in the following areas:
An Oracle instance consists of the Oracle database files, the Oracle background processes, and the System Global Area (SGA). The difference between an Oracle instance and an Oracle database is that the instance is composed of the Oracle background processes, SGA, and database files, whereas the database consists solely of the database files.
When you use Database Manager to start Personal Oracle7, you are actually starting a Personal Oracle7 instance. A system can have more than one instance running simultaneously, and both the Oracle Workgroup Server and the Oracle7 Server for UNIX support the use of multiple instances. If you're supporting a production Oracle application, you should take advantage of this capability.
In addition to your production Oracle instance, you might want to create the following instances:
You can create separate aliases for the production, test, and model instances by adding the following lines to c:\windows\oracle.ini on a client PC:
SQLNET DBNAME prod=T:amarcord:PROD SQLNET DBNAME test=T:amarcord:TEST SQLNET DBNAME model=T:amarcord:MODEL
This chapter focuses on the following key ideas: