Exploring the Oracle Database Architecture
This blog basically foundation of Oracle Database. It cover major concept and include following topics
- DB Basic
We can Break Oracle Database Architecture into Three Part
- Memory Structures - This structure exist only when Oracle Instance is running and some exist when database is running.
- Process Structures - Similar to Memory When Oracle Instance is running then processes exist
- Storage Structures - Storage is associated with Files and it exist all the time whether instance is running or not.
First we know Database Basic in Details
DB Basic
All DBA should know some basic terminology
- What is Database?
- What component comprise database ?
- What is Instance? what is container database ? what is multi tenant database ? what is pdbs ? what is cdb ? etc.
- How to relate database and instance
- What is Oracle Block How OS Block related to Oracle Block
What is Database and What component comprise Database
So first What is Database ? in general Database basically a system to organised, store and retrieve data. It also provide mechanism How to secure database, how to add, update and delete data into database and How data available when failure occur. As far as Oracle Concern , According to Oracle documentation Database is set of file reside on disk.
But general definition is good because it suitable for all database whether it IBM DB2, Mysql, MSSQL or noSQL db.
Organised - Organization deals with database design and normalization.
Store - Storage deals with referential integrity table references constraint on column etc.
Retrieve - Retrieval deals with basically SQL Statement like Select etc.
Security - Who can access data in what amount
Adding, updating, removing - After adding data to database it can be either modified or deleted.
safekeeping - Database keep data safe in case of failure occur such as system crash, hard disk failure etc.
Type Of Database
Flat File System - Like Excel sheet , Store the data in Excel Work Book. But the problem with Flat file system it does not have any mechanism to identified human error like.
- Duplication Data - same data at multiple location
- No Relation between Data
- Less Security etc
Relational Database - to solve the problem of Flat File System Relational Database came. It also have two category SQL and noSQL.
If you don't want to use SQL then you should go with noSQL like monogodb, Dynomodb etc.
Generically If we defined Relational Database - it is database which store data into the logical structure called Table, these table consist row and column, smallest unit of table called tuple. and it have relationship among it's linked tables. What is meaning of linked Tables - linked tables basically two or more tables which have shared column. for example consider employee and department employee table have department_id column which is same as department table department_id.
Object Relational Database - Oracle is officially called Object Relational Database. Because its take all the Relational Database principle and apply them to Object.
Oracle DB = Rel DB + OO data model
Component of Database
As Far as Programming language concern Oracle Database give us Three type of programming language
- SQL
- PL/SQL Oracle owned Programming language
- JAVA
What is Instance, How database and Instance are related, What is Multi-tenant, what is CDB, What is PDB
In this section I will try to give the answer of above question and many more. I hope Database basic terminology is clear .
So What is Instance it is basically Memory Structure and Background process that Run Oracle Software. As we know Oracle Database is paid software and Oracle licencing mechanism based on Instance and Processors.
Before Oracle 12c Oracle support only one instance per Database. In case MYSQL or MSSQL where after installing DATABASE software we can create multiple database. But before oracle 12c it is not possible in Oracle 11g you can create a single database per instance.
But from 12c onward Oracle introduce a concept called multi-tenant database - in simple language if we want to understand what multi-tenant means then we compare it with MYSQL or MSSQL database where we create n number of database according to our requirement.
In Multi-tenant environment Oracle introduce two concept CDB and PDB .
CDB - container DATABASE
PDB - Pluggable DATABASE
Container DATABASE contain all the pluggable database.
So if Some one ask us what is Instance then we simply say Instance is running stage of Oracle Database Software. Some people also use term database instance to refer entire running database.
An instance can exist without database - yes it is true, you can start oracle instance which don't use database.
Database can exist without an instance
An instance can access one and only one database , In Oracle one database per instance is supported.
While In RAC (Real Application Cluster) multiple instance can access a single database.
Memory Structure
Whether you are developer or DBA you should know about Oracle Memory Structure.
By knowing this topic you can improve your performance
System Global Area (SGA)
It is Collection of various memory Structures.
Major Part of SGA are
- Database Buffer Cache
- Buffer Cache State
- Free
- Pinned
- Dirty
- Block Writer Trigger
- Shared Pool
- Library Cache
- Dictionary Cache
- Server Result Cache
- SQL Result Cache
- PL/SQL function Result Cache
- Reserved Pool
- Redo log Buffer
Optional Part of SGA are
- Large Pool
- Java Pool
- Stream Pool
Database Buffer Cache
Its crucial and largest part of SGA allocated by Oracle Process. It is area of memory to scratch space for SQL. DB Buffer temporarily store recently used user query as well as changes made into underlying datafiles. You always remember that RAM is must faster than DISK, DATABASE Buffer sole purpose is to cache data in memory for quicker access. Some Background process write those cached data into datafile.
Database Buffer cache can cache data from all type of object like Table, Index, Materialized View, System Data.
In Database Buffer cache term Buffer means Data Block. A Data Block is smallest unit of data on Disk that Oracle can reads or Write at once. Each OS itself has a block size. Oracle Request data in multiple of Oracle Blocks not Operating System Blocks. The standard Block size is specified by db_block_size initialization parameter. A table data store in data block and a Data block may contain more than one row of a table.
So if you request one row from database oracle reads minimum one data block, which could contain more than one row.
Buffer Cache State - Data Block in buffer cache are one of the following state.
- Free - Data Block is free that is not used by any process.
- Pinned - Data Block currently used.
- Dirty - Data Block modified but not yet written to disk even after commit.
Redo Log Buffer
The redo log buffer is another memory component that record every SQL statements that changes data. The statement itself and any information required to reconstruct it called redo entry. We can replay statement into redo log to reconstruct the database when failure occurs.
When These event occur Oracle flushed Redo log buffer and write it into online Redo log files.
- Every time there’s a commit to data in the database
- Every three seconds
- When the redo buffer is 1/3 full
- Just Before each dirty block written to disk
Shared Pool
Its most used memory area. It stored parse SQL / PLSQL statement, parameter, data dictionary.
Shared Pool itself made up with four main area.
- Library Cache
- Dictionary Cache
- Server Result Cache
- Reserved Pool
Library Cache - Each time when user execute a SQL statement , a lot thing happened in background. This background activity is called parsing.
During parsing, some of these things happen:
- The statement syntax is checked to make sure you typed everything correctly.
- The objects you’re referring to are checked. For example, if you’retrying to access a table called EMPLOYEE, Oracle makes sure it exists in the database.
- Oracle makes sure that you have permission to do what you’re trying to do.
- The code is converted into a database-ready format. The format is called byte-code or p-code.
- Oracle determines the optimum path or plan. This is by far the most expensive part.
Every time you execute a statement, the information is stored in the library cache. That way, the next time you execute the statement not much has to occur (such as checking permissions).
Dictionary Cache - The Dictionary Cache contain the information about user and object accessed by user. It based on LRU algorithm. If Dictionary Cache is not properly organised then it increase DISK I/O which decrease performance.
Because the definitions of objects and permission based information are stored in database files, Oracle has to read disk to reload that information into dictionary cache. This is more time consuming if Oracle Database server have heavy traffic.
Server Result Cache -
It has two part, It store result from SQL or PL/SQL queries.
SQL Result Cache
PL/SQL Result Cache
Reserved Pool - Oracle keep reserve this portion of memory for future allocation.
Large Pool
This is optional component of Memory. Oracle create this portion of memory when such type of Processes are running.
- Oracle Recovery Manager
- Oracle Shared Server
- Parallel Processing
- I/O - related server Processing
Without large pool configured, these processes steal memory from the shared pool's SQL area. Because Large Pool not used LRU algorithm.
Java Pool
From Oracle 8i on wards Oracle came with own JVM. Which can execute JAVA code out of SGA
Stream Pool
Stream Pool is optional, Used to replicated the same transaction, data changes or event from one database to another.
Program Global Area (PGA)
Oracle installation either in Dedicated mode or Shared mode.
Oracle does not provide direct connection of DB server against User Request. When client request some data from Oracle Server started a Background Process and associate PGA with it.
In dedicated architecture each client request create new background process as well as PGA.
While shared architecture each client request create new background process but pga is not dedicated for each process. Oracle create Pool of PGA and allocate PGA for it's background process.
Managing Memory
- Automatic
- Manual
- Combination of Automatic and Manual
From Oracle 11g onwards oracle introduce concept of automatic memory management.
You can leave memory management to oracle by enabling Automatic Memory Management (AMM) and set Memory_target parameter.
SQL> show parameter memory_target NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ memory_target big integer 0
Processes Structures
Oracle Processes are start along with Oracle Instance startup. Oracle Processes are divided into three part.
- Background Process
- Server Process
- User Process
Background Process
Oracle Processes varies OS to OS. In window there is single process under this process other process are running. while in *inx operating system each process run its own.
The following output when only oracle instance is running i.e. startup nomount
[oracle@lap ~]$ ps -ef | grep -i ora_ oracle 16864 1 0 06:54 ? 00:00:00 ora_pmon_orcl oracle 16866 1 0 06:54 ? 00:00:01 ora_psp0_orcl oracle 16877 1 0 06:54 ? 00:03:10 ora_vktm_orcl oracle 16881 1 0 06:54 ? 00:00:00 ora_gen0_orcl oracle 16883 1 0 06:54 ? 00:00:00 ora_mman_orcl oracle 16887 1 0 06:54 ? 00:00:00 ora_diag_orcl oracle 16889 1 0 06:54 ? 00:00:02 ora_dbrm_orcl oracle 16891 1 0 06:54 ? 00:00:00 ora_vkrm_orcl oracle 16893 1 0 06:54 ? 00:00:05 ora_dia0_orcl oracle 16895 1 0 06:54 ? 00:00:00 ora_dbw0_orcl oracle 16897 1 0 06:54 ? 00:00:00 ora_lgwr_orcl oracle 16899 1 0 06:54 ? 00:00:00 ora_ckpt_orcl oracle 16901 1 0 06:54 ? 00:00:00 ora_lg00_orcl oracle 16903 1 0 06:54 ? 00:00:00 ora_smon_orcl oracle 16905 1 0 06:54 ? 00:00:00 ora_lg01_orcl oracle 16907 1 0 06:54 ? 00:00:00 ora_reco_orcl oracle 16909 1 0 06:54 ? 00:00:00 ora_lreg_orcl oracle 16911 1 0 06:54 ? 00:00:00 ora_pxmn_orcl oracle 16913 1 0 06:54 ? 00:00:00 ora_mmon_orcl oracle 16915 1 0 06:54 ? 00:00:01 ora_mmnl_orcl oracle 16917 1 0 06:54 ? 00:00:00 ora_d000_orcl oracle 16919 1 0 06:54 ? 00:00:00 ora_s000_orcl
The following output after database running
oracle 16864 1 0 06:54 ? 00:00:00 ora_pmon_orcl oracle 16866 1 0 06:54 ? 00:00:01 ora_psp0_orcl oracle 16877 1 0 06:54 ? 00:03:32 ora_vktm_orcl oracle 16881 1 0 06:54 ? 00:00:00 ora_gen0_orcl oracle 16883 1 0 06:54 ? 00:00:00 ora_mman_orcl oracle 16887 1 0 06:54 ? 00:00:00 ora_diag_orcl oracle 16889 1 0 06:54 ? 00:00:02 ora_dbrm_orcl oracle 16891 1 0 06:54 ? 00:00:00 ora_vkrm_orcl oracle 16893 1 0 06:54 ? 00:00:06 ora_dia0_orcl oracle 16895 1 0 06:54 ? 00:00:00 ora_dbw0_orcl oracle 16897 1 0 06:54 ? 00:00:00 ora_lgwr_orcl oracle 16899 1 0 06:54 ? 00:00:00 ora_ckpt_orcl oracle 16901 1 0 06:54 ? 00:00:00 ora_lg00_orcl oracle 16903 1 0 06:54 ? 00:00:00 ora_smon_orcl oracle 16905 1 0 06:54 ? 00:00:00 ora_lg01_orcl oracle 16907 1 0 06:54 ? 00:00:00 ora_reco_orcl oracle 16909 1 0 06:54 ? 00:00:00 ora_lreg_orcl oracle 16911 1 0 06:54 ? 00:00:00 ora_pxmn_orcl oracle 16913 1 0 06:54 ? 00:00:02 ora_mmon_orcl oracle 16915 1 0 06:54 ? 00:00:02 ora_mmnl_orcl oracle 16917 1 0 06:54 ? 00:00:00 ora_d000_orcl oracle 16919 1 0 06:54 ? 00:00:00 ora_s000_orcl oracle 28783 1 0 17:04 ? 00:00:00 ora_tmon_orcl oracle 28785 1 1 17:04 ? 00:00:08 ora_arc0_orcl oracle 28787 1 1 17:04 ? 00:00:08 ora_arc1_orcl oracle 28789 1 0 17:04 ? 00:00:00 ora_arc2_orcl oracle 28792 1 1 17:04 ? 00:00:08 ora_arc3_orcl oracle 28794 1 0 17:04 ? 00:00:00 ora_tt00_orcl oracle 28798 1 0 17:04 ? 00:00:00 ora_smco_orcl oracle 28800 1 0 17:04 ? 00:00:00 ora_w000_orcl oracle 28802 1 0 17:04 ? 00:00:00 ora_w001_orcl oracle 28807 1 0 17:04 ? 00:00:00 ora_aqpc_orcl oracle 28818 1 0 17:04 ? 00:00:00 ora_p000_orcl oracle 28820 1 0 17:04 ? 00:00:00 ora_p001_orcl oracle 28822 1 0 17:04 ? 00:00:00 ora_p002_orcl oracle 28824 1 0 17:04 ? 00:00:00 ora_p003_orcl oracle 28826 1 0 17:04 ? 00:00:00 ora_p004_orcl oracle 28828 1 0 17:04 ? 00:00:00 ora_p005_orcl oracle 28830 1 0 17:04 ? 00:00:00 ora_p006_orcl oracle 28832 1 0 17:04 ? 00:00:00 ora_p007_orcl oracle 28834 1 0 17:04 ? 00:00:00 ora_p008_orcl oracle 28836 1 0 17:04 ? 00:00:00 ora_p009_orcl oracle 28838 1 0 17:04 ? 00:00:00 ora_p00a_orcl oracle 28840 1 0 17:04 ? 00:00:00 ora_p00b_orcl oracle 28842 1 0 17:04 ? 00:00:00 ora_p00c_orcl oracle 28844 1 0 17:04 ? 00:00:00 ora_p00d_orcl oracle 28846 1 0 17:04 ? 00:00:00 ora_p00e_orcl oracle 28848 1 0 17:04 ? 00:00:00 ora_p00f_orcl oracle 28854 1 0 17:04 ? 00:00:00 ora_cjq0_orcl oracle 29106 1 0 17:04 ? 00:00:00 ora_qm02_orcl oracle 29115 1 0 17:04 ? 00:00:00 ora_q003_orcl oracle 29117 1 0 17:04 ? 00:00:00 ora_q004_orcl
We can query v$BGPROCESS data dictionary view to list all the Background process.
PADDR PSERIAL# NAME DESCRIPTION ERROR CON_ID ---------------- ---------- ----- ---------------------------------------------------------------- ---------- ---------- 00000000CBB47C80 1 PMON process cleanup 0 0 00000000CBB492D0 1 VKTM Virtual Keeper of TiMe process 0 0 00000000CBB49DF8 1 GEN0 generic0 0 0 00000000CBB4BF70 1 DIAG diagnosibility process 0 0 00 0 OFSD Oracle File Server BG 0 0 00000000CBB4CA98 1 DBRM DataBase Resource Manager 0 0 00000000CBB4D5C0 1 VKRM Virtual sKeduler for Resource Manager 0 0 00 0 RSMN Remote Slave Monitor 0 0 00 0 PING interconnect latency measurement 0 0 00 0 FMON File Mapping Monitor Process 0 0 00000000CBB487A8 1 PSP0 process spawner 0 0 0 PADDR PSERIAL# NAME DESCRIPTION ERROR CON_ID ---------------- ---------- ----- ---------------------------------------------------------------- ---------- ---------- 00 0 RMON rolling migration monitor 0 0 00 0 ACMS Atomic Controlfile to Memory Server 0 0 00 0 DSKM slave DiSKMon process 0 0 00 0 IPC0 IPC Service 0 0 0 00 0 RPOP instant restore repopulation daemon 0 0 00000000CBB4E0E8 1 DIA0 diagnosibility process 0 0 0 00 0 DIA1 diagnosibility process 1 0 0 00 0 DIA2 diagnosibility process 2 0 0 00 0 DIA3 diagnosibility process 3 0 0 00 0 DIA4 diagnosibility process 4 0 0 00 0 DIA5 diagnosibility process 5 0 0 PADDR PSERIAL# NAME DESCRIPTION ERROR CON_ID ---------------- ---------- ----- ---------------------------------------------------------------- ---------- ---------- 00 0 DIA6 diagnosibility process 6 0 0 00 0 DIA7 diagnosibility process 7 0 0 00 0 DIA8 diagnosibility process 8 0 0 00 0 DIA9 diagnosibility process 9 0 0 00 0 LMON global enqueue service monitor 0 0 00 0 LMD0 global enqueue service daemon 0 0 0 00 0 LMD1 global enqueue service daemon 1 0 0 00 0 LMD2 global enqueue service daemon 2 0 0 00 0 LMD3 global enqueue service daemon 3 0 0 00 0 LMD4 global enqueue service daemon 4 0 0 00 0 LMD5 global enqueue service daemon 5 0 0 PADDR PSERIAL# NAME DESCRIPTION ERROR CON_ID ---------------- ---------- ----- ---------------------------------------------------------------- ---------- ---------- 00 0 LMD6 global enqueue service daemon 6 0 0 00 0 LMD7 global enqueue service daemon 7 0 0 00 0 LMD8 global enqueue service daemon 8 0 0 00 0 LMD9 global enqueue service daemon 9 0 0 00 0 LMDa global enqueue service daemon 10 0 0 00 0 LMDb global enqueue service daemon 11 0 0 00 0 LMDc global enqueue service daemon 12 0 0 00 0 LMDd global enqueue service daemon 13 0 0 00 0 LMDe global enqueue service daemon 14 0 0 00 0 LMDf global enqueue service daemon 15 0 0 00 0 LMDg global enqueue service daemon 16 0 0 PADDR PSERIAL# NAME DESCRIPTION ERROR CON_ID ---------------- ---------- ----- ---------------------------------------------------------------- ---------- ---------- 00 0 LMDh global enqueue service daemon 17 0 0 00 0 LMDi global enqueue service daemon 19 0 0 00 0 LMDj global enqueue service daemon 19 0 0 00 0 LMDk global enqueue service daemon 20 0 0 00 0 LMDl global enqueue service daemon 21 0 0 00 0 LMDm global enqueue service daemon 22 0 0 00 0 LMDn global enqueue service daemon 23 0 0 00 0 LMDo global enqueue service daemon 24 0 0 00 0 LMDp global enqueue service daemon 25 0 0 00 0 LMDq global enqueue service daemon 26 0 0 00 0 LMDr global enqueue service daemon 27 0 0 PADDR PSERIAL# NAME DESCRIPTION ERROR CON_ID ---------------- ---------- ----- ---------------------------------------------------------------- ---------- ---------- 00 0 LMDs global enqueue service daemon 28 0 0 00 0 LMDt global enqueue service daemon 29 0 0 00 0 LMDu global enqueue service daemon 30 0 0 00 0 LMDv global enqueue service daemon 31 0 0 00 0 LMDw global enqueue service daemon 32 0 0 00 0 LMDx global enqueue service daemon 33 0 0 00 0 LMDy global enqueue service daemon 34 0 0 00 0 LMDz global enqueue service daemon 35 0 0 00 0 LMS0 global cache service process 0 0 0 00 0 LMS1 global cache service process 1 0 0 00 0 LMS2 global cache service process 2 0 0 PADDR PSERIAL# NAME DESCRIPTION ERROR CON_ID ---------------- ---------- ----- ---------------------------------------------------------------- ---------- ---------- 00 0 LMS3 global cache service process 3 0 0 00 0 LMS4 global cache service process 4 0 0 00 0 LMS5 global cache service process 5 0 0 00 0 LMS6 global cache service process 6 0 0 00 0 LMS7 global cache service process 7 0 0 00 0 LMS8 global cache service process 8 0 0 00 0 LMS9 global cache service process 9 0 0 00 0 LMSa global cache service process 10 0 0 00 0 LMSb global cache service process 11 0 0 00 0 LMSc global cache service process 12 0 0 00 0 LMSd global cache service process 13 0 0 PADDR PSERIAL# NAME DESCRIPTION ERROR CON_ID ---------------- ---------- ----- ---------------------------------------------------------------- ---------- ---------- 00 0 LMSe global cache service process 14 0 0 00 0 LMSf global cache service process 15 0 0 00 0 LMSg global cache service process 16 0 0 00 0 LMSh global cache service process 17 0 0 00 0 LMSi global cache service process 18 0 0 00 0 LMSj global cache service process 19 0 0 00 0 LMSk global cache service process 20 0 0 00 0 LMSl global cache service process 21 0 0 00 0 LMSm global cache service process 22 0 0 00 0 LMSn global cache service process 23 0 0 00 0 LMSo global cache service process 24 0 0 PADDR PSERIAL# NAME DESCRIPTION ERROR CON_ID ---------------- ---------- ----- ---------------------------------------------------------------- ---------- ---------- 00 0 LMSp global cache service process 25 0 0 00 0 LMSq global cache service process 26 0 0 00 0 LMSr global cache service process 27 0 0 00 0 LMSs global cache service process 28 0 0 00 0 LMSt global cache service process 29 0 0 00 0 LMSu global cache service process 30 0 0 00 0 LMSv global cache service process 31 0 0 00 0 LMSw global cache service process 32 0 0 00 0 LMSx global cache service process 33 0 0 00 0 LMSy global cache service process 34 0 0 00 0 RMS0 rac management server 0 0 PADDR PSERIAL# NAME DESCRIPTION ERROR CON_ID ---------------- ---------- ----- ---------------------------------------------------------------- ---------- ---------- 00 0 LMHB lm heartbeat monitor 0 0 00 0 LCK1 Lock Process 1 0 0 00000000CBB4A920 1 MMAN Memory Manager 0 0 00000000CBB4EC10 1 DBW0 db writer process 0 0 0 00 0 DBW1 db writer process 1 0 0 00 0 DBW2 db writer process 2 0 0 00 0 DBW3 db writer process 3 0 0 00 0 DBW4 db writer process 4 0 0 00 0 DBW5 db writer process 5 0 0 00 0 DBW6 db writer process 6 0 0 00 0 DBW7 db writer process 7 0 0 PADDR PSERIAL# NAME DESCRIPTION ERROR CON_ID ---------------- ---------- ----- ---------------------------------------------------------------- ---------- ---------- 00 0 DBW8 db writer process 8 0 0 00 0 DBW9 db writer process 9 0 0 00 0 DBWa db writer process 10 (a) 0 0 00 0 DBWb db writer process 11 (b) 0 0 00 0 DBWc db writer process 12 (c) 0 0 00 0 DBWd db writer process 13 (d) 0 0 00 0 DBWe db writer process 14 (e) 0 0 00 0 DBWf db writer process 15 (f) 0 0 00 0 DBWg db writer process 16 (g) 0 0 00 0 DBWh db writer process 17 (h) 0 0 00 0 DBWi db writer process 18 (i) 0 0 PADDR PSERIAL# NAME DESCRIPTION ERROR CON_ID ---------------- ---------- ----- ---------------------------------------------------------------- ---------- ---------- 00 0 DBWj db writer process 19 (j) 0 0 00 0 DBWk db writer process 20 (k) 0 0 00 0 DBWl db writer process 21 (l) 0 0 00 0 DBWm db writer process 22 (m) 0 0 00 0 DBWn db writer process 23 (n) 0 0 00 0 DBWo db writer process 24 (o) 0 0 00 0 DBWp db writer process 25 (p) 0 0 00 0 DBWq db writer process 26 (q) 0 0 00 0 DBWr db writer process 27 (r) 0 0 00 0 DBWs db writer process 28 (s) 0 0 00 0 DBWt db writer process 29 (t) 0 0 PADDR PSERIAL# NAME DESCRIPTION ERROR CON_ID ---------------- ---------- ----- ---------------------------------------------------------------- ---------- ---------- 00 0 DBWu db writer process 30 (u) 0 0 00 0 DBWv db writer process 31 (v) 0 0 00 0 DBWw db writer process 32 (w) 0 0 00 0 DBWx db writer process 33 (x) 0 0 00 0 DBWy db writer process 34 (y) 0 0 00 0 DBWz db writer process 35 (z) 0 0 00 0 BW36 db writer process 36 0 0 00 0 BW37 db writer process 37 0 0 00 0 BW38 db writer process 38 0 0 00 0 BW39 db writer process 39 0 0 00 0 BW40 db writer process 40 0 0 PADDR PSERIAL# NAME DESCRIPTION ERROR CON_ID ---------------- ---------- ----- ---------------------------------------------------------------- ---------- ---------- 00 0 BW41 db writer process 41 0 0 00 0 BW42 db writer process 42 0 0 00 0 BW43 db writer process 43 0 0 00 0 BW44 db writer process 44 0 0 00 0 BW45 db writer process 45 0 0 00 0 BW46 db writer process 46 0 0 00 0 BW47 db writer process 47 0 0 00 0 BW48 db writer process 48 0 0 00 0 BW49 db writer process 49 0 0 00 0 BW50 db writer process 50 0 0 00 0 BW51 db writer process 51 0 0 PADDR PSERIAL# NAME DESCRIPTION ERROR CON_ID ---------------- ---------- ----- ---------------------------------------------------------------- ---------- ---------- 00 0 BW52 db writer process 52 0 0 00 0 BW53 db writer process 53 0 0 00 0 BW54 db writer process 54 0 0 00 0 BW55 db writer process 55 0 0 00 0 BW56 db writer process 56 0 0 00 0 BW57 db writer process 57 0 0 00 0 BW58 db writer process 58 0 0 00 0 BW59 db writer process 59 0 0 00 0 BW60 db writer process 60 0 0 00 0 BW61 db writer process 61 0 0 00 0 BW62 db writer process 62 0 0 PADDR PSERIAL# NAME DESCRIPTION ERROR CON_ID ---------------- ---------- ----- ---------------------------------------------------------------- ---------- ---------- 00 0 BW63 db writer process 63 0 0 00 0 BW64 db writer process 64 0 0 00 0 BW65 db writer process 65 0 0 00 0 BW66 db writer process 66 0 0 00 0 BW67 db writer process 67 0 0 00 0 BW68 db writer process 68 0 0 00 0 BW69 db writer process 69 0 0 00 0 BW70 db writer process 70 0 0 00 0 BW71 db writer process 71 0 0 00 0 BW72 db writer process 72 0 0 00 0 BW73 db writer process 73 0 0 PADDR PSERIAL# NAME DESCRIPTION ERROR CON_ID ---------------- ---------- ----- ---------------------------------------------------------------- ---------- ---------- 00 0 BW74 db writer process 74 0 0 00 0 BW75 db writer process 75 0 0 00 0 BW76 db writer process 76 0 0 00 0 BW77 db writer process 77 0 0 00 0 BW78 db writer process 78 0 0 00 0 BW79 db writer process 79 0 0 00 0 BW80 db writer process 80 0 0 00 0 BW81 db writer process 81 0 0 00 0 BW82 db writer process 82 0 0 00 0 BW83 db writer process 83 0 0 00 0 BW84 db writer process 84 0 0 PADDR PSERIAL# NAME DESCRIPTION ERROR CON_ID ---------------- ---------- ----- ---------------------------------------------------------------- ---------- ---------- 00 0 BW85 db writer process 85 0 0 00 0 BW86 db writer process 86 0 0 00 0 BW87 db writer process 87 0 0 00 0 BW88 db writer process 88 0 0 00 0 BW89 db writer process 89 0 0 00 0 BW90 db writer process 90 0 0 00 0 BW91 db writer process 91 0 0 00 0 BW92 db writer process 92 0 0 00 0 BW93 db writer process 93 0 0 00 0 BW94 db writer process 94 0 0 00 0 BW95 db writer process 95 0 0 PADDR PSERIAL# NAME DESCRIPTION ERROR CON_ID ---------------- ---------- ----- ---------------------------------------------------------------- ---------- ---------- 00 0 BW96 db writer process 96 0 0 00 0 BW97 db writer process 97 0 0 00 0 BW98 db writer process 98 0 0 00 0 BW99 db writer process 99 0 0 00 0 MRP0 Managed Standby Recovery 0 0 00000000CBB57D18 3 TMON Transport Monitor 0 0 00000000CBB58840 5 ARC0 Archival Process 0 0 0 00000000CBB59368 3 ARC1 Archival Process 1 0 0 00000000CBB59E90 1 ARC2 Archival Process 2 0 0 00000000CBB5A9B8 1 ARC3 Archival Process 3 0 0 00 0 ARC4 Archival Process 4 0 0 PADDR PSERIAL# NAME DESCRIPTION ERROR CON_ID ---------------- ---------- ----- ---------------------------------------------------------------- ---------- ---------- 00 0 ARC5 Archival Process 5 0 0 00 0 ARC6 Archival Process 6 0 0 00 0 ARC7 Archival Process 7 0 0 00 0 ARC8 Archival Process 8 0 0 00 0 ARC9 Archival Process 9 0 0 00 0 ARCa Archival Process 10 0 0 00 0 ARCb Archival Process 11 0 0 00 0 ARCc Archival Process 12 0 0 00 0 ARCd Archival Process 13 0 0 00 0 ARCe Archival Process 14 0 0 00 0 ARCf Archival Process 15 0 0 PADDR PSERIAL# NAME DESCRIPTION ERROR CON_ID ---------------- ---------- ----- ---------------------------------------------------------------- ---------- ---------- 00 0 ARCg Archival Process 16 0 0 00 0 ARCh Archival Process 17 0 0 00 0 ARCi Archival Process 18 0 0 00 0 ARCj Archival Process 19 0 0 00 0 ARCk Archival Process 20 0 0 00 0 ARCl Archival Process 21 0 0 00 0 ARCm Archival Process 22 0 0 00 0 ARCn Archival Process 23 0 0 00 0 ARCo Archival Process 24 0 0 00 0 ARCp Archival Process 25 0 0 00 0 ARCq Archival Process 26 0 0 PADDR PSERIAL# NAME DESCRIPTION ERROR CON_ID ---------------- ---------- ----- ---------------------------------------------------------------- ---------- ---------- 00 0 ARCr Archival Process 27 0 0 00 0 ARCs Archival Process 28 0 0 00 0 ARCt Archival Process 29 0 0 00 0 NSS1 Redo transport NSS1 0 0 00 0 NSS2 Redo transport NSS2 0 0 00 0 NSS3 Redo transport NSS3 0 0 00 0 NSS4 Redo transport NSS4 0 0 00 0 NSS5 Redo transport NSS5 0 0 00 0 NSS6 Redo transport NSS6 0 0 00 0 NSS7 Redo transport NSS7 0 0 00 0 NSS8 Redo transport NSS8 0 0 PADDR PSERIAL# NAME DESCRIPTION ERROR CON_ID ---------------- ---------- ----- ---------------------------------------------------------------- ---------- ---------- 00 0 NSS9 Redo transport NSS9 0 0 00 0 NSSA Redo transport NSSA 0 0 00 0 NSSB Redo transport NSSB 0 0 00 0 NSSC Redo transport NSSC 0 0 00 0 NSSD Redo transport NSSD 0 0 00 0 NSSE Redo transport NSSE 0 0 00 0 NSSF Redo transport NSSF 0 0 00 0 NSSG Redo transport NSSG 0 0 00 0 NSSH Redo transport NSSH 0 0 00 0 NSSI Redo transport NSSI 0 0 00 0 NSSJ Redo transport NSSJ 0 0 PADDR PSERIAL# NAME DESCRIPTION ERROR CON_ID ---------------- ---------- ----- ---------------------------------------------------------------- ---------- ---------- 00 0 NSSK Redo transport NSSK 0 0 00 0 NSSL Redo transport NSSL 0 0 00 0 NSSM Redo transport NSSM 0 0 00 0 NSSN Redo transport NSSN 0 0 00 0 NSSO Redo transport NSSO 0 0 00 0 NSSP Redo transport NSSP 0 0 00 0 NSSQ Redo transport NSSQ 0 0 00 0 RTTD Redo Transport Test Driver 0 0 00 0 TPZ1 Test Process Z1 0 0 00 0 TPZ2 Test Process Z2 0 0 00 0 TPZ3 Test Process Z3 0 0 PADDR PSERIAL# NAME DESCRIPTION ERROR CON_ID ---------------- ---------- ----- ---------------------------------------------------------------- ---------- ---------- 00000000CBB4F738 1 LGWR Redo etc. 0 0 00 0 LCK0 Lock Process 0 0 0 00000000CBB50260 1 CKPT checkpoint 0 0 00 0 ABMR Auto BMR Background Process 0 0 00 0 LSP1 Dictionary build process for Logical Standby 0 0 00 0 LSP0 Logical Standby 0 0 00 0 LSP2 Set Guard Standby Information for Logical Standby 0 0 00 0 CTWR Change Tracking Writer 0 0 00 0 RVWR Recovery Writer 0 0 00 0 FBDA Flashback Data Archiver Process 0 0 00000000CBB518B0 1 SMON System Monitor Process 0 0 PADDR PSERIAL# NAME DESCRIPTION ERROR CON_ID ---------------- ---------- ----- ---------------------------------------------------------------- ---------- ---------- 00000000CBB5C008 1 SMCO Space Manager Process 0 0 00 0 IMCO In-memory coordinator process 0 0 00000000CBB52F00 1 RECO distributed recovery 0 0 00 0 GTX0 Global Txn process 0 0 0 00 0 GTX1 Global Txn process 1 0 0 00 0 GTX2 Global Txn process 2 0 0 00 0 GTX3 Global Txn process 3 0 0 00 0 GTX4 Global Txn process 4 0 0 00 0 GTX5 Global Txn process 5 0 0 00 0 GTX6 Global Txn process 6 0 0 00 0 GTX7 Global Txn process 7 0 0 PADDR PSERIAL# NAME DESCRIPTION ERROR CON_ID ---------------- ---------- ----- ---------------------------------------------------------------- ---------- ---------- 00 0 GTX8 Global Txn process 8 0 0 00 0 GTX9 Global Txn process 9 0 0 00 0 GTXa Global Txn process 10 0 0 00 0 GTXb Global Txn process 11 0 0 00 0 GTXc Global Txn process 12 0 0 00 0 GTXd Global Txn process 13 0 0 00 0 GTXe Global Txn process 14 0 0 00 0 GTXf Global Txn process 15 0 0 00 0 GTXg Global Txn process 16 0 0 00 0 GTXh Global Txn process 17 0 0 00 0 GTXi Global Txn process 18 0 0 PADDR PSERIAL# NAME DESCRIPTION ERROR CON_ID ---------------- ---------- ----- ---------------------------------------------------------------- ---------- ---------- 00 0 GTXj Global Txn process 19 0 0 00000000CBB53A28 1 LREG Listener Registration 0 0 00000000CBB5ECA8 2 CJQ0 Job Queue Coordinator 0 0 00000000CBB54550 1 PXMN PX Monitor 0 0 00 0 RCBG Result Cache: Background 0 0 00000000CBB5E180 1 AQPC AQ Process Coord 0 0 00 0 DMON DG Broker Monitor Process 0 0 00 0 RSM0 Data Guard Broker Resource Guard Process 0 0 0 00 0 NSV0 Data Guard Broker NetSlave Process 0 0 0 00 0 NSV1 Data Guard Broker NetSlave Process 1 0 0 00 0 NSV2 Data Guard Broker NetSlave Process 2 0 0 PADDR PSERIAL# NAME DESCRIPTION ERROR CON_ID ---------------- ---------- ----- ---------------------------------------------------------------- ---------- ---------- 00 0 NSV3 Data Guard Broker NetSlave Process 3 0 0 00 0 NSV4 Data Guard Broker NetSlave Process 4 0 0 00 0 NSV5 Data Guard Broker NetSlave Process 5 0 0 00 0 NSV6 Data Guard Broker NetSlave Process 6 0 0 00 0 NSV7 Data Guard Broker NetSlave Process 7 0 0 00 0 NSV8 Data Guard Broker NetSlave Process 8 0 0 00 0 NSV9 Data Guard Broker NetSlave Process 9 0 0 00 0 NSVA Data Guard Broker NetSlave Process A 0 0 00 0 NSVB Data Guard Broker NetSlave Process B 0 0 00 0 NSVC Data Guard Broker NetSlave Process C 0 0 00 0 NSVD Data Guard Broker NetSlave Process D 0 0 PADDR PSERIAL# NAME DESCRIPTION ERROR CON_ID ---------------- ---------- ----- ---------------------------------------------------------------- ---------- ---------- 00 0 NSVE Data Guard Broker NetSlave Process E 0 0 00 0 NSVF Data Guard Broker NetSlave Process F 0 0 00 0 NSVG Data Guard Broker NetSlave Process G 0 0 00 0 NSVH Data Guard Broker NetSlave Process H 0 0 00 0 NSVI Data Guard Broker NetSlave Process I 0 0 00 0 NSVJ Data Guard Broker NetSlave Process J 0 0 00 0 NSVK Data Guard Broker NetSlave Process K 0 0 00 0 NSVL Data Guard Broker NetSlave Process L 0 0 00 0 NSVM Data Guard Broker NetSlave Process M 0 0 00 0 NSVN Data Guard Broker NetSlave Process N 0 0 00 0 NSVO Data Guard Broker NetSlave Process O 0 0 PADDR PSERIAL# NAME DESCRIPTION ERROR CON_ID ---------------- ---------- ----- ---------------------------------------------------------------- ---------- ---------- 00 0 NSVP Data Guard Broker NetSlave Process P 0 0 00 0 NSVQ Data Guard Broker NetSlave Process Q 0 0 00 0 NSVR Data Guard Broker NetSlave Process R 0 0 00 0 NSVS Data Guard Broker NetSlave Process S 0 0 00 0 NSVT Data Guard Broker NetSlave Process T 0 0 00 0 NSVU Data Guard Broker NetSlave Process U 0 0 00 0 INSV Data Guard Broker INstance SlaVe Process 0 0 00 0 FSFP Data Guard Broker FSFO Pinger 0 0 00 0 RBAL ASM Rebalance master 0 0 00 0 ARB0 ASM Rebalance 0 0 0 00 0 ARB1 ASM Rebalance 1 0 0 PADDR PSERIAL# NAME DESCRIPTION ERROR CON_ID ---------------- ---------- ----- ---------------------------------------------------------------- ---------- ---------- 00 0 ARB2 ASM Rebalance 2 0 0 00 0 ARB3 ASM Rebalance 3 0 0 00 0 ARB4 ASM Rebalance 4 0 0 00 0 ARB5 ASM Rebalance 5 0 0 00 0 ARB6 ASM Rebalance 6 0 0 00 0 ARB7 ASM Rebalance 7 0 0 00 0 ARB8 ASM Rebalance 8 0 0 00 0 ARB9 ASM Rebalance 9 0 0 00 0 ARBA ASM Rebalance 10 0 0 00 0 ASMB ASM Background 0 0 00 0 MARK mark AU for resync koordinator 0 0 PADDR PSERIAL# NAME DESCRIPTION ERROR CON_ID ---------------- ---------- ----- ---------------------------------------------------------------- ---------- ---------- 00 0 FENC IOServer fence monitor 0 0 00 0 GMON diskgroup monitor 0 0 00 0 VBG0 Volume BG 0 0 0 00 0 VBG1 Volume BG 1 0 0 00 0 VBG2 Volume BG 2 0 0 00 0 VBG3 Volume BG 3 0 0 00 0 VBG4 Volume BG 4 0 0 00 0 VBG5 Volume BG 5 0 0 00 0 VBG6 Volume BG 6 0 0 00 0 VBG7 Volume BG 7 0 0 00 0 VBG8 Volume BG 8 0 0 PADDR PSERIAL# NAME DESCRIPTION ERROR CON_ID ---------------- ---------- ----- ---------------------------------------------------------------- ---------- ---------- 00 0 VBG9 Volume BG 9 0 0 00 0 VDBG Volume Driver BG 0 0 00 0 VUBG Volume Driver Umbilical Background 0 0 00 0 VMB0 Volume Membership 0 0 0 00 0 VIO0 Volume IO 0 0 0 00 0 VIO1 Volume IO 1 0 0 00 0 VIO2 Volume IO 2 0 0 00 0 VIO3 Volume IO 3 0 0 00 0 ACFS ACFS CSS 0 0 00 0 SCRB ASM Scrubbing Master 0 0 00000000CBB55078 1 MMON Manageability Monitor Process 0 0 PADDR PSERIAL# NAME DESCRIPTION ERROR CON_ID ---------------- ---------- ----- ---------------------------------------------------------------- ---------- ---------- 00000000CBB55BA0 1 MMNL Manageability Monitor Process 2 0 0 00 0 XDMG cell automation manager 0 0 00 0 XDWK cell automation worker actions 0 0 377 rows selected.
The Output of V$BGPROCESS PADDR Column have either 0 or some hexadecimal value. process which are running out of 377 have memory address rest have 0.
You can also query v$process data dictionary view to know detail about running process.
select * from v$process;
ADDR PID SOSID SPID STID EXECUTION_ PNAME USERNAME SERIAL# TERMINAL PROGRAM T TRACEFILE B LATCHWAIT LATCHSPIN PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM CON_ID ---------------- ---------- ------------------------ ------------------------ ------------------------ ---------- ----- --------------- ---------- ------------------------------ ------------------------------------------------ - --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - ---------------- ---------------- ------------ ------------- ---------------- ----------- ---------- 00000000CBB47158 1 NONE 0 PSEUDO /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_0.trc 0 0 0 0 0 00000000CBB47C80 2 9296 9296 9296 PROCESS PMON oracle 1 UNKNOWN oracle@lap.lambda.asia (PMON) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_pmon_9296.trc 1 797556 974508 0 974508 0 00000000CBB487A8 3 9298 9298 9298 PROCESS PSP0 oracle 1 UNKNOWN oracle@lap.lambda.asia (PSP0) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_psp0_9298.trc 1 775604 949716 0 949716 0 00000000CBB492D0 4 9309 9309 9309 PROCESS VKTM oracle 1 UNKNOWN oracle@lap.lambda.asia (VKTM) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_vktm_9309.trc 1 772356 949716 0 949716 0 00000000CBB49DF8 5 9313 9313 9313 PROCESS GEN0 oracle 1 UNKNOWN oracle@lap.lambda.asia (GEN0) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_gen0_9313.trc 1 780780 1285764 0 1285764 0 00000000CBB4A920 6 9315 9315 9315 PROCESS MMAN oracle 1 UNKNOWN oracle@lap.lambda.asia (MMAN) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_mman_9315.trc 1 775604 949716 0 949716 0 00000000CBB4B448 7 10484 10484 10484 PROCESS TMON oracle 4 UNKNOWN oracle@lap.lambda.asia (TMON) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_tmon_10484.trc 1 772364 949716 0 949716 0 00000000CBB4BF70 8 9319 9319 9319 PROCESS DIAG oracle 1 UNKNOWN oracle@lap.lambda.asia (DIAG) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_diag_9319.trc 1 778700 958084 0 958084 0 00000000CBB4CA98 9 9321 9321 9321 PROCESS DBRM oracle 1 UNKNOWN oracle@lap.lambda.asia (DBRM) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_dbrm_9321.trc 1 1845436 2858628 720896 2858628 0 00000000CBB4D5C0 10 9323 9323 9323 PROCESS VKRM oracle 1 UNKNOWN oracle@lap.lambda.asia (VKRM) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_vkrm_9323.trc 1 772356 949716 0 949716 0 00000000CBB4E0E8 11 9325 9325 9325 PROCESS DIA0 oracle 1 UNKNOWN oracle@lap.lambda.asia (DIA0) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_dia0_9325.trc 1 2229404 2513892 65536 2513892 0 ADDR PID SOSID SPID STID EXECUTION_ PNAME USERNAME SERIAL# TERMINAL PROGRAM T TRACEFILE B LATCHWAIT LATCHSPIN PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM CON_ID ---------------- ---------- ------------------------ ------------------------ ------------------------ ---------- ----- --------------- ---------- ------------------------------ ------------------------------------------------ - --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - ---------------- ---------------- ------------ ------------- ---------------- ----------- ---------- 00000000CBB4EC10 12 9327 9327 9327 PROCESS DBW0 oracle 1 UNKNOWN oracle@lap.lambda.asia (DBW0) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_dbw0_9327.trc 1 7256092 7978964 0 7978964 0 00000000CBB4F738 13 9329 9329 9329 PROCESS LGWR oracle 1 UNKNOWN oracle@lap.lambda.asia (LGWR) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_lgwr_9329.trc 1 964444 1482372 0 1482372 0 00000000CBB50260 14 9331 9331 9331 PROCESS CKPT oracle 1 UNKNOWN oracle@lap.lambda.asia (CKPT) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ckpt_9331.trc 1 837644 1311404 0 1311404 0 00000000CBB50D88 15 9334 9334 9334 PROCESS LG00 oracle 1 UNKNOWN oracle@lap.lambda.asia (LG00) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_lg00_9334.trc 1 772532 949716 0 949716 0 00000000CBB518B0 16 9336 9336 9336 PROCESS SMON oracle 1 UNKNOWN oracle@lap.lambda.asia (SMON) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_smon_9336.trc 1 1008980 1474004 65536 1474004 0 00000000CBB523D8 17 9338 9338 9338 PROCESS LG01 oracle 1 UNKNOWN oracle@lap.lambda.asia (LG01) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_lg01_9338.trc 1 772532 949716 0 949716 0 00000000CBB52F00 18 9340 9340 9340 PROCESS RECO oracle 1 UNKNOWN oracle@lap.lambda.asia (RECO) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_reco_9340.trc 1 941492 1474004 196608 1474004 0 00000000CBB53A28 19 9342 9342 9342 PROCESS LREG oracle 1 UNKNOWN oracle@lap.lambda.asia (LREG) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_lreg_9342.trc 1 825228 949716 0 949716 0 00000000CBB54550 20 9344 9344 9344 PROCESS PXMN oracle 1 UNKNOWN oracle@lap.lambda.asia (PXMN) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_pxmn_9344.trc 1 775604 949716 0 949716 0 00000000CBB55078 21 9346 9346 9346 PROCESS MMON oracle 1 UNKNOWN oracle@lap.lambda.asia (MMON) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_mmon_9346.trc 1 3257188 15310468 3211264 15310468 0 00000000CBB55BA0 22 9348 9348 9348 PROCESS MMNL oracle 1 UNKNOWN oracle@lap.lambda.asia (MMNL) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_mmnl_9348.trc 1 790428 1146324 0 1146324 0 ADDR PID SOSID SPID STID EXECUTION_ PNAME USERNAME SERIAL# TERMINAL PROGRAM T TRACEFILE B LATCHWAIT LATCHSPIN PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM CON_ID ---------------- ---------- ------------------------ ------------------------ ------------------------ ---------- ----- --------------- ---------- ------------------------------ ------------------------------------------------ - --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - ---------------- ---------------- ------------ ------------- ---------------- ----------- ---------- 00000000CBB566C8 23 9350 9350 9350 PROCESS D000 oracle 1 UNKNOWN oracle@lap.lambda.asia (D000) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_d000_9350.trc 1258644 1408468 0 1408468 0 00000000CBB571F0 24 9352 9352 9352 PROCESS S000 oracle 1 UNKNOWN oracle@lap.lambda.asia (S000) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_s000_9352.trc 570916 687572 0 687572 0 00000000CBB57D18 25 9355 9355 9355 PROCESS oracle 1 UNKNOWN oracle@lap.lambda.asia (TNS V1-V3) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_9355.trc 3812028 6209332 1048576 11124532 1 00000000CBB58840 26 10486 10486 10486 PROCESS ARC0 oracle 2 UNKNOWN oracle@lap.lambda.asia (ARC0) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_arc0_10486.trc 1 27611492 30187140 0 30187140 0 00000000CBB59368 27 10488 10488 10488 PROCESS ARC1 oracle 2 UNKNOWN oracle@lap.lambda.asia (ARC1) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_arc1_10488.trc 1 27298748 30187140 0 30187140 0 00000000CBB59E90 28 10490 10490 10490 PROCESS ARC2 oracle 1 UNKNOWN oracle@lap.lambda.asia (ARC2) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_arc2_10490.trc 1 17986332 20749956 1114112 20749956 0 00000000CBB5A9B8 29 10492 10492 10492 PROCESS ARC3 oracle 1 UNKNOWN oracle@lap.lambda.asia (ARC3) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_arc3_10492.trc 1 27298748 30187140 0 30187140 0 00000000CBB5B4E0 30 10494 10494 10494 PROCESS TT00 oracle 1 UNKNOWN oracle@lap.lambda.asia (TT00) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_tt00_10494.trc 1 772460 949716 0 949716 0 00000000CBB5C008 31 10508 10508 10508 PROCESS SMCO oracle 2 UNKNOWN oracle@lap.lambda.asia (SMCO) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_smco_10508.trc 1 772364 949716 0 949716 0 00000000CBB5CB30 32 10510 10510 10510 PROCESS W000 oracle 2 UNKNOWN oracle@lap.lambda.asia (W000) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_w000_10510.trc 1 838772 1211860 0 1211860 0 00000000CBB5D658 33 10512 10512 10512 PROCESS W001 oracle 1 UNKNOWN oracle@lap.lambda.asia (W001) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_w001_10512.trc 1 893028 1146324 0 1146324 0 ADDR PID SOSID SPID STID EXECUTION_ PNAME USERNAME SERIAL# TERMINAL PROGRAM T TRACEFILE B LATCHWAIT LATCHSPIN PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM CON_ID ---------------- ---------- ------------------------ ------------------------ ------------------------ ---------- ----- --------------- ---------- ------------------------------ ------------------------------------------------ - --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - ---------------- ---------------- ------------ ------------- ---------------- ----------- ---------- 00000000CBB5E180 34 10522 10522 10522 PROCESS AQPC oracle 1 UNKNOWN oracle@lap.lambda.asia (AQPC) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_aqpc_10522.trc 1 778044 1080788 0 1080788 0 00000000CBB5ECA8 35 10589 10589 10589 PROCESS CJQ0 oracle 2 UNKNOWN oracle@lap.lambda.asia (CJQ0) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_cjq0_10589.trc 1 2251964 11640452 8257536 11640452 0 00000000CBB5F7D0 36 10534 10534 10534 PROCESS P000 oracle 1 UNKNOWN oracle@lap.lambda.asia (P000) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p000_10534.trc 758412 2063828 851968 10452436 0 00000000CBB602F8 37 10536 10536 10536 PROCESS P001 oracle 1 UNKNOWN oracle@lap.lambda.asia (P001) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p001_10536.trc 735308 1801684 786432 3898836 0 00000000CBB60E20 38 10540 10540 10540 PROCESS P002 oracle 1 UNKNOWN oracle@lap.lambda.asia (P002) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p002_10540.trc 758412 2325972 983040 5602772 0 00000000CBB61948 39 10542 10542 10542 PROCESS P003 oracle 1 UNKNOWN oracle@lap.lambda.asia (P003) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p003_10542.trc 739988 2194900 851968 5668308 0 00000000CBB62470 40 10544 10544 10544 PROCESS P004 oracle 1 UNKNOWN oracle@lap.lambda.asia (P004) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p004_10544.trc 710164 2129364 917504 3571156 0 00000000CBB62F98 41 10546 10546 10546 PROCESS P005 oracle 1 UNKNOWN oracle@lap.lambda.asia (P005) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p005_10546.trc 697732 2129364 983040 2522580 0 00000000CBB63AC0 42 10548 10548 10548 PROCESS P006 oracle 1 UNKNOWN oracle@lap.lambda.asia (P006) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p006_10548.trc 747932 1605076 0 1605076 0 00000000CBB645E8 43 10550 10550 10550 PROCESS P007 oracle 1 UNKNOWN oracle@lap.lambda.asia (P007) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p007_10550.trc 738884 1146324 0 1146324 0 00000000CBB65110 44 10553 10553 10553 PROCESS P008 oracle 1 UNKNOWN oracle@lap.lambda.asia (P008) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p008_10553.trc 562332 687572 0 687572 0 ADDR PID SOSID SPID STID EXECUTION_ PNAME USERNAME SERIAL# TERMINAL PROGRAM T TRACEFILE B LATCHWAIT LATCHSPIN PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM CON_ID ---------------- ---------- ------------------------ ------------------------ ------------------------ ---------- ----- --------------- ---------- ------------------------------ ------------------------------------------------ - --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - ---------------- ---------------- ------------ ------------- ---------------- ----------- ---------- 00000000CBB65C38 45 10555 10555 10555 PROCESS P009 oracle 1 UNKNOWN oracle@lap.lambda.asia (P009) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p009_10555.trc 562332 687572 0 687572 0 00000000CBB66760 46 10557 10557 10557 PROCESS P00A oracle 1 UNKNOWN oracle@lap.lambda.asia (P00A) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p00a_10557.trc 562332 687572 0 687572 0 00000000CBB67288 47 10560 10560 10560 PROCESS P00B oracle 1 UNKNOWN oracle@lap.lambda.asia (P00B) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p00b_10560.trc 562332 687572 0 687572 0 00000000CBB67DB0 48 10562 10562 10562 PROCESS P00C oracle 1 UNKNOWN oracle@lap.lambda.asia (P00C) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p00c_10562.trc 562332 687572 0 687572 0 00000000CBB688D8 49 10564 10564 10564 PROCESS P00D oracle 1 UNKNOWN oracle@lap.lambda.asia (P00D) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p00d_10564.trc 562332 687572 0 687572 0 00000000CBB69400 50 10566 10566 10566 PROCESS P00E oracle 1 UNKNOWN oracle@lap.lambda.asia (P00E) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p00e_10566.trc 562332 687572 0 687572 0 00000000CBB69F28 51 10569 10569 10569 PROCESS P00F oracle 1 UNKNOWN oracle@lap.lambda.asia (P00F) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p00f_10569.trc 562332 687572 0 687572 0 00000000CBB6AA50 52 10837 10837 10837 PROCESS QM02 oracle 5 UNKNOWN oracle@lap.lambda.asia (QM02) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_qm02_10837.trc 1 776828 949716 0 949716 0 00000000CBB6C0A0 54 19546 19546 19546 PROCESS P00H oracle 25 UNKNOWN oracle@lap.lambda.asia (P00H) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p00h_19546.trc 562332 687572 0 687572 0 00000000CBB6CBC8 55 10841 10841 10841 PROCESS Q002 oracle 2 UNKNOWN oracle@lap.lambda.asia (Q002) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_q002_10841.trc 1 1569100 2850260 1114112 2850260 0 00000000CBB6D6F0 56 10843 10843 10843 PROCESS Q003 oracle 2 UNKNOWN oracle@lap.lambda.asia (Q003) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_q003_10843.trc 1 900436 1015252 0 1015252 0 ADDR PID SOSID SPID STID EXECUTION_ PNAME USERNAME SERIAL# TERMINAL PROGRAM T TRACEFILE B LATCHWAIT LATCHSPIN PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM CON_ID ---------------- ---------- ------------------------ ------------------------ ------------------------ ---------- ----- --------------- ---------- ------------------------------ ------------------------------------------------ - --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - ---------------- ---------------- ------------ ------------- ---------------- ----------- ---------- 00000000CBB6E218 57 19544 19544 19544 PROCESS P00G oracle 17 UNKNOWN oracle@lap.lambda.asia (P00G) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p00g_19544.trc 562332 687572 0 687572 0 00000000CBB6ED40 58 19548 19548 19548 PROCESS P00I oracle 3 UNKNOWN oracle@lap.lambda.asia (P00I) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p00i_19548.trc 562332 687572 0 687572 0 00000000CBB6F868 59 19550 19550 19550 PROCESS P00J oracle 2 UNKNOWN oracle@lap.lambda.asia (P00J) /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p00j_19550.trc 562332 687572 0 687572 0 00000000CBB77320 70 10987 10987 10987 PROCESS oracle 1 UNKNOWN oracle@lap.lambda.asia /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10987.trc 2486284 3947316 1048576 32210388 1 59 rows selected.
| Process Name | Full Form | Description |
| SMON | System Monitor | System Monitor is main process, Responsible for mounts and open db and collates free space in data files |
| PMON | Process Monitor | Monitor Server and User Process and detect the Problem |
| DBWn | Data Base Writer | Database Writer sole job taking dirty block from the dirty list and write them to DISK where n start from 0 to 9 after that a to j |
| LGWR | Log Writer | Write Log buffer to redo log files on disk |
| CKPT | Check Point | The checkpoint process is responsible for initiating check points. A check point is when the system periodically dumps all the dirty buffers to disk. Most commonly, this occurs when the database receives a shutdown command. It also updates the data file headers and the control files with the check point information so the SMON know where to start recovery in the event of a system crash. |
| ARCn | Archiver Process | Up to 30 archiver processes (0–9, a–t) are responsible for copying filled redo logs to the archived redo storage area. If your database isn’t running in archive mode, this process shuts down. |
| CJQ0 | Job Queue Coordinator | Job Queue coordinator check for schedule tasks within the database. Job either from user or system. |
| J000 | Job Queue process | A job queue process slave actually runs the job. There can be up to 1,000 of them (000–999). |
| DIA0 | Diagnosabilty | The diagnosability process resolves deadlock situations and investigates hanging issues. |
| VKTM | Virtual Keeper of Time | The virtual keeper of time sounds like a fantasy game character but simply provides a time reference within the database. |
| LREG | Listener Registration | The listener registration process, which registers database instance and dispatcher information with the Oracle listener process. This allows incoming user connections to get from the listener to the database. |
| MMON | Manageability Monitor | The manageablity monitor process supports the Automatic Workload Repository (AWR) by capturing statistics, monitoring threasholds, and taking snapshots. This is related to performance tuning and troubleshooting. |
| MMNL | Manageablity Monitor Lite | The manageability monitor lite’s job is to write Active Session History (ASH) statistics from ASH buffer in the SGA to disk. This is related to performance tuning and troubleshooting. |
User and Server Process
The user processes are those client program which run at client machine to establish database connection from Oracle server like SQLDeveloper or SQLPLUS
The Server Process server and exist on database server. Server process are two type
1. Dedicated
2. Shared
Dedicated server process required lot of server resources like cpu and memory on the other hand shared process share resources among client process.
Storage Structures
- Data files and Temp files
- Control files
- Redo log files
- Archive log files
- Server Initialization Parameter files
Data Files and Temp Files
Data files in oracle are physical files which exist on disk. Oracle store data into it. Data files are binary in nature. You can not read them without starting an instance and executing queries.
Oracle Uses some logical structure to organize data files and store data into it . which are
- Tablespace
- Segment
- Extent
- Oracle Block
Data is stored in an organized format broken up into Oracle Blocks. Whenever server process reads data from database Oracle reads at least one Oracle block.
Oracle Block is different from OS Block. But it is good practice is
Oracle Block = OS Block
or
Oracle Block = even multiplication of OS Block
Best practice to keep Oracle data file extension .dbf but its not mandatory.
Temp files are also physical Files for temporary containing information. for ex- Temporary Tables, or Sorting table data etc.
DPV - v$datafile and v$tempfile
Control Files
Oracle Database Server get information about control file from SPFILE.
Its very important file, it gives Oracle Server Full Intelligence. You should keep several copies of Control Files.
These files can have any file extension but for best practice you should keep .ctl or .con extension.
It contains the following information.
- Names and locations of your data files and redo log files
- Recovery information
- Backup information
- Checkpoint information
- Archiving information
- Database name
- Log history
- Current logging information
- Host information
etc
Redo Log files
This is Binary Files you can't read this without Database. Redo log files store the information from Redo Log Buffer using LGWR Server process. Redo log files contains those SQL Statement Which produce Dirty Buffer. When Data File Get Corrupted we use REDO Log files to rebuild the database.
Best Practice - Extension .LOG or .RDO or you can keep anything you want.
Redo log files organize into the groups at Oracle DB Server have at least two Redo Log Groups.
Redo Log Files Full fill Durability of ACID property. IT contain necessary information to recover the lost data in database. Redo log files not record SELECT statement.
Because Redo Log and control file both responsible for producing data so we need both file multiplexed.
Multiplexed - CF + RF
Archive Log Files
Archive log is simply copies of redo log files there is no difference except one thing that it get the new name.
Best Practice - Extension .ARC, .ARCH, or .LOG.
By default Archiving not available. you should run database in archive mode. In case of data lost you recover from Failure.
Archiving increase server Overhead
- I/O Cost
- CPU cost
- Storage Cost
You should keep archive log files for recovery between each backup. Say you’re doing a backup every Sunday. Now say that your database loses files due to a disk failure on Wednesday. The recovery process would be restoring the lost files from the last backup and then telling Oracle to apply the archive log files from Sunday all the way up to the failure on Wednesday. It’s called rolling forward.
Like Control File redo log file you should keep two copies of archive log file at two different locations.
Server Initialization Parameter
Its smallest files of system. Its Two type PFILE and SPFILE
PFILE
This file just for Backward compatibility. Its old school file available in older version of Oracle. PFILE or Parameter File is text version of Initialization Parameter. You can read and edit this File using File editor like vim, gedit in linux. You can use PFILE but Changes made in this File need to start the DB server Its major draw back of using PFILE.
SPFILE
Spfile introduced in ORACLE 9i.
Spfile > pfile because :
can be backed up by RMAN
Shareable in RAC enviornment
Its Dynamic Server Parameter File, We can made changes using SQL alter command while server is running and changes are visible to Oracle Server.
Its is binary file used by Oracle Server Startup Process. you can't read SPFILE outside the database similar to registry File where you can't read registry outside the Registry Editor. You can create SPFILE from PFILE
Best Practice Extension - .ORA
Location of PFILE or SPFILE depends upon OS
/u01/app/oracle/product/12.1.0/dbhome_1/dbs/init.ora
You can make change in SPFILE file by using alter system set SQL query either in MEMORY or SPFILE or BOTH.
alter system set <parameter name>=<parameter value> scope= [Memory | Scope | Both ]
- Memory : Affect running instance only
- SPFILE : Update SPFILE, need restart. It permanent.
- BOTH : Affect both Memory as well as SPFILE.
Conversion
you can create pfile from spfile using SQL Query
SQL> create pfile='/u03/pfile19feb.ora' from spfile; File created.
SQL> create spfile from pfile='/u03/pfile19feb.ora';
META DATA about ORACLE
use v$parameter DPV
MUST know 30 basic parameter. advance parameter over 300. and Some Hidden control parameter
