Pro Oracle Database 11g Administration (99 page)

If you've set up all of the databases using consistent standards, there should never be issues with locating the alert.log and/or associated trace files. As seen in Chapter 3, I define an operating system function that will derive the location of the alert.log from the ORACLE_BASE and ORACLE_SID variables.

For example, the following shell function works in either an 11
g
or 10
g
environment (assuming that standard Oracle OFA standards are in place for directory names):

#-----------------------------------------------------------#

# cd to bdump

function bdump {

echo $ORACLE_HOME | grep 11 >/dev/null

if [ $? -eq 0 ]; then

lower_sid=$(echo $ORACLE_SID | tr '[:upper:]' '[:lower:]')

cd $ORACLE_BASE/diag/rdbms/$lower_sid/$ORACLE_SID/trace

else

cd $ORACLE_BASE/admin/$ORACLE_SID/bdump

fi

} # bdump

#-----------------------------------------------------------#

When the prior function is placed within a startup file (like .bashrc), you'll be able to immediately navigate to the directory that contains the alert.log and trace files, like so: $ bdump

$ pwd

/ora01/app/oracle/diag/rdbms/o11r2/O11R2/trace

If you inherit environments that were setup by other DBAs, then you've probably noticed that the alert.log and trace files are sometimes located in non-standard locations. When looking for old trace files, I start by attempting to find the alert.log. Usually, there are associated trace files in the same 597

CHAPTER 22 ■ DATABASE TROUBLESHOOTING

directory as the alert.log file. Since the alert.log file has a specific and well-known name (alert_.log), it's usually easier to first find the it and then look for associated trace files.

For Oracle Database 11
g
and higher, there’s a text version of the alert.log in the following standard directory and name:

/diag/rdbms///trace/alert_.log The ADR base location is defined by DIAGNOSTIC_DEST database initialization parameter. If you haven't set DIAGNOSTIC_DEST, then Oracle derives the value from the ORACLE_BASE operating system environment variable. If the operating system ORACLE_BASE variable is not set, then DIAGNOSTIC_DEST is set to the value of ORACLE_HOME/log (where ORACLE_HOME is an operating system variable that is almost universally set).

For Oracle Database 10
g
, the standard location for the alert.log is defined to be:

/admin//bdump

For Oracle Database 10
g
, the prior path is usually the same value as what the BACKGROUND_DUMP_DEST

database initialization parameter is customarily set to. Typical values for the ORACLE_BASE operating system variable are /ora01/app/oracle or /u01/app/oracle. For example, here's an entry in the init.ora for the DEVDB database for the BACKGROUND_DUMP_DEST initialization variable: background_dump_dest=/ora01/app/oracle/admin/DEVDB/bdump

However, nothing prevents an inexperienced DBA from setting parameters like BACKGROUND_DUMP_DEST to completely non-standard locations. For example, here's a setting from a database I was recently asked to maintain:

background_dump_dest=/oralogs08/dba/admin/bdump

Why would a DBA set a parameter like that to such a non-standard location? It really doesn't matter.

You just have to be aware that DBAs sometimes enable features in ways that make maintenance much more difficult.

Regardless, if you can connect to your database through SQL*Plus, it's trivial to determine the location of even the most non-standard locations for the alert.log. This will correctly show the location of the alert.log for any version of the Oracle database:

SQL> show parameter background_dump_dest

How do you determine the location of the alert.log and trace files when the database won't start and the file has been placed in a non-standard (and not obvious) directory? In these situations, use the find command to locate the alert.log file. First, change directories to ORACLE_BASE: $ cd $ORACLE_BASE

Next, use the find command to attempt to locate the file:

$ find . -name "alert*.log"

If you don't have ORACLE_BASE defined or the alert.log isn't in a somewhat standard location, then the prior command may not find any files. In this situation, perform a more system-wide global search.

Navigate to the root directory of the box and then issue the find command: $ cd /

$ find . -name "alert*.log" 2>/dev/null

