Migración de Oracle a Postgres Paso a Paso: El Esquema SH (Particiones, Vistas Materializadas, Índices Bitmap)

El esquema SH (Sales History) de Oracle es el ejemplo estándar de Oracle de un almacén de datos de esquema en estrella.

Tiene una tabla de hechos CENTRAL DE VENTAS, seis tablas de dimensiones, dos vistas materializadas, tablas de hechos particionadas por rango e índices bitmap en columnas de baja cardinalidad.

Todas esas características existen en cada almacén de datos real de Oracle.

Y todas ellas requieren decisiones que un directo ora2pg correr no servirá para ti.

Esta publicación detalla los pasos exactos que utilicé para migrar SH de Oracle 19c a PostgreSQL 18 utilizando ora2pg.

El esquema de ejemplo SH de Oracle está disponible en GitHub.

¿Qué contiene el SO?

SH modela un almacén de datos de ventas minoristas.

Tablas de dimensión: CANALES, PAÍSES, CLIENTES, PRODUCTOS, PROMOCIONES, TIEMPOS

Tablas de hechos: VENTAS, COSTOS — ambos particionados por rangos por TIME_ID

Vistas materializadas: CAL_MES_VENTAS_MV, FWEEK_PSCAT_VENTAS_MV

Volumen de datos:

La tabla de VENTAS con casi un millón de filas hace que la exportación y carga de COPIAR tome entre 15 y 25 minutos.

Planea eso antes de empezar.

Problema 1: Las tablas particionadas requieren dos exportaciones separadas

SALES y COSTS están particionados por rangos según TIME_ID en Oracle.

PostgreSQL admite el particionamiento declarativo por rangos con las mismas semánticas.

El problema es que ora2pg divide esto en dos tipos de exportación.

En MESA export genera el DDL de la tabla padre — la PARTICIONAR POR RANGO (time_id) declaración — pero sin tablas de particiones secundarias.

Si carga esto y trata de insertar filas, PostgreSQL devuelve:

ERROR: no se encontró partición de la relación "sales" para la fila

Las tablas secundarias de partición provienen de una parte separada PARTICIÓN exportar.

Necesitas ambos archivos y debes cargar el DDL de la tabla padre antes que las tablas hijas particionadas.

Así se ve en la práctica:

-- TABLE export generates:
CREATE TABLE sh.sales (
  prod_id       bigint NOT NULL,
  cust_id       bigint NOT NULL,
  time_id       date   NOT NULL,
  channel_id    bigint NOT NULL,
  promo_id      bigint NOT NULL,
  quantity_sold numeric(10,2) NOT NULL,
  amount_sold   numeric(10,2) NOT NULL
) PARTITION BY RANGE (time_id);

-- PARTITION export generates:
CREATE TABLE sales_1995 PARTITION OF sh.sales
  FOR VALUES FROM (MINVALUE) TO ('1996-01-01');
CREATE TABLE sales_1996 PARTITION OF sh.sales
  FOR VALUES FROM ('1996-01-01') TO ('1997-01-01');
-- ... 33 more partitions

El esquema SH tiene 28 particiones de VENTAS y 9 particiones de COSTOS — 37 en total.

Verificar que el recuento coincida con Oracle antes de cargar.

Problema 2: Las columnas NUMBER(38) no son manejadas por PG_INTEGER_TYPE

Cada columna de ID entera en SH se declara como desnuda NÚMERO en Oracle, que almacena internamente como NÚMERO(38).

La opción de ora2pg PG_INTEGER_TYPE 1 convierte desnudo NÚMERO (sin precisión) para entero largo.

Pero NÚMERO(38) tiene una precisión explícita — ora2pg la trata como un tipo numérico, no un entero, y la mapea a numérico(38).

A numérico(38) la columna funciona pero es incorrecta para una columna de clave primaria o clave foránea.

Es 8 veces más grande que entero largo, más lento de indexar e imposible de usar con operaciones específicas de enteros.

La solución es MODIFICAR_TIPO — una directiva de una sola línea en ora2pg.conf que fuerza un tipo específico para columnas específicas.

Regla crítica: MODIFICAR_TIPO es una sola línea.

Todas las anulaciones van en una línea, separadas por comas.

