DBA Interview Questions and Answers Guide

1

Which view shows all of the objects accessible to the user in a database?

Reference answer

ALL_OBJECTS

2

How to increase the redo size.

Reference answer

To increase redo log size, recreate redo log groups with larger size (can't resize existing online redo logs). Steps: - Add new redo log group with desired size: - Switch logfile and drop old group(s) when they are inactive: - Alternatively, create new groups for all member groups and then drop old ones, ensuring at least two or three groups are available. - For RAC, add members on each node or use ASM: Notes: - Plan size based on redo generation rate and checkpoint frequency. - Do this in low activity or maintenance window if possible.

3

Why are you here?

Reference answer

Zoom out to the big picture. Tell me why you're in this chair right now. Is this some sort of career plan? Have they heard good things about the environment from another employee? Are they looking for an employee discount on products? (It doesn't work at wine & spirits companies, just for the record.) Or are they just desperate? Nothing wrong with desperation, but remember that desperate employees are desperate for something, and you'd better find out what it is. If they're desperate for money, it's going to color the judgements they make.

4

I would like to select database from a table which is in different database. How can I do it?

Reference answer

You can create DB LINK and query from the remote table on remote database.

5

Which DBA skills do you need to improve?

Reference answer

Applicants may be trying to improve technical and/or soft skills to enhance their performance and complete projects more easily. For instance, they may focus on improving their communication or data protection and GDPR knowledge. Check with your applicants to see which methods they are using to improve their skills.

6

What happens if the observer is down in a Data Guard setup?

Reference answer

No impact on the Data Guard setup, but FSFO (automatic failover) is disabled until the observer is restored

7

Describe the purpose of the mysqlbinlog utility in MySQL.

Reference answer

The mysqlbinlog utility is used to display the contents of binary log files and to apply binary log events to other servers.

8

What is the difference between clustered and non-clustered indexes, and when would you use each?

Reference answer

A clustered index physically reorders the table data and stores it in sorted order based on the index key. Each table can only have one clustered index because the data can only be physically sorted in one way. I typically use clustered indexes on primary keys or columns that are frequently used in range queries. Non-clustered indexes create a separate structure that points back to the actual data rows. You can have multiple non-clustered indexes per table. I use these for frequently searched columns that aren't the primary key, like email addresses or last names in a user table. In my last role, I added a non-clustered index on a customer lookup field that reduced query time from 3 seconds to 200 milliseconds.

9

Can you share an example of a time when you had to migrate a large database from one system to another? How did you plan and execute the migration while ensuring data accuracy and minimal downtime?

Reference answer

Look for: Migration planning and execution skills.

10

What is the purpose of the model database?

Reference answer

The model database acts as the blueprint for all newly created user databases in SQL Server. Any structural or configuration changes made to the model database will be inherited by every subsequent new database. It's also critical because the tempdb system database is recreated from the model every time the SQL Server instance restarts, ensuring consistent temporary storage behavior.

11

What is a table in Oracle?

Reference answer

The table is the first level of the physical unit in the database. Oracle uses tables of a database to store data into rows and columns. The table is the first level of the physical unit in the database.

12

Which option is used to create an established SQL Server login?

Reference answer

SQL Server Management Studio T SQL script Tech Brother client SQLmitter

13

What is fal_client and fal_server parameter?

Reference answer

FAL_SERVER: Specifies the Oracle Net service name of the primary database from which the standby requests missing archive logs. FAL_CLIENT: Specifies the standby database's service name (deprecated in recent versions). Example: fal_server='primdb', fal_client='stdbydb'. Used for archive gap resolution on the standby.

14

What are the considerations for securing SQL Server 2022?

Reference answer

Securing SQL Server 2022 involves multiple layers of security. At the network level, ensuring the database engine is not exposed directly to the internet and using firewall rules to limit access are crucial steps. Authentication should be managed via Windows authentication mode where possible for better security, complemented by strong password policies for SQL logins. Encrypting data at rest using Transparent Data Encryption (TDE) and securing data in transit with SSL/TLS are also recommended. Additionally, regularly applying updates and patches and conducting audits with SQL Server Audit or Extended Events to track and analyze activities are essential for maintaining security.

15

Have you ever made a mistake as a database administrator? Which steps helped you fix it?

Reference answer

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

16

Can BCT be enabled on a standby database?

Reference answer

Yes, enabling BCT improves incremental backup efficiency for the standby database.

17

Can you explain the concept of database sharding and when it might be necessary?

Reference answer

Database sharding involves partitioning a large database into smaller, more manageable pieces called shards, which can be distributed across multiple servers. This approach is necessary when dealing with massive datasets to improve performance and scalability, especially in high-traffic applications.

18

How do you handle user access and permissions in a database?

Reference answer

I implement role-based access control (RBAC) to ensure users have the appropriate permissions based on their roles. Regular audits and updates of user access levels help maintain security, and I use monitoring tools to detect and address any unauthorized access promptly.

19

How do you check the directory size in ASM?

Reference answer

SELECT f.group_number, f.file_number, bytes, space, space/(1024*1024) AS "Size_MB", a.name FROM v$asm_file f, v$asm_alias a;

20

Have you implemented database security measures, such as encryption and access controls? If yes, how?

Reference answer

Look for: Practical experience with security implementations.

21

Explain the purpose of a model database

Reference answer

The model database consists of the template for all databases created in the SQL system. If the initial model database is modified, all subsequent databases created in the systems will reflect the changes. However, databases created earlier will not.

22

What is SQL injection?

Reference answer

SQL injection is a technique used to extract user data by injecting web page inputs as statements through SQL commands. Basically, malicious users can use these instructions to manipulate the application's web server. - SQL injection is a code injection technique that can compromise your database. - SQL injection is one of the most common web hacking techniques. - SQL injection is the injection of malicious code into SQL statements via web page input.

23

Is there any prerequisite for converting physical standby to snapshot standby database?

Reference answer

You need to set db_recovery_file_dest and db_recovery_file_dest_size parameter on standby and then enable flashback.

24

Soft Parsing vs Hard Parsing:

Reference answer

Soft Parsing: Reuses previously parsed SQL from shared pool. Hard Parsing: New parse required; more CPU/resource-intensive.

25

Walk me through how you would design a database schema for an e-commerce application.

Reference answer

Start by identifying core entities: Users, Products, Orders, Order Items, Categories, Inventory, Payments. Establish relationships—Users have many Orders, Orders have many Order Items, Products belong to Categories. Consider normalization: Separate user addresses into a separate table if users can have multiple addresses. Think about performance: You might denormalize product ratings or create summary tables for reporting. Plan for scalability: Consider partitioning large tables like Orders by date, indexing strategy for common queries (product searches, order lookups), and potential sharding strategies as the system grows.

26

How to see the default temporary tablespace for a database?

Reference answer

To find the default temporary tablespace for your Oracle database, you can query the DBA_USERS view or theV$PARAMETER view.- Using the DBA_USERS view:This will show the default temporary tablespace for each user, including the database default:The temporary_tablespace column will show the default temporary tablespace for the user. The database default is typically used unless overridden by a user-specific setting. - Using the V$PARAMETER view:You can also query theV$PARAMETER view to find the overall default temporary tablespace for the database: This query will return the name of the default temporary tablespace for the entire database. - Using the -

27

Which export option allows table creation without data?

Reference answer

ROWS=N.

28

How do you configure and manage MySQL user accounts and privileges?

Reference answer

Use the CREATE USER, ALTER USER, and GRANT statements to manage user accounts and privileges.

29

Can you explain the differences between SQL and NoSQL databases, and when you would use each?

Reference answer

SQL databases are relational, structured, and use predefined schemas, suitable for complex queries and transactions. NoSQL databases are non-relational, flexible, and handle unstructured data, ideal for scalability and big data applications. I use SQL for structured data and NoSQL for high-velocity or varied data.

30

Explain the difference between INNER JOIN and LEFT JOIN. Provide an example for each.

Reference answer

INNER JOIN The INNER JOIN keyword selects all rows from both tables as long as the condition is satisfied. This keyword will create the result set by combining all rows from both the tables where the condition satisfies i.e. the value of the common field will be the same. Syntax: SELECT table1.column1,table1.column2,table2.column1,.... FROM table1 INNER JOIN table2 ON table1.matching_column = table2.matching_column; table1: First table. table2: Second table matching_column: Column common to both the tables. Note: We can also write JOIN instead of INNER JOIN. JOIN is the same as INNER JOIN. LEFT JOIN This join returns all the rows of the table on the left side of the join and matches rows for the table on the right side of the join. For the rows for which there is no matching row on the right side, the result-set will contain null. LEFT JOIN is also known as LEFT OUTER JOIN. Syntax: SELECT table1.column1,table1.column2,table2.column1,.... FROM table1 LEFT JOIN table2 ON table1.matching_column = table2.matching_column; table1: First table. table2: Second table matching_column: Column common to both the tables. Note: We can also use LEFT OUTER JOIN instead of LEFT JOIN, both are the same.

