Best DBA Job Interview Questions and Answers

1

Can you explain how a DBMS differs from a RDBMS?

Reference answer

Understanding the difference between a Database Management System (DBMS) and a Relational Database Management System (RDBMS) is fundamental for a DBA. You should be able to explain the key differences and the advantages of using an RDBMS over a DBMS. DBMS and RDBMS are both systems for managing databases, but RDBMS has additional relational functionality. While DBMS manages data as files, RDBMS manages data in tables and allows establishing relations between these tables. This relational approach provides more flexibility and efficiency in data management.

2

Which view lists RMAN restore points?

Reference answer

RC_RESTORE_POINT.

3

Why Are Bind Variables Important?

Reference answer

Bind variables are crucial because most database suppliers provide an execution plan cache, which stores recently processed SQL statements and planning processes for again use.

4

Describe your experience with SQL. What are some complex queries you have written?

Reference answer

I have extensive experience with SQL across various databases like MySQL, PostgreSQL, and SQL Server. One of the most complex queries I wrote involved multiple joins and subqueries to generate a comprehensive sales report, optimizing it to run efficiently on large datasets.

5

Write a SQL query to calculate the average salary of employees in a specific department.

Reference answer

SELECT AVG(salary) AS average_salary FROM employees WHERE department_id = your_department_id;

6

Explain why database administrators use primary keys.

Reference answer

Answers will vary, but you need to be on the lookout for applicants who name skills that align with your requirements.

7

How would you perform database restore/refresh?

Reference answer

Performing a database restore/refresh in Oracle typically involves restoring the database to a previous backup or refreshing it with data from another source. The process depends on whether you're restoring from a backup or performing a database refresh using export/import methods or RMAN duplication. Here's an outline of both methods:1. Restore Using RMAN (Recovery Manager): This method is used when you want to restore the database from RMAN backups due to failure or data corruption. Steps to perform a restore: Prepare for Restore: Ensure the Oracle instance is in MOUNT state if it's not already.shutdown immediate; startup mount; Check Backup Availability: Use the following RMAN command to list the available backups: RMAN> list backup; Restore the Control File (If Needed): If the control file is missing or corrupted, restore it using: RMAN> restore controlfile from autobackup; Restore the Database: If you need to restore the entire database, use the following command: RMAN> restore database; Recover the Database: After restoring, apply any necessary archived redo logs to bring the database to a consistent state: RMAN> recover database; Open the Database: After recovery, open the database: alter database open; 2. Database Refresh Using RMAN DUPLICATE: This method is used to refresh a database from a source database (e.g., a production database) to a target (e.g., a test or development database). Steps to perform a refresh: Connect to RMAN: Connect to both the target (the database to be refreshed) and the auxiliary (the new database) using RMAN. Ensure the auxiliary database is in NOMOUNT state. Prepare the Auxiliary Instance: startup nomount; Duplicate the Database: Use the following RMAN command to duplicate the database: RMAN> duplicate target database to newdb from active database; 3. Refresh Using Data Pump (EXPDP/IMPDP): Data Pump is another common method to refresh a database by exporting and importing schemas or entire databases. Steps to perform a refresh using Data Pump: Export the Data (EXPDP): On the source database (e.g., production), export the data using the expdp utility: expdp system/password@prod full=y directory=exp_dir dumpfile=full_exp.dmp logfile=expdp.log Transfer the Dump File: Copy the dump file (full_exp.dmp) to the destination (target) database server. Import the Data (IMPDP): On the target database (e.g., development or test), import the dump file: impdp system/password@dev full=y directory=exp_dir dumpfile=full_exp.dmp logfile=impdp.log This will refresh the target database with the data from the source database.

8

Why we do not run catalog.sql and catproc.sql when we create database using DBCA?

Reference answer

DBCA will run those scripts internally. We must run those scripts only when we create database manually.

9

What is a Flashback Query and when should it be used?

Reference 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.

10

Can you differentiate Instance and Database?

Reference answer

In Oracle, Instance and database are two separate components but work together. Instance resides on RAM and it is the way users speak to database. The user data resides inside data files which reside on Hard Disks. Users connect to database instance and ultimately instance speaks with database. Instance can also be defined as combination of memory structures and background processes.

11

What is an execution plan?

Reference answer

An execution plan is a visual representation of the data retrieval methods chosen by the SQL Server query optimizer for a specific query. It shows how SQL Server will access the data, including join methods, indexes used, and data retrieval paths. Understanding execution plans is crucial for query optimization and troubleshooting.

12

How did AIR assist with seasonal hiring for warehouse and CS roles?

Reference answer

AIR handled all initial screening and interviews for hiring 1,200 warehouse and CS roles in 6 weeks, with the same team size as when they hired 400.

13

How do you handle stress and pressure in your role as a Database Administrator? Can you provide an example?

Reference answer

In my role as a Database Administrator, I often encounter situations that require working under tight deadlines or dealing with critical issues. To handle stress, I prioritize tasks based on their urgency and impact, breaking them down into manageable steps. I practice effective time management techniques to ensure I allocate sufficient time for each task. Additionally, I maintain open lines of communication with stakeholders, keeping them informed about progress and managing expectations. One example of handling stress was during a major database outage, where I remained calm, coordinated efforts with the team, and followed established incident response protocols to resolve the issue promptly.

14

What are the procedures to change the archiving mode of a database from NO ARCHIVELOG to ARCHIVELOG?

Reference answer

- The database instance should be shut down. - Make a backup of the database. - Perform any actions that are particular to your operating system. - Start a new instance and load the database, but don't open it yet. - Change the archiving mode of the database.

15

Describe the purpose of the mysqlhotcopy utility in MySQL.

Reference answer

The mysqlhotcopy utility is used to create backups of MySQL databases while they are still running.

16

Archives are not arriving at the standby. What will you check?

Reference answer

To diagnose this issue, check the following:Query v$dataguard_status on the primary database for errors. Verify log_archive_dest_state_2 is set to ENABLE. Confirm that standby listener and tnsnames.ora are correctly configured. Check network connectivity between primary and standby databases. Ensure there is sufficient disk space in the archive destination.

17

What is a snapshot too old error?

Reference answer

This occurs when Oracle cannot find undo data needed for a query. Increasing undo tablespace or tuning queries can help mitigate this issue.

18

Why is the control file important in Oracle?

Reference answer

The control file must be available for writing by the Oracle database server whenever the database is open. Without the control file, the database cannot be mounted and recovery is difficult. You might also need to create control files if you want to change particular settings in the control files.

19

What is the difference between pfile and spfile?

Reference answer

Pfile is human readable file and spfile is binary file. We can start database instance with either of the files but first preference is given to spfile. Both reside under $ORACLE_HOME/dbs location and are used to allocate instance memory.

20

How can you check the Oracle database version?

Reference answer

"Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – Production With the Partitioning, OLAP, Data Mining, and Real Application Testing options" SQL: SELECT * FROM v$version; A sample output would look like below: BANNER Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – Production PL/SQL Release 11.2.0.4.0 – Production CORE 11.2.o.4.o Production TNS for Linux: Version 11.2.0.4.0 – Production NLSRTLVersion 11.2.0.4.0 – Production

21

What are SNIPPED sessions in database?

Reference answer

The sessions which exceed idle time are marked as snipped. The oracle level processes are cleared when user exceed idle time but OS level processes will still exist. This is overhead on server.

22

Can a non-SYS user be used for redo transport due to security restrictions?

Reference answer

Yes, create a user with SYSOPER privilege and set REDO_TRANSPORT_USER to that username.

23

Can we restore a database from an obsolete backup?

Reference answer

Yes, we can restore a database from an obsolete backup. For that, we need to catalog those files explicitly.

24

What are the two types of upgrade methods for SQL Server?

Reference answer

In-place upgrade and Side-by-Side Upgrade.

25

How to know how much free memory available in sga?

Reference answer

1. Query V$SGA_DYNAMIC_FREE_MEMORYThis view provides the amount of free memory available in the SGA: - BYTES: Displays the amount of free memory in bytes. 2. Query V$SGASTAT The V$SGASTAT view provides a more detailed breakdown of SGA memory usage. To find the free memory:- This will show how much free memory is available in different memory pools. 3. Query V$SGAINFO Another useful view is V$SGAINFO :- This gives the available free memory within the SGA. 4. Query V$SGA To get a summary of SGA memory components: - This helps you understand total memory allocation. 5. Use SHOW SGA

