Extraer archivos .JPG desde campo Blob Oracle 10g, 11g y 12c

 En el presente blog, dejare como validar que tipo de imagen se encuentra almacenada en columna BLOB , cuanto pesa la imagen y como puede ser extraida de la base de datos hacia un directorio del sistema operativo.

select para saber que tipo de imagen se encuentra almacenada

select case when dbms_lob.substr(MCIMAGE1,3,1) = hextoraw('FFD8FF') then 'JPG' end as image_type
FROM esquema.Tabla
         WHERE     MCDATE  = '20181201'
               AND MCLOCATION = '0902E'
               and mctime = '090251';                
   

select para saber cuanto pesa en KB la imagen           
SELECT max(round(DBMS_LOB.getlength(MCIMAGE1)/1024)) KB ,max(MCSIZE1)
   FROM  esquema.Tabla
         WHERE     MCDATE BETWEEN '20181201' AND '20181231'
               AND MCLOCATION IN ('0902E', '0902S');

Plsql Anonimo para extraer los datos

/* Formatted on 10/12/2021 3:45:08 PM (QP5 v5.336) */

declare
    l_file       UTL_FILE.FILE_TYPE;
    l_buffer     RAW (32767);
    l_amount     BINARY_INTEGER := 32767;
    l_pos        INTEGER := 1;
    l_blob       BLOB;
    l_blob_len   INTEGER;

    CURSOR C1 IS
          SELECT MCID,
               MCDATE,
               MCTIME,
               MCIMAGE1,
               MCID||'-'||mclocation||'-'||mcdate||'-'||mctime||'-C01-'||to_char(rownum)||'.JPG' nombre1
          FROM esquema.Tabla
         WHERE     MCDATE BETWEEN '20181201' AND '20181201'
               AND MCLOCATION IN ('0902E', '0902S');
BEGIN
    FOR R1 IN C1
    LOOP
        l_blob := r1.MCIMAGE1;
        l_blob_len := DBMS_LOB.getlength (r1.MCIMAGE1);
        l_pos := 1;

        -- Open the destination file.

       -- IF l_blob_len < 32767
       -- THEN
            l_file :=
                UTL_FILE.FOPEN ('export_Dir',R1.nombre1,'W',32767);

            -- until complete.

            WHILE l_pos < l_blob_len
            LOOP
                DBMS_LOB.READ (l_blob,
                               l_amount,
                               l_pos,
                               l_buffer);

                UTL_FILE.put_raw (l_file, l_buffer, TRUE);

                l_pos := l_pos + l_amount;
            END LOOP;

            -- Close the file.

            UTL_FILE.FCLOSE (l_file);
       -- END IF;
    END LOOP;
EXCEPTION
    WHEN OTHERS
    THEN
        -- Close the file if something goes wrong.
        IF UTL_FILE.IS_OPEN (l_file)
        THEN
            UTL_FILE.FCLOSE (l_file);
        END IF;
        RAISE;
END;

espero les sea de utilidad , dejen sus comentarios.

Reparacion ORA-01578 y ORA-26040 en columna BLOB

Hola , en esta ocacion les comento, un cliente tiene una tabla que contiene una columna de imagenes (blob), resulta que la columna que contiene las imagnes se corrompio y se recibia el siguiente error:

 

y al realizar cualquier accion de adicion/modificacion sobre la tabla, se recibia el siguiente error:

ORA-01578: ORACLE data block corrupted (file # 7, block # 34982)
ORA-01110: data file 7: '+DATA/SISTEMA_01/DATAFILE/imagen_lob.424.1032123411'
ORA-26040: Data block was loaded using the NOLOGGING option

Hagamos una pausa y veamos lo siguiente:
1) Significa que los datos ya estaban dañados, principalmente debido a corrupciones lógicas,
no es probable que sea una falla del hardware. Pero la pregunta clave es:
¿Podemos recuperar los datos dañados?

 

 2)Por defecto, las tablas e indices se crean en logging, a menos que se aplique la clausula durante la creación de la tabla o el tablespace.

Este parametro es completamente ignorado si la base de datos está configurada con FORCE LOGGING.

LOGGING:
Cada vez que la base ejecuta un DML o un DDL o un COMMIT, Oracle escribe  en el redo log buffer, que despues es enviado a los redo log files, convirtiendoce en los archive log files (si la base esta en archivelog mode).