31

What is SQL DBA?

Reference answer

An SQL Database Administrator (SQL DBA) is responsible for installing, maintaining, securing, and managing SQL databases. They handle backups, recovery, performance tuning, user access, and troubleshooting database issues. SQL DBAs make sure that databases stay reliable, fast, and available to the applications and users that depend on them every day.

32

What is Cache Fusion?

Reference answer

A mechanism in Oracle RAC to share data blocks between instances using the interconnect, avoiding disk I/O.

33

What are LMT and DMT?

Reference answer

LMT (Locally Managed Tablespaces) store extent allocation information within the tablespace itself, whereas DMT (Dictionary Managed Tablespaces) rely on the data dictionary for extent tracking. LMT is preferred due to improved performance and reduced contention.

34

Do you suggest to grant RESOURCE role to a user?

Reference answer

No, this will grant unlimited quota on user default tablespace.

35

We need to create user whose password will expire every month.

Reference answer

Use a profile with PASSWORD_LIFE_TIME = 30 (or 1 month). Create user with that profile. Example: Notes: - PASSWORD_LIFE_TIME accepts days (30) or UNLIMITED . You can also set PASSWORD_GRACE_TIME, FAILED_LOGIN_ATTEMPTS, PASSWORD_REUSE_TIME, etc. - Use ALTER PROFILE monthly_pwd LIMIT PASSWORD_LIFE_TIME 30; to change for existing profiles. - For calendar months, you must approximate (30 days) — Oracle stores numeric days.

36

How to restore .patch_storage if deleted?

Reference answer

Restore from backup if available. Otherwise, reapply the patches to regenerate the necessary files.

37

Is it possible that database physical file names also changed after renaming a database?

Reference answer

No, renaming a database will not change its physical file names. You need to manually perform additional steps to change the database physical file names.

38

Parameters for different pools of Oracle instance:

Reference answer

Example parameters:shared_pool_size ,db_cache_size ,java_pool_size ,large_pool_size ,log_buffer . To automatically manage memory pools, setSGA_TARGET andSGA_MAX_SIZE .

39

How can we recover from the loss of an online redo log?

Reference answer

The recovery method depends on the status of the lost or corrupted redo log file. The appropriate steps should be taken based on its availability and whether it was archived.

40

Control Methods of Database Security

Reference answer

Database Security means keeping sensitive information safe and preventing the loss of data. The security of the database is controlled by Database Administrator (DBA). The following are the main control measures used to provide the security of data in databases: - Authentication - Access control - Inference control - Flow control - Database Security applying Statistical Method - Encryption

41

How do you resolve a 1000-archive log gap in a standby database?

Reference answer

Check the SCN numbers between primary and standby databases.Identify missing archive logs and manually apply them. Use RMAN to fetch missing logs and apply them to synchronize standby.

42

Can you name the five different database objects?

Reference answer

Certainly. The five fundamental database objects include: Tables, which are where the actual data is stored; Views, which are virtual tables based on query results; Indexes, used to speed up data retrieval; Stored Procedures, which are pre-compiled SQL code blocks for specific tasks; and Triggers, which automatically execute in response to data changes.

43

How to increase the speed of RMAN backup.

Reference answer

Tactics to make RMAN backups faster: - Use parallel channels: - Use compression (may reduce I/O but increases CPU). Use BACKUP AS COMPRESSED BACKUPSET . - Use incremental backups (reduce full backups), cumulative incremental. - Use multiplexing to write multiple datafiles to a single backup set. - Use faster disks / SSD or dedicated backup network. - Use BACKUP ... PLUS ARCHIVELOG efficiently and skip unnecessary files. - Use MAXOPENFILES and tunedBT buffer sizes as appropriate. - Use RMAN block change tracking for incremental backups (ALTER DATABASE ENABLE BLOCK CHANGE TRACKING ). - Exclude large but nonessential objects or backup particular tablespaces. - Use Backup-as-copy for very large files (on fast storage). - Backup to multiple locations simultaneously (two channels to two targets). - Tune retention policies and avoid unnecessary duplicates. - Use vendor appliances (Data Domain, ExaGrid) integrated with RMAN. - Network: ensure dedicated network for backups with adequate bandwidth.

44

Besides snapshot standby, can a standby be opened read-write for testing and reverted?

Reference answer

Yes, using flashback. Enable flashback, open the standby read-write, perform tests, then flashback to the pre-test SCN and resume as a standby.

45

Can we change database block size after creation?

Reference answer

No, we cannot change block size.

46

What is the best way to perform Oracle installations for batch and single installations?

Reference answer

If you are going to do batch installations, it is best to do it with Oracle Universal Installer in silent mode. For single installations.it is best to start the installer in "interactive mode" and set installation options at each window. However, in batch installations, this will take longer. You need to do the installations in "silent" mode with a "response file". In a silent installation, you start the Oracle Universal Installer from a command prompt and specify the location of the "response file". The installation files and the response file can be shared among the servers via NFS so that you won't have to copy the setup files to each server.

47

How to change the instance name once the database is created?

Reference answer

Steps:Shutdown the Database: First, shut down the Oracle instance you want to rename. Edit Initialization File: Locate the spfile or pfile in your Oracle home. Change the db_name and instance_name parameters in the initialization file. Example:Set the ORACLE_SID: Set the environment variable ORACLE_SID to the new instance name. Restart the Database: Restart the database instance with the new name. Test the Change: Verify that the new instance name works by connecting to the database using the new SID.

48

Which method increase the size of a tablespace?

Reference answer

Add a datafile to a tablespace.

49

Can you tell me of a time you made a mistake and how did you resolve it? Was there any way you could prevent it?

Reference answer

Everyone is human. If you face a candidate giving you the perfect, straight-out-of-the-book answers, believe it or not, that might be a red flag. Instead of the “No, I haven't made any mistakes, I'm perfect” kind of answer, look for someone honest. The trick of this question isn't only about the mistake itself but also about the ‘how.' Learning how they worked under pressure and how they solved it. Additionally, knowing how to prevent it it's also a good sign that they understood exactly what had happened, and because of this, it's very unlikely it will happen to them again.

50

How to check which users are granted sysdba role?

Reference answer

We can query v$syspw_file view.

51

How do you refresh a non-prod DB from prod copy?

Reference answer

Refreshing a non-production database (such as test or dev) from a production copy involves updating the non-prod DB with a recent snapshot of production data. Typical process includes: • Taking a backup or RMAN clone of the production database. • Restoring the backup on the non-prod server or using RMAN duplicate to create a fresh copy. • Optionally masking or anonymizing sensitive data in the non-prod copy for compliance. • Adjusting environment-specific settings such as connection strings, mail servers, or application configurations to avoid impacting production systems. • Synchronizing configurations, users, and roles as needed. • Running any necessary scripts to prepare the environment. • Scheduling refreshes regularly to keep non-prod environments relevant for testing and development. Proper refresh procedures ensure developers and testers work with real data scenarios while maintaining data security.

52

Do you know about limits.configuration ? — i didn't knew

Reference answer

I wasn't familiar with limits.conf specifically, but I understand it's used to configure user-level resource limits on Linux systems, especially for applications like Oracle. I've worked with similar system resource configurations, such as adjusting ulimit settings for performance tuning. I'm always eager to learn more about Linux administration and would certainly dive into the documentation and practical resources to become more familiar with limits.conf if I encountered it in a production environment.

53

What is DBCC CheckDB and how does the 'Suspect_Pages' table provide an 'early warning'?

Reference answer

DBCC CheckDB is a command that checks the logical and physical integrity of all objects in a database. The 'Suspect_Pages' table in the msdb database records pages that have been identified as potentially corrupted during I/O operations. It provides an 'early warning' by alerting administrators to potential corruption before it causes significant data loss, allowing proactive remediation.

54

How to check if the database is in ARCHIVELOG mode?

Reference answer

Run the following SQL command:SELECT log_mode FROM v$database; If the output is ARCHIVELOG, the database is in ARCHIVELOG mode; otherwise, it is in NOARCHIVELOG mode.

55

What is the difference between a role and privilege , can you provide an example?

Reference answer

