Common DBA Interview Questions You Must Know

1

What authentication option should be used for Windows Authentication in SQL Server?

Reference answer

The SQL Server Windows Authentication option should be selected when performing this authentication method in SQL Server.

2

Tell me about a time when you had to implement database changes or upgrades while minimizing disruption to ongoing operations. How did you plan and execute the process?

Reference answer

Look for: Change management and operational continuity skills.

3

What is one of your proudest moments as a DBA so far?

Reference answer

One of my proudest moments as a DBA was successfully migrating a critical production database to a new server with zero downtime, ensuring business continuity during a major system upgrade. This project involved careful planning, testing, and coordination with multiple teams, and it directly improved system performance and reliability, aligning with my company's goals for scalability.

4

Patching and Steps:

Reference answer

Types: Interim Patch, PSU, RU. Steps: - Backup DB and Oracle Home. - Stop services. - Apply patch using opatch or datapatch. - Verify patch. - Start services and validate.

5

If i want to give user a grant on 2 or 3 data dictionary tables how do you provide grant?

Reference answer

GRANT SELECT_CATALOG_ROLE TO username; The SELECT_CATALOG_ROLE role provides read-only access to most data dictionary tables, including the ones you mentioned.

6

What do you understand by In-Memory sort?

Reference answer

All the small data filtering happens in PGA. This is known as In-Memory sort. If the data is big, sorting is done under temp tablespace.

7

How is the Clustered Index different from the Non-Clustered Index?

Reference answer

An index is a schema object, which can search the data efficiently within the table. Indexes can be clustered or non-clustered. Differences include: 1. In a clustered index, table records are sorted physically and stored in a particular order. Hence, a table can have a single clustered index only. While in a non-clustered index, logical sorting happens which does not match the physical order of the records. 2. Leaf node of a clustered index holds the data pages while the non-clustered index holds the index rows.

8

Describe a situation where you had to adapt to a sudden change or unexpected challenge in your work as a Database Administrator. How did you handle it?

Reference answer

Look for: Adaptability and resilience.

9

What is the purpose of the ALTER statement in MySQL?

Reference answer

The ALTER statement is used to modify the structure of a table, such as adding or dropping columns, changing data types, or adding indexes.

10

What is Listener?

Reference answer

Listens for incoming client connections and forwards them to the database server.

11

Can a normal user view public database links?

Reference answer

Yes, using the ALL_DB_LINKS view.

12

What is the distinction between a data block, an extent, and a segment?

Reference answer

- A data block is a database object's basic logical storage unit. As objects expand in size, they require extra storage in the form of continuous data blocks. - Extents are collections of consecutive data chunks. - When all of an object's extents are gathered together, the segment is considered.

13

What is a database?

Reference answer

A database is an organized collection of data that is stored and managed electronically. It allows for efficient data retrieval, insertion, updating, and deletion. Typically, data in a database is organized into tables with rows and columns, where each table represents an entity, and rows represent records. For example, a customer database might have tables for customer information, orders, and transactions.

14

Explain what DBCC means.

Reference answer

Skilled database administrators should know that DBCC is an acronym for database consistency checker. They may explain that the DBCC ensures that the database has logical and physical consistency and that administrators can use various DBCC commands to receive stats, planning, and repair details.

15

Can we change the restore mode of the secondary database from NORECOVERY to STANDBY in SQL Server log shipping configuration?

Reference answer

Yes, we can change the secondary database restore mode from restoring to standby to use it for read-only operations. We will use a system stored procedure sp_change_log_shipping_secondary_database to make this change. This stored procedure will take an input value 1 for argument @restore_mode while running it. There are two values for this argument. Use 1 to restore the log with STANDBY and use 0 to restore the log with NORECOVERY.

16

How to give a tablespace quota to a user?

Reference answer

ALTER USER AISHU QUOTA UNLIMITED ON T;

17

Can you delete the alert log while the database is running?

Reference answer

