With its 53 percent share of the market, Oracle is the worldwide leader in relational database management systems (RDBMSs). Microsoft's SQL Server is more widely used in the Windows NT market--not surprising, because SQL Server is part of BackOffice and Microsoft gives away five-license developer versions of SQL Server with the enterprise editions of Visual Studio 97 and Visual Basic 5.0. But Oracle7 for Windows NT is gaining fast. Oracle's Web site devotes a subsection (http://www.oracle. com/NT) to NT, where, among other things, you can order Oracle on an NT CD-ROM, as 50,000 others have before you. The CD-ROM includes evaluation versions of a variety of Oracle NT products, including the Oracle7 Workgroup Server. (For a comparison of Oracle7 Server for NT and Oracle7 Workgroup Server, see my article "Exploring Oracle7 Server for Windows NT," December 1996.)
Although the new Oracle7 products are easy to install, many users are discovering that Oracle isn't as easy to use as SQL Server. A reasonably savvy user with some Microsoft Access experience can export Access databases or create new SQL Server databases without taking any formal SQL Server training (although I don't recommend doing so).
Don't expect the same level of install-and-go simplicity with Oracle, even with the Oracle7 Workgroup Server. Oracle has spent considerable time making sure that Oracle is consistent across platforms. This consistency means that NT users face the same hundreds of tuning parameters that database administrators (DBAs) face on other platforms. In other words, you probably can't be a weekend-warrior-style Oracle DBA. Good Oracle DBAs command a lot of respect--and a lot of money. If you need to manage an Oracle database, plan to get some training. One obvious avenue for training is Oracle Education (800-633-0575 or http://education.oracle.com), which offers an array of choices that include training by instructors, via satellite, on the Web, and through computer-based tutorials.
I've been gathering reader questions and monitoring Oracle discussion lists and newsgroups, and I have assembled a list of commonly asked questions about using Oracle with NT. Some deal with installation, some with tuning, some with general product information. Consider this article a starting point for discussion about Oracle and NT, and feel free to submit more questions to me directly or to the SQL Server section at http://www.winntmag.com/forums. If readers demonstrate enough interest, perhaps Windows NT Magazine will set up an Oracle forum on its Web site.
Q: How should I organize my NT accounts to work with Oracle?
A useful approach is to create an NT user account called ORACLE to install and administer all databases; grant NT Administrator privileges to this account. Create a local NT group called ORAadmin, for example, into which you add the ORACLE account and the personal accounts of any NT users who will be administering the NT databases. Use the ORAadmin group to assign NT file permissions for all Oracle-related files.
An alternative to setting up a local Oracle DBAs group (which can be quite restrictive and cumbersome for user account management) is to create a global group called OraGlobalAdmin. This way, members can administer Oracle databases across trusted domains without needing to replicate the individual user accounts from domain to domain.
Q: Besides password and privilege management, what else should I worry about when I create a new user?
Make sure to explicitly define a user's default Tablespace and temporary Tablespace (both are the SYSTEM Tablespace by default) to avoid filling up the SYSTEM Tablespace (in SQL Server, you don't want people storing data in the Master database). Tablespace is Oracle's term to describe the set of files that store Oracle data. Tablespaces can contain many entities, including Tables, indexes, and clusters. Clusters let you tell Oracle to store related Tables close together.
Also consider assigning profiles to enforce resource limits--to prevent runaway queries, for example. To assign profiles, you need to include a RESOURCE_LIMIT=TRUE statement in the database instance's initialization parameter file. You can set resource limits for connect time, idle time, the number of sessions, and so forth.
Q: How can I keep track of logon attempts?
Oracle supports auditing of logon attempts, database actions, or specific database objects (such as salary Tables). The first step in enabling auditing is to run Oracle's CATAUDIT.SQL script (found in ORANT/RDBMS73/ADMIN, with scores of other useful scripts). Run the script as the user SYS, and set the AUDIT_TRAIL parameter in INIT.ORA. Oracle keeps configuration parameters in INIT.ORA. You'll need to create your parameters, probably by modifying a copy of the sample template file because that method is generally easier than creating configuration parameters from scratch. As Screen 1 shows, the sample template file is INITORCL.ORA, which you can find in the ORANT/DATABASE directory. AUDIT_TRAIL=DB stores audit information in the database; AUDIT_TRAIL=OS stores it as an NT file. To enable logon auditing, execute the SQL command AUDIT SESSION.
Q: How do I load SQL data into my Oracle database?
Use SQL*Loader. It is similar to SQL Server's bulk copy program (bcp); both let you load data from fixed- or variable-length files into Oracle Tables.
Q: What database block size should I use?
Oracle recommends that your database block size match or be multiples of your operating system block size. You can make your database block size smallerdown to a minimum of 4096 bytes under NT 3.x or 4.xbut the performance cost is significant. Your choice will depend on the type of application you're running. If you have lots of small transactions, as you do with OLTP, use a small block size. With fewer but larger transactions, such as in a decision support system (DSS) application, use a large block size.
Q: What are packages?
A package is a set of related procedures, functions, and other PL/SQL code that's stored in an Oracle database and that client applications can invoke. You (as SYS) can find out which packages are available for a given database, by issuing the SQL statement
SELECT object_name FROM sys.dba_objects WHERE object_type = 'PACKAGE'
To get a list of almost 1500 objects (including Tables, views, indexes, packages, procedures, triggers, and synonyms), issue the command
SELECT * FROM sys.dba_objects
Q: What is SQL*Net?
SQL*NET is Oracle's client/server middleware product that offers transparent connection from client tools to the database or from one database to another. SQL*Net works across multiple network protocols and operating systems, but it is implemented as a threaded NT service under NT. The easiest way to conFigure SQL*Net is to run the interactive SQL*Net Easy Configuration utility that is automatically installed with SQL*Net 2.3 or higher. Both SQL*Net Easy Configuration and the more powerful Oracle Network Manager utility generate a set of configuration files that they distribute to network nodes. Each time you add a new SQL*Net client to the network, you must load the appropriate set of configuration files onto the client. This process can be tedious in large, dynamic networks where nodes are relocated. Consequently, Oracle has come up with a distributed service (Oracle Names) for resolving Oracle service names and aliases. Oracle Names is part of SQL*Net 2.1 and above. If you expect lots of users to access your Oracle database, Oracle Names can help you: Think of it as providing the same sort of service for databases as your address book does for your email program.
Establishing connectivity to a specific database also requires adding (either manually or with the SQL*Net Easy Configuration utility) entries with site-specific network information into the TNSNAMES.ORA and LISTENER.ORA files. TNSNAMES.ORA is a client configuration file on the server that lets a server connect to additional servers. TNS (Transparent Network Substrate) is Oracle's networking architecture. TNS provides a uniform application interface that lets network applications access the underlying network protocols transparently. TNS consists of three software components: TNS-based applications, Oracle Protocol Adapters, and networking software such as TCP/IP. LISTENER.ORA is another server-based configuration file that defines information, such as port number, protocols, and timeout settings, that the listener service uses to connect to a server.
Q: What is a quick way to change my login to SYS in SQL*Plus?
The undocumented command is
ALTER SESSION SET CURRENT_SCHEMA = SYS
Q: How can I monitor and kill dead connections?
You can detect and kill dead connections with SQL*NET if you specify the SQLNET.EXPIRE_TIME=n parameter in your SQLNET.ORA file. This parameter instructs SQL*Net to send a probe through the network to the client every n minutes; if the client doesn't respond, SQL*NET will kill the connection.
Q: Why am I having trouble connecting to Oracle with Open Database Connectivity (ODBC)?
Ah, this problem is nasty. First, remember that ODBC drivers can be 16-bit and 32-bit and have both client- and server-side piecesand that the drivers are both what I call first-generation drivers and newer ones. You can download a free first-generation 16-bit driver from Oracle's Web site. This driver offers ODBC Level One compliance (e.g., with no scrolling cursor support) and is reportedly the same 16-bit driver that Visigenic Software wrote and Microsoft has distributed. Microsoft, however, has now taken the ODBC Oracle driver initiative inhouse and distributes a newer 32-bit version in tools such as Visual Studio 97. You can also purchase Oracle ODBC drivers from vendors such as Visigenic, Intersolv, and OpenLink Software. Note that some third-party multitier ODBC drivers let you avoid installing SQL*Net on the client by using the underlying network protocol (e.g. TCP/IP).
Q: I changed NLS_DATE_FORMAT from DD-MON-YY and to DD-MON-YYYY in INIT.ORA, but some users are still seeing the DD-MON-YY format. Why doesn't Oracle display dates consistently?
You also need to change the date on the client(s) in the ORACLE.INI, ORACLE section of the client's WIN.INI or Registry settings, as appropriate.
Q: I keep getting SNAPSHOT TOO OLD error messages when I try to run my application. I don't have any snapshots. What's going on?
SNAPSHOT TOO OLD is a confusing error message, because it makes you think about Oracle's read-only copies of all or parts of a Table (or a join) that you typically use in a remote site. In case you're not familiar with Oracle's SNAPSHOTS, here's the basic SQL syntax:
CREATE SNAPSHOT <snapshot_name>
\[STORAGE <storage parameters>\]
\[TableSPACE <Tablespace_name>\]
\[REFRESH\[FAST\COMPLETE\FORCE\]\[START WITH <start date> NEXT <next date>\]
AS
<your_query>
The Oracle DBMS-SNAPSHOT package lets you update snapshots manually using a REFRESH procedure call in this format:
SNAPSHOT.REFRESH(snapshot_name,refresh_<type>)
where the values F,C, and ? execute fast, complete, and default updates, respectively. You can issue SQL statements from the SQL worksheet (similar to SQL Server's Interactive SQL--isql), as Screen 2 shows; from SQL*Plus; or from the command-line Server Manager.
However, the SNAPSHOT error message is related to rollback segments, a physical data structure within a Tablespace. Oracle uses the structure to store transaction data in case you need to roll back a transaction. Obviously, you must define rollback segments to be large enough to accommodate the largest transactions that any transaction will generate, but the Oracle engine handles rollback segments dynamically. A SNAPSHOT TOO OLD error means that something went wrong, and the rollback segment information is no longer available (i.e., another transaction overwrote it, generally because the transaction ran extremely long). Once Oracle receives a COMMIT, it copies the transaction information to the redo log. (SQL Server, unlike Oracle, doesn't have separate rollback segments and redo logs, only a single transaction log.)
To avoid the problem, you can
* increase the size of your rollback segments and rollback extents
* specifically assign a large rollback segment to a user or session you know will generate a long-running transaction (e.g. SET TRANSACTION USE ROLLBACK SEGMENT reallybigone)
* try to break up long transactions into shorter ones
* take advantage of multiple CPUs or Oracle's parallel query option
* use the truncate command, which doesn't use rollback segments, for large-scale deletions
Q: How do I use the Parallel Query Option (PQO)?
Oracle's PQO is available for Oracle7 Universal Server for NT, but not for the Workgroup Server for NT. You use PQO primarily for data warehousing and other decision-support applications (as opposed to OnLine Transaction ProcessingOLTPapplications); PQO lets you parallelize sorts, Table scans, and loads; use bitmapped indexes; and so on. Once you've altered INIT.ORA to support PQO, you need to let Oracle know which Tables and indexes you want to parallelize support for. Use syntax similar to this:
ALTER Table Tablename PARALLEL (DEGREE
You can monitor parallel query execution by issuing the command
SELECT * FROM sys.v_$pq_systat
One useful rule of thumb is to set INIT.ORA's PARALLEL_MAX_SERVERS parameter to 2 for each installed CPU. The focus of this parameter is in scanning Tables that use one server (thread) for n pieces of the Table. If the Table spans more than one disk, adding server (threads) will speed Table scans. A disk-limited system won't benefit from adding parallel server scans.
Q: Does Oracle have anything like SQL Server's STATISTICS IO?
Yes, you can run Oracle's UTLBSTAT.SQL and UTLESTAT.SQL scripts to report information about Oracle's file I/O, library cache (shared SQL and PL/SQL areas), latch usage, rollback statistics, and much, much more. After setting Oracle's TIMED_STATISTICS to TRUE, you can run the scripts as user SYS from SQL*Plus or Enterprise Manager's SQL Worksheet or after connecting as INTERNAL in Server Manager. The scripts create temporary objects in the SYSTEM Tablespace with names like STATS$BEGIN_FILE and STATS$END_STATS. So-called X$ Tables, or Tables with a dollar sign in them, are Oracle-maintained pseudoTables; you can list them by selecting from V$FIXED_VIEW. Objects with names starting with V$ (e.g., the crucial V$SYSTAT object for system statistics) are Oracle's dynamic views; DBAs use them widely to tune Oracle.
Q: I have a CD-ROM that has Limited production written on it. What does that mean?
The CD-ROM is a late beta version of a product that eager Oracle customers pay for.
Q: What is Optimal Flexible Architecture (OFA)?
Cary Millsap and the Oracle Performance Group developed OFA in 1995 as a blueprint for administering and managing Oracle databases. OFA has become a standard in the UNIX world, as a way to help DBAs and consultants go to new sites that have adopted the OFA approach to filenames, locations, and so on. Although parts of the OFA document are UNIX specific and don't readily map to NT (/etc directories, for example), I recommend that anyone new to the Oracle DBA world read and become familiar with it. NT DBAs can adopt at least some of the non-OS related recommendations--those that apply to Tablespaces and object fragmentation, for example--and adapt some of the more OS-specific advice related to installation, account and file system management, and so on. You can download the OFA document as a .PDF file from the ODP site at http://tiburon.us.oracle.com.
Q: Some sources recommend installing the starter database and using it as the basis for my database so that I won't need to hassle with setting parameters and running SQL DDL scripts. Others say that using the starter database is crazy. Who's right?
Probably the only people who should opt for the starter database as a model are absolute beginners or people who want a quick-and-dirty database for a workgroup application. The starter database has several important limitations (any of which you can change, of course, by reconfiguring the database--changing parameters, locations, and so on). The most important limitation is that the database is created in NOARCHIVELOG mode, which means that you can't perform a restore if you need to. You can remedy that situation through Server Manager. You see its DOS icon in the SQL Enterprise group, or you can run SVRMGR23 directly. Log in to the database by choosing CONNECT, INTERNAL from Server Manager, as Screen 3 shows. You then start the database by choosing STARTUP, EXCLUSIVE, MOUNT and enter the command ALTERDATABASEARCHIVELOG. You'll need to set the log_archive_start parameter to true in your database's instance initialization file, so that the archiver process, ARCH, can start.
Other potentially risky defaults in the starter database are the result of its being a small test database. The SYSTEM Tablespace, for example is only 5MB, and the rollback, temporary, and user Tablespaces are only 2MB. Remember that Oracle has sized the starter database for development or workgroup use. Specifically, three size considerations make the starter database inadequate for larger databases: the starter database's parameters for the shared global area (SGAa shared memory structure that includes data block buffers that function as a cache, a shared SQL area for storing parsed SQL statements, and a data dictionary cache) and its rollback and temporary segment spaces.
Note that the SGA is a cache, so you assume that it is resident in real memory. If the SGA is larger than real memory, NT might be forced to page, and paging to the same drive as the database can affect performance. So make sure you know which NT settings you have for real and virtual memory when you assign Oracle's SGA. The more real memory you can install, the better--your work will always benefit from increasing your SGA shared pool and database block buffer sizes.
Integrated Systems Consulting Group's Brian Guza made an excellent presentation ("Administer the Oracle7 Server for Windows NT") at the East Coast Oracle Conference (ECO 97) in April, and you can download the paper from http://www.iscg.com/techgood.htm. The six-page .PDF file describes a nine-step procedure for setting up an Oracle database from scratch instead of using the starter database and offers some NT-specific modifications to OFA. ARIS's Mike Curtis, a systems engineer with a decade of Oracle experience, made another presentation, "Oracle Architecture and NT," at the recent International Oracle User GroupAmerica's conference in Dallas. You can download the presentation from http://www.aris.com or http://www.ioug.org.
Q: Are there any Oracle discussion lists?
Yes, but none that are NT specific. Kapur Business Systems (KBS) maintains my favorite site (http://kbs.net). To join the list, send a SUBSCRIBE ORACLE-L yourname message to [email protected]. If you or your organization has purchased Oracle support (call 800-392-2999 for information about Gold, Silver, Bronze, Basic, or Standard support levels), you can access Oracle's premier support site on CompuServe (GO ORASUPP). One useful download in ORASUPP's MISC section is a utility (ORASAFE.EXE) that automatically shuts down your Oracle database when you reboot the server. (Otherwise, Oracle issues an implicit SHUTDOWN ABORT, an inelegant solution resulting in automatic instance recovery when you restart the database.) You can also find several Oracle newsgroups at comp.databases.oracle (.server, .misc, .market. and .tools sections), and you can find excellent resources at Oracle's Developer Programme (ODP) site. Membership in the ODP is well worth its $395 per year cost. Another of my favorite Oracle Web sites is the Underground Oracle FAQ (http://www.orafaq.net), which contains much more than just frequently asked questions.
Q: How serious is Oracle about its NT platform? The company won't get the profit margins in NT that it gets in UNIX sales.
Oracle is very serious about NT. The company has been shipping Oracle7 on NT since December 1993 (the Workgroup Server for NT has been available since September 1994). NT is now an Oracle Tier One platform, putting it on a par with Solaris and other key UNIX platforms for new product releases (the number of Sun Microsystems' Solaris and NT beta sites for Oracle8 were reportedly almost equal). Oracle also has hundreds of programmers and support staff assigned to the NT Technology Center, which is part of Oracle's Worldwide Alliances and Technologies unit; an NT sales force about 150 strong under the leadership of Shari Simon; and an NT center of excellence group in the Oracle Consulting Services Division. And don't forget the NT section on the Oracle Web site.
Wrapping it up
One correction to my December article ("Exploring Oracle7 Server for
Windows NT"): The Parallel Query Option isn't available for the Workgroup
Server, which means Workgroup Server doesn't support bitmapped indexes, either.
Also, Oracle Parallel Server for NT is available now for certain hardware
clustered servers including Data General's AViion. However, Oracle Fail Safe for
NT won't be available until Wolfpack ships.
One Oracle Q&A column hardly does justice to the issues surrounding setting up and maintaining an Oracle database on NT. The bottom line, especially for developers who are used to working with Access or SQL Server databases, is to not take Oracle DBA tasks lightly. Richard Headley, vice president of Platinum Technology, a company that provides a variety of DBA utilities, says he's seen too many one-off databases that novices have designed without paying enough attention to sizing and capacity planning and scheduled backups and reorgs, not to mention basic database design. "Denormalization is one thing," Headley says. "Sheer ignorance is another." In other words, if you're going to use Oracle on NT, plan to spend some time learning your craft.