Difference Between Role and Privilege: A privilege is a specific right or permission granted to a user to perform a particular action in the database. A role is a collection of multiple privileges grouped together for easier management.Example: Instead of granting individual privileges likeCREATE TABLE ,SELECT ,INSERT , andDELETE to multiple users, you can create a role named DATA_MANAGER and assign these privileges to the role. Then, you can grant the DATA_MANAGER role to users, simplifying access management.Types of Privileges: - System Privileges – These allow users to perform administrative tasks on the database. Examples:CREATE SESSION ,CREATE USER ,DROP USER ,ALTER SYSTEM - Object Privileges – These control access to specific objects like tables, views, and sequences. Examples:SELECT ,INSERT ,UPDATE ,DELETE ,ALTER ,INDEX ,REFERENCES on a table - System Privileges – These allow users to perform administrative tasks on the database. -

56

How would you handle a deadlock situation in SQL Server?

Reference answer

A deadlock occurs when two or more sessions are waiting for each other to release locks, causing the processes to be stuck indefinitely. To handle a deadlock, I would first identify and capture the deadlock events using SQL Server Profiler or by enabling the trace flag 1222 to log deadlock information in the SQL Server error log. Once identified, I would analyze the deadlock graph to understand the resources and queries involved. The most common solutions to resolve deadlocks in general include: - Optimizing queries: Reviewing and optimizing the queries involved to ensure they are acquiring locks in the same order to avoid circular wait conditions. - Reducing transaction scope: Keeping transactions as short as possible to minimize the time locks are held. - Implementing deadlock retry logic: Modifying the application code to catch deadlock exceptions and retry the transaction, as SQL Server will automatically choose one of the processes as the deadlock victim. - Using query hints: Using query hints like NOLOCK for read operations that do not require strict consistency or using ROWLOCK to acquire finer-grained locks.

57

What is the purpose of the control file and how is it managed?

Reference answer

The control file is a small but critical file that tracks the overall state of the database. Without it, the database cannot start. It stores: • Database name and unique ID • Names and locations of datafiles and redo logs • SCN (System Change Number) info • Checkpoint data • Backup and archive log history Oracle reads the control file during the MOUNT stage of startup. If the control file is missing or corrupted, the instance won't proceed. Oracle recommends having multiplexed control files—multiple copies on different disks—specified in the parameter file. Backups are also critical. You can use RMAN (BACKUP CURRENT CONTROLFILE) or manually create a text backup (ALTER DATABASE BACKUP CONTROLFILE TO TRACE). In case of control file loss, you can restore it from a backup and recover the database. Managing this file properly is key to database stability and recoverability.

58

How can you retrieve an image stored in SQL Server?

Reference answer

If the image is stored in a column of type VARBINARY(MAX) or IMAGE (deprecated), use a SELECT query like: SELECT ImageData FROM Products WHERE ProductID = 101; The result returns binary data, which must be handled in your application layer (e.g., C#, Python) to decode and display the image properly.

59

How do you find and kill a blocking session?

Reference answer

Blocking sessions cause other sessions to wait, leading to performance problems. To find blocking sessions: • Query views like V$SESSION and V$LOCK to identify which session is holding locks and which are waiting. • Use SQL like: • SELECT blocking_session, sid, serial#, wait_class, event FROM v$session WHERE blocking_session IS NOT NULL; • Oracle Enterprise Manager (OEM) also shows blocking sessions visually. To kill a blocking session: • Use the command: • ALTER SYSTEM KILL SESSION ‘sid,serial#'; • Sometimes, if the session does not terminate, use OS commands to kill the session process. • After killing, monitor the system to ensure locks are released and performance returns to normal.

60

How do you use Transportable Tablespace?

Reference answer

Transportable Tablespaces (TTS) allow you to quickly move large data between Oracle databases by copying tablespace datafiles instead of exporting all rows. Steps: make tablespace read-only → export metadata withexpdp transport_tablespaces → copy datafiles → import metadata withimpdp . It's much faster than full export/import and is often used for migrations and large data transfers.

61

How to see what is the default block size for a database ?

Reference answer

To check the default block size for an Oracle database, you can use the following SQL query: Explanation: v$parameter : This view contains database initialization parameters.db_block_size : This parameter defines the default database block size in bytes. Example Output: This means the default block size for the database is 8 KB (8192 bytes).

62

What's your approach to documenting database structures and processes?

Reference answer

My approach to documentation is that it should be clear, concise, and kept current. I ensure all critical database structures like schemas, tables, and indexes are documented, along with important stored procedures and scripts. For operational processes like backups, restores, and maintenance routines, I create step-by-step guides. I use version control for scripts and store documentation in a centralized, easily accessible knowledge base. This promotes consistency, facilitates troubleshooting, and helps onboard new team members effectively.

63

Can a standby in Maximum Protection mode with one primary and one standby be converted to a snapshot standby?

Reference answer

No, you cannot convert the standby to a snapshot standby in Maximum Protection mode if it's the only standby. Maximum Protection requires redo to be written to the standby redo log before committing transactions, which conflicts with snapshot standby's read-write mode.

64

Explain to me the process how you keep the end-users from accessing the unauthorized data.

Reference answer

To prevent unauthorized data access: 1) Define and enforce row-level security or column-level security using views, stored procedures, or database roles. 2) Implement application-level authentication and authorization to control what data users can see. 3) Use encryption for sensitive columns. 4) Regularly review and revoke unnecessary permissions. 5) Set up auditing to track data access and detect violations. 6) Educate users on data handling policies and enforce strict access controls through the application layer.

65

Which file stores the configuration for host-based naming?

Reference answer

sqlnet.ora.

66

What kind of isses you face on daily basis performance isses any kind of errors or what kind of complaints you get on daily basis from users?

Reference answer

1. Acknowledge the Common Types of Issues: In my daily work, I often come across a variety of issues. These can include performance-related issues, user errors, system failures, or complaints regarding usability or functionality. Depending on the type of issue, I address it by first understanding its root cause and then implementing a suitable solution. 2. Performance Issues: One of the most common performance-related issues I deal with is ensuring that applications or systems run efficiently. This could include slow response times, high resource usage, or system overloads. For example, if users complain about a website's slow loading speed, I typically analyze server performance, database queries, and optimize the code to improve performance.f the issue requires a deeper technical fix or if it's something beyond my expertise, I collaborate with the development team or escalate it to higher levels of support to ensure timely resolution. I'm always focused on resolving issues efficiently and keeping communication open with users to maintain a positive experience.

67

Data dictionary can span across multiple Tablespaces?

Reference answer

No

68

What are the benefits of setting multiple buffer caches?

Reference answer

OLTP databases use small block sizes (2K, 4K, 8K) to optimize performance for small transactions.Data warehouse databases use larger block sizes (8K, 16K, 32K) for efficient large table scans. Hybrid databases require multiple block sizes and tablespaces with different BLOCKSIZE settings.

69

Explain the physical standby architecture . How standby gets synced with primary?

Reference answer

A Physical Standby Database is an exact copy of the primary database and is kept in sync through Redo Apply.? Architecture Overview: 1. Primary Database generates redo logs. 2. Log Transport Services send these redo logs to the standby over the network. 3. Remote File Server (RFS) on standby receives redo and writes to standby redo logs. 4. MRP (Managed Recovery Process) applies redo to the standby datafiles.? Sync Process: – Redo data is shipped in real time or at intervals. – Standby applies the redo to remain consistent with the primary. – This enables disaster recovery and high availability.

70

What are RMAN backup pieces, backup sets, and image copies?

Reference answer

Backup Piece: A backup piece is a physical file created by RMAN that stores the actual data from the database. It is an essential component of a backup set.Backup Set: A backup set is a collection of backup pieces that RMAN groups together to efficiently store and manage backups. It is the preferred method for backup operations. Image Copy: An image copy is an exact duplicate of a database file created at the operating system level. It provides an alternative backup method, often used for fast recovery.

71

Which backup is configured in your system? is it export or RMAN?

Reference answer

✅ If RMAN is used: We use RMAN for database backups, as it provides block-level backups, incremental backups, and automated recovery options. It also integrates well with Oracle features like Data Guard and ASM.✅ If Export (Data Pump) is used: We use Data Pump export for logical backups, mainly for schema-level, table-level, or full database exports. However, this is not a complete database backup solution since it does not capture physical structures like RMAN.

72

How do users connect to SQL Server Management Studio using Windows Authentication?

Reference answer

Users can connect to SQL Server Management Studio using Windows Authentication by selecting "SQL Server Authentication" and providing their username and password.

73

When the SMON Process perform Instance Crash Recovery?

Reference answer

Only at the time of startup after abort shutdown

74

How do you troubleshoot database performance issues?

