Oracle DBA Interview Questions

1. What Is Oracle Database?

Ans: Oracle Database is a relational database management system (RDBMS) which is used to store and retrieve the large amounts of data. Oracle Database had physical and logical structures. Logical structures and physical structures are separated from each other.

2. Differentiate between Oracle DBA and Oracle Developer.

Ans: 

Oracle DBA:

• The key role of an Oracle DBA is about managing the Databases.
• It contains streamlined tasks.
• The working environment maintains the databases.
• It works on the backend database management area.
• Other tasks include Backup, recovery, server connectivity, etc.

Oracle Developer:

• The key role of Oracle Developer is to develop the code.
• It contains a wide range of tasks.
• The working environment develops databases.
• It works on the frontend development area.
• Other tasks include Coding, designing UI, etc

3. You want to find out how many users are defined in the password file and what privileges those users have. How would you accomplish this?

Ans: You can get the information about the existing users in the password file querying the “v$pwfile_users” view. 

Execute the SQL query below:

Sql> SELECT * FROM v$pwfile_users;

The query above will return four columns for each user in the password file.The column names are USERNAME,SYSDBA, SYSOPER and SYSASM.

1. The USERNAME column displays the username of the user in the password file
2. The SYSDBA column displays whether the user has SYSDBA privileges or not
3. The SYSOPER column displays whether the user has SYSOPER privileges or not
4. The SYSASM column displays whether the user has SYSASM privileges or not

Wish to make a career in the world of Oracle DBA ? Start with Oracle DBA training !

4. What are the roles of DBA?

Ans: 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. The roles of DBA include.

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

5. What are the common Oracle DBA tasks?

Ans: The following tasks are carried out by Oracle DBA.

• Installing Oracle software.
• Creating Oracle databases.
• Performing upgrades of the database and software to a new release level.
• 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.

6. Name the various Oracle database objects?

Ans: The various Oracle database objects are:

• Sequences.
• Views.
• Tables.
• Tablespaces.
• Indexes.

7. What are the contents of the control file?

Ans: Every Oracle database has a control file. A control file is a small binary file that records the physical structure of the database and includes:

1. The database name.
2. Names and locations of associated datafiles and online redo log files.
3. The timestamp of the database creation.
4. The current log sequence number.
5. Checkpoint information.

8. List out the tools for administering the database.

Ans: The following tools are the products, tools and utilities used for administering the database.

1. Oracle Universal Installer(OUI).
2. Database Configuration Assistant(DBCA).
3. Database Upgrade assistant.
4. Oracle Net Manager.
5. Oracle Enterprise Manager.

9. What are the dictionary tables used to monitor a database space?

Ans: The following are the dictionary tables used in monitoring a database's space.

• DBA_FREE_SPACE.
• DBA_SEGMENTS.
• DBA_DATA_FILES.

10. What are the differences between EBU and RMAN?

Ans: 

• Enterprise Backup Utility (EBU) is a functionally rich, high-performance interface for backing up Oracle7 databases. It is sometimes referred to as OEBU for Oracle Enterprise Backup Utility. 
• The Oracle Recovery Manager (RMAN) utility that ships with Oracle8 and above is similar to Oracle7's EBU utility. However, there is no direct upgrade path from EBU to RMAN.

11. What is the logical structure of the disk resources?

Ans: Following is the logical structure of the disk resources:

• Data block: The data block refers to the smallest logical storage unit. Size of a data block is a multiple of the operating system block size.
• Extent: It refers to the contiguous set of data blocks, which is allocated as a unit to a segment.
• Tablespace: The tablespace refers to the final logical storage unit. It is mapped to the physical datafile.
• Segment: The segment allocates a logical structure, such as a table. It is a set of extents, which are stored in the same tablespace.

12. How do you find the total database size in the database?

Ans: You can use the following database views to get the information on database size:

• Dba_segments: It provides information about the used space. You can take a total of all the bytes in the dba_segments view to get the used space.
• Dba_data__files: It provides information on space allocated to datafiles for permanent tablespaces.
• v$log: It provides information on redo log files.

 If you have any doubts on Oracle DBA, then get them clarified from Oracle DBA Industry experts on our Oracle DBA Tutorial!

13. What are the different levels of auditing?

Ans: There are three different levels of auditing.

