Operating System - HP-UX
1748256 Members
3871 Online
108760 Solutions
New Discussion юеВ

Re: Oracle 8.1.7.3 export gets errors - ORA-00904.

 
SOLVED
Go to solution
Gino Castoldi_2
Honored Contributor

Oracle 8.1.7.3 export gets errors - ORA-00904.

Hi,

HP-UX 11.0 Oracle 8.1.7.3 VPO 6.14.

We recently upgraded the database from
version 8.0.6 to 8.1.7.3.
Everything appears fine (VPO is running ok).

The first time I tryed an export it failed with these errors:
EXP-00008: ORACLE error 904 encountered
ORA-00904: invalid column name
EXP-00000: Export terminated unsuccessfully

The command I'm using is this:
exp system/manager full=y file=/tmp/ora817.dmp
compress=y log=/tmp/exporacle817.log

Any ideas as to what could be wrong?

10 points to any good answer.
Thank you
Gino

10 REPLIES 10
Ian Lochray
Respected Contributor
Solution

Re: Oracle 8.1.7.3 export gets errors - ORA-00904.

Two things to check are:
1. Have you run catexp.sql
2. Are you running the 8.1.7.3 version of exp. Make sure you are not still picking up the 8.0.6 version.
Gino Castoldi_2
Honored Contributor

Re: Oracle 8.1.7.3 export gets errors - ORA-00904.

Hi Ian,


I haven't run catexp.sql. I'm not sure what that sql program does. (I have basically no Oracle experience).

I'll check to make sure we're using the correct
version of the export program.

10 points ot any good answer.
Thank you
Gino


Gino Castoldi_2
Honored Contributor

Re: Oracle 8.1.7.3 export gets errors - ORA-00904.

Hi,

I checked and we are using the correct version
of the exp program for 8.1.7.

Any ideas as to what could be wrong?

10 points to any good answer.
Thank you
Gino
Massimo Bianchi
Honored Contributor

Re: Oracle 8.1.7.3 export gets errors - ORA-00904.

hi Gino,
catexp.sql is a oracle sql script that create the proper view for the export.

If it is run, it has no side effects. I did it on a VPO installation times ago with no problem.

You should run it as "sys".

Here follows a note from metalink.
Check this only if running $ORACLE_HOME/rdbms/admin/catexp.sql from sqlplus
does not solve the problem.

fix:

How to handle ORA-00904 errors on Export
========================================

When running full database export the following errors will be returned:

exp80 user/password full=y file=D:\DB-Backup\ORCL\expORCL.dmp
log=D:\DB-Backup\ORCL\expORCL.log consistent=y buffer=40960000

EXP-00008: ORACLE error 904 encountered
ORA-00904: invalid column name




To get a clue what has force these ORA-00904 message, follow the following
steps:

1. Connect as privileged user and run the following command inside an
SQLPLUS session:

SQL> alter system set events='904 trace name errorstack';

-------------------------------- Note ------------------------------------

This event will work starting with Oracle8i. If running a Oracle database
version below Oracle8i, you'll get ORA-02065 errors returned, when
specifying this event. In Oracle versions below 8i you'll have to insert
the following entry into INIT.ORA

event="904 trace name errorstack"

--------------------------------------------------------------------------

2. Retry the FULL export. When the ORA-00904 occurs, a trace file will be
written to the storage location specified by the INIT.ORA parameter
'user_dump_dest'. As soon as the ORA-00904 has been returned to your
export session, you can abort the export and examine the trace information.

3. Disable event tracing by running the following command:

SQL> alter system set events='904 trace name errorstack off';

On Oracle versions below 8.1.5 return the event entry from INIT.ORA

4. Examine the trace file:

*** SESSION ID:(9.3) 2001.11.21.15.28.00.494
*** 2001.11.21.15.28.00.494
ksedmp: internal or fatal error
ORA-00904: invalid column name
Current SQL statement for this session:
SELECT fname,fsize,maxextend,inc FROM sys.exu8fil WHERE tsid = :1
===========

a) a problem with object EXU8FIL has been detected
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
In this example, there's a problem with the EXE8FIL view. To examine,
whether this object exist, run the following command:

SQL> select owner,object_name,object_type,object_id,status
from dba_objects
where object_name='EXU8FIL';

If the problem is on the EXU8FIL object you simply can recreate the
object by running the CATEXP.SQL script.

b) a problem with object DBMS_JAVA or DBMS_JAVA_TEST
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ksedmp: internal or fatal error
ORA-00904: invalid column name
Current SQL statement for this session:
SELECT SYNNAM, DBMS_JAVA.LONGNAME(SYNNAM), DBMS_JAVA.LONGNAME(SYNTAB), ...

On systems where the Java Server has been installed the trace file could
contain information on a failing select statement on DBMS_JAVA packages.


HTH,
Massimo

It is mentioned in the 817 upgrade and in the 8173 patchset, so you shuold have run it. Double check the instructions.

HTH,
Massimo
T G Manikandan
Honored Contributor

Re: Oracle 8.1.7.3 export gets errors - ORA-00904.

The main problem with the error is that the objects created by catexp.sql have become corrupt.

You can re-run catexp.sql to resolve the errors!
Yogeeraj_1
Honored Contributor

Re: Oracle 8.1.7.3 export gets errors - ORA-00904.

hi Gino,

I think a DBA should read the migration guide before attempting a migration -- all of the required steps for PROPERLY upgrading a database are contained therein. This is just the TIP of the iceberg. Sure, I can fix this individual problem, but you'll be back tomorrow with something else (quick fix for this = catexp.sql, run it)

You should do it *right*

I would suggest that you go through the Migration guide again and check whether all the steps were done correctly.

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Ian Lochray
Respected Contributor

Re: Oracle 8.1.7.3 export gets errors - ORA-00904.

During the 8.1.7.3 upgrade, catexp is run as part of catalog.sql. This implies that catalog was either not run or failed to complete. Either way there sems to be a bigger problem that it first appears.
Yogeeraj_1
Honored Contributor

Re: Oracle 8.1.7.3 export gets errors - ORA-00904.

hi again,

see metalink note: Note:62291.1

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: Oracle 8.1.7.3 export gets errors - ORA-00904.

Attached metalink Note 62291.1.

Did you run this script: u0800060.SQL ?

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)