Reference answer

I start by gathering baseline metrics using performance monitoring tools like SQL Profiler or MySQL Performance Schema. First, I identify whether the issue is CPU, memory, I/O, or network related using system performance counters. For query-specific issues, I analyze execution plans to identify expensive operations like table scans or nested loops. I check for missing indexes, outdated statistics, or poorly written queries. In one case, I discovered a missing index on a join condition that was causing a report to run for 45 minutes. Adding the index reduced runtime to 3 minutes.

75

Which dynamic view provides control file section details?

Reference answer

V$CONTROLFILE_RECORD_SECTION.

76

What is the Data Guard Broker, and how is it useful?

Reference answer

The Data Guard Broker simplifies management of Data Guard configurations via the dgmgrl utility. The DMON process handles switchovers, failovers, FSFO, and configuration updates across primary and standbys, improving administration efficiency.

77

When and where does the local user group meet?

Reference answer

I'm not asking if they regularly attend, I'm not asking if they speak, I'm not asking if they run for office. At the senior DBA level, I'd just be thankful if they were at least vaguely aware that user groups existed. Huge massive bonus points if they've been involved with the community, and I'd skip the next question.

78

Composite Key in SQL

Reference answer

A composite key is made by the combination of two or more columns in a table that can be used to uniquely identify each row in the table when the columns are combined uniqueness of a row is guaranteed, but when it is taken individually it does not guarantee uniqueness, or it can also be understood as a primary key made by the combination of two or more attributes to uniquely identify every row in a table. Note: - A composite key can also be made by the combination of more than one candidate key. - A composite key cannot be null.

79

What would be the potential reason behind SQL Server connection issues?

Reference answer

You might be failed to connect to SQL Server instance because of various reasons such as: - Ensure you are entering the right connection details like server name and port no - Ensure SQL Server port is open from the machine you are connecting to the instance - Make sure SQL Server instance is running - TCP or named pipe protocols are enabled through SQL Server configuration manager - SQL browser service is not running

80

What is the average database administrator salary in the US?

Reference answer

The database administrator's salary depends on various factors, such as location, company, experience, skills, and background. The average database administrator salary in the US is $90,681/ year (source: indeed.com). Companies such as Apple, EY, Oracle, TCS, Spectrum, and others provide the finest salaries to their database administrators. The average salaries in these companies range between $150,000 to $95,000. Moreover, cities like Dallas, New York, Phoenix, Chicago, and others offer top salaries to DBAs.

81

Describe the purpose of the OPTIMIZE TABLE statement in MySQL.

Reference answer

The OPTIMIZE TABLE statement is used to reclaim space and optimize table storage after deleting or updating data.

82

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

Reference answer

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

83

Tell me about a time when you had to handle a major database failure or outage. How did you respond to the situation, and what steps did you take to recover the database and minimize downtime?

Reference answer

During a critical system failure, our database became inaccessible, leading to a significant disruption in operations. I immediately initiated the incident response plan, and assembling a cross-functional team to investigate and resolve the issue. We quickly identified the root cause as a hardware failure and initiated the necessary steps for recovery, such as restoring from backups and implementing failover mechanisms. While the system was down, I maintained transparent communication with stakeholders, provided regular updates, and set realistic expectations. Through our swift actions and effective collaboration, we were able to restore the database and minimize downtime to just four hours, ensuring minimal impact on business operations.

84

Why enable force logging on the primary before setting up a standby?

Reference answer

Force logging ensures all operations (including nologging ones) generate redo, preventing data inconsistencies on the standby due to missing redo for nologging operations.

85

Can we prevent replicating DELETE operations for a specific article in Replication?

Reference answer

DELETE statements can be prevented to replicate any articles in SQL Server replication. We can get this done by accessing the properties window of the identified article and choose not to replicate delete statements there.

86

What is the difference between dedicated server and shared server architecture in Oracle?

Reference answer

When a user connects to a database, he sends SQL queries to the database to execute. These SQL queries are executed by a "server process" and the result is returned back to the user. In the "dedicated server" architecture, the instance will create one server process for each connected user. That process will be "dedicated" to that user and will only serve that client. However, in "shared server" architecture, a single server process will serve multiple clients. In shared server architecture, the total memory consumption will be less. However, certain operations like DBA activities can only be performed on a dedicated server.

87

Explain the concept of indexes in MySQL.

Reference answer

Indexes improve data retrieval speed by creating a data structure that allows faster searching and sorting.

88

After creating a new user, what all privileges you assign?

Reference answer

We must grant CONNECT role so that user can connect to database. We must also grant other privileges as per environment.

89

How to find the locations of Central and Local Inventory?

Reference answer

Central Inventory: Located in the oraInst.loc file (/etc/oraInst.loc on Linux or C:\Program Files\Oracle\Inventory on Windows). Local Inventory: Found inside $ORACLE_HOME/inventory.

90

What are the common causes of blocking in SQL Server and how do you fix them?

Reference answer

Blocking happens when one process locks resources and others wait. Common causes include missing indexes, long-running transactions, and poor query design. I fix blocking by adding proper indexes, splitting large transactions, and reviewing isolation levels like using Read Committed Snapshot Isolation.

91

What are the different protection modes in Data Guard?

Reference answer

- Maximum Protection: Ensures zero data loss by writing redo to both primary and standby redo logs before commit. If the standby is unreachable, the primary shuts down. - Maximum Availability: Balances protection and availability. Commits wait for redo to reach a synchronized standby but revert to Maximum Performance if the standby is unavailable. - Maximum Performance (default): Commits after writing redo to the primary's online redo log, with asynchronous redo transport to the standby, risking minimal data loss.

92

What is Oracle Multitenant Architecture?

Reference answer

Introduced in 12c. Consists of: - CDB (Container DB): Central DB container. - PDB (Pluggable DBs): User-created DBs inside CDB. - Enables easy cloning, patching, and management.

93

How do you maintain the Archive log files to prevent the database from hanging?

Reference answer

To maintain archive log files: 1) Configure automatic archiving to a dedicated location with sufficient disk space. 2) Set up a retention policy to delete or compress old archive logs after a specified period. 3) Monitor archive log generation rates and adjust the archive destination size accordingly. 4) Use backup scripts to archive logs to off-site storage. 5) Regularly check for archive log space warnings and take proactive action. 6) Implement log shipping or replication to offload logs to a secondary server.

94

What is the GROUP BY keyword used for in SQL?

Reference answer

GROUP BY keyword is an aggregate function such as SUM, MULTIPLE, etc and without this function sum for each individual group value can not be calculated.

95

How do you resolve a gap in archive logs missing from the primary?

Reference answer

Take an incremental backup of the primary starting from the standby's current SCN, transfer it to the standby, and apply it to recover the standby database.

96

What is the backup process in your organization?

Reference answer

The backup process involves taking periodic snapshots of database data to ensure recovery in case of failure. This typically includes full, incremental, and archive log backups using RMAN (Recovery Manager) or third-party tools. Additionally, automated scripts schedule backups and store them in secure locations for disaster recovery.

97

What can be done to improve RMAN backup performance?

Reference answer

Use more channels Enable parallelism Use multi-section backups

98

How to know which parameter is dynamic/static?

Reference answer

Run the following SQL query: 1. Query V$PARAMETER View- If issys_modifiable is IMMEDIATE, the parameter can be changed dynamically. - If issys_modifiable is DEFERRED, the change takes effect at the next session login. - If issys_modifiable is FALSE, the database must be restarted for the change to take effect. 2. Query V$SYSTEM_PARAMETER Another method to check: ISSES_MODIFIABLE = TRUE → Can be changed at the session level.ISSYS_MODIFIABLE = IMMEDIATE → Can be changed dynamically at the system level. 3. Query V$PARAMETER2 for More Details 4. Check with SHOW PARAMETER If you want to see all parameters, use: - If -

99

Steps to Create Oracle Standby Database

Reference answer

