Ejemplo de migración de ora2pg: Esquema HR de Oracle a PostgreSQL

El esquema HR de Oracle es lo más parecido que tiene el mundo de las bases de datos a una referencia universal.

Todo DBA de Oracle lo ha visto.

Todo consultor de migración lo utiliza para explicar cómo funciona el proceso.

Lo que lo hace verdaderamente útil como caso de estudio no es su tamaño — son siete mesas y 107 empleados.

Lo que lo hace útil es que contiene, en forma mínima, los patrones exactos que hacen triviales las migraciones de Oracle a PostgreSQL: secuencias conectadas a disparadores, procedimientos almacenados, discrepancias de tipos que compilan limpiamente y fallan en tiempo de ejecución.

Ejecuté la migración completa usando ora2pg 25.0 con Oracle 19c como origen y PostgreSQL 18 como destino.

Esta publicación cubre las cinco cosas que requirieron intervención manual — y por qué cada una de ellas importa mucho más en un esquema de producción que en una demostración.

¿Qué contiene RR. HH.?

El esquema de RR. HH. es el esquema de referencia estándar de Oracle, incluido con cada instalación de Oracle y disponible en GitHub.

Tiene siete tablas que cubren la jerarquía estándar de RR. HH.: regiones, países, ubicaciones, departamentos, puestos, empleados e historial laboral.

Tiene tres secuencias independientes (secuencia_empleados, departamentos_seq, locations_seq), una vista (DETALLES_EMPLEADO_VER), dos procedimientos almacenados (dml_seguro, agregar_historial_laboral), y cinco activadores.

Tres de esos desencadenadores existen únicamente para llamar sequence.NEXTVAL en INSERT — un patrón que los desarrolladores de Oracle usaban anteriormente DEFAULT con secuencias fue ampliamente adoptado.

Los dos restantes implementan la lógica de negocio: uno restringe las operaciones DML al horario de oficina, el otro escribe una fila de historial cuando un empleado cambia de puesto.

ora2pg califica el esquema HR B-5: se requiere reescritura de código, aproximadamente un día-persona para un experto en PostgreSQL.

La calificación está impulsada completamente por los procedimientos almacenados y los triggers.

Las tablas, vistas y datos migran sin intervención.

1. Mapeo de Tipos NUMÉRICOS: La Trampa de la Columna Monetaria

ora2pg tiene dos directivas que manejan la mayor parte NÚMERO columnas automáticamente.

PG_INTEGER_TYPE 1 mapas NUMÉRICO(p) columnas sin escala al tipo entero correcto: la precisión 1–4 se convierte en smallint, 5–9 se convierte en entero, 10–18 entero largo.

Para el esquema de RR. HH. esto significa ID_EMPLEADO NUMBER(6) se convierte ENTERO, DEPARTMENT_ID NÚMERO(4) se convierte INT PEQUEÑO — ningún trabajo manual necesario.

La trampa es TIPO_NUMÉRICO_PG 1, que maneja columnas que tienen una escala declarada.

Esta directiva no solo mira el tipo declarado – consulta los valores de datos reales.

Si a NUMERO(8,2) la columna actualmente solo contiene valores de números enteros, mapea la columna a doble precisión en lugar de numérico.

EMPLEADOS.SALARIO es NUMERO(8,2) en Oracle.

Cada salario en el conjunto de datos de RR. HH. es un número entero: 24000, 17000, 9000. Con TIPO_NUMÉRICO_PG 1 activo, ora2pg mapas SUELDO a doble precisión.

doble precisión es un tipo de punto flotante.

Los tipos de punto flotante son incorrectos para los valores monetarios, ya que no pueden representar todas las fracciones decimales exactamente, lo que produce errores de redondeo en los cálculos financieros.

El tipo correcto para el salario es numérico.

La solución es un MODIFICAR_TIPO anular

MODIFY_TYPE    EMPLOYEES:SALARY:numeric

En cada migración real de producción que evalúo, las columnas de salario y otras columnas monetarias similares requieren esta anulación.

En PG_NUMERIC_TYPE la directiva está haciendo su trabajo correctamente: está infiriendo tipos a partir de datos.

