Linux and solari recipts for oracle dba

CHAPTER 1

Getting Started

Database administrators (DBAs) are crucial members of every information technology team. They are responsible for mission-critical tasks such as the following:

• Installing software and creating databases
• Providing a highly scalable and well-behaving database environment
• Monitoring and maintaining company databases
• Ensuring that corporate data is backed up, secured, and protected
• Troubleshooting system performance and availability issues
• Being the holistic source of database engineering information

These responsibilities require that a DBA possess a combination of database and operating system (OS) expertise. It’s a fact that you (the DBA) cannot architect, implement, or maintain a large, high-transaction database environment without being an expert on the underlying OS. In many situations, the OS is your only conduit to the database. Therefore, it’s imperative that you be particularly knowledgeable about the OS to competently perform your database administration duties.

When first building a database, DBAs should be able to specify solid, reliable, and scalable system configurations. Furthermore, DBAs are often the cog between system administrators, users, network administrators, managers, and corporate executives. If you’re working (after the fact) on a poorly designed system, you have to possess the tools to diagnose and resolve bottlenecks in the entire technology stack. Regardless of the source of the issues, team members often look to the seasoned DBA to resolve systemic performance, security, and availability concerns. We know this to be true because we live it every day (including nights and weekends).

Oracle software runs on a variety of OSs, including many Linux/UNIX variants and Windows. A large number of Oracle installations are on Oracle Linux or Oracle Solaris. Oracle Linux is Oracle’s version of Linux based on Red Hat Enterprise Linux (RHEL). Oracle Solaris is a UNIX OS originally created by Sun Microsystems and now owned by Oracle Corporation since Oracle’s acquisition of Sun in 2010.

The Linux and Solaris OSs are widely recognized as reliable 24/7, mission-critical, enterprise server platforms. Employers specifically seek out DBAs with Linux/Solaris expertise. In fact, as a DBA, it’s inevitable that you’ll someday use Linux or Solaris servers to store your data. You will be responsible for ensuring that your database is working seamlessly with the underlying OS. Managers will look to you to guarantee that corporate databases are competently implemented and maintained.

This book focuses on helping you understand how to efficiently manage Oracle software on Linux and Solaris servers. On the surface, you’ll find Linux and Solaris are mostly identical in commands and syntax (the same can be said for Linux and any UNIX variant). Having said that, there are occasional differences between the OSs that you have to be aware of. We’ll strive to point out these subtle differences where they exist. When there are no differences, we’ll simply refer to the OS as “Linux/Solaris,” meaning that the concept applies equally to both.

The information in this book will enable you to function as an expert DBA when performing key responsibilities. We provide direct answers to specific problems regarding Oracle database technology running on Linux/Solaris OSs. The recipes in this first chapter assume that you know nothing about Linux/Solaris and cover situations that you’ll be presented with when you first connect to a server and use OS commands. If you are already fairly experienced with the Linux/Solaris OS, feel free to skip this chapter.

In this chapter, we start by walking you through some of the most common methods for logging on to a Linux/Solaris server. We then cover the basics of running Linux/Solaris commands and detail how to use the built-in help and online documentation. We finish the chapter by showing techniques for correcting command-line mistakes and resetting a terminal screen.

1-1. Connecting Securely to a Remote Server

Problem

You’re using a Windows–based laptop or workstation and want to securely connect to a remote Linux/Solaris database server over the network.

Solution

This solution shows how to download and use the PuTTY application to initiate secure remote connections over the network:

1. To get started, download PuTTY from an Internet site such as www.putty.org or www.chiark.greenend.org.uk/~sgtatham/putty/download.html.
2. Once on the PuTTY download site, you’ll find links that enable you to download the PuTTY application. You can download just the putty.exe file or all utilities available via the putty.zip file.
3. After you download the desired files, navigate on your personal computer to the directory in which you downloaded the PuTTY utility. You should see a screen similar to Figure 1-1. Double-click the PuTTY icon to start the connection utility.
   

   Figure 1-1. PuTTY application icon
4. Figure 1-2 shows a partial screenshot of what you see next. From this screen, you can enter the hostname or IP address and connection port of the remote server to which you want to connect. Enter the connection details of your Linux/Solaris database server and click the Open button to initiate a remote connection. If you aren’t sure about the connection information, contact your system administrator for details.
   

   Figure 1-2. PuTTY connection details
5. After you connect to your database server, you should see the screen shown in Figure 1-3. Enter your username and password (contact your system administrator if you don’t know them). Once logged on, you can run shell commands to perform tasks on your database server. (Chapter 2 provides more detail about shells.)
   

   Figure 1-3. Linux/Solaris server logon screen

How It Works

PuTTY is a free open source utility that allows you to create a secure shell (SSH) connection to a remote database server. This utility is popular because it is a free, easy-to-use application that enables you to connect securely from a Windows client over the network to remote Linux/Solaris database servers. With this tool, you can store your server preferences and connection information, which eliminates the need to retype lengthy hostnames or IP addresses.

 Note  Other utilities also allow you to initiate remote connections via an SSH. For example, the Cygwin/X application is a popular Windows-based implementation of the X Window System. This Cygwin/X utility allows you to run X applications on your Windows desktop and start remote SSH connections to your database server.

You can also use PuTTY to connect via proxy servers and SSH tunneling. Examples are explained briefly in the next sections.

Connecting via a Proxy Server

Many companies require all their Internet connections to pass through a proxy server for security and performance reasons. To use PuTTY to connect via a proxy server, open the PuTTY Configuration dialog box and click the Proxy node under the Connection category, as shown in Figure 1-4.

Figure 1-4. PuTTY—proxy server configuration

Select HTTP for the proxy type, provide the hostname or IP address of the proxy server, and provide the corresponding port number. Save the changes to your PuTTY configuration for future use.

Connecting via SSH Tunneling

You can also use PuTTY for tunneling (also called port forwarding) to a remote server. To use tunneling, open the PuTTY Configuration dialog box and then choose the SSH as the connection type. Next, provide the hostname or IP address of the designated SSH server as well as the SSH port number (the default is 22). Afterward, click the Connection node, then the SSH node, and finally the Tunnels node, as shown in Figure 1-5.

Figure 1-5. PuTTY—SSH tunneling configuration

In the “Source port” field under the “Add new forwarded port” section, provide the port number you will connect to at your Windows client. In the Destination field, provide the hostname or IP address of the Linux/Solaris database server, as well as the port number. Save the changes to your PuTTY configuration for future use.

1-2. Logging on Remotely via the Command Line

Problem

Your system administrator has provided you with a username and password for your database server. You now want to log on to the server via a command-line utility such as telnet or ssh.

Solution

This example assumes that you can access a terminal from which you can initiate an ssh command. Depending on your environment, your “terminal” could be a PuTTY session (see recipe 1-1) on your home PC or workstation. Ask your system administrator for help if you’re not sure how to start a terminal session (this can vary quite a bit, depending on your working environment).

In this line of code, the username is oracle and the hostname is rmoug1:

$ ssh -l oracle rmoug1

If ssh successfully locates the database server, you should be prompted for a password (the prompt can vary depending on the OS version):

Password:

For security purposes, your password does not display as you type it. After typing your password, press Enter or Return to complete the logon process.

 Tip  If you think you made a mistake while entering your password, press Ctrl+U to erase all invisible text from the password line. This technique will save you time and prevent many accidental failed logins. Alternatively, you can try to use Backspace or Delete to erase any text you’ve entered.

How It Works

The ssh utility is widely used in Linux/Solaris environments for establishing secure connections to remote servers. By default, most SSH servers listen on port 22. You can verify the port by viewing the contents of the /etc/ssh/sshd_config file. Here’s an example:

$ cat /etc/ssh/sshd_config

Here’s a partial listing of the output:

# The strategy used for options in the default sshd_config shipped with
# OpenSSH is to specify options with their default value where
# possible, but leave them commented. Uncommented options change a
# default value.
#Port 22

If your system administrator has set up the server to listen on a port other than 22, you have to explicitly specify it with the -p (port) option. This example connects the oracle user to the rmoug1 server on port 71:

$ ssh -p 71 -l oracle rmoug1

After a valid username and password are entered, your system might display information such as the last time you logged on, from what machine you initiated the connection, whether your account has unread mail, and so on. Additionally, if your system administrator has entered any text within the /etc/motd (message of the day) file, that information also displays. The following text is a typical login message:

Last login: Wed Mar 28 14:12:50 2015 from from hlrn.rmoug.net

After your username and password have been successfully authenticated by the Linux/Solaris server, you should see a $ (dollar sign) prompt:

$

The $ character signifies that you are at the shell command-line prompt. The $ character is the default command-line prompt for most Linux/Solaris systems.

 Note  All command-line examples in this book show the $ prompt. You don’t have to type the $ prompt as part of any of the example commands in this book.

Your system administrator might have configured your account to display a different prompt from the $ character. DBAs sometimes configure the prompt to include information such as the server name and database name. See recipe 2-6 for changing your command-line prompt from something other than the default.

 Note  If you are logged in with the root account (sometimes called superuser), the default command-line prompt is the # character.

Some servers accept remote connections from telnet clients. For security reasons, we recommend that you do not use telnet to initiate a logon to a server over the network. The telnet utility does not use encryption and is vulnerable to hackers snooping on the network. Whenever possible, you should use the secure ssh tool for remote connections. However, you might occasionally have to use telnet because ssh isn’t available. The following example uses telnet to log on to a remote server over the network:

$ telnet -l oracle dbsrver

1-3. Logging off the Server

Problem

You want to log off the server.

Solution

Three methods for logging out of the database server are covered in this solution:

• Pressing Ctrl+D
• Typing exit
• Typing logout

The quickest way to log off is to press Ctrl+D, which immediately logs you off your server. In this example, the Ctrl and D keys are pressed at the same time:

$ Ctrl+D

You should now see a message like this:

Connection to <your server> closed.

You can also type the exit command to log off your database server:

$ exit

You should now see a message like this:

Connection to <your server> closed.

You can also type the logout command to log off the system:

$ logout

You should now see a message like this:

Connection to <your server> closed.

How It Works

If you start a subshell within an existing shell session, the logout techniques described in the “Solution” section exit you from only the innermost shell. For example, suppose that you have logged on to your server and then issued the following command:

$ bash

You now have started a subshell. If you want to exit the subshell, use one of the techniques described in the “Solution” section:

$ exit

Similarly, if you issue the su command to switch to another user, when you exit that session, you are returned to the shell from which you initiated the su command.

It’s a good security practice to log out of your OS session if you plan to be away from your terminal. As a DBA, you’ll typically find yourself logged on to the server as the user who owns the database binaries (usually the oracle OS account). The oracle account is like a database superuser account.

The database OS account can do potentially damaging operations such as drop databases, remove database files, and so on. Logging out ensures that your database OS account isn’t compromised.

 Tip  Set the TMOUT variable to limit the amount of idle time a session can have before it is automatically logged off. This parameter can be set globally in the /etc/bashrc file. See recipe 2-5 for how to automatically set variables when logging on to a server.

1-4. Running a Command

Problem

You’re new to Linux/Solaris, and you want to run a shell command from the OS prompt.

Solution

Linux/Solaris commands are run from the command line by typing them and pressing the Enter or Return key. This example uses the df (disk-free) command to display the amount of unused disk space on the database server:

$ df

There might be some differences in the output of df, depending on your OS version. On a Linux system, you should see output similar to this:

Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/xvda2           200095024 123528852  66237984  66% /
/dev/xvda1             1019208    138748    827852  15% /boot

On a Solaris system, the output of the df command might look different:

$ df

/                  (rpool/ROOT/solaris-1):104676373 blocks  104676373 files
/devices           (/devices            ):        0 blocks          0 files
/dev               (/dev                ):        0 blocks          0 files
/system/contract   (ctfs                ):        0 blocks 2147483592 files

Sometimes the output of various OS commands is formatted differently from Linux to Solaris. Usually this is important only if you’re trying to write a program (e.g., a shell script) that expects information to consistently be located in certain columns (see Chapter 7 for details on shell scripting). You have to adjust your scripts based on the format of the output for each OS.

How It Works

When you log on to a Linux or Solaris box, by default you are placed at the command line. (The command line is where you type shell commands to accomplish a given DBA task.) The default command-line prompt for most systems is the $ character.

You can modify the default behavior of a command by running it with one or more options (sometimes called flags or switches). This example shows uses the df command with the -h option to display the output in a more human-readable format:

$ df -h

Filesystem            Size  Used Avail Use% Mounted on
/dev/xvda2            191G  118G   64G  66% /
/dev/xvda1            996M  136M  809M  15% /boot

Commands can also take arguments, which typically designate a file or text that the command should use. When running a command, arguments are usually placed after the options. This example uses the df command with the -h option and uses the argument of /dev/sda2 (which is a particular filesystem):

$ df -h /dev/xvda2

