Top Database Administrator Interview Questions

1

How do you manage database capacity planning?

Reference answer

I monitor database growth trends using automated scripts that track table sizes, index growth, and overall database expansion monthly. I analyze seasonal patterns and business growth projections to forecast storage needs 12-18 months ahead. I also monitor query performance trends and connection pool utilization to predict when we'll need additional CPU or memory resources. In my current role, this proactive approach helped us identify that our main transactional database would hit storage limits in 6 months, allowing us to plan and execute a migration to a larger instance with zero downtime.

2

What are differences between dbms_job and dbms_schedular?

Reference answer

DBMS_JOB: 1. Simple job scheduling: DBMS_JOB provides basic job scheduling capabilities. 2. Limited functionality: It only allows for simple, one-time or recurring jobs. 3. No support for complex schedules: DBMS_JOB does not support complex schedules, such as schedules based on events or windows. 4. No support for job chains: DBMS_JOB does not support job chains, which are series of jobs that run in a specific order.DBMS_SCHEDULER: 1. Advanced job scheduling: DBMS_SCHEDULER provides advanced job scheduling capabilities. 2. Support for complex schedules: It supports complex schedules, such as schedules based on events, windows, or calendars. 3. Support for job chains: DBMS_SCHEDULER supports job chains, which enable you to run multiple jobs in a specific order. 4. Support for windows and resource allocation: It allows you to define windows and allocate resources to jobs. 5. Improved manageability and monitoring: DBMS_SCHEDULER provides better manageability and monitoring capabilities, including support for email notifications and job logging.

3

Where you can see the datafile information?

Reference answer

Run the following query to get datafile details: Using SQL Query (DBA Views) sql -

4

What is the redo log in Oracle and why is it important?

Reference answer

The most crucial structure for recovery operations is the redo log, which consists of two or more pre-allocated files that store all changes made to the database as they occur. Every instance of an Oracle Database has an associated redo log to protect the database in case of an instance failure.

5

How does indexing improve query performance?

Reference answer

Indexing creates data structures that allow faster retrieval of records. By using indexes on columns frequently involved in search queries, the database engine can reduce the amount of data it scans, speeding up query execution.

6

Is there any rule or so that you should define so that alert will trigger for taht particular DB?

Reference answer

Yes, in OEM, I define an Incident Rule Set and associate it with a specific database target. Within this rule, I configure metric alerts for key parameters like availability, performance, and storage. When a threshold is crossed, the rule triggers an incident and sends notifications via email or ServiceNow integration.

7

How do you define and manage relationships in a database?

Reference answer

Relationships are defined using foreign keys to link tables, such as one-to-one, one-to-many, or many-to-many. I manage them by ensuring referential integrity, using indexes for performance, and normalizing data to reduce redundancy.

8

How do you ensure data integrity in a database?

Reference answer

Ensuring data integrity is crucial to maintaining the accuracy and consistency of data. Discuss the various techniques and mechanisms, such as constraints and triggers, to maintain data integrity, and how you've used them in your projects. Data integrity can be enforced using various mechanisms. Constraints like primary key, foreign key, unique, not null, and check constraints prevent invalid data entry. Triggers can also be set up to maintain data integrity. Regular audits and backups are also vital to ensure data reliability and recoverability.

9

How do you configure an operator's settings in SQL Server?

Reference answer

To create or change the operator's settings in SQL Server, navigate to the management section and click "configure mail" or "manage Database Mail Account and Profile.

10

Brief me about your database environment like the number of instances, databases, biggest database size, etc.?

Reference answer

This is a non-technical question to evaluate your dedication, positive attitude, flexibility, and readiness to adopt new changes. You should prepare details about your database environment, including the number of instances, databases, and the size of the largest database.

11

How many servers are there in your environment?

Reference answer

You can say 70 to 80 servers with Linux, AIX and windows flavors.

12

How do you handle data corruption or unexpected data loss?

Reference answer

In cases of data corruption or unexpected loss, my first priority is to contain the issue and assess its scope. I'd immediately isolate the affected database to prevent further damage. Then, I'd review the backup strategy to determine the most recent healthy backup. My plan would involve restoring from that backup, and if possible, applying transaction logs to minimize data loss. Throughout the process, clear communication with stakeholders is vital, and a thorough root cause analysis would follow to prevent future occurrences.

13

What is a view in Oracle?

Reference answer

The view is a type of virtual table and there is a query attached to every view in order to identify specific rows and columns of the table. Views are read-only as well as read-write.

14

What does DCL stand for in SQL Server?

Reference answer

DCL stands for Data Control Language. The commands are GRANT, DENY, and REVOKE.

15

What is Oracle Flexible Architecture (OFA)?

Reference answer

OFA is a best practice for organizing database files to improve performance and manageability. It distributes files across multiple disks for optimized I/O

16

What is a scope parameter?

Reference answer

There are various parameters that may be changed flexibly while using spfile. That is the most significant advantage of spfile over pfile. Every value that you change when the database is operating can have one of three scope values: spfile, memory, or sometimes both. S cope of the Spfile will alter after the next reboot, storage range will change instantly but rollback after the reboot, and both will alter quickly.

17

What is the difference between OPatch and OPatchAuto?

Reference answer

OPatch is Oracle's manual patching tool used to apply interim patches to Oracle software like the database or Grid Infrastructure. It requires the DBA to manually stop services, apply patches, and start services again.OPatchAuto is an automated patching tool introduced to simplify patching in Oracle RAC and Grid Infrastructure environments. It automates the process of patch application across all cluster nodes with minimal downtime, often supporting rolling patches where nodes are patched one by one without stopping the entire cluster.In short, OPatch is manual, and OPatchAuto automates patching for easier and safer operations.

18

How to take export backup at two locations.

Reference answer

Use Data Pump to write dump to two locations: Option A: Create two directory objects and run two exports (parallel jobs) or one export and copy dump file to second location. Steps: - Run expdp to write to dp1 : - Copy dump to second location (OS level): Alternatively, start two concurrent expdp jobs to generate separate dumps, or use PARALLEL andDUMPFILE with%U for multiple files and place some members in each location (not directly supported), so copying at OS level or usingDBMS_FILE_TRANSFER /scp is common.For RMAN duplicate to two disk locations, use multiple channels to write to different destinations or configure additional backup copies.

19

Why do you want to work for our organization as a Database Administrator?

Reference answer

I have followed your organization for several months now and I have been attracted to your ambitious plans, your desire to grow and improve, and the fact you have a set of workplace values that I can relate to. As soon as I saw this job advertised, I knew I was going to apply. We spend a lot of time at work, and I want that time to be put to good use in an organization where everyone has a positive attitude, where the employer supports its staff to reach their full potential, and where there is an inclusive working environment.

20

What role does documentation play in your work as a Database Administrator?

Reference answer

Documentation is crucial for maintaining database systems and ensuring smooth team collaboration. It helps in troubleshooting issues quickly and serves as a valuable resource for knowledge transfer and compliance audits.

21

When is the "forced" policy option a good option?

Reference answer

Users may be required to maintain secret passwords when creating an SQL Server login account, although Windows authentication allows for such functionality.

22

Is flashback possible after a table is truncated?

Reference answer

No.

23

How to resize the redolog files?

Reference answer

Not possible, we can create a new group with big size and drop the existing one.

24

How would you improve our 15-hour database backup?

Reference answer

A 15-hour backup window suggests significant bottlenecks. I'd start by investigating whether backup compression is enabled and if we're leveraging striped backups across multiple files or network paths to allow parallel I/O. We could also explore offloading backups to a secondary replica if using Always On Availability Groups, or consider storage-level snapshots if the underlying storage supports it. The goal is to reduce the active database workload during the backup and optimize the I/O path.

25

How do you approach performance tuning at the database level?

Reference answer

First, I review queries through execution plans to find inefficient operations. Then I check indexes, update statistics, and optimize query logic if needed. I also look at server performance counters, memory grants, and I/O bottlenecks.

