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

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

$ ps
   PID TTY         TIME CMD
 24975 pts/5       0:00 ps
 15127 pts/5       0:00 bash

If you want to view all processes running on a box, then use the -e and -f options to show every process in a full output format. If many processes are running on a box, it's useful to pipe the output of ps to grep and restrict the search for a particular user or process name. In this example, ps and grep are used to show any processes that contain the string smon:

$ ps -ef | grep -i smon
oracle  2950     1   0   May 04 ?          12:12 ora_smon_CHAYA
oracle  2952     1   0   May 04 ?          12:08 ora_smon_HEERA

The previous output shows that each Oracle system monitor (SMON) background process was started on May 4 and that each has consumed a little more than 12 hours of CPU time. The second column contains the process ID.

Every time you run a Linux 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:

When there are database connectivity issues, the ps command is useful for quickly identifying 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    7884     1  0 Apr15 ?        00:00:00 ora_pmon_RMDB1
oracle    7886     1  0 Apr15 ?        00:00:00 ora_psp0_RMDB1
oracle    7888     1  0 Apr15 ?        00:00:00 ora_mman_RMDB1

Similarly, if you know the process ID, you can view any associated processes by using the -p option with a PID. This example lists the full output of processes associated with the PID of 7884:

$ ps -fp 7884

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

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

$ ps -ef | grep rman | grep -v grep
oracle   22827 22793  0 14:42 pts/2    00:00:00 rman target /

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

$ kill −9 22827

The −9 option sends a kill signal to the process, which will cause it to terminate.

Oftentimes you will have more than one process running with the same name. For example, in this scenario two rman processes are running on the database server:

$ ps -ef | grep rman | grep -v grep
oracle   14626 14574  0 11:20 pts/4    00:00:01 rman target /
oracle   14717 14685  0 11:20 pts/5    00:00:01 rman target /

When you have multiple processes with the same name, how do you uniquely identify a session? In these scenarios, use the tty command to print the session's terminal information. In this next example, we temporarily exit the rman utility and run tty to identify the session's terminal:

RMAN> host;
tty
/dev/pts/5

You can now type exit to return to the rman prompt. The tty information in the previous example is /dev/pts/5. You can now tell from the prior ps output that this session has the process ID of 14717.

Sometimes it's necessary to use the kill command to terminate unresponsive user and database processes. To run the kill command, you either need to own the process or have root privileges. Be careful when using the kill command. If you accidentally nuke the wrong process, you could cause inadvertent downtime for your database application.

Sometimes in an Oracle environment you'll find yourself in situations where you need to kill a hung SQL process. To accomplish this, you need to first identify the PID by querying the data dictionary as shown here:

SELECT s.username "ORACLE USER"
      ,p.pid       "PROC PID"
      ,s.process   "SESS PID"
      ,s.osuser    "OS User"
      ,s.terminal  "Terminal"
      ,s.machine   "Machine"
      ,s.sid       "SESS ID"
      ,s.serial#   "SESS Serial#"
FROM v$process p
    ,v$session s
WHERE p.addr = s.paddr
/

Here is some sample output from the previous query:

ORACLE USER   PROC PID SESS PID     OS User  Terminal Machine   SESS ID SESS Serial#
SYS                 19 9478         oracle   pts/5    shrek2       1076         1304
HEERA               20 9515         oracle   pts/7    shrek2       1077         1649
HEERA               21 17239        oracle   pts/1    cns-dba      1073          440

The user's session process identifier (SESS PID) in the output maps to the operating system PID. There are two HEERA sessions in the previous output. To uniquely identify one session, while connected via a HEERA session, from the SQL*Plus prompt issue the ! SQL command to run the tty operating system command:

SQL> !tty
/dev/pts/7

To kill the SQL*Plus process being run by the HEERA schema identified by the previous command, issue the following from the operating system as the oracle user on the server that is running the SQL process:

$ kill −9 9515

You won't see any output from the kill command. It unceremoniously removes the specified process. However, the SQL*Plus session that gets killed should show output similar to this:

SQL> killed

You can also kill a SQL session with the SQL*Plus alter system kill session command by using the session ID and serial number. As a DBA, the following command will kill the SQL session that has the session ID of 1073 and the serial number of 440:

SQL> alter system kill session '1073,440';

Another useful operating system utility available for killing processes is killall. This tool allows you to stop all processes running a certain command. This can be a useful alternative to the kill command, which requires that you specify the PID you want to terminate. This can be most useful in a situation where you have a program that has malfunctioned and is spawning multiple processes. In this scenario, it may be difficult to track down all the specific PIDs in a timely manner.

The syntax for running killall is as follows:

$ killall [options] program_name

When used with no options, then all occurrences of the specified program name will be terminated. This next example terminates all instances of sqlplus running:

$ killall sqlplus

Use the -i option to be prompted with the process name and PID for confirmation before terminating the process. This allows you to selectively halt instances of a program:

$ killall -i sqlplus
Kill sqlplus(32402) ? (y/n)

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.

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

$ who

