The Oracle DBA's Guide to NT

Guy Harrison

With Windows NT rapidly gaining market and mind share, are you prepared to keep up?

When Oracle first entered the computing mainstream in the mid-1980s, the platform of choice for Oracle implementations was Digital's VAX minicomputer running the VMS operating system. Core Oracle software was written first for VMS, then ported (sometimes leisurely) to other operating systems such as DG-OS, MVS, and Unix. If you were an Oracle DBA or developer in 1987, chances are that you worked with VMS.
      Toward the end of the '80s, the Oracle computing landscape changed dramatically. The open systems/Unix juggernaut conquered the midrange environment, and most Oracle sites adopted Unix. By the mid-'90s, the vast majority of Oracle installations were Unix-based, and many Oracle professionals had made the not-so-easy move from VMS.
      Now, as we move toward the year 2000, it's clear that a similar--although less dramatic--transformation is again underway. Unix systems continue to hold the high ground and constitute the majority of mission-critical and VLDB implementations. However, Windows NT deployments are increasing rapidly, and NT is capturing market share as well as mind share in the low and medium segments of the Oracle midrange market. Although Unix will continue to be a prominent feature in the Oracle landscape for many years to come, Oracle professionals who ignore the NT operating system do so at their own peril.
      In this article, I'll explain how Windows NT compares to Unix as an Oracle server platform and review some common Oracle administration tasks under Windows NT. A basic understanding of Oracle administration in the Unix environment is assumed.

ORACLE ARCHITECTURE ON NT

Oracle has always placed great emphasis on portability: providing uniform features and facilities across the greatest possible range of operating environments. In general, Oracle has successfully achieved the portability objective. Oracle is available on almost all significant operating environments--from Intel PCs running Windows 95 to midrange systems running Unix or proprietary operating systems to MVS-based mainframe systems. On all these systems, Oracle implements a common architecture that includes the following components:

An area of memory available to all Oracle sessions, known as the system global area (SGA). This area of memory includes recently accessed data blocks (the buffer cache), SQL and PL/SQL objects (the library cache), and transaction information (the redo log buffer). The SGA may also contain session information.
Several tasks that perform dedicated database activities, including the database writer (DBWR), redo log writer (LGWR), system monitor (SMON), process monitor (PMON), and log archiver (ARCH). Other tasks may be configured if required to support Oracle options such as parallel query, distributed database, or multithreaded servers. We will refer to these tasks as background tasks (although they are also often referred to as background processes).
Oracle data files that contain the tables, indexes, and other segments that form the Oracle instance
Redo logs, which record critical transaction information required for roll-forward in the event of instance failure
A separate task created to perform database operations on behalf of each Oracle session, which I'll refer to as a dedicated server. If the multithreaded server option is implemented, many sessions can be supported by a smaller number of shared servers.
A SQL*Net listener task that establishes connections from external systems.

The architecture of Oracle in an NT environment is somewhat different (see Figure 2). Oracle takes advantage of NT's strong support for threads. In almost all operating systems, a process is forbidden to access memory belonging to another process. Threads belonging to the same process, however, share a common memory address space and are therefore able to share memory easily.

      On NT, the Oracle instance is implemented as a single NT process. This process includes threads that implement each of the tasks required for the instance. Therefore, there is a thread for each of the background and server tasks plus a two-thread overhead. Because each thread shares the same memory space, there is no need to implement the SGA in shared memory; if you implement the SGA within the instance's process memory, it is available to all threads within the process.
      Oracle's architecture on NT suits the NT process/thread model. However, the single process model restricts the total memory available to threads belonging to the Oracle instance. Prior to NT version 3.51, the memory limit for a single process was only 256MB--a severe limitation for even moderately sized Oracle instances. In NT version 4.0, a process may address up to 4GB of virtual memory. However, 2GB of this memory is reserved for system overhead, allowing only 2GB for Oracle.
      At first glance, 2GB might sound like a generous memory allocation for an Oracle instance. But remember that this area of memory must be sufficient to store the SGA and data segments for all Oracle sessions. Furthermore, the 2GB is a virtual memory limit; it's possible that 2GB of virtual memory will be expended when physical memory usage is actually far lower.
      There are currently two options for extending the 2GB limit: In Windows NT Server Enterprise Edition, you can reduce the system component of process memory to 1GB, allowing up to 3GB of memory for the Oracle instance. On Alpha NT platforms, the very large memory (VLM) option allows up to 8GB of memory to be made available to the Oracle instance.
      Oracle's multithreaded server option allows multiple client processes to share a smaller number of Oracle server processes. This approach can reduce memory requirements and process overhead. Multithreaded server is also available on NT, but only from Oracle8 onward. Using multithreaded server under Windows NT can reduce the number of threads in the Oracle process as well as overall memory requirements. You may also be able to use the Oracle8 connection pooling and concentrating facilities to further reduce thread and memory overhead.
      With the release of Windows NT version 5.0 (expected in the fourth quarter of 1998), the process memory limit will increase to 32GB, which should be sufficient for most Oracle installations.

