Oracle on Windows

In all the years that I have worked with both Microsoft and Oracle I have never seen so much interoperability and cooperation. This week Oracle announced support for People Tools on SQL Server 2005. See the announcement at http://www.oracle.com/corporate/press/2006_jul/peopletools-848.html. This is just one in many examples of how the interoperability between Oracle and Microsoft products is getting better. Other examples include the ability to integrate Oracle logins with Windows Active Directory and the extensive support of Oracle products on Microsoft Windows.

Oracle was the first database software available for the Windows NT platform and was also the first database software available for the 64-bit editions of the Windows platform. Oracle supports not only their database software, including RAC, on Windows, but also People Tools, Oracle eBusiness Suite software, and the Oracle Fusion Middleware products. Oracle even supports Microsoft clustering with Oracle Fail Safe.

Recently a new type of interactivity has started occurring. Oracle Grid Control monitors the Windows OS, but now does much more. Oracle is now providing Grid Control Plug-ins for the following Microsoft products:

• Active Directory
• BizTalk Server
• Commerce Server
• IIS
• Internet Security and Acceleration (ISA)
• Microsoft .NET Framework
• Microsoft SQL Server

These Plug-ins allow Oracle Grid Control to monitor and provide alerting functions, so that in the event of a failure, you will be notified via email, pager, etc. Oracle has expanded Grid Control in order to provide a full service management and monitoring utility.

On the other side of the fence, Microsoft does not explicitly create Microsoft Operations Manager (MOM) management packs for non-Microsoft products, but has opened this up to the 3rd party vendors. There are several management packs for Oracle products available today. This allows the extensibility of MOM to be used with Oracle and any OS that you happen to run Oracle on.

This week I happened to be working on my upcoming “SQL Server 2005 Administrator’s Companion” book and was going through the editorial process on replication. With SQL Server 2005 replication, you can replicate data into a SQL Server 2005 database from an Oracle publisher. This allows SQL Server reporting servers and batch servers to get their data directly from Oracle using replication. In addition, it is possible to set up an Oracle subscriber to use a SQL Server publisher, thus using Oracle for reporting and SQL Server for OLTP. It is also possible to perform distributed queries between Oracle and SQL Server databases by configuring the Oracle database as a linked server within SQL Server.

There has always been cooperation between Oracle and Microsoft for using Oracle on the Windows platform, but the management cooperation has just begun. In the next few years you will most likely shop around and decide whether you like the Microsoft tools or the Oracle tools better and then implement that tool throughout your entire enterprise.

I’d like to stray a little bit from the pure software aspects of Oracle on Windows to focus on the hardware and OS perspective in this blog. The Oracle software is very important, and the version of Windows 2003 is also very important, but if you do not have the proper hardware running this software, you might be headed for trouble. In this installment I will be describing the hardware components necessary to run Oracle and Oracle RAC on Windows as well as discussing some of the new technologies that are available today.

When putting together a system to run Oracle, you must consider not only the amount and type of hardware, but the brand of hardware. In addition, it is important to properly size the system. All of these tasks lead you to putting together an optimal system for your environment.

I’m not going to recommend that you purchase a particular brand of hardware, but I would recommend that you purchase your hardware from a company that you recognize. Not only is the quality of the hardware important, but the quality of the support as well. Many vendors go the extra step and test their products with Oracle on Windows in order to provide an optimal solution. It is especially important when implementing Oracle RAC that you choose a vendor that can provide a tested solution. These vendors will often supply you with white papers or other docs on how to optimally configure their system for Oracle RAC on Windows.

With Oracle RAC, the storage selection is as important as the selection of the actual servers. The storage system must provide the ability to be shared, be robust and be able to support a sufficient number of disk drives to achieve the performance that you need. In addition, the storage should be configured in order to avoid a single point of failure. In this installment of my blog I will discuss the servers, the storage and the infrastructure.

Servers

When choosing a Windows server to run Oracle there are several features that you should look for. The CPU is the brains of the computer and should be selected for the type of processor, the cache size and its speed. Because of the memory requirements of the dedicated and shared server processes, the larger the better is usually the case with the cache on the CPUs. The more CPUs there are in the system, the more important the CPU cache is. If you have a choice between a faster CPU and more cache, with Oracle it is usually better to go with more cache.

The type of CPU offers quite a selection now. AMD has an excellent processor with the Opteron line, and Intel has done a great job with the EM64T processor. They are both available with single or dual cores and both are x64 processors. I only recommend the Itanium 2 processors for specific applications where a large system with many processors is required due to the cost differential between the x64 systems and the Itanium systems. In more cases, the x64 (Opteron or EM64T) processors will work great for you.

Storage

Storage is one of the most crucial components of your Oracle system. The storage subsystem must be robust, high performing and expandable. Today there are many options to choose from. In the past I would have only recommended SAN storage for an Oracle RAC system, but recently I have had very good experience with both NAS and iSCSI solutions. This in conjunction with Microsoft’s release of its iSCSI support for Microsoft Storage Server 2003 R2, iSCSI is not much more interesting.