Yes, Oracle will automatically create a new alert log when needed.rm -f $ORACLE_BASE/diag/rdbms/*/trace/alert_*.log

18

What must be checked before creating a transportable tablespace?

Reference answer

The target system must use the same operating system.

19

What are statistics in Oracle? Why are they important?

Reference answer

Statistics provide information about database objects, helping the optimizer choose the best execution plan. System Statistics: Hardware-related metrics (CPU speed, I/O rates). Object Statistics: Table and index details (row count, block usage, distinct values). SELECT TABLE_NAME, NUM_ROWS, BLOCKS FROM DBA_TABLES;

20

How to restore control file without backup.

Reference answer

If no controlfile binary backup, you can use CREATE CONTROLFILE from a controlfile trace or from metadata:Options: - Create controlfile from trace: - If you have previously done ALTER DATABASE BACKUP CONTROLFILE TO TRACE; , use the trace file to create controlfile. - Steps: Update the trace file to correct file paths and run as startup nomount: - - If no trace file but you have DB file names and structure, manually create a CREATE CONTROLFILE statement with correct file list and run in NOMOUNT. - Use RMAN to restore controlfile from autobackup (if RMAN configured): But question said without backup; that means using trace OR manually constructing create controlfile. If impossible, you must restore from OS-level copies / restore filesystem snapshot. Important: After creating controlfile, you may need RECOVER DATABASE andALTER DATABASE OPEN RESETLOGS . If multitenant or RAC, follow additional steps.

21

When creating a database user, it must be mapped to a login unless it's a contained database.

Reference answer

Yes, that is correct; when creating a database user in regular databases, they must first be assigned to an SQL Server login, or else they won't have access to its services.

22

Describe a situation where you had to troubleshoot and resolve a performance issue caused by a poorly written or inefficient query. How did you identify the problem, and what steps did you take to optimize the query?

Reference answer

Look for: Query optimization and troubleshooting expertise.

23

User SCOTT creates an index with this statement: CREATE INDEX emp_indx on employee (empno). In which tablespace would be the index created?

Reference answer

SCOTT'S default tablespace

24

What does interfile backup parallelism do?

Reference answer

It divides files into multiple sections and takes the backup in parallel.

25

Why did you choose database administration?

Reference answer

Passion is what makes people thrive. But not everyone has an inspiring, amazing story about how they chose a specific career. It's interesting, however, to learn more about a candidate's background and, in this case, learn how they bumped into this career and whether they really enjoy it or not. You can also ask about their education and where they learned about database management.

26

How do you troubleshoot missing ASM disks?

Reference answer

Troubleshooting missing ASM disks involves: • Checking ASM alert logs and V$ASM_DISK view to identify missing disks. • Verifying physical connectivity and disk health at OS level using tools like lsblk, fdisk. • Confirming disk permissions and ownership for Oracle ASM user. • Restarting ASM instance or using ALTER DISKGROUP commands to drop or re-add disks. • Running asmcmd utility to check disk status and reconfigure. • Investigating recent changes in storage hardware or configuration. • Using Oracle support resources if hardware failure or firmware bugs are suspected. Timely troubleshooting prevents ASM disk group degradation and data loss risks.

27

How to check database uptime?

Reference answer

SELECT * FROM v$instance; Look for the STARTUP_TIME column.

28

How to restart the listener?

Reference answer

lsnrctl stop lsnrctl start

29

How do you REBUILD a really large index without leaving the same amount of FREESPACE on the file?

Reference answer

You can rebuild a really large index without leaving the same amount of freespace by using a temporary swap'n'drop filegroup and file. Create a new filegroup and file, rebuild the index with DROP EXISTING on that filegroup, then drop the old filegroup and file, and finally move the index back if needed.

30

What kind of reasons you found for archive logs not shipping from primary to standby db?

Reference answer

Here are the reasons: 1. Network fluctuations: Temporary network connectivity issues or packet losses. 2. High archive log generation: Large volume of archive logs being generated on the primary database.

31

What is a default role?

Reference answer

A role automatically enabled when the user logs on.

32

Describe your experience with on-premises databases.

Reference answer

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

33

What is the role and configuration of TempDB?

Reference answer

TempDB is a system database used for temporary storage of user objects (e.g., temp tables), internal objects (e.g., sorting), and version stores. Its configuration includes setting the number of data files (typically equal to the number of CPU cores) and managing file size and growth to avoid contention and performance bottlenecks.

34

What all can you do with Oracle client software?

Reference answer

We can only use oracle client software to connect a database server. We cannot create a local database using oracle client.

35

Describe your experience with performance tuning in databases. What tools or techniques do you use?

Reference answer

I use tools like SQL Profiler and EXPLAIN plans to identify slow queries and bottlenecks. By implementing indexing strategies and optimizing queries, I've significantly improved database performance, reducing query times by up to 50% in some cases.

36

For Db refresh what kind of process you are following and what refresh you are doing(export or RMAN) full db or schema refresh?

Reference answer

Process 1. Pre-refresh checks: Verify database consistency, backup availability, and downtime requirements. 2. Export/RMAN backup: Create a full database export or RMAN backup of the source database. 3. Transfer backup: Transfer the backup to the target database server. 4. Restore and recover: Restore the database from the backup and recover to the desired point in time. 5. Post-refresh checks: Verify database integrity, consistency, and functionality. Refresh Types 1. Full database refresh: Complete replacement of the target database with the source database. 2. Schema refresh: Refresh of specific schemas or objects within the database. Tools Used 1. RMAN (Recovery Manager) 2. Data Pump Export/Impor

37

What are the prerequisites for creating a standby database?

Reference answer

Same Oracle version, edition, and patch level on both primary and standby. - Primary database in ARCHIVELOG mode. - Key parameters set: LOG_ARCHIVE_CONFIG, LOG_ARCHIVE_DEST_n, FAL_SERVER, etc. - Reliable network connectivity and adequate bandwidth for redo shipping. - Oracle software installed on standby with identical configuration to primary. - Matching directory structure and file permissions on standby. - Baseline standby copy created from primary backup (RMAN or duplicate). - Time sync via NTP between servers. - (Optional) Data Guard Broker enabled for easier management.

38

Will the db_name and db_unique_name will same for primary and standby databases?

Reference answer

DB_NAME is identical for primary and standbys. DB_UNIQUE_NAME is unique for each database.

39

What will you do if both PFILE and SPFILE are deleted? Can you start the database?

Reference answer

If init.ora (PFILE) or spfile is lost, manually create a PFILE using another database's PFILE. Edit the PFILE to match db_name, control_files, etc. Start the database using startup pfile=. Later, create an SPFILE from the PFILE using: CREATE SPFILE FROM PFILE;

40

Some one configured the dataguard setup, without enabling force_logging on primary. And in primary few nologging operations happened . So getting error like data block was loading using NOLOGGINE option. How can We fix it?

Reference answer

In that case, we can get FIRST_NONLOGGED_SCN value from v$datafile on standby database. Then we can take an incremental backup using the SCN from Primary. and apply the same on standby.

41

Describe what a relational database is.

Reference answer

Do your candidates know that a relational database is a database that recognizes the relationship between stored information items? Can they give an example of a relational database, such as SQL? Can they give examples of its features?

42

How can you identify and troubleshoot slow queries in MySQL?

Reference answer

Use tools like EXPLAIN and EXPLAIN ANALYZE to analyze query execution plans and identify performance bottlenecks.

43

What is a primary key?

Reference answer

A primary key is a column or set of columns uniquely identifying each row in a table. It enforces entity integrity, preventing duplicate or null values for identification.

44

How to restore deleted table without backup

Reference answer

Use Oracle Flashback Table or Flashback Drop feature if enabled, otherwise data recovery is not possible without backups

45

What is the use of root.sh & oraInstRoot.sh?

Reference answer

root.sh: 1. Configures Oracle kernel components: root.sh configures Oracle kernel components, such as shared memory and semaphores. 2. Sets environment variables: It sets environment variables, such as ORACLE_HOME and LD_LIBRARY_PATH. 3. Creates symbolic links: root.sh creates symbolic links for Oracle executables. 4. Runs as root user: This script must be run as the root user.oraInstRoot.sh: 1. Configures Oracle inventory: oraInstRoot.sh configures the Oracle inventory, including the central inventory location. 2. Creates Oracle inventory directory: It creates the Oracle inventory directory and sets permissions. 3. Runs as root user: This script must also be run as the root user.

46

Describe your experience with database servers.

Reference answer

I have X years experience managing SQL Server environments, focusing on performance monitoring, tuning queries, implementing robust backup/DR strategies, and ensuring high availability. I've worked with various database sizes and supported development teams.

47

What are the different types of Oracle database shutdown modes?

Reference answer

- Normal: New connections are not permitted in this option, and the database is terminated when all sessions quit. - Immediate: New connections are not permitted, and all ongoing operations are returned. - Transactional: New connections are not permitted, and Oracle will hold until all current processes have been finished. - Abort: This occurs instantly, although the database is not completely shut down. When the database is restarted, it will have to conduct a quick restoration. We can't utilize this in everyday situations.

48

How does REORGANIZE really work and why should it NEVER be used on Random GUID indexes, and why is converting Random GUIDs to NewSequentialID a bad idea in most cases?

Reference answer

REORGANIZE works by physically reordering index leaf pages to reduce fragmentation, but it does not compact pages or change allocation. It should NEVER be used on Random GUID indexes because the random nature causes excessive page splits and fragmentation, making reorganization ineffective. Converting Random GUIDs to NewSequentialID is a bad idea in most cases because it can lead to contention on the last page, causing performance bottlenecks in high-concurrency environments.

49

Who is the owner of a role?

Reference answer

Nobody

50

What affects flashback query time?

Reference answer

The UNDO_RETENTION parameter.

51

With STANDBY_FILE_MANAGEMENT=AUTO, does renaming a datafile on the primary propagate to the standby?

Reference answer

No, renaming a datafile on the primary requires manually executing the rename command on the standby, even with STANDBY_FILE_MANAGEMENT=AUTO.

52

Can we create database snapshots of a secondary database in the availability group?

Reference answer

Yes, database snapshots can be created of primary or secondary databases if their role state is either PRIMARY or SECONDARY. Database snapshots cannot be created if the role state is RESOLVING.

53

What Is the Difference Between Temp Tables and Table Variables in SQL Server?

Reference answer

Temp Tables (#table): Created in tempdb, statistics are collected and used by the query optimizer, explicit clustered and non-clustered indexes can be defined, persist across multiple batches within the same session, visible to nested stored procedure calls. Table Variables (@table): Also stored in tempdb, statistics are not maintained (optimizer always assumes 1 row), cannot have explicit indexes added (only inline constraints), scoped to the declaring batch. Which one to use: For small datasets (a few hundred rows), table variables are fine. For larger datasets, use a temp table. The optimizer can build an accurate plan based on real statistics, which matters significantly when the table is joined, filtered, or aggregated.

54

What is Transparent Data Encryption (TDE) in SQL Server?

Reference answer

Introduced in SQL Server 2008, TDE is a mechanism to protect SQL Server database files from unauthorized access through encryption. TDE can also protect database backups of the instance on which TDE was set up. However, TDE does not encrypt data during transfer over a communication channel.

55

Where do you define the service name in daatabase?

Reference answer

The service name for a PDB is defined in the database and registered with the listener. It is specified in thelistener.ora file or dynamically registered with the listener using theLOCAL_LISTENER orREMOTE_LISTENER parameters.

56

What is local partitioned index and global partitioned index?

Reference answer

A local index is an index on a partitioned table which is partitioned in the exact same manner as the underlying partitioned table. Each partition of a local index corresponds to one and only one partition of the underlying table. A global partitioned index is an index on a partitioned or non partitioned tables which are partitioned using a different partitioning key from the table and can have different number of partitions. Global partitioned indexes can only be partitioned using range partitioning.

57

Can you discuss some of the tools you use for database management and why you chose them?

Reference answer

The tools used for database administration can significantly impact efficiency and productivity. Discuss the tools you're familiar with, why you chose them, and how they have helped in your database administration duties. I've used SQL Server Management Studio extensively due to its robust features for managing SQL Server instances. It offers comprehensive tools for configuration, monitoring, and administration tasks. For MySQL, I prefer phpMyAdmin because it's web-based and user-friendly. Both tools have strong community support, which is crucial for troubleshooting.

58

Can we get how much space is needed in the tempdb database to run DBCC CHECKDB without running its actual execution?

Reference answer

DBCC CHECKDB heavily uses a tempdb database to temporarily store data and perform activities that are needed for integrity check like reading each data page and ensuring there is no corruption. We can use DBCC CHECKDB WITH ESTIMATEONLY statement to predict the estimated amount of tempdb space before its actual execution.

59

What is index fragmentation and how does it affect performance?

Reference answer

Index fragmentation occurs when the logical order of index pages does not match the physical order on disk, leading to increased I/O and slower query performance. It can be addressed by rebuilding or reorganizing indexes, which defragments the index and improves data access efficiency.

60

How undo tablespace can guarantee retain of required undo data?

Reference answer

Alter tablespace undo_ts retention guarantee;

61

Tell me about a time when you had to troubleshoot a complex database issue. How did you approach the problem, and what was the outcome?

Reference answer

In my previous role, we encountered a performance issue where a critical database query was running significantly slower than expected, impacting the overall system performance. To troubleshoot the issue, I analyzed the query execution plan, identified the bottleneck, and optimized the query by rewriting it and creating appropriate indexes. I also collaborated with the development team to refactor the application code that generated the query. As a result, the query performance improved by 70%, and the system response time returned to acceptable levels.

62

How you create the alert in OEM?How do you define that alert in OEM?

Reference answer

How to Create an Alert in Oracle Enterprise Manager (OEM) 1. Navigate to Event Rules in OEM Login to OEM Cloud Control using your credentials. Go to Enterprise → Monitoring → Incident Rules. Click on Create Rule Set to define a new alerting rule. 2. Define the Alert Criteria Select Target Type (e.g., Database, Listener, ASM, Host). Choose Specific Metrics (e.g., Tablespace Usage, CPU Utilization, Database Down). Set Thresholds for Warning and Critical levels: Warning: 85% tablespace full Critical: 95% tablespace full 3. Configure Notifications Specify Actions when an alert is triggered: Send an email notification to DBAs. Trigger an SNMP trap for ServiceNow integration. Execute a custom script (e.g., auto-purge logs if space issue). 4. Save and Enable the Rule Review the rule configuration. Click Save and Enable. The rule will now trigger alerts when the specified conditions are met.How Do You Define an Alert? Alerts are defined based on threshold values for specific metrics in OEM. You can customize alerts for database, storage, OS, or network conditions. Incident Rules help automate responses (email, ServiceNow, scripts).In OEM, I create alerts by defining Event Rules and configuring Metric Thresholds for key parameters like CPU usage, tablespace utilization, and database availability. I ensure that alerts trigger email notifications, SNMP traps, or automation scripts. This helps in proactive database monitoring and issue resolution.

63

Why LGWR writes before DBWR writes?

Reference answer

In general, transaction recording is more important than transaction execution. Lets take, if we have redo entries on disk and suddenly power failure happens (the dirty blocks are not yet written to disk). Then oracle can still recover the transactions by reading the redologs on disk. This way first redo logs are made permanent and then dirty blocks are written to disk. To gain deeper insights into how redo logs work and how to analyze them, check out our detailed guide on Analyzing Redo Logs with LogMiner.

64

Difference between Physical, Logical, Snapshot, and Active Data Guard

Reference answer

Physical Standby - Exact block-for-block replica of primary - Uses Redo Apply for sync - Best for disaster recovery and high data integrity - Can open read-only for reporting Logical Standby - Applies SQL changes, not raw blocks - Can be opened read-write (some restrictions) - Good for reporting and transformations Snapshot Standby - Physical standby switched to read-write temporarily - Great for testing with live-like data - Changes discarded when reverting back to physical standby Active Data Guard - Physical standby with redo apply active while open read-only - Supports real-time queries, backups, and offloading workloads - Ideal for performance + availability combo

65

Explain what PL/SQL means.

Reference answer

Knowledgeable interviewees should know that PL/SQL means procedural and structured query language. They should know that administrators use this query language with Oracle and that it integrates seamlessly with SQL.

66

What is the difference between PFILE and SPFILE? Where are these files located?

Reference answer

PFILE (Parameter File): A text file that stores database initialization parameters. Changes require a database restart.SPFILE (Server Parameter File): A binary file that allows dynamic parameter changes without restarting the instance. Location: $ORACLE_HOME/dbs.

67

How do you know whether Enhanced Database Failover is configured or enabled for your availability group or not?

Reference answer

We can get this information by accessing a dynamic management view sys.availability_groups. This DMV has a column named DB_Failover, if the value of this column is 0 it means enhanced database failover is not enabled and if the value of this column is 1 then enhanced database failover is enabled for that availability group.

68

What is the path of alert log file?

Reference answer

The path of the alert log file in Oracle is:$ORACLE_BASE/diag/rdbms/instance_name/trace/alert.log

69

Explain a one-to-many relationship.

Reference answer

A one-to-many relationship exists when one record in Table A corresponds to one or more records in Table B, while each record in Table B corresponds to only one record in Table A. Example: One Customer has Many Orders.

70

What are the best industry-wide practices about who should own databases and jobs in SQL Server Agent and why?

Reference answer

Databases and jobs in SQL Server Agent should be owned by a dedicated service account or a specific administrative account, not by individual users. This ensures that ownership is consistent, manageable, and secure, preventing issues like broken ownership chains or unauthorized access. Best practices recommend using a low-privilege account for SQL Server Agent jobs to minimize security risks.

71

What is the difference between restore point & guaranteed restore point?

Reference answer

Restore Point: 1. Snapshot of the database: A restore point is a snapshot of the database at a specific point in time. 2. Not guaranteed: A restore point is not guaranteed, meaning that Oracle does not ensure that the database can be restored to that point. 3. Dependent on flashback logs: A restore point relies on flashback logs, which can be deleted if the flashback retention period is exceeded.Guaranteed Restore Point: 1. Guaranteed snapshot: A guaranteed restore point is a guaranteed snapshot of the database at a specific point in time. 2. Ensures restore: A guaranteed restore point ensures that the database can be restored to that point, even if flashback logs are deleted. 3. Not dependent on flashback logs: A guaranteed restore point does not rely on flashback logs and is stored in the control file.

72

How many Guaranteed Restore Points (GRPs) can I create on a database?

Reference answer

The number of GRPs you can create depends on the available space in the UNDO tablespace and the retention policy you have configured.

73

Explain the concept of normalization and denormalization. When would you choose one over the other?

Reference answer

Normalization involves organizing data to reduce redundancy and improve data integrity, which is crucial for transactional databases. Denormalization, on the other hand, is used to optimize read performance by combining tables, and is often chosen for reporting or read-heavy systems.

74

How would you handle database corruption in SQL Server 2022?

Reference answer

Handling database corruption involves first running the DBCC CHECKDB command to identify the extent and location of the corruption. Depending on the severity and nature of the corruption, options might include restoring from a clean backup, using the DBCC CHECKDB with the REPAIR options to fix the corruption, or manually repairing or reconstructing the affected data if possible. It's important to maintain regular, tested backups, and to isolate the cause of the corruption to prevent recurrence.

75

What is RPO and RTO in disaster recovery?

Reference answer

RPO (Recovery Point Objective) is maximum acceptable data loss time. RTO (Recovery Time Objective) is maximum acceptable downtime. They define disaster recovery strategy goals.

76

How to find out whether database/tablespace/datafile is in backup mode or not?

Reference answer

Query V$BACKUP view.

77

What are the two types of data compression in SQL Server?

Reference answer

Row compression and Page Compression.

78

Is it Possible to Change ORACLE Block size after creating database?

Reference answer

No

79

What is row migration?

Reference answer

Row migration, also known as row movement, occurs in Oracle when a row is updated and its new size exceeds the available space in its current block, but unlike row chaining, the entire row is moved to a new block. When a row is updated: 1. Row grows: The row size increases due to the update. 2. No space available: There is not enough space in the current block to accommodate the larger row. 3. Row migration: Oracle moves the entire row to a new block, freeing up space in the original block.Row migration can lead to: – Performance issues: Increased I/O operations to access migrated rows. – Storage inefficiencies: Wasted space due to fragmented rows.To minimize row migration: – Proper table design: Ensure sufficient column sizes and padding. – Regular table maintenance: Monitor and reorganize tables as needed. – Use automatic segment space management: Enable automatic space management to reduce fragmentation. – Use PCTFREE parameter: Set PCTFREE to a suitable value to reserve space for future row growth.

80

How can you optimize query performance in MySQL?

Reference answer

Strategies include using appropriate indexes, optimizing database schema design, rewriting queries, and analyzing execution plans.

81

How to kill a blocking session?

Reference answer

Identify the blocking session:sql SELECT sid, username, serial#, status, event, blocking_session FROM v$session WHERE blocking_session IS NOT NULL; Kill the blocking session using: sql ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

82

What are the contents of control file?

Reference answer

Database name, SCN, LSN, datafile locations, redolog locations, archive mode, DB Creation Time, RMAN Backup & Recovery Details, Flashback mode.

83

How do redo logs work and why are they critical?

Reference answer

Redo logs record all changes made to the database (both committed and uncommitted). They are crucial for instance recovery in case of failure. Oracle uses a circular set of redo log groups, each with one or more members (copies): • When changes are made to the data, redo entries are written to the redo log buffer. • The LGWR process flushes this buffer to redo log files on disk. • If the database crashes, redo logs are used to replay those changes. Redo logs are required to maintain ACID Properties ACID is an acronym for Atomicity, Consistency, Isolation, and Durability — the four key principles that guarantee database transactions are reliable: • Atomicity: A transaction is all-or-nothing. • Consistency: Transactions take the database from one valid state to another. • Isolation: Transactions don't interfere with each other. • Durability: Once a transaction is committed, it must not be lost, even if the system crashes. Oracle performs a log switch when a redo log file is full, starting to write to the next log group. To avoid data loss, it's recommended to have: • Multiple log members (mirrored copies) • Archived redo logs (if the database is in ARCHIVELOG mode) for point-in-time recovery. Redo logs are vital for ensuring that committed transactions are never lost and that the database remains consistent after unexpected failures.

84

What are Oracle's disk components?

Reference answer

- Redo Log Files. - Parameter files. - Control files. - Data files. - Password files.

85

Tell me about a time when you had to train or mentor a team member or colleague on database management principles or specific database technologies. How did you approach the mentoring process, and what were the results?

Reference answer

Look for: Mentoring and knowledge sharing abilities.

86

Find the total number of downloads for paying and non-paying users by date. Include only records where non-paying customers have more downloads than paying customers. The output should be sorted by earliest date first and contain 3 columns date, non-paying downloads, paying downloads.

Reference answer

WITH download_totals AS ( SELECT c.date AS download_date, SUM(CASE WHEN paying_customer = 'yes' THEN downloads END) AS paying, SUM(CASE WHEN paying_customer = 'no' THEN downloads END) AS non_paying FROM ms_user_dimension a INNER JOIN ms_acc_dimension b ON a.acc_id = b.acc_id INNER JOIN ms_download_facts c ON a.user_id = c.user_id GROUP BY c.date ) SELECT download_date, non_paying, paying FROM download_totals WHERE non_paying > paying ORDER BY download_date ASC;

87

How do you identify a performance bottleneck in Oracle?

Reference answer

To find what's slowing down the database, you start by identifying where the problem is—CPU, memory, I/O, locks, or SQLs. Tools like AWR (Automatic Workload Repository), ASH (Active Session History), and v$ views are helpful. You check: • Wait events (v$system_event, v$session_wait) • CPU usage (top, vmstat from OS, and v$osstat) • Sessions waiting (v$session, v$active_session_history) • Expensive SQLs (v$sql, v$sql_monitor) If sessions are waiting on “db file sequential read,” it may be an I/O issue. If you see “CPU time” wait class high, it's a CPU issue. Start with what's affecting performance the most, using the Top 5 Wait Events in the AWR report.

88

What is your experience with cloud-based database solutions?

Reference answer

I have practical experience working with cloud-based database solutions, particularly Azure SQL Database and AWS RDS for SQL Server. I've been involved in deploying, managing, and optimizing these instances, handling tasks like scaling resources, configuring automated backups, and ensuring high availability through geo-replication. My work has shown me the benefits of cloud platforms in terms of scalability and reduced infrastructure management, and I'm comfortable leveraging their managed services to build resilient database environments.

89

Can you describe the various authentication methods available in SQL Server?

Reference answer

SQL Server supports two authentication modes: Windows Authentication: Uses Active Directory accounts and is considered more secure. SQL Server Authentication: Uses SQL Server logins and passwords, which require additional management for security.

90

Logs are filling up too fast — how do you control logging?

Reference answer

To control excessive logging: • Adjust logging levels and enable only necessary audit or debug logs. • Use log rotation and archival policies to manage log size. • Monitor log generation patterns and identify noisy processes. • Implement filters to exclude less important information. • Regularly archive and purge old logs. • Use Enterprise Manager or scripts to automate log management.

91

What is an image copy in RMAN?

Reference answer

Image copies are exact copies of datafiles, including free space. They are not stored in RMAN backup pieces but as actual datafiles, making them useful for moving a database from non-ASM to ASM.

92

What is PGA or Private Global Area? How it is different from SGA?

Reference answer

Any work inside a system or server is done through background processes. These processes need some memory to store basic information. On database server, we have one server process created for every user connection. These server process also takes some memory. This memory is known as PGA. SGA is shared global area. It is one of the parts of Oracle database instance and resides on RAM. Anything placed in SGA is shared with all the users. Read further on how to find memory used by an Oracle instance

93

If you had to work in a team with people with difficult personalities, and constant conflict, how would you manage it?

Reference answer

You can have a strong personality with clear leadership skills and critical thinking but still, be able to be empathic and listen to the other team members. Whether your candidate has a strong or a more relaxed personality, what you're looking to learn with this question is how they cope with different opinions. It will help you better understand a candidate's personality traits and how they might deal with conflict.

94

In which situation do database administrators use SQL agents?

Reference answer

Interviewees who understand SQL databases will know that SQL agents enable administrators to execute administrative tasks or jobs according to a schedule in the SQL server. It's a job automation service of Microsoft Windows, but administrators can also use it manually.

95

How to create a password file?

Reference answer

orapwd file=$ORACLE_HOME/dbs/pwaishu.ora entries=5 ignorecase=Y;

96

How many databases are there in your environment?

Reference answer

Say any value between 150 to 200 and tell them 40 are prod and rest all are dev, test and QA.

97

What Are SQL Server's Isolation Levels, and When Would You Use Each?

Reference answer

READ UNCOMMITTED: Permits dirty reads. Use only when approximate data is acceptable and maximum read throughput is the priority. READ COMMITTED (SQL Server default): Reads only committed data, but does not hold shared locks for the full duration of the query. REPEATABLE READ: Holds shared locks on all data read until the transaction completes. Prevents non-repeatable reads, but phantom rows can still appear. SERIALIZABLE: The most restrictive level. Prevents dirty reads, non-repeatable reads, and phantom reads using range locks. Use it when absolute correctness is required. SNAPSHOT: Uses row versioning in tempdb instead of locks. Readers do not block writers; writers do not block readers. Trade-off: Read Committed Snapshot Isolation (RCSI) is the pragmatic choice for most high-concurrency SQL Server OLTP environments. It eliminates most blocking with no application-level changes, at the cost of additional tempdb usage for row versioning.

98

How to add or remove a datafile?

Reference answer

Add Datafile:ALTER TABLESPACE T ADD DATAFILE '/u01/oradata/paddu/tbs1.dbf' SIZE 100M; Drop Datafile (only if empty): ALTER TABLESPACE T DROP DATAFILE '/u01/oradata/paddu/tbs1.dbf';

99

How do you switch roles between the primary and standby databases?

Reference answer

On Primary: ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY; On Standby: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY; Restart both databases.

100

How do you get I/O statistics of a specific database file like the number of reads, writes, IO stalls, etc.?

Reference answer

We can use either the system dynamic management function sys.dm_io_virtual_file_stats or a system function fn_virtualfilestats to get this information. We need to pass database and file id details about that specific database file for which we are looking for IO statistics. Below is its example. I am pulling IO statistics for the master database log file. SELECT * FROM sys.dm_io_virtual_file_stats(db_id, file_id); GO SELECT * FROM fn_virtualfilestats(dbid,fileid)

101

What are the ACID properties?

Reference answer

ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure that transactions are processed reliably: Atomicity guarantees that all operations within the work unit are completed successfully; Consistency ensures that the database properly changes states upon a successfully committed transaction; Isolation ensures that transactions are securely and independently processed simultaneously without interference; Durability guarantees that once a transaction is committed, it will remain so, even in the event of a power loss, crash, or error.

102

What is the objective of the COALESCE function in SQL Server?

Reference answer

COALESCE returns the first non-NULL value from a list of expressions. It's useful for handling NULL values by providing a default. Example: SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value'); This would return 'third_value'.

103

How do you stay organized and manage your workload effectively as a Database Administrator?

Reference answer

As a Database Administrator, I prioritize my workload by assessing the urgency and impact of each task. I create a detailed task list or use project management tools to track deadlines and progress. Additionally, I allocate dedicated time for routine tasks such as backups, maintenance, and performance monitoring to ensure they are consistently addressed. I also practice effective time management techniques, such as setting realistic deadlines, breaking down complex tasks into smaller manageable steps, and maintaining clear communication with stakeholders regarding project timelines.

104

What are the different stages of the database startup process?

Reference answer

Oracle startup involves three primary stages: NOMOUNT: Starts the instance by reading the initialization file but does not open the database. MOUNT: Associates the instance with the database and reads control files. OPEN: Fully opens the database, making it accessible for users and applications.

105

Can you provide an example of where you can or should use a foreign key?

Reference answer

A foreign key is used to establish a relationship between two tables. For example, in a database containing ‘Employees' and ‘Departments' tables, the ‘DepartmentID' in the ‘Employees' table could be a foreign key referencing ‘Departments.'

106

What Steps Would You Take if You Notice a Sudden Drop in Database Performance?

Reference answer

1. Initial Assessment: Query sys.dm_exec_requests to see what is actively running and sys.dm_os_wait_stats to understand what the server is waiting on. 2. Identifying the Scope: Determine whether the slowdown is isolated to one query, one database, or the entire instance. 3. Analyzing Specific Areas: Pull actual execution plans for the slow queries and compare them to estimated plans. Look for operators with warnings, index scans where seeks are expected, large key lookups, and spills to tempdb. 4. System-Level Checks: Check for blocking chains using sys.dm_os_waiting_tasks and sys.dm_exec_requests. Also check Page Life Expectancy (PLE). 5. Addressing Identified Issues: For a plan regression, force the last known stable plan using Query Store. For a missing index, add it after validating it does not disproportionately slow writes. For blocking, identify the head blocker and evaluate whether the long-running transaction can be broken into smaller units. 6. Preventive Measures: Set up SQL Server Agent alerts on key thresholds. Enable Query Store with a retention period long enough to provide meaningful baselines. Schedule regular index maintenance. 7. Documentation and Review: Record the root cause, the fix, and the before/after performance impact.

107

What is the difference between FULL and BULK-LOGGED recovery models?

Reference answer

In the FULL recovery model, every transaction is logged fully, allowing point-in-time recovery. BULK-LOGGED minimizes logging for bulk operations, but point-in-time recovery isn't possible during minimal-logged operations.

108

How do you ensure database security?

Reference answer

Database security is ensured through a combination of measures including implementing strong authentication mechanisms, managing user roles and privileges using the principle of least privilege, encrypting data at rest and in transit, regularly applying security patches, auditing database activity, and using firewalls and network segmentation to restrict access.

109

What is AFFIRM/NOAFFIRM in dataguard?

Reference answer

AFFIRM: – Ensures data is written to disk (I/O is completed) before sending an acknowledgment back to the primary. – Offers stronger data protection (synchronous I/O). – Used with Maximum Protection or Maximum Availability modes. – Slower but safer.Example use: sql LOG_ARCHIVE_DEST_2='SERVICE=standby AFFIRM ...' NOAFFIRM: – Acknowledges redo as soon as it reaches standby memory, not waiting for disk write. – Faster but less secure (possible data loss on standby failure). – Common with Maximum Performance mode.Example use: sql LOG_ARCHIVE_DEST_2='SERVICE=standby NOAFFIRM ...'

110

What monitoring tools do you use to track database health and performance?

Reference answer

I rely on a combination of tools for database monitoring. I frequently use SQL Server Management Studio's built-in performance dashboards and Dynamic Management Views (DMVs) for real-time insights into CPU, memory, and I/O. For more comprehensive, historical analysis and proactive alerting, I have experience with dedicated monitoring solutions like SolarWinds DPM. These tools help me track key metrics, identify bottlenecks, and get alerted to potential issues before they impact users or system performance.

111

What is an extent in Oracle?

Reference answer

An extent is a set of contiguous blocks allocated in a database. In the Oracle database program, the first set of contiguous blocks, set up automatically when a segment is created, is called the initial extent. After the initial extent has been filled, the program allocates more extents automatically. These are known as the next extents. The total number of extents that can be allocated in a database is limited by the amount of storage space available, or in some cases, by the program used.

112

How can you get information about existing users in the password file?

Reference answer

You need to query the "v$pwfile_users" view to get information about the existing users in the password file. Execute the SQL query below: Sql>SELECT * FROM v$pwfile_users; The query above will return four columns for each user in the password file. The column names are USERNAME, SYSDBA, SYSOPER, and SYSASM.

113

Can you explain the concept of indexing and how it improves query performance?

Reference answer

Indexing is a database optimization technique that creates a data structure (such as a B-tree or hash index) for one or more columns, allowing the database engine to quickly locate rows without scanning the entire table. This significantly improves the performance of SELECT queries, especially on large tables, by reducing disk I/O. However, indexes can slow down write operations (INSERT, UPDATE, DELETE) and require careful planning to avoid over-indexing.

114

What do SYSTEM and SYSAUX tablespaces contain?

Reference answer

SYSTEM Tablespace: Stores core database objects, dictionary tables, and metadata. SYSAUX Tablespace: From Oracle 10g onward, offloads some SYSTEM tablespace content, including AWR, Oracle session statistics, and execution plans.

115

How LRU algorithm impacts database instance?

Reference answer

RAM works on LRU algorithm. As the database instance resides on RAM, it has to follow same rules as RAM. Hence, Oracle instance also follow LRU algorithm.

116

Explain how you would set up database replication for disaster recovery.

Reference answer

Choose replication type based on requirements: Synchronous for zero data loss but higher latency, or asynchronous for better performance with potential data loss during failures. Configure the setup: Set up master-slave replication with the primary database in the main data center and replicas in geographically distant locations. Implement monitoring to detect replication lag and failures. Plan failover procedures: Document step-by-step failover processes, automate where possible, and regularly test failover scenarios. Consider split-brain scenarios and have clear procedures for determining which database is authoritative.

117

What is an incarnation number?

Reference answer

When we open the database in RESETLOGS mode, a new incarnation number is created. This resets the log sequence number to 1, and online redo logs get a new timestamp and SCN.

118

What role should a manager play for a database administrator?

Reference answer

A manager for a DBA should primarily act as a facilitator and strategic guide. They should ensure we have the necessary resources and tools, help prioritize projects, and provide direction aligned with business goals. I also value a manager who fosters professional development and provides constructive feedback, creating an environment where DBAs can continuously grow and excel in maintaining robust and secure database systems.

119

What is the role of an underwriter?

Reference answer

The task of underwriters is to review insurance applications and carry out risk analysis to assist the companies in determining whether to provide insurance to clients.

120

Oracle Data Guard Architecture:

Reference answer

Components: - Primary DB - Standby DB - Log Transport Services (LNS) - Log Apply Services (MRP/LSP) - Data Guard Broker (optional) - Uses Redo Apply or SQL Apply.

121

Can we replicate SQL Server data to other RDBMS systems like Oracle?

Reference answer

Yes, SQL Server replication can be used to publish data from SQL Server to Oracle or Oracle to SQL Server.

122

What are logical components of Oracle database?

Reference answer

Blocks, Extents, Segments, Tablespaces

123

What is database client?

Reference answer

A database client is a small software which must be installed on application server so that application can connect to database server. For any client to connect oracle database, oracle client must be installed.

124

How do you recover from an RMAN backup?

Reference answer

Start RMAN: rman target / Mount the database (if needed): STARTUP MOUNT; Restore and recover: RESTORE DATABASE; RECOVER DATABASE; Open the database: ALTER DATABASE OPEN;

125

Explain the concept of ACID properties in MySQL.

Reference answer

ACID properties (Atomicity, Consistency, Isolation, Durability) ensure reliability and data integrity in transactions.

126

What are the advantages of ORDBMS?

Reference answer

The items themselves can be saved in the database. The DBMS language can be used with an OOPs language. The language used in the program may even be the same, removing the need for the developer to have two versions of his objects.

127

What constitutes an Oracle Instance?

Reference answer

The SGA is a distributed memory that several programs can use. This section contains data that the instance needs to function. System Global Area (SGA), a distributed memory space on RAM, and applications running make up an instance. The running processes are OS processes, and every program in the instance has a specific role to play. When an instance is 'started,' it creates the GAS and runs processes. The processes are destroyed and the common memory space is returned to the operating system when the instance status is 'shut off.'

128

How do you determine user access levels for different roles?

Reference answer

I determine user access levels based on the principle of least privilege, ensuring users only have the necessary permissions for their specific job functions. I work closely with application owners and business units to understand their exact needs. Then, I define specific database roles (e.g., 'Read-Only Analyst', 'Application Writer') with granular permissions, and assign users to these roles rather than granting individual permissions. This approach simplifies management, enhances security, and ensures clear segregation of duties within the database environment.

129

What is Oracle Flashback Technology?

Reference answer

Flashback technology allows recovery of data without restoring backups. Common commands:Flashback a table: FLASHBACK TABLE table_name TO TIMESTAMP (SYSDATE - 1); Flashback a dropped table: FLASHBACK TABLE table_name TO BEFORE DROP;

130

How do you take an RMAN full backup?

Reference answer

RMAN> BACKUP DATABASE PLUS ARCHIVELOG; This command takes a full database backup along with archived redo logs.

131

How do you find the 'worst' queries and how do they usually differ from the 'longest running' queries?

Reference answer

The 'worst' queries are typically those with the highest resource consumption, such as CPU, I/O, or memory usage, while 'longest running' queries are those with the highest duration. They differ because a query can be long-running but consume few resources (e.g., waiting on locks), or it can be short but resource-intensive (e.g., a high-CPU query). You can find them using DMVs like sys.dm_exec_query_stats or tools like sp_WhoIsActive.

132

What is the LIKE operator used for in SQL?

Reference answer

In order to compare parts, we use the LIKE operator which acts like a regex engine for a database.

133

What are database indexes, and why are they used?

Reference answer

Indexes are database objects that enhance the speed of data retrieval operations. They function by creating a quick lookup mechanism for data based on one or more columns in a table, much like an index in a book helps you find information quickly. Namely, indexes reduce the amount of disk I/O needed to access data, thereby boosting overall database performance. Here's a table illustrating different types of indexes in SQL and their use cases: | Index type | Description | Use case | | Clustered index | Determines the physical order of the data in the table. | Primary key columns where sorted data access is essential. | | Non-clustered index | Creates a separate structure with pointers to the data. | Frequently queried columns like email or date_of_birth. | | Unique index | Ensures that all values in the index are unique. | Ensuring uniqueness in fields like email or username. | | Composite index | Indexes multiple columns in combination. | Queries filtering on multiple columns, like first_name and last_name. | | Full-text index | Facilitates fast text searches in large text fields. | Searching through large text fields like description or comments. |

134

Is running tests on live databases advisable? Why or why not?

Reference answer

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

135

How to set pga size, can you change it while the database is running?

Reference answer

You can set the PGA size using the PGA_AGGREGATE_TARGET parameter, and it can be modified while the database is running.To check the current value: To change the PGA size dynamically: Yes, PGA_AGGREGATE_TARGET is a dynamic parameter, meaning you can adjust it without restarting the database.

136

What are prechecks before adding disk into diskgroup.

Reference answer

Prechecks: - Check device visibility (OS layer) and partition alignment. - Correct permissions for devices and owner oracle . - Ensure device not mounted or in use. - Check disk redundancy policy (NORMAL, HIGH) and matching sector size. - Check ASM compatibility and cluster state (clusterware must be up). - Check existing diskgroup free space and rebalance impact. - Check unique disk names; ensure disk discovered by ASM ( asmcmd lsdsk ). - Backup ASM metadata with asmcmd or use ASM diskgroup metadata backup commands. - Set correct diskstring in asm_diskgroups if using udev rules. - Plan for rebalance load: adding disks triggers rebalance which will consume resources. Command to add: -

137

How would you optimize a slow-running query without changing the application code?

Reference answer

First, analyze the execution plan to identify bottlenecks—look for table scans, expensive joins, or sort operations. Check if statistics are up to date, as outdated statistics can lead to poor execution plans. Evaluate indexing opportunities: Add covering indexes for SELECT queries, consider composite indexes for multi-column WHERE clauses, or partial indexes for frequently filtered subsets of data. Consider database configuration: Adjust memory settings, query timeout values, or parallelism settings. For specific databases, tune parameters like sort_buffer_size in MySQL or max_worker_threads in SQL Server.

138

The choice of a clustering key columns of a clustered index is crucial for performance. Name at least two characteristics that a good clustering key should have.

Reference answer

UNIQUE, SLIM SIZE (small width in bytes), EVER INCREASING, fixed width, not null

139

Name some best practices you use as a database administrator.

Reference answer

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

140

Can a DBA restart a Data Pump job if it stops?

Reference answer

Yes.

141

How does the process configure SQL Server to use static and dynamic ports?

Reference answer

If an error arises, connect to the current SQL Server with its existing connection details, restart its services and define 9222 as a dynamic port if specifying one in IP all; otherwise, leave it blank so SQL Server can pick an active port independently.

142

How do you rebuild an index and why?

Reference answer

Rebuilding an index reorganizes and defragments it to improve performance and space usage. Why rebuild: • When an index is fragmented due to many insertions, updates, or deletes. • To recover space after large data deletions. • To improve query performance by restoring index efficiency. • After importing large data loads or mass updates.How to rebuild: ALTER INDEX index_name REBUILD; This creates a new copy of the index and replaces the old one without affecting database availability. You can also rebuild with options like parallelism or online rebuild for minimal downtime.

143

Which factors made you choose a career as a DBA?

Reference answer

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

144

What is the difference between Windows Authentication and SQL Server Authentication?

Reference answer

Windows Authentication uses a user's Windows login for SQL Server access. SQL Server Authentication uses a separate login name and password managed directly within SQL Server.

145

I have 4 multiplexed copies of control files under /u01. Do you suggest to keep more copies or 4 are enough?

Reference answer

We must have minimum 2 multiplexed copies but they must be on different physical disks. You have kept all 4 files under /u01. If we loose /u01 mount point, we loose all multiplexed copies.

146

How would you identify the SHARED_POOL_SIZE parameter that needs to be adjusted?

Reference answer

Below is the indications for the same: 1.Getting an ORA-04031 error. 2.Degrading the performance even when all the other parameters are already optimized. 3.Poor library cache/data dictionary hits.

147

Does listener require for DBA users

Reference answer

No, DBA users can connect locally via SQL*Plus without listener using OS authentication.

148

That OEM server do you manage is there any other team that manages OEM?

Reference answer

If you do not manage the OEM server While I don't directly manage the OEM server's infrastructure, I do configure event rules, set up alert notifications, and integrate it with tools like ServiceNow. If there are any issues with OEM itself—such as agent communication failures, repository database issues, or performance slowdowns—I coordinate with the respective team for resolution.If you do manage the OEM server, you can modify the response: Yes, I manage the OEM server along with the DBA team. We are responsible for: Installing and configuring Oracle Enterprise Manager Managing the repository database Deploying and troubleshooting OEM agents on target servers Configuring event rules, alert thresholds, and integrations (e.g., ServiceNow) Upgrading and patching the OEM environment when needed

149

How to check database blocking sessions?

Reference answer

SELECT blocking_session, sid, serial#, wait_class, seconds_in_wait FROM v$session WHERE blocking_session IS NOT NULL;

150

Why do you need to do open resetlogs, and what does it do?

Reference answer

Resetlogs is required after performing incomplete recovery (or point-in-time recovery) because the database might not have all redo and archive logs up to the point of failure. Opening with resetlogs initializes new online redo log files, starts a new log sequence, and resets the log history.

151

What are the differences between DELETE and TRUNCATE commands?

Reference answer

DELETE removes rows from a table based on the WHERE condition and can be rolled back. TRUNCATE removes all rows from a table without logging the individual row deletions, thus it's faster but cannot be rolled back.

152

Tell me about yourself and why you are suitable for this Database Administrator position.

Reference answer

Thank you for giving me the opportunity to be interviewed for this Database Administrator position with your company today. Before applying for the role, I read the job description carefully to ensure I had the skills, qualities, and experience to perform to a high standard. My technical knowledge of databases and query languages is strong, I am an excellent problem solver who can work quickly under pressure, and I always put the needs of my employer and their commercial objectives first. I would describe myself as a very good communicator and listener, someone who can be relied upon to adhere to company policies and procedures, and I always take ownership of my ongoing professional development whilst keeping up to date with changes to data protection laws and database technologies. If you hire me in this Database Administrator position, I will be flexible in my work to make sure I am available to help out at short notice when needed, I will be an outstanding team worker and collaborator, I will utilize meticulous attention to detail skills, and I will plan and organize my work to ensure I always give you a positive return on your investment.

153

What is Multi instance Redo Apply? How can we enable this?

Reference answer

Prior to 12.2 , If your dataguard is RAC, then MRP process can be run only on one node. However From 12.2 Onward, We can enable MIRA( Multi instance Redo apply), i.e MRP can be run on multiple instances or all the nodes. Below are the syntax:-- Start MRP from all the instances available: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION INSTANCES ALL; -- Start MRP on 2 Instances: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION INSTANCES 2;

154

What's your approach to automating database maintenance tasks?

Reference answer

My approach to automating database maintenance centers on using the SQL Server Agent to schedule routine jobs. This includes setting up automated backups with verification, daily integrity checks using DBCC CHECKDB, and scheduled index rebuilds/reorganizations to maintain performance. For more complex or custom tasks, I'll often write PowerShell scripts that can be invoked by the Agent. I always ensure these automated tasks include robust logging and alerting mechanisms so any failures are immediately identified and addressed.

155

What is the main function of the redo log in Oracle?

Reference answer

Redo log's main function is to store all changes made to the database as they occur.

156

In SQL, what is a Common Table Expression? Give an example of a situation when you would use it.

Reference answer

A Common Table Expression (CTE) is a named, temporary result set defined using the WITH keyword at the start of a query. It exists only for the duration of that query and can be referenced like a regular table within the SELECT, INSERT, UPDATE, or DELETE that follows. Example: Finding each user's most recent order before filtering: WITH latest_orders AS ( SELECT user_id, order_id, order_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) AS rn FROM orders ) SELECT user_id, order_id, order_date FROM latest_orders WHERE rn = 1;

157

When a session changes data, where does the change get written?

Reference answer

To the data block in the cache, and the redo log buffer

158

Why disconnect and set the static port to zero in the SQL Server configuration manager?

Reference answer

This allows SQL Server to choose any available port to optimise system functionality.

159

What was the impact of AIR on screening time according to a talent leader?

Reference answer

AIR cut screening time by 85%, reducing initial candidate assessments from 3 weeks to 2 days. The conversational interviews feel natural and candidates actually prefer them over phone screens.

160

What is Oracle Data Guard and how does it work?

Reference answer

Oracle Data Guard maintains one or more standby databases in sync with a primary by shipping and applying redo logs. It provides disaster recovery and high availability, supporting automatic failover and multiple protection modes for balancing performance and data safety

161

How many types of indexes are there?

Reference answer

In Oracle, there are several types of indexes:1. B-Tree Index: The most common type, used for columns with high cardinality. 2. Bitmap Index: Used for columns with low cardinality, storing a bitmap of rows. 3. Text Index: Used for full-text search, indexing text data. 4. Function-Based Index: Used to index the result of a function or expression. 5. Unique Index: Used to enforce uniqueness on a column or set of columns. 6. Composite Index: Used to index multiple columns. 7. Descending Index: Used to index data in descending order. 8. Reverse Key Index: Used to improve performance for range scans

162

What are the steps to apply an Oracle patch?

Reference answer

Check the patch details: opatch lsinventory Stop database services (if required). Apply the patch: opatch apply Verify the patch application: opatch lsinventory Restart the database and services.

163

What was the effect of AIR on candidate NPS?

Reference answer

Candidate NPS went from 32 to 78 after implementing AIR. Candidates love the flexibility of interviewing on their own schedule, and the AI adapts to each person naturally.

164

What is a stored procedure?

Reference answer

A stored procedure is a precompiled collection of SQL statements and optional control-of-flow statements stored under a name and processed as a unit. Stored procedures can be used to encapsulate complex business logic and improve performance by reducing network traffic.

165

How can you take a SQL Server cluster group offline?

Reference answer

In Cluster Administrator, right-click on the SQL Server Group, and from the popup menu choose to Take Offline.

166

How do you perform a switchover and failover?

Reference answer

Switchover is a planned role change between primary and standby with no data loss, used for maintenance or load balancing. Failover is unplanned, promoting standby to primary after a failure, possibly with data loss. Both can be done via Data Guard Broker (SWITCHOVER TO / FAILOVER TO) or manually using SQL commands.

167

How do you approach communicating complex technical issues to non-technical stakeholders?

Reference answer

When communicating with non-technical stakeholders, I focus on presenting the issue in terms that they can understand, such as its impact on the business. For example, instead of discussing query optimization and execution plans, I would explain how a slow database is causing delays in order processing, which could affect customer satisfaction. I also use visual aids like charts or graphs to demonstrate performance improvements after changes have been made. This approach helps bridge the gap between technical and non-technical team members and ensures everyone is on the same page.

168

You want to retrieve information about account expiration dates from the data dictionary. Which view do you use?

Reference answer

DBA_USERS

169

Difference between a Data Dictionary table and a regular table?

Reference answer

Data dictionary tables store metadata about database structures, users, and privileges, whereas regular tables contain user-generated data.

170

Which technical skills do you use most often as a database administrator?

Reference answer

Applicants may have top query language knowledge, such as SQL skills. They may be particularly experienced with the installation and configuration of databases. Ask follow-up questions to see how these skills have helped applicants complete projects in the past.

171

Can i generate awr report in a standby database? If yes how?

Reference answer

Yes, you can generate an AWR (Automatic Workload Repository) report on a standby database, but only if the standby is opened in read-only mode or in Active Data Guard mode (licensed separately).? Key Conditions: | Requirement | Details | |————————–|————————————————————————-| | Database Open Mode | Must be OPEN READ ONLY (or use Active Data Guard) | | License Required | Active Data Guard license (for reporting while standby is open) | | DBA Privileges | You must have SELECT_CATALOG_ROLE or DBA privileges | | AWR Snapshot | AWR snapshots are taken automatically only on the primary. You can create them manually on standby if needed.✅ How to Generate AWR Report on Standby: 1. Open standby in read-only mode (or ensure ADG is enabled): sql ALTER DATABASE OPEN READ ONLY;2. Find snapshot IDs: sql SELECT SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME FROM DBA_HIST_SNAPSHOT ORDER BY SNAP_ID;3. Run AWR report script: On the standby, use: “`bash @$ORACLE_HOME/rdbms/admin/awrrpt.sql

172

What is a trigger?

Reference answer

A trigger is a special type of stored procedure that automatically fires when a specific event occurs in the database, such as an INSERT, UPDATE, or DELETE on a table.

173

What is Database Mail in SQL Server?

Reference answer

Database Mail is a feature in SQL Server that allows sending email messages from the database engine. It uses SMTP servers to deliver emails and can be configured to send alerts, reports, or notifications from SQL Agent jobs or stored procedures, providing a way to communicate with administrators.

174

What is the purpose of the SHOW command in MySQL?

Reference answer

The SHOW command is used to display information about various database objects, such as databases, tables, and columns.

175

What were some of the new features released in SQL Server 2005 compared to 2000?

Reference answer

SQL Server 2005 brought in some significant changes from 2000. Key features that stood out to me included database partitioning, which greatly improved performance for large datasets, and Dynamic Management Views (DMVs) for real-time monitoring. It also introduced SQL Server Integration Services (SSIS) for more robust ETL processes, making data handling much more efficient.

176

What is Enhanced Database Failover in Always on availability group?

Reference answer

Microsoft has added a new feature in SQL Server 2016 availability groups where you can configure your databases participating in availability groups to failover if they are unable to do write operations. Earlier versions of SQL Server were not triggering failover if any database from the availability group was unable to write operation. It is also called database level health detection in the availability group. This is an optional feature that can be configured during creating an availability group or you can even configure it by running the below ALTER statement for existing availability groups. --Enable Enhanced database failover on an existing availability group ALTER AVAILABILITY GROUP [AGNAME] SET (DB_FAILOVER = ON) --Disable Enhanced database failover on an existing availability group ALTER AVAILABILITY GROUP [AGNAME] SET (DB_FAILOVER = OFF)

177

Key RMAN commands you use frequently?

Reference answer

RMAN (Recovery Manager) is Oracle's built-in backup and recovery tool. As a DBA, you'll rely heavily on these commands: • BACKUP DATABASE; — takes a full backup of the database. • BACKUP INCREMENTAL LEVEL 1 DATABASE; — incremental backup saving space and time. • RESTORE DATABASE; — restores datafiles from backup. • RECOVER DATABASE; — applies archived logs to bring database to a consistent state. • SHOW ALL; — displays RMAN configuration. • DELETE OBSOLETE; — cleans up old backups no longer needed. • REPORT NEED BACKUP; — tells which files need backup.

178

Describe a challenging database migration you've managed.

Reference answer

I managed a challenging migration from an on-premise SQL Server 2012 instance to Azure SQL Database for a critical financial application. The biggest hurdle was ensuring minimal downtime during the cutover for a 24/7 service. We meticulously planned the migration using a combination of transactional replication for continuous synchronization and then performed a final cutover during a brief maintenance window. Extensive pre-migration testing, including load tests, and rehearsing the failover process multiple times were key to its success, ensuring data integrity and a smooth transition.

179

Can BCT be enabled on a standby database?

Reference answer

Yes, enabling BCT on a standby database improves incremental backup performance, reducing the impact on the primary database.

180

Changing the database to archive log mode:

Reference answer

Bring the database to mount mode and use: sql ALTER DATABASE ARCHIVELOG;

181

RMAN backup you are taking on daily basis its a cron job or is there any third party tool that you are using?

Reference answer

We use a combination of both:Oracle's built-in scheduling tool: DBMS_SCHEDULER We schedule RMAN backups using DBMS_SCHEDULER, which is a built-in Oracle tool for scheduling jobs. This allows us to manage and monitor backups directly within the Oracle database.Cron job for additional automation We also use a cron job to automate the backup process, which calls the RMAN script to perform the backup. This provides an additional layer of automation and ensures that backups are executed consistently.Third-party tools for monitoring and management While not directly used for scheduling backups, we utilize third-party tools like Oracle Enterprise Manager (OEM) for monitoring and managing our Oracle databases, including backup and recovery operations. OEM provides a centralized platform for monitoring, reporting, and automating database administration tasks.

182

What is ASM, and why is it used?

Reference answer

ASM (Automatic Storage Management) is a volume manager and file system for Oracle databases that simplifies storage management. It provides striping and mirroring, improving performance and availability.

183

DR drill has been failed at time of switchover activity what are the steps to make it database available.

Reference answer

If a DR drill (planned switchover or failover test) fails mid-way: - Assess state: determine whether partial switchover occurred: check roles on both databases: - If primary still primary: attempt to stop and rollback any in-progress operations and restart normal apply/log shipping. - If roles changed partially: If primary became standby and standby became primary but was not fully consistent, you may need to: - If target DB is now primary and is open read/write: ensure other databases are reinstated as standbys (resync). - - If incomplete and databases are inconsistent: - Consider Flashback Database to revert to pre-switchover point (if flashback enabled). - Or restore from backup or re-synchronize using RMAN incremental backups. - - Reinstate or rebuild the failed standby: - Use DGMGRL> reinstate database ; if broker used. - Use rman target to duplicate or recover datafiles from primary backup. - - Bring services back and update configurations (TNS, services). - Validate: run test queries and confirm archivelog shipping & apply working. - Document root cause and fix (timeouts, network, ORA errors).

184

What is the difference between a system database and a user database?

Reference answer

A system database is used by the SQL Server to manage system-level information, such as configuration settings and metadata, while a user database is created by users to store application-specific data. System databases are essential for the operation of the SQL Server instance, whereas user databases are designed for custom data storage and retrieval.

185

What are the steps you follow the software and the db from scratch on linux?

Reference answer

General Steps to Set Up an Oracle Database on Linux (from Scratch):Step 1: Prepare the Linux Server 1. Ensure Linux System is Up to Date: bash sudo apt update && sudo apt upgrade -y # For Ubuntu/Debian sudo yum update -y # For RHEL/CentOS2. Install Dependencies: Install the required packages for Oracle installation. bash sudo yum install -y oracle-database-preinstall-19c # For Oracle 19c3. Set Hostname and Configure Network: Make sure your system's hostname is set appropriately. bash sudo hostnamectl set-hostname your-db-hostname4. Ensure proper firewall configuration: You need to open ports for Oracle's listener and other services. “`bash sudo firewall-cmd –zone=public –add-port=1521/tcp –permanentStep 2: *Install Oracle Database Software* Step 3: *Create and Configure the Oracle Database* Step 4: *Verify the Database Installation* Step 5: Configure Oracle Database for Production -

186

Index is a Space demanding Object?

Reference answer

Yes

187

What improvements does SQL Server 2022 offer for query performance insights?

Reference answer

SQL Server 2022 offers enhanced query performance insights through its Query Store feature, which now provides more detailed historical query data to help identify performance degradation and optimize query execution. The Intelligent Query Processing feature suite also contributes by automatically adapting execution plans based on changing data patterns without needing manual intervention. Additionally, SQL Server Management Studio includes more advanced tools for analyzing query performance, such as Live Query Statistics and the Performance Dashboard.

188

We have a database in full recovery model, and transaction log file size of 100 GB. We have a long running transaction, running for many hours. If the last log backup was taken just now, how big the log backup file will be if we take another log backup immediately after this one?

Reference answer

0MB (or close to zero)

189

What is disaster recovery in SQL Server?

Reference answer

Disaster recovery in SQL Server involves strategies and processes to restore database operations after a catastrophic failure. Techniques include backups, log shipping, database mirroring, Always On availability groups, and failover clustering, each designed to minimize data loss and downtime.

190

What is the difference between static and dynamic init.ora/SPFILE parameters?

Reference answer

Static parameters: Require an instance restart to take effect.Dynamic parameters: Can be changed while the database is running and take effect immediately.

191

How can we identify the resources for which the sessions are waiting?

Reference answer

We can find it out using v$session_waits and v$ system _waits

192

What happens during Oracle instance startup and shutdown?

Reference answer

When you start an Oracle database, it goes through three main stages: NOMOUNT, MOUNT, and OPEN. • In NOMOUNT: Oracle starts the instance by allocating memory (SGA) and starting background processes. It reads the initialization parameter file (spfile or pfile). No access to database files yet. • In MOUNT: Oracle reads the control file, which contains information about datafiles and redo logs. The instance is associated with the database but it's not available for users. • In OPEN: Oracle opens datafiles and redo logs, checks their consistency, and makes the database available for users. Read/write operations can now occur. Shutdown can happen in four ways: • Normal: Waits for users to disconnect. • Immediate: Rolls back uncommitted transactions and shuts down quickly. • Transactional: Waits for current transactions to complete. • Abort: Immediate termination without cleanup; requires instance recovery on next startup. This process ensures controlled access and safe recovery in case of failures.

193

What are the responsibilities of a DBA?

Reference answer

A Database Administrator (DBA) is responsible for: Installing and configuring SQL Server. Managing database access and security. Performing backups and recovery operations. Monitoring and optimizing database performance. Ensuring data integrity and compliance with regulations.

194

What happens if the user exists in SQL Server?

Reference answer

A username and password combination will allow users to connect securely for Windows authentication login credentials to connect.

195

Do you have experience working with on-premises databases, cloud databases, or both?

Reference answer

Some companies still use on-premises databases, so your interviewer will want to hear more about your experience with this if you've worked with on-premises databases. Others have switched entirely to the cloud, while some are transitioning. But having experience with both types could be valuable to your future employer if they'll be making that transition and are looking for help with it. Explain how long you've worked with each type of database and what tasks you performed.

196

What is Data Masking?

Reference answer

Data masking is a very important concept to keep data safe from any breaches. Especially, for big organizations that contain heaps of sensitive data that can be easily compromised. Details like credit card information, phone numbers, and house addresses are highly vulnerable information that must be protected. To understand data masking better we first need to know what computer networks are.

197

How do you handle database scalability and capacity planning?

Reference answer

Look for: Understanding of scaling strategies and capacity management.

198

Does Data Pump (expdp) support Flashback?

Reference answer

Yes.

199

How do you connect to the SQL Server from another computer?

Reference answer

To gain access from another computer to SQL Server from its instance and server, one requires permission and then uses Shift + Right Click or opens up SQL Server Management Studio's file location while holding Shift down; once done, the app will run as cluster admin user account.

200

What is the difference between media recovery & crash recovery?

Reference answer

Media recovery is the process of recovering the database from the backup whenever a disk failure is there. Physical files like data files, control files or server parameter files get recovered during media recovery. However, crash recovery will be performed whenever a database instance failure occurs. Media recovery needs to be performed by DBA while crash recovery is an automated process that is taken care of SMON background process.

DON'T WANT TO MISS A THING?

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

Common DBA Interview Questions You Must Know | 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

Common DBA Interview Questions You Must Know | SPOTO

Earn a certification to make your resume stand out.

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