CCNA Migrate data solutions Questions — Page 2 of 2

Multi-Selectmedium

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)

Select 2 answers

A.Verify that the DMS migration job lag is 0 seconds.

B.Enable binary logging on the source.

C.Take a full backup of the source.

D.Delete the DMS migration job to stop replication.

E.Quiesce all write operations to the source database.

AnswersA, E

Ensures source and target are in sync.

Why this answer

Quiesce writes to ensure no new changes, and confirm DMS lag is 0 before promoting.

Practice this question →

MCQeasy

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?

A.Use pg_dump to export the database and then import into Cloud SQL.

B.Create a one-time migration job to copy the database during a maintenance window.

C.Create a continuous migration job with DMS using a VPC peering connection.

D.Use mysqldump to export and import the database.

AnswerC

Continuous migration allows CDC to replicate changes, minimizing downtime.

Why this answer

For minimal downtime, a continuous migration job should be used so that after the initial full dump, ongoing changes are replicated until cutover.

Practice this question →

MCQeasy

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

A.Update connection strings to point to destination, then promote.

B.Promote destination immediately, then stop source.

C.Stop source, promote destination, update connection strings.

D.Quiesce writes, confirm lag 0, promote destination, update connection strings, verify.

AnswerD

This minimizes downtime and ensures data consistency.

Why this answer

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.

Practice this question →

Multi-Selectmedium

A team is migrating a MySQL database to Cloud SQL using DMS with continuous CDC. They want to minimize downtime during cutover. Which three actions should they take as part of the cutover plan? (Choose 3)

Select 3 answers

A.Quiesce write operations on the source database.

B.Delete the migration job immediately after promotion.

C.Promote the destination Cloud SQL instance.

D.Confirm DMS replication lag is 0.

E.Disable binary logging on the source.

AnswersA, C, D

Stop writes to ensure consistency.

Why this answer

During cutover, the steps are: quiesce writes on source, confirm DMS lag is 0, promote the destination, update application connection strings, and keep the source available for rollback. Disabling binary logging or deleting the source prematurely are not part of a safe cutover.

Practice this question →

Multi-Selectmedium

A company is migrating an on-premises PostgreSQL database to Cloud SQL using Database Migration Service with continuous replication. The source database has binary logging enabled and uses the pglogical plugin. The migration job is failing after the full dump phase. Which THREE common issues should the engineer check? (Choose 3 correct answers.)

Select 3 answers

A.The source database is not configured for logical replication (e.g., wal_level is not logical).

B.Insufficient disk space on the source for WAL files.

C.Cloud SQL Auth Proxy is not installed on the source.

D.The replication slot is exhausted or not created.

E.The source database is MySQL.

AnswersA, B, D

Logical replication requires wal_level=logical.

Why this answer

For PostgreSQL CDC migration, DMS uses pglogical. Common issues include: logical replication slot not created or exhausted, insufficient disk space for WAL files, and network connectivity issues (e.g., firewall blocking replication port 5432). Cloud SQL Auth Proxy is not needed for DMS to Cloud SQL connectivity if using private IP/peering.

Source database must be PostgreSQL, not MySQL.

Practice this question →

MCQhard

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?

A.Insufficient BigQuery permissions

B.Source column order differs from destination

C.Transfer schedule is too frequent

D.Incorrect column mapping configuration

AnswerD

If the schema mapping is not correctly defined, columns may be mismapped.

Why this answer

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.

Practice this question →

MCQmedium

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?

A.The BigQuery Data Transfer Service is required for date conversion.

B.The Teradata source has a different date format that is incompatible with BigQuery.

C.The SCTS tool did not fully convert the Teradata-specific date functions to BigQuery equivalents.

D.BigQuery does not support date arithmetic.

AnswerC

Some Teradata date functions have no direct BigQuery equivalent and require manual adjustment.

Why this answer

SCTS converts syntax but may not perfectly translate all functions; manual review is often needed for specific date functions.

Practice this question →

MCQhard

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?

A.pgTAP

B.gcloud sql export

C.pg_dump

D.pglogical

AnswerA

pgTAP provides unit testing capabilities for PostgreSQL stored procedures.