Prerequisites- Primary Database: Ensure the primary database is in ARCHIVELOG mode and hasFORCE LOGGING enabled. - Network Configuration: Ensure proper network connectivity between the primary and standby sites. - Oracle Software: Install the same version of Oracle software on the standby server. - Storage: Ensure sufficient storage is available on the standby server. Steps to Create a Standby Database 1. Enable Archiving and Force Logging on the Primary Database -- Connect to the primary database as SYSDBA SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT; SQL> ALTER DATABASE ARCHIVELOG; SQL> ALTER DATABASE OPEN; SQL> ALTER DATABASE FORCE LOGGING; 2. Create a Password File on the Primary Database Ensure the primary database has a password file. If not, create one: orapwd file=$ORACLE_HOME/dbs/orapw password= entries=10 3. Configure the Primary Database for Data Guard Add the following parameters to the primary database's spfile orpfile :ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary_db,standby_db)'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standby_db VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby_db'; ALTER SYSTEM SET FAL_SERVER=standby_db; ALTER SYSTEM SET FAL_CLIENT=primary_db; ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO; 4. Create a Backup of the Primary Database Use RMAN to create a backup of the primary database: rman target / RMAN> BACKUP DATABASE PLUS ARCHIVELOG; 5. Copy the Backup to the Standby Server Transfer the backup files to the standby server using scp ,rsync , or any other file transfer method.6. Create an Initialization Parameter File for the Standby Database Create a pfile for the standby database:CREATE PFILE='/path/to/standby_init.ora' FROM SPFILE; Edit the pfile to reflect the standby database's settings - Primary Database: Ensure the primary database is in -

100

Explain the concept of storage engines in MySQL.

Reference answer

Storage engines are responsible for handling data storage, retrieval, and indexing in MySQL. Examples include InnoDB, MyISAM, and MEMORY.

101

What is DBCC?

Reference answer

Database Console Commands (DBCC) is a set of Transact-SQL commands for database management and maintenance in Microsoft SQL Server. Here are a few key uses of DBCC commands: - Checking Database Consistency: DBCC commands, such as DBCC CHECKDB, are used to verify the logical and physical integrity of a database. They can assist in identifying and resolving corruption, missing indexes, and other structural issues. - Performance Tuning: Some DBCC commands provide information about database performance and can be used to tune the performance of SQL Server. For example, DBCC SQLPERF provides information about various performance-related counters. - Statistics Updates: DBCC procedures like as DBCC UPDATEUSAGE and DBCC SHOW_STATISTICS are used to update and show statistics about database objects. Keeping statistics up to current is critical for the query optimizer to build effective execution plans.

102

How do you optimize a slow-running query?

Reference answer

To optimize a slow-running query, I would first analyze the query execution plan to identify any bottlenecks or areas causing delays. I look for things like full table scans, missing indexes, or inefficient joins. If the query is performing a full table scan, adding appropriate indexes to the columns used in the WHERE clause or JOIN operations can significantly improve performance. For instance, if the query frequently filters on a column, an index on that column can reduce the data retrieval time. I also consider rewriting the query to simplify it or break it down into smaller parts if possible. For example, using subqueries or temporary tables helps streamline complex queries. Additionally, I check for other factors, such as the proper use of joins, avoiding unnecessary columns in the SELECT statement, and ensuring that the statistics on the tables are up-to-date. These steps help ensure the query runs as efficiently as possible.

103

How do you manage multiple SQL Server instances?

Reference answer

Managing multiple SQL Server instances involves using SQL Server Configuration Manager to start, stop, and configure services, as well as using SQL Server Management Studio (SSMS) to connect to and administer each instance separately. It also requires careful planning for resource allocation, such as memory and CPU, to avoid conflicts between instances.

104

What is the process for running as a different user in SQL Server?

Reference answer

To switch users in SQL Server, press and hold Shift while right-clicking an ODBC window, select your driver for SQL server and assign any friendly names you desire; uncheck any bar and click Save, if applicable or navigate directly to client configuration as appropriate.

105

If an RMAN backup needs to complete within 40 minutes, can we enforce this?

Reference answer

Yes, using the BACKUP DURATION command: BACKUP DURATION 00:40 DATABASE;

106

How to check the listener status?

Reference answer

lsnrctl status

107

What is a response file in Oracle and how can it be created?

Reference answer

A response file is a plain text file, where options to create a database are stored. It is possible to create it manually from scratch but that would take longer and would be erroneous. Installation media comes with a template response file. It is rather easier to customize it manually. This file also contains notes about the parameters. However, the easiest and most reliable way to create a response file is by using Oracle Universal Installer. If you start the installer in "record" mode, every option you choose at each step is automatically recorded in a response file in the correct format. After the installer completes in "record" mode, you'll have a complete response file with all the options set in it.

108

What is the ORDER BY keyword used for in SQL?

Reference answer

ORDER BY keyword is used for sorting the results. It returns the sorted results to your program.

109

What is the difference between ‘rman target /' and ‘rman auxiliary /'?

Reference answer

rman target /: Connects RMAN to the target database for backup and recovery. rman auxiliary /: Connects RMAN to an auxiliary database for duplication and related tasks.

110

How will you resolve the sync issues?

Reference answer

1. Check network connectivity: Verify network stability and configure network settings for optimal performance. 2. Adjust archive log transmission parameters: Modify parameters like LOG_ARCHIVE_MAX_PROCESSES, LOG_ARCHIVE_DEST_1, and LOG_ARCHIVE_DEST_2 to optimize archive log transmission. 3. Clear archive log backlog: Manually delete or move archive logs from the primary database to clear the backlog. 4. Re-sync standby database: Use Oracle's built-in re-sync feature or manually re-create the standby database to re-establish synchronization. 5. Monitor and adjust: Continuously monitor the sync process and adjust parameters as needed to maintain optimal performance.

111

How can you restrict user access to an Oracle database during maintenance?

Reference answer

When a database is open, any user with the "CREATE SESSION" privilege can make a connection. However, it is possible to open the database in "restricted" mode. When a database is open in restricted mode, only users with the "RESTRICTED SESSION" privilege can make a connection to the database. By default, only DBAs have the "RESTRICTED SESSION" privilege and it should not be granted to regular users. Opening a database in "restricted" mode is a good way to prevent regular users from accessing the database during maintenance.

112

How do you approach database security, and what measures do you take to protect sensitive data?

Reference answer

I approach database security by implementing access controls, encryption, regular audits, and patch management. Measures include using strong authentication, encrypting data at rest and in transit, and monitoring for unauthorized access.

113

How can you secure MySQL installations and prevent unauthorized access?

Reference answer

Use strong passwords, disable root access from remote hosts, and restrict user privileges to minimum necessary access.

114

What is a database schema?

Reference answer

The database schema is a two-dimensional representation of how each table talks to each other, a way to view the design of a database as a single entity and not as a mixture of tables. They are Visio diagrams with 40 different tables connected through particular columns on either side.

115

Imp background process

Reference answer

PMON (Process Monitor), SMON (System Monitor), DBWR (Database Writer), LGWR (Log Writer), CKPT (Checkpoint).

116

What are the recovery models available in SQL Server?

Reference answer

SQL Server offers three recovery models: Simple, Full, and Bulk-Logged. Each model dictates how transaction logs are maintained and impacts how you can restore and recover data.

117

Can you describe a time you resolved a critical database issue?

Reference answer

This behavioral question allows candidates to demonstrate problem-solving skills. A good answer details a specific incident, the steps taken to diagnose the problem, and the outcome that improved the database environment.

118

What is the purpose of the mysql_upgrade utility in MySQL?

Reference answer

The mysql_upgrade utility is used to upgrade system tables and databases when upgrading MySQL to a newer version.

119

What is a SYSTEM tablespace and why do we need it?

Reference answer

System tablespace is created at the time of database creation. This tablespace holds all the data dictionary tables and hence it is used for the creation of various database objects. System tablespace must stay online for the database to function smoothly.

120

What happens if archive log destination is full?

Reference answer

The database will hang. We must have archive log backup scripts to take archive log backup and delete to release space.

121

How data dictionary views and dynamic performance views are created?

Reference answer

The catalog.sql and catproc.sql scripts created necessary views and procedures.

122

Suppoese your are firing select query to fetch a row and at the same time some job is running and deleted that row but they haven't comitted or rolled back the data , so for your select what result you will find?

Reference answer

When a SELECT query is issued while a job is running and deleting a row, but the transaction hasn't yet committed or rolled back, the result you get can depend on a few factors.– Undo Segments: Oracle uses undo segments to maintain a consistent view of the data for active transactions. Even though a row is marked for deletion by the transaction that hasn't committed, the undo segments will still store the previous version of the row (the version before the delete). – Consistent Read: Oracle ensures a consistent read for a SELECT query. So, even though the delete operation is in progress, the query will fetch the row as it was at the time the query started, not as it is at the time of the commit.– Your SELECT query would return the row as it existed before the deletion because Oracle would use the undo information to provide a consistent view of the data. – You would not see the row as deleted until the transaction is committed.

123

Which data dictionary view shows the available free space in a certain tablespace?

Reference answer

DBA_FREE_SPACE

124

Can you explain the process of database schema design and normalization?

Reference answer

Look for: Understanding of schema design principles and normalization techniques.

125

