The Oracle DBA's Guide to NT

ORACLE ARCHITECTURE ON NT

On all these systems, Oracle implements a common architecture that includes the following components:

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. 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 2000, the process memory limit will increase to 32GB, which should be sufficient for most Oracle installations.
    This multi-threaded architecture is very efficient, permitting fast, low-overhead context switches, because all the threads of the ORACLE process share resources. Among many other capabilities, the Oracle Administration Assistant provides a way to identify individual threads. The Assistant is run from:
    Start > Programs > Oracle – HOME_NAME > Database Administration > Oracle Administration Assistant for Windows NT
Right-clicking on the SID, such as V815, and choosing Process Information... pops up a window displaying a list of the Oracle threads with their type (background or foreground), associated Oracle user, thread ID and % CPU used. This window also includes a Kill Thread button. To be able to use this Process Information feature, the line SQLNET.AUTHENTICATION_SERVICES= (NTS) must be present in the SQLNET.ORA file, to enable Windows NT native authentication. If necessary, the line should be added and the instance restarted.

MEMORY

Tuning memory on NT is the single most important area to look at first. Since NT is a 32 bit operating system, it can address 4GB of memory, 2GB of which are reserved for the operating system. Thus there is a maximum of 2GB available to applications (including Oracle). Oracle’s memory consumption is primarily a function Of the Shared Global Area or SGA. We will discuss how to change the SGA later, but to manage memory use, you need to ensure the SGA must fit into physical memory, and you have a large enough page file. If your system is consuming more memory that is physically available, you will observe a phenomenon know as paging. You can isolate paging activity by placing the page file on a separate volume. Using Perfmon, you can watch Memory: page faults/sec, pages input/sec and pages read/sec. If you are seeing more than 5 page faults per second over time, you have a paging problem. Another easy indication of paging can be found on the Performance Tab of the task manager, which shows Physical and Virtual memory use at a point in time. to address paging problems you must either reduce consumption (shrink SGA, remove unnecessary services/protocols etc.) and/or add more memory. Do not allow you system to continuously page fault!
 

 
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:

    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 (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.
      VERY IMPORTANT!!!  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.

Using the Oracle Database Instance Manager ORADIM80.EXE
You can use this program to control all aspects of Oracle services. For example, to shutdown the database use the command:
        oradim80.exe -shutdown -sid ORCL -usrpwd passwd -shuttype (srvc,inst) -shutmode I
where shutmode can be a-abort; i-immediate or n-normal.
 

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.
    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.
    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.

MULTIPLE SOFTWARE VERSIONS

    In Unix and NT, 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_HOMEis 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

 
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 Windows NT Performance Monitor 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.

There are more important tools under Control Panel, theyr are System and Networking.
System lets you:

Use Networking for:

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.
 

PERFORMANCE TUNING

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

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

 
General settings
NT provides settings that allow the O/S to "tune itself" to favor particular types of applications and usage. Since NT supports a wide range of network services (such as file serving, web-serving, application services, etc.), these settings allow the administrator to instruct the operating system to allocate resources to optimize their usage for a particular type of service. For function as an ORACLE database server, the following settings should be verified or enabled:

1) Optimize for network throughput. This setting instructs NT to allocate resources such that they are more available and useable to application processes that execute locally on the server. This setting defaults to "Optimize for file sharing", and modifying this setting will typically generate a performance improvement of 5-10% in the ORACLE instance. Note that this setting requires a reboot before it takes effect. This setting can be found in the following location:
START -> Settings -> Control Panel -> Network -> Services -> Server -> Properties -> Maximize throughput for network applications.

2) Minimize foreground boost. This setting allows NT Servers that perform additional duties as user workstations to allocate more resources to interactive sessions that are logged into the console, and defaults to 50%. Assuming this box is a dedicated database server, then this setting should be set such that interactive logins are not given priorities higher than the ORACLE services. Due to NT's architecture, certain administrative tasks may require console login, and it is undesirable for the database end-users to experience a performance degradation each time an interactive login occurs. This setting essentially minimizes a 5-10% degradation during interactive login sessions. Note that this setting requires a reboot before it takes effect. This setting can be found in the following location:
START -> Settings -> Control Panel -> System -> Performance -> Foreground boost = none.

