What are Database storage Architecture files in Oracle?

     1.      Control file

-         Contains the data about the database it self

-         Physical structure of the database and other metadata about the database

-         Without this the data files are unable to open

     2.      Data Files

-         Contains the data related to the user or application data

-         Contains data dictionary and other metadata

     3.      Online Redo Log file

-         Useful to recover the database in case of any crash or other issues

-         It will be used to recover when the data files are not lost and able to access

     4.      Password Files

-         Allows users having sysasm, sysdba, sysoper to connect to the database instance remotely and perform administrative tasks

     5.      Parameter File

-         It contains the information about how the instance was configured when it is started

     6.      Archived redo log file

-         Contains series history of the data changes (Redo)  and useful to recover the data lost when we have the backup of the dB and these redo log files

     7.      Backup File

-         Is used to recover a database when an user accidentally deletes the dB or caused any damage to the database

     8.      Alert Log file and Trace files

-         Alert logs are special type of the trace files that logs each informational and error messages and Oracle recommends to review these logs periodically

-         Trace files are used trace out the process errors and are written by the server and BG processes when an error is occurred and the information about the error is written to these trace files and these intended to the DBA and other Oracle support team.

What are most common background processes in Oracle dB non-RAC?

     1.      Database Writer Process(DBwn)

-         Writes the dirty(modified) buffers from buffer cache to the disk

-         The DB_WRITER_PROCESSES initialization parameter specifies the number of DBWn processes

-         Additional processes DBW0 to DBW9 or DBWa to DBWz can be configured based number of processors and our requirement to improve the Write process

-         Setting up this parameter is not useful with servers having uniprocessors

-         Usually, this parameter is maintained by Oracle automatically based on number of processors and processor groups unless specified by the DBA.

-         This writer process writes buffers to disk and advance the check point (CKPT).

     2.      Log writer Process(LGWR)

-         This is responsible for redo log management to write the redo buffers to the redo log file in the disk.

-         Occurs when a user process commits a transaction

-         Occurs when the redo buffer is one-third full

-         Occurs every 3 seconds

-         Just before DBWn process writes buffers to the disk, if necessary

-         Useful to recover the dB from unexpected issues

    3.      Checkpoint Process(CKPT)

-         A CHECKPOINT is a data structure that defines the system change number (SCN) in the Redo thread of a database.

-         A CKPTs are recorded in control file and all data file headers

    4.      System Monitor Processes(SMON)

-         SMON is used to recover the instance to the point when it started last time.

-         It is also used to clean the temp segments that are not in use

-         It also checks each process very often that if the process is needed or not.

    5.      Process Monitor Process(PMON)

-         PMON is mainly used to recover the process when it fails

-         It also releases the resources assigned to the process

-         It cleans up the buffer cache

-         It monitors Idle sessions for time outs

-         It registers the listeners to the dB services

    6.      Recover Process(RECO)

-         RECO is a BG process used in the configuration of distributed databases configuration and is responsible to resolve the issues in in-doubt transactions.

-         It auto connects the databases involved in the distributed configuration and in case if it failed to connect the dB and it tries to connect with an intervals exponentially.

    7.      Job queue coordinator(CJQ0)

-         It is used to run the user created jobs and the jobs are run automatically based the schedule they were created and the jobs need to be run are selected from the JOB$ table ordered by job runtime

    8.      Job slave processor(Jnnn)

-         The coordinator process dynamically spawns job queue slave processes (Jnnn) to run the jobs

    9.      Archive processes(ARCn)

-         Archive process is to copy the redo log files to a standby storage after log switch is happened.

-         It exists only when the dB is in ARCHIVELOG mode and the dB is enabled for Archiving

-         We could increase the number of processes to archive in case of Bulk loads and so. It is flexible process

    10.   Queue Monitor Process(QMNn)

-         Used to manage Oracle streams advanced queuing

In addition, the other more BG processes can be seen in V$BGPROCESS view.           

Select * from V$SESSION where type ='BACKGROUND'; Or
Select * from V$BGPROCESS;

What Types of processes that run in Oracle?

   1.      User Processes

-        Created when an application or tool code needs to run

   2.      Server Processes

-        Created by oracle to execute a user process

   3.      Background processes(BG)

-        Some of the BG processes are started when the server started and other as per required.

   4.      Oracle Daemons and application processes not limited to the specific dB

-        The grid infrastructure and networking processes fall into this category, these are server level.