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.
Explore Obiee Sample Resumes Download & Edit, Get Noticed by Top Employers!
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.
Wish to make a career in the world of Oracle Fusion HCM ? Start with Oracle Fusion HCM Training
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.
Explore Oracle DBA Sample Resumes Download & Edit, Get Noticed by Top Employers!
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:
Ans. There may be many database processes, and they are given the name "DBWn" within the OS. The process mainly manages to write Oracle's database files at the OS level. It writes/stores the "dirty" buffers on the disc. Here, overlooking the database buffer cache is the most important thing. While updating the data block, a server process verifies the specific block to the buffer cache. If the block is unavailable within the cache, a copy will be directly updated to the cache. This changed database block is the "dirty" block.
Cluster:- To create a grid infrastructure, we use clustering technology. It offers many static resources for particular applications and owners. Further, cluster computing is used primarily in the areas like Databases, WebLogic App Servers, etc.
Grid:- A grid is a distributed computing model which includes multiple Clusters. Grids are resource-supplying pools which we can share across various users and applications. Further, a grid doesn't judge that all the servers execute the same application type. These apps can be designed and moved to the grid servers.
Ans. The following information will be required while creating a sequence.
Ans. The following are the various physical Oracle database components:-
Ans. By default, the database will not automatically start after the server's rebooting. It requires starting manually after a server reboot ends. However, there are two different ways to make it start automatically.
Oracle Restart - It is an Oracle High Availability Service (OHAS) feature that needs the installation of "Grid Infrastructure." Thus, it is a suggested way to start your database automatically.
Using Own Script - To automatically start your database, there is another way that you can write your script. You can place it at the beginning of the operating system for initiating.
Ans. There are two types of Synonyms in Oracle Database:-
Public Synonyms - These synonyms are accessible by any user in the database, which is not a part of any schema.
Private Synonyms - These synonyms are only accessed by a person who developed them and is part of a particular schema.
Ans. To select distinct values from a table in the database, we need to use the "SELECT DISTINCT" keyword.
Ans. Data files consist of all the tables which store the data within the database. These are the physical files of the operating system, and they must be specifically built for each tablespace. Further, Oracle allows two related file numbers for each data file- relative file and absolute file number.
Ans. The following are the different SQL Statements:-
Ans. The keyword to get sorted records from a table is "ORDER BY."
Ans. A data file can connect with only a single database once and cannot modify its size after creation.
Ans. The redo log is the main recovery operation structure that keeps track of all database changes. These consist of more than two pre-alloted files and store all the database changes. Suppose any failure prevents custom data from permanently writing the same as data files. Then the redo log enables us to make necessary corrections to the data.
Ans. A parameter file is a text file that consists of various initialization parameters along with their values.
Ans. We can run a query from the command prompt to know the database version. The database version information is located in the v$version table. The command to check the database version is-
SELECT * FROM V$VERSION
Batch starts on 7th Jun 2023, Weekday batch
Batch starts on 11th Jun 2023, Weekend batch
Batch starts on 15th Jun 2023, Weekday batch