26

What is the difference between the VALIDATE and CROSSCHECK commands?

Reference answer

VALIDATE BACKUPSET: Verifies whether backup files are intact and can be restored. CROSSCHECK BACKUP: Checks if backup files are still available on disk or tape and updates RMAN metadata accordingly.

27

What is database maintenance in SQL Server?

Reference answer

Database maintenance involves routine tasks to ensure database health and performance, such as checking database integrity (DBCC CHECKDB), updating statistics, rebuilding or reorganizing indexes, and performing backups. These tasks are often automated using maintenance plans or SQL Agent jobs.

28

What is the default listener name?

Reference answer

LISTENER.

29

What is database backup and recovery?

Reference answer

Database backup involves creating copies of data to prevent loss in case of failures. Recovery is the process of restoring data from backups. The specific methods depend on the database system but typically include full, incremental, and differential backups.

30

What is a database link in Oracle?

Reference answer

A database link is a schema object in one database to access objects in another database. When you create a database link with a Public clause it is available for access to all the users.

31

Can we have two MRP processes on a DR server?

Reference answer

No, an Oracle Data Guard environment typically allows only one MRP (Managed Recovery Process) running on the standby server at a time. Multiple MRP processes are not supported as each standby database has only one dedicated recovery process responsible for applying archive logs from the primary database. If additional standby databases exist, each will have its own separate MRP process.

32

How does Oracle support transactions?

Reference answer

Oracle supports transactions as defined by the SQL standard. A transaction is a sequence of SQL statements that Oracle treats as a single unit of work. As soon as you connect to the database, a transaction begins. Once the transaction begins, every SQL DML (Data Manipulation Language) statement you issue subsequently becomes a part of this transaction. A transaction ends when you disconnect from the database, or when you issue a COMMIT or ROLLBACK command.

33

What is rolling patching in RAC?

Reference answer

Rolling patching means applying patches to one RAC node at a time while the rest of the cluster keeps running. This approach allows Oracle RAC to stay online and available during patching, providing near-zero downtime for the database users.For example, if you have 3 nodes in a cluster, you patch node 1, reboot it, then patch node 2, and so on, without shutting down the entire RAC cluster.

34

How is RMAN better than the user-managed backup recovery process?

Reference answer

Recovery Manager (RMAN) is an Oracle built-in utility that can automate database backup & recovery processes and administrate backup strategies as well. In user-managed backups, the user needs to perform backup manually. RMAN backup time will be less when compared to user-managed backups as RMAN maintains all the metadata in the Central Repository and can quickly retrieve the same. RMAN does incremental backup rather than taking full file backups which are done by user-managed backups, which again saves time. RMAN creates backup and recovery scripts that can be re-used and scheduled and does not need manual intervention. RMAN can detect corrupted data blocks automatically during the backup process and recover them, whereas it doesn't happen in user-managed backups.

35

What is the difference between schema and user?

Reference answer

Schema is collection of user's objects.

36

What happens when the database encounters a checkpoint?

Reference answer

When a database encounters a checkpoint, SQL Server writes all pending changes from the in-memory buffer cache to disk. This process essentially brings the database to a consistent state on disk and truncates the transaction log. It's crucial because it reduces the amount of time needed for database recovery after a system crash, as fewer transactions need to be replayed, and it helps manage the growth of the transaction log.

37

How you will recover if you lost one/all control file(s)?

Reference answer

Lost one controlfile: a. Shut database b. Copy and rename the controlfile from the existing or mirror controlfile at os level ‘OR' Remove the controlfile location from the pfile c. start the database Lost of all controlfile: using the backup: a. shut the database (abort) b. startup the database in nomount state c. restore the controlfile from the autobackup d. open the database with resetlogs Lost of all controlfile: without using the backup: a. create the controlfile manually with all the datafile locations b. mount the controlfile c. open the database with resetlogs

38

Explain the backup process used in your organization.

Reference answer

A typical backup strategy includes full backups, incremental backups, and archive log backups. RMAN (Recovery Manager) is commonly used to automate and optimize the backup process

39

Denormalization in Databases

Reference answer

Denormalization is a database optimization technique in which we add redundant data to one or more tables. This can help us avoid costly joins in a relational database. Note that denormalization does not mean ‘reversing normalization' or ‘not to normalize'. It is an optimization technique that is applied after normalization.

40

How do we create a text backup of the control file?

Reference answer

ALTER DATABASE BACKUP CONTROLFILE TO TRACE.

41

When is a media recovery required?

Reference answer

If a data file is out of sync with other files.

42

What is a conflict check in patching?

Reference answer

A conflict check ensures that the patch being applied does not interfere with existing patches, dependencies, or software versions.

43

How to restore pfile/spfile without backup.

Reference answer

If spfile/pfile lost and no backup, options: - If spfile lost but pfile exists: Start with pfile: - If both lost but you know parameters: - Create a minimal pfile manually with essential params ( DB_NAME ,CONTROL_FILES ,DB_BLOCK_SIZE , paths). Then startup NOMOUNT: - - Or reconstruct from listener, tnsnames, ORA- files, and environment info. - If using RMAN or Grid: You can use CREATE SPFILE FROM PFILE after starting manually. - If spfile is simply corrupted: If SPFILE binary exists but corrupted, use OS copy from another node, or create new spfile from pfile (or create from memoryCREATE SPFILE ). Always keep a copy of pfile/spfile in backups.

44

What are stored procedures, and how do they improve database performance?

Reference answer

A stored procedure is a precompiled set of SQL statements that can be executed as a unit. Stored procedures improve performance by reducing the amount of data sent between the database and the application, as multiple queries can be executed with a single call. They also help with security, as users can execute procedures without directly accessing the underlying tables. Stored procedures improve code reusability, as they can be written once and used in multiple applications.

45

We need to build a new database for our employee records. How would you define the system storage requirements?

Reference answer

Ideally, the candidate will start answering this question by understanding what your current database situation looks like. Once having a grasp of this, they can start proposing and defining the system storage requirements. There's not a right or wrong answer. It depends on your company, but more importantly, what you're looking for with this question is to learn how a candidate, if this were a real situation, will respond to your demands and what database solutions they propose based on their knowledge and experience.

46

What tools can be used to start up an Oracle database?

Reference answer

You can start up a database with three tools.

47

What is the effect of enabling BLOCK CHANGE TRACKING?

Reference answer

The CTWR background process is invoked.

48

Datapatch has been failed, what are the reasons.

Reference answer

datapatch applies SQL changes (PSU/RU bundles) to the database. Reasons for failure:- Wrong SQL user permissions: SYS must be used; SYSDBA privileges required. - Mismatched OPatch/Oracle Home: binaries and SQL patch mismatch. - Pending patches not applied: or partial application prior. - Database not in correct state: must be mounted/open based on patch instructions. - Missing components or invalid objects in database (invalid objects causing SQL to fail). - Insufficient rollback segment or temporary tablespace overflow during patch SQL execution. - Interrupted run due to network/filesystem issues. - Insufficient privileges for sys or pluggable DB context issues (for multitenant, datapatch must be run in CDB$ROOT and PDBs handled correctly). - Version mismatch between database binary and SQL bundle. Action: - Check $ORACLE_HOME/cfgtoollogs/sqlpatch andsqlpatch logs in DB. - Re-run datapatch -verbose . - If partially applied, check registry$history views or$ORACLE_HOME/OPatch logs to see applied SQL. Restore DB from backup if necessary or apply missing SQL manually with caution.

49

What is a public synonym in Oracle?

Reference answer

A public synonym does not belong to any schema. In other words, when any database user can access it, it is called a public synonym.

50

What is the difference between a logical and physical backup?

Reference answer

