Restablecer la contraseña de un usuario expirado sin conocer la clave actual en Oracle 12c / 19c

Restablecer la contraseña de un usuario expirado sin conocer la clave actual en Oracle 12c / 19c

   julio 20, 2026       Luis Felipe

---

En la administración diaria de bases de datos Oracle, es habitual encontrarnos con un escenario crítico: una cuenta de usuario (ya sea de aplicación, de servicio o de integración) pasa a estado EXPIRED debido a las políticas del perfil de seguridad asignado.

El verdadero problema surge cuando nadie recuerda la contraseña original. Si la cambiamos por una clave completamente nueva, interrumpiremos de inmediato el servicio de las aplicaciones o sistemas dependientes hasta que sus cadenas de conexión sean actualizadas, algo que no siempre es posible realizar de manera inmediata en entornos de producción.

Para resolver esta contingencia sin alterar la contraseña real y ganar tiempo para gestionar el cambio formal, podemos "refrescar" el estado de la cuenta (recomponiendo su estatus a OPEN) reasignando su mismo hash almacenado en el diccionario de datos.

En versiones antiguas de Oracle (10g / 11g), solía extraerse la columna PASSWORD de la tabla sys.user$. Sin embargo, a partir de Oracle 12c, 19c y versiones superiores, el motor utiliza algoritmos de cifrado más avanzados (SHA-2). Por ello, el hash activo para la autenticación se almacena en la columna SPARE4.

A continuación, se detalla el procedimiento paso a paso para realizar esta acción de contingencia en cualquier usuario de la base de datos.

Si estás trabajando en entornos Oracle 12c, 19c o superiores bajo la arquitectura Multitenant, recuerda que antes de ejecutar este procedimiento debes asegurarte de estar conectado en el contenedor correcto:

