Oracle 11g Architecture Interview Questions and Answers for Experienced - 1

Question: 1

What is the difference between a temporary tablespace and a permanent tablespace?

A temporary tablespace provides temporary storage during the processing of database function, such as sorting; whereas a permanent tablespace is used to store permanent database objects, such as tables, partitions, indexes and clusters.

You do not need to backup or restore a temporary tablespace.

Question: 2

What are the different types of memory structure available in Oracle?

System Global Area (SGA) and Program Global Area (PGA) are the two types of memory structure available in Oracle.

Question: 3

What is System Change Number (SCN)?

SCN is an ID that Oracle generates for every transaction. It is recorded with the corresponding change in a redo entry.

Question: 4

What is a listener process?

The listener or Transparent Network Substrate (TNS) listener is a server process that provides network connectivity to the Oracle database. The listener is configured to listen for connection requests on a specified port on the database server.

When an incoming request is received on the port, the listener attempts to resolve the request and forward the connection information to the appropriate database instance.

Question: 5

What is the job of the System Monitor (SMON) and Process Monitor (PMON) processes?

SMON helps in recovery at instance startup. It is responsible for cleaning up temporary segments and coalescing free extends.

SMON also performs failed instance recovery for other failed Real Application Clusters instances.

PMON performs process recovery when a user process fails. It is responsible for cleaning up the database buffer cache and freeing resources that the user process was using. For example, it resets the status of the active transaction table, release locks and removes the process ID from the list of active processes.

PMON periodically checks the status of dispatcher and serve processes, and restarts any process that have stopped running.

It also registers information about the instance and dispatcher processes with the network listener.

Similar to SMON, PMON checks regularly to see whether it is needed and can be called if another process detects the need for it.