PCDE Migrate data solutions Practice Test 8 — 15 Questions

Question 1

A team is migrating a 2 TB MySQL database from on-premises to Cloud SQL. They want to minimize downtime. The source is MySQL 8.0 with InnoDB tables, and the application can be read-only during cutover. Which approach provides the lowest downtime while ensuring data consistency?

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

Accepted Answer

Use Database Migration Service with continuous CDC and promote when lag is zero.. DMS with continuous CDC provides near-zero downtime by replicating ongoing changes. After the initial dump, the source continues to replicate changes, allowing a quick cutover with minimal downtime.

Answer

Use mysqldump with --single-transaction to export, then import to Cloud SQL using mysql client.

Answer

Use Cloud Dataflow to stream data from MySQL to Cloud SQL.

Answer

Use gcloud sql import command with a compressed dump file from Cloud Storage.

Question 2

A migration from Oracle to PostgreSQL using Ora2Pg is complete. The team wants to run unit tests on the converted stored procedures to ensure they produce correct results. Which tool is most suitable for this purpose?

Accepted Answer

pgTAP. pgTAP is a unit testing framework for PostgreSQL that allows writing tests for functions, procedures, and other database objects. It is commonly used to validate converted PL/pgSQL code.

Answer

pgAdmin

Answer

pg_dump

Answer

pg_stat_statements

Question 3

An engineer is migrating a MySQL database to Cloud SQL using DMS. The source database uses MyISAM tables. During the migration, the full dump phase fails with a timeout. What should the engineer do to increase the likelihood of success?

Accepted Answer

Convert the MyISAM tables to InnoDB before migration.. MyISAM tables require table-level locking during dump. Using --single-transaction is only for InnoDB. To reduce lock contention, the engineer should schedule the migration during low-traffic periods or convert MyISAM to InnoDB before migration.

Answer

Add the --single-transaction flag to the mysqldump command.

Answer

Increase the DMS migration job timeout.

Answer

Use --skip-lock-tables to avoid locking.

Question 4

An organization is migrating a MySQL database to Cloud SQL using Database Migration Service (DMS). They need to ensure minimal downtime and continuous replication of changes from the source. Which type of migration job should they create?

Accepted Answer

Continuous migration job (CDC). Continuous (CDC) migration jobs replicate ongoing changes after the initial dump, allowing minimal downtime during cutover. One-time jobs only perform a full dump and import without ongoing replication.

Answer

Batch migration job

Answer

One-time migration job

Answer

Snapshots-only job

Question 5

An organization is migrating an Oracle database to PostgreSQL on Compute Engine. They used Ora2Pg to migrate schema and data. After migration, they want to validate that stored procedures produce correct results. Which tool should they use for unit testing the migrated PL/pgSQL code?

Accepted Answer

pgTAP. pgTAP is a unit testing framework for PostgreSQL that allows developers to write tests for stored procedures, functions, and other database objects in PL/pgSQL. It is specifically designed for testing PostgreSQL code, making it the correct tool for validating migrated PL/pgSQL code.

Answer

gcloud sql export

Answer

pg_dump

Answer

pglogical

Question 6

An engineer needs to migrate a PostgreSQL database to Cloud SQL. They have used pg_dump to create a dump file. Which flags should they use to avoid issues with ownership and ACLs, since Cloud SQL does not support those?

Accepted Answer

--no-owner --no-acl. --no-owner and --no-acl prevent pg_dump from including ownership and ACL commands, which are not supported on Cloud SQL. --no-privileges is not a standard flag (use --no-acl). --clean drops objects before recreating, not recommended.

Answer

--no-privileges --no-owner

Answer

--no-owner --clean

Answer

--no-acl --if-exists

Question 7

During an Oracle to PostgreSQL migration using Ora2Pg, a NUMBER(10,2) column is being mapped. Which PostgreSQL data type should be used to preserve precision and scale?

Accepted Answer

NUMERIC(10,2). Oracle NUMBER(10,2) stores up to 10 total digits with 2 digits after the decimal point. PostgreSQL's NUMERIC(10,2) is the exact-precision equivalent, preserving both precision and scale without rounding errors, which is essential for financial and other exact-value data.

Answer

TEXT

Answer

INTEGER

Answer

FLOAT

Question 8

A company is using DMS to migrate from MySQL to Cloud SQL. During the full dump phase, the migration is taking longer than expected. Which factor most likely affects the duration of the full dump?

Accepted Answer

Size of the source database. The duration of the full dump phase in a DMS migration is primarily determined by the size of the source database — the total volume of data that must be read from the source and written to the target. Larger databases require more time to transfer, regardless of engine version or table count. While other factors can influence performance, data size is the dominant factor in full load duration.

Answer

Source database engine version

Answer

Number of tables in the source

Answer

Replication lag during CDC

Question 9

A team is migrating an on-premises Oracle database to Cloud SQL for PostgreSQL using DMS. They have completed the schema conversion using Ora2Pg and are now setting up continuous migration. The source database is behind a firewall. Which connectivity method should they use for the source connection profile if they cannot use public IP?

Accepted Answer

Configure a VPN or Dedicated Interconnect with VPC. Since the source Oracle database is behind a firewall and cannot use a public IP, a VPN or Dedicated Interconnect with VPC provides a private, encrypted connection between the on-premises network and Google Cloud. This allows Database Migration Service (DMS) to reach the source database securely without exposing it to the public internet. DMS supports connectivity via these private network paths when public IP is not an option.

Answer

VPC peering

Answer

Cloud SQL Auth Proxy

Answer

