Basic DBA Interview Questions for Beginners

1

Can you tell what types of outer joins exist in SQL Server 2016?

Reference answer

LEFT, RIGHT, FULL

2

How do you enable and configure Unified Auditing?

Reference answer

Unified Auditing consolidates all audit types into a single infrastructure, improving performance and manageability. To enable: • For new installations of Oracle 12c and later, Unified Auditing is enabled by default. • For earlier versions or upgrades, you might need to enable the unified audit trail by running the enable script as SYSDBA. Configuration: • Use CREATE AUDIT POLICY to define what actions to audit (e.g., logins, object access). • Enable policies with AUDIT POLICY policy_name;. • View audit records in UNIFIED_AUDIT_TRAIL view. Unified Auditing supports fine-grained, user, and system auditing in one place.

3

When granting the system privilege, which clause enables the grantee to further grant the privilege to other users or roles?

Reference answer

WITH ADMIN OPTION

4

A critical table was accidentally dropped — what steps do you follow?

Reference answer

Steps to recover a dropped table: • Check if Flashback Table is enabled; if yes, use FLASHBACK TABLE to restore it. • If not, use RMAN to perform point-in-time recovery or restore from backup. • You may also use Export/Import if recent exports exist. • Inform users and coordinate downtime as needed. • Verify table integrity and dependent objects after recovery.

5

We did failover, and the standby became the new primary. Now we need to make the old primary as new standby. But the database is very high , So rebuilding it will take time. Is there any way to fix it?

Reference answer

There is a way, But only if flashback is enabled. If flashback is enabled , then we can get the current scn from the new primary database and flashback the old primary(new standby) upto that scn.

6

How regularly would you perform tests to ensure data privacy?

Reference answer

I would perform regular audits and tests, at least quarterly, to ensure that all security measures are effective and up-to-date.

7

What types of data replication are supported in SQL servers?

Reference answer

SQL Server supports three types of data replication: snapshot replication, transactional replication, and merge replication. Snapshot replication distributes data exactly as it appears at a specific moment in time. Transactional replication continuously distributes changes from a publisher to subscribers. Merge replication allows multiple sites to make changes independently and then merge them into a single consistent dataset.

8

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

Reference answer

I start by understanding the Recovery Point Objective (RPO) and Recovery Time Objective (RTO) for the system. Based on that, I plan full backups, transaction log backups, and implement high-availability features like AlwaysOn or failover clusters. Regular testing of recovery steps is critical.

9

What are the different modes to start up an Oracle database?

Reference answer

You can start up a database with the modes below:

10

Describe a time when you had to optimize a database's performance. What strategies did you employ, and what were the results?

Reference answer

Look for: Specific optimization strategies and measurable outcomes.

11

How do you handle data loss?

Reference answer

To handle data loss, I first assess the scope and impact of the loss by reviewing logs and backups. I then initiate a recovery process using the most recent full backup, followed by differential and transaction log backups to restore data to the point of failure. I also troubleshoot the root cause to prevent future occurrences and communicate the recovery status to stakeholders under pressure.

12

Have you worked with large-scale database migrations? If so, what challenges did you encounter and how did you overcome them?

Reference answer

Look for: Experience with migrations, problem-solving skills, and ability to handle data integrity.

13

How do you handle the loss of data during a database migration?

Reference answer

The interviewer will ask you this question to assess how you would handle a database migration and what you would do if any problems occurred. Since data migrations present challenges and there's always a risk data will become lost or corrupted, this is an important topic to cover. You should talk through the precautions you take before the migration to help prevent problems, specifically putting proper failover and recovery processes in place. Also, you can mention how migrating data in smaller increments rather than on large migration is the best way to minimize data loss. You also need a system for validating the migrated data. This allows errors to be detected and managed faster before continuing the migration process. While it won't eliminate loss, it will significantly lower the risk.

14

How do users connect to SQL Server using Excel?

Reference answer

Users can connect to SQL Server using Excel by right-clicking Excel, selecting Data from the menu, clicking connections, and adding their connection, they can browse the options available before selecting New Source, as described herein.

15

Can you change the blocksize once the database is created?

Reference answer

Obsolutely no becoz once the datafiles is formatted into 8k we cannot change the database block sizes , if you need, you have to create fresh database with new block size and restore from backup or import.

16

What is the role of a data dictionary?

Reference answer

The Data Dictionary is a collection of tables and views maintained by Oracle to store metadata about the database. It includes: • Information about tables, indexes, columns • User accounts and privileges • Storage structures • Constraints, triggers, and sequences It is stored in the SYSTEM tablespace and updated automatically by Oracle when you create, modify, or drop objects. Common views: • USER_ views: Info for current user (e.g., USER_TABLES). • ALL_ views: Info about objects accessible by user (e.g., ALL_OBJECTS). • DBA_ views: Full metadata for DBAs (e.g., DBA_USERS). The data dictionary is essential for Oracle to function correctly and is used heavily in scripting, automation, audits, and troubleshooting.

17

What is the difference between Central Inventory and Local Inventory?

Reference answer

Central Inventory: A global repository that tracks all Oracle installations on a server. Local Inventory: Specific to a single Oracle home and contains patching details for that installation.

18

How does AIR conduct interviews and deliver results?

Reference answer

AIR conducts enterprise-scale AI voice interviews and delivers structured scorecards to your ATS automatically.

19

How did AIR help with hiring nurses?

Reference answer

AIR screens candidates 24/7, conducts initial interviews in 16 languages, and time-to-fill dropped from 45 to 12 days for hiring 500+ nurses per quarter.

20

Startup and shutdown modes of Oracle database:

Reference answer

Startup modes: NOMOUNT, MOUNT, OPEN. Shutdown modes: CLOSE, DISMOUNT, SHUTDOWN.

21

Count how many claims submitted in December 2021 are still pending. A claim is pending when it has neither an acceptance nor rejection date.

Reference answer

SELECT COUNT(*) AS pending_claims FROM claims WHERE FORMAT(submitted_date, 'yyyy-MM') = '2021-12' AND acceptance_date IS NULL AND rejection_date IS NULL;

22

What is a private database link in Oracle?

Reference answer

A private database link is created for a specific user. It is only used when the owner of the link specifies a global object name in a SQL statement or in the definition of the owner's views or procedures.

23

Explain OMF? Have you worked on OMF?

Reference answer

Oracle Manged Files enables us to create tablespaces without providing file names and locations. But the problem is the naming convention of the files. No, my environment does not use OMF feature.

24

How can we monitor the space allocations in a database?

Reference answer

We can use the below data dictionary tables to monitor the space allocations:; 1.DBA_FREE_SPACE 2.DBA_SEGMENTS 3.DBA_DATA_FILES

25

Why use an SQL agent?

Reference answer

A SQL agent performs the job scheduling function. You can set jobs to run at a specific time or when a particular event occurs. In most cases, SQL agents are used to schedule repetitive administrative jobs. One example of this is a daily database backup.

26

What are the differences between INNER JOIN, LEFT JOIN, and RIGHT JOIN in SQL?

Reference answer

- An INNER JOIN returns only the rows with a match between the two tables based on the join condition. - A LEFT JOIN returns all the rows from the left table and the matched rows from the right table; if there is no match, NULL values are returned for the columns from the right table. - A RIGHT JOIN is similar to a LEFT JOIN, but it returns all the rows from the right table and the matched rows from the left table, filling in NULLs where there is no match. These joins are used to combine data across multiple tables, and choosing the right join depends on the specific use case. For example, a LEFT JOIN might be used to get a list of all customers, even those without orders, while an INNER JOIN would only return customers who have placed orders. Here's a practical example: Table: Customers | CustomerID | Name | Country | | 1 | Alice | USA | | 2 | Bob | UK | | 3 | Charlie | Canada | Table: Orders | OrderID | CustomerID | OrderAmount | | 101 | 1 | $200 | | 102 | 2 | $150 | | 103 | 4 | $300 | Result of INNER JOIN: Only returns rows where there is a match between the Customers and Orders tables. | CustomerID | Name | OrderID | OrderAmount | | 1 | Alice | 101 | $200 | | 2 | Bob | 102 | $150 | Result of LEFT JOIN: Returns all customers, including those with no orders, with NULLs for unmatched rows. | CustomerID | Name | OrderID | OrderAmount | | 1 | Alice | 101 | $200 | | 2 | Bob | 102 | $150 | | 3 | Charlie | NULL | NULL | Result of RIGHT JOIN: Returns all orders, including those with no matching customer, with NULLs for unmatched rows. | CustomerID | Name | OrderID | OrderAmount | | 1 | Alice | 101 | $200 | | 2 | Bob | 102 | $150 | | NULL | NULL | 103 | $300 |

27

Explain the concept of character sets and collations in MySQL.

Reference answer

Character sets define how characters are stored and sorted, while collations determine the order of characters within a character set.

28

What are the main features introduced in SQL Server 2022?

Reference answer

SQL Server 2022 introduces several new features, including Azure Synapse Link integration, Ledger for SQL Server, containing Availability Groups, enhanced Query Store, and improvements in security, performance, and availability.