Filesystem            Size  Used Avail Use% Mounted on
/dev/xvda2            191G  118G   64G  66% /

This book does not detail the use of various graphical user interfaces (GUIs), which are helpful when you don’t know the actual command. As useful as these graphical tools are, we strongly recommend that you explore using commands from the command-line prompt. As a DBA, you will encounter situations in which the GUI tool doesn’t do everything you need to accomplish a task. For some problems, you need access to the command line to debug and troubleshoot issues. Many complicated or custom DBA tasks require that you be proficient with command–line programming techniques. Although some sites have useful GUIs, some do not. All sites have the command line. If you get dependent on a specific GUI to do your work, you might be lost when you get to a site with no GUIs.

If you don’t know the appropriate shell commands and their features, you might waste time and effort solving a problem when it could have easily been resolved if you knew which tools and options were available.

GOLDEN HAMMER RULE

The Golden Hammer Rule can be stated this way: “When the only tool you have is a hammer, everything looks like a nail.” What does that mean? When people find a tool that solves a problem, they have a natural tendency to use that tool again and again to solve other problems. This dependence can occur because after you’re familiar with a given tool or technique, you’ll continue to use it because it’s available; you’ve had training with the tool; and you’ve developed a skill set.

Nothing is wrong with that approach per se. However, if you want to be a more marketable DBA, you should expand your horizons from time to time by learning new skills and investigating up-to-date methods for solving problems. In today’s ever-changing technology environment, the DBA with the most current skills is often the one who survives the longest.

It’s getting harder and harder to find database environments that don’t use Linux/Solaris. As a DBA, you should take the initiative to learn about Linux/Solaris and how this technology is used by companies around the world to provide cost-effective information technology solutions.

1-5. Getting Help

Problem

You want to find more information about how to use a shell command.

Solution

One extremely nice feature of Linux/Solaris is that there are several options for quickly obtaining more information regarding shell commands. Table 1-1 contains descriptions of command-line help features readily available on most systems:

Table 1-1. Common Help Features

Help Feature	Description
man	Read the online manual for a command.
whatis	View a brief description of a command.
which	Find a tool.
--help	Show help.
apropos	Display man page documentation.
info	List extensive documentation.
Tab key	Show available commands.

Each of the help methods in Table 1-1 is described in the following sections.

Reading Manual Pages

The man (manual) page for a command displays online documentation for almost every shell command. The following command displays information about man:

$ man man

Here’s a partial listing of the output:

man(1)

NAME
       man - format and display the on-line manual pages

SYNOPSIS
       man  [-acdfFhkKtwW]  [--path]  [-m  system]  [-p string] [-C config_file]
[-M pathlist] [-P pager] [-B browser] [-H htmlpager]...

The man command uses a screen pager—usually the less command—to display the help page. The less utility displays a : (colon) prompt at the bottom-left corner of the screen. You can use the spacebar to go to the next page and use the up and down arrows to scroll through the documentation line by line.

Table 1-2 lists the less command options available to you while viewing man pages. Press the Q key to exit the man utility.

Table 1-2. The less Command Options Available While Viewing man Pages

Keystroke	Action
J, E, or Down arrow	Move down one line.
K, Y, or up arrow	Move up one line.
Up arrow	Move up one line.
Down arrow	Move down one line.
/<string>	Search for <string>.
n	Repeat the previous search forward.
Shift+N	Repeat the previous search backward.
H	Display the help page.
F, spacebar, or Page Down	Move down one page.
B or Page Up	Move up one page.
Q	Exit the man page.

The man pages are usually divided into ten sections. The man command displays the first man page match it finds for a specified command. Sometimes a Linux/Solaris utility is documented in more than one man section. To view all man documentation available for a tool, use the -f option (this is equivalent to running the whatis command). This example views all man pages available with the cd command:

$ man -f cd

From the output, you can see that cd is documented in several different man sections:

cd                  (1p)  - change the working directory
cd                   (n)  - Change working directory
cd [builtins]        (1)  - bash built-in commands, see bash(1)

To view the man documentation specific to the cd utility, specify the 1p page:

$ man 1p cd

To scroll through all man sections associated with a command, use the -a option. Here is an example:

$ man -a cd

When in this mode, press the Q key to advance to the next man section of information.

CAPTURING MAN PAGES IN A TEXT FILE

It can sometimes be helpful to capture the output of a man command in a file that can be used later to search and scroll through with a text editor. The following command writes the output of the man page for the find command to a file named find.txt:

$ man find >find.txt

However, if you inspect the output file, you might notice that it contains unreadable characters that are produced from the man page output. Run the following command to clean up the output of the man page:

$ man find | col -b >find.txt

The previous command takes the output from the man command and sends it to the col -b (postprocessing filter) command. This filtering command removes the unreadable backspace characters from the man page output and makes them human-readable.

Viewing a Brief Description of a Command

If you’re new to Linux/Solaris (or if you have forgotten the material), use the aptly named whatis command to answer this question: “What is a command’s basic information?” The whatis command lists the first line of text from the man page. This example shows how to use the whatis command to find more information about the pwd command:

$ whatis pwd
pwd                  (1)  - print name of current/working directory
pwd                 (1p)  - return working directory name
pwd                  (n)  - return the absolute path of the current working directory
pwd [builtins]       (1)  - bash built-in commands, see bash(1)
pwd.h [pwd]         (0p)  - password structure

The number/letter (enclosed in parentheses) specifies the section of the man page in which you can find the command. When you see multiple lines listed by whatis, the command is documented in more than one location in the man pages. The output also indicates that there is a built-in Bash version of the command (see Chapter 2 for more details about built-in commands).

Another interesting use of the whatis command is to view a one-line description of commands in the /bin directory. This example uses whatis with ls, xargs, and less to view the descriptions of all commands in the /bin directory one page at a time:

$ cd /bin
$ ls | xargs whatis | less

arch                 (1)  - print machine architecture
awk                 (1p)  - pattern scanning and processing language
basename             (1)  - strip directory and suffix from filenames
...

The previous code first lists the files in the /bin directory; its output is then piped to the xargs command. The xargs command takes the output of ls and sends it to the whatis utility. The less command displays the output one page at a time. To exit from the documentation (displayed by less), press the Q key (to quit).

 Note  The whatis command is identical to the man -f command.

Locating a Command

Use the which command to locate the executable binary file of a command. This line of code locates the binary man executable file:

$ which man
/usr/bin/man

The which command is extremely useful for determining whether binary files or utilities are available on a server and where they are located. You might find that many commands and utilities are not consistently available, depending on which Linux/Solaris packages have been installed. Talk to your system administrator if you need a utility that is not available.

Showing Help

On Linux systems, use the --help option to quickly display basic information about a tool’s use and syntax. This example demonstrates how to get help for the df command:

$ df --help

Here’s a partial listing of the output:

Usage: df [OPTION]... [FILE]...
Show information about the filesystem on which each FILE resides,
or all filesystems by default. Mandatory arguments to long options are mandatory
for short options too.
  -a, --all             include filesystems having 0 blocks
  -B, --block-size=SIZE use SIZE-byte blocks
  -h, --human-readable  print sizes in human readable format (e.g., 1K 234M 2G)

Depending on the version of the OS, there might not be a --help option for shell commands. To that point, there is no --help option for Solaris shell commands. If this is the case, you have to use one of the other documentation sources (e.g., man) listed in this recipe.

Finding Manual Page Documentation

If you can remember only part of the name of the utility you seek, use the apropos command to find more documentation. The apropos command is similar to whatis, except that it searches for any string that matches your input. The following example searches the whatis command for the string find:

$ apropos find

Here’s a partial snippet of the output:

find               (1)    - search for files in a directory hierarchy
find              (1p)    - find files
find2perl          (1)    - translate find command lines to Perl code
findchip           (8)    - checks the FIR chipset
findfs             (8)    - find a filesystem by label or UUID

The previous output shows that many different types of find commands are available. Use the man command (previously discussed in this recipe) to view more information about a particular find command. The number in the second column (in parentheses) lists the section of the man page in which the documentation is contained.

 Note  The apropos command is equivalent to the man -k command.

Listing Extensive Documentation

The info utility often contains extensive documentation for many Linux/Solaris commands. To view all documents available, type info with no parameters, as shown here:

$ info

Here’s a small snippet of the output:

File: dir       Node: Top       This is the top of the INFO tree

  This (the Directory node) gives a menu of major topics.
  Typing "q" exits, "?" lists all Info commands, "d" returns here,
  "h" gives a primer for first-timers,
  "mEmacs<Return>" visits the Emacs topic, etc.

  In Emacs, you can click mouse button 2 on a menu item or cross reference
  to select it.

* Menu:

Texinfo documentation system
* Pinfo: (pinfo).           curses based lynx-style info browser.
* Texi2HTML: (texi2html).  Texinfo to HTML Converter.
...

Once within the utility, use the N key to go to the next section. The P key takes you to a previous section. Any line that starts with an asterisk (*) is a link to other sections (nodes) in the document. To go to a linked document, navigate to the line containing the asterisk and press Enter or Return. Press the Q key to exit the info page.

Table 1-3 lists some of the commonly used navigational info commands.

Table 1-3. Commonly Used Navigation Keystrokes Within the info Utility

Keystroke	Action
N	Move to the next section.
P	Move to the previous section.
Enter/Return	Move to a linked document.
Q	Exit.
?	List all commands.
D	Return to the introduction page.
H	Go to the tutorial.

You can also view information regarding specific commands. This example starts the info utility to display help for the cpio command:

$ info cpio

To view a tutorial on info, type $ info.

Showing Available Commands

If you’re using the Bash shell (see Chapter 2 for details on using a shell), you can use the Tab key to show all executable files that start with a certain string. For example, if you want to view all commands that start with the string ls, type ls and press the Tab key twice (with no space between the ls command and pressing the Tab key):

$ ls<Tab><Tab>
ls            lsb_release  lsnrctl       lspgpot
lsattr        lshal        lsnrctl0      lss16toppm

After Tab is pressed twice, the Bash shell attempts to find all commands that start with ls that are located in any directories contained in the PATH variable. This feature of automatically looking for files is known as command completion. (See recipe 2-2 for more details on command completion.)

How It Works

The “Solution” section of this recipe contains some of the most useful information you’ll need to elevate your Linux/Solaris skills. You should take some time to become familiar with all the helpful techniques described in this recipe.

Linux/Solaris has extensive utilities for easily viewing command documentation. Using these built-in help features allows you to quickly find basic syntax and use of a given shell command. In particular, we suggest that you become familiar with man and info; you’ll use these informational tools on a regular basis.

1-6. Correcting Command-Line Mistakes

Problem

You’re a typical DBA, so you often mistype things on the command line. You wonder whether there are command-line tools to correct your typing mistakes.

Solution

If you’re using the Bash shell (see Chapter 2 for details on using a shell), press Ctrl+_ (underscore) at the same time to undo what you just typed at the command line. Notice that you have to use the Shift key to get the underscore (_) character. If you type a long command string, pressing Ctrl+_ erases everything to the left of the prompt. If you have backspaced over a command, pressing Ctrl+_ undoes what you have backspaced over.

How It Works

Other keystrokes are available to help you undo what you just typed. For example, you can use Ctrl+T to transpose two characters just to the left of the prompt (ensure that there is no space between the command characters and Ctrl+T). This next bit of code uses Ctrl+T to transpose the last two characters of the letters pdw:

$ pdw Ctrl+T

You should now see the following:

$ pwd

Table 1-4 summarizes the commands available for correcting typing mistakes at the command line.

Table 1-4. Command-Line Keystrokes to Correct Typing Errors

Keystroke	Action
Ctrl+_	Undo what was just typed in.
Ctrl+U	Clear out everything to the left of the prompt.
Ctrl+T	Transpose two characters that are immediately to the left of the prompt.
Alt+T	Transpose two words that are on the left of the prompt.

1-7. Clearing the Screen

Problem

Your screen has become cluttered with command output. You want to clear the screen of any previously displayed text or command output.

Solution

Either use the clear command or press Ctrl+L to clear your terminal screen. The clear command does what you would expect: it clears the screen. Simply type the command as shown with no options or arguments:

$ clear

If you’re using the Bash shell (see Chapter 2 for details on shells), another method for clearing the screen is to press Ctrl+L:

$ Ctrl+L

One nice feature about Ctrl+L is that you can enter this command while typing other commands on the command line. Pressing Ctrl+L clears the screen and retains any current commands you have entered on the command line. For example, suppose that you are in the middle of typing a find command; you can enter Ctrl+L as shown here:

$ find . -name *.sql Ctrl+L

When you press Ctrl+L, it clears the screen and places the command you are currently typing at the top of the screen. In this example, the find command appears at the top of the screen:

$ find . -name *.sql

How It Works

The clear command removes all output visible on your screen and retrieves environment information from the terminfo database to determine how to clear the screen (use man terminfo for details regarding your environment).

