DBS-C01 Mock Exam 81 — 25 Questions

Question 1

A company is running an Oracle database on Amazon RDS. The database has a large table that is frequently accessed by multiple applications. The DBA notices that the table has a high number of index scans but the queries are still slow. Upon investigation, the buffer cache hit ratio is low. Which design change would BEST improve performance?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Increase the instance size to provide more memory. The low buffer cache hit ratio indicates that the database's memory (buffer cache) is insufficient to cache frequently accessed data blocks, causing excessive physical I/O. Increasing the instance size provides more memory, which expands the buffer cache and allows more data to be cached, reducing disk reads and improving query performance. This directly addresses the root cause of the slow queries despite efficient index scans.

Answer

Convert the table to columnar storage using Amazon Redshift

Answer

Add a read replica to offload queries

Answer

Migrate the table to Amazon DynamoDB with DAX

Question 2

Refer to the exhibit. A CloudFormation stack was deployed to create an RDS instance. The application team reports they cannot connect to the database using the endpoint provided. The security group allows inbound traffic on port 3306 from the application's security group. What is the most likely cause?

Accepted Answer

The database password is not provided in the stack outputs; the team may be using the wrong password. The CloudFormation stack outputs typically do not include the database password for security reasons. The application team may be using an incorrect password, which would cause authentication failures even if the network connectivity and security group rules are properly configured. Without the correct password, the RDS instance will reject the connection attempt at the MySQL/MariaDB protocol level.

Answer

The RDS instance is in a Multi-AZ deployment and the endpoint is for the standby

Answer

The security group inbound rule is missing the port 3306

Answer

The RDS endpoint is incorrect; it should include the port number

Question 3

A company runs a critical PostgreSQL database on Amazon RDS and needs to implement point-in-time recovery (PITR) with a recovery window of the last 35 days. Currently, automated backups are set to retain for 35 days. The database size is 500 GB. What additional configuration is required?

Accepted Answer

No additional configuration is required; automated backups enable PITR. Amazon RDS for PostgreSQL automated backups include transaction logs, which are required for point-in-time recovery. With automated backups retained for 35 days, RDS can restore the database to any second within that retention period by replaying transaction logs against a full backup. No additional configuration is needed because PITR is an inherent feature of automated backups when the retention period is set to 35 days.

Answer

Enable manual snapshots every hour

Answer

Set up a replication slot and stream transaction logs to S3

Answer

Enable Multi-AZ deployment

Question 4

A company is migrating an on-premises Oracle data warehouse to AWS. The warehouse contains 50 TB of data and runs complex queries that involve joins and aggregations. The team wants to minimize migration effort and cost while maintaining query performance. Which AWS service should they use?

Accepted Answer

Amazon Redshift. Amazon Redshift is purpose-built for large-scale data warehousing, supporting up to petabytes of data with massively parallel processing (MPP) architecture that efficiently handles complex joins and aggregations. It minimizes migration effort by supporting automated schema conversion from Oracle via the AWS Schema Conversion Tool (SCT) and cost-effective columnar storage with compression, making it the optimal choice for a 50 TB Oracle data warehouse migration.

Answer

Amazon RDS for Oracle

Answer

Amazon ElastiCache for Redis

Answer

Amazon DynamoDB

Question 5

A company is migrating a 5 TB Oracle database to Amazon Aurora PostgreSQL. They have a 4-hour maintenance window weekly. Which THREE steps should be taken to minimize downtime? (Choose 3)

Accepted Answer

Use AWS Snowball to transfer the initial data load.. AWS Snowball provides a physical data transfer mechanism that avoids saturating the network for the initial 5 TB load, which could otherwise take days over typical internet connections. This allows the bulk data to be loaded into Amazon S3 and then into Aurora PostgreSQL before the cutover, while AWS DMS with CDC (Option D) captures ongoing changes to keep the target synchronized with the source during the migration window. Option E is correct because the AWS Schema Conversion Tool (SCT) is essential for converting the Oracle schema (including data types, stored procedures, and indexes) to Aurora PostgreSQL-compatible format before data migration begins, ensuring compatibility and reducing downtime during the final sync.