Si escribes múltiples MODIFICAR_TIPO líneas, ora2pg usa solo el primero e ignora silenciosamente el resto.

Ejecutar MOSTRAR_COLUMNA primero en identificar cada numérico(38) columna

ora2pg -t SHOW_COLUMN > /home/fernando/ora2pg-sh/output/columns-sh.txt
grep 'numeric(38)' /home/fernando/ora2pg-sh/output/columns-sh.txt

Luego, agrega todas las anulaciones a ora2pg.conf en una línea:

MODIFY_TYPE  CUSTOMERS:CUST_ID:bigint,CUSTOMERS:CUST_CITY_ID:bigint,CUSTOMERS:CUST_STATE_PROVINCE_ID:bigint,CUSTOMERS:CUST_TOTAL_ID:bigint,CUSTOMERS:CUST_SRC_ID:bigint,CHANNELS:CHANNEL_ID:bigint,CHANNELS:CHANNEL_CLASS_ID:bigint,CHANNELS:CHANNEL_TOTAL_ID:bigint,COUNTRIES:COUNTRY_ID:bigint,COUNTRIES:COUNTRY_SUBREGION_ID:bigint,COUNTRIES:COUNTRY_REGION_ID:bigint,COUNTRIES:COUNTRY_TOTAL_ID:bigint,PRODUCTS:PROD_ID:bigint,PRODUCTS:PROD_SUBCATEGORY_ID:bigint,PRODUCTS:PROD_CATEGORY_ID:bigint,PRODUCTS:SUPPLIER_ID:bigint,PRODUCTS:PROD_TOTAL_ID:bigint,PRODUCTS:PROD_SRC_ID:bigint,PROMOTIONS:PROMO_ID:bigint,PROMOTIONS:PROMO_SUBCATEGORY_ID:bigint,PROMOTIONS:PROMO_CATEGORY_ID:bigint,PROMOTIONS:PROMO_TOTAL_ID:bigint,SALES:PROD_ID:bigint,SALES:CUST_ID:bigint,SALES:CHANNEL_ID:bigint,SALES:PROMO_ID:bigint,COSTS:PROD_ID:bigint,COSTS:PROMO_ID:bigint,COSTS:CHANNEL_ID:bigint

Antes de ejecutar cualquier exportación, confirme que hay exactamente un activo MODIFICAR_TIPO línea:

grep 'MODIFY_TYPE' /etc/ora2pg/ora2pg.conf | grep -v '^\s*#' | wc -l
# Must be: 1

Problema 3: Índices de mapas de bits — Conservar, Eliminar o Reemplazar

Oracle utiliza índices bitmap en columnas de baja cardinalidad en SH: CHANNEL_ID (5 valores), PROMO_ID (503 valores) y TIME_ID.

PostgreSQL no tiene un tipo de índice de mapa de bits.

Por defecto, ora2pg convierte los índices bitmap en índices GIN (BITMAP_COMO_GIN 1).

Eso está mal para estas columnas — GIN está diseñado para búsqueda de texto completo y datos de tipo array, no para claves foráneas de tipo entero.

Configurar BITMAP_COMO_GINEBRA 0 en ora2pg.conf.

Esto indica a ora2pg que convierta los índices bitmap en índices B-tree regulares, los cuales revisas individualmente.

La tabla de decisión para SH:

BRIN (Block Range Index) es un índice de rangos de bloques que almacena valores mínimos/máximos por bloque de 128 páginas.

Para una columna de series de tiempo en una tabla particionada, es mucho más barato de mantener que un B-tree y igualmente efectivo para escaneos de rangos.

Editar el archivo de exportación de la TABLA antes de cargar:

-- Delete these two lines:
CREATE INDEX sales_channel_bix ON sh.sales (channel_id);
CREATE INDEX costs_channel_bix ON sh.costs (channel_id);

-- Replace:
CREATE INDEX sales_time_bix ON sh.sales (time_id);
-- With:
CREATE INDEX sales_time_bix ON sh.sales USING BRIN (time_id);

-- Replace:
CREATE INDEX costs_time_bix ON sh.costs (time_id);
-- With:
CREATE INDEX costs_time_bix ON sh.costs USING BRIN (time_id);