IP allowlisting

Question 10

Which TWO data type mappings are correct when converting Oracle data types to PostgreSQL?

Accepted Answer

Oracle CLOB → PostgreSQL TEXT. Option A is correct because Oracle CLOB stores large character data, and PostgreSQL's equivalent unbounded character type is TEXT (with no length limit), so CLOB maps cleanly to TEXT. Option D is correct because Oracle NUMBER(10) specifies a 10-digit integer with no scale, which maps directly to PostgreSQL INTEGER (int4), whose range of -2147483648 to 2147483647 accommodates 10-digit values. Option B is wrong because Oracle VARCHAR2(100) is variable-length and should map to PostgreSQL VARCHAR(100), not the blank-padded fixed-length CHAR(100). Option C is wrong because NUMBER(10,2) has scale 2 and requires a decimal type such as NUMERIC(10,2), not INTEGER, which would truncate the fractional part. Option E is wrong because Oracle DATE includes a time component (down to seconds), so it maps to PostgreSQL TIMESTAMP, whereas PostgreSQL DATE holds only a calendar date.

Answer

Oracle VARCHAR2(100) → PostgreSQL CHAR(100)

Answer

Oracle NUMBER(10,2) → PostgreSQL INTEGER

Answer

Oracle DATE → PostgreSQL DATE

Question 11

A company is migrating a MySQL 5.7 database to Cloud SQL for MySQL 8.0 using Database Migration Service (DMS). The source database has binary logging disabled. The team needs to perform a continuous migration with minimal downtime. What must be done before creating the DMS migration job?

Accepted Answer

Enable binary logging on the source MySQL instance.. Database Migration Service (DMS) for MySQL requires binary logging to be enabled on the source instance to perform continuous (ongoing) replication. Binary logs capture all data changes and are essential for the change data capture (CDC) phase that keeps the source and destination in sync with minimal downtime. Without binary logging, DMS can only perform a one-time dump, not a continuous migration.

Answer

Create a Cloud SQL Auth Proxy connection to the source.

Answer

Create a service account with Cloud SQL Admin role.

Answer

Set up VPC peering between the source network and Cloud SQL.

Question 12

A team is migrating an on-premises PostgreSQL 13 database to AlloyDB using DMS continuous migration. During the CDC phase, the migration job shows an error: 'ERROR: could not start WAL streaming: ERROR: replication slots are not enabled on the source.' The source is running in Google Cloud Compute Engine. What is the most likely cause?

Accepted Answer

The source database parameter wal_level is set to 'replica' instead of 'logical'.. DMS continuous migration for PostgreSQL requires logical replication, which uses replication slots. The error indicates that the source database does not have the necessary configuration to support replication slots (wal_level=logical, max_replication_slots > 0).

Answer

The DMS connection profile uses the wrong public IP.

Answer

The source database is in read-only mode.

Answer

The source database does not have the pglogical extension installed.

Question 13

An organization needs to migrate a 2 TB Oracle database to Cloud SQL for PostgreSQL. The schema has many stored procedures using PL/SQL. Which tool should they use for schema conversion and what is a key consideration when converting PL/SQL?

Accepted Answer

Use Ora2Pg for schema conversion; PL/SQL must be rewritten to PL/pgSQL.. Ora2Pg is an open-source tool specifically designed to convert Oracle schemas (including PL/SQL stored procedures, functions, and triggers) to PostgreSQL-compatible PL/pgSQL. Since Oracle's PL/SQL and PostgreSQL's PL/pgSQL have significant syntactic and semantic differences (e.g., exception handling, cursor syntax, package handling), manual rewriting or tool-assisted conversion is required; Ora2Pg automates much of this conversion but still requires manual review and adjustment for complex logic.

Answer

Use pg_dump with --no-owner flag; Oracle PL/SQL is compatible with PostgreSQL.

Answer

Use mysqldump to export Oracle schema; PL/SQL can be reused as-is.

Answer

Use Database Migration Service (DMS) with built-in schema conversion; PL/SQL is automatically converted.

Question 14

An engineer is performing a manual migration from PostgreSQL to Cloud SQL. They run pg_dump and want to import the dump into Cloud SQL. Which pg_dump flags are necessary to avoid errors related to ownership and ACLs?

Accepted Answer

--no-owner and --no-acl. The --no-owner flag prevents pg_dump from setting ownership of database objects to the original owner, which would fail if that role does not exist in Cloud SQL. The --no-acl flag prevents dumping access privileges (GRANT/REVOKE statements), which can also fail if the roles or permissions are not present. Using both flags avoids errors related to ownership and ACLs during import.

Answer

--format=custom and --compress=9

Answer

--schema-only and --data-only

Answer

--create and --clean

Question 15

During a DMS continuous migration from MySQL to Cloud SQL, the full dump phase completed successfully, but the CDC phase is failing with 'Error: binary log not found' after a few minutes. The source MySQL has binary logs enabled. What is the most likely cause?

Accepted Answer

The binary logs were purged due to a short binlog_expire_logs_seconds setting.. DMS continuous migration relies on reading the source MySQL binary logs to replicate ongoing changes after the initial full dump. If binlog_expire_logs_seconds (or the older expire_logs_days) is set too low, MySQL purges binlog files before DMS has finished reading them, producing 'binary log not found' errors. The full dump succeeded, so connectivity and credentials are fine — the failure is specifically that the required binlog position no longer exists on the source.

Answer

The DMS connection profile has incorrect SSL settings.

Answer

The source database was restarted and binary logs were reset.

Answer

The source MySQL server has run out of disk space.