asked on

ORA-01410: invalid ROWID

I'm experiencing this error in a PL/SQL routine that has not changed (for months) and that makes no rowid or "where current of" manipulations. The statement that fails is an update of one table from another (correlated subquery) and both tables pass the "analyze...validate structure cascade" command.

The thing that has changed is that there is a new RAID group in the NT server I'm using and I shifted the indexes for these tables from one group to another. What I'm wondering is if there is some limit to the size of a tablespace or datafile on NT that could be causing this problem?

Also if someone has some other suggestions on how to debug this problem, I'd be interested.

mshaikh

Could you post your update statement please.

Thanks

rkogelhe

ASKER

UPDATE MDI_LINE SET T_LP=(
SELECT VALUE FROM MDI
WHERE MDI.MONTH=MDI_LINE.MONTH AND MDI.COUNTRY=MDI_LINE.COUNTRY AND
MDI.PL=MDI_LINE.PL AND
MDI.FAMILY=MDI_LINE.FAMILY AND
MDI.PLAN=MDI_LINE.PLAN AND
MDI.CH=MDI_LINE.CH AND INDIC='T_LP');

MDI:
Name Null? Type
------------------------------- -------- ----
MONTH VARCHAR2(6)
COUNTRY VARCHAR2(20)
PL VARCHAR2(2)
INDIC VARCHAR2(10)
VALUE NUMBER
PLAN VARCHAR2(10)
CH VARCHAR2(3)
FAMILY VARCHAR2(32)

MDI_LINE:
Name Null? Type
------------------------------- -------- ----
MONTH VARCHAR2(6)
COUNTRY VARCHAR2(20)
PL VARCHAR2(2)
T_LP NUMBER
BDU_LP NUMBER
BD72_LP NUMBER
T_CD NUMBER
BDU_CD NUMBER
BD72_CD NUMBER
T_LA NUMBER
BDU_LA NUMBER
BD72_LA NUMBER
BD_D NUMBER
BDR_D NUMBER
BDU_D NUMBER
BD72_D NUMBER
RR_D NUMBER
T_PP NUMBER
SD_D NUMBER
NDP72R NUMBER
BDU_RFP NUMBER
BDU_LCAIP NUMBER
PLAN VARCHAR2(10)
CH VARCHAR2(3)
FAMILY VARCHAR2(32)
CT VARCHAR2(25)

INDEX_NAME COLUMN_NAME
----------- ------------
IDX_MDI MONTH
IDX_MDI COUNTRY
IDX_MDI PL
IDX_MDI INDIC

OPERATION OPTIONS OBJECT_NAME POSITION
----------------- ----------- ------------ ---------
UPDATE STATEMENT 11
TABLE ACCESS FULL MDI_LINE 1
TABLE ACCESS BY ROWID MDI 2
INDEX RANGE SCAN IDX_MDI 1

I've tried rebuilding the index and reloading both tables.

Mark Geerlings

I don't know if there is an absolute limit on file sizes in Oracle on NT, but I've heard that you do not want them to be over 2 gig.

By the way, what version of Oracle are you running?

rkogelhe

ASKER

Oracle7 Server Release 7.3.4.3.0 - Production

I'm getting nervous now...

I tried rebuilding the index in another index tablespace and got the error:

SQL> Alter Index IDX_MDI REBUILD Tablespace INDX_MIDAS Storage (Initial 1931264 Next 167936 PctIncrease 0);

Alter Index IDX_MDI REBUILD Tablespace INDX_MIDAS Storage (Initial 1931264 Next 167936 PctIncrease 0)
*
ERROR at line 1:
ORA-01410: invalid ROWID

So I decided that I should drop the original table and rebuild the temporary tables:

SQL> DROP TABLE MDI CASCADE CONSTRAINTS ;

Table dropped.

SQL>
SQL> CREATE TABLE MDI (
2 MONTH VARCHAR2(6),
3 COUNTRY VARCHAR2(20),
4 PL VARCHAR2(2),
5 INDIC VARCHAR2(10),
6 VALUE NUMBER,
7 PLAN VARCHAR2(10),
8 CH VARCHAR2(3),
9 FAMILY VARCHAR2(32))
10 TABLESPACE DATA PCTFREE 10
11 STORAGE(INITIAL 10 M NEXT 10 M PCTINCREASE 0 )
12 PARALLEL (DEGREE 1 INSTANCES 1) NOCACHE;

Table created.

SQL>
SQL>
SQL> CREATE INDEX IDX_MDI ON
2 MDI(MONTH, COUNTRY, PL, INDIC)
3 TABLESPACE INDX_PSG PCTFREE 10
4 STORAGE(INITIAL 1675264 NEXT 147456 PCTINCREASE 0 ) ;