STARTUP AND SHUTDOWN

In the Unix environment, Oracle instances are usually started from the Oracle Server Manager or via the dbstart script distributed with Oracle software. During Oracle startup, the shared memory segments for the SGA are allocated and the background processes created. The background processes immediately "daemonize" by disassociating themselves from the login session and terminal so that they can continue to run after the login session disconnects.
Under Windows NT, a process running independently of a login session must be configured as a service. Consequently, each Oracle instance is associated with one or more NT services. The most significant services include:

OracleServicesid. This service must be defined for each Oracle instance on the NT server. Sid represents the instance identifier; in other words, OracleServiceABC1 represents the instance ABC1. Starting this service creates the Oracle process.
OracleStartsid. Starting this service performs an automatic startup for the instance in question--equivalent to issuing a startup command from Server Manager.
OracleTNSListenerNN. This service implements the SQL*Net listener. NN refers to the version of SQL*Net, typically 80 for Oracle8 and 23 for Oracle7.3.

To start an Oracle instance automatically, all you have to do is set the startup property to automatic for the respective services in the services applet or the Oracle Instance Manager applet. To start up an Oracle instance manually, either use the start button on the services applet or issue the net start command to start the appropriate services. For example, the following commands start the GDB1 instance and the SQL*Net listener service:

net start OracleServiceGDB1
net start OracleStartGDB1
net start OracleTNSListener80

      You can also use the Oracle Instance Manager (oradim73.exe or oradim80.exe) in its command-line mode to start or stop Oracle instances and services.
      Terminating the OracleServicesid service will terminate the Oracle instance. However, simply terminating this service aborts the instance without performing any of the normal shutdown procedures. Thus, shutting down an NT server will crash the Oracle instance--a process roughly equivalent to performing a "shutdown abort." Although Oracle will almost always recover from such an abrupt shutdown, most DBAs prefer a cleaner one.
      Oracle8 introduces a mechanism of performing such a clean shutdown. If you set the value of the registry parameter ora_shutdown or ora_sid_shutdown to True, Oracle will start a task to perform a shutdown immediately whenever the OracleServicesid service is stopped--including when NT is shut down. This facility is not available prior to Oracle8, and there is no documented facility for closing an Oracle7 database cleanly during system shutdown. However, you can obtain a freeware tool to perform a safe shutdown at ourworld.compuserve.com/homepages/orasupp/homefram.htm.

INSTALLATION

During the installation of Oracle software, the installer will offer to create a "starter" database. This database is sufficient for familiarization or small-scale development but is not suitable for any nontrivial production purposes. For example, in a starter database you can't specify the instance identifier, create second or subsequent instances, specify the location of data files or redo logs (all are created on the one physical disk and inside the Oracle software distribution), or specify a database block size. Consequently, you'll usually want to create customized production databases.
In the Unix environment, you create a new instance by making entries in the oratab and listener.ora files, creating an initsid.ora file and running create database and create tablespace statements from the Server Manager program.
The critical difference in an NT installation is the need to create the NT services associated with the new instance. The Oracle database assistant, released with Oracle8, creates the necessary services and generates the configuration files and the database create script. Using the database assistant, you can specify key init.ora parameters as well as tablespace, data file, and redo log definitions from within a GUI environment. Figure 3 shows the Oracle Database Assistant tablespace definition screen. Other screens allow you to specify server configuration parameters such as block size, SGA component sizes, redo log locations, archive log destinations, and key configuration settings.

When you've created your basic database using the database assistant, you can use Server Manager or other tools to create additional tablespaces or data files and edit the initsid.ora file to add or change configuration parameters. In Oracle7, however, the process of adding an additional instance is somewhat more complex. In this case, the Oracle Instance Manager can be used in command-line mode to add and configure the NT services required to support the new database instance. For example, the following command creates a service for an Oracle 7.3 instance named GDB3:

Oradim73 -new -sid gdb3 -intpwd
bond007 -startmode auto -pfile
D:\orant\database\initgdb3.ora

You can then use the Instance Manager to start the service and the instance with this command:

Oradim73 -startup -sid gdb3
-starttype srvc,inst -usrpwd bond007 -
pfile D:\orant\database\initgdb3.ora

Finally, you would select the new instance by setting your Oracle_ SID and would use Server Manager to create the database, run the catalog scripts, and create tablespaces. In the following example, cregdb3.sql contains the SQL commands required to create the database (basically the same commands you would use in a Unix environment):

Set Oracle_sid =GDB3
Svrmgr23 @cregdb3.sql

MULTIPLE SOFTWARE VERSIONS

In Unix, we can maintain multiple versions of Oracle in distinct directory trees and switch between them by changing the value of the environment variable oracle_home. In the NT environment, only one oracle_home is supported, and it is defined by the value of the registry value hkey_local_machine\software\oracle\ oracle_home. However, you can have multiple versions of Oracle on the one machine because critical executables are suffixed with their major version number. For example, the Oracle server program is oracle73.exe for version 7.3 and oracle8.exe for Oracle8. This approach does have limitations: Although you can run Oracle7.3 and Oracle8 concurrently, you cannot run two minor versions, such as Oracle8.0.3 and 8.0.4, on the same machine.
NT supports multiple instances, and like Unix, each database is associated with a particular version of Oracle software. In Unix, this association is defined in the oratab file. In NT, the NT service that defines an instance is associated with a particular software version--determined by the version of the Oracle Instance Manager that was first used to create the service.
Oracle_home is not the only Oracle configuration setting to be found in the NT registry. Figure 4 shows some of the registry settings for a typical Oracle instance as seen by the regedit program.

PERFORMING BACKUPS

As with Unix and other environments, you can back up an Oracle instance in NT using either online backups with archive logging, cold (offline) backups, or database exports. The principles for performing offline backups and exports are the same under NT as under Unix. However, performing unattended online backups to tape may be more complicated under NT.
      The Backup Manager applet included in the Oracle for NT distribution can perform online or offline backups and the companion Recovery Manager can automate the recovery process. However, the Backup Manager runs only in an interactive mode and can only back up to 4mm tape or disk. It is therefore most suitable for ad hoc backups.
      The NT backup tool can back up to tape and can be used to perform offline backups, although you will be responsible for placing the tablespaces in backup mode and ensuring that the correct files are copied. Unfortunately, the NT backup tool will not process open files and therefore cannot be used for online backups. To perform hot backups using the NT backup tool, you have to copy data files to a staging area using the Ocopy80 tool included in the Oracle distribution.
      The Backup Manager included with the Oracle Enterprise Manager requires special driver software to communicate with tape drives but has the most extensive functionality and can run as a background task by using the Enterprise Manager job management facility.
      Some backup vendors--such as Cheyenne and Legato--provide complete solutions for backing up Oracle under NT. For mission- or enterprise-critical production systems, such solutions can be very attractive.

SCHEDULING BACKUPS

In Unix, job scheduling is usually achieved using the cron facility, which lets you schedule jobs for regular execution, or the at command, which lets you schedule jobs for once-off execution.
The Windows NT at command can submit jobs for both regular and once-off processing. The basic syntax for the at command is AT time [/every:date] [/next:date] command. Time is the time of execution specified using 24-hour notation and date is one or more days of the week (M, T, W, TH, F, S, SU) or month (1 to 31). For example, the following command runs an archive script every day at 3 p.m.:

at 15:00 /every:m,t,w,th,f,s,su cmd /c 
c:\bin\archive.bat >c:\logs\archive.log

You must start the Windows NT schedule service in order to use the at command.

PERFORMANCE MONITORING

Monitoring performance, diagnosing performance bottlenecks, and performance tuning are ongoing tasks for most DBAs. While the methodology for performing these tasks in NT is similar to Unix, the tools may initially be unfamiliar.
Under Unix, the two most commonly used tools for monitoring performance at the operating system level are sar and top. Sar collects and reports on a variety of system performance metrics while top reports on processes that consume the most CPU. Under NT, the equivalent tools are the Task Manager and Performance Monitor.
You can invoke the Task Manager by issuing the Control-Alt-Delete key sequence and selecting "Task Manager." The applet includes three tabbed pages:

The application list tracks open windows and applications that are currently running. Although useful for identifying applications that are no longer responding, this list conveys no performance information.
The process list shows all active processes together with configuration information such as CPU usage, physical and virtual memory usage, and priority. Figure 5 shows a Task Manager process list sorted by virtual memory. You can see that the Oracle executable is consuming the most virtual memory. Its virtual memory size of 19.4MB includes the SGA and memory for all server threads. You can also see that the process contains 15 threads, which indicates the number of sessions connected to the database. In this case, the 15 threads correspond to six background threads, seven dedicated server threads, and two overhead threads.
The final page of the task manager shows detailed memory and CPU usage. It provides a quick overview of CPU and memory consumption.