Why this answer

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.

Practice this question →

MCQhard

An engineer is migrating an Oracle database to Cloud SQL for PostgreSQL. They use Ora2Pg to convert the schema. One source table uses a column with data type NUMBER(10,2). What is the appropriate PostgreSQL data type after conversion, and which tool should the engineer use to verify the correctness of converted stored procedures?

A.Use VARCHAR2 and test with pgTAP.

B.Use NUMERIC(10,2) and pgTAP to write unit tests for stored procedures.

C.Use FLOAT and rely on Ora2Pg's built-in validation.

D.Use INTEGER for the data type and PL/pgSQL for testing stored procedures.

AnswerB

NUMERIC(10,2) is the correct mapping. pgTAP is a testing framework for PostgreSQL, suitable for verifying stored procedures.

Why this answer

NUMBER(10,2) maps to NUMERIC(10,2) as it is a fixed-point number. Stored procedures converted from PL/SQL to PL/pgSQL should be tested with unit tests, and pgTAP is a popular PostgreSQL testing framework for this purpose. PL/pgSQL is the procedural language, not a testing tool.

Ora2Pg handles conversion but does not provide unit tests.

Practice this question →

Multi-Selectmedium

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?

Select 3 answers

A.NUMBER(10,2) → NUMERIC(10,2)

B.CLOB → VARCHAR

C.BLOB → BYTEA

D.DATE → DATE

E.NUMBER(10) → INTEGER

AnswersA, C, E

This mapping is correct. Oracle NUMBER(10,2) maps directly to NUMERIC(10,2) in Cloud SQL for PostgreSQL, preserving the exact numeric type.

Why this answer

Oracle NUMBER(10,2) maps to NUMERIC(10,2) in Cloud SQL for PostgreSQL because both store exact numeric values with precision and scale. Oracle NUMBER(10) maps to INTEGER because it stores whole numbers without scale. The other options are incorrect: CLOB maps to TEXT, BLOB maps to BYTEA, and Oracle DATE maps to TIMESTAMP (since Oracle DATE includes time).

Practice this question →

MCQmedium

A team is migrating a 2 TB MySQL database from on-premises to Cloud SQL. They want to minimize downtime. The source is MySQL 8.0 with InnoDB tables, and the application can be read-only during cutover. Which approach provides the lowest downtime while ensuring data consistency?

A.Use mysqldump with --single-transaction to export, then import to Cloud SQL using mysql client.

B.Use Cloud Dataflow to stream data from MySQL to Cloud SQL.

C.Use gcloud sql import command with a compressed dump file from Cloud Storage.

D.Use Database Migration Service with continuous CDC and promote when lag is zero.

AnswerD

DMS CDC minimizes downtime by replicating changes in real time.

Why this answer

DMS with continuous CDC provides near-zero downtime by replicating ongoing changes. After the initial dump, the source continues to replicate changes, allowing a quick cutover with minimal downtime.

Practice this question →

MCQeasy

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?

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

B.--lock-all-tables and --flush-logs

C.--single-transaction and --skip-lock-tables

D.--all-databases and --routines

AnswerC

These flags allow a consistent snapshot without locking tables.

Why this answer

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.

Practice this question →

MCQeasy

A company is migrating an on-premises Oracle database to Cloud SQL for PostgreSQL. They have used Ora2Pg to convert the schema. Which of the following data type conversions is correct?

A.Oracle NUMBER(10) → PostgreSQL NUMERIC(10)

B.Oracle CLOB → PostgreSQL TEXT

C.Oracle NUMBER(10,2) → PostgreSQL INTEGER

D.Oracle DATE → PostgreSQL DATE

AnswerB

CLOB is large character object, maps to TEXT.

Why this answer

Oracle's DATE includes both date and time, so the correct mapping is to PostgreSQL TIMESTAMP. NUMBER(10) maps to INTEGER, NUMBER(10,2) to NUMERIC(10,2), and CLOB to TEXT.

Practice this question →

MCQmedium

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?

A.--skip-lock-tables

B.--flush-logs

C.--single-transaction

D.--lock-all-tables

