PCDOE Migrate Database Solutions Practice Test 6 — 15 Questions

Question 1

A company is migrating a MySQL 5.7 database to Cloud SQL for MySQL 8.0 using Database Migration Service (DMS). The source database has binary logging disabled. The team needs to perform a continuous migration with minimal downtime. What must be done before creating the DMS migration job?

Accepted Answer

Enable binary logging on the source MySQL instance.. DMS continuous migration requires binary logging enabled on the source to capture ongoing changes via CDC. Without it, DMS can only perform a one-time dump. Thus, enabling binary logging is a prerequisite.

Answer

Create a Cloud SQL Auth Proxy connection to the source.

Answer

Create a service account with Cloud SQL Admin role.

Answer

Set up VPC peering between the source network and Cloud SQL.

Question 2

A team is migrating an on-premises PostgreSQL 13 database to AlloyDB using DMS continuous migration. During the CDC phase, the migration job shows an error: 'ERROR: could not start WAL streaming: ERROR: replication slots are not enabled on the source.' The source is running in Google Cloud Compute Engine. What is the most likely cause?

Accepted Answer

The source database parameter wal_level is set to 'replica' instead of 'logical'.. DMS continuous migration for PostgreSQL requires logical replication, which uses replication slots. The error indicates that the source database does not have the necessary configuration to support replication slots (wal_level=logical, max_replication_slots > 0).

Answer

The DMS connection profile uses the wrong public IP.

Answer

The source database is in read-only mode.

Answer

The source database does not have the pglogical extension installed.

Question 3

An organization needs to migrate a 2 TB Oracle database to Cloud SQL for PostgreSQL. The schema has many stored procedures using PL/SQL. Which tool should they use for schema conversion and what is a key consideration when converting PL/SQL?

Accepted Answer

Use Ora2Pg for schema conversion; PL/SQL must be rewritten to PL/pgSQL.. Option D is correct because Ora2Pg is an open-source tool specifically designed to convert Oracle schemas (including PL/SQL stored procedures, functions, and triggers) to PostgreSQL-compatible PL/pgSQL. Since Oracle's PL/SQL and PostgreSQL's PL/pgSQL have significant syntactic and semantic differences (e.g., exception handling, cursor syntax, package handling), manual rewriting or tool-assisted conversion is required; Ora2Pg automates much of this conversion but still requires manual review and adjustment for complex logic.

Answer

Use pg_dump with --no-owner flag; Oracle PL/SQL is compatible with PostgreSQL.

Answer

Use mysqldump to export Oracle schema; PL/SQL can be reused as-is.

Answer

Use Database Migration Service (DMS) with built-in schema conversion; PL/SQL is automatically converted.

Question 4

A company is migrating from an on-premises Teradata data warehouse to BigQuery using the Schema Conversion Tool (SCTS). Which of the following is correctly handled by SCTS?

Accepted Answer

Converts Teradata BTEQ scripts to BigQuery SQL syntax.. SCTS can convert Teradata DDL (e.g., CREATE TABLE) to BigQuery SQL, and also helps convert BTEQ scripts (Teradata scripting language) to BigQuery SQL. However, it does not handle data migration or performance tuning.

Answer

Converts stored procedures from Teradata PL/SQL to BigQuery SQL.

Answer

Automatically re-partitions tables based on BigQuery best practices.

Answer

Migrates actual data rows from Teradata to BigQuery.

Question 5

A team is planning a one-time migration of a 500 GB MySQL database to Cloud SQL using Database Migration Service. They want to minimize the impact on the source database. Which mysqldump flags should they use when taking the initial snapshot?

Accepted Answer

--single-transaction and --skip-lock-tables. For a consistent snapshot without locking InnoDB tables, use --single-transaction. --skip-lock-tables avoids table locks. --master-data is not needed for DMS one-time migration.

Answer

--master-data=2 and --single-transaction

Answer

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

Answer

--all-databases and --routines

Question 6

An organization is performing a DMS continuous migration from PostgreSQL to Cloud SQL. The migration job is in the CDC phase. During a planned maintenance window, the source database is restarted. After restart, the DMS job continues to replicate but reports a lag. What is the impact of the restart on the migration?

Accepted Answer

The migration job will resume automatically from where it stopped.. Once the replication slot is established, a restart does not require a full re-sync. The slot retains the position, and DMS will resume from the last committed transaction. Autovacuum may affect performance but does not stop replication.

Answer

The migration job will switch to one-time migration mode.

Answer

The migration job will fail and require a new full dump.

Answer

The migration job will continue but with increased latency until all WAL is replayed.

Question 7

A company wants to migrate a 10 TB Redshift data warehouse to BigQuery with minimal downtime. Which combination of services should they use?

Accepted Answer

Use BigQuery Data Transfer Service for Redshift for initial and incremental loads.. BigQuery Data Transfer Service can directly load data from Redshift. Schema Conversion Tool (SCTS) helps convert DDL. For minimal downtime, an initial load plus CDC or incremental loads can be used.

