1). What is an Oracle Instance? 

An Oracle database server consists of an Oracle database and an Oracle instance. Every time a database is started, a system global area (SGA) is allocated and Oracle background processes are started. The combination of the background processes and memory buffers is called an Oracle instance. We can run multiple instances on the same Oracle Database Server, where each instance connects to its database.

Oracle instance includes:

SGA – System or Shared Global Area

Components of SGA:

  • DBBC – Database Buffer Cache
  • SP – Shared Pool; divided into Library Cache (LC) and Data Dictionary Cache (DDC) or Row Cache.
  • RLB – Redo log Buffer

Background Process (10/11g database):

Mandatory Processes

  • SMON – System Monitor
  • PMON – Process Monitor
  • DBWR – Database writer
  • LGWR – Log Writer
  • CKPT – Check point
  • RECO – Recoverer
  • DIAG – Diagnosability (new in 11g)
  • VKTM – Virtual keeper of time (keeps “SGA Time” variable in current, new in 11g)

Optional Process

  • ARCN – Archiver
  • MMAN – Memory Manager – ASMM
  • MMON – Memory Monitor
  • MMNL – Memory Monitor Light – AWR

and few more…

TIP: For a complete overview of Database 11g Architecture check out this poster: Database 11g Architecture Poster 

[2.74 MB]

List of running processes of a single instance (11g) on Linux:

[oracle@hostname ~]$ top -n 1 -U oracle -c

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

9181 oracle 15 0 745m 111m 109m S 6.9 7.5 1:11.15 ora_j000_DB1_SID

9163 oracle 16 0 745m 68m 65m S 5.3 4.6 0:11.95 ora_j001_DB1_SID

10420 oracle 18 0 744m 12m 11m R 3.0 0.8 0:00.09 ora_j002_DB1_SID

6773 oracle 16 0 748m 40m 34m S 0.7 2.7 0:03.16 ora_dbw0_DB1_SID

6775 oracle 16 0 759m 34m 33m S 0.7 2.4 0:10.74 ora_lgwr_DB1_SID

6767 oracle 15 0 744m 13m 11m S 0.3 0.9 0:02.17 ora_psp0_DB1_SID

6785 oracle 15 0 744m 19m 18m S 0.3 1.3 0:02.66 ora_mmnl_DB1_SID

6560 oracle 18 0 42048 9348 6788 S 0.0 0.6 0:00.86 tnslsnr LISTENER -inherit

6755 oracle 15 0 744m 16m 14m S 0.0 1.1 0:02.50 ora_pmon_DB1_SID

6757 oracle -2 0 744m 13m 11m S 0.0 0.9 0:04.31 ora_vktm_DB1_SID

6761 oracle 15 0 744m 13m 11m S 0.0 0.9 0:00.34 ora_gen0_DB1_SID

6763 oracle 18 0 744m 12m 11m S 0.0 0.9 0:00.53 ora_diag_DB1_SID

6765 oracle 15 0 744m 19m 18m S 0.0 1.3 0:00.59 ora_dbrm_DB1_SID

6769 oracle 18 0 744m 16m 14m S 0.0 1.1 0:07.11 ora_dia0_DB1_SID

6771 oracle 18 0 744m 17m 16m S 0.0 1.2 0:11.13 ora_mman_DB1_SID

6777 oracle 16 0 744m 16m 14m S 0.0 1.1 0:08.51 ora_ckpt_DB1_SID

6779 oracle 15 0 748m 87m 84m S 0.0 5.9 0:04.61 ora_smon_DB1_SID

6781 oracle 18 0 744m 18m 17m R 0.0 1.3 0:00.52 ora_reco_DB1_SID

6783 oracle 15 0 748m 56m 51m S 0.0 3.8 0:06.01 ora_mmon_DB1_SID

6787 oracle 15 0 744m 13m 11m S 0.0 0.9 0:00.35 ora_d000_DB1_SID

6789 oracle 15 0 744m 12m 11m S 0.0 0.8 0:00.31 ora_s000_DB1_SID