29

What is the default port for SQL Server?

Reference answer

1433.

30

What is the distinction between a database and an instance?

Reference answer

The physical files that hold the data and information that compose up a database are referred to as database files. The control files, data files, and redo log files are among them. Whereas, The storage that is distributed and accessible by all threads and background processes is referred to as an instance.

31

What is the difference between /etc/oratab file and ps -ef|greppmon output?

Reference answer

ps -ef | grep pmon gives output of running database instances on a server. /etc/oratab file lists all the databases created on a server, weather they are running or shutdown is a different case.

32

What are Data Guard-related background processes?

Reference answer

MRP: Managed Recovery Process (applies redo on standby). RFS: Remote File Server (receives redo on standby). LNS/NSS/NSA: Log/Network Service (sends redo; NSS for SYNC, NSA for ASYNC in 12c+). DMON: Data Guard Broker monitor. FSFP: Fast-Start Failover Process.

33

Explain ACID properties and their importance in database transactions.

Reference answer

ACID stands for Atomicity, Consistency, Isolation, and Durability. Atomicity ensures all operations in a transaction complete successfully or none do—no partial updates. Consistency guarantees that transactions only leave the database in a valid state according to defined rules. Isolation prevents concurrent transactions from interfering with each other, and Durability ensures committed changes survive system failures. I configure isolation levels based on business requirements—using READ_COMMITTED for most operations but READ_UNCOMMITTED for certain reporting queries where dirty reads are acceptable for performance gains.

34

What is the command to add a datafile to a tablespace?

Reference answer

ALTER TABLESPACE tablespace_name ADD DATAFILE 'datafile_name' SIZE 5M;

35

How would you optimize a SQL query?

Reference answer

First, I would analyze the query execution plan to identify any performance bottlenecks. Indexing is a primary method for improving query performance, so I would ensure that the necessary indexes are in place for columns used in the WHERE clause, JOIN conditions, and ORDER BY clauses. Another approach is to avoid using SELECT * and instead specify only the columns needed, which reduces the amount of data retrieved. Additionally, I would look at rewriting complex queries into simpler subqueries or using temporary tables to break down the query into manageable parts. For instance, instead of using correlated subqueries, I might use JOINs to enhance performance. A table can help you remember the various techniques for optimizing SQL queries: | Optimization technique | Description | Example or application | | Indexing | Add indexes on columns used in WHERE, JOIN, and ORDER BY. | Create an index on customer_id for faster lookups. | | Avoiding SELECT * | Retrieve only the necessary columns to reduce the data being processed. | Use SELECT name, email instead of SELECT *. | | Query execution plan analysis | Use execution plans to find bottlenecks and identify missing indexes. | Analyze query performance using EXPLAIN. | | Avoiding correlated subqueries | Replace correlated subqueries with JOINs for better performance. | Replace WHERE EXISTS with INNER JOIN. | | Limiting rows with LIMIT | Use LIMIT to restrict the number of rows returned, especially during testing. | Use SELECT * FROM orders LIMIT 100. |

36

What is Block Change Tracking (BCT)?

Reference answer

When taking an incremental backup, RMAN typically scans the entire database to identify changed blocks, which can be time-consuming for large databases. By enabling Block Change Tracking (BCT), RMAN maintains a record of changed blocks and backs up only those blocks, avoiding a full database scan, which significantly improves backup performance.

37

Can we change the refresh interval of a materialized view?

Reference answer

Yes.

38

What third party database tools are your favorites?

Reference answer

If somebody's been doing database administration long enough to claim the title Senior DBA, they've built up a little wish list of database management tools they've seen along the way. Tool types might include: - Data modeling - Change management - Backup compression - Performance monitoring - Alerting If they had a $X tool budget for their workstation, how would they spend it? Forget corporate standards – I want to know what tools they'd use if they could pick on their own.

39

During a database migration how would you handle data loss?

Reference answer

Data loss is one of the situations where database administrators experience the most pressure, especially if a migration project falls behind schedule. This question will help you learn more about how a candidate behaves under pressure and stress, and more importantly, what strategies they use to overcome this situation — understanding how to troubleshoot and recover data is a crucial function all DBA roles should know how to handle.

40

Difference between Backup Piece and Backup Set?

Reference answer

Backup Set: A logical structure where the backup is stored, containing one or multiple database files, SPFILEs, control files, etc. Backup Piece: A physical file within a backup set, stored in binary format.

41

SQL HAVING Clause with Examples

Reference answer

The HAVING clause was introduced in SQL to allow the filtering of query results based on aggregate functions and groupings, which cannot be achieved using the WHERE clause that is used to filter individual rows.

42

Explain the concept of binary log in MySQL.

Reference answer

The binary log records all changes to the database, allowing for replication, backup, and point-in-time recovery.

43

How would you differentiate between cluster and grid?

Reference answer

Clustering is an integral part of grid infrastructure and focuses on a specific objective. While grid, which may or may not consist of multiple clusters, possesses a wider framework that enables sharing of storage systems, data resources and remaining others across different geographical locations. A cluster will have single ownership but the grid can have multiple ownership based on the number of the cluster it holds.

44

How can you collect data (by email, activity tracking, etc.)?

Reference answer

It's a necessary step to check the different ways you can collect data. However, you firstly need to audit your data flows properly and verify you're satisfying all the legal obligations, such as obtaining proper consents for processing, providing the data subject with the information specified by GDPR, etc.

45

Can you explain what a transaction is in SQL Server?

Reference answer

A transaction in SQL Server is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased.

46

How do you analyze and resolve high CPU usage in Oracle?

Reference answer

Start by identifying which queries or processes are consuming most CPU: Run: SELECT * FROM v$sql ORDER BY cpu_time DESC; to find top SQLs by CPU. • Use top and ps -ef from OS to see which Oracle processes are heavy. • Look into plans of SQLs causing high CPU—bad plans, missing indexes, or high parse rate can be reasons. • Also check: o Statistics are stale? o Bind peeking or plan instability? o Too many context switches? Use SQL tuning, plan baselines, or rewriting inefficient queries to reduce load.

47

Describe what taking a database offline means.

Reference answer

In the context of database administration, taking a database offline means temporarily removing it from an active state, rendering it unreachable to users and applications. When a database is taken offline, the database engine disallows new connections and terminates current ones. This action can be used for a variety of administrative and maintenance duties. Here's an overview of what putting a database offline entails: - No User Access: When a database is taken offline, users and programs are no longer able to connect or interact with it. Attempts to access the database while in this offline state will result in an error. - Administrative tasks: Taking a database offline is frequently used to undertake administrative procedures that require exclusive access to the database. This might include things like running a backup, installing updates or fixes, or migrating database files. - Maintenance Operations: Maintenance procedures, such as rebuilding indexes, updating statistics, or making substantial changes to the database structure, may necessitate taking the database offline to guarantee data consistency and prevent any conflicts.

48

How to check database SCN?

Reference answer

select current_scn from v$database.

49

What is Oracle Multitenant architecture (CDB/PDB)?

Reference answer

Oracle Multitenant was introduced in version 12c. It allows a single Container Database (CDB) to host multiple Pluggable Databases (PDBs). CDB contains: • Shared components like background processes, memory (SGA), and control/redo/datafiles. • At least one Root Container and Seed PDB (PDB$SEED). PDBs contain: • User objects like tables, views, indexes. • Application-specific data. Benefits: • Easier consolidation of databases. • Simplified patching and upgrades. • Resource isolation between PDBs. • Fast cloning and unplug/plug options for migrations. CDB/PDB model helps organizations manage hundreds of databases more efficiently. It's now the default architecture in Oracle 21c and is mandatory in Oracle 23c.

50

Can you give me references from other DBAs and developers who aren't at your company?

Reference answer

I want to know that they've got at least a couple of people they can call when the going gets rough and the servers catch fire. These other references could be mentors, or could be people they've mentored or just worked with along the way. I expect to get terrified looks, and I'd answer those by saying, “I don't want to raise any red flags by calling people at your current employer, and I'm sure you know people who've left your company and moved on. I just want to talk to people who've worked with you on projects or on problems. I won't ask for your level of technical competency, because these other guys can't judge that. I just want to know you've interacted with them.”

51

What authentication modes does SQL Server support?

Reference answer

SQL Server supports Windows Authentication and mixed-mode. Mixed-mode allows you to use both Windows Authentication and SQL Server Authentication to log into your SQL Server. It is important to note that if you use Windows Authentication, you will not be able to log in.

52

The alert log will never contain information about which database activity?

Reference answer

Performing operating system restore of the database files

53

What is a SQL Server Agent?

Reference answer

SQL Server Agent is a Microsoft Windows service that executes scheduled administrative tasks, which are called jobs in SQL Server. It can manage job scheduling, alert system, and operators for notifying event responses.

54

What are dgmgrl commands for Data Guard operations?

Reference answer

