Oracle DBA Interview Questions and Answers

CTA

Many organizations are hiring Oracle DBA experts to meet their requirements and the needs and demands of their customers. In case you are looking for Oracle DBA interview questions, you are at the right place. These Oracle Apps DBA interview questions and their answers were prepared by industry experts who have more than eight years of experience in Oracle DBA. Let’s start by looking at a few of the most frequently asked Oracle DBA interview questions.

Listed below are the two parts into which this Oracle DBA Interview Questions blog is divided:

1. Basic Interview Questions

2. Intermediate Interview Questions

Basic Oracle DBA Interview Questions for Freshers

1. Mention Oracle DBA characteristics.

• Main storage structures – Logical and physical
• Architecture – Grid architecture
• ACID compliance – Fully compliant
• Data stored as – Data blocks, extents, segments, and tablespaces

2. What is Oracle?

Oracle is a database server that manages data in a 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.

3. 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. The software that runs Oracle and the physical database is collectively 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.

Learn more about Oracle DBA from this Oracle DBA Tutorial!

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

5. Why is the index used?

Indexes are used for easy access to data. In addition, it is used to increase the performance of retrieval data. It helps to find the required data quickly without actually searching each row.

6. Who is responsible for updating the indexes?

Oracle is capable of maintaining and using indexes. It automatically distributes the data into relevant indexes when any change is made in the table data.

7. What are the various databases available in the market?

There are many databases available in the market. The commonly used databases are Oracle,

IBM db2, Microsoft SQL Server, Microsoft Access, MySQL and SQLite, PostgreSQL, MariaDB.

Intellipaat gives its learners industrial training in SQL Courses. Enroll now to get a professional certificate!

8. What are the physical components of the Oracle Database?

Five physical components are there in Oracle Database:

1. Data files
2. Control files
3. Redo log files
4. Password files
5. Parameter files

9. What are the roles of DBA?

A DBA has the authority to create new users, remove the existing users, or modify any of the environment variables or privileges assigned to other users.

• Manage database storage
• Administer users and security
• Manage schema objects
• Monitor and manage database performance
• Perform backup and recovery
• Schedule and automate jobs

Want to become a certified Oracle DBA Course? Enroll now!

10. What are the different Oracle Database objects?

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

11. What is a synonym in Oracle terminology?

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

12. Explain the types of synonyms.

There are two types of synonyms: 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.

If you want to become a professional MS SQL Server DBA expert. Learn and master this technology by enrolling in Intellipaat’s MS SQL Server DBA Course in Hyderabad.

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

14. What are hot backup and cold backup?

Hot backup is also recognized as an online backup because the backup happens while the database is active and running. When the backup can only happen while the database is in shutdown mode, it is called cold backup, which can also be identified as an offline backup.

15. What is the password file and why it is needed?

Database users’ passwords are stored in the database’s data dictionary. When a user attempts to log into the database, the user’s usernames and passwords are compared to the values contained in the database. The user is given database access only if the username and password match.  The data dictionary is stored in the database and can be accessed as long as the database is available. The dictionary also contains the passwords for the administrators.

The data dictionary would be unavailable until the database is locked. Since starting up a down database is one of the administrator’s jobs, there needs to be a way for them to log in even if the database is locked. Password file helps in this situation. A password file is an operating system file that is held on a separate disc from the database. It stores the username and password for users with SYSDBA or SYSOPER privileges. When the database is down,  administrators with certain privileges are authenticated using the password files.

16. What are Data Files?

Data files store and organize various types of information, including text, numbers, images, and more, within a computer system. Software applications and systems rely on these files to access and manage data effectively.

17. How do you switch from an init.ora file to a spfile?

To switch from an init.ora file to a spfile, we should create spfile from pfile command then shutdown instance and startup once again

18. What are the different types of SQL statements?

The five types of SQL statements are as follows:

1. Data Definition Language (DDL)
2. Data Manipulation Language (DML)
3. Transactional Control Language (TCL)
4. Session Control Language (SCL)
5. System Control Language (SysCL)