3) DiskPerf. This setting causes disk performance objects and counters to be loaded at boot time. Without this setting toggled on, examining disk performance with NT Performance Monitor will result in all disk statistics being reported as zero. This setting incurs approximately a 5-10% performance overhead, but the information made available by enabling this setting will allow tuning analysis that it is otherwise not possible. If the disk performance information is used for bottleneck resolution, then having the information available to perform the analysis more than offsets the resulting performance loss, in the author's opinion. Note that this setting requires a reboot before it takes effect. Enable this setting from the command line by typing the following command:
diskperf -y (enables the objects and counters for logical and physical disks) or
diskperf -ye (enables the objects and counters for logical and physical disks plus individual disk drives in a stripped array)

Identifying Operating System Bottlenecks
As stated previously, there's essentially only three areas of the operating system to check for performance bottlenecks: CPU, memory, and disk. NT Performance Monitor provides easy access the objects and counters storing the necessary information. Note that updating the GUI interface provided by PM is a fairly expensive process, although polling the object and counter information itself is reasonably inexpensive. For this reason, adopt one of the two following practices:

1) Run NT Performance Monitor from a remote workstation rather than at the console of the database server. PM can easily access performance information on a remote server, so remove the graphical workload from the database server to prevent the reported values from being over-inflated by the expensive graphical updates.

2) Install the DATALOG.EXE service on the database server, and control the service using the MONITOR.EXE command. The DATALOG.EXE (included in the NT Resource Kit) is the service-version of PM without the graphical components. It appears as "Monitor service" on the Control Panel-Services applet, it's memory and processor footprint are fairly light, and it accepts a performance monitor PMW file as it's data collection configuration information. The configuration information includes the objects and counters to poll, the polling interval, and the PM logfile in which to record the performance data. This logfile of information can then be analyzed offline. Performance data regularly collected with this method also proves useful for constructing usage growth profiles to anticipate future requirements.
The following approach is organized in order of resources easiest to analyze and fix addressed first, and will provide effective results. Check CPU first, since that resource is the easiest to monitor, and typically will be the least costly to rectify. If CPU is not the bottleneck, then examine memory. Although a bit more complicated to analyze, a memory shortage is still reasonably inexpensive to fix. Memory should be examined before disk because a memory shortage will inflate the volume of disk access. If memory is not the bottleneck, then examine disk. If none of these resources appear to be the bottleneck, then instance level initialization parameters should be carefully examined, with assistance from ORACLE support if needed.

1) Check CPU utilization.
Counter: % Total Processor Time
Scrutinize average and maximum utilization. When the maximum utilitization reaches 100%, the CPU's are the bottleneck. If the CPU's consisently reach a maximum utilization of 100%, then perform additional analysis to determine what percentage of the time they are pegged at 100%. If they reach 100% only 1% of the time, but are below the 100% mark 99% of the time, then they are the bottleneck only 1% of the time. If CPU's are at or near 100% any portion of the time, then the DBA should plan ahead and budget the purchase of additional CPU resources before they are actually needed. If the CPU's never reach a max utilization of 100%, CPU is not the current bottleneck. Options for fixing a CPU bottleneck include:
- Add more CPU's
- Replace existing CPU's with processors that are faster and have larger processor caches.
Note that replacing 4 - 200Mhz Pentium Pro CPU's with 4 - 400Mhz Pentium Xeon processors is likely to result in greater overall improvement than adding 4 additional 200Mhz Pentium Pro processors, given NT's scaleability characteristics.

