PCDE Migrate data solutions — Timed 50 Questions

Question 1

A team is migrating a PostgreSQL database to AlloyDB using DMS with continuous replication. The source database is configured with logical replication. After starting the migration job, the initial dump completes but CDC replication fails with a replication slot error. What is the most likely cause?

50 minute time limit — choose an answer to begin.

Accepted Answer

The wal_level parameter on the source is set to 'replica' instead of 'logical'.. The initial dump completes successfully because it uses a snapshot and does not require logical replication. However, for the CDC (change data capture) phase, DMS relies on logical replication slots to capture ongoing changes. This requires the source PostgreSQL database to have its wal_level parameter set to 'logical'. If it is set to 'replica' (the default), the write-ahead log does not contain the necessary information for logical replication, causing DMS to fail when trying to create or use the replication slot. Therefore, option A is correct.

Answer

The source database does not have the pglogical extension installed.

Answer

The destination AlloyDB cluster has insufficient storage capacity.

Answer

The DMS connection profile uses the wrong user credentials.

Question 2

A team is converting an Oracle stored procedure that uses PL/SQL with cursors to PostgreSQL PL/pgSQL. The procedure declares a cursor with parameters. Which conversion is correct for a parameterized cursor in PL/pgSQL?

Accepted Answer

Declare the cursor as CURSOR (param INT) FOR SELECT * FROM table WHERE id = param;. The correct conversion for a parameterized cursor in PL/pgSQL is to declare the cursor using the syntax 'CURSOR (param INT) FOR SELECT ...'. This is the direct equivalent of Oracle's parameterized cursor. While REFCURSOR can also be used, it is not the most direct conversion and does not declare the cursor with parameters in the same way.

Answer

Use a REFCURSOR variable and OPEN it with a query using the parameter.

Answer

Replace the cursor with a FOR loop that returns a set of rows.

Answer

Use a temporary table to store the query results.

Question 3

An engineer is planning a migration from on-premises MySQL to Cloud SQL. They want to use Database Migration Service with continuous replication. What must be enabled on the source MySQL database before starting the migration job?

Accepted Answer

Binary logging. Database Migration Service with continuous replication requires binary logging (binlog) on the source MySQL instance to capture and stream ongoing changes. Binary logs record all data modifications (INSERT, UPDATE, DELETE) in a format that Cloud SQL can replay, enabling near-real-time replication without downtime. Without binary logging enabled, only a one-time snapshot migration is possible, not continuous replication.

Answer

Error log

Answer

Slow query log

Answer

General query log

Question 4

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 5

A company is migrating their on-premises MySQL database to Cloud SQL using Database Migration Service (DMS). The source database is MySQL 5.7, and the target is a Cloud SQL MySQL 8.0 instance. The migration job is set to continuous, but after the full dump phase, the CDC phase keeps failing with the error 'binary log not found'. What is the most likely cause?

Accepted Answer

The source MySQL binary logs have been purged before DMS could replicate them.. DMS continuous migration requires binary logging enabled on the source with row-based format, and the binary log files must be retained until they are consumed. If the source binary logs are purged before DMS reads them, CDC fails.

Answer

Cloud SQL Auth Proxy is not configured on the source.

Answer

The Cloud SQL instance does not have binary logging enabled.

Answer

The DMS migration job uses the wrong source connection profile.

Question 6

An organization is planning a database migration from Oracle to PostgreSQL on Cloud SQL. They have a large number of stored procedures that use Oracle-specific PL/SQL features. Which tool should they use to automate the schema conversion, including conversion of PL/SQL to PL/pgSQL?

Accepted Answer

Ora2Pg. Ora2Pg is an open-source tool specifically designed to automate the migration of Oracle databases to PostgreSQL, including the conversion of Oracle-specific PL/SQL stored procedures into PL/pgSQL. It handles schema objects, data types, and procedural code, making it the correct choice for this scenario where PL/SQL conversion is a key requirement.

Answer

pglogical

Answer

pg_dump

Answer

Database Migration Service (DMS)

Question 7