Physical backup refers to copying the actual binary files of the database—datafiles, control files, and redo logs. Tools like RMAN are used for this. These backups are fast, complete, and ideal for full disaster recovery. Logical backup, on the other hand, involves exporting individual database objects (like tables, schemas, or users) using tools like: • Data Pump Export (expdp) • Original Export (exp) Use Cases: • Physical backups are used for complete recovery and are a must-have in production. • Logical backups are useful for migrations, data moves, or restoring specific objects. Physical backups can be hot (online) or cold (offline). Logical backups are usually done online. Best practice: use both types. Use physical backups for disaster recovery, and logical for migrations or selective restores.

51

What is the difference between backup and restore in SQL Server?

Reference answer

Backup is the process of creating a copy of a database to protect against data loss, while restore is the process of recovering a database from a backup. Backups can be full, differential, or transaction log backups, and restore operations can be used to recover a database to a specific point in time.

52

Can you share an example of a time when you had to explain complex database concepts or technical information to non-technical stakeholders or clients? How did you ensure clear communication and understanding?

Reference answer

Look for: Communication skills and ability to simplify technical concepts.

53

How many standby redo logs are needed, and what should their size be?

Reference answer

Create one additional standby redo log group per thread (e.g., 5 SRLs for 4 primary redo log groups). Their size should match the primary redo logs (e.g., 200MB if primary logs are 200MB). The extra group acts as a buffer during high DML activity. Oracle advises against multiplexing SRLs to avoid I/O overhead.

54

Dropping redo log groups while the database is up and running?

Reference answer

Yes, you can drop a redo log group, but it must be inactive.

55

When we talk about non-clustered index, how many KEY columns can it have and how many INCLUDED columns?

Reference answer

16 key columns and unlimited include columns (all table columns can be included). Answering 1 is really bad. Answer between 2-15 is not that bad. 16 is correct.

56

What does the OPTIMAL parameter control?

Reference answer

The length of a rollback segment.

57

What can you look at in SQL Server to find the top 10 worst CPU-guzzling and IO-using queries without using any code?

Reference answer

You can use SQL Server Management Studio (SSMS) built-in reports, such as the 'Performance - Top Queries by Average CPU Time' or 'Performance - Top Queries by Total I/O' reports. Alternatively, you can use the Query Store or Activity Monitor to identify resource-intensive queries without writing custom code.

58

What is a foreign key?

Reference answer

A foreign key is a column in one table referencing the primary key of another table. It enforces referential integrity, maintaining consistency between related tables.

59

Features of Oracle RAC:

Reference answer

High availability - Scalability - Load balancing - Fault tolerance - Shared disk architecture

60

What is the difference between delete and truncate?

Reference answer

Truncate will release the space. Delete won't. Delete can be used to delete some records. Truncate can't. Delete can be rolled back. Delete will generate undo (Delete command will log the data changes in the log file where as the truncate will simply remove the data without it. Hence data removed by Delete command can be rolled back but not the data removed by TRUNCATE). Truncate is a DDL statement whereas DELETE is a DML statement. Truncate is faster than delete.

61

What is the purpose of the FLUSH statement in MySQL?

Reference answer

The FLUSH statement is used to clear or refresh various caches, privileges, and buffers in MySQL.

62

What do you mean by Database Normalization and why is it important?

Reference answer

Normalization technique is a set of rules that are used to organize the relational database to prevent data redundancy and dependency. Once initial database objects are identified, normalization helps in identifying the relationships between schema objects. Different normalization forms are as follows: 1.First Normal Form (1NF) 2. econd Normal Form (2NF) 3. Third Normal Form (3NF) 4. Boyce-Codd Normal Form (BCNF) 5. Fourth Normal Form (4NF) 6. Fifth Normal Form (5NF)

63

What are DBCC statements in SQL Server?

Reference answer

DBCC statements are Database Console Commands and come in four flavors: Maintenance commands (e.g., shrinking a file), Informational commands (e.g., providing information about the procedure cache), Validation commands (e.g., CHECKDB), and Miscellaneous commands (e.g., DBCC HELP, which provides syntax for a given DBCC command).

64

How would you transfer data from MySQL to Microsoft SQL Server?

Reference answer

I would use a combination of tools like SSIS (SQL Server Integration Services) and scripts to transfer data. First, I'd analyze the schema in both databases to identify any compatibility issues. Then, I'd proceed with the data transfer, ensuring data integrity is maintained.

65

How would you improve our data backup process?

Reference answer

You can speed up the backup process by performing backups when fewer people are accessing the database. Also, consider backing up the database to multiple files instead of one large one. This significantly reduces the completion time of a backup. Incremental backups can also reduce backup time. Keep in mind that companies are always looking for ways to save money and optimize time, and shortening the backup process while maintaining efficiency is important to business owners.

66

What is SQL Server?

Reference answer

Microsoft developed SQL Server as a relational database management system (RDBMS) that efficiently manages and stores data for various applications. It is designed to handle a wide range of data management tasks, from small applications to large-scale enterprise databases. SQL Server uses Transact-SQL (T-SQL) as its primary query language, providing a powerful platform for database operations.

67

What is SQL Agent?

Reference answer

SQL Agent is the job scheduling mechanism in SQL Server. Jobs can be scheduled to run at a determined time or when a specific event happens. Also, jobs can be executed on demand. SQL Agent is popularly used to schedule administrative jobs such as backups.

68

What are the two authentication modes in SQL Server?

Reference answer

SQL Server has two authentication modes: Windows Authentication and SQL Server and Windows Authentication mode, also referred to as Mixed Mode.

69

What is the difference between locally and globally partitioned indexes?

Reference answer

• Locally partitioned indexes: Have the same partitioning scheme as the underlying table. Each index partition corresponds to a table partition, making maintenance easier (e.g., dropping a table partition automatically drops the related index partition). Queries that access one table partition only scan the corresponding index partition. • Globally partitioned indexes: Partitioning is independent of the table's partitioning. The index partitions may not align with table partitions. This allows more flexibility but increases complexity in maintaining index and table consistency. Choosing between local and global depends on workload, partition maintenance requirements, and query patterns.

70

A table without a clustered index is called…?

Reference answer

HEAP

71

Can we take an RMAN backup when the database is down?

Reference answer

RMAN backups require the database to be in MOUNT or OPEN state. If the database is completely down, an RMAN backup is not possible. However, a cold backup (OS-level backup) can be taken when the database is shut down.

72

Is it better to set auto-growth in MB or percentage?

Reference answer

Setting auto-growth in multiples of MB is a better option than setting auto growth in percentage.

73

How does the apply process work in Oracle RAC Data Guard?

Reference answer

Pre-12.2, MRP runs on one node. From 12.2, Multi-Instance Redo Apply (MIRA) allows MRP on multiple/all nodes:ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION INSTANCES ALL; -- All instances ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION INSTANCES 2; -- Two instances

74

How can the SQL Server port be updated and configured to listen on a specified dynamic port?

Reference answer

To modify and configure the SQL Server port to listen to a particular dynamic port, launch the SQL Server Configuration Manager and navigate to TCP, IP endpoint configuration. Next, select your port of interest before clicking Properties; in the IPv4 Properties window, select the dynamic port desired and press the OK button.

75

Should we enable CONFIGURE CONTROLFILE AUTOBACKUP? What is its significance? What is the default value?

Reference answer

It should always be ON. When enabled, RMAN automatically backs up the control file and SPFILE after structural changes (e.g., adding a tablespace, datafile, or backup). The default value is OFF, but it is highly recommended to turn it ON for better recoverability.

76

Listener file name extension?

Reference answer

listener.ora – no extension but it's a plain text configuration file.

77

Describe the purpose of the mysqlimport utility in MySQL.

Reference answer

The mysqlimport utility is used to import data from external files into MySQL tables.

78

Can you kill PMON, SMON, or CKPT processes?

Reference answer

No, killing these mandatory background processes will crash the database.

79

IDLE_TIME is set to 15 min but even after 20 min, user session is not getting disconnected. What is the issue?

Reference answer

