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