In the prior command, you're inspecting all files on the operating system in an attempt to locate a file named with "alert" in the first part of the string and ".log" in the last section of the string. When performing a global search, an error will be thrown when the find command attempts to access files that it has no permissions to read. The 2>/dev/null is useful because it redirects all error messages to the 598

CHAPTER 22 ■ DATABASE TROUBLESHOOTING

/dev/null (bit bucket) file. The result is that you don't see volumes of error messages, and you only see output when find successfully locates a file that matches the string pattern.

When attempting to find the alert.log, you may find more than one file. This happens when inexperienced DBAs move the alert.log from a standard location to a non-standard location and leaves old files scattered throughout the operating system.

When you think you've located the correct alert.log file, check the date on it to ensure that it has been recently updated. Sometimes you find an alert.log, but it turns out not to be the current file. You can check the last update time with the ls command long listing:

$ ls -altr alert_.log

Once you have found the correct alert.log, inspect the most recent entries in it for errors and or look for trace files in the same directory as the alert.log file:

$ ls -altr *.trc

If any of these trace files are over several days old, consider moving or removing them.

Removing Files

Needless to say, be very careful when removing files. When trying to resolve issues, the last thing you want to do is make things worse. Accidentally removing one critical file can be catastrophic. For any files you identify as candidates for deletion, consider moving the files (instead of deleting) them. If you have a mount point that has free space, move the files there and leave them for a couple of days and then remove them.

If you have identified files that can be removed, first list out the files that you will be removed before you actually delete them. Minimally, I do this before removing any file: $ ls -altr

After viewing the results returned by the ls command, remove the file(s). This example uses the Linux/Unix rm command to permanently delete the file:

$ rm

You can also remove files based on the age of the file. For example, say you determine that any trace files over two days old can be safely deleted. Typically, the find command is used in conjunction with the rm command to accomplish this task. Before removing files, first list out the result of the find command:

$ find . -type f -mtime +2 -name "*.trc"

If you are satisfied with the list of files, then add the rm command to remove them: $ find . -type f -mtime +2 -name "*.trc" | xargs rm

In the prior line of code, the results of the find command are piped to xargs command, which executes the rm command for every file found by the find command. This is an efficient method for deleting files based on age. However, make very sure that you know which files will be deleted.

Another file that sometimes consumes large amounts of space is the listener.log file. Since this file is actively written to by the listener process, you can't simply remove it. If you need to preserve the contents of this file, then first copy it to a backup location (that contains free disk space) and then truncate the file. In this example, the listener.log file is first copied to /ora01/backups and then the file is truncated by:

$ cp listener.log /ora01/backups

599

CHAPTER 22 ■ DATABASE TROUBLESHOOTING

Next, use the cat command to replace the contents of the listener.log with the /dev/null file (which contains zero bytes):

$ cat /dev/null > listener.log

Inspecting the Alert Log

When dealing with database issues, the alert.log should be one of the first files you check for relevant error messages. You can use either operating system tools or the ADRCI utility to view the alert.log file and corresponding trace files.

Viewing the Alert Log via OS Tools

After navigating to the directory that contains the alert.log, you can view the most current messages by viewing the end (furthest down) in the file (in other words, the most current messages are written to the end of the file). To view the last 50 lines, use the tail command:

$ tail -50 alert_.log

You can continuously view the most current entries by using the -f switch: $ tail -f alert_.log

You can also directly open the alert.log with an operating system editor (such as vi): $ vi alert_.log

Sometimes it's handy to define a function that will allow you to open the alert.log regardless of your current working directory. The next few lines of code define a function that locates and opens the alert.log with the view command in either an 11
g
or 10
g
environment:

#-----------------------------------------------------------#

# view alert log

function valert {

echo $ORACLE_HOME | grep 11 >/dev/null

if [ $? -eq 0 ]; then

lower_sid=$(echo $ORACLE_SID | tr '[:upper:]' '[:lower:]')

view $ORACLE_BASE/diag/rdbms/$lower_sid/$ORACLE_SID/trace/alert_$ORACLE_SID.log else

view $ORACLE_BASE/admin/$ORACLE_SID/bdump/alert_$ORACLE_SID.log

fi

} # valert