RESOURCE_LIMIT parameter is not set to TRUE.

80

Can you explain the role and responsibilities of a Database Administrator?

Reference answer

As a Database Administrator, my primary responsibility is to ensure the efficient operation of database systems. This includes tasks such as database installation, configuration, and ongoing maintenance. I am responsible for monitoring performance, identifying and resolving bottlenecks, optimizing queries, and ensuring data integrity and security. Additionally, I implement backup and recovery procedures, handle database migrations, and collaborate with other teams to meet business requirements.

81

How to start Listener?

Reference answer

lsnrctl start

82

What happens if the busy port is defined as zero in the SQL Server Configuration Manager?

Reference answer

If a busy port of zero has been defined in SQL Server Configuration Manager, any dynamic port will automatically become active for use by the server; otherwise, active ports can be selected using SQL Server Configuration Manager.

83

How can the SQL Server configuration manager change the dynamic port?

Reference answer

To change the dynamic port in the SQL Server configuration manager, navigate to the server where SQL Server is installed, double-click TCPIP before clicking IP addresses to add and delete entries, and set static port 9221 as your new one.

84

Can a backup taken from a standby database be used to restore the primary database?

Reference answer

Yes, RMAN allows the restoration and recovery of a primary database using backups from a standby database.

85

How can you find the reason for a failed SQL Agent job?

Reference answer

Inside SSMS, in Object Explorer under SQL Server Agent, look for Job Activity Monitor. The job activity monitor displays the current status of all jobs on the instance. Choose a particular job that failed, right-click, and choose view history from the drop-down menu. The execution history is displayed, and you can choose the execution time to see details about the error.

86

How can we improve SQL*Loader (sqlldr) performance?

Reference answer

By using the direct path load method.

87

What does the ANALYZE command do?

Reference answer

- It is used to get statistics on the optimizer's objects and record these in the database. - It is used to remove information from the database about the information used by the database. - It is used to ensure that the object's structure is correct. - It is used to get the table or cluster's imported and linked rows.

88

Why does LGWR writes come before DBWR writes?

Reference answer

Transaction recordkeeping is more significant than transaction processing in general. Assume we have repeated entries on disk and a power interruption occurs. The transactions can then be recovered by retrieving the redo logs on the disc. This method makes redo logs persistent first, and then temporary blocks are sent to storage.

89

How do you clone a database using RMAN?

Reference answer

Cloning a database with RMAN involves creating an exact copy of a source database to a target server. The main steps are: • Take a backup of the source database, including datafiles, control files, and archived logs. • Copy the backup pieces and parameter files to the target server. • Use RMAN RESTORE and RECOVER commands on the target to recreate the database. • Configure init.ora/spfile on the target to reflect the new environment (file paths, DB name if different). • Open the target database with RESETLOGS if necessary. • Optionally, use RMAN DUPLICATE command for an automated cloning process that handles backup, restore, and recovery in one step. • Ensure network connectivity and access permissions between source and target. • After cloning, update configurations like listeners and TNS names for client connections. RMAN cloning is widely used for creating test, development, or standby environments from production

90

How do you create a new Oracle user?

Reference answer

CREATE USER username IDENTIFIED BY password; GRANT CONNECT, RESOURCE TO username;

91

What exactly is a control file?

Reference answer

- It's a two-part file that contains data in the database's physical form. - You can remove the probability of crashing with the assistance of the control file. - It contains information about the database name, checkpoint, duration, and the presence of multiple log files. - The CONTROL FILE argument, which stores information about the control file, makes it simple to locate it. - To avoid the problem related to a corrupted file, it is preferable to make numerous copies of the control file.

92

What is the definition of normalization? What do the various types of normalization entail?

Reference answer

It is the process of structuring a relational database's fields and tables to reduce complexity and dependence. It guarantees that our data is consistent by saving the memory. - First normal form - Only single (atomic) valued attributes or columns should be used. A column's values must all belong to a certain domain. - Second normal form - It must be written in First Normal. It should also not be partially dependent. - Third normal form - It's in the shape of the Second Normal. It also lacks transitive dependencies. - Boyce and Codd Normal Form - Boyce and Codd is a more advanced variation of the Third Normal Form. This form is used to deal with anomalies that aren't addressed by 3NF. BCNF refers to a 3NF table that does not contain several identical candidate keys.

93

Describe the purpose of the InnoDB storage engine in MySQL.

Reference answer

InnoDB is a transaction-safe storage engine that provides support for foreign keys, row-level locking, and ACID transactions.

94

Do you have stats gathering scheduled or taking on requirement basis?

Reference answer

In our environment, we primarily rely on automatic statistics gathering, which is scheduled by Oracle's AutoTask framework. The GATHER_STATS_JOB runs during the maintenance window and collects optimizer statistics for objects with stale or missing stats. However, in some cases, we also perform manual statistics gathering based on business requirements, especially for: High DML activity tables where stale statistics can impact performance. Partitioned tables where incremental stats collection is beneficial. Large data loads (ETL processes) where we gather stats after bulk inserts for better query optimization. Specific performance tuning cases where histograms or extended statistics are needed.

95

What is Management Studio (SSMS) and how is it used?

Reference answer

SQL Server Management Studio (SSMS) is an integrated environment for managing any SQL infrastructure, from SQL Server to Azure SQL Database. It provides tools to configure, monitor, and administer instances of SQL Server, and it is used for tasks such as writing queries, managing databases, and configuring security settings.

96

Are you familiar with SQL? Name the most useful SQL queries and their roles.

Reference answer

Yes, I am familiar with SQL. The most useful queries include SELECT for data retrieval, INSERT for adding new records, UPDATE for modifying existing data, and DELETE for removing records. These are the CRUD operations essential for database management.

97

There are 100 users connected to database and listener goes down. What will happen?

Reference answer

Nothing will happen to existing users. The problem will be only with new database connections. Listener only comes into pictures when there is a new database connection.

98

What is a Synonym in Oracle?

Reference answer

A synonym in Oracle is an alias for a database object, such as a table, view, sequence, or another synonym. It helps simplify access to objects and provides security by hiding the underlying object's name and schema. View: DBA_SYNONYMS Query: Select synonym_name from dba_synonyms;

99

How to find if your Oracle database is 32 bit or 64 bit?

Reference answer

Execute the command “file $ORACLE_HOME/bin/oracle”, you should see output like /u01/db/bin/oracle: ELF 64-bit MSB executable SPARCV9 Version 1 means you are on 64 bit oracle. If your oracle is 32 bit you should see output like below oracle: ELF 32-bit MSB executable SPARC Version 1

100

How to create new user and grant permission is a single command?

Reference answer

SQL> grant create session to user_a identified by ;

101

What is .patch_storage under $ORACLE_HOME, and why is it needed?

Reference answer

It stores patch history, rollback information, and metadata related to applied patches.

102

Can you explain the limitation of Basic availability groups?

Reference answer

Basic availability groups replace database mirroring features. It provides us to maintain a single secondary replica. Below is its main limitation when we compare it with standard availability groups. - Only 2 replicas can be created. One primary replica and another one is a secondary replica - Only 1 database will work as an availability database just like in database mirroring - Secondary replica cannot be used for reading transactions, database backups, or other database maintenance activities - Basic availability groups cannot be part of distributed availability groups - We cannot upgrade basic availability groups to standard availability groups. We need to drop them and reconfigure standard availability groups from scratch

103

Does FSFO trigger if the primary is shut down with SHUTDOWN IMMEDIATE?

Reference answer

No, FSFO does not trigger for SHUTDOWN IMMEDIATE or SHUTDOWN NORMAL, as these are intentional. It triggers for SHUTDOWN ABORT or unexpected outages.

104

What are server side and database configuration settings like Cost Threshold of Parallelism, Max Degree of Parallelism, and backup settings for performance and integrity?

Reference answer