Cuando la transacción es realizada permanente con un COMMIT, se crea un nuevo registro en el redolog buffer y asigna un nuevo SCN  al cambio.

Este es el mecanismo de proteccion que tiene la base para que en caso de tener que hacer un recover, cuente con toda la información a ser aplicada a los datafiles almacenada desde los redolog o archive log.

NOLOGGING:

Solo afecta a los direct-path load que se hagan a la base de datos. No afecta a los DML convencionales como INSERT, UPDATE, DELETE.

 como validar los bloques corruptos en un datafile:

 select * from dba_extents where file_id = 7 and 34982 between block_id and block_id + blocks - 1;

Solucion: 

1) crear tabla de validacion registros malos
CREATE TABLE esquema.corruptos_blobs
(corrupt_rowid rowid, err_num number)
TABLESPACE REPARA_TABLA_IMAGEN;


2) procedimiento que valida los registos con problemas,
-- FOTO      &&blob_columna
-- ESQUEMA   &tabla_owner
-- FOTOS     &tabla_con_blob

declare
error_1578 exception;
error_1555 exception;
error_22922 exception;
pragma exception_init(error_1578,-1578);
pragma exception_init(error_1555,-1555);
pragma exception_init(error_22922,-22922);
num number;
begin
for cursor_lob in (select rowid r, &&blob_columna from &tabla_owner..&tabla_con_blob) loop
begin
num := dbms_lob.instr (cursor_lob.&&blob_columna , hextoraw ('889911')) ;
exception
when error_1578 then
insert into desarrollo.corruptos_blobs values (cursor_lob.r, 1578);
commit;
when error_1555 then
insert into desarrollo.corruptos_blobs values (cursor_lob.r, 1555);
commit;
when error_22922 then
insert into desarrollo.corruptos_blobs values (cursor_lob.r, 22922);
commit;
end;
end loop;
end;


3) marcar los registros con problemas
update ESQUEMA.FOTOS
set foto = empty_blob()
where rowid in (select corrupt_rowid from equema.corruptos_blobs);
commit;

4) realizar exportacion de los registros
[oracle@boston]$ vi exparameter.par
directory=mydirectorio
dumpfile=tbl_fotos.dmp
logfile=tbl_fotos.log
tables=totos
query=foto:"where rowid not in (select corrupt_rowid from esquema.corruptos_blobs)"

expdp \"usuario/clave\" parfile=exparameter.par
exp \"usuario/clave\" TABLES=fotos query=\"where rowid not in \(select corrupt_rowid from esquema.corruptos_blobs\)\" FILE=exp_tabla.dmp log=exp_tabla.log




para crear usuario de base de datos con priv. de subsetting y data masking

create user subset identified by oracle account unlock;
GRANT CONNECT, RESOURCE TO subset;
GRANT ALTER ANY INDEX TO subset;
GRANT ALTER ANY TABLE TO subset;
GRANT ALTER SYSTEM TO subset;
GRANT ANALYZE ANY TO subset;
GRANT CREATE ANY DICTIONARY TO subset;
GRANT CREATE ANY DIRECTORY TO subset;
GRANT CREATE ANY INDEX TO subset;
GRANT CREATE ANY PROCEDURE TO subset;
GRANT CREATE ANY TABLE TO subset;
GRANT CREATE PROCEDURE TO subset;
GRANT CREATE SEQUENCE TO subset;
GRANT CREATE SESSION TO subset;
GRANT CREATE TABLE TO subset;
GRANT CREATE TABLESPACE TO subset;
GRANT CREATE TYPE TO subset;
GRANT DROP ANY INDEX TO subset;
GRANT DROP ANY TABLE TO subset;
GRANT DROP TABLESPACE TO subset;
GRANT EXECUTE ANY PROCEDURE TO subset;
GRANT EXECUTE ON DBMS_AQADM TO subset;
GRANT EXECUTE ON DBMS_CRYPTO TO subset;
GRANT INSERT ANY TABLE TO subset;
GRANT LOCK ANY TABLE TO subset;
GRANT RESUMABLE TO subset;
GRANT SELECT ANY DICTIONARY TO subset;
GRANT SELECT ANY TABLE TO subset;
GRANT UNLIMITED TABLESPACE TO subset;

Listar usuarios en SOLARIS

awk -F':' '{ print $1 }' /etc/passwd

