Mock Interview Questions for Database Administrators

1

How do you handle database partitioning for large tables?

Reference answer

Choose partitioning strategy: Horizontal partitioning (range, hash, or list-based) divides data across multiple physical storage units. Consider range partitioning by date for time-series data, hash partitioning for even distribution, or list partitioning for categorical data. Implement partition pruning: Ensure queries can eliminate partitions they don't need to access. Design partition keys that align with common query patterns to maximize performance benefits. Manage partition maintenance: Automate creation of new partitions, archival of old data, and maintenance tasks like statistics updates across partitions.

2

How to start DB in RAC environment?

Reference answer

Using srvctl:

3

How do you troubleshoot performance issues in SQL Server?

Reference answer

To troubleshoot performance issues, a DBA can: Leverage SQL Server Profiler to monitor and evaluate SQL queries, helping to identify performance issues and optimize database operations. Examine the execution plans to identify inefficient queries. Monitor system performance metrics such as CPU, memory, and disk usage. Analyze wait statistics to determine system bottlenecks. Optimize indexes and query structures.

4

Should we patch the standby database first or the primary? Can we patch both together?

Reference answer

Best practice is to patch the standby database first to validate the patch before applying it to the production system. Patching both together is possible but increases risk and complexity.

5

Can you describe your experience with data migration and the steps you take to ensure a smooth transition?

Reference answer

Data migration involves planning, extracting, transforming, and loading data. I ensure a smooth transition by thoroughly testing the migration process, validating data integrity, and having a rollback plan in case of issues.

6

What is FAST_START_MTTR_TARGET parameters?

Reference answer

This parameter specifies the checkpoint frequency. By default it is set to 3 seconds.

7

What is an Oracle SCN?

Reference answer

System Change Number (SCN) is a unique identifier that Oracle assigns to every transaction to maintain consistency and recovery operations.

8

Can you share an experience where you had to present or deliver a technical presentation related to database management? How did you prepare and engage the audience effectively?

Reference answer

Look for: Presentation and engagement skills.

9

Explain what exactly the deadlock is?

Reference answer

Deadlock occurs when two operations are trying to change rows of a table that are restricted by the other operation. This is frequently caused by failure to issue adequate row lock requests. This condition can be caused by a poorly designed UI application, and the server's productivity will suffer as a result. When a commit/rollback action is conducted, or either of these programs is stopped outside, these restrictions will be freed immediately.

10

Explain your backup and database maintenance strategy for your biggest critical OLTP database?

Reference answer

This is a non-technical question to evaluate your dedication, positive attitude, flexibility, and readiness to adopt new changes. You should explain your backup and maintenance strategy for a critical OLTP database, including frequency, types of backups, and integrity checks.

11

What is Disaster Recovery (DR)?

Reference answer

Disaster Recovery refers to the processes and technologies used to restore and maintain database operations after unexpected failures.

12

How do you find the third highest marks from a Student table?

Reference answer

```sql SELECT TOP 1 marks FROM ( SELECT DISTINCT TOP 3 marks FROM student ORDER BY marks DESC ) AS Top3Marks ORDER BY marks ASC; ``` This finds the 3 highest distinct marks and then selects the lowest from that set.

13

Can RMAN backups be taken in NOARCHIVELOG mode?

Reference answer

Yes, RMAN backups can be performed in NOARCHIVELOG mode, but you will not be able to recover the database to a specific point in time since redo logs are not archived.

14

How can you implement data encryption and security features in MySQL?

Reference answer

Use the AES_ENCRYPT and AES_DECRYPT functions for data encryption and features like SSL/TLS for secure connections.

15

How do you set the Oracle SID and connect as SYSDBA?

Reference answer

$ export ORACLE_SID=ORCL $ export ORACLE_HOME=/uo1/app/oracle/product/11.2.0.4/dbhome $/uol/app/oracle/product/11.2.0.4/dbhome/bin/sqlplus/ as sysdba

16

Explain what SQL is.

Reference answer

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

17

Which tablespace is used as the temporary tablespace if TEMPORARY TABLESPACE is not specified for a user?

Reference answer

SYSTEM

18

What is the functionality of Queue Reader Agent in Merge Replication?

Reference answer

Merge Replication does not use a queue reader agent so there is no functionality of queue reader agent in Merge replication.

19

Which tool can be used to configure DB mail?

Reference answer

SQL Server Management Studio Server CMD SQL Server Management Studio Graphic User Interface

20

Explain any of your two deliveries or solutions for which you have been recognized by your client and leaders?

Reference answer

This is a non-technical question to evaluate your dedication, positive attitude, flexibility, and readiness to adopt new changes. You should describe two specific projects or solutions where you received recognition, highlighting the impact and your role.

21

Explain about optimizer?

Reference answer

When we travel from point A to point B, we tend to take the shortest route even though we have multiple options. Same way, optimizer generates different plans or ways in which a SQL can be executed. These plans are known as execution plans. Optimizer then chooses the best plan based on CPU cost and resources. The job of optimizer is to generate execution plans and choose the best one.

22

How you will give full database read only access to user?

Reference answer

To give full database read-only access to a user:GRANT SELECT ANY TABLE TO username; Alternatively, you can also grant the SELECT_CATALOG_ROLE and READ_ONLY roles: GRANT SELECT_CATALOG_ROLE TO username; GRANT READ_ONLY TO username;

23

SYSTEM TABLESPACE can be made off-line?

Reference answer

No

24

How can indexing strategies be fine-tuned to enhance database performance?

Reference answer

Indexing strategies are refined by analyzing usage patterns, eliminating redundant indexes, leveraging covering indexes, refreshing index statistics, and using filtered or partial indexes for selective optimization.

25

What is table partitioning and why is it required?

Reference answer

It is a process of dividing a table into smaller chunks so as to make the data retrieval easy and quick. Each piece will be known as a partition and can be accessed separately. Apart from tables, indexes can also be partitioned

26

Can you describe your experience with database replication and high-availability solutions?

Reference answer

Look for: Knowledge of replication techniques and HA configurations.

27

How to create password file?

Reference answer

$ orapwd file=orapwSID password=sys_password force=y nosysdba=y

28

What you will check first before downloading Oracle client?

Reference answer

The server operating system where we are installing Oracle client as we need to download as per client server, not as per database server OS.

29

What is a cursor in SQL Server?

Reference answer

A cursor is a database mechanism that allows traversal over rows in a result set one row at a time. While sometimes necessary, set-based operations are generally preferred for performance.

30

What is RAC and what are the various benefits of using RAC architecture?

Reference answer

RAC or Real Application Cluster allows the database to be installed across multiple servers forming a cluster and sharing the storage structure at the same time. This prevents the database from a single point of failure as one or the other instance will always stay up even if the other fails. Using RAC helps in 1.Maintaining high availability of the system. 2.Managing workload with the least expenses. 3.Scalability & agility.

31

Explain to me the process how you rebuild indexes and when would you do that?

Reference answer

Index rebuilding is performed to reduce fragmentation and improve query performance. The process: 1) Analyze index fragmentation levels using tools like DBCC SHOWCONTIG (SQL Server) or ANALYZE INDEX (Oracle). 2) Rebuild indexes with a fragmentation level above 30% using commands like ALTER INDEX REBUILD. 3) For lower fragmentation (5-30%), consider reorganizing indexes instead. 4) Schedule index maintenance during off-peak hours to minimize impact. 5) After rebuilding, update statistics to ensure the query optimizer has accurate data. Common times to rebuild include after large data loads, heavy update operations, or when performance degradation is observed.

32

Explain what a join clause is in SQL.

Reference answer

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

33

How would you migrate a large database with minimal downtime?

Reference answer

Plan the migration: Use replication-based approaches where possible—set up the target system as a replica, let it sync, then perform a quick cutover. For schema changes, use online migration tools that can handle data transformation during replication. Minimize downtime window: Pre-stage as much as possible, use tools like AWS DMS or Azure Database Migration Service for cloud migrations, and coordinate with application teams to implement connection string changes quickly. Prepare rollback procedures: Maintain the original system until the migration is validated, have clear rollback triggers and procedures, and test the entire process in staging environments first.

34

Explain the digits in Oracle database version 11.2.0.4.

Reference answer

"11": This first digit shows the major database version. Oracle usually publishes a major release once a 4 year. This digit is usually followed by a character describing the nature of the release. For example: 9i (internet), 10g (grid), 11g (grid), 12c (cloud). "2": This second digit shows the maintenance release number of the software. Oracle publishes the major release as maintenance release 1 and then usually publishes a second maintenance release during the lifetime of the software. New features are added to database Software with maintenance releases. "o": This third digit is Fusion Middleware Number. This will be o for database software. "4": This fourth digit is called Component-Specific Release Number and it shows the path set update that was applied to the software. Patch set updates are published 4 times a year by Oracle and as you apply them to your database software, this fourth digit advances.

