Good Interview Questions to Ask as a DBA Candidate | SPOTO

The tnsnames.ora file is a configuration file that contains network service names mapped to connect descriptors. It helps Oracle clients resolve database service names to their corresponding network locations (host, port, and service name/SID).

Switchover: A planned role reversal where the primary becomes the standby and vice versa, typically for maintenance, with no data loss. Failover: An unplanned transition to a standby when the primary fails, used in disasters. Data loss depends on the protection mode (e.g., none in Maximum Protection, possible in Maximum Performance).

The backup process may fail or produce errors if it attempts to access the original file location.

RMAN uses a database structure called a recovery catalog to hold information from Oracle databases. The catalog is usually kept in a separate database. A recovery catalog adds repetition to the RMAN library contained within every destination database's control file. It acts as a backup metadata store. Even if the destination control file and all duplicates are deleted, the RMAN information remains in the recovery catalog. A recovery catalog adds repetition to the RMAN library contained within every destination database's control file. It acts as a backup metadata store. Even if the destination control file and all duplicates are deleted, the RMAN information remains.

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

SQL Server 2022 enhances its capabilities to manage large volumes of data through features such as Columnstore indexes, which optimize large-scale data warehouse queries, and In-Memory OLTP, which improves performance for high transaction rates. Partitioning large tables and indexes can also manage data more efficiently by dividing them into smaller, more manageable pieces. Data compression features help reduce storage costs while maintaining performance. Additionally, SQL Server 2022 integrates with Big Data clusters, facilitating the management and analysis of large datasets across both structured and unstructured data sources.

To ensure database scalability, I would use a combination of vertical and horizontal scaling strategies, along with optimizing database design and architecture. Here are a few ways I'd ensure scalability: - Vertical scaling: This involves adding more resources, such as CPU, memory, or storage, to the existing database server. While it's the simplest approach, it has its limits since hardware can only be upgraded to a certain extent. I would use vertical scaling as a short-term solution or in scenarios where the database isn't extremely large or doesn't require frequent scaling. - Horizontal scaling (sharding): For larger databases or when dealing with massive datasets, horizontal scaling, or sharding, is more effective. This involves distributing the database across multiple servers or nodes, where each shard holds a subset of the data. It allows the system to handle a higher volume of queries by spreading the load. For instance, in an e-commerce platform with millions of users, I could shard the database by user ID to distribute the load across several servers. - Replication: Replication involves copying data to multiple database servers to distribute the read workload. I would set up master-slave or master-master replication to allow multiple servers to handle read requests, improving read scalability. This method also adds redundancy, which enhances data availability and fault tolerance. - Database indexing and query optimization: Efficient indexing and query optimization can significantly improve performance, making the database more scalable. By analyzing and optimizing slow queries, adding appropriate indexes, and avoiding expensive operations like full table scans, I can reduce the load on the database, which indirectly contributes to scalability. - Caching: Implementing a caching layer, like Redis or Memcached, helps offload frequently accessed data from the database. By storing and retrieving common queries from the cache, I can reduce the load on the database, resulting in faster response times and improved scalability. - Partitioning: Database partitioning involves splitting a large table into smaller, more manageable pieces, improving query performance and making data management more efficient. For example, I might partition a large transactions table by date, so queries that target specific time ranges only scan the relevant partitions, reducing I/O and speeding up response times. A table can help you better remember the difference between vertical and horizontal scaling in database architectures: | Vertical scaling (scale-up) | Horizontal scaling (scale-out) | | Add more resources to a single server (e.g., more CPU, RAM). | Add more servers or nodes to handle the load. | | Limited by the maximum hardware capacity. | Can scale indefinitely by adding more nodes. | | Simpler to implement but not as scalable long-term. | More complex to implement but offers better long-term scalability. | | Example: Upgrading an RDS instance to a higher instance class. | Example: Sharding a database across multiple servers. |

Parsing: SQL statement is checked for syntax and semantics. - Execution Plan: Optimizer generates the best plan. - Buffer Cache: Data is loaded into buffer cache. - Update: Rows are modified in the buffer cache. - Redo Generation: Redo is created for recovery. - Undo Generation: Undo data is written for rollback. - Commit: Changes are written to redo logs and the transaction is marked complete.

Best practices include using least privilege access controls, implementing robust authentication mechanisms, regularly auditing database activities, segregating duties among users, encrypting data both at rest and in transit, and continuously applying security patches and updates.