La inferencia es simplemente incorrecta para el dinero, donde la precisión declarada importa más que lo que contienen los datos actuales.

2. El Patrón del Detector de Secuencia

En Oracle, el patrón estándar para claves primarias autoincrementales era:

1. Crea una secuencia independiente.
2. Crear un disparador BEFORE INSERT que se active para cada fila y asigne sequence.NEXTVAL a la columna ID.

Así se construye Recursos Humanos. EMPLEADOS.ID_EMPLEADO no tiene DEFAULT cláusula en Oracle — el disparador la rellena en cada INSERT.

En PostgreSQL, el equivalente es una columna DEFAULT nextval('sequence_name').

Cuando se inserta una fila sin especificar la columna ID, PostgreSQL llama nextval() automáticamente. No se necesita ningún desencadenador.

ora2pg convierte los triggers de secuencia de Oracle; los exporta como funciones de trigger de PostgreSQL.

Pero la exportación de TABLA no añade nextval(...) por omisión a las definiciones de columna, porque Oracle nunca tuvo una. Después de cargar el DDL exportado, las tablas no tienen generación de ID automática. Cualquier INSERT sin una inserción explícita id_empleado fallará con el valor nulo en la columna viola la restricción de no nulidad.

La solución es añadir los valores predeterminados después de que se carguen los desencadenadores:

ALTER TABLE hr.employees   ALTER COLUMN employee_id   SET DEFAULT nextval('hr.employees_seq');
ALTER TABLE hr.departments ALTER COLUMN department_id SET DEFAULT nextval('hr.departments_seq');
ALTER TABLE hr.locations   ALTER COLUMN location_id   SET DEFAULT nextval('hr.locations_seq');

También enlaza cada secuencia a su columna con PROPIEDAD DE para que al eliminar la tabla se elimine automáticamente la secuencia.

Sin PROPIEDAD DE, las secuencias se convierten en huérfanas que deben desecharse por separado.

3. %TIPO en Firmas de Parámetros de Procedimiento

agregar_historial_laboral es exportado por ora2pg con la sintaxis de tipo anclado de Oracle en su lista de parámetros:

-- What ora2pg exports
CREATE OR REPLACE PROCEDURE hr.add_job_history (
    p_emp_id        job_history.employee_id%TYPE,
    p_start_date    job_history.start_date%TYPE,
    ...

PostgreSQL soporta %TIPO dentro del cuerpo de una función o procedimiento para declaraciones de variables. No admite %TIPO en la firma de parámetros.

La carga del archivo exportado falla inmediatamente con un error de sintaxis.

La solución es sustituir los tipos concretos:

CREATE OR REPLACE PROCEDURE hr.add_job_history (
    p_emp_id        integer,
    p_start_date    timestamp,
    p_end_date      timestamp,
    p_job_id        varchar(10),
    p_department_id smallint
)

Los tipos correctos provienen del análisis de columnas realizado antes de la migración. EMPLOYEE_ID es ENTERO, FECHA_INICIO es MARCA DE TIEMPO, y así sucesivamente.

Por eso el paso de análisis de tipos no es opcional: necesitas los tipos finales de PostgreSQL para escribir esta corrección.

En un esquema de producción con docenas de procedimientos almacenados, este patrón aparece con frecuencia.

Cada procedimiento que utiliza parámetros de tipo anclado en Oracle necesita la misma sustitución manual antes de que pueda cargarse en PostgreSQL.

4. Incompatibilidad de tipos de statement_timestamp()

En actualizar_historial_laboral detonar llamadas agregar_historial_laboral escribir una fila de historial cuando un empleado cambia de puesto.

Necesita pasar la hora actual como el fecha_fin argumento.

ora2pg convierte Oracle FECHA_SISTEMA a statement_timestamp(). statement_timestamp() devoluciones marca de tiempo con zona horaria en PostgreSQL. agregar_historial_laboral espera marca de tiempo (sin zona horaria) para fecha_fin.

PostgreSQL no realiza conversiones implícitas timestamptz a marca de tiempo en llamadas a procedimientos.

La función de activación se carga sin errores. CREAR FUNCIÓN tiene éxito.

La incompatibilidad de tipos solo se manifiesta en tiempo de ejecución, la primera vez que un empleado cambia de puesto o departamento.

Esta es la categoría más insidiosa de errores de migración: código que compila limpiamente y falla en producción.

Yo uso plpgsql_check para capturar exactamente esta clase de error.

Realiza un análisis estático de los cuerpos de funciones compiladas y reporta incompatibilidades de tipos, variables faltantes y llamadas a procedimientos inválidas, todo antes de cualquier prueba de aplicación.

CREATE EXTENSION plpgsql_check;

Ejecutar plpgsql_check contra la función disparador en este laboratorio produjo:

trigger_fct_update_job_history() | error | procedure add_job_history(integer,
timestamp without time zone, timestamp with time zone, character varying, smallint)
does not exist

La solución es un solo molde:

CALL add_job_history(OLD.employee_id, OLD.hire_date, statement_timestamp()::TIMESTAMP,
                     OLD.job_id, OLD.department_id);

plpgsql_check no es una parte estándar del flujo de trabajo de ora2pg — no se ejecuta por defecto, no se menciona en la documentación de ora2pg, y no se incluye en ningún paquete de PostgreSQL por defecto.

Lo ejecuto en cada migración antes de entregar el código migrado al equipo de desarrollo. Ha detectado errores en tiempo de ejecución en cada esquema de producción que he migrado.

5. Las restricciones FK no se vuelven a aplicar después de la carga de datos

La exportación con COPY de ora2pg elimina todas las restricciones de clave externa al inicio del archivo de datos, carga los datos y luego confirma.

No vuelve a añadir las restricciones FK después del commit.

Este es un error conocido. problema #1960 en el repositorio darold/ora2pg.

Después de cargar el archivo de datos, la base de datos migrada no tiene restricciones de FK.

En ora2pg -t TEST La comparación mostrará que todas las tablas tienen cero FKs en PostgreSQL, aunque las definiciones de FK existan en el DDL exportado.

La solución es volver a aplicar las restricciones FK de la exportación de la tabla después de la carga de datos:

grep 'ADD CONSTRAINT.*FOREIGN KEY' HR_tables.sql | \
  PGPASSWORD=hr psql -U hr -d hrdb -h localhost

Para el esquema de RR. HH., esto restaura 10 restricciones de clave foránea en cinco tablas.

En un esquema de producción con cientos de tablas, el recuento de claves foráneas (FK) es un punto de control de validación crítico: si no coincide con el recuento de Oracle después de la migración, el modelo de datos está incompleto.

Ejecutando la Migración: Cada Comando en Orden

Medio ambiente

Dos máquinas virtuales en la misma red. srv1 (192.168.0.180) ejecuta Oracle 19c con el esquema HR en PDB pdb1. srv2 (192.168.0.181) ejecuta Ubuntu con PostgreSQL 18 y ora2pg instalado. Todos los comandos a continuación se ejecutan en srv2 a menos que se indique lo contrario.

Paso 1 — Recopilar estadísticas de Oracle

Ejecutar esto en srv1 antes de generar el informe de evaluación. las estimaciones de costos de ora2pg se basan en las estadísticas almacenadas de Oracle. las estadísticas obsoletas producen recuentos de filas y estimaciones de unidades de migración inexactos.

-- On Oracle (srv1)
BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS('HR');
  DBMS_STATS.GATHER_DICTIONARY_STATS;
END;
/

Paso 2: cree el directorio de salida

mkdir -p /home/fernando/ora2pg-hr/output

Paso 3 — ora2pg.conf mínimo

# /etc/ora2pg/ora2pg.conf
ORACLE_DSN      dbi:Oracle:host=192.168.0.180;service_name=pdb1;port=1521
ORACLE_USER     system
ORACLE_PWD      <system password>
SCHEMA          HR
EXPORT_SCHEMA   1
OUTPUT_DIR      /home/fernando/ora2pg-hr/output

Utilice sistema, no RRHH — la exportación de COPIA dice v$base de datos que requiere privilegios de DBA.

Paso 4: Generar el informe de evaluación

ora2pg -t SHOW_REPORT --estimate_cost --dump_as_html \
  > /home/fernando/ora2pg-hr/output/report-hr.html

Abre el HTML en un navegador.

Puntuaciones de RRHH B-5, aproximadamente una persona-día.

Los impulsores de costos son los dos procedimientos almacenados y dos disparadores de lógica de negocio.

Paso 5 — Analizar tipos de columna

ora2pg -t SHOW_COLUMN > /home/fernando/ora2pg-hr/output/columns-hr.txt

Luego consulta rangos de datos reales en Oracle para cualquier NÚMERO columna sin precisión declarada

-- On Oracle (srv1)
SELECT 'REGIONS.REGION_ID'   AS col, MIN(region_id), MAX(region_id) FROM regions
UNION ALL
SELECT 'COUNTRIES.REGION_ID',         MIN(region_id), MAX(region_id) FROM countries;

Ambos al máximo a 50 — ENTERO es la correcta asignación.

Paso 6: Añadir MODIFY_TYPE y volver a guardar la configuración

MODIFY_TYPE    REGIONS:REGION_ID:integer,COUNTRIES:REGION_ID:integer,EMPLOYEES:SALARY:numeric

EMPLEADOS:SALARIO:numérico se requiere porque TIPO_NUMÉRICO_PG 1 inspecciona datos reales, encuentra solo valores de salario de números enteros y mapea NUMERO(8,2) a doble precisión — incorrecto para una columna monetaria.

Las tres anulaciones en una sola línea MODIFICAR_TIPO ignora todo después de la primera línea.

Paso 7 — Ejecutar las exportaciones

ora2pg -t TABLE           -o HR_tables.sql           2>&1 | tee output/table-export-hr.log
ora2pg -t VIEW            -o HR_views.sql            2>&1 | tee output/view-export-hr.log
ora2pg -t SEQUENCE        -o HR_sequences.sql        2>&1 | tee output/sequence-export-hr.log
ora2pg -t SEQUENCE_VALUES -o HR_sequence_values.sql  2>&1 | tee output/seqval-export-hr.log
ora2pg -t TRIGGER         -o HR_triggers.sql         2>&1 | tee output/trigger-export-hr.log
ora2pg -t PROCEDURE       -o HR_procedures.sql       2>&1 | tee output/procedure-export-hr.log
ora2pg -t COPY            -o HR_data.sql             2>&1 | tee output/copy-export-hr.log

La exportación de TABLA hace no incluir secuencias — la exportación SEPARADA de SEQUENCE es obligatoria. Sin ella, la carga de sequence_values en el Paso 12 falla con el mensaje “la relación no existe”.

Paso 8 — Escribir los archivos de corrección manual

Dos archivos que no se pueden generar de la salida de ora2pg.

HR_procedimientos_fijos.sql — reemplaza %TIPO parámetros anclados con tipos concretos:

SET search_path = hr,public;

CREATE OR REPLACE PROCEDURE hr.add_job_history (
    p_emp_id        integer,
    p_start_date    timestamp,
    p_end_date      timestamp,
    p_job_id        varchar(10),
    p_department_id smallint
) AS $body$
BEGIN
  INSERT INTO job_history(employee_id, start_date, end_date, job_id, department_id)
    VALUES (p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
END;
$body$
LANGUAGE PLPGSQL SECURITY DEFINER;

CREATE OR REPLACE PROCEDURE hr.secure_dml () AS $body$
BEGIN
  IF TO_CHAR(clock_timestamp(), 'HH24:MI') NOT BETWEEN '08:00' AND '18:00'
      OR TO_CHAR(clock_timestamp(), 'DY') IN ('SAT', 'SUN') THEN
      RAISE EXCEPTION '%', 'You may only make changes during normal office hours'
        USING ERRCODE = '45205';
  END IF;
END;
$body$
LANGUAGE PLPGSQL SECURITY DEFINER;

HR_trigger_fixes.sql — lanzamiento statement_timestamp() para evitar timestamptz incoherencia, y agrega valores predeterminados de secuencia a las columnas de ID:

SET search_path = hr,public;

CREATE OR REPLACE FUNCTION hr.trigger_fct_update_job_history() RETURNS trigger AS $BODY$
BEGIN
  CALL add_job_history(OLD.employee_id, OLD.hire_date,
                       statement_timestamp()::TIMESTAMP,
                       OLD.job_id, OLD.department_id);
  RETURN NEW;
END
$BODY$
LANGUAGE 'plpgsql' SECURITY DEFINER;

ALTER TABLE hr.employees   ALTER COLUMN employee_id   SET DEFAULT nextval('hr.employees_seq');
ALTER TABLE hr.departments ALTER COLUMN department_id SET DEFAULT nextval('hr.departments_seq');
ALTER TABLE hr.locations   ALTER COLUMN location_id   SET DEFAULT nextval('hr.locations_seq');

ALTER SEQUENCE hr.employees_seq   OWNED BY hr.employees.employee_id;
ALTER SEQUENCE hr.departments_seq OWNED BY hr.departments.department_id;
ALTER SEQUENCE hr.locations_seq   OWNED BY hr.locations.location_id;

Paso 9 — Crear la base de datos

sudo -u postgres psql -c "CREATE USER hr WITH PASSWORD 'hr';"
sudo -u postgres psql -c "CREATE DATABASE hrdb OWNER hr;"

Paso 10: Cargar en orden de dependencia

# Tables first — everything else depends on them
PGPASSWORD=hr psql -U hr -d hrdb -h localhost \
  -f /home/fernando/ora2pg-hr/output/HR_tables.sql

# Sequences — must exist before data load and before sequence_values
PGPASSWORD=hr psql -U hr -d hrdb -h localhost \
  -f /home/fernando/ora2pg-hr/output/HR_sequences.sql

# Data — loads rows via COPY FROM stdin; FKs are dropped inside the file before load
PGPASSWORD=hr psql -U hr -d hrdb -h localhost \
  -f /home/fernando/ora2pg-hr/output/HR_data.sql

Paso 11 — Volver a aplicar restricciones FK

El archivo COPY de ora2pg elimina todas las restricciones de FK antes de la carga de datos y nunca las vuelve a agregar. Sin este paso, la base de datos no tiene restricciones de FK después del Paso 10.

(echo "SET search_path TO hr;"; \
 grep 'ADD CONSTRAINT.*FOREIGN KEY' \
   /home/fernando/ora2pg-hr/output/HR_tables.sql) | \
  PGPASSWORD=hr psql -U hr -d hrdb -h localhost

Verifica el conteo:

PGPASSWORD=hr psql -U hr -d hrdb -h localhost -c "
SELECT COUNT(*) FROM information_schema.table_constraints
WHERE constraint_schema = 'hr' AND constraint_type = 'FOREIGN KEY';"

Esperado: 10

Paso 12 — Carga los archivos restantes

# Reset sequences to Oracle's last values — run after data, not before
PGPASSWORD=hr psql -U hr -d hrdb -h localhost \
  -f /home/fernando/ora2pg-hr/output/HR_sequence_values.sql

# Procedures — must load before triggers (triggers call these procedures)
PGPASSWORD=hr psql -U hr -d hrdb -h localhost \
  -f /home/fernando/ora2pg-hr/output/HR_procedures_fixed.sql

# Triggers — after procedures
PGPASSWORD=hr psql -U hr -d hrdb -h localhost \
  -f /home/fernando/ora2pg-hr/output/HR_triggers.sql

# Trigger fixes — replaces the timestamptz trigger function, adds sequence defaults
PGPASSWORD=hr psql -U hr -d hrdb -h localhost \
  -f /home/fernando/ora2pg-hr/output/HR_trigger_fixes.sql

# View — after all tables are in place
PGPASSWORD=hr psql -U hr -d hrdb -h localhost \
  -f /home/fernando/ora2pg-hr/output/HR_views.sql

Paso 13 — Análisis estático con plpgsql_check

Instala la extensión, luego revisa todo el código almacenado antes de cualquier prueba de aplicación.

sudo apt install -y postgresql-18-plpgsql-check
sudo -u postgres psql -d hrdb -c "CREATE EXTENSION plpgsql_check;"

Revisar procedimientos y funciones:

PGPASSWORD=hr psql -U hr -d hrdb -h localhost -c "
SELECT pg_proc.oid::regprocedure AS function, level, message
FROM pg_proc,
     plpgsql_check_function_tb(pg_proc.oid) AS pcf
WHERE pronamespace = 'hr'::regnamespace
  AND prolang = (SELECT oid FROM pg_language WHERE lanname = 'plpgsql')
  AND prorettype <> 'trigger'::regtype
ORDER BY 1, 2;"

Verificar funciones de activación:

PGPASSWORD=hr psql -U hr -d hrdb -h localhost -c "
SELECT pg_proc.oid::regprocedure AS function, level, message
FROM pg_proc
JOIN pg_trigger ON pg_trigger.tgfoid = pg_proc.oid,
     plpgsql_check_function_tb(pg_proc.oid, pg_trigger.tgrelid) AS pcf
WHERE pg_proc.pronamespace = 'hr'::regnamespace
  AND pg_proc.prolang = (SELECT oid FROM pg_language WHERE lanname = 'plpgsql')
  AND pg_proc.prorettype = 'trigger'::regtype
ORDER BY 1, 2;"

Esperado después de aplicar todas las correcciones: (0 filas) en ambas consultas.

Paso 14 — Ejecutar la comparación de PRUEBA

Agrega la conexión de PostgreSQL a ora2pg.conf:

PG_DSN      dbi:Pg:dbname=hrdb;host=localhost;port=5432
PG_USER     hr
PG_PWD      hr

ora2pg -t TEST 2>&1 | tee /home/fernando/ora2pg-hr/output/migration_diff-hr.txt

Cómo se ve el Informe de Prueba Final

Después de aplicar las cinco correcciones, ora2pg -t TEST compara el origen de Oracle y el destino de PostgreSQL lado a lado. Cada sección debe mostrar OK, excepto dos falsos positivos conocidos:

VALOR PREDETERMINADO DE COLUMNA DIFERENCIA — Oracle usó triggers para poblar columnas ID; PostgreSQL usa nextval(...) por omisión. Las tres tablas donde agregamos valores predeterminados mostrarán una diferencia en el recuento. Este es el diseño correcto de PostgreSQL, no un error.

FUNCIÓN CONTAR DIFERENCIA — PostgreSQL almacena la lógica de los disparadores en un objeto de función separado que no tiene equivalente en el diccionario de datos de Oracle. Las dos funciones de disparador (activar_fct_empleados_seguros, trigger_fct_update_job_history) aparecen como funciones adicionales de PostgreSQL sin contraparte en Oracle. Esto también es esperado.

Cualquier otra línea de DIFF en la salida de TEST es un problema real que debe resolverse antes de la puesta en marcha.

En resumen

RR es un esquema pequeño. Solucionar cinco problemas en un conjunto de datos de 107 filas es medio día de trabajo.

En un esquema de producción real — 50 tablas, 30 procedimientos almacenados, cadenas de disparadores complejas, 10 millones de filas — existen las mismas cinco categorías de problemas, con 10 veces el volumen.

El error de la columna monetaria afecta a todos los esquemas que almacenan precios, salarios o cifras financieras.

En %TIPO El problema del parámetro afecta a todos los esquemas con procedimientos almacenados.

En statement_timestamp() error de tipo afecta a cada disparador que registra una marca de tiempo actual.

El problema de la re-aplicación de FK afecta a todos los esquemas con claves foráneas.

El patrón sequence-trigger-to-default afecta a todos los esquemas de Oracle creados antes de la versión 12c.

El valor de ejecutar la migración en RRHH primero no es la migración en sí, sino la creación de la lista de verificación que se aplica a cada esquema posterior.

Si usted está planeando una migración de Oracle a PostgreSQL y desea una evaluación independiente de la complejidad, el esfuerzo y el riesgo antes de comprometerse a un cronograma, Ofrezco una auditoría de migración a precio fijo que produce exactamente eso.