TROUBLESHOOTING GUIDE
Common Performance Tuning Issues
Table of Contents
1. Introduction
2. Shared Pool and Library Cache Performance Tuning
3. Buffer Cache Performance Tuning
4. Latch Contention
5. Redo Log Buffer Performance Tuning
6. Rollback Segment Performance Tuning
7. Temporary Tablespace Performance Tuning
8. Checkpoint Performance Tuning
9. Query Performance Tuning
10. Import Performance Tuning
11. STATPACK Utility
12. Utlbstat/Utlestat Utility
1. Introduction
This document covers some of the most common tuning scenarios. More specific
information on each tuning area is available through the links provided.
2. Shared Pool and Library Cache Performance Tuning
Oracle keeps SQL statements, packages, object information and many other items
in an area in the SGA known as the shared pool. This sharable area of memory
is managed as a sophisticated cache and heap manager rolled into one. It has 3
fundamental problems to overcome:
- The unit of memory allocation is not a constant. Memory
allocations from the pool can be anything from a few bytes to
many kilobytes
- Not all memory can be 'freed' when a user finishes with, as the
aim of the shared pool is to maximize sharability of
information.
- There is no disk area to be able to page out to so this is not
like a traditional cache where there is a file-backing store.
Only "recreatable" information can be discarded from the cache
and it has to be re-created when it is next needed.
Here are some tips in tuning the shared pool:
· Flushing the Shared Pool will coalesce small chunks of memory. When
the shared pool is highly fragmented, this may temporarily restore
performance. To flush the shared pool issue: alter system flush shared_pool;
Please note that executing this statement will cause a spike in performance
while the objects are reloaded and should be done when the database is not
being heavily used.
· Make sure that OLTP application uses "bind variables".
This is not that important for DSS.
· Make sure that the library cache pinhitratio is > 95%
. Increasing the size of the shared pool is not always the answer for poor
hitratios.
3. Buffer Cache Performance Tuning
The database buffer cache holds copies of data blocks read from disk.
Since the cache is usually limited due to memory constraints, all the data on
the disk cannot fit in the cache. When the cache is full, subsequent cache
misses cause Oracle to write data already in the cache to disk. A subsequent
access to the data written to disk results in a cache miss.
Here are some tips in tuning the buffer cache:
· Enable Buffer Cache Advisory in order to size your Buffer Cache correctly.
Avoid the following
· 'cache buffers lru chain' latch contention
· Large "Average Write Queue" length
· Lots of time spent waiting for "write complete waits"
· Lots of time spent waiting for "free buffer waits"
4. Latch Contention
Latches are low level serialization mechanisms used to protect
shared data structures in the SGA. A latch is a type of a lock
that can be very quickly acquired and freed. The implementation
of latches is operating system dependent, particularly in regard
to whether a process will wait for a latch and for how long.
Here are some of the important latches to tune:
- Redo Copy/Allocation Latch
- Shared Pool Latch
- Library Cache Latch
5. Redo Log Buffer Performance Tuning
LGWR writes redo entries from the redo log buffer to a redo log file.
Once LGWR copies the entries to the redo log file the user process can
over write these entries. The statistic "redo log space requests" reflects
the number of times a user process waits for space in the redo log buffer.
Here are some tips in sizing the redo logs:
· The value of "redo log space requests" statistic in v$sysstat should be near 0.
· Size your redo appropriately. The recommendation is to have the redo
log switch every 15-30 minutes.
Using the options UNRECOVERABLE in Oracle7 and NOLOGGING in Oracle8 you can avoid
redolog entries generation of certain operation to improve the performance.
Operations like: index creation, create table as select,SQL*Loader operation, etc.
can be easily rebuild without having redolog entries available.
6. Rollback Segment Performance Tuning
The Oracle database provides read consistency on rows fetched for
operations such as SELECT, INSERT, UPDATE, and DELETE against any
database object. Rollback segments are used to store undo transactions
in case the actions need to be "rolled back" or the system needs to
generate a read-consistent image from an earlier time.
Here are some tips in sizing the rollback segments:
· It is recommended to have at least 1 rollback segment for every 4
transactions.
· One large rollback segment is recommended for long running queries.
7. Temporary Tablespace Performance Tuning
In RDBMS release 7.3, Oracle introduced the concept of a temporary
tablespace. This tablespace would be used to hold temporary objects,
like sort segments. Sort segments take their storage parameters from
the DEFAULT STORAGE (NEXT) clause of the tablespace in which they reside.
Here are some tips in tuning the temporary tablespace:
· If there is a lot of contention for the Sort Extent Pool latch, even in
the stable state, then you should increase the extent size by changing
the NEXT value of the DEFAULT STORAGE clause of the temporary tablespace.
· If there is a lot of contention for the Sort Extent Pool latch and if the
wait is the result of too many concurrent sorts, you should increase the
SORT_AREA_SIZE parameter so that more sorts stay in memory.
. It is recommended to have the extent size equal to sort_area_size.
Here is an example why. Say your extent size = 500K and sort_area_size = 1Mg.
Now if there is a sort to the disk, it aquires 2 extents of 500K each and
this could cause performance degradation.
8. Checkpoint Performance Tuning
A Checkpoint is a database event, which synchronizes the data blocks in memory
with the datafiles on disk. A checkpoint has two purposes:
(1) to establish data consistency, and
(2) Enable faster database recovery.
When a checkpoint fails messages must be verified into into the alert.log file.
Here are some tips to tune the checkpoint process:
· The CKPT process can improve performance significantly and decrease the
amount of time users have to wait for a checkpoint operation to complete.
· If the value of LOG_CHECKPOINT_INTERVAL is larger than the size of the redo
log, then the checkpoint will only occur when Oracle performs a log switch
from one group to another, which is preferred. There has been a change in
this behaviour in Oracle 8i.
· The LOG_CHECKPOINTS_TO_ALERT when set to TRUE allows you to log checkpoint
start and stop times in the alert log. This is very helpful in determining
if checkpoints are occurring at the optimal frequency
. Ideally checkpoints should occur only at log swiches.
9. Query Performance Tuning
If queries are running slow consider the following:
· How fast do you want the query to run and is it a reasonable request?
· What is the OPTIMIZER_MODE set to?
· Are all indexes involved in the query valid?
· Is there any other long running query on the database?
In case of CBO:
· Are there statistics on the tables and indexes?
· Were the statistics computed or estimated?
Here are the 2 main diagnostic tools used for query performance tuning
- TKPROF
- AUTOTRACE
10. Import Performance Tuning
There is very little consolidated information on how to speed up import when it
is unbearably slow. Obviously import will take whatever time it needs to
complete, but there are some things that can be done to shorten the time it
will take.
11. STATPACK Utility
The STATPACK utility is the next generation of the Utlbstat/Utlesta report which
helps the database administrator to gather statistical information to detect
performance problems. Statspack improves on the existing UTLBSTAT/UTLESTAT performance
scripts collecting more data, including high resource SQL, pre-calculating some ratios,
such as cache hit ratios, per transaction and per second statistics, keeping a
permanent repository which makes historical data comparisons easier and separating
data collection from the report generation.
12. Utlbstat/Utlestat Utility
Bstat/Estat is a set of sql scripts located under your $ORACLE_HOME/rdbms/admin
directory that are useful for capturing a snapshot of system wide database
performance statistics. UTLESTAT creates a second snapshot of these views and
reports on the differences between the two snapshots to a file called
'report.txt'.
Bstat.sql creates a set of tables and views in your sys account, which contain
a beginning snapshot of database performance statistics.
Estat.sql creates a set of tables in your sys account, which contain an ending
snapshot of the database performance statistics and them to a file called
'report.txt'.
Here are some tips:
· Make sure that you have TIMED_STATSTICS set to TRUE (this adds only a very
small overhead to database operations).
. Make sure that the database is up and running for a while before running
utlbstat.
· Run the utbstat.sql and the utlestat.sql from svrmgrl and not sql*plus.
. Make sure that the database is not shutdown while the utlbstat/estat scripts
are running, otherwise the statstics generated are not accurate.
· Run utlbstat/estat at least for 1-3hrs during the period you are trying to
tune the database.
Sunday, August 20, 2006
Performance Tuning Approaches on Oracle and UNIX
Performance Tuning Approaches on Oracle and UNIX
As a system administrator, you will often be confronted with users who say that the response time on the system is unacceptable. What is unacceptable performance? There are two sides to this:
* Quantifiable performance
* Unmeasurable performance like user dissatisfaction.
This paper will help identify quantifiable performance problems and methods to solve them. When performance-tuning databases, it is a good to split the problem into parts to facilitate the process. Performance tuning Oracle databases can be divided into three subcategories:
1. Application tuning
2. RDBMS tuning
3. UNIX system tuning
Each of the above categories has a different maximum possible effect on the performance of your ORACLE database.
1. Application Tuning
Application tuning is by far the most effective aspect for database tuning. You will need to tune your SQL statements since they intervolve both your application and database. To analyze your SQL statements you will need to:
1. Add the following lines to your init.ora file:
SQL_TRACE = true
TIMED_STATISTICS = true
2. Restart your database.
3. Run your application.
4. Run tkprof against the tracefile created by your application:
tkprof EXPLAIN=username/passwd
5. Look at formatted output of trace command and make sure that your
SQL statement is using indexes correctly. Refer to DBA guide for a list
of rules that the oracle optimizer uses when choosing a path for a SQL statement.
<<<<<< OUTPUT OF TKPROF FILE >>>>>>>
count = number of times OPI procedure was executed
cpu = cpu time executing in hundredths of seconds
elap = elapsed time executing in hundredths of secs
phys = number of physical reads of buffers (from disk)
cr = number of buffers gotten for consistent read
cur = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the OPI call
===========================================================
select * from emp where empno=7369
count cpu elap phys cr cur rows
Parse: 1 0 0 0 0 0
Execute: 1 0 0 0 0 2 0
Fetch: 1 0 0 219 227 0 2
Execution plan:
TABLE ACCESS (FULL) OF 'EMP'
===========================================================
select empno from emp where empno=7934
count cpu elap phys cr cur rows
Parse: 2 0 0 0 0 0
Execute: 2 0 0 0 0 2 0
Fetch: 2 0 0 0 2 0 2
Execution plan:
INDEX (RANGE SCAN) OF 'ALEX_INDX' (NON-UNIQUE)
===========================================================
When your query is returning less than 10% of the rows of a table and the table is a reasonably large table, you will want to index your query. Above is an example of the same query run twice. The first time the optimizer chose to do a full table scan on the table EMP. The second time an index was created called ALEX_INDX. The optimizer chose to use the index. On a table
with 1000 rows this should have resulted in a faster query.
Oracle uses a rule-based optimizer for its SQL statements. When a SQL statement is parsed, the optimizer decides which query path will be chosen. The optimizer can often times make mistakes or simply illustrate that your SQL statement was written incorrectly. You can refer to Chapter 2 of the Performance Tuning Guide for more information on SQL statement tuning. Page 19-17 of the V6 DBA guide has a listing of the query paths that the optimizer will use ranked by speed.
Other considerations for application tuning may include the actual design of your application. A common problem found in menu5/forms30 applications is the way in which one calls the other. Make sure that menu5 is calling forms30 directly and not through a UNIX system call. Calling forms30 through a UNIX system call has the effect of doubling the number of connections to the database and also doubling the load on the UNIX machine.
2. RDBMS Tuning
The default init.ora database configuration file is inadequate for large
RDBMS's. Tools for tuning the database are:
1. SQLDBA * Monitor
2. Bstat/Estat
3. V$ database tables.
By looking at SQLDBA * Monitor one can get a good idea of what is happening to the database at that moment. The first place to look is the IO display. This will display the CACHE HIT RATIO. The cache-hit ratio is the ratio of hits to misses in the SGA for data. The higher the ratio, the more data is being found in the SGA. This means that oracle will not have to do as many disk reads to retrieve information, thus saving time and CPU processing.
While a hit ratio of 1.00 would be ideal, it is more realistic to aim for achieving a hit ratio of about .80.DB_BUFFER values by setting the init.ora parameter
DB_BLOCK_LRU_EXTENDED_STATISTICS equal to the additional number of DB_BUFFERS you wish to add. You will then be able to see the additional number of cache hits that would occur if you add X number of database buffers. For more information on this you can refer to pg 3-20 of the database performance tuning guide.
After looking at the cache-hit ratio and adjusting the number of database buffers accordingly you can analyze FILE IO. This screen
in SQLDBA * Monitor will show IO to each oracle datafile. It allows you
to identify two problem areas:
1. Table scans (indicated by a low number of reads/s & high number of blks/R)
2. Heavily used tablespaces
Table scans indicate that your SQL statements might not be tuned correctly. If your query is returning less than 15% of the rows of a table you should be using indexes to query the tables. Secondly you will also be aware of which tablespaces are used heavily and perhaps consider moving them to a separate disk to balance the IO.
The Rollback screen can be very useful on an update or insert-heavy system. Whenever you are changing or inserting new data, you write the changes to a rollback segment until they are committed. If you have 30 transactions and 1 rollback segment, you will have contention for that rollback segment. This will incur a performance penalty. To make sure that this is not happening, verify that every rollback segment has a maximum of 4 active transactions using it during heavy updating of the database.
The table V$ROWCACHE also contains useful information for tuning. The following select statement can determine whether or not any of the init.ora parameters beginning with DC_XXXX should be raised. These parameters control the size of various data dictionary caches.
SELECT parameter,gets,getmisses,count,usage FROM sys.v$rowcache;
PARAMETER GETS GETMISSES COUNT USAGE
------------- ------- ---------- ---------- -----
dc_usernames 134 5 50 5
dc_columns 11772 288 300 300
The parameter dc_usernames has a very low number of cache misses (GETMISSES). We have allocated 50 username entries (COUNT) and are only using 5 (USAGE). There is no need to raise the value of dc_usernames.
We have allocated 300 DC_COLUMNS and we are using all 300. In addition, the number of GETMISSES or cache misses are high for the parameter DC_COLUMNS. In this case, it is recommended to increase the value of DC_COLUMNS to reduce the number of GETMISSES or cache misses. A cache miss can be a very expensive operation for oracle since it means that the data requested was not in cache and thus a recursive call had to be made. For more information on this you can refer to pg 3-13 of the v6 performance tuning guide.
3. UNIX System Analysis
It is useful to subdivide UNIX system analysis into three subcategories:
1. Memory
2. CPU
3. IO
1. MEMORY
One of the most common problems when running large numbers of concurrent users on UNIX machines is lack of memory. In this case, a quick review of UNIX memory management is useful to see what effect lack of RAM can have on performance. A UNIX machine has virtual memory: the total addressable memory range. Virtual memory is composed of RAM, DISK and SWAP space.
Generally, you will want to have the available SWAP space equal to 2 to 3 times the RAM.
How does UNIX use SWAP space? It uses two memory management policies: swapping and paging. Swapping occurs when UNIX transfers an entire process from RAM to a SWAP device. This frees up a large amount of RAM. Paging occurs when UNIX only transfers a "PAGE" of memory to the SWAP device. Only a tiny portion of a process might actually be "paged out" to a SWAP device. While swapping frees up memory - it is slower than paging. Paging generally is more efficient but does not allow for large amounts of memory to be freed simultaneously. Most UNIX systems today use a combination of paging and swapping to manage memory. Generally, you will see the following behavior:
* System lightly used: no paging or swapping occurs.
* System under a medium load: paging occurs as RAM memory runs low
* System under a very heavy load: paging stops and swapping begins.
When analyzing your UNIX machine, make sure that the machine is not swapping at all and at worst paging lightly. This indicates a system with a healthy amount of memory available. To analyze paging and swapping, use the following commands. Commands used in Berkeley UNIX based systems will be marked as BSD. Commands used in ATT system V will be marked as ATT.
1. vmstat 5 5 (BSD)
procs memory page disk faults cpu
r b w avm fre re at pi po fr de sr d0 s1 d2 s3 in sy cs us sy id
0 0 0 0 1088 0 2 2 0 1 0 0 0 0 0 0 26 72 24 0 1 98
Note: There are NO pageouts (po) occurring on this system. There are also 1088 * 4k pages of free RAM available (4 Meg). It is OK and normal to have page out (po) activity. You should get worried when the number of page ins (pi) starts rising. This indicates that you system is starting to page.
2. pstat -s (BSD)
12112k allocated + 3252k reserved = 15364k used, 37280k available
Note: pstat will also give you the amount of RAM and SWAP currently available on your machine.
3. sar -wpg 5 5 (ATT)
09:54:29 swpin/s pswin/s swpot/s pswot/s pswch/s
atch/s pgin/s ppgin/s pflt/s vflt/s slock/s
pgout/s ppgout/s pgfree/s pgscan/s %s5ipf
09:54:34 0.00 0.0 0.00 0.0 12
0.00 0.22 0.22 0.65 3.90 0.87
0.00 0.00 0.00 0.00 0.00
Note: There is absolutely no swapping or paging going on. (swpin,swpot,ppgin,ppgout).
4. sar -r 5 5 (ATT)
10:10:22 freemem freeswp
10:10:27 790 5862
This will give you a good indication of how much free swap and RAM you have on your machine. There are 790 pages of memory available and 5862 disk blocks of SWAP available.
2. CPU
Once you have monitored your systems available memory you will want to make sure the the CPU(s) are not being overloaded. Here is some general information about how processes get allocated CPU time. UNIX is a multi-processing operating system. That means that a UNIX machine has to manage and process multiple user processes simultaneously. UNIX does this in the same way that people wait in line to buy groceries.
When a process is ready to be processed by a CPU it will be placed on the waiting line or RUN-QUEUE. This is a queue of processes waiting to be run. Obviously there are limits within which one wants to keep the RUN-QUEUE size. Another factor of interest is the percentage of time the the CPU spends in user mode, system mode, or idle mode. Some commands that determine whether or not there is a CPU resource problem occurring:
1. vmstat 5 5 (BSD)
procs memory page disk faults cpu
r b w avm fre re at pi po fr de sr d0 s1 d2 s3 in sy cs us sy id
0 0 0 0 1088 0 2 2 0 1 0 0 0 0 0 0 26 72 24 0 1 98
Note: The CPU is spending most of its time in IDLE mode (id). That means that the CPU is not being heavily used at all! There are no processes that are waiting to be run (r), blocked (b), or waiting for IO (w) in the RUN QUEUE.
2. sar -qu 5 5 (ATT)
10:58:02 runq-sz %runocc swpq-sz %swpocc
%usr %sys %wio %idle
10:58:07 2.8 100
0 2 4 94
Note: The CPU is spending most (94%) of its time in idle mode. This CPU is not being heavily used at all. Two solutions to this are:
1. Obtain a faster processor
2. Use more CPU's.
Avoid overloading your CPU. Response time on your machine will suffer if it is overloaded. Try to keep the run queue 100% occupied and have less that 6 processes waiting to be run for one CPU. This changes as you add more CPU's or a faster CPU. You may also want to avoid the CPU spending most of its time (more than 50%) in system mode. This may indicate that you are spending too much time in kernel mode servicing interrupts, swapping processes etc.
3. I/O
The last step in analyzing your UNIX machine is taking a look at IO. After having looked at SQLDBA monitor to see which datafiles are being used heavily you may also want to take a look at what UNIX says about file IO. These commands are for analyzing file IO on file systems.
1. iostat -d 5 5(BSD)
sd1 sd3
bps tps msps bps tps msps
1 0 0.0 4 0 0.0
iostat will display the number of kilobytes transferred per second, the number of transfers per second, and the milliseconds per average seek. In the example above, both SD1 and SD3 are not used heavily at all. BPS rates over 30 indicate heavy usage of a particular disk. If only one disk shows heavy usage, consider moving some of your datafiles off it or striping your data across several disks.
2. sar -d 5 5 (ATT)
09:17:20 device %busy avque r+w/s blks/s avwait avserv
09:17:26 iop0/pdisk010 472.45 1.18 9 107 39.26 512.66
iop0/pdisk000 18.43 2.66 8 132 31.36 24.10
iop0/pdisk020 317.08 1.11 11 165 31.95 294.02
iop0/pdisk021 590.88 1.34 27 518 96.26 219.96
iop0/pdisk040 34.94 1.64 18 113 43.70 19.58
iop0/pdisk041 45.33 1.17 20 79 3.73 22.89
Note: The "-d" option reports activity for each block device, The following is an explanation on the output. %busy, avque - portion of time device was busy servicing a transfer request, average number of requests outstanding during that time; r+w/s, blks/s - number of data transfers from or to device, number of bytes transferred in 512-byte units; avwait, avserv - average time (in ms) that transfer requests wait idly on queue, and average time to be serviced. There is a relationship between the number of blocks transferred per second and the average wait time. You can use this to identify which disks are heavily used and which are
underutilized.
3. sar -b 5 5 (ATT)
15:52:57 bread/s lread/s %rcache bwrit/s lwrit/s %wcache pread/s pwrit/s
15:53:12 0 2 90 1 2 38 0 0
Note: The "-b" option indicates the overall health of the IO subsystem. The %rcache should be greater than 90% and %wcache should be greater than 60%. If this is not the case, your system may be bound by disk IO. The sum of bread, bwrit, pread, and pwrit gives a good indicator of how well your file subsystem is doing. The sum should not be greater than 40 for 2 drives and
60 for 4-8 drives. If you exceed these values, your system may be IO bound. For more information on this refer to pg 2-22 of the Oracle V7 technical reference guide.
When analyzing disk IO, make sure that you have balanced the load on your system. Here is a "wish" list of steps for designing a disk layout for Oracle:
1. Make sure that your logfiles and archived logfiles are NOT on the same disk as your datafiles. This is a basic safety precaution against disk failure.
2. Put your files on raw devices.
3. Allocate one disk for the User Data Tablespace.
4. Place Rollback, Index, and System Tablespaces on separate disks.
5. Consider using Raid level 5 disk striping to stripe your datafiles across separate disks. You can also use the above-mentioned UNIX commands to monitor the IO on your system and identify problem areas.
Other tools for system administratation are platform-dependent. For example, HP and SEQUENT provide a facility called MONITOR that gives much of the above information in a graphical format. ATT SVR 4 also provides a product called GSAR (graphical sar). Look in your operating system (OS) documentation for additional monitoring commands. As a further
reference, consider "SYSTEM PERFORMANCE TUNING" published by O'Reilly and Associates.
Conclusion: There are many ways to approach Oracle performance issues. A structured approach like the one discussed above will allow the system administrator to systematically analyze her system and identify any problem areas. Once this has been accomplished, measures can be taken to correct problems. Performance is subjective, so find out what is expected.
As a system administrator, you will often be confronted with users who say that the response time on the system is unacceptable. What is unacceptable performance? There are two sides to this:
* Quantifiable performance
* Unmeasurable performance like user dissatisfaction.
This paper will help identify quantifiable performance problems and methods to solve them. When performance-tuning databases, it is a good to split the problem into parts to facilitate the process. Performance tuning Oracle databases can be divided into three subcategories:
1. Application tuning
2. RDBMS tuning
3. UNIX system tuning
Each of the above categories has a different maximum possible effect on the performance of your ORACLE database.
1. Application Tuning
Application tuning is by far the most effective aspect for database tuning. You will need to tune your SQL statements since they intervolve both your application and database. To analyze your SQL statements you will need to:
1. Add the following lines to your init.ora file:
SQL_TRACE = true
TIMED_STATISTICS = true
2. Restart your database.
3. Run your application.
4. Run tkprof against the tracefile created by your application:
tkprof
5. Look at formatted output of trace command and make sure that your
SQL statement is using indexes correctly. Refer to DBA guide for a list
of rules that the oracle optimizer uses when choosing a path for a SQL statement.
<<<<<< OUTPUT OF TKPROF FILE >>>>>>>
count = number of times OPI procedure was executed
cpu = cpu time executing in hundredths of seconds
elap = elapsed time executing in hundredths of secs
phys = number of physical reads of buffers (from disk)
cr = number of buffers gotten for consistent read
cur = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the OPI call
===========================================================
select * from emp where empno=7369
count cpu elap phys cr cur rows
Parse: 1 0 0 0 0 0
Execute: 1 0 0 0 0 2 0
Fetch: 1 0 0 219 227 0 2
Execution plan:
TABLE ACCESS (FULL) OF 'EMP'
===========================================================
select empno from emp where empno=7934
count cpu elap phys cr cur rows
Parse: 2 0 0 0 0 0
Execute: 2 0 0 0 0 2 0
Fetch: 2 0 0 0 2 0 2
Execution plan:
INDEX (RANGE SCAN) OF 'ALEX_INDX' (NON-UNIQUE)
===========================================================
When your query is returning less than 10% of the rows of a table and the table is a reasonably large table, you will want to index your query. Above is an example of the same query run twice. The first time the optimizer chose to do a full table scan on the table EMP. The second time an index was created called ALEX_INDX. The optimizer chose to use the index. On a table
with 1000 rows this should have resulted in a faster query.
Oracle uses a rule-based optimizer for its SQL statements. When a SQL statement is parsed, the optimizer decides which query path will be chosen. The optimizer can often times make mistakes or simply illustrate that your SQL statement was written incorrectly. You can refer to Chapter 2 of the Performance Tuning Guide for more information on SQL statement tuning. Page 19-17 of the V6 DBA guide has a listing of the query paths that the optimizer will use ranked by speed.
Other considerations for application tuning may include the actual design of your application. A common problem found in menu5/forms30 applications is the way in which one calls the other. Make sure that menu5 is calling forms30 directly and not through a UNIX system call. Calling forms30 through a UNIX system call has the effect of doubling the number of connections to the database and also doubling the load on the UNIX machine.
2. RDBMS Tuning
The default init.ora database configuration file is inadequate for large
RDBMS's. Tools for tuning the database are:
1. SQLDBA * Monitor
2. Bstat/Estat
3. V$ database tables.
By looking at SQLDBA * Monitor one can get a good idea of what is happening to the database at that moment. The first place to look is the IO display. This will display the CACHE HIT RATIO. The cache-hit ratio is the ratio of hits to misses in the SGA for data. The higher the ratio, the more data is being found in the SGA. This means that oracle will not have to do as many disk reads to retrieve information, thus saving time and CPU processing.
While a hit ratio of 1.00 would be ideal, it is more realistic to aim for achieving a hit ratio of about .80.DB_BUFFER values by setting the init.ora parameter
DB_BLOCK_LRU_EXTENDED_STATISTICS equal to the additional number of DB_BUFFERS you wish to add. You will then be able to see the additional number of cache hits that would occur if you add X number of database buffers. For more information on this you can refer to pg 3-20 of the database performance tuning guide.
After looking at the cache-hit ratio and adjusting the number of database buffers accordingly you can analyze FILE IO. This screen
in SQLDBA * Monitor will show IO to each oracle datafile. It allows you
to identify two problem areas:
1. Table scans (indicated by a low number of reads/s & high number of blks/R)
2. Heavily used tablespaces
Table scans indicate that your SQL statements might not be tuned correctly. If your query is returning less than 15% of the rows of a table you should be using indexes to query the tables. Secondly you will also be aware of which tablespaces are used heavily and perhaps consider moving them to a separate disk to balance the IO.
The Rollback screen can be very useful on an update or insert-heavy system. Whenever you are changing or inserting new data, you write the changes to a rollback segment until they are committed. If you have 30 transactions and 1 rollback segment, you will have contention for that rollback segment. This will incur a performance penalty. To make sure that this is not happening, verify that every rollback segment has a maximum of 4 active transactions using it during heavy updating of the database.
The table V$ROWCACHE also contains useful information for tuning. The following select statement can determine whether or not any of the init.ora parameters beginning with DC_XXXX should be raised. These parameters control the size of various data dictionary caches.
SELECT parameter,gets,getmisses,count,usage FROM sys.v$rowcache;
PARAMETER GETS GETMISSES COUNT USAGE
------------- ------- ---------- ---------- -----
dc_usernames 134 5 50 5
dc_columns 11772 288 300 300
The parameter dc_usernames has a very low number of cache misses (GETMISSES). We have allocated 50 username entries (COUNT) and are only using 5 (USAGE). There is no need to raise the value of dc_usernames.
We have allocated 300 DC_COLUMNS and we are using all 300. In addition, the number of GETMISSES or cache misses are high for the parameter DC_COLUMNS. In this case, it is recommended to increase the value of DC_COLUMNS to reduce the number of GETMISSES or cache misses. A cache miss can be a very expensive operation for oracle since it means that the data requested was not in cache and thus a recursive call had to be made. For more information on this you can refer to pg 3-13 of the v6 performance tuning guide.
3. UNIX System Analysis
It is useful to subdivide UNIX system analysis into three subcategories:
1. Memory
2. CPU
3. IO
1. MEMORY
One of the most common problems when running large numbers of concurrent users on UNIX machines is lack of memory. In this case, a quick review of UNIX memory management is useful to see what effect lack of RAM can have on performance. A UNIX machine has virtual memory: the total addressable memory range. Virtual memory is composed of RAM, DISK and SWAP space.
Generally, you will want to have the available SWAP space equal to 2 to 3 times the RAM.
How does UNIX use SWAP space? It uses two memory management policies: swapping and paging. Swapping occurs when UNIX transfers an entire process from RAM to a SWAP device. This frees up a large amount of RAM. Paging occurs when UNIX only transfers a "PAGE" of memory to the SWAP device. Only a tiny portion of a process might actually be "paged out" to a SWAP device. While swapping frees up memory - it is slower than paging. Paging generally is more efficient but does not allow for large amounts of memory to be freed simultaneously. Most UNIX systems today use a combination of paging and swapping to manage memory. Generally, you will see the following behavior:
* System lightly used: no paging or swapping occurs.
* System under a medium load: paging occurs as RAM memory runs low
* System under a very heavy load: paging stops and swapping begins.
When analyzing your UNIX machine, make sure that the machine is not swapping at all and at worst paging lightly. This indicates a system with a healthy amount of memory available. To analyze paging and swapping, use the following commands. Commands used in Berkeley UNIX based systems will be marked as BSD. Commands used in ATT system V will be marked as ATT.
1. vmstat 5 5 (BSD)
procs memory page disk faults cpu
r b w avm fre re at pi po fr de sr d0 s1 d2 s3 in sy cs us sy id
0 0 0 0 1088 0 2 2 0 1 0 0 0 0 0 0 26 72 24 0 1 98
Note: There are NO pageouts (po) occurring on this system. There are also 1088 * 4k pages of free RAM available (4 Meg). It is OK and normal to have page out (po) activity. You should get worried when the number of page ins (pi) starts rising. This indicates that you system is starting to page.
2. pstat -s (BSD)
12112k allocated + 3252k reserved = 15364k used, 37280k available
Note: pstat will also give you the amount of RAM and SWAP currently available on your machine.
3. sar -wpg 5 5 (ATT)
09:54:29 swpin/s pswin/s swpot/s pswot/s pswch/s
atch/s pgin/s ppgin/s pflt/s vflt/s slock/s
pgout/s ppgout/s pgfree/s pgscan/s %s5ipf
09:54:34 0.00 0.0 0.00 0.0 12
0.00 0.22 0.22 0.65 3.90 0.87
0.00 0.00 0.00 0.00 0.00
Note: There is absolutely no swapping or paging going on. (swpin,swpot,ppgin,ppgout).
4. sar -r 5 5 (ATT)
10:10:22 freemem freeswp
10:10:27 790 5862
This will give you a good indication of how much free swap and RAM you have on your machine. There are 790 pages of memory available and 5862 disk blocks of SWAP available.
2. CPU
Once you have monitored your systems available memory you will want to make sure the the CPU(s) are not being overloaded. Here is some general information about how processes get allocated CPU time. UNIX is a multi-processing operating system. That means that a UNIX machine has to manage and process multiple user processes simultaneously. UNIX does this in the same way that people wait in line to buy groceries.
When a process is ready to be processed by a CPU it will be placed on the waiting line or RUN-QUEUE. This is a queue of processes waiting to be run. Obviously there are limits within which one wants to keep the RUN-QUEUE size. Another factor of interest is the percentage of time the the CPU spends in user mode, system mode, or idle mode. Some commands that determine whether or not there is a CPU resource problem occurring:
1. vmstat 5 5 (BSD)
procs memory page disk faults cpu
r b w avm fre re at pi po fr de sr d0 s1 d2 s3 in sy cs us sy id
0 0 0 0 1088 0 2 2 0 1 0 0 0 0 0 0 26 72 24 0 1 98
Note: The CPU is spending most of its time in IDLE mode (id). That means that the CPU is not being heavily used at all! There are no processes that are waiting to be run (r), blocked (b), or waiting for IO (w) in the RUN QUEUE.
2. sar -qu 5 5 (ATT)
10:58:02 runq-sz %runocc swpq-sz %swpocc
%usr %sys %wio %idle
10:58:07 2.8 100
0 2 4 94
Note: The CPU is spending most (94%) of its time in idle mode. This CPU is not being heavily used at all. Two solutions to this are:
1. Obtain a faster processor
2. Use more CPU's.
Avoid overloading your CPU. Response time on your machine will suffer if it is overloaded. Try to keep the run queue 100% occupied and have less that 6 processes waiting to be run for one CPU. This changes as you add more CPU's or a faster CPU. You may also want to avoid the CPU spending most of its time (more than 50%) in system mode. This may indicate that you are spending too much time in kernel mode servicing interrupts, swapping processes etc.
3. I/O
The last step in analyzing your UNIX machine is taking a look at IO. After having looked at SQLDBA monitor to see which datafiles are being used heavily you may also want to take a look at what UNIX says about file IO. These commands are for analyzing file IO on file systems.
1. iostat -d 5 5(BSD)
sd1 sd3
bps tps msps bps tps msps
1 0 0.0 4 0 0.0
iostat will display the number of kilobytes transferred per second, the number of transfers per second, and the milliseconds per average seek. In the example above, both SD1 and SD3 are not used heavily at all. BPS rates over 30 indicate heavy usage of a particular disk. If only one disk shows heavy usage, consider moving some of your datafiles off it or striping your data across several disks.
2. sar -d 5 5 (ATT)
09:17:20 device %busy avque r+w/s blks/s avwait avserv
09:17:26 iop0/pdisk010 472.45 1.18 9 107 39.26 512.66
iop0/pdisk000 18.43 2.66 8 132 31.36 24.10
iop0/pdisk020 317.08 1.11 11 165 31.95 294.02
iop0/pdisk021 590.88 1.34 27 518 96.26 219.96
iop0/pdisk040 34.94 1.64 18 113 43.70 19.58
iop0/pdisk041 45.33 1.17 20 79 3.73 22.89
Note: The "-d" option reports activity for each block device, The following is an explanation on the output. %busy, avque - portion of time device was busy servicing a transfer request, average number of requests outstanding during that time; r+w/s, blks/s - number of data transfers from or to device, number of bytes transferred in 512-byte units; avwait, avserv - average time (in ms) that transfer requests wait idly on queue, and average time to be serviced. There is a relationship between the number of blocks transferred per second and the average wait time. You can use this to identify which disks are heavily used and which are
underutilized.
3. sar -b 5 5 (ATT)
15:52:57 bread/s lread/s %rcache bwrit/s lwrit/s %wcache pread/s pwrit/s
15:53:12 0 2 90 1 2 38 0 0
Note: The "-b" option indicates the overall health of the IO subsystem. The %rcache should be greater than 90% and %wcache should be greater than 60%. If this is not the case, your system may be bound by disk IO. The sum of bread, bwrit, pread, and pwrit gives a good indicator of how well your file subsystem is doing. The sum should not be greater than 40 for 2 drives and
60 for 4-8 drives. If you exceed these values, your system may be IO bound. For more information on this refer to pg 2-22 of the Oracle V7 technical reference guide.
When analyzing disk IO, make sure that you have balanced the load on your system. Here is a "wish" list of steps for designing a disk layout for Oracle:
1. Make sure that your logfiles and archived logfiles are NOT on the same disk as your datafiles. This is a basic safety precaution against disk failure.
2. Put your files on raw devices.
3. Allocate one disk for the User Data Tablespace.
4. Place Rollback, Index, and System Tablespaces on separate disks.
5. Consider using Raid level 5 disk striping to stripe your datafiles across separate disks. You can also use the above-mentioned UNIX commands to monitor the IO on your system and identify problem areas.
Other tools for system administratation are platform-dependent. For example, HP and SEQUENT provide a facility called MONITOR that gives much of the above information in a graphical format. ATT SVR 4 also provides a product called GSAR (graphical sar). Look in your operating system (OS) documentation for additional monitoring commands. As a further
reference, consider "SYSTEM PERFORMANCE TUNING" published by O'Reilly and Associates.
Conclusion: There are many ways to approach Oracle performance issues. A structured approach like the one discussed above will allow the system administrator to systematically analyze her system and identify any problem areas. Once this has been accomplished, measures can be taken to correct problems. Performance is subjective, so find out what is expected.
process to upgrade an existing database instance running on Oracle8i Enterprise Edition Release 8.1.7.x directly to a version 9.0.1 patch set.
Upgrading Directly to a 9.0.1 Patch Set
Database Server Enterprise Edition Release 9.0.1
August 2002
This document describes the process to upgrade an existing database
instance running on Oracle8i Enterprise Edition Release 8.1.7.x
directly to a version 9.0.1 patch set. Upgrading your system to an
9.0.1 patch set from a version earlier than 8.1.7 (for example 8.0.6
or 8.1.5) is not supported. In this case you must either
migrate/upgrade to 9.0.1.0 first and then install the patch set, or
upgrade/migrate to 8.1.7 and then follow these intructions.
Attention: There is an error in the 9.0.1.4 README (patch_note.htm)
indicating that upgrading from 8.1.7 directly to 9.0.1.4 is not
supported. It is supported as documented here. See Note 214972.1 for
the wording which should have appeared in the 9.0.1.4 Patch Set
README.
Attention: These notes apply to UNIX and Windows NT/2000 platforms.
However, you may need to modify some instructions slightly depending
upon your platform. For example, these notes typically use UNIX syntax
when specifying a directory, so Windows NT/2000 users will need to
substitute the appropriate syntax when accessing that
directory(folder).
Attention: You can obtain the most recent 9.0.1 patchset from
OracleMetaLink. After logging on to OracleMetaLink, navigate to the
patch download page using the menu on the left of the screen. Query
for the patchset using the following parameters:
Parameter
Value
Product Family Oracle Server
Release 9.0.1.x (where x is the highest version available)
Platform
Limit Search to Latest Product Patchsets or Minipacks
Upgrading from Oracle8i Release 8.1.7.x Database Server to Oracle9i
Enterprise Edition Release 9.0.1.x
Follow instructions in this section only if you have an existing
Database Server using Oracle8i Enterprise Edition Release 8.1.7.x.
Upgrading from pre-8.1.7 versions directly to an 9.0.1 patch set is
not supported.
1.
Install Oracle9i Enterprise Editions Release 9.0.1 in new ORACLE_HOME.
Log in as the user who manages (owns) the Oracle9i Enterprise
Edition files and database. Make sure that none of the environment
settings such as ORACLE_HOME, PATH, ORA_NLS, etc. refer to the
existing Oracle8i Enterprise Edition Release 8.1.7 environment.
If you have not already installed the current Oracle9i
Enterprise Edition Release 9.0.1 files, follow the instructions in the
Oracle9i Installation Guide. Install the files in a location other
than the existing 8.1.7 Oracle home. Choose to install all components
currently used by your 8.1.7 database.
Attention: Windows NT/2000 customers should not install the
following development tools. These tools do not support multiple
Oracle Homes.
* Oracle Objects for OLE
* Oracle ODBC Drivers
* Oracle OLE Providers for OLEDB
We also recommend choosing all the languages, (or at a minimum
all the required languages) during the installation.
Do not run any migration scripts at this time.
2. Install 9.0.1 Patch Set
Install the latest Oracle9i Release 9.0.1 patch set into the
9.0.1 Oracle Home using Oracle Universal Installer.
Do not run any SQL scripts at this time.
3. Check OracleMetaLink for additional patches
Additional issues with 9.0.1 may have been identified since this
document and the Patch Set Release Notes were authored. Check Document
149018.1 on Metalink for the latest issues/alerts.
4. Prepare Database for Upgrade
The 'Prepare to Upgrade' section of Chapter 7 (Upgrading from an
Older Release of Oracle to the New Oracle9i Release) of the Oracle9i
Migration Release 1 (9.0.1) contains steps to be executed to the
existing database in the existing environment before upgrade. Complete
all the steps listed there.
5.
Shutdown Servers, Concurrent Managers and Database
All servers and processes such as the Web Server and Net8
Listener must be shut down and all users must be logged out before
starting the database upgrade.
The Database will be unavailable to users until all tasks in
these notes are completed.
6. Modify init.ora parameters.
Some of the database initialization parameters have changed or
become obsolete in Oracle9i Enterprise Edition Release 9.0.1. Details
are available in the Oracle9i Migration manual.
Attention: During your database migration, instructions in the
Oracle9i Database Migration Release 1 (9.0.1) require the value of
_system_trig_enabled to be temporarily set to false at the beginning
of the migration, and restored to true before upgrading JServer.
7. Backup the Database.
We recommend taking a backup of your database.
8. Ensure Adequate Rollback and System Space
Ensure that there is sufficient free space in the SYSTEM
tablespace and for the Rollback segments (see Oracle9i Migration
Release 1 (9.0.1) for more details.
9. Upgrade the Database
Follow instructions in the manual Oracle9i Migration Release 1
(9.0.1) to upgrade the database to the current release. Ensure that
you have the latest version of the manual, which can be found on the
Oracle Technology Network.
Continue Chapter 7 of the Oracle9i Migration Release 1 (9.0.1)
Guide with the section titled 'Upgrade the Database'. If using the
dbma to perform the migration then start with the section titled
'Running the Oracle Data Migration Assistant Independently'. If
choosing to perform the migration manually start with the section
entitled 'Upgrade the Database Manually' but skip to step 9 as the new
release has already been installed .
You will be upgrading directly to the current 9.0.1 patch set level.
1. You must now complete the steps specific to any components
referred in the Migration Document that you are using.
There is no need to compile invalid database objects at this time.
2. Oracle9i Release 9.0.1 Patch Set release notes contains
additional steps required to complete the install of the patch set.
Follow the instructions in the How to Install This Patch Set section
to upgrade the Databases to the latest patch set level. The step 9 in
this document has instructions to run the following script, you may
ignore running this script:
* ?/rdbms/admin/catpatch.sql
10. Compile All objects.
You may use the standard utility utlrp.sql (found in
$ORACLE_HOME/rdbms/admin) to compile all invalid objects in the
database.
Some database objects will have become invalid due to the
database upgrade.
Change Log
Date Description
June 21, 2002
* Document created based on 8.1.7 version and Applications 11i
Interoperability guide.
August 13, 2002
* Updated for 9.0.1.4
Database Server Enterprise Edition Release 9.0.1
August 2002
This document describes the process to upgrade an existing database
instance running on Oracle8i Enterprise Edition Release 8.1.7.x
directly to a version 9.0.1 patch set. Upgrading your system to an
9.0.1 patch set from a version earlier than 8.1.7 (for example 8.0.6
or 8.1.5) is not supported. In this case you must either
migrate/upgrade to 9.0.1.0 first and then install the patch set, or
upgrade/migrate to 8.1.7 and then follow these intructions.
Attention: There is an error in the 9.0.1.4 README (patch_note.htm)
indicating that upgrading from 8.1.7 directly to 9.0.1.4 is not
supported. It is supported as documented here. See Note 214972.1 for
the wording which should have appeared in the 9.0.1.4 Patch Set
README.
Attention: These notes apply to UNIX and Windows NT/2000 platforms.
However, you may need to modify some instructions slightly depending
upon your platform. For example, these notes typically use UNIX syntax
when specifying a directory, so Windows NT/2000 users will need to
substitute the appropriate syntax when accessing that
directory(folder).
Attention: You can obtain the most recent 9.0.1 patchset from
OracleMetaLink. After logging on to OracleMetaLink, navigate to the
patch download page using the menu on the left of the screen. Query
for the patchset using the following parameters:
Parameter
Value
Product Family Oracle Server
Release 9.0.1.x (where x is the highest version available)
Platform
Limit Search to Latest Product Patchsets or Minipacks
Upgrading from Oracle8i Release 8.1.7.x Database Server to Oracle9i
Enterprise Edition Release 9.0.1.x
Follow instructions in this section only if you have an existing
Database Server using Oracle8i Enterprise Edition Release 8.1.7.x.
Upgrading from pre-8.1.7 versions directly to an 9.0.1 patch set is
not supported.
1.
Install Oracle9i Enterprise Editions Release 9.0.1 in new ORACLE_HOME.
Log in as the user who manages (owns) the Oracle9i Enterprise
Edition files and database. Make sure that none of the environment
settings such as ORACLE_HOME, PATH, ORA_NLS, etc. refer to the
existing Oracle8i Enterprise Edition Release 8.1.7 environment.
If you have not already installed the current Oracle9i
Enterprise Edition Release 9.0.1 files, follow the instructions in the
Oracle9i Installation Guide. Install the files in a location other
than the existing 8.1.7 Oracle home. Choose to install all components
currently used by your 8.1.7 database.
Attention: Windows NT/2000 customers should not install the
following development tools. These tools do not support multiple
Oracle Homes.
* Oracle Objects for OLE
* Oracle ODBC Drivers
* Oracle OLE Providers for OLEDB
We also recommend choosing all the languages, (or at a minimum
all the required languages) during the installation.
Do not run any migration scripts at this time.
2. Install 9.0.1 Patch Set
Install the latest Oracle9i Release 9.0.1 patch set into the
9.0.1 Oracle Home using Oracle Universal Installer.
Do not run any SQL scripts at this time.
3. Check OracleMetaLink for additional patches
Additional issues with 9.0.1 may have been identified since this
document and the Patch Set Release Notes were authored. Check Document
149018.1 on Metalink for the latest issues/alerts.
4. Prepare Database for Upgrade
The 'Prepare to Upgrade' section of Chapter 7 (Upgrading from an
Older Release of Oracle to the New Oracle9i Release) of the Oracle9i
Migration Release 1 (9.0.1) contains steps to be executed to the
existing database in the existing environment before upgrade. Complete
all the steps listed there.
5.
Shutdown Servers, Concurrent Managers and Database
All servers and processes such as the Web Server and Net8
Listener must be shut down and all users must be logged out before
starting the database upgrade.
The Database will be unavailable to users until all tasks in
these notes are completed.
6. Modify init.ora parameters.
Some of the database initialization parameters have changed or
become obsolete in Oracle9i Enterprise Edition Release 9.0.1. Details
are available in the Oracle9i Migration manual.
Attention: During your database migration, instructions in the
Oracle9i Database Migration Release 1 (9.0.1) require the value of
_system_trig_enabled to be temporarily set to false at the beginning
of the migration, and restored to true before upgrading JServer.
7. Backup the Database.
We recommend taking a backup of your database.
8. Ensure Adequate Rollback and System Space
Ensure that there is sufficient free space in the SYSTEM
tablespace and for the Rollback segments (see Oracle9i Migration
Release 1 (9.0.1) for more details.
9. Upgrade the Database
Follow instructions in the manual Oracle9i Migration Release 1
(9.0.1) to upgrade the database to the current release. Ensure that
you have the latest version of the manual, which can be found on the
Oracle Technology Network.
Continue Chapter 7 of the Oracle9i Migration Release 1 (9.0.1)
Guide with the section titled 'Upgrade the Database'. If using the
dbma to perform the migration then start with the section titled
'Running the Oracle Data Migration Assistant Independently'. If
choosing to perform the migration manually start with the section
entitled 'Upgrade the Database Manually' but skip to step 9 as the new
release has already been installed .
You will be upgrading directly to the current 9.0.1 patch set level.
1. You must now complete the steps specific to any components
referred in the Migration Document that you are using.
There is no need to compile invalid database objects at this time.
2. Oracle9i Release 9.0.1 Patch Set release notes contains
additional steps required to complete the install of the patch set.
Follow the instructions in the How to Install This Patch Set section
to upgrade the Databases to the latest patch set level. The step 9 in
this document has instructions to run the following script, you may
ignore running this script:
* ?/rdbms/admin/catpatch.sql
10. Compile All objects.
You may use the standard utility utlrp.sql (found in
$ORACLE_HOME/rdbms/admin) to compile all invalid objects in the
database.
Some database objects will have become invalid due to the
database upgrade.
Change Log
Date Description
June 21, 2002
* Document created based on 8.1.7 version and Applications 11i
Interoperability guide.
August 13, 2002
* Updated for 9.0.1.4
Step-By-Step Installation of 9.2.0.5 RAC on Linux
Step-By-Step Installation of 9.2.0.5 RAC on Linux
Note: This note was created for 9i RAC. The 10g Oracle documentation
provides installation instructions for 10g RAC. These instructions
can be found on OTN:
Oracle(r) Real Application Clusters Installation andOracle(r) Real
Application Clusters Installation and Configuration Guide
10g Release 1 (10.1) for AIX-Based Systems, hp HP-UX PA-RISC (64-bit),
hp Tru64 UNIX, Linux, Solaris Operating System (SPARC 64-bit)
Purpose
This document will provide the reader with step-by-step instructions
on how to install a cluster, install Oracle Real Application Clusters
(RAC) (Version 9.2.0.5), and start a cluster database on Linux. For
additional explanation or information on any of these steps, please
see the references listed at the end of this document.
Disclaimer: If there are any errors or issues prior to step 2, please
contact your Linux distributor.
The information contained here is as accurate as possible at the time
of writing.
* 1. Configuring the Cluster Hardware
o 1.1 Minimal Hardware list / System Requirements
+ 1.1.1 Hardware
+ 1.1.2 Software
o 1.2 Installing the Shared Disk Subsystem
o 1.3 Configuring the Cluster Interconnect and Public Network Hardware
* 2. Creating a cluster
o 2.1 UNIX Pre-installation tasks
o 2.2 Configuring the Shared Disks
o 2.3 Run the Oracle Universal Installer to install the
9.2.0.4 ORACM (Oracle Cluster Manager)
o 2.4 Configure the hangcheck-timer
o 2.5 Install Version 10.1.0.2 of the Oracle Universal Installer
o 2.6 Run the 10.1.0.2 Oracle Universal Installer to patch
the Oracle Cluster Manager (ORACM) to 9.2.0.5
o 2.7 Modify the ORACM configuration files to utilize the
hangcheck-timer
o 2.8 Start the ORACM (Oracle Cluster Manager)
* 3. Installing RAC
o 3.1 Install 9.2.0.4 RAC
o 3.2 Patch the RAC Installation to 9.2.0.5
o 3.3 Start the GSD (Global Service Daemon)
o 3.4 Create a RAC Database using the Oracle Database
Configuration Assistant
* 4. Administering Real Application Clusters Instances
* 5. References
1. Configuring the Clusters Hardware<>
1.1 Minimal Hardware list / System Requirements
Please check the RAC/Linux certification matrix for information on
currentlyRAC/Linux certification matrix for information on currently
supported hardware/software.
1.1.1 Hardware
* Requirements:
o Refer to the RAC/Linux certification matrix for
information onRAC/Linux certification matrix for information on
supported configurations. Ensure that the system has at least the
following resources:
- 400 MB in /tmp
- 512 MB of Physical Memory (RAM)
- Three times the amount of Physical Memory for Swap
space (unless the system exceeds 1 GB of Physical Memory, where two
times the amount of Physical Memory for Swap space is sufficient)
An example system disk layout is as follows:-
A sample system disk layout
Slice
Contents
Allocation (in Mbytes)
0
/
2000 or more
1
/boot
64
2
/tmp
1000
3
/usr
3000-7000 depending on operating system and packages installed
4
/var
512 (can be more if required)
5
swap
Three times the amount of Physical Memory for Swap space (unless the
system exceeds 1 GB of Physical Memory, where two times the amount of
Physical Memory for Swap space is sufficient).
6
/home
2000 (can be more if required)
1.1.2 Software
* For RAC on Linux support, consult the operating system vendor
and see the RAC/Linux certification matrix.
* RAC/Linux certification matrix. Make sure you have make and
rsh-server packages installed, check with:
$rpm -q rsh-server make
rsh-server-0.17-5
make-3.79.1-8
If these are not installed, use your favorite package manager to
install them.
1.1.3 Patches
Consult with your operating system vendor to get on the latest patch
version of the kernel.
1.2 Installing the Shared Disk Subsystem
This is highly dependent on the subsystem you have chosen. Please
refer to your hardware documentation for installation and
configuration instructions on Linux. Additional drivers and patches
might be required. In this article we assume that the shared disk
subsystem is correctly installed and that the shared disks are visible
to all nodes in the cluster.
1.3 Configuring the Cluster Interconnect and Public Network Hardware
If not already installed, install host adapters in your cluster nodes.
For the procedure on installing host adapters, see the documentation
that shipped with your host adapters and node hardware.
Each system will have at least an IP address for the public network
and one for the private cluster interconnect. For the public network,
get the addresses from your network manager. For the private
interconnect use 1.1.1.1 , 1.1.1.2 for the first and second node. Make
sure to add all addresses in /etc/hosts.
[oracle@opcbrh1 oracle]$ more /etc/hosts
ex:
9.25.120.143 rac1 #Oracle 9i Rac node 1 - public network
9.25.120.143 rac2 #Oracle 9i Rac node 2 - public network
1.1.1.1 int-rac1 #Oracle 9i Rac node 1 - interconnect
1.1.1.2 int-rac2 #Oracle 9I Rac node 2 - interconnect
Use your favorite tool to configure these adapters. Make sure your
public network is the primary (eth0).
Interprocess communication is an important issue for RAC since cache
fusion transfers buffers between instances using this mechanism. Thus,
networking parameters are important for RAC databases. The values in
the following table are the recommended values. These are NOT the
default on most distributions.
Parameter
Meaning
Value
/proc/sys/net/core/rmem_default
The default setting in bytes of the socket receive buffer
262144
/proc/sys/net/core/rmem_max
The maximum socket receive buffer size in bytes
262144
/proc/sys/net/core/wmem_default
The default setting in bytes of the socket send buffer
262144
/proc/sys/net/core/wmem_max
The maximum socket send buffer size in bytes
262144
You can see these settings with:
$ cat /proc/sys/net/core/rmem_default
Change them with:
$ echo 262144 > /proc/sys/net/core/rmem_default
This will need to be done each time the system boots. Some
distributions already have setup a method for this during boot. On Red
Hat , this can be configured in /etc/sysctl.conf (like :
net.core.rmem_default = 262144).
2. Creating a Cluster
On Linux, the cluster software required to run Real Application
Clusters is included in the Oracle distribution.
The Oracle Cluster Manager (ORACM) installation process includes eight
major tasks.
1. UNIX pre-installation tasks.
2. Configuring the shared disks
3. Run the Oracle Universal Installer to install the 9.2.0.4 ORACM
(Oracle Cluster Manager)
4. Configure the hangcheck-timer.
5. Install version 10.1.0.2 of the Oracle Universal Installer
6. Run the 10.1.0.2 Oracle Universal Installer to patch the Oracle
Cluster Manager (ORACM) to 9.2.0.5
7. Modify the ORACM configuration files to utilize the hangcheck-timer.
8. Start the ORACM (Oracle Cluster Manager)
2.1 UNIX Pre-installation tasks
These steps need to be performed on ALL nodes.
* First, on each node, create the Oracle group. Example:
# groupadd dba -g 501
* Next, make the Oracle user's home directory. Example:
# mkdir -p /u01/home/oracle
* On each node, create the Oracle user. Make sure that the Oracle
user is part of the dba group. Example:
# useradd -c "Oracle Software Owner" -G dba -u 101 -m -d
/u01/home/oracle -s /bin/csh oracle
* On each node, Create a mount point for the Oracle software
installation (at least 2.5 GB, typically /u01). The oracle user
should own this mount point and all of the directories below the mount
point. Example:
# mkdir /u01
# chown -R oracle.dba /u01
# chmod -R ug=rwx,o=rx /u01
* Once this is done, test the permissions on each node to ensure
that the oracle user can write to the new mount points. Example:
# su - oracle
$ touch /u01/test
$ ls -l /u01/test
-rw-rw-r-- 1 oracle dba 0 Aug 15 09:36 /u01/test
* Depending on your Linux distribution, make sure inetd or xinetd
is started on all nodes and that the ftp, telnet, shell and login (or
rsh) services are enabled (see /etc/inetd.conf or /etc/xinetd.conf and
/etc/xinetd.d). Example:
# more /etc/xinetd.d/telnet
# default: on
# description: The telnet server serves telnet sessions; it uses # unencrypted username/password pairs for authentication.
service telnet
{
flags = REUSE
socket_type = stream
wait = no
user = root
server = /usr/sbin/in.telnetd
log_on_failure += USERID
disable = no
}
In this example, disable should be set to 'no'.
* On the node from which you will run the Oracle Universal
Installer, set up user equivalence by adding entries for all nodes in
the cluster, including the local node, to the .rhosts file of the
oracle account, or the /etc/hosts.equiv file.
Sample entries in /etc/hosts.equiv file:
rac1
rac2
int-rac1
int-rac2
* As oracle user, check for user equivalence for the oracle
account by performing a remote copy (rcp) to each node (public and
private) in the cluster. Example:
RAC1:
$ touch /u01/test
$ rcp /u01/test rac2:/u01/test1
$ rcp /u01/test int-rac2:/u01/test2
RAC2:
$ touch /u01/test
$ rcp /u01/test rac1:/u01/test1
$ rcp /u01/test int-rac1:/u01/test2
$ ls /u01/test*
/u01/test /u01/test1 /u01/test2
RAC1:
$ ls /u01/test*
/u01/test /u01/test1 /u01/test2
Note: If you are prompted for a password, you have not given the
oracle account the same attributes on all nodes. You must correct this
because the Oracle Universal Installer cannot use the rcp command to
copy Oracle products to the remote node's directories without user
equivalence.
System Kernel Parameters
Verify operating system kernel parameters are set to appropriate levels:
Kernel Parameter
Setting
Purpose
SHMMAX
2147483648
Maximum allowable size of one shared memory segment.
SHMMIN
1
Minimum allowable size of a single shared memory segment.
SHMMNI
100
Maximum number of shared memory segments in the entire system.
SHMSEG
10
Maximum number of shared memory segments one process can attach.
SEMMNI
100
Maximum number of semaphore sets in the entire system.
SEMMSL
250
Minimum recommended value. SEMMSL should be 10 plus the largest
PROCESSES parameter of any Oracle database on the system.
SEMMNS
1000
Maximum semaphores on the system. This setting is a minimum
recommended value. SEMMNS should be set to the sum of the PROCESSES
parameter for each Oracle database, add the largest one twice, plus
add an additional 10 for each database.
SEMOPM
100
Maximum number of operations per semop call.
You will have to set the correct parameters during system startup, so
include them in your startup script (startoracle_root.sh):
$ export SEMMSL=100
$ export SEMMNS=1000
$ export SEMOPM=100
$ export SEMMNI=100
$ echo $SEMMSL $SEMMNS $SEMOPM $ SEMMNI > /proc/sys/kernel/sem
$ export SHMMAX=2147483648
$ echo $SHMMAX > /proc/sys/kernel/shmmax
Check these with:
$ cat /proc/sys/kernel/sem
$ cat /proc/sys/kernel/shmmax
You might want to increase the maximum number of file handles, include
this in your startup script or use /etc/sysctl.conf :
$ echo 65536 > /proc/sys/fs/file-max
To allow your oracle processes to use these file handles, add the
following to your oracle account login script (ex.: .profile)
$ ulimit -n 65536
Note: This will only allow you to set the soft limit as high as the
hard limit. You might have to increase the hard limit on system level.
This can be done by adding ulimit -Hn 65536 to /etc/initscript. You
will have to reboot the system to make this active. Sample
/etc/initscript:
ulimit -Hn 65536
eval exec "$4"
Establish Oracle environment variables: Set the following Oracle
environment variables:
Environment Variable
Suggested value
ORACLE_HOME
eg /u01/app/oracle/product/920
ORACLE_TERM
xterm
PATH
/u01/app/oracle/product/9.2.0/bin: /usr/ccs/bin:/usr/bin/X11/:/usr/local/bin
and any other items you require in your PATH
DISPLAY
:0.0
(review Note:153960.1 for detailed information)
Note:153960.1 for detailed information)
TMPDIR
Set a temporary directory path for TMPDIR with at least 100 Mb of free
space to which the OUI has write permission.
ORACLE_SID
Set this to what you will call your database instance. This should be
UNIQUE on each node.
It is best to save these in a .login or .profile file so that you do
not have to set the environment every time you log in.
* Create the directory /var/opt/oracle and set ownership to the
oracle user. Example:
$ mkdir /var/opt/oracle
$ chown oracle.dba /var/opt/oracle
* Set the oracle user's umask to "022" in you ".profile" or
".login" file. Example:
$ umask 022
Note: There is a verification script InstallPrep.sh available which
may beverification script InstallPrep.sh available which may be
downloaded and run prior to the installation of Oracle Real
Application Clusters. This script verifies that the system is
configured correctly according to the Installation Guide. The output
of the script will report any further tasks that need to be performed
before successfully installing Oracle 9.x DataServer (RDBMS). This
script performs the following verifications:-
* ORACLE_HOME Directory Verification
* UNIX User/umask Verification
* UNIX Group Verification
* Memory/Swap Verification
* TMP Space Verification
* Real Application Cluster Option Verification
* Unix Kernel Verification
. ./InstallPrep.sh
You are currently logged on as oracle
Is oracle the unix user that will be installing Oracle Software? y or n
y
Enter the unix group that will be used during the installation
Default: dba
Enter the version of Oracle RDBMS you will be installing
Enter either : 901 OR 920 - Default: 920
920
The rdbms version being installed is 920
Enter Location where you will be installing Oracle
Default: /u01/app/oracle/product/oracle9i
/u01/app/oracle/product/9.2.0
Your Operating System is Linux
Gathering information... Please wait
JDK check is ignored for Linux since it is provided by Oracle
Checking unix user ...
Checking unix umask ...
umask test passed
Checking unix group ...
Unix Group test passed
Checking Memory & Swap...
Memory test passed
/tmp test passed
Checking for a cluster...
Linux Cluster test section has not been implemented yet
No cluster warnings detected
Processing kernel parameters... Please wait
Running Kernel Parameter Report...
Check the report for Kernel parameter verification\n
Completed.
/tmp/Oracle_InstallPrep_Report has been generated
Please review this report and resolve all issues before attempting to
install the Oracle Database Software
Note: If you get an error like this:
InstallPrep.sh: line 45: syntax error near unexpected token `fi'
or
./InstallPrep.sh: Command not found.
Then you need to copy the script into a text file (it will not run if
the file is in binary format).
2.2 Configuring the Shared Disks
For 9.2 Real Application Clusters on Linux, you can use either OCFS
(Oracle Cluster Filesystem), RAW, or NFS (Redhat and Network Appliance
Only) for storage of Oracle database files.
* For more information on setting up OCFS for RAC on Linux, see
the following MetaLink Note:
Note 220178.1 - Installing and setting up ocfs on Linux -
BasicNote 220178.1 - Installing and setting up ocfs on Linux - Basic
Guide
* For more information on setting up RAW for RAC on Linux, see the
following MetaLink Note:
Note 246205.1 - Configuring Raw Devices for Real
ApplicationNote 246205.1 - Configuring Raw Devices for Real
Application Clusters on Linux
* For more information on setting up NFS for RAC on Linux, see the
following MetaLink Note (Steps 1-6):
Note 210889.1 - RAC Installation with a NetApp Filer in Red
HatNote 210889.1 - RAC Installation with a NetApp Filer in Red Hat
Linux Environment
2.3 Run the Oracle Universal Installer to install the 9.2.0.4 ORACM
(Oracle Cluster Manager)
These steps only need to be performed on the node that you are
installing from (typically Node 1).
* If you are using OCFS or NFS for your shared storage, pre-create
the quorum file and srvm file. Example:
# dd if=/dev/zero of=/ocfs/quorum.dbf bs=1M count=20
# dd if=/dev/zero of=/ocfs/srvm.dbf bs=1M count=100
# chown root:dba /ocfs/quorum.dbf
# chmod 664 /ocfs/quorum.dbf
# chown oracle:dba /ocfs/srvm.dbf
# chmod 664 /ocfs/srvm.dbf
* Verify the Environment - Log off and log on as the oracle user
to ensure all environment variables
are set correctly. Use the following command to view them:
% env | more
Note: If you are on Redhat Advanced Server 3.0, you will need to
temporarily use an older gcc for the install:
mv gcc gcc3.2.3
mv g++ g++3.2.3
ln -s /usr/bin/gcc296 /usr/bin/gcc
ln -s /usr/bin/g++296 /usr/bin/g++
You will also need to apply patch 3006854 if on RHAS 3.0
* Before attempting to run the Oracle Universal Installer, verify
that you can successfully run the following command:
% /usr/bin/X11/xclock
* If this does not display a clock on your display screen, please
review the following article:
Note 153960.1 FAQ: X Server testing and troubleshooting
Note 153960.1 FAQ: X Server testing and troubleshooting
* Start the Oracle Universal Installer and install the RDBMS
software - Follow these procedures to use the Oracle Universal
Installer to install the Oracle Cluster Manager software. Oracle9i is
supplied on multiple CD-ROM disks. During the installation process it
is necessary to switch between the CD-ROMS. OUI will manage the
switching between CDs.
Use the following commands to start the installer:
% cd /tmp
% /cdrom/runInstaller
Or cd to /stage/Disk1 and run ./runInstaller
Respond to the installer prompts as shown below:
* At the "Welcome Screen", click Next.
If this is your first install on this machine:
* If the "Inventory Location" screen appears, enter the inventory
location then click OK.
* If the "Unix Group Name" screen appears, enter the unix group
name created in step 2.1 then click Next.
* At this point you may be prompted to run /tmp/orainstRoot.sh.
Run this and click Continue.
* At the "File Locations Screen", verify the destination listed is
your ORACLE_HOME directory. Also enter a NAME to identify this
ORACLE_HOME. The NAME can be anything.
* At the "Available Products Screen", Check "Oracle Cluster
Manager". Click Next.
* At the public node information screen, enter the public node
names and click Next.
* At the private node information screen, enter the interconnect
node names. Click Next.
* Enter the full name of the file or raw device you have created
for the ORACM Quorum disk information. Click Next.
* Press Install at the summary screen.
* You will now briefly get a progress window followed by the end
of installation screen. Click Exit and confirm by clicking Yes.
Note: Create the directory $ORACLE_HOME/oracm/log (as oracle) on the
other nodes if it doesn't exist.
2.4 Configure the hangcheck-timer
These steps need to be performed on ALL nodes.
Some kernel versions include the hangcheck-timer with the kernel. You
can check to see if your kernel contains the hangcheck-timer by
running:
# /sbin/lsmod
Then you will see hangcheck-timer listed. Also verify that
hangcheck-timer is starting in your /etc/rc.local file (on Redhat) or
/etc/init.d/boot.local (on United Linux). If you see hangcheck-timer
listed in lsmod and in the rc.local file or boot.local, you can skip
to section 2.5.
If hangcheck-timer is not listed here and you are not using Redhat
Advanced Server, see the following note for information on obtaining
the hangcheck-timer:
Note 232355.1 - Hangcheck Timer FAQ
Note 232355.1 - Hangcheck Timer FAQ
If you are on Redhat Advanced Server, you can either apply the latest
errata version (> 12) or go to MetaLink - Patches:
Enter 2594820 in the Patch Number field.
Click Go.
Click Download.
Save the file p2594820_20_LINUX.zip to the local disk, such as /tmp.
Unzip the file. The output should be similar to the following:
inflating: hangcheck-timer-2.4.9-e.3-0.4.0-1.i686.rpm
inflating: hangcheck-timer-2.4.9-e.3-enterprise-0.4.0-1.i686.rpm
inflating:
hangcheck-timer-2.4.9-e.3-smp-0.4.0-1.i686.rpm
inflating: README.TXT
Run the uname -a command to identify the RPM that corresponds to the
kernel in use. This will show if the kernel is single CPU, smp, or
enterprise.
The p2594820_20_LINUX.zip file contains four files. The following
describes the files:
hangcheck-timer-2.4.9-e.3-0.4.0-1.i686.rpm is for single CPU machines
hangcheck-timer-2.4.9-e.3-enterprise=0.4.0-1.i686.rpm is for
multi-processor machines with more than 4 GB of RAM
hangcheck-timer-2.4.9-e.3-smp-0.4.0-1.i686.rpm is for multi-processor
machines with 4 GB of RAM or less
The three RPMs will work for the e3 kernels in Red Hat Advanced Server
2.1 gold and the e8 kernels in the latest Red Hat Advanced Server 2.1
errata release. These RPMs are for Red Hat Advanced Server 2.1 kernels
only.
Transfer the relevant hangcheck-timer RPM to the /tmp directory of the
Oracle Real Applications Cluster node.
Log in to the node as the root user.
Change to the /tmp directory.
Run the following command to install the module:
#rpm -ivh hangcheck-timer RPM name
If you have previously installed RAC on this cluster, remove or
disable the mechanism that loads the softdog module at system start
up, if that module is not used by other software on the node. This is
necessary for subsequent steps in the installation process. This step
may require log in as the root user. One method for setting up
previous versions of Oracle Real Applications Clusters involved
loading the softdog module in the /etc/rc.local (on Redhat) or
/etc/init.d/boot.local (on United Linux) file. If this method was
used, then remove or comment out the following line in the file:
/sbin/insmod softdog nowayout=0 soft_noboot=1 soft_margin=60
Append the following line to the /etc/rc.local file (on Redhat) or
/etc/init.d/boot.local (on United Linux):
/sbin/insmod hangcheck-timer hangcheck_tick=30 hangcheck_margin=180
Load the hangcheck-timer kernel module using the following command as root user:
# /sbin/insmod hangcheck-timer hangcheck_tick=30 hangcheck_margin=180
Repeat the above steps on all Oracle Real Applications Clusters nodes
where the kernel module needs to be installed.
Run dmesg after the module is loaded. Note the build number while
running the command. The following is the relevant information output:
build 334adfa62c1a153a41bd68a787fbe0e9
The build number is required when making support calls.
2.5 Install Version 10.1.0.2 of the Oracle Universal Installer
These steps need to be performed on ALL nodes.
Download the 9.2.0.5 patchset from MetaLink - Patches:
Enter 3501955 in the Patch Number field.
Click Go.
Click Download.
* Place the file in a patchset directory on the node you are
installing from. Example:
$ mkdir $ORACLE_HOME/9205
$ cp p3501955_9205_LINUX.zip $ORACLE_HOME/9205
* Unzip the file:
$ cd $ORACLE_HOME/9205
$ unzip p3501955_9205_LINUX.zip
Archive: p3501955_9205_LINUX
inflating: 9205_lnx32_release.cpio
inflating: README.html
inflating: ReleaseNote9205.pdf
* Run CPIO against the file:
$ cpio -idmv < 9205_lnx32_release.cpio
Run the installer from the 9.2.0.5 staging location:
$ cd $ORACLE_HOME/9205/Disk1
$ ./runInstaller
Respond to the installer prompts as shown below:
* At the "Welcome Screen", click Next.
* At the "File Locations Screen", Change the $ORACLE_HOME name
from the dropdown list to the 9.2 $ORACLE_HOME name. Click Next.
* On the "Available Products Screen", Check "Oracle Universal
Installer 10.1.0.2. Click Next.
* Press Install at the summary screen.
* You will now briefly get a progress window followed by the end
of installation screen. Click Exit and confirm by clicking Yes.
Remember to install the 10.1.0.2 Installer on ALL cluster nodes. Note
that you may need to ADD the 9.2 $ORACLE_HOME name on the "File
Locations Screen" for other nodes. It will ask if you want to specify
a non-empty directory, say "Yes".
2.6 Run the 10.1.0.2 Oracle Universal Installer to patch the Oracle
Cluster Manager (ORACM) to 9.2.0.5
These steps only need to be performed on the node that you are
installing from (typically Node 1).
The 10.1.0.2 OUI will use SSH (Secure Shell) if it is configured. If
it is not configured it will use RSH (Remote Shell). If you have SSH
configured on your cluster, test and make sure that you can SSH and
SCP to all nodes of the cluster without being prompted. If you do not
have SSH configured, skip this step and run the installer from
$ORACLE_BASE/oui/bin as noted below.
SSH Test:
As oracle user, check for user equivalence for the oracle account by
performing a secure copy (scp) to each node (public and private) in
the cluster. Example:
RAC1:
$ touch /u01/sshtest
$ scp /u01/sshtest rac2:/u01/sshtest1
$ scp /u01/sshtest int-rac2:/u01/sshtest2
RAC2:
$ touch /u01/sshtest
$ scp /u01/sshtest rac1:/u01/sshtest1
$ scp /u01/sshtest int-rac1:/u01/sshtest2
$ ls /u01/sshtest*
/u01/sshtest /u01/sshtest1 /u01/sshtest2
RAC1:
$ ls /u01/sshtest*
/u01/sshtest /u01/sshtest1 /u01/sshtest2
Run the installer from the 9.2.0.5 oracm staging location:
$ cd $ORACLE_HOME/9205/Disk1/oracm
$ ./runInstaller
Respond to the installer prompts as shown below:
* At the "Welcome Screen", click Next.
* At the "File Locations Screen", make sure the source location is
to the products.xml file in the 9.2.0.5 patchset location under
Disk1/stage. Also verify the destination listed is your ORACLE_HOME
directory. Change the $ORACLE_HOME name from the dropdown list to the
9.2 $ORACLE_HOME name. Click Next.
* At the "Available Products Screen", Check "Oracle9iR2 Cluster
Manager 9.2.0.5.0". Click Next.
* At the public node information screen, enter the public node
names and click Next.
* At the private node information screen, enter the interconnect
node names. Click Next.
* Click Install at the summary screen.
* You will now briefly get a progress window followed by the end
of installation screen. Click Exit and confirm by clicking Yes.
2.7 Modify the ORACM configuration files to utilize the hangcheck-timer.
These steps need to be performed on ALL nodes.
Modify the $ORACLE_HOME/oracm/admin/cmcfg.ora file:
Add the following line:
KernelModuleName=hangcheck-timer
Adjust the value of the MissCount line based on the sum of the
hangcheck_tick and hangcheck_margin values. (> 210)
MissCount=210
Make sure that you can ping each of the names listed in the private
and public node name sections from each node. Example:
$ ping rac2
PING opcbrh2.us.oracle.com (138.1.137.46) from 138.1.137.45 :
56(84) bytes of data.
64 bytes from opcbrh2.us.oracle.com (138.1.137.46): icmp_seq=0
ttl=255 time=1.295 msec
64 bytes from opcbrh2.us.oracle.com (138.1.137.46): icmp_seq=1
ttl=255 time=154 usec
Verify that a valid CmDiskFile line exists in the following format:
CmDiskFile=file or raw device name
In the preceding command, the file or raw device must be valid. If a
file is used but does not exist, then the file will be created if the
base directory exists. If a raw device is used, then the raw device
must exist and have the correct ownership and permissions. Sample
cmcfg.ora file:
ClusterName=Oracle Cluster Manager, version 9i
MissCount=210
PrivateNodeNames=int-rac1 int-rac2
PublicNodeNames=rac1 rac2
ServicePort=9998
CmDiskFile=/u04/quorum.dbf
KernelModuleName=hangcheck-timer
HostName=int-rac1
Note: The cmcfg.ora file should be the same on both nodes with the
exception of the HostName parameter which should be set to the local
(internal) hostname.
Make sure all of these changes have been made to all RAC nodes. More
information on ORACM parameters can be found in the following note:
Note 222746.1 - RAC Linux 9.2: Configuration of cmcfg.ora
andNote 222746.1 - RAC Linux 9.2: Configuration of cmcfg.ora and
ocmargs.ora
Note: At this point it would be a good idea to patch to the latest
ORACM, especially if you have more than 2 nodes. For more information
see:
Note 278156.1 - ORA-29740 or ORA-29702 After Applying 9.2.0.5 Patchset
on RAC / Linux
ORA-29740 or ORA-29702 After Applying 9.2.0.5 Patchset on RAC / Linux
2.8 Start the ORACM (Oracle Cluster Manager)
These steps need to be performed on ALL nodes.
Cd to the $ORACLE_HOME/oracm/bin directory, change to the root user,
and start the ORACM.
$ cd $ORACLE_HOME/oracm/bin
$ su root
# ./ocmstart.sh
oracm &1 >/u01/app/oracle/product/9.2.0/oracm/log/cm.out &
Verify that ORACM is running with the following:
# ps -ef | grep oracm
On RHEL 3.0, add the -m option:
# ps -efm | grep oracm
You should see several oracm threads running. Also verify that the
ORACM version is the same on each node:
# cd $ORACLE_HOME/oracm/log
# head -1 cm.log
oracm, version[ 9.2.0.2.0.49 ] started {Fri May 14 09:22:28 2004 }
3.0 Installing RAC
The Real Application Clusters installation process includes four major tasks.
1. Install 9.2.0.4 RAC.
2. Patch the RAC Installation to 9.2.0.5.
3. Start the GSD.
4. Create and configure your database.
3.1 Install 9.2.0.4 RAC
These steps only need to be performed on the node that you are
installing from (typically Node 1).
Note: Due to bug 3547724, temporarily create a symbolic link /oradata
directory pointing to an oradata directory with space available as
root prior to running the RAC install:
# mkdir /u04/oradata
# chmod 777 /u04/oradata
# ln -s /u04/oradata /oradata
Install 9.2.0.4 RAC into your $ORACLE_HOME by running the installer
from the 9.2.0.4 cd or your original stage location for the 9.2.0.4
install.
Use the following commands to start the installer:
% cd /tmp
% /cdrom/runInstaller
Or cd to /stage/Disk1 and run ./runInstaller
Respond to the installer prompts as shown below:
* At the "Welcome Screen", click Next.
* At the "Cluster Node Selection Screen", make sure that all RAC
nodes are selected.
* At the "File Locations Screen", verify the destination listed is
your ORACLE_HOME directory and that the source directory is pointing
to the products.jar from the 9.2.0.4 cd or staging location.
* At the "Available Products Screen", check "Oracle 9i Database
9.2.0.4". Click Next.
* At the "Installation Types Screen", check "Enterprise Edition"
(or whichever option your prefer), click Next.
* At the "Database Configuration Screen", check "Software Only".
Click Next.
* At the "Shared Configuration File Name Screen", enter the path
of the CFS or NFS srvm file created at the beginning of step 2.3 or
the raw device created for the shared configuration file. Click Next.
* Click Install at the summary screen. Note that some of the
items installed will say "9.2.0.1" for the version, this is normal
because only some items needed to be patched up to 9.2.0.4.
* You will now get a progress window, run root.sh when prompted.
* You will then see the end of installation screen. Click Exit and
confirm by clicking Yes.
Note: You can now remove the /oradata symbolic link:
# rm /oradata
3.2 Patch the RAC Installation to 9.2.0.5
These steps only need to be performed on the node that you are installing from.
Run the installer from the 9.2.0.5 staging location:
$ cd $ORACLE_HOME/9205/Disk1
$ ./runInstaller
Respond to the installer prompts as shown below:
* At the "Welcome Screen", click Next.
* View the "Cluster Node Selection Screen", click Next.
* At the "File Locations Screen", make sure the source location is
to the products.xml file in the 9.2.0.5 patchset location under
Disk1/stage. Also verify the destination listed is your ORACLE_HOME
directory. Change the $ORACLE_HOME name from the dropdown list to the
9.2 $ORACLE_HOME name. Click Next.
* At the "Available Products Screen", Check "Oracle9iR2 PatchSets
9.2.0.5.0". Click Next.
* Click Install at the summary screen.
* You will now get a progress window, run root.sh when prompted.
* You will then see the end of installation screen. Click Exit and
confirm by clicking Yes.
3.3 Start the GSD (Global Service Daemon)
These steps need to be performed on ALL nodes.
Start the GSD on each node with:
% gsdctl start
Successfully started GSD on local node
Then check the status with:
% gsdctl stat
GSD is running on the local node
If the GSD does not stay up, try running 'srvconfig -init -f' from the
OS prompt. If you get a raw device exception error or PRKR-1064 error
then see the following note to troubleshoot:
Note 212631.1 - Resolving PRKR-1064 in a RAC Environment
Note 212631.1 - Resolving PRKR-1064 in a RAC Environment
Note: After confirming that GSD starts, if you are on Redhat Advanced
Server 3.0, restore gcc296:
rm /usr/bin/gcc
mv /usr/bin/gcc3.2.3 /usr/bin/gcc
rm /usr/bin/g++
mv /usr/bin/g++3.2.3 /usr/bin/g++
3.4 Create a RAC Database using the Oracle Database Configuration Assistant
These steps only need to be performed on the node that you are
installing from (typically Node 1).
The Oracle Database Configuration Assistant (DBCA) will create a
database for you. The DBCA creates your database using the optimal
flexible architecture (OFA). This means the DBCA creates your database
files, including the default server parameter file, using standard
file naming and file placement practices. The primary phases of DBCA
processing are:-
* Verify that you correctly configured the shared disks for each
tablespace (for non-cluster file system platforms)
* Create the database
* Configure the Oracle network services
* Start the database instances and listeners
Oracle Corporation recommends that you use the DBCA to create your
database. This is because the DBCA preconfigured databases optimize
your environment to take advantage of Oracle9i features such as the
server parameter file and automatic undo management. The DBCA also
enables you to define arbitrary tablespaces as part of the database
creation process. So even if you have datafile requirements that
differ from those offered in one of the DBCA templates, use the DBCA.
You can also execute user-specified scripts as part of the database
creation process.
Note: Prior to running the DBCA it may be necessary to run the NETCA
tool or to manually set up your network files. To run the NETCA tool
execute the command netca from the $ORACLE_HOME/bin directory. This
will configure the necessary listener names and protocol addresses,
client naming methods, Net service names and Directory server usage.
If you are using OCFS or NFS, launch DBCA with the
-datafileDestination option and point to the shared location where
Oracle datafiles will be stored. Example:
% cd $ORACLE_HOME/bin
% dbca -datafileDestination /ocfs/oradata
If you are using RAW, launch DBCA without the -datafileDestination
option. Example:
% cd $ORACLE_HOME/bin
% dbca
Respond to the DBCA prompts as shown below:
* Choose Oracle Cluster Database option and select Next.
* The Operations page is displayed. Choose the option Create a
Database and click Next.
* The Node Selection page appears. Select the nodes that you want
to configure as part of the RAC database and click Next.
* The Database Templates page is displayed. The templates other
than New Database include datafiles. Choose New Database and then
click Next. Note: The Show Details button provides information on the
database template selected.
* DBCA now displays the Database Identification page. Enter the
Global Database Name and Oracle System Identifier (SID). The Global
Database Name is typically of the form name.domain, for example
mydb.us.oracle.com while the SID is used to uniquely identify an
instance (DBCA should insert a suggested SID, equivalent to name1
where name was entered in the Database Name field). In the RAC case
the SID specified will be used as a prefix for the instance number.
For example, MYDB, would become MYDB1, MYDB2 for instance 1 and 2
respectively.
* The Database Options page is displayed. Select the options you
wish to configure and then choose Next. Note: If you did not choose
New Database from the Database Template page, you will not see this
screen.
* Select the connection options desired from the Database
Connection Options page. Click Next.
* DBCA now displays the Initialization Parameters page. This page
comprises a number of Tab fields. Modify the Memory settings if
desired and then select the File Locations tab to update information
on the Initialization Parameters filename and location. The option
Create persistent initialization parameter file is selected by
default. If you have a cluster file system, then enter a file system
name, otherwise a raw device name for the location of the server
parameter file (spfile) must be entered. The button File Location
Variables… displays variable information. The button All
Initialization Parameters… displays the Initialization Parameters
dialog box. This box presents values for all initialization parameters
and indicates whether they are to be included in the spfile to be
created through the check box, included (Y/N). Instance specific
parameters have an instance value in the instance column. Complete
entries in the All Initialization Parameters page and select Close.
Note: There are a few exceptions to what can be altered via this
screen. Ensure all entries in the Initialization Parameters page are
complete and select Next.
* DBCA now displays the Database Storage Window. This page allows
you to enter file names for each tablespace in your database.
* The Database Creation Options page is displayed. Ensure that the
option Create Database is checked and click Finish.
* The DBCA Summary window is displayed. Review this information
and then click OK. Once you click the OK button and the summary
screen is closed, it may take a few moments for the DBCA progress bar
to start. DBCA then begins to create the database according to the
values specified.
During the database creation process, you may see the following error:
ORA-29807: specified operator does not exist
This is a known issue (bug 2925665). You can click on the "Ignore"
button to continue. Once DBCA has completed database creation,
remember to run the 'prvtxml.plb' script from $ORACLE_HOME/rdbms/admin
independently, as the user SYS. It is also advised to run the
'utlrp.sql' script to ensure that there are no invalid objects in the
database at this time.
A new database now exists. It can be accessed via Oracle SQL*PLUS or
other applications designed to work with an Oracle RAC database.
Additional database configuration best practices can be found in the
following note:
Note 240575.1 - RAC on Linux Best Practices
Note 240575.1 - RAC on Linux Best Practices
4.0 Administering Real Application Clusters Instances
Oracle Corporation recommends that you use SRVCTL to administer your
Real Application Clusters database environment. SRVCTL manages
configuration information that is used by several Oracle tools. For
example, Oracle Enterprise Manager and the Intelligent Agent use the
configuration information that SRVCTL generates to discover and
monitor nodes in your cluster. Before using SRVCTL, ensure that your
Global Services Daemon (GSD) is running after you configure your
database. To use SRVCTL, you must have already created the
configuration information for the database that you want to
administer. You must have done this either by using the Oracle
Database Configuration Assistant (DBCA), or by using the srvctl add
command as described below.
To display the configuration details for, example, databases racdb1/2,
on nodes racnode1/2 with instances racinst1/2 run:-
$ srvctl config
racdb1
racdb2
$ srvctl config -p racdb1 -n racnode1
racnode1 racinst1 /u01/app/oracle/product/9.2.0
$ srvctl status database -d racdb1
Instance racinst1 is running on node racnode1
Instance racinst2 is running on node racnode2
Examples of starting and stopping RAC follow:-
$ srvctl start database -d racdb2
$ srvctl stop database -d racdb2
$ srvctl stop instance -d racdb1 -i racinst2
$ srvctl start instance -d racdb1 -i racinst2
For further information on srvctl and gsdctl see the Oracle9i Real
Application Clusters Administration manual.
5.0 References
* 9.2.0.5 Patch Set Notes
* Tips for Installing and Configuring Oracle9i RealTips for
Installing and Configuring Oracle9i Real Application Clusters on Red
Hat Linux Advanced Server
* Note 201370.1 - LINUX Quick Start Guide - 9.2.0 RDBMSNote
201370.1 - LINUX Quick Start Guide - 9.2.0 RDBMS Installation
* Note 252217.1 - Requirements for Installing Oracle 9iR2 on RHEL3
* Note 240575.1 - RAC on Linux Best Practices
* Note 240575.1 - RAC on Linux Best Practices Note 222746.1 - RAC
Linux 9.2: Configuration of cmcfg.oraNote 222746.1 - RAC Linux 9.2:
Configuration of cmcfg.ora and ocmargs.ora
* Note 212631.1 - Resolving PRKR-1064 in a RAC EnvironmentNote
212631.1 - Resolving PRKR-1064 in a RAC Environment
* Note 220178.1 - Installing and setting up ocfs on Linux -Note
220178.1 - Installing and setting up ocfs on Linux - Basic Guide
* Note 246205.1 - Configuring Raw Devices for RealNote 246205.1 -
Configuring Raw Devices for Real Application Clusters on Linux
* Note 210889.1 - RAC Installation with a NetApp Filer inNote
210889.1 - RAC Installation with a NetApp Filer in Red Hat Linux
Environment
* Note 153960.1 FAQ: X Server testing and troubleshootingNote
153960.1 FAQ: X Server testing and troubleshooting
* Note 232355.1 - Hangcheck Timer FAQ
* Note 232355.1 - Hangcheck Timer FAQ RAC/Linux certification matrix
* RAC/Linux certification matrix Oracle9i Real Application
Clusters AdministrationOracle9i Real Application Clusters
Administration
* Oracle9i Real Application Clusters Concepts
* Oracle9i Real Application Clusters Concepts Oracle9i Real
Application Clusters Deployment andOracle9i Real Application Clusters
Deployment and Performance
* Oracle9i Real Application Clusters Setup andOracle9i Real
Application Clusters Setup and Configuration
* Oracle9i Installation Guide Release 2 for UNIXOracle9i
Installation Guide Release 2 for UNIX Systems: AIX-Based Systems,
Compaq Tru64 UNIX, HP 9000 Series HP-UX, Linux Intel, and Sun Solaris
Note: This note was created for 9i RAC. The 10g Oracle documentation
provides installation instructions for 10g RAC. These instructions
can be found on OTN:
Oracle(r) Real Application Clusters Installation andOracle(r) Real
Application Clusters Installation and Configuration Guide
10g Release 1 (10.1) for AIX-Based Systems, hp HP-UX PA-RISC (64-bit),
hp Tru64 UNIX, Linux, Solaris Operating System (SPARC 64-bit)
Purpose
This document will provide the reader with step-by-step instructions
on how to install a cluster, install Oracle Real Application Clusters
(RAC) (Version 9.2.0.5), and start a cluster database on Linux. For
additional explanation or information on any of these steps, please
see the references listed at the end of this document.
Disclaimer: If there are any errors or issues prior to step 2, please
contact your Linux distributor.
The information contained here is as accurate as possible at the time
of writing.
* 1. Configuring the Cluster Hardware
o 1.1 Minimal Hardware list / System Requirements
+ 1.1.1 Hardware
+ 1.1.2 Software
o 1.2 Installing the Shared Disk Subsystem
o 1.3 Configuring the Cluster Interconnect and Public Network Hardware
* 2. Creating a cluster
o 2.1 UNIX Pre-installation tasks
o 2.2 Configuring the Shared Disks
o 2.3 Run the Oracle Universal Installer to install the
9.2.0.4 ORACM (Oracle Cluster Manager)
o 2.4 Configure the hangcheck-timer
o 2.5 Install Version 10.1.0.2 of the Oracle Universal Installer
o 2.6 Run the 10.1.0.2 Oracle Universal Installer to patch
the Oracle Cluster Manager (ORACM) to 9.2.0.5
o 2.7 Modify the ORACM configuration files to utilize the
hangcheck-timer
o 2.8 Start the ORACM (Oracle Cluster Manager)
* 3. Installing RAC
o 3.1 Install 9.2.0.4 RAC
o 3.2 Patch the RAC Installation to 9.2.0.5
o 3.3 Start the GSD (Global Service Daemon)
o 3.4 Create a RAC Database using the Oracle Database
Configuration Assistant
* 4. Administering Real Application Clusters Instances
* 5. References
1. Configuring the Clusters Hardware<>
1.1 Minimal Hardware list / System Requirements
Please check the RAC/Linux certification matrix for information on
currentlyRAC/Linux certification matrix for information on currently
supported hardware/software.
1.1.1 Hardware
* Requirements:
o Refer to the RAC/Linux certification matrix for
information onRAC/Linux certification matrix for information on
supported configurations. Ensure that the system has at least the
following resources:
- 400 MB in /tmp
- 512 MB of Physical Memory (RAM)
- Three times the amount of Physical Memory for Swap
space (unless the system exceeds 1 GB of Physical Memory, where two
times the amount of Physical Memory for Swap space is sufficient)
An example system disk layout is as follows:-
A sample system disk layout
Slice
Contents
Allocation (in Mbytes)
0
/
2000 or more
1
/boot
64
2
/tmp
1000
3
/usr
3000-7000 depending on operating system and packages installed
4
/var
512 (can be more if required)
5
swap
Three times the amount of Physical Memory for Swap space (unless the
system exceeds 1 GB of Physical Memory, where two times the amount of
Physical Memory for Swap space is sufficient).
6
/home
2000 (can be more if required)
1.1.2 Software
* For RAC on Linux support, consult the operating system vendor
and see the RAC/Linux certification matrix.
* RAC/Linux certification matrix. Make sure you have make and
rsh-server packages installed, check with:
$rpm -q rsh-server make
rsh-server-0.17-5
make-3.79.1-8
If these are not installed, use your favorite package manager to
install them.
1.1.3 Patches
Consult with your operating system vendor to get on the latest patch
version of the kernel.
1.2 Installing the Shared Disk Subsystem
This is highly dependent on the subsystem you have chosen. Please
refer to your hardware documentation for installation and
configuration instructions on Linux. Additional drivers and patches
might be required. In this article we assume that the shared disk
subsystem is correctly installed and that the shared disks are visible
to all nodes in the cluster.
1.3 Configuring the Cluster Interconnect and Public Network Hardware
If not already installed, install host adapters in your cluster nodes.
For the procedure on installing host adapters, see the documentation
that shipped with your host adapters and node hardware.
Each system will have at least an IP address for the public network
and one for the private cluster interconnect. For the public network,
get the addresses from your network manager. For the private
interconnect use 1.1.1.1 , 1.1.1.2 for the first and second node. Make
sure to add all addresses in /etc/hosts.
[oracle@opcbrh1 oracle]$ more /etc/hosts
ex:
9.25.120.143 rac1 #Oracle 9i Rac node 1 - public network
9.25.120.143 rac2 #Oracle 9i Rac node 2 - public network
1.1.1.1 int-rac1 #Oracle 9i Rac node 1 - interconnect
1.1.1.2 int-rac2 #Oracle 9I Rac node 2 - interconnect
Use your favorite tool to configure these adapters. Make sure your
public network is the primary (eth0).
Interprocess communication is an important issue for RAC since cache
fusion transfers buffers between instances using this mechanism. Thus,
networking parameters are important for RAC databases. The values in
the following table are the recommended values. These are NOT the
default on most distributions.
Parameter
Meaning
Value
/proc/sys/net/core/rmem_default
The default setting in bytes of the socket receive buffer
262144
/proc/sys/net/core/rmem_max
The maximum socket receive buffer size in bytes
262144
/proc/sys/net/core/wmem_default
The default setting in bytes of the socket send buffer
262144
/proc/sys/net/core/wmem_max
The maximum socket send buffer size in bytes
262144
You can see these settings with:
$ cat /proc/sys/net/core/rmem_default
Change them with:
$ echo 262144 > /proc/sys/net/core/rmem_default
This will need to be done each time the system boots. Some
distributions already have setup a method for this during boot. On Red
Hat , this can be configured in /etc/sysctl.conf (like :
net.core.rmem_default = 262144).
2. Creating a Cluster
On Linux, the cluster software required to run Real Application
Clusters is included in the Oracle distribution.
The Oracle Cluster Manager (ORACM) installation process includes eight
major tasks.
1. UNIX pre-installation tasks.
2. Configuring the shared disks
3. Run the Oracle Universal Installer to install the 9.2.0.4 ORACM
(Oracle Cluster Manager)
4. Configure the hangcheck-timer.
5. Install version 10.1.0.2 of the Oracle Universal Installer
6. Run the 10.1.0.2 Oracle Universal Installer to patch the Oracle
Cluster Manager (ORACM) to 9.2.0.5
7. Modify the ORACM configuration files to utilize the hangcheck-timer.
8. Start the ORACM (Oracle Cluster Manager)
2.1 UNIX Pre-installation tasks
These steps need to be performed on ALL nodes.
* First, on each node, create the Oracle group. Example:
# groupadd dba -g 501
* Next, make the Oracle user's home directory. Example:
# mkdir -p /u01/home/oracle
* On each node, create the Oracle user. Make sure that the Oracle
user is part of the dba group. Example:
# useradd -c "Oracle Software Owner" -G dba -u 101 -m -d
/u01/home/oracle -s /bin/csh oracle
* On each node, Create a mount point for the Oracle software
installation (at least 2.5 GB, typically /u01). The oracle user
should own this mount point and all of the directories below the mount
point. Example:
# mkdir /u01
# chown -R oracle.dba /u01
# chmod -R ug=rwx,o=rx /u01
* Once this is done, test the permissions on each node to ensure
that the oracle user can write to the new mount points. Example:
# su - oracle
$ touch /u01/test
$ ls -l /u01/test
-rw-rw-r-- 1 oracle dba 0 Aug 15 09:36 /u01/test
* Depending on your Linux distribution, make sure inetd or xinetd
is started on all nodes and that the ftp, telnet, shell and login (or
rsh) services are enabled (see /etc/inetd.conf or /etc/xinetd.conf and
/etc/xinetd.d). Example:
# more /etc/xinetd.d/telnet
# default: on
# description: The telnet server serves telnet sessions; it uses # unencrypted username/password pairs for authentication.
service telnet
{
flags = REUSE
socket_type = stream
wait = no
user = root
server = /usr/sbin/in.telnetd
log_on_failure += USERID
disable = no
}
In this example, disable should be set to 'no'.
* On the node from which you will run the Oracle Universal
Installer, set up user equivalence by adding entries for all nodes in
the cluster, including the local node, to the .rhosts file of the
oracle account, or the /etc/hosts.equiv file.
Sample entries in /etc/hosts.equiv file:
rac1
rac2
int-rac1
int-rac2
* As oracle user, check for user equivalence for the oracle
account by performing a remote copy (rcp) to each node (public and
private) in the cluster. Example:
RAC1:
$ touch /u01/test
$ rcp /u01/test rac2:/u01/test1
$ rcp /u01/test int-rac2:/u01/test2
RAC2:
$ touch /u01/test
$ rcp /u01/test rac1:/u01/test1
$ rcp /u01/test int-rac1:/u01/test2
$ ls /u01/test*
/u01/test /u01/test1 /u01/test2
RAC1:
$ ls /u01/test*
/u01/test /u01/test1 /u01/test2
Note: If you are prompted for a password, you have not given the
oracle account the same attributes on all nodes. You must correct this
because the Oracle Universal Installer cannot use the rcp command to
copy Oracle products to the remote node's directories without user
equivalence.
System Kernel Parameters
Verify operating system kernel parameters are set to appropriate levels:
Kernel Parameter
Setting
Purpose
SHMMAX
2147483648
Maximum allowable size of one shared memory segment.
SHMMIN
1
Minimum allowable size of a single shared memory segment.
SHMMNI
100
Maximum number of shared memory segments in the entire system.
SHMSEG
10
Maximum number of shared memory segments one process can attach.
SEMMNI
100
Maximum number of semaphore sets in the entire system.
SEMMSL
250
Minimum recommended value. SEMMSL should be 10 plus the largest
PROCESSES parameter of any Oracle database on the system.
SEMMNS
1000
Maximum semaphores on the system. This setting is a minimum
recommended value. SEMMNS should be set to the sum of the PROCESSES
parameter for each Oracle database, add the largest one twice, plus
add an additional 10 for each database.
SEMOPM
100
Maximum number of operations per semop call.
You will have to set the correct parameters during system startup, so
include them in your startup script (startoracle_root.sh):
$ export SEMMSL=100
$ export SEMMNS=1000
$ export SEMOPM=100
$ export SEMMNI=100
$ echo $SEMMSL $SEMMNS $SEMOPM $ SEMMNI > /proc/sys/kernel/sem
$ export SHMMAX=2147483648
$ echo $SHMMAX > /proc/sys/kernel/shmmax
Check these with:
$ cat /proc/sys/kernel/sem
$ cat /proc/sys/kernel/shmmax
You might want to increase the maximum number of file handles, include
this in your startup script or use /etc/sysctl.conf :
$ echo 65536 > /proc/sys/fs/file-max
To allow your oracle processes to use these file handles, add the
following to your oracle account login script (ex.: .profile)
$ ulimit -n 65536
Note: This will only allow you to set the soft limit as high as the
hard limit. You might have to increase the hard limit on system level.
This can be done by adding ulimit -Hn 65536 to /etc/initscript. You
will have to reboot the system to make this active. Sample
/etc/initscript:
ulimit -Hn 65536
eval exec "$4"
Establish Oracle environment variables: Set the following Oracle
environment variables:
Environment Variable
Suggested value
ORACLE_HOME
eg /u01/app/oracle/product/920
ORACLE_TERM
xterm
PATH
/u01/app/oracle/product/9.2.0/bin: /usr/ccs/bin:/usr/bin/X11/:/usr/local/bin
and any other items you require in your PATH
DISPLAY
(review Note:153960.1 for detailed information)
Note:153960.1 for detailed information)
TMPDIR
Set a temporary directory path for TMPDIR with at least 100 Mb of free
space to which the OUI has write permission.
ORACLE_SID
Set this to what you will call your database instance. This should be
UNIQUE on each node.
It is best to save these in a .login or .profile file so that you do
not have to set the environment every time you log in.
* Create the directory /var/opt/oracle and set ownership to the
oracle user. Example:
$ mkdir /var/opt/oracle
$ chown oracle.dba /var/opt/oracle
* Set the oracle user's umask to "022" in you ".profile" or
".login" file. Example:
$ umask 022
Note: There is a verification script InstallPrep.sh available which
may beverification script InstallPrep.sh available which may be
downloaded and run prior to the installation of Oracle Real
Application Clusters. This script verifies that the system is
configured correctly according to the Installation Guide. The output
of the script will report any further tasks that need to be performed
before successfully installing Oracle 9.x DataServer (RDBMS). This
script performs the following verifications:-
* ORACLE_HOME Directory Verification
* UNIX User/umask Verification
* UNIX Group Verification
* Memory/Swap Verification
* TMP Space Verification
* Real Application Cluster Option Verification
* Unix Kernel Verification
. ./InstallPrep.sh
You are currently logged on as oracle
Is oracle the unix user that will be installing Oracle Software? y or n
y
Enter the unix group that will be used during the installation
Default: dba
Enter the version of Oracle RDBMS you will be installing
Enter either : 901 OR 920 - Default: 920
920
The rdbms version being installed is 920
Enter Location where you will be installing Oracle
Default: /u01/app/oracle/product/oracle9i
/u01/app/oracle/product/9.2.0
Your Operating System is Linux
Gathering information... Please wait
JDK check is ignored for Linux since it is provided by Oracle
Checking unix user ...
Checking unix umask ...
umask test passed
Checking unix group ...
Unix Group test passed
Checking Memory & Swap...
Memory test passed
/tmp test passed
Checking for a cluster...
Linux Cluster test section has not been implemented yet
No cluster warnings detected
Processing kernel parameters... Please wait
Running Kernel Parameter Report...
Check the report for Kernel parameter verification\n
Completed.
/tmp/Oracle_InstallPrep_Report has been generated
Please review this report and resolve all issues before attempting to
install the Oracle Database Software
Note: If you get an error like this:
InstallPrep.sh: line 45: syntax error near unexpected token `fi'
or
./InstallPrep.sh: Command not found.
Then you need to copy the script into a text file (it will not run if
the file is in binary format).
2.2 Configuring the Shared Disks
For 9.2 Real Application Clusters on Linux, you can use either OCFS
(Oracle Cluster Filesystem), RAW, or NFS (Redhat and Network Appliance
Only) for storage of Oracle database files.
* For more information on setting up OCFS for RAC on Linux, see
the following MetaLink Note:
Note 220178.1 - Installing and setting up ocfs on Linux -
BasicNote 220178.1 - Installing and setting up ocfs on Linux - Basic
Guide
* For more information on setting up RAW for RAC on Linux, see the
following MetaLink Note:
Note 246205.1 - Configuring Raw Devices for Real
ApplicationNote 246205.1 - Configuring Raw Devices for Real
Application Clusters on Linux
* For more information on setting up NFS for RAC on Linux, see the
following MetaLink Note (Steps 1-6):
Note 210889.1 - RAC Installation with a NetApp Filer in Red
HatNote 210889.1 - RAC Installation with a NetApp Filer in Red Hat
Linux Environment
2.3 Run the Oracle Universal Installer to install the 9.2.0.4 ORACM
(Oracle Cluster Manager)
These steps only need to be performed on the node that you are
installing from (typically Node 1).
* If you are using OCFS or NFS for your shared storage, pre-create
the quorum file and srvm file. Example:
# dd if=/dev/zero of=/ocfs/quorum.dbf bs=1M count=20
# dd if=/dev/zero of=/ocfs/srvm.dbf bs=1M count=100
# chown root:dba /ocfs/quorum.dbf
# chmod 664 /ocfs/quorum.dbf
# chown oracle:dba /ocfs/srvm.dbf
# chmod 664 /ocfs/srvm.dbf
* Verify the Environment - Log off and log on as the oracle user
to ensure all environment variables
are set correctly. Use the following command to view them:
% env | more
Note: If you are on Redhat Advanced Server 3.0, you will need to
temporarily use an older gcc for the install:
mv gcc gcc3.2.3
mv g++ g++3.2.3
ln -s /usr/bin/gcc296 /usr/bin/gcc
ln -s /usr/bin/g++296 /usr/bin/g++
You will also need to apply patch 3006854 if on RHAS 3.0
* Before attempting to run the Oracle Universal Installer, verify
that you can successfully run the following command:
% /usr/bin/X11/xclock
* If this does not display a clock on your display screen, please
review the following article:
Note 153960.1 FAQ: X Server testing and troubleshooting
Note 153960.1 FAQ: X Server testing and troubleshooting
* Start the Oracle Universal Installer and install the RDBMS
software - Follow these procedures to use the Oracle Universal
Installer to install the Oracle Cluster Manager software. Oracle9i is
supplied on multiple CD-ROM disks. During the installation process it
is necessary to switch between the CD-ROMS. OUI will manage the
switching between CDs.
Use the following commands to start the installer:
% cd /tmp
% /cdrom/runInstaller
Or cd to /stage/Disk1 and run ./runInstaller
Respond to the installer prompts as shown below:
* At the "Welcome Screen", click Next.
If this is your first install on this machine:
* If the "Inventory Location" screen appears, enter the inventory
location then click OK.
* If the "Unix Group Name" screen appears, enter the unix group
name created in step 2.1 then click Next.
* At this point you may be prompted to run /tmp/orainstRoot.sh.
Run this and click Continue.
* At the "File Locations Screen", verify the destination listed is
your ORACLE_HOME directory. Also enter a NAME to identify this
ORACLE_HOME. The NAME can be anything.
* At the "Available Products Screen", Check "Oracle Cluster
Manager". Click Next.
* At the public node information screen, enter the public node
names and click Next.
* At the private node information screen, enter the interconnect
node names. Click Next.
* Enter the full name of the file or raw device you have created
for the ORACM Quorum disk information. Click Next.
* Press Install at the summary screen.
* You will now briefly get a progress window followed by the end
of installation screen. Click Exit and confirm by clicking Yes.
Note: Create the directory $ORACLE_HOME/oracm/log (as oracle) on the
other nodes if it doesn't exist.
2.4 Configure the hangcheck-timer
These steps need to be performed on ALL nodes.
Some kernel versions include the hangcheck-timer with the kernel. You
can check to see if your kernel contains the hangcheck-timer by
running:
# /sbin/lsmod
Then you will see hangcheck-timer listed. Also verify that
hangcheck-timer is starting in your /etc/rc.local file (on Redhat) or
/etc/init.d/boot.local (on United Linux). If you see hangcheck-timer
listed in lsmod and in the rc.local file or boot.local, you can skip
to section 2.5.
If hangcheck-timer is not listed here and you are not using Redhat
Advanced Server, see the following note for information on obtaining
the hangcheck-timer:
Note 232355.1 - Hangcheck Timer FAQ
Note 232355.1 - Hangcheck Timer FAQ
If you are on Redhat Advanced Server, you can either apply the latest
errata version (> 12) or go to MetaLink - Patches:
Enter 2594820 in the Patch Number field.
Click Go.
Click Download.
Save the file p2594820_20_LINUX.zip to the local disk, such as /tmp.
Unzip the file. The output should be similar to the following:
inflating: hangcheck-timer-2.4.9-e.3-0.4.0-1.i686.rpm
inflating: hangcheck-timer-2.4.9-e.3-enterprise-0.4.0-1.i686.rpm
inflating:
hangcheck-timer-2.4.9-e.3-smp-0.4.0-1.i686.rpm
inflating: README.TXT
Run the uname -a command to identify the RPM that corresponds to the
kernel in use. This will show if the kernel is single CPU, smp, or
enterprise.
The p2594820_20_LINUX.zip file contains four files. The following
describes the files:
hangcheck-timer-2.4.9-e.3-0.4.0-1.i686.rpm is for single CPU machines
hangcheck-timer-2.4.9-e.3-enterprise=0.4.0-1.i686.rpm is for
multi-processor machines with more than 4 GB of RAM
hangcheck-timer-2.4.9-e.3-smp-0.4.0-1.i686.rpm is for multi-processor
machines with 4 GB of RAM or less
The three RPMs will work for the e3 kernels in Red Hat Advanced Server
2.1 gold and the e8 kernels in the latest Red Hat Advanced Server 2.1
errata release. These RPMs are for Red Hat Advanced Server 2.1 kernels
only.
Transfer the relevant hangcheck-timer RPM to the /tmp directory of the
Oracle Real Applications Cluster node.
Log in to the node as the root user.
Change to the /tmp directory.
Run the following command to install the module:
#rpm -ivh hangcheck-timer RPM name
If you have previously installed RAC on this cluster, remove or
disable the mechanism that loads the softdog module at system start
up, if that module is not used by other software on the node. This is
necessary for subsequent steps in the installation process. This step
may require log in as the root user. One method for setting up
previous versions of Oracle Real Applications Clusters involved
loading the softdog module in the /etc/rc.local (on Redhat) or
/etc/init.d/boot.local (on United Linux) file. If this method was
used, then remove or comment out the following line in the file:
/sbin/insmod softdog nowayout=0 soft_noboot=1 soft_margin=60
Append the following line to the /etc/rc.local file (on Redhat) or
/etc/init.d/boot.local (on United Linux):
/sbin/insmod hangcheck-timer hangcheck_tick=30 hangcheck_margin=180
Load the hangcheck-timer kernel module using the following command as root user:
# /sbin/insmod hangcheck-timer hangcheck_tick=30 hangcheck_margin=180
Repeat the above steps on all Oracle Real Applications Clusters nodes
where the kernel module needs to be installed.
Run dmesg after the module is loaded. Note the build number while
running the command. The following is the relevant information output:
build 334adfa62c1a153a41bd68a787fbe0e9
The build number is required when making support calls.
2.5 Install Version 10.1.0.2 of the Oracle Universal Installer
These steps need to be performed on ALL nodes.
Download the 9.2.0.5 patchset from MetaLink - Patches:
Enter 3501955 in the Patch Number field.
Click Go.
Click Download.
* Place the file in a patchset directory on the node you are
installing from. Example:
$ mkdir $ORACLE_HOME/9205
$ cp p3501955_9205_LINUX.zip $ORACLE_HOME/9205
* Unzip the file:
$ cd $ORACLE_HOME/9205
$ unzip p3501955_9205_LINUX.zip
Archive: p3501955_9205_LINUX
inflating: 9205_lnx32_release.cpio
inflating: README.html
inflating: ReleaseNote9205.pdf
* Run CPIO against the file:
$ cpio -idmv < 9205_lnx32_release.cpio
Run the installer from the 9.2.0.5 staging location:
$ cd $ORACLE_HOME/9205/Disk1
$ ./runInstaller
Respond to the installer prompts as shown below:
* At the "Welcome Screen", click Next.
* At the "File Locations Screen", Change the $ORACLE_HOME name
from the dropdown list to the 9.2 $ORACLE_HOME name. Click Next.
* On the "Available Products Screen", Check "Oracle Universal
Installer 10.1.0.2. Click Next.
* Press Install at the summary screen.
* You will now briefly get a progress window followed by the end
of installation screen. Click Exit and confirm by clicking Yes.
Remember to install the 10.1.0.2 Installer on ALL cluster nodes. Note
that you may need to ADD the 9.2 $ORACLE_HOME name on the "File
Locations Screen" for other nodes. It will ask if you want to specify
a non-empty directory, say "Yes".
2.6 Run the 10.1.0.2 Oracle Universal Installer to patch the Oracle
Cluster Manager (ORACM) to 9.2.0.5
These steps only need to be performed on the node that you are
installing from (typically Node 1).
The 10.1.0.2 OUI will use SSH (Secure Shell) if it is configured. If
it is not configured it will use RSH (Remote Shell). If you have SSH
configured on your cluster, test and make sure that you can SSH and
SCP to all nodes of the cluster without being prompted. If you do not
have SSH configured, skip this step and run the installer from
$ORACLE_BASE/oui/bin as noted below.
SSH Test:
As oracle user, check for user equivalence for the oracle account by
performing a secure copy (scp) to each node (public and private) in
the cluster. Example:
RAC1:
$ touch /u01/sshtest
$ scp /u01/sshtest rac2:/u01/sshtest1
$ scp /u01/sshtest int-rac2:/u01/sshtest2
RAC2:
$ touch /u01/sshtest
$ scp /u01/sshtest rac1:/u01/sshtest1
$ scp /u01/sshtest int-rac1:/u01/sshtest2
$ ls /u01/sshtest*
/u01/sshtest /u01/sshtest1 /u01/sshtest2
RAC1:
$ ls /u01/sshtest*
/u01/sshtest /u01/sshtest1 /u01/sshtest2
Run the installer from the 9.2.0.5 oracm staging location:
$ cd $ORACLE_HOME/9205/Disk1/oracm
$ ./runInstaller
Respond to the installer prompts as shown below:
* At the "Welcome Screen", click Next.
* At the "File Locations Screen", make sure the source location is
to the products.xml file in the 9.2.0.5 patchset location under
Disk1/stage. Also verify the destination listed is your ORACLE_HOME
directory. Change the $ORACLE_HOME name from the dropdown list to the
9.2 $ORACLE_HOME name. Click Next.
* At the "Available Products Screen", Check "Oracle9iR2 Cluster
Manager 9.2.0.5.0". Click Next.
* At the public node information screen, enter the public node
names and click Next.
* At the private node information screen, enter the interconnect
node names. Click Next.
* Click Install at the summary screen.
* You will now briefly get a progress window followed by the end
of installation screen. Click Exit and confirm by clicking Yes.
2.7 Modify the ORACM configuration files to utilize the hangcheck-timer.
These steps need to be performed on ALL nodes.
Modify the $ORACLE_HOME/oracm/admin/cmcfg.ora file:
Add the following line:
KernelModuleName=hangcheck-timer
Adjust the value of the MissCount line based on the sum of the
hangcheck_tick and hangcheck_margin values. (> 210)
MissCount=210
Make sure that you can ping each of the names listed in the private
and public node name sections from each node. Example:
$ ping rac2
PING opcbrh2.us.oracle.com (138.1.137.46) from 138.1.137.45 :
56(84) bytes of data.
64 bytes from opcbrh2.us.oracle.com (138.1.137.46): icmp_seq=0
ttl=255 time=1.295 msec
64 bytes from opcbrh2.us.oracle.com (138.1.137.46): icmp_seq=1
ttl=255 time=154 usec
Verify that a valid CmDiskFile line exists in the following format:
CmDiskFile=file or raw device name
In the preceding command, the file or raw device must be valid. If a
file is used but does not exist, then the file will be created if the
base directory exists. If a raw device is used, then the raw device
must exist and have the correct ownership and permissions. Sample
cmcfg.ora file:
ClusterName=Oracle Cluster Manager, version 9i
MissCount=210
PrivateNodeNames=int-rac1 int-rac2
PublicNodeNames=rac1 rac2
ServicePort=9998
CmDiskFile=/u04/quorum.dbf
KernelModuleName=hangcheck-timer
HostName=int-rac1
Note: The cmcfg.ora file should be the same on both nodes with the
exception of the HostName parameter which should be set to the local
(internal) hostname.
Make sure all of these changes have been made to all RAC nodes. More
information on ORACM parameters can be found in the following note:
Note 222746.1 - RAC Linux 9.2: Configuration of cmcfg.ora
andNote 222746.1 - RAC Linux 9.2: Configuration of cmcfg.ora and
ocmargs.ora
Note: At this point it would be a good idea to patch to the latest
ORACM, especially if you have more than 2 nodes. For more information
see:
Note 278156.1 - ORA-29740 or ORA-29702 After Applying 9.2.0.5 Patchset
on RAC / Linux
ORA-29740 or ORA-29702 After Applying 9.2.0.5 Patchset on RAC / Linux
2.8 Start the ORACM (Oracle Cluster Manager)
These steps need to be performed on ALL nodes.
Cd to the $ORACLE_HOME/oracm/bin directory, change to the root user,
and start the ORACM.
$ cd $ORACLE_HOME/oracm/bin
$ su root
# ./ocmstart.sh
oracm &1 >/u01/app/oracle/product/9.2.0/oracm/log/cm.out &
Verify that ORACM is running with the following:
# ps -ef | grep oracm
On RHEL 3.0, add the -m option:
# ps -efm | grep oracm
You should see several oracm threads running. Also verify that the
ORACM version is the same on each node:
# cd $ORACLE_HOME/oracm/log
# head -1 cm.log
oracm, version[ 9.2.0.2.0.49 ] started {Fri May 14 09:22:28 2004 }
3.0 Installing RAC
The Real Application Clusters installation process includes four major tasks.
1. Install 9.2.0.4 RAC.
2. Patch the RAC Installation to 9.2.0.5.
3. Start the GSD.
4. Create and configure your database.
3.1 Install 9.2.0.4 RAC
These steps only need to be performed on the node that you are
installing from (typically Node 1).
Note: Due to bug 3547724, temporarily create a symbolic link /oradata
directory pointing to an oradata directory with space available as
root prior to running the RAC install:
# mkdir /u04/oradata
# chmod 777 /u04/oradata
# ln -s /u04/oradata /oradata
Install 9.2.0.4 RAC into your $ORACLE_HOME by running the installer
from the 9.2.0.4 cd or your original stage location for the 9.2.0.4
install.
Use the following commands to start the installer:
% cd /tmp
% /cdrom/runInstaller
Or cd to /stage/Disk1 and run ./runInstaller
Respond to the installer prompts as shown below:
* At the "Welcome Screen", click Next.
* At the "Cluster Node Selection Screen", make sure that all RAC
nodes are selected.
* At the "File Locations Screen", verify the destination listed is
your ORACLE_HOME directory and that the source directory is pointing
to the products.jar from the 9.2.0.4 cd or staging location.
* At the "Available Products Screen", check "Oracle 9i Database
9.2.0.4". Click Next.
* At the "Installation Types Screen", check "Enterprise Edition"
(or whichever option your prefer), click Next.
* At the "Database Configuration Screen", check "Software Only".
Click Next.
* At the "Shared Configuration File Name Screen", enter the path
of the CFS or NFS srvm file created at the beginning of step 2.3 or
the raw device created for the shared configuration file. Click Next.
* Click Install at the summary screen. Note that some of the
items installed will say "9.2.0.1" for the version, this is normal
because only some items needed to be patched up to 9.2.0.4.
* You will now get a progress window, run root.sh when prompted.
* You will then see the end of installation screen. Click Exit and
confirm by clicking Yes.
Note: You can now remove the /oradata symbolic link:
# rm /oradata
3.2 Patch the RAC Installation to 9.2.0.5
These steps only need to be performed on the node that you are installing from.
Run the installer from the 9.2.0.5 staging location:
$ cd $ORACLE_HOME/9205/Disk1
$ ./runInstaller
Respond to the installer prompts as shown below:
* At the "Welcome Screen", click Next.
* View the "Cluster Node Selection Screen", click Next.
* At the "File Locations Screen", make sure the source location is
to the products.xml file in the 9.2.0.5 patchset location under
Disk1/stage. Also verify the destination listed is your ORACLE_HOME
directory. Change the $ORACLE_HOME name from the dropdown list to the
9.2 $ORACLE_HOME name. Click Next.
* At the "Available Products Screen", Check "Oracle9iR2 PatchSets
9.2.0.5.0". Click Next.
* Click Install at the summary screen.
* You will now get a progress window, run root.sh when prompted.
* You will then see the end of installation screen. Click Exit and
confirm by clicking Yes.
3.3 Start the GSD (Global Service Daemon)
These steps need to be performed on ALL nodes.
Start the GSD on each node with:
% gsdctl start
Successfully started GSD on local node
Then check the status with:
% gsdctl stat
GSD is running on the local node
If the GSD does not stay up, try running 'srvconfig -init -f' from the
OS prompt. If you get a raw device exception error or PRKR-1064 error
then see the following note to troubleshoot:
Note 212631.1 - Resolving PRKR-1064 in a RAC Environment
Note 212631.1 - Resolving PRKR-1064 in a RAC Environment
Note: After confirming that GSD starts, if you are on Redhat Advanced
Server 3.0, restore gcc296:
rm /usr/bin/gcc
mv /usr/bin/gcc3.2.3 /usr/bin/gcc
rm /usr/bin/g++
mv /usr/bin/g++3.2.3 /usr/bin/g++
3.4 Create a RAC Database using the Oracle Database Configuration Assistant
These steps only need to be performed on the node that you are
installing from (typically Node 1).
The Oracle Database Configuration Assistant (DBCA) will create a
database for you. The DBCA creates your database using the optimal
flexible architecture (OFA). This means the DBCA creates your database
files, including the default server parameter file, using standard
file naming and file placement practices. The primary phases of DBCA
processing are:-
* Verify that you correctly configured the shared disks for each
tablespace (for non-cluster file system platforms)
* Create the database
* Configure the Oracle network services
* Start the database instances and listeners
Oracle Corporation recommends that you use the DBCA to create your
database. This is because the DBCA preconfigured databases optimize
your environment to take advantage of Oracle9i features such as the
server parameter file and automatic undo management. The DBCA also
enables you to define arbitrary tablespaces as part of the database
creation process. So even if you have datafile requirements that
differ from those offered in one of the DBCA templates, use the DBCA.
You can also execute user-specified scripts as part of the database
creation process.
Note: Prior to running the DBCA it may be necessary to run the NETCA
tool or to manually set up your network files. To run the NETCA tool
execute the command netca from the $ORACLE_HOME/bin directory. This
will configure the necessary listener names and protocol addresses,
client naming methods, Net service names and Directory server usage.
If you are using OCFS or NFS, launch DBCA with the
-datafileDestination option and point to the shared location where
Oracle datafiles will be stored. Example:
% cd $ORACLE_HOME/bin
% dbca -datafileDestination /ocfs/oradata
If you are using RAW, launch DBCA without the -datafileDestination
option. Example:
% cd $ORACLE_HOME/bin
% dbca
Respond to the DBCA prompts as shown below:
* Choose Oracle Cluster Database option and select Next.
* The Operations page is displayed. Choose the option Create a
Database and click Next.
* The Node Selection page appears. Select the nodes that you want
to configure as part of the RAC database and click Next.
* The Database Templates page is displayed. The templates other
than New Database include datafiles. Choose New Database and then
click Next. Note: The Show Details button provides information on the
database template selected.
* DBCA now displays the Database Identification page. Enter the
Global Database Name and Oracle System Identifier (SID). The Global
Database Name is typically of the form name.domain, for example
mydb.us.oracle.com while the SID is used to uniquely identify an
instance (DBCA should insert a suggested SID, equivalent to name1
where name was entered in the Database Name field). In the RAC case
the SID specified will be used as a prefix for the instance number.
For example, MYDB, would become MYDB1, MYDB2 for instance 1 and 2
respectively.
* The Database Options page is displayed. Select the options you
wish to configure and then choose Next. Note: If you did not choose
New Database from the Database Template page, you will not see this
screen.
* Select the connection options desired from the Database
Connection Options page. Click Next.
* DBCA now displays the Initialization Parameters page. This page
comprises a number of Tab fields. Modify the Memory settings if
desired and then select the File Locations tab to update information
on the Initialization Parameters filename and location. The option
Create persistent initialization parameter file is selected by
default. If you have a cluster file system, then enter a file system
name, otherwise a raw device name for the location of the server
parameter file (spfile) must be entered. The button File Location
Variables… displays variable information. The button All
Initialization Parameters… displays the Initialization Parameters
dialog box. This box presents values for all initialization parameters
and indicates whether they are to be included in the spfile to be
created through the check box, included (Y/N). Instance specific
parameters have an instance value in the instance column. Complete
entries in the All Initialization Parameters page and select Close.
Note: There are a few exceptions to what can be altered via this
screen. Ensure all entries in the Initialization Parameters page are
complete and select Next.
* DBCA now displays the Database Storage Window. This page allows
you to enter file names for each tablespace in your database.
* The Database Creation Options page is displayed. Ensure that the
option Create Database is checked and click Finish.
* The DBCA Summary window is displayed. Review this information
and then click OK. Once you click the OK button and the summary
screen is closed, it may take a few moments for the DBCA progress bar
to start. DBCA then begins to create the database according to the
values specified.
During the database creation process, you may see the following error:
ORA-29807: specified operator does not exist
This is a known issue (bug 2925665). You can click on the "Ignore"
button to continue. Once DBCA has completed database creation,
remember to run the 'prvtxml.plb' script from $ORACLE_HOME/rdbms/admin
independently, as the user SYS. It is also advised to run the
'utlrp.sql' script to ensure that there are no invalid objects in the
database at this time.
A new database now exists. It can be accessed via Oracle SQL*PLUS or
other applications designed to work with an Oracle RAC database.
Additional database configuration best practices can be found in the
following note:
Note 240575.1 - RAC on Linux Best Practices
Note 240575.1 - RAC on Linux Best Practices
4.0 Administering Real Application Clusters Instances
Oracle Corporation recommends that you use SRVCTL to administer your
Real Application Clusters database environment. SRVCTL manages
configuration information that is used by several Oracle tools. For
example, Oracle Enterprise Manager and the Intelligent Agent use the
configuration information that SRVCTL generates to discover and
monitor nodes in your cluster. Before using SRVCTL, ensure that your
Global Services Daemon (GSD) is running after you configure your
database. To use SRVCTL, you must have already created the
configuration information for the database that you want to
administer. You must have done this either by using the Oracle
Database Configuration Assistant (DBCA), or by using the srvctl add
command as described below.
To display the configuration details for, example, databases racdb1/2,
on nodes racnode1/2 with instances racinst1/2 run:-
$ srvctl config
racdb1
racdb2
$ srvctl config -p racdb1 -n racnode1
racnode1 racinst1 /u01/app/oracle/product/9.2.0
$ srvctl status database -d racdb1
Instance racinst1 is running on node racnode1
Instance racinst2 is running on node racnode2
Examples of starting and stopping RAC follow:-
$ srvctl start database -d racdb2
$ srvctl stop database -d racdb2
$ srvctl stop instance -d racdb1 -i racinst2
$ srvctl start instance -d racdb1 -i racinst2
For further information on srvctl and gsdctl see the Oracle9i Real
Application Clusters Administration manual.
5.0 References
* 9.2.0.5 Patch Set Notes
* Tips for Installing and Configuring Oracle9i RealTips for
Installing and Configuring Oracle9i Real Application Clusters on Red
Hat Linux Advanced Server
* Note 201370.1 - LINUX Quick Start Guide - 9.2.0 RDBMSNote
201370.1 - LINUX Quick Start Guide - 9.2.0 RDBMS Installation
* Note 252217.1 - Requirements for Installing Oracle 9iR2 on RHEL3
* Note 240575.1 - RAC on Linux Best Practices
* Note 240575.1 - RAC on Linux Best Practices Note 222746.1 - RAC
Linux 9.2: Configuration of cmcfg.oraNote 222746.1 - RAC Linux 9.2:
Configuration of cmcfg.ora and ocmargs.ora
* Note 212631.1 - Resolving PRKR-1064 in a RAC EnvironmentNote
212631.1 - Resolving PRKR-1064 in a RAC Environment
* Note 220178.1 - Installing and setting up ocfs on Linux -Note
220178.1 - Installing and setting up ocfs on Linux - Basic Guide
* Note 246205.1 - Configuring Raw Devices for RealNote 246205.1 -
Configuring Raw Devices for Real Application Clusters on Linux
* Note 210889.1 - RAC Installation with a NetApp Filer inNote
210889.1 - RAC Installation with a NetApp Filer in Red Hat Linux
Environment
* Note 153960.1 FAQ: X Server testing and troubleshootingNote
153960.1 FAQ: X Server testing and troubleshooting
* Note 232355.1 - Hangcheck Timer FAQ
* Note 232355.1 - Hangcheck Timer FAQ RAC/Linux certification matrix
* RAC/Linux certification matrix Oracle9i Real Application
Clusters AdministrationOracle9i Real Application Clusters
Administration
* Oracle9i Real Application Clusters Concepts
* Oracle9i Real Application Clusters Concepts Oracle9i Real
Application Clusters Deployment andOracle9i Real Application Clusters
Deployment and Performance
* Oracle9i Real Application Clusters Setup andOracle9i Real
Application Clusters Setup and Configuration
* Oracle9i Installation Guide Release 2 for UNIXOracle9i
Installation Guide Release 2 for UNIX Systems: AIX-Based Systems,
Compaq Tru64 UNIX, HP 9000 Series HP-UX, Linux Intel, and Sun Solaris
Wednesday, August 16, 2006
guidelines for tuning an Oracle database when the main source of contention is I/O-related.
Subject: Tuning I/O-related waits
Doc ID: Note:223117.1 Type: TROUBLESHOOTING
Last Revision Date: 18-MAY-2004 Status: PUBLISHED
-------
PURPOSE
-------
This article provides guidelines for tuning an Oracle database
when the main source of contention is I/O-related.
-------------------
SCOPE & APPLICATION
-------------------
The techniques described here can be followed when:
o Statspack reports show I/O wait events in the "Top 5 Wait Events" section.
o SQL Tracing with wait events of a database session shows it is limited
mainly by I/O wait events.
o Operating System tools show very high utilization or saturation of disks
used for storage of database files.
The article should be of use to Database Administrators, Support Engineers,
Consultants and Database Performance Analysts.
-------------------------
TUNING WITH RESPONSE TIME
-------------------------
A critical activity in Database Performance Tuning is
Response Time Analysis: this consists of finding out where time is being
spent in a database.
TIME is the most important property in Performance Tuning.
Users perceive the performance of a system through the response time
they experience for their transactions or batch jobs.
Response Time Analysis for an Oracle Database is done
using the following equation:
Response Time = Service Time + Wait Time
'Service Time' is measured using the statistic 'CPU used by this session'
'Wait Time' is measured by summing up time spent on Wait Events
Note: although similar in appearance, this equation is not the fundamental
equation of Queueing Theory.
Performance Tuning methods using tools such as Statspack work by evaluating
the relative impact of the various components of overall Response Time and
direct the tuning effort to those components having the most impact in terms
of time consumed.
For a detailed discussion of this subject please refer to
Note 190124.1 THE COE PERFORMANCE METHOD
----------------------------------------------------
DETERMINING THE REAL SIGNIFICANCE OF I/O WAIT EVENTS
----------------------------------------------------
Many tools including Statspack produce listings of the most significant Wait
Events. Statspack reports in versions previous to Oracle9i Release 2 contain
this information in a section called "Top 5 Wait Events".
When presented with such a list of top Wait Events it sometimes becomes easy
to simply start dealing with the listed Wait Events and to forget evaluating
their impact on overall Response Time first.
In situations where 'Service Time' i.e. CPU usage is much more significant
than 'Wait Time', it is very likely that investigating Wait Events will not
produce significant savings in 'Response Time'.
Therefore, one should always compare the time taken by the top wait events
to the 'CPU used by this session' and direct the tuning effort to the biggest
consumers.
Note:
To address this possible source of confusion, starting with Oracle9i Release 2
the "Top 5 Wait Events" section has been renamed to "Top 5 Timed Events".
Here, 'Service Time' as measured by the statistic 'CPU used by this session'
is listed as 'CPU time'. This means that it is now easier to accurately measure
the impact of Wait Events in overall 'Response Time' and to correctly target
the subsequent tuning effort.
-----------------------------------------------------
MISINTERPRETING THE IMPACT OF WAIT EVENTS: AN EXAMPLE
-----------------------------------------------------
Here is a real life example of why it is important to look at both 'Wait Time'
and 'Service Time' when investigating database performance.
The following is the "Top 5 Wait Events" section of a Statspack report
generated from two snapshots 46 minutes apart:
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
direct path read 4,232 10,827 52.01
db file scattered read 6,105 6,264 30.09
direct path write 1,992 3,268 15.70
control file parallel write 893 198 .95
db file parallel write 40 131 .63
-------------------------------------------------------------
Based on this listing we may be tempted to immediately start looking at the
causes between the 'direct path read' and 'db file scattered read' waits and
to try to tune them. This approach would not take into account 'Service Time'.
Here is the statistic that measures 'Service Time' from the same report:
Statistic Total per Second per Trans
--------------------------------- ---------------- ------------ ------------
CPU used by this session 358,806 130.5 12,372.6
Let's do some simple math from these figures:
'Wait Time' = 10,827 x 100% / 52,01% = 20,817 cs
'Service Time' = 358,806 cs
'Response Time' = 358,806 + 20,817 = 379,623 cs
If we now calculate percentages for all the 'Response Time' components:
CPU time = 94.52%
direct path read = 2.85%
db file scattered read = 1.65%
direct path write = 0.86%
control file parallel write = 0.05%
db file parallel write = 0.03%
It is now obvious that the I/O-related Wait Events are not really a significant
component of the overall Response Time and that subsequent tuning should be
directed to the Service Time component i.e. CPU consumption.
Incidentally, the improved "Top 5 Timed Events" section in Statspack starting
with Oracle9i Release 2 would show output similar to our calculated listing.
-----------------------
I/O-RELATED WAIT EVENTS
-----------------------
In this section we list the I/O-related Wait Events that occur most often
in Oracle databases together with reference notes describing each wait.
In subsequent sections we explore some of the most important of these in detail.
Datafile I/O-Related Wait Events:
'db file sequential read' Note 34559.1
'db file scattered read' Note 34558.1
'db file parallel read'
'direct path read' Note 50415.1
'direct path write' Note 50416.1
'direct path read (lob)'
'direct path write (lob)'
Controlfile I/O-Related Wait Events:
'control file parallel write'
'control file sequential read'
'control file single write'
Redo Logging I/O-Related Wait Events:
'log file parallel write' Note 34583.1
'log file sync' Note 34592.1
'log file sequential read'
'log file single write'
'switch logfile command'
'log file switch completion'
'log file switch (clearing log file)'
'log file switch (checkpoint incomplete)'
'log switch/archive'
'log file switch (archiving needed)'
Buffer Cache I/O-Related Wait Events:
'db file parallel write' Note 34416.1
'db file single write'
'write complete waits'
'free buffer waits'
-------------------------------------------
GENERAL APPROACHES FOR HANDLING I/O PROBLEMS
-------------------------------------------
After an analysis of the database's Response Time using e.g. Statspack
has shown that performance is limited by I/O-related Wait Events, a number
of possible approaches can be followed.
Refer to the next section for the approaches to follow for each Wait Event.
Some of the approaches can be used regardless of the particular Wait Event.
In this section we present and explain the concepts and rationale behind
each approach.
o Reduce the I/O requirements of the database by tuning SQL:
A database with no user SQL being run generates little or no I/O.
Ultimately all I/O generated by a database is directly or indirectly
due to the nature and amount of user SQL being submitted for execution.
This means that it is possible to limit the I/O requirements of a database
by controlling the amount of I/O generated by individual SQL statements.
This is accomplished by tuning SQL statements so that their execution plans
result in a minimum number of I/O operations.
Typically in a problematic situation there will only be a few SQL statements
with suboptimal execution plans generating a lot more physical I/O than
necessary and degrading the overall performance for the database.
o Reduce the I/O requirements of the database by tuning instance parameters:
This works in two ways:
a) Using memory caching to limit I/O:
The amount of I/O required by the database is limited by the use of a number
of memory caches e.g. the Buffer Cache, the Log Buffer, various Sort Areas etc.
Increasing the Buffer Cache, up to a point, results in more buffer accesses
by database processes (logical I/Os) being satisfied from memory instead of
having to go to disk (physical I/Os).
With larger Sort Areas in memory, the likelihood of them being exhausted
during a sorting operation and having to use a temporary tablespace on disk
is reduced.
The other caches also work according to similar concepts.
b) Tuning the size of multiblock I/O:
The size of individual multiblock I/O operations can be controlled by instance
parameters.
Up to a limit, multiblock I/Os are executed faster when there are fewer larger
I/Os than when there are more smaller I/Os.
For example, transferring 100Mb of data will complete faster if it is done in
100 requests of size 1Mb each than if it is done in 1,000 requests of size
100Kb each or 10,000 requests of 10Kb each.
After this limit is reached, the difference is no longer important: transferring
1Gb of data in 100 requests of size 10Mb each (if allowed by limits on maximum
I/O transfer size of Operating Systems) would be almost as efficient as a
single transfer of size 1Gb.
This is because the time taken to service an I/O involves two main components:
I/O Setup Time and I/O Transfer Time.
I/O Setup Time tends to be fairly constant across different I/O sizes
and for small I/O sizes tends to dominate the total service time.
I/O Transfer Time tends to increase in proportion to the size of the I/O
and for small I/O sizes is usually less than the I/O Setup Time.
The consequence of the above is that it is usually better to configure instance
parameters so that the database issues larger and fewer multiblock I/Os.
o Optimizing I/O at the Operating System level
This involves making use of I/O capabilities such as Asynchronous I/O or
using Filesystems with advanced capabilities such as Direct I/O (bypassing the
Operating System's File Caches). Another possible action is to raise the limit
of maximum I/O size per transfer (referred to as max_io_size in this article).
o Balancing the database I/O by usage of Striping, RAID, SAN or NAS
This approach relies on storage technologies such as Striping, RAID, Storage
Area Networks (SAN) and Network Attached Storage (NAS) to automatically load
balance database I/O across multiple available physical disks in order to avoid disk
contention and I/O bottlenecks when there is still available unused disk
throughput in the storage hardware.
For more detailed discussions on these technologies please refer to
"Optimal Storage Configuration Made Easy" by J. Loaiza
Note 30286.1 I/O Tuning with Different RAID Configurations
o Redistribute database I/O by manual placement of database files across
different filesystems, controllers and physical devices
This is an approach used in the absence of advanced modern storage technologies.
Again the aim is to distribute the database I/O so that no single set of disks
or controller becomes saturated from I/O requests when there is still unused
disk throughput. It is harder to get right than the previous approach and most
often less successful.
Finally, it is important to remember that some I/O will always exist in most
databases. After all the guidelines above have been considered, if performance
is still not satisfactory on the existing system, you can consider:
o Reducing the data volumes of the current database by moving older data out.
o Investing in more & faster hardware.
--------------------------------
DATAFILE I/O-RELATED WAIT EVENTS
--------------------------------
These Wait Events occur on I/O operations to datafiles.
'db file sequential read' Note 34559.1
------------------------------------------------------------
This is one of the most common I/O-related waits.
It is in most cases a single block read e.g. for index data blocks or for
table data blocks accessed through an index but can also be seen for reads
on datafile header blocks.
In earlier versions it could be a multiblock read from Sort segments on disk
to contiguous ('sequential') buffers in the Buffer Cache.
If this Wait Event is a significant portion of Wait Time then a number of
approaches are possible:
o Find the Top SQL statements in Physical Reads (from a Statspack report in
the section titled "SQL ordered by Reads" or from the view V$SQL)
and tune them in order to reduce their I/O requirements:
- If Index Range scans are involved, more blocks than necessary could be
being visited if the index is unselective: by forcing or enabling the
use of a more selective index, we can access the same table data by
visiting fewer index blocks (and doing fewer physical I/Os).
- If indexes are fragmented, again we have to visit more blocks because
there is less index data per block. In this case, rebuilding the index
will compact its contents into fewer blocks.
- If the index being used has a large Clustering Factor, then more table
data blocks have to be visited in order to get the rows in each Index
block: by rebuilding the table with its rows sorted by the particular
index columns we can reduce the Clustering Factor and hence the number
of table data blocks that we have to visit for each index block.
For example, if the table has columns A, B, C & D and the index is on B, D
then we can rebuild the table as
CREATE TABLE new AS SELECT * FROM old ORDER BY b,d;
Note 39836.1 Clustering Factor
- Use Partitioning to reduce the number of index and table data blocks to be
visited for each SQL statement by usage of Partition Pruning.
o If there are no particular SQL statements with bad execution plans doing more
Physical I/Os than necessary, then one of the following may be happening:
- I/Os on particular datafiles may be being serviced slower due to excessive
activity on their disks. In this case, looking at the Statspack "File I/O
Statistics" section (or V$FILESTAT) will help us find such hot disks and
spread out the I/O by manually moving datafiles to other storage or by
making use of Striping, RAID and other technologies to automatically
perform I/O load balancing for us.
- Starting with Oracle 9.2, we can also find which segments (tables or
indexes) have the most Physical Reads being performed against them by
using the new Segment Statistics data from view V$SEGMENT_STATISTICS.
We can then look in detail at such segments and see if e.g. indexes
should be rebuilt or Partitioning could be used to reduce I/O on them.
Statspack also generates a "Segment Statistics" report starting at level 7.
o If there is no SQL with suboptimal execution plans and I/O is evenly spread
out with similar response times from all disks then a larger Buffer Cache
may help:
- In Oracle8i experiment with gradual increments of DB_BLOCK_BUFFERS followed
by measurements of the Buffer Cache Hit Ratio from Statspack until there is
no further improvement to it.
- In Oracle9i and above use the Buffer Cache Advisory facility (also available
in the Statspack report) to tune the size of the Buffer Cache.
For details please refer to the manual
Oracle9i Database Performance Guide and Reference,
Ch. 14 Memory Configuration and Use, Configuring and Using the Buffer Cache
- For hot segments, usage of Multiple Buffer Pools can be explored: place
such hot indexes and tables in the KEEP Buffer Pool. For details refer to
Note 76374.1 Multiple Buffer Pools
o Finally, you can consider reducing the data held in the most frequently
accessed segments (by moving older unneeded data out of the database) or
moving these segments to new faster disks to reduce the response time on
their I/Os.
'db file scattered read' Note 34558.1
------------------------------------------------------------
This is another very common Wait Event.
It occurs when Oracle performs multiblock reads from disk into non-contiguous
('scattered') buffers in the Buffer Cache. Such reads are issued for up to
DB_FILE_MULTIBLOCK_READ_COUNT blocks at a time.
These typically happen for Full Table Scans and for Fast Full Index scans.
If this Wait Event is a significant portion of Wait Time then a number of
approaches are possible:
o Find which SQL statements perform Full Table or Fast Full Index scans and
tune them to make sure these scans are necessary and not the result of a
suboptimal plan.
- Starting with Oracle9i the new view V$SQL_PLAN view can help:
(ignore data dictionary SQL in the output of these queries)
For Full Table scans:
select sql_text from v$sqltext t, v$sql_plan p
where t.hash_value=p.hash_value and p.operation='TABLE ACCESS'
and p.options='FULL'
order by p.hash_value, t.piece;
For Fast Full Index scans:
select sql_text from v$sqltext t, v$sql_plan p
where t.hash_value=p.hash_value and p.operation='INDEX'
and p.options='FULL SCAN'
order by p.hash_value, t.piece;
- In Oracle8i a possible approach is to find sessions performing multiblock
reads by querying V$SESSION_EVENT for this Wait Event and then SQL Tracing
them. Alternatively, the Top SQL statements for Physical Reads can be
investigated to see if their execution plans contain Full Table or Fast
Full Index scans.
o In cases where such multiblock scans occur from optimal execution plans
it is possible to tune the size of multiblock I/Os issued by Oracle by
setting the instance parameter DB_FILE_MULTIBLOCK_READ_COUNT so that
DB_BLOCK_SIZE x DB_FILE_MULTIBLOCK_READ_COUNT = max_io_size of system
For more information refer to
Note 30712.1 Init.ora Parameter "DB_FILE_MULTIBLOCK_READ_COUNT" Reference
Note 1037322.6 WHAT IS THE DB_FILE_MULTIBLOCK_READ_COUNT PARAMETER?
o As blocks read using Full Table and Fast Full Index scans are placed on the
least recently used end of the Buffer Cache replacement lists, sometimes
it may help to use Multiple Buffer Pools and place such segments in the KEEP
pool. For more information please refer to
Note 76374.1 Multiple Buffer Pools
o Partitioning can also be used to reduce the amount of data to be scanned
as Partition Pruning can restrict the scan to a subset of the segment's
partitions.
o Finally, you can consider reducing the data held in the most frequently
accessed segments (by moving older unneeded data out of the database) or
moving these segments to new faster disks to reduce the response time on
their I/Os.
'db file parallel read'
------------------------------------------------------------
This Wait Event is used when Oracle performs in parallel reads from multiple
datafiles to non-contiguous buffers in memory (PGA or Buffer Cache).
This is done during recovery operations or when buffer prefetching is being
used as an optimization i.e. instead of performing multiple single-block reads.
If this wait is an important component of Wait Time, follow the same guidelines
as 'db file sequential read'.
'direct path read' Note 50415.1
'direct path write' Note 50416.1
'direct path read (lob)'
'direct path write (lob)'
------------------------------------------------------------
These occur when database processes perform special types of multiblock I/Os
between the disk and process PGA memory, thus bypassing the Buffer Cache.
Such I/Os may be performed both synchronously and asynchronously.
Examples where they may be used are:
o Sort I/Os when memory Sort areas are exhausted and temporary tablespaces
are used to perform the sort
o Parallel Execution (Query and DML)
o Readahead operations (buffer prefetching)
o Direct Load operations
o I/O to LOB segments (which are not cached in the Buffer Cache)
Due to the way in which time for these waits is recorded (it does not measure
the time taken to perform the I/O), their relative position in listings such
as Statspack's "Top 5 Wait/Timed Events" cannot be used to evaluate their
true impact.
Guidelines for tuning:
o Usage of Asynchronous I/O is recommended where available.
o In Oracle8i, minimize the number of I/O requests by setting the
DB_FILE_DIRECT_IO_COUNT instance parameter so that
DB_BLOCK_SIZE x DB_FILE_DIRECT_IO_COUNT = max_io_size of system
In Oracle8i the default for this is 64 blocks.
(In Oracle9i, it is replaced by _DB_FILE_DIRECT_IO_COUNT which governs
the size of direct I/Os in BYTES (not blocks). The default is 1Mb but
will be sized down if the max_io_size of the system is smaller.)
Note 47324.1 Init.ora Parameter "DB_FILE_DIRECT_IO_COUNT" Reference Note
o Tune memory Sort areas so that disk I/O for Sorting is minimized:
In 9i and above use Automated SQL Execution Memory Management.
In 8i tune the various Sort areas manually.
Note 147806.1 Oracle9i New Feature: Automated SQL Execution Memory Management
Note 109907.1 How to Determine an Optimal SORT_AREA_SIZE
o For LOB segments, store them on filesystems where an Operating System File
Buffer Cache can provide some memory caching.
o Identify sessions performing direct I/Os by querying V$SESSION_EVENT
for these Wait Events or V$SESSTAT for statistics
'physical reads direct', 'physical reads direct (lob)',
'physical writes direct' & 'physical writes direct (lob)'
and tune their SQL statements.
o Identify datafiles on bottlenecked disk storage and move elsewhere
using V$FILESTAT or Statspack's "File IO Statistics" section.
-----------------------------------
CONTROLFILE I/O-RELATED WAIT EVENTS
-----------------------------------
These Wait Events occur during I/O to one or all copies of the controlfile.
Frequency of Controlfile access is governed by activities such as Redo Logfile
switching and Checkpointing. Therefore it can only be influenced indirectly
by tuning these activities.
'control file parallel write'
------------------------------------------------------------
This occurs when a server process is updating all copies of the controlfile.
If it is significant, check for bottlenecks on the I/O paths (controllers,
physical disks) of all of the copies of the controlfile.
Possible solutions:
o Reduce the number of controlfile copies to the minimum that ensures
that not all copies can be lost at the same time.
o Use Asynchronous I/O if available on your platform.
o Move the controlfile copies to less saturated storage locations.
'control file sequential read'
'control file single write'
------------------------------------------------------------
These occur on I/O to a single copy of the controlfile.
If they are significant find out whether the waits are on particular copy
of the controlfile and if so whether its I/O path is saturated.
The following query can be used to find which controlfile is being accessed.
It has to be run when the problem is occuring:
select P1 from V$SESSION_WAIT
where EVENT like 'control file%' and STATUS='WAITING';
Possible solutions:
o Move the problematic controlfile copy to a less saturated storage location.
o Use Asynchronous I/O if available on your platform.
------------------------------------
REDO LOGGING I/O-RELATED WAIT EVENTS
------------------------------------
There are a number of Wait Events that happen during Redo Logging activities
and most of them are I/O-related.
The two most important ones are 'log file parallel write' and 'log file sync'.
Oracle foreground processes wait for 'log file sync' whereas the LGWR process
waits for 'log file parallel write'.
Although we usually find 'log file sync' in the "Top 5 Wait/Timed Events"
section of the Statspack report, in order to understand it we will first look
at 'log file parallel write':
'log file parallel write' Note 34583.1
------------------------------------------------------------
The LGWR background process waits for this event while it is copying redo
records from the memory Log Buffer cache to the current redo group's member
logfiles on disk.
Asynchronous I/O will be used if available to make the write parallel, otherwise
these writes will be done sequentially one member after the other.
However, LGWR has to wait until the I/Os to all member logfiles are complete
before the wait is completed.
Hence, the factor that determines the length of this wait is the speed with
which the I/O subsystem can perform the writes to the logfile members.
To reduce the time waited for this event, one approach is to reduce the amount
of redo generated by the database:
o Make use of UNRECOVERABLE/NOLOGGING options.
o Reduce the number of redo group members to the minimum necessary to ensure
not all members can be lost at the same time.
o Do not leave tablespaces in BACKUP mode for longer than necessary.
o Only use the minimal level of Supplemental Logging required to achieve
the required functionality e.g. in LogMiner, Logical Standby or Streams.
Another approach is to tune the I/O itself:
o Place redo group members on storage locations so that parallel
writes do not contend with each other.
o Do not use RAID-5 for redo logfiles.
o Use Raw Devices for redo logfiles.
o Use faster disks for redo logfiles.
o If archiving is being used setup redo storage so that writes for the current
redo group members do not contend with reads for the group(s) currently being
archived.
'log file sync' Note 34592.1
------------------------------------------------------------
This Wait Event occurs in Oracle foreground processes when they have issued
a COMMIT or ROLLBACK operation and are waiting for it to complete.
Part (but not all) of this wait includes waiting for LGWR to copy the redo
records for the session's transaction from Log Buffer memory to disk.
So, in the time that a foreground process is waiting for 'log file sync',
LGWR will also wait for a portion of this time on 'log file parallel write'.
The key to understanding what is delaying 'log file sync' is to compare
average times waited for 'log file sync' and 'log file parallel write':
o If they are almost similar, then redo logfile I/O is causing the delay
and the guidelines for tuning it should be followed.
o If 'log file parallel write' is significantly different i.e smaller,
then the delay is caused by the other parts of the Redo Logging mechanism
that occur during a COMMIT/ROLLBACK (and are not I/O-related).
Sometimes there will be latch contention on redo latches, evidenced by
'latch free' or 'LGWR wait for redo copy' wait events.
'log file sequential read'
'log file single write'
------------------------------------------------------------
Both these Wait Events are I/O-related so they are likely to appear together
with 'log file parallel write' if there is I/O contention on the redo logs.
Follow the same guidelines for tuning them.
'switch logfile command'
'log file switch completion'
'log file switch (clearing log file)'
------------------------------------------------------------
More LGWR I/O-related Wait Events, tune as before.
'log file switch (checkpoint incomplete)'
------------------------------------------------------------
This Wait Event occurs when Checkpointing activities are not occuring
quickly enough.
For guidelines on tuning Checkpoint operations please refer to:
Note 147468.1 Checkpoint Tuning and Troubleshooting Guide
Note 76713.1 8i Parameters that Influence Checkpoints
'log switch/archive'
'log file switch (archiving needed)'
------------------------------------------------------------
These Wait Events occur when archiving is enabled and indicate that archiving
is not performing fast enough.
For guidelines on tuning archiving operations please refer to:
Note 45042.1 Archiver Best Practices
------------------------------------
BUFFER CACHE I/O-RELATED WAIT EVENTS
------------------------------------
These Wait Events occur because of Buffer Cache operations involving the
DBWR process(es) and I/O Slaves.
'db file parallel write' Note 34416.1
'db file single write'
'write complete waits'
'free buffer waits'
------------------------------------------------------------
For guidelines on tuning these waits please refer to the following articles:
Note 62172.1 Understanding and Tuning Buffer Cache and DBWR
Note 147468.1 Checkpoint Tuning and Troubleshooting Guide
Note 76713.1 8i Parameters that Influence Checkpoints
---------------------------------
FINAL NOTE: CORRECT I/O OPERATION
---------------------------------
As a final note in this article, whenever I/O performance and response times
are low it is worth checking for related errors in Operating System logs.
There is little point in investigating I/O performance at the Oracle database
level if the I/O subsystem is malfunctioning. If this is the case your Hardware,
Operating System or Filesystem vendor should be contacted for assistance.
Please ensure that all steps described in Oracle Installation manuals and
Administrator's Reference guides involving Operating System patches, Kernel
parameters & related configuration tasks have been performed on systems
hosting Oracle databases.
----------------------------
REFERENCES & FURTHER READING
----------------------------
Note 190124.1 THE COE PERFORMANCE METHOD
Note 30286.1 I/O Tuning with Different RAID Configurations
Note 30712.1 Init.ora Parameter "DB_FILE_MULTIBLOCK_READ_COUNT" Reference Note
Note 1037322.6 WHAT IS THE DB_FILE_MULTIBLOCK_READ_COUNT PARAMETER?
Note 39836.1 Clustering Factor
Note 47324.1 Init.ora Parameter "DB_FILE_DIRECT_IO_COUNT" Reference Note
Note 45042.1 Archiver Best Practices
Note 62172.1 Understanding and Tuning Buffer Cache and DBWR
Note 147468.1 Checkpoint Tuning and Troubleshooting Guide
Note 76713.1 8i Parameters that Influence Checkpoints
Note 76374.1 Multiple Buffer Pools
Note 147806.1 Oracle9i New Feature: Automated SQL Execution Memory Management
Note 109907.1 How to Determine an Optimal SORT_AREA_SIZE
"Optimal Storage Configuration Made Easy" by J. Loaiza
http://otn.oracle.com/deploy/performance/pdf/opt_storage_conf.pdf
"Diagnosing Performance Using Statspack" by C. Dialeris & G. Wood
http://otn.oracle.com/deploy/performance/pdf/statspack.pdf
"Performance Tuning with Statspack, Part I" by C. Dialeris & G. Wood
http://otn.oracle.com/deploy/performance/pdf/20TUNING_dialeris.pdf
"Performance Tuning with Statspack, Part II" by C. Dialeris & G. Wood
http://otn.oracle.com/deploy/performance/pdf/statspack_tuning_otn_new.pdf
Oracle8i Designing and Tuning for Performance Release 2 (8.1.6)
Part Number A76992-01
Oracle9i Database Performance Methods Release 1 (9.0.1)
Part Number A87504-02
Oracle9i Database Performance Guide and Reference Release 1 (9.0.1)
Part Number A87503-02
Oracle9i Database Performance Planning Release 2 (9.2)
Part Number A96532-01
Oracle9i Database Performance Tuning Guide and Reference Release 2 (9.2)
Part Number A96533-01
Doc ID: Note:223117.1 Type: TROUBLESHOOTING
Last Revision Date: 18-MAY-2004 Status: PUBLISHED
-------
PURPOSE
-------
This article provides guidelines for tuning an Oracle database
when the main source of contention is I/O-related.
-------------------
SCOPE & APPLICATION
-------------------
The techniques described here can be followed when:
o Statspack reports show I/O wait events in the "Top 5 Wait Events" section.
o SQL Tracing with wait events of a database session shows it is limited
mainly by I/O wait events.
o Operating System tools show very high utilization or saturation of disks
used for storage of database files.
The article should be of use to Database Administrators, Support Engineers,
Consultants and Database Performance Analysts.
-------------------------
TUNING WITH RESPONSE TIME
-------------------------
A critical activity in Database Performance Tuning is
Response Time Analysis: this consists of finding out where time is being
spent in a database.
TIME is the most important property in Performance Tuning.
Users perceive the performance of a system through the response time
they experience for their transactions or batch jobs.
Response Time Analysis for an Oracle Database is done
using the following equation:
Response Time = Service Time + Wait Time
'Service Time' is measured using the statistic 'CPU used by this session'
'Wait Time' is measured by summing up time spent on Wait Events
Note: although similar in appearance, this equation is not the fundamental
equation of Queueing Theory.
Performance Tuning methods using tools such as Statspack work by evaluating
the relative impact of the various components of overall Response Time and
direct the tuning effort to those components having the most impact in terms
of time consumed.
For a detailed discussion of this subject please refer to
Note 190124.1 THE COE PERFORMANCE METHOD
----------------------------------------------------
DETERMINING THE REAL SIGNIFICANCE OF I/O WAIT EVENTS
----------------------------------------------------
Many tools including Statspack produce listings of the most significant Wait
Events. Statspack reports in versions previous to Oracle9i Release 2 contain
this information in a section called "Top 5 Wait Events".
When presented with such a list of top Wait Events it sometimes becomes easy
to simply start dealing with the listed Wait Events and to forget evaluating
their impact on overall Response Time first.
In situations where 'Service Time' i.e. CPU usage is much more significant
than 'Wait Time', it is very likely that investigating Wait Events will not
produce significant savings in 'Response Time'.
Therefore, one should always compare the time taken by the top wait events
to the 'CPU used by this session' and direct the tuning effort to the biggest
consumers.
Note:
To address this possible source of confusion, starting with Oracle9i Release 2
the "Top 5 Wait Events" section has been renamed to "Top 5 Timed Events".
Here, 'Service Time' as measured by the statistic 'CPU used by this session'
is listed as 'CPU time'. This means that it is now easier to accurately measure
the impact of Wait Events in overall 'Response Time' and to correctly target
the subsequent tuning effort.
-----------------------------------------------------
MISINTERPRETING THE IMPACT OF WAIT EVENTS: AN EXAMPLE
-----------------------------------------------------
Here is a real life example of why it is important to look at both 'Wait Time'
and 'Service Time' when investigating database performance.
The following is the "Top 5 Wait Events" section of a Statspack report
generated from two snapshots 46 minutes apart:
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
direct path read 4,232 10,827 52.01
db file scattered read 6,105 6,264 30.09
direct path write 1,992 3,268 15.70
control file parallel write 893 198 .95
db file parallel write 40 131 .63
-------------------------------------------------------------
Based on this listing we may be tempted to immediately start looking at the
causes between the 'direct path read' and 'db file scattered read' waits and
to try to tune them. This approach would not take into account 'Service Time'.
Here is the statistic that measures 'Service Time' from the same report:
Statistic Total per Second per Trans
--------------------------------- ---------------- ------------ ------------
CPU used by this session 358,806 130.5 12,372.6
Let's do some simple math from these figures:
'Wait Time' = 10,827 x 100% / 52,01% = 20,817 cs
'Service Time' = 358,806 cs
'Response Time' = 358,806 + 20,817 = 379,623 cs
If we now calculate percentages for all the 'Response Time' components:
CPU time = 94.52%
direct path read = 2.85%
db file scattered read = 1.65%
direct path write = 0.86%
control file parallel write = 0.05%
db file parallel write = 0.03%
It is now obvious that the I/O-related Wait Events are not really a significant
component of the overall Response Time and that subsequent tuning should be
directed to the Service Time component i.e. CPU consumption.
Incidentally, the improved "Top 5 Timed Events" section in Statspack starting
with Oracle9i Release 2 would show output similar to our calculated listing.
-----------------------
I/O-RELATED WAIT EVENTS
-----------------------
In this section we list the I/O-related Wait Events that occur most often
in Oracle databases together with reference notes describing each wait.
In subsequent sections we explore some of the most important of these in detail.
Datafile I/O-Related Wait Events:
'db file sequential read' Note 34559.1
'db file scattered read' Note 34558.1
'db file parallel read'
'direct path read' Note 50415.1
'direct path write' Note 50416.1
'direct path read (lob)'
'direct path write (lob)'
Controlfile I/O-Related Wait Events:
'control file parallel write'
'control file sequential read'
'control file single write'
Redo Logging I/O-Related Wait Events:
'log file parallel write' Note 34583.1
'log file sync' Note 34592.1
'log file sequential read'
'log file single write'
'switch logfile command'
'log file switch completion'
'log file switch (clearing log file)'
'log file switch (checkpoint incomplete)'
'log switch/archive'
'log file switch (archiving needed)'
Buffer Cache I/O-Related Wait Events:
'db file parallel write' Note 34416.1
'db file single write'
'write complete waits'
'free buffer waits'
-------------------------------------------
GENERAL APPROACHES FOR HANDLING I/O PROBLEMS
-------------------------------------------
After an analysis of the database's Response Time using e.g. Statspack
has shown that performance is limited by I/O-related Wait Events, a number
of possible approaches can be followed.
Refer to the next section for the approaches to follow for each Wait Event.
Some of the approaches can be used regardless of the particular Wait Event.
In this section we present and explain the concepts and rationale behind
each approach.
o Reduce the I/O requirements of the database by tuning SQL:
A database with no user SQL being run generates little or no I/O.
Ultimately all I/O generated by a database is directly or indirectly
due to the nature and amount of user SQL being submitted for execution.
This means that it is possible to limit the I/O requirements of a database
by controlling the amount of I/O generated by individual SQL statements.
This is accomplished by tuning SQL statements so that their execution plans
result in a minimum number of I/O operations.
Typically in a problematic situation there will only be a few SQL statements
with suboptimal execution plans generating a lot more physical I/O than
necessary and degrading the overall performance for the database.
o Reduce the I/O requirements of the database by tuning instance parameters:
This works in two ways:
a) Using memory caching to limit I/O:
The amount of I/O required by the database is limited by the use of a number
of memory caches e.g. the Buffer Cache, the Log Buffer, various Sort Areas etc.
Increasing the Buffer Cache, up to a point, results in more buffer accesses
by database processes (logical I/Os) being satisfied from memory instead of
having to go to disk (physical I/Os).
With larger Sort Areas in memory, the likelihood of them being exhausted
during a sorting operation and having to use a temporary tablespace on disk
is reduced.
The other caches also work according to similar concepts.
b) Tuning the size of multiblock I/O:
The size of individual multiblock I/O operations can be controlled by instance
parameters.
Up to a limit, multiblock I/Os are executed faster when there are fewer larger
I/Os than when there are more smaller I/Os.
For example, transferring 100Mb of data will complete faster if it is done in
100 requests of size 1Mb each than if it is done in 1,000 requests of size
100Kb each or 10,000 requests of 10Kb each.
After this limit is reached, the difference is no longer important: transferring
1Gb of data in 100 requests of size 10Mb each (if allowed by limits on maximum
I/O transfer size of Operating Systems) would be almost as efficient as a
single transfer of size 1Gb.
This is because the time taken to service an I/O involves two main components:
I/O Setup Time and I/O Transfer Time.
I/O Setup Time tends to be fairly constant across different I/O sizes
and for small I/O sizes tends to dominate the total service time.
I/O Transfer Time tends to increase in proportion to the size of the I/O
and for small I/O sizes is usually less than the I/O Setup Time.
The consequence of the above is that it is usually better to configure instance
parameters so that the database issues larger and fewer multiblock I/Os.
o Optimizing I/O at the Operating System level
This involves making use of I/O capabilities such as Asynchronous I/O or
using Filesystems with advanced capabilities such as Direct I/O (bypassing the
Operating System's File Caches). Another possible action is to raise the limit
of maximum I/O size per transfer (referred to as max_io_size in this article).
o Balancing the database I/O by usage of Striping, RAID, SAN or NAS
This approach relies on storage technologies such as Striping, RAID, Storage
Area Networks (SAN) and Network Attached Storage (NAS) to automatically load
balance database I/O across multiple available physical disks in order to avoid disk
contention and I/O bottlenecks when there is still available unused disk
throughput in the storage hardware.
For more detailed discussions on these technologies please refer to
"Optimal Storage Configuration Made Easy" by J. Loaiza
Note 30286.1 I/O Tuning with Different RAID Configurations
o Redistribute database I/O by manual placement of database files across
different filesystems, controllers and physical devices
This is an approach used in the absence of advanced modern storage technologies.
Again the aim is to distribute the database I/O so that no single set of disks
or controller becomes saturated from I/O requests when there is still unused
disk throughput. It is harder to get right than the previous approach and most
often less successful.
Finally, it is important to remember that some I/O will always exist in most
databases. After all the guidelines above have been considered, if performance
is still not satisfactory on the existing system, you can consider:
o Reducing the data volumes of the current database by moving older data out.
o Investing in more & faster hardware.
--------------------------------
DATAFILE I/O-RELATED WAIT EVENTS
--------------------------------
These Wait Events occur on I/O operations to datafiles.
'db file sequential read' Note 34559.1
------------------------------------------------------------
This is one of the most common I/O-related waits.
It is in most cases a single block read e.g. for index data blocks or for
table data blocks accessed through an index but can also be seen for reads
on datafile header blocks.
In earlier versions it could be a multiblock read from Sort segments on disk
to contiguous ('sequential') buffers in the Buffer Cache.
If this Wait Event is a significant portion of Wait Time then a number of
approaches are possible:
o Find the Top SQL statements in Physical Reads (from a Statspack report in
the section titled "SQL ordered by Reads" or from the view V$SQL)
and tune them in order to reduce their I/O requirements:
- If Index Range scans are involved, more blocks than necessary could be
being visited if the index is unselective: by forcing or enabling the
use of a more selective index, we can access the same table data by
visiting fewer index blocks (and doing fewer physical I/Os).
- If indexes are fragmented, again we have to visit more blocks because
there is less index data per block. In this case, rebuilding the index
will compact its contents into fewer blocks.
- If the index being used has a large Clustering Factor, then more table
data blocks have to be visited in order to get the rows in each Index
block: by rebuilding the table with its rows sorted by the particular
index columns we can reduce the Clustering Factor and hence the number
of table data blocks that we have to visit for each index block.
For example, if the table has columns A, B, C & D and the index is on B, D
then we can rebuild the table as
CREATE TABLE new AS SELECT * FROM old ORDER BY b,d;
Note 39836.1 Clustering Factor
- Use Partitioning to reduce the number of index and table data blocks to be
visited for each SQL statement by usage of Partition Pruning.
o If there are no particular SQL statements with bad execution plans doing more
Physical I/Os than necessary, then one of the following may be happening:
- I/Os on particular datafiles may be being serviced slower due to excessive
activity on their disks. In this case, looking at the Statspack "File I/O
Statistics" section (or V$FILESTAT) will help us find such hot disks and
spread out the I/O by manually moving datafiles to other storage or by
making use of Striping, RAID and other technologies to automatically
perform I/O load balancing for us.
- Starting with Oracle 9.2, we can also find which segments (tables or
indexes) have the most Physical Reads being performed against them by
using the new Segment Statistics data from view V$SEGMENT_STATISTICS.
We can then look in detail at such segments and see if e.g. indexes
should be rebuilt or Partitioning could be used to reduce I/O on them.
Statspack also generates a "Segment Statistics" report starting at level 7.
o If there is no SQL with suboptimal execution plans and I/O is evenly spread
out with similar response times from all disks then a larger Buffer Cache
may help:
- In Oracle8i experiment with gradual increments of DB_BLOCK_BUFFERS followed
by measurements of the Buffer Cache Hit Ratio from Statspack until there is
no further improvement to it.
- In Oracle9i and above use the Buffer Cache Advisory facility (also available
in the Statspack report) to tune the size of the Buffer Cache.
For details please refer to the manual
Oracle9i Database Performance Guide and Reference,
Ch. 14 Memory Configuration and Use, Configuring and Using the Buffer Cache
- For hot segments, usage of Multiple Buffer Pools can be explored: place
such hot indexes and tables in the KEEP Buffer Pool. For details refer to
Note 76374.1 Multiple Buffer Pools
o Finally, you can consider reducing the data held in the most frequently
accessed segments (by moving older unneeded data out of the database) or
moving these segments to new faster disks to reduce the response time on
their I/Os.
'db file scattered read' Note 34558.1
------------------------------------------------------------
This is another very common Wait Event.
It occurs when Oracle performs multiblock reads from disk into non-contiguous
('scattered') buffers in the Buffer Cache. Such reads are issued for up to
DB_FILE_MULTIBLOCK_READ_COUNT blocks at a time.
These typically happen for Full Table Scans and for Fast Full Index scans.
If this Wait Event is a significant portion of Wait Time then a number of
approaches are possible:
o Find which SQL statements perform Full Table or Fast Full Index scans and
tune them to make sure these scans are necessary and not the result of a
suboptimal plan.
- Starting with Oracle9i the new view V$SQL_PLAN view can help:
(ignore data dictionary SQL in the output of these queries)
For Full Table scans:
select sql_text from v$sqltext t, v$sql_plan p
where t.hash_value=p.hash_value and p.operation='TABLE ACCESS'
and p.options='FULL'
order by p.hash_value, t.piece;
For Fast Full Index scans:
select sql_text from v$sqltext t, v$sql_plan p
where t.hash_value=p.hash_value and p.operation='INDEX'
and p.options='FULL SCAN'
order by p.hash_value, t.piece;
- In Oracle8i a possible approach is to find sessions performing multiblock
reads by querying V$SESSION_EVENT for this Wait Event and then SQL Tracing
them. Alternatively, the Top SQL statements for Physical Reads can be
investigated to see if their execution plans contain Full Table or Fast
Full Index scans.
o In cases where such multiblock scans occur from optimal execution plans
it is possible to tune the size of multiblock I/Os issued by Oracle by
setting the instance parameter DB_FILE_MULTIBLOCK_READ_COUNT so that
DB_BLOCK_SIZE x DB_FILE_MULTIBLOCK_READ_COUNT = max_io_size of system
For more information refer to
Note 30712.1 Init.ora Parameter "DB_FILE_MULTIBLOCK_READ_COUNT" Reference
Note 1037322.6 WHAT IS THE DB_FILE_MULTIBLOCK_READ_COUNT PARAMETER?
o As blocks read using Full Table and Fast Full Index scans are placed on the
least recently used end of the Buffer Cache replacement lists, sometimes
it may help to use Multiple Buffer Pools and place such segments in the KEEP
pool. For more information please refer to
Note 76374.1 Multiple Buffer Pools
o Partitioning can also be used to reduce the amount of data to be scanned
as Partition Pruning can restrict the scan to a subset of the segment's
partitions.
o Finally, you can consider reducing the data held in the most frequently
accessed segments (by moving older unneeded data out of the database) or
moving these segments to new faster disks to reduce the response time on
their I/Os.
'db file parallel read'
------------------------------------------------------------
This Wait Event is used when Oracle performs in parallel reads from multiple
datafiles to non-contiguous buffers in memory (PGA or Buffer Cache).
This is done during recovery operations or when buffer prefetching is being
used as an optimization i.e. instead of performing multiple single-block reads.
If this wait is an important component of Wait Time, follow the same guidelines
as 'db file sequential read'.
'direct path read' Note 50415.1
'direct path write' Note 50416.1
'direct path read (lob)'
'direct path write (lob)'
------------------------------------------------------------
These occur when database processes perform special types of multiblock I/Os
between the disk and process PGA memory, thus bypassing the Buffer Cache.
Such I/Os may be performed both synchronously and asynchronously.
Examples where they may be used are:
o Sort I/Os when memory Sort areas are exhausted and temporary tablespaces
are used to perform the sort
o Parallel Execution (Query and DML)
o Readahead operations (buffer prefetching)
o Direct Load operations
o I/O to LOB segments (which are not cached in the Buffer Cache)
Due to the way in which time for these waits is recorded (it does not measure
the time taken to perform the I/O), their relative position in listings such
as Statspack's "Top 5 Wait/Timed Events" cannot be used to evaluate their
true impact.
Guidelines for tuning:
o Usage of Asynchronous I/O is recommended where available.
o In Oracle8i, minimize the number of I/O requests by setting the
DB_FILE_DIRECT_IO_COUNT instance parameter so that
DB_BLOCK_SIZE x DB_FILE_DIRECT_IO_COUNT = max_io_size of system
In Oracle8i the default for this is 64 blocks.
(In Oracle9i, it is replaced by _DB_FILE_DIRECT_IO_COUNT which governs
the size of direct I/Os in BYTES (not blocks). The default is 1Mb but
will be sized down if the max_io_size of the system is smaller.)
Note 47324.1 Init.ora Parameter "DB_FILE_DIRECT_IO_COUNT" Reference Note
o Tune memory Sort areas so that disk I/O for Sorting is minimized:
In 9i and above use Automated SQL Execution Memory Management.
In 8i tune the various Sort areas manually.
Note 147806.1 Oracle9i New Feature: Automated SQL Execution Memory Management
Note 109907.1 How to Determine an Optimal SORT_AREA_SIZE
o For LOB segments, store them on filesystems where an Operating System File
Buffer Cache can provide some memory caching.
o Identify sessions performing direct I/Os by querying V$SESSION_EVENT
for these Wait Events or V$SESSTAT for statistics
'physical reads direct', 'physical reads direct (lob)',
'physical writes direct' & 'physical writes direct (lob)'
and tune their SQL statements.
o Identify datafiles on bottlenecked disk storage and move elsewhere
using V$FILESTAT or Statspack's "File IO Statistics" section.
-----------------------------------
CONTROLFILE I/O-RELATED WAIT EVENTS
-----------------------------------
These Wait Events occur during I/O to one or all copies of the controlfile.
Frequency of Controlfile access is governed by activities such as Redo Logfile
switching and Checkpointing. Therefore it can only be influenced indirectly
by tuning these activities.
'control file parallel write'
------------------------------------------------------------
This occurs when a server process is updating all copies of the controlfile.
If it is significant, check for bottlenecks on the I/O paths (controllers,
physical disks) of all of the copies of the controlfile.
Possible solutions:
o Reduce the number of controlfile copies to the minimum that ensures
that not all copies can be lost at the same time.
o Use Asynchronous I/O if available on your platform.
o Move the controlfile copies to less saturated storage locations.
'control file sequential read'
'control file single write'
------------------------------------------------------------
These occur on I/O to a single copy of the controlfile.
If they are significant find out whether the waits are on particular copy
of the controlfile and if so whether its I/O path is saturated.
The following query can be used to find which controlfile is being accessed.
It has to be run when the problem is occuring:
select P1 from V$SESSION_WAIT
where EVENT like 'control file%' and STATUS='WAITING';
Possible solutions:
o Move the problematic controlfile copy to a less saturated storage location.
o Use Asynchronous I/O if available on your platform.
------------------------------------
REDO LOGGING I/O-RELATED WAIT EVENTS
------------------------------------
There are a number of Wait Events that happen during Redo Logging activities
and most of them are I/O-related.
The two most important ones are 'log file parallel write' and 'log file sync'.
Oracle foreground processes wait for 'log file sync' whereas the LGWR process
waits for 'log file parallel write'.
Although we usually find 'log file sync' in the "Top 5 Wait/Timed Events"
section of the Statspack report, in order to understand it we will first look
at 'log file parallel write':
'log file parallel write' Note 34583.1
------------------------------------------------------------
The LGWR background process waits for this event while it is copying redo
records from the memory Log Buffer cache to the current redo group's member
logfiles on disk.
Asynchronous I/O will be used if available to make the write parallel, otherwise
these writes will be done sequentially one member after the other.
However, LGWR has to wait until the I/Os to all member logfiles are complete
before the wait is completed.
Hence, the factor that determines the length of this wait is the speed with
which the I/O subsystem can perform the writes to the logfile members.
To reduce the time waited for this event, one approach is to reduce the amount
of redo generated by the database:
o Make use of UNRECOVERABLE/NOLOGGING options.
o Reduce the number of redo group members to the minimum necessary to ensure
not all members can be lost at the same time.
o Do not leave tablespaces in BACKUP mode for longer than necessary.
o Only use the minimal level of Supplemental Logging required to achieve
the required functionality e.g. in LogMiner, Logical Standby or Streams.
Another approach is to tune the I/O itself:
o Place redo group members on storage locations so that parallel
writes do not contend with each other.
o Do not use RAID-5 for redo logfiles.
o Use Raw Devices for redo logfiles.
o Use faster disks for redo logfiles.
o If archiving is being used setup redo storage so that writes for the current
redo group members do not contend with reads for the group(s) currently being
archived.
'log file sync' Note 34592.1
------------------------------------------------------------
This Wait Event occurs in Oracle foreground processes when they have issued
a COMMIT or ROLLBACK operation and are waiting for it to complete.
Part (but not all) of this wait includes waiting for LGWR to copy the redo
records for the session's transaction from Log Buffer memory to disk.
So, in the time that a foreground process is waiting for 'log file sync',
LGWR will also wait for a portion of this time on 'log file parallel write'.
The key to understanding what is delaying 'log file sync' is to compare
average times waited for 'log file sync' and 'log file parallel write':
o If they are almost similar, then redo logfile I/O is causing the delay
and the guidelines for tuning it should be followed.
o If 'log file parallel write' is significantly different i.e smaller,
then the delay is caused by the other parts of the Redo Logging mechanism
that occur during a COMMIT/ROLLBACK (and are not I/O-related).
Sometimes there will be latch contention on redo latches, evidenced by
'latch free' or 'LGWR wait for redo copy' wait events.
'log file sequential read'
'log file single write'
------------------------------------------------------------
Both these Wait Events are I/O-related so they are likely to appear together
with 'log file parallel write' if there is I/O contention on the redo logs.
Follow the same guidelines for tuning them.
'switch logfile command'
'log file switch completion'
'log file switch (clearing log file)'
------------------------------------------------------------
More LGWR I/O-related Wait Events, tune as before.
'log file switch (checkpoint incomplete)'
------------------------------------------------------------
This Wait Event occurs when Checkpointing activities are not occuring
quickly enough.
For guidelines on tuning Checkpoint operations please refer to:
Note 147468.1 Checkpoint Tuning and Troubleshooting Guide
Note 76713.1 8i Parameters that Influence Checkpoints
'log switch/archive'
'log file switch (archiving needed)'
------------------------------------------------------------
These Wait Events occur when archiving is enabled and indicate that archiving
is not performing fast enough.
For guidelines on tuning archiving operations please refer to:
Note 45042.1 Archiver Best Practices
------------------------------------
BUFFER CACHE I/O-RELATED WAIT EVENTS
------------------------------------
These Wait Events occur because of Buffer Cache operations involving the
DBWR process(es) and I/O Slaves.
'db file parallel write' Note 34416.1
'db file single write'
'write complete waits'
'free buffer waits'
------------------------------------------------------------
For guidelines on tuning these waits please refer to the following articles:
Note 62172.1 Understanding and Tuning Buffer Cache and DBWR
Note 147468.1 Checkpoint Tuning and Troubleshooting Guide
Note 76713.1 8i Parameters that Influence Checkpoints
---------------------------------
FINAL NOTE: CORRECT I/O OPERATION
---------------------------------
As a final note in this article, whenever I/O performance and response times
are low it is worth checking for related errors in Operating System logs.
There is little point in investigating I/O performance at the Oracle database
level if the I/O subsystem is malfunctioning. If this is the case your Hardware,
Operating System or Filesystem vendor should be contacted for assistance.
Please ensure that all steps described in Oracle Installation manuals and
Administrator's Reference guides involving Operating System patches, Kernel
parameters & related configuration tasks have been performed on systems
hosting Oracle databases.
----------------------------
REFERENCES & FURTHER READING
----------------------------
Note 190124.1 THE COE PERFORMANCE METHOD
Note 30286.1 I/O Tuning with Different RAID Configurations
Note 30712.1 Init.ora Parameter "DB_FILE_MULTIBLOCK_READ_COUNT" Reference Note
Note 1037322.6 WHAT IS THE DB_FILE_MULTIBLOCK_READ_COUNT PARAMETER?
Note 39836.1 Clustering Factor
Note 47324.1 Init.ora Parameter "DB_FILE_DIRECT_IO_COUNT" Reference Note
Note 45042.1 Archiver Best Practices
Note 62172.1 Understanding and Tuning Buffer Cache and DBWR
Note 147468.1 Checkpoint Tuning and Troubleshooting Guide
Note 76713.1 8i Parameters that Influence Checkpoints
Note 76374.1 Multiple Buffer Pools
Note 147806.1 Oracle9i New Feature: Automated SQL Execution Memory Management
Note 109907.1 How to Determine an Optimal SORT_AREA_SIZE
"Optimal Storage Configuration Made Easy" by J. Loaiza
http://otn.oracle.com/deploy/performance/pdf/opt_storage_conf.pdf
"Diagnosing Performance Using Statspack" by C. Dialeris & G. Wood
http://otn.oracle.com/deploy/performance/pdf/statspack.pdf
"Performance Tuning with Statspack, Part I" by C. Dialeris & G. Wood
http://otn.oracle.com/deploy/performance/pdf/20TUNING_dialeris.pdf
"Performance Tuning with Statspack, Part II" by C. Dialeris & G. Wood
http://otn.oracle.com/deploy/performance/pdf/statspack_tuning_otn_new.pdf
Oracle8i Designing and Tuning for Performance Release 2 (8.1.6)
Part Number A76992-01
Oracle9i Database Performance Methods Release 1 (9.0.1)
Part Number A87504-02
Oracle9i Database Performance Guide and Reference Release 1 (9.0.1)
Part Number A87503-02
Oracle9i Database Performance Planning Release 2 (9.2)
Part Number A96532-01
Oracle9i Database Performance Tuning Guide and Reference Release 2 (9.2)
Part Number A96533-01
Subscribe to:
Posts (Atom)
