Pages

Monday, January 30, 2023

expdp error "ORA-39021: Database compatibility version 19.xx.0.0.0 is not supported."

=============
Issue
=============
Error during expdp version 19.x
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-39001: invalid argument value
ORA-39021: Database compatibility version 19.4.0.0.0 is not supported.


=============
Investigation
=============
Per Oracle technote "DataPump Reports ORA-39021: Database compatibility version 19.X.0.0.0 is not supported (Doc ID 2610939.1)"

After upgrade to 19.x, compatible parameter is increased, because RU or an RUR changed the COMPATIBLE parameter.

SQL> show parameter compatible
NAME          TYPE        VALUE
------------- ----------- ----------
compatible    string      19.4.0

This is due to bug #30828205 - EXPDP IS FAILING WITH ORA-39021 WHEN COMPATIBLE IS SET AS 19.4.0.

Data Pump uses DBMS_UTILITY.DB_VERSION() to retrieve the current local database version.
Compatibility version was raised to the level of RU installation.
So database compatibility version was raised, but datapump version was not.
Thus the error "Database compatibility version 19.4.0.0.0 is not supported".

Solution

Upgrade to  19.11.0.0.210420DBRU.
In this version this bug was fixed

Workaround
Add parameter VERSION=19.0.0 to expdp command

Wednesday, January 18, 2023

How to copy stats from existing partition to a new partition

How to copy stats from existing partition to a new partition

--Copy Start from Old Partition to New partition
BEGIN
 DBMS_STATS.COPY_TABLE_STATS ('MY_OWNER', 'MY_TABLE', 'P_OLD', 'P_NEW', FORCE=>TRUE);
END;
/

--Lock stats. to avoid reset from scheduled stats collection
BEGIN
 DBMS_STATS.LOCK_TABLE_STATS('MY_OWNER','MY_TABLE');
END;
/

--Make stats incremental, when collecting stats. 
For big table, partitions were data was not changed, will not be involved again and again in stats collection, only partitions were data was changed, will be worked upon.
BEGIN
 DBMS_STATS.SET_TABLE_PREFS('MY_OWNER','MY_TABLE','INCREMENTAL','TRUE');
END;
/

--Gather table stats - on the table
BEGIN
 DBMS_STATS.gather_table_stats('MY_OWNER','MY_TABLE',CASCADE => TRUE);
END;
/