Oracle for NT

Tips for using Oracle effectively on Windows NT

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 forNT and Oracle7 Workgroup Server, see my article "Exploring Oracle7 Serverfor Windows NT," December 1996.)

Although the new Oracle7 products are easy to install, many users arediscovering that Oracle isn't as easy to use as SQL Server. A reasonably savvyuser with some Microsoft Access experience can export Access databases or createnew SQL Server databases without taking any formal SQL Server training (althoughI don't recommend doing so).

Don't expect the same level of install-and-go simplicity with Oracle, evenwith the Oracle7 Workgroup Server. Oracle has spent considerable time makingsure that Oracle is consistent across platforms. This consistency means that NTusers face the same hundreds of tuning parameters that database administrators(DBAs) face on other platforms. In other words, you probably can't be aweekend-warrior-style Oracle DBA. Good Oracle DBAs command a lot of respect--anda lot of money. If you need to manage an Oracle database, plan to get sometraining. One obvious avenue for training is Oracle Education (800-633-0575 orhttp://education.oracle.com), which offers an array of choices that includetraining by instructors, via satellite, on the Web, and through computer-basedtutorials.

I've been gathering reader questions and monitoring Oracle discussion listsand newsgroups, and I have assembled a list of commonly asked questions aboutusing Oracle with NT. Some deal with installation, some with tuning, some withgeneral product information. Consider this article a starting point fordiscussion about Oracle and NT, and feel free to submit more questions to medirectly or to the SQL Server section at http://www.winntmag.com/forums. Ifreaders demonstrate enough interest, perhaps Windows NT Magazinewill 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 installand administer all databases; grant NT Administrator privileges to this account.Create a local NT group called ORAadmin, for example, into which you add theORACLE account and the personal accounts of any NT users who will beadministering the NT databases. Use the ORAadmin group to assign NT filepermissions for all Oracle-related files.

An alternative to setting up a local Oracle DBAs group (which can be quiterestrictive and cumbersome for user account management) is to create a globalgroup called OraGlobalAdmin. This way, members can administer Oracle databasesacross trusted domains without needing to replicate the individual user accountsfrom domain to domain.

Q: Besides password and privilege management, what else should I worryabout when I create a new user?

Make sure to explicitly define a user's default Tablespace and temporaryTablespace (both are the SYSTEM Tablespace by default) to avoid filling up theSYSTEM Tablespace (in SQL Server, you don't want people storing data in theMaster database). Tablespace is Oracle's term to describe the set offiles that store Oracle data. Tablespaces can contain many entities, includingTables, indexes, and clusters. Clusters let you tell Oracle to store relatedTables close together.

Also consider assigning profiles to enforce resource limits--to preventrunaway queries, for example. To assign profiles, you need to include aRESOURCE_LIMIT=TRUE statement in the database instance's initializationparameter file. You can set resource limits for connect time, idle time, thenumber of sessions, and so forth.

Q: How can I keep track of logon attempts?

Oracle supports auditing of logon attempts, database actions, or specificdatabase objects (such as salary Tables). The first step in enabling auditing isto run Oracle's CATAUDIT.SQL script (found in ORANT/RDBMS73/ADMIN, with scoresof other useful scripts). Run the script as the user SYS, and set theAUDIT_TRAIL parameter in INIT.ORA. Oracle keeps configuration parameters inINIT.ORA. You'll need to create your parameters, probably by modifying a copy ofthe sample template file because that method is generally easier than creatingconfiguration parameters from scratch. As Screen 1 shows, the sample templatefile is INITORCL.ORA, which you can find in the ORANT/DATABASE directory.AUDIT_TRAIL=DB stores audit information in the database; AUDIT_TRAIL=OSstores it as an NT file. To enable logon auditing, execute the SQL command AUDITSESSION.

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); bothlet 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 ofyour operating system block size. You can make your database block size smaller­downto a minimum of 4096 bytes under NT 3.x or 4.x­but the performance cost issignificant. 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 blocksize. 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 codethat'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, byissuing 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 offerstransparent connection from client tools to the database or from one database toanother. SQL*Net works across multiple network protocols and operating systems,but it is implemented as a threaded NT service under NT. The easiest way toconFigure SQL*Net is to run the interactive SQL*Net Easy Configuration utilitythat is automatically installed with SQL*Net 2.3 or higher. Both SQL*Net EasyConfiguration and the more powerful Oracle Network Manager utility generate aset of configuration files that they distribute to network nodes. Each time youadd a new SQL*Net client to the network, you must load the appropriate set ofconfiguration files onto the client. This process can be tedious in large,dynamic networks where nodes are relocated. Consequently, Oracle has come upwith a distributed service (Oracle Names) for resolving Oracle service names andaliases. Oracle Names is part of SQL*Net 2.1 and above. If you expect lots ofusers to access your Oracle database, Oracle Names can help you: Think of it asproviding the same sort of service for databases as your address book does foryour email program.