2) Check memory.
Counter: Pages/sec
The simplest measurement of memory on NT is the rate of paging. Average and maximum paging should be examined, with additional analysis such as calculating the percentile divisions for particular time windows if maximum paging is excessive. Hit ratios for SGA memory structures (such as buffer cache) may be within the target range that should yield adequate performance (typically 80-95%, depending on a particular organization's requirements and expectations), but checking only SGA hit ratios may be misleading. NT virtual memory works such that the total memory available to an application (up to the maximum O/S limit) is the combination of physical memory and paged memory (ie. memory contents temporarily written to disk). Buffer cache hit ratio could read 95%, but if physical memory is being paged hundreds of times per second, a severe performance degradation will be experienced. Typically, the best balance on NT is 40-45% of the physical memory available on the server allocated to the SGA. A rate of memory paging greater than approximately 5 pages/second will result in exponential performance degradation. If SGA hit ratios are low, then increase the memory allocated to the necessary cache. If SGA hit ratios are high but the O/S is paging more than the suggested minimum, then add more physical memory to the server. Ideally, the DBA should plan ahead and budget the purchase of additional memory before hit ratios and paging becomes a significant bottleneck. If the SGA hit ratios are within the target range and O/S paging is minimal, then memory is not the bottleneck. Note that it is possible to allocate too much memory to the SGA: in this scenario, the SGA hit ratios will be excellent, but paging will occur at a rate than more than offsets the better hit ratios, thus making overall performance slower than it would be with lower SGA hit ratios. The objective for memory should be balancing between SGA hit ratios and O/S paging. Options for fixing a memory shortage include:
- Adjusting SGA memory allocations to balance the ORACLE hit ratios and O/S paging.
- Add physical memory to the server.

3) Check disk.
Counters (for each logical disk): Disk transfers/sec and Avg Disk sec/transfer
Examine the workload imposed upon the disks used by the ORACLE instance. Logical disks, whether single physical disks or disk arrays, have a workload threshold that they can sustain without performance degradation; after that workload threshold is exceeded, read and write performance will degrade exponentially. Throughput is defined as the rate of data transfer, while workload is defined as the rate of I/O requests. In practice, the rate that data is read or written is relatively constant, given that the disk rotates at a constant speed. Because of the characteristic mechanical and rotational latencies, finding the data on disk can take as long or longer than performing the actual transfer, particularly in cases (like database systems) where the nature of disk I/O is more random than sequential. With this attribute in mind, it should be noted that workload is the most likely candidate to overload a disk's performance capacity. As the workload threshold (ie. transfers/second) of the disk or disk array is exceeded the amount of time to complete each request (seconds/transfer) will increase exponentially. Most hot-swappable SCSI disk is capable of sustaining around 60-80 transfers/second, depending upon the particular model and vendor. (Contact your hardware vendor to obtain these attributes specific to your particular disk drive models.) With this information available for each disk, the approximate workload threshold can be calculated for disk arrays visible to the O/S as single logical disks. (Please see the article on RAID) For instance, if we have a disk array that has a workload threshold of 180 transfers/second, the seconds/transfer should be in the millisecond range when the actual workload is below this limit. When this workload threshold becomes drastically exceeded, the seconds/transfer will increase into the tens or even hundreds of milliseconds. Unfortunately, I/O-bound situations are typically the most difficult and costly to fix. Options include:
- Balancing the disk I/O over multiple disks or disk arrays/controllers using information obtained from the ORACLE instance's BSTAT/ESTAT reports (please see the article on instance tuning for more information).
- Add physical disks individually or to arrays to increase the workload threshold.
- Add more independent disk arrays and controllers to the database server.
- Change the selected RAID technology (RAID5 to RAID1 or RAID10, non-RAID to RAID0, etc.).
- Replace disks or arrays with faster disk and/or array controllers.
- If SGA hit ratios are below ideal, add a significant volume of memory to reduce disk usage. Note that the higher the SGA hit ratios already are, the less effective this approach will be.
 

Isolation from other application services and environmental variables
One of the most effective techniques for improving the availability of a given ORACLE on NT instance is to isolate the database service from other application services. A production ORACLE instance with requirements to support more than a handful of concurrent users and/or with stringent availability requirements should be run on a dedicated database server. Below are details of specific steps that can be implemented.