Cost Threshold of Parallelism controls the threshold at which SQL Server considers parallel query execution, while Max Degree of Parallelism limits the number of processors used in parallel plan execution. Backup settings for performance include compression, striping, and buffer count; for integrity, use checksums and verify backups. Additionally, file groups for LOBs should be used instead of storing them in the Primary file group to improve performance and manageability.

105

Why do we need to rebuild indexes?

Reference answer

Rebuilding indexes is required in order to improve the performance of an application. Due to various INSERT & DELETE operations, the index gets fragmented & unstructured, thereby making the application slow. To reorganize data within these indexes, rebuilding is performed.

106

Explain what a system database and a user database are

Reference answer

A system database is the default database installed when the SQL server is installed. There are 4 system databases: Master, MSDB, TempDB, and Model. A user database, on the other hand, is a database created to store data and start working with the data.

107

It is very difficult to grant and manage common privileges needed by different groups of database users using roles?

Reference answer

No

108

What is a potential reason for a Snapshot too old error message?

Reference answer

An ITL entry in a data block has been reused.

109

In which situation would you use a recovery-only restore for databases?

Reference answer

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

110

What are kernel parameters and why to set them?

Reference answer

They will define the memory allocation from physical memory to Oracle database.

111

How do you check database performance issues?

Reference answer

Use AWR Reports: @$ORACLE_HOME/rdbms/admin/awrrpt.sql Use ASH Reports: @$ORACLE_HOME/rdbms/admin/ashrpt.sql Query performance views: SELECT * FROM v$session_wait; SELECT * FROM v$sqlarea ORDER BY elapsed_time DESC;

112

Describe a challenging database issue you faced and how you resolved it.

Reference answer

I once faced a severe performance issue where a critical database query was taking hours to execute. By analyzing the query execution plan and optimizing the indexes, I reduced the execution time to just a few minutes, significantly improving system performance.

113

Why to run orainstRoot.sh and root.sh scripts at the end of installation?

Reference answer

orainstRoot.sh will change the permissions for oraInventory and root.sh will create oratab fil.

114

What is an operator in SQL Server?

Reference answer

An operator in SQL Server is an administrative user-defined entity that manages tasks such as creating alerts or jobs.

115

What is row migration? How can you detect and fix it?

Reference answer

Row migration happens when a row no longer fits in its original block after an update, forcing Oracle to move it. Detect migrated rows: SELECT TABLE_NAME, CHAIN_CNT FROM DBA_TABLES WHERE TABLE_NAME='TABLE_NAME'; Solution: Set PCT_FREE storage parameter appropriately to reduce row migration. ALTER TABLE EMPLOYEE STORAGE (PCT_FREE 20);

116

Explain the difference between clustered and non-clustered indexes in SQL Server.

Reference answer

A clustered index sorts and stores the data rows in the table or view based on the key values. Each table can have only one clustered index. A non-clustered index, on the other hand, does not alter the way data is stored but creates a separate object within the table that points back to the original row, allowing for more than one non-clustered index per table.

117

When would you use a CTE?

Reference answer

We reach for a CTE when an intermediate result needs to be referenced more than once, or when nesting multiple subqueries would make the logic hard to follow. CTEs let you name each step of a computation separately, which makes complex queries easier to read and debug. Trade-off: CTEs improve readability but are not always materialized by the optimizer. SQL Server may inline the CTE rather than caching the intermediate result, which means a CTE referenced multiple times may be evaluated multiple times. For large, expensive intermediate results that are referenced more than once, a #temp table forces materialization and gives more predictable performance.

118

How does a database administrator approach role-based access control to improve database security?

Reference answer

A database administrator defines roles based on job functions, assigns only necessary permissions to each role, conducts regular reviews of role assignments, and removes unneeded privileges to minimize risk, ensuring compliance with security policies.

119

What are some best practices for migrating on-premises databases to the cloud?

Reference answer

Here are some best practices I follow: - Assessment and planning: I'd start by assessing the existing database environment to understand the schema, data size, and application dependencies. Next, I'd select the appropiate cloud service and instance type based on the workload requirements – it's important to plan for network configuration, security, and compliance considerations. - Data migration strategy: Choose an appropriate data migration strategy such as offline migration using tools like AWS Database Migration Service (DMS) or Azure Database Migration Service for minimal downtime. For large databases, using a phased approach or data pipeline solutions like AWS Snowball for initial bulk data transfer can be effective. - Testing: Conduct thorough testing in a staging environment that mirrors the production setup. Test the data migration process, connectivity, performance, and failover scenarios to identify any issues before the actual migration. - Minimal downtime cutover: Plan the final cutover during a low-usage period. Use database replication to keep the cloud database in sync with the on-premises database until the final cutover to ensure minimal downtime and data loss. - Post-migration validation: After migration, validate data integrity, run performance tests, and monitor the cloud database to ensure everything operates as expected.

120

Which parameters are added on the primary for standby configuration?

Reference answer

LOG_ARCHIVE_CONFIG: Enables archive log destinations. DB_UNIQUE_NAME: Unique name for each database. STANDBY_FILE_MANAGEMENT=AUTO: Automates datafile creation. LOG_ARCHIVE_DEST_2: Standby service name and transport mode. LOG_ARCHIVE_DEST_STATE_2: Enables the destination. DB_FILE_NAME_CONVERT/LOG_FILE_NAME_CONVERT: Maps file paths between primary and standby. FAL_SERVER: Primary's service name for gap resolution.

121

How do you recover a lost control file?

Reference answer

If one of the control files is lost or gets corrupted, we can use another copy of the control file maintained at the OS level. If all the copies of control files are lost or if a user is maintaining only one copy of the control file which gets lost, then a user can 1.Manually create a control file. 2.Restore it from the backup control file using the below command. 3.Restore using RMAN backup by using the below commands.ALTER DATABASE BACKUP CONTROL FILE TO TRACE;

122

What is an operator in SQL Server Management Studio?

Reference answer

A person who manages the SQL Server instance Security measures to protect against unauthorised access Configuration setting for sending email alerts Type of job that runs automatically in SQL Server

123

Can I change the location of Flashback Logs to a custom directory?

Reference answer

No, Flashback logs are automatically managed in the FRA, and their location cannot be customized.

124

What are prerequisites for index creation activity.

Reference answer

Prerequisites and checks before creating index: - Available disk space in tablespace where index will be stored. - Sufficient UNDO and TEMP space for index creation (especially for CREATE INDEX ONLINE ). - Check existing data: uniqueness, nullability, expected cardinality. - Statistics: You may collect new stats after index creation. - Impact on DML: index creation can generate locks or block DML if not using ONLINE option. - System resources: CPU and I/O; consider creating during low peak. - Consider using CREATE INDEX ... NOLOGGING for faster creation (but requires media recovery considerations). - For large tables: use PARALLEL to speed up creation, but ensure resource manager or CPU available. - For partitioned tables: create local index or global index appropriately (local indexes align with partitioning). - Permission: user needs CREATE INDEX privilege or appropriate role and quota on tablespace. - Check foreign key dependencies: creating indexes for FK columns may help performance. - For RAC: consider node-level resource impacts.

125

Can RMAN backups be taken from a standby database?

Reference answer

Yes, RMAN backups can be taken from a standby database, which helps in offloading backup operations from the primary database.

126

How do you check RMAN backup details?

Reference answer

SELECT * FROM v$backup; RMAN> LIST BACKUP SUMMARY;

127

What is the safest way to upgrade SQL Server 2000 to SQL Server 2008?

Reference answer

The safest way to upgrade SQL Server 2000 to SQL Server 2008 is a side-by-side upgrade. You can do this by detaching/attaching the database files or using the backup and restore option. However, the latter is safer than the former. You have to perform the following steps to upgrade the SQL server:

128

You have created a database snapshot on the primary replica. What would happen to the database snapshot if the primary replica failed over to the secondary replica?

Reference answer

Database snapshots will not have any impact. They will be there on previous primary replica and you can use them for your requirements.

