PCDOE Migrate Database Solutions Practice Test 4 — 15 Questions

Question 1

A company is migrating a MySQL database to Cloud SQL using Database Migration Service (DMS). The source database is on-premises and uses InnoDB tables. The migration job is configured as continuous (CDC). After starting the job, the full dump phase completes successfully, but the CDC phase shows no replicated changes. What is the most likely cause?

Accepted Answer

The source database does not have binary logging enabled.. Continuous CDC replication in DMS requires binary logging to be enabled on the source database. If binary logging is disabled, DMS cannot capture change data. The full dump works because it uses mysqldump, which does not rely on binary logs.

Answer

The Cloud SQL destination does not have public IP connectivity.

Answer

The source database is using MyISAM tables instead of InnoDB.

Answer

The DMS migration job was configured as one-time instead of continuous.

Question 2

An engineer is planning to migrate a PostgreSQL database to AlloyDB using Database Migration Service (DMS). The source database is version 13 and the target is AlloyDB. The migration must replicate ongoing changes with minimal downtime. Which source configuration is required to enable continuous change data capture (CDC) with DMS for PostgreSQL?

Accepted Answer

Set wal_level=logical and install the pglogical extension.. DMS for PostgreSQL continuous migration uses logical replication. The source must have the `pglogical` extension installed and configured, with the `wal_level` set to `logical`.

Answer

Configure streaming replication with a replication slot.

Answer

Enable binary logging and set binlog_format=ROW.

Answer

Set max_replication_slots=0 to disable replication conflicts.

Question 3

A company is performing a heterogeneous migration from Oracle to Cloud SQL for PostgreSQL using Ora2Pg. During testing, they find that a stored procedure using Oracle's PL/SQL `DBMS_OUTPUT.PUT_LINE` does not execute after conversion. What is the most likely issue, and how should it be resolved?

Accepted Answer

The procedure uses `DBMS_OUTPUT.PUT_LINE`, which should be replaced with `RAISE NOTICE` in PostgreSQL.. Ora2Pg converts PL/SQL to PL/pgSQL. `DBMS_OUTPUT.PUT_LINE` in Oracle is equivalent to `RAISE NOTICE` in PostgreSQL. Ora2Pg may not automatically convert all procedural constructs, requiring manual review and adjustment.

Answer

The procedure uses `DBMS_OUTPUT.PUT_LINE`, which should be replaced with `RAISE EXCEPTION`.

Answer

The procedure uses `DBMS_OUTPUT.PUT_LINE`, which is supported natively in PostgreSQL.

Answer

The procedure must be rewritten in Python using PL/Python.

Question 4

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?

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 5

An organization is migrating a Teradata data warehouse to BigQuery. They need to convert existing Teradata DDL and BTEQ scripts to BigQuery SQL. Which Google Cloud service should they use for schema conversion?

Accepted Answer

Schema Conversion Tool (SCT). Schema Conversion Tool (SCT) (now part of Database Migration Service) converts DDL and scripts from sources like Teradata to BigQuery. BigQuery Data Transfer Service handles data loading, not schema conversion.

Answer

Cloud Data Fusion

Answer

Cloud Composer

Answer

BigQuery Data Transfer Service

Question 6

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 7

A company is migrating a PostgreSQL database to Cloud SQL using DMS with continuous CDC. During cutover, the engineer checks DMS metrics and sees the replication lag is consistently 0 seconds. What is the next step to complete the migration?

Accepted Answer

Promote the destination Cloud SQL instance.. When the lag is 0, the source and target are in sync. The next step is to promote the destination (Cloud SQL) to make it the primary, which stops replication and allows writes.

Answer

Restart the migration job.

Answer

Increase the number of DMS worker nodes.

Answer

Delete the source database.

Question 8

An engineer needs to migrate a 500 GB MySQL database to Cloud SQL. The source is in a private network with no public IP. The Cloud SQL instance will also use a private IP. Which connectivity method should the engineer use for Database Migration Service?

Accepted Answer

Use VPC peering between the source network and the Cloud SQL VPC.. For private IP scenarios, DMS requires VPC peering between the source network and the Cloud SQL VPC. Alternatively, Cloud SQL Auth Proxy can be used if the source can connect to the proxy, but VPC peering is the recommended direct method for DMS.

Answer

Assign a public IP to the source database and allowlist it in Cloud SQL.