A company wants to migrate their on-premises SQL Server database to Cloud SQL for PostgreSQL using Database Migration Service. They need to minimize downtime. The source database is 2 TB and the network link has 1 Gbps bandwidth. What should they do first?

Accepted Answer

Create a continuous migration job with DMS using a VPC peering connection.. For minimal downtime, a continuous migration job should be used so that after the initial full dump, ongoing changes are replicated until cutover.

Answer

Use pg_dump to export the database and then import into Cloud SQL.

Answer

Create a one-time migration job to copy the database during a maintenance window.

Answer

Use mysqldump to export and import the database.

Question 8

A company is migrating an on-premises PostgreSQL 13 database to AlloyDB for PostgreSQL using DMS continuous migration. The source is configured with logical replication using the pglogical extension. During the initial sync, the migration job fails with the error 'could not open relation with OID xxxx'. What is the most likely cause?

Accepted Answer

A DDL operation on the source dropped or altered a table after the replication slot was created.. DMS uses logical replication slots when possible. If the source has DDL changes (e.g., table dropped) after the slot is created, the replication slot may become invalid.

Answer

The source database has a firewall blocking the DMS IP address.

Answer

The source database does not have the pglogical extension installed.

Answer

The target AlloyDB cluster does not have the pglogical extension.

Question 9

A data engineer is migrating a large Teradata data warehouse to BigQuery using the Schema Conversion Tool (SCTS). They need to convert BTEQ scripts and Teradata DDL to BigQuery-compatible SQL. After conversion, several date functions are not working correctly. What is the most likely reason?

Accepted Answer

The SCTS tool did not fully convert the Teradata-specific date functions to BigQuery equivalents.. SCTS converts syntax but may not perfectly translate all functions; manual review is often needed for specific date functions.

Answer

The BigQuery Data Transfer Service is required for date conversion.

Answer

The Teradata source has a different date format that is incompatible with BigQuery.

Answer

BigQuery does not support date arithmetic.

Question 10

A team is migrating a MySQL database to Cloud SQL using mysqldump for the initial snapshot. The source database uses InnoDB tables. Which mysqldump flags should be used to ensure a consistent snapshot without locking tables?

Accepted Answer

--single-transaction --skip-lock-tables. --single-transaction uses a read transaction to get a consistent snapshot without table locks for InnoDB.

Answer

--all-databases --routines

Answer

--lock-tables --single-transaction

Answer

--flush-logs --master-data=2

Question 11

A company is migrating an Oracle database to Cloud SQL for PostgreSQL. They have a table with a column defined as NUMBER(10,2). To maintain data integrity, what should be the corresponding PostgreSQL data type?

Accepted Answer

NUMERIC(10,2). NUMBER(10,2) represents a decimal number with 10 digits total and 2 after decimal; NUMERIC(10,2) is the equivalent in PostgreSQL.

Answer

FLOAT

Answer

INTEGER

Answer

DECIMAL(10,0)

Question 12

An organization needs to continuously replicate data from an on-premises PostgreSQL database to AlloyDB with minimal downtime during cutover. They have set up DMS continuous migration. During the cutover phase, what is the correct sequence of steps?

Accepted Answer

Quiesce writes to source, confirm DMS lag is 0, promote destination, update connection strings, verify application, keep source running read-only.. The standard cutover: quiesce writes, verify DMS lag is 0, promote (stop replication), update connection strings, verify app, and keep source for rollback.

Answer

Update connection strings first, then promote destination, then verify lag.

Answer

Stop source database, promote destination, then update connection strings.

Answer

Promote target immediately, then verify lag, then update connection strings.

Question 13

A company wants to migrate their on-premises PostgreSQL database to Cloud SQL using DMS. The source database is behind a firewall and does not have a public IP. The target Cloud SQL instance uses a private IP. How should the engineer connect the source to DMS?

Accepted Answer

Use a VPN or VPC peering to connect the source network to the GCP VPC.. For private connectivity, VPC peering between the source network and GCP VPC is required. Cloud SQL Auth Proxy is for connecting to Cloud SQL, not for source.

