PCDE Migrate data solutions Practice Test 9 — 15 Questions

Question 1

A company is migrating Oracle to Cloud SQL for PostgreSQL using Ora2Pg. For a column defined as NUMBER(10,2) in Oracle, what is the corresponding data type in PostgreSQL?

Accepted Answer

NUMERIC(10,2). Oracle's NUMBER(10,2) specifies a fixed-point number with up to 10 digits total and 2 digits after the decimal point. PostgreSQL's NUMERIC(10,2) is the direct equivalent, offering identical precision and scale semantics. Ora2Pg automatically maps NUMBER(p,s) to NUMERIC(p,s) to preserve exact numeric storage.

Answer

INTEGER

Answer

DOUBLE PRECISION

Answer

TEXT

Question 2

A team is planning the cutover for a DMS continuous migration. They want to minimize downtime. What is the correct cutover procedure?

Accepted Answer

Quiesce writes, confirm lag 0, promote destination, update connection strings, verify.. The correct cutover procedure for a DMS continuous migration involves quiescing writes to the source database, waiting until replication lag is zero to ensure all changes are replicated, then promoting the destination to be the new primary, updating connection strings in applications, and finally verifying that everything works. This minimizes downtime because the source is only stopped briefly during the final catch-up and promotion. The key is to ensure no data loss by confirming lag is zero before promotion.

Answer

Update connection strings to point to destination, then promote.

Answer

Promote destination immediately, then stop source.

Answer

Stop source, promote destination, update connection strings.

Question 3

A company is migrating a 5 TB Oracle database to Cloud SQL for PostgreSQL using DMS with continuous migration. They need to minimize downtime and have a rollback plan. Which TWO actions should they include in their migration plan? (Choose TWO.)

Accepted Answer

Test the application against the destination Cloud SQL instance before cutover.. Option B is correct because validating the application against the destination Cloud SQL for PostgreSQL instance before cutover verifies query compatibility, performance, and connection behavior after the Database Migration Service (DMS) schema and data conversion, so issues are caught while the source is still authoritative and downtime is minimized. Option E is correct because keeping the source Oracle database running in read-only mode after cutover preserves a rollback path: if the promoted Cloud SQL destination fails validation, the team can revert the application to the still-consistent source without data loss. Option A is wrong because a one-time migration job cannot capture ongoing changes, which would force a lengthy outage for a 5 TB database instead of the required continuous migration with minimal downtime. Option C is wrong because deleting the source immediately eliminates any rollback capability and risks permanent data loss if the destination has problems. Option D is wrong because skipping schema conversion and using raw Oracle SQL is incompatible with PostgreSQL, so the converted schema and SQL dialect must be handled by DMS and the application.

Answer

Use a one-time migration job instead of continuous.

Answer

Delete the source database immediately after promoting the destination.

Answer

Skip schema conversion and use raw Oracle SQL.

Question 4

An engineer is migrating a 20 TB PostgreSQL database to AlloyDB using DMS continuous migration. The full dump phase is taking longer than expected. Which THREE factors could affect the duration of the full dump phase? (Choose THREE.)

Accepted Answer

The number of tables and indexes on the source.. Option B is correct because the full dump phase must read and export every table and index, so a higher object count (many tables and indexes) increases metadata handling and parallel extraction overhead, directly lengthening the dump. Option C is correct because the dump is driven by the source server's ability to read data, so limited CPU and disk I/O on the source throttles how fast rows can be scanned and transmitted. Option E is correct because the extracted data must be streamed from the source to the DMS instance, so insufficient network bandwidth becomes a bottleneck that extends the full dump duration. Option A does not belong because the PostgreSQL major/minor version affects feature and replication-slot compatibility, not the raw duration of the full dump. Option D does not belong because SSL versus non-SSL adds only minor encryption overhead and is not a primary determinant of full dump duration.

Answer

The version of the source PostgreSQL database.

Answer

The choice of SSL or non-SSL connection.

Question 5