19. What is Data Normalization?

Data normalization is a process in database design that involves organizing and structuring data to minimize redundancy and dependency issues. It follows a set of rules and guidelines to ensure data integrity and improve database efficiency. The main objective of data normalization is to eliminate data duplication and anomalies by breaking down data into smaller, more manageable tables and establishing relationships between them.

20. What is a Control File?

A binary file that records the physical structure of the database and is required to start and run the database.

A control file contains information such as:

• Data file
• Database name
• Redo file name
• Locations of associated data files and redo files.
• Timestamp of database creation.
• Current log sequence number
• Checkpoint information

21. How will you recover a lost control file?

The steps to recover a lost control file:

1. Start the database in NOMOUNT mode.
2. Create the control file from the control file backup with the CREATE CONTROLFILE statement and place it in the correct location.
3. Mount the database.
4. Recover the database.
5. Open the database.

22. What do you mean by redo logo files?

The redo log’s main purpose is to keep track of all data changes. If a failure stops personalized data from being permanently written to data files, corrections may be made from the redo log, ensuring that the work is never wasted.

23. Define Parameter Files.

A parameter file is a text file that includes a list of initialization parameters as well as their values. Initialization parameters are defined in a parameter file that is exclusive to your installation.

24. What do you mean by Recovery Catalog?

The recovery catalog is a database scheme containing the metadata RMAN manages for data restoration and recovery processes. The metadata used by RMAN for reinstallation and healing process is stored in a recovery catalog. The RMAN metadata will be in the recovery catalog even if the target control file and all backups are lost.

25. Define System Tablespace and its significance.

When the database is created, the system tablespace is formed. Since this tablespace contains all of the data dictionary tables, it is used to create a number of database objects. For the database to run efficiently, the system tablespace must remain online.

26. How to find the database version?

To determine the version of an Oracle database, you can follow these steps:

• Connect to the Oracle database using a tool such as SQL*Plus.
• Once connected, enter the following query: “SELECT * FROM V$VERSION.”
• Execute the query to retrieve the version information of the database.
• The result will display details about the database version, including the Oracle release number and additional version-specific information.

27. What is a Sequence?

For numerical columns in database tables, a sequence produces a serial list of unique numbers. We may use the sequence on columns for data where we want to insert data systematically.

28. What is the definition of the table in Oracle?

In a database, the table is the first physical object. Oracle divides data into rows and columns using tables in a database. In a database, the table is the first physical object.

29. What do you mean by a view in SQL terminology?

A query is added to any view in order to define specific rows and columns of the table. The view is a type of virtual table. Read-only and read-write views are available types of views.

30. Explain the advantages of using view?

The advantages of using a view in the table are

• It is a subset of the data in a table.
• It stores complex queries.
• It can simplify multiple tables into one.
• It occupies very little space.
• It presents the data from different perspectives.

31. In Oracle terminology, what do you mean by tablespace?

A tablespace is a logical storage unit that groups similar logical structures. It is the logical structure that will organize all of the database’s objects.

32. How long does it take for the SYSTEM tablespace to be created?

Every database in Oracle has a tablespace called SYSTEM, which is generated automatically when the database is created. It also includes a data dictionary table for the entire set of data.

33. What is the relationship between tablespace and data files?

Each database has one or more tablespaces, each of which is divided into one or more data files.

34. How do we use the materialized view?

Materialized views are items that contain condensed sets of data from base tables that have been summarized, clustered, or aggregated. They are commonly used in data centers and decision-making systems.

35. What is the purpose of the SELECT statement?

The SELECT statement is used to retrieve a collection of unique values from a database table based on the conditions defined in a SQL query.

36. How can you compare a part of the name rather than the entire name?

To compare different parts, we use the LIKE operator, which functions as a database’s regex engine.

37. What is the keyword to get distinct records from a table?

The user will use SELECT DISTINCT to select distinct values from a database table.

38. In order to get sorted records from a table, what is the keyword?