35

What is an Oracle database and what are its file types?

Reference answer

An Oracle database resides on disk and this is permanent. It is composed of files that are stored on a disk. These files can be categorized into three types:

36

How to shutdown the database?

Reference answer

SHUTDOWN [NORMAL | IMMEDIATE | TRANSACTIONAL | ABORT]

37

What are some famous authors in the SQL Server DBA field, and why is it important to know about them?

Reference answer

Famous authors include Brent Ozar, Paul Randal, and others like Kimberly Tripp and Glenn Berry. Knowing about them is important because they are heavy hitters who provide authoritative guidance, best practices, and tools that are widely respected in the industry. If a non-developer DBA doesn't know about them, it may indicate a lack of engagement with the community and current best practices.

38

How can we view the status of a rollback segment?

Reference answer

Using the DBA_ROLLBACK_SEG view.

39

What is scope parameter?

Reference answer

When we use spfile, there are some parameters which can be modified dynamically. That is the biggest benefit of spfile when compared to pfile. All the parameters that you modify while database is up and running can take three scope values: spfile, memory, both. Spfile scope will make changes from next reboot, memory scope will make changes immediately but will revert back after reboot and both will make changes immediately.

40

Explain what databases are.

Reference answer

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

41

What are the advanced methods for encrypting sensitive data within a database?

Reference answer

Advanced methods encompass transparent data encryption (TDE), column-level and application-level encryption, use of hardware security modules (HSMs) for key management, and integrating with external key management services for enhanced security.

42

What is a SQL Server trigger?

Reference answer

A trigger in SQL Server is a special kind of stored procedure that automatically runs when certain events occur in the database table, such as insert, update, or delete operations.

43

You get the alert and tickest from OEM so your OEM is integrated to Service Now or not?How it is?

Reference answer

Yes, Oracle Enterprise Manager (OEM) can be integrated with ServiceNow to automate incident management. The integration ensures that alerts generated in OEM automatically create tickets in ServiceNow, reducing manual effort and improving response time. OEM Alerts and Tickets OEM provides alerts and tickets for various database-related events, such as:– Performance issues – Space utilization – Security vulnerabilities – Configuration changes Integration with ServiceNow To integrate OEM with ServiceNow, we can use the OEM's built-in integration capabilities or third-party pluginsExample of OEM-ServiceNow Integration 1. OEM detects a performance issue with a database instance and generates an alert. 2. The OEM-ServiceNow plugin sends the alert details to ServiceNow, which creates a new incident. 3. The incident is assigned to the database team, which investigates and resolves the issue. 4. Once the issue is resolved, the incident is closed in ServiceNow, and the resolution details are synced back to OEM.By integrating OEM with ServiceNow, we can streamline incident management, improve collaboration, and enhance reporting and analytics capabilities, ultimately leading to better database operations and improved service quality.

44

In a company, how does an Oracle DBA position vary from an Oracle Developer role? Is there any resemblance between these as well?

Reference answer

The Oracle developer is mostly in charge of creating backend applications. The model data in accordance with business rules. They generate tables, indexes, and other types of restrictions. They are required to be familiar with SQL and PL/SQL. These languages are used to create processes. Oracle developers, on the other hand, are not required to manage the database software. But, an Oracle DBA's primary responsibility is to administer the database, which covers areas such as doing maintenance to keep the databases operational, taking backups, maintaining privacy standards, and so on. DBAs are not typically tasked with writing code. DBAs, like developers, are expected to have a strong understanding of SQL and PL/SQL, as they are also essential for database administration. DBAs may be allocated development responsibilities or, at the very least, support developers when needed, depending on the company's structure.

45

SMON process is used to write into LOG files?

Reference answer

No

46

How do you handle conflicts in a multi-user database environment?

Reference answer

I implement appropriate isolation levels to manage concurrent transactions and use locking mechanisms to prevent data conflicts. Additionally, I monitor for deadlocks and resolve them promptly to ensure smooth database operations.

47

How many maximum no of control files we can have within a database?

Reference answer

8

48

When users complain about performance, what is the name of the procedure you can use to quickly list the current sessions and see what are they doing or waiting for?

Reference answer

sp_who2 or sp_WhoIsActive (one is enough)

49

What are the different types of retention policies? Explain about them.

Reference answer

Recovery Window-Based Retention Policy: In this policy, Oracle checks the current backup and looks for its relevance backward in time. Example: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; This ensures that the database can be recovered to any point in the last 7 days.Redundancy-Based Retention Policy: Specifies how many backups of each datafile must be retained. Older backups beyond the specified count become obsolete. Example: CONFIGURE RETENTION POLICY TO REDUNDANCY 2;No Retention Policy: Means backups will never be obsolete. Example: CONFIGURE RETENTION POLICY TO NONE;

50

What happens if a datafile or tablespace is dropped during an RMAN backup?

Reference answer

The backup may face inconsistencies or errors if RMAN tries to access a missing file.

51

How does AIR reduce time-to-hire?

Reference answer

AIR reduces time-to-hire by 80% through conversational voice interviews, resume matching & stack ranking, customizable scoring frameworks, and enterprise-scale assessments in 16+ languages.

52

What are Rolling, Non-Rolling, and Hybrid Patching in Oracle RAC?

Reference answer

Rolling Patching: Nodes are patched one at a time while the cluster remains online, minimizing downtime. Non-Rolling Patching: The entire cluster is taken offline, and all nodes are patched simultaneously. Hybrid Patching: A mix of both, where some nodes are patched in a rolling manner, while others are patched together.

53

Explain what SQL Server Integration Services (SSIS) is.

Reference answer

SQL Server Integration Services (SSIS) is a platform for building enterprise-level data integration and data transformations solutions. You can use SSIS to solve complex business problems by copying or downloading files, loading data warehouses, cleansing and mining data, and managing SQL Server objects and data.

54

How data storage is different from data representation?

Reference answer

Data storage can be done by using any data tools. Data storage defines how data is stored. On the other hand, data representation is how data is displayed to a user. For example, we can store data in the form of table but represent it in the form of charts!

55

How to find out when was a materialized view refreshed?

Reference answer

Query dba_mviews or dba_mview_analysis or dba_mview_refresh_times SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,'YYYY-MM-DD HH24:MI:SS') from dba_mviews; (or) SQL> select NAME, to_char(LAST_REFRESH,'YYYY-MM-DD HH24:MI:SS') from dba_mview_refresh_times; (or) SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,'YYYY-MM-DD HH24:MI:SS') from dba_mview_analysis;

56

What resources are required for a SQL Server failover cluster?

Reference answer

Resources include a virtual network name for SQL Server, a virtual IP address for SQL Server, IP addresses for the Public Network and Private Network (Heartbeat) for each node, shared drives for SQL Server Data and Log files, Quorum Disk, and MSDTC Disk.

57

What is Data Normalization and Why Is It Important?

Reference answer

Normalization is the process of reducing data redundancy in a table and improving data integrity. Then why do you need it? If there is no normalization in SQL, there will be many problems, such as: - Insert Anomaly: This happens when we cannot insert data into the table without another. - Update Anomaly: This is due to data inconsistency caused by data redundancy and data update. - Delete exception: Occurs when some attributes are lost due to the deletion of other attributes The main use of normalization is to utilize in order to remove anomalies that are caused because of the transitive dependency. Normalization is to minimize the redundancy and remove Insert, Update and Delete Anomaly. It divides larger tables into smaller tables and links them using relationships. Need for normalization : - It eliminates redundant data. - It reduces the chances of data error. - The normalization is important because it allows the database to take up less disk space. - It also helps in increasing the performance. - It improves the data integrity and consistency. A relation is in the third normal form, if there is no transitive dependency for non-prime attributes as well as it is in the second normal form. A relation is in 3NF if at least one of the following conditions holds in every non-trivial function dependency X –> Y. - X is a super key. - Y is a prime attribute (each element of Y is part of some candidate key). In other words A relation that is in First and Second Normal Form and in which no non-primary-key attribute is transitively dependent on the primary key, then it is in Third Normal Form (3NF).

58

Can i run sql tuning advisor in standby database?

Reference answer

No, you cannot run SQL Tuning Advisor on a physical standby database directly.? Why? – A physical standby database is read-only and does not support DML or advisory operations like SQL Tuning Advisor. – SQL Tuning Advisor needs access to execution plans, statistics, and in some cases, it needs to simulate or generate plans, which is not allowed on standby.

59

What is AlwaysOn Availability Groups in SQL Server?

Reference answer

AlwaysOn Availability Groups is a SQL Server feature providing high availability and disaster recovery by replicating user databases. It supports automatic failover and allows readable secondaries for offloading read workloads.

60

What is Update Statistics used for?

Reference answer

