Architecture - 3 of Oracle database- Best Interview Q & A for all

3. Architecture - 3

1. Which background process write dirty blocks from database buffer cache to   data files?

  Database writer (DBWR)

2. Which background process writes data from log buffer to redo log files?      

    Log writer (LGWR)

3. Which background process performs Crash recovery?
   SMON (system monitor)

4. What is Log Switch?
   The point at which ORACLE ends writing to one online redo log file and begins writing to another is called a log switch.

5. What is On-line Redo Log?
   The On-line Redo Log is a set of two or more on-line redo files that record all committed changes made to the database. Whenever a transaction is committed, the   corresponding redo entries temporarily stores in redo log buffers of the SGA are   written to an on-line redo log file by the background process LGWR. The on-line redo log files are used in cyclical fashion. 

6. What are the steps involved in Instance Recovery?

  Rolling forward to recover data that has not been recorded in data files yet has      been recorded in the on-line redo log, including the contents of rollback segments. Rolling back transactions that have been explicitly rolled back or have  not been committed as indicated by the rollback segments regenerated in step a. Releasing any resources (locks) held      by transactions in process at the time of the failure. Resolving any Pending distributed transactions undergoing a two-phase commit at the time of the instance failure.

7. What does COMMIT do?

 COMMIT makes permanent the changes resulting from all SQL statements in the transaction. The changes made by  the SQL statements of a transaction become visible to other user sessions transactions that start only after transaction  is committed.

8. How do you know whether the process is Server Side Process?
 By seeing the process in ps-ef as ORACLE_SID.

9. When ckpt occurs?
     1. For every 3 seconds
     2. When 1/3rd of DB buffer fills
     3. When log switch occurs
     4. When database shuts down

10. What are the two steps involved in instance recovery?

  1. Roll forward (redofiles data to datafiles), 2.Roll backward (undo files to data files).

11. What is the use of SMON?

  SMON is an Oracle mandatory background process. It is used for Instance recovery.

12. List the Optional Flexible Architecture (OFA) of Oracle database? How can we organize the tablespaces in Oracle database to have maximum performance?

1.    SYSTEM - Data dictionary tables.

2.    DATA - Standard operational tables.

3.    DATA2- Static tables used for standard operations

4.    INDEXES - Indexes for Standard operational tables.

5.    INDEXES1 - Indexes of static tables used for standard operations.

6.    TOOLS - Tools table.

7.    TOOLS1 - Indexes for tools table.

8.    RBS - Standard Operations Rollback Segments,

9.    RBS1, RBS2 - Additional/Special Rollback segments.

10.  TEMP - Temporary purpose tablespace

11.  TEMP_USER - Temporary tablespace for users.

12.  USERS - User tablespace.

13. How do you know when the process is started?

  Using ps -ef grep process name

14. What is meant by redo log buffer?
Changes made to entries are written to the on-line redo log files. So that they can be used in roll forward operations during database recoveries. Before writing them into the redo log files, they will first brought to redo log buffers in SGA and LGWR will write into files frequently. LOG_BUFFER parameter will decide the size.