DGMGRL (Data Guard Manager command-line) is used to manage Data Guard environments—ensuring disaster recovery and data availability. Common commands: • SHOW CONFIGURATION; — view overall Data Guard setup and status. • SHOW DATABASE ‘dbname'; — check individual database status in the DG setup. • ENABLE CONFIGURATION; and DISABLE CONFIGURATION; — activate or deactivate Data Guard. • SWITCHOVER TO ‘dbname'; — switch primary and standby roles gracefully. • FAILOVER TO ‘dbname'; — emergency failover if primary fails. • VALIDATE DATABASE ‘dbname'; — check health and sync status. • EDIT DATABASE ‘dbname' SET PROPERTY; — modify database properties like log transport.

55

Describe your approach to database monitoring and alerting.

Reference answer

Monitor key metrics: CPU usage, memory consumption, disk I/O, connection counts, query response times, and database-specific metrics like buffer cache hit ratios or lock waits. Set up meaningful alerts: Configure thresholds based on baseline performance—alert when response times exceed 2x normal levels or when disk space drops below 20%. Avoid alert fatigue by tuning thresholds carefully. Use monitoring tools: Implement database-specific monitoring (like SQL Server Management Studio, MySQL Enterprise Monitor) and integrate with broader infrastructure monitoring (Nagios, Zabbix, or cloud-native solutions).

56

What is the transaction log in SQL Server?

Reference answer

The transaction log is a critical component of SQL Server that records all transactions and database modifications. It ensures data integrity by supporting rollback and recovery operations, and it is used for point-in-time restoration. Managing its size and growth is essential to prevent performance issues.

57

Multiple Tablespaces can share a single datafile?

Reference answer

No

58

What are the types of Data Guard setups and their differences?

Reference answer

Physical Standby: An exact block-for-block replica of the primary, synchronized via redo apply. Supports read-only mode (Active Data Guard) for reporting. Ideal for high availability and disaster recovery. Logical Standby: Maintains logical equivalence using SQL Apply, allowing read-write operations and different physical structures. Suitable for reporting and database upgrades. Snapshot Standby: A temporary read-write physical standby for testing, reverting to physical standby after use, discarding changes.

59

What is ORA-01555 – snapshot too old error and how do you avoid it?

Reference answer

ORA-01555: snapshot too old error occurs when a query attempts to access a snapshot of data that is no longer available in the undo tablespace. This happens when:1. A long-running query requires a read-consistent view of data. 2. The undo retention period is too short, causing the required undo data to be overwritten.To avoid ORA-01555: 1. Increase undo retention period: Set the UNDO_RETENTION parameter to a higher value to retain undo data for a longer period. 2. Increase undo tablespace size: Expand the undo tablespace to store more undo data. 3. Tune queries: Optimize long-running queries to reduce their execution time and minimize the need for old snapshots. 4. Use GUARANTEE option: Use the GUARANTEE option when creating the undo tablespace to ensure that enough space is allocated for undo data. 5. Monitor undo tablespace usage: Regularly monitor undo tablespace usage and adjust parameters as needed.

60

How to patch Oracle RAC database.

Reference answer

General steps (high-level): - Read patch README carefully and prerequisites. - Backup: full DB backup, backup ORACLE_HOME, and take GI/ASM backups if needed. - Put cluster in maintenance: stop services using srvctl that could interfere. - Apply patch to Oracle Grid Infrastructure first (if required by patch): - On GI home run OPatch or runInstaller for GI; run root scripts as needed. - Use patchmgr oropatch in GI home. - - Apply patch to database ORACLE_HOME on one node: - Stop DB instances on that node or follow rolling upgrade approach. - - Rolling patching: For RAC, perform a rolling patch to minimize downtime: - Patch one node at a time, srvctl stop instance -d -n , apply patch, rundatapatch (if SQL changes), start instance on that node, then move to next node. - - Run datapatch to apply SQL changes to the database (often required after binary patch). - Validate services and opatch lsinventory . - Check cluster status: crsctl stat res -t andsrvctl status database -d . - Complete root scripts on each node as required. - Test application connectivity, do smoke tests. Always follow specific patch README for switching off/on services and running opatch with -local or-applyRolling .

61

What is the difference in installing SQL Server failover cluster between SQL Server 2005 and 2008 or above?

Reference answer

On SQL Server 2005, installing the SQL Server failover cluster is a single-step process where the installation installs on all nodes. On SQL Server 2008 or above, it is a multi-step process where you need to install separately on all nodes (e.g., 2 times for a 2-node cluster, 3 times for a 3-node cluster).

62

What is the purpose of a password file, and why is it required?

Reference answer

The credentials of the database users are saved in the database's data dictionary. When the user tries to request access to the database, it compares the user's account and passcode to the data contained in the database. Once The user is only allowed database access if the account and passcode match. We can access the data dictionary as long as the database is open. This dictionary also contains the credentials for admins. You can't access the dictionary once the database is shut down. Because starting up a closed database is one of the executive's jobs, there has to be a means for admins to login into the database even when the server is down. A password file is a distinct OS file that is kept on a disc and is not connected to the server. It stores the login details for SYSDBA and SYSOPER clients. While the database is offline, the password file is used to verify admins with those rights.

63

What is the difference between CHAR and VARCHAR data types in MySQL?

Reference answer

CHAR is a fixed-length string data type, while VARCHAR is a variable-length string data type.

64

How to Draw Entity Relationship Diagrams

Reference answer

In the world of database design, entity relationship diagrams serve as valuable tools for designing complex systems and their relationships. In this article will go through the step-by-step process of designing an ER diagram, and defining how entities, attributes, and relationships are defined. Entity relationship diagrams are extremely important in database design and require a clear structure of all data. Entity Relationship Diagram for BANK We are following the below steps to design an ER diagram: - Defining Entities - Adding Attributes - Establishing Relationships - Specify Cardinality - Identify Primary Keys - Draw the ER Diagram - Benefits of ER Diagram

65

Explain the ACID properties in database transactions.

Reference answer

ACID properties are pivotal to maintaining database reliability, especially in transaction processing. You should understand and explain the concept behind Atomicity, Consistency, Isolation, and Durability, their importance in database transactions, and the role they play in ensuring data integrity and consistency. ACID properties are Atomicity, Consistency, Isolation, and Durability. Atomicity ensures that a transaction is completely processed or not at all to prevent partial updates. Consistency maintains the rules of the database, ensuring that each transaction brings the database from one consistent state to another. Isolation keeps transactions separate until completed to avoid conflicts. Durability guarantees that once a transaction is confirmed, it will remain committed even in the event of system failure.

66

What is ASM (Automatic Storage Management)?

Reference answer

Oracle's volume manager and file system optimized for DB files.

67

What are the different responsibilities of a Oracle DBA?

Reference answer

Oracle DBA is the database administrator who performs all administrative tasks. Administrative tasks include: 1 .User-level administration i.e. creates users, remove existing users or modifies user permissions. 2 .Maintains database security. 3 .Manages database storage & objects. 4 .Tunes performance of a database. 5. Performs backups & recovery tasks.

68

How to revoke privileges or roles?

Reference answer

To revoke an object privilege: REVOKE SELECT ON T FROM AISHU; To revoke a role: REVOKE SURESH FROM AISHU; -- SURESH is a role here

69

What happens if we patch the primary database but not the standby?

Reference answer

It may lead to compatibility and synchronization issues between the two databases, potentially causing replication failures.

70

Which process is involved when a user starts a new session on the database server?

Reference answer

The Oracle server process

71

How to increase speed of patching activity.

Reference answer

Ways to speed patching: - Pre-check and stage patches to each node before execution. - Use local storage rather than slow NFS for ORACLE_HOME. - Ensure OPatch and patch tools are present and up-to-date. - Parallelize non-dependent steps: prepare homes on multiple nodes in parallel. - Disable antivirus or system checks temporarily if they slow file operations (with approvals). - Use snapshots and clone ORACLE_HOME to avoid lengthy extraction each time. - Ensure sufficient CPU, disk I/O before patch; have spare resources. - Run prechecks in advance (opatch prereq) to avoid mid-patch failures. - Apply patches during low activity windows to avoid waiting for services to quiesce. - Keep ORACLE_HOME backups ready so you can quickly rollback without lengthy repair. - Use automation scripts that run root scripts in parallel across nodes (careful with cluster resources). - Use faster patch bundles (choose cumulative RU vs full patching strategy based on guidance).

72

What is the procedure for resolving “ORA-03113: end-of-file on communication channel”?

Reference answer

ORA-03113 means the connection between client and server was unexpectedly terminated. This could be caused by server crash, network issues, or bugs. To resolve: • Check the database alert log and trace files to identify server-side errors or crashes. • Verify network connectivity and firewall settings. • Confirm that the listener is running and accepting connections. • Check for server resource issues like memory, CPU, or disk space. • Apply patches or updates if the problem is caused by bugs. • Restart the database and listener services carefully. • Use SQL*Net tracing for detailed communication logs if needed.

73

What is the difference between '=' and 'LIKE' in SQL?

Reference answer

In '=', the value being returned must match the value it is being compared to. However, you can use LIKE with several different parameters, allowing you to be more flexible in your rules.

