75 of 100 questions · Page 1/2 · Pcdoe Migrate topic · Answers revealed
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?
These flags skip owner and ACL settings that may not exist in Cloud SQL.
Why this answer
Cloud SQL does not allow superuser access; --no-owner and --no-acl prevent errors from missing roles or privileges.
An engineer is planning to migrate a PostgreSQL database to AlloyDB using Database Migration Service (DMS). The source database is version 13 and the target is AlloyDB. The migration must replicate ongoing changes with minimal downtime. Which source configuration is required to enable continuous change data capture (CDC) with DMS for PostgreSQL?
This is the correct configuration for PostgreSQL logical replication with DMS.
Why this answer
DMS for PostgreSQL continuous migration uses logical replication. The source must have the `pglogical` extension installed and configured, with the `wal_level` set to `logical`.
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.)
Converts Teradata DDL and BTEQ scripts to BigQuery SQL.
Why this answer
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.
Exam trap
Cisco often tests the distinction between migration tools for homogeneous vs. heterogeneous migrations, and the trap here is that candidates confuse Database Migration Service (DMS) as a general-purpose tool when it only supports specific source/target pairs like MySQL to Cloud SQL, not Teradata to BigQuery.
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?
SCTS includes functionality to convert BTEQ scripts to BigQuery-compatible SQL.
Why this answer
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.
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?
Continuous migration jobs replicate changes in real-time, minimizing downtime during cutover.
Why this answer
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.
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?
These flags exclude ownership and ACL commands, avoiding errors in Cloud SQL.
Why this answer
--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.
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?
Logical replication (via WAL) captures ongoing changes. Binary logging is not used in PostgreSQL, but the concept of change capture is correct.
Why this answer
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.
A company wants to migrate a self-managed PostgreSQL database to AlloyDB with minimal downtime. They plan to use Database Migration Service with continuous CDC. The source database is in a different region. Which THREE steps should they include in their cutover plan? (Choose 3 correct answers.)
Stop writes to prevent changes after cutover.
Why this answer
Cutover plan: quiesce writes to source, confirm replication lag is 0, promote the destination, update connection strings, and keep source running read-only for rollback. Testing before cutover is important but not part of the cutover plan step. DMS can work across regions but may have increased lag.
Deleting the source immediately is risky.
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?
VPC peering enables private connectivity; Cloud SQL Auth Proxy provides secure tunnel.
Why this answer
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.
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.)
An important prerequisite for DMS CDC.
Why this answer
DMS can compress data and supports CDC, reducing downtime. Manual migration gives more control over the process but may require more scripting.
Which TWO actions should be performed during cutover planning for a DMS continuous migration to minimize downtime?
Stops new changes, ensuring consistency.
Why this answer
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.
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?
Logical replication requires wal_level='logical' to decode changes; 'replica' is insufficient.
Why this answer
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.
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?
This allows rolling back to the source without data divergence.
Why this answer
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.
A data team is migrating an Oracle database to Cloud SQL for PostgreSQL. They have used Ora2Pg to convert the schema. After conversion, they notice several issues with data type mappings. Which THREE Oracle-to-PostgreSQL mappings are correct? (Choose 3)
Correct mapping: same precision and scale.
Why this answer
NUMBER(10) -> INTEGER, NUMBER(10,2) -> NUMERIC(10,2), DATE -> TIMESTAMP are correct. CLOB -> TEXT is also correct but only three needed.
An engineer is using Database Migration Service to migrate a MySQL database to Cloud SQL. They need to set up a source connection profile. Which TWO methods can the engineer use to allow DMS to connect to the source database? (Choose 2 correct answers.)
If the source uses private IP, use VPC peering to connect.
Why this answer
Option A is correct because VPC peering allows DMS to connect to a source database using private IP addresses without traversing the public internet, which is a secure and recommended method for connectivity within Google Cloud. This method establishes a direct, low-latency connection between the DMS service's VPC and the source database's VPC, provided the IP ranges do not overlap. It is particularly suitable when the source database is hosted in a different VPC or on-premises via a VPN-connected VPC.
Exam trap
Cisco often tests the distinction between connectivity methods that DMS directly supports (VPC peering and IP allowlisting) versus infrastructure components (like Cloud VPN or Auth Proxy) that are prerequisites or unrelated, leading candidates to select options that are part of the broader network setup but not valid source connection profile methods.
A company is migrating an on-premises PostgreSQL 13 database to AlloyDB for PostgreSQL using DMS continuous migration. The source is configured with logical replication using the pglogical extension. During the initial sync, the migration job fails with the error 'could not open relation with OID xxxx'. What is the most likely cause?
DDL changes invalidate the replication slot, causing the relation OID error.
Why this answer
DMS uses logical replication slots when possible. If the source has DDL changes (e.g., table dropped) after the slot is created, the replication slot may become invalid.
A team is migrating a PostgreSQL database to AlloyDB using Database Migration Service. They need to perform an initial one-time full dump without continuous replication. Which migration job type should they choose?
One-time performs a full dump and stops. This matches the requirement.
Why this answer
Database Migration Service supports two job types: 'One-time' (full dump only) and 'Continuous' (full dump + CDC). For a one-time migration without ongoing replication, select 'One-time'. Continuous includes CDC for ongoing sync.
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?
DMS uses a specific IP range for the region; this must be allowlisted on the source firewall.
Why this answer
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.
A company is performing a heterogeneous migration from Oracle to Cloud SQL for PostgreSQL using Ora2Pg. During testing, they find that a stored procedure using Oracle's PL/SQL `DBMS_OUTPUT.PUT_LINE` does not execute after conversion. What is the most likely issue, and how should it be resolved?
Correct. `DBMS_OUTPUT.PUT_LINE` is Oracle-specific; in PostgreSQL, use `RAISE NOTICE`.
Why this answer
Ora2Pg converts PL/SQL to PL/pgSQL. `DBMS_OUTPUT.PUT_LINE` in Oracle is equivalent to `RAISE NOTICE` in PostgreSQL. Ora2Pg may not automatically convert all procedural constructs, requiring manual review and adjustment.
A company is migrating their on-premises Oracle database to Cloud SQL for PostgreSQL. They want to test the converted stored procedures using unit tests. Which framework should they use?
pgTAP is a PostgreSQL testing framework for unit testing SQL functions and procedures.
Why this answer
pgTAP is a unit testing framework for PostgreSQL that can test stored procedures and functions.
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?
CURRENT_TIMESTAMP returns date and time with time zone, equivalent to Oracle's SYSDATE.
Why this answer
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.
A company is migrating their on-premises MySQL database to Cloud SQL using Database Migration Service (DMS). The source database is MySQL 5.7, and the target is a Cloud SQL MySQL 8.0 instance. The migration job is set to continuous, but after the full dump phase, the CDC phase keeps failing with the error 'binary log not found'. What is the most likely cause?
If source binary logs are purged (e.g., due to low retention), DMS cannot find them and CDC fails.
Why this answer
DMS continuous migration requires binary logging enabled on the source with row-based format, and the binary log files must be retained until they are consumed. If the source binary logs are purged before DMS reads them, CDC fails.
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?
Correct combination for consistent InnoDB snapshot without locking.
Why this answer
--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.
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?
NUMERIC(10,2) exactly matches the Oracle NUMBER(10,2) precision and scale.
Why this answer
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.
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?
This ensures all changes have been replicated and there is no data loss.
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?
wal_level must be set to 'logical' to enable logical replication slots.
Why this answer
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).
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?
Ora2Pg converts Oracle objects to PostgreSQL, and PL/SQL must be manually or automatically converted to PL/pgSQL.
Why this answer
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.
Exam trap
Cisco often tests the misconception that Database Migration Service (DMS) provides full schema conversion for Oracle-to-PostgreSQL migrations, when in fact DMS focuses on data migration and requires separate tools (like Ora2Pg) for schema and stored procedure conversion.
How to eliminate wrong answers
Option A is wrong because pg_dump is a PostgreSQL utility for exporting/importing PostgreSQL databases, not for converting Oracle schemas; it cannot read Oracle PL/SQL, and Oracle PL/SQL is not compatible with PostgreSQL without conversion. Option B is wrong because mysqldump is a MySQL/MariaDB export tool and cannot interpret Oracle's proprietary PL/SQL syntax; PL/SQL cannot be reused as-is in PostgreSQL. Option C is wrong because Database Migration Service (DMS) does not include built-in schema conversion for Oracle to Cloud SQL for PostgreSQL; DMS handles data migration but relies on external tools like Ora2Pg or manual conversion for schema and stored procedure translation.
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?
Row-based logging is required for DMS to capture all changes.
Why this answer
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.
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?
DMS automates migration with continuous CDC replication, reducing downtime and providing monitoring.
Why this answer
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.
Exam trap
Cisco often tests the distinction between one-time export/import tools (pg_dump, pg_basebackup) and managed continuous replication services (DMS), trapping candidates who assume any backup tool can be adapted for ongoing sync without understanding Cloud SQL's connectivity and managed service limitations.
How to eliminate wrong answers
Option A is wrong because pg_basebackup is a physical replication method that creates a binary copy of the database files, but Cloud SQL does not accept direct physical streaming from an external pg_basebackup process; it requires logical replication or a supported migration service. Option C is wrong because while pg_dump with --no-owner and --no-acl can export data, it does not set up ongoing replication; manually configuring logical replication slots on the source and connecting them to Cloud SQL is complex, unsupported by Cloud SQL's managed service, and does not provide a managed continuous migration job. Option D is wrong because pg_dump export/import is a one-time bulk copy that does not provide ongoing replication, and application-level dual writes introduce significant complexity, risk of data inconsistency, and do not guarantee minimal downtime during cutover.
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?
Provides the logical replication functionality (or built-in pgoutput).
Why this answer
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.
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.)
More objects increase overhead.
Why this answer
Full dump time depends on data volume, network bandwidth, CPU/memory of the source, and configuration (e.g., parallel workers).
An organization is migrating a large on-premises MySQL database to Cloud SQL using DMS. They want to minimize downtime. Which TWO configurations should they implement? (Choose 2)
Allows ongoing replication, reducing downtime.
Why this answer
Continuous migration enables CDC. Parallel dump speeds up the initial sync.
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?
SCTS converts Teradata DDL and BTEQ scripts to BigQuery SQL.
Why this answer
Schema Conversion Tool (SCTS) is designed to convert DDL and SQL scripts from sources like Teradata to BigQuery-compatible format.
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?
Proper cutover sequence: quiesce writes, verify lag is zero, promote destination, then update connection strings.
Why this answer
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.
A migration from Oracle to PostgreSQL using Ora2Pg is complete. The team wants to run unit tests on the converted stored procedures to ensure they produce correct results. Which tool is most suitable for this purpose?
pgTAP is the standard unit testing framework for PostgreSQL.
Why this answer
pgTAP is a unit testing framework for PostgreSQL that allows writing tests for functions, procedures, and other database objects. It is commonly used to validate converted PL/pgSQL code.
A company is migrating a Teradata data warehouse to BigQuery. They need to convert Teradata DDL and BTEQ scripts to BigQuery-compatible SQL. Which TWO services or tools should they use? (Choose 2 correct answers.)
SCTS converts DDL and scripts from Teradata to BigQuery.
Why this answer
Schema Conversion Tool (SCTS) is used to convert Teradata DDL to BigQuery DDL. BigQuery Data Transfer Service can be used to load data from Teradata into BigQuery. BigQuery Migration Assessment is for discovery, not conversion.
Dataflow and Dataproc are not specialized for Teradata schema conversion.
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?
Ensures all changes are replicated before promotion.
Why this answer
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.
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?
Fewer writes mean fewer changes to replicate, allowing DMS to catch up.
Why this answer
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.
A data analyst needs to load data from an on-premises Teradata system into BigQuery on a recurring daily schedule. Which Google Cloud service should they use?
Data Transfer Service supports Teradata scheduled transfers.
Why this answer
BigQuery Data Transfer Service supports scheduled transfers from Teradata (via agent) and other sources.
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?
Continuous jobs perform initial dump and then CDC.
Why this answer
DMS offers 'one-time' for single dump and 'continuous' for ongoing CDC. Continuous jobs replicate changes after dump.
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?
The logical replication slot maintains the LSN position; after restart, DMS continues from that point.
Why this answer
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.
An engineer is migrating a MySQL database to Cloud SQL using DMS. The source database uses MyISAM tables. During the migration, the full dump phase fails with a timeout. What should the engineer do to increase the likelihood of success?
InnoDB supports transactional dump with less locking.
Why this answer
MyISAM tables require table-level locking during dump. Using --single-transaction is only for InnoDB. To reduce lock contention, the engineer should schedule the migration during low-traffic periods or convert MyISAM to InnoDB before migration.
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?
VPC peering provides private connectivity between VPCs without internet exposure.
Why this answer
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.
Exam trap
The trap here is that candidates confuse VPC peering with Direct peering, assuming both are for VPC-to-VPC connections, but Direct peering is specifically for on-premises or external networks, not for connecting two Google Cloud VPCs.
How to eliminate wrong answers
Option B is wrong because Direct peering is used for on-premises networks connecting to Google Cloud via a dedicated interconnect or partner interconnect, not for connecting two VPCs within Google Cloud. Option C is wrong because Cloud SQL Auth Proxy is a client-side tool for securely connecting to Cloud SQL instances, not for connecting DMS to a self-managed PostgreSQL database in a different VPC. Option D is wrong because IP allowlisting for public IP would expose the source database to the internet, which contradicts the requirement to avoid internet exposure.
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?
DMS connects from its own IP range; allowlisting those IPs enables the connection.
Why this answer
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.
An engineer needs to migrate a 500 GB MySQL database to Cloud SQL. The source is in a private network with no public IP. The Cloud SQL instance will also use a private IP. Which connectivity method should the engineer use for Database Migration Service?
VPC peering enables private connectivity for DMS.
Why this answer
For private IP scenarios, DMS requires VPC peering between the source network and the Cloud SQL VPC. Alternatively, Cloud SQL Auth Proxy can be used if the source can connect to the proxy, but VPC peering is the recommended direct method for DMS.
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?
MyISAM tables require table locks during dump, causing slower performance and potential contention. InnoDB tables with --single-transaction allow non-blocking dumps.
Why this answer
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.
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?
SCTS automates conversion of Teradata schemas and BTEQ scripts to BigQuery.
Why this answer
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.
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.)
Validates functionality before final switch.
Why this answer
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.
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?
pgTAP is a testing framework that can validate stored procedures and functions.
Why this answer
pgTAP is a unit testing framework for PostgreSQL that allows writing tests for functions, procedures, and other database objects.
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?
REFCURSOR with OPEN FOR is the standard way to handle parameterized cursors in PL/pgSQL.
Why this answer
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.
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?
Binary logging must be enabled for DMS to support continuous (CDC) migration.
Why this answer
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.
An organization is migrating a 500 GB Oracle database to Cloud SQL for PostgreSQL using DMS with continuous CDC. The migration is in progress, and the team needs to ensure that if the migration fails, they can roll back with minimal data loss. Which two pre-migration steps should they take? (Choose 2)
Backup ensures point-in-time recovery for rollback.
Why this answer
A rollback plan should include keeping the source running (read-only during cutover) and taking a full backup before migration. Exporting from the target is not helpful for rollback. Setting up a replica on-premises is unnecessary.
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?
Read-only source allows validation and rollback if issues occur.
Why this answer
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.
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.)
Prevents new changes during cutover.
Why this answer
Before cutover, quiesce writes to source and confirm DMS lag is zero to avoid data loss.
A team is migrating a 5 TB MySQL database to Cloud SQL using DMS. The full dump phase is taking longer than expected. They suspect network bandwidth is the bottleneck. Which action can they take to improve the dump speed within DMS?
Parallel dump uses multiple threads to export data faster, improving throughput.
Why this answer
DMS uses a single-threaded dump by default. Enabling parallel dump can improve speed for large databases.
An organization is migrating a Teradata data warehouse to BigQuery. They need to convert existing Teradata DDL and BTEQ scripts to BigQuery SQL. Which Google Cloud service should they use for schema conversion?
SCT is designed for heterogeneous schema conversion, including Teradata to BigQuery.
Why this answer
Schema Conversion Tool (SCT) (now part of Database Migration Service) converts DDL and scripts from sources like Teradata to BigQuery. BigQuery Data Transfer Service handles data loading, not schema conversion.
A company plans to migrate a MySQL database to Cloud SQL with minimal downtime. They use Database Migration Service with continuous CDC. After starting the migration, the initial full dump completes, and CDC replication begins. The application team needs to cut over during a maintenance window. What must the engineer do just before promoting the destination to ensure no data loss?
This is the correct procedure: stop writes, verify zero lag, then promote to cutover cleanly.
Why this answer
Before promoting the destination, the engineer must verify that the replication lag is zero, meaning all changes from the source have been applied to the destination. This ensures no data loss during cutover. After confirming zero lag, the source should be quiesced (writes stopped) to prevent further changes, then the destination can be promoted.
A company is migrating an Oracle database to Cloud SQL for PostgreSQL. They have a table with a column defined as NUMBER(10,2). To maintain data integrity, what should be the corresponding PostgreSQL data type?
NUMERIC(10,2) matches the precision and scale of NUMBER(10,2).
Why this answer
NUMBER(10,2) represents a decimal number with 10 digits total and 2 after decimal; NUMERIC(10,2) is the equivalent in PostgreSQL.
An organization is planning a database migration from Oracle to PostgreSQL on Cloud SQL. They have a large number of stored procedures that use Oracle-specific PL/SQL features. Which tool should they use to automate the schema conversion, including conversion of PL/SQL to PL/pgSQL?
Ora2Pg is designed to convert Oracle schemas and objects to PostgreSQL.
Why this answer
Ora2Pg is an open-source tool specifically designed to automate the migration of Oracle databases to PostgreSQL, including the conversion of Oracle-specific PL/SQL stored procedures into PL/pgSQL. It handles schema objects, data types, and procedural code, making it the correct choice for this scenario where PL/SQL conversion is a key requirement.
Exam trap
Cisco often tests the misconception that Database Migration Service (DMS) can handle all aspects of migration including PL/SQL conversion, but DMS primarily handles data transfer and schema creation, not procedural code conversion, which requires a specialized tool like Ora2Pg.
How to eliminate wrong answers
Option B (pglogical) is wrong because it is a PostgreSQL extension for logical replication, not a schema or PL/SQL conversion tool; it replicates data changes between PostgreSQL databases but cannot convert Oracle PL/SQL. Option C (pg_dump) is wrong because it is a utility for backing up and restoring PostgreSQL databases, not for converting Oracle schemas or PL/SQL code. Option D (Database Migration Service) is wrong because while DMS can migrate data from Oracle to Cloud SQL, it does not automate the conversion of PL/SQL to PL/pgSQL; it relies on other tools like Ora2Pg or manual rewriting for stored procedure conversion.
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?
Larger databases take longer to dump and transfer.
Why this answer
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.
During a MySQL to Cloud SQL migration using DMS, the migration job fails during the full dump phase with an error indicating 'Access denied for user'. The DMS connection profile to the source was created with a user that has only SELECT privileges. What additional privilege is required for the full dump?
RELOAD is needed for FLUSH operations during mysqldump.
Why this answer
DMS full dump uses mysqldump, which requires the RELOAD privilege to flush tables and the LOCK TABLES privilege for consistency. Without RELOAD, the dump cannot proceed.
A company is migrating a PostgreSQL database to Cloud SQL using DMS with continuous CDC. During cutover, the engineer checks DMS metrics and sees the replication lag is consistently 0 seconds. What is the next step to complete the migration?
Promoting makes Cloud SQL the primary and stops replication.
Why this answer
When the lag is 0, the source and target are in sync. The next step is to promote the destination (Cloud SQL) to make it the primary, which stops replication and allows writes.
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?
This provides secure private connectivity from on-premises to GCP.
Why this answer
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.
Exam trap
Cisco often tests the distinction between VPC peering (which only works between Google Cloud VPCs) and hybrid connectivity options like VPN or Interconnect (which connect on-premises to Google Cloud), leading candidates to mistakenly choose VPC peering for on-premises scenarios.
How to eliminate wrong answers
Option B (VPC peering) is wrong because VPC peering connects two VPCs within Google Cloud, not an on-premises network; it cannot bridge the on-premises firewall. Option C (Cloud SQL Auth Proxy) is wrong because it is a client-side tool for connecting to Cloud SQL instances, not for connecting DMS to an external source database. Option D (IP allowlisting) is wrong because it requires the source database to have a public IP, which is explicitly not available in this scenario.
Which TWO data type mappings are correct when converting Oracle data types to PostgreSQL?
Correct mapping for large character objects.
Why this answer
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.
Exam trap
Cisco often tests the misconception that Oracle's DATE and PostgreSQL's DATE are equivalent, when in fact Oracle's DATE includes time components, and candidates may overlook the need to map to TIMESTAMP.
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?
These flags provide a consistent snapshot without locking tables for InnoDB.
Why this answer
--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.
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?
pgTAP is the standard unit testing framework for PostgreSQL.
Why this answer
pgTAP is a unit testing framework for PostgreSQL that allows writing tests in SQL/PL/pgSQL. It is suitable for testing stored procedures.
A company is planning a migration from an on-premises SQL Server database to Cloud SQL for PostgreSQL. They need to convert both schema and data. Which two Google Cloud services should they consider? (Choose 2)
SCT converts SQL Server schema to PostgreSQL.
Why this answer
Database Migration Service (DMS) handles data migration and can assist with schema conversion for homogeneous migrations. However, for heterogeneous migrations like SQL Server to PostgreSQL, Schema Conversion Tool (SCT) is needed for schema conversion. Cloud SQL is the target.
BigQuery and Cloud Spanner are not applicable here.
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?
SCTS is purpose-built for converting schemas and scripts from Teradata and other sources to BigQuery.
Why this answer
Schema Conversion Tool (SCTS) is designed for heterogeneous migrations, converting DDL and scripts from sources like Teradata to BigQuery.
An engineer is migrating a MySQL database to Cloud SQL using mysqldump. The source database uses InnoDB tables. Which TWO mysqldump options should the engineer use to perform a consistent online backup without locking tables? (Choose 2 correct answers.)
Prevents mysqldump from buffering entire table in memory; helpful large tables.
Why this answer
--single-transaction starts a transaction to get a consistent snapshot without locking. --quick prevents memory buffering. --skip-lock-tables avoids table locks, but --single-transaction already does that. --no-data exports only schema. --routines exports stored procedures but not consistency.
A company wants to migrate a 10 TB Redshift data warehouse to BigQuery with minimal downtime. Which combination of services should they use?
BQ DTS supports Redshift as a source, enabling both initial and scheduled incremental loads.
Why this answer
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.
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?
These flags omit ownership and ACL commands, which are not supported by Cloud SQL.
Why this answer
Cloud SQL does not allow setting ownership or ACLs; using --no-owner and --no-acl prevents errors during restore.
A company is migrating a MySQL database to Cloud SQL using Database Migration Service (DMS). The source database is on-premises and uses InnoDB tables. The migration job is configured as continuous (CDC). After starting the job, the full dump phase completes successfully, but the CDC phase shows no replicated changes. What is the most likely cause?
Correct. DMS CDC requires binary logging on the source to capture ongoing changes.
Why this answer
Continuous CDC replication in DMS requires binary logging to be enabled on the source database. If binary logging is disabled, DMS cannot capture change data. The full dump works because it uses mysqldump, which does not rely on binary logs.
A team is migrating a MySQL database to Cloud SQL using mysqldump for the initial snapshot. The source database uses InnoDB tables. Which mysqldump flags should be used to ensure a consistent snapshot without locking tables?
--single-transaction provides a consistent snapshot; --skip-lock-tables avoids additional locks.
Why this answer
--single-transaction uses a read transaction to get a consistent snapshot without table locks for InnoDB.
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?
Correct order: stop writes, ensure all changes replicated, then promote and switch.
Why this answer
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.
An organization needs to continuously replicate data from an on-premises PostgreSQL database to AlloyDB with minimal downtime during cutover. They have set up DMS continuous migration. During the cutover phase, what is the correct sequence of steps?
This is the correct cutover sequence.
Why this answer
The standard cutover: quiesce writes, verify DMS lag is 0, promote (stop replication), update connection strings, verify app, and keep source for rollback.
Ready to test yourself?
Try a timed practice session using only Pcdoe Migrate questions.