A point-in-time recovery involves restoring a SQL Server database to a specific point in time. This is achieved by restoring the full backup, the differential backup, and the transaction log backups up to the desired point of time.

Methods include Transparent Data Encryption (TDE) for data at rest, Always Encrypted for specific columns, implementing strict role-based access control, and auditing database activity to track access.

Yes.

LGWR switching from one redo log file to another is known as log switch.

Oracle Wallet is a secure storage container for storing: • Database credentials • SSL certificates • Encryption keys Use cases: • Auto-login for scripts without exposing passwords. • Data encryption at rest or during transmission (TDE or SSL). • Secure database links without hardcoding credentials. Example: Storing DB credentials for backup scripts: mkstore -wrl /u01/app/oracle/wallet -create mkstore -wrl /u01/app/oracle/wallet -createCredential mydb scott tiger Later, apps can use the wallet instead of plaintext passwords. It's part of Oracle's Advanced Security features.

Normalization organizes data into tables to reduce redundancy and dependency. It breaks large tables into smaller ones and links them using relationships.

The interviewer will test your experience by asking a mixture of basic and advanced SQL Server database administrator questions. They will ask about the purpose of the model database server, your experience with the SQL Server DBA, what replication and DCL are, different types of recovery models and their importance, ways to create databases in SQL Server, and more.

INSERTED, DELETED

• Hard Parse: Happens when a SQL statement is new to Oracle and not found in shared pool. It must go through: o Syntax check o Semantic check o Optimization o Plan generation o Loading into memory• Soft Parse: When the same SQL already exists in memory. Oracle skips optimization and reuses the plan. Too many hard parses can cause: • High CPU usage • Latches/locks • Library cache contention Solution: Use bind variables and reduce unnecessary reparsing.

INSERT

This is a non-technical question to evaluate your dedication, positive attitude, flexibility, and readiness to adopt new changes. You should describe a specific issue you encountered, the steps you took to diagnose and resolve it, and the outcome.

View: 1. Virtual table: A view is a virtual table based on a SQL query. 2. No physical storage: Views do not store data physically; they derive data from underlying tables. 3. Real-time data: Views always show real-time data from the underlying tables. 4. Query-based: Views are defined by a SQL query that is executed every time the view is queried.Materialized View: 1. Physical storage: A materialized view stores data physically, just like a table. 2. Periodic refresh: Materialized views are refreshed periodically, either on demand or at scheduled intervals. 3. Stale data: Materialized views can show stale data if not refreshed recently. 4. Query optimization: Materialized views can be used to optimize queries by precomputing and storing results.

The main duty of an Oracle DBA is to keep the Oracle Databases of the organization up and running. This may involve installing and configuring a database from scratch. On a running system, the DBA will be the only privileged person who can shut down and startup the database. The DBA will create new users and manage the privileges of each user. He will take regular backups to ensure that data is safe. In case of a disaster, he will be responsible for restoring the database from backups. He will have to do monitor the space usage and do capacity planning for the database. He will be responsible for enforcing security policies. He will have to monitor database activities. He will have to tune the database so that it works at an acceptable speed. He is expected to follow the latest patches and apply them when applicable.

If central inventory (oraInventory) corrupts, patching may fail. Steps to recover: - Stop patch operations and do not force further changes. - Backup current inventory (oraInventory/oraInst.loc) and relevant Oracle homes. - Identify error in $ORACLE_HOME/oraInventory/logs/ssu/ or$ORACLE_HOME/cv/admin/log or Vendor (OPatch) logs. - Check oraInventory location in /etc/oraInst.loc (Linux) or file specified by environment. - Validate permissions & ownership (inventory directory must be owned by the user who installed Oracle, usually oracle orgrid for GI) and group; Common fix:chown -R oracle:oinstall /u01/oraInventory andchmod -R 775 . - Repair inventory XML/files: If individual inventory XMLs are corrupted, restore from backup. If no backup: - Use opatch lsinventory to see what's missing;opatch often reports corrupt entries. - Recreate missing inventory entries by running runInstaller with-silent -detachHome or useoraInventory commands to re-register homes. - - Rebuild inventory (only if necessary): You can build a new inventory and re-register Oracle homes using ./runInstaller -silent -attachHome ORACLE_HOME= ORACLE_HOME_NAME= . - Use OPatch rollback: If patch applied partially, use opatch rollback -id from ORACLE_HOME to revert. - Validate with opatch lsinventory and ./OPatch/opatch lsinventory until clean. - Test startup of DB & components. - If Grid Infrastructure inventory corrupted: use cluvfy and follow Oracle Support note or reinstall GI components after backing up CRS. Prevention: Always backup oraInventory before patching, run opatch prereq &opatch lsinventory , and ensure OS patches/permissions correct.

Flashback Recovery allows rolling back the database to a previous point without using full restore operations. It requires Flashback Logging to be enabled.

OLTP systems are designed for managing transactional data, focusing on fast query processing, high concurrency, and maintaining data integrity. They typically involve a large number of short, write-heavy transactions, such as insert, update, and delete operations. To optimize an OLTP database, I would use techniques like normalization to reduce data redundancy, implement appropriate indexing to speed up query execution, and use efficient transaction management to handle concurrent access. On the other hand, OLAP systems are optimized for complex queries and data analysis. They are designed to handle large volumes of read-heavy queries that aggregate and summarize data. OLAP databases often use denormalization to improve query performance, as the data is structured in a way that allows for faster retrieval and analysis. For optimizing OLAP databases, I would focus on building and maintaining materialized views, implementing data partitioning to manage large datasets, and using indexing strategies that cater to multi-dimensional queries, like bitmap indexes. A table comparing OLTP and OLAP can clarify the differences between these two types of database systems: | Feature | OLTP | OLAP | | Focus | Transactional processing | Analytical processing | | Query type | Simple, frequent transactions | Complex, long-running queries | | Data size | Small transactions | Large data sets, often historical | | Schema design | Highly normalized | Often denormalized | | Typical use case | E-commerce, banking systems | Data warehouses, reporting systems | | Examples | MySQL, PostgreSQL | Redshift, Snowflake |

Ensuring database security in a multi-user environment requires a rigorous approach. I always implement the principle of least privilege, meaning users only get the minimum access necessary for their role. This involves setting up role-based access control (RBAC), using strong authentication, and encrypting sensitive data both at rest and in transit. Regular auditing of access logs and applying security patches promptly are also crucial to proactively identify and mitigate potential threats.

Tablespaces that record extent allocation in the dictionary are called dictionary managed tablespaces, and tablespaces that record extent allocation in the tablespace header are called locally managed tablespaces.

Replication in SQL Server involves copying and distributing data between databases. Types include snapshot replication (periodic full copies), transactional replication (real-time changes), and merge replication (bidirectional synchronization). Key concepts include publishers, distributors, subscribers, and articles.

Here's a refined version highlighting the key differences between V$ Views (Dynamic Views) and DBA Views (Static Views): Feature V$ Views (Dynamic Performance Views) DBA Views (Static Data Dictionary Views) Nature Dynamic (data comes from memory and changes in real time). Static (data is stored in system tables and persists). Availability Accessible in MOUNT and OPEN states. Available only when the database is in OPEN mode. Data Source Fetched from SGA (System Global Area) and reflects current status. Stored in data dictionary tables inside the SYSTEM tablespace. Persistence Data is lost after a database restart (except logs stored in AWR/Alert logs). Data remains permanently stored in system tables. Usage Used for real-time monitoring, performance tuning, and troubleshooting. Used for database administration, object management, and metadata querying. Examples V$DATABASE ,V$DATAFILE ,V$SESSION ,V$LOG DBA_TABLES ,DBA_USERS ,DBA_DATA_FILES ,DBA_SEGMENTS -

A primary key uniquely identifies each record in a table. It cannot have NULL values. A foreign key links one table to another, maintaining referential integrity between two related tables.

SQL Server has three types of replication: Snapshot, Merge, and Transaction. Snapshot replication creates a point-in-time picture of the data to deliver to subscribers, useful when data changes infrequently or there is a small amount of data. Merge replication uses a snapshot to seed replication and tracks changes on both sides for synchronization when connected, typical in client-server scenarios. Transaction replication begins with a snapshot but tracks changes as transactions, replicating them in near real-time in the same order as they occurred, useful when the subscriber needs every change.

The newly added datafile or tablespace will not be included in the current backup. It will be backed up in subsequent backup runs.

Situation: A developer wanted to denormalize several tables for a new feature to improve query performance. Task: I needed to address my concerns about data integrity while respecting their performance requirements. Action: I proposed a compromise: keeping the normalized structure for transactional operations but creating denormalized views for reporting. I demonstrated both approaches with performance tests and showed that my solution achieved 90% of their performance gains while maintaining data consistency. Result: We implemented my approach, which prevented several data integrity issues that would have occurred with full denormalization while still meeting performance requirements.

This parameter determines how long RMAN retains backup records in the control file before reusing them. If the value is too low, older backup records may be overwritten, affecting recovery operations.

ASM instance background processes (some of them): - SMON — System Monitor for ASM instance. - PMON — Process Monitor. - RBAL — Rebalancer background process (for rebalance requests). - LMD /LMS — Lock Manager Daemon/Server processes (manage IO/metadata locking in cluster). - MMNL — ASM Master Node Monitor. - RSMON — ASM recovery/monitor processes. - ASMB — ASM background processes for remirroring. - ASM also interfaces withASMFD in modern versions. Note: Exact process names can vary with versions. Useps -ef | grep asm_pmon etc.

I'd handle a difference of opinion by first ensuring I fully understand their perspective and reasoning. Then, I'd clearly articulate my own viewpoint, backing it with relevant data or documentation. If we still don't reach a consensus, I'd suggest we consult a third, neutral technical resource, or potentially run a small proof-of-concept to test both approaches. My priority is always to find the best technical solution for the company, while maintaining a respectful and collaborative relationship.

Unless you're a micromanager on top of everyone at the office, looking for someone who can work independently is important. Moreover, when hiring developers who work remotely, they need to work without direct supervision all the time. Learn about their workflow and their working process, if they are organized and have a clear agenda of their priorities, or if they expect their leaders every day to tell them what to do.

There can be multiple database background processes. They are named "DBWn" in the operating system. This process is responsible for writing "dirty" buffers to the disk. When a server process wants to update a data block, it reads the block from disk to buffer cache if the block is not already in the cache and then updates the copy in the cache. The modified database block in the buffer cache is called a "dirty" block.

Securing a SQL Server database involves: Implementing strong authentication and authorization mechanisms. Using SQL Server's built-in encryption features to protect sensitive data. Regularly updating the SQL Server instance to protect against vulnerabilities. Auditing database activities to monitor access and changes. Configuring firewalls and network security measures.

Using env | grep ORA. Using echo command like echo $ORACLE_HOME.

Navigational databases use pointers to navigate through data, while relational databases use tables to define relationships between data. Relational databases are more flexible and easier to query.

SELECT current_scn FROM v$database;

An Active-Active cluster is a failover cluster configured so that both cluster nodes are active at any given point in time. One instance of SQL Server runs on each node. When one node fails, both instances run on only one node until the failed node is brought up, then the instance is failed back to its designated node.

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

Data administrators, and any other IT roles, have to be on top of the new resources that appear on the market. The industry is constantly changing, and a big mistake would be to hire a DBA that is still working on old database versions. Make sure they know about all the changes whether because they are part of communities, Facebook groups, or simply because they keep informed all the time.

Restore a backup and apply binary logs using the mysqlbinlog utility to recover the database to a specific point in time.

Yes, we can execute CHEKDB on a secondary replica for database integrity check. This will ensure the integrity of the database hosted on secondary replica only. This execution will not guarantee the integrity of the database hosted on a primary replica as a primary replica uses a different set of storage systems to host their databases.

I start by gathering detailed requirements from stakeholders to understand the data relationships and usage patterns. I then focus on creating a scalable and normalized schema, ensuring data integrity and optimal performance. Security measures are integrated from the beginning to protect sensitive information.

Connections that surpass the given time are noted as clipped. When the client exceeds the normal time limit, the Oracle-level operations are removed, but the OS-level processes remain.

An instance is made up of a shared memory region on RAM called System Global Area (SGA) and background processes. The system's global area is a shared memory, which means it can be accessed by multiple processes. This holds data that is required by the instance to operate. The background processes are operating system processes and each process has a specific responsibility in the instance. The System Global Area and background processes are created when the instance is "started". When the instance is "shut down", the processes are killed and the shared memory region is "released" back to the operating system.

SQL Server clustering involves using multiple servers (nodes) that work together to provide high availability and redundancy. Clustering ensures that if one server fails, another server can take over without downtime. This setup is crucial for mission-critical applications requiring constant availability.