1) Do not run the ORACLE instance on a primary or backup domain controller. Services on the PDC or BDC can impact the ORACLE instance, as well as the ORACLE instance having a undesired impact on the PDC.

2) Limit file service access to the database server. Only the DBA's and system or network administrators (plus perhaps a few application developers) need access to the file system. End users should not be able to store documents, spreadsheets, etc., on the file system of the database server. File serving and sharing on the database server absorbs computing resources as well as giving end users an opportunity to accidentally damage data files, executables, scripts, etc., used by the database instance, unless the file system security is tightly managed. In this case, the best course of action is avoidance. There may be lots of space available on the database server, but the assumption should be made that the database will need most or all of that space at some future point in time.

3) Limit print services on the database server. There will likely be very few cases where print services from database server are actually needed. Eliminate this overhead and any associated stability risks by refraining from using the print services through the database server.

4) Limit console login authority. Only the DBA's and system administrators need login authority to the database server. Console login privileges provide the ability to accidentally start or stop a variety of services that can impact the ORACLE instance, as well as the ability to perform a shutdown or kill operating system processes. All of these capabilities can be secured, but again the best policy is avoidance. If a well-meaning user can't accidentally break something, then he or she won't.

5) Secured physical location. The database server should be in a location that is inaccessible to everyone except the administration personnel. Ideally, this physical location should have a sufficient UPS in the event of power failure, and a fire containment system. The database server should not be physically located such that an end-user could accidentally trip over the power cord or network cabling while walking past the server.

6) One ORACLE instance and version per box. Since multiple application services compete for computing resources, only one database instance should be run per box. A database engine will attempt to use all available CPU, memory, and disk workload resources when necessary. Eliminate this competition and any instability that it generates by running only a single instance on each box, especially if these are production instances. Ideally, there should be at least two seperate database servers: one for production, and one for development. This seperation prevents development mishaps, upgrades, etc., from impacting a production instance. In some cases, a quality assurance instance is also justifiable. A good rule of thumb is to keep the development server at about half of the throughput capacity of the production server. In this way, potential performance problems are more likely to be noticed before the application goes into production.
ORACLE8 now provides the capability to have multiple versions installed on the same box through the use of multiple ORACLE_HOMES. This feature can be useful for upgrade activity, but should not be taken as an encouragement to run multiple instances (of the same or a different version) on the same box. The inability to have multiple ORACLE7 versions safely installed on the same NT box constraints the ability to apply a patch to a development instance for testing without automatically applying that same patch to all other instances on the same box. Seperation of instances not only opens up better maintenance windows, it also removes any instabilities resulting from contention and competition of instances.

7) Limit non-ORACLE software, applications, and processes. Due to the nature of Microsoft's Component Object Model, it's shared components, and the object resolution provided by the registry, it is entirely possible to install a non-ORACLE software component that damages an ORACLE-related software component or registry entry. A good rule of thumb is to assume that different applications will not run on the same box until you have explicitly proven that assumption to be false. Whether the non-ORACLE application is a commercial product or a custom creation, it's peaceful co-existence should at least be tested with a non-production instance before installing on a production instance. Ideally, this application or component should be run on a server seperate from the ORACLE database, even if it connects to and does work with the ORACLE instance. SQL*Net/NET8 provides the transport layer that makes the physical location of the ORACLE instance completely transparent to any application, so there is absolutely no reason that a server-side application that interacts with an ORACLE instance has to be installed and run on the same box, regardless of whether it is a middle-tier component, a web-server based component, etc.

8) Do not run a multi-server backup solution from the database server. Although this temptation is appealing because the database server will attain higher throughput on backups with the backup solution physically connected to and run from the database server, it can cause instabilities through competition for resources, and it generally severely limits the window of opportunity for server or database maintenance. Essentially, offline corrections and administrative work cannot be done during the day because of user workload, and cannot be done at night because the database server and other servers are being backed up by the database server. This is especially true of production instances, and can also easily become true of development instances. Avoid this particular temptation, unless the backup apparatus is dedicated only to the database server.
Isolation from other application services and environmental variables
One of the most effective techniques for improving the availability of a given ORACLE on NT instance is to isolate the database service from other application services. A production ORACLE instance with requirements to support more than a handful of concurrent users and/or with stringent availability requirements should be run on a dedicated database server. Below are details of specific steps that can be implemented.
 
 

