Each database requires at least one database administrator (DBA) to administer it. The Oracle DBA is not a one-person job as the system is large and has many users. In these cases, there is a group of DBAs who share responsibilities.
In this article, you can go through the set of Oracle DBA interview questions most frequently asked in the interview panel. This will help you crack the interview as the topmost industry experts curate these at HKR training.
Let us have a quick review of the Oracle DBA interview questions.
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.
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.
Wish to make a career in the world of Oracle DBA ? Start with Oracle DBA training !
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.
Ans: The following tasks are carried out by Oracle DBA.
Ans: The various Oracle database objects are:
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:
Ans: The following tools are the products, tools and utilities used for administering the database.
Ans: The following are the dictionary tables used in monitoring a database's space.
Ans: Following is the logical structure of the disk resources:
Ans: You can use the following database views to get the information on database size:
If you have any doubts on Oracle DBA, then get them clarified from Oracle DBA Industry experts on our Oracle DBA Tutorial!
Ans: There are three different levels of auditing.
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.
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.
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.
Ans: Following is the procedure of restoring RMAN for the recovery:
Ans: The following files must be backed up.
If you have any doubts on Oracle DBA, then get them clarified from Oracle DBA Industry experts on our Oracle DBA Community !
Ans: Following are the architectural components of RMAN:
Ans: A recovery catalogue is a database plan that holds the metadata used by RMAN for reinstallation and healing processes. It stores information on
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.
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.
Ans: You can start up a database with three tools.
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.
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
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.
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.
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.
Ans: Database contain one or more Rollback Segments to roll back transactions and data recovery.
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
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.
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.
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.
Ans: The Grid Management feature of Oracle Enterprise Manager 10g provides a single console to manage multiple systems together as a logical group.
Ans: An extent is a specific number of consecutive data blocks allocated for storing a specific type of information
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.
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.
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.
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.
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.
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
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.
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.
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.
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.
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.
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.
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.
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).
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
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.
Ans: To get total records in a table, we use the COUNT keyword, and we can count NULL values also using COUNT(*)
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.
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.
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.
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.
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.
There are four types of backups in Oracle they are:
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.
Batch starts on 29th Sep 2022, Weekday batch
Batch starts on 3rd Oct 2022, Weekday batch
Batch starts on 7th Oct 2022, Fast Track batch