6852 oracle 18 0 744m 14m 13m S 0.0 1.0 0:00.40 ora_qmnc_DB1_SID

6859 oracle 15 0 744m 25m 23m S 0.0 1.7 0:00.53 ora_q000_DB1_SID

6864 oracle 18 0 744m 15m 14m S 0.0 1.0 0:00.21 ora_q001_DB1_SID

6983 oracle 15 0 748m 54m 48m S 0.0 3.7 0:05.40 ora_cjq0_DB1_SID

7141 oracle 15 0 744m 13m 12m S 0.0 0.9 0:00.26 ora_smco_DB1_SID

7722 oracle 16 0 753m 58m 55m S 0.0 4.0 0:07.64 oracleDB1_SID (LOCAL=NO)

10254 oracle 15 0 744m 14m 12m S 0.0 1.0 0:00.10 ora_w000_DB1_SID 

2). What information is stored in Control File ?

Oracle Database must have at least one control file.

It’s a binary file contains some of the following information:

  • The database name and unique ID
  • The timestamp of database creation
  • The names and locations of associated datafiles and redo log files
  • Tablespace information
  • Datafile offline ranges
  • Archived log information and history
  • Backup set and backup piece information
  • Backup datafile and redo log information
  • Datafile copy information
  • Log records: sequence numbers, SCN range in each log
  • RMAN Catalog
  • Database block corruption information

The location of the control files is specified through the control_files init param:

SYS@DB1_SID SQL>show parameter control_file;

NAME TYPE VALUE

———————————— ———– ——————————

control_file_record_keep_time integer 7

control_files string /u01/app/oracle/oradata/DB1_SID

/control01.ctl, /u01/app/oracle/flash_recovery_area/DB1_SID/control02.ctl 

3). When you start an Oracle DB which file is accessed first ? 

Oracle first opens and reads the initialization parameter file (init.ora)

[oracle@hostname ~]$ ls -la $ORACLE_HOME/dbs/initDB1_SID.ora

-rw-r–r– 1 oracle oinstall 1023 May 10 19:27 /u01/app/oracle/product/11.2.0/dbs/initDB1_SID.ora

4). What is the job of  SMON and PMON processes ? 

SMON – System Monitor Process – Performs recovery after instance failure, monitors temporary segments and extents; cleans temp segments, coalesces free space (mandatory process for DB and starts by default)

PMON – Process Monitor – Recovers failed process resources. In Shared Server architecture, monitors and retarts any failed dispatcher or server proceses (mandatory process for DB and starts by default) 

[oracle@hostname ~]$ ps -ef |grep -e pmon -e smon |grep -v grep

oracle 6755 1 0 12:59 ? 00:00:05 ora_pmon_DB1_SID

oracle 6779 1 0 12:59 ? 00:00:06 ora_smon_DB1_SID

5). What is Instance Recovery ?

While Oracle instance fails, Oracle performs an Instance Recovery when the associated database is being re-started.

Instance recovery occurs in two steps:

Cache recovery:

Changes being made to a database are recorded in the database buffer cache.

These changes are also recorded in online redo log files simultaneously. When there are enough data in the database buffer cache,they are written to data files.

If an Oracle instance fails before the data in the database buffer cache are written to data files, Oracle uses the data recorded in the online redo log files to recover the lost data when the associated database is re-started.

This process is called cache recovery.

Transaction recovery:

When a transaction modifies data in a database, the before image of the modified data is stored in an undo segment.

The data stored in the undo segment is used to restore the original values in case a transaction is rolled back.

At the time of an instance failure, the database may have uncommitted transactions. It is possible that changes made by these uncommitted transactions have gotten saved in data files.

To maintain read consistency, Oracle rolls back all uncommitted transactions when the associated database is re-started.

Oracle uses the undo data stored in undo segments to accomplish this.

This process is called transaction recovery.

6). What is being written into the Redo Log Files ? 