WINDOWS REGISTRY

The Windows NT registry is a central location for programs to store initialization and configuration information.Oracle uses this facility to store various configuration setting. You can change these using the REGEDT32.EXE program, however "use at your own risk" as you can completely toast your system if you mess up. Having said that, it is often necessary to change some of the settings.

Under the HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE key. Some entries you might want to change are:
 


Registry Key

Value

Comments

ORA_%SID%_PWFILE %ORACLE_HOME%\DATABASE

Change location of PASSWORD file for Connect Internal

ORA_%SID%_SHUTDOWN

TRUE or FALSE

TRUE cause shutdown immediate when service terminates

ORA_%SID%_SHUTDOWN_TIMEOUT

Number Seconds to wait for timeout -numbers < 30 are ignored

ORACLE_SID

SID

Default SID

SQLPATH %ORACLE_HOME%\SQLPLUS

Directory to search for SQL files

There are a few more under the HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Oracle80\Performance\Oracle%HOME%80
 

SECURITY

Database security on for Oracle on Windows NT is exactly like Oracle for other platforms and will not be covered in this paper. Two main tasks that you will want to do when setting up Oracle security under NT are:

The starter database for installed by Oracle is not secure and should be modified as follows

  1. Change the SYS and SYSTEM Password

  2. Change the INTERNAL or DBA Password
  3. Protect Oracle REGESTRY ENTRIES from view/modification
  4. Protect STRTSID.CMD from viewing
  5. Protect ORACLE EXECUTABLES from spoofing or deletion
  6. Protect ORACLE datafiles, redo logs, control files from deletion or unauthorized capture
  7. Protect EXPORTS and BACKUPS deletion or unauthorized capture

The Identified Externally authorization mechanism is a means for Oracle users to connect to ORACLE without providing a password. Essentially, you tell Oracle that you trust the operating system to authenticate a user. In order to have this work some preparation is required. If correctly configured, you can provide the following
privileges:

You may use NT authentication across NT domains, or for LOCAL access. These examples will demonstrate the DOMAIN-based access.
For NT authentication to work:
1. You must modify the %ORACLE_HOME%\NET80\ADMIN\SQLNET.ORA file on the server and client to include the following line:
        SQLNET.AUTHENTICATION_SERVICES = (NTS)
2. You must modify the INITSID.ORA file to include the following line and shutdown/restart Oracle :
        OS_AUTHEN_PREFIX = ""
3. Use NT User Manager to create a USER for the domain
4. Set/Create HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEn\OSAUTH_PREFIX_DOMAIN to TRUE.
This optional step requires the Oracle username to include the DOMAIN (i.e. DBCORP\SCOTT)
5. Set/Create HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEn\OSAUTH_ENFORCE_STRICT to TRUE
This optional step requires any user to be a member of the LOCAL group ORA_USER or ORA_SID_USER.
You need to create these groups and assign the user.
6. Use SQLPLUS or SRVMGR to "create user DBCORP\SCOTT IDENTIFIED EXTERNALLY" and grant the user the roles you wish.
You should now be able to log into oracle with the connect string “Connect /@DBNAME” when you are connected to the NT server. Note that you must either LOG ON to a client computer with the correct USERNAME and Domain or access a shared area in the Windows NT server using the NET USE command or Windows Explorer in order to be authenticated under Windows NT. In order to connect as SYSOPER or SYSDBA without a password, you must create NT groups. Create the groups ORA_OPER or ORA_DBA for access to all instances, or ORA_SID_OPER and ORA_SID_DBA for specific instances on the NT server. Then add the NT user to these groups, and then you can "connect/@DBNAME AS SYSDBA". To CONNECT INTERNAL without as password, you must additionally add the following entry into the INIT.ORA file:
REMOTE_LOGIN_PASSWORD=NONE
Connect INTERNAL then will work from any Oracle tool if you are connected LOCALLY, but only Server Manager will work remotely. Finally, if you want to use NT to grant roles, it is important to know that you must use OS roles exclusively. You can't use a mixture of NT roles and Oracle roles. To enable this feature you need to put the following line in the INITSID.ORA:
OS_ROLES = TRUE
And then simply create the NT groups you want to assign roles to with the following naming convention:
ORA_SID_ROLENAME [_D] [_A]
Where rolename is the Oracle role you have created and the optional _A or _D indicate whether you want to have the role as DEFAULT or WITH ADMIN OPTION.
 

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 Ocopy 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.
                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"

