| repair/rebuild SYS.HISTGRM [message #265725] |
Fri, 07 September 2007 03:09  |
cmd1234
Messages: 8
Registered: September 2007
Location: Kent
|
Junior Member |
|
|
|
I have got a corrupted SYS.HISTGRM$ how do I go about repairing it or dropping and rebuilding it
|
|
|
|
|
|
| Re: repair/rebuild SYS.HISTGRM [message #265734 is a reply to message #265730] |
Fri, 07 September 2007 03:49  |
cmd1234
Messages: 8
Registered: September 2007
Location: Kent
|
Junior Member |
|
|
Oracle Database 10g Release 10.1.0.3.0 - Production
in a trace file I get
ksedmp: internal or fatal error
Current SQL statement for this session:
delete from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3
when I do
SQL> ANALYZE TABLE HISTGRM$ VALIDATE STRUCTURE CASCADE;
ANALYZE TABLE HISTGRM$ VALIDATE STRUCTURE CASCADE
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file
and part of that trace log says.
row not found in index tsn: 0 rdba: 0x004005f9
|
|
|
|
|
|
| Re: repair/rebuild SYS.HISTGRM [message #265738 is a reply to message #265737] |
Fri, 07 September 2007 03:59 |
cmd1234
Messages: 8
Registered: September 2007
Location: Kent
|
Junior Member |
|
|
/oracle/admin/rebus/udump/rebus_ora_9195.trc
Oracle Database 10g Release 10.1.0.3.0 - Production
ORACLE_HOME = /oracle/product/10.1.0/db_1
System name: Darwin
Node name: rebusdb
Release: 7.9.0
Version: Darwin Kernel Version 7.9.0: Wed Mar 30 20:11:17 PST 2005; root:xnu/xnu-517.12.7.obj~1/RELEASE_PPC
Machine: Power Macintosh
Instance name: rebus
Redo thread mounted by this instance: 1
Oracle process number: 22
Unix process pid: 9195, image: oracle@rebusdb (TNS V1-V3)
*** 2007-09-07 09:45:45.385
*** SERVICE NAME:(SYS$USERS) 2007-09-07 09:45:45.371
*** SESSION ID:(147.442) 2007-09-07 09:45:45.371
row not found in index tsn: 0 rdba: 0x004005f9
env: (scn: 0x0000.03d07517 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 statement num=0 parent xid: xid: 0x0000.000.00000000 scn: 0x0000.00000000 $
col 0; len 2; (2): c2 52
col 1; len 2; (2): c1 03
col 2; len 6; (6): 00 40 d8 91 00 00
Block header dump: 0x0040d891
Object id on Block? Y
seg/obj: 0xd2 csc: 0x00.3c5de4f itc: 2 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0003.02f.00000476 0x008004f0.024e.35 C--- 0 scn 0x0000.000bd449
0x02 0x0003.003.0000d1b2 0x008007a6.51dd.40 C--- 0 scn 0x0000.03c4b544
data_block_dump,data header at 0x532a805c
===============
tsiz: 0x1fa0
hsiz: 0x1c
pbl: 0x532a805c
bdba: 0x0040d891
76543210
flag=-------K
ntab=2
nrow=3
frre=2
fsbo=0x1c
fseo=0x138f
avsp=0x1f57
tosp=0x1f57
0xe:pti[0] nrow=1 offs=0
0x12:pti[1] nrow=2 offs=1
0x16:pri[0] offs=0x1f87
0x18:pri[1] offs=0x138f
0x1a:pri[2] sfll=-1
block_row_dump:
tab 0, row 0, @0x1f87
tl: 25 fb: K-H-FL-- lb: 0x0 cc: 2
curc: 1 comc: 1 pk: 0x0040d891.0 nk: 0x0040d891.0
col 0: [ 2] c2 52
col 1: [ 2] c1 03
tab 1, row 0, @0x138f
tl: 20 fb: -CH-FL-- lb: 0x0 cc: 4 cki: 0
col 0: [ 2] c1 03
col 1: [ 1] 80
col 2: [ 3] c2 02 24
col 3: [ 6] c5 2b 09 4f 28 4b
end_of_block_dump
Table 1 : Slot 0
end_of_block_dump
Table 1 : Slot 0
|
|
|
|
|
|
| Re: repair/rebuild SYS.HISTGRM [message #265814 is a reply to message #265791] |
Fri, 07 September 2007 08:13 |
cmd1234
Messages: 8
Registered: September 2007
Location: Kent
|
Junior Member |
|
|
SQL> drop index "SYS"."I_H_OBJ#_COL#"
2 ;
drop index "SYS"."I_H_OBJ#_COL#"
*
ERROR at line 1:
ORA-00701: object necessary for warmstarting database cannot be altered
I used startup restrict.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: repair/rebuild SYS.HISTGRM [message #266019 is a reply to message #265791] |
Sun, 09 September 2007 00:26 |
mson77
Messages: 208
Registered: August 2007
Location: Brazil
|
Senior Member |
|
|
| Michel Cadot wrote on Fri, 07 September 2007 09:22 | Shutdown the database, start it up in restrict mode, drop and recreate index SYS.I_H_OBJ#_COL#.
To get the DDL use:
select dbms_metadata.get_ddl('INDEX','I_H_OBJ#_COL#','SYS') from dual;
Beware! Operation in SYS schema, it is better to take a cold backup before.
Regards
Michel
|
Hello Michel,
Sorry for any inconvenience... but...
How did you realize that cmd1234 should drop/recreate index SYS.I_H_OBJ#_COL# ?
I read carefully the posted trace file as recommended by "ORA-01499: table/index cross reference failure - see trace file" but I did not read any reference to this index file.
Thank you,
mson77
|
|
|
|
|
|
| Re: repair/rebuild SYS.HISTGRM [message #266028 is a reply to message #266027] |
Sun, 09 September 2007 01:42 |
mson77
Messages: 208
Registered: August 2007
Location: Brazil
|
Senior Member |
|
|
APPLAUD!!!
mson77
Below is my experiment following the Michel Cadot guidance:
SQL> desc dba_ind_columns;
Name Null? Type
----------------------------------------- -------- ----------------------------
INDEX_OWNER NOT NULL VARCHAR2(30)
INDEX_NAME NOT NULL VARCHAR2(30)
TABLE_OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
COLUMN_NAME VARCHAR2(4000)
COLUMN_POSITION NOT NULL NUMBER
COLUMN_LENGTH NOT NULL NUMBER
CHAR_LENGTH NUMBER
DESCEND VARCHAR2(4)
SQL> col index_name format a30;
SQL> col table_name format a30;
SQL> col column_name format a10;
SQL> col table_name format a30;
SQL> set linesize 160;
SQL> select table_name,index_name,table_name,column_name from dba_ind_columns where table_name='HISTGRM$';
TABLE_NAME INDEX_NAME TABLE_NAME COLUMN_NAM
------------------------------ ------------------------------ ------------------------------ ----------
HISTGRM$ I_H_OBJ#_COL# HISTGRM$ OBJ#
HISTGRM$ I_H_OBJ#_COL# HISTGRM$ COL#
I also tried search for object id:
SQL> desc dba_objects;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL> select object_name from dba_objects where object_id=210;
OBJECT_NAME
--------------------------------------------------------------------------------
I_VIEWCON1
SQL> select object_name from dba_objects where data_object_id=210;
OBJECT_NAME
--------------------------------------------------------------------------------
I_VIEWCON1
Or... by object belonging block pointed by...
SQL> desc dba_extents;
Name Null? Type
----------------------------------------------------------------------------------------- -------- ------------------------------------------------------------
OWNER VARCHAR2(30)
SEGMENT_NAME VARCHAR2(81)
PARTITION_NAME VARCHAR2(30)
SEGMENT_TYPE VARCHAR2(18)
TABLESPACE_NAME VARCHAR2(30)
EXTENT_ID NUMBER
FILE_ID NUMBER
BLOCK_ID NUMBER
BYTES NUMBER
BLOCKS NUMBER
RELATIVE_FNO NUMBER
SQL> select * from dba_extents
2 where tablespace_name='SYSTEM' and
3 block_id=1529 and
4 relative_fno=4;
no rows selected
SQL>
[Updated on: Sun, 09 September 2007 02:39] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