When selecting a storage subsystem make sure to minimize the single points of failure. This is done by creating as many redundant components as possible. In addition, not only the size of the disk drive is important, but the number of disk drives is crucial. A state of the art disk drive should be configured to do no more than 125 IOs Per Second (IOPS). Running disk drives hotter than this will cause latencies to increase, thus causing system performance problems.

Infrastructure

The network infrastructure is an important piece of your entire system, especially with network storage. If you are using Network Attached Storage (NAS) or iSCSI you should dedicate a network specifically for storage. By sharing the storage network with other network functions such as the RAC interconnect or general network connectivity you could create higher latencies and thus reduced performance. Since the speed of the network is critical to your storage performance (in a NAS or iSCSI environment) you should not use anything less than Gigabit speed networks.

Network storage should always be available locally, that is, there should not be any active components between the database server and its storage; such as routers, firewalls, etc. The only components that should reside between the database server and its storage is one or more network switches. Active components such as routers and firewalls can add too much latency.

Summary

In summary, it is important to think about the hardware that you will be deploying in terms of both stability (supportability) and performance. Many of the performance engagements that I am brought in on are caused by an undersized system. If you are using network attached storage, make sure that you have a sufficient network infrastructure to handle it.

For those of you who have used Oracle on Windows you either think of the Windows registry as a blessing or a curse. The Windows registry is critical for the setup and maintenance of services as well as to maintain parameters relating to Oracle Home, etc. Without the registry, or with an incorrectly configured registry, Oracle on Windows will not function. Thus it is unwise to modify the registry own your own. Let the Oracle installer and DBCA modify the registry.

One of the main differences between Oracle on Windows and other platforms (besides the registry) is the use of services. With the Windows operating system any process started by the login user will exit when the user exits. This would not be good for something like the Oracle database, thus the Oracle instance is started as a Windows Service.

A Microsoft Windows service allows you to create long-running executable applications that run in their own Windows sessions. These services can be automatically started when the computer boots, can be manually started, stopped and paused. Because they run in their own session, they do not contain a user interface, but they can take parameters or use registry parameters. Services are used for long-running functionality that does not interact with other users, thus is good for server applications such as the Oracle Database Server and Oracle Listener. In addition, services can be invoked under the user context of a domain user, local user or the system account. Thus the Oracle service can be started by a user other than the Oracle database owner, but will run under the context of the Oracle user.

By default, the Oracle database server must create a minimum of two services:

• OracleServiceSID is the Oracle bootstrap service that is used as the service that the Oracle instance runs under. The Oracle bootstrap service must be started in order for the Oracle instance to start, however, the Oracle instance can be started up or shutdown manually once the bootstrap instance is running.
• OracleHOME_NAMETNSListener is the service that controls the Oracle listener.

These are the only two services that are critical to the Oracle instance (unless you are using ASM). Other services that you might encounter include:

• OracleDBConsoleSID is the Oracle Enterprise manager DB Console service.
• OracleJobSchedulerSID is the Oracle job scheduler process.
• OracleHOME_NAMEiSQL*Plus is obviously the iSQL service.
• OracleServiceASM_SID is the ASM instance service.

As I mentioned earlier, the OracleServiceSID is really just a service to bootstrap the Oracle instance. This service is required before the Oracle instance can be started, but if this service is running, you can still use SQL*Plus to start and stop the Oracle instance. The registry parameters HKEY_LOCAL_MACHINE->SOFTWARE->ORACLE->KEY_HOME_NAME->ORA_SID_AUTOSTART and ORA_SID_SHUTDOWN controls whether the Oracle instance automatically starts and stops when the OracleServiceSID service does. In addition to these parameters, the registry location HKEY_LOCAL_MACHINE->SOFTWARE->ORACLE->KEY_HOME_NAME holds some other very useful entries:

• ORACLE_BASE is the location of the Oracle base directory for this Oracle home.
• ORACLE_HOME is the location of the Oracle home directory for this Oracle home.
• ORACLE_SID is the Oracle System Identifier or SID.
• SQLPATH specifies the location of SQL scripts.
• RDBMS_CONTROL specifies the location of control files. This is where Oracle will look for the spfile or the pfile for this instance.

There are other registry parameters, but I have touched on some of the most important. Beware, registry parameters must be exact. Spelling and capitalization are extremely important. If you do not have the parameter exactly right, it will be ignored and you will be debugging an illusive problem.

The registry is very useful in that it centralizes the location of parameters and settings for the Oracle installation. However, if something goes wrong, it can sometimes be very difficult to fix, so be careful.

Services are an important part of Windows 2003 and the entire Windows family. Without services it would be very difficult to implement Oracle without requiring the Oracle user to stay logged in at all times. Oracle on Windows is different from other platforms, and this is what distinguishes it.