Problema 4: La Actualización de la Vista Materializada Ya No Es Automática

Oracle CAL_MONTH_SALES_MV y FWEEK_PSCAT_SALES_MV usan REFRESCAR COMPLETAMENTE BAJO DEMANDA.

La actualización COMPLETA significa que la vista se reconstruye desde cero en cada actualización.

PostgreSQL REFRESCAR VISTA MATERIALIZADA hace exactamente esto — ningún problema de traducción.

El problema es la programación.

Oracle DBMS_MVIEW.REFRESH y DBMS_SCHEDULER gestionar la actualización automática.

PostgreSQL no tiene un equivalente incorporado.

Después de la migración, las MV son instantáneas estáticas hasta que alguien ejecuta explícitamente:

REFRESH MATERIALIZED VIEW sh.cal_month_sales_mv;
REFRESH MATERIALIZED VIEW sh.fweek_pscat_sales_mv;

Para replicar la actualización programada, instala pg_cron (requiere superusuario) y crea un trabajo después de la migración.

También existe una restricción de orden de carga.

PostgreSQL ejecuta la consulta MV SELECT inmediatamente cuando la ejecutas CREAR VISTA MATERIALIZADA COMO SELECCIONAR.

Si carga el DDL de la MV antes de los datos, la consulta se ejecuta contra tablas vacías y la MV se crea vacía.

Carga primero los datos, luego las vistas de materiales.

También hay un problema con search_path.

ora2pg no añade SET search_path al archivo de exportación de MVIEW.

Si cargas SH_mviews.sql directamente, psql se ejecuta como el usuario del sistema operativo postgres, cuyo search_path predeterminado es público los videos musicales se crean en público, no a.

La solución es el truco de la tubería:

(echo "SET search_path TO sh;"; cat /home/fernando/ora2pg-sh/output/SH_mviews.sql) | \
  sudo -u postgres psql -d shdb

En eco ante pone el SET search_path directiva así que cada CREAR VISTA MATERIALIZADA en el archivo aterriza en el a esquema.

Problema 5: Los objetos DIMENSION se descartan silenciosamente

Oracle es compatible CREAR DIMENSIÓN sintaxis, que define jerarquías de consolidación para el motor de reescritura de consultas y las operaciones OLAP de Oracle.

PostgreSQL no tiene un equivalente.

ora2pg omite silenciosamente los objetos DIMENSION.

No hay advertencia, ni error, ni DDL de marcador de posición.

Si no los revisas manualmente, no sabrás que existían hasta que alguien pregunte por qué un informe del lado de Oracle que utilizaba la reescritura de consultas ya no funciona como se esperaba.

En el esquema SH, se omiten CUSTOMERS_DIM, PRODUCTS_DIM, TIMES_DIM y CHANNELS_DIM.

No hay ningún impacto funcional en las consultas SQL estándar: las dimensiones son metadatos del optimizador, no datos.

La acción correcta es documentarlos como abandonados en el registro de riesgos de migración.

Ejecutando la Migración: Cada Comando en Orden

Paso 1: poblar los datos de Oracle SH usando SQLcl

El script de instalación de SH llama sh_populate.sql internamente, que usa SQLcl's CARGAR comando para leer archivos CSV.

SQL*Plus no soporta CARGAR — salta silenciosamente cada CARGAR llamada con SP2-0734: comando desconocido.

El esquema se crea pero cada tabla grande permanece con 0 filas.

Primero descargue los archivos CSV:

# On srv1 (Oracle)
cd /home/oracle/sh

for f in costs customers promotions sales supplementary_demographics times; do
  curl -L -o ${f}.csv \
    "https://raw.githubusercontent.com/oracle-samples/db-sample-schemas/main/sales_history/${f}.csv"
done

ls -lh *.csv
# Expected: 6 files; sales.csv ~74 MB, customers.csv ~13 MB

Ejecuta la instalación usando SQLcl, no SQL*Plus:

# On srv1 (Oracle)
cd /home/oracle/sh
sql / as sysdba

Dentro de SQLcl:

ALTER SESSION SET CONTAINER = pdb1;
@sh_install.sql
-- Prompts:
-- Enter a password for the user SH:             sh
-- Enter a tablespace for SH [USERS]:            (press Enter)
-- Do you want to overwrite the schema? [YES|no]: YES

