Page 829
Page 830
This appendix covers Oracle's relationship with Microsoft's Windows NT operating system platform. For references to non-platform specific Oracle database issues, please refer to the rest of the book. Because this publication is dedicated to providing information on the latest and most used versions of software, the information provided in this section is specific to version 7.3xx of the Oracle Relational Database and mainly version 4.0 of the Windows NT 4.0 Server operating system.
Table B.1 lists the supported versions (for versions 7.3xx only) for Windows NT Server, as published and released by Oracle Worldwide Customer Support, July 1997 (latest available).
Table B.1 Version Availability
Oracle Version | Windows NT 3.1 | Windows NT 3.5 | Windows NT 3.51 | Windows NT 4.0 |
7.3.2.1.1 | no | no | yes | yes |
7.3.2.2.1 | no | no | yes | yes |
7.3.2.3.1 | no | no | yes | yes |
7.3.3.0.0 | no | no | no | yes |
The latest 7.3xx version of Oracle released on Windows NT to date is version 7.3.3.0.0 (there is a 7.3.3.3.0 patch). A version 7.4 will not likely be released. Oracle8 was released instead. The latest version of Oracle8 for Windows NT available (as of September 1, 1997) is version 8.03.
There is no doubt that businesses today are looking for less expensive alternatives to provide quality service information systems. As a result, a market has grown for an operating system that is capable of supporting a small to midsize business. Smaller businesses don't necessarily want or need to put out the money for large platforms when their needs don't require all the options of a larger system. Microsoft Windows NT was created to provide this exact service.
Windows NT is becoming more and more robust every day, which enables consumers to consider Windows NT as a viable platform consideration. Because consumers are interested in cutting costs wherever possible, Windows NT is taking over a bigger and bigger share of the operating system market. Oracle has seen the growth of this particular operating system and has moved Windows NT up to its developmental platform level. This means that Oracle will be developing its software on Windows NT at the same level as Sun Solaris, HP/UX, and other developmental platforms.
Oracle on Windows NT is a fully functional RDBMS (Relational Database Management System). The Oracle software, on Windows NT, is a single process with multiple operating system threads. This is different from some versions of UNIX. Because of its development level status, Oracle has been integrated tightly with the Windows NT operating system, including relationships between Oracle and Windows NT's Performance Monitor, Event Viewer, and its Registry.
Page 831
Windows NT also provides security capabilities, which is a much desired option in the database world. Of course, the added advantage of Oracle's single point-of-control GUI (Graphical User Interface) administration toolOracle Enterprise Manager (OEM) (see Chapter 9, "Oracle Enterprise Manager")is also an added user-friendly product.
The main concern on most DBAs' minds is how well Windows NT can really handle large, DSS (Decision Support System) or OLTP (Online Transaction Processing) databases. Well, the answer is simply this: Windows NT has come a tremendously long way with its capabilities to handle multiple users and large amounts of data. There is no doubt that it is incapable of handling the multi-user, terabyte-sized systems that are being handled by large UNIX platforms at present. Oracle is being limited only by Windows NT's limitations. However, Windows NT's capabilities are growing every day and eventually it will be a serious competitor for the current large systems. In the meantime, Windows NT provides a cheaper and often satisfactory solution for many situations that arise in small to mid-size businesses. Given the speed of its development at Microsoft, it is useful to note that several years down the road, Windows NT may possibly be the solution of choice.
Before installing the Windows NT operating system, the administrator needs to decide which file system is going to be appropriate for the database server. The Windows NT operating system provides two systems: FAT (File Allocation Table) and NTFS (NT File System). This section describes both the advantages and disadvantages of each file system so that you can make an educated decision on which file system will be appropriate for your particular situation.
The File Allocation Table (FAT) is a file system that has been used on DOS, Windows 3.x, OS2, and Windows 95 PCs. Table B.2 lists the features of the FAT.
Table B.2File Allocation Table (FAT) Features
Feature | Details |
Operating systems that can access FAT | Windows NT, Windows 95, MS DOS, OS/2 |
Partition size limit | 4GB |
Filename length limit | 255 characters |
File size limit | 4GB |
File types | Read Only, Archive, System, Hidden |
Local security | None |
File compression capability | None |
Page 832
Advantages The main advantage to the FAT file system is that the system overhead is very low (less than 1MB per partition). This is a good choice for drives/partitions that are less than 400MB. Also, because FAT has been present on previous operating systems (as listed in the introduction to this table), FAT is backward compatible and capable of performing multiple operating system boots.
Disadvantages Security is the main issue with the FAT file system. The only security on FAT is directory-level sharing. Outside of that, FAT has no local security. Even the directory-level sharing allows users that log on locally access to the physical directories. This is a serious issue for databases who contain sensitive information. Another issue with the FAT file system is that once the drives/partitions grow to greater than 400MB, file access becomes much slower and database performance will decrease significantly. This is due to the fact that FAT uses a linked list folder structure. Therefore, as a file gets larger and largeras a result of inserts and updates, for exampleit becomes fragmented on the hard disk.
The NT File System (NTFS) can be accessed only by the Windows NT operating system, allowing for higher security. Table B.3 lists the features for the NTFS file system.
Table B.3NT File System (NTFS) Features
Feature | Detail |
Operating systems that can access NTFS | Windows NT |
Partition size limit | 2 terabytes actually (16 exabytes theoretically) |
Filename length limit | 255 characters |
File size limit | 4GB_64GB actually (16 exabytes theoretically) |
File types | Further extended, extensible |
Local security | Yes |
Compression capability | Yes: on files, on folders, and on drives |
Advantages NTFS is a much more robust file system than FAT mainly because of its security features. Local security is required. NTFS allows for file-level (versus directory-level) security. This gives the administrator the ability to control access to all the information stored in the file system. Also security is added because the NTFS file system can be accessed only by Windows NT; therefore, someone cannot start the computer with any other operating system and access information on the NTFS partition. In addition to its security features, NTFS is a better choice for drives/partitions greater than 400MB due to performance advantages over the FAT file system dealing with larger drives/partitions.
Page 833
Disadvantages The overhead for an NTFS drive ranges from 1MB_5MB (or more), depending on the size of the partition. This is fine for large volumes, but for smaller volumes (less than 400MB), the overhead is a disadvantage.
TIP |
Make sure that you install Service Pack 3 for Windows NT. This will work out a lot of kinks with the operating system, one being the Windows Explorer tool. Without the service pack, Explorer can give you misleading file size numbers if you are pushing file size limits. You can download this service pack from the Microsoft Web site at www.microsoft.com. |
Administering Oracle from a Windows NT machine can be an easy task once the administrator is familiar with the NT atmosphere. There are a number of tools and utilities that Windows NT provides that are integrated with the Oracle software. In this section, you will find some basic information about the Windows NT operating system as it pertains to Oracle, which will provide the administrator with a clear mapping of the interaction between a Windows NT server and an Oracle database.
There are many ways to access and monitor your Oracle database using Windows NT Tools, including creating, editing, and starting Oracle services and instances, user management, and backup utilities. In this section, you will find descriptions of the Windows NT Tools that you will be using as an Oracle DBA.
Control Panel The Windows NT Control Panel is found by going to the Start menu and selecting Settings, Control Panel. The screen that appears will look like Figure B.1.
You will find several tools within this panel. A tool used often in conjunction with Oracle is the Services tool. NT Services includes both a listing of executables on the NT server that is identified in the Registry (for example, FTP, third-party backup software, Oracle instances, and so on) and their availability status on the machine. Look to a following section "Windows NT Services and Oracle Instances" for more information regarding the definition of a Windows NT Service.
User Manager for Domains User Manager is Windows NT's administrative tool for managing user, group, and network security (see Figure B.2). It is important to note that user security on the Windows NT Server will be mapped to Oracle security. For example, if a user on the Windows NT Server is not allowed to access the directory where a data file is, then that same user will not be able to query the tables that store data in that directory. This tool is opened by going to the Start menu and selecting Programs, Administration Tools, User Manager for Domains.
Page 834
FIG. B.1
Control Panel is the
Windows NT computer
configuration interface.
FIG. B.2
User Manager for
Domains is Windows NT
Server's administrative
tool for managing user,
group, and network
security.
Windows NT Backup Windows NT's Backup utility is used to take backups of anything residing on the machine (see Figure B.3). Windows NT Backup is good for taking cold physical backups of the Oracle database. It allows you to select which drives you want to back up and where you want to send the backup and offers verification upon completion of the backup. This tool is activated by going to the Start menu and selecting Programs, Administration Tools, Backup.
Page 835
FIG. B.3
The Backup utility is
Windows NT Server's
tool for performing cold
physical backups.
Windows NT Instance Manager The Windows NT Instance Manager can be used to create a SID (instance), Service, Database, and Data Dictionary for Oracle (see Figure B.4).
FIG. B.4
Instance Manager is the
Windows NT Server's
tool for creating and
monitoring instances,
services, the database,
and the data dictionary
for Oracle.
When working with Oracle on Windows NT, you, the Oracle DBA, may find that the difference between a Windows NT service and an Oracle instance is very confusing. This section will clear up this confusion.
A service in Windows NT is an executable that was installed in the Windows NT Registry. The Windows NT Registry tracks security information for each service created. An Oracle instance is a word used to describe a unique SGA (System Global Area) paired with an Oracle service.
At least one Oracle service is created for each specific database: one that runs the database and a possible other that starts the database whenever the operating system itself is started. When an Oracle service is created, it can be found under Control Panel, Services and is named in the
Page 836
format OracleServicesid (see Figure B.5). The sid (system identifier) identifies which instance the service refers to. Also, you may find the OracleStartsid service if the service was created in Automatic start mode by the Windows NT Instance Manager.
TIP |
When Oracle is installed, there are also services created for the Oracle Listener for NamedPipes (nmp) and the Oracle Listener for TCP/IP (tcp). An Oracle Listener literally acts like an ear on the Oracle server, listening for clients who want to contact the Oracle server. |
FIG. B.5
Windows NT Services is
an administrative tool
that can be used to
monitor Oracle services.
When you first install Oracle on Windows NT, you will find that the installation already has created an Oracle instance named orcl. The orcl instance is the default database provided by Oracle. If you open up Services in the Control Panel, you will find two things: the Oracle service OracleServiceorcl and another service called OracleStartorcl. You'll also notice that next to the name of the service, the service is identified as being Automatic. There are three different start modes for a service, as described in Table B.4.
Table B.4Windows NT Service Start Modes
Mode | Description |
Automatic | Service starts automatically every time the operating system is started. |
Manual | Service can be started manually, by a user, or by a dependent service. |
Disabled | Service cannot be started. |
TIP |
If there is an OracleStartsid service, the Oracle database service will not only be started, but the actual database will open as if a startup command had been issued by the database's internal user. The OracleStartsid service creates a strtsid.cmd file in the ORANT\DATABASE\ directory. Therefore, when the operating system starts, the database is also started, and there is no need to start each separately. |
Page 837
CAUTION |
As always, make sure to shut down the database first before shutting down the operating system, because this will not be done automatically. |
There are some things you must consider when you install Oracle on the Windows NT Server. Previous lessons with other software may have taught you to be prepared for any new installation onto a computer. Well, Oracle is, by far, no exception. In the following sections you will find some basic installation checklists.
Before you install Oracle on the Windows NT Server, it is important to use the following as a basic checklist:
Installation begins by putting the Oracle7 Server CD-ROM into the CD-ROM drive. The Oracle Installer should start up immediately; but if it doesn't, go to Windows Explorer (or My Computer) and select the CD-ROM drive and then select SETUP.EXE. After that, simply follow the directions and enter information as you are prompted for it. It is highly suggested that you choose the defaults during installation. If you decide to use your own selection instead of a default, make sure to take note and be aware of the effect it may have on a successful instal-lation.
CAUTION |
For some reason, the Oracle Installer for Workgroup Server 7.3.2 (and maybe other versions) seems to attempt to install Enterprise Manager and its repository before it has even installed the database itself. You can either skip over the repository parts of the installation by not choosing to create or drop, or you can do a Custom Install of the database and leave Enterprise Manager to be installed after the database. |
Page 838
The following general instructions are for upgrades of previous versions of Oracle7 to Oracle 7.3xx:
Now that you have prepared for installation, you can enter the installation phase.
Follow these steps in order to perform the upgrade:
>ORADIM73 -new -sid SID -intpwd PASSWORD -startmode AUTO -pfile C:\ORANT\DATABASE\INITsid.ora.
>ORADIM73 -startup -sid SID -pfile C:\ORANT\DATABASE\INITsid.ora.
Page 839
Now you have completed installation of the Oracle software. At this point, you can continue to create an instance in the next section.
Although Oracle provides a default instance (orcl), it is suggested that you create an instance that is specially designed for your database's needs. The following section shows you how.
When creating a new instance, the first thing you need to do is determine all the parameters you need to delineate the shape and size of your database. The parameter file, called the INITsid.ora file (where sid is the name of the instance), is used to start up the database with all the proper configurations. The simplest way to create your new INITsid.ora file is by making a copy of the INITorcl.ora file and modifying it. This file is the parameter file for the default database created on installation. You can find the INITorcl.ora file in the \ORANT\DATABASE directory. Make sure to rename the copy to INITnsid.ora (where nsid is the name this section will use for the new instance) and change, at the very least, the following parameters:
You can alter all the other parameters in the file also, but the two noted above are mandatory changes that must be made. In addition, make sure the INITnsid.ora file is in the \ORANT\DATABASE directory before making a service for the new instance.
After completion of the INITnsid.ora file, it is necessary to create a Windows NT service for the new instance (for a description of a Windows NT service, see the previous section, "Windows NT Services and Oracle Instances"). You can create a service by using either the Instance Manager (GUI tool) or by using Line mode.
Using Instance ManagerFollow these steps in order to create an Oracle instance:
Page 840
FIG. B.6
The New Instance
window allows the
administrator to create
a new sid.
SIDThis is the name of the instance. It should be four characters or less.
DBA Authorization PasswordThis will be used as the internal password for this instance.
Confirm DBA Authorization PasswordSame as DBA Authorization Password.
Maximum Number of DBA/Operators (optional)This field limits the number of operators allowed on the instance. The default setting is 5.
Instance Startup Mode (optional)There are three modes: Automatic, Manual, and Disabled. Automatic is the default. (For descriptions of startup modes, see the section "Windows NT Services and Oracle Instances.")
Using Line Mode To use Line mode, you need to open a DOS command prompt. Go to Start and choose Programs, MS-DOS Prompt. Type the following at the prompt:
> oradim73 -new -sid NSID -intpwd ORACLE -startmode AUTO -pfile Âc:\ORANT\DATABASE\INITnsid.ora
where NSID is the name of the new instance and ORACLE is the internal password for that instance.
After you are done creating the instance, open Server Manager. Connect internal and try to start the database as follows:
>CONNECT INTERNAL/ORACLE@2:NSID >STARTUP PFILE=C:\ORANT\DATABASE\INITnsid.ora
TIP |
If the new SID is not the default database, you need to use the @2 operator for version 7.3 when connecting internal. If the new SID is the default, you don't need to use the @ operator at all. |
This will verify that the instance has been created.
Page 841
Hopefully, if your database is properly designed, it will need little or no tuning and optimizing. However, more often than not, unexpected situations arise, causing your database to need some tweaking. In the following sections you will find several causes and solutions for adjusting your database into a well-oiled machine.
There are several default configurations of a Windows NT server that can be adjusted if necessary. The following are some examples:
Page 842
Configurations for Oracle are stored in the Windows NT Registry. To open the Registry, go to the Start menu, choose Run, and then type regedt32. You can find all the information for Oracle stored under the Registry key \HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE. In order for the changes to take place, the Oracle services and instances must be restarted.
Some configurations to consider are as follows:
For more performance tuning tips, please refer to Chapter 29, "Performance Tuning Fundamentals," found earlier in this book.
I have often found that the most valuable information is that learned from previous experiences. In the following sections I have listed some of my personal encounters and struggles with Oracle on Windows NT.
It is important to be aware of any limitations that may affect your database design. Oracle often puts this information in obscure places, so for your convenience I have added some of the limitations that I've run across:
Page 843
When installing Enterprise Manager on the server (realize that Enterprise Manager is mainly used for remote administration), make sure that your Windows NT server has the TCP/IP option installed. If you don't install TCP/IP, set to NETBUI and make sure Oracle's Bequeth is identified with a unique alias.
Oracle is capable of working around Windows NT in order to access files larger than 4GB. However, you need to note that the Backup utility provided by Windows NT will most likely have trouble backing up these files. You may need to obtain a third-party software package (such as Seagate, Arcserve, and so on) to use for backup or you can make several smaller files to replace the one large file.
Exporting directly to tape, unfortunately, cannot be done. There is no way in the command line to name the tape drive. It is expecting a filename or drive name, but there is no name associated with a tape drive on a Windows NT system. I have tried alternative methods: named pipes (not available on NT, too low level), third-party software (Seagate, MKS Toolkit, Perl, C). Nothing has worked. This is an unresolved issue with Oracle, and it is not fixed in version 8.03.
When trying to automate in UNIX, you are able to use the shell (utilities like awk, and so on) to automate most anything. In Windows NT, there is a tool called the Windows AT command that can be used along with the DOS batch language. The AT command is not very robust and is rather inflexible, and the lack of a proper scripting language is one of Windows NT's biggest shortcomings. It is nearly impossible to automate procedures, exports, and the like using the AT command. This can be aided with some third-party software (Arcserve, MKS Toolkit, and so on).
The UTL_FILE package was first introduced in version 7.3xx of the Oracle database. This package provides capabilities for PL/SQL programs to access files and write output to them. It also has its own error handling and debugging. The UTL_FILE package is not automatically installed on the system during installation, but the script is included with Oracle7.3xx and can be found in the \ORANT\RDBMS73\ADMIN directory. The documentation for this package can be found
Page 844
in Chapter 8 of the Oracle7 Server Application Developer's Guide (which is provided as a part of the Oracle Server documentation).
If you choose to use this package, make sure you put the name of the directories in the INITsid.ora file. This requirement ensures security in accessing files. To set the directory (and you can put in multiple directories) in the parameter file:
UTL_FILE_DIR = directory name
Make sure the directory name does not end in a /, because it will cause a UTL_FILE.INVALID_PATH error. Also make sure to give the Oracle owner permissions in the UTL_FILE directories in order to provide security against overwriting system files. Remember that in order for the database to recognize any change in the INITsid.ora file, the database must be shut down and restarted.
The following sample code shows a message being written to a log file:
DECLARE al UTL_FILE.FILE_TYPE; linebuff VARCHAR2(80); BEGIN al := UTL_FILE.FOPEN(`C:\LOG\dbproc.log','w'); linebuff := `SUCCESSFUL COMPLETION `||TO_CHAR(SYSDATE,'mm/dd/yyyy hh:mi:ss'); UTL_FILE.PUT_LINE(al,linebuff); UTL_FILE.FCLOSE(al); END;
The maximum buffer size for a UTL_FILE.PUT_LINE or
UTL_FILE.GET_LINE is 1,023 bytes.
The file size limit is dependent on the operating system (see the section "Windows NT
File Systems" at the beginning of this appendix).
Although it does provide a much needed programming capability, there are a couple of bugs in the UTL_FILE package:
Hopefully, the above information has provided you with knowledge that you will be able to use in the future, if not today. Remember, no software package is bulletproof.
Page 845
Oracle version 8 on Windows NT promises to make the database more enterprising by making many of its sizing limits much bigger. Support for Oracle7 will continue until at least December 31, 1999 (this is according to Oracle's World Wide Customer Support Services announcement by Randy Baker, August 1997). Version 8.03 of the Oracle database is the most current release for the Windows NT platform as of September 1997.
Here are some features and changes to be expected in Oracle 8.0xx:
Some changes that were expected in the 8.0xx releases have been moved to 8.1xx releases and later. More object-oriented features will be included, such as Java stored procedures.l
Page 846