An engineer is migrating an Oracle database to Cloud SQL for PostgreSQL. They use Ora2Pg to convert the schema. One source table uses a column with data type NUMBER(10,2). What is the appropriate PostgreSQL data type after conversion, and which tool should the engineer use to verify the correctness of converted stored procedures?

Accepted Answer

Use NUMERIC(10,2) and pgTAP to write unit tests for stored procedures.. NUMBER(10,2) maps to NUMERIC(10,2) as it is a fixed-point number. Stored procedures converted from PL/SQL to PL/pgSQL should be tested with unit tests, and pgTAP is a popular PostgreSQL testing framework for this purpose. PL/pgSQL is the procedural language, not a testing tool. Ora2Pg handles conversion but does not provide unit tests.

Answer

Use VARCHAR2 and test with pgTAP.

Answer

Use FLOAT and rely on Ora2Pg's built-in validation.

Answer

Use INTEGER for the data type and PL/pgSQL for testing stored procedures.

Question 6

An engineer is manually migrating a MySQL database to Cloud SQL using mysqldump and import. They need to capture the binary log position to enable CDC with Database Migration Service later. Which TWO mysqldump flags should they include to ensure a consistent snapshot and capture the log position? (Choose 2 correct answers.)

Accepted Answer

--single-transaction. Option B (--single-transaction) is correct because it makes mysqldump issue a START TRANSACTION WITH CONSISTENT SNAPSHOT, giving a consistent InnoDB snapshot without locking tables, which is essential for a clean migration baseline. Option C (--master-data=2) is correct because it writes the binary log file and position into the dump as a comment (value 2 = commented, non-executable), which is exactly the log position needed to later configure CDC in Database Migration Service. Option A (--no-data) is wrong because it would dump only the schema and omit the data needed for the initial snapshot. Option D (--skip-lock-tables) is wrong because it disables table locking without providing snapshot consistency or log-position capture. Option E (--triggers) is wrong because it only includes trigger definitions in the dump and has nothing to do with consistent snapshots or binary log positions.

Answer

--no-data

Answer

--skip-lock-tables

Answer

--triggers

Question 7

A company is migrating an on-premises PostgreSQL database to Cloud SQL using Database Migration Service with continuous replication. The source database has binary logging enabled and uses the pglogical plugin. The migration job is failing after the full dump phase. Which THREE common issues should the engineer check? (Choose 3 correct answers.)

Accepted Answer

The source database is not configured for logical replication (e.g., wal_level is not logical).. Option A is correct because Database Migration Service for PostgreSQL relies on logical replication, which requires the source to have wal_level set to logical (plus max_replication_slots and max_wal_senders configured); if wal_level is not logical, the pglogical-based replication cannot stream changes after the initial dump. Option B is correct because continuous replication retains WAL segments until they are consumed by the replication slot, so insufficient disk space on the source for accumulating WAL files will cause the migration job to fail after the full dump phase. Option D is correct because the migration depends on a logical replication slot; if the slot was not created or has been exhausted/invalidated (e.g., due to exceeding max_slot_wal_keep_size), change data capture stops and the job fails. Option C is not applicable because Cloud SQL Auth Proxy is a client-side connector for reaching Cloud SQL instances, not something installed on the on-premises source for DMS replication. Option E is incorrect because the scenario explicitly states the source is PostgreSQL with pglogical, so it cannot be MySQL.

Answer

Cloud SQL Auth Proxy is not installed on the source.

Answer

The source database is MySQL.

Question 8

A team is planning a migration from Oracle to PostgreSQL on Cloud SQL. They use Ora2Pg to convert the schema. Which THREE Oracle data types require special attention for correct mapping to avoid data loss or precision issues? (Choose 3 correct answers.)

Accepted Answer