The keyword “ORDER BY” is used to sort the data. It returns the sorted results to your program.

39. In order to get total records from a table, what is the keyword?

The COUNT keyword is used to find the total number of records in a table.

40. What is the definition of GROUP BY?

The keyword GROUP BY is an aggregate function like SUM, MULTIPLE, and so on, and without it, the sum for each individual group value cannot be determined.

41. What are the methods for shutting down an Oracle Database?

Oracle has several modes for shutting down the database:

1. Normal Mode:

In normal mode, the database is shut down by default. It can be used when no other clause is provided. The database waits for all currently linked users to detach from it before shutting it down, and no new connections are permitted once the statement is released. The command line is as follows:

SHUTDOWN NORMAL

2. Transactional Mode:

Transactional mode helps shut down the database while allowing it to complete the active transactions. In this process, no new connections are allowed, and this mode waits for all transactions to finish before shutting down the database. Use this command line to shut down the database:

SHUTDOWN TRANSACTIONAL

3. Immediate Mode:

When you know a power outage is at the door, you can use immediate mode. All sessions will be disconnected, all running transactions will be rolled back, the database will not wait for existing database users to disconnect before proceeding, and the database will be shut down. No instance recovery is needed during this next startup.

Issue the command SHUTDOWN with an IMMEDIATE clause to shut down a database immediately.

SHUTDOWN IMMEDIATE

4. Abort Mode:

When you want to shut down the database immediately within seconds without any active transactions, you can use the abort method. Hereafter, the statement has been released, and no new connections or transactions are permitted to be initiated. The Oracle Database automatically terminates all current client SQL statements and does not wait for existing database users to disconnect. Transactions that have not been committed will not be rolled back. All linked users are automatically disconnected from the database.

Use the command SHUTDOWN with the ABORT clause:

SHUTDOWN ABORT

Intermediate Oracle DBA Interview Questions for Experienced Professionals

42. What are the benefits of ORDBMS?

In ORDBMS, the objects can be stored as they are. The language of the DBMS can be integrated with an object-oriented programming language. The language may even be exactly the same as that used in the application, which does not force the programmer to have two representations of his objects.

43. What are the common Oracle DBA tasks?

As an Oracle DBA, we have to carry out the following tasks:

• Installing Oracle software
• Creating Oracle databases
• Performing upgrades of the database and software to new release levels
• Starting up and shutting down the database
• Managing the database’s storage structures
• Managing users and security
• Managing schema objects, such as tables, indexes, and views
• Making database backups and performing recovery when necessary
• Proactively monitoring the database’s health and taking preventive or corrective actions as required
• Monitoring and tuning performance

In a small-to-midsize database environment, a single DBA might be the sole person performing all these tasks. In large enterprise environments, the whole job is often divided among several DBAs, titled Database Security Administrator or Database Tuning Expert, each with his/her area of specialization.

Get ready for the industry with an Oracle DBA Certification now!

44. List out the tools for administering the database.

Following are some of the products, tools, and utilities we use to achieve our goals as database administrators:

• Oracle Universal Installer (OUI): The Oracle Universal Installer installs the Oracle software and options. It can automatically launch the Database Configuration Assistant to install a database.
• Database Configuration Assistant (DBCA): The Database Configuration Assistant creates a database from the templates that are supplied by Oracle, or we can create our own templates. In this case, it enables us to copy a preconfigured seed database, thus saving the time and effort of customizing and generating a database from scratch.
• Database Upgrade Assistant: This tool guides us through the upgrading of our existing database to a new Oracle release.
• Oracle Net Manager: This tool guides us through our Oracle network configuration.
• Oracle Enterprise Manager: The primary tool for managing our database is Oracle Enterprise Manager, a web-based interface. Once we install the Oracle software, create or upgrade a database, and configure the network, we can use Oracle Enterprise Manager as the single interface for managing our database. In addition, Oracle Enterprise Manager also provides an interface for performance advisors and an interface for Oracle utilities such as SQL*Loader and Recovery Manager.

45. Differentiate between a cluster and a grid.