74

How do you stay updated with the latest trends and technologies in database management?

Reference answer

I regularly follow industry blogs and forums to stay updated with the latest trends and technologies. Additionally, I attend webinars and conferences, and participate in online courses to continuously enhance my skills.

75

What types of databases do you work with?

Reference answer

There are many different types of database infrastructures, so with this question, you'll be confirming if a candidate has the background you're looking for. For example, if your database is built with MySQL or Oracle, then it's important to see how familiar candidates are with these database servers and their level of expertise.

76

I'm a developer. Explain why I need a unique key on my table.

Reference answer

And really pretend that you're a developer. If you're a DBA manager, bring in one of your toughest developers to play bad cop. Challenge them – does it really improve performance or manageability? How do you know? Is it just your opinion, or where's the proof? If they can explain it in clear, easy-to-comprehend terms, that bodes well for their ability to communicate with other teams.

77

What are the three main components in Replication?

Reference answer

The three main components are Publisher, Distributor, and Subscriber. The publisher is the data source of a publication. The distributor is responsible for distributing the database objects to one or more destinations. The subscriber is the destination where the publisher's data is copied or replicated.

78

What is the difference between restoring a database and recovering a database?

Reference answer

During the restoration process, backup files are copied from the hard disk, media or tapes to the restoration location and later make the database operational. Recovery has an additional step of updating these data files by applying redo logs so as to recover the changes which are not backed up. Let us understand this with the help of a scenario. 1.Database full backup is taken on Friday 11 PM 2.Database crash happened on Saturday 7 AM We can restore the lost files using the 11 PM full backup which is Restoration. However, the data will be restored up till Friday at 11 PM and not till Saturday at 7 AM. In order to do the same, redo logs can be applied which will bring the database to the point of failure.

79

Wait events in Oracle with reason and resolution.

Reference answer

Common wait events: - db file sequential read — single-block reads (index lookups). Cause: physical I/O. Resolution: tune queries, add index, improve I/O. - db file scattered read — multi-block reads (table scans). Cause: full table scans. Resolution: add indexes, tune queries, increase buffer_cache. - log file sync — wait for LGWR to write redo — caused by commit waits. Resolution: reduce commit frequency, tune application, faster redo logs, increase LOG_BUFFER. - log file parallel write — LGWR writing multiple redo members. Resolution: check I/O subsystem for redo logs; ensure redo logs on fast disks. - enqueue waits (enq: TX) — locks for transactions. Cause: uncommitted transactions, row locks. Resolution: find blocking session and resolve. - latch free — contention for internal structures. Resolution: application tuning, parameter tuning, reducing parallelism. - buffer busy waits — contention for blocks. Resolution: reduce hot block access, partition tables, increase freelist. - CPU — CPU bound. Resolution: tune SQL, add CPUs, offload work. - log file switch (checkpoint incomplete) — waiting for checkpoint. Resolution: increase DBWR performance, tune checkpoints, increase redo logs, add more redo groups. For each event: analyze with AWR / ASH andv$session_wait /v$system_event to find root cause and fix.

80

What is a subquery, and how is it different from a JOIN? Provide an example of using a subquery.

Reference answer

A subquery, as the name itself suggests, is a query within another query. It acts like a mini-query that helps to solve a specific part of the main query, and its results are used by the outer query to filter or manipulate the final data. Subqueries are nested queries that filter data based on the results of another query, while joins combine data from multiple tables based on a shared relationship. Subqueries are generally easier to understand but less efficient for large datasets, while joins are more efficient but can be a little complex. For ex: Find all customers who placed orders exceeding the average order amount. Subquery for the above question: SELECT * FROM customers WHERE customer_id IN ( SELECT customer_id FROM orders WHERE amount > (SELECT AVG(amount) FROM orders) );

81

When tables are stored in locally managed tablespaces, where is extent allocation information stored?

Reference answer

Corresponding tablespace itself

82

How do you handle major incidents?

Reference answer

This is a non-technical question to evaluate your dedication, positive attitude, flexibility, and readiness to adopt new changes. You should describe your incident management process, including detection, escalation, resolution, and post-incident analysis.

83

How does SQL Server 2022 support cloud integration?

Reference answer

SQL Server 2022 supports cloud integration primarily through Azure SQL Edge, enabling seamless data streaming, management, and analysis across cloud environments. It also offers enhanced Azure Hybrid Benefit options, allowing customers to use their on-premises SQL Server licenses to manage Azure SQL Database instances at reduced costs. Features like Managed Instance in Azure SQL provide near-complete compatibility for migrating on-premises SQL Server databases to the cloud, facilitating easier and more secure cloud adoption.

84

Describe a time when you had to learn a new database technology quickly.

Reference answer

Situation: Our company acquired another firm that used PostgreSQL while we were primarily a SQL Server shop. Task: I needed to become proficient in PostgreSQL to manage the integrated systems within two months. Action: I created a structured learning plan including online courses, hands-on labs, and connecting with PostgreSQL community forums. I also set up a test environment to practice common administrative tasks and compare them to SQL Server equivalents. Result: I successfully migrated three critical databases and became the go-to person for PostgreSQL questions, eventually leading to a 15% salary increase for my expanded skill set.

85

How do you perform schema and full database export/import using Data Pump?

Reference answer

For schema export/import, I use Data Pump with schemas= in expdp and impdp. For a full database, I use full=y. I store dump files in a directory object with proper permissions, and use parallelism for speed. During import, I can remap schemas or tablespaces and verify results via logs.

86

What is Open Database Connectivity (ODBC)?

Reference answer

You can use Open Database Connectivity to make different frontends talk to different data sources (DSNs). You should know that the specifics available depend on certain factors, such as the type of application being used, the backend to which it is being applied, and the driver being used.

87

Which soft skills do you use as a database administrator?

Reference answer

From attention to detail to problem-solving, soft skills are critical for database administrators. Gauge their skill level by asking them this DBA interview question.

88

What is a transaction in a database?

Reference answer

A transaction is a sequence of operations performed as a single logical unit of work. It ensures that a series of operations are completed successfully or not at all. Transactions are essential for maintaining data consistency and integrity. They follow the ACID properties: Atomicity, Consistency, Isolation, and Durability.

89

Explain to me the process how you backup your database?

Reference answer

A comprehensive backup strategy includes: 1) Performing at least two types of backups: a logical backup (e.g., export using tools like mysqldump or Oracle expdp) and a physical backup (e.g., cold or hot backup). 2) Scheduling nightly backups, either cold (database offline) or hot (online) depending on availability requirements. 3) Storing backups off-site to ensure disaster recovery, not on the same server as the database. 4) Testing backups regularly by restoring them to a test environment. 5) Implementing incremental or differential backups to reduce backup time and storage. 6) Documenting the backup and recovery procedures.

90

How is error handling implemented in SQL Server?

Reference answer

Error handling in SQL Server is implemented using TRY...CATCH blocks in T-SQL. The TRY block contains the code that might cause an error, and the CATCH block handles the error by capturing details like error number, severity, state, and message using functions such as ERROR_NUMBER(), ERROR_MESSAGE(), and ERROR_SEVERITY().

91

How do you keep up with the latest trends and advancements in database technologies?

Reference answer

Database technologies are constantly evolving. It's crucial to stay updated to enhance your skills and bring new ideas to your projects. Discuss the resources you use to keep up with the latest trends and how you apply your learnings. I regularly follow reputed tech blogs, attend webinars, and participate in online forums. I also take online courses to learn new technologies. By staying updated, I've been able to bring new ideas to my projects, such as implementing NoSQL databases for certain use cases and using automation tools for routine tasks.

92

Which resource can not be controlled using profiles?

Reference answer

PGA memory allocations

93

Can you discuss your experience with cloud-based databases and any challenges you faced?

Reference answer

I have worked extensively with AWS RDS and Azure SQL Database, managing large-scale deployments and ensuring high availability. One challenge I faced was optimizing cost while maintaining performance, which I resolved by implementing automated scaling and performance monitoring tools.

94

What are different types of partitions?

Reference answer

In Oracle, there are several types of partitions:1. Range Partitioning: Divides data into partitions based on a range of values for a specific column. 2. List Partitioning: Divides data into partitions based on a list of discrete values for a specific column. 3. Hash Partitioning: Divides data into partitions based on a hash function applied to a specific column. 4. Composite Partitioning: Combines range, list, or hash partitioning to create a multi-level partitioning scheme. 5. Interval Partitioning: An extension of range partitioning, where new partitions are created automatically as new data is inserted. 6. Reference Partitioning: Partitions a table based on a referential constraint to another table. 7. System Partitioning: Allows the database administrator to define partitions manually. 8. Hybrid Partitioning: Combines different partitioning methods, such as range and hash partitioning.

95

How frequently would you carry out tests to keep your data private and secure?

Reference answer

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

96

How do you check the Oracle Database version?

Reference answer

SELECT * FROM v$version;

97

Where do you check the free space of objects?

Reference answer

Query the DBA_FREE_SPACE view: sql SELECT * FROM DBA_FREE_SPACE;

