PCDE Migrate data solutions Practice Test 2 — 15 Questions

Question 1

A company needs to migrate a 5 TB Oracle database to Cloud SQL for PostgreSQL. They need to convert schemas and data, and also set up ongoing replication with minimal downtime. Which TWO services/tools should they use? (Choose TWO.)

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

Accepted Answer

Database Migration Service. Database Migration Service (A) is correct because it is Google Cloud's managed service for migrating Oracle databases to Cloud SQL for PostgreSQL, supporting both the initial full dump/load and continuous change data capture (CDC) replication for minimal-downtime cutover. Ora2Pg (E) is correct because it is the open-source schema and data conversion tool that Database Migration Service uses under the hood to translate Oracle PL/SQL objects, data types, and schemas into PostgreSQL-compatible DDL and data. Cloud SQL for MySQL (B) is wrong because the target is PostgreSQL, not MySQL, so it is the wrong database engine. BigQuery Data Transfer Service (C) is wrong because it is for loading data into BigQuery for analytics, not for migrating or replicating an OLTP database into Cloud SQL. gcloud bigquery load (D) is wrong because it is a BigQuery data-loading command and has nothing to do with Oracle-to-PostgreSQL schema conversion or ongoing replication.

Answer

Cloud SQL for MySQL

Answer

BigQuery Data Transfer Service

Answer

gcloud bigquery load

Question 2

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 3

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.. For PostgreSQL sources, DMS requires logical replication to capture ongoing changes. This means setting 'wal_level=logical' in postgresql.conf and installing the pglogical extension (or using the native logical decoding plugin). The pglogical extension provides the necessary replication set and slot management for DMS to read the write-ahead log and replicate changes continuously. Without logical decoding, DMS cannot perform CDC.

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 4

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). The Schema Conversion Tool (SCT) is specifically designed to automate the conversion of legacy data warehouse schemas and SQL dialects (including Teradata) to BigQuery. It parses Teradata DDL, BTEQ scripts, and other SQL constructs, then generates equivalent BigQuery SQL, handling data type mappings, function translations, and syntax differences. This directly addresses the need to convert existing Teradata DDL and BTEQ scripts.

Answer

Cloud Data Fusion

Answer

Cloud Composer

Answer

BigQuery Data Transfer Service

Question 5

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 DMS continuous CDC shows replication lag of 0 seconds, the source and destination are fully synchronized. The next step is to promote the destination Cloud SQL instance to make it the primary database, completing the cutover. This stops replication and redirects application traffic to the new instance.

Answer

Restart the migration job.

Answer

Increase the number of DMS worker nodes.

Answer

Delete the source database.

Question 6

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 Database Migration Service to migrate a MySQL database from a private network with no public IP to a Cloud SQL instance with a private IP, VPC peering between the source network and the Cloud SQL VPC is the appropriate connectivity method. This allows private IP communication without exposing the source to the public internet, and DMS can use the peered connection to replicate data securely.

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 7

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. For a full dump in a MySQL to Cloud SQL migration using DMS, the source user requires the RELOAD privilege in addition to SELECT. RELOAD is needed to perform operations like FLUSH TABLES WITH READ LOCK, which ensures a consistent snapshot during the dump.

Answer

INSERT privilege

Answer

SUPER privilege

Answer

CREATE privilege

Question 8

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 CLOB (Character Large Object) stores large blocks of character data, and PostgreSQL TEXT is an unlimited-length variable character type, making it the direct and correct equivalent. Ora2Pg maps CLOB to TEXT because both handle large character payloads without a fixed length limit. This preserves the semantics of the original column without truncation or padding.

Answer

Oracle NUMBER(10) → PostgreSQL NUMERIC(10)

Answer

Oracle NUMBER(10,2) → PostgreSQL INTEGER

Answer

Oracle DATE → PostgreSQL DATE

Question 9

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.. 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 10

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 11

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.. Option A is correct because quiescing (stopping) write operations on the source database ensures no new transactions are generated, allowing DMS to fully drain the remaining changes and reach a consistent state before cutover. Option C is correct because promoting the destination Cloud SQL instance (for example, promoting a replica or completing the DMS migration job's promotion step) makes the target writable and finalizes the switchover to the new database. Option D is correct because confirming DMS replication lag is 0 verifies that all changes from the source have been applied to the destination, which is essential to avoid data loss during cutover. Option B is not appropriate because deleting the migration job immediately after promotion removes the ability to verify replication status or roll back if issues arise; the job should be retained until cutover is validated. Option E is incorrect because disabling binary logging on the source would break continuous CDC, since DMS relies on the binary log to capture ongoing changes.

Answer

Delete the migration job immediately after promotion.

Answer

Disable binary logging on the source.

Question 12

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.. Option A is correct because taking a full backup of the source Oracle database before starting the DMS migration provides a reliable restore point; if the migration fails or data is corrupted, the team can restore the source with minimal data loss. Option E is correct because keeping the source Oracle database running and accepting writes until cutover ensures the source remains the authoritative system, so a failed migration can be rolled back simply by continuing to use the source, with CDC catching up any changes. Option B is not required as a rollback safeguard because exporting the Cloud SQL for PostgreSQL schema only captures the target schema, not the source data needed to recover from a failed migration. Option C is incorrect because allowing writes to the source after promotion is a post-cutover configuration concern and does not protect against migration failure. Option D is incorrect because an Oracle Data Guard replica is a high-availability/disaster-recovery solution for Oracle, not a pre-migration rollback step for a DMS migration to Cloud SQL for PostgreSQL.

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.

Question 13

A team is migrating a self-managed PostgreSQL database to AlloyDB using DMS. They need to set up the source connection profile. The source database is in a different VPC network, and the team wants to avoid exposing it to the internet. Which connectivity option should they use?

Accepted Answer

VPC peering. VPC peering is the correct connectivity option because it allows private IP connectivity between two VPC networks without exposing the source database to the internet. DMS can use VPC peering to connect to a source PostgreSQL database in a different VPC, as long as the CIDR ranges do not overlap and the necessary firewall rules allow traffic on port 5432. This meets the requirement of avoiding internet exposure while enabling secure, low-latency replication.

Answer

Direct peering

Answer

Cloud SQL Auth Proxy

Answer

IP allowlisting for public IP

Question 14

A company is using mysqldump to migrate a MySQL database to Cloud SQL. The source database uses InnoDB tables and is running a production workload. They want to ensure a consistent snapshot without locking tables. Which mysqldump flags should they use?

Accepted Answer

--single-transaction --skip-lock-tables. `--single-transaction` starts a consistent snapshot by issuing a REPEATABLE READ transaction, and `--skip-lock-tables` prevents mysqldump from acquiring table locks, allowing the production workload to continue. Together they produce a consistent InnoDB dump without locking tables.

Answer

--single-transaction --master-data

Answer

--lock-all-tables --flush-logs

Answer

--skip-lock-tables --all-databases

Question 15

A data engineer is migrating a Teradata data warehouse to BigQuery. They have a large number of BTEQ scripts that need to be converted. Which tool is designed to automate the conversion of Teradata DDL and BTEQ scripts to BigQuery-compatible SQL?

Accepted Answer

Schema Conversion Tool (SCTS). Schema Conversion Tool (SCTS) — often referred to as BigQuery Schema Conversion Tool or the Teradata-to-BigQuery migration tool — is designed to automate the conversion of Teradata DDL and BTEQ scripts into BigQuery-compatible SQL. It parses Teradata-specific syntax and translates it to GoogleSQL, reducing manual effort.

Answer

Database Migration Service

Answer

Ora2Pg

Answer

BigQuery Data Transfer Service