Index created.

SQL> exec EIFFEL_BRIO('199907')
begin EIFFEL_BRIO('199907'); end;

*
ERROR at line 1:
ORA-00600: internal error code, arguments: [17281], [600], [85558564], [], [], [], [], []
ORA-00600: internal error code, arguments: [526], [89838628], [5], [library cache], [2], [89838748], [1], []

SQL> analyze table MDI validate structure cascade;

Table analyzed.

Very worried...

ASKER CERTIFIED SOLUTION

mshaikh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mshaikh

Don't worry too much. You probably may have a lot of work ahead of you, but you most likely won't loose any data if any. The worst case, you may have to do a full export, recreate the database and import. Oracle can help you with that.

rkogelhe

ASKER

Phoned Oracle and they suggested an upgrade (surprise, surprise)... oh joy...

Mark Geerlings

Did you recompile your EIFFEL_BRIO procedure after dropping and recreating the tables it referenced? I've seen ORA-00600 errors sometimes when it was really an error in a large stored procedure that I was attempting to compile or recompile. I've also found it necessary to reboot Oracle sometimes when a recompile would cause ORA-00600 errors. After a reboot, the procedure would recompile fine. Maybe an upgrade will fix your problems, but if you can't upgrade immediately make sure that your procedure is valid and that you can recompile it without errors.

rkogelhe

ASKER

You may not be interested, but Oracle suggested that an upgrade to 7.3.4.4 would correct the problem and that one customer had success in increasing the shared pool size.

We upgraded last night and it was still giving the "invalid rowid" error. So as a desperation move I moved all the indexes back to the original volume. In the process certain indexes wouldn't rebuild and gave the invalid rowid error on rebuild. All the tables involved would have been in use during a power outage that we had on Sunday.

I truncated them (they were working tables) and moved all the indexes back to the original volume anyway. Everything is working now, but I suspect it is just luck. Perhaps the disk was damaged, but you'd figure RAID5 would get around that. I'm having our NT team give the server a once over.

mshaikh

Keep your eye on alertlog file and trace file. Power outage can have results that can be hidden until a corruption is encountered.

To feel completely cured after a power outage while database was up I really would do two things.

First, add a UPS to the system, and set it to automatically shut the database and system down if there was a power went out for a long time (15-30min or more depending on your UPS and power needs)

Second, after the UPS is installed, take a FULL offline backup of the database. Then take full export of the database. And then, delete all the datafile. Do a disk surface scan to mark bad block. Recreate, the database and do a full import.

This might seem like too much. But, I am a victim of just such a power failure. I noticed that I started get 600 error on indexes. After, I fixed that, I got errors in a table. Then, corruption in 4 of ~20 datafiles. Oracle, finally told me what I have suggested to you.

Try to do it in your first opurtunity.

rkogelhe

ASKER

mshaikh,

Thanks for helping on this one. Especially considering that I gave you wrong information on some of it.

The database did not go down (there was a network problem that made me think it did and I didn't check the v$ view).

We've upgraded again to 7.3.4.4.1 at Oracle's recommendation and we still get the error. After correcting the problem twice, I've noticed a pattern:

1) I rebuild indexes to move them to the new RAID array.
2) A batch procedure runs that truncates some of the tables rebuilt in the above procedure.
3) The batch procedure fails with an ORA-0600 or ORA-01410.
4) I attempt to move the indexes back to the old array using rebuild.
5) Some of the indexes fail to rebuild with ORA-01410.
6) I manually drop and recreate the indexes in the old tablespace.

So I'm thinking that either there's a problem with rebuild or some sort of problem with the new array. Our NT group has alarms on the RAID monitoring software and they say nothing is wrong and no alerts have been logged...

Ryan

Mark Geerlings

I'm not sure you want a RAID array for Oracle, especially if you are using RAID-5. Most OLTP system will perform slower on RAID-5, since it takes longer to calculate and write the control bits, than just a straight write to a non-RAID disk. RAID-0 (or disk mirroring) is usually better in terms of performance. Also, with RAID-5 what happens if you lose two drives at the same time? More logical drives that are mirrored also give you more ability to balance the system's I/O load than fewer large RAID-5 drives.

rkogelhe

ASKER

markgeer:

:)

I heartily agree with you. I've been griping incessently to the people who order the machines complaining about RAID. Unfortunately, they just ignore me and keep ordering more servers with hardware RAID. You can lead a horse to water, but you can't make it drink...

rkogelhe

ASKER

I still havn't solved this problem, but I hate leaving questions open :-)