Answer

Create an Aurora read replica for testing.

Answer

Use AWS Direct Connect for network connectivity.

Question 6

A company needs to store JSON documents that require complex querying on nested attributes. The database must support ACID transactions and be fully managed. Which service should they use?

Accepted Answer

Amazon Aurora MySQL. Amazon Aurora MySQL is correct because it supports JSON documents with complex querying on nested attributes via MySQL's JSON data type and JSON path expressions, while also providing full ACID transaction support through its MySQL-compatible engine. As a fully managed service, Aurora handles provisioning, backups, and patching, meeting all stated requirements.

Answer

Amazon DocumentDB (with MongoDB compatibility)

Answer

Amazon DynamoDB

Answer

Amazon Neptune

Question 7

Which TWO AWS services can be used to migrate an on-premises SQL Server database to Amazon RDS for SQL Server?

Accepted Answer

AWS DMS. AWS DMS (Database Migration Service) is correct because it is purpose-built for migrating databases to AWS with minimal downtime, supporting homogeneous migrations like SQL Server to Amazon RDS for SQL Server. It uses change data capture (CDC) to replicate ongoing changes, enabling near-zero downtime migrations. Native SQL Server backup to S3 and restore to RDS is also correct because SQL Server's native backup/restore mechanism can be used to take a full backup, upload it to Amazon S3, and then restore it directly onto an RDS for SQL Server instance, which is a supported and common offline migration method.

Answer

AWS DataSync

Answer

AWS Storage Gateway

Answer

AWS DynamoDB

Question 8

A company wants to migrate their on-premises Oracle database to Amazon RDS for Oracle. They have a complex data loading process that uses Oracle Data Pump. Which migration approach is MOST efficient and minimizes downtime?

Accepted Answer

Use AWS Database Migration Service (DMS) with ongoing replication from the source Oracle database.. AWS DMS with ongoing replication (change data capture, CDC) is the most efficient approach for migrating an Oracle database to Amazon RDS for Oracle with minimal downtime. It allows a full load of the existing data followed by continuous replication of changes from the source until cutover, reducing the outage window to seconds or minutes. This directly addresses the requirement to minimize downtime while handling complex data loading processes.

Answer

Use AWS Schema Conversion Tool (SCT) to convert the schema and then copy data files directly.

Answer

Take a physical backup of the on-premises database and restore to RDS.

Answer

Export data using Oracle Data Pump and import into RDS.

Question 9

A company runs a MySQL database on Amazon RDS for an e-commerce platform. The application performs frequent INSERT and UPDATE operations on the 'orders' table. The team notices an increase in disk I/O and CPU usage. They want to optimize the database for write-heavy workloads without changing the application. Which option is the MOST effective?

Accepted Answer

Increase the InnoDB buffer pool size. Increasing the InnoDB buffer pool size allows more data and indexes to be cached in memory, reducing disk I/O for write operations by delaying writes and enabling more efficient page merging. This directly addresses the high disk I/O and CPU usage from frequent INSERT and UPDATE operations without requiring application changes.

Answer

Enable Multi-AZ deployment for redundancy

Answer

Change the storage engine to MyISAM

Answer

Upgrade to Amazon Aurora MySQL

Question 10

A company wants to migrate an on-premises Oracle database to Amazon RDS for Oracle with minimal downtime. Which AWS service should be used for the initial data load and ongoing replication?

Accepted Answer

AWS Database Migration Service (DMS). AWS Database Migration Service (DMS) is the correct choice because it supports both full-load migration and ongoing change data capture (CDC) replication from Oracle to Amazon RDS for Oracle. DMS uses Oracle LogMiner or binary reader to capture incremental changes from the source database's redo logs, enabling near-zero downtime migration without requiring application modifications.

Answer

AWS Snowball