rem -- List jobs in scheduling queue --
AT

rem -- Remove all jobs from scheduling queue --
rem AT /DELETE /YES

rem Note: The AT command does not utilize the PATH environment variable.
rem       Use full path name to the batch file and other commands

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

 

Notification On Windows
Many operating systems provide an intrinsic method for sending messages via email. However, one common irritation with Windows NT is the lack of a UNIX-like "sendmail" command. Luckily, there are utilities available to add this functionality.
Here are some Windows Utilities to send mails:
http://www.ipass.net/~davesisk/oont_management_scripting_2.htm
http://www.users.globalnet.co.uk/~jchap/tvde.htm
http://www.komaromi.com/dos_email/
http://home.concepts.nl/~fiber/dose.htm

In fact, one such command-line utility (MailTo.exe) is available as freeware. This particular MailTo command sends STMP mail, and can include text files as the message body or attachments, and binary files as attachments. The syntax is very simple, and since it is a command-line executable, calls to it can be included in BAT or CMD files or more advanced scripts. Below is a list of possible uses, some of which have examples in the scripting article of this section:

1) Scan the ORACLE alert log for errors using the FINDSTR command, and, if any errors are found, email them to a list of email addresses. A job such as this can be scheduled to execute every 60 minutes, for instance.
findstr /N "ORA- incomplete" C:\oracle\admin\DIE\bdump\dieALRT.LOG


2) Check services to make sure they are started, and, if not, send an email to operational personnel. Again, this type of job can be automated to run at short periodic intervals, such as every 15 minutes or once per hour. Consequently, the job could be constructed in a manner that attempts to restart the particular service, as well as mailing a note to an administrator.

3) Scan the spooled output from a hot backup for errors using FINDSTR, then email any errors that were found to an administrator. This functionality could be added to the job that runs the hot backup.

4) Scan the spooled output from a full export for errors using FINDSTR, and, if errors are found, send an email containing the error listing to a DBA. This functionality could be added to the job that actually runs the full export.

5) Perform a TNSPING or even connect to a database instance. If connection is not achieved, send an email to an administrator, and perhaps even attempt to restart the database instance if the connection error indicates the instance is not available. This type of job could be scheduled to run several times per hour, if necessary.

6) Spool the output of a DIR command, or the output of the DIRUSE command, to a text file and email this output to an administrator. This type of job can be used to monitor available disk space, and could be scheduled to run each morning, once per week, etc.

 

CLEANING A WINDOWS MACHINE

Section A (steps 1-6) describes the removal of all Oracle components on Windows NT,2000 and XP.
Section B (steps 1-4) describes the removal of all Oracle components on Windows 95 and Windows 98.

A. Removing Components on Windows NT/2000/XP.
To remove all Oracle components from a computer on Windows NT/2000/XP:
1. Check privileges:
1.a. Ensure you are logged in as a user with Administrator privileges.

2. Stop all Oracle services (if any are running):
2.a. NT: Choose Start > Settings > Control Panel > Services.
     2000,XP: Right click My Computer > Manage > Services and Applications > Services

2.b. If any Oracle services (their names begin with Oracle) exist and have the status Started, select the service and click Stop.

2.c. Click Close to exit the Services window.

2.d. Close the Control Panel/Computer Management window.


