- 30 Jan, 2025
- 0 Comments
- 14 Mins Read
Oracle DBA Interview Questions
-
What are the different responsibilities of a Oracle DBA?
Answer: Oracle DBA is the database administrator who performs all administrative tasks. Administrative tasks include:
1 .User-level administration i.e. creates users, remove existing users or modifies user permissions.
2 .Maintains database security.
3 .Manages database storage & objects.
4 .Tunes performance of a database.
5. Performs backups & recovery tasks. -
Why do we use the materialized view instead of a table or views?
Answer: A Materialized View is a database object that stores the results of a query. Using materialized views instead of tables or regular views in complex queries can significantly improve performance since the query results are precomputed and do not need to be re-executed repeatedly.
-
What do you mean by Database Normalization and why is it important?
Answer: Normalization technique is a set of rules that are used to organize the relational database to prevent data redundancy and dependency. Once initial database objects are identified, normalization helps in identifying the relationships between schema objects.
Different normalization forms are as follows:
1.First Normal Form (1NF)
2. econd Normal Form (2NF)
3. Third Normal Form (3NF)
4. Boyce-Codd Normal Form (BCNF)
5. Fourth Normal Form (4NF)
6. Fifth Normal Form (5NF) -
Can you list down the different components of physical and logical database structure?
Answer: Given below is the list of different components.
The physical structure includes:
1.Data files, which hold all the DB objects like tables, views, indexes, etc.
2. Redo Log files, which maintains the records of database changes as a result of user transactions.
3. Control files, which maintain the database status and physical structure.
The logical structure includes:
1. Tablespace, which is a logical storage unit where the database object resides.
2. Segments are logical storage units only but within a tablespace.
3. Extent is the logical unit where various contiguous data blocks and extents together form a segment.
4. A data block is the smallest logical storage unit in the database. -
What is a SYSTEM tablespace and why do we need it?
Answer: System tablespace is created at the time of database creation. This tablespace holds all the data dictionary tables and hence it is used for the creation of various database objects. System tablespace must stay online for the database to function smoothly.
-
How is the Clustered Index different from the Non-Clustered Index?
Answer: An index is a schema object, which can search the data efficiently within the table.
Indexes can be clustered or non-clustered. Differences include:
1. In a clustered index, table records are sorted physically and stored in a particular order. Hence, a table can have a single clustered index only. While in a non-clustered index, logical sorting happens which does not match the physical order of the records.
2. Leaf node of a clustered index holds the data pages while the non-clustered index holds the index rows. -
What do you mean by SGA and how is it different from PGA?
Answer: SGA means System Global Area is the memory area that is defined by Oracle during instance startup. This area can be shared by the system-level processes and hence it is known as the Shared Global Area as well. PGA is Program Global Area is memory specific to a process or session. It is created when the Oracle process gets started and each process will have a dedicated PGA
-
What is a password file in a database and why is it required when a user can be authenticated using data dictionary tables?
Answer: Database users can be authenticated using data dictionary tables as they store the username & password. If the password provided by a user matches with the one stored in the database, then the user would be able to log in. However, this can happen only if the database is open. If the database is in shutdown mode, then these tables cannot be accessed and hence password file will be used by the database administrators to log in and open the database.
-
What are the different types of backups that are available in Oracle?
Answer: On a higher level, there are 2 types of backup that are available in Oracle which are physical & logical. During physical backup, copies of physical database files (like data files, control files, redo logs & other executables) are created and saved for the future. This can be achieved using either operating system utilities or RMAN.
In contrast, logical backup allows taking a backup of the database objects like tables, views, indexes, stored procedures, etc. individually through Export/Import utility provided by Oracle. -
What do we mean by hot backup & cold backup and how are they different?
Answer: Hot backup is the process of taking database backup while the database is in running mode. Hence, it is also known as Online Backup. While cold backup can be taken only when the database is in shut down mode and hence it is known as Offline Backup as well. There are few websites like banking & trading ones, which are 24 hours operational and hence, cannot support bringing the database down. Hence, DBAs need to take the backup in online mode only.
-
What is the difference between restoring a database and recovering a database?
Answer: During the restoration process, backup files are copied from the hard disk, media or tapes to the restoration location and later make the database operational. Recovery has an additional step of updating these data files by applying redo logs so as to recover the changes which are not backed up. Let us understand this with the help of a scenario.
1.Database full backup is taken on Friday 11 PM
2.Database crash happened on Saturday 7 AM
We can restore the lost files using the 11 PM full backup which is Restoration. However, the data will be restored up till Friday at 11 PM and not till Saturday at 7 AM. In order to do the same, redo logs can be applied which will bring the database to the point of failure. -
What do you understand by Redo Log file mirroring?
Answer: Redo log is the most crucial component of database architecture that records all transactions within the database even before it goes to the data file. Hence, the mirroring of these files is done to protect them. Redo Log file mirroring allows redo logs to be copied to different disks simultaneously. And this can be achieved using Data Guard and other utilities.
-
How is incremental backup different from differential backup?
Answer: Incremental backup is known for keeping back up of only the changed data files since the last backup, which might be full or incremental. For Example, An incremental/full backup is done at 10 AM on Friday and the next backup is done at 10 AM Saturday. The second incremental backup will only have the transactions occurred after Friday at 10 AM. While Differential backup backs up the files that changed during the last full backup. If you take a full back up on Friday at 10 AM and then differential backup on Saturday at 10 AM, it will take the backup of the files changed since Friday, 10 AM. Further, if the differential backup is taken on Sunday at 10 AM, it will take the backup of the files changed since Friday, 10 AM.
-
What is a Flashback Query and when should it be used?
Answer: Oracle has introduced a flashback technology to recover the past states of database objects. It can recover the accidental changes, which got committed as well. Recovery depends on the specified value of the UNDO_RETENTION parameter. For Example, the UNDO_RETENTION parameter is set to 2 hours and if a user accidentally deletes the data at 11 AM with commit performed. Then, using FLASHBACK QUERY, he can retrieve these rows until 1 PM only.
-
How is RMAN better than the user-managed backup recovery process?
Answer: Recovery Manager (RMAN) is an Oracle built-in utility that can automate database backup & recovery processes and administrate backup strategies as well. In user-managed backups, the user needs to perform backup manually. RMAN backup time will be less when compared to user-managed backups as RMAN maintains all the metadata in the Central Repository and can quickly retrieve the same. RMAN does incremental backup rather than taking full file backups which are done by user-managed backups, which again saves time. RMAN creates backup and recovery scripts that can be re-used and scheduled and does not need manual intervention. RMAN can detect corrupted data blocks automatically during the backup process and recover them, whereas it doesn’t happen in user-managed backups.
-
What is a Recovery Catalog?
Answer: Recovery catalog is a database schema that holds the metadata used by RMAN for restoration and recovery processes. It basically stores information on
1.Datafiles & their backup files.
2.Archived Redo Logs & their backup sets.
3.Stored scripts
4.Incarnation
5.Backup history The catalog gets updated once RMAN takes the backup or switches redo log or changes data file. -
How do you recover a lost control file?
Answer: If one of the control files is lost or gets corrupted, we can use another copy of the control file maintained at the OS level. If all the copies of control files are lost or if a user is maintaining only one copy of the control file which gets lost, then a user can
1.Manually create a control file.
2.Restore it from the backup control file using the below command.
ALTER DATABASE BACKUP CONTROL FILE TO TRACE;
1. Restore using RMAN backup by using the below commands. -
What is the difference between media recovery & crash recovery?
Answer: Media recovery is the process of recovering the database from the backup whenever a disk failure is there. Physical files like data files, control files or server parameter files get recovered during media recovery. However, crash recovery will be performed whenever a database instance failure occurs. Media recovery needs to be performed by DBA while crash recovery is an automated process that is taken care of SMON background process.
-
What is RAC and what are the various benefits of using RAC architecture?
Answer: RAC or Real Application Cluster allows the database to be installed across multiple servers forming a cluster and sharing the storage structure at the same time. This prevents the database from a single point of failure as one or the other instance will always stay up even if the other fails.
Using RAC helps in
1.Maintaining high availability of the system.
2.Managing workload with the least expenses.
3.Scalability & agility. -
How would you differentiate between cluster and grid?
Answer: Clustering is an integral part of grid infrastructure and focuses on a specific objective. While grid, which may or may not consist of multiple clusters, possesses a wider framework that enables sharing of storage systems, data resources and remaining others across different geographical locations. A cluster will have single ownership but the grid can have multiple ownership based on the number of the cluster it holds.
-
What do you understand from Cache Fusion?
Answer: Cache fusion is the process of transferring data from one instance buffer cache to
another at a very high speed within a cluster. Instead of fetching data from physical disk which is
a slow process, the data block can be accessed from the cache.
For Example, Instance A wants to access a data block, owned by instance B. It will send an
access request to instance B and hence can access the same using the other instance B’s buffer
cache. -
How can we monitor the space allocations in a database?
Answer: We can use the below data dictionary tables to monitor the space allocations:;
1.DBA_FREE_SPACE
2.DBA_SEGMENTS
3.DBA_DATA_FILES -
What do you understand by “Performance Tuning of DB” & what are the different
areas where we can perform tuning?
Answer: It is the process of enhancing database performance by making optimal use of the
available resources.
Performance can be enhanced by tuning any of the below areas:
1.Database design.
2.Memory allocation.
3.Disk I/Os.
3.Database contention.
4.OS level (CPU). -
What are the different tools that are provided by Oracle to assist performance
monitoring?
Answer: Various tools include:
1.AWR(Automatic Workload Repository)
2.ADDM(Automated Database Diagnostics Monitor)
3.TKPROF
4.STATSPACK
5.OEM(Oracle Enterprise Manager) -
What are the different optimizers that are used to optimize the database?
Answer: There are two types of optimizers:
1. Rule-Based Optimizer (RBO): If the referenced objects don’t maintain any
internal statistics, RBO is used.
2. Cost-Based Optimizer (CBO): If the referenced objects maintain internal
statistics, CBO will check all the possible execution plans and select the one with
the lowest cost. -
What is an explain plan and how does it help in optimizing the SQL query?
Answer: An explain plan is a statement that displays the execution plan selected by the Oracle
optimizer for SELECT, INSERT, UPDATE & DELETE statements. By looking at this plan, one
can figure out Oracle selection of the right indexes, proper joins & sorts operations, etc. -
How can we collect the statistics of different database objects?
Answer: ANALYZE statement can be used to collect the statistics of various database objects
like tables, indexes, partitions, cluster or object references. Using this statement we can also
identify migrated as well as chained rows within a table or cluster. -
Why do we need to rebuild indexes?
Answer: Rebuilding indexes is required in order to improve the performance of an
application. Due to various INSERT & DELETE operations, the index gets fragmented &
unstructured, thereby making the application slow. To reorganize data within these indexes,
rebuilding is performed. -
What is TKPROF and how can we use it?
Answer: TKPROF is a tuning utility provided by Oracle which can convert SQL trace files into a
readable format.
Once trace file is generated using SQL Trace Utility, the TKPROF tool can be run against trace
file and output can be read. It can also generate the execution plan for SQL statements. The
executable for TKPROF is located in the ORACLE HOME/bin directory. -
How can we tune a SQL query to optimize the performance of a database?
Answer: Enlisted are a few of the best practices for writing SQL queries.
1.Column names should be provided instead of * in SELECT statements.
2.Joins should be used in the place of sub-queries.
3.EXISTS should be used instead of IN to verify the existence of data.
4.UNION ALL should be used in the place of UNION.
5.HAVING should be used only for filtering the resulted rows from the SQL query. -
How would you identify the SHARED_POOL_SIZE parameter that needs to be adjusted?
Answer: Below is the indications for the same:
1.Getting an ORA-04031 error.
2.Degrading the performance even when all the other parameters are already
optimized.
3.Poor library cache/data dictionary hits. -
What do you understand by Row Chaining?
Answer: When a row is too large that it cannot fit in a block, then it will end up using consequent
blocks which lead to the concept of Row Chaining. It can be avoided by updating the storage
parameters to an appropriate value -
What is table partitioning and why is it required?
Answer: It is a process of dividing a table into smaller chunks so as to make the data retrieval
easy and quick. Each piece will be known as a partition and can be accessed separately. Apart
from tables, indexes can also be partitioned -
How can we identify the resources for which the sessions are waiting?
Answer: We can find it out using v$session_waits and v$ system _waits
-
How to create password file?
Answer: $ orapwd file=orapwSID password=sys_password force=y nosysdba=y
-
How many types of indexes are there?
Answer: Clustered and Non-Clustered
1.B-Tree index
2.Bitmap index
3.Unique index
4.Function based index
5. Implicit index and explicit index
Explicit indexes are again of many types like simple index, unique index, Bitmap index, Functional index,
Organizational index, cluster index. -
What is bitmap index & when it’ll be used?
Answer:
– Bitmap indexes are preferred in Data warehousing environment. Refer Q31
– Preferred when cardinality is low. -
What is B-tree index & when it’ll be used?
Answer:
– B-tree indexes are preferred in OLTP environment. Refer Q31
– Preferred when cardinality is high -
How you will find out fragmentation of index?
Answer:
– AUTO_SPACE_ADVISOR_JOB will run in daily maintenance window and report fragmented
Indexes/Tables
SQL>ANALYZE INDEX VALIDATE STRUCTURE;
This populates the table ‘INDEX_STATS’. It should be noted that this table contains only one row and therefore only
one index can be analyzed at a time.
An index should be considered for rebuilding under any of the following conditions:
* the percentage of deleted rows exceeds 30% of the total, i.e. if del_lf_rows / lf_rows > 0.3.
* If the ‘HEIGHT’ is greater than 4.
* If the number of rows in the index (‘LF_ROWS’) is significantly smaller than ‘LF_BLKS’ this can indicate a large
number of deletes, indicating that the index should be rebuilt. -
What is the difference between delete and truncate?
Answer:
Truncate will release the space. Delete won’t.
Delete can be used to delete some records. Truncate can’t.
Delete can be rolled back.
Delete will generate undo (Delete command will log the data changes in the log file where as the truncate will simply
remove the data without it. Hence data removed by Delete command can be rolled back but not the data removed by
TRUNCATE).
Truncate is a DDL statement whereas DELETE is a DML statement.
Truncate is faster than delete.