26

What is SQL Server Management Studio, and when would you use it?

Reference answer

Answers will vary, but you need to be on the lookout for applicants who name skills that align with your requirements.

27

What is the difference between SYSDBA, SYSOPER and SYSASM?

Reference answer

SYSOPER can't create and drop database. SYSOPER can't do incomplete recovery. SYSOPER can't change character set. SYSOPER can't CREATE DISKGROUP; ADD/DROP/RESIZE DISK SYSASM can do anything SYSDBA can do

28

Which process on standby receive the changes from primary?

Reference answer

On the standby database, the Remote File Server (RFS) process receives the changes (redo data) from the primary database.

29

What happens to the objects if we change default tablespace?

Reference answer

Nothing will happen, things will continue normally.

30

What are the key responsibilities of a Database Administrator?

Reference answer

A Database Administrator is the person who designs, implements, and ensures that the database system is regularly updated. He also establishes policies and procedures that pertain to the efficient management, security, and use of the database.

31

Using which package we can convert Tablespace from DMTS to LMTS?

Reference answer

DBMS_SPACE_ADMIN

32

What happens if the public is disabled?

Reference answer

If the public role becomes unavailable for all, its DBA will create another server role that replaces it and use Windows authentication instead.

33

What are the entries/location of oraInst.loc?

Reference answer

/etc/oraInst.loc is pointer to central/local Oracle Inventory

34

What is your experience with SQL and database design principles?

Reference answer

I have extensive experience writing complex SQL queries including joins, subqueries, and aggregate functions, as well as optimizing query performance using indexes and execution plans. In terms of database design, I follow normalization principles to reduce data redundancy, ensure referential integrity through foreign keys, and design schemas that balance performance with maintainability. I also have experience with denormalization for read-heavy workloads.

35

What are system databases in SQL Server?

Reference answer

System databases in SQL Server include master (stores instance-level metadata), model (template for new databases), msdb (used by SQL Server Agent for scheduling jobs and alerts), and tempdb (temporary storage for user objects and internal operations). These databases are essential for SQL Server operation.

36

Explain what ODBC means.

Reference answer

Answers will vary, but you need to be on the lookout for applicants who name skills that align with your requirements.

37

Can we flashback a Pluggable Database (PDB) to a restore point in Oracle 12cR1 and 12cR2?

Reference answer

In Oracle 12c Release 1 (12cR1): Flashback of an individual PDB to a restore point is not supported. In Oracle 12c Release 2 (12cR2): You can flashback a PDB to a restore point independently.

38

What are the main types of backups in SQL Server?

Reference answer

The main types are Full Backup, Differential Backup, and Transaction Log Backup. Full backup copies the entire database. Differential backup copies only changes since the last full backup. Transaction Log Backup records all transactions since the last backup, allowing point-in-time recovery.

39

What is Hadoop?

Reference answer

Hadoop is an open-source software library for storing and processing vast datasets across clusters of servers. It does this efficiently using distributed storage and parallel processing to manage big data better.

40

How to give access to 100 tables to a user?

Reference answer

Create a role:

41

What are the uses of undo tablespace or redo segments?

Reference answer

Undo Tablespace: 1. Transaction Rollback: Stores undo information to roll back transactions in case of failure or user error. 2. Read Consistency: Provides read-consistent views of data for queries, ensuring that queries see a consistent view of data despite concurrent changes. 3. Error Recovery: Enables recovery from errors, such as deadlocks or corrupted data blocks.Redo Logs: 1. Transaction Recovery: Stores redo information to recover transactions in case of failure or crash. 2. Database Recovery: Enables recovery of the database in case of a crash or media failure. 3. Replication and Standby: Supports replication and standby databases by providing a record of changes made to the primary database. 4. Auditing and Troubleshooting: Provides a record of changes made to the database, which can be useful for auditing and troubleshooting purposes.

42

Can you add CPU to SQL Server? If so, how?

Reference answer

Yes, you can add CPU to SQL Server by adding new hardware. You can do this logically by hardware partitioning or virtually through a virtualization layer. You should be aware that from 2008 onwards, the SQL Server version supports CPU Hot Add. Below are the requirements for the use of CPU Hot Add:

43

How do you prioritize tasks when managing multiple databases or projects simultaneously?

Reference answer

I prioritize tasks by evaluating their urgency and impact on business operations, ensuring critical issues are addressed first. I use project management tools to track and organize tasks, and maintain open communication with stakeholders to align priorities and expectations.

44

What are the different types of indexes in SQL Server?

Reference answer

The simplest answer is Clustered and Non-Clustered Indexes. Other types include Unique, XML, Spatial, and Filtered Indexes. In a clustered index, the leaf level pages are the actual data pages of the table, and there can only be one clustered index on a table. In a non-clustered index, the leaf level pages contain pointers to the data pages, and there can be multiple non-clustered indexes on a single table.

45

Why doesn't a tempfile added on the primary reflect on the standby with STANDBY_FILE_MANAGEMENT=AUTO?

Reference answer

Adding a tempfile generates no redo, so it's not automatically created on the standby. You must manually add the tempfile on the standby.

46

Explain what a frontend software system is.

Reference answer

Answers will vary, but you need to be on the lookout for applicants who name skills that align with your requirements.

47

How to untar the Oracle home backup.

Reference answer

If you created a tar.gz backup of ORACLE_HOME, untar it as follows: Notes: - Always stop DBs and services dependent on ORACLE_HOME before restoring. - Use --numeric-owner or run as oracle user depending on tar flags and environment to preserve owner/group. - If using snapshots or rsync, use corresponding restore commands.

48

What steps do you take to optimize database performance?

Reference answer

When dealing with performance issues, I start by identifying the root cause through performance monitoring tools. I analyze query execution plans and optimize poorly performing queries by using appropriate indexes, rewriting SQL statements, or implementing caching mechanisms. I also ensure that database statistics are up to date. If necessary, I fine-tune database configuration parameters or consider hardware upgrades. I continuously monitor and analyze system performance to proactively address potential bottlenecks.

49

Can you list down the different components of physical and logical database structure?

Reference 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.

50

What is a private synonym in Oracle?

Reference answer

A private synonym is one that does belong to a specific schema. In other words, when only the owner can access it, it is called a private synonym.

51

What is the difference between with grant option and with admin option while assigning privileges?

Reference answer

1️⃣ WITH GRANT OPTION✅ Used only for object privileges (e.g., SELECT ,INSERT ,UPDATE ,DELETE on tables, views, etc.). ✅ Allows the grantee (user who receives the privilege) to grant the same privilege to other users. ✅ If the original grantor's privilege is revoked, all granted privileges are also revoked.? Example: ? Now, user1 can use theSELECT privilege and also grant it to others. 2️⃣ WITH ADMIN OPTION ✅ Used only for roles (not for system or object privileges). ✅ Allows the grantee to grant and revoke the role to/from other users. ✅ Even if the original grantor loses the role, the users they granted it to still keep it.? Example: ? Now, user2 can use theDBA role and grant/revoke it to other users.

52

How to resize a datafile?

Reference answer

Ensure the datafile is auto-extensible before resizing. ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' RESIZE 100M;

53

What are the different SQL server versions you have worked on?

Reference answer

I've worked extensively with several SQL Server versions, including SQL Server 7, 2000, 2005, and 2008. My most recent hands-on experience has been with 2008. I found it quite easy to transition between these versions, quickly adapting to new features and changes.

54

What is the purpose of Flashback Logs in the database?

Reference answer

Flashback Logs store historical information that allows the database to be reverted to a previous state, helping in quick recovery without requiring traditional backups.

55

What is the most challenging project you've worked on? Why was it challenging and what was your role?

Reference answer

A project can be challenging for different reasons. Maybe they had a hard time coping with some team members, or maybe, because of an external situation they struggled with deadlines. Although this is a common question, it's a great way of learning more about a candidate and their soft skills.

56

Name two reasons why would UNIQUEIDENTIFIER data type (aka GUID) be a bad choice for a clustered key?

Reference answer

Because of FRAGMENTATION it causes and large SIZE (16 bytes)

57

What are the different types of backups that are available in Oracle?

Reference 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.

58

What is SQL Server Reporting Services (SSRS)?

Reference answer