Verificar recuentos de filas:

# On srv1 (Oracle)
sqlplus sh/sh@//localhost:1521/pdb1 <<'EOF'
SELECT 'SALES',      COUNT(*) FROM sh.sales    UNION ALL
SELECT 'COSTS',      COUNT(*) FROM sh.costs    UNION ALL
SELECT 'CUSTOMERS',  COUNT(*) FROM sh.customers;
EXIT;
EOF
# Expected: SALES 918843, COSTS 82112, CUSTOMERS 55500
# If any large table is 0: SQLcl was not used — re-run using sql, not sqlplus

Paso 2 — Crear la base de datos y el usuario de PostgreSQL

# On srv2 (PostgreSQL)
sudo -u postgres psql -c "CREATE USER sh WITH PASSWORD 'sh';"
sudo -u postgres psql -c "CREATE DATABASE shdb OWNER sh;"

sudo -u postgres psql -c "\l shdb"
# Expected: one row, owner sh, encoding UTF8

Paso 3 – Crea el directorio de salida

# On srv2 (PostgreSQL)
mkdir -p /home/fernando/ora2pg-sh/output

Paso 4 — Configurar ora2pg.conf

# On srv2 (PostgreSQL)
sudo cp /etc/ora2pg/ora2pg.conf /etc/ora2pg/ora2pg.conf.$(date +%Y%m%d)
sudo vi /etc/ora2pg/ora2pg.conf

Establecer estos valores:

ORACLE_DSN      dbi:Oracle:host=192.168.0.180;sid=pdb1;port=1521
ORACLE_USER     sh
ORACLE_PWD      sh
SCHEMA          SH
OUTPUT_DIR      /home/fernando/ora2pg-sh/output
CREATE_SCHEMA   1
PG_INTEGER_TYPE 1
PG_NUMERIC_TYPE 1
BITMAP_AS_GIN   0

Paso 5: Ejecutar SHOW_COLUMN para identificar columnas NUMBER(38)

# On srv2 (PostgreSQL)
ora2pg -t SHOW_COLUMN > /home/fernando/ora2pg-sh/output/columns-sh.txt
grep 'numeric(38)' /home/fernando/ora2pg-sh/output/columns-sh.txt
# Every column listed here needs a MODIFY_TYPE override to bigint

Paso 6: establecer anulaciones de MODIFY_TYPE

Añadir el MODIFICAR_TIPO línea a ora2pg.conf (debe ser una sola línea — ver Problema 2 arriba).

Verificar que existe exactamente una línea activa:

# On srv2 (PostgreSQL)
grep 'MODIFY_TYPE' /etc/ora2pg/ora2pg.conf | grep -v '^\s*#' | wc -l
# Must be: 1

Paso 7 — Exportar TABLA

# On srv2 (PostgreSQL)
ora2pg -t TABLE -o SH_tables.sql 2>&1 | tee /home/fernando/ora2pg-sh/output/table-export-sh.log

ls -lh /home/fernando/ora2pg-sh/output/SH_tables.sql
# Expected: file present, non-zero size

Paso 8 — Corregir la exportación de TABLAS (índices de mapa de bits)

# On srv2 (PostgreSQL)
cp /home/fernando/ora2pg-sh/output/SH_tables.sql \
   /home/fernando/ora2pg-sh/output/SH_tables_fixed.sql

vi /home/fernando/ora2pg-sh/output/SH_tables_fixed.sql

Aplique las decisiones de índice del Problema 3:

• Borrar canal_de_ventas_bix y costos_canal_bix
• Reemplazar tiempo_de_venta_bix y costos_tiempo_bix con USANDO BRIN

Paso 9 — Exportar PARTICIÓN

# On srv2 (PostgreSQL)
ora2pg -t PARTITION -o SH_partitions.sql 2>&1 | tee /home/fernando/ora2pg-sh/output/partition-export-sh.log

grep -c 'PARTITION OF' /home/fernando/ora2pg-sh/output/SH_partitions.sql
# Expected: 37 (28 SALES + 9 COSTS)

Paso 10 — Exportar MVIEW