Clustering is one technology used to create grid infrastructure. Simple clusters have static resources for specific applications owned by specific owners.

Grids, consisting of multiple clusters, are dynamic resource pools shareable among many applications and users. A grid does not assume that all servers are running the same set of applications. Applications can be scheduled and migrated across servers in the grid. Grids share resources with and among independent system owners.

At its highest level, the idea of grid computing is computing as a utility. In other words, we need not care where our data resides or which computer processes our request. We should be able to request information or computation and have it delivered as per our requirements. This is analogous to the way electric utilities work; without knowing where the generator is or how the electric grid is wired, we ask for electricity, and we get it. The goal is to make computing a utility, a commodity, and ubiquitous. Hence, it is called a grid. This perspective of utility computing is, of course, a ‘client-side’ view.

From the ‘server-side’ (or behind the scenes), the grid is about resource allocation, information sharing, and high availability. Resource allocation ensures that all those who request resources get what they need and that those resources are not standing idle while requests go unserviced. Information sharing makes sure that the information that users and applications need is available as and when it is requested for. High-availability features guarantee that all the data and computations are always available as a utility.

46. Explain the architecture of the Oracle Grid.

The Oracle Grid architecture pools large numbers of servers, storage, and networks into a flexible, on-demand computing resource for enterprise computing needs. The grid computing infrastructure continually analyzes the demand for resources and adjusts the supply accordingly.

For example, we can run different applications on a grid of several linked database servers. When reports are due at the end of the month, a database administrator can automatically provision more servers for that application to handle the increased demand.

Grid computing uses sophisticated workload management that makes it possible for applications to share resources across many servers. Data processing capacity can be added or removed on demand, and resources within a location can be dynamically provisioned. Web services can quickly integrate applications to create new business processes.

47. What are the tools you can use to start up an Oracle Database?

You can start up a database using three tools:

1. SQL *Plus: To startup an Oracle Database instance, you can use the SQL *Plus startup command.
2. Oracle Enterprise Manager: It is a system management tool. You can startup the Oracle Database with Oracle Enterprise Manager. It provides an integrated solution for managing your heterogeneous environment. Even if the database is stopped, you can still log in to OEM. It will present you with the Startup button by detecting the status of the down database.
3. Recovery Manager: RMAN is also known as the RMAN repository connected with the TARGET keyword, which is also a database on which RMAN performs backup and recovery operations in the control file of the database.

48. What would you specify in the script while creating a database with the SQL script?

An SQL script can also be used to build a database. The following will be included in the script:

• The database’s name
• The SYS user’s password
• The device user’s password
• At least three redo log classes are available online. In my view, each redo log group should have at least two members.
• The database’s character set and national character set
• SYSTEM and SYSAUX tablespace locations and sizes: These tablespaces will be used to store device information.
• As the database’s default tablespace, we will define a normal tablespace.
• Then specify a temporary tablespace to use as the database’s default temporary tablespace.
• Now we will build an undo tablespace.

49. Can you find out the indexes for a table in Oracle ?

This is the basic syntax that is used to find out the indexes for a table in Oracle.

SELECT owner, index_name,
tablespace_name, status
Set pages
Break on table_name on index_name
column table_name
column index_name
column column_name
select table_name, index_name, column_name
from dba_ind_columns
where table_owner
order by table_name, index_name
column_position

50. What is SQL*plus? What can be done with it?

SQL* Plus is a component of Oracle Database. It is basically a command-line tool that allows you to submit SQL queries to the server interactively.

• We can view the results by running a SELECT query.
• With SQL*Plus, we can startup and shut down an Oracle Database.
• Submit PL/SQL blocks for execution to the Oracle Server.
• Execution of SQL*Plus script file can be done
• Create, modify, or drop database objects (tables, indexes, and users) using DDL statements and any other SQL statements that Oracle supports.
• We can write the output to a file.
• Execute stored procedures and functions in a database

51. Can you explain each digit in the Oracle database version?

