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

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.

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

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.

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

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

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

Oracle has the main theme of storing, creating and managing the database. For this, it has software i.e. Oracle Software which has the system of the physical and logical structure that contains the information of the user, control and much more. Oracle database server is the software that maintains the database. In a simple way, Oracle database system maintains and manages the Oracle and its physical database. Oracle solve its every quibble and try to keep it better. Like fast accessing in the database is possible now because of the functions like data buffer cache.

Moreover, it provides redo logs that take care of any change made in the database. More functions like Data Guard are present to protect the data and provide full security to the database. It also watches over the availability of data and also checks the file record. Data Guard also looks after the physical structure of the database.

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.

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.

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

  • Hot backup
  • Cold backup