Update Statistics is used to force a recalculation of query optimization statistics for a table or indexed view. Query optimization statistics are automatically recomputed, but in some cases, a query may benefit from updating those statistics more frequently. However, re-computing the query statistics causes queries to be recompiled, which may negate performance gains or have a negative impact.

61

What does SELECT DISTINCT do in SQL?

Reference answer

SELECT DISTINCT allows the user to select the distinct values from a table in a database.

62

Are there any methods you use to evaluate whether your database server functions correctly?

Reference answer

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

63

How do you restore a database from an available backup?

Reference answer

The database restoration process involves the following steps:Connect to RMAN using rman target / Start the database in NOMOUNT mode: STARTUP NOMOUNT; Restore the Server Parameter File (SPFILE): RESTORE SPFILE FROM AUTOBACKUP; Restore the control file: RESTORE CONTROLFILE FROM AUTOBACKUP; Mount the database: ALTER DATABASE MOUNT; Restore the datafiles: RESTORE DATABASE; Apply archived logs for recovery: RECOVER DATABASE; Open the database with resetlogs: ALTER DATABASE OPEN RESETLOGS;

64

Imagine that you have to design a database system for an important client. You have a tight deadline, so all the team is working hard. However, you've noticed that as your boss is more worried about the deadline, he has ignored certain aspects. For example, not using SQL facilities to protect data integrity and he has even decided not to conduct regular testings to speed up the process. How would you handle this?

Reference answer

Ideally, your team doesn't have this type of leader. However, this imaginary leader can be, in fact, a colleague who doesn't care about the quality of a project but cares more about finishing it to get a paycheck. This question can help you understand the priorities a candidate has and how they behave around this environment. Whether they speak up for what's right or simply agree that the boss is the master and commander, and if he says he's right, he is right.

65

How do you set up a database user in SQL Server?

Reference answer

To set up a user in SQL Server, navigate to user mapping and list all databases where the new account should reside before selecting its role from a drop-down box and clicking OK.

66

Can you drop objects from a read-only tablespace?

Reference answer

Yes

67

What is the difference between an Oracle DBA and an Oracle Developer?

Reference answer

An Oracle developer is mainly responsible for developing backend applications. They do data modeling according to business rules. The design tables, create indexes and other types of constraints. They are expected to know SQL and PL/SQL. They develop procedures using this language. However, the Oracle developers are not expected to administer the database software itself. On the other side, an Oracle DBA's main duty is to administer the database which involves tasks like doing maintenance to keep the databases up and running, taking backups, enforcing security policies, etc. DBAs are not primarily assigned to develop code. DBAs are supposed to have a good knowledge of SQL and PL/SQL like a developer as these are also required for administering the database. According to the structure of the organization, DBAs might also be assigned development tasks or at least assist the developers where necessary.

68

What are some performance tips for large exports/imports?

Reference answer

Use PARALLEL for multiple worker threads. - Ensure fast I/O and adequate disk space. - Prefer direct path mode. - Use NOLOGGING where safe to reduce redo. - Disable indexes/constraints during import, then rebuild. - Run during off-peak hours. - Export/import only required data (filters). - Optimize network bandwidth in network mode. - Gather statistics after import.

69

How can you identify if a database server is running properly?

Reference answer

I would look at several factors like CPU usage, memory usage, and query performance. Regular logs and alerts also provide valuable information. If all these are within optimal levels, the server is likely running properly.

70

Application team requested you to delete a database. What will you do?

Reference answer

These kind of requests must be checked with database architect. If we still have to do so, we can stop the listener for 1 week, Next shutdown the database and take DB cold backup. If application team does not come back after 1 month, then we can drop database.

71

What do the INSERT, DROP, and UPDATE functions do in a database?

Reference answer

INSERT: It submits data into a database as a new row through a form. Forms take multiple forms or an HTML form. When you click the submit button, it will trigger the built-in form reaction to scan the form of particular fields. This way, it makes sure the required forms are entered correctly. DROP: It removes a table from a database server or a database. You must know that it's a dangerous command and must be used only in situations when necessary. Unless you have a backup, you cannot come back from this. UPDATE: You can modify the values where they meet the criteria using UPDATE. You can either alter rows or a subset using this condition.

72

What is the significance of the CHECK constraint?

Reference answer

The CHECK constraint ensures data integrity by limiting the values allowed in a column. It specifies a condition that must be true for data to be inserted or updated, e.g., age must be > 0.

73

What factors do you consider when planning storage for a database?

Reference answer

Factors include data volume, growth rate, performance requirements, redundancy, and cost. I plan by estimating future storage needs, selecting appropriate storage technologies like SSDs or HDDs, and configuring RAID for fault tolerance.

74

How do you perform DB patching? step by step

Reference answer

Pre-checks: Verify existing patch level, active sessions, and take a backup. Shutdown Database & Listener: Ensure services are stopped before patching. Apply the Patch: Use OPatch or OPatchAuto in RAC. Post-checks: Start the database, run Datapatch, and validate system health. Monitor for Issues: Check logs and verify performance after patching

75

How do you verify that standby is in sync with primary?

Reference answer

Run these queries: -- On Primary SELECT thread#, MAX(sequence#) FROM v$archived_log; -- On Standby SELECT thread#, MAX(sequence#) FROM v$archived_log WHERE applied = 'YES';

76

What are the benefits of implementing two-factor authentication in a database environment?

Reference answer

Implementing two-factor authentication (2FA) in a database context adds a strong layer of protection, functioning as a virtual bouncer for sensitive data. Consider it a double lock for your most critical information. Here are a few human-friendly benefits: - Double Lock, Double Security: Just as opening a high-security vault takes both a key and a fingerprint, two-factor authentication requires two distinct forms of identity. This implies that even if someone learns your password, they'll still need the second piece of the puzzle. - Guarding Against Stolen Passwords: Passwords can leak or be stolen. Even if your password is hacked, 2FA adds an extra layer of security (such as a temporary code texted to your phone) to prevent an intruder from gaining access. - Remote Access Protection: In a world where remote work is becoming more frequent, 2FA serves as a virtual bodyguard for your data, making it more difficult for unauthorised users to penetrate the system, even if they are not physically there. - Preventing Unauthorized Access: 2FA provides an additional degree of security against brute force attacks and other malicious efforts to acquire unauthorised access. It's like having a gatekeeper who requires both a key and a secret handshake.

77

Tell me about a time when you had to resolve a critical database outage under pressure.

Reference answer

Situation: Our main e-commerce database went down during Black Friday, affecting all online sales. Task: I needed to identify the root cause and restore service as quickly as possible while keeping stakeholders informed. Action: I immediately activated our incident response procedure, started investigating log files, and discovered a corrupted index was causing the database engine to crash. I coordinated with the development team to implement a temporary workaround while rebuilding the index. Result: We restored service in 47 minutes, well within our 1-hour SLA, and prevented an estimated $200,000 in lost sales.

78

Describe your experience with databases.

Reference answer

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

79

How to multiplex redo log files?

Reference answer

You can add a redo log file member to an existing group using: ALTER DATABASE ADD LOGFILE MEMBER 'new_location' TO GROUP ;

80

How would you secure sensitive data stored in SQL Server?

Reference answer

I use Transparent Data Encryption (TDE) for encrypting data at rest. For specific columns like credit card numbers, I apply Always Encrypted. I also restrict access through role-based permissions and audit database activities regularly.

81

What is Data Guard, and how does it work?

Reference answer

Oracle Data Guard is a disaster recovery and high availability solution. It maintains a standby database that remains synchronized with the primary database using redo apply or SQL apply.

82

What happens in open stage?

Reference answer

The contents of control file is validated against physical files. Oracle will physically check data files and redo log files on disk. The data file headers and redo log files are matched with the SCN number in control file. Once validation is done, database will be open.

83

What is Automatic Seeding in Always on availability group?

Reference answer

Automatic seeding is a feature launched in SQL Server 2016 to initialize the secondary replica. Earlier we have only one option to initialize secondary replica that is using backup, copy and restore database operation. We use this feature while creating the always on availability group. Although Microsoft has given this feature, but it is not suitable for very big databases or if you have multiple databases on your SQL Server instance because this feature is a single-threaded process that affects performance and can take long hours to initialize your secondary replica.

84

What are the mandatory datafiles to create a database in Oracle 11g?

Reference answer

SYSTEM, SYSAUX, UNDO

85

How would you create a new login and user in SQL Server?

Reference answer

First, create a login at the server level using CREATE LOGIN. Then map it to a database user using CREATE USER. Assign roles or permissions as needed.

86

What is the purpose of the NOLOCK hint?

Reference answer

The NOLOCK hint allows SQL Server to ignore locks, providing a way to read data without being blocked by other transactions. It's used to increase performance in scenarios where real-time accuracy of data is not critical.

87

What happens if Central or Local Inventory is deleted?

Reference answer

Central Inventory Loss: Oracle installations on the server become difficult to manage, patch, or upgrade. Local Inventory Loss: Patching operations for that Oracle home may fail, but the database may still function.

