Oracle DBA interview Questions

Are you planning to give interview for “Oracle DBA”?
Oracle is one of the market leader in “Databases” segment.
check out below article on Oracle DBA Interview Questions for Experienced or Freshers.
Test your knowledge with Advanced Oracle DBA Interview Questions 2018 that helps
you to judge your readiness for the Oracle DBA Interview.

Oracle DBA interview Questions

1.What are dedicated and shared server process?

Server Configuration:

• Dedicated server process
• Shared server process

The server process created on behalf of each user process is called a dedicated server process (or shadow process).

Shared server architecture removes the need for a dedicated server process for each connection.

A dispatcher routes multiple incoming network session requests to a pool of shared server processes.

An idle shared server process from a pool of shared server processes picks up a request from a common queue, which does mean a small number of shared servers can do the same amount of processing as many dedicated servers.

2.What is Archiver Process?

Ans: It copies redo log files to a designated storage device after a log switch has occurred. The database has to be in ARCHIVELOG mode, and automatic archiving should be enabled.

An Oracle instance can have up to 10 Archiver processes (ARC0 to ARC9). The LGWR process starts a new ARCn process whenever the current number of Archiver processes is insufficient to handle the workload

3.What is Recoverer Process?

Ans: The re-coverer process (RECO) is a background process used with the distributed database configuration that automatically resolves failures. The RECO process of a node automatically connects to other databases involved in an in- doubt distributed transaction.

4.What is Process Monitor (PMON)?

Ans:It performs process recovery when a user process fails. Process Monitor is responsible for cleaning up the database buffer cache and reclaiming resources that the user process was using. Process Monitor also registers information about the instance and dispatcher processes with the network listener.

5.What is System Monitor (SMON)?

Ans:

• It performs recovery, if necessary, at instance startup.

• If any terminated transactions were skipped during instance recovery due to file-read or offline errors, System Monitor Process recovers them when the tablespace or file is brought back online.

• With Application Clusters, the System Monitor process of one instance can perform instance recovery for a failed CPU or instance.

6.What is Log Writer Process?

Ans: It is responsible for writing the redo log buffer to a redo log file on disk. Log Writer writes all redo entries in the buffer to the disk since the last time it wrote.

7.What is Database Writer Process?

Ans: It writes the contents of buffers to data files. The DBW(n) process is responsible for writing modified buffers in the database buffer cache to disk.

8.What are the background processes in an oracle instance?

Ans:

The background processes in an Oracle instance can include the following:

• Database Writer Process (DBWn)

• Log Writer Process (LGWR)

• Checkpoint Process (CKPT)

• System Monitor Process (SMON)

• Process Monitor Process (PMON)

• Recoverer Process (RECO)

• Job Queue Processes

• Achiever Processes (ARCn)

• Queue Monitor Processes (QMNn)

9. What are server processes in oracle?

Ans : Oracle creates server processes to serve user processes requests, connected to the instance. In some situations, when the application and Oracle are on the same system, it is possible to combine the user process and corresponding server process into one process for reducing system overhead. However, when the application and Oracle operate on different computers, a user process always talks to Oracle through a separate server process.

10.What is PGA?

Ans: A Program Global Area (PGA) is a memory region that contains data and control information for a server process. It is a memory created by Oracle when a server process is started and it is not shared. Access to it is exclusive to that server process and is read and written by Oracle code.

The total PGA memory allocated by each server process associated to an Oracle instance is also known as aggregated PGA memory allocated by the instance.

Oracle DBA Interview Questions

11.What are the contents of PGA?

• Ans: Private SQL Area: A private SQL area contains information such as bind information and runtime memory structures.

• Session memory: The memory assigned to hold logon information and other session information. The session memory is shared and not private for a shared server.

• SQL Work Areas: For complex queries such as decision-support queries, a big portion of the runtime area is dedicated to work areas allocated by memory- intensive operators.

12.What is Shared Pool?

Ans: The Shared Pool part of the System Global Area contains the library cache, the dictionary cache, buffers for parallel execution messages, and control structures.

13.What is Redo Log Buffer?

Ans:The Redo Log Buffer is a circular buffer in the System Global Area that holds information about changes made to the database.

Redo entries contain the information to reconstruct, or redo, changes made to the database by INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP operations.

Redo entries are used for database recovery, if necessary.

14.What are Database Buffers?

Ans: It is the portion of the System Global Area that holds copies of data blocks read from datafiles. All user processes are connected to the instance concurrently and share access to the database buffer cache.

Oracle DBA interview Questions 15.Explain oracle memory structures?

The oracle database server is comprised of database and instance. When an oracle database is started, a system global area (SGA) is assigned memory and oracle background processes are started. Oracle instance is combination of background processes and memory buffers.

All memory structures exist in the main memory of the computers that constitute the database. Two basic memory structures are there in Oracle database, the system global area (SGA), and the program global area (PGA).

Oracle DBA interview Questions16.What is SGA?

A system global area (SGA) is a set of shared memory structures that contain data and control information for one database instance.

When many users are connected to the same instance at the same time, then the data in the instance’s System Global Area is shared among the users. SGA is sometimes called as shared global area.

Oracle DBA interview Questions 17.What data structures does SGA contain?

Ans:The SGA contains the following data structures:

• Database buffer cache

• Redo log buffer

• Shared pool

• Java pool

• Large pool (optional)

• Streams pool

• Data dictionary cache

Other miscellaneous information

Oracle DBA interview Questions 18.Explain oracle grid?

Ans: Grid computing is an approach where independent hardware and software components can be connected and rejoined on demand to meet the changing needs of businesses.

19.What is a SYSTEM table space?

Ans: When the database is created in Oracle database system, it automatically generate a SYSTEM named SYSTEM table space. The SYSTEM table space contains data dictionary tables for the entire database.

20.What are the components of physical database structure in oracle?

Ans:

•  One or more data files.
•  One or more redo log files.
• One or more control files.

21.Name a few logical database structures in oracle?

Ans:

• Tablespaces
• Database’s schema objects