SQL Server Reporting Services (SSRS) is a server-based report generating software system that enables users to create and manage a wide range of reports. It supports various report delivery options and interactive reports.

59

What are the steps to configure Oracle Data Guard?

Reference answer

Enable ARCHIVELOG mode.Set STANDBY_FILE_MANAGEMENT to AUTO. Configure TNS and listener settings. Set log_archive_config and fal_client parameters. Use RMAN to create a standby database. Start managed recovery.

60

What are base tables?

Reference answer

Base tables are binary tables inside database which contains encrypted data. These are also called as metadata because it stores data regarding other data inside database. Base tables are copied only to data dictionary area under instance and flushed out. Base tables are also known as dictionary tables. Any modifications to base tables will corrupt the database. Only oracle background process can modify these tables. Base tables reside under system tablespace.

61

What are some advantages of using SQL?

Reference answer

SQL has several advantages, which is why it's so widely used. It eliminates the need for coding, making databases easier and simpler to manage, which results in faster query processing. SQL is considered easy to learn and is a standard language supported by most database management systems.

62

How do you approach troubleshooting database issues? Can you provide a specific example?

Reference answer

I start by identifying the root cause using diagnostic tools like SQL Profiler and performance logs. For instance, I once resolved a critical issue by pinpointing a problematic query and optimizing it, which reduced execution time by 70%.

63

Comparison: RMAN Active Database Duplicate vs. RMAN Duplicate from Backup

Reference answer

Active Database Duplicate: Pros: Faster duplication, minimal downtime, no need for prior backups. Cons: Puts additional load on the source database. Duplicate from Backup: Pros: Uses existing backups, reducing the impact on the source database. Cons: Requires pre-existing backups, making it slower.

64

What is a trace file, and how does one make one?

Reference answer

A trace file can be created for each server and backend process. When a process or user process detects an operational fault, it spills data about the bugs to its trace. This information can be used to fine-tune the database.

65

How are passwords for database users stored and authenticated in Oracle?

Reference answer

Passwords for database users are stored in the data dictionary of the database. When a user wants to log into the database, the username and password provided by the user are checked against the values stored in the database. If the username and password match, the user is granted access to the database. The data dictionary is part of the database and it will be accessible as long as the database is open. The passwords for administrators are stored in the dictionary as well. When the database is closed, the data dictionary will be inaccessible. There needs to be a mechanism for administrators to login into the database even when it is closed because it is one of the administrator's tasks to start up a down database. A password file is a separate operating system file that is stored on a disk outside of the database. The username and password for the users who have SYSDBA or SYSOPER privileges are stored in it. Administrators who have those privileges are authenticated using this password file even when the database is down.

66

What is an explain plan and how does it help in optimizing the SQL query?

Reference 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.

67

Can i run expdp command on standby?

Reference answer

Yes we can run expdp on standby database by createing db_link and using network_link parameter in expdp.

68

What are the five fully automated steps in AIR's process?

Reference answer

The five fully automated steps are: from application to ranked, verified talent. Zero recruiter coordination required until you're ready to interview.

69

What is DBCC?

Reference answer

DBBC statements are Database Console Commands and have four different areas: - Maintenance commands: Are those commands that allow the database administrator to perform maintenance activities (for example shrinking a file.) - Informational commands: Provide feedback regarding the database. - Validation commands: Include commands that validate the database such as the ever-popular CHECKDB. - Miscellaneous commands: Are those who can't be categorized in the three previous areas. They include statements like DBCC help.

70

How do you tune a long-running SQL query?

Reference answer

First, check the execution plan using EXPLAIN PLAN or DBMS_XPLAN.DISPLAY_CURSOR. Look for: • Full Table Scans? • Missing Indexes? • Expensive Joins or Sorts? Then: • Add indexes or rewrite query. • Use bind variables to reuse plans. • Update statistics if stale. • Consider hints like /*+ index(emp emp_idx1) */ if Oracle chooses a wrong path. Also analyze the buffer gets, CPU time, and elapsed time from v$sql to confirm inefficiency.

71

Explain about SCN and checkpoint number.

Reference answer

SCN is unique transaction number assigned to set of redo logs generated. This identify that OK, these all redo entries are part of one transaction. A checkpoint is a database event, which synchronize the database blocks in memory with the datafiles on disk. It has two main purposes – To establish a data consistency and enable faster database Recovery.

72

Which option is required to run the application as another user?

Reference answer

Pressing and holding the Shift key and right-clicking on the application Opening the SQL Server Management Studio file location and pressing and holding the Shift key Providing the SQL Server hostname CMD

73

Explain about data and how do you store data?

Reference answer

Data is any value which we store for future reference. There are different types of data tools which we can use to store data. The simplest data storage tools are notepad, MS-Excel, MS-Access etc.

74

Is there any new technology that you have learned recently? And where did you imply them?

Reference answer

Recently, I learned about cloud-based database solutions like Amazon RDS and Azure SQL Database. I applied this knowledge by migrating a legacy on-premises database to Azure SQL Database, which reduced maintenance overhead and improved scalability. I also explored automated monitoring tools using PowerShell scripts to proactively identify performance bottlenecks.

75

If database is slow what you will check? what could be the reasons?

Reference answer

If a database is slow, here are some key areas to check: 1. Check the Database Sessions and Wait Events 2. Check CPU and Memory Usage 3. Check Disk I/O Performance 4. Check Running SQL Queries 5. Check Index Usage 6. Check Redo Log and Archive Log Activity 7. Check Background Processes and Alerts 8. Check Database Parameters 9. Check Network Latency (For RAC or Distributed Databases) 10. Check Blocking Locks Some common reasons for slow database performance include:– Inefficient SQL queries – Insufficient indexing – Poor database configuration – Disk I/O bottlenecks – Memory constraints – Network congestion – Lock contention and deadlocks – Pending database maintenance tasks

76

How do you handle database security? What measures do you implement to protect sensitive data?

Reference answer

I implement encryption for both data at rest and in transit to ensure data security. Additionally, I regularly update and patch database systems to protect against vulnerabilities, and use role-based access control to limit data access to authorized users only.

77

What is the difference between clustered and non-clustered index?

Reference answer

A clustered index physically sorts data rows in the table (one per table). A non-clustered index is a separate structure with pointers to the data, like a separate directory (multiple allowed).

78

What is utlrp.sql script?

Reference answer

Compiles all invalid PL/SQL modules in the database after upgrade or patching.

79

What are DBCC statements, and can you name the different types?

Reference answer

DBCC stands for Database Console Commands. These are special commands used to perform various maintenance, validation, and informational tasks on SQL Server. The primary types include Maintenance commands, like DBCC CHECKDB for checking database integrity; Informational commands, which provide feedback about the database; and Validation commands, which confirm consistency. They're essential for troubleshooting and ensuring database health.

80

What are views in SQL?

Reference answer

Views are virtual tables based on SQL query results. They simplify complex queries, restrict access to specific data, and provide a consistent interface to data.

81

What are the elements of a logical data model, and how do they vary from a physical data model?

Reference answer

The following are the elements of a logical data model: - Entity - An entity is a type of object that we utilize to store data. It comes with its own table. - Attribute - This reflects the information about the entity we're looking at. It is recorded as a table column with a particular data type connected to it. - Record - A record is a group of all the features associated with an object for a single condition, expressed in a table as a row. - Domain - A domain is the collection of all possibilities for a certain property. - Relationship - A relationship between two entities is represented by this object.

82

How do you enable ARCHIVELOG mode in Oracle?

Reference answer

Shut down the database: SHUTDOWN IMMEDIATE; Start the database in mount mode: STARTUP MOUNT; Enable ARCHIVELOG mode: ALTER DATABASE ARCHIVELOG; Open the database: ALTER DATABASE OPEN;

83

How can we start the database and what are the stages?

Reference answer

Stages: - NOMOUNT: Start instance (read init.ora/spfile). - MOUNT: Control files are read. - OPEN: Datafiles are opened; DB is available.

84

How do you configure Data Guard broker?

Reference answer

To configure Data Guard Broker, I first set DG_BROKER_START=TRUE on both primary and standby, then use dgmgrl to create the configuration, add both databases, set protection and transport properties, and enable it. From then on, Broker can manage and monitor the Data Guard environment, including automated role transitions.

85

What occurs when we run an SQL statement in Oracle?

Reference answer

