Interview questions for Oracle DBA

1. What is Oracle?

Oracle is a company. Oracle is also a database server, which manages data in a very structured way. It allows users to store and retrieve related data in a multi-user environment so that the users can concurrently access the same data. All this is accomplished while delivering high performance. A database server also prevents unauthorized access and provides efficient solutions for failure recovery. A standby database is a database replica created by taking a backup of a primary database.

2. What is an Oracle Database?

Oracle provides software to create and manage the Oracle Database. The database consists of physical and logical structures in which system, user, and control information are stored. The software that manages the database is called the Oracle Database server. Collectively, the software that runs Oracle and the physical database is called the Oracle Database system.
A database buffer cache stores the data in memory for quicker access. The redo logs track and store all the changes made to the database. A Data Guard ensures data protection and high availability of data, and a control file records the physical structure of the database.

3. What is an Oracle index?

An index is an optional structure associated with a table to have direct access to rows that can be created to increase the performance of data retrieval. An index can be created on one or more columns of a table.

4. What are the different Oracle Database objects?

• Tables
• Views
• Indexes
• Synonyms
• Sequences
• Tablespaces

5. What is a Synonym in Oracle terminology?

A synonym which is also known as an alias is an identifier that can be used to reference another database object in a SQL statement. A table, view, sequence are the types of database objects that can be created for a synonym.

6. Explain the types of Synonyms?

There are two types of Synonyms which are- Public and Private.
A public synonym isn’t part of any schema. A public synonym is one that can be used by any database user.
A private synonym does belong to a specific schema. In other words, when only the owner can access it, it is called a private synonym.

7. How to check opatch applied or not in our Oracle home?

/opatch lsinventory 100 GB of database size while applying opatch it will take 30 mins, so 500 GB of database size how much time it will take to apply opatch. Same 30 Mins, We will apply the opatch for oracle binary only not for data.

8. What is sqlnet.ora ?

Answer: It is used to security of our database, Privileged IP address only able to access the database.

9. What are the different components of physical and logical database structure?

• physical: Datafile,Controlfiles, redologfiles.
• Logical: Database buffer cache, Redo log buffer cache,Shared pool,large pool

10. What is Database Writer (DBWR)?

It is responsible to writing data from database buffer cache into datafiles.

11. What is a listener process?

It is network connectivity, Application and sql developer tool to access database. Lietener.ora and Tnsnames.ora files are used.

12. What is the difference between SPFILE and PFILE?

• Spfile: Server parameter file of binary file, It contains all the information for setting the database initialization parameters. SPFILE permits dynamic changes without requiring you to restart that instance.
• Pfile: It is text parameter file, information for setting the database initialization parameters.

18. What is the purpose of collecting statistics?

It will help optimizer to generate best execution plan

19. What is an Oracle index?

It is an database object used to increase the performance for retrieval data.

20.  If the table is fragmented, how would you rebuild it?

Alter table tablename move, We can analyze the table.

21. What are the different tools that are provided by Oracle to assist performance monitoring?

AWR,ADDM,Trace,TKPROF,OEM

22. What is an explain plan and how does it help in optimizing the SQL Query?

We will check if query is using proper indexes are not. If not we will either create new indexes or can use hints to specify which indexes to use.

23. List four possible ways (direct or indirect) to execute an SQL query against an Oracle Database?

• Using the SQL*Plus command-line tool. With this tool, you can directly execute SQL commands.
• Using a GUI (Graphical User Interface) tool like SQL Developer. You can directly execute SQL commands with such tools.
• Using Oracle Enterprise Manager. This is an indirect way of executing an SQL query. When you perform certain operations with Oracle Enterprise Manager, they are converted to SQL queries implicitly and these SQL queries are executed against the database.
• Writing your own program. This is not a conventional way of executing your queries but actually, it is widely used. Any web or windows program that uses an Oracle database at the backend, executes SQL queries. These programs are written using a programming language like .NET or JAVA and they use a driver to connect to the database.

24. What Is SQL*Plus? How can one acquire it and what kind of operations can be performed with it?

• SQL*Plus is a command-line tool developed by Oracle Corporation.
• It is freely distributed. It is shipped with Oracle client installations or Oracle database installations as a default. So, if Oracle client or Oracle database software is installed on a computer, you can find it under the “$ORACLE_HOME/bin/” directory. The name of the executable is “surplus” on Linux systems and “sqlplus.exe” on Microsoft Window Systems.
• You can connect to an Oracle database with it. Once connected, you can execute Oracle commands or SQL queries against the connected database. SQL*Plus has also its own commands for formatting the output so that you can display the results in a meat way.

25. Who is responsible to update the indexes?

Oracle automatically maintains and uses indexes and when any change is made in the table data Oracle automatically distributes it into relevant indexes.

26. What are the types of backups in Oracle?

The main four types of backups in Oracle are:
(ⅰ) COLD Backup
(ⅱ) HOT Backup
(ⅲ) Import – Export Backup
(ⅳ) RMAN Backup

27. What are Hot Backup and Cold Backup?

Hot backup is also recognized as an Online Backup because here the backup is taken while the database is active and running. And when the backup can only happen while the database is in shut down mode then it is called Cold Backup which can also be identified as Offline Backup.