DATE. Option A (DATE) is correct because Oracle DATE stores both date and time (down to the second), whereas PostgreSQL DATE stores only the date; Ora2Pg must map it to TIMESTAMP (or TIMESTAMPTZ) to avoid losing the time component. Option B (NUMBER(10,2)) is correct because Oracle NUMBER is a variable-precision type, and mapping it to PostgreSQL requires choosing NUMERIC(p,s) with matching precision/scale (or BIGINT for scale 0) to prevent rounding or overflow issues. Option D (CLOB) is correct because Oracle CLOB must be mapped to PostgreSQL TEXT (or BYTEA for BLOB), and Ora2Pg needs special handling for large-object storage and encoding to avoid truncation or data loss. Option C (FLOAT) is not among the marked answers because Oracle FLOAT is essentially a NUMBER subtype that maps predictably to PostgreSQL DOUBLE PRECISION or NUMERIC, so it does not require the same special attention. Option E (VARCHAR2) is not marked because it maps straightforwardly to PostgreSQL VARCHAR/TEXT, with only length semantics (byte vs. character) needing routine adjustment rather than special precision handling.

Answer

FLOAT

Answer

VARCHAR2

Question 9

A company is migrating a 4 TB on-premises PostgreSQL 13 database to Cloud SQL for PostgreSQL. They require a one-time migration with less than 30 minutes of downtime. The database has a high volume of write transactions. Which migration approach should they use?

Accepted Answer

Use Database Migration Service (DMS) to perform a continuous migration with minimal downtime.. Database Migration Service (DMS) is designed for minimal-downtime migrations to Cloud SQL. It performs an initial snapshot and then continuously replicates changes from the source, allowing cutover with minimal downtime. For a 4 TB PostgreSQL database with high write volume, DMS handles the scale and keeps the destination nearly in sync, meeting the 30-minute downtime requirement.

Answer

Configure a read replica in Cloud SQL and promote it after replication catches up.

Answer

Use pglogical to set up logical replication between on-premises and Cloud SQL.

Answer

Perform a pg_dump and pg_restore while the application is offline.

Question 10

A financial services company is migrating a 4 TB on-premises PostgreSQL 13 database to Cloud SQL for PostgreSQL. The database must remain available for read and write operations throughout the migration, with a cutover downtime of less than five minutes. They have a 1 Gbps dedicated interconnect and can provision a Cloud SQL instance with sufficient storage. Which migration approach should they use?

Accepted Answer

Use Database Migration Service (DMS) for PostgreSQL with continuous replication, then promote the destination instance during cutover.. Database Migration Service for PostgreSQL is designed for continuous, minimal-downtime migrations to Cloud SQL. It performs an initial snapshot and then replicates ongoing changes, allowing the source to remain fully operational. During cutover, the destination is promoted, and applications are redirected. This meets the requirement of less than five minutes of downtime for a 4 TB database.

Answer

Use the Cloud SQL import feature to load a CSV export of all tables, then apply binary logs for changes.

Answer

Set up a read replica in Cloud SQL for PostgreSQL, then promote it to a standalone instance after replication catches up.

Answer

Perform a pg_dump and pg_restore over the dedicated interconnect during a scheduled maintenance window.

Question 11

A healthcare company is migrating a 10 TB on-premises Microsoft SQL Server 2019 database to Cloud SQL for SQL Server. The database has a high transaction rate, and the company requires near-zero downtime. They plan to use Database Migration Service (DMS) for SQL Server. Which configuration must they implement to support continuous replication?

Accepted Answer

Enable Change Data Capture (CDC) on the source database and ensure the SQL Server Agent is running.. Database Migration Service for SQL Server relies on Change Data Capture (CDC) to replicate ongoing changes. CDC must be enabled on the source database, and the SQL Server Agent service must be running to execute the capture jobs. This setup allows DMS to read change tables and apply them to the destination, achieving near-zero downtime. Without CDC and the Agent, continuous replication cannot function.

Answer

Enable Change Tracking on the source database and configure DMS to use it.

Answer

Enable Always On Availability Groups and use the secondary replica as the DMS source.

Answer

Configure transactional replication from the source to the destination and point DMS to the distributor.

Question 12