Answer

Use Cloud VPN to connect the source network to Cloud SQL.

Answer

Configure IP allowlisting on Cloud SQL to accept traffic from the source's private IP range.

Question 9

During a MySQL to Cloud SQL migration using DMS, the migration job fails during the full dump phase with an error indicating 'Access denied for user'. The DMS connection profile to the source was created with a user that has only SELECT privileges. What additional privilege is required for the full dump?

Accepted Answer

RELOAD privilege. DMS full dump uses mysqldump, which requires the RELOAD privilege to flush tables and the LOCK TABLES privilege for consistency. Without RELOAD, the dump cannot proceed.

Answer

INSERT privilege

Answer

SUPER privilege

Answer

CREATE privilege

Question 10

A company is migrating an on-premises Oracle database to Cloud SQL for PostgreSQL. They have used Ora2Pg to convert the schema. Which of the following data type conversions is correct?

Accepted Answer

Oracle CLOB → PostgreSQL TEXT. Oracle's DATE includes both date and time, so the correct mapping is to PostgreSQL TIMESTAMP. NUMBER(10) maps to INTEGER, NUMBER(10,2) to NUMERIC(10,2), and CLOB to TEXT.

Answer

Oracle NUMBER(10) → PostgreSQL NUMERIC(10)

Answer

Oracle NUMBER(10,2) → PostgreSQL INTEGER

Answer

Oracle DATE → PostgreSQL DATE

Question 11

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 12

A team is migrating a 10 TB PostgreSQL database to AlloyDB using DMS with continuous CDC. The migration starts, but the CDC phase is falling behind, with lag increasing over time. The source is a busy production database with high write throughput. What is the most effective action to reduce lag?

Accepted Answer

Increase the source database's resources (CPU/memory) to handle logical replication load.. Option B is correct because increasing the source database's resources (CPU/memory) directly addresses the root cause of CDC lag in a high-write-throughput PostgreSQL environment. DMS logical replication relies on the source's ability to decode WAL (Write-Ahead Log) records quickly; if the source is CPU- or memory-bound, it cannot keep up with the rate of changes, causing lag to grow. Scaling up the source reduces the bottleneck in WAL generation and decoding, allowing DMS to consume changes faster.

Answer

Reduce the batch size for the CDC phase.

Answer

Increase the number of DMS worker nodes.

Answer

Add more indexes to the target AlloyDB tables.

Question 13

A company is planning a migration from an on-premises SQL Server database to Cloud SQL for PostgreSQL. They need to convert both schema and data. Which two Google Cloud services should they consider? (Choose 2)

Accepted Answer

Schema Conversion Tool (SCT). Database Migration Service (DMS) handles data migration and can assist with schema conversion for homogeneous migrations. However, for heterogeneous migrations like SQL Server to PostgreSQL, Schema Conversion Tool (SCT) is needed for schema conversion. Cloud SQL is the target. BigQuery and Cloud Spanner are not applicable here.

Answer

Cloud SQL

Answer

BigQuery

Answer

Cloud Spanner

Question 14

A team is migrating a MySQL database to Cloud SQL using DMS with continuous CDC. They want to minimize downtime during cutover. Which three actions should they take as part of the cutover plan? (Choose 3)

Accepted Answer

Quiesce write operations on the source database.. During cutover, the steps are: quiesce writes on source, confirm DMS lag is 0, promote the destination, update application connection strings, and keep the source available for rollback. Disabling binary logging or deleting the source prematurely are not part of a safe cutover.

Answer

Delete the migration job immediately after promotion.

Answer

Disable binary logging on the source.

Question 15

An organization is migrating a 500 GB Oracle database to Cloud SQL for PostgreSQL using DMS with continuous CDC. The migration is in progress, and the team needs to ensure that if the migration fails, they can roll back with minimal data loss. Which two pre-migration steps should they take? (Choose 2)

Accepted Answer

Take a full backup of the source database before starting the migration.. A rollback plan should include keeping the source running (read-only during cutover) and taking a full backup before migration. Exporting from the target is not helpful for rollback. Setting up a replica on-premises is unnecessary.

Answer

Export the Cloud SQL for PostgreSQL schema before migration.

Answer

Configure DMS to allow writes to the source after promotion.

Answer

Set up an Oracle Data Guard replica on-premises.