129

Tnsping is not working, everything is fine on listener.ora and tnsnames.ora file. What could be the issue?

Reference answer

The default port 1521 or if you specified any other port is not enabled on servers. We can ask network team to enable specific ports.

130

Did you perform grid patching?-I said No

Reference answer

I haven't directly performed grid patching, but I have extensive experience with database patching, including applying Oracle Database Release Updates and SQL Server Cumulative Updates. I understand that grid patching involves updating the clusterware and infrastructure components, and I've collaborated with infrastructure teams during these cycles to ensure compatibility and smooth operations. I'm confident in my ability to learn and adapt to grid patching if required, and I'm always eager to expand my skill set.

131

How do you implement password policies?

Reference answer

Using PROFILES MANAGEMENT.

132

What measurements would you take to protect our databases from external threats?

Reference answer

I would implement multiple layers of security such as firewalls, encryption, and regular audits. I'd also restrict user permissions to limit data exposure.

133

Ask stressful questions.

Reference answer

In one of the earlier questions, you probably found an area of weakness for the DBA's knowledge – heck, nobody's perfect. Go after that weakness. Challenge it, make them uncomfortable, and really push their buttons. This is your one chance to see how they handle stress before the brown stuff actually hits the fan. Ideally, a senior DBA is someone who's had their cage rattled more than once, and they're comfortable under the gun.

134

Explain what T-SQL means.

Reference answer

Skilled applicants should know that T-SQL means Transact-SQL. They should be able to explain that it's an extension that administrators use in Microsoft for Structured Query Language and functions best when they use it with the MS SQL server.

135

Should we patch the Grid Infrastructure (GI) home first or the Database (DB) home first?

Reference answer

Best practice: Patch GI home first, as it provides services to the database. Patching the database home first can lead to compatibility issues.

136

What would be the impact on Log shipping if the SQL Server Agent job is stopped and not running?

Reference answer

Log shipping relies on agent jobs to do all its activities. If SQL Server Agent is stopped, log shipping will not perform these activities of backup, copy, and restore and can become out of sync if the agent will be stopped for a longer duration.

137

Can we configure listener on Oracle client?

Reference answer

No, listener must be configured only on database server which can accept incoming connections.

138

What are the most important skills and qualities needed to be a competent and effective Database Administrator?

Reference answer

The most important skills and qualities needed to be a competent and effective Database Administrator include in-depth knowledge and understanding of company databases, how they operate, query languages and access rights, and the ability to prioritize tasks based on the needs of the department or organization you are working for. You also need exceptional planning and organizing skills, the ability to troubleshoot problems quickly, work closely with departmental technical and non-technical team members, and the understanding of how important it is to keep updated with database developments, laws, and compliance regulations. Critical-thinking and analytical skills are also needed including the ability to explain technical concepts to non-technical individuals. Other essential skills and qualities needed to be a Database Administrator include planning and forecasting skills, the ability to identify the problem using a logical approach, and the understanding of how important it is to keep and maintain accurate records.

139

How do you educate and support users who are unfamiliar with database policies?

Reference answer

To educate and support users unfamiliar with database policies, I focus on clear, jargon-free communication and explaining the 'why' behind the policies, not just the 'what.' I often provide concise guides or conduct brief, focused workshops on topics like data privacy or proper data entry techniques. I also encourage an open-door policy for questions, acting as a resource to help them understand how their actions impact data integrity and security, fostering a collaborative approach to data governance.

140

Write a query to return the total loan balance for each user based on their most recent 'Refinance' submission. The submissions table joins to the loans table using loan_id from submissions and id from loans.

Reference answer

WITH RecentLoans AS (SELECT id AS loan_id, user_id, created_at, MAX(created_at) OVER (PARTITION BY user_id) AS most_recent FROM loans WHERE TYPE = 'Refinance') SELECT ... FROM RecentLoans r JOIN submissions s ON r.loan_id = s.loan_id WHERE r.created_at = r.most_recent;

141

I lost control file under /u01 but I have multiplexed copy in /u02. How do you recover database?

Reference answer

We can simply copy control file from /u02 using cp command and make a copy under /u01 with same name as lost control file. Once we have both control files, we can start the database.

142

What is the difference between Navigational DBMS and Relational DBMS?

Reference answer

In a Navigational DBMS, each value is associated with another and, most of the time, with no other direct way to access the data. Contrastingly, Relational DBMS uses values common to multiple tables to establish a unique key. By doing this, they make sure there are multiple ways to get to the same place.

143

What is incomplete recovery? How does it work? What are the different scenarios?

Reference answer

Incomplete recovery (or point-in-time recovery) is performed when we do not have all the required redo logs for a complete recovery. Scenarios where it is needed: 1.Missing or damaged archived redo logs or online redo logs 2.Intentional rollback to a specific point in time (e.g., recovering from an accidental table truncation)

144

Can you tell what types of APPLY operators exist in SQL Server 2016?

Reference answer

OUTER APPLY, CROSS APPLY

145

Tell me something about yourself and your experience in SQL Server?

Reference answer

This is a non-technical question to evaluate your dedication, positive attitude, flexibility, and readiness to adopt new changes. You should prepare a concise summary of your background, experience, and key achievements in SQL Server.

146

Describe a situation where you had to prioritize multiple urgent database issues simultaneously.

Reference answer

Situation: In one morning, I had three urgent issues: a corrupted backup, a performance problem affecting customer orders, and a security audit requiring immediate attention. Task: I needed to triage these issues based on business impact and urgency. Action: I prioritized the performance issue first since it was actively blocking revenue, delegated the backup investigation to a junior DBA while providing guidance, and scheduled the security audit for later that day. I communicated timelines to all stakeholders upfront. Result: I resolved the performance issue in 30 minutes, the backup was restored by my colleague, and we completed the security audit on schedule. No revenue was lost, and all stakeholders appreciated the clear communication.

147

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

Reference answer

A clustered index determines the physical order of data rows in a table, meaning the table can have only one clustered index. It stores the actual data at the leaf level of the index tree. A non-clustered index, on the other hand, creates a separate structure that contains a pointer (or row locator) to the actual data rows. A table can have multiple non-clustered indexes, and they are useful for queries that do not need to retrieve all columns.

148

How do you monitor database performance and what metrics do you consider most important?

Reference answer

I use monitoring dashboards and performance logs to track key metrics such as query response time, CPU usage, and disk I/O. By regularly analyzing these metrics, I can quickly identify and resolve performance bottlenecks, ensuring optimal database performance.

149

Can we take SYSTEM and SYSAUX tablespace offline?

Reference answer

We can take SYSAUX offline but not SYSTEM.

150

How can we take RMAN backup in parallel?

Reference answer

Yes, we can. Either by mentioning the number of channels or using the parallel parameter.

151

What is the purpose of the GRANT statement in MySQL?

Reference answer

The GRANT statement is used to assign privileges and permissions to users and roles for specific database objects.

152

What is database mirroring?

Reference answer

Database mirroring is a high-availability solution that maintains a copy of a database on a separate server. It involves three roles: the principal server (primary), the mirror server (secondary), and optionally, a witness server. The mirror server maintains a hot standby of the principal database, allowing for quick failover in case of a failure.

153

Describe the purpose of the REPAIR TABLE statement in MySQL.

Reference answer

The REPAIR TABLE statement is used to repair corrupted MyISAM tables.

154

What is the difference between a cluster and a grid?

Reference answer

