On all these systems, Oracle implements a common architecture that includes the following components:
An area of memory available to all Oracle sessions, known as
the system global area (SGA). This area of memory includes
recently accessed data blocks (the buffer cache), SQL and PL/SQL
objects (the library cache), and transaction information (the redo
log buffer). The SGA may also contain session information.
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.
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!
In the Unix environment, Oracle instances are
usually started from the Oracle Server Manager or via the dbstart
script distributed with Oracle software. During Oracle startup, the
shared memory segments for the SGA are allocated and the background
processes created. The background processes immediately "daemonize" by
disassociating themselves from the login session and terminal so that
they can continue to run after the login session disconnects.
Under Windows NT, a process running independently of
a login session must be configured as a service. Consequently, each
Oracle instance is associated with one or more NT services. The most
significant services include:
OracleServiceSID. This service must be defined for each Oracle
instance on the NT server. SID represents the instance identifier; in
other words, OracleServiceABC1 represents the instance ABC1. Starting
this service creates the Oracle process.
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.
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.
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
Monitoring performance, diagnosing performance
bottlenecks, and performance tuning are ongoing tasks for most DBAs.
While the methodology for performing these tasks in NT is similar to
Unix, the tools may initially be unfamiliar.
Under Unix, the two most commonly used tools for
monitoring performance at the operating system level are sar
and top. Sar collects and reports on a variety of system
performance metrics while top reports on processes that consume the
most CPU. Under NT, the equivalent tools are the Task Manager
and Performance Monitor.
You can invoke the Task Manager
by issuing the Control-Alt-Delete key sequence and selecting "Task
Manager." The applet includes three tabbed pages:
The application list tracks open windows and applications that
are currently running.
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:
Prevent a performance boost for any foreground application that
may also be running
Use Networking for:
Choose "Maximize Throughput for Network Applications" for the
Server service
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 principles for Oracle in an NT environment are fundamentally the same principles that govern Unix tuning. They include:
Tuning SQL and the application design
There are, however, a few NT-specific considerations:
Windows NT supports asynchronous I/O; configuring multiple db
writers is not required.
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.
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
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:
· tighten security for the "starter database", and
The starter database for installed by Oracle is not secure and should be modified as follows
Change the SYS and SYSTEM Password
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:
Connecting without a password as a 'normal' user
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.
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.
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.
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.
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.
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
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