The Windows NT performance monitor is an NT utility that displays a range of performance metrics, including overall CPU, memory, network, and disk metrics, not only at a summary level but also for individual threads and processes. The tool can record metrics to a log file for later playback and can report in both graphical and report formats.
From an Oracle DBA's perspective, the best thing about the NT performance monitor is that it can also display or record Oracle performance metrics--provided you install the Oracle performance manager option. Figure 6 shows an example of the performance monitor reporting on both Oracle and NT metrics.

While the integration of Oracle and NT performance metrics provides an excellent means of correlating database and operating system performance metrics, Oracle's selection of metrics is a little disappointing. For example, there is no metric that shows the number of logical reads per second or the rate of SQL statement execution. In theory, you could hand-tailor such metrics by amending the %oracle_home/dbs/perf80.ora file, which contains the SQL statements that define the metrics, but most users will prefer to employ third-party monitoring tools.

MONITORING THREADS

Although the task manager can show you the most resource-intensive processes, it does not break this information at the thread level; consequently, it can't tell you which Oracle sessions (which server threads) are consuming the most resources. However, you can use the query in Listing 1 to display the overall CPU usage for each thread (although the CPU usage for background tasks might not be shown).

LISTING 1. CPU usage query.

column program format a20
column username format a12

select p.spid thread, s.username,
    decode(nvl(p.background,0),1,bg.description,
         s.program ) program,
    ss.value/100 CPU,physical_reads disk_io
 from v$process p,
    v$session s,
    v$sesstat ss,
    v$sess_io si,
    v$bgprocess bg
 where s.paddr=p.addr
  and ss.sid=s.sid
  and ss.statistic#=12 
  and si.sid=s.sid
  and bg.paddr(+)=p.addr
 order by ss.value desc;

The first two threads in the Oracle executable are the main thread and the dispatcher thread, which are not associated with any background or server process. The next five threads at least correspond to background tasks. The number of background tasks can be greater if the database is in archivelog mode, has a dedicated checkpoint process, or has implemented shared servers or parallel query.
Although it's possible to display individual thread CPU and other resource usage in the NT performance monitor, mapping the thread numbers shown in performance monitor to thread IDs recorded in v$process is complex and unreliable because the mapping of thread IDs to thread numbers can change as threads are created and destroyed. If you need to monitor the performance of individual threads, you could try the Oracle Top Sessions monitor, which is available as an add-on to Oracle Enterprise manager, or use a third-party tool. If you install the Perl utility and the Oracle DBI interface (more on Perl later), you can use my dbtop.pl script (available at my home page, werple.net.au/~gharriso) to monitor CPU and IO usage on a thread-by-thread basis.

PERFORMANCE TUNING

Performance tuning principles for Oracle in an NT environment are fundamentally the same principles that govern Unix tuning. They include:

Tuning SQL and the application design
Ensuring suitably sized hardware--particularly that memory, CPU, network, and disk resources are adequate for the demands of the application
Spreading data files evenly across a sufficient number of disks to eliminate disk bottlenecks or excessive queuing
Separating redo logs from data files and the archive log destination, and possibly alternating redo logs across multiple disks
Adequately sizing Oracle internal resources such as the SGA, rollback segments, sort areas, and temporary tablespace.

There are, however, a few NT-specific considerations:

Windows NT supports asynchronous I/O; configuring multiple db writers is not required.
You can create raw partitions for Windows NT for highly I/O-intensive applications. The pros and cons of NT raw partitions are the same as those for Unix raw partitions.
If you're not using raw partitions, you will have the choice of either FAT or NTFS ones (see sidebar, "Which File System To Use?"). You may get some small performance improvement using NTFS partitions, but more important, only NTFS partitions can properly implement NT security. If you implement data files on FAT partitions, they can be viewed by anyone with access to the NT server. On NTFS, you can restrict access to DBA accounts only.

WITH A LITTLE HELP...

