DBA Responsibilities
In this article we will discuss the Oracle DBA Interview Questions and answers. Typically As an Oracle Database Administrator (DBA) responsibilities are following
- Database installation, configuration, and upgrades: Installing and configuring Oracle databases, applying patches and upgrades to existing databases.
- Performance monitoring and tuning: Monitoring the performance of the database and identifying areas where performance can be improved. Tuning the database to optimize performance.
- Backup and recovery: Implementing backup and recovery strategies to ensure that data is protected and can be recovered in the event of a disaster.
- Security management: Managing the security of the database by controlling access, monitoring user activity, and implementing security policies.
- Capacity planning: Estimating the growth of the database and planning for future capacity needs.
- Troubleshooting and problem resolution: Identifying and resolving problems that arise with the database, such as data corruption or system failures.
- Documentation: Maintaining documentation on the database, including system architecture, configuration, and operational procedures.
- Database design: Collaborating with developers and architects to design databases that meet the needs of the organization.
- User management: Creating and managing database users, and granting and revoking permissions as required.
- Automation: Automating routine tasks to improve efficiency and reduce errors.
Oracle DBA Interview Questions and Answers.
Q 1: What is an Oracle DBA?
An Oracle DBA (Database Administrator) is a professional who is responsible for the installation, configuration, maintenance, and management of Oracle database systems. The DBA also ensures the security, availability, and performance of the database system.
Q 2: What is the difference between media recovery & crash recovery?
Media recovery is the process of recovering the database from the backup whenever a disk failure is there. Media recovery recovers physical files like data files, control files, or server parameter files. Whenever a database instance failure occurs, the system will perform crash recovery.
The DBA needs to perform media recovery, while the SMON background process takes care of the automated process of crash recovery.
Q 3: What are the different responsibilities of a DBA?
DBA is the database administrator who performs all administrative tasks. Administrative tasks include:
User-level administration i.e. creates users, removes existing users, or modifies user permissions.
- Maintains database security.
- Manages database storage & objects.
- Tunes the performance of a database.
- Performs backups & recovery tasks.
Q 4: What are the different types of tablespaces in Oracle?
A: The different types of tablespaces in Oracle are:
- System Tablespace
- Temporary Tablespace
- Undo Tablespace
- User Tablespace
Q 5: What is the difference between a hot backup and a cold backup in Oracle?
When taking a hot backup, the database continues to run, and actively updates the data files. Shutting down the database is necessary when taking a cold backup.
Q 6: What is the purpose of redo logs in Oracle?
A: The database uses redo logs to record all the changes made to it. In case of a system failure or a media failure, the database uses the redo logs for recovery.
Q 7: What is the difference between a single-instance database and an Oracle RAC database?
A single-instance database runs on a single server, while an Oracle RAC database runs on multiple servers in a cluster configuration. Oracle RAC provides high availability, scalability, and load balancing for mission-critical applications.
Q 8: 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 performs incremental backups instead of user-managed backups that take full file backups, which helps save time.
- RMAN creates backup and recovery scripts that require no manual intervention and can be scheduled and re-used as needed.
- RMAN can detect corrupted data blocks automatically during the backup process and recover them, whereas it doesn’t happen in user-managed backups.
Q 9: What is the purpose of the SGA and PGA in Oracle?
1- The System Global Area (SGA) in Oracle is a shared memory area that stores data and control information for the Oracle database instance. It contains the database’s buffer cache, shared pool, and other structures that manage the database.
2- The Program Global Area (PGA) in Oracle is a memory area that stores data and control information for a single database process or session. It contains the session-specific data such as sort area, hash area, and session variables.
Q 10: How is incremental backup different from differential backup?
Incremental backup is known for keeping a backup 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 that 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 backup on Friday at 10 AM and then differential back up on Saturday at 10 AM, it will take the backup of the files
changed since Friday, at 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, at 10 AM.
Q 11: 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 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.
Q12: What is the difference between an index and a cluster in Oracle?
A: An index is a database object that is used to improve the performance of queries by providing fast access to specific data. A cluster is a database object that groups related tables together based on common columns, and allows queries to access data from multiple tables at once.
Q 13: 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 shutdown 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.
Q 14: What is the purpose of the Data Dictionary in Oracle?
Answer: Oracle, a data dictionary is a collection of metadata, or data about data, that describes the structure and contents of the database. It is a set of tables and views that Oracle uses to store information about database objects such as tables, indexes, columns, and constraints.
The data dictionary is used by various Oracle tools and utilities, such as SQL*Plus, Oracle Enterprise Manager, and the Oracle Data Dictionary Browser. It is also used by Oracle itself to optimize queries and perform other tasks such as security and backup and recovery.
Q 15: What is the difference between restoring a database and recovering a database?
Answer: The restoration process involves copying backup files from hard disks, media, or tapes to the restoration location to make the database operational. An additional step in the recovery process is updating the data files by applying redo logs to recover changes that were not backed up.
Let us understand this with the help of a scenario.
A full database backup is taken at 11 PM on Fridays.
The database crash happened on Saturday at 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.
Q 16: 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 Manually create a control file. Restore it from the backup control file using the below command.
ALTER DATABASE BACKUP CONTROL FILE TO TRACE;
Restore using RMAN backup by using the below commands.
setdbid XX;
restorecontrolfile;
Q 17: When should you use a Flashback Query, and what does it do?
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 a commit performed.
Then, using FLASHBACK QUERY, he can retrieve these rows until 1 PM only.
Q 18: 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 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.
Q 19: Why do we use the materialized view instead of a table or views?
Answer: Materialized view is a database object that holds query results. If materialized views are used instead of tables or views in complex query executions, performance gets enhanced as re-execution is not required for repeated queries.
Q 20: What are the different types of backups that are available in Oracle?
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 the Export/Import utility provided by Oracle.
Conclusion:
This post consists of number of question for Oracle DBA Interview Questions and answers.
Read More