At the very beginning, it validates the syntax and semantics in the cache of the library before creating a blueprint. Data will be automatically returned to the client if it is already present in the buffer. If the data is not present in the database, it will go to the data files and store it in the database buffer cache, then transmit it to the server, which will then deliver it to the user.

86

If we create many non-clustered indexes on a table, what operations are likely to be considerably slower than before?

Reference answer

Write operations (INSERT, UPDATE, DELETE, MERGE)

87

Can we convert the physical standby database to logical standby database.

Reference answer

Yes it is possible to convert physical to logical standby.

88

What are the common issues you face in dataguard environments?

Reference answer

Replication issues due to network issues Archive log Missing errors If a datafile is renamed on primary, then also error will come in standby. Issue may occur if someone mistakenly changes the dataguard related parameters like log_archive_config, log_archive_dest parameters

89

Can you have a Distributor on a previous version than the Publisher?

Reference answer

No, you cannot have a Distributor on a previous version than the Publisher.

90

Can we create different size of redo files inside one group?

Reference answer

No, all the members inside a group must have same size.

91

What are the different levels of normalization?

Reference answer

Common levels are 1NF (no repeating groups), 2NF (1NF + non-key attributes depend on the whole primary key), and 3NF (2NF + no transitive dependencies).

92

How do you stay current with new database technologies and trends?

Reference answer

I stay current by regularly following industry leaders on platforms like SQL Server Central and DataPlatformGeeks, and I subscribe to key technical newsletters. I also make it a point to attend webinars and virtual conferences focused on new features in cloud databases like Azure SQL or AWS RDS. Hands-on experimentation in personal lab environments is also crucial for me to truly understand and apply new technologies. This blend of continuous learning ensures my skills remain sharp and relevant.

93

Describe your backup and recovery strategy.

Reference answer

My backup strategy follows the 3-2-1 rule: three copies of data, on two different media types, with one copy stored offsite. I implement full backups weekly, differential backups daily, and transaction log backups every 15 minutes for critical systems. I test recovery procedures monthly using a separate environment to ensure our RTO of 4 hours and RPO of 15 minutes are achievable. Last year, when a storage array failed, I successfully restored our main customer database using point-in-time recovery, losing only 8 minutes of data and getting users back online in 2.5 hours.

94

If observor is unable to connect with primary , but it can connect with standby, then what will happen?

Reference answer

If the observor is unable to connect to primary, then it will check the status of primary, through standby.

95

How do you approach learning new database technologies or tools to stay updated in your field?

Reference answer

As a Database Administrator, I understand the importance of staying updated with emerging technologies. I actively engage in self-learning through online resources, industry forums, and professional networks. I also attend webinars, workshops, and conferences to expand my knowledge. Additionally, I participate in relevant certification programs to validate my skills. For instance, I recently completed a course on cloud-based database solutions to enhance my understanding of this evolving area. By embracing a continuous learning mindset, I can stay ahead of industry advancements and implement best practices in my role.

96

How can we view the status of a rollback segment?

Reference answer

Using the DBA_ROLLBACK_SEG view.

97

How do you switch from non-CDB to CDB architecture?

Reference answer

To migrate from a non-CDB to CDB/PDB, you use the plugin method. Steps: 1. Connect to the non-CDB and run DBMS_PDB.DESCRIBE() to generate an XML manifest file. 2. Create or use an existing CDB. 3. Use the CREATE PLUGGABLE DATABASE command in the CDB to plug in the non-CDB using the manifest file. 4. Open the new PDB in READ WRITE mode. 5. Run noncdb_to_pdb.sql inside the PDB to clean up non-CDB metadata. After that, the database behaves as a PDB within the CDB. Oracle provides tools like Data Pump, RMAN, and Clone PDB methods for other migration strategies. Testing this conversion in non-production is highly recommended.

98

Which command adds a new redo log member?

Reference answer

ALTER DATABASE ADD LOGFILE MEMBER.

99

Why did Microsoft remove the old 'Reorg between 5 and 30% logical fragmentation and rebuild after 30' best practice, and what should be done instead?

Reference answer

Microsoft removed that old best practice because it was never meant to be a 'Best Practice'; the author, Paul Randal, stated in 2009 that those numbers were arbitrary and often led to incorrect index maintenance. Instead, you should use a more dynamic approach based on actual fragmentation patterns, page density, and performance monitoring, such as using sys.dm_db_index_physical_stats to determine when to reorganize or rebuild based on current conditions.

100

How do you approach performance tuning at the database level?

Reference answer

I start by identifying bottlenecks using monitoring tools. Then, analyze execution plans for inefficient queries, check/add indexes, update statistics, and review server configuration/resources (I/O, CPU).

101

What are database snapshots in SQL Server?

Reference answer

Database snapshots are read-only, point-in-time copies of a database that use sparse files to store changes since the snapshot was created. They are used for reporting, protecting against user errors, and reverting a database to a previous state, but they do not replace full backups.

102

Where can you find the non-default parameters when an instance is started?

Reference answer

Alert log

103

How can you monitor and manage MySQL temporary tables?

Reference answer

Temporary tables are automatically dropped when the session ends or when explicitly deleted using the DROP TEMPORARY TABLE statement.

104

Which password management feature is NOT available by using a profile?

Reference answer

Password change

105

What's the Difference Between Clustered and Non-Clustered Indexes in SQL?

Reference answer

Clustered Index: A clustered index determines the physical storage order of rows in a table. There can only be one clustered index per table because data can only be physically arranged in one order. The leaf level of the clustered index holds the actual data row, so retrieval is direct with no additional I/O. Non-Clustered Index: A non-clustered index is a separate structure from the table data. It holds a sorted list of key values, with each entry containing a pointer (row locator) back to the corresponding data row. A table can have multiple non-clustered indexes, each optimized for a different query pattern. Trade-off: Every non-clustered index improves reads but increases the cost of INSERT, UPDATE, and DELETE because SQL Server must maintain each index on every write.

106

From last 2 hours patching is ongoing, still not complete. Where to look? What are reasons?

Reference answer

Where to look: - Patch tool logs: $ORACLE_HOME/OPatch/log/ ororaInventory/logs/ . - Installer logs: $ORACLE_BASE/oraInventory/logs andrunInstaller logs. - /var/log/messages or OS logs for I/O errors. - Database alert logs: $ORACLE_BASE/diag/rdbms///trace/alert_.log . - Grid logs (if GI): /u01/app/19.0.0/grid/your_crs/log/ andgrid/log/ . - OPatch progress and background processes ( ps -ef | grep opatch ). - Check for locks: lsof | grep orfuser for files being used. Possible reasons: - Large binary files being copied (slow I/O). - Pending DB/instance not shut down or external process holding files. - Incorrect permissions causing retries. - Inventory corruption or version mismatch with opatch. - Network FS / NFS latency if ORACLE_HOME on network mount. - Missing prerequisites or wrong Java versions (causing internal checks to wait). - Waiting for services to be stopped/started (e.g., clusterware) or cluster quorum issues. - Human intervention prompt waiting for confirmation in silent mode (rare). - System resource shortage (low free space, low memory, high load). - Locks from other patch/inventory processes. Action: check logs, checktop ,iostat , disk space, running processes, and confirm no interactive prompt.

107

How do you handle schema changes in a live production database?

Reference answer

For schema changes in production, I follow a very careful and methodical process. All changes are rigorously tested in non-production environments first. I always ensure the scripts are under version control and have a rollback plan prepared and tested. I'd communicate the planned changes and any expected impact to stakeholders in advance, and schedule the deployment during off-peak hours. Post-deployment, I closely monitor the database for any performance deviations or errors to ensure stability and functionality.

108

What UNIX parameters you will set while Oracle installation?

Reference answer

During an Oracle installation on a UNIX-based system, the following parameters are typically set:1. kernel.shmall: Sets the maximum number of shared memory segments. 2. kernel.shmmax: Sets the maximum size of a shared memory segment. 3. kernel.shmmni: Sets the maximum number of shared memory identifiers. 4. semaphores: Sets the semaphore parameters (semmsl, semmns, semopm, semmni). 5. file descriptors (nofiles): Sets the maximum number of open file descriptors. 6. processes (nproc): Sets the maximum number of processes. 7. stack size (stack): Sets the maximum stack size for a process.These parameters are usually set in the /etc/sysctl.conf file or using the ulimit command. Example: bash # sysctl -w kernel.shmall=2097152 # sysctl -w kernel.shmmax=2147483648 # sysctl -w kernel.shmmni=4096 # ulimit -n 65536 # ulimit -u 16384

