Oracle DBA Interview Q&A - Part 3

 Dear All,

Here i'm posting few Oracle DBA Interview Question and answers these will help you on Fresher or experience level technical Interviews .

1. Oracle Database Administration ( DBA ):

Q: How do you create a new user in Oracle DB?

Answer: To create a new user in Oracle, you can use the CREATE USER SQL statement. For example:

SQL > CREATE USER username IDENTIFIED BY password;

Q: Explain the difference between ARCHIVELOG and NOARCHIVELOG mode in Oracle DB.

Answer:

 In ARCHIVELOG mode, the database archives filled redo log files to a specified storage location, allowing for complete recovery from media or instance failure. In NOARCHIVELOG mode, the database does not archive filled redo log files, limiting recovery options.

 

Q. What is Oracle Database ( DB)?

 

Answer: 

Oracle Database is a relational database management system (RDBMS) developed by Oracle Corporation. It is a collection of data and procedures, which can be accessed and managed by various applications.

 

Q. Explain the architecture of Oracle Database ( DB).

 

Answer: 

Oracle Database architecture consists of the following components:

 

Instance: It is a set of memory structures and background processes that manage a database.

Database: It is a collection of physical files on disk managed by Oracle.

Tablespace: Logical storage unit within a database where actual data is stored.

Data files, Control files, Redo log files: Physical files associated with an Oracle database.

Q. Explain the role of the REDO log in Oracle DB.

 

Answer: 

The REDO log records all changes made to the database. It plays a crucial role in database recovery in case of a failure. REDO log files store changes before they are actually written to the database files, ensuring data integrity and recoverability.

 

2. Performance Tuning:

Q: How do you identify and resolve performance bottlenecks in Oracle Database ?

Answer: Performance bottlenecks can be identified using tools like Oracle Enterprise Manager or by querying dynamic performance views (e.g., V$SESSION, V$SQL, V$SQL_PLAN). Common tuning techniques include optimizing SQL queries, adding indexes, adjusting memory parameters, and optimizing I/O.

Q: What is the purpose of an Oracle index and how does it improve query performance in Database?

Answer:

 An Oracle index is a database object that improves the speed of data retrieval operations on a table at the cost of additional storage and decreased performance on data modification operations. Indexes provide a faster way to access rows, reducing the need for full-table scans.

 

3. Oracle Backup and Recovery:

Q: How do you perform a hot backup in Oracle database?

Answer: 

Hot backup, also known as online backup, can be performed using tools like RMAN (Recovery Manager) or by using the ALTER TABLESPACE BEGIN BACKUP and END BACKUP commands to put tablespaces in backup mode and copy the data files while the database is online.

Q: Explain the steps to recover a database using RMAN after a critical failure of database.

Answer: 

To recover a database using RMAN, you would typically:

 

Restore the database files from a valid backup.

Apply incremental backups, if available, to reduce recovery time.

Apply archived redo logs to roll forward the database to the desired point in time.

Open the database with the RESETLOGS option to reset the redo log sequence.

 

4. Security in Oracle DB:

Q: How do you grant and revoke privileges in Oracle DB?

Answer:

 Privileges can be granted using the GRANT statement and revoked using the REVOKE statement. For example:

 

SQL > GRANT SELECT, INSERT ON table_name TO username;

SQL > REVOKE SELECT ON table_name FROM username;

Q: What is Oracle Transparent Data Encryption (TDE) and how does it enhance security in Oracle DB?

Answer: 

Oracle TDE encrypts sensitive data stored in database columns, tablespaces, or entire databases, adding an additional layer of security. It helps protect data at rest, preventing unauthorized access to sensitive information.

 

5. Troubleshooting Questions :

Q: How do you diagnose and resolve locking issues in Oracle database?

Answer: 

Locking issues can be diagnosed by querying the V$LOCK and V$SESSION views. To resolve locking problems, you can either commit or rollback open transactions, release locks explicitly, or tune SQL statements causing excessive locks.

Q: What are common causes of performance degradation in Oracle databases ( DB), and how do you address them?

Answer:

 Performance degradation can be caused by suboptimal SQL queries, inadequate hardware resources, inefficient database design, or improper configuration. Addressing these issues involves optimizing SQL queries, adjusting memory and I/O configurations, and periodically reviewing database schema and indexing strategies.

Q. How do you optimize SQL queries for better performance in Oracle database?

 

Answer: 

Optimization techniques include:

Analyzing query execution plans using tools like Explain Plan.

Indexing frequently queried columns.

Avoiding the use of SELECT * and fetching only necessary columns.

Using appropriate hints to guide the optimizer.

Regularly analyzing and rebuilding indexes.

Properly designing database schema and normalizing data.

Q. Explain what a deadlock is and how to resolve it in oracle.

 Answer:

 A deadlock occurs when two or more transactions are blocked, each waiting for the other to release a resource. To resolve a deadlock, Oracle automatically detects and resolves it by rolling back one of the statements involved in the deadlock. Proper transaction management and application design can also prevent deadlocks.

Thanks,

Srini