- Grid architecture can be built by using the famous technology known as clustering. The cluster provides static resources dedicated to certain applications and owners. Grids, which can be made up of numerous clusters, are resource provisioning pools that can be used by a wide range of applications and users. A grid does not presume that all of its servers are performing the same set of apps. Applications can be planned and transferred among grid servers. - Grid computing is, at its most basic level, processing as a service. If we talk in simple terms, it doesn't matter where your data is stored or your request is handled by which machine. You must be able to fetch data or computations and have them provided anytime and as much as you desire. This is similar to how power systems function in that you don't know where the generator is or how the grid is wired, all you have to do is ask for power and it will be provided. The objective is to turn computers into a utility, a commodity, and something that everyone has access to. As a result, the moniker 'The Grid' was coined. This is, of course, a 'client-side' approach to utility computing. - The grid is all about the distribution of the resources, data exchange, and reliability from the 'server-side' or the backend. Resource allocation guarantees that people who require or seek resources receive them and that assets are not unused while demands go unmet. Information sharing ensures that users and applications have access to the information they require, and when they require it. High availability characteristics ensure that all data and computations are always available, similar to how a utility provider ensures that electricity is always available.

155

What is a snapshot standby database?

Reference answer

A snapshot standby is a physical standby temporarily converted to a read-write database for testing or development. It remains in sync with the primary via redo logs but allows read-write operations. After testing, it reverts to a physical standby, discarding changes made in snapshot mode.

156

What happens when you issue a STARTUP UPGRADE command?

Reference answer

This mode is used when upgrading the Oracle database. It restricts normal operations and allows only necessary upgrade scripts to be executed.

157

What is Replication in SQL Server?

Reference answer

Replication is a feature that helps publish database objects and data and copy it to one or more destinations. It is often considered a high-availability option. There are three types: Snapshot, Transactional, and Merge Replication. The type chosen depends on requirements. Snapshot is useful when data does not change frequently, Transactional is useful for transactional tables, and Merge is useful for distributed systems.

158

What are the free buffers in the database buffer cache?

Reference answer

Free Buffers: 1. Unmodified buffers: Free buffers are unmodified buffers that do not contain any changed or dirty data. 2. Available for new data: These buffers are available to store new data blocks read from disk. 3. Not containing any useful data: Free buffers do not contain any useful data and can be overwritten with new data.

159

What are ASM templates and redundancy types?

Reference answer

ASM templates define the attributes and defaults for disk groups and files in ASM, including redundancy and striping policies. Redundancy types in ASM: • EXTERNAL: No mirroring by ASM; storage system handles redundancy. • NORMAL: Two-way mirroring; data is mirrored across two disks for fault tolerance. • HIGH: Three-way mirroring; data is mirrored across three disks for maximum protection. Templates help automate the selection of redundancy and file placement for consistent performance and reliability.

160

Base tables are in encrypted format, how can you check data from it?

Reference answer

There are Data dictionary views and dynamic performance views created on base tables. We can query these views as they have data in human readable format.

161

What Is the Difference Between a Stored Procedure and a Function in SQL Server?

Reference answer

Both are named, reusable blocks of SQL code. The differences determine where and how each can be used. Trade-off: Scalar user-defined functions are a known performance hazard in SQL Server. They execute once per row and prevent query parallelism, which can make queries dramatically slower on large tables. Inline table-valued functions avoid this because the optimizer can inline them and apply pushdown predicates.

162

Explain how user connectivity happens in database.

Reference answer

All new user connections lands on listener. Listener hands over the incoming connections to PMON. The user credentials are then verified with base tables. If details are correct, server process is created on server side by allocating PGA memory.

163

What is ODBC?

Reference answer

Open database connectivity (ODBC) is an API that unifies access to multiple SQL databases. It's interoperable, meaning it enables a single application to communicate with different DSNs and return results.

164

What are differences between dbms_schedular and cron jobs?

Reference answer

DBMS_SCHEDULER: 1. Database-specific: DBMS_SCHEDULER is a built-in Oracle database feature. 2. Runs database jobs: It runs PL/SQL code, stored procedures, and other database-specific tasks. 3. Integrated with database security: DBMS_SCHEDULER uses database authentication and authorization. 4. Supports complex schedules: It supports calendars, windows, and event-based scheduling. 5. Provides detailed logging and monitoring: DBMS_SCHEDULER offers detailed logging and monitoring capabilities.Cron Jobs: 1. Operating system-specific: Cron jobs are a feature of Unix-like operating systems. 2. Runs system-level commands: Cron jobs run system-level commands, scripts, and programs. 3. Uses system authentication: Cron jobs use system authentication and authorization. 4. Supports simple schedules: Cron jobs support simple, time-based scheduling (e.g., daily, weekly). 5. Limited logging and monitoring: Cron jobs have limited logging and monitoring capabilities.

165

I would like to know current user details in database. How to find this information?

Reference answer

You can query V$SESSION view to see the user connection details. To safely terminate user sessions in Oracle, use alter system kill session.

166

Write a SQL query to find the second-highest salary in an "employees" table.

Reference answer

select * from employee where salary=(select Max(salary) from employee);

167

What does the command ALTER DATABASE . . . RENAME DATAFILE do?

Reference answer

It updates the control file.

168

How do you perform a point-in-time recovery using RMAN?

Reference answer

Point-In-Time Recovery (PITR) is used to recover the database to a specific past time before a failure (like accidental data deletion). You can recover: • Entire database (DBPITR) • A single tablespace (TSPITR) Example for DBPITR:SHUTDOWN IMMEDIATE; STARTUP MOUNT; RMAN> RUN { SET UNTIL TIME "TO_DATE('2025-05-30 10:00:00','YYYY-MM-DD HH24:MI:SS')"; RESTORE DATABASE; RECOVER DATABASE; } ALTER DATABASE OPEN RESETLOGS; This is useful during logical corruption or accidental operations.

169

Finding Oracle Homes and instances on a host:

Reference answer

Check /etc/oratab for Oracle homes. Use ps -ef | grep pmon to list running Oracle instances.

170

What is a deadlock and how do you resolve it?

Reference answer

A deadlock occurs when two or more transactions hold locks on resources that each other needs, causing them to wait indefinitely. The database management system typically detects deadlocks and resolves them by terminating one of the transactions (the deadlock victim). To prevent deadlocks, I ensure transactions access resources in a consistent order, keep transactions short, use appropriate isolation levels, and implement retry logic in the application.

171

What measurements would you take to protect our databases from external threats?

Reference answer

Cyberattacks have existed since the beginning of the internet era. Now that the digital environment keeps increasing, protecting your company's and client's data becomes even more relevant. This question attains that urgency and a candidate should be able to give you different strategies to protect your databases from any type of threats. Whether it's by setting an HTTPS server, using real-time database monitoring, or deploying data encryption protocols, the answer can vary depending on a candidate's knowledge and experience.

172

What type of an index can have less rows than it's table?

Reference answer

FILTERED index

173

Can you explain the difference between DELETE, TRUNCATE, and DROP commands in SQL?

Reference answer

These SQL commands are fundamental and have different implications on data and database structure. You should be able to explain the difference and provide examples of when you'd use each command. DELETE command is used to remove specific records from a table and it's a logged operation. TRUNCATE is used to delete all records from a table quickly and it's a minimally logged operation. Meanwhile, DROP command is used to remove an entire table or database. While DELETE and TRUNCATE can be rolled back if used inside a transaction, DROP cannot be rolled back.

174

What is FRA (Fast Recovery Area)?

Reference answer

A unified storage location for: - Backups - Archive logs - Flashback logs - Control files

175

What credentials and skills should you look for in a Database Administrator candidate?

Reference answer

Experienced candidates who are familiar with data privacy and recovery procedures stand out. Look for the one who is dedicated and understand your company's needs and business goals.

176

What is sequence?

Reference answer

A sequence in Oracle is used to generate unique and incremental values, typically for columns that require unique identifiers, such as employee numbers or account IDs. Example Use Case: - Create a table to store employee information: - Create a sequence to generate unique numbers for the emp_id column: - Insert data into the employee table using the sequence to auto-generate the employee ID: -

177

How do you trace the transaction traffic accessing a SQL Server?

Reference answer

