OracleI have posted about the Oracle OFA (Optimal Flexible Architecture) and recent changes to OFA for Oracle Database 10g, but I wanted to go into a more practical application of these rules to act as a quick reference.

This is not intended to be a complete explanation of the OFA standard. For more complete information refer to the OFA whitepaper. The OFA Standard Recommendations below are taken directly from the OFA whitepaper.

OFA recommendations 9-11 pertain to very specific installation types I have chosen to exclude them. The examples below are based on a UNIX environment.

OFA Standard Recommendation 1: Name all mount points that will hold site specific data to match the pattern /pm where p is a string constant chosen not to misrepresent the contents of any mount point, and m is a unique fixed-length key that distinguishes one mount point from another.

Typical Application: Name operating system mount points with the convention /u01, /u02, /u03 etc.

Options: Anything can be used instead of the leading ‘u’, however be careful not to use something which could eventually misrepresent the future contents of the directory.

OFA Standard Recommendation 2:Name home directories matching the pattern /pm/h/u, where pm is a mount point name, h is selected from a small set of standard directory names, and u is the name of the owner of the directory.

Typical Application: The original OFA whitepaper states the oracle user home directory be placed in a directory like /u01/app/oracle.

Options: This rule is typically overlooked. The oracle user is usually given a home directory which matches other UNIX users, such as /export/home/oracle/ or /home/oracle, however the directory /u01/app/oracle (or similar) should be created for software installation (see recommendation 4). The recommended home directory (/u01/app/oracle) is typically referred to as $ORACLE_BASE.

OFA Standard Recommendation 3:Refer to explicit path names only in files designed specifically to store them, such as the UNIX /etc/passwd file and the Oracle oratab file; refer to group memberships only in /etc/group.

Typical Application: Whenever possible, refer to the oracle user’s home directory as ~oracle and use other environment variables such as $ORACLE_HOME and $ORACLE_BASE instead of full paths. As an example, you would typically use $ORACLE_BASE/admin instead of /u01/app/oracle/admin.

Options: ~oracle, $ORACLE_BASE and $ORACLE_HOME are typically used as environment variables. Other variables such as $ORACLE_ADMIN can be set and used in a similar fashion.

OFA Standard Recommendation 4: Store each version of Oracle Server distribution software in a directory matching the pattern h/product/v, where h is the login home directory of the Oracle software owner, and v represents the version of the software.

Typical Application: Oracle server software should be stored in a directory below the home ($ORACLE_BASE) in the format $ORACLE_BASE/product/9.2. 9.2 should be replaced with the version of Oracle software installed.

Options: This could be on any of the /u01 sequence of partitions.

NOTE: For 10g Oracle has added another level to allow multiple installs of the same version of software. The new recommendation is to install software in a directory in the format of $ORACLE_BASE/product/10.1/db_1.

OFA Standard Recommendation 5:For each database with db_name=d, store database administration files in the following
subdirectories of h/admin/d:
• adhoc — ad hoc SQL scripts for a given database
• adump — audit trail trace files
• arch — archived redo log files
• bdump — background process trace files
• cdump — core dump files
• create — programs used to create the database
• exp – database export files
• logbook — files recording the status and history of the database
• pfile — instance parameter files
• udump — user SQL trace files
where h is the Oracle software owner’s login home directory.

Typical Application: Again the $ORACLE_BASE directory should be used for the home directory, resulting in a path of $ORACLE_BASE/admin/adump

Options: Not all these directories are always used; however, if there are multiple DBAs I highly recommend the adhoc and logbook directories.

OFA Standard Recommendation 6:Name Oracle database files using the following patterns:
• /pm/q/d/control.ctl — control files
• /pm/q/d/redon.log — redo log files
• /pm/q/d/tn.dbf — data files
where pm is a mount point name, q is a string denoting the separation of Oracle data from all other files, d is the db_name OFA Standard • 21 Oracle System Performance Group Technical Paper, September 24, 1995 of the database, n is a distinguishing key that is fixed-length for a given file type, and t is an Oracle tablespace name. Never store any file other than a control, redo log, or data file associated with database d in /pm/q/d.