109

What do you understand by database indexing and how does it improve database performance?

Reference answer

Database indexing is a performance optimization technique that speeds up data retrieval. You should be able to explain how indexing works, its implications on database performance, and the circumstances in which it's most effective. Database indexing works like a book index, providing a quick lookup for data retrieval. It improves database performance by reducing disk I/O operations. However, while it speeds up data retrieval, it can slow down operations like insertions and deletions as indices need updating. Therefore, it's a trade-off that needs careful consideration.

110

How do you prioritize continuous improvement and learning in your role as a Database Administrator? Can you provide an example of how you have applied new knowledge or skills to enhance your work?

Reference answer

Look for: Continuous improvement mindset.

111

Can static parameters be changed using SCOPE=BOTH?

Reference answer

No.

112

How would you design a disaster recovery plan for a critical database?

Reference answer

Start by defining RPO/RTO. Implement a robust backup schedule (full, differential, log). Utilize technologies like AlwaysOn Availability Groups or log shipping. Crucially, regularly test the recovery process.

113

Archive log destination filled up — what now?

Reference answer

When archive log destination fills: • Immediately free up space by backing up archive logs to tape or disk. • Delete obsolete archive logs using RMAN's DELETE ARCHIVELOG command. • Add more space or configure multiple destinations. • Monitor archive log generation rate and destination usage regularly. • Adjust backup and archive log management policies to prevent recurrence.

114

What is a parameter file and what is the search order for Oracle to locate it?

Reference answer

A parameter file holds instance parameters that govern how an instance operates. In order to start up an instance, Oracle needs to locate this file. The search order is as below: /DBS/spfile.ora – This is a server parameter file and this is the first place that oracle will look for. SID- is the service identifier of the instance. <$ORACLE_HOME-/dbs/spfile.ora -If Oracle cannot find the file in the first location, it will search this file. This is again a server parameter file. /dbs/init.ora – This is a parameter file and it is plain text. If Oracle cannot find the two files listed above, it will search for this file. This is the last location to search.

115

What is an Oracle data file?

Reference answer

An Oracle datafile is a big unit of physical storage in the OS file system. One of many Oracle data files is organized together to provide physical storage to a single Oracle tablespace. The data file is used to store tables and indexes allocated to the database. Every database consists of one or more data files.

116

How can you monitor MySQL performance using built-in tools?

Reference answer

Use tools like SHOW STATUS, SHOW PROCESSLIST, and the EXPLAIN command to monitor and analyze database performance.

117

How is incremental backup different from differential backup?

Reference 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.

118

After flashing back the database, are the latest table statistics reverted, or do they remain persistent?

Reference answer

Flashback Database does not revert table statistics. The statistics remain as they were at the time of the Flashback operation.

119

How do you handle database security and what measures do you implement?

Reference answer

I implement database security using a defense-in-depth strategy. At the network level, I configure firewalls and VPNs to restrict database access. For the database itself, I use role-based access control, ensuring users only have the minimum permissions needed for their roles. I also implement Transparent Data Encryption (TDE) for data at rest and SSL/TLS for data in transit. Regular security audits are crucial—I schedule monthly reviews of user permissions and quarterly penetration testing. In my previous position, I discovered and remediated several unused service accounts during these audits, significantly reducing our attack surface.

120

How does shared server architecture work in Oracle?

Reference answer

In shared server architecture, the clients connect to a "dispatcher" process. This dispatcher is responsible for delivering the SQL requests to the "request queue". The shared server process monitors the request queue. When they find an incoming request, they execute this SQL query and place the results in the response queue. The request queue and the response queue reside in the system global area. The dispatcher processes also monitor the response queue. When it receives a result, they deliver the result to the relevant client. In this architecture, there will be multiple shared server processes and dispatcher processes.

121

Where are Flashback Logs stored?

Reference answer

Flashback logs are stored in the Fast Recovery Area (FRA) under the directory: flash_recovery_area/DB_UNIQUE_NAME/flashback.

122

What are the grants required to take the export/import.

Reference answer

For Data Pump (expdp/impdp) and legacy export/import: Data Pump (expdp/impdp) privileges: - EXP_FULL_DATABASE for full database export (grants to user or use a user with necessary privileges). - IMP_FULL_DATABASE for full import. - DATAPUMP_EXP_FULL_DATABASE andDATAPUMP_IMP_FULL_DATABASE synonyms exist in many setups. - Additionally, user should have READ access to source objects,WRITE on dump directory (OS directory object), and quota on target tablespace for import. - Grant directory access: - For legacy exp /imp , ensure SELECT on objects andDBA role or equivalent for full exports. Note: Avoid granting powerful roles (DBA) unnecessarily; prefer fine-grained grants if possible.

123

If there was a database incident, what steps would you take to resolve the issue?

Reference answer

In a database incident, my immediate steps would be to assess the impact and isolate the problem to prevent further damage. Next, I'd work on restoring service using our documented recovery procedures, prioritizing mission-critical functions. Throughout this, I'd maintain clear communication with stakeholders. Once the immediate crisis is resolved, I'd conduct a root cause analysis to understand why it happened and implement preventative measures to ensure it doesn't recur, and then document everything thoroughly.

124

What are the two operating modes of Database Mirroring?

Reference answer

Database Mirroring runs in two operating modes: High-Safety Mode and High-Performance Mode.

125

What is MySQL, and what are its key features?

Reference answer

MySQL is an open-source relational database management system. Key features include multi-user support, data integrity, transaction support, and support for various storage engines.

126

Can I take full backup on standby and incremental backup on primary?

Reference answer

Yes we can do that.

127

What is Fast-Start Failover (FSFO)?

Reference answer

FSFO enables automatic failover to a standby if the primary becomes unavailable. An observer process, running on a separate server, monitors the primary. If the primary is unreachable for a defined threshold (FastStartFailoverThreshold), failover occurs. Post-failover, the old primary is reinstated using flashback if enabled. Prerequisites: Flashback enabled on both databases, observer on a separate server.

128

What is the purpose of SQL Server Agent?

Reference answer

SQL Server Agent automates tasks like backups, index maintenance, and running scheduled jobs. It helps manage routine operations without manual effort.

129

How do you handle a database stuck in mount mode?

Reference answer

When the database is stuck in mount mode, it means the instance has started but the database is not open yet. Steps to handle: • Check alert logs to identify any issues during startup. • Verify the status of control files and redo logs. • Confirm no recovery is pending or failed during mount. • Try to open the database with ALTER DATABASE OPEN;. • If recovery is needed, perform RECOVER DATABASE. • If control files or datafiles are corrupt or missing, restore from backup. • If stuck due to corrupted SPFILE or parameter file, recreate or fix them. • If still stuck, consider restarting the instance after resolving errors.

130

How do you create and manage Oracle users?

Reference answer

In Oracle, users are accounts that can connect to the database and access data or perform operations. Each user owns their own set of tables, procedures, etc. To create a user, we use:CREATE USER john IDENTIFIED BY welcome123; This creates a user named john with the password welcome123. But the user can't do anything until we grant permissions, like:GRANT CONNECT, RESOURCE TO john; As a DBA, you manage users by: • Creating users. • Assigning privileges (like read, write, admin). • Locking/unlocking accounts. • Changing passwords. • Dropping (deleting) users when they're no longer needed.

131

Can you explain the concept of data warehousing and its significance?

Reference answer

Data warehousing is integral to business intelligence and data analytics. Explain what a data warehouse is, how it differs from a regular database, and how it can transform data into meaningful information for decision-making. A data warehouse is a large-scale repository of historical and transactional data used for reporting and data analysis. Unlike regular databases, it's optimized for read-heavy operations and typically involves data from various sources. It's crucial for business intelligence as it helps businesses derive insights from their data and make data-driven decisions.

132

Describe a situation where you had to explain complex technical concepts to non-technical stakeholders.

Reference answer

