Full Database Export Failing With ORA-00980

While trying to do a full export for 10.2.0.3 database, we were getting following errors

About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
EXP-00008: ORACLE error 980 encountered
ORA-00980: synonym translation is no longer valid
EXP-00000: Export terminated unsuccessfully

So as to debug the problem, we set errorstack for ORA-00980 as follows and tried exporting again

alter system set events '980 trace name errorstack level 3';

Export job again failed with error but this time it generated a trace file in user_dump_dest location. We got the following sql as failing statement in tracefile

ORA-00980: synonym translation is no longer valid
Current SQL statement for this session:
SELECT SYNNAM, DBMS_JAVA.LONGNAME(SYNNAM2) SYNNAM2,DBMS_JAVA.LONGNAME(SYNTAB) SYNTAB,TABOWN, TABNODE, PUBLIC$, SYNOWN, SYNOWNID,
 TABOWNID, SYNOBJNO FROM   SYS.EXU9PTS      WHERE  TABOWNID = :1 ORDER  BY SYNTIME

A possible cause is that the package DBMS_JAVA is INVALID or absent in database so the public synonym DBMS_JAVA is no longer valid.Checking DBA_REGISTRY confirms that the JVM is in invalid state.

SQL> select COMP_NAME,VERSION,STATUS from dba_registry;
CATALOG  SYS      VALID        10.2.0.3.0 Oracle Database Catalog Views
CATPROC  SYS      VALID        10.2.0.3.0 Oracle Database Packages and Types
JAVAVM   SYS      INVALID      10.2.0.3.0 JServer JAVA Virtual Machine <--- Invalid state

We used Note 276554.1 – “How to Reload the JVM in 10.1.0.X and 10.2.0.X” in order to correct this problem.

Key things to look when diagnosing export/import issues

1)Check if you are using correct syntax. Use exp help=y or imp help=y to get the correct syntax.

2)Check if you are using correct exp/imp utility (version) against the database. Use following metalink note for compatibility matrix

Note 132904.1 – Compatibility Matrix for Export And Import Between Different Oracle Versions
3)Check DBA_REGISTRY view to see if the components are in valid state.
4) Try setting errorstack if you are facing “ORA-“ error.
5)Try setting trace=y parameter in export/import. This will generate a trace file (similar to sql trace) which can be used to diagnose the problems

e.g exp system/passwd full=y file=expdat.dmp log=exp.log trace=y

Tags: 10gdatabaseexpimporacle