Answer

AWS Direct Connect

Answer

AWS Schema Conversion Tool (SCT)

Question 11

A company is migrating a 1 TB SQL Server database from on-premises to Amazon RDS for SQL Server. The database has a large table with a LOB column. The network bandwidth between the on-premises data center and AWS is limited to 50 Mbps. The migration must be completed within a week. Which approach should the company use?

Accepted Answer

Use AWS DMS with compression enabled.. AWS DMS with compression enabled is the correct approach because it can migrate the 1 TB database within the limited 50 Mbps bandwidth by compressing data during transfer, reducing the total data volume. DMS supports ongoing replication and handles LOB columns efficiently, making it suitable for a time-constrained migration. The 50 Mbps link can transfer approximately 1.5 TB in a week (50 Mbps * 604,800 seconds / 8 = ~3.78 TB raw capacity), but compression and overhead make DMS the most reliable choice for this scenario.

Answer

Use BCP to export the LOB table to flat files and then import to RDS.

Answer

Use SQL Server native backup and restore to RDS.

Answer

Create a read replica of the on-premises database and promote it.

Question 12

A startup is building a social media application that requires storing user profiles, posts, comments, and likes. The workload has variable traffic, with spikes after marketing campaigns. The team expects to run complex JOIN queries to generate a user's feed. Which AWS database service is MOST suitable for this relational workload?

Accepted Answer

Amazon RDS for PostgreSQL. Amazon RDS for PostgreSQL is the most suitable choice because the workload requires complex JOIN queries on relational data (user profiles, posts, comments, likes). PostgreSQL provides full SQL support, ACID compliance, and robust indexing capabilities (e.g., B-tree, GiST, GIN) that efficiently handle multi-table joins. RDS also offers managed scaling, automated backups, and read replicas to accommodate traffic spikes after marketing campaigns.

Answer

Amazon Neptune

Answer

Amazon DynamoDB with global secondary indexes

Answer

Amazon ElastiCache for Redis

Question 13

A company needs to store session state for a web application that runs on Amazon EC2 instances behind an Application Load Balancer. The session data is small (less than 1 KB per user) and must be highly available with low latency. Which AWS database service is best for this use case?

Accepted Answer

Amazon ElastiCache for Redis. Amazon ElastiCache for Redis is the best choice because it provides an in-memory data store with sub-millisecond latency, ideal for storing small session state data (less than 1 KB per user). Redis supports key-value operations with built-in data expiration (TTL), making it perfect for session management. Its replication and automatic failover capabilities ensure high availability, meeting the application's requirements.

Answer

Amazon S3

Answer

Amazon DynamoDB

Answer

Amazon RDS for MySQL

Question 14

A company wants to migrate an on-premises Oracle database to Amazon RDS for Oracle with minimal downtime. Which AWS service should be used for the migration?

Accepted Answer

AWS Database Migration Service (DMS). AWS Database Migration Service (DMS) is the correct choice because it is specifically designed to migrate databases to AWS with minimal downtime by using ongoing replication (change data capture, CDC) from the source Oracle database to the target Amazon RDS for Oracle instance. DMS supports heterogeneous and homogeneous migrations, and for Oracle-to-Oracle migrations, it can use Oracle LogMiner or binary reader to capture changes continuously, allowing the source to remain operational during the migration.

Answer

AWS Systems Manager

Answer

AWS Schema Conversion Tool (SCT)

Answer

AWS Service Control Policies (SCP)

Question 15

A company wants to migrate a 10 TB Microsoft SQL Server database from on-premises to Amazon RDS for SQL Server. The migration must be completed within a week and minimize network bandwidth usage. Which approach is most suitable?

Accepted Answer

Use AWS Snowball Edge to transfer backup files, then restore to RDS. AWS Snowball Edge is the most suitable approach because it allows you to transfer the 10 TB database backup files physically, bypassing the internet entirely. This eliminates network bandwidth constraints and ensures the migration can be completed within a week, as the data is shipped to AWS and then restored to Amazon RDS for SQL Server using native restore operations.

