PCDOE Migrate Database Solutions Practice Test 3 — 15 Questions

Question 1

A company is migrating an on-premises PostgreSQL database to Cloud SQL for PostgreSQL. They need to ensure minimal downtime and maintain ongoing replication until cutover. Which approach should they use?

Accepted Answer

Use Database Migration Service with a continuous migration job.. Database Migration Service (DMS) with a continuous migration job is the correct approach because it supports minimal-downtime migrations from on-premises PostgreSQL to Cloud SQL by using PostgreSQL's native logical replication. DMS handles the initial one-time data load and then continuously replicates ongoing changes until you are ready to cut over, meeting the requirement for minimal downtime and ongoing replication.

Answer

Create a replica of the on-premises PostgreSQL and use pg_basebackup to stream to Cloud SQL.

Answer

Export using pg_dump with --no-owner and --no-acl flags, then manually configure logical replication slots.

Answer

Use pg_dump to export the database and import it into Cloud SQL, then set up application-level dual writes.

Question 2

An organization is migrating an Oracle database to Cloud SQL for PostgreSQL. They have numerous stored procedures written in PL/SQL. Which tool should they use to automatically convert these procedures to PL/pgSQL?

Accepted Answer

Ora2Pg. Ora2Pg is a schema conversion tool that translates Oracle PL/SQL code to PostgreSQL PL/pgSQL, handling data type mappings and object conversions.

Answer

BigQuery Data Transfer Service

Answer

Database Migration Service (DMS)

Answer

gcloud sql import

Question 3

During a MySQL to Cloud SQL migration using Database Migration Service, the full dump phase is taking much longer than expected. The source MySQL database is 500 GB and the Cloud SQL instance is of sufficient size. What is the most likely cause of the slow dump?

Accepted Answer

The source database has many MyISAM tables.. Without --single-transaction, mysqldump locks tables, causing contention and slow performance. DMS uses mysqldump internally and requires InnoDB tables with --single-transaction for consistent non-blocking dumps.

Answer

The source database is using a public IP address.

Answer

Binary logging is not enabled on the source.

Answer

The Cloud SQL instance is using a shared-core machine type.

Question 4

A company is migrating a 10 TB Teradata data warehouse to BigQuery. They need to convert Teradata DDL and BTEQ scripts to BigQuery SQL. Which Google Cloud service should they use?

Accepted Answer

Schema Conversion Tool (SCTS). Schema Conversion Tool (SCTS) is designed to convert DDL and SQL scripts from sources like Teradata to BigQuery-compatible format.

Answer

BigQuery Data Transfer Service

Answer

Database Migration Service (DMS)

Answer

gcloud bigquery load

Question 5

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 6

During cutover of a Database Migration Service continuous migration job, the engineer observes that the source database still receives writes after promoting the destination Cloud SQL instance. What should the engineer do to complete the migration?

Accepted Answer

Stop the application that writes to the source, then confirm DMS lag is zero before promoting.. Cutover requires quiescing writes to the source to ensure no new changes are generated. The application should be stopped or switched to read-only first.

Answer

Adjust the DMS job to ignore further changes.

Answer

Promote the destination again; this will force a stop of writes.

Answer

Delete the source database to force the cutover.

Question 7

A company is migrating an Oracle database to Cloud SQL for PostgreSQL using Ora2Pg for schema conversion. After conversion, they want to test the correctness of converted stored procedures. Which tool should they use?

Accepted Answer

pgTAP. pgTAP is a unit testing framework for PostgreSQL that allows writing tests for functions, procedures, and other database objects.

Answer

Database Migration Service validation

Answer

Ora2Pg --test flag

Answer

Cloud SQL Insights

Question 8

An engineer needs to connect Database Migration Service to a source MySQL database that has only a private IP address. The Cloud SQL destination is also private. What is the recommended method for DMS to connect to the source?

Accepted Answer

Use VPC peering or Cloud SQL Auth Proxy. For private IP sources, VPC peering or Cloud SQL Auth Proxy can be used. Auth Proxy is recommended for secure connectivity without public IP exposure.

Answer

Use a Cloud VPN tunnel with static IP

Answer

Use Cloud NAT to provide outbound connectivity

Answer

Use the source database's public IP with IP allowlisting

Question 9

During a one-time (non-continuous) migration from MySQL to Cloud SQL using mysqldump, the engineer wants to ensure the dump does not lock InnoDB tables. Which flag should be included in the mysqldump command?

Accepted Answer

--single-transaction. --single-transaction starts a transaction at the time of dump, ensuring a consistent snapshot without locking tables (for InnoDB).

Answer

--skip-lock-tables

Answer

--flush-logs

Answer

--lock-all-tables

Question 10

Which data type mapping is correct when converting an Oracle NUMBER(10,2) column to PostgreSQL?

Accepted Answer

NUMERIC(10,2). NUMBER(p,s) maps to NUMERIC(p,s) in PostgreSQL. NUMBER(10,2) becomes NUMERIC(10,2).

Answer

INTEGER

Answer

DECIMAL(10)

Answer

REAL

Question 11

A company is migrating a 2 TB PostgreSQL database to AlloyDB using Database Migration Service. They need to minimize downtime. After the initial full dump, the CDC lag remains high for hours. What should the engineer check first?

Accepted Answer

Source database resource utilization (CPU, IO). High CDC lag often indicates resource constraints on the source or network bandwidth. Source database performance (CPU, IO) impacts replication speed.

Answer

Cloud SQL Auth Proxy configuration

Answer

Binary log retention period

Answer

AlloyDB cluster size

Question 12

An engineer is migrating from on-premises PostgreSQL to Cloud SQL for PostgreSQL using pg_dump. Which flags should be used to avoid errors related to roles and privileges when importing into Cloud SQL?

Accepted Answer

--no-owner --no-acl. Cloud SQL does not allow superuser access; --no-owner and --no-acl prevent errors from missing roles or privileges.

Answer

--schema-only --no-data

Answer

--clean --if-exists

Answer

--inserts --column-inserts

Question 13

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

Accepted Answer

Database Migration Service. Ora2Pg converts schemas; DMS can handle data migration with continuous replication from Oracle (using logical replication) to Cloud SQL.

Answer

Cloud SQL for MySQL

Answer

BigQuery Data Transfer Service

Answer

gcloud bigquery load

Question 14

An organization is migrating from Teradata to BigQuery. They have 20 TB of data, complex BTEQ scripts, and require near-zero downtime. Which THREE components should be part of their migration plan? (Choose THREE.)

Accepted Answer

Schema Conversion Tool (SCTS). Schema Conversion Tool (SCTS) is correct because it automates the conversion of Teradata-specific DDL, BTEQ scripts, and stored procedures into BigQuery-compatible SQL. This is critical for migrating 20 TB of data and complex BTEQ scripts without manual rewriting, reducing errors and accelerating the migration timeline.

Answer

Cloud SQL Auth Proxy

Answer

Database Migration Service (DMS)

Question 15

A company is planning a cutover from an on-premises MySQL database to Cloud SQL after a DMS continuous migration. To ensure minimal downtime and a successful cutover, which TWO actions should be part of the cutover procedure? (Choose TWO.)

Accepted Answer

Stop all writes to the source database.. Before cutover, quiesce writes to source and confirm DMS lag is zero to avoid data loss.

Answer

Increase the source database's CPU.

Answer

Delete the DMS migration job immediately.

Answer

Enable binary logging on Cloud SQL.