This blog basically foundation of Oracle Database. It cover major concept and include following topics
and many more. At the end of this section i hope you clear most of your your doubt related to Oracle terminology.
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
- 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.