3. Remove the entries in the Windows registry:
3.a. Start the registry editor:
Choose Start > Run > regedit
Note: On Windows NT you can use regedt32 instead. The searching capabilities of regedt32 is limited compared to regedit. It will be needed to be able to edit 32-bit entries in the registry. Since we are not going to update any 32-bit entry it is not needed.

3.b. Go to HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
Note the value of the key INST_LOC, this is the location of the Oracle Universal Installer (OUI). The default location is C:\Program Files\Oracle\Inventory. If this value is different, make note of it, so we can delete these files later. Delete this ORACLE key.

3.c. Go to HKEY_LOCAL_MACHINE\SOFTWARE\ODBC and expand all subkeys and remove all keys under here which are related with the "Oracle ODBC Driver"

3.d. Go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services and remove all keys under here that begin with ORACLE or ORAWEB.

3.e. Go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\......\Application and remove all keys under here that begin with ORACLE.

3.f. Go to HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\......\Uninstall and remove any entries related to Oracle.

3.g. Go to HKEY_CLASSES_ROOT, remove all keys that begin with Ora or ORCL (e.g. Oracle..., ORADC..., ORAMMC..., OraOLE..., OraPerf... and ORCL...).

3.h. Close the registry.


4. Clean up the environment settings:
4.a. NT: Choose Start > Settings > Control Panel > System > Environment tab
     2000,XP: Choose Start > Settings > Control Panel > System > Advanced tab > Environment variables.

4.b. At "System Variables" click on the variable PATH in order to modify the value. For example, you may see a path similar to this one:
      C:\ORACLE\ORA81\BIN;C:\PROGRAM FILES\ORACLE\JRE\1.1.7\BIN

4.c. If an %ORACLE_HOME% was installed, remove this %ORACLE_HOME%\BIN path.

4.d. If JRE was installed by Oracle, remove the JRE path.

4.e. If there is a CLASSPATH variable under "System Variables", first make note of the path defined, then delete it. This variable can be added back at a later date if needed.

4.f. Check if there are any other Oracle variables set in "System Variables", ORACLE_HOME, ORACLE_SID, TNS_ADMIN, JSERV or WV_GATEWAY_CFG. If these exist, delete them also.

4.g. Click on APPLY and OK.

4.h. Close the Control Panel window.


5. Delete the software and icons:
5.a. NT: Choose Start > Programs > Windows NT Explorer.
     2000,XP: Choose Start > Programs > Accessories > Windows Explorer.

5.b. NT: Go to %SystemDrive%\WINNT\PROFILES\ALL USERS\START MENU\PROGRAMS
     2000,XP: Go to %SystemDrive%\DOCUMENTS AND SETTINGS\ALL USERS\......\START MENU\PROGRAMS

Note 1: These locations depend on whether OS was upgraded from NT, or this was a fresh install of 2000/XP.

Note 2: To locate your System Drive, type in DOS-box: echo %SystemDrive% and delete the following icons:
- Oracle Installation Products
- PRODUCT_NAME - HOME_NAME e.g.
Oracle for Windows NT - Dev6i
Oracle Reports 6i - Dev6i
Oracle Olap Client 2.2 - Dev6i
Oracle9i Lite
Oracle - OraHome92

5.c. Go to %SystemDrive%\Program Files\Oracle or the location of INST_LOC as noted earlier in step 3.b. and delete this directory.
Note: In order to successfully delete all files, you may have to reboot your computer first, in order to clear Operating System locks on those files.

5.d. Go to the temporary directory and delete all files and directories in here (see note in 5.c.).
     NT: %SystemDrive%\Temp
     2000,XP: %SystemDrive%\Documents and Settings\<username>\Local Settings\Temp\

5.e. Go to the drive where the Oracle software is installed on your machine and delete all ORACLE_BASE directories on your hard drive (see note in 5.c.).

5.f. Close the Windows Explorer / Windows NT Explorer.


6. Finish the removal:
6.a. Empty the recycle bin Right click on recycle bin > Empty Recycle Bin.

6.b. Reboot your computer.

6.c. Optionally: If you are on Windows 2000 or XP run the System Defragmenter utility:
- from Control Panel, select Administrative Tools > Computer Management
- expand Storage, then select Disk Defragmenter
- highlight each virtual drive, in turn, and click Defragment
- reboot your computer when finished.


B. Removing Components on Windows 95 or Windows 98.
To remove all Oracle components from a computer on Windows 95/Windows 98:

1. Remove the entries in the Windows registry:
1.a. Start the registry editor at the MS-DOS command prompt:
     C:\> REGEDIT

1.b. Go to HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
Note the value of the key INST_LOC, this is the location of the Oracle Universal Installer. The default location is C:\Program Files\Oracle\Inventory
If this value is different, make note of it, so we can delete these files later. Delete this ORACLE key.

1.c. Go to HKEY_LOCAL_MACHINE\SOFTWARE\ODBC and expand all subkeys and remove all keys under here which are related with the "Oracle ODBC Driver".

1.d. Go to:
HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\Uninstall and remove any entries related to Oracle.

1.e. Go to HKEY_CLASSES_ROOT, remove all keys that begin with Ora or ORCL (e.g. Oracle..., ORADC..., ORAMMC..., OraOLE..., OraPerf... and ORCL...).

1.f. Close the registry.


2. Clean up the environment settings:
2.a. Open your AUTOEXEC.BAT with an editor to modify the PATH variable. For example, you may see a path similar to this one:
C:\ORACLE\ORA81\BIN;G:\PROGRAM FILES\ORACLE\JRE\1.1.7\BIN .

2.b. If an %ORACLE_HOME% was installed, remove this %ORACLE_HOME%\BIN path.

2.c. If there is a CLASSPATH variable set in the AUTOEXEC.BAT, first make note of the path defined, then delete it. This variable can be added back at a later date if needed.

2.d. If JRE was installed by Oracle, remove the JRE path.

2.e. Save the AUTOEXEC.BAT file and close the edit.


3. Delete the software and icons:
3.a. Choose Start > Programs > Windows Explorer.

3.b. Go to SYSTEM_DRIVE:\WINDOWS\START MENU\PROGRAMS and delete the following icons:
- Oracle - HOME_NAME (where HOME_NAME is the previous Oracle home name.)
- Oracle Installation Products

3.c. Go to SYSTEM_DRIVE:\Program Files\Oracle or the location of INST_LOC as noted earlier in step 1.b and delete this directory.

3.d Go to SYSTEM_DRIVE:\Temp and delete all files and directories in here.

3.e. Go to the drive where the Oracle software is installed on your machine and delete all ORACLE_BASE directories on your hard drive.

3.f. Remove any Oracle-related .INI files that may exist in the Windows directory. The Windows directory may be found by entering "echo %WINDIR%" from a command prompt. Typical Oracle .INI files include ORADIM73.INI, ORADIM80.INI,
   ORACLE.INI, ORAODBC.INI

3.g. Close the Windows Explorer.


4. Finish the removal:
4.a. Empty the recycle bin
     Right click on recycle bin > Empty Recycle Bin
4.b. Reboot your computer.


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.
    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).
      You can obtain a binary distribution of the popular Perl scripting language from http://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.
 
 

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 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

Last thoughts

A useful utility is called CYGWIN, which is an Unix-like shell that can be used to run shell scripts on NT. BASH is distributed via the GNU for Win32 project and is able to run most shell scripts and other Unix utilities. You can use BASH to run your existing Unix shell scripts without porting them to CMD files.

One thing to be aware of is that Windows NT has a default behavior that is different from Unix for running scripts. In Unix, if you call a HOST command on SQL*PLUS, your scripts waits for return before executing the next HOST command. With NT, your calling script will continue without waiting and this can cause problems if commands need to be executed in order. To get around this in your scripts, you can use the command-line SQLPLUS and call the programs with the following syntax:
    HOST start/wait PROGRAM.EXE
Or
    HOST start CALL PROGRAM.EXE