Um Probleminha bem legal, Uma Base sem archivelog com um bloco corrompido o que fazer?
Bem se fosse uma tabela qualquer apenas isolaríamos o bloco com o dbms_repair.
Mais se esse objeto corrompido fosse a tabela obj$ do esquema sys?
Se fossemos utilizar o dbms_repair daria:
ERROR at line 1:
ORA-00701: object necessary for warmstarting database cannot be altered
ORA-06512: at “SYS.DBMS_REPAIR”, line 419
ORA-06512: at line 1
Mais como isolar esse bloco e deixar o acesso a essa tabela funcionando corretamente?
SQL*Plus: Release 10.2.0.4.0 – Production on Wed Feb 12 16:45:11 2014
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> select /*+full(obj$)*/ count(*) from obj$;
select /*+full(obj$)*/ count(*) from obj$
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 1, block # 87367)
ORA-01110: +DG15/unihm/datafile/system.456.831649887′
SQL> exec dbms_repair.skip_corrupt_blocks(‘SYS’,’OBJ$’);
BEGIN dbms_repair.skip_corrupt_blocks(‘SYS’,’OBJ$’); END;
*
ERROR at line 1:
ORA-00701: object necessary for warmstarting database cannot be altered
ORA-06512: at “SYS.DBMS_REPAIR”, line 419
ORA-06512: at line 1
SQL> set pages 100
SQL> SELECT * FROM BOOTSTRAP$ WHERE SQL_TEXT LIKE ‘%OBJ$%’;
LINE# OBJ#
———- ———-
SQL_TEXT
——————————————————————————–
18 90724
CREATE TABLE OBJ$(“OBJ#” NUMBER NOT NULL,”DATAOBJ#” NUMBER,”OWNER#” NUMBER NOT N
ULL,”NAME” VARCHAR2(30) NOT NULL,”NAMESPACE” NUMBER NOT NULL,”SUBNAME” VARCHAR2(
30),”TYPE#” NUMBER NOT NULL,”CTIME” DATE NOT NULL,”MTIME” DATE NOT NULL,”STIME”
DATE NOT NULL,”STATUS” NUMBER NOT NULL,”REMOTEOWNER” VARCHAR2(30),”LINKNAME” VAR
CHAR2(128),”FLAGS” NUMBER,”OID$” RAW(16),”SPARE1″ NUMBER,”SPARE2″ NUMBER,”SPARE3
” NUMBER,”SPARE4″ VARCHAR2(1000),”SPARE5″ VARCHAR2(1000),”SPARE6″ DATE) PCTFREE
10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 16384 NEXT 106496 MINEX
TENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 90724 EXTENTS (FILE 1 BLOCK 94
505))
Edite o arquivo pfile e adicione o evento
event=”10231 trace name context forever, level 10″
SQL> startup pfile=’/tmp/pfile_new’ force
ORACLE instance started.
Total System Global Area 622149632 bytes
Fixed Size 2230912 bytes
Variable Size 398460288 bytes
Database Buffers 213909504 bytes
Redo Buffers 7548928 bytes
Database mounted.
Database opened.
SQL> select /*+full(obj$)*/ count(*) from obj$;
COUNT(*)
———-
74503
Espero ter ajudado, Ate a próxima!