• Si el usuario afectado es un Common User (ej. C##APP_USER), deberás ejecutarlo desde el CDB$ROOT.

• Si es un usuario local de una aplicación, debes moverte primero a su PDB correspondiente: ALTER SESSION SET CONTAINER = mi_pdb;

Antes de realizar cualquier ajuste, confirmamos el estado de la cuenta y el perfil asociado consultando la vista DBA_USERS:

SELECT username, account_status, profile, expiry_date FROM dba_users WHERE username = 'USUARIO';

Para evitar el error ORA-28007: the password cannot be reused, debemos verificar si el perfil asignado permite la reasignación de la misma clave. Consultamos DBA_PROFILES:

SELECT resource_name, limit FROM dba_profiles WHERE profile = (SELECT profile FROM dba_users WHERE username = 'USUARIO') AND resource_name IN ('PASSWORD_REUSE_TIME', 'PASSWORD_REUSE_MAX');

• Si ambos valores son UNLIMITED: Se puede avanzar al siguiente paso.
• Si tienen un límite numérico restrictivo: Se pueden modificar temporalmente los parámetros del perfil antes de ejecutar el cambio, para este caso en particular tenia asignado el perfil "default":

ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_TIME UNLIMITED PASSWORD_REUSE_MAX UNLIMITED;

Nota: Si tu usuario tiene un perfil personalizado, reemplaza DEFAULT por el nombre de su perfil para no alterar las políticas globales de la base de datos.

Consultamos la tabla interna sys.user$ filtrando por el nombre del usuario afectado:

SELECT spare4 FROM sys.user$ WHERE name = 'USUARIO';

La consulta devolverá la cadena alfanumérica completa correspondiente al hash de autenticación: 

Es importante copiar la cadena devuelta por SPARE4 en su totalidad (incluyendo prefijos como S: o T:), ya que especifica el tipo de algoritmo de cifrado empleado por la versión de la base de datos.

Ejecutamos la sentencia ALTER USER utilizando la cláusula IDENTIFIED BY VALUES. Esta sintaxis le indica a Oracle que registre directamente la clave procesada sin aplicar una nueva función de hashing sobre la cadena:

ALTER USER USUARIO IDENTIFIED BY VALUES 'S:A8B9C0D1E2F3...[HASH_COMPLETO_AQUÍ]...';

Nota: Si la cuenta también se encontraba bloqueada (LOCKED), se puede incluir la cláusula ACCOUNT UNLOCK en la misma sentencia

Con esto, el motor revalida el hash, calcula la nueva fecha de vencimiento según el perfil y cambia el estado de la cuenta a OPEN.




Validamos nuevamente la vista DBA_USERS para verificar que el procedimiento se haya completado exitosamente:

SELECT username, account_status, expiry_date FROM dba_users WHERE username = 'USUARIO';

El estado cambiará a OPEN y la columna EXPIRY_DATE reflejará la nueva fecha de vencimiento.

Si en los pasos anteriores modificaste los parámetros de reutilización de contraseña, no olvides regresar el perfil a sus valores originales para mantener el estándar de seguridad de la organización:

ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_TIME 365 PASSWORD_REUSE_MAX 3;

Este procedimiento constituye una herramienta de contingencia fundamental para el DBA al momento de resolver bloqueos de servicio urgentes en aplicaciones cuyas contraseñas no puedan modificarse de forma inmediata.

Sin embargo, no debe adoptarse como una solución permanente. La buena práctica de administración dicta planificar ventanas de mantenimiento periódicas para la rotación formal de credenciales y la asignación de perfiles con la directiva PASSWORD_LIFE_TIME UNLIMITED exclusivamente a cuentas de servicio de aplicación debidamente auditadas.

Saludos.

Read More

El valor de los Backups Incrementales ante un borrado accidental de Archivelogs

El valor de los Backups Incrementales ante un borrado accidental de Archivelogs

   julio 01, 2026       Luis Felipe

---

Durante los procesos de migración de bases de datos Oracle, la gestión del espacio en la Flash Recovery Area (FRA) suele volverse crítica debido a la alta generación de Archive Logs. En escenarios de alta presión, es común recurrir a comandos manuales de depuración. Sin embargo, el uso descuidado de la cláusula FORCE puede derivar en un escenario catastrófico.

En este artículo analizaremos cómo un borrado forzado de secuencias de Archive Logs afectó un respaldo de migración en ejecución, y cómo la ejecución de un Backup Incremental Level 1 actuó como salvavidas para mantener la disponibilidad de los datos.

El objetivo era generar un respaldo completo para mover la base de datos hacia un nuevo servidor. Debido al alto volumen de transacciones, la FRA comenzó a quedarse sin espacio. Para mitigar esto, se procedió a respaldar los logs directamente hacia un Filesystem alterno para liberar espacio local.

Para acelerar la depuración, se optó por eliminar los Archive Logs basándose en rangos de secuencias (sequence) en lugar de la cláusula habitual por tiempo (BEFORE 'SYSDATE'), aplicando además el parámetro FORCE:

RMAN> DELETE FORCE ARCHIVELOG FROM SEQUENCE 12345 UNTIL SEQUENCE 12355;

⚠️ El Peligro del Modificador FORCE: > El parámetro FORCE de RMAN ejecuta un borrado físico e ignora si los archivos están catalogados correctamente o si residen en múltiples destinos. Al ejecutarlo, RMAN no solo eliminó las secuencias de la FRA, sino que también barrió con las copias de esos mismos Archive Logs que ya se habían respaldado en la ruta del Filesystem destinada a la migración.

Dado que en ese instante se estaba ejecutando el Backup Full (Level 0) base para la migración, la pérdida de estos Archive Logs comprometía la consistencia del respaldo para el posterior RESTORE y RECOVER en el destino. La ventana de mantenimiento parecía perdida.

Originalmente, la estrategia no contemplaba el uso de respaldos incrementales. Sin embargo, ante la crisis, se optó por una solución alternativa basada en la arquitectura de RMAN:

• Archive Logs: Registran de manera lineal y cronológica los cambios vectoriales aplicados en la base de datos.

• Backup Incremental Level 1: Captura el estado final de los bloques de datos que sufrieron modificaciones desde el último Level 0.

Para salvar la situación, inmediatamente después de que finalizó el Backup Full, se lanzó de manera manual un Backup Incremental:

RMAN>BACKUP INCREMENTAL LEVEL 1 DATABASE FORMAT '/ruta_de_backup/db_inc_%u';

Al ejecutarse en ese instante, el Level 1 escaneó los datafiles y capturó en un conjunto de respaldos consolidado los bloques que habían cambiado. De esta manera, los cambios atrapados en la brecha de los Archive Logs destruidos quedaron asegurados directamente a nivel de bloques de datos.

Al realizar la restauración en el nuevo servidor, la ausencia de las secuencias de Archive Logs eliminadas por error no detuvo el proceso.

RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;

Durante el paso de RECOVER, RMAN detectó la existencia del Backup Incremental Level 1. En lugar de detenerse inmediatamente solicitando las secuencias perdidas, RMAN aplicó directamente los bloques modificados del archivo incremental sobre el Backup Level 0 restaurado.

Esto consolidó los cambios pendientes directamente en los datafiles, permitiendo alcanzar la consistencia requerida para abrir la base de datos de manera segura:

SQL> ALTER DATABASE OPEN RESETLOGS;

Este escenario demuestra que el Backup Incremental Level 1 es una excelente herramienta de contingencia para mitigar rupturas en la cadena de redo histórica, reduciendo drásticamente el RTO al consolidar cambios directamente a nivel de bloques. Sin embargo, para salvaguardar la integridad de la estrategia de recuperación, se debe desterrar el uso reactivo de DELETE FORCE para liberar espacio en la FRA durante operaciones en caliente, ya que RMAN puede eliminar copias válidas en otros destinos activos. En su lugar, la buena práctica exige dimensionar correctamente la FRA antes de la migración, priorizar comandos de cambio de catálogo (CROSSCHECK y DELETE EXPIRED) o reubicar temporalmente los destinos de los logs (LOG_ARCHIVE_DEST_n) hacia un storage externo sin romper la consistencia del respaldo base.

Saludos.

Read More

Cómo automatizar GRANTS en nuevas tablas y procedimientos usando Triggers DDL y DBMS_SCHEDULER

Cómo automatizar GRANTS en nuevas tablas y procedimientos usando Triggers DDL y DBMS_SCHEDULER

   junio 15, 2026       Luis Felipe

---

¿Cuántas veces te ha pasado? Alguien del equipo crea una nueva tabla o un procedimiento en el ambiente de desarrollo, pero los demás integrantes no pueden acceder a ella debido a que no tienen los privilegios necesarios y para completar el DBA está fuera de oficina o de vacaciones...

Asignar permisos manualmente es ineficiente y propenso a errores humanos. Hoy vamos a ver cómo automatizar por completo esta tarea mediante un Trigger DDL.

Sin embargo, si estás trabajando en Oracle 12c, hacerlo directamente dentro de un trigger te llevará de cabeza a un callejón sin salida (específicamente al temido ORA-04092). En este artículo te enseño cómo estructurar una solución asíncrona, limpia y segura utilizando Transacciones Autónomas y DBMS_SCHEDULER.

Cuando ejecutas un CREATE TABLE, Oracle realiza un COMMIT implícito inmediatamente antes y después del comando. Si intentas meter un GRANT (que es un comando DCL) directamente dentro de un Trigger DDL convencional, romperás el ciclo transaccional del motor, resultando en un error de bloqueo o un fallo de compilación.

Para solucionarlo en Oracle 12c, debemos aplicar estos dos conceptos clave:

1. Un Job asíncrono: Que se encargue de dar el permiso milisegundos después de que termine la creación del objeto.

2. Una Transacción Autónoma (PRAGMA AUTONOMOUS_TRANSACTION): Que le permita al trigger abrir una "mini-sesión" independiente para agendar el Job sin interferir con el CREATE principal.

Nota: Aunque en versiones antiguas se usaba DBMS_JOB, en Oracle 12c la recomendación oficial es usar el paquete moderno DBMS_SCHEDULER, ya que es mucho más robusto y se auto-gestiona de forma más eficiente.

Primero creamos un procedimiento auxiliar. Usamos SQL dinámico (EXECUTE IMMEDIATE) combinándolo con DBMS_ASSERT.ENQUOTE_NAME por seguridad frente a inyecciones de SQL.

Es fundamental incluir la cláusula AUTHID CURRENT_USER para que se ejecute bajo los privilegios del usuario actual en tiempo de ejecución.

CREATE OR REPLACE PROCEDURE sp_otorgar_grant (
    p_object_name IN VARCHAR2,
    p_object_type IN VARCHAR2
) AUTHID CURRENT_USER AS
    v_sql VARCHAR2(1000);
BEGIN
    -- Evaluamos el tipo de objeto para asignar el privilegio correcto
    IF p_object_type = 'TABLE' THEN
        v_sql := 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' || dbms_assert.enquote_name(p_object_name) || ' TO ROL_TEST';
    ELSIF p_object_type = 'PROCEDURE' THEN
        v_sql := 'GRANT EXECUTE ON ' || dbms_assert.enquote_name(p_object_name) || ' TO ROL_TEST';
    END IF;
 
    -- Si el tipo de objeto aplica, ejecutamos el DCL
    IF v_sql IS NOT NULL THEN
        EXECUTE IMMEDIATE v_sql;
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        RAISE;
END;
/

Ahora creamos el Trigger a nivel de base de datos (AFTER CREATE ON DATABASE) para centralizar los permisos en un solo trigger. Gracias al PRAGMA AUTONOMOUS_TRANSACTION, este bloque puede ejecutar su propio COMMIT interno (necesario al crear un Job) sin afectar la transacción de la tabla que se está creando en ese instante.

CREATE OR REPLACE TRIGGER trg_auto_grant_objetos
AFTER CREATE ON SCHEMA
DECLARE
    -- Habilitamos la transacción independiente para evitar el ORA-04092
    PRAGMA AUTONOMOUS_TRANSACTION;
    v_obj_type  VARCHAR2(30);
    v_job_name  VARCHAR2(100);
    v_what      VARCHAR2(2000);
BEGIN
    v_obj_type := DICTIONARY_OBJ_TYPE;
    -- Filtramos únicamente los objetos que nos interesan
    IF v_obj_type IN ('TABLE', 'PROCEDURE') THEN
        -- Generamos un nombre único y dinámico para el Job
        v_job_name := DBMS_SCHEDULER.GENERATE_JOB_NAME('JOB_GRANT_');
        -- Definimos el bloque anónimo que ejecutará el Job en segundo plano
        v_what     := 'BEGIN sp_otorgar_grant(''' || DICTIONARY_OBJ_NAME || ''', ''' || v_obj_type || '''); END;';
        -- Lanzamos el Job de ejecución inmediata
        DBMS_SCHEDULER.CREATE_JOB (
            job_name        => v_job_name,
            job_type        => 'PLSQL_BLOCK',
            job_action      => v_what,
            start_date      => SYSTIMESTAMP + INTERVAL '3' SECOND,
            enabled         => TRUE,
            auto_drop       => TRUE -- El job se elimina automáticamente al finalizar
        );
        -- Confirmamos la creación del Job en su transacción independiente
        COMMIT;
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
END;
/

Nota: Es necesario que el usuario (esquema) donde fue creado el trigger necesita privilegios para crear jobs en la base de datos.

GRANT CREATE JOB TO USUARIO_TRIGGER;

 

Una peculiaridad de delegarle la tarea a DBMS_SCHEDULER es que, si algo falla (por ejemplo, el rol no existe o el objeto tiene un nombre extraño), el error no saltará en tu script de despliegue. Ocurrirá de manera silenciosa en segundo plano.

Para auditar y comprobar que los permisos se están asignando con éxito, puedes interrogar periódicamente a la vista del diccionario de datos:

SELECT log_date, job_name, status, error#
FROM USER_SCHEDULER_JOB_RUN_DETAILS
WHERE job_name LIKE 'JOB_GRANT_%'
ORDER BY log_date DESC;

Si en la columna STATUS visualizas SUCCEEDED, felicidades: tus objetos en Oracle 12c ya se auto-gestionan solos.

Importante: Si vas a realizar un despliegue masivo en producción (un script que crea cientos de tablas de golpe), la generación masiva de Jobs individuales podría estresar momentáneamente el Scheduler de Oracle. En esos escenarios específicos, la buena práctica dicta deshabilitar temporalmente el trigger antes de la actualización y habilitarlo al finalizar:

-- Deshabilitar el trigger
ALTER TRIGGER trg_auto_grant_objetos DISABLE;
-- Habilitar el trigger
ALTER TRIGGER trg_auto_grant_objetos ENABLE;

En definitiva, automatizar la asignación de permisos mediante Transacciones Autónomas y el poder asíncrono de DBMS_SCHEDULER transforma una tarea manual, tediosa y propensa a fallos en un proceso invisible que eleva la robustez de tu infraestructura. Al implementar esta solución —asegurándote siempre de que tu usuario cuente con el privilegio directo CREATE JOB y configurando un ligero retraso de un par de segundos en el inicio del Job para sincronizarse con el COMMIT principal—, no solo eliminarás de raíz los molestos tickets de soporte por olvido de GRANTS, sino que llevarás tus despliegues en Oracle al siguiente nivel de madurez técnica y profesional.

Saludos.

Read More

Corregir la fragmentación y reducir el High Water Mark (HWM) con SHRINK SPACE

Corregir la fragmentación y reducir el High Water Mark (HWM) con SHRINK SPACE

   mayo 21, 2026       Luis Felipe

---

En la administración de bases de datos Oracle, un escenario común de degradación de rendimiento ocurre cuando una tabla que solía albergar millones de registros sufre un proceso masivo de depuración (DELETE). A pesar de que los datos ya no residen en el disco, las consultas tipo Full Table Scan (FTS) sobre dicha tabla continúan demorando el mismo tiempo o consumiendo una cantidad idéntica de operaciones de E/S (I/O).

¿Por qué sucede esto? La respuesta se encuentra en el High Water Mark (HWM) o la "marca de agua" del segmento. En este artículo analizaremos conceptualmente este comportamiento, construiremos un laboratorio en la versión 19c para simular la fragmentación y demostraremos cómo diagnosticar y compactar el espacio desperdiciado utilizando herramientas nativas.

¿Qué es el High Water Mark?

El HWM es un puntero dentro de la estructura de almacenamiento de Oracle que define el límite hasta donde se han escrito bloques de datos en un segmento.

• Cuando se realizan operaciones de INSERT, el HWM sube de manera dinámica para asignar nuevos bloques.

• Cuando se realiza una operación de DELETE, los registros se borran lógicamente y los bloques quedan vacíos, pero el HWM no se reduce.

Dado que un Full Table Scan lee obligatoriamente todos los bloques por debajo del HWM (asumiendo que contienen datos válidos), Oracle terminará leyendo bloques vacíos de manera innecesaria, degradando el rendimiento de los queries y desperdiciando memoria en el Buffer Cache.

Simulación de Fragmentación

Para demostrar este impacto, crearemos una tabla de pruebas, la poblaremos con un volumen considerable de datos, realizaremos una depuración masiva y mediremos el comportamiento de los bloques.

-- Creamos una tabla basada en los objetos del diccionario para simular volumen

CREATE TABLE t_hwm_test AS SELECT rownum as id, a.* FROM all_objects a, (SELECT 1 FROM dual CONNECT BY level <= 10); 

-- Contamos la cantidad de registros creados en la tabla
SELECT COUNT(*) FROM t_hwm_test;

-- Verificamos el tamaño inicial del segmento y bloques asignados 

SELECT blocks, bytes/1024/1024 AS MB FROM user_segments WHERE segment_name = 'T_HWM_TEST';

Procederemos a eliminar el 90% de los registros de la tabla para inducir una fragmentación severa del segmento.

-- Eliminamos la gran mayoría de los registros 
DELETE FROM t_hwm_test WHERE MOD(id, 10) != 0;
COMMIT;

-- Ejecutamos estadísticas para actualizar el diccionario de datos
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_HWM_TEST');

Si consultamos las vistas USER_TABLES (datos lógicos) y USER_SEGMENTS (datos físicos), notaremos la discrepancia:

SELECT t.table_name, t.num_rows, t.blocks AS blocks_with_data, s.blocks AS total_allocated_blocks FROM user_tables t JOIN user_segments s ON t.table_name = s.segment_name WHERE t.table_name = 'T_HWM_TEST';

Para no depender exclusivamente de la ejecución de estadísticas de las tablas, podemos utilizar el paquete nativo DBMS_SPACE.UNUSED_SPACE para obtener el estado del HWM en tiempo real.

Ejecutaremos el siguiente bloque de código en nuestra sesión:

DECLARE
    v_total_blocks       NUMBER;
    v_total_bytes        NUMBER;
    v_unused_blocks      NUMBER;
    v_unused_bytes       NUMBER;
    v_last_used_extent_file_id NUMBER;
    v_last_used_extent_block_id NUMBER;
    v_last_used_block    NUMBER;
BEGIN
    DBMS_SPACE.UNUSED_SPACE(
        segment_owner             => USER,
        segment_name              => 'T_HWM_TEST',
        segment_type              => 'TABLE',
        total_blocks              => v_total_blocks,
        total_bytes               => v_total_bytes,
        unused_blocks             => v_unused_blocks,
        unused_bytes              => v_unused_bytes,
        last_used_extent_file_id  => v_last_used_extent_file_id,
        last_used_extent_block_id => v_last_used_extent_block_id,
        last_used_block           => v_last_used_block
    );
    DBMS_OUTPUT.PUT_LINE('Bloques Totales Asignados: ' || v_total_blocks);
    DBMS_OUTPUT.PUT_LINE('Bloques sobre el HWM (Sin usar): ' || v_unused_blocks);
    DBMS_OUTPUT.PUT_LINE('Bloques por debajo del HWM (HWM Actual): ' || (v_total_blocks - v_unused_blocks));
END;
/

Compactación del Segmento con SHRINK SPACE 

A partir de la introducción de los tablespaces con administración automática de espacio de segmentos (ASSM), Oracle provee una instrucción en línea (ONLINE) extremadamente eficiente para reorganizar las filas, liberar el espacio desperdiciado y reajustar el HWM hacia abajo: el comando SHRINK.

El proceso consta de dos fases lógicas ejecutadas internamente por el motor:

1. Fase de movimiento de filas (Row Movement): Desplaza las filas de la parte superior del segmento hacia los huecos libres en la parte inferior.

2. Fase de ajuste del HWM: Bloquea brevemente la tabla para bajar el puntero del HWM y liberar el espacio sobrante al tablespace.

Para poder reubicar físicamente los registros y alterar sus ROWID, es obligatorio activar la propiedad ROW MOVEMENT en la tabla:

ALTER TABLE t_hwm_test ENABLE ROW MOVEMENT;

Procedemos a ejecutar el shrink. Este comando liberará el espacio de vuelta al tablespace de forma inmediata.

ALTER TABLE t_hwm_test SHRINK SPACE;

Nota: Si se tratara de una tabla crítica en un entorno productivo de alta concurrencia, podemos ejecutar ALTER TABLE t_hwm_test SHRINK SPACE COMPACT; para realizar solo la fase 1 sin bloquear el HWM, y posteriormente ejecutar el comando completo en una ventana de mantenimiento corta.

Volvemos a recopilar estadísticas y evaluamos nuevamente el estado físico de la tabla en el diccionario de datos.

-- Ejecutamos nuevamente estadísticas sobre la tabla
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_HWM_TEST');

-- Verificamos el total de bloques de la tabla nuevamente 

SELECT t.table_name, t.num_rows, s.blocks AS total_allocated_blocks FROM user_tables t JOIN user_segments s ON t.table_name = s.segment_name WHERE t.table_name = 'T_HWM_TEST';

 

El total_allocated_blocks se redujo drásticamente de 20480 que tenia inicialmente a 2048, alineándose al volumen real actual de la tabla.

Consideraciones

El comando SHRINK SPACE es la herramienta ideal para combatir la fragmentación en bases de datos modernas, sin embargo, como DBAs debemos considerar las siguientes pautas antes de su aplicación en producción:

1. Índices: A diferencia de un ALTER TABLE MOVE, el comando SHRINK SPACE mantiene los índices de la tabla en estado VALID, ya que actualiza de manera interna las modificaciones de los ROWID. No es necesario un rebuild posterior.

2. Restricciones: No se puede aplicar SHRINK sobre tablas con índices basados en funciones, vistas materiales con logs de refresco o tablas que contengan columnas de tipo LONG.

3. Alternativa Tradicional: Si el tablespace no cuenta con ASSM (lo cual es raro en versiones 19c/23ai), la alternativa obligada sigue siendo el clásico ALTER TABLE MOVE seguido de un ALTER INDEX REBUILD.

Comprender el comportamiento del High Water Mark en Oracle 19c es fundamental para evitar diagnósticos erróneos ante caídas de rendimiento repentinas tras procesos de purga masiva. Como DBAs, nuestro rol no solo consiste en liberar espacio en disco, sino en garantizar la eficiencia de las operaciones de E/S.

Saludos.

Read More

Cómo generar y analizar trazas en Oracle con DBMS_MONITOR y TKPROF

Cómo generar y analizar trazas en Oracle con DBMS_MONITOR y TKPROF

   mayo 18, 2026       Luis Felipe

---

En muchas ocasiones nos encontramos con problemas de rendimiento en nuestras aplicaciones y necesitamos saber con exactitud qué consultas SQL están consumiendo la mayor cantidad de recursos en nuestra base de datos Oracle.

Una de las herramientas más potentes y clásicas para resolver este rompecabezas es la combinación de DBMS_MONITOR (para capturar la actividad) y TKPROF (para formatear y entender el archivo de traza resultante).

A partir de las versiones modernas de Oracle, el uso de DBMS_SUPPORT o ALTER SESSION SET SQL_TRACE ha quedado en desuso en favor del paquete DBMS_MONITOR, el cual nos da un control mucho más granular.

Identificar y activar la traza

Para este ejemplo, identificaremos el SID y el SERIAL# de la sesión que queremos auditar desde la vista V$SESSION. Una vez obtenidos los valores, ejecutamos el siguiente bloque PL/SQL desde una cuenta con privilegios de Administrador (como SYS o SYSTEM):

BEGIN
  DBMS_MONITOR.session_trace_enable(
    session_id => &target_sid,
    serial_num => &target_serial,
    waits      => TRUE,   -- Captura de eventos en v$session_wait / Extended Trace
    binds      => TRUE    -- Captura el volcado de variables bind en el dump
  );
END;
/

A partir de este momento, toda la actividad de esa sesión se registrará en un archivo de traza (.trc) en el servidor.

Localizar el archivo generado (.trc)

Para encontrar el archivo generado en el servidor, podemos lanzar la siguiente consulta en SQL*Plus o SQL Developer mientras la sesión siga activa para conocer la ruta exacta del directorio de diagnóstico:

SELECT
    p.tracefile
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
WHERE s.sid = &target_sid;

Activar la traza con binds => TRUE y waits => TRUE genera un impacto en el rendimiento de la sesión afectada y puede llenar rápidamente el sistema de archivos si el proceso procesa millones de filas. Úsalo con moderación en entornos de producción.

Desactivar el rastreo

Cuando la sesión termine de ejecutar los procesos lentos, debemos proceder a deshabilitar el rastreo para no saturar el almacenamiento:

BEGIN
  DBMS_MONITOR.session_trace_disable(session_id => &target_sid, serial_num => &target_serial);
END;
/

Formatear la traza con TKPROF

El archivo .trc original es difícil de leer directamente de forma manual. Aquí es donde entra TKPROF, una utilidad de línea de comandos del sistema operativo que convierte el archivo plano en un reporte legible.

Abrimos la terminal de nuestro servidor (Linux/Windows) y ejecutamos la sintaxis básica:

tkprof ora_12345_pdb.trc user_report.txt sys=no sort=prsela,exeela,fchela

• sys=no: Filtra y descarta las consultas recursivas del diccionario de datos ejecutadas por el usuario SYS, aislando únicamente el SQL emitido por la aplicación.
  
  
• sort=prsela,exeela,fchela: Ordena el archivo de salida priorizando las sentencias SQL que acumularon el mayor tiempo transcurrido (Elapsed Time) durante las fases de Parsing, Ejecución y Fetch.

Interpretación de los resultados

Al abrir nuestro archivo user_report.txt, veremos bloques de información para cada sentencia ejecutada, organizados en una tabla de estadísticas como esta:

• Elapsed: Es el tiempo total que tardó la consulta de cara al usuario. Si es muy alto comparado con el tiempo de CPU, significa que la consulta estuvo esperando por recursos (bloqueos, lectura de disco, etc.).
  
• Query: Representa las lecturas lógicas en memoria (Buffers). Un número extremadamente alto aquí suele indicar que a la consulta le falta un índice adecuado y está realizando un Full Table Scan.
• Disk: Lecturas físicas en el disco duro. Las lecturas en disco siempre penalizan el rendimiento.

El uso combinado de DBMS_MONITOR y TKPROF sigue siendo una de las metodologías más certeras y recomendadas por los expertos para realizar Tuning de SQL, ya que nos muestra la realidad de lo que ocurre sin suposiciones.

Saludos.

Read More

Evento enq: JQ - contention ¿Por qué se detienen mis Jobs?

Evento enq: JQ - contention ¿Por qué se detienen mis Jobs?

   mayo 13, 2026       Luis Felipe

---

Recientemente, me encontré con un escenario donde los procesos de Job Queue de una base de datos Oracle parecían no avanzar. Al revisar las vistas de rendimiento, identifiqué una espera prolongada en el evento enq: JQ - contention

Para entender el problema, primero debemos recordar cómo gestiona Oracle los jobs. A partir de Oracle 11g y 12c, la arquitectura de Oracle Scheduler separa la programación de la ejecución real mediante varios procesos de fondo:

• CJQ0 (Job Queue Coordinator): Es el director de orquesta. Lee la tabla de jobs, determina cuál debe ejecutarse y levanta procesos esclavos para realizar el trabajo.

• Jnnn / Wnnn (Job/Worker Slaves): Son los procesos que ejecutan el código PL/SQL del job.

El Enqueue JQ es un bloqueo de nivel de instancia utilizado para serializar el control de los trabajos. Oracle lo usa para asegurar que un job no sea ejecutado por dos procesos a la vez y para gestionar la creación/terminación de los procesos esclavos.

Cuando vemos el evento enq: JQ - contention, significa que hay una fila de procesos esperando por ese "permiso". Esto suele ocurrir por saturación del coordinador, límites bajos en JOB_QUEUE_PROCESSES o jobs "zombies" que mantienen el bloqueo sin liberar el recurso.

Al ejecutar una consulta a v$session para monitorear los procesos tipo Job (J001), observamos lo siguiente:

SELECT sid, serial#, event, p1, p2, p3, seconds_in_wait FROM v$session WHERE program LIKE '%(J001)%';

Observen la columna SECONDS_IN_WAIT. El proceso lleva más de 12 horas esperando. El valor de P1 (1246822406) traducido a ASCII corresponde precisamente a 'JQ'.

Podemos obtener el valor de P1 traducido a ASCII, con el siguiente query:

SELECT chr(bitand(1246822406,-16777216)/16777216)|| chr(bitand(1246822406,16711680)/65536) AS Enqueue_Type FROM dual;

Para identificar el SPID a nivel de sistema operativo:

SELECT s.sid, p.spid, s.program, s.event FROM v$session s JOIN v$process p ON s.paddr = p.addr WHERE s.program LIKE '%(J001)%';

 En este escenario, al revisar otros procesos esclavos (Workers W...), detectamos sesiones con un LAST_CALL_ET muy alto, lo que indica que están ejecutando tareas que se quedaron colgadas o son ineficientes:

 SELECT p.spid, s.username, s.program, s.last_call_et FROM v$session s JOIN v$process p ON s.paddr = p.addr WHERE s.sid = 4170;

Si la contención es crítica y los jobs están paralizados, la solución más efectiva es "resetear" el coordinador de jobs:

1. Verificamos el valor actual del parámetro job_queue_processes.
   

   sqlplus / as sysdba

   SHOW PARAMETER "job_queue_processes";

    

2. Establecemos el parámetro a cero.
   

   ALTER SYSTEM SET job_queue_processes=0;

3. Si existen procesos a nivel de OS que no mueren, identificarlos por su SPID y terminarlos.

4. Volvemos al valor original, en este caso teníamos lo establecido en 2000.
   

   ALTER SYSTEM SET job_queue_processes=2000;

Esto obliga al CJQ0 a reiniciarse y liberar los enqueues persistentes.

Como hemos visto, el evento enq: JQ - contention es una señal clara de que el motor de jobs está saturado o bloqueado por un proceso ineficiente. En entornos críticos, especialmente en RAC, no basta con aumentar el número de procesos; es fundamental monitorear el LAST_CALL_ET de los workers para identificar tareas "zombies" que secuestran al coordinador. Realizar un reseteo controlado de la cola de trabajos suele ser la solución más efectiva para normalizar el servicio sin afectar al resto de las instancias del cluster.

Read More

Configurar y Utilizar Oracle Flashback Technology

Configurar y Utilizar Oracle Flashback Technology

   mayo 05, 2026       Luis Felipe

---

Oracle Flashback es una de las herramientas más útiles para un DBA, ya que nos permite recuperar datos o estados de la base de datos sin necesidad de recurrir a backups (RMAN). A diferencia de RMAN, Flashback trabaja con los datos actuales para "deshacer" errores en segundos.

IMPORTANTE: Todos los ejemplos prácticos de esta guía han sido validados y probados en Oracle Database 23ai.

Para utilizar Flashback Database, la base de datos debe estar en modo ARCHIVELOG. Primero verificamos el estado:

SELECT log_mode, flashback_on FROM v$database;

En este caso no se encuentra activo ni el modo archivelog de la base por lo tanto tampoco el modo flashback. Procedemos con la configuración de la Fast Recovery Area (FRA), activamos ambos modos.

--Definir espacio y ruta de la FRA ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G; ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/opt/oracle/fast_recovery_area';

--Definir retención (ej. 24 horas en minutos) ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET = 1440;

--Activar modos (Requiere modo MOUNT) 

SHUTDOWN IMMEDIATE; 

STARTUP MOUNT; 

ALTER DATABASE ARCHIVELOG; 

ALTER DATABASE FLASHBACK ON; 

ALTER DATABASE OPEN;

No todos los Flashback funcionan igual. Se dividen principalmente por el origen de los datos que utilizan:

Basados en Segmentos de UNDO

Estos dependen del UNDO_TABLESPACE. Si los datos en el undo han sido sobrescritos, la operación fallará.

• Flashback Query: Permite consultar datos en un tiempo pasado (AS OF TIMESTAMP/SCN).

  
  
  

  

SELECT last_name, salary FROM hr.employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MINUTE) WHERE department_id = 60;

• Flashback Version Query: Ideal para auditoría, permite ver los cambios de una fila.

--Muestra todas las versiones de una fila en un intervalo

SELECT versions_starttime, versions_operation, salary

FROM hr.employees

VERSIONS BETWEEN TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR) AND MAXVALUE

WHERE employee_id = 101;

• Flashback Table: Revierte una tabla completa. (Ojo: requiere Row Movement).

-- Indispensable para evitar error ORA-08189 

ALTER TABLE hr.employees ENABLE ROW MOVEMENT; 

-- Revertir tabla a un punto exacto FLASHBACK TABLE hr.employees TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE);

Basados en Flashback Logs (FRA)

Dependen de la Fast Recovery Area y capturan cambios a nivel de bloque físico.

• Flashback Database: Revierte toda la base de datos. Es la opción más drástica y rápida ante errores masivos de despliegue.

-- Si estás en un entorno PDB (CDB/PDB), primero cierra la PDB: 

ALTER PLUGGABLE DATABASE FREEPDB1CLOSE; 

-- Ejecuta el retroceso 

FLASHBACK PLUGGABLE DATABASE FREEPDB1TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '30' MINUTE); 

-- Abre con resetlogs para fijar la nueva línea de tiempo 

ALTER PLUGGABLE DATABASE FREEPDB1OPEN RESETLOGS;

Basados en Metadata (Recycle Bin)

• Flashback Drop: Recupera tablas borradas con DROP. Oracle simplemente renombra el objeto en el diccionario de datos.

-- Si borraste la tabla por accidente: 

DROP TABLE hr.locations CASCADE CONSTRAINTS; 

-- Recupérala de la "papelera" 
FLASHBACK TABLE hr.locations TO BEFORE DROP;

TIP: A veces, recordar una fecha exacta o un SCN es complicado. Puedes crear un "alias" para el estado actual de tu base de datos antes de realizar cambios importantes.

-- Crear un punto de retorno normal 

CREATE RESTORE POINT antes_del_cambio;

Puedes consultar los puntos guardados

SELECT name, scn, time, guarantee_flashback_database FROM v$restore_point; 

Para regresar al punto especifico creado

-- Para una tabla específica 
FLASHBACK TABLE hr.locations TO RESTORE POINT antes_del_cambio;  

Recuerda que la tecnología Flashback no es solo una funcionalidad de recuperación, sino una estrategia de continuidad de negocio. Mientras que un backup tradicional en RMAN es tu seguro de vida ante un desastre físico o de hardware, Flashback es tu mejor aliado contra el error humano cotidiano.

Read More