Typical Application: Control, redo, and data files are typically stored in one or more of the /u01 series of partitions in a subdirectory called oradata then in a folder matching the database name. The resulting path should resemble /u01/oradata/orcl.

Only control, redo, and data files should reside in this path and they should have the appropriate extensions (.ctl, .log or .dbf respectively).

Control and redo files should have a fixed length number to identify them, such as control01.ctl or redo04.log.

Data files should contain the tablespace name and a fixed length number resulting in the format system01.dbf.

Options: Many place the database name in these files. This is redundant since the database name is already in the path to these files and should be avoided as it complicates changing the database name.

OFA Standard Recommendation 7: Separate groups of segments with different lifespans, I/O request demands, and backup frequencies among different tablespaces. For each Oracle database, create the following special tablespaces in addition to those needed for applications segments:
• SYSTEM — data dictionary segments only
• TEMP — temporary segments only
• RBS — rollback segments only
• TOOLS — general-purpose tools only
• USERS — miscellaneous user segments

Typical Application: Here a segment is a piece of data in the database associated with a table, index, or other database object. Basically you should represent the tablespaces listed above and any needed for the appropriate application. Do not put application data in any of these tablespaces, but instead create one or more tablespaces for each application.

Options: TOOLS and USERS tablespaces may be omitted, however it is likely you will want them in the long run.

NOTE: For database 10g Oracle has added a new tablespace called SYSAUX which contains all non-essential system components.

OFA Standard Recommendation 8: Name tablespaces connotatively with eight or fewer characters.

Typical Application: The eight character limit is not necessary; however, tablespace names should be kept fairly short. More importantly tablespace names should be indicative of their contents. WEBCT_DATA or BANNER_INDEX_SMALL are acceptable tablespace names, but DATA or INDEX would not be.

Options: There is a lot of flexibility here. Using a consistent convention at your site is key.

oracle, sql, dba, database administration, database development, database security, database, oracle security

OracleHere is an example of the kernel parameter settings I typically use for Oracle Database on Solaris. This is provided only as an example. You should not implement these settings without understanding what they do!

For a more in-depth explanation of these parameters see my other article Semaphore Settings and Shared Memory for Oracle Database.

You should always consult the Oracle documentation for your platform and release of Oracle for recommended kernel settings.

These settings reside in the /etc/system file on Solaris and must be setup by the root user. Any line beginning with an asterisk (*) is treated as a comment and not processed by the operating system. After these settings are implemented in teh /etc/system file the system will need to be rebooted for them to take affect.

************************************************************
* Example Semaphore and Shared Memory Settings *
* for Oracle on Solaris *
* Written by Jon Emmons *
* www.lifeaftercoffee.com *
************************************************************
* Shared memory settings
set shmsys:shminfo_shmmax=4294967295
* shmmax sets the largest memory segment in bytes
* which can be allocated
set shmsys:shminfo_shmmin=1
* shmmin sets the smalles memory segment in bytes
* which can beallocated
set shmsys:shminfo_shmmni=500
* shmmni defines the maximum number of shared memory
* segments in the entire system
set shmsys:shminfo_shmseg=50
* shmseg defines the maximum number of shared memory
* segments which can be used by one process* Semaphore settings
set semsys:seminfo_semmni=300
* semmni sets the number of semaphore sets available
set semsys:seminfo_semmsl=500
* semmsl sets the number of semaphores per set
set semsys:seminfo_semmns=30000
* semmns sets the total number of semaphores available
* the actual semaphores available will be the lesser of
* (semmni * semmns) or semmns
set semsys:seminfo_semopm=250
* semopm determines the maximum number of operations
* per semop call

database, database administration, database tuning, dba, solaris, system administration, oracle