Situation: Our CEO wanted to understand why we needed to invest $50,000 in database infrastructure upgrades. Task: I needed to explain database performance concepts and ROI in business terms. Action: I created a presentation using analogies—comparing database indexes to a book's table of contents and explaining how outdated hardware was like trying to run modern software on a 10-year-old laptop. I included metrics showing how slow queries were affecting customer experience and potential revenue impact. Result: The CEO approved the budget immediately and later commented that it was the clearest technical explanation he'd ever received.

133

Why you became DBA and didn't go into coding?

Reference answer

I have always been interested in database management, data security, and ensuring high availability of critical systems. While I did explore coding, I found that my strengths and passion lie in managing databases, optimizing performance, and ensuring data integrity. The role of a DBA is crucial in maintaining the backbone of any application, and I enjoy the challenge of troubleshooting issues, managing backups and recovery, and working on high-availability solutions like Oracle RAC and Data Guard. Additionally, I like the combination of administration, problem-solving, and automation that a DBA role offers. While coding is an essential skill, I prefer scripting for automation rather than full-time application development. This balance makes the DBA role a perfect fit for me.

134

Which SGA memory structure cannot be resized dynamically after instance startup?

Reference answer

Log buffer

135

How can you monitor and manage MySQL server status and variables?

Reference answer

Use commands like SHOW STATUS, SHOW VARIABLES, and SHOW GLOBAL STATUS to monitor server status and runtime variables.

136

How can you monitor and manage MySQL server logs?

Reference answer

Configure the MySQL server to generate error logs, general logs, and slow query logs, and use tools like SHOW VARIABLES to manage log settings.

137

What is the purpose of creating operators in SQL Server?

Reference answer

Operators in SQL Server help automate and streamline various processes efficiently by automating alerts, running jobs, or performing other functions within your database; they provide alerts when something needs to be completed quickly while automating workflow for more time-efficient work processes.

138

Full db backup and level 0 backup are same?

Reference answer

Yes, in RMAN, level 0 backup is a full backup that serves as base for incremental backups.

139

What do you mean by SGA and how is it different from PGA?

Reference 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

140

Describe your experience with cloud databases.

Reference answer

Answers will vary, but you need to be on the lookout for applicants who name skills that align with your requirements.

141

How can you monitor and manage MySQL binary logs for replication and recovery?

Reference answer

Use the SHOW BINARY LOGS command to list binary log files and the PURGE BINARY LOGS command to remove old log files.

142

Solve the FizzBuzz problem with SQL code.

Reference answer

Write a program that prints the numbers from 1 to 100. But for multiples of three print “Fizz” instead of the number and for the multiples of five print “Buzz”. For numbers which are multiples of both three and five print “FizzBuzz”. Challenge the DBA to do this on a whiteboard with pseudocode. The code doesn't have to be perfect syntax, but we're looking for the ability to solve this problem clearly and quickly.

143

How do you approach collaborating and building relationships with other IT teams or departments to achieve common goals?

Reference answer

In a previous project, we were implementing a new software system that required integrating with various databases across different departments. I proactively reached out to the IT teams and department heads involved, scheduling regular meetings to align objectives, identify potential challenges, and establish clear communication channels. I actively listened to their concerns and perspectives, seeking opportunities for synergy and collaboration. By fostering strong relationships and open lines of communication, we successfully implemented the system, ensuring seamless integration and achieving the project's goals.

144

What is bitmap index & when it'll be used?

Reference answer

– Bitmap indexes are preferred in Data warehousing environment. Refer Q31 – Preferred when cardinality is low.

145

What is the difference between an RMAN channel and an RMAN auxiliary channel?

Reference answer

RMAN Channel: A session between RMAN and a target/auxiliary database, used for parallel backup and restore operations. Auxiliary Channel: Specifically used in auxiliary database operations like duplication.

146

How to create a database manually?

Reference answer

Create an initialization parameter file (pfile). Create necessary directories for datafiles, logs, and control files. Execute the CREATE DATABASE command. Run catalog.sql and catproc.sql. Update listener.ora and tnsnames.ora. Add an entry in /etc/oratab

147

You have to take one table backup and you have to import it to other db but that table already exist in that system how will you export that table?

Reference answer

1. Export the table: Use the Data Pump Export (EXPDP) utility to export the table from the source database.expdp username/password@source_db directory=dpump_dir dumpfile=table_export.dmp tables=table_name 2. Import the table: Use the Data Pump Import (IMPDP) utility to import the table into the target database, using the REPLACE option to overwrite the existing table. impdp username/password@target_db directory=dpump_dir dumpfile=table_export.dmp tables=table_name replace

148

How many times can I flashback my database?

Reference answer

There is no fixed limit on the number of times you can perform Flashback Database. However, it depends on the availability of the required undo data and flashback logs.

149

What is the complete syntax to set DB_CACHE_SIZE in memory and SPFILE?

Reference answer

ALTER SYSTEM SET DB_CACHE_SIZE=2G SCOPE=BOTH; SCOPE=BOTH applies changes to both memory and SPFILE.

150

What approaches are used to maintain secure configurations for database servers?

Reference answer

Approaches include disabling unused features and ports, enforcing strong authentication, applying configuration baselines, encrypting communications, regularly auditing configuration changes, and restricting access to sensitive database files.

151

What is the ghost cleanup process in SQL Server?

Reference answer

Any record deleted from SQL Server is not actually deleted from its physical data pages but marked as "To BE DELETED" or "GHOSTED" by changing a bit in the row header. These records will be cleaned up by an internal process that is called the ghost cleanup process. This is a single-threaded background process that automatically runs on some intervals to check if any page is marked for ghost records or not and if it finds any, then it physically removes those ghosted records from pages.

152

When do we start and stop the database in production?

Reference answer

During: - Patch application - OS-level maintenance - Backup requiring shutdown - Hardware replacement - Major upgrades or migrations

153

Can you discuss a time when you had to advocate for a database-related change or improvement within your organization?

Reference answer

I identified a performance bottleneck due to outdated indexing strategies, which was slowing down critical queries. After presenting a detailed analysis and proposed solution to the management team, we implemented the changes, resulting in a 40% improvement in query performance.

154

In the event of an Instance failure, which files store command data NOT written to the datafiles?

Reference answer

Online redo logs

155

What is the Data Replication?

Reference answer

Data Replication is the process of storing data in more than one site or node. It is useful in improving the availability of data. It is simply copying data from a database from one server to another server so that all the users can share the same data without any inconsistency. Types of Data Replication – - Transactional Replication: In Transactional replication users receive full initial copies of the database and then receive updates as data changes. Data is copied in real-time from the publisher to the receiving database(subscriber) in the same order as they occur with the publisher therefore in this type of replication, transactional consistency is guaranteed. - Snapshot Replication: Snapshot replication distributes data exactly as it appears at a specific moment in time and the does not monitor for updates to the data. The entire snapshot is generated and sent to Users. Snapshot replication is generally used when data changes are infrequent. - Merge Replication: Data from two or more databases is combined into a single database. Merge replication is the most complex type of replication because it allows both publisher and subscriber to independently make changes to the database.

156

What is a foreign key in a database?

Reference answer

A foreign key is a field in one table that refers to the primary key in another table, creating a relationship between the two tables. It ensures referential integrity, meaning that the data in the foreign key field must match the values in the primary key it references. For example, in a table of orders, a foreign key might link each order to a specific customer from the customer table, ensuring that the order is associated with a valid customer.

157

What is Configuration Manager in SQL Server?

Reference answer

SQL Server Configuration Manager is a tool used to manage the services associated with SQL Server, configure network protocols, and manage client connectivity. It allows administrators to start, stop, pause, and restart SQL Server services, as well as enable or disable protocols like TCP/IP and Named Pipes.

158

Explain the concept of read replicas in MySQL.

Reference answer

Read replicas are copies of a master database used to distribute read traffic, improve scalability, and provide high availability.

159

How can you transport tablespaces across platforms with different endian formats?

Reference answer

Endian Format: Endian format refers to the way bytes are stored in memory:– Big Endian (BE): Most significant byte first (e.g., Oracle on SPARC, AIX). – Little Endian (LE): Least significant byte first (e.g., Oracle on x86, Windows).Transporting Tablespaces: To transport tablespaces across platforms with different endian formats:1. *Use the dbms_tts.transport_set_check procedure*: Verify that the tablespaces can be transported between platforms. 2. Convert the tablespaces to a platform-independent format: Use the dbms_tts.transport_set_convert procedure with the CONVERT option to convert the tablespaces to a platform-independent format. 3. *Use the RMAN CONVERT command*: Use the RMAN CONVERT command to convert the tablespaces to the target platform's endian format. 4. Transport the converted tablespaces: Transport the converted tablespaces to the target platform.RMAN CONVERT Command: The RMAN CONVERT command is used to convert tablespaces between endian formats:RMAN> CONVERT TABLESPACE 'tablespace_name' FORMAT '/path/to/converted/tablespace' ENDIAN CONVERSION;