The Ctrl+L keystroke works with the Bash shell and it can work with other shells, depending on your version of the OS. Unlike the clear command, Ctrl+L retains whatever command you are currently typing and displays it at the top of the cleared-out screen.

1-8. Resetting the Screen

Problem

Your screen has become cluttered with strange, unreadable characters. Using the clear command and Ctrl+L doesn’t have any effect.

Solution

Try to use the reset command to restore the screen to a sane state:

$ reset

If the reset command doesn’t work, try the stty sane command:

$ stty sane

If that doesn’t work, try exiting your terminal session and restarting it. Although it is not the ideal solution, sometimes it is the only thing that does work.

How It Works

Sometimes your screen can become cluttered with unreadable characters. For example, it can happen if you accidentally use the cat command to display the contents of a binary file. Use either the reset or stty sane command to restore your screen to a normal state.

The reset command is actually a symbolic link (see recipe 5-33 for more details on links) to the tset (terminal initialization) command. (View the man pages for tset for more details on this utility.) The reset command is particularly useful for clearing your screen when a program abnormally aborts and leaves the terminal in an unusual state.

The stty (set terminal type) command displays or changes terminal characteristics. If you type stty without any options, it displays the settings that are different from those set by issuing the stty sane command.

CHAPTER 2

Working in the Shell

Every Linux/Solaris system includes at least one command-line interpreter that enables you to interact with the OS. This interpreter is known as a shell. It is an appropriate moniker because the shell’s purpose is to act as a layer that shields you from having to know the internal workings of the OS. The shell allows you to perform complex tasks using simple commands.

If you’re a DBA, you should know how to run shell commands from the command line (OS prompt). Yes, you can perform many DBA tasks through various GUIs, but regardless of the robustness of the GUI, you still have to use the command line to perform tasks that the GUI can’t handle. For example, you might be in a stressful database restore-and-recovery situation in which you have access only to a terminal to run the appropriate commands. In this scenario, your job depends on being able to work from the command line to diagnose possible media failures and then perform the appropriate database restore-and-recovery operations.

There are many command-line interpreters available (the Bourne Again (Bash) shell, Korn shell, C shell, and so on). Because the Bash shell is usually the default command-line interpreter on many systems, the focus of this chapter (and this book, for that matter) centers on how to perform DBA tasks using the Bash shell. We strongly encourage you to use the Bash shell as your default command-line interpreter because it incorporates most of the useful features of other available shells and provides additional functionality. Where appropriate, we’ll also juxtapose key features of the Bash shell with both the Korn shell and C shell.

When you log on to your database server, you are “in” a shell, which means that you can enter commands that the shell will interpret and run for you. The most common default shell prompt is the $ (dollar sign) character, so most examples in this book denote the shell command line with the $ character. Here’s an example:

$ <commands run here>

 Note  The # (hash) is used for the prompt to denote operations that need to be performed as the root user.

This chapter covers topics such as obtaining information about the shell environment and customizing it. The solutions will enable you to work efficiently at the command line on database servers. The command line is where you will perform most of your database administrator activities.

Because a typical DBA spends several hours per day using commands at the shell prompt, the shell is a tool that all DBAs must thoroughly understand. The solutions in this chapter will form the building blocks for the more complex DBA tasks covered in the rest of this book.

2-1. Displaying the Current Shell

Problem

You want to verify which shell you are currently using.

Solution

When you first log on to a server, you can verify the default shell viewing the value of the SHELL variable. You must specify a $ in front of the variable to display its contents:

$ echo $SHELL

In this example, the output indicates that the Bash shell is in use:

/bin/bash

Many of the recipes in this chapter require the use of the Bash shell. What if the prior output showed that you were using a shell other than the Bash shell? For example, suppose that you view the value contained in $SHELL, and it indicates that your default shell is the C shell after first logging in:

$ echo $SHELL
/bin/csh

In this situation, to enter a Bash shell session type in the bash command, use the following:

$ bash

The prior command does not change your default logon shell, so the value of SHELL remains unchanged. However, you can use the $0 variable to show your current working shell. The $0 variable holds the name of the shell or script that is currently running. The following command verifies that the current working shell has been changed to the Bash shell:

$ echo $0
bash

In this situation, to exit out of the Bash shell session, type the exit command, which returns you to the parent session:

exit

 Tip  See recipe 2-13 for details on changing your default logon shell.

How It Works

You might occasionally be logged on to a box and wonder why certain Bash shell commands aren’t working. This is most likely because your default logon shell is not the Bash shell. In shops that have a large number of Linux/Solaris servers, there might not be a consistent shell used as a common standard. In these types of environments, first verify the default logon shell in use by displaying the contents of the SHELL variable. As shown in the solution section of this recipe, you can modify the current working shell by entering in the name of the shell you want to enter.

 Note  On Linux boxes, you can verify which shells are available by viewing the contents of the /etc/shells file. This file isn’t a standard file on Solaris systems, but it can be created by the system administrator.

Another method for viewing the current shell is to run the ps command without any options:

$ ps

The first line of the output shows which shell you are currently using. You should see output similar to this:

PID  TTY       TIME     CMD
9088 pts/1     00:00:00 bash
9137 pts/1     00:00:00 ps

If you enter a shell from a shell (e.g., from the C shell to the Bash shell), you should see all shells in use in the output. The following example demonstrates this. First verify your default shell:

$ echo $SHELL
/bin/csh

Next, enter the Bash shell:

$ bash
$ echo $0
bash

Now view the process status:

$ ps
  PID TTY          TIME CMD
 1521 pts/13   00:00:00 csh
 5944 pts/13   00:00:00 bash
 5951 pts/13   00:00:00 ps

When the ps command is used without any parameters, it displays all processes associated with the current user and terminal. So you might see more processes, depending on which commands you executed in the current session.

 Tip  On Linux boxes, use the pstree command to view the hierarchical relationship of processes. On Solaris systems, use the ptree command to view process ancestry.

2-2. Running Previously Entered Commands

Problem

You are spending a lot of time retyping commands that were previously entered. You want to view, edit, and rerun shell commands that were recently executed.

Solution

One timesaving feature of the Bash shell is that it has several methods for editing and rerunning previously executed commands. This bulleted list highlights several options available for manipulating previously typed commands:

• Scrolling with the up and down arrow keys
• Using Ctrl+P and Ctrl+N
• Listing command history
• Searching in reverse
• Setting the command editor

Each of these techniques is described briefly in the following sections. Keep in mind that some of these techniques might not be available, depending on the OS version.

Scrolling with the Up and Down Arrow Keys

Use the up arrow to scroll up through your recent command history. As you scroll through previously run commands, you can rerun a desired command by pressing Enter or Return.

If you want to edit a command, use the Backspace key to erase characters or use the left arrow to navigate to the desired location in the command text. After you have scrolled up through the command stack, use the down arrow to scroll back down through previously viewed commands.

 Note  If you’re familiar with Windows, scrolling through the command stack is similar to the DOSKEY utility.

Pressing Ctrl+P and Ctrl+N

The Ctrl+P keystroke (pressing the Ctrl and P keys at the same time) displays your previously entered command. If you have pressed Ctrl+P several times, you can scroll back down the command stack by pressing Ctrl+N (pressing the Ctrl and N keys at the same time).

Listing Command History

Use the history command to display commands that have been previously entered by the user:

$ history

Depending on how many commands have previously been executed, you might see a lengthy stack. You can limit the output to the last n number of commands by providing a number with the command. For example, the following lists the last five commands that have been run:

$ history 5

Here is some sample output:

273  cd -
274  grep -i ora alert.log
275  ssh -Y -l oracle 65.217.177.98
276  pwd
277  history 5

To run a previously listed command in the output, use an exclamation point (!, sometimes called the bang) followed by the history number. In this example, to run the pwd command on line 276, use ! as follows:

$ ! 276

To run the last command you ran, use !!, as shown here:

$ !!

Searching in Reverse

Press Ctrl+R (press the Ctrl and R keys at the same time), and you’ll see the Bash shell reverse search utility:

$ (reverse-i-search)`’:

As you type each letter from the reverse-i-search prompt, the tool automatically searches through previously run commands that have text similar to the string you entered. As soon as you see the desired command match, you can rerun the command by pressing Enter or Return. To view all commands that match a string, press Ctrl+R repeatedly. To exit from the reverse search, press Ctrl+C.

Setting the Command Editor

You can use the set -o command to make your command-line editor to be either vi or emacs. This example sets the command-line editor to be vi:

$ set -o vi

Now when you press Esc+K, you are placed in a mode in which you can use vi commands to search through the stack of previously entered commands.

For example, if you want to scroll up the command stack, you can use the K key and you can scroll down using the J key. When in this mode, you can use the slash (/) key and then type a string to be searched for in the entire command stack.

 Tip  Before you attempt to use the command editor feature, ensure that you are thoroughly familiar with either the vi or emacs editor (see Chapter 4 for details on using vi).

A short example illustrates the power of this feature. Suppose that you know you ran the ls -altr command about an hour ago. You want to run it again, but this time without the r (reverse sort) option. To enter the command stack, press Esc+K:

$ Esc+K

You should now see the last command you executed. To search the command stack for the ls command, type /ls and then press Enter or Return:

$ /ls

The most recently executed ls command should appear at the prompt:

$ ls -altr

To remove the r option, use the right arrow key to place the prompt over the r on the screen and press X to remove the r from the end of the command. After you edit the command, press Enter or Return to have it executed.

How It Works

Your command history is a stored sequential list of all the commands you have previously entered. You can use any of the techniques described in the “Solution” section of this recipe to view and manipulate previously entered commands.

The Bash shell command history is stored in your home directory in a file named .bash_history. If your current working directory is your home directory, you can view the contents of this file with a utility such as cat. The following example uses the cd command to navigate to the home directory and then displays the contents of the .bash_history file with the cat command:

$ cd
$ cat .bash_history

The number of entries stored in the .bash_history file is determined by the HISTSIZE OS variable. You can verify the history size using the echo utility. On this system, the command history size is 1000:

$ echo $HISTSIZE
1000

System administrators usually set the HISTSIZE variable in the /etc/profile file, which is automatically executed whenever a user logs on to a server. You can override the system’s default value for command history by setting the HISTSIZE variable in a special startup file (see recipe 2-5 for details).

The easiest way to view the Bash shell command history is to use the up and down arrows to find the command of interest, and use the left and right arrows and/or the Backspace key to modify the command. Other shells typically do not allow the use of the up and down arrows.

Table 2-1 lists some common shells and what types of command history manipulation each tool supports.

Table 2-1. Command History Options Available in Each Shell

2-3. Automatically Completing Long Commands

Problem

You are tired of typing long commands and wonder whether there is some way the Bash shell can automatically fill in the text for long command strings.

Solution

The Tab key can be used for command completion. For example, suppose that there are two files in the current directory: initRMOUGDB.ora and initBRDSTN.ora:

$ ls
initBRDSTN.ora initRMOUGDB.ora

In this example, you want to edit the initRMOUGDB.ora file with the vi utility. All you have to do is type enough characters to make the file name unique within a directory and then press Tab (ensure that there is no space between the text and the Tab key):

$ vi initR<Tab>

Because there are no other files in the current directory that begin with the string initR, the Bash shell automatically fills in the text initRMOUGDB.ora:

$ vi initRMOUGDB.ora

Now you can press Enter or Return to edit this file. Note that you can use this technique on any program, directory, command, or file. For example, if you need to change directories to a subdirectory named products, and there are no other directories beneath your current working directory that start with the letter p, you can type cd p and then press Tab. Your prompt then shows the following:

$ cd products/

You can now press Enter or Return to execute the cd products command.

How It Works

A timesaving feature of the Bash shell is command completion, which allows you to only partially type a program, command, file, or directory. You then press Tab, and the shell attempts to complete the rest of the text.

If the Bash shell can’t uniquely identify a program, command, file, or directory, it beeps (or does nothing) after you press Tab. In this situation, if you press Tab again, the shell displays all the possible programs, commands, files, or directories that match the partially typed string.

For example, command completion can be used to show all executable files that start with a certain string. If you want to view all commands that start with the string di, type di and press Tab twice:

$ di<Tab><Tab>
diff        diff-jars  dir        dirname    disable    disown
diff3       dig        dircolors  dirs       disol

You might hear a bell or beeping sound after you enter the first Tab. The Bash shell will search for commands that start with the string di that are located in any directories contained within the PATH variable.

2-4. Viewing Environment Variables

Problem

You want to view the current settings of your environment variables.

Solution

You can use any of the following Linux/Solaris commands to display OS variables:

• printenv
• env
• set
• export
• echo

To display all variables set in your environment, use any of these commands (without any options): printenv, env, set, or export. The next bit of code uses the printenv command to show all environment variables:

$ printenv

Here’s a partial listing of the output:

HOSTNAME=rmg.rmg.org
TERM=cygwin
SHELL=/bin/bash
HISTSIZE=1000
SSH_TTY=/dev/pts/0
USER=oracle

If you know the name of the variable you want to display, you can display it directly with the echo command. To display the contents of an OS variable, you must preface it with a $ (dollar sign) character. This example uses the echo command to display the contents of the USER variable:

$ echo $USER
oracle

You can also use the printenv command to display environment variables. The following example uses printenv to show the current setting of the USER variable:

$ printenv USER
oracle

How It Works

Most DBAs work with multiple database servers. Every time you log on to a server, certain OS variables are automatically set for you. Table 2-2 lists several environment variables you should know. Use any of the commands described in the “Solution” section of this recipe to view variables in your environment.

Table 2-2. Commonly Used Environment Variables

Variable	Description
PATH	Contains a list of directories in which the shell looks to find the executable commands. This variable is usually set by a shell startup script.
USER or LOGNAME	Contains the user account you used to log on to the server. It is automatically set for you when you log on.
HOME	Holds the home directory for a user. It is set for you when you log on.
~	Holds the home directory for a user. The tilde character is a shorthand way to reference your home directory. It is set for you when you log on.
PWD	Contains the location of the current working directory. It is set whenever you use cd to navigate to a new directory.
SHELL	Contains the name of your login shell.
EDITOR	Holds the name of the default editor used with some utilities (such as cron).
PS1	Contains the values used for displaying the command prompt.
SHLVL	Keeps track of how many subshell levels deep your current shell is.
DISPLAY	Is used by X applications to determine the display server used for input and output.

Sometimes you might not know the exact name of the variable. You can use the grep command to filter the output. This example uses the set command and sends the output to the grep command for filtering any variables:

$ set | grep ORA

Here’s typical output from the previous command:

ORACLE_BASE=/oracle
ORACLE_HOME=/oracle/product/10.2
ORACLE_SID=RMDB1
PS1=’[\h:\u:${ORACLE_SID}]$ ’

 Tip  For details on all variables set by the Bash shell in your environment, inspect the output of the man bash command. Search the man page for the Shell Variables section.

2-5. Automatically Setting Shell Variables

Problem

You want to automatically set various database variables whenever you log on to the database server.

Solution

Place shell variables that you want automatically set (when you log on) in the .bash_profile file in your home directory. In the next example, the ORACLE_SID variable is set to BRDSTN. The following text is placed in the .bash_profile file:

export ORACLE_SID=BRDSTN

If you log off and log back on, you can verify that this variable has been set by echoing it:

$ echo $ORACLE_SID
BRDSTN

If you don’t want to log off and back on, run the file manually using the . (dot) command, which executes the lines contained within a file. The following example runs the .bash_profile file:

$ . $HOME/.bash_profile

The . (dot) instructs the shell to source the script. Sourcing tells the shell process that you are currently logged on to inherit any variables set with an export command in an executed script. If you don’t use the . (dot) notation, the variables set within the script are visible only within the context of the subshell that is spawned when the script is executed.

 Note  In the Bash shell, the source command is equivalent to the . (dot) command.

How It Works

When you use the Bash shell, several special startup files can be executed for you when you first log on to your database server:

• /etc/profile
• ~/.bash_profile
• ~/.bash_login
• ~/.profile

 Note  Depending on the version of the Linux/Solaris OS, there might be different startup files in use. Run the ls -altr command in your home directory to view all the available files. Files beginning with a dot are sometimes referred to as hidden files and don’t appear in the output of the ls command when it is used with no arguments.

The /etc/profile file is maintained by your system administrator, and you need root privileges to modify it. This file sets systemwide variables common to all users logging on to the system. Here is a snippet of some typical entries in the /etc/profile file:

# No core files by default
ulimit -S -c 0 > /dev/null 2>&1
# Set OS variables
USER="`id -un`"
LOGNAME=$USER
MAIL="/var/spool/mail/$USER"
HOSTNAME=`/bin/hostname`
HISTSIZE=1000

After running the /etc/profile file, the Bash shell next searches for the following files and runs only the first file it locates (in the following order): ~/.bash_profile, ~/.bash_login, ~/.profile.

Here are some typical entries in the oracle user’s ~/.bash_profile file:

# User specific environment and startup programs
export ORACLE_SID=O1212
export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1
export PATH=$PATH:$ORACLE_HOME/bin
PS1=’[\h:\u:${ORACLE_SID}]$ ’

You should be aware of two additional important startup type files:

• ~/.bashrc
• ~/.bash_logout

If you start a nonlogin Bash shell by typing bash at the command line, the ~/.bashrc file is automatically executed for you. DBAs place commands in ~/.bashrc to ensure that database–related OS commands are consistently set, regardless of whether they are using a login shell or a nonlogin shell. A common technique to address this is to place the following code in the ~/.bash_profile file:

# Run .bashrc if it exists
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

When first logging on to a server, the previous bit of code checks to see whether the ~/.bashrc file exists; if so, it runs it. This method ensures that aliases and functions are defined in a consistent manner, regardless of whether it is a login or nonlogin shell.

The ~/.bash_logout file is appropriately named and is executed when you issue the exit command. Typically, you might see the clear command executed in the ~/.bash_logout file to clear text off the terminal when logging out.

2-6. Customizing the Command Prompt

Problem

You work with several database servers and usually have several terminal screens open simultaneously on your screen. To avoid confusion, you want the hostname and username to display in the command prompt.

Solution

In the Bash shell, the special OS variable PS1 holds the text string of what appears in the command prompt. This example changes the PS1 variable to hold the hostname and username:

PS1=’[\h:\u]$ ’

The \h and \u variables are special Bash variables that hold the hostname and username, respectively. After setting PS1 in this example, the prompt now shows the following:

[rmougprd1:oracle] $

In the prior string, rmougprd1 is the hostname, and oracle is the current OS user. You can also combine special variables and user-defined variables to be displayed in the prompt. For example, the following combination contains current hostname, username, and database SID:

PS1=’[\h:\u:${ORACLE_SID}] $ ’

Here’s what the command prompt looks like now:

[rmougprd1:oracle:ORA12CR1] $

If you place the previous line of code in your .bashrc startup file (see recipe 2-5 for details), your prompt will automatically be set to contain hostname, OS user, and the current value of ORACLE_SID every time you log on to the server or start a new Bash shell. Additionally, if you change the value of ORACLE_SID, your prompt will automatically reflect the new setting.

How It Works

Setting your command prompt to something informational can be invaluable for DBAs who work with multiple servers, OS accounts, and databases. If you work in complex environments, this simple technique will keep you aware of your current surroundings.

 Tip  You can view all variables available in your environment by issuing a man bash command. Search for PROMPTING in the man page.

Table 2-3 lists many of the Bash shell backslash–escaped special variables that you can use to customize your command prompt.

Table 2-3. Bash Shell Backslash–Escaped Variables Used to Customize the Command Prompt

Variable	Description
\a	ASCII bell character.
\d	Date in “weekday month date” format (for example, Thu Aug 21).
\e	ASCII escape character.
\h	Hostname.
\j	Number of jobs managed by shell.
\l	Base name of shell’s terminal device.
\n	Newline.
\r	Carriage return.
\s	Name of shell.
\t	Time in 24-hour HH:MM:SS format.
\T	Time in 12-hour HH:MM:SS format.
\@	Time in 12-hour a.m./p.m. format.
\A	Time in 24-hour HH:MM format.
\u	Current username.
\v	Version of Bash shell.
\V	Release of Bash shell.
\w	Current working directory.
\W	Base name of current working directory.
\!	History number of command.
\$	If effective UID is 0, display #. Otherwise, display $.

2-7. Creating a Command Shortcut

Problem

You frequently retype long sets of commands and want to create a shortcut to the lengthy commands.

Solution

There are two very common methods for creating shortcuts to other commands: aliases and functions. These two techniques are described in the following sections.

Using an Alias

Suppose that you often navigate to a database background process logging destination to view log files. You have to type something similar to this:

$ cd /u01/app/oracle/diag/rdbms/o1212/O1212/trace

You can use the alias command to create a shortcut to accomplish the same task. This example creates an alias named ad (alert directory) that will change directories to a background location that is dependent on the value of the ORACLE_SID variable:

$ alias ad=’cd /u01/app/oracle/diag/rdbms/o1212/O1212/trace’

Now you can type ad, which is the same as changing your current working directory to the Oracle background dump directory.

Using a Function

You can also use a function to create command shortcuts. We provide only a brief example of how to use a function in this recipe; for full details on using functions, see Chapter 7. The following line of code creates a simple function named ad:

$ function ad { cd /u01/app/oracle/diag/rdbms/o1212/O1212/trace; pwd;}

You can now type ad at the command line to change your working directory to the Oracle background dump directory. Notice that the current working directory is displayed by the pwd command after you change directories.

How It Works

An alias is a simple mechanism for creating a short piece of text that executes other shell commands. To show all aliases that have been defined, use the alias command with no arguments:

$ alias

Listed here are some common examples of alias definitions that DBAs use:

alias l.=’ls -d .*’
alias ll=’ls -l’
alias lsd=’ls -altr | grep ^d’
alias ad=’cd /u01/app/oracle/diag/rdbms/o1212/O1212/trace’
alias sqlp=’sqlplus "/ as sysdba"’
alias shutdb=’echo "shutdown immediate;" | sqlp’
alias startdb=’echo "startup;" | sqlp’
alias valert=’view /u01/app/oracle/diag/rdbms/o1212/O1212/trace/alert_O1212.log’

Depending on your implementation of Oracle’s Optimal Flexible Architecture (OFA) standard and the version of Oracle, you have to modify some of the previous alias definitions. This is especially true in regard to the location of the alert.log file.

If you want to view a definition for a particular alias, use the following:

$ alias ad
alias ad=’cd /u01/app/oracle/diag/rdbms/o1212/O1212/trace’

You can also use the type command to verify an alias:

$ type ad
ad is an alias for ’cd /u01/app/oracle/diag/rdbms/o1212/O1212/trace’

If you want to remove an alias definition from your current environment, use the unalias command. The following example removes the alias for ad:

$ unalias ad

The unalias command affects only your current connection. Although using aliases is a common way to create command shortcuts, we recommend using functions over aliases. Functions are more powerful because of features such as the ability to operate on parameters passed in on the command line and can contain multiple commands.

Functions are described in much more detail in Chapter 7, but we’ll take a few minutes to discuss them here. DBAs commonly establish aliases and functions by setting them in the $HOME/.bashrc file. For example, here we created a file named dba_fncs and placed the following lines of code in it:

#------------------------------------------
# cd to alert log directory
function ad {
 if [ $ORACLE_SID = "O1212" ]; then
    cd  /u01/app/oracle/diag/rdbms/o1212/O1212/trace
 elif [ $ORACLE_SID = "DWREP" ]; then
    cd /orahome/app/oracle/diag/rdbms/dwrep/DWREP/trace
 fi
 pwd
} # ad
#------------------------------------------

A common practice is to create a bin directory beneath the oracle account’s home directory and place the dba_fncs file in HOME/bin. The dba_fncs file can be manually sourced as follows:

. $HOME/bin/dba_fcns

If you put the prior line of code in the $HOME/.bashrc file, the functions in the dba_fncs file are sourced and available for you to use as command shortcuts every time you log on.

If you ever wonder whether a shortcut is an alias or a function, use the type command to verify a command’s origin:

$ type ad
ad is a function
ad ()
{
  if [ $ORACLE_SID = "O1212" ]; then
     cd /u01/app/oracle/diag/rdbms/o1212/O1212/trace;
    else
      if [ $ORACLE_SID = "DWREP" ]; then
            cd /orahome/app/oracle/diag/rdbms/dwrep/DWREP/trace;
      fi;
  fi;
  pwd
}

2-8. Providing Input to Commands

Problem

You want a shell command to receive its input from a file or another process instead of commands typed from the keyboard.

Solution

In Linux/Solaris, you can instruct a command to receive its input from a file with the < character. This technique is known as redirection. In this example, the mailx command is used to send a trace file named to an e-mail address:

$ mailx -s "trace file" dba@gmail.com <O1212_ora_21018.trc

You can also use the output of one command as the input to another command. This technique is known as pipelining. The pipe (|) character instructs a process to receive its input from the output of another process. This example uses the output of the cat command as the input to the mail command:

$ cat O1212_ora_21018.trc | mailx -s "trace file" dba@gmail.com

This technique of piping the output from one command to another is an extremely powerful tool. It is called a pipe because the output of the first command flows through the pipe and becomes the input of the next command. You can also think of the pipe as a temporary memory buffer that holds the output from the command to the left of the pipe. The command to the right of the pipe uses the output stored in the memory buffer as its input. DBAs and developers use this method to chain commands together to perform complex tasks. Many examples of this approach are provided throughout the book.

How It Works

In Linux/Solaris, there are three data streams associated with a process:

• Standard input (also called standard in or stdin)
• Standard output (also called standard out or stdout)
• Standard error (also called stderr)

Figure 2-1 displays the three data streams associated with each process. Starting on the left side of the figure, standard input is the data provided to the process, which is usually data entered by you from the keyboard. As demonstrated in the “Solution” section of this recipe, standard input can also come from a file or as the output of another process.

Figure 2-1. Process input and output data streams

Shell programs often generate output data. By default, standard output data is directed to the terminal. Standard output can also be redirected to a file or another process.

Sometimes shell programs generate error messages. By default, standard error messages are displayed to your terminal. Like standard output, standard error messages can be redirected to a file or another process.

The process doesn’t care where its input comes from or where the output is delivered. This means you can string together combinations of commands that feed output from one command to be used as the input to another command. This piping of command output to another command’s input is a very powerful and flexible feature of the shell.

Table 2-4 summarizes the ways in which standard input, output, and errors can be redirected using the Bash shell.

Table 2-4. Command Input and Output Redirection Operators

Operation	Bash Shell Redirection Syntax
Reads standard input from a file	command <file
Reads standard input until end of marker	command <<end of marker
Writes standard output to a file	command >file
Appends standard output to end of file	command >>file
Writes standard output and standard error to separate files	command >file 2>file2
Writes standard output and standard error to the same file	command >file 2>&1
Writes standard output and standard error to the same file	command &> file
Appends standard output and standard error to the end of a file	command >>file 2>&1
Sends (pipe) standard output of first command to input of second command	command | command2
Sends (pipe) standard output and standard error to input of second command	command 2>&1 | command2

Some additional explanation is required for interpreting the second column of Table 2-4. Notice that the < and << redirection operators are used to redirect standard input. The > and >> redirection operators are used to redirect standard output. Also notice that the redirection operators > and >> are shorthand for 1> and 1>>.

The 2> and 2>> redirection operators are used to redirect standard error. The syntax 2>&1 instructs the shell to redirect the standard error stream to the same location as standard output. In the Bash shell, you can also use the syntax &> to send both standard error and standard output to the same file.

The &0, &1, and &2 file descriptors are synonymous with the /dev/stdin, /dev/stdout, and /dev/stderr files, respectively. To illustrate this point, this example uses the file /dev/stdout to redirect standard error to the same location as standard output:

$ cat initBRDSTN.ora 1>myfile.txt 2>/dev/stdout

The previous command is equivalent to the following:

$ cat initBRDSTN.ora 1>myfile.txt 2>&1

The previous command directs the standard output 1> to be sent to myfile.txt. If any errors are encountered when issuing the command (for example, if file initBRDSTN.ora doesn’t exist), the standard error stream 2> is sent to the same location as standard out (which is myfile.txt).

2-9. Redirecting Command Output

Problem

You want to save the output of a command to a file.

Solution

By default, the output from a command displays on your terminal. The > character redirects the output of a command to a specified file. The > character is synonymous with 1>. For example, the following command takes the output of the cat command and places it in a file named output.txt:

$ cat init.ora >output.txt

If the init.ora file doesn’t exist, you’ll receive an error message such as this:

cat: init.ora: No such file or directory

How It Works

You should know about some other interesting features of redirecting command output. For example, you can also instruct the shell command to redirect any errors that are encountered when running a script to a separate file. The 2> characters specify where errors should be written. Just as > means redirect output to a file, 2> means redirect error messages to a file. This example redirects standard output to the file output.txt and sends any error messages encountered to errors.txt:

$ cat init.ora >output.txt 2>errors.txt

You can also use a shorthand notation to send both standard output and error messages to the same file:

$ cat init.ora >output.txt 2>&1

The 2>&1 notation instructs the shell to send output stream number 2 (error output) to the same place as output stream number 1 (standard output). You’ll find it useful to use this notation when running scripts such as database-monitoring jobs.

If for any reason you don’t want to overwrite the output file, use the >> syntax, which instructs the shell to append any messages to the end of an existing file:

$ cat init.ora >>output.txt 2>&1

2-10. Sending Output to Nowhere

Problem

You want to run a command, but you don’t want the output to be shown or saved anywhere.

Solution

If you don’t want text to appear on your screen or saved in a physical file, you can send it to the proverbial “bit bucket” (referred to as the /dev/null device).

The /dev/null device is useful when you don’t want to see all the error messages generated by a command. For example, the following find command generates error messages for directories that it can’t read:

$ cd /
$ find . -name "alert*.log"

Here is a partial snippet of the output:

find: cannot read dir ./var/fm/fmd/xprt: Permission denied
find: cannot read dir ./var/fm/fmd/rsrc: Permission denied
find: cannot read dir ./var/fm/fmd/ckpt: Permission denied

To eliminate those error messages, send the error output to the /dev/null device:

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

If you know you will run a program or command that generates output you don’t need, you can redirect the output to the /dev/null device.

 Tip  The /dev/null device can also be used to quickly reduce a large file to 0 bytes without deleting the original file (see recipe 5-31 for details).

How It Works

The /dev/null file is a special file known as the null device. It also called slash dev slash null or the bit bucket. It contains nothing, and any output you send to this special file will never be seen again.

The following is a slightly different example of how to use the /dev/null device. Suppose that you want to test how long it takes to read a file, but you don’t want the output of the dd command to actually create a file. In this example, the output is sent to the /dev/null device using the of parameter:

$ time dd if=/u01/dbfile/O1212/users01.dbf of=/dev/null

Here is some sample output:

20496+0 records in
20496+0 records out
10493952 bytes (10 MB) copied, 0.0820684 seconds, 128 MB/s
real    0m0.09s
user    0m0.00s
sys     0m0.01s

The previous command is useful for troubleshooting disk I/O issues without the overhead of physically creating a file. If you divide the data file size by the total time the command took to run, you get the disk I/O rate. You can compare that value with V$BACKUP_ASYNC_ IO.EFFECTIVE_BYTES_PER_SECOND to help you determine whether the Recovery Manager (RMAN) has a potential I/O problem.

CHECKING FOR DATA FILE CORRUPTION

In the old days, before there were tools such as dbverify and Oracle Recovery Manager (RMAN), DBAs needed a method to detect corrupt blocks in data files. To accomplish this, DBAs used the export utility. As the export utility runs, it writes information on detected corrupt blocks to the database alert.log file.

If the DBA wanted to check only for block corruption without saving the output of the export to a file, the output could be sent to the /dev/null file, as shown here:

$ exp user/pass full=y file=/dev/null

Thus, the DBA could detect database corruption without actually having to create a potentially large export file. Having said that, you should not rely on this approach for detecting database corruption because it has several known limitations (for example, it can’t detect corruption in blocks above a table’s high water mark—see MOS note 214369.1 for details). You should instead use dbverify or RMAN to detect bad blocks in Oracle database data files.

2-11. Displaying and Capturing Command Output

Problem

You want to see the output of a command onscreen and store that output in a file.

Solution

If you want to capture only the output associated with the execution of a specific OS command, you can use the tee command, which enables any output generated by a command to be written to both the screen and a designated file.

The following example runs the ls command to list all files in a directory. It also saves that output to a file that can later be viewed to determine the names and sizes of files on a particular date:

$ ls -altr /ora01/BRDSTN | tee /home/oracle/log/oct15_df.log

Here is some sample output of what is displayed to the screen and recorded in the log file:

-rw-r----- 1 oracle oinstall 52429312 Oct 15 08:00 redo03a.log
-rw-r----- 1 oracle oinstall 838868992 Oct 15 08:25 undotbs01.dbf
-rw-r----- 1 oracle oinstall 524296192 Oct 15 08:30 system01.dbf
-rw-r----- 1 oracle oinstall 15056896 Oct 15 08:37 control01.ctl

How It Works

The tee command is useful when you want to interactively see the output of a command displayed onscreen and also require that the display be logged to a file that can be inspected later for debugging or troubleshooting activities. For example, when you back up a database and you want to log all output to a file, you can do so with the RMAN utility as follows:

$ rman target / | tee rman.log

Any output from commands you run while connected to RMAN are displayed onscreen and captured in the log file.

 Tip  The tee command is similar to a plumbing t-splitter pipe in that it allows one input stream to be split into separate output pipes.

2-12. Recording All Shell Command Output

Problem

You’re performing a database upgrade and you need to run several SQL scripts. You want to record the output of everything printed to your screen to a log file.

Solution

The script command enables the recording of all output printed to your screen to also be written to an OS file. This example writes all output to a file named upgrade.log:

$ script upgrade.log
Script started, file is upgrade.log

From this point on, all output printed to your terminal is also recorded in the script upgrade.log. To end the script logging session, press Ctrl+D or type exit. You should see a message similar to this:

Script done, file is upgrade.log

How It Works

The script command is invaluable when you need to capture all output being printed to the terminal. This command stores all output in an OS file that can later be used to verify the tasks performed and whether the operations succeeded or failed.

If you don’t specify a file name when using script, a default file with the name of typescript is created. If you need to append to an already existing file, use script with the -a option, as shown here:

$ script -a upgrade.log

This code enables the capture of all output from all scripts that are being run. It can be extremely useful when you have a mix of OS commands and database commands and want to capture the output of every operation.

DBAs use the script command when the person who developed upgrade scripts (DBA #1) is passing the scripts to a different team member (DBA #2) to have those scripts executed in another database environment. In this scenario, DBA #2 will start a script job, run the upgrade scripts, end the script logging, and send the generated log file to DBA #1 for verification purposes.

2-13. Changing the Login Shell

Problem

Your system administrator has set your OS account to use the Korn shell, and you want to change it to the Bash shell.

Solution

Use the chsh (change shell) command to change the default login shell. You can specify the desired login shell directly with the -s option. In this example, the default shell for the oracle user is changed to the Bash shell:

$ chsh -s /bin/bash
Changing shell for oracle.
Password:

After successfully entering the password, a message “shell changed” is displayed. Now when you subsequently log on to the oracle account, the default shell will be the Bash shell.

How It Works

You might want to change your shell if the default is something other than your preferred shell. We recommend that you use the Bash shell on Linux/Solaris systems. The Bash shell is very robust and incorporates most of the useful features from the Bourne shell, Korn shell, and C shell.

The valid shells available on your server are stored in the /etc/shells file. You can use the chsh -l command or the cat command to view the contents of the /etc/shells file. This example uses the cat command to display the available shells:

$ cat /etc/shells
/bin/sh
/bin/bash
/bin/tcsh
/bin/csh
/bin/ksh

Table 2-5 describes the various shells available in Linux/Solaris environments.

Table 2-5. Descriptions of Popular Shells Available

Shell	Description
bsh or sh	The original Bourne shell written by Steve Bourne.
bash	The Bourne Again shell is considered to be one of the most robust shells.
ash	The Almquist shell has a smaller footprint than the Bash shell and is useful on systems in which disk space is limited.
ksh	The Korn shell is popular among DBAs on many varieties of UNIX.
pdksh	This shell is a public domain implementation of the Korn shell.
tcsh	The TENEX C shell is an enhanced version of the C shell.
csh	The C shell is like the C programming language and is popular on many older variants of UNIX.

You can verify what your default login shell has been set to by viewing the contents of the /etc/passwd file. For example, to view the default shell for the oracle user, use the cat and grep commands:

$ cat /etc/passwd | grep -i oracle
oracle:x:2000:500:sb-db1 Oracle Account:/orahome/oracle:/bin/bash

 Note  You do not need any special privileges or the assistance of your system administrator to use the chsh command.

2-14. Modifying Command Path Search

Problem

You need your shell to automatically find executable programs that aren’t currently in a directory included in your PATH variable. You want to add directories to your PATH variable.

Solution

You can manually add a directory to your PATH variable by using the export command. The following example prepends the /orahome/app/oracle directory to the current contents of the PATH variable:

$ export PATH=/orahome/app/oracle:$PATH

In the previous line of code, the PATH variable is defined to be the directory containing the Oracle binaries plus the contents of PATH directory. You separate directories in the PATH variable with a colon (:).

If you want to have a directory added to your PATH variable every time you log on to your database server, place the export command in a special startup file (see recipe 2-5 for details).

How It Works

The export command allows you to define variables that will be available to subsequently executed commands. You can add directories to the PATH variable by exporting it. To verify the contents of your PATH variable, you can use the echo command:

$ echo $PATH

Occasionally, you might encounter an Oracle RMAN issue when the directory /usr/X11R6/bin appears in the PATH variable before the ORACLE_HOME/bin directory. You see an error message when you try to start rman:

$ rman target /
rman: can’t open target

You received this error because there is an rman executable in the /usr/X11R6/bin directory that has nothing to do with the Oracle rman backup and recovery utility. In this scenario, you have to modify your PATH variable to ensure that ORACLE_HOME/bin comes before /usr/X11R6/bin:

$ export PATH=$ORACLE_HOME/bin:$PATH

The other alternative is to rename the rman executable in /usr/X11R6/bin to something like rman.X11.

 Tip  See MOS note 101050.1 for issues regarding the PATH variable and the rman executable.

CURRENT WORKING DIRECTORY AND PATH

To set your path to include the current working directory, simply use this:

$ export PATH=$PATH:.

Security experts recommend against having the current working directory referenced near the beginning of a PATH variable because a malicious person could place a program in a directory that does bad things to your system. For example, somebody could place a script named ls in a user’s home directory and have commands within the ls script delete files recursively. If the bad ls script is referenced before the ls command in /bin or /usr/bin, the results could be ugly.

2-15. Viewing Built-in Commands

Problem

You wonder whether you’re running a built-in version of a shell command or using the binary program located in the /bin directory.

Solution

Use the help command to view all built-in commands. For example, if you type help with no arguments, all built-in commands display:

$ help

Here is a partial listing of the output:

alias [-p] [name[=value] ... ]     bg [job_spec ...]
 bind [-lpvsPVS] [-m keymap] [-f fi break [n]
 builtin [shell-builtin [arg ...]]  caller [EXPR]
 case WORD in [PATTERN [| PATTERN]. cd [-L|-P] [dir]
 command [-pVv] command [arg ...]   compgen [-abcdefgjksuv] [-o option
 complete [-abcdefgjksuv] [-pr] [-o continue [n]

You can also use the type command to determine whether a program is built in. Use the -a option of type to print all locations in the PATH variable that include a command (including all aliases and functions). The following example shows that there is a built-in pwd command and also a pwd program in the /bin directory:

$ type -a pwd
pwd is a shell builtin
pwd is /bin/pwd

 Note  You might wonder why Linux/Solaris provides both a built-in command and a program for pwd. Because some shells might not have a built-in command for pwd, a program is explicitly provided in the /bin or /usr/bin directory.

How It Works

Some commands are built in to the Bash shell. Built in simply means that the shell contains its own version of the command. Therefore, if you run a built-in command such as pwd, the Bash shell runs its version of the pwd command instead of the executable program located in the /bin or /usr/bin directory. If you want to explicitly run the pwd command in the /bin directory, specify the complete path and file name. This example shows how to run the pwd program located in the /bin directory:

$ /bin/pwd

In some situations, it’s important to know whether a command is built in to the shell. Built-in commands execute faster than their counterparts in the /bin or /usr/bin directory because there is no overhead of looking in directories for the command and then loading the command into memory for execution.

Additionally, there are code portability issues to consider. Built-in commands typically have the same behavior from one OS to another, whereas the commands in the /bin and /usr/bin directories might behave differently between different ports of Linux/Solaris (especially older versions).

Built-in commands don’t start a separate process (sometimes called forked, child, or spawned) when they are executed. This is a requirement of some commands such as cd because a child process can’t modify its parent process. If cd were executed as a child process, it couldn’t modify the current working directory of the parent process and therefore wouldn’t be of much use. For this reason, the Bash shell contains its own executable code for the cd command. Table 2-6 describes some of the more commonly used Bash built-in commands.

Table 2-6. Commonly Used Bash Built-in Commands

Bash Built-in Command	Description
cd	Changes directories
echo	Displays strings and the contents of variables
help	Displays help on Bash built-in commands
history	Shows recently run commands
pwd	Prints the current working directory
ulimit	Sets and displays various system resource limits imposed on the shell

You can instruct the Bash shell to explicitly run a built-in command with the builtin command, which can be useful when porting scripts between different versions of Linux/Solaris.

The builtin command runs the built-in version of the command, even if there is an alias declared for it. This example runs the pwd built-in command:

$ builtin pwd

Likewise, you can explicitly instruct Bash to run the Linux/Solaris program version of a command, even if there is a built-in command or alias defined with the same name. This example runs the program version of the pwd command located in the /bin directory:

$ command pwd

You can also use the enable command to enable or disable the use of a built-in command. This example uses enable -n to disable the pwd built-in command:

$ enable -n pwd
$ type pwd
pwd is /bin/pwd

This example re-enables the pwd command:

$ enable pwd
$ type pwd
pwd is a shell builtin

 Tip  You can also use the man bash command to view all built-in commands. When viewing the man page, search for “shell builtin” string.

2-16. Setting the Backspace Key

Problem

You entered a command incorrectly and you want to backspace over previously entered characters so that you can correct them. When you attempt to use the Backspace key, it shows the ^? characters instead of deleting the previously entered characters.

Solution

Use the stty command to set your Backspace key to delete characters properly. The following bit of code sets the Backspace key to delete characters:

$ stty erase Ctrl+Backspace

In the previous command, ensure that you press the Ctrl and Backspace keys at the same time. This might vary depending on your system; for example, the following is an alternative way of setting the Backspace key:

$ stty erase <Backspace>

In the previous line of code, don’t type the word Backspace; instead, press the Backspace key.

How It Works

The stty command is used to view and change terminal settings. To view current stty settings, issue the following command:

$ stty -a
speed 38400 baud; rows 36; columns 97; line = 0;
intr = ^C; quit = ^\; erase = ^?; kill = ^U; eof = ^D; eol = <undef>; eol2 = <undef>;
...

When you work with older versions of Linux or Solaris, you might be logged on to a server when the Backspace key doesn’t work. In such situations, you have to manually set the Backspace key to function correctly. If you want the Backspace key to be automatically set every time you log on to the server, place the stty command in a special startup file (see recipe 2-5 for details).

 Note  On some older Linux or Solaris systems, you might need to use the stty erase ^H command or the stty erase ^? command to set the Backspace key.

2-17. Typing a Long Command in Multiple Lines

Problem

You want to type a long command, but it doesn’t fit nicely on one line.

Solution

To extend a line of code to the next line, use the backslash (\) character, followed by pressing Enter or Return. The following example shows how to break a long rman connection command over several lines:

$ /usr/oracle/product/10.2.0/db_1/bin/rman \
>  target / \
>  catalog rman/rman@db11g_bllnx1 \
>  cmdfile=/home/oracle/scripts/rmancheck.rmn \
>  msglog=/home/oracle/rmanlog/rmancheck01.log append

Although you could have typed the previous command on one line, consider breaking a command up into multiple lines when you have a limited terminal width.

How It Works

Sometimes it is desirable to extend a command across multiple lines for readability reasons. When you type \ followed by pressing Enter or Return, the next line will be preceded by the > character. This character indicates that the command has been extended to the next line. Both the backslash and the new line marker are ignored when this technique is used.

It doesn’t matter which location within the command the backslash is placed; you can break a command over as many lines as necessary.

CHAPTER 3

Managing Processes and Users

Diagnosing and resolving availability and performance issues is a key part of every DBA’s job. When troubleshooting problems, DBAs often start by identifying the processes that are currently running and identifying details about users logged on to the server. It is critical to know how to extract process and user activity from the OS.

When you work with OS users and processes, some of your tasks require root privileges. For example, when you install database software on the server, one of the first steps is to create an OS user and group. Depending on your environment, you might not have a system administrator (SA) to perform these operations. In this scenario, you have to log on to the server with root privileges and perform these tasks yourself. Even if you do have an SA, you have to adequately document and communicate with your SA about the database setup tasks that require root privileges. Therefore, it’s important to know which database tasks require root privileges and the commands to execute them.

This chapter starts by showing commands that get information about processes and users. It then progresses to examples of how to access the root account. The chapter wraps up with common database installation tasks that require root privileges, such as adding OS users and groups.

3-1. Listing Processes

Problem

You want to view processes currently running on the database server.

Solution

To view process information, use the ps (process status) utility. If you type the ps command without any parameters, you see all processes that have been started by the user you’re currently logged on as:

$ ps
PID   TTY      TIME CMD
12620 pts/1    00:00:00 bash
14103 pts/1    00:00:00 ps

If you want to view all processes running on a box, use the -e and -f options to show every process in a full output format:

$ ps -ef

If many processes are running on a box, it is useful to pipe the output of ps to grep and display only a particular user or process name. For example, to determine which Oracle instances are running on the server, you can filter the output to display only Oracle system monitor (SMON) background processes executing. In this example, ps and grep are used to show any processes that contain the string smon (the grep -v grep removes grep from the output):

$ ps -ef | grep -i smon | grep -v grep
oracle    7994     1  0 Jan25 ?        00:01:43 ora_smon_TRG
oracle   28035     1  0 Feb23 ?        00:00:50 ora_smon_ORA12CR1

The first column indicates that oracle is the OS owner of the processes. The second column contains the process identifier. The fifth column shows that one Oracle instance was started on January 25th, and the other was started on February 23rd. The seventh column indicates that the TRG database SMON process has consumed 1 hour and 43 minutes of CPU time, and the ORA12CR1 database SMON process has consumed 50 minutes. The last column is the name of the process (and the target of the grep command). There are two databases running on this server, as evidenced by two Oracle SMON background processes.

How It Works

Every time you run a Linux/Solaris command, a process is automatically created for you. Each process is assigned a unique number called a process identifier (PID). DBAs use the ps utility for a couple of important reasons:

• Checking on background processes
• Identifying hung or runaway processes that need to be killed
• Troubleshooting performance issues

When there are database connectivity issues, the ps command is useful to quickly identify whether a required database background processes is running. To list all processes for a specific user, use the -u (user) option and specify a username. This example lists all processes running under the oracle user with a full listing of the output:

$ ps -fu oracle

Here’s a snippet of the output:

UID       PID   PPID  C  STIME TTY     TIME     CMD
oracle    7964     1  0  Jan25 ?       00:03:54 ora_pmon_TRG
oracle    7966     1  0  Jan25 ?       00:11:35 ora_psp0_TRG
oracle    7968     1  0  Jan25 ?       08:55:51 ora_vktm_TRG
oracle    7972     1  0  Jan25 ?       00:02:23 ora_gen0_TRG
oracle    7974     1  0  Jan25 ?       00:01:41 ora_mman_TRG
...

When you diagnose database performance issues, it can sometimes be useful to get an overall count of the number of Oracle processes running on a server. Obtaining the overall count of Oracle processes is especially useful when trying to determine whether you have some sort of runaway process that is unnecessarily spawning SQL connections or parallel processes. This example pipes the output of ps to the wc (word count) command. The -l switch instructs wc to count the number of lines in the output:

$ ps -fu oracle | wc -l
84

This output indicates there are 84 Oracle processes running, which is within the normal range for a server running a database. Although there is no black-and-white rule of thumb to tell you how many processes should be running, if the process count is into the hundreds per database, you should investigate what program is starting the processes and determine whether it is normal for that database and application.

3-2. Terminating Processes

Problem

You’re running a database backup job, and you think the process is hung. You want to kill the process.

Solution

The OS PID can be used to terminate a process with the kill utility. In this example, ps is used to show the PID of an RMAN backup job that seems to be hung and needs to be terminated:

$ ps -ef | egrep ’rman|UID’ | egrep -v egrep

Here is some sample output:

UID        PID  PPID  C STIME TTY          TIME CMD
oracle    6822  6234  0 11:40 pts/0    00:00:00 rman target /

The PID is 6822 in this example. To terminate that process, issue a kill command, as shown here:

$ kill -9 6822

The -9 option sends a kill signal to the process, which causes it to terminate. You don’t see any output from the kill command because it unceremoniously removes the specified process. Ensure that you don’t kill the wrong Oracle process. If you accidentally kill a required Oracle background process, your instance will abort.

 Note  To see a list of all available types of kill signals, use the kill -l command.

How It Works

To run the kill command, you either have to own the process or have root privileges. Sometimes it is necessary to use the kill command to terminate unresponsive database processes. For example, you might sometimes need to kill a long-running or hung Oracle process (e.g., RMAN, SQL*Plus, and so on). If you know the process name, you can use the ps command to identify the PID (as shown in the solution section of this recipe).

In some situations, you might not know the OS process name. In these scenarios, you can identify the PID by querying data dictionary views, as shown here:

ACCEPT active DEFAULT ’y’ PROMPT ’Active only processes y/n? [y is default]: ’
SET LINES 200 PAGES 0 HEAD OFF LONG 100000
COL dummy_value NOPRINT
--
SELECT ’dummy_value’ dummy_value,
  ’USERNAME    : ’ || s.username     || CHR(10) ||
  ’SCHEMA      : ’ || s.schemaname   || CHR(10) ||
  ’OSUSER      : ’ || s.osuser       || CHR(10) ||
  ’MODULE      : ’ || s.program      || CHR(10) ||
  ’ACTION      : ’ || s.schemaname   || CHR(10) ||
  ’CLIENT INFO : ’ || s.osuser       || CHR(10) ||
  ’PROGRAM     : ’ || s.program      || CHR(10) ||
  ’SPID        : ’ || p.spid         || CHR(10) ||
  ’SID         : ’ || s.sid          || CHR(10) ||
  ’SERIAL#     : ’ || s.serial#      || CHR(10) ||
  ’KILL STRING : ’ || ’’’’ || s.sid || ’,’ || s.serial# || ’’’’  || CHR(10) ||
  ’MACHINE     : ’ || s.machine      || CHR(10) ||
  ’TYPE        : ’ || s.type         || CHR(10) ||
  ’TERMINAL    : ’ || s.terminal     || CHR(10) ||
  ’CPU         : ’ || q.cpu_time/1000000     || CHR(10) ||
  ’ELAPSED_TIME: ’ || q.elapsed_time/1000000 || CHR(10) ||
  ’BUFFER_GETS : ’ || q.buffer_gets  || CHR(10) ||
  ’SQL_ID      : ’ || q.sql_id       || CHR(10) ||
  ’CHILD_NUM   : ’ || q.child_number || CHR(10) ||
  ’START_TIME  : ’ || TO_CHAR(s.sql_exec_start,’dd-mon-yy hh24:mi’) || CHR(10) ||
  ’STATUS      : ’ || s.status       || CHR(10) ||
  ’SQL_TEXT    : ’ || q.sql_fulltext
FROM            v$session s
JOIN            v$process p ON (s.paddr  = p.addr)
LEFT OUTER JOIN v$sql     q ON (s.sql_id = q.sql_id)
WHERE s.username IS NOT NULL -- eliminates background procs
AND NVL(q.sql_text,’x’) NOT LIKE ’%dummy_value%’ -- eliminates this query from output
AND s.status != DECODE(’&&active’,’n’,’xyz’,’N’,’xyz’,’INACTIVE’)
ORDER BY q.cpu_time;

You’ll be prompted about whether you want to see only active sessions displayed in the output:

Active only processes y/n? [y is default]:

Press Enter; here’s some sample output from the previous query:

USERNAME    : SYS
SCHEMA      : SYS
OSUSER      : oracle
MODULE      : rman@cs-xvm (TNS V1-V3)
ACTION      : SYS
CLIENT INFO : oracle
PROGRAM     : rman@cs-xvm (TNS V1-V3)
SPID        : 9458
SID         : 102
SERIAL#     : 49521
KILL STRING : ’102,49521’
MACHINE     : cs-xvm
TYPE        : USER
TERMINAL    : pts/0
CPU          :
ELAPSED_TIME :
BUFFER_GETS  :
SQL_ID       :
CHILD_NUM    :
START_TIME   :
STATUS       : ACTIVE
SQL_TEXT     :

From the name of the program in the prior output, you can tell this is an RMAN process executing. The SPID column in the output is the OS PID. Once the SPID is identified, the kill command can be used to terminate the process:

$ kill -9 9458

 Caution  In some rare situations, killing an Oracle process associated with a SQL transaction can have an adverse impact on the stability of the instance. For example, killing a process participating in a distributed transaction might cause the instance to crash. To determine whether this is an issue for the version of the database you’re using, see MOS bug IDs 8686128 and 12961905. In older versions of Oracle, various other bugs associated with killing a process have been identified and fixed and are documented in MOS bug IDs 5929055 and 6865378.

You can also kill a database connection with the SQL*Plus alter system kill session command by using the session ID (SID) and serial number. Here is the general syntax:

alter system kill session ’integer1, integer2 [,integer3]’ [immediate];

In this syntax statement, integer1 is the value of the SID column, and integer2 is the value from the SERIAL# column (of V$SESSION). In an RAC environment, you can optionally specify the value of the instance ID for integer3. The instance ID can be retrieved from the GV$SESSION view.

As a DBA privileged user, the following command kills the database connection that has an SID of 102 and a serial number of 49521:

SQL> alter system kill session ’102,49521’

If successful, you should see this output:

System altered.

When you kill a session, the session is marked as terminated, active transactions (within the session) are rolled back, and any locks (held by the session) are released. The session will stay in a terminated state until any dependent transactions are rolled back. If it takes a minute or more to roll back the transaction, Oracle reports the session as “marked to be terminated” and returns control to the SQL prompt. Suppose that you specify IMMEDIATE:

SQL> alter system kill session ’102,49521’ immediate;

Oracle will roll back any active transactions and immediately return control back to you.

 Caution  Killing a session that is executing a select statement is fairly harmless. However, if you terminate a session that is performing a large insert/update/delete, you might see a great deal of database activity (including I/O in the online redo logs) associated with Oracle rolling back the terminated transaction.

3-3. Listing the Users Logged On

Problem

You are experiencing performance problems with your database server. To help diagnose the issues, you first want to view all users currently logged on to the box.

Solution

Use the who command to display the users logged on to a box:

$ who

The output consists of four columns: users logged on, terminal name, time they logged on, and location where they logged on. Here’s a typical listing of the who command:

ptownshend    pts/1        Jun 15 14:17 (vpn-229-150-36-51.com)
rdaltrey      pts/2        Aug 10 22:11 (122.120.44.181)
jentwistle    pts/3        Aug 16 03:14 (111.155.23.114)
kmoon         pts/4        Sep 4  01:23  (10.6.77.121)
kjones        pts/6        Dec 4  06:66  (101.120.23.171)

You can also use the who command with the am i option to display your current user information:

$ who am i
oracle   pts/2        Aug 4 15:30 (vpn-109-150-32-93.brdstn.com)

 Tip  You can also use whoami or the id -un to display information about your current user. Contrast this with the who am i command, which always shows which user you initially used to log on to a server. For example, if you su to a different user, whoami displays your current user status, whereas who am i shows which user you originally logged on to the server as.

How It Works

The who command is important for listing a snapshot of users logged on to the server. An alternative to the who command is the w utility. This simply titled but powerful tool is an extension to the who command. The output of the w command is like a combination of the listings from the who, uptime, and ps -a commands. This example uses the w command to eavesdrop on who is logged on to the system and what they are doing:

$ w
17:59:54 up 9 days,  5:37,  4 users,  load average: 0.00, 0.00, 0.00
USER     TTY      FROM              LOGIN@   IDLE   JCPU   PCPU    WHAT
enehcd   pts/1    vpn-128-156-33-6  12:32    5:46   0.12s  0.12s  -bash
evork    pts/2    vpn-129-156-33-6  15:22   34:14   0.01s  0.01s -chmod
aznoga   pts/3    vpn-129-156-32-6  17:22   55:24   0.03s  0.01s -sleep
wroot    pts/4    vpn-129-150-150-  17:48   0.00s   0.02s  0.00s      w

The first line of the w output is similar to that produced by the uptime command; it shows current time, how long the system has been up, number of users, and system load averages. After the header line, it displays users logged on, from where and what time, how long they’ve been idle, current job CPU (JCPU), foreground process CPU (PCPU), and what command the user is running.

To specifically look at one user, specify the process name as an option. The following command looks at all oracle accounts logged on to the server:

$ w oracle

The following output indicates that there are two active oracle users on the box:

14:14:58 up 130 days, 21:52,  2 users,  load average: 0.00, 0.00, 0.00
USER     TTY      FROM              LOGIN@   IDLE   JCPU   PCPU   WHAT
oracle   pts/1    63-231-82-100.hl   13:10   0.00s  0.03s  0.00s  w oracle
oracle   pts/2    63-231-82-100.hl   14:14   6.00s  0.01s  0.01s  -bash

If a user has logged on twice to a server (as in the previous output), you can use the tty command to identify a session. Log on to one of the oracle sessions; here is the tty command and its output:

$ tty
/dev/pts/1

You can also use the finger command to display information about users logged on to a server. If you don’t provide the finger command with a username, it will display all users on a system:

$ finger
Login      Name        Tty        Idle  Login  Time   Office     Office Phone
oracle                 pts/0            Jun 20 13:29  (br-ea-fw-nat.surg.com)
oracle                 pts/1         2  Jun 20 13:29  (br-ea-fw-nat.surg.com)

The pinky command is a lightweight version of the finger command. If no users are specified as a parameter, all users logged on will display:

$ pinky
Login      Name        TTY      Idle   When          Where
oracle                 pts/0           Jun 20 13:29  br-ea-fw-nat.surg.com
oracle                 pts/1    00:03  Jun 20 13:29  br-ea-fw-nat.surg.com

3-4. Listing the Last Logon Time of a User

Problem

You think that somebody might have hacked into the database OS account, so you want to see when the last time the oracle account was logged on to the database server.

Solution

Use the last command to determine a user’s last logon time. This bit of code determines the last time the oracle account logged on to the database server:

$ last oracle
oracle   pts/1       63-227-41-191.hl Fri Dec 21 17:18   still  logged in
oracle   pts/1       63-227-41-191.hl Mon Dec 17 15:55 - 17:53  (01:58)
oracle   pts/1       63-227-41-191.hl Mon Dec 17 11:55 - 13:33  (01:38)
oracle   pts/1       63-227-41-191.hl Mon Dec 17 08:28 - 10:45  (02:17)
oracle   pts/0       63-227-41-191.hl Sat Dec 15 20:59 - 22:00  (01:00)
oracle   pts/0       63-227-41-191.hl Sat Dec 15 20:43 - 20:59  (00:15)

The output indicates that the oracle user is currently logged on and has logged on several times in the month of December.

How It Works

If you use the last command without any arguments, it displays the last logon time of all users. You can pipe the output of last to the less command to display one page at a time:

$ last | less

You can also limit the output of last by piping its output to the head command. The following will display the last ten users logged on:

$ last | head

 Tip  On Linux servers, the last command retrieves its information from the /var/log/wtmp file, which records all logons and logouts on the server. On Solaris servers, the /var/adm/wtmpx file is used to record logons and logouts.

Another useful command in regard to last logons is the lastb utility. This useful command displays a list of recent bad logon attempts. When first using lastb, you might see the following output:

lastb: /var/log/btmp: No such file or directory
Perhaps this file was removed by the operator to prevent logging lastb info.

The previous message means that the /var/log/btmp file needs to be created. You can enable lastb by running the following commands as root (# indicates that the command is run as root):

# touch /var/log/btmp
# chown --reference=/var/log/wtmp /var/log/btmp
# chmod --reference=/var/log/wtmp /var/log/btmp

3-5. Limiting the Number of User Processes

Problem

For security purposes, your database installation instructions indicate that you need to limit the number of processes that can be started by the database OS account. By limiting the number of processes a user can start, you ensure that no single user can consume inordinate amounts of resources on the server.

Solution

On a Linux system, as the root user, add an entry in the /etc/security/limits.conf file that restricts the number of concurrently running processes for an OS account. The following lines of code (in the limits.conf file) establish a soft limit of 2,047 and impose a hard limit of 16,384 processes for the oracle OS user:

oracle        soft    nproc            2047
oracle        hard    nproc            16384

A soft limit enforces a maximum of 2,047 processes that can be running simultaneously by the oracle OS user. The oracle user can manually override the soft limit (with the ulimit command) and increase the limit on the number of processes up to 16,384 processes. Only the root user can modify the hard limit to be higher.

 Tip  See Chapter 9 for full details on configuring oracle shell limits.

To limit the number of processes on a Solaris system, set the maxusers parameter in the /etc/system file. This parameter doesn’t set a hard limit for the number of processes; instead, it is used by Solaris to derive the overall number of processes allowed. The formula Solaris uses to calculate the maximum number of processes varies by release.

How It Works

For performance and security reasons, you might want to impose a limit on the number of processes allowed on a server. If you’re feeling brave, you can test the maximum number of processes allowed by running this function:

: () { :|:& };:

The previous bit of code creates a : function that recursively calls itself and puts its processes in the background. Be warned that you can lock up your system if you don’t properly have process limits in place. This type of program is known as a fork bomb. It is appropriately named because it continuously creates (forks) new processes on the system. Because the program starts itself in the background, it cannot be stopped by pressing Ctrl+C.

3-6. Viewing How Long the Server Has Been Running

Problem

You want to know how long the server has been running.

Solution

Use the uptime command to determine how long your database server has been running:

$ uptime
08:37:00 up 33 days, 16:14, 1 user, load average: 0.00, 0.00, 0.00

This output shows that this server has been up for a little more than 33 days.

How It Works

When resolving database availability issues, it is sometimes helpful to know when the server was last rebooted because (not surprisingly) there is a direct correlation between the server being down and the database being unavailable. Viewing the server uptime can help determine whether the database was unavailable because of a system reboot.

Interestingly, the output of uptime is identical to the first line of the w command. The next example shows running the w command and the corresponding output:

$ w
 08:37:01 up 33 days, 16:14,  1 user,  load average: 0.00, 0.00, 0.00
USER     TTY      FROM              LOGIN@   IDLE   JCPU   PCPU  WHAT
oracle   pts/0    63-227-41-191.hl  07:11    0.00s  0.06s  0.00s w

In the first line of the output, 08:37:01 is the current time on the server. Also displayed in the first line are load averages for the past 1, 5, and 15 minutes.

3-7. Viewing How Long a Process Has Been Running

Problem

You wonder how long the Oracle database background processes have been running.

Solution

Use the ps -ef command to determine how long a process has been running. This line code uses ps with the egrep command to find out when the Oracle system monitor process was started:

$ ps -ef | egrep ’smon|UID’ | egrep -v egrep
UID        PID  PPID  C STIME TTY          TIME  CMD
Oracle    8843     1  0 Oct31 ?        00:02:33  ora_smon_RMDB1

In this command, the process status output is filtered by egrep to list any lines that contain either smon or UID. When you filter also for the UID string, the header of the ps command displays. In the output, the SMON process was started on October 31 and has consumed 2 hours and 33 minutes of CPU on the database server.

How It Works

When troubleshooting database problems, you might want to know when your database was last started. For example, you might have had an unexpected server reboot, which caused the databases to stop and restart. You can verify the time your database process was started by using the ps command.

The following is another slight variation of how DBAs use the process status command:

$ ps -ef | grep smon | grep -v grep
oracle    8843     1  0 Oct31 ?        00:02:33 ora_smon_RMDB1

This command limits the output to just the specific process that you’re interested in observing. The grep -v grep code strips out any lines containing the string grep from the output.

3-8. Displaying Your Username

Problem

You want to display your OS username.

Solution

Use the id command to display the OS account you’re current logged on as:

$ id

Listed next is a sample of output. By default, both the user and group information are displayed:

uid=56689(oracle) gid=500(oinstall) groups=500(oinstall),501(dba)

 Tip  Use the groups command if you just want to display the groups of your current OS account.

How It Works

An effective DBA has to be good at multitasking. You’ll find yourself logged on to multiple boxes in a myriad of development, test, and production environments. It is easy to lose your bearings; when you do, you’ll want to identify the OS account you’re currently using. You can instinctively fulfill this need with the id command.

You can also use the who command with the am i option to display the user you used to log on to the box:

$ who am i
oracle   pts/2        Aug 4 15:30 (vpn-109-150-32-93.brdstn.com)

You can also use whoami or id -un to display information about your current user; the who am i command shows information about the user you used to originally log on to the box. For example, if you initially log on to a box as oracle and then switch to the root user, the whoami command will display root, whereas the who am i command will display oracle:

# who am i
oracle   pts/2        Jun 20 14:20 (br-ea-fw-nat.surg.com)
# whoami
root
# id -un
root

3-9. Changing Your Password

Problem

You have just been handed a new database server and want to change your password to something more secure than changeme.

Solution

Use the passwd command to change your password. Log on to the user account you want to change the password for and type passwd with no options:

$ passwd

After you type the passwd command, you are prompted to type the current password and then to enter the new password twice:

Changing password for user oracle.
Changing password for oracle
(current) UNIX password:
New UNIX password:
Retype new UNIX password:
passwd: all authentication tokens updated successfully.

How It Works

As a DBA, you manage passwords for various OS accounts on the database servers that you log on to. Use the passwd command to ensure that your database OS user is secure and available for your use.

If you have access to the root user, you can change passwords for other users. This example changes the password for the oracle OS user:

# passwd oracle

You are then prompted to type a new password for the oracle OS user:

Changing password for user oracle.
New UNIX password:
Retype new UNIX password:
passwd: all authentication tokens updated successfully.

To set up additional password security, use the chage program. This utility can be used to set the maximum amount of time that a password is valid. This example is run as the root user specifies that the oracle user will have to change its password after 60 days:

# chage -M 60 oracle

To verify the changes to the oracle user, use the -l option:

# chage -l oracle
Last password change                                    : Mar 08, 2015
Password expires                                        : May 07, 2015
Password inactive                                       : never
Account expires                                         : never
Minimum number of days between password change          : -1
Maximum number of days between password change          : 60
Number of days of warning before password expires       : -1

The chage utility is part of the Shadow Password Suite. This suite includes programs such as chfn, chpasswd, chsh, dpasswd, expiry, faillog, and so on. You can add this group of programs to your system to provide extra security. These utilities allow you to encrypt user passwords in the /etc/shadow file, which is readable only with root privileges.

3-10. Becoming the System Privileged (root) User

Problem

You’re installing Oracle on a database server, and an SA is not available. You need to become root (sometimes referred to as the superuser) so that you can add new OS groups and user accounts.

Solution

You need the root user password for this recipe to work. Use the su - command to switch to the root account. The - (hyphen) option specifies that the target user’s login shell will be invoked (it runs all login scripts of the target user):

$ su - root

Or you can simply do this:

$ su -

You should now see a prompt similar to this:

Password:

After a successful logon, your shell prompt will change to the # character, indicating that you are now logged on as root:

#

You can now perform such tasks as adding the groups and users required for the database installation. To exit from the root user, type the following:

# exit

 Note  You can also press Ctrl+D to exit a shell login.

How It Works

Some database installation tasks require root privileges. Sometimes an SA isn’t available to perform these tasks, or your SA might be comfortable with temporarily providing you access with the root account. However, competent SAs rarely give out the root password to DBAs (or any non-SA users, for that matter). There are several problems with providing carte blanche superuser access:

• It is hard to trace who did what on the system.
• Security is better administered by granting privileges on an “as-needed” basis, not through wide-open system access.

If several people have direct access to the root password, and one of them logs on to the system and accidentally removes critical files, it is hard to tell who did the damaging deed. For this reason, SAs do not usually provide direct root access. Instead, they provide auditable and/or limited access to root privileges through the sudo command (see recipe 3-11 for details).

3-11. Running Commands as the root User

Problem

You want access to commands that can be run only by root, but your security-conscious SA won’t give you the root password.

Solution

Have your SA insert the appropriate entries in the /etc/sudoers file to grant access to restricted commands. You can be granted complete root access or access to only specified commands.

As the root user, use the visudo command to add an entry to the /etc/sudoers file. The visudo command ensures that the /etc/sudoers file is edited in a secure fashion. When using visudo, the sudoers file is locked to disallow multiple users from simultaneously editing the file. The visudo command also performs a syntax check on any edits to the /etc/sudoers file and saves only correct entries.

For example, the SA can provide root access to the oracle account by adding the following line to the /etc/sudoers file:

oracle ALL=(ALL) ALL

The oracle account can use sudo to execute commands (that would otherwise be required to run as the root user). For example, the groupadd command can now be run by oracle as follows:

$ sudo /usr/sbin/groupadd dba

The first time you run sudo, you are prompted for your password (not the root password). For a short period of time (5 minutes by default), you can run sudo without being prompted for your password again.

You can also specify that only certain commands can be run with root privileges. For example, if your SA wanted to limit the oracle account to the commands that add groups and users, the /etc/sudoers entry would be as follows:

oracle ALL=/usr/sbin/groupadd,/usr/sbin/useradd

This method allows your SA to limit access to specific commands that require root privileges. You can view the commands you are allowed to run as sudo with the following command:

$ sudo -l

The following output shows that this server has been configured to allow the oracle account to run the following commands:

User oracle may run the following commands on this host:
(root) /usr/sbin/groupadd, (root) /usr/sbin/useradd

How It Works

The sudo command allows a command to be executed as the root user. The sudo permissions are maintained in the /etc/sudoers file by the SA.

One compelling reason for using sudo is that it allows SAs to grant superuser access without having to give out the root password. This allows SAs to temporarily grant root access to consultants who might have short-term assignments and require root access. SAs can also quickly revoke root access by deleting the appropriate entry from the /etc/sudoers file without affecting other users.

Another advantage of using sudo is that it provides an audit trail. An entry is written to a system log file in the /var/log directory whenever a sudo command is issued. Additionally, you can specify a log file for sudo activity by placing the following line in the /etc/sudoers file:

Defaults logfile=/var/log/sudolog

After successfully adding the previous line to the /etc/sudoers file, all commands run as sudo are logged on to /var/log/sudolog.

3-12. Adding a Group

Problem

You’re performing a database installation and need to add an OS group.

Solution

Use the groupadd command to add OS groups. Typical Oracle installations require that you add two groups: oinstall and dba. If you have root access, you can run the groupadd command as shown here:

# groupadd oinstall
# groupadd dba

If you don’t have access to a root account, your SA has to run the commands in this recipe. Sometimes SAs are amenable to granting root access via the sudo command (see recipe 3-11 for details).

If you have a company requirement that a group be set up with the same group ID on different servers, use the -g option. This example explicitly sets the group ID to 505:

# groupadd -g 505 dba

Sometimes it’s desirable to consistently create groups with the same group ID across multiple servers. For example, in Network File System (NFS) environments, you might encounter permission problems unless the group is set up with the same group ID across multiple servers.

How It Works

Sometimes DBAs are required to perform system administration tasks such as adding (or deleting) users and adding (or deleting) groups because an SA might not be available in some environments. Or you might be installing database software on your workstation and you are the SA. Regardless of the situation, you should be familiar with these types of tasks so that you can build and maintain your database server.

You can verify that the group was added successfully by inspecting the contents of the /etc/group file. Here are the entries created in the /etc/group file after running the commands in the “Solution” section of this recipe:

oinstall:x:500:
dba:x:501:

If you need to remove a group, use the groupdel command (see recipe 3-13 for details). If you need to modify a group, use the groupmod command.

 Tip  On Linux systems, if you have access to an X Window terminal, you might want to investigate using the system-config-users utility. This graphical tool allows you to add and delete users and groups.

3-13. Removing a Group

Problem

You want to clean up a database server and remove an old dba OS group.

Solution

Use the groupdel command to remove OS groups. This command requires root access. The following command deletes the dba group:

# groupdel dba

If you don’t have access to a root account, your SA has to run the commands in this recipe. Sometimes SAs are willing to grant root access via the sudo command (see recipe 3-11 for details).

How It Works

You will not be prompted to confirm whether you really want to delete a group, so make certain you really want to delete a group before using the groupdel command. You can view the /etc/group file to verify that the group has been deleted.

 Note  You cannot remove a group that is a user’s primary group. You must first modify or delete the users who have the group to be deleted (so that the group isn’t the primary group of any user on the system).

3-14. Adding a User

Problem

You’re doing a database installation and need to create an OS user.

Solution

Use the useradd command to add OS users. This command requires root access. The following command creates an OS account named oracle, with the primary group being oinstall, and the dba group specified as a supplementary group:

# useradd -g oinstall -G dba oracle

If you don’t have access to a root account, your SA has to run the commands in this recipe. Sometimes SAs are agreeable to granting root access via the sudo command (see recipe 3-11 for details).

If you have a company requirement that a user be set up with the same user ID across multiple servers, use the -u option. This example explicitly sets the user ID to 500:

# useradd -u 500 -g oinstall -G dba

Sometimes it’s desirable to consistently create a user with the same user ID on different servers. For example, in NFS environments, you might encounter permission problems unless the user is set up with the same user ID on all servers.

How It Works

Occasionally as a DBA you will perform system administration–type tasks such as adding (or deleting) users and adding (or deleting) groups. In some environments, an SA might not be available. Or you might be installing database software on your workstation and you are the SA. Regardless of the situation, you should be familiar with these types of tasks so that you are better able to build and maintain your database server.

You can verify user account information by viewing the /etc/passwd file. Here is what you can expect to see after running the useradd command in the “Solution” section of this recipe:

oracle:x:500:500::/home/oracle:/bin/bash

During the process of creating a new user, the /etc/skel directory contains the files and directories that are automatically created for new users added to the server with the useradd command. Typical files included are .bashrc, .bash_profile, and .bash_logout. The location of the /etc/skel directory can be changed by modifying the value of SKEL in the /etc/default/useradd file.

The userdel command can be used to delete a user (see recipe 3-15 for details). Use the usermod command to modify existing accounts.

 Tip  If you have access to an X Window terminal, you might want to investigate using the system-config-users utility. This is a graphical tool that allows you to add and delete users and groups.

3-15. Removing a User

Problem

You want to clean up a server and remove an old oracle OS account.

Solution

Use the userdel command to remove an OS account. You need root privileges to run the userdel command. This example removes the oracle user from the server:

# userdel oracle

If you don’t have access to a root account, your SA has to run the commands in this recipe. Sometimes SAs are open to granting root access via the sudo command (see recipe 3-11 for details).

How It Works

You will not be prompted to confirm whether you really want to delete a user, so make certain you absolutely want to remove a user before using the userdel command. You can view the /etc/ passwd file to verify that the user has been deleted (by the absence of the user).

You can also instruct the userdel -r command to remove the user’s home directory and any files in that location. This example removes the oracle account and its home directory:

$ userdel oracle -r

Before you use this command, make sure the user doesn’t need any of the files in his or her home directory tree ever again. You can use a command such as tar to do a quick backup of the files in a user’s home directory (see Chapter 6 for details).