1. Statement Auditing: It allows you to track who is issuing a specific type of statements. The AUDIT statement form of the AUDIT command allows you to specify any SQL statement to be audited.
2. Privilege Auditing: It lets you track the use of system privileges. In the AUDIT command, you can specify any system privilege. An audit record will be generated any time you exercise the specified system privilege. 
3. Object Auditing: It is the auditing of accesses to specific schema objects without regard to the user. It can audit all SELECT and DML statements permitted by object privileges, such as SELECT or DELETE statements on a particular table. 

14. Why is performance tuning a menacing area for DBA's?

Ans: The performance tuning field can't be automated like other features of exp/imp, backup recovery.This is an area that requires more detective work to be carried on part of application programmers and DBA's to check how some process is running slower than expected, why can't we scale applications to a larger number of users without problems like performance degradation etc. This is an area where the technical knowledge must be used along with constant experimentation and observation.

15. What are the dynamic performance views? Who has the access to these views?

Ans:

• Dynamic performance views are also called V$ views. These views provide information about the sessions. 
• Any Oracle user can get information from dynamic performance views if the user has to select any table privilege. This privilege is generally granted through the SELECT_CATALOG_ROLE role.

16. What is an ASM instance?

Ans: It is an instance that manages ASM disk groups. It comprises the System Global Area(SGA) and background processes. ASM instance mounts a disk group that is made available to the database instance. An ASM instance manages the metadata of a disk group and provides file layout information to the database instances.

Oracle Database Administrator Certification Training

• Master Your Craft
• Lifetime LMS & Faculty Access
• 24/7 online expert support
• Real-world & Project Based Learning

Explore Curriculum

17. What is an ASM Disk Group?

Ans: It is a group of disks that ASM manages as a unit. Within the disk groups, a file system interface maintains the Oracle database files. ASM simplifies database storage by consolidating disks into disk groups. This reduces I/O overhead.

18. Explain the procedure of restoring RMAN for the recovery if all the instances are down.

Ans: Following is the procedure of restoring RMAN for the recovery:

1. Bring all the nodes down.
2. Start a node.
3. Restore all the data files and archive logs.
4. Recover the node.
5. Open the database.
6. Bring other nodes up.
7. Confirm that all nodes are operational.

19. What is instance recovery?

Ans: 

• Instance recovery is used in Real Application Cluster (RAC) environments only.
• It occurs in an open database when one instance detects that another instance has crashed.

20. Which files must be backed up?

Ans: The following files must be backed up.

• Database files.
• Control files.
• Archived log files.
• Password files.
• INIT.ORA.

21. What is a backup set?

Ans: 

• A backup set is a logical grouping of backup files that are created when you issue an RMAN backup command.
• It is RMAN's name for a collection of files associated with a backup. A backup set is composed of one or more backup pieces.

If you have any doubts on Oracle DBA, then get them clarified from Oracle DBA Industry experts on our Oracle DBA Community !

22. What are the architectural components of RMAN?

Ans: Following are the architectural components of RMAN:

• RMAN executable.
• Server processes.
• Channels.
• Target database.
• Recovery catalog database.
• Media management layer.
• Backups, backup sets and backup pieces.

23. What does RMAN backup consist of?

Ans:

• RMAN backup consists of a backup of all or part of a database.
• This results from issuing an RMAN backup command.
• A backup consists of one or more backup sets.

24. What do you mean by recovery catalog?

Ans: A recovery catalogue is a database plan that holds the metadata used by RMAN for reinstallation and healing processes. It stores information on

• Data files & their backup file.
• Incarnation.
• Stored scripts.
• Backup history.
• Archived Redo Logs & their backup sets.

25. What are the uses of a database Resource Manager?

Ans:

1) The database resource manager enables us to limit the length of time a user session can stay idle and to automatically terminate long-running SQL statements and user sessions.

2) We can set initial login priorities for various consumer groups by using the database resource manager. 

3) We can specify the maximum number of concurrent active sessions for a consumer group by using the concept of active session pool. The Database resource manager will automatically queue all the subsequent requests until the currently running sessions complete.

26. What is a Resource Plan?

Ans: The resource plan lays how resource consumer groups will allocate the resources. Each resource plan contains a set of resource consumer groups that belong to this plan, together with instructions as to how resources are to be allocated among these groups.

