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');

Trabajando con JOB en oracle 11g

hola, este dia en mi trabajo se me asigno la tarea de crear un job, para mi no es problema, pero que pasa si alguien no sabe?? , entonces les dejo este pequenio manual, ha sido probado en la version 11g.

1) primero vamos a crear una tabla la que llenaremos por medio del JOB

CREATE TABLE TMP_BORRAME_PRUEBA
(
  DESCRIPCION  VARCHAR2(100 BYTE),
  FECHA        DATE
);


2) creamos un procedimiento PL/SQL para ser llamado desde el JOB

CREATE OR REPLACE procedure prc_tmp_borrame
is
v_contador number(5) := null;
begin
    select count(1)
       into v_contador
         from tmp_borrame_prueba;
       
   insert into tmp_borrame_prueba
              (descripcion, fecha)
      values  ('Registros contados :'|| to_char(v_contador), sysdate);
      commit;          
end;
/


3) lo siguiente , puedes usarlo de acuerdo a tu conveniencia

--- para ver todos los jobs del esquema ONDE EJSTOY connected
SELECT JOB,
       SCHEMA_USER,
       LAST_DATE,
       NEXT_DATE,
       INTERVAL,
       WHAT
  FROM DBA_JOBS
     WHERE SCHEMA_USER = (SELECT USER FROM DUAL);



BEGIN
DBMS_JOB.BROKEN( <NUMERO JOB>, FALSE );  -- pone estado on line el JON [dependiendo tu caso]
DBMS_JOB.BROKEN( <NUMERO JOB>, TRUE );  -- pone estado OFF line el JON [dependiendo tu caso]
COMMIT;
END;



BEGIN
DBMS_JOB.REMOVE ( <NUMERO JOB> );  ---BORRA ELJOB
COMMIT;
END;


-- forma para crear un job.....
DECLARE
  X NUMBER;
BEGIN
    SYS.DBMS_JOB.SUBMIT
    ( JOB       => X
     ,WHAT      => -- colocamos entre [BEGIN] ..... [ END; ] el codigo pl que deseamos ejecutar
                   'begin
                    prc_tmp_borrame;
                    end;'
     ,NEXT_DATE => TO_DATE('31/10/2014 14:36:37','dd/mm/yyyy hh24:mi:ss') --- fecha y hora de inicio
     ,INTERVAL  => 'SYSDATE+1/1440'  --- intervalo de ejecucion
   --,interval  => 'TRUNC(SYSDATE+1)+3.5/24'  -- para ejecutarlo todos los dias a las 3:30 .....
     ,NO_PARSE  => FALSE  --- vale chonga :D
    );
    SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || TO_CHAR(X));
  COMMIT;
END;
/