AnswerC

This flag ensures a consistent read without locking InnoDB tables.

Why this answer

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

Practice this question →

MCQeasy

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

A.NUMERIC(10,2)

B.INTEGER

C.DECIMAL(10)

D.REAL

AnswerA

NUMERIC maps directly to NUMBER with same precision and scale.

Why this answer

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

Practice this question →

Multi-Selectmedium

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

Select 2 answers

A.Database Migration Service

B.Cloud SQL for MySQL

C.BigQuery Data Transfer Service

D.gcloud bigquery load

E.Ora2Pg

AnswersA, E

DMS supports Oracle to Cloud SQL for PostgreSQL migration with continuous CDC.

Why this answer

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

Practice this question →

MCQhard

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?

A.The binary logs were purged due to a short binlog_expire_logs_seconds setting.

B.The DMS connection profile has incorrect SSL settings.

C.The source database was restarted and binary logs were reset.

D.The source MySQL server has run out of disk space.

AnswerA

If logs expire before DMS consumes them, DMS cannot continue CDC.

Why this answer

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.

Practice this question →

Multi-Selecthard

An engineer is manually migrating a MySQL database to Cloud SQL using mysqldump and import. They need to capture the binary log position to enable CDC with Database Migration Service later. Which TWO mysqldump flags should they include to ensure a consistent snapshot and capture the log position? (Choose 2 correct answers.)

Select 2 answers

A.--no-data

B.--single-transaction

C.--master-data=2

D.--skip-lock-tables

E.--triggers

AnswersB, C

This flag ensures a consistent snapshot without locking tables.

Why this answer

--single-transaction ensures a consistent snapshot by starting a transaction and avoiding table locks (for InnoDB). --master-data=2 writes the binary log position as a comment in the dump file (value 2). --skip-lock-tables is not needed because --single-transaction handles consistency. --triggers exports triggers but not binary log position. --no-data excludes data.

Practice this question →

MCQmedium

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?

A.Ora2Pg

B.BigQuery Data Transfer Service

C.Database Migration Service (DMS)

D.gcloud sql import

AnswerA

Ora2Pg is designed for Oracle-to-PostgreSQL schema conversion, including stored procedures.

Why this answer

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

Practice this question →

Multi-Selectmedium

A team is planning a migration from Oracle to PostgreSQL on Cloud SQL. They use Ora2Pg to convert the schema. Which THREE Oracle data types require special attention for correct mapping to avoid data loss or precision issues? (Choose 3 correct answers.)

Select 3 answers

A.DATE

B.NUMBER(10,2)

C.FLOAT

D.CLOB

E.VARCHAR2

AnswersA, B, D

Oracle DATE includes time; maps to TIMESTAMP, not DATE.

Why this answer

NUMBER(10,2) maps to NUMERIC(10,2) (exact). DATE in Oracle includes time, so it maps to TIMESTAMP. CLOB maps to TEXT.

VARCHAR2 maps to VARCHAR. FLOAT maps to double precision, but NUMBER(10) without decimal maps to INTEGER. The tricky ones are DATE (includes time), NUMBER with precision (exact numeric), and CLOB (large text).

Practice this question →

MCQmedium

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?

A.INTEGER

B.NUMERIC(10,2)

C.DOUBLE PRECISION

D.TEXT

AnswerB

NUMERIC(10,2) matches the precision and scale.

Why this answer

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.

Exam trap

A common misconception in Google Cloud migrations is that NUMBER(p,s) maps to DOUBLE PRECISION or FLOAT. Candidates often confuse fixed-point and floating-point semantics, especially when they see 'NUMBER' and assume it's a generic numeric type instead of recognizing the exact precision and scale specified.

How to eliminate wrong answers

Option A is wrong because INTEGER stores only whole numbers without decimal places, losing the fractional precision required by NUMBER(10,2). Option C is wrong because DOUBLE PRECISION is a floating-point type that can introduce rounding errors for exact decimal values, unlike the fixed-point NUMERIC. Option D is wrong because TEXT is a variable-length string type, completely unsuitable for storing numeric data with precision and scale constraints.

Practice this question →