Oracle Architecture Interview Question & Answers

1: What constitute an oracle instance?

SGA and oracle background processes constitute an oracle instance. 

(Or) 

Combination of memory structure and background process.

2: What is SGA?

The system global area is a shared memory region allocated by oracle that contains data and control information for one oracle instance.

3: What are the components of SGA?

Database buffers, redo log buffer, shared pool, cursors.

4: What do Database Buffers contain?

Database buffers store the most recently used blocks of database data.

It can also contain modified data that has not yet been permanently written to disk.

5: What do Redo Log Buffers contain?

Redo Log Buffer stores redo entries, a log of changes made to the database.

6: What is Shared Pool?

Shared Pool is a portion of the SGA that contains shared memory constructs such shared SQL areas.

7: What is Shared SQL Area?

A Shared SQL area is required to process every unique SQL statement submitted to a database and contains information such as the parse tree and execution plan for the corresponding statement.

8: What is cursor?

A cursor is a handle(a name or pointer) for the memory associated with a specific statement.

9: What is PGA?

Program global area is a memory buffer that contains data and control information for a server process.

10: What is User Process?

A user process is created and maintained to execute the software code of an application program.

It is a shadow process created automatically to facilitate communication between the

user and the server process.

11: What is server process?

Server process handle requests from connected user process.

A server process is in charge of communicating with the user process and interacting with oracle carry out requests of the associated user process.

12: What are the two types of server configurations?

Dedicated server configuration and multi-threaded server configuration.

13: What is dedicated server configuration?

In a dedicated server configuration a server process handles requests for a single user process.

14: What is a multi-threaded server configuration?

In a multi-threaded server configuration many user processes share a group of server process.

15: What is a parallel server option in oracle?

A configuration for loosely coupled systems where multiple instance share a single physical database is called parallel server.

16: Name the ORACLE Background Process ?

DBWR - Database Writer.

LGWR - Log Writer

CKPT - Check Point

SMON - System Monitor

PMON - Process Monitor

ARCH - Archiver

RECO - Recover

Dnnn - Dispatcher, and

LCKn - Lock

Snnn - Server

17: What Does DBWR do ?

Database writer writes modified blocks from the database buffer cache to the data files.

18: When Does DBWR write to the database ?

DBWR writes when more data needs to be read into the SGA and too few database buffers are free.

The least recently used data is written to the data files first.

DBWR also writes when CheckPoint occurs.

The DBWR writes the dirty buffers present in the database buffer cache to respective files.

(1)When a check point is issued.

(2)Every three seconds.

(3)When a log switch occurs.

(4)When dirty-blocks reaches to thresold value.

(5)When dirty-blocks are becoming LRU blocks.

(6)When database shuts down.

19: What does LGWR do ?

Log Writer (LGWR) writes redo log entries generated in the redo log buffer of the SGA to on-line Redo Log File.

20: When does LGWR write to the database?

LGWR writes redo log entries into an on-line redo log file when transactions commit and the log buffer files are full.

21: What is the function of checkpoint CKPT)?

The Checkpoint (CKPT) process is responsible for signaling DBWR at checkpoints and updating all the data files and control files of the database.

22: What are the functions of SMON ?

System Monitor (SMON) performs instance recovery at instance start-up.

In a multiple instance system (one that uses the Parallel Server), SMON of one instance can also perform instance recovery for other instance that have failed.

SMON also cleans up temporary segments that are no longer in use and recovers dead transactions skipped during crash and instance recovery because of file-read or off-line errors.

These transactions are eventually recovered by SMON when the tablespace or file is brought back on-line.

SMON also coalesces free extents within the database to make free space contiguous and easier to allocate.

23: What are functions of PMON ?

Process Monitor (PMON) performs process recovery when a user process fails.

PMON is responsible for cleaning up the cache and Freeing resources that the process was using.

PMON also checks on dispatcher and server processes and restarts them if they have failed.

24: What is the function of ARCH ?

Archiver (ARCH) copies the on-line redo log files to archival storage when they are full.

ARCH is active only when a database's redo log is used in ARCHIVELOG mode.

25: What is function of RECO ?

RECOver (RECO) is used to resolve distributed transactions that are pending due to a network or system failure in a distributed database.

At timed intervals, the local RECO attempts to connect to remote databases and automatically complete the commit or rollback of the local portion of any pending distributed transactions.

26: What is the function of Dispatcher (Dnnn) ?

Dispatcher (Dnnn) process is responsible for routing requests from connected user processes to available shared server processes and returning the responses back to the appropriate user processes.

27: How many Dispatcher Processes are created ?

Atleast one Dispatcher process is created for every communication protocol in use.

28: What is the function of Lock (LCKn) Process ?

Lock (LCKn) are used for inter-instance locking when the ORACLE Parallel Server option is used.

29: What is the maximum number of Lock Processes used ?

Though a single LCK process is sufficient for most Parallel Server Systems.

Upto Ten Locks (LCK0,....LCK9) are used for inter-instance locking.