98

Do you work in shifts?

Reference answer

Yes, I do work in shifts. As a DBA, I am responsible for ensuring the availability and performance of the database 24/7. To achieve this, our team works in shifts to provide around-the-clock coverage. This includes night shifts, weekend shifts, and holiday shifts

99

What is datapatch and when is it used?

Reference answer

Datapatch applies the SQL part of an Oracle patch to the database after binary patching. It ensures the dictionary and components are upgraded to match the new binaries, and it's always run after applying PSUs, RUs, or one-off patches.

100

How do you find the database size?

Reference answer

SELECT SUM(bytes)/1024/1024/1024 AS size_in_GB FROM dba_data_files;

101

How would you handle a situation where a database is running slow?

Reference answer

Database performance optimization is a crucial part of a DBA's role. Discuss the steps you'd take to diagnose and resolve performance issues, and any tools or techniques you might use. When a database is running slow, I first look at the server's CPU, memory, and disk I/O utilization. If these are okay, I then examine the database logs and run an EXPLAIN PLAN to identify slow queries. Once identified, I optimize these queries, look for missing indices and unnecessary full table scans. Regular monitoring and proactive optimization can help avoid such situations.

102

What are best practices for proactive DB health checks?

Reference answer

Best practices include: • Regularly review alert logs and system logs. • Monitor key performance metrics (CPU, memory, I/O). • Check space usage for tablespaces, datafiles, and undo. • Validate backups and test recovery procedures. • Apply patches and upgrades during maintenance windows. • Run health check scripts or OEM automated checks. • Analyze wait events and SQL performance. • Review security settings and audit logs. • Document and automate routine checks. Proactive checks help prevent outages and improve reliability.

103

What is the purpose of the SQL Server Management Studio Graphic User Interface and T-SQL script?

Reference answer

The SQL Server Management Studio Graphic User Interface and T-SQL script serve to guide users step-by-step in creating operators in SQL Server.

104

What methods have you used to ensure database scalability?

Reference answer

Database scalability is critical for applications that need to grow over time. Discuss your understanding and experience of horizontal and vertical scaling strategies and how you've implemented them. To ensure database scalability, I've used both vertical and horizontal scaling. Vertical scaling involves adding more resources to the existing server. While it's easier to implement, it has limitations. Horizontal scaling, or sharding, involves distributing the database across multiple servers. While more complex, it allows virtually unlimited scalability.

105

If the archive log files are more than 300 or 400 what you will do to sync? — We can take the incremental backup on primary and apply it to DR to solve the sync issue

Reference answer

If archive log files exceed 300-400, to sync the primary and standby databases:Take Incremental Backup on Primary and Apply to DR 1. Take an incremental backup on the primary database. 2. Transfer the backup to the DR site. 3. Apply the incremental backup to the standby database.This approach helps to: – Reduce the archive log gap between primary and standby. – Re-sync the standby database with the primary. – Avoid manual deletion of archive logs.By taking an incremental backup and applying it to the DR site, you can efficiently re-sync the databases and resolve the sync issue.

106

Can you share an example of a time when you had to work collaboratively with other teams or departments to implement a database-related project or resolve an issue? How did you ensure effective communication and successful outcomes?

Reference answer

In a recent project, we needed to integrate a third-party application with our existing database system. I collaborated closely with the development team, the vendor's technical team, and the business stakeholders to ensure a smooth implementation. I organized regular meetings to align requirements, set expectations, and establish clear communication channels. I facilitated discussions to address technical challenges and ensured everyone was on the same page throughout the project. By fostering effective collaboration and maintaining open lines of communication, we successfully implemented the integration, meeting the project goals and delivering value to the business.

107

What are main steps after database creation manually?

Reference answer

Execute catalog.sql and catproc.sql. Update /etc/oratab file.

108

Describe what a View is in SQL Server.

Reference answer

A view in SQL Server is a virtual table based on the result-set of an SQL statement. It contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

109

How do you handle database backups and disaster recovery?

Reference answer

I implement a comprehensive backup strategy that includes full, differential, and transaction log backups based on recovery point objectives (RPO) and recovery time objectives (RTO). Backups are stored in multiple locations, including off-site or cloud storage, and are regularly tested for restore integrity. For disaster recovery, I design and maintain failover solutions such as Always On Availability Groups, database mirroring, or replication to ensure minimal downtime.

110

How to add redo log groups to a database?

Reference answer

Add a new redo log group using: sql Copy Edit ALTER DATABASE ADD LOGFILE GROUP ‘location' SIZE 50M;

111

What is a correlated sub-query in SQL Server?

Reference answer

A correlated sub-query is a nested query that is linked to the outer query. For example, to find all employees who have not entered their time for the week, you could query the Employee table and use a correlated sub-query with NOT EXISTS, where the inner query relates to the outer query on the employee ID. The inner query is evaluated once per the outer query row.

112

What is the model database used for in SQL Server?

Reference answer

The model database serves as the template for all databases created on the same instance. If the model database is modified, all subsequent databases created on that instance will pick up those changes, but earlier created databases will not. Note that TEMPDB is also created from the model every time SQL Server starts up.

113

To create a Guaranteed Restore Point (GRP), must Flashback Database be enabled?

Reference answer

Yes, Flashback Database must be enabled before you can create a Guaranteed Restore Point.

114

What is the role of the PMON process?

Reference answer

The Process Monitor (PMON) in Oracle is responsible for cleaning up after failed user processes by releasing locked resources, recovering transactions, and ensuring that any session-specific memory is freed up.

115

How many bytes an index key can have at the maximum in SQL Server 2016?

Reference answer

900

116

Where do you see the tablespace information?

Reference answer

SELECT * FROM dba_tablespaces; To view tablespace information in an Oracle database, you can query theDBA_TABLESPACES orUSER_TABLESPACES view, depending on the access level: 1. DBA_TABLESPACES: This view shows tablespaces for the entire database (requires DBA privileges).SELECT tablespace_name, status, contents, extent_management, allocation_type FROM dba_tablespaces; 2. USER_TABLESPACES: This view shows tablespaces for the current user (no DBA privileges required).

117

Can you drop normal tablespaces?

Reference answer

Yes, but you must ensure the tablespace is empty or use INCLUDING CONTENTS or INCLUDING CONTENTS AND DATAFILES clauses to drop it and the associated objects.

118

Explain what are the Right of Access requests

Reference answer

The Right of Access request (Art.15 – GDPR) provides individuals with the ability to know if a data controller is processing their personal data and, if that's the case, what that information is and why it is being processed. Individuals are entitled to a copy of the personal data in question, which doesn't necessarily depend if someone is an employer, worker, or self-employed. The DDPR contains exceptions that mostly focus on questions of public interest (like the investigation of crime). Moreover, the GDPR recital 47 states that fraud prevention is a legitimate interest to process personal data: ‘'The processing of personal data is strictly necessary for the purpose of preventing fraud also constitutes a legitimate interest of the data controller concerned''. Even for the purpose of fraud, the controller still has to prove that legitimate interest applies and that the processing of personal data is necessary.

119

How can you determine if a SQL Server instance is a default or named instance?

Reference answer

Go to SQL Server Configuration Manager. In the left pane, select SQL Server Services. The right side pane displays all SQL Server services/components installed on that machine. If the service is displayed as (MSSQLSERVER), it indicates a default instance; otherwise, the instance name is displayed.

120

Explain Oracle architecture components (SGA, PGA, background processes).

Reference answer

Oracle Database uses a combination of memory areas and background processes to perform its tasks efficiently. The architecture is broadly split into two parts: memory structures and processes. SGA (System Global Area) is shared memory accessible by all sessions. It's used to cache data and reduce disk I/O: • Shared Pool: Caches SQL statements, PL/SQL code, and data dictionary information. • Database Buffer Cache: Holds copies of data blocks read from disk. Speeds up reads and writes. • Redo Log Buffer: Stores change data (redo entries) temporarily before it's written to redo log files. • Large Pool, Java Pool, Streams Pool: Used for RMAN, Java, or stream operations. PGA (Program Global Area) is private memory allocated to each server or background process. It holds session-specific data like sort areas, hash joins, and connection state. Background Processes perform essential tasks: • DBWn: Writes modified (dirty) buffers to disk. • LGWR: Writes redo data to redo logs to ensure recoverability. • CKPT: Signals checkpoints to keep the database consistent. • SMON (System Monitor): Performs recovery after crashes. • PMON (Process Monitor): Cleans up failed user sessions. • Others include ARCn, RECO, MMON, depending on features used. These work together to manage data access, query processing, and ensure high performance and recoverability.

121

What are the different modes of Oracle Data Guard?

Reference answer

Physical Standby: Exact copy of the primary database.Logical Standby: Uses SQL apply to keep standby updated. Snapshot Standby: Read-write mode for testing. Active Data Guard: Allows queries while logs are applied.

122

Explain the concept of user-defined variables in MySQL.

Reference answer

User-defined variables allow you to store temporary values within a session for use in queries and calculations.

123

How do you make webservice calls from SQL Server?