#-----------------------------------------------------------#

Usually the prior lines of code are placed in a startup file so that the function is automatically defined when you logon to a server. Once defined, you can view the alert.log by typing in: $ valert

When inspecting the bottom of the alert.log, look for errors that indicate issues with:

• Archiver process hung due to inadequate disk space.

• File system out of space.

• Tablespace out of space.

600

CHAPTER 22 ■ DATABASE TROUBLESHOOTING

• Running out of memory in the buffer cache or shared pool.

• Media error indicating a datafile is missing or damaged.

• For example, here's an error indicating there is an issue writing an archive redo log:

ORA-19502: write error on file "/ora01/fra/O11R2/archivelog/...

For a serious error message listed in the alert.log file there is almost always a corresponding trace file. For example, here is the accompanying message to the prior error message: Errors in file

/oracle/app/oracle/diag/rdbms/o11r2/O11R2/trace/O11R2_arc0_4485.trc

Inspecting the trace file will often (but not always) provide additional insight into the issue.

Viewing the alert.log Using the ADRCI Utility

If you're using Oracle Database 11
g
or higher, you can use the ADRCI utility to view the contents of the alert.log file. Run the following command from the operating system to start the ADRCI utility: $ adrci

You should be presented with a prompt:

adrci>

Use the SHOW ALERT command to view the alert.log file:

adrci> show alert

If there are multiple Oracle homes on the server, then you will be prompted to choose which alert.log you want to view. The SHOW ALERT command will open up the alert.log with the utility that has been set as the default editor for your operating system. On Linux/Unix systems, the default editor is derived from the operating system EDITOR variable (which is usually set to an utility such as vi).

■
Tip
When presented with the alert.log, if you are unfamiliar with vi, and want to exit, first hit the escape key, then press and hold down the shift key while also pressing the : key. Then type in a q!. That should exit you out of the vi editor and back to the ADRCI prompt.

You can override the default editor within ADRCI using the SET EDITOR command. This example sets the default editor to emacs:

adrci> set editor emacs

You can view the last N number of lines in the alert.log with the TAIL option. The following command displays the last 50 lines of the alert.log:

adrci> show alert -tail 50

If you have multiple Oracle homes, you may see a message like this:

601

CHAPTER 22 ■ DATABASE TROUBLESHOOTING

DIA-48449: Tail alert can only apply to single ADR home

The ADRCI utility doesn't assume that you want to work with one Oracle home over another on a server. To specifically set the Oracle home (for the ADRCI utility), first use the SHOW HOMES command to display all available Oracle homes:

adrci> show homes

Here is some sample output for this server:

diag/rdbms/e64208/E64208

diag/rdbms/e64211/E64211

diag/rdbms/e64214/E64214

Now to specifically set the Oracle home, use the SET HOMEPATH command. This sets the HOMEPATH to diag/rdbms/e64208/E64208:

adrci> set homepath diag/rdbms/e64208/E64208

To continuously display the end of the file, use the following command: adrci> show alert -tail -f

Press Ctrl+C to break out of continuously viewing the alert.log file. To display lines from the alert.log that contains specific strings, use the MESSAGE_TEXT LIKE command. This example shows messages that contain the ORA-27037 string:

adrci> show alert -p "MESSAGE_TEXT LIKE '%ORA-27037%'"

You will be presented with a file that contains all lines in the alert.log that match the specified string.

■
Tip
See the Oracle Database Utilities guide for full details on how to use the ADRCI utility.

Identifying Bottlenecks via Operating System Utilities

In the Oracle world, there is sometimes a tendency to assume that you have a dedicated machine for one Oracle database. Furthermore, this database is the latest version of Oracle, fully patched, and monitored by a sophisticated graphical tool. This database environment is completely automated and kept trouble free through the use of visual tools that quickly pinpoint problems and efficiently isolate and resolve issues. If you live in this ideal world, then you probably don't need any of the material in this chapter.