160

Can RMAN backups be taken from a standby database?

Reference answer

Yes, this method helps distribute the backup workload and enhances data availability.

161

What are the common trace flags used in SQL Server?

Reference answer

The common trace flags used in SQL Server are:

162

What are precheck and postcheck for DR Drill activity.

Reference answer

Prechecks: - Verify backups are valid and recent (full+archivelog). - Network connectivity between primary and DR (latency). - Standby health: check archivelog apply, ARCH destinations, gaps. - Ensure DR site has required compute, storage and service configurations. - Ensure DNS/TNS updates and client routing plan. - Check database and OS patch levels compatibility. - Check service accounts & permissions. - Check failover/witness config (if using Data Guard Broker or orchestration). - Check SLRM or application config for DR. - Test scripts (startup/shutdown, mount/open) are present and executable. - Notify stakeholders and have rollback plan. Postchecks: - Validate database role and consistency after drill (primary/standby). - Check data consistency: applied logs, no gaps. - Validate application connectivity and failback procedures. - Check scheduled jobs and monitoring (restore cron, OEM alerting). - Ensure backups are resumed and future archiving works. - Record RTO/RPO, measure actual time taken, note anomalies. - Cleanup: restore original routing, DNS; re-synchronize any re-instated databases.

163

What do you understand by "Performance Tuning of DB" & what are the different areas where we can perform tuning?

Reference 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).

164

How do you stay up-to-date with new database applications and technologies?

Reference answer

I stay updated by attending industry conferences, participating in online forums, reading technical blogs, and taking certification courses. I also experiment with new tools in sandbox environments to evaluate their potential.

165

Explain the concept of query caching in MySQL.

Reference answer

Query caching involves storing the results of SELECT queries in memory, allowing subsequent identical queries to be served faster.

166

How are tablespaces and data files related in Oracle?

Reference answer

Each tablespace is divided into one or more data files and one and more tablespace(s) are created for each database.

167

How does Block Change Tracking work internally?

Reference answer

The Block Change Tracking file maintains a bitmap structure to track changes. Each chunk consists of four contiguous 8K blocks (totaling 32K). If any block in this chunk changes, the corresponding bitmap entry is updated. During an incremental backup, RMAN checks this bitmap and backs up only changed chunks instead of scanning the entire database.

168

How do you create a disk group in ASM?

Reference answer

Creating a disk group in ASM involves: • Identifying the disks or partitions that ASM will manage. • Ensuring the disks are properly prepared (e.g., raw devices or partitioned). • Using the asmca GUI or SQL commands via SQL*Plus. • In SQL, connect to ASM instance as sysasm and run: • CREATE DISKGROUP data NORMAL REDUNDANCY • DISK ‘/dev/sdb1', ‘/dev/sdc1', ‘/dev/sdd1'; • Specify redundancy levels (EXTERNAL, NORMAL, HIGH) depending on mirroring needs. • Once created, the disk group appears as a logical volume and is used to store database files. • Add or drop disks as needed without downtime. • Monitor disk group space and health regularly.

169

How to move a datafile to a new location?

Reference answer

Take the tablespace offline: ALTER TABLESPACE T OFFLINE; Move the file at the OS level. Rename it in Oracle: ALTER DATABASE RENAME FILE '/old/path/datafile.dbf' TO '/new/path/datafile.dbf'; Bring the tablespace online: ALTER TABLESPACE T ONLINE;

170

Can you share an experience where you successfully communicated technical concepts or complex information to non-technical stakeholders or clients as a Database Administrator?

Reference answer

Look for: Communication and simplification skills.

171

What are SQL constraints?

Reference answer

Constraints are rules applied to columns in a table to limit the type of data that can go into a table, ensuring accuracy and integrity. Examples include PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and NOT NULL.

172

What background process will writes data to datafiles?

Reference answer

DBWR

173

How to check listener status at OS level?

Reference answer

ps -ef | grep tns.

174

Explain how you would design a backup strategy for a 24/7 application.

Reference answer

Implement continuous backup: Use transaction log shipping or continuous WAL archiving to minimize data loss potential. Combine this with periodic full backups and incremental backups. Ensure backup availability: Store backups in multiple locations (local, offsite, cloud), test restore procedures regularly, and maintain backup retention policies that meet compliance requirements. Plan for different recovery scenarios: Design for point-in-time recovery, corrupted table recovery, and full disaster recovery. Document recovery procedures and train team members on execution.

175

How to see which undo tablespace is used for database?

Reference answer

To find the undo tablespace being used by your Oracle database, you can query the V$PARAMETER view for theundo_tablespace parameter:This query will return the name of the undo tablespace currently in use by the database. Alternatively, you can also query the DBA_TABLESPACES view to check for any tablespaces that are specifically used for undo purposes:This will show all tablespaces that are designated for undo purposes.

176

Can you describe a situation where you had to balance conflicting priorities while managing multiple database projects simultaneously? How did you prioritize tasks and ensure successful outcomes?

Reference answer

In my previous role, I was responsible for managing multiple concurrent database projects with tight deadlines. To balance conflicting priorities, I evaluated each project's importance, impact on business objectives, and urgency. I created a project roadmap, set clear priorities, and communicated them with stakeholders. I also collaborated with team members to delegate tasks and ensured regular communication to track progress and resolve any roadblocks. By effectively managing priorities and fostering teamwork, we successfully delivered all projects within the specified timeframes.

177

What is a password file in a database and why is it required when a user can be authenticated using data dictionary tables?

Reference 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.

178

Describe what hierarchical databases are.

Reference answer

Answers will vary, but you need to be on the lookout for applicants who name skills that align with your requirements.

179

What are some important Oracle alert log messages?

Reference answer

Important alert log messages include: • Database startup and shutdown messages. • Errors such as ORA-600, ORA-7445 indicating internal failures. • Archiver process status and archive log switches. • Datafile or control file corruption warnings. • Listener registration and connection issues. • RMAN backup and recovery operations. • ASM disk group rebalancing and disk failures. Alert log is the first place to check for troubleshooting critical database issues.

180

Can we create a Restore Point inside a Pluggable Database (PDB)?

Reference answer

Yes, Restore Points can be created inside a Pluggable Database (PDB).

181

How do you patch Grid Infrastructure?

Reference answer

Patching Grid Infrastructure involves these main steps: Get the patch – Download the required GI patch from My Oracle Support. Prepare – Backup critical configs and verify cluster health. Enter maintenance mode – Stop non-essential workloads or put the cluster in patch-ready state. Apply patch – Use opatchauto (preferred) or opatch manually. Rolling patch – If supported, patch nodes sequentially to minimize downtime. Validate – Run opatch lsinventory to confirm installation. Restart/restore services – Bring the cluster and resources back online.

182

Can you describe your experience with different database management systems (DBMS) and which one you prefer to work with? Why?

Reference answer

I have extensive experience with MySQL, Oracle, and SQL Server, but I prefer working with MySQL due to its open-source nature and strong community support. Its flexibility and ease of integration with various applications make it ideal for the dynamic environments I've worked in.

183

How do we modify database parameters dynamically?

Reference answer

Using the ALTER SYSTEM command.

184

Tell me about a time when you had to handle multiple competing deadlines or urgent requests as a Database Administrator. How did you manage your time and ensure timely delivery?

Reference answer

Look for: Time management and prioritization.

185

What is the main responsibility of a Database Administrator?

Reference answer

The main responsibility of a Database Administrator is to manage the performance, integrity, and security of an organizational database. You will be responsible for assisting in the planning and development of the database and be on hand to quickly troubleshoot problems on behalf of service users. As a Database Administrator you will also be responsible for ensuring organizational data remains clearly defined, the data is consistent across the database, users can access data in an easy-to-use format, and the security provisions are constantly updated and fit for purpose. Key duties you will have as a Database Administrator include monitoring access by users, maintaining the security of the database, liaising with service users to determine their specific needs, creating and testing recovery plans, collaborating with IT Project Managers and other departmental team members, and creating database user documentation including standards, protocols, and procedures.

