Troubleshooting the Downgrade of Oracle Database

Use this troubleshooting information to address issues that may occur when downgrading Oracle Database.

This section contains known errors that may occur during downgrades, and workarounds to address those errors.

Parent topic: Downgrading Oracle Database to an Earlier Release

Errors Downgrading Oracle Database Components with catdwgrd.sql Script

Use this section to troubleshoot errors when you run the catdwgrd.sql script during a downgrade, such as ORA-20001: Downgrade cannot proceed.

The catdwgrd.sql script downgrades all Oracle Database components in the database to the major release from which you originally upgraded. This script must run before the Data Dictionary can be downgraded. If you encounter any problems when you run the script, then correct the causes of the problems, and rerun the script.

Errors you can see include ORA-39709 and ORA-06512. When these errors occur, downgrades cannot proceed.

  • Cause: One or more components that must be downgraded before proceeding with the Data Dictionary downgrade did not downgrade.

  • Action: Review the log files to determine what errors occurred before the catdwgrd.sql script halted, and the downgrade was stopped.

Review these examples to understand how to correct this issue.

Errors typically describe what you must do to fix the issue that is preventing the downgrade to complete. Follow the instructions in the error message. After you have fixed the cause of the error, rerun the catdwgrd.sql script.

For example, If the CDB downgrade fails during the downgrade of CDB$ROOT due to a check, then follow the instructions in the error message to fix the condition error. After you fix the error, rerun catdwgrd.sql with catcon.pl. Use the -c option to run the command with the inclusion list 'CDB$ROOT PDB1'. Use the -r option to run the command first on the PDB, and then on CDB$ROOT. For example: 

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -d $ORACLE_HOME/rdbms/admin -e -b catdwgrd -l /scratch/rac/downgradeLogs -c 'CDB$ROOT, PDB1, PDB2' -r catdwgrd.sql

Example 10-3 ORA-20001 Error Due To ORA-06512

Your downgrade stops. When you review the log files, you find that catdwgrd.sql terminates on this error: 

DECLARE * ERROR at line 1: ORA-20001: Downgrade cannot proceed - 
Unified Audit Trail data exists. Please clean up the data first 
using DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL. ORA-06512: at line 65 
ORA-06512: at line 42

You must purge the unified audit trial on CDB$ROOT and on all PDBs.

For example:

  1. Look for the presence of unified audit trails:

    SQL> SELECT COUNT(*) FROM UNIFIED_AUDIT_TRAIL;
COUNT(*)
----------
      4538

  2. Purge the audit trail. on the CDB.

    For example, where the audit trail type is DBMS_AUDIT.MGMT.AUDIT: 

    EXEC DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL DBMS_AUDIT_MGMT.AUDIT

  3. Run catdwngrd.sql on CDB$ROOT. If PDBs still have unified audit data, then the script fails with ORA20001: 

     62        execute immediate
 63        'select count(*) from audsys.'||'"'||tab_name||'"' into no_rows;
 64
 65        -- If audit trail has some data, raise the application error
 66        IF no_rows > 0 THEN
 67          RAISE_APPLICATION_ERROR(-20001, ErrMsg);
 68        END IF;
 69      END IF;
 70    END IF;
 71  EXCEPTION
 72    WHEN NO_DATA_FOUND THEN
 73     NULL;
 74    WHEN OTHERS THEN
 75     RAISE;
 76  END;
 77  /
DECLARE
*
ERROR at line 1:
ORA-20001: Downgrade cannot proceed - Unified Audit Trail data exists.Please
clean up the data first using DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL.
ORA-06512: at line 75

  4. Connect to individual PDBs, and find if they have unified audit trails. Clear the unified audit trail for all PDBs. For example, The PDB named PDB1 has unified audit trails:

    ALTER SESSION SET container = PDB1;

SQL> SELECT COUNT(*) FROM UNIFIED_AUDIT_TRAIL;
 COUNT(*)
----------
      1330

  5. Identify the unified audit trails: 

    SQL> CREATE TABLE UA_DATA AS (SELECT * FROM UNIFIED_AUDIT_TRAIL);

  6. Purge the audit trails.

    In this example, the audit trail type is DBMS_AUDIT_MGMT.AAUDIT_TRAIL_UNIFIED, the USE_LAST_ARCH_TIMESTAMP value is set to FALSE, so that all audit records are deleted, without considering last archive timestamp, and the CONTAINER value is set to DBMS_AUDIT_MGMT.CONTAINER_ALL, so that audit records on all PDBs are purged. 

    BEGIN
  DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
   AUDIT_TRAIL_TYPE           =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
   USE_LAST_ARCH_TIMESTAMP    =>  FALSE,
   CONTAINER                  =>  DBMS_AUDIT_MGMT.CONTAINER_ALL
END;
/

  7. Rerun catdwngrd.sql at the PDB and CDB level. For example: 

    $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -c 'CDB$ROOT,PDB1' -d $ORACLE_HOME/rdbms/admin -e -b catdwgrd -l /u01/oracle/product/19.0.0/downgrade_logs -r catdwgrd.sql

  8. Repeat the process of finding and purging audit trails and run catdwgrd.sql until the script completes successfully on the CDB and PDBs, and you no longer see ORA-20001 errors in logs

Related Topics

Parent topic: Troubleshooting the Downgrade of Oracle Database

Downgrading Oracle Grid Infrastructure (Oracle Restart) After Successful or Failed Upgrade

To downgrade Oracle Restart, you must deconfigure and then reinstall Oracle Grid Infrastructure. You can then add back the databases and services.

Related Topics

Parent topic: Troubleshooting the Downgrade of Oracle Database

Errors Downgrading Databases with Oracle Messaging Gateway

If you downgrade a database configured with Oracle Messaging Gateway , then you can encounter ORA-02303 errors.

If you downgrade an Oracle Database that contains Oracle Messaging Gateway objects, then you can encounter the following error: 

ORA-02303: cannot drop or replace a type with type or table dependents
  • Cause The catrelod.sql script is attempting to reload Oracle Messaging Gateway objects of a different type than the earlier Oracle Database release.
  • Action No action. You can ignore this error.

Parent topic: Troubleshooting the Downgrade of Oracle Database