To trace transaction traffic, I typically use SQL Server Profiler or, for more performant options, Extended Events. I'd set up a trace, usually filtering it down to the specific events or transactions I'm interested in to minimize overhead. This allows me to capture and analyze the activity, helping with troubleshooting performance issues, auditing, or understanding how applications interact with the database.

178

What is a profile? How do you manage profiles?

Reference answer

A profile is a set of resource limits for users.SELECT USERNAME, PROFILE FROM DBA_USERS; ALTER USER AISHU PROFILE DEFAULT;

179

What is SQLOS?

Reference answer

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

180

What are the characteristics of an Oracle data file?

Reference answer

Each data file can only be associated with only one database and once it is created it can not change its size.

181

What is Redo Log?

Reference answer

Redo logs store all changes made to the database. Essential for recovery.

182

What is the difference between an RMAN target database and an RMAN auxiliary database?

Reference answer

Target Database: The primary database for which backup, restore, and recovery operations are performed. Auxiliary Database: A separate database used in operations like duplication or cloning.

183

How to check if the instance is using SPFILE or PFILE?

Reference answer

ALTER TABLE EMPLOYEE STORAGE (PCT_FREE 20);

184

Explain oracle installation pre-requisite steps.

Reference answer

Create oinstall& dba groups, modify kernel parameters, check disk space for installation, create oracle user and provide permissions on installation location to oracle user.

185

Can we have multiple DBWR processes in database?

Reference answer

We can have between 1 to 36 DBWR in 11g.

186

What is your experience working with database servers?

Reference answer

What you're looking for in this question is to learn more about a candidate's familiarity with database servers, how they have used each system, whether it was Oracle or Microsoft or any other, and the years of experience they've had working with them.

187

Explain a situation where you had to restore a database from backup.

Reference answer

I once had to restore a critical production database after an accidental data deletion. My first step was to immediately assess the damage and stop application access to prevent further inconsistencies. I then identified the most recent full, differential, and transaction log backups. I performed the restore using RESTORE DATABASE, applying the backups in sequence. After restoration, I ran DBCC CHECKDB to verify integrity and had application teams confirm data accuracy before bringing the system back online. This situation truly highlighted the importance of a well-tested backup and recovery plan.

188

How to check the size of a table and database?

Reference answer

Table Size: SELECT SEGMENT_NAME, BYTES/1024/1024 AS SIZE_MB FROM DBA_SEGMENTS WHERE SEGMENT_NAME='TABLE_NAME'; Database Size: SELECT SUM(BYTES)/1024/1024 AS SIZE_MB FROM DBA_SEGMENTS;

189

Sequence of events takes place while starting a Database is?

Reference answer

Instance started, Database mounted & Database opened

190

What are some common concerns with database management?

Reference answer

A common concern for database managers is data safety. This is because one of the top priorities is to ensure there's no loss or corruption of data during migration or during general use. Security is another common concern. Whether the breach is caused by employee negligence or hackers, data leaks can be detrimental to a business's reputation and survival. It's crucial to take preventative measures and have plans to deal with these types of issues.

191

Did you upgrade database? what version to what version you did upgradation?

Reference answer

Yes, I have experience upgrading an Oracle database from 12c to 19c in my previous role. The process included several key steps and considerations to ensure a smooth transition: 1. Pre-Upgrade Information Gathering: The first step I would take is to run Oracle's Pre-Upgrade Information Tool to identify any deprecated features and check for compatibility issues. The tool will highlight any features that are no longer supported in 19c, such as certain initialization parameters or Oracle options.2. Backups: As with any upgrade, taking a full backup is crucial. Oracle's RMAN (Recovery Manager) would be used to create a backup of the entire database and ensure recovery in case something goes wrong. 3. Review of Deprecated Features: Oracle 19c comes with some deprecated features that need to be checked, including changes to Oracle RAC and multitenant architecture. It's important to review and either address or replace these features during the upgrade. 4. Database Upgrade Using DBUA: The Database Upgrade Assistant (DBUA) is Oracle's recommended tool for performing an upgrade. It automates the process, performs checks, and handles the database schema changes. DBUA can also handle patching and required configurations for new features, ensuring a smoother upgrade. 5.Manual Steps After DBUA: After using DBUA, it's important to manually address certain tasks, such as gathering statistics and verifying application compatibility. If necessary, manual intervention may be required for adjusting configurations for performance tuning or handling deprecated parameters.6. Post-Upgrade Validation: Once the upgrade is complete, testing and validation are essential. I would ensure that all applications and queries are functioning as expected and check the performance of the database after the upgrade, focusing on any bottlenecks or regressions. 7. New Features in 19c: After the upgrade, I would explore the new features in 19c, such as improvements in in-memory processing, automatic indexing, pdb enhancements, and optimizations in SQL performance. It would also be a good idea to review Data Guard settings and ensure everything is properly synchronized post-upgrade. 8. Documentation and Cleanup: Finally, I'd ensure that any deprecated initialization parameters are removed, and I would update configuration settings according to the best practices outlined in the Oracle 19c documentation.

192

What is the difference between opatch apply and opatchauto?

Reference answer

opatch apply: Used for manually applying patches to Oracle software. You need to specify the patch location and control the process manually. opatchauto: Automates the patching process, simplifying patch application across multiple components. It's especially useful for Oracle Grid Infrastructure (GI) and database homes.

193

Explain the steps you would take to handle complaints about the slow performance of a database.

Reference answer

Using problem-solving skills is a fundamental method for handling complaints about the performance of a database. Applicants may explain that analyzing tickets is the step one for handling slow databases. They may follow this up by using a memory reconfiguration method and then proceed with a memory upgrade.

194

Can you walk us through your troubleshooting process when a database issue arises?

Reference answer

My process involves identifying the symptom, checking logs, isolating the cause (e.g., query performance, hardware failure), and applying a fix. I prioritize minimizing downtime and document the resolution for future reference.

195

How do you handle database performance issues, such as slow queries or bottlenecks?

Reference answer

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

196

Discuss the role of Artificial Intelligence (AI) in SQL Server 2022.

Reference answer

SQL Server 2022 integrates Artificial Intelligence through its built-in AI capabilities in SQL Server Machine Learning Services and SQL Server Big Data Clusters. These allow users to run Python and R scripts to perform complex analytics directly within the database engine, eliminating the need for data movement. SQL Server also offers integration with Azure Machine Learning, enabling more advanced machine learning models and AI-driven insights directly from SQL Server, enhancing decision-making processes and predictive analytics.

197

What is a Recovery Catalog?

Reference answer

Recovery catalog is a database schema that holds the metadata used by RMAN for restoration and recovery processes. It basically stores information on 1.Datafiles & their backup files. 2.Archived Redo Logs & their backup sets. 3.Stored scripts 4.Incarnation 5.Backup history The catalog gets updated once RMAN takes the backup or switches redo log or changes data file.

198

What is row chaining?

Reference answer

Row chaining, also known as row migration, occurs in Oracle when a row is updated and its new size exceeds the available space in its current block.When a row is updated: 1. Row grows: The row size increases due to the update. 2. No space available: There is not enough space in the current block to accommodate the larger row. 3. Row chaining: Oracle chains the row to a new block, storing the additional data in the new block.Row chaining can lead to: – Performance issues: Increased I/O operations to access chained rows. – Storage inefficiencies: Wasted space due to fragmented rows.

199

What are your best DBA technical skills?

Reference answer

DBA technical skills are fundamental for database administrators, so learning which skills your applicants think are their best will give you a good idea of how prepared they are to take on the open position. Answers will vary, but you need to be on the lookout for applicants who name skills that align with your requirements.

200

How can you monitor and optimize memory usage in MySQL?

Reference answer

Use tools like SHOW VARIABLES and SHOW STATUS to monitor memory usage, and adjust buffer sizes and cache settings for optimization.

DON'T WANT TO MISS A THING?

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

Top Database Administrator Interview Questions | 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

Top Database Administrator Interview Questions | SPOTO

Earn a certification to make your resume stand out.

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