For instance, a resource plan may dictate CPU resources be allocated among three resource consumer groups so that the first group gets 60 per cent and the remaining two groups get 20 per cent each of the total CPU time.

27. Which components of your database environment can be protected by an “Oracle Restart” configuration?

Ans:

• Database Instances and Automatic Storage Management (ASM): Database instances and ASM instances will be restarted if they crash somehow.
• Oracle NET Listener: Oracle NET Listener will be started if it crashes and stops listening for an incoming connection.
• ASM Disk Groups: Oracle Restart will mount ASM Disk groups if they are dismounted.
• Database Services: Non-default database services will be started by Oracle Restart feature.
• Oracle Notification Services (ONS): This is another Oracle component that can be protected by Oracle Restart.

28. Which tools can you use to start up an Oracle database?

Ans: You can start up a database with three tools.

• SQL*Plus: This is the most widely used option. You first connect to an idle instance with SQL*Plus and then startup the instance with the “startup” command.
• Oracle Enterprise Manager: This is another way of starting up a database. You can logon to Oracle Enterprise Manager even if the database is stopped. OEM will detect the status of the down database and will present you with a “Startup” button. You can startup the database by clicking this button.
• RMAN: This is rather a less used tool for starting up a database but it is possible to startup a database from the Recovery Manager command line.

29. What are the main aspects of oracle database security management?

Ans: 

• Controlling access to data(authorization).
• Restricting access to legitimate users (authentication).
• Ensuring accountability on part of the users(auditing).
• Safeguarding key data in the database(encryption).
• Managing the security of the entire organizational information structure (enterprise security).

30. How can you improve the performance of Sql*loader?

Ans: You can use direct path load to improve the performance. Indexes and constraints make inserts slow. Removing indexes and constraints improve performance of inserts; and therefore, of SQL*Loader.

Subscribe to our youtube channel to get new updates..!

Subscribe

31. What are the responsibilities of the database administrator’s tasks?

Ans:

• Installing and upgrading the Oracle server and application tools.
• Allocating system storage and planning future storage requirements for the database system.
• Creating primary database storage structures known as tablespaces after application developers have designed an application.
• Creating primary objects such as tables, views, indexes once application developers have designed an application.
• Modifying the database structure, as necessary, from information given by application developers.
• Enrolling users and maintaining system security.
• Ensuring compliance with your Oracle license agreement.
• Controlling and monitoring user access to the database.
• Monitoring and optimizing the performance of the database.
• Planning for backup and recovery of database information.
• Maintaining archived data on tape.
• Backing up and restoring the database.
• Contacting Oracle Corporation for technical support.

32. Explain Oracle Grid Architecture?

Ans: Grid computing is a information technology architecture that provides lower cost enterprise information systems. Using grid computing, independent hardware, and software components can be connected and rejoined on demand to meet the changing needs of businesses. It also enables the use of smaller individual hardware components

33. What Is The Difference Between Large Dedicated Server And Oracle Grid?

Ans: 

• Large dedicated server
• It has expensive costly components.
• High incremental costs.
• It has single point of failure.
• Enterprise service at higher cost.
• Oracle Grid
• It has low cost modular components.
• Low incremental costs.
• It has no single point of failure.
• Enterprise service at low cost.

34. What Are The Computing Components Of Oracle Grid?

Ans:

• The computing componenets of oracle grid are
• Oracle Enterprise Manager and Grid Control
• Oracle 10g Database and Real Application Clusters.
• ASM Storage Grid

35. What Is Recoverer Process?

Ans: The re-coverer process (RECO) is a background process used with the distributed database configuration that automatically resolves failures. The RECO process of a node automatically connects to other databases involved in an in- doubt distributed transaction.

36. What Are Job Queue Processes?

Ans: Job queue processes are used for processing batch. They run user jobs. They can be viewed as a scheduler service that can be used to schedule jobs like PL/SQL statements or procedures on an Oracle instance.

37. What Are Archiver Processes?

Ans: It copies redo log files to a designated storage device after a log switch has occurred. Archiver processes are there only when the database is in ARCHIVELOG mode, and automatic archiving is enabled. An Oracle instance can have up to 10 Archi ver processes (ARC0 to ARC9). The LGWR process starts a new ARCn process whenever the current number of Archiver processes is insufficient to handle the workload.

