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.

  • 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 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
  • 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.

The important tasks of the Common Oracle DBA are mentioned below.

  • The primary task of Common Oracle DBA is the installment of Oracle software.
  • It also creates databases of Oracle.
  • It is efficient in upgrading the software and releasing new software.
  • It helps in starting and closing of the database.
  • It also maintains the database's storage building.
  • It manages users and gives security to the database.
  • It maintains the feature and design such as tables, views, and indexes.
  • If it is necessary it performs the action like recovery and database backups.
  • It prepared itself and monitored the database conditions. It takes the necessary action in case of any problematic issue.
  • In the small field of work a single entity can watch over the tasks but in an enterprising company, it can't be handled to a single person. Therefore, DBAs are hired to perform the tasks who have skills and are experts in their field.

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.

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

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 is a database company that stores the data. Oracle stores the data in a very definite way so that client can regain it in a multi-user condition. It is very efficient for the client to access the same data by a few users. It is done in very high performance. Not only this, database prevents the unauthorized access that provides the security to the clients. Moreover, it provides solutions for failure restoration. There is a need for copying that is created by the backup of the primary database which is known as a standby database.

  • 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.

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.

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.
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.