Oracle DBA Interview Questions

Oracle DBA Interview Questions

If you are preparing for your interviews and you want to gain more knowledge on Oracle Database, then we are here to provide you the finest collections of questions that are frequently asked in interviews. Give a look at the questions listed in this article and get enhanced your knowledge.

Download Oracle DBA Interview Questions PDF

Oracle DBA Interview Questions

To depreciate the redundancy and necessity, the tables and the fields of a correlated database are organized. This process is known as Normalization

It helps to conserve the space for storage and look after the regularity of the data There are six various normal forms which are given below.

First Normal Form -

It is a level of database normalization where only atomic values are present in the fundamental domains.

Second Normal Form -

It is a level of database normalization where it is in first normal form and each non-key trait is completely functionally reliant on the primary key.

Third Normal Form -

It is a level of database normalization where it is in 2nd normal form and each non-key trait is non-transitionally reliant on the primary key.

Boyce Codd Normal Form -

It is a level of database normalization where each determinant is a candidate key.

Fourth Normal Form -

It is a level of database normalization where no non-trivial multivalent dependencies other than any candidate key is present

Fifth Normal Form -

It also referred to the project-join normal form. It is a level of database normalization which is created to lessen redundancy.

SGA (System Global Area)PGA (Program or Process Global Area)
It is a memory area assigned at the time of an instance start up.It is a memory area that conserve the information regarding the user session.
It is distributed as 40% of RAM size.It is distributed only 10% of RAM size.
Cold backupHot backup
Cold backup occurs when the database is offline.Hot backup occurs when the database is online.
Cold backup is consistent backup.Hot backup is inconsistent backup.

Following is the list of the advantage of using RMAN.

  • It does not include tablespaces in the backup. As a result, it does not need more redo log file at the time of backup.
  • It detects the defective blocks.
  • It includes listing commands and built-in reporting.
  • It also helps in Parallelization of I/O operations.

It is used to track backups which are utilized by RMAN for the database.

You can also be used RMAN without consideration of recovery catalog.

It stored RMAN scripts which further used by RMAN to backup.

There are two types of backup available in the Oracle. These are: -

  • Hot backup
  • Cold backup

The following files are very important that should be backed up.

  • Control files
  • Database files
  • Password file
  • Archived log files
  • Parameter files such as SPFILE and PFILE

In the database, very important data is stored. To avoid the chances of any corruption or the future use of files it needs to keep them safe and that is done with the help of back up.

  • You can check the information regarding the control file in the initialization parameter file.
  • You can also the query v$controlfile that will show you the information regarding control file.

You can also perform SQL> show parameter control_files form SQL. This query gives you the information such as name, the location of the file in the disk.

You can make changes in the PFILE with the help of vi editor and control_files provide you with the information regarding the location and the name of the control files.

  • It is a twofold file that keeps the record of the bodily structure of the database
  • It holds the information regarding checkpoint, database name, timestamp, number of log files and their location
  • It is easy to find the control file location by using the CONTROL_FILE parameter as it holds its information.
  • It is better to make multiple numbers of the control file to avoid the problem of the corrupted file.
  • With the help of the control file, you can withdraw the failure's risk.
  • Oracle 9i or higher version provides SPFILE.
  • SPFILE's parameters are changed vigorously.
  • It is not possible to make any changes to PFILE at the time of database is up.
  • It is not possible for RMAN to backup PFILE while possible to backup SPFILE.
  • Typing errors in SPFILE are diminished because the file is checked before its submission and it is managed by the servers of Oracle.

For every transaction, a unique id is generated by the Oracle. This unique ID is referred to as SCN.

It is recorded whenever a change occurs in redo entry.

On every checkpoint, SCN is generated.

The numbers are updated for every 3 seconds.

Oracle Grid Architecture is the factory of a great number of servers, storage, networks, computing resource at an instance for enterprise computing. It looks after the demands of resources and supply. Grid computing uses advanced technology that helps the users to share the resources over multiple servers. On the demand, it is possible to add or remove the data processing capacity. Resources are supplied in a location vigorously. Different web services can also combine to build a fresh business.

- SGA stands for System Global Area which is helpful to conserve the shared information all over the database users. It comprises of Data Dictionary cache, Library cache, Database Buffer Cache, Shared Pool, Redo log Buffer cache.

  • Library Cache - Library cache is employed to deposit statements of Oracle
  • Data Dictionary Cache - Data Dictionary Cache comprises of the key of Database objects and rights that are given to users.
  • Data Base buffer cache - Data Base buffer cache collects the information regarding the data blocks which are usually accessed. holds copies of data blocks by this, it is easy to regained quickly for the future requests.
  • Redo log buffer cache - Redo log buffer cache look after the records of the changes that are made in the data files.

A database is the collection of three basic physical structure and these structures are data files, redo log files and control files which are stored in a disk whereas instance comprises an Oracle background process such as System MOnitor (SMON), Process MOnitor (PMON), Database writer (DBWR), Log WRiter (LGWR) and memory structure such as System Global Area (SGA), Program Global Area (PGA). These Oracle background Processes share a common memory area while running on a computer.

Another important difference between the database and an instance is that an instance is able to install and open only a single database whereas a database can be installed or opened by more than instance with the help of Real Application Cluster (RAC).