Answer

Use DMS with a Redshift source endpoint.

Answer

Export Redshift data to CSV and load using bq load.

Answer

Use Cloud Dataflow to stream from Redshift to BigQuery.

Question 8

An engineer is performing a manual migration from PostgreSQL to Cloud SQL. They run pg_dump and want to import the dump into Cloud SQL. Which pg_dump flags are necessary to avoid errors related to ownership and ACLs?

Accepted Answer

--no-owner and --no-acl. Cloud SQL does not allow setting ownership or ACLs; using --no-owner and --no-acl prevents errors during restore.

Answer

--format=custom and --compress=9

Answer

--schema-only and --data-only

Answer

--create and --clean

Question 9

A team is migrating an on-premises MySQL database to Cloud SQL using DMS with continuous CDC. The source database uses public IP. The DMS connection profile for the source requires IP allowlisting. Which IP addresses should be allowlisted?

Accepted Answer

The IP address of the DMS service in the region of the destination.. DMS uses a managed VPC network; the outgoing IPs come from the region where the DMS job runs. These are published as a range and must be allowlisted on the source.

Answer

The IP address of the Cloud SQL Auth Proxy.

Answer

The public IP of the Cloud SQL instance.

Answer

The private IP range of the destination VPC.

Question 10

During a DMS continuous migration from MySQL to Cloud SQL, the full dump phase completed successfully, but the CDC phase is failing with 'Error: binary log not found' after a few minutes. The source MySQL has binary logs enabled. What is the most likely cause?

Accepted Answer

The binary logs were purged due to a short binlog_expire_logs_seconds setting.. DMS requires that binary logs are retained until the CDC phase has consumed them. If logs expire due to a short binlog retention period (e.g., default 1 day), the migration fails. The source should have binlog retention set to 7 days or more.

Answer

The DMS connection profile has incorrect SSL settings.

Answer

The source database was restarted and binary logs were reset.

Answer

The source MySQL server has run out of disk space.

Question 11

A company is migrating Oracle to Cloud SQL for PostgreSQL using Ora2Pg. For a column defined as NUMBER(10,2) in Oracle, what is the corresponding data type in PostgreSQL?

Accepted Answer

NUMERIC(10,2). Option B is correct because Oracle's NUMBER(10,2) specifies a fixed-point number with up to 10 digits total and 2 digits after the decimal point. PostgreSQL's NUMERIC(10,2) is the direct equivalent, offering identical precision and scale semantics. Ora2Pg automatically maps NUMBER(p,s) to NUMERIC(p,s) to preserve exact numeric storage.

Answer

INTEGER

Answer

DOUBLE PRECISION

Answer

TEXT

Question 12

A team is planning the cutover for a DMS continuous migration. They want to minimize downtime. What is the correct cutover procedure?

Accepted Answer

Quiesce writes, confirm lag 0, promote destination, update connection strings, verify.. The standard cutover: quiesce writes to source, verify DMS lag is 0, promote the destination, update application connection strings, then verify. Keeping source read-only allows rollback.

Answer

Update connection strings to point to destination, then promote.

Answer

Promote destination immediately, then stop source.

Answer

Stop source, promote destination, update connection strings.

Question 13

A company is migrating a 5 TB Oracle database to Cloud SQL for PostgreSQL using DMS with continuous migration. They need to minimize downtime and have a rollback plan. Which TWO actions should they include in their migration plan? (Choose TWO.)

Accepted Answer

Test the application against the destination Cloud SQL instance before cutover.. A rollback plan involves keeping the source available in read-only mode for a validation window. Testing the application against the destination also validates the migration before cutover.

Answer

Use a one-time migration job instead of continuous.

Answer

Delete the source database immediately after promoting the destination.

Answer

Skip schema conversion and use raw Oracle SQL.

Question 14

An engineer is migrating a 20 TB PostgreSQL database to AlloyDB using DMS continuous migration. The full dump phase is taking longer than expected. Which THREE factors could affect the duration of the full dump phase? (Choose THREE.)

Accepted Answer

The number of tables and indexes on the source.. Full dump time depends on data volume, network bandwidth, CPU/memory of the source, and configuration (e.g., parallel workers).

Answer

The version of the source PostgreSQL database.

Answer

The choice of SSL or non-SSL connection.

Question 15

A team is migrating a MySQL database to Cloud SQL using a manual approach (mysqldump + import) instead of DMS. They have limited bandwidth. Which TWO considerations should they take into account when choosing between DMS and manual migration? (Choose TWO.)

Accepted Answer

DMS requires the source to have binary logging enabled for continuous migration.. DMS can compress data and supports CDC, reducing downtime. Manual migration gives more control over the process but may require more scripting.

Answer

Manual migration does not support resumable uploads.

Answer

DMS does not support MySQL 8.0 as a source.

Answer

Manual migration is always faster than DMS.