This blog aims to support DBAs who encounter issues during the EBS application R12.2 ADOP phase=cleanup, specifically when it fails with the ORA-20001 error message: ‘Error while calling ad_zd.cleanupORA-01555: snapshot too old.’ The steps provided here offer guidance in identifying and addressing the challenges associated with the ADOP phase=Cleanup problem.
Below Step:
Issue:
We encountered the following issue during the application of the adpatch ADOP patching cycle when executing the adop phase=cleanup
[applmgr@r122 ~]$ adop phase=cleanupEnter the APPS password:
Enter the SYSTEM password:
Enter the WLSADMIN password:
Please wait. Validating credentials…
RUN file system context file: /u01/applmgr/UAT_TEST/fs2/inst/apps/UAT_TEST_r122/appl/admin/UAT_TEST_r122.xml
PATCH file system context file: /u01/applmgr/UAT_TEST/fs1/inst/apps/UAT_TEST_r122/appl/admin/UAT_TEST_r122.xml
************* Start of session *************
version: 12.2.0
started at: Wed May 24 2021 03:34:53
APPL_TOP is set to /u01/applmgr/UAT_TEST/fs2/EBSapps/appl
[STATEMENT] Using 4 workers (Default: 4, Recommended maximum limit: 62)Cleanup is not done in earlier session
[START 2021/05/24 03:35:27] adzdoptl.pl runADOP Session ID: 4
Phase: cleanup
Log file: /u01/applmgr/UAT_TEST/fs_ne/EBSapps/log/adop/4/adop_20210524_033421.log
[START 2021/05/24 03:35:48] cleanup phase [EVENT] [START 2021/05/24 03:35:52] Performing Cleanup steps [EVENT] [START 2021/05/24 03:35:57] Running CLEANUP ddls in ddl handler tableCalling: adpatch options=hotpatch,nocompiledb interactive=no console=no workers=4 restart=no abandon=yes defaultsfile=/u01/applmgr/UAT_TEST/fs2/EBSapps/appl/admin/UAT_TEST/adalldefaults.txt patchtop=/u01/applmgr/UAT_TEST/fs2/EBSapps/appl/ad/12.0.0/patch/115/driver logfile=cleanup.log driver=ucleanup.drv
ADPATCH Log directory: /u01/applmgr/UAT_TEST/fs_ne/EBSapps/log/adop/4/cleanup_20210524_033421/UAT_TEST_r122/log
[EVENT] [END 2021/05/24 03:39:43] Running CLEANUP ddls in ddl handler table [EVENT] Cleaning up ABORT DDL from DDL Handler Table [START 2021/05/24 03:39:54] Generating All DDL Report [EVENT] Report: /u01/applmgr/UAT_TEST/fs2/EBSapps/appl/ad/12.0.0/sql/ADZDALLDDLS.sql [EVENT] Output: /u01/applmgr/UAT_TEST/fs_ne/EBSapps/log/adop/4/cleanup_20210524_033421/UAT_TEST_r122/adzdallddls_20210524_033957.out [END 2021/05/24 03:39:58] Generating All DDL Report [EVENT] Calling cleanup in QUICK mode [WARNING] Cleanup may take a while. Please wait. [ERROR] Failed to execute sql statement :declare
result varchar2(10);
begin
ad_zd.cleanup(‘QUICK’);
exception
when others then
raise_application_error(-20001,’Error while calling ad_zd.cleanup’ || sqlerrm);
end;
[ERROR] SQLPLUS error: buffer=SQL*Plus: Release 10.1.0.5.0 – UAT_TESTuction on Wed May 24 03:40:01 2021
Copyright (c) 1982, 2005, applmgr. All rights reserved.
SQL> SQL> Connected.
SQL> declare
ERROR at line 1:
ORA-20001: Error while calling ad_zd.cleanupORA-01555: snapshot too old:
rollback segment number 2 with name “_SYSSMU2_735814084$” too small
ORA-06512: at line 7
Disconnected from applmgr Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit UAT_TESTuction
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[UNEXPECTED]Error occurred while calling cleanup plsql API [UNEXPECTED]Cleanup phase completed with errors/warnings. Please check logfilesLog file: /u01/applmgr/UAT_TEST/fs_ne/EBSapps/log/adop/4/adop_20210524_033421.log
adop exiting with status = 1 (Fail)
[applmgr@r122 ~]$Solution:
STEP:1
To address this issue, we conducted a thorough check and discovered that the undo tablespace was completely filled. Subsequently, we resolved the problem by extending the undo tablespace using the following steps
SQL> SELECT df.tablespace_name TABLESPACE,
df.total_space_mb TOTAL_SPACE_GB,
(df.total_space_mb – fs.free_space_mb) USED_SPACE_GB, fs.free_space_mb FREE_SPACE_GB,
ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE
FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,TRUNC((SUM(bytes) / 1048576/1024),2) TOTAL_SPACE_MB
FROM dba_data_files GROUP BY tablespace_name) df,
(SELECT tablespace_name, SUM(bytes) FREE_SPACE,TRUNC((SUM(bytes) / 1048576/1024),2) FREE_SPACE_MB
FROM dba_free_space GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+) ORDER BY fs.tablespace_name;
TABLESPACE_NAME TOTAL_GB USED_GB FREE_MB PCT_FREE
APPS_UNDOTS1 10 9.99 0.1 0.01
SQL> ALTER DATABASE DATAFILE ‘/u01/applmgr/UAT_TEST/data/undo01.dbf’ RESIZE 15G;
Database altered.
SQL>
STEP:3
Then again After increasing the tablespace,we have executed the adop phase=cleanup then it got completed with out any issues.
[applmgr@r122 ~]$ adop phase=cleanupEnter the APPS password:
Enter the SYSTEM password:
Enter the WLSADMIN password:
Please wait. Validating credentials…
RUN file system context file: /u01/applmgr/UAT_TEST/fs2/inst/apps/UAT_TEST_r122/appl/admi
n/UAT_TEST_r122.xml
PATCH file system context file: /u01/applmgr/UAT_TEST/fs1/inst/apps/UAT_TEST_r122/appl/ad
min/UAT_TEST_r122.xml
************* Start of session *************
version: 12.2.0
started at: Fri Feb 28 2021 04:11:18
APPL_TOP is set to /u01/applmgr/UAT_TEST/fs2/EBSapps/appl
[STATEMENT] Using 4 workers (Default: 4, Recommended maximum limit: 59)Cleanup is not done in earlier session
[START 2021/02/28 04:11:45] adzdoptl.pl runADOP Session ID: 4
Phase: cleanup
Log file: /u01/applmgr/UAT_TEST/fs_ne/EBSapps/log/adop/4/adop_20210228_041049.log
[START 2021/02/28 04:12:03] cleanup phase [EVENT] [START 2021/02/28 04:12:06] Performing Cleanup steps [EVENT] [START 2021/02/28 04:12:11] Running CLEANUP ddls in ddl handler tableCalling: adpatch options=hotpatch,nocompiledb interactive=no console=no workers=4 restart=no abandon=yes defaultsfile=/u01/applmgr/UAT_TEST/fs2/EBSapps/appl/admin/UAT_TEST/adalldefaults.txt patchtop=/u01/applmgr/UAT_TEST/fs2/EBSapps/appl/ad/12.0.0/patch/115/driver logfile=cleanup.log driver=ucleanup.drv
ADPATCH Log directory: /u01/applmgr/UAT_TEST/fs_ne/EBSapps/log/adop/4/cleanup_20210228_041049/UAT_TEST_r122/log
[EVENT] [END 2021/02/28 04:13:26] Running CLEANUP ddls in ddl handler table [EVENT] Cleaning up ABORT DDL from DDL Handler Table [START 2021/02/28 04:13:32] Generating All DDL Report [EVENT] Report: /u01/applmgr/UAT_TEST/fs2/EBSapps/appl/ad/12.0.0/sql/ADZDALLDDLS.sql [EVENT] Output: /u01/applmgr/UAT_TEST/fs_ne/EBSapps/log/adop/4/cleanup_20210228_041049/UAT_TEST_r122/adzdallddls_20210228_041335.out [END 2021/02/28 04:13:36] Generating All DDL Report [EVENT] Calling cleanup in QUICK mode [WARNING] Cleanup may take a while. Please wait. [EVENT] [END 2021/02/28 04:57:56] Performing Cleanup steps [END 2021/02/28 04:58:05] cleanup phase [START 2021/02/28 04:58:11] Generating AD_ZD_LOGS Report [EVENT] Report: /u01/applmgr/UAT_TEST/fs2/EBSapps/appl/ad/12.0.0/sql/ADZDSHOWLOG.sql [EVENT] Output: /u01/applmgr/UAT_TEST/fs_ne/EBSapps/log/adop/4/cleanup_20210228_041049/UAT_TEST_r122/adzdshowlog.out [END 2021/02/28 04:58:24] Generating AD_ZD_LOGS Report [END 2021/02/28 04:58:25] adzdoptl.pl runadop phase=cleanup – Completed Successfully
Log file: /u01/applmgr/UAT_TEST/fs_ne/EBSapps/log/adop/4/adop_20210228_041049.log
adop exiting with status = 0 (Success)
[applmgr@r122 ~]$Thanks for visiting a blog, hope you would have fixed the issue you have faced using above steps.