Establishing connectivity to a specific database also requires adding(either manually or with the SQL*Net Easy Configuration utility) entries withsite-specific network information into the TNSNAMES.ORA and LISTENER.ORA files.TNSNAMES.ORA is a client configuration file on the server that lets a serverconnect to additional servers. TNS (Transparent Network Substrate) is Oracle'snetworking architecture. TNS provides a uniform application interface that letsnetwork applications access the underlying network protocols transparently. TNSconsists of three software components: TNS-based applications, OracleProtocol Adapters, and networking software such as TCP/IP. LISTENER.ORA isanother server-based configuration file that defines information, such as portnumber, protocols, and timeout settings, that the listener service uses toconnect 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 theSQLNET.EXPIRE_TIME=n parameter in your SQLNET.ORA file. This parameterinstructs SQL*Net to send a probe through the network to the client every nminutes; if the client doesn't respond, SQL*NET will kill the connection.

Q: Why am I having trouble connecting to Oracle with Open DatabaseConnectivity (ODBC)?

Ah, this problem is nasty. First, remember that ODBC drivers can be 16-bitand 32-bit and have both client- and server-side pieces­and that thedrivers are both what I call first-generation drivers and newer ones. You candownload a free first-generation 16-bit driver from Oracle's Web site. Thisdriver offers ODBC Level One compliance (e.g., with no scrolling cursor support)and is reportedly the same 16-bit driver that Visigenic Software wrote andMicrosoft has distributed. Microsoft, however, has now taken the ODBC Oracledriver initiative inhouse and distributes a newer 32-bit version in tools suchas Visual Studio 97. You can also purchase Oracle ODBC drivers from vendors suchas Visigenic, Intersolv, and OpenLink Software. Note that some third-partymultitier ODBC drivers let you avoid installing SQL*Net on the client by usingthe 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 displaydates consistently?

You also need to change the date on the client(s) in the ORACLE.INI, ORACLEsection 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 myapplication. I don't have any snapshots. What's going on?

SNAPSHOT TOO OLD is a confusing error message, because it makes you thinkabout Oracle's read-only copies of all or parts of a Table (or a join) that youtypically use in a remote site. In case you're not familiar with Oracle'sSNAPSHOTS, here's the basic SQL syntax:

CREATE SNAPSHOT

[STORAGE ]

[TableSPACE ]

