OraFAQ Forum: Backup & Recovery » repair/rebuild SYS.HISTGRM

Home » RDBMS Server » Backup & Recovery » repair/rebuild SYS.HISTGRM

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Re: repair/rebuild SYS.HISTGRM [message #265730 is a reply to message #265725]

Fri, 07 September 2007 03:24

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Which corruption?
Which Oracle version (4 decimals)

Regards
Michel

Report message to a moderator

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

Report message to a moderator

Re: repair/rebuild SYS.HISTGRM [message #265737 is a reply to message #265734]

Fri, 07 September 2007 03:54

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

ORA-01499: table/index cross reference failure - see trace file

What is in this trace file?

Regards
Michel

Report message to a moderator

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

Report message to a moderator

Re: repair/rebuild SYS.HISTGRM [message #265791 is a reply to message #265738]

Fri, 07 September 2007 07:22

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Shutdown the database, start it up in restrict mode, drop and recreate index SYS.I_H_OBJ#_COL#.
To get the DDL use:

Code: [Select all] [Show/ hide]

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

Report message to a moderator

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.

Report message to a moderator

Re: repair/rebuild SYS.HISTGRM [message #265819 is a reply to message #265814]

Fri, 07 September 2007 08:37

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Ok, try rebuild instead.

Regards
Michel

Report message to a moderator

Re: repair/rebuild SYS.HISTGRM [message #265833 is a reply to message #265819]

Fri, 07 September 2007 09:26

cmd1234
Messages: 8
Registered: September 2007
Location: Kent

Junior Member

I had tried that.

Looking at a Chinese site, it says do

startup migrate;

and then do the commands

So I tried that and it worked

Thank you very much for your help, it has been greatly appreciated, and I might just add, that I have used Oracle SR support system for support in the past, and you are a lot better and faster Cool

Report message to a moderator

Re: repair/rebuild SYS.HISTGRM [message #265834 is a reply to message #265725]

Fri, 07 September 2007 09:27

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>you are a lot better and faster
And CHEAPER!

Report message to a moderator

Re: repair/rebuild SYS.HISTGRM [message #265850 is a reply to message #265833]

Fri, 07 September 2007 10:44

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Thanks for the feedback, it is appreciated.

Regards
Michel

Report message to a moderator

Re: repair/rebuild SYS.HISTGRM [message #265958 is a reply to message #265834]

Sat, 08 September 2007 01:18

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

And CHEAPER!
Not Cheaper ...it is Free Service Smile

Report message to a moderator

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:

Code: [Select all] [Show/ hide]

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

Report message to a moderator

Re: repair/rebuild SYS.HISTGRM [message #266027 is a reply to message #266019]

Sun, 09 September 2007 01:31

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Just searched in dictionary and saw this is the only index of the table.

Otherwise I'd asked which object has id 210 (0xd2):

Quote:

seg/obj: 0xd2 csc: 0x00.3c5de4f itc: 2 flg: - typ: 1 - DATA

Otherwise I'd asked to which object belongs block pointed by:

Quote:

row not found in index tsn: 0 rdba: 0x004005f9

tablespace 0 (SYSTEM), relative file 4, block 1529 (0x5f9).

Regards
Michel

Report message to a moderator

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:

Code: [Select all] [Show/ hide]

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:

Code: [Select all] [Show/ hide]

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

But my system is different from the cmd1234 (i.e., looking for object_id = 210)

Or... by object belonging block pointed by...

Code: [Select all] [Show/ hide]

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

Previous Topic:	RMAN error
Next Topic:	Installing Oracle Client for 9i down existing Database

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 02 16:38:38 CDT 2025