Category: UNIX

Getting Hardware Information in Solaris

Here’s a quick way to find out what hardware is installed in your Solaris system, including RAM, CPUs, PCI cards, and external devices. The output will usually include the size of each memory chip in the system.

The one trick to using this command is to make sure you use the backtick instead of a single quote around the uname -i. The backtick is typically shift tilde and forces the command between the backticks to be executed and substituted in to that part of the command.

/usr/platform/`uname -i`/sbin/prtdiag

prtdiag actually displays system diagnostic information. I’ve used this on Solaris 7 through 10. The best parts is you do not even need to be root to execute this.

For more info on prtdiag check out the man page.

solaris, sun, unix, system administration, sysadmin

Oracle Prefers Solaris 10

It looks like Oracle is re-aligning themselves with Sun Solaris as their preferred 64-bit platform. In the March issue of Oracle Magazine they have a short announcement on the topic:

Oracle has chosen the Solaris 10, Sun Microsystems’ multiplatform, open source operating system, as its preferred development and deployment platform for most 64-bit architectures. Solaris 10 will be used throughout Oracle’s development organization, and Oracle will release and ship 64-bit versions of all Oracle products on Solaris prior to, or simultaneously with, release on other operating systems.

Check out the whole article here or sign up to get your free copy of Oracle Magazine.

Thanks to Jon G. for sending this on to me.

oracle, database, database administration, dba, systems administration, sysadmin, solaris, sun, sun microsystems, UNIX, open source, 64-bit

EditPlus for SQL Editing and More

EditPlus in actionWhile there are many text editors out there offering a broad set of features my favorite right now is EditPlus.

EditPlus is a Windows shareware application designed for text, HTML, Java, PHP, etc. It’s very thin requiring little (nearly no) load time and has many great features such as:

  • Syntax hilighting for many languages
  • EditPlus on Windows right-click
  • FTP and SFTP integration
  • Templates
  • Line Numbering
  • Column Selection
  • Optional Spell Checker

The list of features is long and every revision brings more. Check out the Features page for more.

I’ve been using the Oracle 9iR2 syntax file from the user files section of EditPlus.com and it seems to pick up all the SQL and PL/SQL syntax I use, although many other syntax files exist and you could always make your own.

As mentioned above, EditPlus is shareware. A single user license is only $30, and with discounts for buying in bulk there is no reason not to pay, but for now, download it, try it, and see why you can’t live without it.

Thanks to Zach for showing this to me a couple years ago.

text, text editing, editing, editor, edit, sql, plsql, pl/sql, sftp, ftp, notepad, wordpad, oracle, unix, php, perl, programming, database programming, dba, database administration, systems administration, c, c++, java, javascript, css, html

Oracle Optimal Flexible Architecture Explained

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

Kernel Parameter Settings for Oracle on Solaris

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