Interview Questions and Answers on Oracle Architecture

Q. What do you mean by “Logical” and “Physical” storage in Oracle database system?

A. The storage architecture is that part of Oracle which holds the data – either system generated or user generated. Logical and Physical are only the two different points of views to look at this storage. Logical storage is the one as Oracle System perceives this storage i.e. set of objects that Oracle System considers to be holding the data. Physical Storage is the one as Operating System looks at the same storage i.e. set of objects that Operating System considers the Oracle data has been stored in.

Q. What are the components of Physical Storage in Oracle database system?

A. The components of the physical storage in Oracle database system consist of a set of files –

  • Control Files – The control files stores important synchronization information of all files of Oracle database. They are usually replicated preferably on different drives.
  • Data Files – They are several in number and hold the actual data of the database and specifically structured and formatted according to Oracle specification of block size.
  • Redo Log Files – The set of files which must at minimum exist in pair, but usually more. They store history of changes (called “Redo”) made to the database which is required to apply in case of recovery of the database.

Q. What are the components of Logical Storage in Oracle database system?

A. The components of Logical Storage in Oracle database system consist of set of logical objects –

  • Tablespaces – A tablespace is considered as group of datafiles, where units of data storage called segments are stored. A segment may spred over all the datafiles of the tablespace in the form of small chunks of space called extents which form space allocation units. The extents consist of several juxtaposed data blocks. There are five main types of segments –
    • Tables – The most important segments in the RDBMS which are supposed to hold the data
    • Indexes – The segments which are created on certain columns of the tables where values are arranged in sorted order with pointers to the location of the row in the table. They help in quick search of the data in the tables. The pointers to the location of the rows are called as “RowId”.
    • Clusters – A type of segment which may allow store the related tables together in the pre-joined form (in common set of blocks). They are suitable for such tables which are frequently accessed through join queries.
    • Temporary – The segments which are created temporarily by the system implicitly to support sorting operations required for some queries like those containing “Order By”, “Group By”, or “distinct” clauses.
    • Rollback – Also called as Undo segments, they hold the original version of the data for the duration of transaction (when data is undergoing change) to facilitate user to discard the change (rollback) and also for consistent read operation.
  • Schemas – The logical unit of storage which is related to set of objects owned by an user account. The schema belongs to a user. When a user creates an object the object is said to be added to user’s schema.

Q. What is the importance of a Control File?

A. The Control File in the Oracle database is considered as the most important of the files. It maintains state and status of each of the other file, particularly datafiles and redo log files in the Oracle database. Control file is the most important component of recovery operation in the Oracle database. It actually consists of copies of the headers of all the files of the Oracle database. Headers of the files contain a combination three numbers SCN (System Change Number), CPC (Check Point Counter) and LSN (Log Sequence Number). The difference in the combinations of these numbers in the actual headers of the files and their corresponding copy in the Control file point to some type of failure or de-synchronization of the file and hence helpful in signaling the recovery. File headers and obviously the copies of those headers in the control file also contains a fuzzy bit to indicate if the file is open, read only or closed (offline).

Q. My Oracle database contains only one Control File. Is it OK for operating the database?

A. While technically it is possible to run an Oracle database with a single copy of Control File, it is recommended that you must have the Control File multiplexed preferably on different physical hard drives. Control File is the most important file of the Oracle database and as it plays very crucial role in the recovery of the database a valid Control File is mandatory and hence multiplex control file.

Q. Why Oracle uses Tablespace for storing tables? OR Why not oracle use concept of one table per file like storage?

A. Tablespace may consist of more than one data file and each of those files belonging to a single tablespace may be physically located on different drives. There are two significant advantages of this –

  • A table may grow to huge size as it may span all the datafiles of the tablespace in which it exists. One table per file will restrict the size of the table to the maximum allowable size of the file as limited by OS.
  • A huge table may be read from different drives concurrently under the condition of multiuser access as the extents of the table may be lying in different data files of the tablespace which may be located on different drives. So it reduces read / write contention under multiple users accessing the same table concurrently.

Q. Explain Online and Offline Redo Log Files.

A. Oracle maintains the history of changes, called “Redo”, in set of files as the changes are made, they are called Online Redo Log Files. The “Redo” may be imagined to be stored in the form of commands which made those changes to the database and hence it may be used for the recovery of the database. However since the Online Redo Log Files are recyclable components i.e. they may be overwritten by new history as Oracle uses them in round robin fashion, the history captured by Online Redo Log Files will have to be preserved before being overwritten in separate files called as Offline Redo Log Files. They are also called as Archived Redo Log Files. However this preservation of Redo in Archived Redo Logs is optional and if In use the database is said to be running in Archivelog mode. All production databases are advised to run in this mode. The other mode is NoArchivelog mode in which Redo is not preserved; the mode may be used in case of non-production databases.