Troubleshooting

Many problems can be resolved before they become problems with a little proactive work (and monitoring) by the DBA. Troubleshooting could encompass an entire book; it is beyond the scope of this chapter to do anything more than give some general guidelines.

UNIX Shared Memory and/or Semaphore issues

The first thing to check when getting most any kind of ORA-7xxx errors is to consult the Oracle Installation Guide that accompanies the Oracle distribution software. This document lists the recommended settings for SHMMAX (max size of a shared memory segment), SHMMHI (max number of shared memory segments), SEMMNI (minimum number of semaphores on the system), SEMMNS (max number of semaphores on the system), and SHMSEG (max number of segments per process). Make sure these values are set to the recommendations. If Oracle is unable to allocate a semaphore, increase the SEMMNI and SEMMNS values. If Oracle is unable to attain shared memory, try increasing the SHMSEG value.

If Oracle aborts abnormally, it may leave semaphores and locks on shared memory behind, creating problems with trying to restart the Oracle processes.

---

Note:  
Use the UNIX command ipcs -s to identify semaphores, using ipcrm -s <id> to remove any Oracle semaphores.

---

---

Note:  
Use ipcs -m to identify and ipcrm -m <id> to clean up any Oracle locks on shared memory.

---

The following query (Listing 19.6) can be used to monitor both growth and the number of extents as well as the maximum number of extents assigned. A DBA should watch for tables that are getting close to their maximum number of extents. A DBA should monitor the number of extents and check for extent interleaving among various tables in the same tablespace (see Chapter 24 for more details on tablespace fragmentation).

Listing 19.6. Segment growth and extent assignment query.

SELECT    tablespace_name,

          segment_name,

          extents “EXTENTS”,

          max_extents “MAX EXTENTS”,

          bytes “SIZE”,

          owner “OWNER”

 FROM     dba_segments

WHERE     owner not in (‘SYS’,’SYSTEM’,’SCOTT’,’NET_CONF’)

  and     extents > 1

ORDER  BY  tablespace_name,owner,segment_name; 

If Oracle is having problems starting or stopping, or if there are abnormalities with any of the Oracle processes, the “alert.log” in the Oracle dump dest (see init.ora file or query v$parameters for location of dump dest) can help identify the processes and any associated Oracle error that was issued. There is some other information in this file as well, including all of the parameters used to start the Oracle instance, and, upon shutdown, the license high water mark, the current log, and the order in which things were shut down.

Sometimes UNIX processes that create large files can hit a Ulimit violation and have the process aborted. Ulimit is a global file system limit from the early days of UNIX. Its intent was to stop runaway user processes or prevent single users from creating files that exceeded their business need.

---

Tip:  
I always set Ulimit to a size larger than the largest single disk drive or RAID disk array.

---

Test any SQL*Net or Net8 changes with SQL*Plus. Test the new TNSNAMES, Server Names, and so on, by using the following syntax: sqlplus valid_user/valid_ password@<new TNSNAMES>. Problems are easier to debug from the server-side before adding the network and processes outside the server to the mix of things that can cause problems.

---

Tip:  
Indexes can be created with a NOSORT option if the index key data is already sorted.

---

---

Tip:  
Indexes can be rebuilt with the REBUILD command much more quickly than dropping and recreating the index.

---

I would not allow objects to have more than 120 extents. Oracle8 has lifted this restriction, however, at the expense of chaining additional blocks onto the object block header. I would review object growth of objects with more than 50 extents. These objects could benefit from reorganization and larger extent sizes.

Summary

This chapter introduces the reader to the many administrative needs of the Oracle8 server. You learn about backup and recovery needs, security, and access needs. Tablespace maintenance is covered, including an in-depth discussion on various types of tablespace fragmentation issues. Schema maintenance includes how to appropriately size objects. This chapter also contains a good overview of the tuning process that is discussed in detail in Chapter 24. This chapter concludes with a variety of miscellaneous tasks including details on troubleshooting.