Answer

Back up the database to Amazon S3 using AWS CLI

Answer

Set up AWS Direct Connect and use DMS

Answer

Use AWS DMS over the internet

Question 16

A company has a 200 GB PostgreSQL database on-premises and wants to migrate to Amazon RDS for PostgreSQL. The company requires encrypted data transfer and wants to minimize downtime. Which combination of services should be used?

Accepted Answer

Use AWS DMS with SSL and ongoing replication. AWS DMS with SSL provides encrypted data transfer and supports ongoing replication (change data capture) to minimize downtime. This allows the on-premises PostgreSQL database to remain operational during the initial full load and then continuously replicate changes until cutover, meeting both security and minimal-downtime requirements.

Answer

Use native pg_dump to S3, then import to RDS

Answer

Use AWS VPN to connect on-premises to RDS and perform a database dump

Answer

Use an EC2 instance with PostgreSQL replication to RDS

Question 17

A startup is building a mobile app backend with user profiles and social features. They need a database that can handle flexible schemas, high read throughput for user profiles, and strong consistency for friend requests. Which database service should they choose?

Accepted Answer

Amazon DynamoDB. Amazon DynamoDB is the correct choice because it provides flexible schemas (schema-less tables) ideal for user profiles that may vary in attributes, supports high read throughput via auto-scaling and DAX caching, and offers strongly consistent reads (when requested) to ensure friend requests are processed reliably. Its fully managed nature and single-digit millisecond latency align with the startup's need for a scalable, consistent database.

Answer

Amazon RDS for MySQL

Answer

Amazon Neptune

Answer

Amazon DocumentDB

Question 18

A company is planning to deploy a new Amazon RDS for Oracle database in a Multi-AZ configuration. The database must be highly available and fault-tolerant. Which THREE actions should the company take to meet these requirements? (Choose three.)

Accepted Answer

Enable Multi-AZ deployment. Enabling Multi-AZ deployment for Amazon RDS for Oracle automatically provisions and maintains a synchronous standby replica in a different Availability Zone. This provides automatic failover in the event of an AZ outage or primary instance failure, ensuring high availability and fault tolerance without manual intervention.

Answer

Take manual snapshots daily

Answer

Create a read replica in a different Availability Zone

Question 19

A company runs an Oracle database on Amazon RDS. The database is used by multiple applications, and the company needs to capture all data modification language (DML) changes for auditing. Which solution should be used?

Accepted Answer

Use AWS DMS with change data capture (CDC) to stream changes to Amazon S3.. AWS DMS with change data capture (CDC) can continuously capture DML changes from an Oracle RDS instance and stream them to Amazon S3 in a format such as Parquet or CSV. This provides a durable, queryable audit trail of all data modifications without requiring additional Oracle licensing or impacting database performance. CloudTrail captures API-level events, not DML changes, and Oracle Flashback and Audit Vault are not fully supported or manageable on Amazon RDS.

Answer

Use AWS CloudTrail to capture database events.

Answer

Enable Oracle Flashback and store the flashback logs.

Answer

Install Oracle Audit Vault on the RDS instance.

Question 20

Refer to the exhibit. The exhibit shows CloudWatch metrics from an Amazon RDS for PostgreSQL instance. The application is experiencing slow query performance. Which is the most likely cause?

Accepted Answer

High storage latency due to provisioned IOPS being fully utilized or EBS volume contention. The exhibit shows that the 'Write IOPS' metric is consistently at or near the provisioned IOPS limit, while 'Write Latency' is elevated. When provisioned IOPS are fully utilized, Amazon EBS throttles I/O, causing increased storage latency. This directly degrades query performance for PostgreSQL, especially for write-heavy workloads, as each write operation must wait for the storage layer to complete.

Answer

High CPU utilization due to complex queries

Answer

Too many database connections causing context switching

Answer

Insufficient memory causing disk swaps