88

How to find opatch Version?

Reference answer

Opatch is utility to apply database patch, In order to find opatch version execute “$ORACLE_HOME/OPatch/opatch version”

89

What type of databases do you have experience with?

Reference answer

You probably covered this on your resume, but the interviewer will likely want to talk through this early in your interview. It's a great opener because it allows you to discuss your skills as they relate to the position you're interviewing for and any projects you've worked on in other jobs or freelance work. When you're narrowing down which databases to discuss, you could look back at the job description to find the ones that are most relevant to this job. Prioritize talking about the ones listed on the job description, and then, talk about others you have experience with as well.

90

What is the difference between Differential and Cumulative Incremental Backups?

Reference answer

Differential Incremental Backup: Backs up only the blocks that have changed since the last incremental backup (level 1 or level 0). Backup size is smaller, but restore requires multiple incremental backups. Cumulative Incremental Backup: Backs up all blocks changed since the last level 0 (full) backup. Backup size is larger, but restoration is faster because only one incremental backup is required.

91

If a user is firing the update query in db how the process happens?

Reference answer

Architecture Components The following architecture components are involved in the update query process: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 query plan, performing the update operation. 4. Lock Manager: Manages concurrency control, acquiring locks on rows. 5. Transaction Manager: Manages the transaction, ensuring atomicity and consistency. 6. Log Manager: Logs the update operation, ensuring recoverability. 7. Recovery Manager: Ensures that the update is recoverable in case of a failure. 8. Data Storage: Stores the updated data.

92

Which method would you use to troubleshoot a problem with a database?

Reference answer

Ticket checking and proactive monitoring are two methods your applicants may mention when responding to this DBA interview question. Some other steps your applicants may use to troubleshoot databases could be to: Gather as much information as possible about the issue; Do testing in different environments; Check the error log on the SQL Server; Check the event log; Make a testing plan; Back up the database.

93

What is the difference between a clustered and non-clustered index in SQL?

Reference answer

A clustered index determines the physical order of the data in the table and can only be applied to one column per table, as the table's data is sorted by that index. When you query a table by a clustered index, the database engine can directly locate the data because the index defines how the data is stored on disk. A non-clustered index, on the other hand, creates a separate structure that stores pointers to the physical data, allowing for multiple non-clustered indexes per table. Non-clustered indexes are helpful for columns frequently used in search queries but do not affect the table's physical storage order. For instance, a clustered index could be applied to a primary key, while non-clustered indexes could be used for columns like email or order date to speed up search operations. Here's a table that illustrates the differences between clustered and non-clustered indexes: | Feature | Clustered index | Non-clustered index | | Definition | Determines the physical order of the data in the table. | Creates a separate structure with pointers to the physical data. | | Number of indexes | Only one clustered index per table (since it defines the physical order). | Multiple non-clustered indexes can exist on a single table. | | Effect on data storage | Directly impacts how the data is stored on disk (sorted). | Does not affect the physical storage of data. | | Use case | Typically applied to the primary key or a column frequently queried for sorted results. | Used for columns frequently queried but not necessarily in sorted order (e.g., search operations on email, date). | | Data access | Faster when querying by the indexed column since the data is physically ordered. | Requires additional lookups (via pointers) to retrieve the actual data. | | Storage structure | Stores both the data and the index together in the same structure. | Stores only the index separately, with pointers to the actual data rows. | | Example | Clustered index on CustomerID. | Non-clustered index on Email. |

94

Which two tasks occur as a database transitions from the mount stage to the open stage?

Reference answer

The online data files & Redo log files are opened.

95

How to become a database administrator?

Reference answer

You must have a bachelor's degree in information technology or computer science to become a database administrator in at entry-level position. If you have a master's degree, you will be prioritized in complex or experienced job positions. However, having a profound knowledge of databases, how they work, and how to resolve the problems will help you become an efficient database administrator.

96

What is indexing in a database?

Reference answer

Indexing is a technique used to improve the performance of database queries. It involves creating a data structure that allows for faster data retrieval. Indexes are created on columns that are frequently used in search conditions, joins, and sorting operations.

97

How do you troubleshoot performance issues in a database?

Reference answer

I start by identifying the bottleneck using monitoring tools and performance metrics such as CPU usage, memory, disk I/O, and wait statistics. I then analyze slow queries using execution plans, look for missing or unused indexes, check for locking and blocking issues, and review configuration settings. Based on the findings, I take corrective actions such as optimizing queries, adding or rebuilding indexes, adjusting database parameters, or scaling hardware resources.

98

What is normalization and why is it important?

Reference answer

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity by dividing large tables into smaller, related tables and defining relationships between them. It typically involves applying normal forms (1NF, 2NF, 3NF, etc.). Normalization is important because it minimizes data duplication, prevents update anomalies, and ensures consistency, though it may sometimes require denormalization for performance optimization in read-heavy environments.

99

Explain how you would cope with data loss when completing a database migration.

Reference answer

Backups and tests are the starting point for preventing data loss during migration. Administrators who notice that the data fails to translate may troubleshoot by checking the old databases to see whether there are duplicate files. They may then clean up any extra data to make the migration seamless.

100

Do you have experience working without supervision as a DBA?

Reference answer

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

101

What is RMAN Backup and its types?

Reference answer

RMAN (Recovery Manager): Oracle's backup tool. Types: - Full backup - Incremental backup (Level 0 & Level 1) - Image copy - Archive log backup - Control file & SPFILE backup

102

Are there any processes that you use to defend databases from external threats?

Reference answer

Some processes your applicants may use to defend your database are to: Maintain the physical security of the database; Establish an HTTPS proxy server; Deploy protocols for data encryption; Use application and database firewalls; Back up the database regularly.

103

What is the component of an Oracle instance?

Reference answer

The SGA

104

What are SQL Agent jobs and how are they used?

Reference answer

SQL Agent jobs are automated tasks scheduled and managed by SQL Server Agent. They consist of one or more steps, each executing T-SQL scripts, SSIS packages, or other commands, and can be triggered by schedules, alerts, or manual requests. They are used for routine maintenance, backups, and data processing.

105

Describe a time when you had to make a difficult decision or take a calculated risk as a Database Administrator. How did you analyze the situation and reach a resolution?

Reference answer

Look for: Decision-making and risk assessment skills.

106

What is transportable tablespace (and across platforms)?

Reference answer

Transportable tablespaces (TTS) is a feature in Oracle that allows you to move tablespaces between databases, while maintaining the data integrity and consistency. Benefits: 1. Faster data transfer: TTS is faster than traditional data export/import methods. 2. Reduced downtime: TTS minimizes downtime, as the tablespaces are only offline for a short period. 3. Platform independence: TTS allows you to move tablespaces across different platforms (e.g., from Linux to Windows).Process: 1. Make the tablespace read-only: Make the tablespace read-only to ensure data consistency. 2. Export the tablespace metadata: Export the tablespace metadata using the expdp utility. 3. Transport the tablespace files: Transport the tablespace files to the target database. 4. Import the tablespace metadata: Import the tablespace metadata using the impdp utility.Across Platforms: To transport tablespaces across platforms:1. *Use the dbms_tts.transport_set_check procedure*: Verify that the tablespaces can be transported between platforms. 2. Convert the tablespaces to a platform-independent format: Use the dbms_tts.transport_set_convert procedure to convert the tablespaces. 3. Transport the tablespaces: Follow the same process as above.

107

What is Summary.txt and Detail.txt?

Reference answer

File Name | Purpose | Content | |---|---|---| | Summary.txt | Overview of high-level summary | Condensed summary of events, actions, or data | | Detail.txt | Detailed information or granular breakdown | In-depth log or detailed information about specific events/data |

108

In which recovery models the minimally logged operation is possible to occur?

Reference answer

SIMPLE and BULK_LOGGED. (If he mentiones FULL in addition to those two, it is a wrong answer)

109

Which command merges two RMAN recovery catalogs?

Reference answer

IMPORT CATALOG.

110

What are the prechecks before patching activity.

Reference answer

- Full DB backup (RMAN full or level 0) and controlfile backup. Backup ORACLE_HOME (file-level or snapshot). Check space on ORACLE_HOME, ORACLE_BASE, CRS home, and disk groups. OPatch/OPatch Version: Ensure OPatch is compatible with the patch. Inventory health: opatch lsinventory should return clean results.Check DB alert logs and health (no outstanding errors). Check listeners and TNS connectivity. Check running sessions and schedule downtime. Check prerequisites: OS patches, kernel parameters, package versions, Java versions (if required). Check cluster health for RAC/GI ( crsctl stat res -t ,cluvfy ).Disable automatic jobs (NAT/cron/DB maintenance) which may interfere. Check filesystem (NFS) latency and mount options. Check DB components versions (datapatch compatibility). Collect environment details: ORACLE_HOME, Oracle version, PSU version, OPATCH version. Test patch in non-prod (staging environment). Notify stakeholders & plan rollback steps & timeline. Ensure root access to run root scripts and correct OS user.