Explain the concept of the information_schema database in MySQL.

Reference answer

The information_schema database contains metadata about all other databases, tables, columns, and privileges in MySQL.

126

How can you implement full-text search in MySQL?

Reference answer

Use the MATCH and AGAINST keywords in the SELECT statement to perform full-text searches on text-based columns.

127

Which action occurs during a checkpoint?

Reference answer

Checkpoint Actions: 1. All modified database buffers are written to disk: All dirty buffers in the buffer cache are written to their corresponding datafiles on disk. 2. The SCN (System Change Number) is updated: The SCN is incremented and updated in the control file, datafile headers, and redo logs. 3. The checkpoint position is updated: The checkpoint position is updated in the control file to reflect the new SCN. 4. The redo logs are switched: The redo logs are switched, and a new redo log is opened for writing. 5. The database is marked consistent: The database is marked consistent, indicating that all changes have been written to disk

128

Can you describe the differences between a system database and a user database?

Reference answer

System databases are the core databases that come with the SQL Server installation; they're essential for the server's operation, holding crucial configuration and metadata. In contrast, a user database is created by an administrator or application to store actual business data. The main difference lies in their purpose: one manages the server itself, while the other stores an organization's specific information.

129

Can you explain the steps of upgradation?

Reference answer

I. Backup your database. II. Empty recycle bin & gather stats. III. Run oracle's database pre-upgrade utility. IV. Install the oracle's 19c database s/w. V. Run the DBUA Utility. VI. Run post-upgrade script & restart database.

130

What is a foreign key?

Reference answer

A foreign key is a column or a set of columns that references a primary key in another table. It establishes a relationship between two tables and ensures referential integrity. Foreign keys are used to enforce constraints and maintain consistency across related tables.

131

Can you explain what a deadlock is and how SQL Server handles deadlocks?

Reference answer

A deadlock occurs when two or more sessions are waiting for each other to release locks, in a circular chain, so that they can proceed with their transaction. SQL Server detects deadlocks automatically and resolves them by choosing one process as a deadlock victim and rolling back its transaction, allowing other transactions to proceed.

132

What is normalization in databases and why is it important?

Reference answer

Normalization is the process of organizing data to reduce redundancy and improve data integrity. It involves dividing a database into tables and defining relationships between them to ensure data consistency and efficient querying.

133

Explain the purpose of the my.cnf file in MySQL.

Reference answer

The my.cnf file is the configuration file for MySQL server settings, including database directories, buffer sizes, and other parameters.

134

Which data dictionary view would you query to retrieve a table's header block number?

Reference answer

DBA_SEGMENTS

135

A project manager needs a new SQL Server. What do you ask her?

Reference answer

Use a brochure from any third party application (like Microsoft Project Server or Blackberry Enterprise Server) and say the project manager wants to set this up. I want the DBA to ask questions like: - How big will the database be? (Leading to questions about whether we can add the database to an existing server) - How critical is the database? (Leading to questions about clustering, disaster recovery, high availability) - What's the company standard on virtualization? (Can we save money by using a virtual server) If the senior DBA candidate comes back with a shocked look and doesn't know where to begin, then they haven't done a lot of deployments.

136

What is sharding in SQL Server?

Reference answer

Sharding is a database architecture pattern that horizontally partitions data across multiple databases or servers, called shards, based on a shard key. It improves scalability and performance by distributing load, but it requires careful design for data distribution and query routing.

137

How do you create a new database in MySQL?

Reference answer

To create a new database, use the SQL command: sqlCopy code CREATE DATABASE dbname;

138

How would you migrate a database from one server to another?

Reference answer

First, I take a full backup from the source server. Then, I restore it on the target server with the right recovery options. After restoring, I fix orphaned users, update server-specific settings, and test the application connection.

139

Describe the enhancements in SQL Server 2022 for disaster recovery planning.

Reference answer

SQL Server 2022 enhances disaster recovery planning through its improved Always On Availability Groups, which now support automatic page repair for additional reliability and easier management of disaster recovery configurations. The Accelerated Database Recovery feature significantly reduces recovery times by maintaining a persistent version store in the database, speeding up the rollback and recovery processes. SQL Server 2022 also includes better integration with Azure for disaster recovery, allowing databases to be backed up directly to Azure Blob Storage and facilitating geo-replication for global distribution and redundancy.

140

Can you describe a situation where you had to recover data from a database backup?

Reference answer

Data recovery is an essential skill for any DBA. Discuss a scenario where you successfully recovered data from a backup, the approach you took, and any lessons learned from the experience. Once, due to a system failure, we lost some data. Fortunately, we had a backup strategy in place, including regular full and incremental backups. I restored the full backup and then applied the incremental backups in the order they were created until the point of failure. This experience emphasized the importance of a robust backup strategy and regular testing.

141

What are the key steps in an Oracle database upgrade?

Reference answer

Backup the database. Gather statistics and clean up unnecessary objects. Run Oracle's pre-upgrade utility. Install the new database software. Execute the upgrade scripts. Perform post-upgrade checks and testing.

142

What is default value for storage parameter INITIAL in 10g if extent management is Local?

Reference answer

40k

143

Explain the difference between INNER JOIN and LEFT JOIN in MySQL.

Reference answer

An INNER JOIN returns only matching rows from both tables, while a LEFT JOIN returns all rows from the left table and matching rows from the right table.

144

I would like to perform 100 database installations. How will you do it?

Reference answer

For such big number of installations, we can go with silent mode installation using response file.

145

Does a materialized view occupy space?

Reference answer

Yes.

146

What is the use of redo log files?

Reference answer

Redo log files in Oracle are used to record all changes made to the database. Their primary purposes are: - Crash Recovery – Helps recover the database in case of a system failure. - Data Integrity – Ensures committed transactions are not lost. - Replication & Standby – Used for redo shipping in Data Guard and replication setups. - Rollback & Roll-Forward – Enables rolling back uncommitted transactions and rolling forward committed changes.

147

After opening the database with RESETLOGS, can I still flashback the database?

Reference answer

No, once the database is opened with the RESETLOGS option, you cannot flashback to a point before the resetlogs operation.

148

What types of a backup do we need, if we want to restore to a point in time?

Reference answer

FULL, LOG, and optionally DIFFERENTIAL

149

Have you worked with on-premises databases, cloud databases or both?

Reference answer

Most businesses are currently moving to cloud infrastructure. If that's your case, then it's important to learn more about a candidate's experience working with cloud databases and know what environment they work best. Additionally, suppose your organization has an on-premises database and is looking to shift towards a hybrid database. In that case, you need to find out if a candidate will be able to support your long-term goals.

150

What do we mean by hot backup & cold backup and how are they different?

Reference answer

Hot backup is the process of taking database backup while the database is in running mode. Hence, it is also known as Online Backup. While cold backup can be taken only when the database is in shut down mode and hence it is known as Offline Backup as well. There are few websites like banking & trading ones, which are 24 hours operational and hence, cannot support bringing the database down. Hence, DBAs need to take the backup in online mode only.

151

What is the SELECT statement used for in SQL?

Reference answer

The SELECT statement is used to select the set of specific values from a table in a database depending on the various conditions specified in a SQL query.

152

When are base tables created? what will happen if we do not run catalog.sql and catproc.sql

Reference answer

Base tables are created when you create a database. If you do not run those scripts, we will not be able to query any data dictionary view or dynamic performance view.

153

Why can Row Compression create massive fragmentation problems where there were none on the table before, and what other compression would affect that?

Reference answer

Row Compression can create massive fragmentation because it changes the physical layout of data pages, potentially causing page splits and increased logical fragmentation. Page compression can also affect fragmentation by reorganizing data within pages, but it may reduce fragmentation in some cases. Both compression types should be tested and monitored for fragmentation impacts.

154

When a select query runs in db how it fectches the data?

Reference answer

When a SELECT query is executed:1. Parser: Parses the query and generates an abstract syntax tree (AST). 2. Query Optimizer: Analyzes the query and generates an optimal execution plan. 3. Execution Engine: Executes the plan, retrieving data from storage or memory. 4. Data Processing: Performs aggregations, transformations, and generates the result set. 5. Result Set Return: Returns the result set to the client application.Key architecture components: – Parser – Query Optimizer – Execution Engine – Data Retrieval – Data Processing – Result Set Generator – Storage

155

Suppose that data is not there in undo block what will happen?

Reference answer

If the undo information for the deleted row has been overwritten or is no longer available, Oracle will not be able to return the row. The transaction's consistent view will be lost, and either the query will return no data or the system might raise an ORA-01555 error.

156

Explain how you contributed to a challenging project you worked on.