38. What Is Rollback Segment ?

Ans: Database contain one or more Rollback Segments to roll back transactions and data recovery.

39. What Is A Segment?

Ans: A segment is a group of extents, each of which has been allocated for a specific data structure and all of which are stored in the same table-space

40. What Is A Synonyms?

Ans: 

A synonym is a database object that serves the following purposes:

• Provides an alternative name for another database object, referred to as the base object, that can exist on a local or remote server.

• Provides a layer of abstraction that protects a client application from changes made to the name or location of the base object.

41. What Is Server Virtualization?

Ans: Oracle Real Application Clusters 10g (RAC) enables a single database to run across multiple clustered nodes in a grid, pooling the processing resources of several standard machines.

42. What Is Storage Virtualization?

Ans: The Oracle Automatic Storage Management (ASM) is a feature of Oracle Database 10g which provides a virtual layer between the database and storage so that group of disks can be treated as a single disk group and disks can be dynamically added or removed while keeping databases online.

43. What Is Grid Management Feature?

Ans: The Grid Management feature of Oracle Enterprise Manager 10g provides a single console to manage multiple systems together as a logical group.

44. What Is An Extent?

45. What Are Data Blocks?

Ans: Oracle stores data in data blocks also called as logical blocks, Oracle blocks or pages. A data block represents specific number of bytes of space on disk.

46. When Oracle Allocates An Sga?

Ans: When Oracle starts, it reads the initialization parameter file to determine the values of initialization parameters. After this, it allocates an SGA and creates background processes.

47. What Is An Oracle Instance?

Ans: When you start, the database instance comes into picture into system memory. Combination of the SGA and the Oracle processes is called an Oracle instance.

48. What Are The Several Tools For Interacting With The Oracle Database Using Sql?

Ans:

• There are several tools for interfacing with the database using SQL
• Oracle SQL*Plus and iSQL*Plus
• Oracle Forms, Reports, and Discoverer
• Oracle Enterprise Manager
• Third-party tools

49. How Views Are Used?

Ans: 

• It provides security by restricting access to a predetermined set of rows or columns of a table. It hides data complexity. It simplifies statements for the user.
• An example would be the views, which allow users to select data from multiple tables without actually knowing how to perform a join.
• It presents the data in a different perspective from that of the base table. It isolate applications from changes in definitions of base tables. It saves complex queries.

50. How Oracle Works?

Ans:

• An instance has started on the database server.
• A client established a connection to the server, using the proper Oracle Net Services driver.
• The server creates a dedicated server process on behalf of the user process.
• The user executes SQL statement and commits the transaction.
• The server process receives the statement and checks for any shared SQL area that contains a similar SQL.
• The server process retrieves data from datafile (table) or SGA.
• The server process modifies data in the SGA area. The DBWn process writes modified blocks permanently to disk. The LGWR process records the transaction in the redo log file.
• The server process sends a message to the application.

51. What Contains Oracle Physical Database Structure?

Ans: 

• It contains
• Datafiles
• Control Files
• Redo Log Files
• Archive Log Files
• Parameter Files
• Alert and Trace Log Files
• Backup Files

52. What Is A Tablespace

Ans: Oracle use Tablespace for logical data Storage. Physically, data will get stored in Datafiles. Datafiles will be connected to tablespace. A tablespace can have multiple datafiles. A tablespace can have objects from different schema's and a schema can have multiple tablespace's. Database creates "SYSTEM tablespace" by default during database creation. It contains read only data dictionary tables which contains the information about the database.

53. What Is A Dimension?

Ans: A dimension is hierarchical relationships between pairs of columns or column sets. Each value at the child level is tied with one value at the parent level.A dimension is a container of logical relationships between columns and it does not contain any data.

54. Explain Sequence Generator In Oracle?

Ans: The sequence generator gives a sequential series of numbers.The sequence generator is especially useful for generating unique sequential numbers. Sequence numbers are Oracle integers of up to 38 digits defined in the database.A sequence definition provides information, such as

• The sequence name
• ascending or descending sequence
• The interval between numbers
• Whether Oracle should cache sequence numbers in memory

