Oracle Admin Interview Question and Answers

21. 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.
 
Your Name Your Email-ID
Your Answer
22. How do you control the maximum number of redo log files in a database?
  The maximum number of redo log files can be controlled by the parameter MAXLOGFILES.
 
Your Name Your Email-ID
Your Answer
23. What is the process of updating or inserting certain data in the database?
  Following is the process to update or insert data in the database:
  • RDBMS searches for parsed statement in library cache or parses the statement to generate execution plan.
  • Server process retrieves relevant data from disk to the buffer cache.
  • In case of inserting data in the database, the data block with sufficient free space will be retrieved.
  • Lock is acquired. The data block is updated in the buffer cache.
  • A lock is acquired on rollback segment as well to store old version of data in case rollback is required.
  • User process creates a redo entry in the redo log buffer to support recovery.
 
Your Name Your Email-ID
Your Answer
24. When does DBWR write to the data file?
  Following are the situations when DWBR writes to the data file.
  • When checkpoint occurs
  • When number of dirty blocks reaches a threshold
  • Every three seconds due to timeout
  • When server process needs free space in buffer cache to read new blocks.
 
Your Name Your Email-ID
Your Answer
12345678910 Page 6 of 10