PCDOE Migrate Database Solutions — 40 Questions

Question 1

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 2

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 3

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 4

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 5

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 6

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 7

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 8

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 9

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 10

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 11

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 12

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 supports scheduled transfers from Teradata (via agent) and other sources.

Answer

Cloud Composer

Answer

Cloud Data Fusion

Answer

Database Migration Service (DMS)

Question 13

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 14

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 15

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 16

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.

Question 17

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?

Accepted Answer

The wal_level parameter on the source is set to 'replica' instead of 'logical'.. DMS uses logical replication slots. If the max_replication_slots is not set sufficiently high, DMS cannot create or use the required slot, causing CDC failure.

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 18

An organization is migrating an Oracle database to Cloud SQL for PostgreSQL using Ora2Pg. During testing, a stored procedure that uses Oracle's SYSDATE produces incorrect results. What is the correct approach to handle this conversion?

Accepted Answer

Replace SYSDATE with CURRENT_TIMESTAMP.. Oracle's SYSDATE returns date and time; PostgreSQL equivalent is CURRENT_TIMESTAMP (or NOW()). Simply mapping to CURRENT_DATE would lose time component, breaking logic.

Answer

Replace SYSDATE with LOCALTIMESTAMP.

Answer

Use TO_DATE(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') to convert the value.

Answer

Replace SYSDATE with CURRENT_DATE in the converted procedure.

Question 19

A company is using DMS to migrate a MySQL database to Cloud SQL with continuous replication. During the CDC phase, the replication lag is increasing and not catching up. The source database is heavily used for OLTP workloads. Which action would most likely reduce the replication lag?

Accepted Answer

Reduce the number of concurrent write operations on the source database.. Increasing the source database's binary log retention period does not reduce lag; it retains more logs. Reducing concurrent writes on source directly slows the rate of change, allowing DMS to catch up.

Answer

Increase the size of the Cloud SQL destination instance.

Answer

Increase the binary log retention period on the source.

Answer

Enable parallel replication on DMS.

Question 20

A team needs to migrate a large Teradata data warehouse to BigQuery. They want to automatically convert Teradata DDL and BTEQ scripts to BigQuery-compatible SQL. Which Google Cloud service should they use?

Accepted Answer

Schema Conversion Tool (SCTS). Schema Conversion Tool (SCTS) is designed for heterogeneous migrations, converting DDL and scripts from sources like Teradata to BigQuery.

Answer

BigQuery Data Transfer Service

Answer

Database Migration Service (DMS)

Answer

Cloud Dataflow