All Oracle distributions now include the Oracle Enterprise Manager (OEM). OEM allows almost all database administration tasks to be performed in a Windows GUI system environment.
      Although OEM can be run in a client/ server configuration against a Unix server, many of its features are awkward or impractical when run in this manner. For example, the Instance Manager tool is unable to read or write from configuration files, and the Data Manager must perform exports and import over the network. It's also necessary to configure remote access to privileged accounts through an Oracle password file.
      Under NT, none of these client/server limitations apply because OEM can run directly on the server. Consequently, OEM is a much more attractive option for an NT server and can improve your productivity substantially.
      Although the GUI interfaces to Oracle provided by the Oracle for NT applets and OEM definitely make common chores easier than on Unix, complex tasks may be impossible or impractical without resorting to a procedural scripting language--a facility that is conspicuously absent under Windows NT. At these times, you may miss the shell language and utilities available under Unix.
      The good news is that it's completely possible to run a Unix--or at least a Posix--environment under Windows NT. The NT kernel includes a Posix subsystem, and you can obtain a basic set of Posix tools--including a shell, the vi editor, and Unix commands such as grep and awk--from The Windows NT Resources Kit (Microsoft Press, 1996). Alternately, if you prefer to implement the GNU bash shell and GNU utilities, they can be obtained from www.cygnus.com/misc/gnu-win32/.
      You can obtain a binary distribution of the popular Perl scripting language from www.perl.com. Not only has this version of Perl been ported to NT, but hooks into some useful NT system facilities have also been added. You can even obtain a binary distribution of the DBD/DBI Oracle add-ons to Perl, which allow the language to communicate directly with one or more Oracle instances. The performance and productivity of Perl, together with its ability to directly access the NT environment and Oracle databases, make it an invaluable tool.

BUGS? WHAT BUGS?

I may have painted a fairly rosy picture of the Oracle/NT environment. However, the NT environment is no more immune to bugs than others. Although most bugs are either noncritical or have workarounds or patches, you may encounter a few of them while attempting to implement some of the features I've discussed. For example:

The Oracle8 facility to shut down a service cleanly is not functional in Oracle8.0.3.0 (see Oracle Product Change Request #513414).
Running in archivelog mode--and hence performing online backups--is not supported in Oracle7.3.2.1 (see alert #10802099).
The integration of Oracle logging into the NT performance monitor does not always work without manual intervention (see Oracle Product Change Requests #556846 and #520639).

ORACLE ON NT: HERE TO STAY

I've given you just a taste of Oracle administration under Windows NT. Oracle administration is a big topic, and we've barely scratched the surface here. However, I think you'll see that transitioning Oracle from Unix to NT is neither particularly unpleasant nor troublesome, although there are challenges.
Both Unix and NT are here to stay, at least for the foreseeable future. While Oracle professionals who restrict themselves to the Unix environment will continue to be in demand, those who embrace both environments will have access to a greater range of Oracle installations and will experience greater variety in their professional lives. If you're an Oracle professional, you owe it to yourself to be at least familiar with Oracle on NT.

Guy Harrison is an independent Oracle consultant specializing in Oracle development and performance issues and author of Oracle SQL High Performance Tuning (Prentice Hall, 1997). He is currently working with Quest Software Inc. in Australia. You can contact Guy at [email protected] or through his home page at werple.net.au/~gharriso/.

Which File System To Use?
NT offers the FAT and NTFS file system types; furthermore, the NTFS file system can be compressed or uncompressed, or data files can be created on unformatted "raw" partitions. So when creating an NT Oracle database, which file system type should you choose for your data files and redo logs? Should you avoid file systems altogether?
      I was curious about the performance characteristics of the various alternatives, so I performed a simple benchmark to compare insert performance, indexed I/O, and full tablescan I/O. I created a simple database on FAT, NTFS, compressed NTFS, and raw partitions. For each database, I imported an 8MB export file, performed a complex query that joined (via index) four of the larger tables I had imported, and performed a full export.
      The tests were performed on a Pentium Pro 256MHz single-CPU host with 64MB of memory and a single 2.5GB IDE disk drive. The machine was rebooted before each test. The configuration I used is hardly typical of production NT servers, and the tests I performed could hardly be described as exhaustive. You should perform your own benchmarks where necessary to determine the best configuration for your system.
      The results--as indicated in Figure 7--revealed no significant performance difference between the FAT and NTFS file systems. However, compressed NTFS file systems performed much worse than either FAT or uncompressed NTFS.
      The raw partition database significantly outperformed both NTFS and FAT file systems for all operations. I expected that raw partitions would perform well for write-intensive operations, but I was surprised to see that operations involving tablescans and index lookups also improved.
      These results should not be seen as in any way conclusive. However, on the basis of these results, I would consider raw partitions for high-performance NT databases but steer clear of compressed NTFS file systems. Don't forget to take issues such as security, maintainability, and backup, into account when deciding on a type of file system.

This is a copy of an article published @ http://www.oreview.com/