adop phase=prepare failed with error "data dictionary corrupted"

 

After fresh clone when I run adop phase=prepare then prepare phase failed with the error "data dictionary corrupted".

Solution:

Follow the instructions in the "Fix data dictionary corruption" section of My Oracle Support Knowledge Document 1531121.1 .

* Fix Data Dictionary Corruption (Conditional)

1. Copy the script $AD_TOP/patch/115/sql/adzddmpfix.sql to the database tier.
2. On the database tier, connect to the database as SYSDBA.
3. Run the $AD_TOP/patch/115/sql/adzddmpfix.sql script.

[oraprod@deverpdb01 12.1.0]$ sqlplus / as sysdba

SQL> @adzddmpfix.sql

4. On the application tier, run the $AD_TOP/sql/ADZDDBCC.sql script again to identify whether data dictionary corruption is still present.

[applprod@deverpapp01 sql]$ cd $AD_TOP/sql

[applprod@deverpapp01 sql]$ sqlplus apps @ADZDDBCC.sql

a.    If no corruption is found, proceed with the upgrade or patching cycle.

b.    If corruption is still present, proceed to Step below as per the doc. 1531121.1

1. On the database tier, go to the $ORACLE_HOME/rdbms/admin directory.
2. Run the utlirp.sql script , following the usage instructions provided.

[oraprod@deverpdb01 12.1.0]$ Cd $ORACLE_HOME/dbms/admin

[oraprod@deverpdb01 admin]$ sqlplus / as sysdba

SQL> @utlrp.sql

3. On the application tier, run the $AD_TOP/sql/ADZDDBCC.sql script again to identify whether data dictionary corruption is still present.

[applprod@deverpapp01 sql]$ cd $AD_TOP/sql

[applprod@deverpapp01 sql]$ sqlplus apps @ADZDDBCC.sql

 

1. If no corruption is found, proceed with the upgrade or patching cycle.
2. If corruption is still present, contact Oracle Support and request a bug to be logged.

Now again run adop phase=prepare if it fails then try installing missing etcc patches at DB level.

Note: In my environment the prepare phase failed again with the same error.

- I will now install the missing etcc DB patches

- I have installed one missing etcc patch at DB level.

The output of the above script ADZDDBCC.sql was showing XXX_HR_MOBILE_EMPLOYEE_SUP_PO issue with the materialized view and oracle support asked me to drop and recreate this Materialized view. I have dropped and recreated it from SQL Developer then performed the below steps and issue got resolved.

On APP Node:

[applprod@deverpapp01 scripts]$ sqlplus apps/apps

SQL> exec sys.ad_grants.cleanup;

 [applprod@deverpapp01 scripts]$ cd $ADMIN_SCRIPTS_HOME

[applprod@deverpapp01 scripts]$ ./adautocfg.sh

On DB Node:

[oraprod@deverpdb01 UPG_deverpdb01]$ cd $ORACLE_HOME/appsutil/scripts/UPG_deverpdb01/

[oraprod@deverpdb01 UPG_deverpdb01]$ ./adautocfg.sh

On APP Node:

[applprod@deverpapp01 scripts]$ sqlplus apps/apps @$AD_TOP/sql/ADZDDBCC.sql   -àrun to see the MV is still has issue

[applprod@deverpapp01 scripts]$ adop phase=prepare  à completed successfully