Redo log records all changes made in datafiles.

In the Oracle database, redo logs comprise files in a proprietary format which log a history of all changes made to the database. Each redo log file consists of redo records. A redo record, also called a redo entry, holds a group of change-vectors, each of which describes or represents a change made to a single block in the database.

Let’s get into this topic a little bit dipper:

Log writer (LGWR) writes redo log buffer contents Into Redo Log FIles. LGWR does this every three seconds, when the redo log buffer is 1/3 full and immediately before the Database Writer (DBWn) writes its changed buffers into the datafile. The redo log of a database consists of two or more redo log files. The database requires a minimum of two files to guarantee that one is always available for writing while the

other is being archived (if the DB is in ARCHIVELOG mode). LGWR writes to redo log files in a circular fashion. When the current redo log file fills, LGWR begins writing to the next available redo log file. When the last available redo log file is filled, LGWR returns to the first redo log file and writes to it, starting the cycle again. 

Filled redo log files are available to LGWR for reuse depending on whether archiving is enabled.

If archiving is disabled (the database is in NOARCHIVELOG mode), a filled redo log file is available after the changes recorded in it have been written to the datafiles.

If archiving is enabled (the database is in ARCHIVELOG mode), a filled redo log file is available to LGWR after the changes recorded in it have been written to the datafiles and the file has been archived.

Oracle Database uses only one redo log files at a time to store redo records written from the redo log buffer. The redo log file that LGWR is actively writing to is called the current redo log file. Redo log files that are required for instance recovery are called active redo log files. Redo log files that are no longer required for instance recovery are called inactive redo log files.

If the database is in ARCHIVELOG mode it cannot reuse or overwrite an active online log file until one of the archiver background processes (ARCn) has archived its contents.

If archiving is disabled (DB is in NOARCHIVELOG mode), then when the last redo log file is full, LGWR continues by overwriting the first available active file.

A log switch is the point at which the database stops writing to one redo log file and begins writing to another. Normally, a log switch occurs when the current redo log file is completely filled and writing must continue to the next redo log file. However, you can configure log switches to occur at regular intervals, regardless of whether the current redo log file is completely filled. You can also force log switches manually.

Oracle Database assigns each redo log file a new log sequence number every time a log switch occurs and LGWR begins writing to it.

When the database archives redo log files, the archived log retains its log sequence number.

7). How do you control number of Datafiles one can have in an Oracle database ? 

The db_files parameter is a “soft limit ” parameter that controls the maximum number of physical OS files that can map to an Oracle instance.

The maxdatafiles parameter is a different – “hard limit” parameter.

When issuing a “create database” command, the value specified for maxdatafiles is stored in Oracle control files and default value is 32.

The maximum number of database files can be set with the init parameter db_files.

8). How many Maximum Datafiles can there be in Oracle Database ?

Regardless of the setting of this paramter, maximum per database: 65533 (May be less on some operating systems)

Maximum number of datafiles per tablespace: OS dependent = usually 1022

Limited also by size of database blocks and by the DB_FILES initialization parameter for a particular instance

Bigfile tablespaces can contain only one file, but that file can have up to 4G blocks.

9). What is a Tablespace ?

A tablespace is a logical storage unit within an Oracle database.

Tablespace is not visible in the file system of the machine on which the database resides.

A tablespace, in turn, consists of at least one datafile which, in turn, are physically located in the file system of the server.

A datafile belongs to exactly one tablespace. Each table, index and so on that is stored in an Oracle database belongs to a tablespace.

The tablespace builds the bridge between the Oracle database and the filesystem in which the table’s or index’ data is stored.

There are three types of tablespaces in Oracle:

  • Permanent tablespaces
  • Undo tablespaces
  • Temporary tablespaces

10). What is the purpose of Redo Log files ? 

Before Oracle changes data in a datafile it writes these changes to the redo log.

If something happens to one of the datafiles, a backed up datafile can be restored and the redo, that was written since, replied, which brings the datafile to the state it had before it became unavailable.