111

What are Roles?

Reference answer

A collection of privileges grouped for ease of management.

112

Explain the use of Dynamic Data Masking in SQL Server 2022 and its limitations.

Reference answer

Dynamic Data Masking (DDM) in SQL Server 2022 is used to limit sensitive data exposure by masking it to non-privileged users. It is simple to implement and does not affect database operations because the data in the database is not changed; only the output is masked. However, DDM is not a security control but a data obfuscation technique that should be used in conjunction with other security features. Limitations include the possibility for users to infer data through query patterns or mistakes in configuration, which can lead to unintentional data exposure.

113

Describe Oracle Architecture.

Reference answer

Oracle's architecture consists of several components, including memory structures, background processes, and database storage. The key memory components are the System Global Area (SGA), which contains the Shared Pool, Buffer Cache, Redo Log Buffer, and other pools. The Program Global Area (PGA) holds data related to user sessions. Background processes like Log Writer (LGWR), Database Writer (DBWR), System Monitor (SMON), and others ensure the smooth functioning of the database. The database itself consists of data files, control files, redo logs, and archive logs, which together support data storage and recovery.

114

What are different types of indexes in Oracle?

Reference answer

Oracle provides several types of indexes to improve query performance by speeding up data retrieval: • B-tree indexes: The most common type; ideal for high-cardinality columns with many unique values. They organize data in a balanced tree structure for fast searches. • Bitmap indexes: Efficient for columns with low cardinality (few distinct values), such as gender or status. They use bitmaps to represent data and work well in data warehousing. • Function-based indexes: Indexes based on expressions or functions on columns, like indexing UPPER(name) to make case-insensitive searches faster. • Reverse key indexes: Reverse the byte order of keys to reduce contention in insert-heavy environments. • Domain indexes: Custom indexes for specific data types like spatial, text, or XML data. • Clustered indexes: Logical grouping of rows for faster access but less common in Oracle. Each type suits different use cases based on data distribution and query patterns.

115

What do you understand from Cache Fusion?

Reference answer

Cache fusion is the process of transferring data from one instance buffer cache to another at a very high speed within a cluster. Instead of fetching data from physical disk which is a slow process, the data block can be accessed from the cache. For Example, Instance A wants to access a data block, owned by instance B. It will send an access request to instance B and hence can access the same using the other instance B's buffer cache.

116

Explain what network databases are.

Reference answer

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

117

Which is your preferred database to work with?

Reference answer

From Oracle to MySQL to PostgreSQL, there are many databases your applicants may prefer. Are they able to explain why they prefer one over the other? It's also important to assess whether your applicants' knowledge of the databases they quote is sufficient. A great follow-up question is “What do you dislike about your preferred database?”

118

What techniques can be used to identify and resolve performance bottlenecks in databases?

Reference answer

Techniques involve analyzing slow queries through execution plans, indexing optimization, partitioning large tables, tuning database configuration parameters, monitoring resource utilization, and leveraging tools for real-time performance monitoring and diagnostics.

119

What is TKPROF and how can we use it?

Reference answer

TKPROF is a tuning utility provided by Oracle which can convert SQL trace files into a readable format. Once trace file is generated using SQL Trace Utility, the TKPROF tool can be run against trace file and output can be read. It can also generate the execution plan for SQL statements. The executable for TKPROF is located in the ORACLE HOME/bin directory.

120

Find the total AdWords earnings for each business type. Output the business types along with the total earnings.

Reference answer

SELECT business_type, SUM(adwords_earnings) AS earnings FROM google_adwords_earnings GROUP BY business_type;

121

What is SQLOS (SQL Server Operating System)?

Reference answer

SQLOS stands for SQL Server Operating System. It works like a mini operating system for SQL Server operations. It is the lowest layer of the SQL Server database engine, which performs critical internal tasks, like memory management, scheduling threads on the CPU to execute SQL Server transactions, deadlock detection, and IO completion activities.

122

I'm a manager, and you're my Senior DBA. Explain to me why we shouldn't switch to MySQL or Oracle.

Reference answer

A senior DBA should have a basic grasp of the advantages and disadvantages of the major database platforms. They've probably answered this question before, too – if not from a manager, then from a developer who's whining because they think Platform X is better than Platform Y. I also want to see senior DBAs that can clearly explain a very political concept without taking it personally. My ideal DBA knows when his platform isn't the right fit, and has no problem suggesting other ideas.

123

How do you recover a dropped table using RMAN or Flashback?

Reference answer

If Flashback is enabled, it's the fastest method: FLASHBACK TABLE table_name TO BEFORE DROP; If Flashback is not available or table is purged: • Use RMAN to perform Tablespace Point-In-Time Recovery (TSPITR). • It creates a temporary auxiliary database, restores the tablespace there, and copies back the dropped table. Example:RMAN> RECOVER TABLE scott.emp UNTIL TIME "SYSDATE-1" AUXILIARY DESTINATION '/u01/rman_aux'; Alternatively, if the table was dropped and archived logs are available, RMAN TSPITR is your best option when Flashback can't help.

124

Tell me about your process for troubleshooting database problems

Reference answer

Troubleshooting is a regular task for a most DBA, so it's crucial to hire one that is able to do it efficiently. They need to answer by telling you what their process for identifying and addressing the issue is like. You should look for a solid process; if a candidate gives a general answer, this might be a red flag. Try to understand in detail how this process is like and how they use their available resources/tools, and their experience.

125

Can you describe a time when you had to manage a high-pressure situation during a database outage? What was your approach?

Reference answer

During a critical e-commerce sale event, the database went down due to a sudden spike in traffic. My first step was to communicate the issue to the stakeholders and ensure proper monitoring and alerting were in place. I quickly analyzed the logs and identified that a lack of database connections was causing the outage. I increased the connection pool size and implemented load balancing across multiple read replicas to distribute the load more evenly. The database was restored, and I then worked on root cause analysis to prevent future occurrences.

126

Can multiple listeners share the same network interface card?

Reference answer

Yes.

127

What is ODBC?

Reference answer

ODBC is an administrative tool for creating 32-bit and 64-bit database sources that connect directly to SQL Server.

128

What are some changes and enhancements in SQL Server 2005?

Reference answer

A few changes and enhancements in SQL Server 2005 include support for Analysis Services on a Failover Cluster, and high-availability features such as Failover Clustering, Database Mirroring, Log Shipping, and Replication.

129

I made changes to .bash_profile but still variables are not set.

Reference answer

When you make changes to .bash_profile, you must execute it at least once using . .bash_profile

130

Why more archivelogs are generated, when database is begin backup mode?

Reference answer

When a database is put in BEGIN BACKUP mode:1. Redo log generation increases: Oracle generates additional redo logs to ensure data consistency and integrity. 2. Archivelogs are generated more frequently: The increased redo log generation leads to more frequent archiving of redo logs, resulting in a higher number of archivelogs.This is because: – Oracle ensures data consistency: By generating more redo logs, Oracle ensures that all changes are recorded and can be recovered in case of a failure. – Prevents data corruption: The increased archiving of redo logs helps prevent data corruption by ensuring that all changes are properly recorded and stored.When the database is taken out of BEGIN BACKUP mode, the redo log generation and archiving return to normal.

131

How you will find out fragmentation of index?

Reference answer