Reference answer

Webservice calls from SQL Server can be made using the sp_OACreate and sp_OAMethod stored procedures to create and invoke COM objects, or by using SQLCLR to create custom functions that call web services. More modern approaches involve using external scripts or integration with tools like Azure Logic Apps.

124

SQL Queries on Clustered and Non-Clustered Indexes

Reference answer

Indexing is a procedure that returns your requested data faster from the defined table. Without indexing, the SQL server has to scan the whole table for your data. By indexing, the SQL server will do the exact same thing you do when searching for content in a book by checking the index page. In the same way, a table's index allows us to locate the exact data without scanning the whole table. There are two types of indexing in SQL. - Clustered index - Non-clustered index Clustered Index A clustered index is the type of indexing that establishes a physical sorting order of rows. Suppose you have a table Student_info which contains ROLL_NO as a primary key, then the clustered index which is self-created on that primary key will sort the Student_info table as per ROLL_NO. A clustered index is like a Dictionary; in the dictionary, the sorting order is alphabetical and there is no separate index page. Examples: CREATE TABLE Student_info ( ROLL_NO int(10) primary key, NAME varchar(20), DEPARTMENT varchar(20), ); INSERT INTO Student_info values(1410110405, 'H Agarwal', 'CSE'); INSERT INTO Student_info values(1410110404, 'S Samadder', 'CSE'); INSERT INTO Student_info values(1410110403, 'MD Irfan', 'CSE'); SELECT * FROM Student_info; Output: ROLL_NO | NAME | DEPARTMENT | |---|---|---| | 1410110403 | MD Irfan | CSE | | 1410110404 | S Samadder | CSE | | 1410110405 | H Agarwal | CSE |

125

Can you describe the process of database normalization and its benefits?

Reference answer

Database normalization is crucial in eliminating redundant data and ensuring data integrity. Demonstrate your understanding of normalization, its purpose, the different normal forms, and how it helps achieve a desirable database structure. Database normalization is a process to minimize redundancy and dependency by organizing fields and table relationships. It involves several stages, called normal forms, each with a certain set of rules. The benefits include avoiding duplication, reducing the database size, and ensuring consistency, hence making data manipulation processes more efficient.

126

What is AlwaysOn Availability Groups in SQL Server?

Reference answer

AlwaysOn Availability Groups provide high availability and disaster recovery by replicating databases across multiple servers. It allows automatic failover, readable secondary replicas, and backup operations on secondary nodes to reduce primary server load.

127

What are the key differences between managing an on-premises database versus a cloud-based database?

Reference answer

Managing on-premises databases requires handling hardware procurement, software installation, and regular maintenance like backups, patching, and monitoring. In contrast, cloud-based databases leverage the cloud provider's infrastructure, offering scalability, built-in high availability, and automated backups. Cloud databases also provide options for scaling resources up or down as needed, without the need to invest in physical hardware. For example, in AWS RDS, you can easily scale compute power and storage with just a few clicks, and the system manages the hardware side of things for you.

128

How can you monitor and manage MySQL replication lag between master and slave servers?

Reference answer

Monitor the difference between the master's binary log position and the slave's relay log position to calculate replication lag.

129

What is the difference between CPU and PSU patch?

Reference answer

Difference Between CPU and PSU Patch in Oracle Feature CPU (Critical Patch Update) PSU (Patch Set Update) Full Form Critical Patch Update Patch Set Update Purpose Security fixes only Security fixes + Bug fixes Release Cycle Quarterly (Jan, Apr, Jul, Oct) Quarterly (Jan, Apr, Jul, Oct) Contents Includes only security vulnerability fixes Includes CPU patches + additional bug fixes for stability Impact Minimal impact on functionality May have minor functional changes due to bug fixes Recommended For Systems needing only security fixes Systems needing security + stability improvements Superset Of Independent security patch Includes CPU patches and additional fixes

130

How many team members you have in your team?

Reference answer

Our Oracle DBA team consists of 10 members, each handling different aspects of database management to ensure smooth operations. The team is divided as follows: - Production Support (4 members): These team members monitor and maintain production databases, troubleshoot performance issues, and ensure 24/7 availability. - Backup & Recovery (2 members) - Patching & Upgrades (2 members) - Performance Tuning & Optimization (1 member) - Automation & Scripting (1 member):

131

What are Oracle snapshots and how are they used?

Reference answer

Oracle Snapshots, now known as Materialized Views, are database objects that store the results of a query physically. They allow you to replicate and cache remote data locally, improving query performance and enabling offline reporting. Snapshots can be refreshed periodically or on-demand to keep the data current. They are useful for: • Replicating data across databases for distributed applications. • Offloading reporting workloads from production. • Improving query performance by avoiding repeated complex joins. • Enabling data warehousing and ETL processes. Materialized views can be simple or complex, support fast refresh using logs, and are tightly integrated into Oracle's optimizer for efficient query rewriting.

132

Explain how to optimize a slow-running SQL Server query.

Reference answer

To optimize a slow-running query, the first step is examining the query execution plan to identify bottlenecks such as table scans, key lookups, and costly operations. Evaluating indexing strategies, including adding, modifying, or removing indexes, is crucial to improving performance. Analyzing I/O statistics and CPU usage helps pinpoint inefficiencies. Keeping statistics up-to-date is essential. Refactoring the query to simplify joins or eliminate subqueries, and using query hints selectively, can also enhance query performance.

133

What is the 'Trustworthy' setting on databases for, what does it provide, and when should it or should it not be used?

Reference answer

The 'Trustworthy' setting on databases is used to indicate whether SQL Server trusts the database and its contents. It provides the ability for database objects to access resources outside the database, such as executing assemblies with external access or unsafe permissions. It should be used only when necessary, such as for databases that require cross-database ownership chaining or CLR integration, and should not be used on databases that do not need these capabilities due to security risks.

134

What are the two options for Windows authentication in SQL Server?

Reference answer

In SQL Server, there are two choices for registering Windows authentication: SQL server authentication or Windows authentication, when selecting this latest method, leave the default database alone to avoid confusion and hassles.

135

Can you discuss your experience with database monitoring and troubleshooting tools?

Reference answer

Look for: Familiarity with monitoring tools and troubleshooting methodologies.

136

If the database hangs, what are the processes you would use to troubleshoot this issue?

Reference answer

Troubleshooting a hanging database involves: 1) Checking the database alert log and system logs for errors. 2) Identifying blocking sessions using tools like SQL Server Activity Monitor or Oracle's V$SESSION. 3) Analyzing wait events to determine the root cause (e.g., I/O bottlenecks, lock contention, or resource starvation). 4) Killing problematic sessions if necessary. 5) Checking disk space, memory, and CPU usage. 6) Reviewing recent changes to the database or application. 7) Restarting the database service if the issue persists, after ensuring a proper backup is available.

137

We are building a new database for our employee records. How do you define system storage requirements?

Reference answer

I would start by estimating the volume of data, considering growth over time. Then, I'd look into the types of queries and transactions to determine the required performance. Based on these factors, I'd define the storage requirements.

138

What steps would you take if a transaction log file is full?

Reference answer

If a transaction log file is full, a DBA can: Create a backup of the transaction log to clear space and prevent the log file from becoming full. Truncate the log using the BACKUP LOG command. Increase the size of the transaction log file or add additional log files. Check for long-running transactions and resolve them.

139

Would you run a test on a live database? Why or why not?

Reference answer

No, running tests on a live database is risky and not recommended. I would use a staging environment that mimics the live database for all testing purposes.

140

What are physical components of Oracle database?

Reference answer

Oracle database is comprised of three types of files. One or more datafiles, two or more redo log files, and one or more control files. Password file and parameter file also come under physical components.

141

Is it possible to stop all I/O activity while the database is open for a snapshot?

Reference answer

Yes, it is possible to stop all I/O activity while the database is open. Normally, when a database is open, there will be constant I/O to online redo log files or data files. Even if the database is idle, there is no guarantee that the database will not write anything to files during the snapshot. However, if you "suspend" the database, Oracle will halt I/O operations to these datafiles until it is reverted back to normal mode. So, you should "suspend" the database, take the snapshot of the disk and then put the database back in normal mode immediately after that.

142

How is data stored in Oracle (blocks, extents, segments)?

Reference answer

Oracle stores data in a structured format to manage space efficiently. The smallest unit is a data block. • Data Block: Smallest unit of I/O; usually 8KB or 16KB in size. It stores rows of table data. • Extent: A set of contiguous data blocks. Oracle allocates extents to store new data as needed. • Segment: A set of extents allocated to a database object, such as a table, index, or LOB. When a table is created, Oracle allocates an initial extent. As the table grows, more extents are added. All these extents together form a segment. Segments reside in tablespaces, and tablespaces use datafiles. This layered storage model helps Oracle efficiently manage growth, fragmentation, and access speed. Oracle's storage model allows for dynamic space allocation, meaning objects can grow without manual intervention. DBAs can monitor and tune object growth using views like DBA_EXTENTS and DBA_SEGMENTS.

143