# On srv2 (PostgreSQL)
ora2pg -t MVIEW -o SH_mviews.sql 2>&1 | tee /home/fernando/ora2pg-sh/output/mview-export-sh.log

Paso 11 — Exportar VISTA

# On srv2 (PostgreSQL)
ora2pg -t VIEW -o SH_view.sql 2>&1 | tee /home/fernando/ora2pg-sh/output/view-export-sh.log

Paso 12 — Exportar datos (COPY)

# On srv2 (PostgreSQL)
ora2pg -t COPY -o SH_data.sql 2>&1 | tee /home/fernando/ora2pg-sh/output/copy-export-sh.log
# Expect 10-20 minutes -- SALES is ~918,000 rows
# Monitor in a second terminal: watch -n 10 "wc -l /home/fernando/ora2pg-sh/output/SH_data.sql"

tail -5 /home/fernando/ora2pg-sh/output/SH_data.sql
# Expected: ends with COMMIT; -- if truncated, the export was interrupted, re-run

Paso 13: Cargar en el orden correcto

Verificar que el objetivo esté vacío:

# On srv2 (PostgreSQL)
sudo -u postgres psql -d shdb -c "\dn"
# Expected: no rows (sh schema does not exist yet)

Carga 1 — DDL de tablas (tablas padre, índices, PK):

# On srv2 (PostgreSQL)
sudo -u postgres psql -d shdb < /home/fernando/ora2pg-sh/output/SH_tables_fixed.sql
# Expected: CREATE TABLE, CREATE INDEX, ALTER TABLE -- no ERROR lines

sudo -u postgres psql -d shdb -c "\dt sh.*"
# Expected: 9 tables listed

Cargar 2 — Particionar tablas secundarias (debe ir antes de los datos):

# On srv2 (PostgreSQL)
sudo -u postgres psql -d shdb < /home/fernando/ora2pg-sh/output/SH_partitions.sql

sudo -u postgres psql -d shdb -c "
SELECT COUNT(*) FROM pg_tables
WHERE schemaname = 'sh'
  AND (tablename LIKE 'sales_%' OR tablename LIKE 'costs_%');"
# Expected: 37

Cargar 3 — Datos:

# On srv2 (PostgreSQL)
sudo -u postgres psql -d shdb < /home/fernando/ora2pg-sh/output/SH_data.sql
# Single transaction -- if any COPY fails, everything rolls back
# Expect several minutes for SALES

Carga 4 — Vistas materializadas (después de los datos, con el truco de tubería `search_path`):

# On srv2 (PostgreSQL)
(echo "SET search_path TO sh;"; cat /home/fernando/ora2pg-sh/output/SH_mviews.sql) | \
  sudo -u postgres psql -d shdb

sudo -u postgres psql -d shdb -c "\dm sh.*"
# Expected: cal_month_sales_mv, fweek_pscat_sales_mv, both ispopulated = t

Cargar 5 — Ver:

# On srv2 (PostgreSQL)
sudo -u postgres psql -d shdb < /home/fernando/ora2pg-sh/output/SH_view.sql

sudo -u postgres psql -d shdb -c "\dv sh.*"
# Expected: sh | profits | view

Paso 14 — Volver a aplicar restricciones FK

El archivo de datos COPY de ora2pg elimina todas las restricciones FK antes de cargar los datos y no las vuelve a añadir.