– AUTO_SPACE_ADVISOR_JOB will run in daily maintenance window and report fragmented Indexes/Tables SQL>ANALYZE INDEX VALIDATE STRUCTURE; This populates the table ‘INDEX_STATS'. It should be noted that this table contains only one row and therefore only one index can be analyzed at a time. An index should be considered for rebuilding under any of the following conditions: * the percentage of deleted rows exceeds 30% of the total, i.e. if del_lf_rows / lf_rows > 0.3. * If the ‘HEIGHT' is greater than 4. * If the number of rows in the index (‘LF_ROWS') is significantly smaller than ‘LF_BLKS' this can indicate a large number of deletes, indicating that the index should be rebuilt.

132

What are the benefits of using a view in Oracle?

Reference answer

The view helps provide security, presentation of data from a different perspective, and store complex queries.

133

What is database sharding, and when would you implement it?

Reference answer

Database sharding is a horizontal partitioning strategy where a large database is split into smaller, more manageable pieces called shards. Each shard is stored on a separate server, allowing for greater scalability and performance in distributed systems. Sharding is typically used when dealing with large datasets, such as for social media platforms or e-commerce websites, where the database needs to handle high transaction volumes and millions of users. For example, a user database might be sharded by user ID so that each shard handles a subset of users, improving query performance and balancing the load across multiple servers.

134

What is SQL?

Reference answer

SQL (Structured Query Language) is a programming language used to create, manage, and query relational databases. It allows users to retrieve, insert, update, and delete data. SQL is widely used in applications across industries because it helps manage structured information easily and reliably.

135

Describe your experience with different database management systems (DBMS) and their specific strengths and weaknesses.

Reference answer

I have experience working with multiple DBMS platforms, including Oracle, Microsoft SQL Server, and MySQL. Oracle offers robust scalability and advanced features for enterprise-level applications, while SQL Server provides excellent integration with other Microsoft technologies. MySQL, on the other hand, is known for its simplicity and flexibility. I understand the strengths and weaknesses of each system and can leverage their specific features to optimize performance, scalability, and reliability based on the specific requirements of the organization.

136

What is Data Encryption?

Reference answer

Data Encryption is a method of preserving data confidentiality by transforming it into ciphertext, which can only be decoded using a unique decryption key produced at the time of the encryption or prior to it. Data encryption converts data into a different form (code) that can only be accessed by people who have a secret key (formally known as a decryption key) or password. Data that has not been encrypted is referred to as plaintext, and data that has been encrypted is referred to as ciphertext. Encryption is one of the most widely used and successful data protection technologies in today's corporate world.

137

What is the purpose of a synonym in Oracle?

Reference answer

A synonym is used to mask the original name and owner of an object and provides public access to an object.

138

What are the different SQL Server versions you have worked on and what administration have you performed?

Reference answer

This is a generic question often asked by interviewers. Explain what different SQL Server versions you have worked on and what kind of administration of those instances has been done by you. Your role and responsibilities in earlier projects would be significant. For example, you might say you have experience working in SQL Server 7, SQL Server 2000, 2005, and 2008. Be honest about the versions you have worked on.

139

What happens if .patch_storage is deleted?

Reference answer

Patch history will be lost, making future patch management difficult. However, the database itself remains unaffected.

140

System Data File Consists of? in oracle

Reference answer

In Oracle, a System Data File is a part of the SYSTEM tablespace and contains critical metadata required for database operation. It consists of: - Data Dictionary – Metadata about database objects such as tables, indexes, users, privileges, and constraints. - System Undo Segments – Used to store undo information for system-related transactions. - Bootstrap Information – Required to initialize the database instance. - Views and Stored Procedures – System-related PL/SQL packages and views. - Rollback Segments (if using Manual Undo Management) – For transaction consistency and rollback operations.

141

What is the difference between a Function and a Stored Procedure in SQL Server?

Reference answer

| Feature | Function | Stored Procedure | |---|---|---| | Returns | Must return a value (scalar or table) | May return nothing, one, or multiple output values | | Use in SELECT | ✅ Yes | ❌ Not allowed | | Can modify data? | ❌ No (read-only) | ✅ Yes (can use INSERT/UPDATE/DELETE) | | Transactions | Cannot start/commit/rollback | ✅ Full transaction support | | Dynamic SQL | ❌ Not supported | ✅ Supported |

142

What are table variables and temporary tables?

Reference answer

Table variables are in-memory, limited to their batch/proc scope, no statistics. Temporary tables (#temp or ##global) are stored in tempdb, can be indexed, have statistics, and have broader scope/persistence.

143

What is RMAN active database duplication?

Reference answer

It is a method of creating a duplicate database by copying data directly from the primary database while it remains online.

144

What is a bitmap index and where is it used?

Reference answer

A bitmap index uses bitmaps (bit arrays) to represent the presence or absence of a value in rows, making it highly space efficient. Use cases: • Ideal for columns with low cardinality (few distinct values), like gender, marital status, or categories. • Common in data warehouse environments where queries aggregate large data sets. • Not suitable for OLTP systems with frequent updates, as bitmap indexes can cause locking issues. Bitmap indexes speed up complex ad hoc queries involving AND, OR, NOT operations on multiple columns.

145

How do you create a standby clone for reporting?

Reference answer

A standby clone is a physical standby database used mainly for reporting or testing without impacting the primary.Steps to create a standby clone for reporting: • Set up a physical standby database using RMAN or Data Guard. • Open the standby database in read-only mode or use Active Data Guard for real-time query capability. • Ensure the standby database continuously applies redo logs to stay synchronized. • Configure reporting tools or applications to connect to the standby clone. • Schedule regular monitoring to ensure data freshness and apply lag is minimal. • Use standby cloning to offload heavy reporting queries from primary, reducing load and risk. Standby clones provide high availability with reporting flexibility.

146

What Is The Distinction Between Raid 5 And Raid 10? Which Is the Better Option for Oracle?

Reference answer

- Striping with an additional drive for parity is what RAID 5 is all about. If we damage a disk, we may rebuild it from the parity disk. - RAID 10 mirror pairs of drives before striping over those mirrors. - RAID 5 was developed at a time when drives were costly. Its goal was to deliver RAID on a budget. If a disk stops working, the IO module will function VERY slowly while it is being rebuilt. Furthermore, with all of the increased weight of the rebuild, your likelihood of failure increases considerably during this period. RAID 5 is sluggish for everything except reading even when it is running properly. Given that, plus the fact that databases (particularly Oracle's redo logs) continue to suffer write activity at all times, we should avoid RAID5 in all but the odd database that has primarily read activity. Redo logs should not be placed on RAID5. - RAID10 is just all-around awesome. If you lose a single disk in a set of ten, for example, you might lose any of the remaining eight disks and be OK. Furthermore, because you're only creating a mirror duplicate, rebuilding has no influence on productivity. Finally, RAID10 performs admirably in all sorts of databases.

147

What process will get data from datafiles to DB cache?

Reference answer

Server process

148

List types of Oracle objects

Reference answer

Types of Oracle Objects:Table – Stores structured data in rows and columns. Index – Improves query performance (B-Tree, Bitmap, etc.). Cluster Table – Stores related tables together to improve performance. IOT (Index-Organized Table) – Stores table data in a B-Tree index structure. Function – A stored PL/SQL program that returns a value. Procedure – A stored PL/SQL program that does not return a value. Package – A collection of related procedures and functions. Trigger – Executes automatically on specific database events (INSERT, UPDATE, DELETE). Sequence – Generates unique numbers (often used for primary keys). Synonym – An alias for database objects (used to simplify access). View – A virtual table based on a query. Materialized View – A stored query result for fast retrieval. Database Link – Allows access to remote databases. Tablespace – A logical storage unit that contains datafiles. Directory – Defines file system paths accessible from Oracle.

149

Explain to me the process how you keep our Corporate database secured?

Reference answer

Securing a corporate database involves multiple layers: 1) Implementing strong authentication mechanisms, such as complex passwords or multi-factor authentication. 2) Applying the principle of least privilege by granting only necessary permissions to users and roles. 3) Encrypting data at rest and in transit using technologies like Transparent Data Encryption (TDE) or SSL/TLS. 4) Regularly auditing access logs and monitoring for suspicious activity. 5) Keeping the database software patched and up to date. 6) Using firewalls and network segmentation to restrict access to the database server.

150

What is a primary key?

Reference answer

A primary key is a column or a set of columns that uniquely identifies a row in a table. It ensures that each row is distinct and can be used to reference the row from other tables. Primary keys are essential for maintaining data integrity and enabling efficient querying.

151

How you will find out fragmentation of index?

Reference answer