Sequence numbers are generated independent of any tables. The same sequence generator can be used for many tables. Sequence number generation can be used to produce primary keys for your data automatically. Oracle stores the definitions of all sequences for a particular database as rows in data dictionary table in the SYSTEM table- space.

55. What Is A Control File?

Ans: Control file is a binary file which stores Database name, associated data files, redo files, DB creation time and current log sequence number. Without control file database cannot be started and can hamper data recovery.

56.What Is An Index?

Ans: Indexes are structures associated with tables and clusters. You can create indexes on one or more columns of a table to enhance the speed of SQL statement execution on that table. Just as the index in Oracle manual helps you to locate information faster than if there were no indexes. An Oracle index provides a faster access path to table data.

57. Explain Briefly Shared Server Architecture?

Ans: This architecture removes the need for a dedicated server process for each connection. A dispatcher routes various incoming network session requests to a shared server processes pool. An idle shared server process from a shared pool of server processes chooses a request from a common queue,which means a small number of shared servers can do the same amount of processing as many dedicated servers.
It is because of the amount of memory required for each user is comparatively small, less memory and process management are required, and more users can be supported.

58. What is the definition of the transaction?

The transaction is defined as a series of SQL statements in which Oracle considers each SQL as a single unit of work, and the transaction is done after the user logs in. Every transaction is unique and involves different details. After the transaction begins, the SQL Data Manipulation Language will be a part of the transaction; it will be completed when a ROLLBACK or COMMIT command is issued. Oracle database will assign a unique identifier called transaction ID to every transaction. Oracle transactions obey ACID properties, considered basic functions of database transactions. Some examples of transactions are bill pay, purchasing, and money transfer.

59. What is the definition of GROUP BY?

Ans: A GROUP BY clause in oracle is a part of Oracle SELECT expression, its existence is obligatory but still used to gather data from different records and group them. In each group, there is no similarity between two rows in their values for columns or grouping columns. The result in a GROUP BY includes a single row for each group.

GROUP BY clause is optional for a select statement, and its subsets result in groups. 

60. The main function(s) of the redo log is?

Ans: The redo log's primary function is to record all the modifications in the database, which are used to reconstruct all the changes. The redo log secures the rollback data.

61. What is the definition of SQL?

Ans:  SQL means Structured Query Language. It is a computer language used to store, manipulate and extract data stored in a relational database management system (RDBMS).

62. What do you understand about the public database link?

Ans:  The Public database link is used by any user. It is created when more users require an access path to a single remote Oracle Database. When your database is a public clause, then it is accessible to all the users

63. What are the benefits of ORDBMS?

Ans:  ORDBMS has many benefits like it can store projects, its language can be integrated with an object-oriented programming language, queries can be made using SQL, and it is easy to learn. 

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

Ans:  To get total records in a table, we use the COUNT keyword, and we can count NULL values also using COUNT(*)

65. What is the use of the SELECT statement?

Ans:  A SELECT statement or subquery is used to retrieve data from database tables, object tables, object views, views, or materialised views depending on the instructions defined by SQL query.

66. do we use the materialised view?

Ans:  Materialised views are also called summaries as they store summarised data. Materialised views are used to store and reckon aggregated data like sales and are widely used in data centers to make decisions. 

67. What is the sequence?

Ans:  A sequence is an object in the database which produces an arrangement of numeric values. Sequences are created by using the CREATE SEQUENCE command. They are mainly used in databases as many applications require unique values in their tables and sequence to furnish the values.

68. What is Data Normalisation?

Ans: The process of reorganising data within the database is called a normalisation. Users can use the normalised data for analysis and queries. The processed data is always clean, removing unstructured and redundant data.

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

Ans: It can be done simply by Just creating a spfile  from the pfile command  and then close the instance and start up again then spfile is switched from an init.ora file.

70. How many types of backups are there in Oracle?

Ans: 

There are four types of backups in Oracle they are:

1. COLD Backup
2. HOT Backup
3. Import-Export Backup
4. RMAN Backup

Conclusion

The interview questions for Oracle DBA are curated and prepared by our subject expert. These will help in your interview. Before going to the interview, make your fundamentals strong, and go through these questions and face the interview with confidence. You can go through our mock interview sessions, which are taken by experts and become familiar with real interview experience. Follow us for more updated and interesting content.