In UNIX semaphore and shared memory settings control several aspects of how applications can use memory. Many people just drop these settings into place without truly understanding what they do but this can bite you in the long run.

In this article I hope to shed some light on what these settings really do. Though settings vary by platform and database version their purpose remains constant. As always, mileage may vary. Consult the documentation for your system/db version before changing any settings.

My examples come from a Solaris system running several Oracle 9i databases. Semaphore and shared memory settings need to be set by root and will likely require a restart of the system to take affect.

Typically in UNIX each process obtains memory for its purposes. That memory is protected from access by other processes. Shared memory allows an application to allocate a chunk of memory which can be viewed by other processes. Oracle uses shared memory to create the System Global Area (SGA) which all Oracle processes must be able to access.

Semaphores act as flags for shared memory. Semaphores are either set on or off. When an Oracle process accesses the SGA (in shared memory) it will check for a semaphore for that portion of memory. If it finds a semaphore set on for that portion of memory that process will sleep and check again later. If there is no semaphore set on for that portion of memory it will set one on and proceed with its operation. When it is done it will switch that semaphore back to off.

If that all makes sense let’s look at some of these settings and some example values. These appear in the format you would see in a Solaris /etc/system file. An asterisk (*) is treated as a comment in this file.

Shared Memory Parameters

Ideally Oracle would like to find one contiguous chunk of shared memory to put the entire SGA into. When this is not possible it will attempt to find several contiguous segments of shared memory. Failing that it will resort to several non-contiguous segments of shared memory. If none of these are available your Oracle database will fail to start.

set shmsys:shminfo_shmmax=4294967295
*shmmax sets the largest memory segment which can be
*allocated

As the comment implies, this is the largest single segment of shared memory which can be allocated in the system. This setting is set in bytes. Setting this to a large value (half of the physical memory of the system or more) makes it possible that Oracle will be able to allocate the SGA in one contiguous memory segment. This example is from a machine with 8GB of physical memory so the largest shared segment is set to 4GB. Note that this is a maximum, so Oracle will still only occupy as much memory as the SGA requires.

set shmsys:shminfo_shmmin=1
*shmmin sets the smallest memory segment which can be
*allocated

This sets the minimum size a shared memory segment is allowed to be. Of course it is unlikely (read impossible) to have a 1 byte SGA, but having this set to 1 guarantees maximum flexibility.

set shmsys:shminfo_shmmni=500
*shmmni defines the maximum number of shared memory
*segments in the entire system

The number of shared memory segments available is controlled by SHMMNI. Ideally we may only use one per Oracle instance, but realistically it is nice to have plenty around.

set shmsys:shminfo_shmseg=50
*shmseg defines the maximum number of shared memory
*segments which can be used by one process

SHMSEG limits the number of simultaneous shared memory segments which can be accessed by an individual process. Again, this would ideally be low, but it doesn’t seem to hurt to have plenty of overhead on this setting.

Semaphore Parameters

Oracle requires one semaphore for each process of each database on a system. By this logic the number of semaphores available need only be equal to the PROCESSES parameter in the init.ora file (or spfile). For systems with multiple databases the number would be the sum of the PROCESSES parameter for all databases.

That’s nice in theory, but there are a couple additional considerations. For some reason durring database startup Oracle will request enough semaphores for twice the processes specified. I guess the theory is to make sure there are plenty of semaphores to go around.

It is also nice to consider expansion when planning semaphores. If you need to increase the PRCOESSES parameter for one of your databases, or want to add a database to an existing server, you would have to restart to update the semaphore parameters. For this reason it seems fortuitous to have relatively (but not astronomically) high values for the semaphore settings.

set semsys:seminfo_semmni=300
*semmni sets the number of semaphore sets available

This controls the number of semaphore sets available. In an ideal world we would only need one set per database, but more realistically it is nice to have a bunch extra around.

set semsys:seminfo_semmsl=500
*semmsl sets the number of semaphores per set