Como Monitorear una Standby

1) validar en la primaria el numero maximo de secuencia de archive log

SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG;


2) validar el numero maximo de secuencia aplicada en standby database

SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE APPLIED=’YES’;


3) validacion del las secuencias aplicadas
SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG
  WHERE APPLIED=’YES’
ORDER BY SEQUENCE# ;

en la primaria ejecutar cualquiera de las 2 lineas para forzar un switch a los redolog

alter system switch logfile;
ALTER SYSTEM CHECKPOINT;

4) ejecutar los pasos 1 y 2 para corroborar el cambio en la secuencia


5) en la standby database , validar los estados de las secuencias
SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG
    WHERE APPLIED='YES'
ORDER BY SEQUENCE#  ;

hagamos un export con expdp/impdp

1) Para bajar la base

1.       Verificar las variables de ambiente

2.       sqlplus sys/oracle_4U as sysdba

3.       shutdown immediate;

4.       lsnrctl stop

2) Para subir la base modo restrictive

             1. Startup restrict;

3) para crear un directorio

            1.       create or replace directory RESPALDO as ‘/u01/direccion…’;
             2.       grant read, write on directory RESPALDO to <USUARIO> ;

4) Checar la vista V$NLS_PARAMETERS

1)     Tomar los valores de NLS_LANGUAGE, NLS_TERRITORY, NLS_CHARACTERSET
     Y formar el export de la siguiente manera

select trim(parameter)

                       ||'='

            ||trim(value)

                      from v$nls_parameters
                  where parameter like '%NLS_LANGUAGE%'
                        or parameter like '%NLS_TERRITORY%'
                       or parameter like '%NLS_CHARACTERSET%';

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 

5) para hacer un export full

expdp system/oracle_4U full=y directory=RESPALDO dumpfile=salida.dmp logfile=salida.log

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 

impdp system/oracle_4U directory=RESPALDO content=all dumpfile=salida2.dmp schemas=OE,HR logfile=salida_imp.log

6) iniciar la base y el listener

                 Startup  -- inicia la base

                Lsnrctl start

sql muy utiles en migracion de bases

--cuantos indices invalidos
select owner,count(1)
from dba_indexes
where status = 'INVALID'
group by owner;
--cuales son los indices invalidos
select owner,
       index_name,
       index_type,
       table_owner,
       table_name,
       table_type,
       tablespace_name
from dba_indexes
where status = 'INVALID';

-- cuantos son los objetos invalidos
select owner,object_type,count(1)
from dba_objects
where status = 'INVALID'
group by owner,object_type
order by 1,2;

-- cuales son los objetos invalidos
select owner,
       object_name,
       created,
       last_ddl_time
from dba_objects
where status = 'INVALID'
order by 1,2;

-- Paquetes con bodies sin que no tengan sus correspondientes headers
select unique owner,name
from dba_source a
where type = 'PACKAGE BODY'
  and not exists (select null
                  from dba_source b
                  where a.owner = b.owner
                    and a.name = b.name
                    and b.type = 'PACKAGE') ;

-- Constraints deshabilitadas
select owner,
       case constraint_type
          when 'P' then 'PRIMARY_KEY'
          when 'R' then 'FOREIGN_KEY'
          when 'U' then 'UNIQUE'
          when 'C' then 'CHECK'
       end constraint_type, 
       count(1)
from dba_constraints
where status = 'DISABLED'
group by owner,constraint_type
order by 1,2;

select owner,
       constraint_name,
       constraint_type,
       table_name
from dba_constraints
where status = 'DISABLED'
order by 1,2;

--Triggers deshabilitados
select owner,
       trigger_name,
       trigger_type,
       triggering_event,
       table_owner,
       table_name
from dba_triggers
where status = 'DISABLED'
order by 1,2;

-- para contar registros de todas las tablas
SELECT 'SELECT '
       || q'{'}'
       ||OWNER
       || '.'
       || OBJECT_NAME
       || q'{'}'
       || ' as Origen, COUNT(*) as registros'
       || ' FROM '
       ||OWNER ||'.'
       || OBJECT_NAME
       || ' UNION'  --,
      -- OBJECT_TYPE
FROM ALL_OBJECTS
WHERE OBJECT_TYPE='TABLE'
   and owner not in  ('SYS','SYSTEM','DBSNMP');