What are the three recovery models available for a database in SQL Server?

Reference answer

The three recovery models are Full, Bulk-Logged, and Simple. The recovery model is chosen based on the amount of data loss one can afford. If minimal or no data loss is expected, the Full recovery model is a good choice. Depending on the recovery model, the behavior of the database log file changes.

144

Explain the concept of replication in MySQL.

Reference answer

Replication involves copying data from one MySQL database to another, allowing for high availability, data redundancy, and read scalability.

145

How you start and stop the databse in RAC?

Reference answer

Start Database 1. CRS start: Start the Oracle Clusterware (CRS) service using crsctl start crs 2. Database start: Start the database instance using srvctl start database -d 3. ASM start: Start the Automatic Storage Management (ASM) instance using srvctl start asm -n Stop Database 1. Database stop: Stop the database instance using srvctl stop database -d 2. ASM stop: Stop the ASM instance using srvctl stop asm -n 3. CRS stop: Stop the CRS service using crsctl stop crs

146

Some one mistakenly truncated a table on primary database. We already have a dataguard setup. Is there any way we can recover that table?

Reference answer

If Flashback Database is enabled, or if standby is in delay mode, or if we have RMAN backups, we can recover the truncated table by using Flashback, standby database, or tablespace point-in-time recovery.

147

Scenario: Full backup Monday, incremental Tuesday, hourly archive backup; one datafile corrupted. From which backup/logs able to restore datafile — step by step.

Reference answer

Given: Full backup Mon, incremental Tue (level 1), and hourly archivelog backups. If a single datafile corrupted at time X (after Tue), steps: - Identify SCN or time of corruption and the sequence of archivelogs needed. - Restore the corrupted datafile using RMAN from the most recent backup that contains that file (could be full backup from Monday or incremental that included that tablespace): - If incremental included the datafile and made it current to Tue, you can restore incremental + apply archivelogs since Tue. - - Steps: - RMAN will look for backups: full backup (Mon) + incremental backup (Tue) + archived logs from Tue->now. Since hourly archivelog backups exist, they'll be applied to bring file to current state. - If using block change tracking and incremental backups: consider RECOVER ... with incremental backups. - If datafile was part of tablespace that could be restored online, you may use RMAN> SWITCH andRECOVER . - After apply, open the tablespace or database: - If corruption is irrecoverable (missing required archived logs), you must restore full DB from last valid backup and roll forward using available archived logs, or use point-in-time recovery. In summary: restore the datafile from the most recent backup that contains it (full or incremental), then apply archived logs (hourly backups) from the time of that backup up to the required SCN/time.

148

What are the different methods for transferring data between databases?

Reference answer

Data transfer between databases can be accomplished using several methods, including export/import utilities (such as Oracle Data Pump or SQL Server Import/Export Wizard), database links for direct queries, replication technologies, ETL (Extract, Transform, Load) processes, and bulk copy tools like BCP. The choice depends on factors such as data volume, latency requirements, and whether the transfer is one-time or ongoing.

149

What is an index in SQL?

Reference answer

An index is a database structure that speeds up data retrieval from a table. It works like a book's index, providing quick lookup access paths to data rows based on key values.

150

What MRP process do?

Reference answer

In Oracle Data Guard, MRP (Managed Recovery Process) is responsible for applying archived and redo logs to a physical standby database to keep it synchronized with the primary database.

151

What are the different types of backups in SQL Server?

Reference answer

SQL Server offers Full backups (entire database), Differential backups (changes since last full), and Transaction Log backups (transactions since last log backup), crucial for point-in-time recovery.

152

What are the types of synonyms in Oracle?

Reference answer

Synonym types are private and public.

153

How users save their password and describe the purpose of connecting to the SQL Server instance?

Reference answer

Users can save their password and explain their intent when accessing an SQL Server instance by setting customer SQL as their user-friendly incense; from here, users will have access to customer data.

154

Can you describe some of the most challenging database problems you have faced and how you resolved them?

Reference answer

This question tests your problem-solving skills and experience. Discuss significant challenges you've encountered in your career as a DBA, how you approached them, and the solutions you implemented. One of the most challenging problems I faced was dealing with a large database that was running extremely slow. I diagnosed the problem, identified inefficient queries and missing indexes as the root cause, and fixed them. I also implemented a performance monitoring system to prevent such issues in the future.

155

What are the types of redo transport services in Data Guard?

Reference answer

ARCH: Archive logs are sent by the ARCH process after log switch; no real-time apply (standby redo logs not required). SYNC: Synchronous redo transport for Maximum Protection/Availability; commits wait for standby acknowledgment. ASYNC: Asynchronous redo transport for Maximum Performance; commits don't wait for standby acknowledgment.

156

How to check db status on os level

Reference answer

Use ps -ef | grep pmon to check PMON process or lsnrctl status for listener status.

157

How do you handle database backups and disaster recovery planning?

Reference answer

To handle database backups, I follow a regular schedule and leverage automated backup tools to ensure data is protected. I perform full backups periodically and supplement them with incremental or differential backups to minimize data loss. As for disaster recovery planning, I develop comprehensive strategies that involve offsite backups, standby servers, and replication. I conduct periodic tests and simulations to validate the effectiveness of the recovery plan.

158

What is an SQL agent, and how would you use it?

Reference answer

The SQL Agent is SQL Server's built-in job scheduling service. I use it extensively to automate routine administrative tasks. This includes scheduling database backups and restores, performing regular integrity checks like DBCC CHECKDB, and managing index rebuilds or reorganizations. It ensures that critical maintenance tasks run reliably and on schedule, reducing manual effort and improving overall database health.

159

SQL*Plus Command Reference

Reference answer

SQLPlus is a command-line tool for Oracle Database that allows users to interact with the database using SQL and PL/SQL commands. When passing SQL commands as arguments to SQLPlus in a Linux environment, there are limits on the maximum length of the command line. In this article, we will learn about the Maximum length of command line arguments that can be passed to SQL*Plus. Command Line Arguments in SQL*Plus Command line arguments in SQL*Plus serve as parameters that control the behavior of the tool. These arguments can include script names, connection details, and other options. Users frequently utilize these arguments to streamline their workflows and automate tasks. Maximum Length of Command Line Argument The maximum length of a command line argument is determined by the operating system. In Linux, the maximum length is typically 131,072 bytes (or 128 KB). This includes the length of the SQL*Plus command, SQL query, and any additional parameters. Syntax: sqlplus [username]/[password]@[database] @script.sql - sqlplus: Command to start SQL*Plus. - [username]/[password]@[database]: Connection details to log in to the Oracle Database. - @script.sql: The SQL script file to be executed.

160

How to change the default tablespace for a user?

Reference answer

ALTER USER AISHU DEFAULT TABLESPACE

161

What is normalization in database design?

Reference answer

Normalization is the process of organizing database tables to reduce data redundancy and improve data integrity. It involves dividing large tables into smaller, related tables and defining relationships between them, typically following normal forms such as 1NF, 2NF, and 3NF.

162

How do you restore a database from a backup?

Reference answer

Start the database in NOMOUNT mode.Restore the SPFILE and Control Files if required. Mount the database and restore the datafiles using RMAN. Recover the database using archived logs. Open the database using RESETLOGS if necessary.

163

How can we tune a SQL query to optimize the performance of a database?

Reference answer

Enlisted are a few of the best practices for writing SQL queries. 1.Column names should be provided instead of * in SELECT statements. 2.Joins should be used in the place of sub-queries. 3.EXISTS should be used instead of IN to verify the existence of data. 4.UNION ALL should be used in the place of UNION. 5.HAVING should be used only for filtering the resulted rows from the SQL query.

164

Describe the main differences between frontend and backend systems.

Reference answer

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

165

What is the highest number of database servers you have experience working with?

Reference answer

This is an important question because hiring managers expect you to be able to work with a specific number of databases. The number of databases will be relative to the size of the database you manage. When answering the question, mention the number of databases you're capable of working with at one time, along with their size. This is crucial because, for example, if you have worked with five at a time, but you fail to mention they were 1 TB, you'll give the interviewer an inaccurate idea of your skills and experience.

166

Can RMAN backups be taken when the database is in MOUNT mode?

Reference answer

Yes, RMAN backups can be taken in the MOUNT state. This mode is commonly used for consistent backups, especially for whole database backups.

167

Do all businesses need a Data Protection Officer (DPO)?

Reference answer

A DPO must be appointed in three specific cases: 1) public authorities, 2) organizations that engage in large-scale systematic monitoring, or 3) organizations that engage in large-scale processing of sensitive personal data. If your organization doesn't fall into these categories, it's not necessary to appoint a DPO.

168

Explain how will you change the physical file name of a data file?

Reference answer

Changing the physical database file name is multistep activity. We must not perform such activities on regular basis and only do them on a need basis. Below are the high-level steps to change a physical file of a database. - Set database in OFFLINE state - Go to the location where your database files are saved and rename each file as per your desired naming convention - Update system catalog with new files names by running ALTER DATABASE statement for each file separately USE master GO ALTER DATABASE SQLSHACK MODIFY FILE (Name='SQLSHACK_Data1', FILENAME='F:\MSSQL12.MSSQLSERVER\MSSQL\DATA\SQLSHACK_Renamed.mdf') GO - Bring database ONLINE

