PCDOE Migrate Database Solutions Practice Test 2 — 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 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 2

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 3

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 4

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 5

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

Question 6

An engineer is manually migrating a MySQL database to Cloud SQL using mysqldump and mysql import. They need to ensure the dump captures a consistent snapshot without locking InnoDB tables. Which mysqldump flags should they use?

Accepted Answer

--single-transaction --skip-lock-tables. --single-transaction uses a transaction to get a consistent snapshot for InnoDB without locking. --skip-lock-tables prevents table locks. Together they achieve consistent backup without disrupting writes.

Answer

--single-transaction --lock-tables

Answer

--lock-tables --skip-lock-tables

Answer

--all-databases --single-transaction

Question 7

During a cutover from an on-premises Oracle database to Cloud SQL for PostgreSQL, the team needs to minimize downtime. They have set up continuous replication using a custom tool. Which sequence of steps should they follow to perform the cutover with minimal data loss?

Accepted Answer

Quiesce writes, verify lag is 0, promote destination, update connection strings, verify application.. The correct sequence: quiesce writes to source (stop application writes), verify replication lag is zero (all changes replicated), promote destination (make it writable), update connection strings, then verify the application. Rolling back involves keeping source read-only.

Answer

Quiesce writes, promote destination, verify lag, update connection strings.

Answer

Promote destination, quiesce writes, verify lag, update connection strings, verify application.

Answer

Update connection strings, quiesce writes, verify lag, promote destination.

Question 8

A company is migrating a PostgreSQL database to Cloud SQL using DMS. The source database has binary logging enabled for the migration. What is the purpose of binary logging in this context?

Accepted Answer

To capture changes for continuous replication after the initial dump.. DMS uses PostgreSQL logical replication, not binary logs. The question incorrectly mentions binary logs; PostgreSQL uses WAL with logical decoding. However, for MySQL, binary logs are used for CDC. This question tests understanding that binary logging is a MySQL concept; the correct answer is that for PostgreSQL, logical replication is used via publication/slot.

Answer

To enable point-in-time recovery on the source.

Answer

To improve performance of the initial dump.

Answer

To convert data types between source and destination.

Question 9

An organization is migrating a MySQL database to Cloud SQL using DMS with continuous replication. After promoting the destination, they need a rollback plan. Which approach should they use to enable a quick rollback if issues arise?

Accepted Answer

Keep the source database running in read-only mode for a few days.. A rollback plan should keep the source database running but read-only for a validation window. This allows switching back if needed without data loss.

Answer

Delete the source database immediately after promotion.

Answer

Take a snapshot of the source database before promotion.

Answer

Enable binary logging on the Cloud SQL instance after promotion.

Question 10

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

Use a REFCURSOR variable and OPEN it with a query using the parameter.. PL/pgSQL does not support parameterized cursors directly in the same way; the correct approach is to declare a REFCURSOR and then OPEN it with a SELECT using the parameters.

Answer

Replace the cursor with a FOR loop that returns a set of rows.

Answer

Use a temporary table to store the query results.

Answer

Declare the cursor as CURSOR (param INT) FOR SELECT * FROM table WHERE id = param;

Question 11

A company is migrating a PostgreSQL database to AlloyDB using DMS. They need to test the converted stored procedures. Which tool should they use to write and run unit tests for PL/pgSQL functions?

Accepted Answer

pgTAP. pgTAP is a unit testing framework for PostgreSQL that allows writing tests in SQL/PL/pgSQL. It is suitable for testing stored procedures.

Answer

Cloud SQL Insights

Answer

pgAdmin

Answer

pg_stat_statements

Question 12

A team is migrating an on-premises PostgreSQL database to Cloud SQL using DMS. They want the migration job to continuously replicate changes after the initial dump. Which type of migration job should they create?

Accepted Answer

Continuous migration job. DMS offers 'one-time' for single dump and 'continuous' for ongoing CDC. Continuous jobs replicate changes after dump.

Answer

One-time migration job

Answer

Scheduled export job

Answer

Bulk load job

Question 13

A company is migrating an Oracle database to Cloud SQL for PostgreSQL. They need to convert data types. Which TWO data type mappings are correct?

Accepted Answer

NUMBER(10,2) → NUMERIC(10,2). Oracle NUMBER(10) maps to INTEGER, and CLOB maps to TEXT. BLOB maps to BYTEA, not VARCHAR. DATE maps to TIMESTAMP, not DATE because Oracle DATE includes time.

Answer

CLOB → VARCHAR

Answer

BLOB → BYTEA

Answer

DATE → DATE

Question 14

During a cutover from MySQL to Cloud SQL, the team must minimize downtime. Which THREE steps are essential for a successful cutover with minimal data loss?

Accepted Answer

Verify that DMS replication lag is zero.. Quiesce writes to stop changes, verify replication lag is zero to ensure all changes are applied, then promote the destination to make it writable. Updating connection strings and testing are also needed but the three essential steps for data integrity are quiescing, verifying lag, and promoting.

Answer

Enable point-in-time recovery on the destination.

Answer

Take a full backup of the source after quiescing.

Question 15

A team is migrating a MySQL database to Cloud SQL using DMS. They need to configure the source database for binary logging. Which TWO parameters must be set correctly?

Accepted Answer

binlog_format = ROW. For MySQL binary logging to work with DMS, log_bin must be ON and binlog_format must be ROW (or MIXED). ROW-level logging ensures all changes are captured.

Answer

expire_logs_days = 0

Answer

binlog_format = STATEMENT

Answer

server_id = 1