This parameter will determine how many semaphores exist in each semaphore set. Since semaphores are always allocated in sets it is most convenient if this is greater than or equal to the PROCESSES parameter for your largest database.

set semsys:seminfo_semmns=30000
*semmns sets the total number of semaphores available

SEMMNS limits the number of semaphores which can be generated in the system. I recommend setting this fairly high to avoid limiting the number of semaphores available. The actual number of semaphores available will actually be the lower of either SEMMNS or SEMMSL*SEMMNI.

set semsys:seminfo_semvmx=65534
*semvmx is the semaphore maximum value

I have to admit to not knowing exactly what SEMVMX controls (other than what is in the comment above). After some extensive googleing I cannot turn up a satisfactory answer for this. If you know, please leave a comment to this post.

NOTE: See comment from Christopher Gait regarding SEMVMX. He mentions that this parameter is obsolete in Solaris 10. I don’t believe this is even in the Oracle install guide for Database 9i and up, but has been left in this article for legacy purposes.

set semsys:seminfo_semopm=250
*semopm determines the maximum number of operations
*per semop call

This controls the number of operations which can occur during a single semaphore call. A value of 250 should allow a sufficient number of operations while keeping the number of calls low.

NOTE: These are examples given for educational purposes. As mentioned earlier in this article, always consult your documentation before changing system parameters.

oracle, database, dba, database administration, system administration, solaris, database tuning

Wondering if you’re doing everything possible to secure your Oracle database? You probably aren’t, but the Center for Internet Security has compiled a practical checklist which will get you damned close!

The Center for Internet Security (CIS) is a non-profit organization which is committed to providing information on best-practices for security. The CIS does not report every vulnerability in a piece of software, but rather provides a set of best-practices for setup and configuration of systems and applications to minimize security risks.

The CIS Oracle Benchmarks provide a wealth of information on installing and configuring Oracle. Far from a step-by-step on how to install Oracle, it does fill in many of the gaps that are easily overlooked.

The CIS approach is very practical. As an example, item 2.01 from the Oracle benchmark reads as follows:

2.01
Installation
Try to ensure that no other users are connected while installing Oracle 10g

The Oracle 10g installer application could potentially
create files in a temporary directory with public
privileges. It would be possible for any local user to
delete, overwrite or corrupt these files during the
installation process. Try to ensure that no other users
are connected while installing Oracle 10g. Also set the
$TMP and $TMPDIR environment variables to a
protected directory with access given only to the Oracle
software owner and the ORA_INSTALL group.

In this compressed format the Oracle 10g Benchmarks still span 55 pages; however, these 55 pages represent the equivalent of several years of experience.

While the complete list of benchmarks offered by the CIS is relatively small it hits the high points on enterprise level software. I expect in the long run the list will grow to include more of the open-source solutions we are now finding commonplace.

So grab your favorite sys-admin and a big cup of coffee and run through this checklist. You’ll be surprised what you find. If you happen to be the DBA _and_ the sys-admin you’d better make it an extra large coffee.

oracle, oracle security, oracle 10g, oracle 9i, database administration, dba, database, database security

For some unknown reason, Oracle considers it necessary to distribute their UNIX software in .cpio files. Since this is the only time I ever use cpio, I can never remember the command and I always end up looking it up.

Well, for future reference, here is how you extract a .cpio file to the current directory on most platforms:

cpio -idmv < filename_to_extract.cpio

Some platforms, like AIX, may give errors like this with these options:

cpio: 0511-903 Out of phase!
cpio attempting to continue...

cpio: 0511-904 skipping 732944 bytes to get back in phase!
One or more files lost and the previous file is possibly corrupt!

cpio: 0511-027 The file name length does not match the expected value.

If you run into these you need to add the c option as the headers are stored in ASCII. The command should now look like this:

cpio -idcmv < filename_to_extract.cpio

For more information refer to the man page for cpio, but this is all I ever do with cpio. For a better UNIX archiving utility, consider tar.

« Previous PageNext Page »