Este es un comportamiento confirmado de ora2pg (incidencia #1960).

Verificar que faltan las FK después de la carga:

# On srv2 (PostgreSQL)
sudo -u postgres psql -d shdb -c "
SELECT COUNT(*) FROM information_schema.table_constraints
WHERE constraint_schema = 'sh'
  AND constraint_type   = 'FOREIGN KEY';"
# Expected: 0

Reaplica todas las 10 FKs en un comando:

# On srv2 (PostgreSQL)
grep '^ALTER TABLE.*FOREIGN KEY' /home/fernando/ora2pg-sh/output/SH_tables.sql | \
  sudo -u postgres psql -d shdb
# Expected: 10 ALTER TABLE lines, no ERROR lines

Verificar

# On srv2 (PostgreSQL)
sudo -u postgres psql -d shdb -c "
SELECT COUNT(*) FROM information_schema.table_constraints
WHERE constraint_schema = 'sh'
  AND constraint_type   = 'FOREIGN KEY';"
# Expected: 10

Cómo se ve el Informe de Prueba Final

Habilitar PG_DSN en ora2pg.conf, luego ejecuta:

# On srv2 (PostgreSQL)
ora2pg -t TEST 2>&1 | tee /home/fernando/ora2pg-sh/output/SH_test.txt

Diferencias esperadas (no errores de migración):

Desajuste en el recuento dePRIMARY KEY en CHANNELS, COUNTRIES, PRODUCTS (Oracle: 0, PostgreSQL: 1):

El esquema SH de Oracle define PKs con DESACTIVAR NOVALIDATE — las restricciones están definidas pero no se aplican.

El catálogo de Oracle los reporta como 0 PKs activos.

PostgreSQL los aplica correctamente.

Este es un artefacto del diseño del esquema de ejemplo de Oracle; no se necesita ninguna acción.

Inconsistencia en el recuento NOT NULL en COSTS y SALES (Oracle: 6 y 7, PostgreSQL: 0):

Ambas son tablas particionadas.

PostgreSQL aplica restricciones NOT NULL en las tablas hijas de la partición, no en la padre.

ora2pg's TEST lee el padre y obtiene 0.

Las restricciones existen y se imponen; verifique con \d sh.ventas_1995 si es necesario.

Conteo de MVIEW (2 vs 0):

La prueba de ora2pg no detecta de manera confiable las vistas materializadas en el esquema de destino.

Verificar directamente:

# On srv2 (PostgreSQL)
sudo -u postgres psql -d shdb -c "\dm sh.*"
# Expected: both MVs present with ispopulated = t
# If they appear: ignore the TEST counter

Recuentos de filas: todas las tablas deben coincidir:

CANALES 5 / 5 PARTIDO
PAÍSES 23 / 23 COINCIDENCIA
CLIENTES 55500 / 55500 COINCIDEN
PRODUCTOS 72 / 72 COINCIDENCIA
PROMOCIONES 503 / 503 COINCIDENCIA
TIEMPOS 1826 / 1826 MATCH
COSTES 82112 / 82112 MATCH
VENTAS 918843 /918843 MATCH
DATOS_DEMOGRÁFICOS_COMPLEMENTARIOS 4500 / 4500 MATCH

Si SALES muestra una discrepancia, revise el recuento de filas de particiones individuales:

# On srv2 (PostgreSQL)
sudo -u postgres psql -d shdb -c "
SELECT tableoid::regclass AS partition, COUNT(*)
FROM   sh.sales
GROUP  BY tableoid::regclass
ORDER  BY tableoid::regclass;"
# Look for partitions with 0 rows -- those indicate a load failure for that time range

En resumen

La mayoría de los esquemas de almacén de datos de Oracle se parecen a SH.

Tablas de hechos particionadas, índices de mapa de bits en columnas FK, vistas materializadas reconstruidas según un calendario y metadatos de dimensiones que nadie ha mirado en años.

Ninguno de estos son bloqueadores — todos tienen equivalentes limpios en PostgreSQL.

Pero la migración no es automática.

ora2pg te lleva el 80 de camino%.

Las 20% restantes son un conjunto de decisiones específicas: ejecutar TABLE y PARTITION como exportaciones separadas y cargarlas en el orden correcto; sobrescribir cada columna NUMBER(38) a bigint usando MODIFY_TYPE; establecer BITMAP_AS_GIN en 0 y revisar cada índice individualmente; anteponer SET search_path al cargar vistas materializadas; y volver a aplicar las restricciones FK después de la carga de datos porque ora2pg las elimina y nunca las vuelve a poner.

Los objetos DIMENSION son los únicos sin equivalente en PostgreSQL.

Son metadatos del optimizador; eliminarlos no tiene ningún efecto en la corrección de la consulta.

Documentarlas como caídas y seguir adelante.

En cada migración de clase SH que ejecuto, surgen los mismos siete problemas.

Conocerlos antes de la primera exportación es lo que separa una migración limpia de un día de depuración.

Próximos pasos

Si tienes un esquema de data warehouse de Oracle y quieres entender el alcance de la migración antes de comprometerte a una evaluación completa, comienza con la auditoría de migración gratuita en rootfan.com/services/.