System Processing

Inherent in every Oracle database is a considerable amount of system processing that takes place behind the scenes. Every database has a significant amount of overhead, much of which is composed of the background processes. Here are some of the most common background processes:

On a UNIX system, Oracle processes are typically named in the following format:

ora_process_SID

where process is the name of the Oracle process (such as pmon), and SID is the name of the system identifier for the associated database instance.

Monitoring Processes

Oracle processes can be monitored at both the operating system level and the database level. On a UNIX platform, you can list the Oracle processes with the following command, or a similar one:

aim% ps -ef |grep pti1 |grep -v grep



oracle   2377  0.0  2.5 1152 1552 ? S   Mar 24  0:27  ora_p000_pti1

oracle  10301  0.0  0.0 1172    0 ? IW  Apr  2  0:01  oraclepti1

oracle   2314  0.0  2.9 1152 1816 ? S   Mar 24  0:25  ora_ckpt_pti1

oracle   2338  0.0  2.6 1168 1636 ? S   Mar 24  0:25  ora_db08_pti1

oracle   2318  0.0  0.0 1176    0 ? IW  Mar 24  0:53  ora_reco_pti1

oracle   2308  0.0  2.9 1152 1772 ? S   Mar 24  3:06  ora_pmon_pti1

oracle   2336  0.0  2.8 1168 1736 ? S   Mar 24  0:27  ora_db07_pti1

oracle   2324  0.0  2.4 1168 1464 ? S   Mar 24  0:33  ora_db03_pti1

oracle   2322  0.0  3.2 1168 1968 ? S   Mar 24  0:33  ora_db02_pti1

oracle   2329  0.0  3.2 1168 1980 ? S   Mar 24  0:30  ora_db04_pti1

oracle   2334  0.0  3.0 1168 1856 ? S   Mar 24  0:29  ora_db06_pti1

oracle   2332  0.0  3.0 1168 1872 ? S   Mar 24  0:32  ora_db05_pti1

oracle   2379  0.0  2.5 1152 1552 ? S   Mar 24  0:26  ora_p001_pti1

oracle   2320  0.0  3.3 1168 2024 ? S   Mar 24  0:41  ora_db01_pti1

oracle   2346  0.0  0.4 1316  256 ? S   Mar 24  5:45  ora_d001_pti1

oracle   2310  0.0  5.9 1156 3644 ? S   Mar 24 62:24  ora_dbwr_pti1

oracle   2344  0.0  0.0 1316    0 ? IW  Mar 24  5:59  ora_d000_pti1

oracle   2340  0.0  0.3 1748  200 ? I   Mar 24 11:54  ora_s000_pti1

oracle   2342  0.0  0.0 1752    0 ? IW  Mar 24  6:15  ora_s001_pti1

oracle  10289  0.0  0.0 1172    0 ? IW  Apr  2  0:00  oraclepti1

oracle   2312  0.0  2.1 1152 1308 ? S   Mar 24  1:11  ora_lgwr_pti1

oracle   2316  0.0  0.0 1168    0 ? IW  Mar 24 16:27  ora_smon_pti1

Notice how the background processes are named. Also notice the process named oraclepti1. This process represents a current user session in the database instance pti1. All processes are owned by oracle. In this case, that’s the UNIX account identified as the Oracle software owner—and also the account that was used to start up the database instance. Oracle background processes run automatically and require very little manual intervention, if any. However, it does not hurt to monitor these processes for any abnormalities at the operating system level. On Windows NT, look under Services in the Control Panel to view the Oracle processes that are running on the server.

Oracle automatically monitors major database events, such as startup and shutdown, and records information pertaining to these events in a running log file residing at the operating system level. This log file is called the database alert log, and is named alert_dbname.log. The database alert log file is an excellent resource for database troubleshooting and should be monitored on a regular basis.

In UNIX, you can list the last 20 lines of this running log file as follows:

aim% tail -20 alert_pti1.log

starting up 2 shared server(s) ...

starting up 2 dispatcher(s) for network protocol ‘tcp’...

Sat Apr 4 13:28:10 1998

alter database mount exclusive

Sat Apr 4 13:28:11 1998

Successful mount of redo thread 1.

Sat Apr 4 13:28:11 1998

Completed: alter database mount exclusive

Sat Apr 4 13:28:11 1998

alter database open

Sat Apr 4 13:28:13 1998

Thread 1 opened at log sequence 6

  Current log# 3 seq# 6 mem# 0: /mnt1/oradata/pti1/log30.dbf

  Current log# 3 seq# 6 mem# 1: /mnt2/oradata/pti1/log32.dbf

Successful open of redo thread 1.

Sat Apr 4 13:28:13 1998

SMON: enabling cache recovery

SMON: enabling tx recovery

Sat Apr 4 13:28:20 1998

Completed: alter database open

The tail -f option will keep a list of the contents of the alert log file open, showing new entries as they are written by Oracle. The location of the alert log file is designated by the BACKGROUND_DUMP_DEST parameter in the initialization parameter file.

Another key process to an Oracle database is the LISTENER process, which listens for new database connections from a remote client. If the LISTENER process is down, remote users cannot connect to the database. The simplest way to check the availability of LISTENER is by issuing the following command at the UNIX prompt:

    lsnrctl status (for the SQL*Net LISTENER)



    or



    lsnrctl80 status (for the Net8 LISTENER)

Monitoring Oracle processes from within the database has the potential of yielding a great deal of information, in addition to that from the operating system level. This is accomplished using the data dictionary views: