NÃO QUER PERDER NADA?

Dicas para passar no exame de certificação

Últimas notícias sobre exames e informações sobre descontos

Curadoria e atualizada por nossos especialistas

Sim, me envie o boletim informativo

Ver outras perguntas de entrevista
1
Resposta de referência
Choose partitioning strategy: Horizontal partitioning (range, hash, or list-based) divides data across multiple physical storage units. Consider range partitioning by date for time-series data, hash partitioning for even distribution, or list partitioning for categorical data. Implement partition pruning: Ensure queries can eliminate partitions they don't need to access. Design partition keys that align with common query patterns to maximize performance benefits. Manage partition maintenance: Automate creation of new partitions, archival of old data, and maintenance tasks like statistics updates across partitions.
2
Resposta de referência
Using srvctl:
Aceleração de carreira

Obtenha uma certificação para destacar o seu currículo.

Segundo análise de dados, titulares de certificações IT ganham 26% mais por ano do que candidatos médios. Na SPOTO, pode acelerar o crescimento da sua carreira preparando certificações e entrevistas simultaneamente.

1 100% taxa de aprovação
2 2 semanas de prática com dumps
3 Passar no exame de certificação
Reconhecido globalmente Mais de 1M certificados Estudar enquanto trabalha
Criar o seu plano de estudo
3
Resposta de referência
To troubleshoot performance issues, a DBA can: Leverage SQL Server Profiler to monitor and evaluate SQL queries, helping to identify performance issues and optimize database operations. Examine the execution plans to identify inefficient queries. Monitor system performance metrics such as CPU, memory, and disk usage. Analyze wait statistics to determine system bottlenecks. Optimize indexes and query structures.
4
Resposta de referência
Best practice is to patch the standby database first to validate the patch before applying it to the production system. Patching both together is possible but increases risk and complexity.
5
Resposta de referência
Data migration involves planning, extracting, transforming, and loading data. I ensure a smooth transition by thoroughly testing the migration process, validating data integrity, and having a rollback plan in case of issues.
6
Resposta de referência
This parameter specifies the checkpoint frequency. By default it is set to 3 seconds.
7
Resposta de referência
System Change Number (SCN) is a unique identifier that Oracle assigns to every transaction to maintain consistency and recovery operations.
8
Resposta de referência
Look for: Presentation and engagement skills.
9
Resposta de referência
Deadlock occurs when two operations are trying to change rows of a table that are restricted by the other operation. This is frequently caused by failure to issue adequate row lock requests. This condition can be caused by a poorly designed UI application, and the server's productivity will suffer as a result. When a commit/rollback action is conducted, or either of these programs is stopped outside, these restrictions will be freed immediately.
10
Resposta de referência
This is a non-technical question to evaluate your dedication, positive attitude, flexibility, and readiness to adopt new changes. You should explain your backup and maintenance strategy for a critical OLTP database, including frequency, types of backups, and integrity checks.
11
Resposta de referência
Disaster Recovery refers to the processes and technologies used to restore and maintain database operations after unexpected failures.
12
Resposta de referência
```sql SELECT TOP 1 marks FROM ( SELECT DISTINCT TOP 3 marks FROM student ORDER BY marks DESC ) AS Top3Marks ORDER BY marks ASC; ``` This finds the 3 highest distinct marks and then selects the lowest from that set.
13
Resposta de referência
Yes, RMAN backups can be performed in NOARCHIVELOG mode, but you will not be able to recover the database to a specific point in time since redo logs are not archived.
14
Resposta de referência
Use the AES_ENCRYPT and AES_DECRYPT functions for data encryption and features like SSL/TLS for secure connections.
15
Resposta de referência
$ export ORACLE_SID=ORCL $ export ORACLE_HOME=/uo1/app/oracle/product/11.2.0.4/dbhome $/uol/app/oracle/product/11.2.0.4/dbhome/bin/sqlplus/ as sysdba
16
Resposta de referência
Answers will vary, but you need to be on the lookout for applicants who name skills that align with your requirements.
17
Resposta de referência
SYSTEM
18
Resposta de referência
Merge Replication does not use a queue reader agent so there is no functionality of queue reader agent in Merge replication.
19
Resposta de referência
SQL Server Management Studio Server CMD SQL Server Management Studio Graphic User Interface
20
Resposta de referência
This is a non-technical question to evaluate your dedication, positive attitude, flexibility, and readiness to adopt new changes. You should describe two specific projects or solutions where you received recognition, highlighting the impact and your role.
21
Resposta de referência
When we travel from point A to point B, we tend to take the shortest route even though we have multiple options. Same way, optimizer generates different plans or ways in which a SQL can be executed. These plans are known as execution plans. Optimizer then chooses the best plan based on CPU cost and resources. The job of optimizer is to generate execution plans and choose the best one.
22
Resposta de referência
To give full database read-only access to a user:GRANT SELECT ANY TABLE TO username; Alternatively, you can also grant the SELECT_CATALOG_ROLE and READ_ONLY roles: GRANT SELECT_CATALOG_ROLE TO username; GRANT READ_ONLY TO username;
23
Resposta de referência
No
24
Resposta de referência
Indexing strategies are refined by analyzing usage patterns, eliminating redundant indexes, leveraging covering indexes, refreshing index statistics, and using filtered or partial indexes for selective optimization.
25
Resposta de referência
It is a process of dividing a table into smaller chunks so as to make the data retrieval easy and quick. Each piece will be known as a partition and can be accessed separately. Apart from tables, indexes can also be partitioned
26
Resposta de referência
Look for: Knowledge of replication techniques and HA configurations.
27
Resposta de referência
$ orapwd file=orapwSID password=sys_password force=y nosysdba=y
28
Resposta de referência
The server operating system where we are installing Oracle client as we need to download as per client server, not as per database server OS.
29
Resposta de referência
A cursor is a database mechanism that allows traversal over rows in a result set one row at a time. While sometimes necessary, set-based operations are generally preferred for performance.
30
Resposta de referência
RAC or Real Application Cluster allows the database to be installed across multiple servers forming a cluster and sharing the storage structure at the same time. This prevents the database from a single point of failure as one or the other instance will always stay up even if the other fails. Using RAC helps in 1.Maintaining high availability of the system. 2.Managing workload with the least expenses. 3.Scalability & agility.
31
Resposta de referência
Index rebuilding is performed to reduce fragmentation and improve query performance. The process: 1) Analyze index fragmentation levels using tools like DBCC SHOWCONTIG (SQL Server) or ANALYZE INDEX (Oracle). 2) Rebuild indexes with a fragmentation level above 30% using commands like ALTER INDEX REBUILD. 3) For lower fragmentation (5-30%), consider reorganizing indexes instead. 4) Schedule index maintenance during off-peak hours to minimize impact. 5) After rebuilding, update statistics to ensure the query optimizer has accurate data. Common times to rebuild include after large data loads, heavy update operations, or when performance degradation is observed.
32
Resposta de referência
Answers will vary, but you need to be on the lookout for applicants who name skills that align with your requirements.
33
Resposta de referência
Plan the migration: Use replication-based approaches where possible—set up the target system as a replica, let it sync, then perform a quick cutover. For schema changes, use online migration tools that can handle data transformation during replication. Minimize downtime window: Pre-stage as much as possible, use tools like AWS DMS or Azure Database Migration Service for cloud migrations, and coordinate with application teams to implement connection string changes quickly. Prepare rollback procedures: Maintain the original system until the migration is validated, have clear rollback triggers and procedures, and test the entire process in staging environments first.
34
Resposta de referência
"11": This first digit shows the major database version. Oracle usually publishes a major release once a 4 year. This digit is usually followed by a character describing the nature of the release. For example: 9i (internet), 10g (grid), 11g (grid), 12c (cloud). "2": This second digit shows the maintenance release number of the software. Oracle publishes the major release as maintenance release 1 and then usually publishes a second maintenance release during the lifetime of the software. New features are added to database Software with maintenance releases. "o": This third digit is Fusion Middleware Number. This will be o for database software. "4": This fourth digit is called Component-Specific Release Number and it shows the path set update that was applied to the software. Patch set updates are published 4 times a year by Oracle and as you apply them to your database software, this fourth digit advances.
35
Resposta de referência
An Oracle database resides on disk and this is permanent. It is composed of files that are stored on a disk. These files can be categorized into three types:
36
Resposta de referência
SHUTDOWN [NORMAL | IMMEDIATE | TRANSACTIONAL | ABORT]
37
Resposta de referência
Famous authors include Brent Ozar, Paul Randal, and others like Kimberly Tripp and Glenn Berry. Knowing about them is important because they are heavy hitters who provide authoritative guidance, best practices, and tools that are widely respected in the industry. If a non-developer DBA doesn't know about them, it may indicate a lack of engagement with the community and current best practices.
38
Resposta de referência
Using the DBA_ROLLBACK_SEG view.
39
Resposta de referência
When we use spfile, there are some parameters which can be modified dynamically. That is the biggest benefit of spfile when compared to pfile. All the parameters that you modify while database is up and running can take three scope values: spfile, memory, both. Spfile scope will make changes from next reboot, memory scope will make changes immediately but will revert back after reboot and both will make changes immediately.
40
Resposta de referência
Answers will vary, but you need to be on the lookout for applicants who name skills that align with your requirements.
41
Resposta de referência
Advanced methods encompass transparent data encryption (TDE), column-level and application-level encryption, use of hardware security modules (HSMs) for key management, and integrating with external key management services for enhanced security.
42
Resposta de referência
A trigger in SQL Server is a special kind of stored procedure that automatically runs when certain events occur in the database table, such as insert, update, or delete operations.
43
Resposta de referência
Yes, Oracle Enterprise Manager (OEM) can be integrated with ServiceNow to automate incident management. The integration ensures that alerts generated in OEM automatically create tickets in ServiceNow, reducing manual effort and improving response time. OEM Alerts and Tickets OEM provides alerts and tickets for various database-related events, such as:– Performance issues – Space utilization – Security vulnerabilities – Configuration changes Integration with ServiceNow To integrate OEM with ServiceNow, we can use the OEM's built-in integration capabilities or third-party pluginsExample of OEM-ServiceNow Integration 1. OEM detects a performance issue with a database instance and generates an alert. 2. The OEM-ServiceNow plugin sends the alert details to ServiceNow, which creates a new incident. 3. The incident is assigned to the database team, which investigates and resolves the issue. 4. Once the issue is resolved, the incident is closed in ServiceNow, and the resolution details are synced back to OEM.By integrating OEM with ServiceNow, we can streamline incident management, improve collaboration, and enhance reporting and analytics capabilities, ultimately leading to better database operations and improved service quality.
44
Resposta de referência
The Oracle developer is mostly in charge of creating backend applications. The model data in accordance with business rules. They generate tables, indexes, and other types of restrictions. They are required to be familiar with SQL and PL/SQL. These languages are used to create processes. Oracle developers, on the other hand, are not required to manage the database software. But, an Oracle DBA's primary responsibility is to administer the database, which covers areas such as doing maintenance to keep the databases operational, taking backups, maintaining privacy standards, and so on. DBAs are not typically tasked with writing code. DBAs, like developers, are expected to have a strong understanding of SQL and PL/SQL, as they are also essential for database administration. DBAs may be allocated development responsibilities or, at the very least, support developers when needed, depending on the company's structure.
45
Resposta de referência
No
46
Resposta de referência
I implement appropriate isolation levels to manage concurrent transactions and use locking mechanisms to prevent data conflicts. Additionally, I monitor for deadlocks and resolve them promptly to ensure smooth database operations.
47
Resposta de referência
8
48
Resposta de referência
sp_who2 or sp_WhoIsActive (one is enough)
49
Resposta de referência
Recovery Window-Based Retention Policy: In this policy, Oracle checks the current backup and looks for its relevance backward in time. Example: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; This ensures that the database can be recovered to any point in the last 7 days.Redundancy-Based Retention Policy: Specifies how many backups of each datafile must be retained. Older backups beyond the specified count become obsolete. Example: CONFIGURE RETENTION POLICY TO REDUNDANCY 2;No Retention Policy: Means backups will never be obsolete. Example: CONFIGURE RETENTION POLICY TO NONE;
50
Resposta de referência
The backup may face inconsistencies or errors if RMAN tries to access a missing file.
51
Resposta de referência
AIR reduces time-to-hire by 80% through conversational voice interviews, resume matching & stack ranking, customizable scoring frameworks, and enterprise-scale assessments in 16+ languages.
52
Resposta de referência
Rolling Patching: Nodes are patched one at a time while the cluster remains online, minimizing downtime. Non-Rolling Patching: The entire cluster is taken offline, and all nodes are patched simultaneously. Hybrid Patching: A mix of both, where some nodes are patched in a rolling manner, while others are patched together.
53
Resposta de referência
SQL Server Integration Services (SSIS) is a platform for building enterprise-level data integration and data transformations solutions. You can use SSIS to solve complex business problems by copying or downloading files, loading data warehouses, cleansing and mining data, and managing SQL Server objects and data.
54
Resposta de referência
Data storage can be done by using any data tools. Data storage defines how data is stored. On the other hand, data representation is how data is displayed to a user. For example, we can store data in the form of table but represent it in the form of charts!
55
Resposta de referência
Query dba_mviews or dba_mview_analysis or dba_mview_refresh_times SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,'YYYY-MM-DD HH24:MI:SS') from dba_mviews; (or) SQL> select NAME, to_char(LAST_REFRESH,'YYYY-MM-DD HH24:MI:SS') from dba_mview_refresh_times; (or) SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,'YYYY-MM-DD HH24:MI:SS') from dba_mview_analysis;
56
Resposta de referência
Resources include a virtual network name for SQL Server, a virtual IP address for SQL Server, IP addresses for the Public Network and Private Network (Heartbeat) for each node, shared drives for SQL Server Data and Log files, Quorum Disk, and MSDTC Disk.
57
Resposta de referência
Normalization is the process of reducing data redundancy in a table and improving data integrity. Then why do you need it? If there is no normalization in SQL, there will be many problems, such as: - Insert Anomaly: This happens when we cannot insert data into the table without another. - Update Anomaly: This is due to data inconsistency caused by data redundancy and data update. - Delete exception: Occurs when some attributes are lost due to the deletion of other attributes The main use of normalization is to utilize in order to remove anomalies that are caused because of the transitive dependency. Normalization is to minimize the redundancy and remove Insert, Update and Delete Anomaly. It divides larger tables into smaller tables and links them using relationships. Need for normalization : - It eliminates redundant data. - It reduces the chances of data error. - The normalization is important because it allows the database to take up less disk space. - It also helps in increasing the performance. - It improves the data integrity and consistency. A relation is in the third normal form, if there is no transitive dependency for non-prime attributes as well as it is in the second normal form. A relation is in 3NF if at least one of the following conditions holds in every non-trivial function dependency X –> Y. - X is a super key. - Y is a prime attribute (each element of Y is part of some candidate key). In other words A relation that is in First and Second Normal Form and in which no non-primary-key attribute is transitively dependent on the primary key, then it is in Third Normal Form (3NF).
58
Resposta de referência
No, you cannot run SQL Tuning Advisor on a physical standby database directly.? Why? – A physical standby database is read-only and does not support DML or advisory operations like SQL Tuning Advisor. – SQL Tuning Advisor needs access to execution plans, statistics, and in some cases, it needs to simulate or generate plans, which is not allowed on standby.
59
Resposta de referência
AlwaysOn Availability Groups is a SQL Server feature providing high availability and disaster recovery by replicating user databases. It supports automatic failover and allows readable secondaries for offloading read workloads.
60
Resposta de referência
Update Statistics is used to force a recalculation of query optimization statistics for a table or indexed view. Query optimization statistics are automatically recomputed, but in some cases, a query may benefit from updating those statistics more frequently. However, re-computing the query statistics causes queries to be recompiled, which may negate performance gains or have a negative impact.
61
Resposta de referência
SELECT DISTINCT allows the user to select the distinct values from a table in a database.
62
Resposta de referência
Answers will vary, but you need to be on the lookout for applicants who name skills that align with your requirements.
63
Resposta de referência
The database restoration process involves the following steps:Connect to RMAN using rman target / Start the database in NOMOUNT mode: STARTUP NOMOUNT; Restore the Server Parameter File (SPFILE): RESTORE SPFILE FROM AUTOBACKUP; Restore the control file: RESTORE CONTROLFILE FROM AUTOBACKUP; Mount the database: ALTER DATABASE MOUNT; Restore the datafiles: RESTORE DATABASE; Apply archived logs for recovery: RECOVER DATABASE; Open the database with resetlogs: ALTER DATABASE OPEN RESETLOGS;
64
Resposta de referência
Ideally, your team doesn't have this type of leader. However, this imaginary leader can be, in fact, a colleague who doesn't care about the quality of a project but cares more about finishing it to get a paycheck. This question can help you understand the priorities a candidate has and how they behave around this environment. Whether they speak up for what's right or simply agree that the boss is the master and commander, and if he says he's right, he is right.
65
Resposta de referência
To set up a user in SQL Server, navigate to user mapping and list all databases where the new account should reside before selecting its role from a drop-down box and clicking OK.
66
Resposta de referência
Yes
67
Resposta de referência
An Oracle developer is mainly responsible for developing backend applications. They do data modeling according to business rules. The design tables, create indexes and other types of constraints. They are expected to know SQL and PL/SQL. They develop procedures using this language. However, the Oracle developers are not expected to administer the database software itself. On the other side, an Oracle DBA's main duty is to administer the database which involves tasks like doing maintenance to keep the databases up and running, taking backups, enforcing security policies, etc. DBAs are not primarily assigned to develop code. DBAs are supposed to have a good knowledge of SQL and PL/SQL like a developer as these are also required for administering the database. According to the structure of the organization, DBAs might also be assigned development tasks or at least assist the developers where necessary.
68
Resposta de referência
Use PARALLEL for multiple worker threads. - Ensure fast I/O and adequate disk space. - Prefer direct path mode. - Use NOLOGGING where safe to reduce redo. - Disable indexes/constraints during import, then rebuild. - Run during off-peak hours. - Export/import only required data (filters). - Optimize network bandwidth in network mode. - Gather statistics after import.
69
Resposta de referência
I would look at several factors like CPU usage, memory usage, and query performance. Regular logs and alerts also provide valuable information. If all these are within optimal levels, the server is likely running properly.
70
Resposta de referência
These kind of requests must be checked with database architect. If we still have to do so, we can stop the listener for 1 week, Next shutdown the database and take DB cold backup. If application team does not come back after 1 month, then we can drop database.
71
Resposta de referência
INSERT: It submits data into a database as a new row through a form. Forms take multiple forms or an HTML form. When you click the submit button, it will trigger the built-in form reaction to scan the form of particular fields. This way, it makes sure the required forms are entered correctly. DROP: It removes a table from a database server or a database. You must know that it's a dangerous command and must be used only in situations when necessary. Unless you have a backup, you cannot come back from this. UPDATE: You can modify the values where they meet the criteria using UPDATE. You can either alter rows or a subset using this condition.
72
Resposta de referência
The CHECK constraint ensures data integrity by limiting the values allowed in a column. It specifies a condition that must be true for data to be inserted or updated, e.g., age must be > 0.
73
Resposta de referência
Factors include data volume, growth rate, performance requirements, redundancy, and cost. I plan by estimating future storage needs, selecting appropriate storage technologies like SSDs or HDDs, and configuring RAID for fault tolerance.
74
Resposta de referência
Pre-checks: Verify existing patch level, active sessions, and take a backup. Shutdown Database & Listener: Ensure services are stopped before patching. Apply the Patch: Use OPatch or OPatchAuto in RAC. Post-checks: Start the database, run Datapatch, and validate system health. Monitor for Issues: Check logs and verify performance after patching
75
Resposta de referência
Run these queries: -- On Primary SELECT thread#, MAX(sequence#) FROM v$archived_log; -- On Standby SELECT thread#, MAX(sequence#) FROM v$archived_log WHERE applied = 'YES';
76
Resposta de referência
Implementing two-factor authentication (2FA) in a database context adds a strong layer of protection, functioning as a virtual bouncer for sensitive data. Consider it a double lock for your most critical information. Here are a few human-friendly benefits: - Double Lock, Double Security: Just as opening a high-security vault takes both a key and a fingerprint, two-factor authentication requires two distinct forms of identity. This implies that even if someone learns your password, they'll still need the second piece of the puzzle. - Guarding Against Stolen Passwords: Passwords can leak or be stolen. Even if your password is hacked, 2FA adds an extra layer of security (such as a temporary code texted to your phone) to prevent an intruder from gaining access. - Remote Access Protection: In a world where remote work is becoming more frequent, 2FA serves as a virtual bodyguard for your data, making it more difficult for unauthorised users to penetrate the system, even if they are not physically there. - Preventing Unauthorized Access: 2FA provides an additional degree of security against brute force attacks and other malicious efforts to acquire unauthorised access. It's like having a gatekeeper who requires both a key and a secret handshake.
77
Resposta de referência
Situation: Our main e-commerce database went down during Black Friday, affecting all online sales. Task: I needed to identify the root cause and restore service as quickly as possible while keeping stakeholders informed. Action: I immediately activated our incident response procedure, started investigating log files, and discovered a corrupted index was causing the database engine to crash. I coordinated with the development team to implement a temporary workaround while rebuilding the index. Result: We restored service in 47 minutes, well within our 1-hour SLA, and prevented an estimated $200,000 in lost sales.
78
Resposta de referência
Answers will vary, but you need to be on the lookout for applicants who name skills that align with your requirements.
79
Resposta de referência
You can add a redo log file member to an existing group using: ALTER DATABASE ADD LOGFILE MEMBER 'new_location' TO GROUP ;
80
Resposta de referência
I use Transparent Data Encryption (TDE) for encrypting data at rest. For specific columns like credit card numbers, I apply Always Encrypted. I also restrict access through role-based permissions and audit database activities regularly.
81
Resposta de referência
Oracle Data Guard is a disaster recovery and high availability solution. It maintains a standby database that remains synchronized with the primary database using redo apply or SQL apply.
82
Resposta de referência
The contents of control file is validated against physical files. Oracle will physically check data files and redo log files on disk. The data file headers and redo log files are matched with the SCN number in control file. Once validation is done, database will be open.
83
Resposta de referência
Automatic seeding is a feature launched in SQL Server 2016 to initialize the secondary replica. Earlier we have only one option to initialize secondary replica that is using backup, copy and restore database operation. We use this feature while creating the always on availability group. Although Microsoft has given this feature, but it is not suitable for very big databases or if you have multiple databases on your SQL Server instance because this feature is a single-threaded process that affects performance and can take long hours to initialize your secondary replica.
84
Resposta de referência
SYSTEM, SYSAUX, UNDO
85
Resposta de referência
First, create a login at the server level using CREATE LOGIN. Then map it to a database user using CREATE USER. Assign roles or permissions as needed.
86
Resposta de referência
The NOLOCK hint allows SQL Server to ignore locks, providing a way to read data without being blocked by other transactions. It's used to increase performance in scenarios where real-time accuracy of data is not critical.
87
Resposta de referência
Central Inventory Loss: Oracle installations on the server become difficult to manage, patch, or upgrade. Local Inventory Loss: Patching operations for that Oracle home may fail, but the database may still function.
88
Resposta de referência
Opatch is utility to apply database patch, In order to find opatch version execute “$ORACLE_HOME/OPatch/opatch version”
89
Resposta de referência
You probably covered this on your resume, but the interviewer will likely want to talk through this early in your interview. It's a great opener because it allows you to discuss your skills as they relate to the position you're interviewing for and any projects you've worked on in other jobs or freelance work. When you're narrowing down which databases to discuss, you could look back at the job description to find the ones that are most relevant to this job. Prioritize talking about the ones listed on the job description, and then, talk about others you have experience with as well.
90
Resposta de referência
Differential Incremental Backup: Backs up only the blocks that have changed since the last incremental backup (level 1 or level 0). Backup size is smaller, but restore requires multiple incremental backups. Cumulative Incremental Backup: Backs up all blocks changed since the last level 0 (full) backup. Backup size is larger, but restoration is faster because only one incremental backup is required.
91
Resposta de referência
Architecture Components The following architecture components are involved in the update query process:1. Parser: Parses the query and generates an abstract syntax tree (AST). 2. Query Optimizer: Analyzes the query and generates an optimal execution plan. 3. Execution Engine: Executes the query plan, performing the update operation. 4. Lock Manager: Manages concurrency control, acquiring locks on rows. 5. Transaction Manager: Manages the transaction, ensuring atomicity and consistency. 6. Log Manager: Logs the update operation, ensuring recoverability. 7. Recovery Manager: Ensures that the update is recoverable in case of a failure. 8. Data Storage: Stores the updated data.
92
Resposta de referência
Ticket checking and proactive monitoring are two methods your applicants may mention when responding to this DBA interview question. Some other steps your applicants may use to troubleshoot databases could be to: Gather as much information as possible about the issue; Do testing in different environments; Check the error log on the SQL Server; Check the event log; Make a testing plan; Back up the database.
93
Resposta de referência
A clustered index determines the physical order of the data in the table and can only be applied to one column per table, as the table's data is sorted by that index. When you query a table by a clustered index, the database engine can directly locate the data because the index defines how the data is stored on disk. A non-clustered index, on the other hand, creates a separate structure that stores pointers to the physical data, allowing for multiple non-clustered indexes per table. Non-clustered indexes are helpful for columns frequently used in search queries but do not affect the table's physical storage order. For instance, a clustered index could be applied to a primary key, while non-clustered indexes could be used for columns like email or order date to speed up search operations. Here's a table that illustrates the differences between clustered and non-clustered indexes: | Feature | Clustered index | Non-clustered index | | Definition | Determines the physical order of the data in the table. | Creates a separate structure with pointers to the physical data. | | Number of indexes | Only one clustered index per table (since it defines the physical order). | Multiple non-clustered indexes can exist on a single table. | | Effect on data storage | Directly impacts how the data is stored on disk (sorted). | Does not affect the physical storage of data. | | Use case | Typically applied to the primary key or a column frequently queried for sorted results. | Used for columns frequently queried but not necessarily in sorted order (e.g., search operations on email, date). | | Data access | Faster when querying by the indexed column since the data is physically ordered. | Requires additional lookups (via pointers) to retrieve the actual data. | | Storage structure | Stores both the data and the index together in the same structure. | Stores only the index separately, with pointers to the actual data rows. | | Example | Clustered index on CustomerID. | Non-clustered index on Email. |
94
Resposta de referência
The online data files & Redo log files are opened.
95
Resposta de referência
You must have a bachelor's degree in information technology or computer science to become a database administrator in at entry-level position. If you have a master's degree, you will be prioritized in complex or experienced job positions. However, having a profound knowledge of databases, how they work, and how to resolve the problems will help you become an efficient database administrator.
96
Resposta de referência
Indexing is a technique used to improve the performance of database queries. It involves creating a data structure that allows for faster data retrieval. Indexes are created on columns that are frequently used in search conditions, joins, and sorting operations.
97
Resposta de referência
I start by identifying the bottleneck using monitoring tools and performance metrics such as CPU usage, memory, disk I/O, and wait statistics. I then analyze slow queries using execution plans, look for missing or unused indexes, check for locking and blocking issues, and review configuration settings. Based on the findings, I take corrective actions such as optimizing queries, adding or rebuilding indexes, adjusting database parameters, or scaling hardware resources.
98
Resposta de referência
Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity by dividing large tables into smaller, related tables and defining relationships between them. It typically involves applying normal forms (1NF, 2NF, 3NF, etc.). Normalization is important because it minimizes data duplication, prevents update anomalies, and ensures consistency, though it may sometimes require denormalization for performance optimization in read-heavy environments.
99
Resposta de referência
Backups and tests are the starting point for preventing data loss during migration. Administrators who notice that the data fails to translate may troubleshoot by checking the old databases to see whether there are duplicate files. They may then clean up any extra data to make the migration seamless.
100
Resposta de referência
Answers will vary, but you need to be on the lookout for applicants who name skills that align with your requirements.
101
Resposta de referência
RMAN (Recovery Manager): Oracle's backup tool. Types: - Full backup - Incremental backup (Level 0 & Level 1) - Image copy - Archive log backup - Control file & SPFILE backup
102
Resposta de referência
Some processes your applicants may use to defend your database are to: Maintain the physical security of the database; Establish an HTTPS proxy server; Deploy protocols for data encryption; Use application and database firewalls; Back up the database regularly.
103
Resposta de referência
The SGA
104
Resposta de referência
SQL Agent jobs are automated tasks scheduled and managed by SQL Server Agent. They consist of one or more steps, each executing T-SQL scripts, SSIS packages, or other commands, and can be triggered by schedules, alerts, or manual requests. They are used for routine maintenance, backups, and data processing.
105
Resposta de referência
Look for: Decision-making and risk assessment skills.
106
Resposta de referência
Transportable tablespaces (TTS) is a feature in Oracle that allows you to move tablespaces between databases, while maintaining the data integrity and consistency. Benefits: 1. Faster data transfer: TTS is faster than traditional data export/import methods. 2. Reduced downtime: TTS minimizes downtime, as the tablespaces are only offline for a short period. 3. Platform independence: TTS allows you to move tablespaces across different platforms (e.g., from Linux to Windows).Process: 1. Make the tablespace read-only: Make the tablespace read-only to ensure data consistency. 2. Export the tablespace metadata: Export the tablespace metadata using the expdp utility. 3. Transport the tablespace files: Transport the tablespace files to the target database. 4. Import the tablespace metadata: Import the tablespace metadata using the impdp utility.Across Platforms: To transport tablespaces across platforms:1. *Use the dbms_tts.transport_set_check procedure*: Verify that the tablespaces can be transported between platforms. 2. Convert the tablespaces to a platform-independent format: Use the dbms_tts.transport_set_convert procedure to convert the tablespaces. 3. Transport the tablespaces: Follow the same process as above.
107
Resposta de referência
File Name | Purpose | Content | |---|---|---| | Summary.txt | Overview of high-level summary | Condensed summary of events, actions, or data | | Detail.txt | Detailed information or granular breakdown | In-depth log or detailed information about specific events/data |
108
Resposta de referência
SIMPLE and BULK_LOGGED. (If he mentiones FULL in addition to those two, it is a wrong answer)
109
Resposta de referência
IMPORT CATALOG.
110
Resposta de referência
- Full DB backup (RMAN full or level 0) and controlfile backup. Backup ORACLE_HOME (file-level or snapshot). Check space on ORACLE_HOME, ORACLE_BASE, CRS home, and disk groups. OPatch/OPatch Version: Ensure OPatch is compatible with the patch. Inventory health: opatch lsinventory should return clean results.Check DB alert logs and health (no outstanding errors). Check listeners and TNS connectivity. Check running sessions and schedule downtime. Check prerequisites: OS patches, kernel parameters, package versions, Java versions (if required). Check cluster health for RAC/GI ( crsctl stat res -t ,cluvfy ).Disable automatic jobs (NAT/cron/DB maintenance) which may interfere. Check filesystem (NFS) latency and mount options. Check DB components versions (datapatch compatibility). Collect environment details: ORACLE_HOME, Oracle version, PSU version, OPATCH version. Test patch in non-prod (staging environment). Notify stakeholders & plan rollback steps & timeline. Ensure root access to run root scripts and correct OS user.
111
Resposta de referência
A collection of privileges grouped for ease of management.
112
Resposta de referência
Dynamic Data Masking (DDM) in SQL Server 2022 is used to limit sensitive data exposure by masking it to non-privileged users. It is simple to implement and does not affect database operations because the data in the database is not changed; only the output is masked. However, DDM is not a security control but a data obfuscation technique that should be used in conjunction with other security features. Limitations include the possibility for users to infer data through query patterns or mistakes in configuration, which can lead to unintentional data exposure.
113
Resposta de referência
Oracle's architecture consists of several components, including memory structures, background processes, and database storage. The key memory components are the System Global Area (SGA), which contains the Shared Pool, Buffer Cache, Redo Log Buffer, and other pools. The Program Global Area (PGA) holds data related to user sessions. Background processes like Log Writer (LGWR), Database Writer (DBWR), System Monitor (SMON), and others ensure the smooth functioning of the database. The database itself consists of data files, control files, redo logs, and archive logs, which together support data storage and recovery.
114
Resposta de referência
Oracle provides several types of indexes to improve query performance by speeding up data retrieval: • B-tree indexes: The most common type; ideal for high-cardinality columns with many unique values. They organize data in a balanced tree structure for fast searches. • Bitmap indexes: Efficient for columns with low cardinality (few distinct values), such as gender or status. They use bitmaps to represent data and work well in data warehousing. • Function-based indexes: Indexes based on expressions or functions on columns, like indexing UPPER(name) to make case-insensitive searches faster. • Reverse key indexes: Reverse the byte order of keys to reduce contention in insert-heavy environments. • Domain indexes: Custom indexes for specific data types like spatial, text, or XML data. • Clustered indexes: Logical grouping of rows for faster access but less common in Oracle. Each type suits different use cases based on data distribution and query patterns.
115
Resposta de referência
Cache fusion is the process of transferring data from one instance buffer cache to another at a very high speed within a cluster. Instead of fetching data from physical disk which is a slow process, the data block can be accessed from the cache. For Example, Instance A wants to access a data block, owned by instance B. It will send an access request to instance B and hence can access the same using the other instance B's buffer cache.
116
Resposta de referência
Answers will vary, but you need to be on the lookout for applicants who name skills that align with your requirements.
117
Resposta de referência
From Oracle to MySQL to PostgreSQL, there are many databases your applicants may prefer. Are they able to explain why they prefer one over the other? It's also important to assess whether your applicants' knowledge of the databases they quote is sufficient. A great follow-up question is “What do you dislike about your preferred database?”
118
Resposta de referência
Techniques involve analyzing slow queries through execution plans, indexing optimization, partitioning large tables, tuning database configuration parameters, monitoring resource utilization, and leveraging tools for real-time performance monitoring and diagnostics.
119
Resposta de referência
TKPROF is a tuning utility provided by Oracle which can convert SQL trace files into a readable format. Once trace file is generated using SQL Trace Utility, the TKPROF tool can be run against trace file and output can be read. It can also generate the execution plan for SQL statements. The executable for TKPROF is located in the ORACLE HOME/bin directory.
120
Resposta de referência
SELECT business_type, SUM(adwords_earnings) AS earnings FROM google_adwords_earnings GROUP BY business_type;
121
Resposta de referência
SQLOS stands for SQL Server Operating System. It works like a mini operating system for SQL Server operations. It is the lowest layer of the SQL Server database engine, which performs critical internal tasks, like memory management, scheduling threads on the CPU to execute SQL Server transactions, deadlock detection, and IO completion activities.
122
Resposta de referência
A senior DBA should have a basic grasp of the advantages and disadvantages of the major database platforms. They've probably answered this question before, too – if not from a manager, then from a developer who's whining because they think Platform X is better than Platform Y. I also want to see senior DBAs that can clearly explain a very political concept without taking it personally. My ideal DBA knows when his platform isn't the right fit, and has no problem suggesting other ideas.
123
Resposta de referência
If Flashback is enabled, it's the fastest method: FLASHBACK TABLE table_name TO BEFORE DROP; If Flashback is not available or table is purged: • Use RMAN to perform Tablespace Point-In-Time Recovery (TSPITR). • It creates a temporary auxiliary database, restores the tablespace there, and copies back the dropped table. Example:RMAN> RECOVER TABLE scott.emp UNTIL TIME "SYSDATE-1" AUXILIARY DESTINATION '/u01/rman_aux'; Alternatively, if the table was dropped and archived logs are available, RMAN TSPITR is your best option when Flashback can't help.
124
Resposta de referência
Troubleshooting is a regular task for a most DBA, so it's crucial to hire one that is able to do it efficiently. They need to answer by telling you what their process for identifying and addressing the issue is like. You should look for a solid process; if a candidate gives a general answer, this might be a red flag. Try to understand in detail how this process is like and how they use their available resources/tools, and their experience.
125
Resposta de referência
During a critical e-commerce sale event, the database went down due to a sudden spike in traffic. My first step was to communicate the issue to the stakeholders and ensure proper monitoring and alerting were in place. I quickly analyzed the logs and identified that a lack of database connections was causing the outage. I increased the connection pool size and implemented load balancing across multiple read replicas to distribute the load more evenly. The database was restored, and I then worked on root cause analysis to prevent future occurrences.
126
Resposta de referência
Yes.
127
Resposta de referência
ODBC is an administrative tool for creating 32-bit and 64-bit database sources that connect directly to SQL Server.
128
Resposta de referência
A few changes and enhancements in SQL Server 2005 include support for Analysis Services on a Failover Cluster, and high-availability features such as Failover Clustering, Database Mirroring, Log Shipping, and Replication.
129
Resposta de referência
When you make changes to .bash_profile, you must execute it at least once using . .bash_profile
130
Resposta de referência
When a database is put in BEGIN BACKUP mode:1. Redo log generation increases: Oracle generates additional redo logs to ensure data consistency and integrity. 2. Archivelogs are generated more frequently: The increased redo log generation leads to more frequent archiving of redo logs, resulting in a higher number of archivelogs.This is because: – Oracle ensures data consistency: By generating more redo logs, Oracle ensures that all changes are recorded and can be recovered in case of a failure. – Prevents data corruption: The increased archiving of redo logs helps prevent data corruption by ensuring that all changes are properly recorded and stored.When the database is taken out of BEGIN BACKUP mode, the redo log generation and archiving return to normal.
131
Resposta de referência
– AUTO_SPACE_ADVISOR_JOB will run in daily maintenance window and report fragmented Indexes/Tables SQL>ANALYZE INDEX VALIDATE STRUCTURE; This populates the table ‘INDEX_STATS'. It should be noted that this table contains only one row and therefore only one index can be analyzed at a time. An index should be considered for rebuilding under any of the following conditions: * the percentage of deleted rows exceeds 30% of the total, i.e. if del_lf_rows / lf_rows > 0.3. * If the ‘HEIGHT' is greater than 4. * If the number of rows in the index (‘LF_ROWS') is significantly smaller than ‘LF_BLKS' this can indicate a large number of deletes, indicating that the index should be rebuilt.
132
Resposta de referência
The view helps provide security, presentation of data from a different perspective, and store complex queries.
133
Resposta de referência
Database sharding is a horizontal partitioning strategy where a large database is split into smaller, more manageable pieces called shards. Each shard is stored on a separate server, allowing for greater scalability and performance in distributed systems. Sharding is typically used when dealing with large datasets, such as for social media platforms or e-commerce websites, where the database needs to handle high transaction volumes and millions of users. For example, a user database might be sharded by user ID so that each shard handles a subset of users, improving query performance and balancing the load across multiple servers.
134
Resposta de referência
SQL (Structured Query Language) is a programming language used to create, manage, and query relational databases. It allows users to retrieve, insert, update, and delete data. SQL is widely used in applications across industries because it helps manage structured information easily and reliably.
135
Resposta de referência
I have experience working with multiple DBMS platforms, including Oracle, Microsoft SQL Server, and MySQL. Oracle offers robust scalability and advanced features for enterprise-level applications, while SQL Server provides excellent integration with other Microsoft technologies. MySQL, on the other hand, is known for its simplicity and flexibility. I understand the strengths and weaknesses of each system and can leverage their specific features to optimize performance, scalability, and reliability based on the specific requirements of the organization.
136
Resposta de referência
Data Encryption is a method of preserving data confidentiality by transforming it into ciphertext, which can only be decoded using a unique decryption key produced at the time of the encryption or prior to it. Data encryption converts data into a different form (code) that can only be accessed by people who have a secret key (formally known as a decryption key) or password. Data that has not been encrypted is referred to as plaintext, and data that has been encrypted is referred to as ciphertext. Encryption is one of the most widely used and successful data protection technologies in today's corporate world.
137
Resposta de referência
A synonym is used to mask the original name and owner of an object and provides public access to an object.
138
Resposta de referência
This is a generic question often asked by interviewers. Explain what different SQL Server versions you have worked on and what kind of administration of those instances has been done by you. Your role and responsibilities in earlier projects would be significant. For example, you might say you have experience working in SQL Server 7, SQL Server 2000, 2005, and 2008. Be honest about the versions you have worked on.
139
Resposta de referência
Patch history will be lost, making future patch management difficult. However, the database itself remains unaffected.
140
Resposta de referência
In Oracle, a System Data File is a part of the SYSTEM tablespace and contains critical metadata required for database operation. It consists of: - Data Dictionary – Metadata about database objects such as tables, indexes, users, privileges, and constraints. - System Undo Segments – Used to store undo information for system-related transactions. - Bootstrap Information – Required to initialize the database instance. - Views and Stored Procedures – System-related PL/SQL packages and views. - Rollback Segments (if using Manual Undo Management) – For transaction consistency and rollback operations.
141
Resposta de referência
| Feature | Function | Stored Procedure | |---|---|---| | Returns | Must return a value (scalar or table) | May return nothing, one, or multiple output values | | Use in SELECT | ✅ Yes | ❌ Not allowed | | Can modify data? | ❌ No (read-only) | ✅ Yes (can use INSERT/UPDATE/DELETE) | | Transactions | Cannot start/commit/rollback | ✅ Full transaction support | | Dynamic SQL | ❌ Not supported | ✅ Supported |
142
Resposta de referência
Table variables are in-memory, limited to their batch/proc scope, no statistics. Temporary tables (#temp or ##global) are stored in tempdb, can be indexed, have statistics, and have broader scope/persistence.
143
Resposta de referência
It is a method of creating a duplicate database by copying data directly from the primary database while it remains online.
144
Resposta de referência
A bitmap index uses bitmaps (bit arrays) to represent the presence or absence of a value in rows, making it highly space efficient. Use cases: • Ideal for columns with low cardinality (few distinct values), like gender, marital status, or categories. • Common in data warehouse environments where queries aggregate large data sets. • Not suitable for OLTP systems with frequent updates, as bitmap indexes can cause locking issues. Bitmap indexes speed up complex ad hoc queries involving AND, OR, NOT operations on multiple columns.
145
Resposta de referência
A standby clone is a physical standby database used mainly for reporting or testing without impacting the primary.Steps to create a standby clone for reporting: • Set up a physical standby database using RMAN or Data Guard. • Open the standby database in read-only mode or use Active Data Guard for real-time query capability. • Ensure the standby database continuously applies redo logs to stay synchronized. • Configure reporting tools or applications to connect to the standby clone. • Schedule regular monitoring to ensure data freshness and apply lag is minimal. • Use standby cloning to offload heavy reporting queries from primary, reducing load and risk. Standby clones provide high availability with reporting flexibility.
146
Resposta de referência
- Striping with an additional drive for parity is what RAID 5 is all about. If we damage a disk, we may rebuild it from the parity disk. - RAID 10 mirror pairs of drives before striping over those mirrors. - RAID 5 was developed at a time when drives were costly. Its goal was to deliver RAID on a budget. If a disk stops working, the IO module will function VERY slowly while it is being rebuilt. Furthermore, with all of the increased weight of the rebuild, your likelihood of failure increases considerably during this period. RAID 5 is sluggish for everything except reading even when it is running properly. Given that, plus the fact that databases (particularly Oracle's redo logs) continue to suffer write activity at all times, we should avoid RAID5 in all but the odd database that has primarily read activity. Redo logs should not be placed on RAID5. - RAID10 is just all-around awesome. If you lose a single disk in a set of ten, for example, you might lose any of the remaining eight disks and be OK. Furthermore, because you're only creating a mirror duplicate, rebuilding has no influence on productivity. Finally, RAID10 performs admirably in all sorts of databases.
147
Resposta de referência
Server process
148
Resposta de referência
Types of Oracle Objects:Table – Stores structured data in rows and columns. Index – Improves query performance (B-Tree, Bitmap, etc.). Cluster Table – Stores related tables together to improve performance. IOT (Index-Organized Table) – Stores table data in a B-Tree index structure. Function – A stored PL/SQL program that returns a value. Procedure – A stored PL/SQL program that does not return a value. Package – A collection of related procedures and functions. Trigger – Executes automatically on specific database events (INSERT, UPDATE, DELETE). Sequence – Generates unique numbers (often used for primary keys). Synonym – An alias for database objects (used to simplify access). View – A virtual table based on a query. Materialized View – A stored query result for fast retrieval. Database Link – Allows access to remote databases. Tablespace – A logical storage unit that contains datafiles. Directory – Defines file system paths accessible from Oracle.
149
Resposta de referência
Securing a corporate database involves multiple layers: 1) Implementing strong authentication mechanisms, such as complex passwords or multi-factor authentication. 2) Applying the principle of least privilege by granting only necessary permissions to users and roles. 3) Encrypting data at rest and in transit using technologies like Transparent Data Encryption (TDE) or SSL/TLS. 4) Regularly auditing access logs and monitoring for suspicious activity. 5) Keeping the database software patched and up to date. 6) Using firewalls and network segmentation to restrict access to the database server.
150
Resposta de referência
A primary key is a column or a set of columns that uniquely identifies a row in a table. It ensures that each row is distinct and can be used to reference the row from other tables. Primary keys are essential for maintaining data integrity and enabling efficient querying.
151
Resposta de referência
– AUTO_SPACE_ADVISOR_JOB will run in daily maintenance window and report fragmented Indexes/Tables SQL>ANALYZE INDEX VALIDATE STRUCTURE; This populates the table ‘INDEX_STATS'. It should be noted that this table contains only one row and therefore only one index can be analyzed at a time. An index should be considered for rebuilding under any of the following conditions: * the percentage of deleted rows exceeds 30% of the total, i.e. if del_lf_rows / lf_rows > 0.3. * If the ‘HEIGHT' is greater than 4. * If the number of rows in the index (‘LF_ROWS') is significantly smaller than ‘LF_BLKS' this can indicate a large number of deletes, indicating that the index should be rebuilt.
152
Resposta de referência
When analyzing AWR (Automatic Workload Repository) reports in Oracle, I focus on the following key areas to diagnose performance issues: 1. Report Summary (Snapshot Information) 2. Top Timed Events 3. Load Profile 4. Instance Efficiency Percentage 5. Top SQL Queries (SQL Statistics) 6. Wait Events (Wait Classes) 7. System Statistics 8. Segment Statistics 9. I/O Statistics 10. RAC Statistics (For RAC Environments)
153
Resposta de referência
Yes, Flashback Database can be performed using both the SQL*Plus command prompt and the RMAN command prompt.
154
Resposta de referência
Redo Apply: Applies redo after the standby redo log is archived. Real-Time Apply: Applies redo as it's received (from redo log buffer or file), without waiting for archiving.
155
Resposta de referência
Answers will vary, but you need to be on the lookout for applicants who name skills that align with your requirements.
156
Resposta de referência
Database replication involves copying and maintaining database objects across multiple servers to ensure data redundancy and high availability. It can be synchronous or asynchronous. - Synchronous replication ensures that changes are reflected in real time across servers. - Asynchronous replication updates replicas with a slight delay. Replication is particularly useful in scenarios where uptime is critical, such as for e-commerce platforms, where users expect the database to always be available, even during maintenance or hardware failures.
157
Resposta de referência
A function is a routine that accepts parameters, performs actions, and returns a single value or a table. Example: `SELECT GETDATE()` returns the current server date and time.
158
Resposta de referência
Maintaining a positive and proactive attitude is crucial in my role as a Database Administrator. During challenging situations, I focus on the solution rather than dwelling on the problem. I remain calm, assess the situation objectively, and collaborate with the team to develop an action plan. I believe in proactive communication, keeping stakeholders informed about progress, challenges, and potential solutions. I also actively seek opportunities for process improvements and efficiency gains. By maintaining a positive mindset and proactive approach, I can effectively navigate challenging situations and contribute to a supportive and productive work environment.
159
Resposta de referência
A Snapshot Control File is a temporary copy of the control file created at the beginning of an RMAN backup. It ensures a consistent point-in-time view of the control file while the backup is running. If a tablespace or file is added after the backup starts, it will not be included in that backup.
160
Resposta de referência
Look for: Problem-solving approach and resolution skills.
161
Resposta de referência
Materialized views are objects that have reduced sets of information that have been summarized, grouped, or aggregated from base tables. They are typically used in data warehouses or decision support systems.
162
Resposta de referência
Standby redo logs (SRLs) receive redo data from the primary database in real-time, enabling real-time apply on the standby database. This minimizes data loss by ensuring redo is written to the standby redo log simultaneously with the primary's online redo log. Without SRLs, archived redo logs are applied only after completion, risking data loss in failover scenarios. SRLs are mandatory for real-time apply and recommended on both primary and standby for faster switchovers.
163
Resposta de referência
| Factor | Database Normalization | Database Optimization | |---|---|---| | Process | Database Normalization involves breaking up data into smaller, related tables and creating relationships between them. | Database Optimization involves making changes to the physical structure of the database, such as adding indexes, creating partitions, and reorganizing tables and other system parameters. | | Security | Database normalization does not affect database security. | Database optimization can improve database security. | | Data Access | Database normalization does not affect data access. | Database optimization can improve data access. | | Output | Database Normalization results in a more organized and efficient database structure. | Database Optimization results in a faster and more efficient database. |
164
Resposta de referência
The fill factor tells SQL Server how much free space to leave in index pages when re-indexing. The performance benefit is fewer page splits because there is room for growth built into the index.
165
Resposta de referência
An Oracle block is the smallest unit of storage in an Oracle database, containing data like table and index records. It has a fixed size (e.g., 4 KB, 8 KB) set during database creation. The block is divided into several sections: Header: Stores metadata and transaction info. Row Directory: Contains pointers to rows in the block. Free Space: Empty area for new data. Data Space: Holds actual data.
166
Resposta de referência
Recovery models in SQL Server control how transaction logs are managed and determine the level of data loss tolerance. The three types are: Simple (minimal logging, no point-in-time recovery), Full (complete logging, supports point-in-time recovery), and Bulk-Logged (minimal logging for bulk operations, with full logging for other transactions).
167
Resposta de referência
The index is used to increase the performance of retrieval. We can make use of one or more rows in order to make the index. The index can increase the performance of retrieval and slows down the performance of insertion.
168
Resposta de referência
This is a non-technical question to evaluate your dedication, positive attitude, flexibility, and readiness to adopt new changes. You should explain your approach, such as escalating to senior team members, consulting documentation or community forums, or engaging vendor support.
169
Resposta de referência
A flat file is a plain text file that doesn't take up much space. It's sometimes referred to as a text database and can be saved as a comma-separated value (CSV) file or a delimited flat file. Because they don't take up much space, they work well for transferring data. Many applications require flat files when importing data.
170
Resposta de referência
To view the roles and privileges assigned to a user in Oracle, you can use the following queries: 1️⃣ Check System Privileges Assigned to a User ? This shows system-level privileges like CREATE SESSION ,CREATE TABLE , etc. 2️⃣ Check Object Privileges Assigned to a User ? This displays object-level privileges (e.g., SELECT ,INSERT ,UPDATE ,DELETE on tables, views, etc.). 3️⃣ Check Roles Assigned to a User ? This lists the roles granted to the user. 4️⃣ Check Privileges Included in a Role ? This helps identify what privileges are included in a specific role. 5️⃣ Check All Roles in the Database ? This retrieves all available roles in the database. Alternative for Non-DBA Users If you don't have access to DBA views, you can check your privileges using: -
171
Resposta de referência
Database backups are essential for data recovery in case of data loss. SQL Server offers several types of backups, including: Full Backup: Backs up the entire database. Differential Backup: Backs up changes since the last full backup. Transaction Log Backup: Backs up the transaction log, which records all transactions since the last backup. A comprehensive backup strategy should include a combination of these types, scheduled appropriately to meet the organization's recovery point objectives (RPO) and recovery time objectives (RTO).
172
Resposta de referência
• AWR (Automatic Workload Repository) collects performance data like wait events, SQLs, sessions, and system stats every hour (default). o It helps compare performance between two points in time. o Use: @$ORACLE_HOME/rdbms/admin/awrrpt.sql • ASH (Active Session History) stores real-time session activity from v$session for active sessions only. o It's like a mini version of AWR with focus on active sessions. Both are great for troubleshooting performance issues. AWR helps with historical patterns; ASH is more session-focused.
173
Resposta de referência
RMAN offers parameters like BACKUP OPTIMIZATION, COMPRESSION, and PARALLELISM to improve backup efficiency.
174
Resposta de referência
We will first try to take backup of archives if possible. If not, we will move some archives to another location OR we can even change the archive destination inside database to a location which has more space.
175
Resposta de referência
When managing multiple database projects, I start by clearly understanding the priorities and deadlines for each project. I collaborate with stakeholders to identify critical tasks and use project management tools like Jira or Trello to organize and track progress. I prioritize tasks based on their impact on the business, potential risks, and dependencies. For instance, a task involving security patches would take precedence over routine maintenance. I also allocate dedicated time slots for each project to ensure steady progress without context switching. Regular communication is key, so I keep stakeholders informed of the progress and any potential delays. I also prepare for unforeseen issues by building buffer time into my schedule. If a high-priority issue arises, such as a database outage, I can quickly pivot to address it while keeping other projects on track.
176
Resposta de referência
Partitioning divides a large table or index into smaller, manageable pieces called partitions, each stored separately but appearing as one logical object. Benefits: • Improves query performance by pruning irrelevant partitions. • Simplifies maintenance tasks like backup, restore, and data loading on partitions. • Enhances manageability and scalability for very large tables. • Enables parallel processing on partitions. • Supports different storage characteristics per partition. Partitioning can be based on ranges, lists, hashes, or composite methods.
177
Resposta de referência
Oracle's grid design combines a huge database server, memory, and connections to create a customized, as per requirement computing resource for business computing demands. The grid computing architecture constantly evaluates the consumption of resources and changes supply accordingly. You might, for example, run several programs on a grid of numerous connected database servers. If monthly reports are pending, the database administrator might automatically supply a dedicated server to that program to manage the growing demands. Grid computing employs advanced workload management, enabling programs to work cooperatively across several servers. As per requirement data processing ability may be added or deleted, and resources within a region can be provided flexibly. Web services enable the rapid integration of apps to establish a business.
178
Resposta de referência
Archived copy of redo logs. Enables point-in-time recovery.
179
Resposta de referência
Common methods include backup and restore, detaching and attaching database files, or using SSIS packages for transformation. Key steps involve compatibility checks, security setup, and thorough testing post-migration.
180
Resposta de referência
From my previous role as a Database Administrator, I have gained strong communication skills for explaining technical issues to non-technical stakeholders, collaboration skills for working with development teams on database design, and problem-solving skills for resolving conflicts during system outages. I also developed attention to detail through meticulous documentation and empathy when supporting end-users with data access issues.
181
Resposta de referência
Dynamic Management View
182
Resposta de referência
Yes
183
Resposta de referência
Blocking occurs when one process holds a lock needed by another. Fixes include identifying the blocker, optimizing blocking queries, adding appropriate indexes, and ensuring transactions are short.
184
Resposta de referência
RMAN (Recovery Manager) is Oracle's built-in tool for performing backup and recovery operations. It automates and simplifies the process of taking backups, validating them, and recovering data. Compared to user-managed backups (using OS commands like cp or SQL*Plus), RMAN offers major advantages: • It tracks backup metadata internally. • Supports incremental backups. • Performs block-level corruption checks. • Handles restore/recovery with fewer manual steps. • Supports backup to disk or tape using Media Management Layer (MML). User-managed backups are prone to human error, lack cataloging, and require more manual effort, whereas RMAN is reliable, consistent, and Oracle-supported.
185
Resposta de referência
Tracking unauthorized access involves monitoring and auditing all login attempts and resource access. Steps: • Enable auditing on user logins and privilege usage. • Use Unified Auditing or traditional audit features. • Monitor failed login attempts via DBA_AUDIT_SESSION or UNIFIED_AUDIT_TRAIL. • Set up alerts or automated notifications for suspicious activities. • Use Oracle's Fine-Grained Auditing (FGA) to track access to sensitive data at row or column level. • Regularly review audit logs and correlate with user roles and permissions. Combining audit with security tools ensures early detection of unauthorized access.
186
Resposta de referência
You have to go through the job description first. Look into what the company demands. Then, you have to fit into the shoes and prepare every answer that matches the candidate the company is looking for. You can start preparing by reading the most anticipated database administrator interview questions mentioned above. Later on, you can practice behavioral questions before your known ones to gain confidence and enhance communication skills.
187
Resposta de referência
A view is a virtual table based on the result set of a SQL query. Views can be used to simplify complex queries, restrict access to specific data, and present data in a user-friendly manner. They do not store data physically but provide a way to access data from one or more tables.
188
Resposta de referência
BWR
189
Resposta de referência
In the default configuration, the Oracle database will not automatically start after the server reboots. You'll have to start it manually after each reboot. You'll usually want it to start automatically. There are two methods to accomplish this:
190
Resposta de referência
Yes, you can still perform a Flashback Database after modifying the compatibility parameter. However, changing compatibility might impact certain database features.
191
Resposta de referência
Tablespaces are logical containers that group datafiles. They help manage space and organize data. System Tablespaces: • SYSTEM: Stores core Oracle metadata like data dictionary tables. • SYSAUX: Stores auxiliary components like AWR, OEM data. User Data Tablespaces: • USERS: Default tablespace for user-created objects like tables and indexes. • You can create custom tablespaces to separate data logically (e.g., SALES_TBS). Temporary Tablespace: • Used for sorting, hashing, and other temp operations during query execution. • TEMP tablespace stores data that doesn't need to persist after a session ends. UNDO Tablespace: • Stores undo records to support rollback, flashback, and read consistency. • Automatically managed by Oracle. Tablespaces allow Oracle DBAs to manage storage and performance efficiently. Each tablespace consists of one or more datafiles on disk.
192
Resposta de referência
Restoring a 2-node RAC depends on failure scenario. General options: - If restoring entire RAC from backups: - Ensure both nodes' Grid Infrastructure (ASM) is up or restore GI first. - Restore ASM metadata and diskgroups if ASM metadata lost (consult Oracle Support). - Restore database from RMAN on ASM: - - For RAC, you may need to restore OCR and voting disks for GI first. - Restore SPFILE for RAC nodes and configure cluster resources. - If one node fails (node crash): - Bring the failed node back via OS-level restore or rebuild node OS, re-install Grid and Oracle home, join cluster. - Start CRS and database instances on that node. - - If datafiles lost: - Use RMAN to restore to ASM diskgroups. - Consider using duplicate target database ... to rebuild standby as primary then recreate RAC. - - If you have standby: use Data Guard to failover and then rebuild RAC from standby or duplicate back. - Volume-snapshot restore: restore ASM diskgroup volumes from snapshot. - General steps: - Restore GI (OCR/voting) -> Start cluster -> Restore ASM diskgroups -> Restore database -> Register instances on both nodes -> Start services. - Because RAC involves GI / OCR / Voting / ASM, registry steps must be precise. Always follow documented RAC recovery procedures; test in non-prod.
193
Resposta de referência
We need to check the query and if possible tune the query. We can even speak with application team and allocate more temp space and reclaim space once their activity is done.
194
Resposta de referência
OEM is Oracle's web-based tool for database monitoring and management. To configure: • Install OEM agent on the target database server. • Register the database with the OEM repository. • Configure monitoring metrics and thresholds for alerts. • Set up credentials for agent to connect to the database. Use OEM to: • Monitor performance, sessions, wait events. • View alert logs and diagnostics. • Manage backups, jobs, and patches. • Set up automated alerts and notifications. • Generate performance reports. OEM simplifies DBA tasks and provides a centralized management console.
195
Resposta de referência
If it's resource contention, optimize system usage. If it's index fragmentation, rebuild indexes. If it's wait events, resolve blocking or storage bottlenecks. If it's bad statistics, regenerate them properly
196
Resposta de referência
BCT is a feature that tracks modified database blocks, making incremental backups faster by identifying only changed blocks since the last backup, thus reducing backup time and resource consumption.
197
Resposta de referência
If You Handle Both DB and Grid Patching:Yes, I handle both Database patching and Grid Infrastructure patching in our environment. - For Database patching, I apply Quarterly Patch Set Updates (PSU) or Release Updates (RU) using OPatch or DBMS_ROLLING (for Data Guard setups). - For Grid Infrastructure patching, I use Rolling Patching to minimize downtime, applying patches on one node at a time in an Oracle RAC setup. - I ensure proper pre-patch checks, take backups, and validate post-patch health using opatch lsinventory, srvctl, crsctl, and log verification. If You Only Handle Database Patching:I mainly handle Database patching, where I apply RU/PSU patches using OPatch. Before patching, I perform pre-checks, backup verification, and post-patch validations to ensure smooth deployment. If there are Grid patches required, I coordinate with the team managing Grid Infrastructure. If You Only Handle Grid Patching: I focus on Grid Infrastructure patching, ensuring a smooth rolling upgrade process in an Oracle RAC environment. I patch one node at a time to minimize downtime and ensure cluster stability. I use tools like opatchauto, crsctl, srvctl, and run pre-checks before applying patches.
198
Resposta de referência
The process involves extracting a copy of the production database, ensuring it is consistent, and then restoring it to the development environment. Steps typically include: 1) Taking a full backup of the production database (logical or physical). 2) Transferring the backup to the development server. 3) Restoring the backup to the development database, often with a different name or location to avoid conflicts. 4) Performing any necessary data masking or sanitization to protect sensitive information. 5) Validating the refresh by checking data integrity and running key queries.
199
Resposta de referência
The correct answer to this question is no. That's because running tests in a live production environment can lead to data corruption or a system crash, causing the server to become unstable. This can lead to a very pricy fix, resulting in a significant financial loss for a company. Rather than running tests live, it's better to run them in a staging environment, which should happen before pushing anything out to the live database.
200
Resposta de referência
I implement constraints and validation rules at the schema level to enforce data integrity. Additionally, I regularly audit and monitor the database for inconsistencies, using transactions to ensure atomicity and consistency of operations.


Copyright © 2024. Designer by SPOTO Official Website. All Rights Reserved.