Answer

Assign a public IP to the source database.

Answer

Use DMS with a connection profile that specifies the source's private IP without any network configuration.

Answer

Configure Cloud SQL Auth Proxy on the source.

Question 14

A team is migrating a 5 TB MySQL database to Cloud SQL using DMS. The full dump phase is taking longer than expected. They suspect network bandwidth is the bottleneck. Which action can they take to improve the dump speed within DMS?

Accepted Answer

Enable parallel dump in the DMS migration job settings.. DMS uses a single-threaded dump by default. Enabling parallel dump can improve speed for large databases.

Answer

Use a larger machine type for the source connection profile.

Answer

Increase the Cloud SQL instance storage size.

Answer

Switch to a one-time migration job instead of continuous.

Question 15

A company is migrating their on-premises Oracle database to Cloud SQL for PostgreSQL. They want to test the converted stored procedures using unit tests. Which framework should they use?

Accepted Answer

pgTAP. pgTAP is a unit testing framework for PostgreSQL that can test stored procedures and functions.

Answer

pgAdmin

Answer

SQL Developer

Answer

Ora2Pg

Question 16

A data analyst needs to load data from an on-premises Teradata system into BigQuery on a recurring daily schedule. Which Google Cloud service should they use?

Accepted Answer

BigQuery Data Transfer Service. BigQuery Data Transfer Service is the correct service for scheduled, recurring data loads from on-premises Teradata into BigQuery. It supports Teradata via an agent-based transfer configuration. Cloud Composer is a workflow orchestration tool, not directly designed for this use case. Cloud Data Fusion provides ETL capabilities but is not the best fit for simple daily Teradata-to-BigQuery transfers. Database Migration Service is intended for migrating databases to Cloud SQL, not for loading data into BigQuery.

Answer

Cloud Composer

Answer

Cloud Data Fusion

Answer

Database Migration Service (DMS)

Question 17

A company is migrating a PostgreSQL database to Cloud SQL using DMS continuous migration. After the full dump, the CDC phase is replicating changes. To prepare for cutover, which TWO actions should the engineer take? (Choose 2)

Accepted Answer

Verify that the DMS migration job lag is 0 seconds.. Quiesce writes to ensure no new changes, and confirm DMS lag is 0 before promoting.

Answer

Enable binary logging on the source.

Answer

Take a full backup of the source.

Answer

Delete the DMS migration job to stop replication.

Question 18

A data team is migrating an Oracle database to Cloud SQL for PostgreSQL. They have used Ora2Pg to convert the schema. After conversion, they notice several issues with data type mappings. Which THREE Oracle-to-PostgreSQL mappings are correct? (Choose 3)

Accepted Answer

NUMBER(10,2) -> NUMERIC(10,2). NUMBER(10) -> INTEGER, NUMBER(10,2) -> NUMERIC(10,2), DATE -> TIMESTAMP are correct. CLOB -> TEXT is also correct but only three needed.

Answer

VARCHAR2(10) -> TEXT

Answer

DATE -> DATE

Question 19

An organization is migrating a large on-premises MySQL database to Cloud SQL using DMS. They want to minimize downtime. Which TWO configurations should they implement? (Choose 2)

Accepted Answer

Enable continuous migration with CDC.. Continuous migration enables CDC. Parallel dump speeds up the initial sync.

Answer

Disable binary logging on the source.

Answer

Use a one-time migration job.

Answer

Use mysqldump and import manually.

Question 20

A company is migrating a MySQL database to Cloud SQL using Database Migration Service (DMS). The source database is on-premises with a public IP address. Which networking configuration is required on the source to allow DMS to connect?

Accepted Answer

Allowlist the DMS public IP addresses in the source database firewall.. For DMS to connect to a source with a public IP, the source must allowlist the IP addresses of the DMS instance to bypass firewall restrictions.

Answer

Create a VPC peering connection between the source and DMS.

Answer

Install the Cloud SQL Auth Proxy on the source database.

Answer

Configure a Cloud VPN tunnel between the on-premises network and Google Cloud.