A healthcare organization is migrating an on-premises Oracle database to Cloud SQL for PostgreSQL. They need to convert the schema and perform a one-time migration with minimal downtime. They have a 1 TB database and can tolerate up to 1 hour of downtime. Which combination of tools should they use?

Accepted Answer

Use Ora2Pg for schema conversion and Database Migration Service (DMS) for data migration.. Ora2Pg is specifically designed to convert Oracle schemas to PostgreSQL, handling tables, indexes, and some PL/SQL. DMS provides continuous data migration with minimal downtime. Together, they cover both schema conversion and data migration, meeting the 1-hour downtime requirement.

Answer

Use pg_dump and pg_restore for both schema and data migration.

Answer

Use Oracle GoldenGate for schema conversion and data migration.

Answer

Use Database Migration Service (DMS) for both schema conversion and data migration.

Question 13

A retail company runs a 3 TB on-premises PostgreSQL 14 database on a single server. They want to migrate to Cloud SQL for PostgreSQL with less than 15 minutes of downtime. The database has a high volume of write transactions and a continuous stream of updates. They have a 1 Gbps dedicated interconnect between on-premises and Google Cloud. Which migration approach should they use?

Accepted Answer

Use Database Migration Service (DMS) for PostgreSQL with continuous replication.. Database Migration Service (DMS) for PostgreSQL is purpose-built for minimal-downtime migrations to Cloud SQL. It performs an initial snapshot and then continuously replicates changes from the source's WAL, keeping the destination up-to-date. With a high-speed interconnect, replication lag can be minimized, allowing a cutover well within the 15-minute target. Other methods either require downtime or lack managed continuous replication.

Answer

Set up a primary-replica replication between on-premises PostgreSQL and Cloud SQL, then promote the replica.

Answer

Use pg_dump and pg_restore with the --jobs flag to perform a parallel dump and restore.

Answer

Use the Cloud SQL import feature with a CSV file exported from the source database.

Question 14

A financial services company is migrating its on-premises Oracle database to Cloud SQL for PostgreSQL. The database contains many PL/SQL packages and stored procedures. They plan to use the Google Cloud Database Migration Service (DMS) for the migration. However, DMS does not automatically convert PL/SQL code. Which approach should they take to ensure that the business logic is properly migrated and continues to function after the migration?

Accepted Answer

Use Ora2Pg to convert PL/SQL to PL/pgSQL, then manually review and adjust the converted code.. Ora2Pg is a specialized tool that automates the conversion of Oracle PL/SQL to PostgreSQL PL/pgSQL. While it handles much of the syntax translation, manual review is essential to address semantic differences and ensure the converted code behaves correctly. Using Ora2Pg aligns with best practices for migrating Oracle business logic to Cloud SQL for PostgreSQL, as it reduces manual effort and provides a structured conversion process.

Answer

Migrate the PL/SQL code as-is by creating wrapper functions in PostgreSQL that call external Oracle procedures.

Answer

Enable the Oracle compatibility mode in Cloud SQL for PostgreSQL to run PL/SQL directly.

Answer

Use the Database Migration Service's built-in PL/SQL to PL/pgSQL converter during the migration.

Question 15

A startup is migrating a small 50 GB MySQL 8.0 database from an on-premises server to Cloud SQL for MySQL. They can afford a few hours of downtime and want the simplest migration method with minimal configuration. Which approach should they use?

Accepted Answer

Use mysqldump to export the database and import it into Cloud SQL.. mysqldump is the simplest and most appropriate method for migrating a small MySQL database when some downtime is acceptable. It captures the entire database schema and data in a single logical backup that can be easily imported into Cloud SQL. DMS and binary log replication are designed for minimal downtime but require more setup and are unnecessary here.

Answer

Use Database Migration Service (DMS) for MySQL with continuous replication.

Answer

Set up MySQL binary log replication from on-premises to Cloud SQL.

Answer

Use the Cloud SQL import feature with a CSV file exported from the source database.