PCDOE Migrate Database Solutions Practice Test 5 — 15 Questions

Question 1

An organization is migrating a MySQL database to Cloud SQL using Database Migration Service (DMS). They need to ensure minimal downtime and continuous replication of changes from the source. Which type of migration job should they create?

Accepted Answer

Continuous migration job (CDC). Continuous (CDC) migration jobs replicate ongoing changes after the initial dump, allowing minimal downtime during cutover. One-time jobs only perform a full dump and import without ongoing replication.

Answer

Batch migration job

Answer

One-time migration job

Answer

Snapshots-only job

Question 2

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 3

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 uses a transaction to get a consistent snapshot without locking tables (works with InnoDB). --skip-lock-tables prevents explicit table locks. --lock-all-tables would lock tables. --master-data is for binary log coordinates but not required for consistency.

Answer

--single-transaction --master-data

Answer

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

Answer

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

Question 4

An organization is migrating an Oracle database to PostgreSQL on Compute Engine. They used Ora2Pg to migrate schema and data. After migration, they want to validate that stored procedures produce correct results. Which tool should they use for unit testing the migrated PL/pgSQL code?

Accepted Answer

pgTAP. pgTAP is a popular unit testing framework for PostgreSQL that allows writing tests for stored procedures, functions, and other database objects. It is commonly used to validate migrated code.

Answer

gcloud sql export

Answer

pg_dump

Answer

pglogical

Question 5

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) is provided by Google Cloud to automate the conversion of Teradata (and other) DDL and BTEQ scripts to BigQuery SQL. BigQuery Data Transfer Service is for loading data from SaaS applications. Ora2Pg is for Oracle to PostgreSQL. Database Migration Service is for database migrations to Cloud SQL or AlloyDB.

Answer

Database Migration Service

Answer

Ora2Pg

Answer

BigQuery Data Transfer Service

Question 6

A team is planning a migration from PostgreSQL to Cloud SQL with minimal downtime. They have set up DMS continuous migration. At cutover time, they need to ensure no data loss. What should they verify before promoting the destination?

Accepted Answer

The DMS replication lag is 0 seconds. Confirming DMS replication lag is 0 ensures all changes from the source have been applied to the destination. DMS status 'Running' is not enough. Source reads disabled would cause issues. DMS should be in a healthy state, but lag = 0 is the key.

Answer

The DMS job status is 'Running'

Answer

The destination has been promoted already

Answer

The source database is still accepting writes

Question 7

An engineer needs to migrate a PostgreSQL database to Cloud SQL. They have used pg_dump to create a dump file. Which flags should they use to avoid issues with ownership and ACLs, since Cloud SQL does not support those?

Accepted Answer

--no-owner --no-acl. --no-owner and --no-acl prevent pg_dump from including ownership and ACL commands, which are not supported on Cloud SQL. --no-privileges is not a standard flag (use --no-acl). --clean drops objects before recreating, not recommended.

Answer

--no-privileges --no-owner

Answer

--no-owner --clean

Answer

--no-acl --if-exists

Question 8

During an Oracle to PostgreSQL migration using Ora2Pg, a NUMBER(10,2) column is being mapped. Which PostgreSQL data type should be used to preserve precision and scale?

Accepted Answer

NUMERIC(10,2). NUMBER(10,2) maps directly to NUMERIC(10,2) in PostgreSQL, which preserves the exact precision and scale. INTEGER does not support scale, TEXT is for strings, and FLOAT may introduce rounding errors.

Answer

TEXT

Answer

INTEGER

Answer

FLOAT

Question 9

A company is using DMS to migrate from MySQL to Cloud SQL. During the full dump phase, the migration is taking longer than expected. Which factor most likely affects the duration of the full dump?

Accepted Answer

Size of the source database. The duration of the full dump phase primarily depends on the size of the database and the network bandwidth. The number of tables has some impact, but size is more significant. CDC lag is relevant during CDC phase, not full dump. Source engine version may affect compatibility but not duration significantly.

Answer

Source database engine version

Answer

Number of tables in the source

Answer

Replication lag during CDC

Question 10

An organization is migrating a Redshift data warehouse to BigQuery using BigQuery Data Transfer Service. They have scheduled a recurring transfer. However, they notice that some columns are not being mapped correctly. What is the most likely cause?

Accepted Answer

Incorrect column mapping configuration. BigQuery Data Transfer Service may require schema mapping, and incorrect column mapping can cause data to go to wrong columns or be dropped. Permission issues would cause failures, not incorrect mapping. Scheduled transfers don't affect mapping. Column ordering is not relevant.

Answer

Insufficient BigQuery permissions

Answer

Source column order differs from destination

Answer

Transfer schedule is too frequent

Question 11

A team is migrating an on-premises Oracle database to Cloud SQL for PostgreSQL using DMS. They have completed the schema conversion using Ora2Pg and are now setting up continuous migration. The source database is behind a firewall. Which connectivity method should they use for the source connection profile if they cannot use public IP?

Accepted Answer

Configure a VPN or Dedicated Interconnect with VPC. Since the source Oracle database is behind a firewall and cannot use a public IP, a VPN or Dedicated Interconnect with VPC provides a private, encrypted connection between the on-premises network and Google Cloud. This allows Database Migration Service (DMS) to reach the source database securely without exposing it to the public internet. DMS supports connectivity via these private network paths when public IP is not an option.

Answer

VPC peering

Answer

Cloud SQL Auth Proxy

Answer

IP allowlisting

Question 12

After promoting a Cloud SQL destination in a DMS continuous migration, the application team updates the connection strings. However, they want a rollback plan in case issues arise. What should they keep running to allow a rollback?

Accepted Answer

Keep the source database running in read-only mode. Keeping the source database running (read-only) allows reverting to it if needed. The DMS job is stopped after promotion. The destination is now the primary. Keeping both writable could cause conflicts.

Answer

Keep both databases writable

Answer

Keep the destination database in read-only mode

Answer

Keep the DMS job active

Question 13

Which TWO actions should be performed during cutover planning for a DMS continuous migration to minimize downtime?

Accepted Answer

Quiesce writes to the source database. Quiescing writes to the source ensures no new changes after the last sync. Confirming DMS replication lag is 0 ensures all changes are applied. Creating a DMS job is done earlier. Updating connection strings happens after promotion. Dropping source is not recommended for rollback.

Answer

Drop the source database

Answer

Update application connection strings to the destination

Answer

Create a new DMS migration job

Question 14

A company is migrating from PostgreSQL to AlloyDB using DMS. They need to set up logical replication on the source. Which THREE components are required for PostgreSQL logical replication?

Accepted Answer

pglogical extension. Logical replication requires a publication on the source, a subscription on the destination, and the pglogical extension (or built-in logical replication). DMS is the migration service but not a component of logical replication itself. A replication slot is created automatically.

Answer

DMS migration job

Answer

Replication slot

Question 15

Which TWO data type mappings are correct when converting Oracle data types to PostgreSQL?

Accepted Answer

Oracle CLOB → PostgreSQL TEXT. Option A is correct because Oracle's CLOB (Character Large Object) stores large variable-length character data, and PostgreSQL's TEXT type is the direct equivalent, supporting up to 1 GB of character data without the length limitations of VARCHAR(n). Both handle large strings efficiently, making this a standard mapping in database migrations.

Answer

Oracle VARCHAR2(100) → PostgreSQL CHAR(100)

Answer

Oracle NUMBER(10,2) → PostgreSQL INTEGER

Answer

Oracle DATE → PostgreSQL DATE