Background Process Information (vbgproc.sql)

What This Script Does

This script provides information about Oracle background processes by:

Joining V$SESSION and V$BGPROCESS views
Showing which background processes are currently active
Displaying session IDs for process monitoring
Providing descriptions of each background process function

Script

rem  vbgproc.sql
rem
ttitle 'Background Processes'
rem
col sid format 99 heading 'SID'
col name format a20 heading 'NAME'
col description format a40 heading 'DESCRIPTION'
rem
select sid, name, description
  from v$session s,
       v$bgprocess b
 where b.paddr = s.paddr;

Usage

SQL> @vbgproc.sql

Required Privileges

SELECT on V$SESSION
SELECT on V$BGPROCESS

Sample Output

Background Processes

SID NAME                 DESCRIPTION
--- -------------------- ----------------------------------------
  1 PMON                 process cleanup
  2 PSP0                 process spawner 0
  3 VKTM                 Virtual keeper of time
  4 GEN0                 generic0
  5 DIAG                 diagnosability process
  6 DBRM                 DataBase Resource Manager
  7 VKRM                 Virtual sKeduler for Resource Manager
  8 DIA0                 diagnosability process 0
  9 MMAN                 Memory Manager
 10 DBW0                 db writer process 0
 11 LGWR                 Redo etc.
 12 CKPT                 checkpoint
 13 SMON                 System Monitor Process
 14 RECO                 distributed recovery
 15 CJQ0                 Job Queue Coordinator
 16 MMON                 Manageability Monitor Process
 17 MMNL                 Manageability Monitor Process 2
 18 ARC0                 Archival Process 0
 19 ARC1                 Archival Process 1
 20 ARC2                 Archival Process 2
 21 ARC3                 Archival Process 3
 22 Q000                 oracle scheduler slave process
 23 Q001                 oracle scheduler slave process

Key Background Processes

Core Database Processes

PMON: Process Monitor - cleans up failed processes
SMON: System Monitor - performs system-level cleanup
DBW0-DBWn: Database Writers - write dirty buffers to disk
LGWR: Log Writer - writes redo log entries
CKPT: Checkpoint - manages checkpoint operations
MMON: Manageability Monitor - automatic workload repository

Optional Processes

ARCn: Archiver processes - archive redo logs
RECO: Recoverer - distributed transaction recovery
CJQ0: Job Queue Coordinator - manages job queue
Qnnn: Job queue slave processes
MMNL: Manageability Monitor Lite

Advanced Feature Processes

DIAG: Diagnosability process
DBRM: Database Resource Manager
VKTM: Virtual Keeper of Time
VKRM: Virtual Scheduler for Resource Manager
MMAN: Memory Manager (Automatic Memory Management)

Understanding Process States

Normal Operations

All essential processes (PMON, SMON, DBW0, LGWR, CKPT) should be present and running.

Optional Features

Archive processes: Only if archivelog mode enabled
Job queue processes: Only if job_queue_processes > 0
Resource manager: Only if resource manager enabled

Common Use Cases

Health Checks
- Verify all required processes are running
- Identify missing critical processes
- Check for process failures
Troubleshooting
- Find background process session IDs
- Correlate with alert log messages
- Monitor process resource usage
Configuration Validation
- Verify archiver processes in archivelog mode
- Check job queue process count
- Validate advanced feature enablement

Monitoring Background Processes

Check for missing processes:

-- Essential processes that should always be present
SELECT name FROM v$bgprocess
WHERE name IN ('PMON','SMON','DBW0','LGWR','CKPT')
AND paddr = '00';  -- Not active

Find process resource usage:

SELECT s.sid, b.name, s.status, s.program,
       p.spid, p.pga_used_mem, p.pga_max_mem
FROM v$session s, v$bgprocess b, v$process p
WHERE b.paddr = s.paddr
AND s.paddr = p.addr
ORDER BY b.name;

Check archiver process activity:

SELECT name, status, log_sequence,
       thread#, error
FROM v$archive_processes;

Troubleshooting

Missing Critical Processes

If essential processes are missing:

Check alert log for process failures
Review initialization parameters
Consider instance restart if needed

High Resource Usage

If background processes consume excessive resources:

Check for configuration issues
Review workload patterns
Consider parameter adjustments

Process Errors

For background process errors:

Review alert log and trace files
Check system resources
Investigate underlying causes