1. This first digit indicates the major database version and it describes the nature of release. Oracle releases a new release every four years on average.

For example, Oracle 9i (internet), Oracle 10g (grid), Oracle 11g (grid), and Oracle 12c (cloud)

2. The second digit represents the software’s maintenance release number. Oracle releases the major update as maintenance release 1 and then follows up with a second maintenance release later in the software’s lifecycle. With maintenance releases, new features are introduced to database software.

For example, Oracle 12c release 1 and Oracle 12c release 2

3. Fusion Middleware Number is the third digit. This will be 0 for database software.
4. This fourth digit is called component-specific release number, and a component release level is identified by the fourth digit. Depending on component patch sets or temporary releases, different components can have different numbers in this role.

Example:  12.1.0.1; 12.1.0.2; 12.2.1.1

5. A platform-specific release is identified by the fifth digit. This is usually a patch kit. This number will be the same across all affected platforms when different platforms need the same patch package.

Example: GA release (initial release-no patch): 12.2.0.1.0

52. What kind of information can be given while creating a sequence?

The syntax for creating a sequence is as follows:

CREATE SEQUENCE schema_name. sequence_name
[INCREMENT BY interval]
[START WITH first_number]
[MAXVALUE max_value | NOMAXVALUE]
[MINVALUE min_value | NOMINVALUE]
[CYCLE | NOCYCLE ]
[CACHE cache_size | NOCACHE]
[ORDER |NOORDER];

While creating a sequence, use the CREATE SEQUENCE command and add a sequence_name that should be unique. The next increment is used to show how much the sequence will increment at each move.

Then using START WITH, we start the sequence either in ascending or descending.

MAXVALUE and NOMAXVALUE are maximum limits. With MAXVALUE, we can provide the maximum sequence value, whereas NOMAXVALUE is fixed: 10^27 for an ascending sequence or -1 for a descending sequence.

MINVALUE is used to specify the minimum value of the sequence. NOMINVALUE is fixed. For an ascending sequence, it indicates a minimum value of 1, and for a descending sequence, it indicates -10^26.

To allow the sequence to generate values after it reaches the limit, use CYCLE, which is the minimum value for a descending sequence and MAXVALUE for an ascending sequence. NOCYCLE is the default behavior, and it is used to stop generating the next value when the sequence reaches the limit.

ORDER ensures that Oracle generates sequence numbers in the order they are requested. If you don’t want Oracle to generate sequence numbers in the order of your requests, use NOORDER. This is a default setting.

53. Explain how the database writer process (DBWn) works.

Multiple database context processes are possible. In the operating system, they are referred to as DBWn. This method is in charge of storing “dirty” buffers on the disc. When a server process has to update a data block, it first reads it from the disc into the buffer cache. If it isn’t already there, it updates the cache copy. So, a “dirty” block refers to a modified database block in the buffer cache.

54. What are the instance parameters that are used for configuring the shared server architecture?

DISPATCHERS: In the shared server architecture, configures dispatcher processes.

MAX _DISPATCHERS: The maximum number of dispatcher processes that can run at the same time is defined.

SHARED_SERVERS: There will be a minimum of shared server processes on the server. This number of shared servers is determined during the initialization process.

MAX_SHARED_SERVERS: The maximum number of shared server processes that can run at the same time is determined by this parameter.

SHARED_SERVER SESSIONS: This is the only required parameter for using shared servers, and it defines the maximum number of concurrent sessions that can be used for shared server connections.

CIRCUITS: The maximum number of virtual circuits that can exist in the system is determined by this parameter.

55. How to know when operations happened on a table?

In the database, FLASHBACK_TRANSACTION_QUERY shows all information about flashback transaction queries.

SELECT *
FROM flashback_transaction_query
WHERE TABLE_NAME
AND table_owner
and operation
order by start_timestamp desc

56. Give a few important views used in Oracle you have learned.

A few commands that can be used to view Oracle are as follows:

• V$Parameter
• V$Database
• V$Instance
• V$Datafiles
• V$Controlfiles
• V$Logfiles