PCDE Migrate data solutions — Timed 10 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?

10 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