Reference answer

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

157

How would you handle security in cloud-based databases?

Reference answer

Some key measures I take are: - Data encryption: Enable encryption both at rest and in transit. For at-rest encryption, I use the cloud provider's encryption services like AWS KMS or Azure Key Vault to manage encryption keys. For data in transit, I use SSL/TLS to encrypt connections between the application and the database. - Access control: Implement the principle of least privilege by granting only the necessary permissions to users and applications. Use Identity and Access Management (IAM) roles and policies to control access to the database and its resources. Additionally, enable multi-factor authentication (MFA) for administrative access. - Network security: Utilize Virtual Private Cloud (VPC) or Virtual Network (VNet) configurations to isolate databases within a secure network. Use security groups, firewalls, and network ACLs to restrict access to the database to trusted IP addresses or subnets. - Monitoring and auditing: Enable database logging and monitoring features to track access and query execution. Use services like AWS CloudTrail, Azure Monitor, or Google Cloud Audit Logs to maintain an audit trail of database activities. - Compliance and regular security audits: Ensure the database complies with relevant regulations like GDPR or HIPAA by configuring data protection settings and performing regular security audits and vulnerability assessments.

158

Can we enable tracing in dataguard .

Reference answer

Yes we can set LOG_ARCHIVE_TRACE parameter to trace redo transport and apply services on primary and standby.possible values are 0(default means no tracing) , 1,2,4,8,16,32,64 …. 8192

159

What is a sequence in Oracle?

Reference answer

A sequence generates a serial list of unique numbers for numerical columns of database tables. We can use the sequence on columns for data where we want to insert data in a sequential manner.

160

How do you perform a backup and restore in MySQL?

Reference answer

Use the mysqldump command for backups and the mysql command or a tool like mysqlimport for restores. Example: bashCopy code mysqldump -u username -p dbname > backup.sql mysql -u username -p dbname < backup.sql

161

What will happen if we do not specify user default tablespace?

Reference answer

The DATABASE DEFAULT TABLESPACE will become user default tablespace.

162

Archive dest full – quickest way to solve this issue

Reference answer

Add more archive destinations, increase disk space, or delete/move old archived logs after confirming backups.

163

When Dictionary tables are created?

Reference answer

Once for the Entire Databasecreation.

164

How is a full-text search different from a regular search in SQL Server?

Reference answer

A full-text search enables powerful linguistic searches such as finding word variations, phrases, or words near each other. It supports CONTAINS, FREETEXT, and inflectional forms (e.g., “run” also matches “running”). In contrast, regular searches (like WHERE Name LIKE '%run%') only match literal string patterns, which limits flexibility and performance on large text fields.

165

Advantages and Disadvantages of Backup Sets and Image Copies

Reference answer

Backup Sets: Advantages: Efficient in terms of storage, supports compression, and improves backup and restore performance. Disadvantages: Takes more time to create and restore compared to image copies. Image Copies: Advantages: Faster restoration since files can be directly copied back to their original location. Disadvantages: Requires more storage space and is generally slower for backup operations compared to backup sets.

166

What strategies contribute to ensuring data integrity in transactional systems?

Reference answer

Strategies include enforcing foreign key and unique constraints, using transactions with appropriate isolation levels, implementing triggers for validation, periodic consistency checks, and maintaining thorough backup and restore procedures.

167

Difference between Oracle home and Oracle base?

Reference answer

ORACLE_BASE: The root directory for Oracle software installations. ORACLE_HOME: The directory under ORACLE_BASE where Oracle products are installed.

168

Explain what Oracle is.

Reference answer

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

169

What policies and procedures help ensure ongoing data integrity after migrations or system upgrades?

Reference answer

Policies and procedures involve performing pre- and post-migration integrity checks, validating data transformations, running reconciliation reports, thorough testing in pre-production environments, and maintaining rollback plans to ensure data consistency.

170

How to examine SQL Server's CPU utilisation in percentages?

Reference answer

First, remote into the server where SQL Server instances are running, next, use Task Manager's "processes" column to observe SQL Server Processes if one or multiple instances on the same server exist; for CPU utilisation analysis, use Task Manager again by viewing all applications sold and their usage.

171

Your data guard is on same location or different

Reference answer

In a typical *disaster recovery setup, I would configure Oracle Data Guard to have the Primary and Standby databases located in different cities or geographic regions. This setup helps ensure that even if there's a localized disaster (like a natural disaster, power outage, or network failure) affecting one location, the other location can take over, minimizing downtime and preventing data loss.

172

What is ASM and why is it used in Oracle?

Reference answer

Automatic Storage Management (ASM) is Oracle's integrated volume manager and file system designed for Oracle database files. It simplifies storage management by abstracting the physical disks into logical disk groups. ASM automatically manages striping and mirroring of data across disks to improve performance and provide redundancy. It reduces the complexity of managing individual files and disks manually.ASM is used because it: • Simplifies database storage administration. • Improves I/O performance via balanced striping. • Provides fault tolerance with different redundancy levels (normal, high). • Supports dynamic resizing and addition/removal of disks. • Integrates fully with Oracle tools like RMAN and Grid Infrastructure. • Enables easier scaling of storage with less downtime. ASM is the preferred storage solution in Oracle RAC and large database environments.

173

What are the different optimizers that are used to optimize the database?

Reference answer

There are two types of optimizers: 1. Rule-Based Optimizer (RBO): If the referenced objects don't maintain any internal statistics, RBO is used. 2. Cost-Based Optimizer (CBO): If the referenced objects maintain internal statistics, CBO will check all the possible execution plans and select the one with the lowest cost.

174

Locally Managed table spaces will increase the performance?

Reference answer

TRUE

175

What is B-tree index & when it'll be used?

Reference answer

– B-tree indexes are preferred in OLTP environment. Refer Q31 – Preferred when cardinality is high

176

How to create a user and assign a profile?

Reference answer

CREATE USER PADDU IDENTIFIED BY PADDU DEFAULT TABLESPACE TESTTBS1 PROFILE TEST; GRANT CONNECT, RESOURCE TO PADDU;

177

How do you create a new database in SQL Server?

Reference answer

To create a new database in SQL Server, you can use the CREATE DATABASE statement in T-SQL, specifying the database name and optional parameters like file locations and sizes. Alternatively, you can use the graphical interface in SSMS by right-clicking on the Databases folder and selecting 'New Database'.

178

We are having dataguard setup in maximum protection mode , and we created one datafile in a new diskgroup on primary, But that diskgroup was not present in standby? What will be the outcome?

Reference answer

The primary will shutdown .Primary crashes immediately because standby can't apply redo in Maximum Protection mode

179

What is the difference between a Data Dictionary Table and a Normal Table?

Reference answer

A data dictionary table contains metadata about database objects such as tables, indexes, users, privileges, and schemas. It provides essential information for database management but is not modifiable by users. In contrast, normal tables store user data and can be read, modified, and deleted by authorized users.

180

How can a lost control file be restored?

Reference answer

By restoring from a backup and using RECOVER DATABASE USING BACKUP CONTROLFILE

181

Difference between MS SQL Server and PostgreSQL

Reference answer

MS SQL SERVER | POSTGRESQL | |---|---| | Developed by Microsoft Corporation and initially released on April 24, 1989 | Developed by PostgreSQL Global Development Group on 1989. | | MS SQL server is written in C++ language. | PostgreSQL is written in C language. | | It is a Microsoft relational DBMS. | It is a widely used open-source RDBMS. | | The primary database model for MS SQL Server is Relational DBMS. | The primary database model for PostgreSQL is also Relational DBMS. | | It also has two Secondary database models – Document Store and Graph DBMS. | It has a Document store as a Secondary database model. |

182

Database Cloning Steps (Manual/RMAN):

Reference answer

Using RMAN: - Take backup of source DB. - Copy backups to clone server. - Restore controlfile. - Restore and recover datafiles. - Open database with RESETLOGS.

183

What are the different types of databases?

Reference answer

There are several types of databases, including relational databases, NoSQL databases, cloud databases, and data warehouses. Relational databases use tables to store data, while NoSQL databases use key-value pairs, document stores, or graph databases. Cloud databases are hosted on cloud platforms, and data warehouses are used for analytical processing.

184

How can we collect the statistics of different database objects?

Reference answer

ANALYZE statement can be used to collect the statistics of various database objects like tables, indexes, partitions, cluster or object references. Using this statement we can also identify migrated as well as chained rows within a table or cluster.

185

Which parameter defines redo log transport service attributes?

Reference answer

LOG_ARCHIVE_DEST_n (e.g., LOG_ARCHIVE_DEST_2) specifies the standby service name, transport mode (SYNC/ASYNC), and attributes like AFFIRM/NOAFFIRM.

