Message xxxx not found; No message file for product=RDBMS, facility=ORA

I was helping a colleague with datapump export/import. He exported a small table from one system and when he was trying to import to another system, he got the following error:

This was a weird error when I first looked at it. Not sure what’s happening. The source database and target database both are on 19c. The source is with 19.10.0.0.0 and the target is on a higher maintenance number — 19.14.0.0.0. We have tried to export different tables and all had the same issue when trying to do the import. The parameter compatible is set to 19.0.0.0.0.

Because we had another task pending on this, we had to use the traditional exp/imp to finish the job.

After that I started to think about the error, and realized it actually had two issues:

  1. ORA-39002 error itself.
  2. impdp was trying to display the error messages for ORA-39002, ORA-31694 & ORA-39097 but it couldn’t. That’s why we saw “Message 39002 not found; No message file for product=RDBMS, facility=ORA“.

For ORA-39002, it has the meaning “invalid operation”

[oracle@joetestvm ]$ oerr ora 39002
39002, 00000, "invalid operation"
// *Cause:  The current API cannot be executed because of inconsistencies
//          between the API and the current definition of the job.
//          Subsequent messages supplied by DBMS_DATAPUMP.GET_STATUS
//          will further describe the error.
// *Action: Modify the API call to be consistent with the current job or
//          redefine the job in a manner that will support the specified API.

According to the article from my Oracle support — Impdp Fails With ORA-39002: Invalid Operation (Doc ID 2482971.1), likely the source database has a higher timezone version while the target database has a lower one. To check it, we can use the following queries:

SQL> Select name,value$ from props$ where name='DST_PRIMARY_TT_VERSION';

NAME                           VALUE$
------------------------------ ----------------------------------------
DST_PRIMARY_TT_VERSION         32

SQL> SELECT DBMS_DST.get_latest_timezone_version FROM dual;

GET_LATEST_TIMEZONE_VERSION
---------------------------
                         32

For the message not found issue, it’s likely the following two files are missing, or wrong permissions/ownership or even corrupted.

[oracle@joetestvm ]$ ls -lart $ORACLE_HOME/rdbms/mesg/oraus*
-rw-r--r--. 1 oracle oinstall 6417501 Jul 20  2021 /opt/oracle/190000/rdbms/mesg/oraus.msg
-rw-r--r--. 1 oracle oinstall 1431552 Jul 20  2021 /opt/oracle/190000/rdbms/mesg/oraus.msb

We can search the finary file to see the message for the error 39002:

[oracle@joetestvm mesg]$ strings oraus.msg|grep 39002
39002, 00000, "invalid operation"

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s