The output consists of four columns: users logged on, their terminal name, the time they logged on, and from 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)

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 shortly named (but powerful) tool is an extension to the who command. Its output 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 user has logged on twice to a server (like in the previous output), you can use the tty command to identify a session. While logged 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, then 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, then all users logged on will be displayed:

$ 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

You think that somebody may have hacked into the database operating system account. You want to see when the last time the oracle account was logged on to the Linux database server.

Use the last command to determine the last login time of a user. This next bit of code determines when 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.

If you use the last command without any arguments, then 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

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:

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

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

As the root user, add an entry in the /etc/security/limits.conf file that restricts the number of concurrently running processes for an operating system account. The following lines of code (in the limits.conf file) establishes a soft limit of 2047 and imposes a hard limit of 16,384 processes for the oracle operating system user:

oracle          soft    nproc           2047
oracle          hard    nproc           16384

A soft limit will enforce that at most 2,047 processes can be running simultaneously by the oracle operating system 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.

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 called : 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's 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.

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

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

From the output, this Linux server has been up for a little more than 33 days.

Sometimes when resolving database availability issues, it's helpful to know when the server was last rebooted. That's because (not surprisingly) there's 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.

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

Use the ps -ef command to determine how long a process has been running. This next line of 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 the previous 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, this displays the header of the ps command. 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.

Sometimes when troubleshooting database problems, it's useful to know when your database was last started. For example, you may 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

The previous command will limit 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.

You want to display your operating system username.

Use the id command to display the operating system 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)

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's easy to lose your bearings, and when you do, you'll then want to identify the operating system 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 who 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, whereas 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

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

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 will be 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.

As a DBA, you'll have to manage the passwords for various operating system accounts on the database servers that you log on to. Use the passwd command to ensure that your database operating system 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 operating system user:

# passwd oracle

You will then be prompted to type a new password for the oracle operating system 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 next 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
Minimum:        0
Maximum:        90
Warning:        7
Inactive:       −1
Last Change:            Sep 30, 2007
Password Expires:       Dec 29, 2007
Password Inactive:      Never
Account Expires:        Never

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. This shadow file is readable only with root privileges.

You're installing Oracle on a database server. You don't have a system administrator. You need to become root (sometimes referred to as the superuser) so that you can add new operating system groups and user accounts.

You have to access to 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 (runs all login scripts of the target user):

$ su - root

You should now see a prompt similar to this:

Password:

After a successful logon, your shell prompt will change to the # character (on most Linux systems) indicating 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

Some database installation tasks require root privileges. Sometimes there isn't an SA available to perform these tasks, or your system administrator might be comfortable with temporarily providing you access with the root account. However, competent system administrators 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:

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's hard to tell who did the damaging deed. For this reason, SAs do not usually provide direct root access. Rather, they provide auditable and/or limited access to root privileges through the sudo command (see recipe 3-11 for details).

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

Have your system administrator insert the appropriate entries in the /etc/sudoers file to grant access to restricted commands. You can be granted complete root access or can be granted 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 will also perform a syntax check on any edits to the /etc/sudoers file and will save only correct entries.

For example, your 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 will be prompted for your password (not the root password). For a short period of time (by default, five minutes), you will be able to 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 system administrator wanted to limit the oracle account to the commands that add groups and users, then 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

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 system administrator.

One compelling reason for using sudo is that it allows your SA to grant superuser access without having to give out the root password. This allows system administrators to temporarily grant root access to consultants who may 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 will be logged on to /var/log/sudolog.

You're performing a database installation and need to add an operating system group.

Use the groupadd command to add operating system groups. Typical Oracle installations require that you add two groups named 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, then you'll need to get your system administrator 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, then 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.

Sometimes as a DBA you will be required to perform system administration tasks such as adding (or deleting) users and adding (or deleting) groups. This is because in some environments, you may not have an SA available. Or you might be installing database software on your home Linux box (and you are the system administrator). Regardless of the situation, you should be familiar with these types of tasks so that you're able to 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 for any reason you need to remove a group, then use the groupdel command (see recipe 3-13 for details). If you need to modify a group, use the groupmod command.

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

Use the groupdel command to remove operating system groups. This command requires root access. The following command will delete the dba group:

# groupdel dba

If you don't have access to a root account, then you'll need to get your system administrator to run the commands in this recipe. Sometimes SAs are willing to granting root access via the sudo command (see recipe 3-11 for details).

You will not be prompted on 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.

You're doing a database installation and need to create an operating system user.

Use the useradd command to add operating system users. This command requires root access. The following command will create an operating system 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, then you'll need to get your system administrator 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 Network File System (NFS) environments, you might encounter permission problems unless the user is set up with the same user ID on all servers.

Sometimes as a DBA you will have to perform system administration type tasks such as adding (or deleting) users and adding (or deleting) groups. In some environments, you may not have an SA available. Or you might be installing database software on your home Linux box. Regardless of the situation, you should be familiar with these types of tasks so that you're 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

When creating a new user, the /etc/skel directory contains the files and directories that are automatically created for a 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 file 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.

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

Use the userdel command to remove an operating system 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, then you'll need to get your system administrator 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).

You will not be prompted on 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 the user's home directory tree ever again. You can use a command like tar to do a quick backup of the files in a user's home directory (see Chapter 6 for details).