PCDE Migrate data solutions Practice Test 7 — 15 Questions

Question 1

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?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Declare the cursor as CURSOR (param INT) FOR SELECT * FROM table WHERE id = param;. The correct conversion for a parameterized cursor in PL/pgSQL is to declare the cursor using the syntax 'CURSOR (param INT) FOR SELECT ...'. This is the direct equivalent of Oracle's parameterized cursor. While REFCURSOR can also be used, it is not the most direct conversion and does not declare the cursor with parameters in the same way.

Answer

Use a REFCURSOR variable and OPEN it with a query using the parameter.

Answer

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

Answer

Use a temporary table to store the query results.

Question 2

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 3

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. A continuous migration job in Database Migration Service (DMS) performs an initial full dump and then continuously replicates ongoing changes (via change data capture) from the source to the destination until the job is promoted. This is exactly what the team needs to keep the Cloud SQL target in sync after the initial load. One-time jobs stop after the initial dump and do not replicate subsequent changes.

Answer

One-time migration job

Answer

Scheduled export job

Answer

Bulk load job

Question 4

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?

Accepted Answer

Use Database Migration Service with a continuous migration job.. 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.

Answer

Create a replica of the on-premises PostgreSQL and use pg_basebackup to stream to Cloud SQL.

Answer

Export using pg_dump with --no-owner and --no-acl flags, then manually configure logical replication slots.

Answer

Use pg_dump to export the database and import it into Cloud SQL, then set up application-level dual writes.

Question 5

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?

Accepted Answer

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

Answer

BigQuery Data Transfer Service

Answer

Database Migration Service (DMS)

Answer

gcloud sql import

Question 6

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?

Accepted Answer

Schema Conversion Tool (SCTS). BigQuery Schema Conversion Tool (SCTS), part of the BigQuery Migration Service, is purpose-built to translate Teradata DDL, DML, and BTEQ scripts into BigQuery-compatible SQL. It handles Teradata-specific syntax, data types, and procedural constructs, producing a translation report and assessment. This is the only option that performs code conversion rather than data movement.

Answer

BigQuery Data Transfer Service

Answer

Database Migration Service (DMS)

Answer

gcloud bigquery load

Question 7

An engineer is planning a migration from on-premises MySQL to Cloud SQL. They want to use Database Migration Service with continuous replication. What must be enabled on the source MySQL database before starting the migration job?

Accepted Answer

Binary logging. Database Migration Service with continuous replication requires binary logging (binlog) on the source MySQL instance to capture and stream ongoing changes. Binary logs record all data modifications (INSERT, UPDATE, DELETE) in a format that Cloud SQL can replay, enabling near-real-time replication without downtime. Without binary logging enabled, only a one-time snapshot migration is possible, not continuous replication.

Answer

Error log

Answer

Slow query log

Answer

General query log

Question 8

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?

Accepted Answer

Stop the application that writes to the source, then confirm DMS lag is zero before promoting.. 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.

Answer

Adjust the DMS job to ignore further changes.

Answer

Promote the destination again; this will force a stop of writes.

Answer

Delete the source database to force the cutover.

Question 9

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?

Accepted Answer

pgTAP. pgTAP is a unit testing framework for PostgreSQL that allows writing tests for functions, procedures, and other database objects.

Answer

Database Migration Service validation

Answer

Ora2Pg --test flag

Answer

Cloud SQL Insights

Question 10

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?

Accepted Answer

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

Answer

--skip-lock-tables

Answer

--flush-logs

Answer

--lock-all-tables

Question 11

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

Accepted Answer

NUMERIC(10,2). Oracle NUMBER(10,2) specifies a fixed-point number with a total precision of 10 digits and a scale of 2 digits after the decimal point. PostgreSQL's NUMERIC(10,2) provides exactly the same semantics: up to 10 significant digits, with 2 digits to the right of the decimal. This ensures exact numeric representation and identical rounding behavior, making it the direct and correct mapping.

Answer

INTEGER

Answer

DECIMAL(10)

Answer

REAL

Question 12

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?

Accepted Answer

--no-owner --no-acl. The --no-owner and --no-acl flags tell pg_dump to omit ownership and access privilege (GRANT/REVOKE) commands from the dump. Cloud SQL for PostgreSQL does not allow superuser-level role creation or arbitrary ownership assignments, so importing a dump that includes ALTER ... OWNER TO or GRANT statements for roles that do not exist in Cloud SQL will fail. Stripping these with --no-owner --no-acl produces a dump that imports cleanly.

Answer

--schema-only --no-data

Answer

--clean --if-exists

Answer

--inserts --column-inserts

Question 13

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

Accepted Answer

Schema Conversion Tool (SCTS). 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.

Answer

Cloud SQL Auth Proxy

Answer

Database Migration Service (DMS)

Question 14

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

Accepted Answer

Stop all writes to the source database.. Option A is correct because stopping all writes to the on-premises MySQL source quiesces the database, ensuring no new transactions are generated that DMS would still need to replicate, which is essential before promoting Cloud SQL to avoid data loss during cutover. Option E is correct because verifying that DMS replication lag is zero confirms the destination Cloud SQL instance has fully caught up with the source, so the two databases are in sync at the moment of cutover. Options B, C, and D do not belong: increasing source CPU (B) does not address synchronization or downtime, deleting the DMS migration job immediately (C) would break replication before the destination is verified as current, and enabling binary logging on Cloud SQL (D) is unnecessary because Cloud SQL is the target replica, not the source, so binary logging for outbound replication is not required for the cutover.

Answer

Increase the source database's CPU.

Answer

Delete the DMS migration job immediately.

Answer

Enable binary logging on Cloud SQL.

Question 15

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?

Accepted Answer

The procedure uses `DBMS_OUTPUT.PUT_LINE`, which should be replaced with `RAISE NOTICE` in PostgreSQL.. 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.

Answer

The procedure uses `DBMS_OUTPUT.PUT_LINE`, which should be replaced with `RAISE EXCEPTION`.

Answer

The procedure uses `DBMS_OUTPUT.PUT_LINE`, which is supported natively in PostgreSQL.

Answer

The procedure must be rewritten in Python using PL/Python.