186

What kernal parameters you set before running ./runInstaller?

Reference answer

– Setting Kernel Parameters: It adjusts various kernel parameters in /etc/sysctl.conf to optimize the system for Oracle Database performance. – Setting Resource Limits: It modifies /etc/security/limits.conf to configure limits for the Oracle user (e.g., file descriptors, processes). – Configuring Groups and Users: It creates the oracle user and relevant groups (e.g., oinstall, dba). – Disk Partition and Directory Creation: It sets up Oracle directories and ensures permissions are correctly set. – Enabling Required Services: It prepares the system by configuring necessary services like swap, shared memory, and file system. Pre-Installation Package Actions:1. Installing the Pre-Installation Package (RHEL/CentOS/Oracle Linux): bash sudo yum install -y oracle-database-preinstall-19c This package will automatically adjust various system settings, and you won't need to manually adjust kernel parameters unless you want to customize them further.2. The Pre-Installation Package Automatically Configures Kernel Parameters -

187

How can you find data files related to a tablespace?

Reference answer

We can query DBA_DATA_FILES to check tablespace related information.

188

How do you monitor ASM performance and space?

Reference answer

Monitoring ASM involves: • Using views like V$ASM_DISKGROUP, V$ASM_DISK, and V$ASM_OPERATION to check disk group space, health, and ongoing operations. • Checking ASM alert logs for errors or warnings. • Using Oracle Enterprise Manager ASM plugin for graphical monitoring. • Tracking space usage, free space, and rebalance progress. • Monitoring I/O statistics and disk failures to detect bottlenecks or faults early. • Setting up alerts for disk failures, space thresholds, or rebalancing issues. • Regularly reviewing ASM metadata and disk status to maintain high availability

189

Tell me about a time when you improved database performance significantly.

Reference answer

Situation: Our main reporting system was taking 2+ hours to generate daily executive reports, causing delays in business decision-making. Task: I needed to optimize performance while maintaining data accuracy and keeping the system online. Action: I analyzed query execution plans and discovered missing indexes on key join columns. I also identified several inefficient queries that were doing full table scans. I implemented index changes during maintenance windows and worked with developers to rewrite problematic queries. Result: Report generation time dropped to 15 minutes—an 87% improvement—and we could now run reports multiple times per day, giving executives more timely business insights.

190

If primary database has 2 DR and one DR is in sync and one is out of sync, are we able to perform switchover activity? What are the switchove_status in primary database.

Reference answer

Terminology: Primary with two standbys (Data Guard). If one standby is synchronized (REDO APPLY / SYNC) and the other lagging, switchover feasibility depends: - For a switchover, the target standby must be a valid switchover target (healthy, consistent with archived redo) and must be in READY state. Only one standby is required for switchover. - You can switchover to the in-sync standby. The out-of-sync standby will need later re-synchronization. Switch-over statuses you can check: Important details: - If using Data Guard broker ( DGMGRL ),show database 'standby_db' status will showSTANDBY /SUCCESS /NEED-LOG etc. - Use v$database :OPEN_MODE ,DATABASE_ROLE . - Use v$dataguard_stats for gaps. If the chosen standby is in sync (APPLY or NO GAP), you can perform switchover. Ensure logs shipped and applied and that the standby can be opened read/write after switchover. After switchover, reconfigure and resync the out-of-sync standby (maybe by reinstatement or full resync).

191

Which data dictionary view would you query to retrieve a table's header block number?

Reference answer

DBA_SEGMENTS

192

How do you ensure high availability for critical database systems?

Reference answer

I implement high availability using a combination of Always On Availability Groups for SQL Server or Master-Slave replication for MySQL. I configure automatic failover for critical systems with health monitoring and alerting. For our e-commerce platform, I set up a three-node cluster with synchronous replication to a secondary node and asynchronous replication to a disaster recovery site. This configuration provides an RTO of under 60 seconds for automatic failover and has maintained 99.97% uptime over the past two years.

193

How do you ensure high availability for databases in the cloud?

Reference answer

One common approach is to utilize the cloud provider's managed database services, like Amazon RDS, Azure SQL Database, or Google Cloud SQL, which offer built-in HA features. These services provide multi-AZ (Availability Zone) deployments, automatic failover, and backup solutions. For example, in AWS, I would set up an Amazon RDS instance with Multi-AZ deployment, which automatically replicates data to a standby instance in a different Availability Zone. In case of a failure, the system will automatically failover to the standby instance, minimizing downtime. Another method is to implement replication and clustering. For instance, using PostgreSQL on a cloud VM, I could set up streaming replication and a failover mechanism with tools like pgPool or Patroni to ensure database availability. I also configure regular automated backups and monitor the database with alerting mechanisms for proactive issue detection. This table illustrates different high availability (HA) strategies in cloud-based database environments: | HA Strategy | Description | Example cloud provider feature | | Multi-AZ deployment | Automatically replicates data across multiple availability zones for failover. | AWS RDS Multi-AZ | | Read replicas | Creates replicas in different regions or AZs for load balancing and failover. | AWS Aurora Read Replicas | | Automated backups & snapshots | Regular automated backups for disaster recovery and point-in-time recovery. | Google Cloud SQL Backups | | Active-passive failover | A secondary server takes over if the primary server fails, ensuring availability. | Azure SQL Database Failover Groups |

194

Stored procedures in SQL

Reference answer

Stored procedures are prepared SQL code that you save so you can reuse it over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure and call it to run it.

195

What is log shipping in SQL Server?

Reference answer

Log shipping is a high-availability feature in SQL Server that automatically sends transaction log backups from a primary database to one or more secondary databases. It involves backing up the transaction log on the primary server, copying it to the secondary server, and restoring it to keep the secondary database synchronized.

196

There are 20 databases created from one single oracle home. How many listeners will you configure?

Reference answer

One is enough but it is recommended to have separate listeners for each database.

197

What is database monitoring and how is it done?

Reference answer

Monitoring involves tracking key metrics like CPU usage, query response times, disk I/O, and memory usage. Tools like Oracle Enterprise Manager, SQL Server Profiler, or third-party monitoring software help identify bottlenecks and optimize performance.

198

What is a far sync instance in Data Guard?

Reference answer

A far sync instance is a lightweight Oracle instance (control file only, no datafiles) that receives redo synchronously from the primary and forwards it asynchronously to remote standbys (up to 30). It's used when standbys are geographically distant, requiring Active Data Guard licensing.

199

Calculate the monthly retention rate for users for each account_id for December 2020 and January 2021. The retention rate is defined as the percentage of users active in a given month who have activity in any future month. The final output should include the account_id and the ratio of the retention rate in January 2021 to the retention rate in December 2020 for each account_id.

Reference answer

WITH max_dates AS (SELECT user_id, account_id, MAX(record_date) AS max_date FROM sf_events GROUP BY user_id, account_id), dec_2020 AS (SELECT DISTINCT account_id, user_id FROM sf_events WHERE FORMAT(record_date, 'yyyy-MM-01') = '2020-12-01'), jan_2021 AS (SELECT DISTINCT account_id, user_id FROM sf_events WHERE FORMAT(record_date, 'yyyy-MM-01') = '2021-01-01'), retention_dec AS (SELECT d.account_id, COUNT(DISTINCT CASE WHEN m.max_date > '2020-12-31' THEN d.user_id END) * 1.0 / COUNT(DISTINCT d.user_id) AS retention_dec FROM dec_2020 d JOIN max_dates m ON d.user_id = m.user_id AND d.account_id = m.account_id GROUP BY d.account_id), retention_jan AS (SELECT j.account_id, COUNT(DISTINCT CASE WHEN m.max_date > '2021-01-31' THEN j.user_id END) * 1.0 / COUNT(DISTINCT j.user_id) AS retention_jan FROM jan_2021 j JOIN max_dates m ON j.user_id = m.user_id AND j.account_id = m.account_id GROUP BY j.account_id) SELECT rj.account_id, ISNULL(rj.retention_jan, 0) / NULLIF(rd.retention_dec, 0) AS ratio FROM retention_dec rd LEFT JOIN retention_jan rj ON rd.account_id = rj.account_id;

200

What is the purpose of mapping a database user to a login?

Reference answer

Give the user access to the SQL Server database Enable the user to use other schemas Restrict the user's access to the SQL Server database Create a new schema for the user

DON'T WANT TO MISS A THING?

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

DBA Interview Questions and Answers Guide | 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

DBA Interview Questions and Answers Guide | SPOTO

Earn a certification to make your resume stand out.

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