[REFRESH[FASTCOMPLETEFORCE][START WITH NEXT ]

AS

The Oracle DBMS-SNAPSHOT package lets you update snapshots manually using aREFRESH procedure call in this format:

SNAPSHOT.REFRESH(snapshot_name,refresh_)

where the values F,C, and ? execute fast, complete, and default updates,respectively. You can issue SQL statements from the SQL worksheet (similar toSQL Server's Interactive SQL--isql), as Screen 2 shows; from SQL*Plus; or fromthe 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 tostore transaction data in case you need to roll back a transaction. Obviously,you must define rollback segments to be large enough to accommodate the largesttransactions that any transaction will generate, but the Oracle engine handlesrollback segments dynamically. A SNAPSHOT TOO OLD error means thatsomething went wrong, and the rollback segment information is no longeravailable (i.e., another transaction overwrote it, generally because thetransaction ran extremely long). Once Oracle receives a COMMIT, it copies thetransaction information to the redo log. (SQL Server, unlike Oracle, doesn'thave separate rollback segments and redo logs, only a single transaction log.)

To avoid the problem, you can

Q: How do I use the Parallel Query Option (PQO)?

Oracle's PQO is available for Oracle7 Universal Server for NT, but not forthe Workgroup Server for NT. You use PQO primarily for data warehousing andother decision-support applications (as opposed to OnLine Transaction Processing­OLTP­applications);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 Oracleknow which Tables and indexes you want to parallelize support for. Use syntaxsimilar 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_SERVERSparameter to 2 for each installed CPU. The focus of this parameter is inscanning 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 toreport information about Oracle's file I/O, library cache (shared SQL and PL/SQL areas),latch usage, rollback statistics, and much, much more. After settingOracle's TIMED_STATISTICS to TRUE, you can run the scripts as user SYS fromSQL*Plus or Enterprise Manager's SQL Worksheet or after connecting as INTERNALin Server Manager. The scripts create temporary objects in the SYSTEM Tablespacewith 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; youcan list them by selecting from V$FIXED_VIEW. Objects with names starting withV$ (e.g., the crucial V$SYSTAT object for system statistics) are Oracle'sdynamic views; DBAs use them widely to tune Oracle.

Q: I have a CD-ROM that hasLimited production written on it. What does that mean?

The CD-ROM is a late beta version of a product that eager Oracle customerspay for.

Q: What is Optimal Flexible Architecture (OFA)?

Cary Millsap and the Oracle Performance Group developed OFA in 1995 as ablueprint for administering and managing Oracle databases. OFA has become astandard in the UNIX world, as a way to help DBAs and consultants go to newsites 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 DBAworld read and become familiar with it. NT DBAs can adopt at least some of thenon-OS related recommendations--those that apply to Tablespaces and objectfragmentation, for example--and adapt some of the more OS-specific advicerelated to installation, account and file system management, and so on. You candownload 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 asthe basis for my database so that I won't need to hassle with setting parametersand running SQL DDL scripts. Others say that using the starter database iscrazy. Who's right?

Probably the only people who should opt for the starter database as a modelare absolute beginners or people who want a quick-and-dirty database for aworkgroup application. The starter database has several important limitations(any of which you can change, of course, by reconfiguring the database--changingparameters, locations, and so on). The most important limitation is that thedatabase is created in NOARCHIVELOG mode, which means that you can'tperform a restore if you need to. You can remedy that situation through ServerManager. You see its DOS icon in the SQL Enterprise group, or you can runSVRMGR23 directly. Log in to the database by choosing CONNECT, INTERNAL fromServer Manager, as Screen 3 shows. You then start the database by choosingSTARTUP, EXCLUSIVE, MOUNT and enter the command ALTERDATABASEARCHIVELOG. You'llneed to set the log_archive_start parameter to true in your database'sinstance initialization file, so that the archiver process, ARCH, can start.

Other potentially risky defaults in the starter database are the result ofits being a small test database. The SYSTEM Tablespace, for example is only 5MB,and the rollback, temporary, and user Tablespaces are only 2MB. Remember thatOracle has sized the starter database for development or workgroup use.Specifically, three size considerations make the starter database inadequate forlarger databases: the starter database's parameters for the shared global area(SGA­a shared memory structure that includes data block buffers thatfunction as a cache, a shared SQL area for storing parsed SQL statements, and adata dictionary cache) and its rollback and temporary segment spaces.

Note that the SGA is a cache, so you assume that it is resident in realmemory. If the SGA is larger than real memory, NT might be forced to page, andpaging to the same drive as the database can affect performance. So make sureyou know which NT settings you have for real and virtual memory when you assignOracle's SGA. The more real memory you can install, the better--your work willalways benefit from increasing your SGA shared pool and database block buffersizes.

Integrated Systems Consulting Group's Brian Guza made an excellentpresentation ("Administer the Oracle7 Server for Windows NT") at theEast Coast Oracle Conference (ECO 97) in April, and you can download the paperfrom http://www.iscg.com/techgood.htm. The six-page .PDF file describes anine-step procedure for setting up an Oracle database from scratch instead ofusing the starter database and offers some NT-specific modifications to OFA.ARIS's Mike Curtis, a systems engineer with a decade of Oracle experience, madeanother presentation, "Oracle Architecture and NT," at the recentInternational Oracle User Group­America's conference in Dallas. You candownload 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) maintainsmy favorite site (http://kbs.net). To join the list, send a SUBSCRIBE ORACLE-Lyourname message to [email protected]. If you or yourorganization has purchased Oracle support (call 800-392-2999 for informationabout Gold, Silver, Bronze, Basic, or Standard support levels), you can accessOracle's premier support site on CompuServe (GO ORASUPP). One useful download inORASUPP's MISC section is a utility (ORASAFE.EXE) that automatically shuts downyour Oracle database when you reboot the server. (Otherwise, Oracle issues animplicit SHUTDOWN ABORT, an inelegant solution resulting in automatic instancerecovery when you restart the database.) You can also find several Oraclenewsgroups at comp.databases.oracle (.server, .misc, .market. and .toolssections), 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. Anotherof my favorite Oracle Web sites is the Underground Oracle FAQ(http://www.orafaq.net), which contains much more than justfrequently asked questions.

Q: How serious is Oracle about its NT platform? The company won't get theprofit margins in NT that it gets in UNIX sales.

Oracle is very serious about NT. The company has been shipping Oracle7 onNT since December 1993 (the Workgroup Server for NT has been available sinceSeptember 1994). NT is now an Oracle Tier One platform, putting it on a par withSolaris and other key UNIX platforms for new product releases (the number of SunMicrosystems' Solaris and NT beta sites for Oracle8 were reportedly almostequal). Oracle also has hundreds of programmers and support staff assigned tothe NT Technology Center, which is part of Oracle's Worldwide Alliances andTechnologies unit; an NT sales force about 150 strong under the leadership ofShari Simon; and an NT center of excellence group in the Oracle ConsultingServices 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 forWindows NT"): The Parallel Query Option isn't available for the WorkgroupServer, which means Workgroup Server doesn't support bitmapped indexes, either.Also, Oracle Parallel Server for NT is available now for certain hardwareclustered servers including Data General's AViion. However, Oracle Fail Safe forNT won't be available until Wolfpack ships.

One Oracle Q&A column hardly does justice to the issues surroundingsetting up and maintaining an Oracle database on NT. The bottom line, especiallyfor developers who are used to working with Access or SQL Server databases, isto not take Oracle DBA tasks lightly. Richard Headley, vice president ofPlatinum Technology, a company that provides a variety of DBA utilities, sayshe's seen too many one-off databases that novices have designed withoutpaying enough attention to sizing and capacity planning and scheduled backupsand reorgs, not to mention basic database design. "Denormalization is onething," Headley says. "Sheer ignorance is another." In otherwords, if you're going to use Oracle on NT, plan to spend some time learningyour craft.