186

What is xp_CmdShell and how do you correctly set it up and use it?

Reference answer

xp_CmdShell is not a security risk; the people that set it up and allow its usage incorrectly are the only security risk. People CAN run stored procedures that run xp_CmdShell and other goodies without those people being able to run xp_CmdShell themselves. NEVER grant individual users the privs to execute it, PERIOD.

187

What do you understand by Redo Log file mirroring?

Reference 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.

188

How do you monitor and optimize the cost of cloud database services?

Reference answer

To optimize cloud database costs, I continuously monitor usage patterns and resource consumption using the cloud provider's monitoring tools, like AWS CloudWatch or Azure Monitor. I look for underutilized instances and consider rightsizing them to lower-tier instances when possible. Additionally, I leverage features like auto-scaling to ensure that I'm not overpaying for unused capacity during off-peak hours. Another way to save costs is by using Reserved Instances or Savings Plans for long-term workloads. Finally, I regularly review storage usage and clean up any unused data or logs that are incurring unnecessary costs.

189

How to take the RMAN backup at two locations.

Reference answer

RMAN BACKUP can create multiple copies usingBACKUP COPIES orBACKUP +BACKUP TO multiple times.Options: - BACKUP COPIES to create multiple copies in the same backupset (saves N copies): This will create two identical backupsets (possibly on same disk target location). To put them in two distinct directories, configure multiple channels and direct them to different destinations in script: - Use backup to disk then copy: - Run two backup jobs: run one backup to first location and another to second location using different FORMAT or RUN blocks with channels pointing to different devices. - Use BACKUP DEVICE TYPE sbt to tape & disk simultaneously if supported by media manager. - Use BACKUP DATABASE PLUS ARCHIVELOG to both local disk and remote by running backup twice or usingBACKUP ... TAG andBACKUP ... to alternate.

190

Oracle does not consider a transaction committed until?

Reference answer

The LGWR successfully writes the changes to redo

191

How can you identify which data file is modified today?

Reference answer

We can check the data file timestamp at OS level.

192

What is Oracle inventory?

Reference answer

It is a location which provides the oracle product information which are installed on a server.

193

Can you elaborate on the different types of database models and how they impact database design?

Reference answer

Understanding different database models is key to designing efficient databases. With a solid understanding of models like hierarchical, network, relational, and object-oriented, you can make informed decisions on which to use based on the nature of the data and the application's requirements. You should also be able to explain the pros and cons of each model. Different database models include the hierarchical model, network model, relational model, and object-oriented model. The hierarchical model organizes data in a tree-like structure with a single root. Meanwhile, the network model allows many-to-many relationships, making it more complex. The relational model uses tables to represent data and relationships, allowing more flexibility, while the object-oriented model organizes data as objects, useful for complex data structures. The choice of model affects the database's efficiency, flexibility, and complexity.

194

What types of relationships exist in a database?

Reference answer

Databases contain four types of relationships: one-to-one, one-to-many, many-to-one, and many-to-many. These are associations between tables created to join statements that retrieve data. There can be only one record on each side of the relationship for both tables. Each of the primary key values relates to only one record or no records in the table related to it.

195

What is SQL?

Reference answer

SQL is a database computer language designed for managing data in relational database management systems (RDBMS) and originally based upon relational algebra. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

196

Does the DROP DATABASE command remove the SPFILE?

Reference answer

Yes.

197

What is the role of a tax accountant?

Reference answer

The task of tax accountant is to coordinate the payment of obligations as well as tax returns on a timely basis.

198

What are the RAC related parameters in Data Pump.

Reference answer

When using Data Pump in RAC environment, main considerations/parameters: - PARALLEL — use multiple workers to speed up parallel export/import; ensures workers distributed across RAC instances: - NETWORK_LINK — for network-based import/export across nodes. - ESTIMATE and DEGREE settings to optimize performance. - TRANSPORT parameters for transportable tablespaces; watch for RAC-specific ASM paths ( +DATA ). - DIRECTORY object pointing to shared ASM mount or cluster file system (must be accessible from all nodes). - KEEP_STATISTICS / REMAP_SCHEMA / REMAP_TABLESPACE work in RAC same as non-RAC. - DUMPFILE location must be accessible from all nodes if import/export will run across nodes; typically use a shared filesystem (ACFS, NFS) or ASM (with ASM driver). - Use PARALLEL andCLUSTER aware settings: Oracle will use multiple processes but Data Pump itself is cluster-unaware — ensure job runs on appropriate node and dump location is shared. - Consider using DIRECTORY on ACFS or shared NFS to ensure all nodes can access.

199

What would you check if archives are not arriving at the standby database?

Reference answer

Verify Data Guard configurations.Check network connectivity between primary and standby. Query v$dataguard_status for potential errors. Ensure proper log_archive_dest configuration.

200

What is normalization, and why is it important in a database?

Reference answer

Normalization is the process of organizing data within a database to reduce redundancy and ensure data integrity. It involves breaking down a table into smaller, more manageable tables and defining relationships between them. This process ensures that data is stored efficiently and consistently across the database. For example, instead of storing customer data in multiple tables, normalization would involve creating one customer table and referencing it using keys in other tables, reducing duplicate data. Here's how that looks in practice: In this unnormalized form, data redundancy is evident as customer and product details are repeated across multiple rows: | OrderID | CustomerName | CustomerAddress | ProductID | ProductName | Quantity | Price | | 101 | Alice | 123 Main St | 1 | Laptop | 1 | $1000 | | 102 | Alice | 123 Main St | 2 | Mouse | 2 | $50 | | 103 | Bob | 456 Oak St | 3 | Keyboard | 1 | $80 | | 104 | Bob | 456 Oak St | 4 | Monitor | 1 | $300 | First Normal Form (1NF) To achieve 1NF, we eliminate repeating groups and ensure that each column contains atomic values: | OrderID | CustomerID | CustomerName | CustomerAddress | ProductID | ProductName | Quantity | Price | | 101 | 1 | Alice | 123 Main St | 1 | Laptop | 1 | $1000 | | 102 | 1 | Alice | 123 Main St | 2 | Mouse | 2 | $50 | | 103 | 2 | Bob | 456 Oak St | 3 | Keyboard | 1 | $80 | | 104 | 2 | Bob | 456 Oak St | 4 | Monitor | 1 | $300 | Second Normal Form (2NF) For 2NF, we remove partial dependencies by separating the table into two tables: one for Orders and another for Customers. This avoids duplicating customer details: Orders Table | OrderID | CustomerID | ProductID | Quantity | Price | | 101 | 1 | 1 | 1 | $1000 | | 102 | 1 | 2 | 2 | $50 | | 103 | 2 | 3 | 1 | $80 | | 104 | 2 | 4 | 1 | $300 | Customers Table | CustomerID | CustomerName | CustomerAddress | | 1 | Alice | 123 Main St | | 2 | Bob | 456 Oak St | Third Normal Form (3NF) For 3NF, we remove transitive dependencies. The product details are moved to a separate table to avoid redundant information in the Orders table: Orders Table | OrderID | CustomerID | ProductID | Quantity | Price | | 101 | 1 | 1 | 1 | $1000 | | 102 | 1 | 2 | 2 | $50 | | 103 | 2 | 3 | 1 | $80 | | 104 | 2 | 4 | 1 | $300 | Customers Table | CustomerID | CustomerName | CustomerAddress | | 1 | Alice | 123 Main St | | 2 | Bob | 456 Oak St | Products Table | ProductID | ProductName | Price | | 1 | Laptop | $1000 | | 2 | Mouse | $50 | | 3 | Keyboard | $80 | | 4 | Monitor | $300 |

DON'T WANT TO MISS A THING?

Latest Cisco, PMP, AWS, CompTIA, Microsoft Materials on SALE
Get Now

Best DBA Job Interview Questions and Answers | SPOTO

Earn a certification to make your resume stand out.

DON'T WANT TO MISS A THING?

Latest Cisco, PMP, AWS, CompTIA, Microsoft Materials on SALE Get Now

Best DBA Job Interview Questions and Answers | SPOTO

Earn a certification to make your resume stand out.

Latest Cisco, PMP, AWS, CompTIA, Microsoft Materials on SALE
Get Now