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.

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.

DBA stands for the Database administrator. DBA has the proper rights that can add or remove new users. It can also modify the conditions or opportunities of the user.

Some of its roles in the database are: -

• It is effective in the maintenance of database storage
• It also helps in the administration of users and looks after the security.
• It maintains the scheme objects
• It controls and maintains database performance.
• It also helps to do backup and restoration
• It also watches for the program and mechanizes work.

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.

Here is the list of the tools that help the worker in administering the database.

-Oracle Universal Installer (OUI)

The Oracle Universal Installer is great to install for the Oracle software and many other options. It installs the database as it launched the database configuration Assistant all by itself.

-Database Configuration Assistant (DBCA)

It is significant to build a database from the database. It can be supplied by the Oracle or you can create it by self. It saves a lot of your precious time and effort managing and creating a database from scratch by replication of preconfigured seed database.

-Database Upgrade Assistant

It is important to keep your database up-to-date. It looks after the update of your present database.

-Oracle Net Manager

It guides you regarding the network of Oracle.

-Oracle Enterprise Manager

It is a very important tool of the Oracle as it manages and maintains the database. It is a web-based linkage. You are able to use Oracle Enterprise Manager as a single entity to maintain your database after a lot of hard work in creating or upgrading the database, design of the Oracle network etc. It is also valuable for the performance advisor as it provides an interface. Moreover, it also provides an interface like SQL* Loader for the Oracle services.

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.

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

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

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.

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

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

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

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.

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

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

• Hot backup
• Cold backup

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.

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.

Difference between cold backup and hot backup