Introduction
During the ADOP (Online Patching) process of Oracle E-Business Suite (EBS), you might encounter the following error message while running Autoconfig as part of adop phase=prepare:
ORA-20001: Synonym does not point to an editioning view: ADOP_VALID_NODES
This issue occurs when the synonym for the ADOP_VALID_NODES view is not correctly mapped to an editioned view.
This is particularly common if the editioned view was manually dropped or modified, leaving the synonym pointing to an invalid object.
In this blog, we’ll go through the steps to resolve this issue and restore the correct mappings for the ADOP_VALID_NODES synonym, allowing the ADOP process to proceed successfully.
Cause:
The error arises when the synonym for ADOP_VALID_NODES is pointing to an incorrect or non-editioned view, which causes Oracle EBS to fail in recognizing it as an editioned view.
This situation often occurs when:
The editioned view ADOP_VALID_NODES# has been dropped or modified manually.
A non-editioned view with the same name (ADOP_VALID_NODES#) was created manually.
Oracle ADOP requires an editioned view, which is a special type of view tied to Oracle’s editioning system, to manage data across patching cycles.
Solution:
Step 1: Check the Synonym and View
First, confirm that the synonym ADOP_VALID_NODES exists and points to the view APPLSYS.ADOP_VALID_NODES# (the editioned view). However, if Oracle fails to recognize this as an editioned view, you will need to recreate the editioned view correctly.
Step 2: Drop the Incorrect View
If the ADOP_VALID_NODES# view is not an editioned view, it will need to be dropped. This can happen if the view was created manually or incorrectly. Execute the following command to drop the view:
SQL> drop view ADOP_VALID_NODES#;
Step 3: Run the ad_zd_table.upgrade Procedure
Once the incorrect view is dropped, use the ad_zd_table.upgrade procedure to recreate the correct editioned view and update the synonym.
Execute the following PL/SQL procedure to restore the editioned view:
SQL> exec ad_zd_table.upgrade('APPLSYS', 'ADOP_VALID_NODES');
This procedure will create an editioned view and also establish the necessary synonym in the APPS schema.
Step 4: Verify the Synonym and Editioned View
Once the upgrade is successful, it’s important to verify that the synonym is correctly pointing to the editioned view. You can use the ADZDSHOWTAB.sql script to check the view mappings.
Execute the script:
SQL> @ADZDSHOWTAB.sql ADOP_VALID_NODES
This should show the following output:
SYNONYM_NAME -> TABLE_OWNER TABLE_NAME -------------------------------------------- ADOP_VALID_NODES -> APPLSYS ADOP_VALID_NODES#
EV Column Mapping
VIEW_COLUMN -> TABLE_COLUMN --------------------------- NODE_NAME ===> NODE_NAME#1 CONTEXT_NAME = CONTEXT_NAME ZD_EDITION_NAME = ZD_EDITION_NAME ZD_SYNC = ZD_SYNC
This confirms that the synonym is now properly pointing to the editioned view.
Step 5: Verify Editioned View Columns
To further confirm that the editioned view has been properly set up, use the ADZDSHOWEV.sql script to check the column mappings:
SQL> @ADZDSHOWEV.sql ADOP_VALID_NODES
This will display the column mappings between the view and the underlying table, such as:
VIEW_COLUMN -> TABLE_COLUMN --------------------------- NODE_NAME ===> NODE_NAME#1 CONTEXT_NAME = CONTEXT_NAME ZD_EDITION_NAME = ZD_EDITION_NAME ZD_SYNC = ZD_SYNC
Step 6: Run adop phase=prepare Again
With the editioned view now in place, you can retry running the ADOP phase. The adop phase=prepare process should now complete successfully, as the ADOP_VALID_NODES synonym is correctly mapped to the editioned view.
Run the following command to continue with the ADOP process:
adop phase=prepare
Conclusion
By following the above steps, you should be able to resolve the ORA-20001: Synonym does not point to an editioning view error during the ADOP phase=prepare. The root cause was the synonym for the ADOP_VALID_NODES view pointing to an incorrect or non-editioned view. By dropping the faulty view and recreating the editioned view using the ad_zd_table.upgrade procedure, we restored the necessary mappings for Autoconfig to complete successfully.
This process should now allow your ADOP procedure to run without further issues.