169

What is RMAN, and how do you check RMAN's default configuration?

Reference answer

RMAN (Recovery Manager) is an Oracle tool for backup and recovery automation. To check the configuration: SHOW ALL;

170

How many databases can I create?

Reference answer

Unlimited, as much as CPU and memory is supported.

171

What is the key benefit of using AIR for enterprise recruiting?

Reference answer

Reduce time-to-screen by 80% while improving candidate experience and consistency.

172

What kind of patching activities done?

Reference answer

1. CPU (Critical Patch Update) patching: Apply quarterly CPU patches to fix security vulnerabilities. 2. PSU (Patch Set Update) patching: Apply quarterly PSU patches to fix bug fixes, security patches, and stability improvements. 3. BP (Bundle Patch) patching: Apply bundle patches that include a collection of fixes for specific Oracle Database versions. 4. RU (Release Update) patching: Apply release updates that include a collection of fixes, security patches, and new features for specific Oracle Database versions. 5. OPatch patching: Apply individual patches using OPatch, Oracle's patching tool. 6. Grid Infrastructure patching: Patch Oracle Grid Infrastructure (GI) components, such as Oracle Clusterware and Oracle Automatic Storage Management (ASM). 7. Database patching: Patch Oracle Database software, including the database kernel and other components.These patching activities help ensure the security, stability, and performance of Oracle databases.

173

How do you monitor Data Guard synchronization?

Reference answer

I monitor Data Guard sync using views like V$DATAGUARD_STATS for transport/apply lag, V$ARCHIVE_DEST_STATUS to check log shipping, and V$STANDBY_LOG for redo log usage. I also use OEM for visual monitoring and Data Guard Broker for automated health checks and alerts.

174

What is the difference between a control file and a parameter file?

Reference answer

Control File: Stores database metadata such as data file locations and log sequences.Parameter File (PFILE/SPFILE): Contains configuration parameters for the database instance.

175

How do we configure multiple buffer caches in Oracle?

Reference answer

Use the DB_NK_CACHE_SIZE dynamic parameter in PFILE or SPFILE. (NK can be 2K, 4K, 8K, 16K, or 32K.)Example: ALTER SYSTEM SET DB_4K_CACHE_SIZE=512M SCOPE=BOTH; If DB_BLOCK_SIZE=8K, then DB_8K_CACHE_SIZE is not allowed.

176

To enable Flashback, does the database need to be stopped?

Reference answer

No, the database does not need to be stopped. Flashback Database can be enabled while the database is running.

177

What are the five types of SQL statements?

Reference answer

There are five types of SQL statements

178

What tools and processes support proactive detection of data corruption or loss?

Reference answer

Tools and processes include implementing checksums, leveraging database-integrated consistency checks, scheduling regular backups with verification routines, deploying data replication across multiple nodes, and using monitoring solutions that alert on anomalies.

179

Are uncommitted transactions written to flashback redo logs?

Reference answer

Yes.

180

How can you find which node is active in a SQL Server cluster?

Reference answer

Using Cluster Administrator, connect to the cluster and select the SQL Server cluster. Once the SQL Server group is selected, on the right-hand side of the console, the column 'Owner' gives the information of the node on which the SQL Server group is currently active.

181

Explain the concept of event scheduling in MySQL.

Reference answer

Event scheduling allows you to schedule and automate recurring tasks within the database, similar to cron jobs.

182

What is a datafile?

Reference answer

Every Oracle database has one or more physical datafiles. Datafiles contain all the database data. The data of logical database structures such as tables and indexes is physically stored in the datafiles allocated for a database.

183

How do you handle “ORA-01555: snapshot too old”?

Reference answer

ORA-01555 occurs when a query tries to access an older version of data that has been overwritten in the undo tablespace before the query completed. This usually happens during long-running queries or when undo retention is too low. To handle it: • Increase undo tablespace size and undo retention period to keep undo data longer. • Optimize queries to run faster, reducing undo data age. • Avoid unnecessary large transactions that hold undo for a long time. • Use Oracle's Automatic Undo Management to better handle undo space. • Monitor undo tablespace usage and tune accordingly. • If it occurs in batch jobs, break the job into smaller transactions. • Check for high undo segment contention or frequent commits.

184

Describe your process for performing database capacity planning.

Reference answer

My capacity planning process starts with analyzing historical performance trends for CPU, memory, I/O, and storage usage. I then factor in anticipated business growth, like new users or applications, and project future resource requirements. This involves forecasting peak workloads and identifying potential bottlenecks. I'd then propose scalable solutions, whether that's recommending hardware upgrades, optimizing queries, or leveraging cloud scalability. The goal is to ensure the database can always meet demand efficiently without performance degradation.

185

Explain what a backend 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.

186

How do sessions communicate with database?

Reference answer

Server processes execute SQL received from user processes.

187

Describe the purpose of the SHOW ENGINE statement in MySQL.

Reference answer

The SHOW ENGINE statement displays information about MySQL storage engines, status, and variables.

188

What are fine-grained auditing (FGA) policies?

Reference answer

FGA policies audit access to specific rows or columns based on user-defined conditions. Features: • Enables detailed monitoring of sensitive data access. • Can audit SELECT, INSERT, UPDATE, DELETE operations selectively. • Useful for compliance requirements like GDPR or HIPAA. • Policies are created using DBMS_FGA.ADD_POLICY specifying the object, columns, and conditions. • Generates audit records only when conditions are met, reducing noise. FGA helps organizations detect unauthorized or suspicious access to critical data.

189

What steps do you take to optimize database queries and improve performance?

Reference answer

To optimize database queries, I typically begin by identifying slow-running queries using Profiler or Extended Events. Then, I analyze their execution plans to pinpoint bottlenecks, such as missing indexes or inefficient joins. I'd then propose creating new indexes, modifying existing ones, or suggesting query rewrites to developers. Additionally, I regularly ensure statistics are up-to-date and address any fragmentation issues, all aimed at improving the query optimizer's efficiency and overall database performance.

190

What are the top OS-level metrics you monitor for DB performance?

Reference answer

Important OS-level metrics include: • CPU usage: High CPU can indicate inefficient queries or processes. • Memory usage: Insufficient memory causes swapping and slows DB. • Disk I/O: Monitor throughput and latency to detect bottlenecks. • Network traffic: Ensure bandwidth is sufficient for DB operations. • Process count: Too many processes can exhaust system resources. • Load average: High load suggests CPU saturation. • Swap usage: Excessive swapping degrades performance. • Filesystem free space: Running out of disk space affects database files. Monitoring these helps identify infrastructure issues affecting database performance.

191

Which statement about profiles is false?

Reference answer

Profiles are assigned to users, roles, and other profiles.

192

Explain a method you used to enhance or increase data storage.

Reference answer

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

193

Which is the biggest databases in your environment?

Reference answer

Say any value between 700 DB to 1.5 TB.

194

What is SQL Plan Baseline and how does it help?

Reference answer

SQL Plan Baseline is a feature to control execution plans for SQL queries. When a good plan is captured, Oracle stores it in the baseline. If a future change (e.g., stats, patch, bind value) causes a new bad plan, Oracle can reject it and keep using the trusted plan from the baseline. Benefits: • Avoids plan regressions. • Stabilizes performance for critical SQLs. • Ensures consistent performance after upgrades or re-gathering stats. Use DBMS_SPM package to manage baselines.

195

What is row chaining in Oracle?

Reference answer

Row Chaining occurs when the row is too large to fit into one data block when it is first inserted. In this case, Oracle stores the data for the row in a chain of data blocks (one or more) reserved for that segment. Row chaining most often occurs with large rows, such as rows that contain a column of datatype LONG, LONG RAW, LOB, etc. Row chaining in these cases is unavoidable.

196

Explain the concept of bulk insert in MySQL.

Reference answer

Bulk insert involves inserting a large number of rows into a table in a single statement, improving performance compared to individual inserts.

197

Tell me about a situation where you had to work under pressure to meet a tight deadline for a database-related project. How did you manage your time and resources to deliver the project successfully?

Reference answer

Look for: Time management and resource allocation skills.

198

Tell me about a time when you had to work independently on a complex database project. How did you stay motivated and focused throughout the project?

Reference answer

Look for: Independence and self-motivation.

199

Can a single listener handle multiple database services?

Reference answer

Yes, but the problem is if the listener is down, all new connections to multiple DB will not be accepted. It is suggested to have different listeners for each database. If we bring down one listener for one database, other listeners will not be affected.

200

What tools did AIR replace for one enterprise team?

Reference answer

AIR replaced resume screening, skills assessments, phone screening, and scheduling tools. ROI was positive in the first month.

DON'T WANT TO MISS A THING?

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

Basic DBA Interview Questions for Beginners | 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

Basic DBA Interview Questions for Beginners | SPOTO

Earn a certification to make your resume stand out.

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