– AUTO_SPACE_ADVISOR_JOB will run in daily maintenance window and report fragmented Indexes/Tables SQL>ANALYZE INDEX VALIDATE STRUCTURE; This populates the table ‘INDEX_STATS'. It should be noted that this table contains only one row and therefore only one index can be analyzed at a time. An index should be considered for rebuilding under any of the following conditions: * the percentage of deleted rows exceeds 30% of the total, i.e. if del_lf_rows / lf_rows > 0.3. * If the ‘HEIGHT' is greater than 4. * If the number of rows in the index (‘LF_ROWS') is significantly smaller than ‘LF_BLKS' this can indicate a large number of deletes, indicating that the index should be rebuilt.

152

What kind of things you check in AWR reports?

Reference answer

When analyzing AWR (Automatic Workload Repository) reports in Oracle, I focus on the following key areas to diagnose performance issues: 1. Report Summary (Snapshot Information) 2. Top Timed Events 3. Load Profile 4. Instance Efficiency Percentage 5. Top SQL Queries (SQL Statistics) 6. Wait Events (Wait Classes) 7. System Statistics 8. Segment Statistics 9. I/O Statistics 10. RAC Statistics (For RAC Environments)

153

Can I perform Flashback Database using SQL*Plus or RMAN?

Reference answer

Yes, Flashback Database can be performed using both the SQL*Plus command prompt and the RMAN command prompt.

154

What are the types of apply services in Data Guard?

Reference answer

Redo Apply: Applies redo after the standby redo log is archived. Real-Time Apply: Applies redo as it's received (from redo log buffer or file), without waiting for archiving.

155

Explain which method you use to learn or discover more about new applications.

Reference answer

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

156

What is database replication, and when would you use it?

Reference answer

Database replication involves copying and maintaining database objects across multiple servers to ensure data redundancy and high availability. It can be synchronous or asynchronous. - Synchronous replication ensures that changes are reflected in real time across servers. - Asynchronous replication updates replicas with a slight delay. Replication is particularly useful in scenarios where uptime is critical, such as for e-commerce platforms, where users expect the database to always be available, even during maintenance or hardware failures.

157

What is a function in SQL Server and give an example?

Reference answer

A function is a routine that accepts parameters, performs actions, and returns a single value or a table. Example: `SELECT GETDATE()` returns the current server date and time.

158

How do you maintain a positive and proactive attitude in your work as a Database Administrator, especially during challenging or demanding situations?

Reference answer

Maintaining a positive and proactive attitude is crucial in my role as a Database Administrator. During challenging situations, I focus on the solution rather than dwelling on the problem. I remain calm, assess the situation objectively, and collaborate with the team to develop an action plan. I believe in proactive communication, keeping stakeholders informed about progress, challenges, and potential solutions. I also actively seek opportunities for process improvements and efficiency gains. By maintaining a positive mindset and proactive approach, I can effectively navigate challenging situations and contribute to a supportive and productive work environment.

159

What is a Snapshot Control File?

Reference answer

A Snapshot Control File is a temporary copy of the control file created at the beginning of an RMAN backup. It ensures a consistent point-in-time view of the control file while the backup is running. If a tablespace or file is added after the backup starts, it will not be included in that backup.

160

Can you discuss a challenging database-related problem you encountered in the past and how you resolved it?

Reference answer

Look for: Problem-solving approach and resolution skills.

161

What are materialized views in Oracle?

Reference answer

Materialized views are objects that have reduced sets of information that have been summarized, grouped, or aggregated from base tables. They are typically used in data warehouses or decision support systems.

162

What is the use of standby redo logs?

Reference answer

Standby redo logs (SRLs) receive redo data from the primary database in real-time, enabling real-time apply on the standby database. This minimizes data loss by ensuring redo is written to the standby redo log simultaneously with the primary's online redo log. Without SRLs, archived redo logs are applied only after completion, risking data loss in failover scenarios. SRLs are mandatory for real-time apply and recommended on both primary and standby for faster switchovers.

163

Database Normalization vs Database Optimization

Reference answer

| Factor | Database Normalization | Database Optimization | |---|---|---| | Process | Database Normalization involves breaking up data into smaller, related tables and creating relationships between them. | Database Optimization involves making changes to the physical structure of the database, such as adding indexes, creating partitions, and reorganizing tables and other system parameters. | | Security | Database normalization does not affect database security. | Database optimization can improve database security. | | Data Access | Database normalization does not affect data access. | Database optimization can improve data access. | | Output | Database Normalization results in a more organized and efficient database structure. | Database Optimization results in a faster and more efficient database. |

164

What is the fill factor in SQL Server indexes?

Reference answer

The fill factor tells SQL Server how much free space to leave in index pages when re-indexing. The performance benefit is fewer page splits because there is room for growth built into the index.

165

What is the oracle block, can you explain?

Reference answer

An Oracle block is the smallest unit of storage in an Oracle database, containing data like table and index records. It has a fixed size (e.g., 4 KB, 8 KB) set during database creation. The block is divided into several sections: Header: Stores metadata and transaction info. Row Directory: Contains pointers to rows in the block. Free Space: Empty area for new data. Data Space: Holds actual data.

166

What are recovery models in SQL Server?

Reference answer

Recovery models in SQL Server control how transaction logs are managed and determine the level of data loss tolerance. The three types are: Simple (minimal logging, no point-in-time recovery), Full (complete logging, supports point-in-time recovery), and Bulk-Logged (minimal logging for bulk operations, with full logging for other transactions).

167

What is the purpose of an index in Oracle?

Reference answer

The index is used to increase the performance of retrieval. We can make use of one or more rows in order to make the index. The index can increase the performance of retrieval and slows down the performance of insertion.

168

Suppose there is a database issue, and you don't have any solution. You did everything to find the solution to fix the issue, but you did not get anything. What would be your next step to handle this situation?

Reference answer

This is a non-technical question to evaluate your dedication, positive attitude, flexibility, and readiness to adopt new changes. You should explain your approach, such as escalating to senior team members, consulting documentation or community forums, or engaging vendor support.

169

What is a flat file?

Reference answer

A flat file is a plain text file that doesn't take up much space. It's sometimes referred to as a text database and can be saved as a comma-separated value (CSV) file or a delimited flat file. Because they don't take up much space, they work well for transferring data. Many applications require flat files when importing data.

170

Where to view the roles and privileges assigned to a user?

Reference answer

To view the roles and privileges assigned to a user in Oracle, you can use the following queries: 1️⃣ Check System Privileges Assigned to a User ? This shows system-level privileges like CREATE SESSION ,CREATE TABLE , etc. 2️⃣ Check Object Privileges Assigned to a User ? This displays object-level privileges (e.g., SELECT ,INSERT ,UPDATE ,DELETE on tables, views, etc.). 3️⃣ Check Roles Assigned to a User ? This lists the roles granted to the user. 4️⃣ Check Privileges Included in a Role ? This helps identify what privileges are included in a specific role. 5️⃣ Check All Roles in the Database ? This retrieves all available roles in the database. Alternative for Non-DBA Users If you don't have access to DBA views, you can check your privileges using: -

171

How do you manage database backups?

Reference answer

Database backups are essential for data recovery in case of data loss. SQL Server offers several types of backups, including: Full Backup: Backs up the entire database. Differential Backup: Backs up changes since the last full backup. Transaction Log Backup: Backs up the transaction log, which records all transactions since the last backup. A comprehensive backup strategy should include a combination of these types, scheduled appropriately to meet the organization's recovery point objectives (RPO) and recovery time objectives (RTO).

172

What are AWR and ASH reports?

Reference answer

• AWR (Automatic Workload Repository) collects performance data like wait events, SQLs, sessions, and system stats every hour (default). o It helps compare performance between two points in time. o Use: @$ORACLE_HOME/rdbms/admin/awrrpt.sql • ASH (Active Session History) stores real-time session activity from v$session for active sessions only. o It's like a mini version of AWR with focus on active sessions. Both are great for troubleshooting performance issues. AWR helps with historical patterns; ASH is more session-focused.

173

What are RMAN optimization parameters?

Reference answer

RMAN offers parameters like BACKUP OPTIMIZATION, COMPRESSION, and PARALLELISM to improve backup efficiency.

174

If archive destination is full, what will you do?

Reference answer

We will first try to take backup of archives if possible. If not, we will move some archives to another location OR we can even change the archive destination inside database to a location which has more space.

175

How do you prioritize and manage multiple database projects simultaneously?

Reference answer

When managing multiple database projects, I start by clearly understanding the priorities and deadlines for each project. I collaborate with stakeholders to identify critical tasks and use project management tools like Jira or Trello to organize and track progress. I prioritize tasks based on their impact on the business, potential risks, and dependencies. For instance, a task involving security patches would take precedence over routine maintenance. I also allocate dedicated time slots for each project to ensure steady progress without context switching. Regular communication is key, so I keep stakeholders informed of the progress and any potential delays. I also prepare for unforeseen issues by building buffer time into my schedule. If a high-priority issue arises, such as a database outage, I can quickly pivot to address it while keeping other projects on track.

176

What are Oracle partitions and why use them?

Reference answer

Partitioning divides a large table or index into smaller, manageable pieces called partitions, each stored separately but appearing as one logical object. Benefits: • Improves query performance by pruning irrelevant partitions. • Simplifies maintenance tasks like backup, restore, and data loading on partitions. • Enhances manageability and scalability for very large tables. • Enables parallel processing on partitions. • Supports different storage characteristics per partition. Partitioning can be based on ranges, lists, hashes, or composite methods.

177

What is the Oracle Grid Architecture?

Reference answer

Oracle's grid design combines a huge database server, memory, and connections to create a customized, as per requirement computing resource for business computing demands. The grid computing architecture constantly evaluates the consumption of resources and changes supply accordingly. You might, for example, run several programs on a grid of numerous connected database servers. If monthly reports are pending, the database administrator might automatically supply a dedicated server to that program to manage the growing demands. Grid computing employs advanced workload management, enabling programs to work cooperatively across several servers. As per requirement data processing ability may be added or deleted, and resources within a region can be provided flexibly. Web services enable the rapid integration of apps to establish a business.

178

What is Archive Log?

Reference answer

Archived copy of redo logs. Enables point-in-time recovery.

179

How do you migrate data to a new SQL Server?

Reference answer

Common methods include backup and restore, detaching and attaching database files, or using SSIS packages for transformation. Key steps involve compatibility checks, security setup, and thorough testing post-migration.

180

What are the interpersonal skills you have gained from your previous job as a Database Administrator?

Reference answer

From my previous role as a Database Administrator, I have gained strong communication skills for explaining technical issues to non-technical stakeholders, collaboration skills for working with development teams on database design, and problem-solving skills for resolving conflicts during system outages. I also developed attention to detail through meticulous documentation and empathy when supporting end-users with data access issues.

181

What DMV stands for?

Reference answer

Dynamic Management View

182

Can a tablespace hold objects from different schemes?

Reference answer

Yes

183

What is blocking and how do you fix it?

Reference answer

Blocking occurs when one process holds a lock needed by another. Fixes include identifying the blocker, optimizing blocking queries, adding appropriate indexes, and ensuring transactions are short.

184

What is RMAN and how is it better than user-managed backup?

Reference answer

RMAN (Recovery Manager) is Oracle's built-in tool for performing backup and recovery operations. It automates and simplifies the process of taking backups, validating them, and recovering data. Compared to user-managed backups (using OS commands like cp or SQL*Plus), RMAN offers major advantages: • It tracks backup metadata internally. • Supports incremental backups. • Performs block-level corruption checks. • Handles restore/recovery with fewer manual steps. • Supports backup to disk or tape using Media Management Layer (MML). User-managed backups are prone to human error, lack cataloging, and require more manual effort, whereas RMAN is reliable, consistent, and Oracle-supported.

185

How do you track unauthorized user access?

Reference answer

Tracking unauthorized access involves monitoring and auditing all login attempts and resource access. Steps: • Enable auditing on user logins and privilege usage. • Use Unified Auditing or traditional audit features. • Monitor failed login attempts via DBA_AUDIT_SESSION or UNIFIED_AUDIT_TRAIL. • Set up alerts or automated notifications for suspicious activities. • Use Oracle's Fine-Grained Auditing (FGA) to track access to sensitive data at row or column level. • Regularly review audit logs and correlate with user roles and permissions. Combining audit with security tools ensures early detection of unauthorized access.

186

How to prepare for a DBA interview?

Reference answer

You have to go through the job description first. Look into what the company demands. Then, you have to fit into the shoes and prepare every answer that matches the candidate the company is looking for. You can start preparing by reading the most anticipated database administrator interview questions mentioned above. Later on, you can practice behavioral questions before your known ones to gain confidence and enhance communication skills.

187

What is a view in a database?

Reference answer

A view is a virtual table based on the result set of a SQL query. Views can be used to simplify complex queries, restrict access to specific data, and present data in a user-friendly manner. They do not store data physically but provide a way to access data from one or more tables.

188

What background process will write undo data?

Reference answer

BWR

189

How can you configure an Oracle database to start automatically after a server reboot?

Reference answer

In the default configuration, the Oracle database will not automatically start after the server reboots. You'll have to start it manually after each reboot. You'll usually want it to start automatically. There are two methods to accomplish this:

190

After changing the compatibility parameter, can I still flashback the database?

Reference answer

Yes, you can still perform a Flashback Database after modifying the compatibility parameter. However, changing compatibility might impact certain database features.

191

What are the different types of tablespaces in Oracle?

Reference answer

Tablespaces are logical containers that group datafiles. They help manage space and organize data. System Tablespaces: • SYSTEM: Stores core Oracle metadata like data dictionary tables. • SYSAUX: Stores auxiliary components like AWR, OEM data. User Data Tablespaces: • USERS: Default tablespace for user-created objects like tables and indexes. • You can create custom tablespaces to separate data logically (e.g., SALES_TBS). Temporary Tablespace: • Used for sorting, hashing, and other temp operations during query execution. • TEMP tablespace stores data that doesn't need to persist after a session ends. UNDO Tablespace: • Stores undo records to support rollback, flashback, and read consistency. • Automatically managed by Oracle. Tablespaces allow Oracle DBAs to manage storage and performance efficiently. Each tablespace consists of one or more datafiles on disk.

192

How to restore 2 node rac database.

Reference answer

Restoring a 2-node RAC depends on failure scenario. General options: - If restoring entire RAC from backups: - Ensure both nodes' Grid Infrastructure (ASM) is up or restore GI first. - Restore ASM metadata and diskgroups if ASM metadata lost (consult Oracle Support). - Restore database from RMAN on ASM: - - For RAC, you may need to restore OCR and voting disks for GI first. - Restore SPFILE for RAC nodes and configure cluster resources. - If one node fails (node crash): - Bring the failed node back via OS-level restore or rebuild node OS, re-install Grid and Oracle home, join cluster. - Start CRS and database instances on that node. - - If datafiles lost: - Use RMAN to restore to ASM diskgroups. - Consider using duplicate target database ... to rebuild standby as primary then recreate RAC. - - If you have standby: use Data Guard to failover and then rebuild RAC from standby or duplicate back. - Volume-snapshot restore: restore ASM diskgroup volumes from snapshot. - General steps: - Restore GI (OCR/voting) -> Start cluster -> Restore ASM diskgroups -> Restore database -> Register instances on both nodes -> Start services. - Because RAC involves GI / OCR / Voting / ASM, registry steps must be precise. Always follow documented RAC recovery procedures; test in non-prod.

193

A query is executing and temp tablespace is full. You added 20GB but again temp is full. What will you do next?

Reference answer

We need to check the query and if possible tune the query. We can even speak with application team and allocate more temp space and reclaim space once their activity is done.

194

How do you configure and use Oracle Enterprise Manager (OEM)?

Reference answer

OEM is Oracle's web-based tool for database monitoring and management. To configure: • Install OEM agent on the target database server. • Register the database with the OEM repository. • Configure monitoring metrics and thresholds for alerts. • Set up credentials for agent to connect to the database. Use OEM to: • Monitor performance, sessions, wait events. • View alert logs and diagnostics. • Manage backups, jobs, and patches. • Set up automated alerts and notifications. • Generate performance reports. OEM simplifies DBA tasks and provides a centralized management console.

195

Suppose if the plan is also same then how you will resolve that issue?

Reference answer

If it's resource contention, optimize system usage. If it's index fragmentation, rebuild indexes. If it's wait events, resolve blocking or storage bottlenecks. If it's bad statistics, regenerate them properly

196

What is Block Change Tracking (BCT) in RMAN, and why is it needed?

Reference answer

BCT is a feature that tracks modified database blocks, making incremental backups faster by identifying only changed blocks since the last backup, thus reducing backup time and resource consumption.

197

You are doing Db patching or Grid Patching?

Reference answer

If You Handle Both DB and Grid Patching:Yes, I handle both Database patching and Grid Infrastructure patching in our environment. - For Database patching, I apply Quarterly Patch Set Updates (PSU) or Release Updates (RU) using OPatch or DBMS_ROLLING (for Data Guard setups). - For Grid Infrastructure patching, I use Rolling Patching to minimize downtime, applying patches on one node at a time in an Oracle RAC setup. - I ensure proper pre-patch checks, take backups, and validate post-patch health using opatch lsinventory, srvctl, crsctl, and log verification. If You Only Handle Database Patching:I mainly handle Database patching, where I apply RU/PSU patches using OPatch. Before patching, I perform pre-checks, backup verification, and post-patch validations to ensure smooth deployment. If there are Grid patches required, I coordinate with the team managing Grid Infrastructure. If You Only Handle Grid Patching: I focus on Grid Infrastructure patching, ensuring a smooth rolling upgrade process in an Oracle RAC environment. I patch one node at a time to minimize downtime and ensure cluster stability. I use tools like opatchauto, crsctl, srvctl, and run pre-checks before applying patches.

198

Explain to me the process how you refresh our Development database with our Production database.

Reference answer

The process involves extracting a copy of the production database, ensuring it is consistent, and then restoring it to the development environment. Steps typically include: 1) Taking a full backup of the production database (logical or physical). 2) Transferring the backup to the development server. 3) Restoring the backup to the development database, often with a different name or location to avoid conflicts. 4) Performing any necessary data masking or sanitization to protect sensitive information. 5) Validating the refresh by checking data integrity and running key queries.

199

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

Reference answer

The correct answer to this question is no. That's because running tests in a live production environment can lead to data corruption or a system crash, causing the server to become unstable. This can lead to a very pricy fix, resulting in a significant financial loss for a company. Rather than running tests live, it's better to run them in a staging environment, which should happen before pushing anything out to the live database.

200

How do you ensure data integrity and consistency in a database?

Reference answer

I implement constraints and validation rules at the schema level to enforce data integrity. Additionally, I regularly audit and monitor the database for inconsistencies, using transactions to ensure atomicity and consistency of operations.

DON'T WANT TO MISS A THING?

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

Mock Interview Questions for Database Administrators | 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

Mock Interview Questions for Database Administrators | SPOTO

Earn a certification to make your resume stand out.

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